Excel將數(shù)據(jù)提取到指定工作表，一個公式就夠了

五毒缺嫖賭 2019-05-28

展開全文

在日常應(yīng)用中，從總表中拆分?jǐn)?shù)據(jù)還是經(jīng)常會用到的。比如說，將銷售數(shù)據(jù)提取到各個銷售部工作表、將學(xué)生名單提取到各個班級工作表……

今天分享的內(nèi)容，就是和拆分有關(guān)的技巧。

一、.動態(tài)獲取工作表名稱

打開一個Excel表，在某個單元格里輸入公式：

=CELL（“filename“,A1）

會返回一串字符串，比如D:\學(xué)習(xí)\[me.xlsx]總表。

其中，“學(xué)習(xí)”是文件夾的名稱；“[me.xlsx]是工作簿的名稱和類型；總表是A1單元格所在工作表的名稱。

如果我們想單獨得到工作表的名稱，比如這里的“總表”，我們可以使用文本函數(shù)來處理單元函數(shù)的結(jié)果。

=MID（CELL（“filename“,A1）,FIND（“]“,CELL（“filename“,A1））+1,99）

FIND函數(shù)查詢字符“]“在字符串中的位置并加1，（為啥加1？猜猜發(fā)生了什么），MID函數(shù)開始在這個結(jié)果上取數(shù)字，99個數(shù)字，99是一個大數(shù)，這里也可以是66，88等等，只要把預(yù)期字符串的長度，改為250或25也是可能的。

二、批量拆分?jǐn)?shù)據(jù)

有這樣一種表格，就是公司人事信息表，按性別、相關(guān)人事信息填寫的子表格，如女生填寫的女生表格、男生填寫的男生表格等。

當(dāng)主表中的信息更改或添加新數(shù)據(jù)時，子表中的數(shù)據(jù)將相應(yīng)更改。

接下來，讓我們看看具體的步驟：

1、選擇要拆分?jǐn)?shù)據(jù)的工作表

2、單擊位于左側(cè)的蘋果工作表標(biāo)簽，按住Shift鍵，再單擊最右側(cè)的【人妖】工作表，此時除【總表】外的分表會成為一個【工作組】，每個分表均處于選中狀態(tài)。

3、輸入公式，拆分?jǐn)?shù)據(jù)

在成組工作表中的A2單元格，輸入下方的數(shù)組公式，按組合鍵 Ctrl Shift Enter ，向下向右復(fù)制填充到A2:B50區(qū)域。

=INDEX（總表!A:A,SMALL（IF（總表!$C$2:$C$13=MID（CELL（“filename“,A1）,FIND（“]“,CELL（“filename“,A1））+1,99）,ROW（$2:$13）,4^8）,ROW（A1）））＆““

或者簡單地說出這個公式的含義：

MID（CELL（“filename“,A1）,FIND（“]“,CELL（“filename“,A1））+1,99）

用于獲取A1單元格所在工作表的表名。

應(yīng)注意，不能省略CELL的第二參數(shù)A1（“文件名”，A1）。如果省略，則獲取上次更改單元格的工作表的表名將導(dǎo)致不正確的結(jié)果。

如果C13單元格區(qū)域的值等于對應(yīng)工作表的表名，如果C13單元格區(qū)域的值等于C列值，則返回與C列值對應(yīng)的行號，否則返回到4-8，結(jié)果是獲得內(nèi)存數(shù)組。

SMALL函數(shù)對IF函數(shù)的結(jié)果進(jìn)行從小到大取數(shù)，隨著公式的向下填充，依次提取第1、2、3、4＆hellip;＆hellip;N個最小值。這又給出了符合標(biāo)準(zhǔn)的單元格的行號性別和公式所在的工作表的名稱。

INDEX函數(shù)根據(jù)SMALL函數(shù)返回的索引值，得出結(jié)果。當(dāng)小函數(shù)得到4^8，即65536時，表示排位號已被拿走。此時，INDEX函數(shù)將返回B65536單元格的值。一般而言，具有如此大的行號的單元是空白單元。使用＆“”方法可以避免空單元格的問題。

4、取消合并工作表狀態(tài)

完成公式后，單擊不屬于組表的摘要表選項卡，excel自動取消組合表狀態(tài)。至此，完成根據(jù)工作表名稱的匯總數(shù)據(jù)批量拆分的操作，當(dāng)摘要表的數(shù)據(jù)發(fā)生變化時，演示如下：