今天跟大家分享的是Excel新函數(shù)TOCOL，它不僅可以將二維數(shù)組轉(zhuǎn)化成一列數(shù)據(jù)，還有很多厲害的高級(jí)用法。今天就跟大家分享TOCOL函數(shù)的2種高級(jí)用法，建議收藏備用！

‌tocol函數(shù)哪個(gè)版本能用

• TOCOL函數(shù)可在Excel 365和最新版本的WPS表格中使用‌。

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡(jiǎn)體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大小：2.2GB
• 語言：簡(jiǎn)體中文
• 時(shí)間：2024-09-12

查看詳情

TOCOL函數(shù)介紹

功能：將二維數(shù)組轉(zhuǎn)化成一列數(shù)據(jù)

語法：=TOCOL(數(shù)組,[忽略特殊值],[通過列掃描])

• 第1參數(shù)：數(shù)組就是要轉(zhuǎn)化成一列顯示的數(shù)據(jù)
• 第2參數(shù)：忽略特殊值
• 如果輸入0：不忽略特殊值
• 輸入1：忽略空白單元格
• 輸入2：忽略錯(cuò)誤值
• 輸入3：忽略空白單元格和錯(cuò)誤值
• 第3參數(shù)：通過列掃描，F(xiàn)ALSE，按行，TRUE按列，如果省略默認(rèn)按行

基本用法：（多行多列數(shù)據(jù)轉(zhuǎn)換成一列）

如下圖所示，我們需要把左側(cè)多行多列數(shù)據(jù)轉(zhuǎn)換成一列

在目標(biāo)單元格中輸入公式

=TOCOL(A2:F4,3)

然后點(diǎn)擊回車即可

解讀：

①公式就是把A2:F4數(shù)據(jù)區(qū)域按行轉(zhuǎn)換成一列數(shù)據(jù)，第二參數(shù)是3代表忽略空白單元格和錯(cuò)誤值。

②如果是想按列的轉(zhuǎn)換的話，第三參數(shù)設(shè)置成TRUE即可，公式如下：

=TOCOL(A2:F4,3,TRUE)

高級(jí)進(jìn)階用法一：一對(duì)多匹配查詢

如下圖所示，我們想根據(jù)所屬部門，查找對(duì)應(yīng)的員工姓名，然后姓名按行排列。

在目標(biāo)單元格中輸入公式：

=TOCOL(FILTER($B:$B,$C:$C=E1,""),3)

然后點(diǎn)擊回車，向右填充數(shù)據(jù)即可

解讀：

公式中先使用FILTER函數(shù)根據(jù)條件查詢出數(shù)據(jù)（因?yàn)樾枰蛴刑畛?，所?B:$B,$C:$C要絕對(duì)引用），然后再使用TOCOL函數(shù)把查詢結(jié)果轉(zhuǎn)換成一列顯示，TOCOL函數(shù)第2參數(shù)是3，代表忽略空白單元格和錯(cuò)誤值。

高級(jí)進(jìn)階用法二：數(shù)據(jù)逆透視

如下圖所示，下面1-3個(gè)表格都是員工1-3月份銷售業(yè)績(jī)明細(xì)，只是表格數(shù)據(jù)展現(xiàn)形式不同。如果想把表格2轉(zhuǎn)換成表格1樣式，我們只需要使用透視表即可，也就是表格3樣式；如果想把表格1轉(zhuǎn)換成表格2樣式就是數(shù)據(jù)逆透視的效果。

下面直接上干貨，TOCOL+IF函數(shù)組合輕松實(shí)現(xiàn)數(shù)據(jù)逆透視效果。

第一步：對(duì)左側(cè)表格姓名逆透視

在目標(biāo)單元格中輸入公式：

=TOCOL(IF(B2:D10<>"",A2:A10,NA()),3)

然后點(diǎn)擊回車即可。

解讀：

公式中首先使用IF判斷函數(shù)，B2:D10每個(gè)月份銷售額是否為空，如果不為空返回右側(cè)A2:A10區(qū)域的姓名，否則返回錯(cuò)誤值（NA函數(shù)會(huì)把不符號(hào)條件的數(shù)據(jù)全部轉(zhuǎn)換成錯(cuò)誤值）；然后再使用TOCOL函數(shù)把查詢結(jié)果轉(zhuǎn)換成一行顯示，TOCOL函數(shù)第2參數(shù)是3，代表忽略空白單元格和錯(cuò)誤值。

第二步：對(duì)左側(cè)表格月份逆透視

在目標(biāo)單元格中輸入公式：

=TOCOL(IF(B2:D10<>"",B1:D1,NA()),3)

然后點(diǎn)擊回車即可。

解讀：

同樣的道理，首先使用IF判斷函數(shù)，B2:D10每個(gè)月份銷售額是否為空，如果不為空返回上側(cè)B1:D1區(qū)域的月份，否則返回錯(cuò)誤值（NA函數(shù)會(huì)把不符號(hào)條件的數(shù)據(jù)全部轉(zhuǎn)換成錯(cuò)誤值）；然后再使用TOCOL函數(shù)把查詢結(jié)果轉(zhuǎn)換成一行顯示，TOCOL函數(shù)第2參數(shù)是3，代表忽略空白單元格和錯(cuò)誤值。

第三步：對(duì)左側(cè)表格每個(gè)月銷售業(yè)績(jī)逆透視

在目標(biāo)單元格中輸入公式：

=TOCOL(IF(B2:D10<>"",B2:D10,NA()),3)

然后點(diǎn)擊回車即可。

解讀：

跟上面第一步、第二步原理一樣，只是如果B2:D10每個(gè)月份銷售額不為空，就返回B2:D10數(shù)據(jù)區(qū)域?qū)?yīng)的數(shù)值。

最新評(píng)論