學不完．教不停．用不盡

有網友問到：如下的 Excel 資料表，如何找出各個品項中，日期最大者對應的數量？

若你使用Google試算表，可參考：

利用陣列公式找出各品項資料中日期最大對應的數值(ARRAYFORMULA)

【準備工作】

選取儲存格A1:C50，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、品項、數量。

有網友問到：根據一個銷售額清單的 Excel 資料表，如何根據金額區間來計算總額？參考下圖，以每 500 為區間來計算總額。

【準備工作】

選取儲存格B2:B26，按一下 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：銷售額。

【輸入公式】

儲存格E2：

網友問到：要如何統計儲存格中不同儲存格色彩的個數，其中每二個儲存格已合併，該如何處理？先請參考之前的文章：

Excel-取得儲存格的色彩(http://isvincent.pixnet.net/blog/post/34018213)

作法參考下圖：先取得色彩代表的數值，再測試資料欄位的色彩數值，進一步統計個數。

【參考步驟】

1. 定義二個名稱：

有網友問到一個常見的 Excel 問題：如何合併兩個相同格式但不同內容的表格(參考下圖)？

建議作法：

1. 選取儲存格B2:F13。(其中一個表格的資料區)

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

3. 在[到]對話框中按一下[特殊]按鈕。

有網友問到：在 Excel 中，如果要從多個欄位中列出資料重覆的清單(資料交集)，該如何處理？(參考下圖)

【準備工作】

選取儲存格A1:A26，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：A資料。

選取儲存格A2:E26，定義名稱：全部資料。

【輸入公式】

有網友想要解決一個問題：在一個加班的資料表中，希望能分別計算平日和假日的加班時數。而上班情形又分為二種狀況，一種是正常的星期六日為假日加班日，另一種是輪班之星期二四為加班日。

【準備工作】

選取儲存格A1:C24，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、時數。

一、星期六日為假日加班日

(1) 計算平日加班時數

開學三週了，有老師問到如何讓課程中的小老師，協助老師利用網路輸入班上同學的小考成績？我想使用 Google 試算表是一個不錯的選擇！

首先，你和小老師都先要具有 Gmail 的帳號。

1. 連線至 http://google.com/drive，進入 Google 雲端硬碟。

2. 按一下「建立」，再選取「試算表」。

3. 建立如下圖的成績表：

網友問到一個常見的問題：參考下圖，想要藉由下拉式清單來篩選不同類別的資料，該如何處理？

處理原則是下拉式清單可以使用「資料驗證」功能，而篩選資料通常會用到陣列公式。

【準備工作】

選取「級數」欄位中有資料的儲存格範圍，按一下 Ctrl+Shift+F3 鍵，定義名稱：級數。

【輸入公式】

有網友想要在如下的資料表中，統計特定起始字元的資料個數，該如何處理？

儲存格D2：=SUMPRODUCT(--COUNTIF($A$2:$A$26,C2&"*"))

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

這個問題的公式主要是要在 COUNTIF 函數中使用萬用字元「*」。「C2&"*"」表示以儲存格C2為開頭的資料。

使用 SUMPRODUCT 函數即可以一個公式來計算整個資料陣列的和，相當方便好用。

註：所謂起始字元並不限定一個字元，也可以是多個字元，例如：SD、ABD等。

在 Excel 的資料表中含有 A 組和 B 組資料，如果想要判斷各組的奇／偶數個數，及二者和之奇／偶數個數，該如何處理。

【準備工作】

選取儲存格A1:B22，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：A組、B組。

【基礎知識】

(1) MOD 函數用以餘數，所以可以使用 MOD 函數來找出一個數除以 2 的餘數，用以判斷該數為奇數或是偶數。例如：

最近上課又教到了 AND 函數和 OR 函數，初學者大多不知如何看待這些函數，以下稍微對這兩個函數做一些分析。

一、AND 函數

儲存格C2：=AND(A2,B2)      【複製公式，往下各列貼上】

如果要將輸入 A、B 以 0、1 來表示，則輸出函數改為：

儲存格G2：=AND(E2,F2)*1  【複製公式，往下各列貼上】

有網友問到：如下圖中的資料，欄為各種長度，列為各種高度，欄列的交集為數量，想要藉由輸入長度和寬度來求得數量，該如何處理？

【準備工作】

選取儲存格B1:H1，在名稱管理中新增一個名稱：長度。

選取儲存格A2:A11，在名稱管理中新增一個名稱：寬度。

有網友問到一個非常實用的問題：一般在 Excel 的工作表中輸入資料時，可以利用「資料驗證」中的清單來產生一個選單，方便使用者以選取選項的方式來輸入資料。但是，如果選項內容是會變動的，或是內容會出現一些空白選項(參考下圖)，將會造成一些困擾，該如何克服這些問題？

我們要的是一個會自動增加內容的選單，而且沒有多餘的空白項：

做法如下：

假設選取用來作為選項的內容為儲存格D2:D22。

有網友問到：如下圖左的資料，希望能在不同的工作表中以「區別」篩選出合乎的資料(分別將北區、西區、南區、東區篩選至不同工作表)，參考下圖右，該如何處理？

【準備工作】

選取所有的資料範圍，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名、區別、金額。

以篩選「北區」為例來說明：

【輸入公式】

有網友問到：如果給一個起始的日期，要產生一個固定星期幾週期的日期，該如何處理？參考下圖，以產生每週星期一和星期四為例。

其中：星期一和星期四差三天，星期四和下個星期一相差四天。

在儲存格A2輸入一個起始日期，本例為 2014/2/17。

儲存格A3：=A2+IF(MOD(ROW(1:1),2),3,4)

ROW(1:1)：取得第1列的數值(ROW(1:1)=1)，當往下複製儲存格時會產生 ROW(2:2)=2、ROW(3:3)=3、…。

有網友根據之前一篇：Excel-標示週末假日的日期(WEEKDAY)，想要知道如果日期資料放在不同欄(同一列)時，該如何處理？(參考下圖)

要標示特定日期，需要藉助 WEEKDAY 函式，並利用設定格式化的條件來處理。

1. 選取儲存格A2:I3。

2. 選取「設定格式化的條件」指令，新增一個規則。

有網友問到：在以下的資料表中，如何根據 Num 欄位的內容來傳回「大/中/小」文字？

規則：輸入1,2,3，傳回「小」；輸入4,5,6，傳回「中」；輸入7,8,9，傳回「大」。

以下提供二種不同作法：

(1) 儲存格B1：=VLOOKUP(A2,{1,"小";4,"中";7,"大"},2,TRUE)

這是利用陣列查表方法找出數字對應的文字。

在 Excel 中有一個資料表，其中有多個分組的資料(參考下圖)，如何找出各組最大值和最小值呢？

你可能使用過 SUMIF、COUNTIF、AVERAGEIF 等條件式運算函數，但 Excel 中並沒有 MAXIF 或 MINIF 等函數，不過你可以使用陣列公式來取代。

【準備工作】

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

【建立公式】

有網友問到(參考下圖)，想要找尋資料中的每一列之最後一筆資料為何，該如何處理？

(1) 每列資料全為數字

儲存格K1：=OFFSET(B1,0,COUNT(B1:H1)-1)

複製儲存格K1，貼至儲存格K1:K10。

COUNT(B1:H1)：計數儲存格B1:H1中共有幾個數字，再透過 OFFSET 函數取得後一個儲存格的位置。

有位網友問到：參考下圖，每月有不固定的人員支領薪資，如何計算各個人員整年度支領的薪資？下圖中以四個月來當範例。

【準備工作】

選取儲存格B2:C25，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名、薪資。

【輸入公式】