Excel办公实用小技巧分享

等风人

Excel办公实用小技巧分享
注：有些公式我也是抄作业，但是自己能理解怎么用，如果能帮助到大家，十分荣幸，如果有不对的地方或者有更好的小妙招，欢迎大家交流沟通。
#excel编号
#subtotal按条件求和、sumproduct按条件求和
1.编辑自动序号
一般情况下编辑序号用自动填充或者ROW函数进行编号。这种编号方法简单实用。我在实际工作中遇到了其他编号需求，这两种办法无法解决。现在分享给大家。
①非合并行自动编号
Excel表中数据过多的情况下，需要经常性筛选，每次筛选后需要重新编排序号，这时就可以用到subtotal函数，以下图为例。选定进出货列B列为非空单元格参考列，对序号列A列进行编号。公式为“=SUBTOTAL(103,$B$2:B2)”，这个公式的含义就是，只要进出货列B列的格子不空，就可以计数，序号列A列就递增+1。公式中第一个值103为功能代码，不用动，第二个为参考列，参考列的第一个值需要绝对引用，也就是字母和数字前面都需要加$符。
但是subtotal函数默认最后一列为汇总行，不计入筛选，这时候就需要对公式稍微修饰一下，改为“=SUBTOTAL(103,$B$2:B2)*1”，就可以避免最后一行不计入筛选的问题。


②含合并行批量编号
Excel表中含合并行时向下填充编号，遇到格式不同无法填充的问题，或者ROW函数编辑完不连续。可以用MAX函数进行编号。


如上图所示，需要进行连续编号。首先选中需要编号的全部单元格，直接输入“=MAX($A$1:A1)+1”，然后按组合键“Ctrl+enter”，完成。这个公式理解后套用比较方便。公式的含义就是在本单元格之上的最大数值+1，得到本单元格的值。套用的时候$A$1替换为需要编号区域上方的单元格，A1替换为编号区域紧挨着的上方单元格。
2.按条件求和筛选或隐藏后的数据（筛选隐藏或右键隐藏）。
按条件求和，经常使用的公式为sumif或者sumproduct，但是sumif或者sumproduct按条件对隐藏过数据的区域求和的时候对数据的规范性要求较高，本次分享的公式为含subtotal的适用性较高的公式。
公式共包含三个函数。SUMPRODUCT、SUBTOTAL、OFFSET。如果有相似问题，直接抄作业就可以。


以上为未筛选表格，假设需要筛选出所有进货数据，筛选后还需要统计出所有五金类别的数量和。直接抄作业“=SUMPRODUCT(($D$2:$D$11=$C$14)*(SUBTOTAL(3,OFFSET(E1,ROW(1:10),))*E2:E11))”




现在说一下这个公式怎么理解，怎么用。
理解思路：
将公式分为两部分。一部分是sumproduct函数。一部分是subtotal（offset）函数
sumproduct函数的功能是带条件求和，就是一列数乘以另一列数的和，然后可以增加条件。公式里第一个乘号之前的就是本次公式中的条件，类别=五金。
Subtotal函数加offset函数就是将筛选后的数据以数列的形式展现出来，但是为（0，1，1，0，…）的数列。筛选出来的为1，隐藏的是0。然后这组数列乘以原本的数列再求和，就是只保留了筛选数据的和。
整个公式可以理解为sumproduct（条件*subtotal（offset）数列*数量数列）。
公式套用：
上面的不理解也没关系，直接套用公式就好。
“=SUMPRODUCT(($D$2:$D$11=$C$14)*(SUBTOTAL(3,OFFSET(E1,ROW(1:10),))*E2:E11))”
①OFFSET(E1,ROW(1:10),)
这一部分中，E1为数量列数值的上一格，也就是数量的标题行，需要修改为自己的统计数量标题行。
ROW(1：10)中需要修改的是10，这个值为有效数据的行数。本次示例表中有10行有效数据，所以为10，按自己的实际有效数据行进行修改。
②SUBTOTAL(3,OFFSET(E1,ROW(1:10),))
这部分其实就是对offset就行了编辑，这个3是subtotal的功能序号，无需改动。
③SUMPRODUCT(($D$2:$D$11=$C$14)*(SUBTOTAL(3,OFFSET(E1,ROW(1:10),))*E2:E11))
第一个乘号*之前的为条件，本次示例中只有一个条件，如果有多个条件，多相乘几个条件即可，但是都需要括号括起来。然后最后一个乘号*之后的数列为需要求和的数量列，按自己的需求修改。
④因为这个公式为数组求和，所以编辑完公式之后，不能直接按enter（回车），需要按组合键（ctrl+shift+enter）。
以上就是套用公式的详细步骤。
SUMPRODUCT函数后续更新。