學不完．教不停．用不盡

有網友問到：如果要使用 Excel 來計算年齡30~34=-1分、35~39=0分、40~44=1分、45~49=2分、50~54=3分、55~59=4分、60~64=5分、65~69=6分、70~74=7分，該如何寫公式？

參考下圖，通常必須先建立年齡區間和得分的對照表，而且年齡區間依排序好。以下分成二種作法來說明。

(1) 年齡區間使用三個欄位

儲存格B2：=VLOOKUP(A2,$D$2:$G$10,4,TRUE)

複製儲存格B2，貼至儲存各B2:B14。

新學期，老師陸續給學生們考試，產生了許多的小考成績，老師也問到了各種的成績採計方式，以下列舉使用 Excel 除了一般的平均(AVERAGE)之外，可能會用到的方式。

(1) 計算加權平均

儲存格L4：=SUMPRODUCT(B4:K4*$B$2:$K$2)/SUMPRODUCT($B$2:$K$2)

SUMPRODUCT(B4:K4*$B$2:$K$2)：計算各次成績乘以加權數的和。

SUMPRODUCT($B$2:$K$2)：計算各次加權數的和。

將以上二式相除，即為加權平均。複製儲存格L4，貼至儲存格L4:L19。

在 Excel 的各個版本中，一直都有提供 AND、OR、NOT 函數，而到了 Excel 2013 版後開始提供 XOR 函數。

XOR 稱為 Exclusive OR，其真值表如下，當有奇數個 TRUE 時，XOR 會傳回 TRUE。

【應用範例】

有網友問到：在一個 Excel 工作表中有一個數列清單，其中有些儲存格有資料，有些儲存格沒有任何內容，如何將有內容的儲存格集合在一起(重新排列)？

參考下圖，分就水平資料和垂直資料來處理。

(1)

儲存格J2：=IFERROR(SMALL($A$2:$H$2,ROW(1:1)),"")

複製儲存格J2，貼至儲存格J2:J9。依序取出最小到最大的值重新排列。

儲存格J12：=IFERROR(SMALL($G$12:$G$19,ROW(1:1)),"")

有網友問到：在 Excel 的工作表中有一個商品的清單(參考下圖左)，如何找出各個商品的出現的次數，並且做出排行？(參考下圖右)

【準備工作】

選取Ｂ欄中有資料的儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：名稱。

【輸入公式】

有網友問到在 Excel 的資料表中有一個資料清單(參考下圖右)，如何根據這個資料清單給予二個條件查表得到結果(參考下圖左)？

【備準工作】

選取儲存格E1:G18，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：項目、勞點時數、勞點點數。

【輸入公式】

儲存格C2：=LOOKUP(1,1/((A2=項目)*(B2=勞點時數)),勞點點數)

在 Excel 中能接受的日期格式，例如：「西元年／月／日」或是「西元年－月－月」，輸入後都會轉換為：西元年／月／日。常見國人會輸入日期格式：西元年‧月‧日，無法被 Excel 接受。如果要將日期分開年、月、日來顯示該如何處理呢？

參考下圖(上)的「日期格式一」為例，你可能最常使用 MID 函數來處理：

儲存格B2：=MID(A2,1,4)，儲存格C2：=MID(A2,6,2)，儲存格D2：=MID(A2,9,2)

在網路上看到有人用以下的公式，覺得可以用在陣列觀念的練習：

儲存格B2：=INDEX(MID($A2,{1,6,9},{4,2,2}),COLUMN(A2))

藉助查表公式 INDEX 函數，將上述的三個 MID 函數，併入一個公式中。其中 COLUMN(A2)=1，當往右欄位複製／貼上時，會產生COLUMN(B2)=2、COLUMN(C2)=3。

根據前二篇文章：

Excel-在日期區間數列中小計分年分月的人數(SUMPRODUCT)
Excel-在日期區間數列中小計分年分月的人數2(SUMPRODUCT)

本篇要延伸列出每個月通過認證的名單，本例僅以 2014 年1 ~ 12 月為例，參考下圖。

【準備工作】

有網友問到：在 Excel 的工作表中有一連續排列的資料，若要取出儲存格重新排列，例如，不要取出第1~18筆資料、要取出第19~64筆資料(有46筆)、不要取出65~82筆資料(18筆)、取出第83~128筆資料(46筆)、...，該如何處理？

為了方便理解，先簡化內容。參考下圖為例來說明，其中項次 1-4 不取用(4個)、5-10 要取用(6個)、11-14 不取用(4個)、15-20 要取用(6個)、...。

依題意，每10個為一個週期，前4個不顯示，後6個要顯示。

【解法一】藉助輔助欄位(D欄)

(1) 在儲存格D2輸入 5。

當你在使用 Dropbox 時要找尋某些檔案，除了使用瀏覽的方式之外，使用「搜尋」工具應該是較方便省時省事的。有同事問到，如何使用除了「關鍵字」搜尋之外的進階搜尋呢？

當你在搜尋框輸入關鍵字時：

當下即會在搜尋框的左側顯示「進階搜尋」字樣：

按一下「進階搜尋」，即可使用「包含其中一個單字」、「不包含其中任何一個單字」、「精確對應字詞」、等搜尋條件，也可調整搜尋範圍：檔案、資料夾、已刪除的項目：