文章图片



文章图片



文章图片



文章图片



文章图片



文章图片



文章图片



文章图片


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

案例：
下图 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 图表若密密麻麻不看也罢，要让数据可视化，先重新调整源表
  • 今天要给大家推荐的是5款软件|5款excel软件，每个都是同类软件中的个中翘楚
  • excel|去除Excel中的不可见字符，就这么几步...
  • excel|苹果5G芯片开发失败，华为呢？
  • excel|4款下半年将发布的顶级旗舰，华为苹果小米都有，堪称行业天花板
  • excel|Excel选择性粘贴，让办公简单化，可别只会ctrl+V？
  • excel|超乎想象的Excel函数，你以为的并不是你以为的
  • iqoo|excel函数公式大全！让工作变得迅速高效
  • 纸质表格|“数字化”助推火箭升空
  • excel|pandas读取Excel知识点合集来了！赶紧进来看看！