學不完．教不停．用不盡

有網友問到：在 Excel 中的一個資料表(參考下圖右)，要從其中摘要出對應的內容(參考下圖左)，該如何處理？

本例題的意思是，例如：在項目「甲」中，在儲存格C3輸入「價格」後(本例為229)，自動會在同一列的儲存格D3中顯示對應的數量(本例為38)，然後在儲存格B3中顯示價格所對應的類別(本例為CC)。

【輸入公式】

(1)儲存格D3：=OFFSET(F3,0,MATCH(C3,F3:M3,0))

有一位網友問到：在 Excel 中，如果要產生一些亂數值，其整數部分為三個位數，小數部分為四個位數，該如何處理？(參考下圖)

可用的方法很多，以下使用 INT 函數和 RAND 函數來完成。

儲存格A2：=(INT(RAND()*9000000)+1000000)/10000

RAND()：產生小於 1 且大於等於 0 的亂數。

RAND()*9000000：產生小於 9000000 且大於等於 0 的亂數。

網友問到：在下列的 Excel 資料清單中，如果要根據項目的內容，標示出每個項目的最大值／最小值，該如何處理？

【準備工作】

選取儲存格A1:B25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：項目、內容。

【輸入公式】

接續上一篇：計算有標示日期者的平均金額(陣列公式,SUMPRODUCT)，網友想要在一個日期清單中，給予一個區間，篩選某個項目的平均值，該如何理？

【準備工作】

選取儲存格A1:C25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、項目、數量。

【輸入公式】

這是個符合多條件計算小計的做法，本例為三個條件：(1)大於或等於「2014/6/5，(2)小於或等於「2014/6/21」，(3)符合項目「A」。

有網友問到：參考下圖，如果只想要將有標示日期者所對應的金額予以平均，該如何處理？

(1) 使用陣列公式

儲存格D2：{=AVERAGE(IF(A2:A20<>"",B2:B20,FALSE))}

這是陣列公式，輸入公式後，要按 Ctrl+Shift+Enter 鍵。

判斷在儲存格A2:A20陣列中，不是空白儲存格者所對應的B2:B20陣列，再透過 AVERAGE 函數，計算這些陣列值的平均。其中的參數「FALSE」，不可以用空白或是 0 取代。

網友根據另一篇：Excel-條件式加總練習(SUMIF+COUNTIF)，想要詢問不同人員之進料／出料次數。以下補充該部分的公式。

請先閱讀原來文章：http://isvincent.pixnet.net/blog/post/35181133

儲存格H11：=SUMPRODUCT((進出=$G11)*(經手人=H$10))

複製儲存格H11，貼至儲存格H11:J12。

(進出=$G11)：判斷「進出」的陣列中是否符合儲存格G11的內容，傳回 TRUE／FALSE 陣列。

(經手人=H$10)：判斷「經手人」的陣列中是否符合儲存格H10的內容，傳回 TRUE／FALSE 陣列。

參考下圖，有網友問到：如果在一個儲存格範圍中出現某一數字時，即將不同列中的儲存格範圍予以加總。

本例以在儲存格A2:E2中出現「2」為例，分別計算不同色彩的儲存格範圍中的數字總和。

(1) 儲存格B7：=(COUNTIF(A2:E2,2)>0)*SUM(A2:E2)

COUNTIF(A2:E2,2)：判斷是否在儲存格A2:E2中出現「2」。

COUNTIF(A2:E2,2)>0)：只要有一個「2」，則傳回 TRUE，否則傳回 FALSE。

有網友問到：在下圖的資料表中，如何根據「級距」和「天數」，查出對應的「勞工」和「單位」？

本例要使用的查詢函數有二個：OFFSET 和 MATCH。

要注意這個表格是每二欄為一種級距，這也是一個水平／垂直方向交叉位置的查詢，參考以下的公式：

儲存格B16：=OFFSET(B2,B15,MATCH(B14,B1:I1,0)-1,,)

MATCH(B14,B1:I1,0)：使用 MATCH 函數，將儲存格B14的內容和儲存格B1:I1中的內容比對，傳回位於第幾欄的數值。

有網友問到：在 Excel 中的一個資料清單，如何計算符合垂直和水平標題者的小計？

參考下圖，月份和人員(Ａ、Ｂ、Ｃ)沒有固定順序且可能重覆。

為了解說方便，首先要定義名稱，先選取［公式／已定義之名稱／名稱管理員］，定義以下名稱：

儲存格B1:G1：月份；儲存格A2:A5：人員；儲存格B2:G5：資料。

網友問到：在一個 Excel 的資料清單中，如果要根據某個欄位的資料來計算小計，該如何處理？這類問題很適合使用 SUMPRODUCT 函數來運算！

儲存格I2：=SUMPRODUCT(($C$2:$C$16=H2)*$D$2:$D$16)

複製儲存格I2，貼至儲存格I2:I4。

在 SUMPRODUCT 函數中使用 SUMPRODUCT((=區小姐？)*(小計))，其中的「*」運算，可以將邏輯運算結果的 TRUE／FALSE 陣列，轉換為 1／0 陣列，再和「小計」一起計算其「乘積和」。