學不完．教不停．用不盡

網友問到：在一個 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 函數的語法說明如下：

有網友問到：如下圖的資料表，如何將各欄位中有「V」勾選的項目，將第一欄予以加總小計？例如：現貨的小計為 20000+5000+600+1000 = 26600。

這是一個很典型的 SUMPRODUCT 函數的應用，函數的基本語法：

SUMPRODUCT：http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

有網友問到：如何將一個資料來源清單，複製到另一個資料清單中，而且每個資料要間隔二列。(參考下圖)

(1) 假設下圖C欄中的資料，每二列有一個「空白」儲存格。

參考以下的做法：

1. 選取C欄中的資料的儲存格。

2. 按一下 Ctrl+G 鍵，開啟[到]對話框。

有網友問到：如何在一個 Excel 的日期清單中，只計算本週的金額小計？

【準備工作】

選取Ａ欄至C欄中要輸入資料的儲存格，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、星期、金額。

【輸入公式】

(1) 計算本週的第一天(星期日)

有同仁不知道自己在 Word 文件中執行了什麼動作，導致開啟這個文件時，出現了如下圖的提示訊息，主要是說明此文件可能參照到其他檔案的連結，詢問是否要使用連結檔案的資料來更新此文件。你可以選取「是」或「否」來決定要不要連結更新。

通常你可能不知道這個訊息的形成原因，或是這個文件不是你製作的，所以也無從得知連結至那些檔案。

依據 Word 的說明文件，其描述可能發生的原因：

●使用 [選擇性貼上] 命令 (位於 [常用] 索引標籤的 [剪貼簿] 群組，按一下 [貼上] 下方的箭頭，再按一下 [選擇性貼上])，然後按一下 [貼上連結] 選項。

●使用 [物件] 命令 (在 [插入] 索引標籤的 [文字] 群組，按一下 [物件]，再按一下 [物件])，然後核取 [檔案來源] 索引標籤中的 [連結至檔案] 選項。

有網友問到：參考下圖的金額清單中，因為前三碼是幣別，如果要取出金額的數字部分，並且給予小計加總，該如何處理？

【輸入公式】

儲存格D2：=SUMPRODUCT((LEFT($A$2:$A$25,3)=C2)*(VALUE(RIGHT($A$2:$A$25,LEN($A$2:$A$25)-3))))

複製儲存格D2，貼至儲存格D2:Ｄ7。

LEFT($A$2:$A$25,3)=C2：使用 LEFT 函數取得在金額清單中儲存格前三碼，並且判斷是否和儲存格C2中的幣別相同，得到一個 TRUE／FALSE 的陣列。

網友提問了一個實用的問題：參考下圖，想要在一個下拉式清單中選取某一月份時，在第二個下拉式清單中只出現該月份清單中的日期；並且在輸入時間後，比對日期對應的起姳／終止時間，如果不在範圍內則以不同色彩標示出來。

這個問題看起來有點複雜，可能對某些讀者而言會有些難度，以下就盡量詳細說明解釋：

【準備工作】

選取儲存格C1:C17，按一下 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：一月。

選取儲存格D1:D15，按一下 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：二月。

選取儲存格E1:E16，按一下 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：三月。

有網友問到在以下的日期清單中(參考下圖)，如果工時超過 8 小時的部分算為加班，如何分開計算平時和假日的加班？又如何在一個儲存格中即算出加班的總和？

【計算方式一】

(1) 計算平時加班時數

儲存格D2：=IF(WEEKDAY(A2,2)<6,C2-8,0)

WEEKDAY(A2,2)<6：判斷日期是否為「平時」，其中 WEEKDAY 函數的參數 2，代表星期一傳回 1，星期二傳回 2，…，星期六傳回 6，星期日傳回 7。

最近同仁從網路書店搜集到一些購書的資料放在 Excel 的工作表中(參考下圖左)，資料是直式的清單，不知如何轉換為表格形式來呈現(參考下圖右)？

這個例子，要使用 OFFSET 函數試試看！

【輸入公式】

(1) 儲存格C2：

=SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(A:A)-1,0),"","")

在 Excel 的工作表中，有一個含有組別的清單(參考下圖)，如何能根據這個清單，讓其自動依組別編號？

