近期讲LAMBDA和LET的内容，有部分粉丝朋友反馈难度偏大。这是很正常的情况，毕竟LAMBDA和LET函数的设计初衷就是为了解决复杂的问题的，是Excel迈向编程的重大革命性举动。不过话说回来，如果你的基础知识足够扎实，LAMBDA和LET对你而言就是老虎加翅膀。这一期我们分享比较基础的内容，涉及的知识点虽然简单，但都是Excel高手进阶必须100%掌握的内容，建议将加粗的文字部分截图保留起来。如下图，要求对各组成员按顺序编号，并统计每组的业绩之和。在数据表中，根据特定的条件，快速定位到需要的位置，而不是手动去多选或者一个一个去选。间隔填充序号分两种情况，连续编号和不连续编号，本例属于不连续编号，因为各组的起始编号都是1。在文章末尾，我会普及一下连续编号。函数公式返回的结果，通常我们肉眼看到的未必是真正的计算结果。有的函数返回的是值，这就是所见即真。有的返回的就不仅仅是值，还包括单元格的地址属性，可以被当作单元格位置作为物理地址的引用。后者就是函数保留单元格引用的属性，常见的保留单元格引用属性的函数有INDEX、OFFSET、INDIRECT、CHOOSE、XLOOKUP等。假设我们分别用VLOOKUP和XLOOKUP查找某个值，返回结果是在B4单元格。VLOOKUP函数返回的是B4单元格的值，返回结果不包含任何B4单元格的地址属性。XLOOKUP函数返回的不仅仅是B4单元格的值，还包含了B4单元格的物理地址信息。VLOOKUP函数返回的是值，不包含返回值所在单元格的物理地址信息，所以公式“=B1:VLOOKUP(D2,A2:B5,2,0)”提示错误。XLOOKUP函数保留了B4单元格的地址信息，所以公式“=B1:XLOOKUP(D2,A2:A5,B2:B5)”的返回结果就等同于“B1:B4”。换句话说，保留单元格引用属性的函数，它的公式返回值，可以被当作单元格地址直接引用。我们可以使用ISREF函数来检测公式返回值是否保留引用属性，也可以使用CELL函数来获取物理地址。首先选中A2:A27，如果行数过多，鼠标拖动很麻烦，可以将光标定位在表格中没有空白单元格的列的任意位置，按Ctrl+↓快速定位到最后一行。例如本例中，B列没有空白单元格，光标定位在B列任意单元格，Ctrl+↓，获取最后一行的行号27。需要填充编号的是A列，直接在名称框输入“A2:A27”，可以快速选中表格中的A列区域。然后按Ctrl+G，或者F5，弹出的对话框选择“定位条件”。这个时候，表格区域A列的所有空白单元格都被选中了，鼠标不要乱动。将光标定位到公式栏，先输入如下公式，不要急着按Enter：注意，这里一定是按下Ctrl+Enter组合键，这个组合键用于批量填充。N函数的参数是文本值时返回0，数值时返回数值本身。第一个空单元格是A3，A2是文本值，公式“=N(A2)+1”就等于1，即A3的值就是1。由于是相对引用，A3在A2下方一行，对应到An的值就是“N(An-1)+1”。所以就能够实现每组都是从1开始编号，因为每组的上方都是组名，属于文本值。Excel中，很多时候，瞪眼法都是非常有效的解决问题的方法。我们注意观察，小计在各组的上方一行。本组的小计与下一组的小计之间的所有数据，都属于本组的数据。当然，最后一组的下方，是找不到小计的。不过这种唯一的特殊情况，都可以通过特殊方式来处理的。Excel中，找准数据的特定规律，往往是问题的突破口。可以直接查找“小计”二字，查找范围下移一行，以C2单元格为例，在B3:B27范围去找“小计”二字，返回值区域是C3:C27。利用XLOOKUP函数保留单元格引用属性的特征，第一个“小计”就是B8位置，返回值C8向上位移一个单位就到C7，这样就得到了A组的最后一个数值的单元格位置，其他组的也是同样的逻辑。问题来了，最后一组呢？最后一组下方找不到“小计”，那就请出XLOOKUP函数的第四参数if_not_found，找不到就让它返回C28（因为XLOOKUP函数的返回值要向上位移1个单位）。=SUM(C3:OFFSET(XLOOKUP("小计",B3:$B$27,C3:$C$27,$C$28),-1,))
这里需要注意的是，因为我们批量选择空单元格，第一个位置是相对的，例如B3相对于B2就是下方一行，但是后面一个位置是固定的，无论是B列查找区域，还是C列返回值区域，最后一个位置的行号都是27，因为是从上往下动态位移。复制以上公式，选中C列的空单元格，粘贴公式进去，然后Ctrl+Enter即可批量填充。如果你的Excel版本不支持XLOOKUP函数，可以用以下公式：=SUM(C3:INDIRECT("C"&IFNA(MATCH("小计",B3:$B$27,0),27)+ROW(A1)))
如果小计位于数据下方，操作很简单，只需要通过条件定位，选中数值区域中的空单元格，按Alt+=即可。由于我文章写完忘记保存Excel工作簿，所以本例暂时没有练习文件，如果一两日内我有发布相关视频，就会有练习文件。
