2013年3月19日 星期二

Excel-以文字查表計算總和(VLOOKUP,HLOOKUP,陣列公式)

有網友問到先前的文章中,有一篇提到在 Excel 的工作表中,每種色彩對應一個數(參考下圖),如果想要以色彩查表取得數值再計算總和。如果要將「欄」的內容轉換為「列」來呈現該如何修改公式?

(一)資料以「欄」排列

(1) 轉換數字後計算總和

儲存格B2:=VLOOKUP(A2,$F$1:$G$10,2,FALSE)

利用 VLOOKUP 函數,取得每個色彩所代表的數值,再加總即為答案。

複製儲存格B2,貼至儲存格B2:B15。

(2) 直接計算總和

如果不想先將色彩轉換為數字,而要直接求得總和。

儲存格D2:{=SUM(IF(A2:A15={"紅","橙","黃","綠","藍","靛","紫","黑","白"},{1,2,3,4,5,6,7,8,9}))}

此為陣列公式,輸入完成要按 Ctrl+Shfit+Enter 鍵。

 

(二)資料以「列」排列

(3) 轉換數字後計算總和

儲存格B2:=HLOOKUP(B1,$B$6:$J$7,2,FALSE)

利用 HLOOKUP 函數,取得每個色彩所代表的數值,

複製儲存格B2,貼至儲存格B2:O2。

比較 (1) 和 (3) 的公式,以欄排列使用 VOOKUP 函數,以列排列使用 HLOOKUP 函數。

(4) 直接計算總和

利用 HLOOKUP 函數,取得每個色彩所代表的數值,再加總即為答案。

儲存格B4:{=SUM(IF(B1:O1={"紅";"橙";"黃";"綠";"藍";"靛";"紫";"黑";"白"},{1;2;3;4;5;6;7;8;9}))}

此為陣列公式,輸入完成要按 Ctrl+Shfit+Enter 鍵。

比較 (2) 和 (4) 的公式,在顏色陣列以欄排列使用「,」,以列排列使用「;」。

沒有留言:

張貼留言

檢視其他文章

好康東東