Excel公式練習(xí)1：將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)

江上向東數(shù)峰青 2017-06-18

展開全文

作者按：練習(xí)才能熟能生巧，練習(xí)才能靈活運用。要掌握一項技能，唯有反復(fù)練習(xí)。對于Excel公式與函數(shù)來說，也是如此。在Excel公式練習(xí)系列中，我會將自已碰到的（無論是在網(wǎng)上看到的、還是在書中找到的、還是應(yīng)用時或解答問題中使用的）一些公式在這里解析，也算是和大家一起練習(xí)，以精進(jìn)Excel技能。

本次的練習(xí)是：使用公式將工作表內(nèi)一列中的重復(fù)項依次移到一行中。具體如下圖所示，要將工作表：

轉(zhuǎn)換成工作表：

可以看出，在“數(shù)據(jù)”工作表中，列A中有些姓名出現(xiàn)1次，有些出現(xiàn)2次，有些出現(xiàn)3次，但在列B中有不同的數(shù)據(jù)。

現(xiàn)在，需要將“數(shù)據(jù)”工作表中的數(shù)據(jù)轉(zhuǎn)換成基于姓名的一行數(shù)據(jù)，即將與每個姓名相關(guān)的列B中的數(shù)據(jù)放置到單獨的一行中。

使用數(shù)組公式來解決。在單元格B2中輸入公式：

=IFERROR(INDEX(數(shù)據(jù)!$B$2:$B$7,SMALL(IF(數(shù)據(jù)!$A$2:$A$7=$A2,ROW(數(shù)據(jù)!$A$2:$A$7)-ROW(數(shù)據(jù)!$A$2)+1),COLUMNS($B2:B2))),'')

按Ctrl+Shift+Enter組合鍵完成輸入。結(jié)果如下圖所示：

將單元格B2向右向下拖動，將公式復(fù)制到B2:E4區(qū)域后獲得所需結(jié)果，如下圖所示：

公式思路

將列A中的值與“數(shù)據(jù)”工作表列A中的值相比較，如果相等則獲取“數(shù)據(jù)”工作表中該值所在行的行號，然后根據(jù)獲取的行號在“數(shù)據(jù)”工作表列B中找到相應(yīng)的值并放置在本單元格中，如果沒找到則為空。

公式解析

數(shù)據(jù)!$A$2:$A$7=$A2將單元格A2的值與“數(shù)據(jù)”工作表列A的值比較，因為前3行都相等，因此結(jié)果為：{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}。

ROW(數(shù)據(jù)!$A$2:$A$7)-ROW(數(shù)據(jù)!$A$2)+1獲取一個值為1至6的數(shù)組：{1;2;3;4;5;6}。

IF(數(shù)據(jù)!$A$2:$A$7=$A2,ROW(數(shù)據(jù)!$A$2:$A$7)-ROW(數(shù)據(jù)!$A$2)+1)的結(jié)果為數(shù)組{1;2;3;FALSE;FALSE;FALSE}。

COLUMNS($B2:B2)返回當(dāng)前單元格與B2單元格相間隔的列數(shù)，因為當(dāng)前單元格就在B2，因此返回1。該數(shù)值用來作為SMALL函數(shù)的參數(shù)，獲取第k個最小值，即需要第幾人數(shù)據(jù)，從而獲取列A值在“數(shù)據(jù)”工作表中對應(yīng)的行號。本例中為SMALL({1;2;3;FALSE;FALSE;FALSE},1)。返回第1個最小值，即1。