學不完．教不停．用不盡

今天有老師問到：如何在平時成績表上用簡單的方式讓學生知道目前成績是否及格？

參考下圖，讓不及格的成績加上紅色打叉記號，讓成績及格者加上綠色打勾記號。

在 Excel 中這是很簡單的事！只要使用「設定格式化的條件即可做到」。

1. 選取儲存格I2:I25。

2. 選取「常用/樣式」中的「設定格式化的條件」，再選取「新增規則」。

有人問到：在下圖的 Excel 資料表中含有四個組的人員配置，想要將「參加人員」欄位中的姓名儲存格依其組別顯示該組的底色，該如何處理？這個問題只要使用「設定格式化的條件」即可輕鬆達成！

【準備工作】

選取儲存格C1:F11，按一下 Ctrl+Shift+F3 鍵，建立名稱：第一組、第二組、第三組、第四組。

【操作方式】

1. 選取A2:A28。

最近在學校的 Excel 程式單元中，教完 COUNTIF 和 SUMIF 函數後，接著教 SUMPRODUCT 函數，以下是學生的練習題，你也可以來試試：

【準備工作】

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

【輸入公式】

(1) 性別：男/女，求「人員」個數和其數值總和

有網友問到：在 Excel 中如果有資料是以「清單」形式列表(如下圖左)，想要改以「矩陣」表格列表，該如何轉換？反之，又該如何轉換？

(一) 清單資料轉換為矩陣表格資料(下圖左→下圖右)

儲存格E2：{=IFERROR(INDIRECT(ADDRESS(SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A)),2)),"")}

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

SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A))：在陣列公式中找出和儲存格D2相同組別的第 1 個「列號」，例如：第 1 個「A」在第 5 列。

有人有這樣的需求：想要將 Word 文件檔中的每一頁轉換為 PowrePoint 的一張張投影片。以下圖的 Word 檔中有三頁內容為例，想要將其所有內容轉換至 PowerPoint 中成為三張投影片。

這有別於利用 Word 中的大網文字來轉換為 PowerPoint 投影片大綱，也有人會將 Word 的每一頁轉成圖片檔，再放入 PowerPoint 中。有沒有其他更適合或更好的方法呢？

我其實也沒有特別的方法，不過，曾經藉助 Wondershare PDF Converter Pro 這套軟體，倒是可以輕鬆做到。這是一套可以將 PDF 檔轉換為 Word、Excel、PowerPoint、EPUB、圖像、HTML等格式的軟體。

Wondershare PDF Converter Pro：http://www.wondershare.com.tw/

下載中文版：http://www.wondershare.com.tw/pdf-converter/

在使用的資料中，有一個常見的例子(參考下圖)，其中有二個欄位，一是日期，一是內容。其中日期是由最新到最舊排列，有些日期有重覆，有些日期不在清單上。

根據這個資料表，要來求下圖中的四種統計數量：

(一) 根據距今天的日數來統計累計的筆數

儲存格E2：=SUMPRODUCT((日期>TODAY()-100*ROW(A1))*1)

ROW(A1)：向下複製後，可以產生ROW(A1)=1、ROW(A2)=2、…、ROW(A10)=10。

有網友提到要將多個 Word 文件檔整併成一個，不知道要如何操作？

參考以下的做法(以 Word 2010 版本為例)：

1. 先開啟一個全新的 Word 文件檔。

2. 在檔案總中選取想要合併的 Word 檔。

3. 將所選取的 Word 檔以拖曳的方式拉進已開啟的 Word 文件中。(參考下圖)

有人有這樣的需求，想要取出 Excel 工作表儲存格中數字的每一個位數，該如何處理呢？

(一) 整數數值

參考下圖的數字為 9 位數的數值，要取出其中的每個位數。

儲存格B2：=MID($A2,COLUMN(A2),1)

將數值視為文字來處理，以 MID 函數來取出文字。

複製儲存格B2，貼至儲存格B2:J2。再往下複製到各列中。

在 Excel 中要判斷一個數是偶數或是奇數，常用的方式不外以下數種：(以判斷儲存格A1為例)

(1) 公式 =IF(ISEVEN(A1),"偶數","奇數")

ISEVEN 函數判斷是否為偶數(傳回 True/False)，ISODD 函數判斷是否為奇數(傳回 True/False)。

(2) 公式 =IF(MOD(A1,2)=0,"偶數","奇數")

使用 MOD 函數來求儲存格A1除以 2 的餘數，結果為 0，傳回「偶數」，否則，傳回「奇數」。

(3) 公式 =IF(INT(A1/2)=A1/2,"偶數","奇數")

有讀者問到：在 Excel 中是否可以在資料驗證中的來源資料，使用跨工作表的資料？答案是可以的。

例如：在下圖中的BBB工作表建立一些資料清單(儲存格A1:B31)。

選取儲存格B1:B31，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，建立名稱：姓名。

當你為一個儲存格建立資料驗證時，在資料驗證準中的儲存格內允許選取：清單，在來源中選取儲存格B2:B31，Excel 會自動更換為「=姓名」，它已認得資料範圍被定義為：姓名。

