王志軍

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

第1步：選取分表

單擊位于最左側(cè)的“行政樓”工作表標(biāo)簽，按住Shift鍵，單擊最右側(cè)的“實(shí)訓(xùn)樓”工作表，這些分表將成為一個(gè)工作組，每個(gè)分表均處于選中狀態(tài)。請(qǐng)?jiān)诟鱾€(gè)分表中復(fù)制相應(yīng)的列標(biāo)題，效果如圖2所示。

第2步：拆分?jǐn)?shù)據(jù)

在成組工作表的B2單元格，輸入公式“=INDEX（學(xué)校IP地址分配表！C：C，SMALL（IF（學(xué)校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”組合鍵轉(zhuǎn)換為數(shù)組公式，向下、向右拖曳填充柄，建議適當(dāng)多拖曳幾行，最終效果如圖3所示。

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

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

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

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

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