學不完．教不停．用不盡

在 Excel 資料表中有一個數列形成的清單，有人想要指出隨意給一個數值，要反推找出兩個數的和恰好為這個數值，該如何處理？(參考下圖)

例如：找出總為1952的兩個數，分別為 966 和 986。(予以不同色彩標示)

這個例子可以使用設定格式化的條和搭配 COUNTIF 函數來處理。

1. 選取儲存格A1:I16。

2. 選取[常用／樣式／設定格式化的條件]中的「新增規則」。

大家對於使用 Word 2010 以上的版本來將文件轉換為 PDF 檔，相信已經很有經驗。( Excel 和PowerPoint 也都具有此功能)，最近學校同仁問到，是否有好用的工具可以將 PDF 檔直接轉換為 Word 文件檔，以利後續的排版作業？

其實 Word 2013 中已內建了這個功能，用以下的 PDF 檔為例來說明：(轉換前的文件)

1. 打開 Word 應用程式。

2. 在開啟舊檔時，選取檔案格式為：PDF，再選取一個 PDF 檔。

如果想要計算在 Excel 的儲存格中共出現幾種數字，該如何處理？數字是由 0, 1, 2, …, 9 所組成。(參考下圖)

(1) 使用陣列公式

儲存格B2：{=COUNT(FIND(ROW($1:$10)-1,A2))}

這是陣列公式，輸入完成，要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「{  }」。

ROW($1:$10)-1：在陣列公式中代表 0, 1, 2, …, 9。

如果你在一個 Excel 的資料表中，參考下圖，如果小計欄位是甲除以乙的結果，如果想要取出日期介於 6/5 至 6/15 之間的小計來加總，而在第 8 列出現了一個錯誤訊息，該如何在加總時能排除不計含有錯誤訊息的儲存格呢？

【準備工作】

選取儲存格A1:D21，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、甲、乙、小計。

【輸入公式】

(1) 含錯誤

有網友問到：如何判斷一個儲存格範圍的內容是否都已輸入數字，如果是則顯示「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：資料。