學不完．教不停．用不盡

開學三週了，有老師問到如何讓課程中的小老師，協助老師利用網路輸入班上同學的小考成績？我想使用 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 鍵，勾選「頂端列」，定義名稱：姓名、薪資。

【輸入公式】

有網友想要知道一年中每個月份的星期一、星期二、…、星期日的數量，該如何處理？

參考下圖，以2014年為例，找出每個月星期日到星期六的日數：

【輸入公式】

儲存格C2：=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0))),1)=COLUMN(A:A)))

DATE($A2,$B2,1)：取得該月的第一天。

有網友問到：

如何將同一列上的資料，由左至右改成由右至左呈現(水平翻轉)？(參考下圖)

如何將同一列上的資料，由左至右改成由上至下呈現(90度轉置)？(參考下下圖)

有位網友問到：在 Excel 中有一列數值資料，如何分別計算奇數欄位(A,C,E,…)和偶數欄位(B,D,F,…)的和？(參考下圖)

假設資料範圍：A1:Z1。

(1) 計算偶數欄位和

儲存格AB2：=SUMPRODUCT((A1:Z1)*(MOD(COLUMN(A:Z),2)=0))

COLUMN(A:Z)：欄位A~Z，將要運算的資料範圍起迄欄位轉換為數字，欄位A=1、欄位B=2、欄位C=3、…。

在網路上看到有人在討論：在一個資料範圍中，要找出指定項目的最大值和最小值，而指定一個項目時，該如何處理？指定二個項目時，該如何處理？(參考下圖)

通常這種題目，都必須透過陣列公式來處理。可以用來練習、認識陣列公式的處理！

【準備工作】

選取儲存格A1:B25，按一下 Ctrl+Shift+F3 鍵，選取「頂端列」，定義名稱：項目、內容。

有網友問到一個問題：在 Excel 中要如何怎麼設定，才能呈現以下的效果：

設定一基數，超過此基數時呈現負差值，字體變紅色；小於此基數時呈現正差值，字體變藍色。例如：(參考下圖)

如基數為10....對應數為15.....則呈現-5(文字轉紅)

如基數為10....對應數為7.......則呈現+3(文字轉藍)

(1) 顯示正負差值

學校老師用 Excel 記錄學生小考成績時，要善用試算表的設定格式化的條件功能。製作動態效果的成績表，達到最大的提醒作用。

例如：將不及格的分數用紅色標示，並且將累計超過三次不及格者，以紅色底色顯示。

(1) 標示不及格分數

1. 選取儲存格C2:H26。

2. 在設定格式化的條件中，新增一個規則：