繼上篇文章：讓YoutTube影片可以隨著視窗縮放大小，稍加修改網址即可將 YouTube 的影片跟著視窗調整而調整，符合一些老師上課或是做簡報時使用。可以兩種視窗並例，並且沒有其他YouTube 影片的干擾。做法很簡單，例如：

http://www.youtube.com/watch?v=ImlTUol7-vU

在網址中的「watch」之後加入「_popup」：

http://www.youtube.com/watch_popup?v=ImlTUol7-vU

現在你還可以利用第二種方法做到相同效果：

學校取得某次考試的成績，如果想要自行計算各科成績的五標(底標, 後標, 均標, 前標, 頂標)，並標示學生的程度，該如何處理？以下以 320 個學生，五個科目的成績為例。

首先，選取儲存格C1:H321，按一下 Ctrl+Shift+F3 鍵，定義名稱：國文、英文、數學、社會、自然、總分。參考下圖，來計算五標及標示學生成績的程度。

(一) 若考試成績的五標如下定義：

• 底標：該科成績位於第12百分位數之考生分數
• 後標：該科成績位於第25百分位數之考生分數
• 均標：該科成績位於第50百分位數之考生分數
• 前標：該科成績位於第75百分位數之考生分數
• 頂標：該科成績位於第88百分位數之考生分數

【輸入公式】

(1) 底標(第12百分位)　

有人想要使用 Excel 來找出一年中指定週別的起始日期(星期日)和結束日期(星期六)，該如何處理？

以下圖為例，今年(2013)第一週的起始日期是 2012/12/30，結束日期是 2013/1/5。

現在，首要任務是要先找出每一年的第一週的第一個日期。再設計微調按鈕來控制週別，即可查出所有週別的起迄日期。

第一週起始日期：=DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),1)+1

DATE(C1,1,1)：該年度的第一天。

在 Excel 中常使用自動篩選來做為挑選資料的工具，而且提供使用「萬用字元」來自訂篩選，並且可以使用二個條件的邏輯運算。以下圖的資料為例，來練習自訂篩選。

(2) 篩選文字欄位

如果以「文字」欄位來自訂篩選，你可以看到「等於、不等於、開始於、結束於、包含、不包含」等篩選項目：

這些篩選工具相當於在自訂篩選中使用萬用字元「?、*」的那些用法呢？其中「?」代表任何單一字，「*」代表任何連續字串。

有老師想要知道計算每次考試後前三名的平均成績，在 Excel 中，該用什麼公式？以下的例子就來找尋前三名的平均成績，並且算出多個名次為一組的各組平均成績。

參考下圖，要用人工方式找出名次分組再計算分組，是有點不容易！不過藉由陣列觀念可以輕鬆達到。

【準備工作】

選取儲存格B2：B21，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：分數。

【輸入公式】

在 Excel 2013 版已開始提供 XOR 函數，但是在 Excel 2013 之前的版本都沒有 XOR 函數。如果你想要執行 XOR 的邏輯運算效果，只能使用 NOT、AND、OR 函數來組成。

在你還沒有使用 Excel 2013 之前，先來做做邏輯訓練吧！先看一下 XOR 的真值表(參考下圖)，以二個輸入 A, B，一個輸出 Y 來觀察，可視為二個輸入相同，則輸出為 FALSE，二個輸入不同，則輸出為 TRUE。在運算式中 TRUE 視為 1，False 視為 0。

【輸入公式】

以下五種做法，都可以達到 XOR 功能：

(1) 儲存格C2：=OR(AND(A2,NOT(B2)),AND(NOT(A2),B2))

先前一篇文章：將學生名條亂數重排是在 Excel 中，利用手動方式將學生名條亂數加以重新排列，有人又問到如何利用公式來執行亂數重排呢？(參考下圖)

【準備工作】

先選取儲存格C1:C29，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：亂數。

【輸入公式】

以下提供三種公式供練習：

有人問到：如何在 Excel 的工作表中執行多個工作表的運算，例如：將每一個工作表的同一個儲存格加總？若增加工作表時該如何處理呢？(參考下圖)

你可以使用 keyin 方式輸入以下的公式：

儲存格B4：=SUM(工作表2:工作表6!A1) (先觀察一下工作表名稱的順序性)

你還可以有以下兩種操作方式：

(1) 先輸入「=SUM(」，按一下工作表名稱：工作表2，按著 Shift 鍵，再按一下工作表名稱：工作表6，此時工作表2至工作表6都會被選取，再點選工作表2的儲存格A1，即會產生該公式。

在 Excel 中 LEN 函數用來計算字串的字元個數，常用在字串處理上，以下來看看有那些應用？(參考下圖).

(1) 儲存格B2：=LEN("ABCD")

計算 ABCD 字串的字元個數，結果為 4。

(2) 儲存格B3：=LEN("12345")

計算 12345 字串的字元個數，結果為 5。

(3) 儲存格B4：=LEN(12345)

上一篇介紹了 CHOOSE 函數的基本用法(http://isvincent.pixnet.net/blog/post/40527883)，這一篇要來介紹如何利用 CHOOSE 來輔助查表。

根據下圖中的例子，如果要由序號求得對應的資料 A 和資料 B，通常你可以使用 VLOOKUP 和 INDEX 函數來查表。

例如：

根據序號 4 來求對應的資料 A：

儲存格F3：=VLOOKUP(F1,A2:C16,2,FALSE)