學不完．教不停．用不盡

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

(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 陣列，再和「小計」一起計算其「乘積和」。

有網友問到：如果想要在一個 Excel 的通訊錄清單中，想要藉由下拉式清單來查詢名單中的資料，該如何處理呢？

大多數網友在使用 Excel 來查詢資料時，都會用到幾個常用的查詢函數，藉由這個例子再來練習相關的函數。

參閱下圖，學號是一個唯一值，就用學號來做為查詢的關鍵字。

參考以下步驟：

1. 選取儲存格A1:A25，按 Ctrl+Shift+F3 鍵，選取「頂端列」，定義名稱：學號。

有網友想要知道在下圖中的 Excel 資料清單，如何計算前幾天的平均分別為多少？

本例使用 OFFSET 函數來取得相對某一儲存格的儲存格範圍，例如：

儲存格C5：=AVERAGE(OFFSET($B$3,0,0,1,ROW(1:1)))

複製儲存格C5，貼至儲存格C5:C14。

OFFSET($B$3,0,0,1,ROW(1:1))：表示儲存格B3:B3

有網友問到如下圖左的 Excel 資料清單中，如果要將編號為奇數／偶數的項目，分別取出計算其平均，該如何處理？其中有部分儲存格的內容為空白。

計算平均時，如果儲存格的內容為空白，理應不併入計算。而 AVERAGE 函數，也是會將儲存格為空白儲存格者不列入平均。

先選取儲存格A1:B20，按一下 Ctrl+Shfit+F3 鍵，定義名稱：編號、數值。

【錯誤結果】

儲存格E2：=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT(--(MOD(編號,2)=1))

有網友根據這篇：Excel-計算實際年齡(年月日)-DATEDIF文章，提出了一個問題：

如果在 DATEDIF 函數中，使用參數：md(忽略年月,計算天數)，當起始日為 2014/7/28，結束日分別是 2014/10/9 與 2014/11/9，結果分別是11和12，為何會有這樣的差異呢？

關於 DATEDIF 函數的語法說明如下：