Excel公式練習1：將列數據轉換為行數據

作者按：練習才能熟能生巧，練習才能靈活運用。要掌握一項技能，唯有反覆練習。對於Excel公式與函數來說，也是如此。在Excel公式練習系列中，我會將自已碰到的（無論是在網上看到的、還是在書中找到的、還是應用時或解答問題中使用的）一些公式在這裡解析，也算是和大家一起練習，以精進Excel技能。

本次的練習是：使用公式將工作表內一列中的重複項依次移到一行中。具體如下圖所示，要將工作表：

轉換成工作表：

可以看出，在「數據」工作表中，列A中有些姓名出現1次，有些出現2次，有些出現3次，但在列B中有不同的數據。

現在，需要將「數據」工作表中的數據轉換成基於姓名的一行數據，即將與每個姓名相關的列B中的數據放置到單獨的一行中。

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

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

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

將單元格B2向右向下拖動，將公式複製到B2:E4區域後獲得所需結果，如下圖所示：

公式思路

將列A中的值與「數據」工作表列A中的值相比較，如果相等則獲取「數據」工作表中該值所在行的行號，然後根據獲取的行號在「數據」工作表列B中找到相應的值並放置在本單元格中，如果沒找到則為空。

公式解析

數據!$A$2:$A$7=$A2將單元格A2的值與「數據」工作表列A的值比較，因為前3行都相等，因此結果為：{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}。

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

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

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

然後，該值用作INDEX函數的參數：INDEX(數據!$B$2:$B$7,1)，獲取「數據」工作表列B中相應行的數據，即「數據」工作表單元格B2中的數據。

IFERROR函數用來在沒有找到數據時給單元格填充空值，而不是顯示錯誤值。

小結

個人覺得這個例子對於理解數組公式特別有用，值得反覆練習與琢磨。

舉一反三，本示例也可以用作查找重複值的參考方法。

如果您對本文介紹的內容有什麼建議或好的示例，歡迎發送郵件給我：xhdsxfjy@163.com

也可以在本文下面發表留言，留下您的足跡。

本文屬原創文章，轉載請聯繫我或者註明出處。

關注《完美Excel》微信公眾賬號：