论文部分内容阅读
同事前来求助，如图1所示，现在需要根据楼宇的建筑物名称，将相关人员的信息批量填充到各个分表，例如行政楼的数据填写到“行政楼”工作表，图文信息楼的数据填写到“图文信息楼”工作表。当“学校IP地址分配表”这个总表的信息发生改变，或者有新增数据时，各个分表的数据也会发生相应的改变，该如何操作呢？
第1步：选取分表
单击位于最左侧的“行政楼”工作表标签，按住Shift键，单击最右侧的“实训楼”工作表，这些分表将成为一个工作组，每个分表均处于选中状态。请在各个分表中复制相应的列标题，效果如图2所示。
第2步：拆分数据
在成组工作表的B2单元格，输入公式“=INDEX（学校IP地址分配表！C：C，SMALL（IF（学校IP地址分配表！$B$2：$B$309=MID（CELL（"filename"，A1），FIND（"]"，CELL（"filename"，A1））+1，99），ROW（$2：$309），4^8），ROW（A1）））&""”，按下“Ctrl+Shift+Enter”组合键转换为数组公式，向下、向右拖曳填充柄，建议适当多拖曳几行，最终效果如图3所示。
MID（CELL（"filename"，A1），FIND（"]"，CELL（"filename"，A1））+1，99），这部分是用于获取A1单元格所在工作表的表名。需要说明的是，此处CELL（"filename"，A1）第二参数A1是不能省略的，倘若省略，获取的将是最后更改单元格所在工作表的表名，会导致公式得出错误结果。FIND函数查询字符"]"在字符串中的位置并加1，MID函数在此结果上开始取数，取99个数，99是一个很大的数，此处也可以是66、88等，只要超过了预想字符串的长度即可。
IF函数判断总表B2：B309单元格区域的值是否和相应分表的表名相等，如果相等，则返回B列值对应的行号，否则返回4^8，结果得到一个内存数组;SMALL函数对IF函数的结果进行从小到大取数，随着公式的向下填充，依次提取第1、2、3、4......N个最小值，依次得到符合条件的IP地址等信息和公式所在工作表的名称一致的单元格的行号;INDEX函数根据SMALL函数返回的索引值，得出结果，当SMALL函数所得到的结果为4^8，即65536时，意味着符合条件的行号已经被取完，此时INDEX函数将返回C65536单元格的值，通常来说，这么大行号的单元格是空白单元格，使用&“”的方式，规避空白单元格返回零值的问题，使之返回假空。
第3步：取消组合工作表状态
公式填寫完成后，单击不属于成组工作表的“学校IP地址分配表”标签，Excel会自动取消组合工作表状态。至此根据工作表名称批量拆分总表数据的操作即告完成，当总表的数据发生改变时，分表的数据也会随之改变。
当然，我们利用数据透视表的“显示报表筛选页”功能，或者借助VBA代码，也可以实现数据拆分的要求，感兴趣的朋友可以一试。
第1步：选取分表
单击位于最左侧的“行政楼”工作表标签，按住Shift键，单击最右侧的“实训楼”工作表，这些分表将成为一个工作组，每个分表均处于选中状态。请在各个分表中复制相应的列标题，效果如图2所示。
第2步：拆分数据
在成组工作表的B2单元格，输入公式“=INDEX（学校IP地址分配表！C：C，SMALL（IF（学校IP地址分配表！$B$2：$B$309=MID（CELL（"filename"，A1），FIND（"]"，CELL（"filename"，A1））+1，99），ROW（$2：$309），4^8），ROW（A1）））&""”，按下“Ctrl+Shift+Enter”组合键转换为数组公式，向下、向右拖曳填充柄，建议适当多拖曳几行，最终效果如图3所示。
MID（CELL（"filename"，A1），FIND（"]"，CELL（"filename"，A1））+1，99），这部分是用于获取A1单元格所在工作表的表名。需要说明的是，此处CELL（"filename"，A1）第二参数A1是不能省略的，倘若省略，获取的将是最后更改单元格所在工作表的表名，会导致公式得出错误结果。FIND函数查询字符"]"在字符串中的位置并加1，MID函数在此结果上开始取数，取99个数，99是一个很大的数，此处也可以是66、88等，只要超过了预想字符串的长度即可。
IF函数判断总表B2：B309单元格区域的值是否和相应分表的表名相等，如果相等，则返回B列值对应的行号，否则返回4^8，结果得到一个内存数组;SMALL函数对IF函数的结果进行从小到大取数，随着公式的向下填充，依次提取第1、2、3、4......N个最小值，依次得到符合条件的IP地址等信息和公式所在工作表的名称一致的单元格的行号;INDEX函数根据SMALL函数返回的索引值，得出结果，当SMALL函数所得到的结果为4^8，即65536时，意味着符合条件的行号已经被取完，此时INDEX函数将返回C65536单元格的值，通常来说，这么大行号的单元格是空白单元格，使用&“”的方式，规避空白单元格返回零值的问题，使之返回假空。
第3步：取消组合工作表状态
公式填寫完成后，单击不属于成组工作表的“学校IP地址分配表”标签，Excel会自动取消组合工作表状态。至此根据工作表名称批量拆分总表数据的操作即告完成，当总表的数据发生改变时，分表的数据也会随之改变。
当然，我们利用数据透视表的“显示报表筛选页”功能，或者借助VBA代码，也可以实现数据拆分的要求，感兴趣的朋友可以一试。