學不完．教不停．用不盡

（網友提問）參考下圖，如何在一個資料表中，指定欄、列數值，在區間範圍中查詢？

例如：欄數值55是在46-65之間，列數值7在6-8之間，欄列交叉對應得到11。

【公式設計與解析】

儲存格H3：=INDEX(B2:E5,MATCH(H2,{1,3,6,9},1),MATCH(H1,{0,21,46,66},1))

（網友提問）在 Excel 中如果要使用公式以起始字串來篩選清單中的項目，該如何處理？

參考下圖，要以起始字串（本例：A135790）在一個號碼的清單中篩選。

【手動篩選】

如果你使用篩選工具，就可以使用「文字篩選」下的「開始於」功能來篩選。

（網友提問）參考下圖，如何在 Excel 中根據分組表將成員名單顯示每位成員所分的隊伍？

下圖中，右邊是分組表，左邊是成員名單，有些成員沒有被分到組別，要將有分到組別者，顯示其組名。

【公式設計與解析】

儲存格C3：=IFERROR(OFFSET($E$2,0,SUMPRODUCT(($E$3:$H$13=A3)*

在 Excel 中有時會用到多表格的查詢（如下圖），你可以使用各種查詢的公式，例如：INDEX、VLOOKUP、OFFSET等，本例以3種不同的做法來練習在多個表格中查詢，也要看看如何簡化公式。前提是多個表格的格式要是一致的。

若要在儲存格H2:J2中使用了3 個條件來搜尋（概念如下圖），可以看成是 3D 的搜尋。

【公式設計與解析】

觀察下圖！圖裡的 Excel 工作表有個資料清單（本例中的儲存格有部分是以亂數產生數值），完全無法辨識儲存格中是否有為含有：公式、註解、設定格式化的條件、常數、資料驗證等。如果想要對這些項目進行操作時，還得先找出各個項目所屬的儲存格才能處理。

還好，Excel 已內建搜尋這些項目的功能，可以用搜尋方式找到：公式、註解、設定格式化的條件、常數、資料驗證等

(1) 搜尋含有「公式」的儲存格（和搜尋含有「常數」的儲存格互為相反）

在 Excel 中己提供了將儲存格範圍複製作圖片的功能，但是沒有提供將該圖片儲存成圖片檔的功能，只能以間接的方式存檔了。

以下圖為例，選取一個儲存格範圍（不接受不連續的儲存格範圍），再選取「剪貼簿／複製／複製成圖片」功能。

設定選項：

然後，開啟一個 Word 文件，貼上剛剛複製的圖片。

在 Excel 中以欄名列號當為儲存格名稱，如果想要根據欄的名稱轉換為欄的數值，該如何處理？（參考下圖）

【公式設計與解析】

參考下圖，在 Excel 中是以A～Z、AA~AZ、BA~BZ、...為欄名的順序。欄的數值為A=1、B=2、C=3、...、AA=27欄。

（網友提問）在 Excel 中常會用到要執行查詢的工作，或許你會用 VLOOKUP 函數或是 INDEX 函數，以欄、列交會之處查詢。但是如果要列出相同者的清單，就無法直接使用這兩個函數。這次來試試 OFFSET 函數。

【公式設計與解析】

本題已假設同機種的物料是連續排列。

儲存格E2：=IF(ROW(1:1)<=COUNTIF($A$2:$A$24,$D$2),OFFSET($B$1,

許多網友對於 Excel 中的 VLOOKUP 函數或許不陌生，但如果要在公式中使用雙條件或多條件，可能就會產生一些困擾，甚至不知如何下手。

因為 VLOOKUP 函數是藉由將要查詢的內容和資料陣列的第一欄比對，查到第幾列時，再由公式中指定的欄數，以欄列交會查出對應的儲存格內容。在此，若要使用雙條件，的確很不方便。該如何解決？

【公式設計與解析】

我的部落格上有許多應用雙條件的查詢公式可參考：

網友問到關於 Excel 中 WEEKDAY 函數的使用：

在公式中使用 WEEKDAY 函數，可以依傳回的數值來判斷為星期幾。

WEEKDAY的參數與傳回值：

1：適用星期日為每週第一天者（傳回值以 1 為起始）

2：適用星期六為每週第一天者（傳回值以 2 為起始）

3：適用星期一為每週第一天者（傳回值以 0 為起始）

在Excel中執行排序動作時，通常習慣使用由上而下的排序方式，如果想要由左至右排序時，該如何處理？而中文字的排序預設是依筆劃多寡來排序（還可以調整為依注音排序），如果想要依自己定義的順序排序，該如何處理？

1. 由上而下遞減自訂清單排序（癸→壬→辛→．．．→丙→乙→甲）

Excel 預設為「由上而下」排序，如果要自訂依癸→壬→辛→．．．→丙→乙→甲，必須要先在排序的順序中選取「自訂清單」。

在 Excel 中使用 VLOOKUP 函數和 HLOOKUP 函數都可以用來做為查詢的工具，這兩者的查詢在概念上有何差別？

【公式設計與解析】

1. 使用VLOOKUP函數

儲存格N4：=VLOOKUP(N3,B3:K22,MATCH(N2,B2:K2,0),FALSE)

當在 Excel 中的公式運算結果出現小數，如果想要轉換為特定分母的分數，該如何處理？以下圖例，將１～24 除以 24，會得到以小數顯示的數值。如何轉換為以 24 為分母的分數？

選取儲存格A15:D20，設定數值格式，自訂：# ??/24。

即 # 後接空格，再接 ??，再接 /，再接 24（指定的分母）。

這是設定數值格式：# ??/48 的結果：

在 Excel 中，日期是以數值來儲存，其格式為「年/月/日」和「數值/數值/數值」很相像，學生常會搞不清楚。試著比較在儲存格中輸入以下的內容顯示的結果：

(1) =2016/2/4：顯示252，其執行公式運算2016除以2、再除以4的結果。

(2) 2016/2/4：顯示2016/2/4，其執行輸入日期2016年2月4日。

(3) '2016/2/4：顯示2016/2/4，其執行輸入2016/2/4字串。

(4) ="2016/2/4"：顯示2016/2/4，其執行公式運算顯示2016/2/4字串。

（回答網友提問）網友根據下圖，想要利用 Excel 來根據指定條件來計算次數，該如何處理？

例如：根據「編號」來計算「遲到、未帶卡、傳遞物品」等的次數。

【公式設計與解析】

選取儲存格A1:G8，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：編號、日期、排班、姓名、遲到、未帶卡、傳遞物品。

如何利用 Excel 來計算多重選擇的總得分？如下圖，假設每個題目的答案由 A, B, C, D 所組成，答案可能是其中的 1 ~ 4 個所組成。

其得分的標準：(可能和實際計分方式不一樣，以下標準僅供練習公式對照之用。)

●該選的選項也有選：+1分

●該選的選項沒有選：+0分

●不該選的選項沒有選：+1分

在 Excel 的工作表中如果能好好利用設定格式化的條件來處理多儲存格的格式，可以不怕儲存格新增／刪除所帶來又要重設的困擾。

例如：（參考下圖）如何在一個資料表中，自動為間隔欄、間隔列、間隔欄列交會產生不同的背景色彩。

這是原始表格：

1. 產生間隔欄不同背景色彩

在 Excel 的工作表中有一個數值構成的資料清單，如何由些數值中找出出現次數最多者，並依由大至小排列？

在下圖中，出現最多的數字是 81，而其出數次數是 9。排列順序依次數由大至小排列。

【公式設計與解析】

選取儲存格A2:J18，按 Ctrl+F3 鍵，在名稱管理員中定義名稱：DATA。

（網友提問）根據下圖左的 Excel 工作表中的資料清單，來找出物料碼以 EIM 為首者的各月訂單數量總和，該如何處理？

【公式設計與解析】

假設資料來源位於儲存格A1:C115。

選取儲存格A1:C115，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：交貨日期、物料、訂單數量。

（網友提問）在 Excel 的工作表中有一組資料清單（參考下圖），如何列出清單中含有指定字元的資料？

例如：要找出資料中含有 T、N、R 的清單。

【公式設計與解析】

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