文章图片



文章图片



文章图片



文章图片



文章图片



文章图片



文章图片



文章图片


筛选这个功能 ， 某些情况下让人欢喜让人忧 ， 可以筛选固然方便 ， 但是如果一个表有各种计算项 ， 能否在筛选后忽略隐藏单元格进行计算？

案例：
下图 1 是某小区的馄饨团购表 ， 请按以下要求统计各项数据：

• 在 A 列仅对筛选后的可见行编号；
  
• D2 单元格：计算可见行的购买总数
  
• E2 单元格：计算可见行的总价
  
  

效果如图 2 所示 。


解决方案：
1. 在 A5 单元格中输入以下公式 --> 下拉复制公式：
=SUBTOTAL(3C$5:C5)



公式释义：

• subtotal 函数的作用是返回列表或数据库中的分类汇总；
  
• 语法为 SUBTOTAL(function_numref1[ref2
  ...) ， 以下是所有 function_num 对应的功能；在筛选的情况下 ， 该函数都只统计可见单元格内容；
  

• 从上图可以看出 ， 参数 3 的作用是统计区域内可见单元格的数量；
  
• 区域的起始单元格必须绝对引用 ， 结尾单元格须相对引用 ， 这样就能让区域随着公式下拉不断增加 ， 从而起到序列递增的效果
  

有关 subtotal 函数的详解 ， 请参阅 Excel函数(三) – Subtotal 函数智能求和 。
不管怎么筛选 ， 序号都会自动按显示行重新顺序编号 。



2. 在 D2 单元格中输入以下公式：
=SUBTOTAL(9D5:D36)
公式释义：

• 参数 9 的作用是求和
  

不管怎么筛选 ， 序号和总数都正确 。


3. 在 E2 单元格中输入以下公式：
=SUMPRODUCT(SUBTOTAL(3OFFSET(C4ROW(A1:A32)))*D5:D36*E5:E36)
公式释义：

• OFFSET(C4ROW(A1:A32))：以 C4 单元格为起点 ， 向下依次偏移 1 至 32 行 ， 即遍历品类的所有选项；
  
• SUBTOTAL(3...)：对遍历的数组忽略隐藏行计数 ， 就会生成由 1 和 0 组成的数组 ， 1 代表未隐藏 ， 0 则是隐藏行；
  
• SUMPRODUCT(...*D5:D36*E5:E36)：将上述数组与 D 和 E 的数值相乘求和 ， 只有值为 1 的能求出结果 ， 即计算出所有未隐藏的乘积之和
  • 上一页
  • 1
  • 2
  • 下一页

  
  

  • 在Excel表格的使用中|excel怎么快速批量删除所有空白行
  • 在word文档中绘制表格的过程中|word表格右边空白处打不了字的解决方法
  • Excel有着强大的计算能力|excel函数公式计算身份证号中的年龄
  • 怎么让excel单元格的内容自动换行
  • 在平时办公中|excel表格中如何快速提取部分数据
  • 大家在工作和生活中可能会遇到这样的事情|word做好表格忘记加标题怎么办？最简单实用的操作方法
  • 在Excel表格的使用中|excel一键填充2000个序列号
  • 在Excel表格的使用中|excel自动变回小数点+e+数字
  • Excel表格是大家平时工作中经常用到的办公软件|怎么把excel表格中多余的格子删掉
  • 教你用excel做漂亮的折线图