要求:如下图所示,我们需要在总表中,将产品分类相同的产品名称拆分到以产品分类为名称的工作表中。
具体操作步骤如下 :
1、首先,点击鼠标左键先选中“零食”工作表,按住键盘上的“Shift”键,点击鼠标左键再选中“美妆”工作表,此时,除了总表外的所有工作表会成为一个组合工作表,组合工作表中的每个表都处于被选中的状态。
2、选中组合工作表中任意一个表的A2单元格,在编辑栏中输入公式:=INDEX(总表!B:B,SMALL(IF(总表!$C$2:$C$100=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) 1,99),ROW($2:$100),4^8),ROW(A1)))&"",按组合键“Ctrl Shift Enter”,然后将公式向右填充至B2单元格,向下填充至B10单元格即可。
3、公式解析:
①=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) 1,99):
公式CELL("filename",A1)的意思是:获取A1单元格所在工作表的路径;
公式FIND("]",CELL("filename",A1)) 1的意思是:使用find函数查找 ] 在路径中的起始位置,后面的 1就是从 ] 后面的第一个字符开始。
整个公式的意思是:使用MID函数从工作表路径中,从 ] 后面的第一个字符开始,截取99个字符,最后返回的结果是当前工作表的名称。
②=IF(总表!$C$2:$C$100=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) 1,99),ROW($2:$100),4^8),ROW(A1):
使用IF函数判断C2:C10单元格区域中的产品分类是否与当前工作表的名称相同,如果相同,返回当前的行号,如果不相同,返回一个很大的数字。
③=SMALL(IF(总表!$C$2:$C$100=MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) 1,99),ROW($2:$100),4^8),ROW(A1)):
SMALL函数的意思是返回数组中第K个最小值。例如公式:=SMALL({2;7;4;6;9},3),意思是返回数组{2;7;4;6;9}中第3个最小的值,结果为:6。
最后用INDEX函数在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。
4、动图演示如下。
此时,如果总表的数据修改,分表的数据也会自动更新!
本期教程跟大家分享到这里,想学更多的办公技巧,欢迎关注我哦!
如果文章对您有帮助,可以转发、点赞支持小编,创作不易,希望大家多多支持!