學不完．教不停．用不盡

有網友問到：如何判斷一個儲存格範圍的內容是否都已輸入數字，如果是則顯示「Yes」，如果有其中一個以上尚未輸入，則顯示「No」，該如何處理？(參考下圖)

儲存格G2：=IF(COUNT(A2:F2)<6,"No","Yes")

假設已知一段儲存格範圍共有 6 個儲存格，則利用 COUNT 函數判斷儲存格範圍內的數字是否小於 6，若是則表示有儲存格未輸入，若否，則表示全部都輸入完成了。

複製儲存格G2，在往下各列貼上。

如果不知道一段儲存格範圍中儲存格的個數，則可以改用以下公式：

網友想要在一個數值清單中，給予限定的範圍內，將符合條件的數值予以加總，該如何處理？

以下圖為例，如果要取 20 ~ 80 的數值來加總，可以使用 SUMPRODUCT 函數來執行乘積和，這樣的做法最簡單。

儲存格D2：=SUMPRODUCT((A2:A25>=20)*(A2:A25<=80)*A2:A25)

(A2:A25>=20)：條件 1，數值小於或等於 20，傳回 TRUE／FALSE 陣列。

(A2:A25<=80)：條件 1，數值大於或等於 80，傳回 TRUE／FALSE 陣列。

(A2:A25>=20)*(A2:A25<=80)*A2:A25：其中的運算子「*」，可以將上式傳回的 TRUE／FALSE 陣列，在運算過程中轉換為 1／0 陣列。

網友根據另一篇文章：Excel-取出間隔欄／列的最小值(ROW,COLUMN,INT,陣列公式)，想要擴大處理的動作，參考下圖，要求不同類別的最大值／最小值，該如何處理？

【準備工作】

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

【輸入公式】

有網友問到：在 Excel 中有一個如下圖的資料清單，每 8 個人為一組，並給予一個流水號作為裝箱代號，該如何處理？

每 8 人分一組並給予編號，可以利用 INT 函數和 ROW 函數來處理：

儲存格C2：=INT((ROW(1:1)-1)/8)+1

ROW(1:1)：ROW(1:1)=1，往下複製／貼上時，會自動產生 ROW(2:2)=2、ROW(3:3)=3、ROW(4:4)=4、…。

再使用 INT 函數，將上式除以 8 的商取不大於的最大整數，再加 1 即為所求。公式中的「+1」是因為代號由 1 開始編號。公式中的「-1」，是因為第 1 筆資料位於第 2 列。

複製儲存格C2，往下各列有資料的位置貼上。

在網友想要知道在 Excel 中如果將時間每二個小時為一個單位並給予一個編號(如下圖左)，如何在輸入一個時間字串後，能自動傳回對應的編號(如下圖右)？

根據上述的規則，其輸入的時間為 4 碼，由時和分組成，設計以下的公式：

儲存格B2：=INT(VALUE(LEFT(D2,2))/2)+1

LEFT(D2,2)：取出時間字串的左邊 2 碼，代表「時」的部分。

VALUE(LEFT(D2,2))：將取出時間字串代表時的 2 碼，轉換為數值。

INT(VALUE(LEFT(D2,2))/2)+1：將上述的數值除以 2，再經由 INT 函數的結果加 1，即為所求。

如果你在 Excel 的資料表中，想要在一欄或一列中取出間隔欄／列來計算其和、平均、最大值、最小值等，通常需要用到「陣列公式」。

在下圖中分別來找出間隔欄的最小值和間隔列的最小值。

(1) 間隔欄的最小值，計算儲存格A2:J2中的價格最小值

儲存格G6：{=MIN(IF(COLUMN(A2:J2)/2<>INT(COLUMN(A2:J2)/2),A2:J2,FALSE))}

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

有老師在一個學生號碼的 Excel 資料清單中，想要自動列出 1 至 35 個號碼中，有那些號碼缺少了而未出現。例如：學生繳交作業的號碼，逐筆記錄後，想要知道有那些學生尚未繳交，該如何處理這個問題呢？(參考下圖)

【輸入公式】

儲存格B2：{=SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格B2，往下各列貼上。

COUNTIF($A$2:$A$26,ROW($1:$35)：找出儲存格A2:A26中，含有 1 至 35 的個數，其中不是 1 就是 0。

有網友問到：在 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 陣列，再和「小計」一起計算其「乘積和」。

有網友問到：如果想要在一個 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))