學不完．教不停．用不盡

網友想要在 Excel 中的資料清單中查詢指定字串所在的儲存格位址，該如何處理？

參考下圖，要搜尋指定字串的內容在A欄中的儲存格位址。

【公式設計與解析】

儲存格B2：=IFERROR(ADDRESS(MATCH(D2,A2:A23,0)+1,1,4),"字串不存在")

網友問到一個實用的問題：如何在 Excel 的工作表中根據指定的文字以標示不同色彩？

方法一：（參考下圖）使用 FIND 函數。

選取儲存格B2:B27，設定格式化的規則，輸入公式：

=(FIND($D$2,B2)>0)*($D$2<>"")

(1) FIND($D$2,B2)>0

孩子三更半夜還在寫報告，突然問到在 Excel 中，如何把下圖左轉換成下圖右的圖表？

（座標軸顯示0, 1, 2, 3, 4, 5，其中 0 顯示在垂直座標軸上。）

你的做法會是如何？

（Ｘ）(1) 選取儲存格A1:B7，產生折線圖。參考下圖上。

結果 Excel 將項目欄位也視為數列（因為項目中的內容都是數值）了。

最近的教學又提到了長文件編輯，現在愈來愈強調學生應有實做的能力，會編輯 100 個 1 頁的文件和會編輯 1 個 100 頁的文件，是不一樣的！本篇教材會提到：頁首、頁尾、分節的概念，來加以練習長文件編輯。

在 Word 文件中準備了：「首頁、目錄、本文」三個區塊，如何能在這三個區塊中有各自的頁首和頁尾，並能不同的頁碼格式？要求如下：

1. 首頁區：不顯示頁首文字、不在頁尾顯示放頁碼。（頁首、頁尾皆留空白）

2. 目錄區：頁首顯示「Word練習」，頁尾顯示頁碼，頁碼格式為「Ⅰ、Ⅱ、Ⅲ、...」。

3. 本文區：頁首顯示「Word練習」，頁尾顯示頁碼，頁碼格式為「1、2、3、...」。

以下是三頁分別代表上述的三個區域（已經先以「分頁符號」區隔此三個部分）：

最近幾年在學校裡的工作範疇，有包含了學校網頁的建置（Windwos平台）。但是有些網頁內容應該由其他同仁自已處理，而非再轉一手要由我代勞。

今天想到要回顧這個問題，並且描述我的解決方案！問題：

同仁：不會也不想要建置網頁，但又想要取得檔案的超連結或是提供下載網頁。

自己：不想要隨時待命替同仁轉發檔案和製作網頁，即無時效也責任不清。

（如下圖，提供下載網頁並且可以取得檔案的超連結）

繼前面二篇文章 :

(1) Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

(2) Excel-由原始清單中挑出符合指定內容的清單(ROW,COLUMN,INT,MOD,OFFSET)

網友又問到：（如下圖）如何分年／分月篩選資料？

1. 篩選指定年份

今天同時有兩個網友問了類似的問題，另一篇請參考：

Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

本篇和上一篇的差異在於資料清單的安排不同。

【公式設計與解析】

回答網友提問：如何在 Excel 中，由原始清單中挑出符合指定內容的清單？

如下圖，如何由原始清單中（如下圖左），挑出符合指定內容的清單（如下圖左）？

【公式設計與解析】

如果不想大費周章的寫公式，篩選工具是你很好的選擇，而且很容易即可完成。本篇是要以撰寫公式來完成這個工作。

在小學服務的網友提問：如何根據學生閱讀書本的多寡給予不同等級的認證？

參考下圖的認證規則，我們想要藉由 VLOOKUP 函數以查表方式來處理。

首先，將證認的規則轉換成電腦查表要用的對照表（儲存格F2:G11），如上圖右。

接著，將利用 VLOOKUP 函數來查表：

儲存格D2：=VLOOKUP(C2,$F$2:$G$11,2)

網友根據這篇：http://isvincent.pixnet.net/blog/post/47506173

衍伸發問問題：（參考下圖）如何取出字串中兩個『-』之間的數字？

藉此機會也將公式簡化。

【公式設計與解析】

網友問到：如何在 Word 文件中建立可以同步更新的文字內容？

例如：在下圖中，第一個姓名（王大明）和電話（0987654321）被修改時，其後的多個姓名、電話也能同步被更新。

如果在 Excel 中執行這個工作，就十分的容易了，在 Word 中必須使用「功能變數」來處理。做法如下：

1. 先選取第一個姓名（本例為『王小明』）。

2. 建立書籤，並且命名為『name』，然後按一下[新增]按鈕。

網友問到：在 Excel 中，如何讓活頁簿裡的 2 個工作表比對內容，並且將相同內容的儲存格以相同色標示？

例如以下圖中的表 1 和表 2，不同工作表中的相同儲存格，若是相同內容，則以粉紅色底色標示。

表1：

表2：

網友提問一個有趣的題目：在 Excel 的資料表中，如何根據互相指定同房人員來自動分配房間？

參考下圖，B欄是每個人的姓名，C欄是這個人指定的同房人員，根據這兩欄的資訊要自動分好房間，讓指定的兩人自動分在同一間。

【公式設計與解析】

為了讓讀者更清楚公式，所以使用了輔助欄位。

網友提問：在 Excel 中如何在儲存格中輸入資料時給予提醒或加以限制？

例如：參考下圖，當在A欄分別輸入甲、乙、丙、丁時，當點選B,C,D,E各欄儲存格時，依下圖指示給予提醒或限制。

通常要自動改變儲存格底色是使用「設定格式化的條件」功能；而要顯示提醒或限制的文字，則可以使用「資料驗證」功能。

資料表預設值如下：

回答網友提問：如下圖的 Excel 工作表，如何取出『包/盒』和『盒/箱』前的數字？

1. 計算『包/盒』數量

儲存格B2：=MID(A2,1,FIND("包",A2)-1)

FIND("包",A2)：利用 FIND 函數找尋『包』在儲存格中第 1 次出現的位置。

參考下圖，網友問到：如何在 Excel 中將一個原始金額調整其格式如下圖右？

在下圖中，要將一個數值轉換為金額的表示方式，其中億為 2 位數、萬為 4 位數、元為 4 位數，並且要套用千分位符號。

【公式設計與解析】

儲存格C2：

網友問到：在 Excel 中如何如下圖將數字金額轉換為國字金額？

例如：將 472 轉換為肆佰柒拾貳元。

【公式設計與解析】

儲存格F1：

=TEXT(MID(TEXT($C1,"000"),INT(COLUMN(A:A)/2)+1,1),"[DBNum2]")

參考下圖，網友問到如何在 Excel 的工作表中找出間隔欄位資料的最大值？

例如：在蘋果那一列找出最大的公斤數（500），並且求得最大公斤數對應的價格（24）。

【公式計與解析】

1. 找出最大公斤數

網友問到：如何在 Excel 的工作表中，依數值區間查詢分級的作法？

分級方式如下：

正常值       男生12~18                  ，女11~15

異常D級     男生10~11.9或18.1~19　，女生10~10.9或15.1~16

異常C級     男生8~9.9或19.1~20    ，女生8~9.9或16.1~17

網友原本想要使用巢狀 IF 函數，但是我不建議這樣做。

讀者根據這篇：

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

問到：（參考下圖）如果要計算每個學生所獲得 A 有幾個『＋』、B 有幾個『＋』，該如何處理。

【公式設計與解析】