儲存格B1：{=IF($A2=B$1,B$1&SUM(--(B$1:B1<>"")),"")}

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

複製儲存格B1，貼至儲存格B1:E25。

SUM(--(B$1:B1<>""))：其中(B$1:B1<>"")是判斷儲存格內容是否為空白(非數字)，結果傳回True／False，「--」運算可以轉換為 1／0，再經由 SUM 函數運算小計。藉由第一個儲存格開始的累計數量做為編號。

網友問到：請問有沒有辦法將2013/9/10 下午 2:40:00轉換成20130910144000，為了排序避免缺 0 產生問題，所以 9 月變成 09，下午 2：40 變成 1440，秒省略。(參考下圖)

看來這是個有趣的題目，試試以下的公式：

儲存格B2：=TEXT(A2,"yyyymmdd")&RIGHT("0000"&HOUR(A2)*60+MINUTE(A2),4)&"00"

TEXT(A2,"yyyymmdd")：使用 TEXT 函數將日期部分顯示為年４碼、月２碼、曰碼。

HOUR(A2)*60+MINUTE(A2)：取出時和分的數目，再計算時間部分共有幾分鐘。

RIGHT("0000"&HOUR(A2)*60+MINUTE(A2),4)：利用 RIGHT 函數將上式的分鐘數取４碼，不足４碼則右側補０。

在 Excel 的工作表中要取消隱藏某一欄／列，有個簡單的做法，例如：G欄已被隱藏，若要取消隱藏，則只要選取F:H欄，再按一下右鍵，並選取「取消隱藏」即可。(參考下圖)

但是，如果你要取消隱藏的第一欄／第一列，或是在數個連續的欄／列中只想取消隱藏某一欄／列時，該如何處理呢？

試試以下的方式：

例如：要取消隱藏H欄，則按一下 Ctrl+G 鍵，在參照位址中輸入「h1」，按一下[確定]按鈕。

練習題：從微軟網站抓取匯率對照表，建立一個匯率換算的試算表。

常有網友會問到這樣的類似題，所以此次以微軟網站提供的匯率對照表，來練習建立以下拉式清單挑選不同幣別，即可獲得兩個匯率的轉換。

1. 在新增的 Excel 活頁簿中新增一個[匯率]工作表。

2. 選取[資料／取得外部資料]，再按「從 Web」。

小學生在剛遇見分數的運算時，實在是令人頭大的一件事，而父母親在檢查小孩功課時，即使使用計算機也無法顯示分數的結果(參考下圖)。心血來潮，設計一下可以顯示分數的運算式，可以套用在小孩的作業檢查上。

(1) 計算整數

儲存格G2：=INT(C3/C4+E3/E4)

(2) 計算分子

儲存格H2：=LEFT(TEXT((C3/C4+E3/E4)-G3,"???/???"),3)*1

有網友根據這篇：計算儲存格範圍中含有特定字元的儲存格個數(ISERROR,SEARCH)，來搜尋儲存格中是否含有某一特定字，網友想要知道如可查詢含有兩個字元的數量。(參考下圖)

以下的例子，要計算含有「民」且含有「中時」的儲存格數量：

儲存格D2：{=SUM(NOT(ISERROR(SEARCH("民",$A$2:$A$30)))*1)}

儲存格D3：{=SUM(NOT(ISERROR(SEARCH("中時",$A$2:$A$30)))*1)}

儲存格D4：

{=SUM(NOT(ISERROR(SEARCH("民",$A$2:$A$30)*SEARCH("中時",$A$2:$A$30)))*1)}

其中兩個 SEARCH 函數之間的「*」，在觀念上是執行 AND 的邏輯運算。

前一篇文章：提到將欄位中的資料重組為資料為欄位的表格，網友還想要知道左邊表格如何轉換為右邊表格。(參考下圖)

方法應有很多，例舉使用陣列公式的做法：

儲存格H2：

{=OFFSET($A$1,0,SMALL(IF($B2:$E2="彈班",COLUMN($A:$D),99),COLUMN(A1)))}

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

IF($B2:$E2="彈班",COLUMN($B:$E),99)：找出儲存格B2:E2中含有「彈班」的儲存格，對應至欄A:D，可傳回 1,2,3,4。若不含「彈班」者，傳回 99。(99的用意是要給一個較大的數值)