學不完．教不停．用不盡

（網友提問）在 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 鍵，勾選「頂端列」，定義名稱：資料。

當多人編輯一份文件進行協作時，除了輸入資料這個基本工作，過程中的對話機制是很重要的。如何能快速而迶效的傳遞訊息，讓編輯工作不會陷於反覆不斷的修正，而且能正確讓團隊成員知悉各自的看法，相對更是重要的歷程。

以下用Google文件的編輯為例，來稍微描述Google在共享文件編輯時的對話機制。本例假設這個文件有二個使用者共用：Ａ使用者：文件原始擁有者／Ｂ使用者：文件共用者。

1. 使用建議操作

B使用者在編輯文件時啟用「建議操作」，並輸入一些建議。

Ｂ使用者所輸入的文字會以不同色彩標示之外，視窗右側會顯示編輯的內容和編輯的時間。

當你在Google雲端硬碟編輯文件時，如何能在文件中插入Google試算表中的資料和圖表？如何在Google試算表中修改資料時，Google文件的內容也隨之變動呢？

當你在Google試算表中建立好一個資料表，也依該資料表產生了圖表。要將此資料表放置於Google文件，步驟如下：

1. 選取Google資料表的儲存格範圍。（本例：儲存格A1:D5）

2. 選取「編輯／複製」。

（網友提問）如下圖，在 Excel 中的工作表有一個資料清單，如何根據指定的月份、起始項目、終止項目求得此區間的總和？

本例試著使用 OFFSET 函數和 SUMPRODUCT 函數來處理。

【公式設計與解析】

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

（讀者提問）在 Excel 中如何利用公式求得變動範圍中儲存格的最小值？（參考下圖）

【公式設計與解析】

儲存格E2：=MIN(OFFSET($B$2,(ROW(1:1)-1)*E$1,0,E$1,1))

複製儲存格E2，貼至儲存格E2:E10，貼至儲存格G2:G10，貼至儲存格I2:I10。

（網友提問）在 Excel 的工作表中（參考下圖），如何將下圖中的員工編號配給每個產品代碼？

員工編號的顯示數量是依產品代碼的數量而定，如何產生員工編號和產品代碼的清單？

【公式設計與解析】

1. 定義名稱

根據先前的這一篇文章：

Excel-找出同一欄最後一個指定數字對應的日期(SUMPRODUCT,LARGE)

參考下圖，如果將數值範圍擴大為多欄，則原來公式要如何變更？

【公式設計與解析】

在 Excel 的工作表中，常見要把左右兩個儲存格的內容集合在一個儲存格並且上下分列，或是把一個儲存格並且上下分列的內容改成左右分欄置放，該如何處理？

【公式設計與解析】

1. 合併2筆資料上下分列

儲存格F2：=B2&CHAR(10)&C2