2208Excel函數用法-COUNT、COUNTIF、IF、IFERROR、MATCH、MID、OFFSET、RIGHT、VLOOKUP @ 錦子老師

2208

Excel函數用法-COUNT、COUNTIF、IF、IFERROR、MATCH、MID、OFFSET、RIGHT、VLOOKUP、LOOKUP

錦子老師您好：

小弟目前有一計算表，呈現的資訊是每個學號分別對於國英數理四個科目的參加競賽的數目。

假如學號眾多，我要如何做成一個統計表呈現出每個學號對於有參加的科目及數量。

如果用IF函數，變成每個學號無論有無參加都會呈現4個科目及數量（沒參加的就空白或一槓），那有另外的函數或是可以與IF搭配，只呈現出有參與的科目及數量就好嗎？謝謝！

一、建立輔助欄

首先我們要在F欄建立一個輔助欄存放那些科目有數量及總共有多少科目有數量。

1、點取F1儲存格輸入「1」，這是為了解決欄位標題「學號」用，比較其數量。

2、點取F2儲存格輸入公式：

=IF(B2>0,$B$1,"")&IF(C2>0,$C$1,"")&IF(D2>0,$D$1,"")&IF(E2>0,$E$1,"")&COUNT(B2:E2)

3、將F2公式複製到F3:F9儲存格。

【公式說明】

IF(B2>0,$B$1,"")&IF(C2>0,$C$1,"")&IF(D2>0,$D$1,"")&IF(E2>0,$E$1,"") 如果該欄位第二列有數值的話，則填入該欄位第一列科目資料。

COUNT(B2:E2) 統計第二列有數值的儲存格數。

二、統計學號數量及填入該學號

再來我們就要依照有數值的科目數量填入其學號筆數。

首先統計前面學號筆數是否小於表格中該學號有數值的科目數量，公式如下：

COUNTIF($H$1:H1,H1)<VALUE(RIGHT(VLOOKUP(H1,$A:$F,6,0),1))

COUNTIF($H$1:H1,H1) 統計到目前儲存格中，H1儲存格內容數量。

COUNTIF(資料範圍，條件) 統計資料範圍符合條件的數量。

VALUE(RIGHT(VLOOKUP(H1,$A:$F,6,0),1) 統計在A:F欄中該學號的第6欄(輔助欄)內容右邊第一個字，並將其由文字轉換為數值。

VLOOKUP(搜尋值，搜尋範圍，擷取第幾欄值，比對方式) 傳回搜尋值在搜尋範圍第一欄比對後，若有相同內容則傳回搜尋範圍第幾欄資料，其比對方式為近似值比對(1)還是絕對比對(空白或0)

VALUE(文字資料) 將文字資料轉換為數值資料。

接下來要判斷如果條件為真，則抓取前一列內容，否則要移到表格中該學號的下一列(下一個學號)，同時則要判斷是不是空白列，若是則不填入資料，否則填入下一個學號，公式如下：

IF(COUNTIF($H$1:H1,H1)<VALUE(RIGHT(VLOOKUP(H1,$A:$F,6,0),1)),H1,IF(OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1)=0,"",OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1)))

MATCH(H1,$A:$A,0) 傳回上一儲存格(H1)位於A欄的位置(列號)。

MATCH(搜尋值，搜尋範圍，比對方式) 搜尋值在搜尋範圍尋找傳回其位於第幾列，比對方式為1是小於或等於最大值(搜尋範圍必須遞增排列)，比對方式為0是完全等於搜尋值(搜尋範圍不須排列)，比對方式為-1是大於或等於最小值(搜尋範圍必須遞減排列)。

OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1) 從A1儲存格移動MATCH(H1,$A:$A,0)計算的列數，0欄後傳回該儲存格內容。

OFFSET(起點位置，移動列數，移動欄數，抓取列數，抓取欄數)

IF(OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1)=0,"",OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1)) 如果從A1儲存格移動MATCH(H1,$A:$A,0)計算的列數，0欄後該儲存格內容，若該內容為0表示空白儲存格，則不填入資料，否則填入從A1儲存格移動MATCH(H1,$A:$A,0)計算的列數，0欄後該儲存格內容。

最後若擷取不到有資料的學號儲存格，則會出現錯誤值#N/A，故必須其變為空白。所以H2公式如下：

IFERROR(IF(COUNTIF($H$1:H1,H1)<VALUE(RIGHT(VLOOKUP(H1,$A:$F,6,0),1)),H1,IF(OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1)=0,"",OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1))),"") 如果出現錯誤值則不填入資料。

所以H2公式如下：

IFERROR(IF(COUNTIF($H$1:H1,H1)<VALUE(RIGHT(VLOOKUP(H1,$A:$F,6,0),1)),H1,IF(OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1)=0,"",OFFSET($A$1,MATCH(H1,$A:$A,0),0,1,1))),"")

將H2公式複製到H3:H20儲存格。

三、填入各學號對應的科目

當H欄學號都填好後，則在I欄填入其相對應的科目，I2儲存格公式如下：

=MID(VLOOKUP(H2,$A:$F,6,0),LOOKUP(COUNTIF($H$2:H2,H2),{1,2,3,4},{1,3,5,7}),2)

將I2儲存格公式複製到I3:I20儲存格。

VLOOKUP(H2,$A:$F,6,0) 傳回H2儲存格內容在A:F欄表格中的A欄搜尋後，若有則傳回其同列F欄內容(輔助欄)。

COUNTIF($H$2:H2,H2) 統計到目前為止的H欄範圍中該學號筆數。

LOOKUP(COUNTIF($H$2:H2,H2),{1,2,3,4},{1,3,5,7}) 如果COUNTIF($H$2:H2,H2)傳回的數值是1到4之間，則相對應1,3,5,7這4個數值。

MID(VLOOKUP(H2,$A:$F,6,0),LOOKUP(COUNTIF($H$2:H2,H2),{1,2,3,4},{1,3,5,7}),2) 傳回F欄對應儲存格中LOOKUP(COUNTIF($H$2:H2,H2),{1,2,3,4},{1,3,5,7})傳回第幾個字開始的2個字。