利用函數(shù)公式快速拆分總表

王志軍

同事前來求助，如圖1所示，現(xiàn)在需要根據(jù)樓宇的建筑物名稱，將相關人員的信息批量填充到各個分表，例如行政樓的數(shù)據(jù)填寫到“行政樓”工作表，圖文信息樓的數(shù)據(jù)填寫到“圖文信息樓”工作表。當“學校IP地址分配表”這個總表的信息發(fā)生改變，或者有新增數(shù)據(jù)時，各個分表的數(shù)據(jù)也會發(fā)生相應的改變，該如何操作呢？

第1步：選取分表

單擊位于最左側的“行政樓”工作表標簽，按住Shift鍵，單擊最右側的“實訓樓”工作表，這些分表將成為一個工作組，每個分表均處于選中狀態(tài)。請在各個分表中復制相應的列標題，效果如圖2所示。

第2步：拆分數(shù)據(jù)

在成組工作表的B2單元格，輸入公式“=INDEX（學校IP地址分配表！C：C，SMALL（IF（學校IP地址分配表！$B$2：$B$309=MID（CELL（"filename"，A1），F(xiàn)IND（"]"，CELL（"filename"，A1））+1，99），ROW（$2：$309），4^8），ROW（A1）））&""”，按下“Ctrl+Shift+Enter”組合鍵轉換為數(shù)組公式，向下、向右拖曳填充柄，建議適當多拖曳幾行，最終效果如圖3所示。

MID（CELL（"filename"，A1），F(xiàn)IND（"]"，CELL（"filename"，A1））+1，99），這部分是用于獲取A1單元格所在工作表的表名。需要說明的是，此處CELL（"filename"，A1）第二參數(shù)A1是不能省略的，倘若省略，獲取的將是最后更改單元格所在工作表的表名，會導致公式得出錯誤結果。FIND函數(shù)查詢字符"]"在字符串中的位置并加1，MID函數(shù)在此結果上開始取數(shù)，取99個數(shù)，99是一個很大的數(shù)，此處也可以是66、88等，只要超過了預想字符串的長度即可。

IF函數(shù)判斷總表B2：B309單元格區(qū)域的值是否和相應分表的表名相等，如果相等，則返回B列值對應的行號，否則返回4^8，結果得到一個內存數(shù)組;SMALL函數(shù)對IF函數(shù)的結果進行從小到大取數(shù)，隨著公式的向下填充，依次提取第1、2、3、4......N個最小值，依次得到符合條件的IP地址等信息和公式所在工作表的名稱一致的單元格的行號;INDEX函數(shù)根據(jù)SMALL函數(shù)返回的索引值，得出結果，當SMALL函數(shù)所得到的結果為4^8，即65536時，意味著符合條件的行號已經(jīng)被取完，此時INDEX函數(shù)將返回C65536單元格的值，通常來說，這么大行號的單元格是空白單元格，使用&“”的方式，規(guī)避空白單元格返回零值的問題，使之返回假空。

第3步：取消組合工作表狀態(tài)

公式填寫完成后，單擊不屬于成組工作表的“學校IP地址分配表”標簽，Excel會自動取消組合工作表狀態(tài)。至此根據(jù)工作表名稱批量拆分總表數(shù)據(jù)的操作即告完成，當總表的數(shù)據(jù)發(fā)生改變時，分表的數(shù)據(jù)也會隨之改變。

當然，我們利用數(shù)據(jù)透視表的“顯示報表篩選頁”功能，或者借助VBA代碼，也可以實現(xiàn)數(shù)據(jù)拆分的要求，感興趣的朋友可以一試。