學不完．教不停．用不盡

網友提問：在下圖中的 Excel 資料表，如何根據下圖右的『程度／標準』對照表，在下圖左中依據『耗時』欄位判斷是否合於標準？

如下圖，當耗時小於標準值時，以『V』標示。

【公式設計與解析】

儲存格D2：=IF(C2<VLOOKUP(B2,{"易",30;"中",120;"難",240},2,FALSE),"V","")

網友問到一個 Excel 問題，雖然是不難，但是邏輯判斷工作對某些人而言，卻是會造成一些困擾，而不知如何下公式。例如：

網友原題目：

總共有A, B, C三個欄位，如果在A欄位key入『Y』或者『N』，如果是『N』的話，C欄位直接顯示『-』 ，如果是『Y』的話，C欄位會判讀B欄位有無輸入任何的符號或數值，如果有的會顯示『1』，沒有的話顯示『2』。

我將其翻譯為：

1. 若 A 是『N』，則 C 是『-』。

2. 若 A 是『Y』，則：

網友問到的 Excel 問題：如下圖A欄的日期是非數值的日期，無法以數值格式設定方式來調整格式(例如：dd/mm/yyyy→mm/dd/yyyy)，該如何才能調整？

下圖中的A欄格式：dd/mm/yyyy，想要調整為C欄：mm/dd/yyyy，該如何處理？

【公式設計與解析】

該例中的日期格式是固定的，均為：日2碼/月2碼/年4碼，所以可以直接用 MID 函數取出想要的部分。

儲存格C2：=MID(A2,4,3)&MID(A2,1,3)&MID(A2,7,4)

先前的文章：

用Goole表單來設計線上測驗卷並且評分，還可以獲得測驗結果的統計分析

參考下圖，已經可以由回應結果中看到有一個欄位是『分數』欄位，不用再自行計算分數。但是，如果你「修改了正確的答案，或是修改了各題的得分」，該如何修正分數？

本篇文章要來練習如果要自行判斷得分，該如何處理？

1. 先下載回應結果為試算表格式。

每隔一段時間，就有老師會問到在實務上會遇到的問題：如何運用 Excel 來產生隨機座位表？

例如下圖中，每按一次 F9 鍵，就可以產生一個隨機座位表，該如何處理？

【公式設計與解析】

觀察I欄、J欄、K欄，除了座號和姓名之外，在I欄中多了一個亂數欄位，其儲存格內容：『=RAND()』。

網友問到的 Excel 的問題：如下圖，有一個日期和服務的清單列表，其中是三種服務的記錄，如何根據這個服務清單(下圖左)，轉換為個別三個服務的日期清單(下圖右)

【公式設計與解析】

首先，選取儲存格A1:D27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、A服務、B服務、C服務。

接著輸入公式，儲存格F2：

{=OFFSET($A$1,SMALL(IF(INDIRECT(F$1)="V",ROW(日期),999),ROW(1:1))-1,0)}

網友問到 Excel 的問題：如下圖，如何找出每個項目最高價的公司名稱？

觀察下圖，不同項目都有三個公司的標價，其中F欄標示出最高價，要從這二個資訊反推最高價的公司，該如何處理？

【公式設計與解析】

這個問題的概念像是要由表格內容反推欄或列的標題。

儲存格G2：=OFFSET($C$1,0,MATCH(F2,C2:E2,0)-1)

網友問到：在 Excel 的工作表中，如果已寫好了公式，如何因應可能增加資料而造成公式要跟著調整？

例如：下圖中要計算B欄清單中的數量總和，目前有16筆資料。如果增加一筆時，如何能不用修改公式，即可正確運算？

如果你使用公式：

儲存格E2：=SUM(B2:B17)

當新增一筆資料時，公式仍維持『=SUM(B2:B17)』，當然結果也不會有所調整。

網友想問：在 Excel 中如果有兩個固定間隔時間的清單，該如何找出兩者之間所有時間重疊者？

在下圖中：

條件A：在 9:00～13:00 中每間隔 3 分鐘的時間清單。

條件B：在 9:00～13:00 中每間隔 5 分鐘的時間清單。

想要找出條件A和條件B時間重疊者，如下圖右(D欄)。

最近開始鼓勵學校同事在處理各類報表時能善用 Excel 的樞紐分析工具，因此提供以下的多個練習範例。

基本表：

《練習一》

試練習呈現下列表格格式，該如何設定？

網友問到這類的 Excel 應用問題：參考下圖，在加法、減法、乘法前面以『V』代表勾選，如果依勾選結果列出 X 和 Y 的運算式。

下圖中的例子為勾選『乘法』後，運算式為『X*Y=23*4=92』，該如何撰寫公式？

【公式設計與解析】

1. 使用輔助儲存格M1

在 Excel 中如下圖左的資料表共有 1000 筆，其中包含了類別、項目和數量的資料。其中：類別內容為『甲／乙／丙／丁／戊／己』，項目內容為『子／丑／寅／卯／辰／巳』。要如何產出「各個類別中各個項目前三名數量總和的前四名」(如下圖右)？並且希望類別和項目都依由大到小遞減排序該如何處理？

例如：在下圖右中各類別的前四名是：甲→戊→丙→己。而甲的前三名是：卯→丑→子。

這時候『樞紐分析表』工具就可以派上用場了！參考以下的步驟來練習：

1. 選取資料清單中的任一個儲存格，點選[插入／表格]功能表中的「樞紐分析表」。

網友根據前一篇文章：Excel-計算多欄多列交集的小計(SMPRODUCT)，如下圖，如果想要列入小計的欄列交集處包含了文字(例如：A)，則原公式會發生錯誤，該如何調整？

例如：人員『寅』和項目『乙』資料的交集是『A,A,59,A,79,A,A,78,21,69』，其中包含了 3 個A是非數，該如何計算數字和？

【公式設計與解析】

儲存格M3：{=SUM(IFERROR(B2:J18*(B1:J1=M2)*(A2:A18=M1),0))}

有網友想問：在 Excel 的工作表中有如下圖的資料表，如果給予人員和項目的內容，想要查詢交集資料的小計(總和)，該如何處理？

例如：人員『卯』和項目『丙』資料的交集是『96,34,12,13』，其小計(總和)為155。該如何設計公式。

【公式設計與解析】

儲存格M3：=SUMPRODUCT(B2:J18*(B1:J1=M2)*(A2:A18=M1))

公式中的『*』運算子相當於執行邏輯 AND 運算。

學校老師常常要為學生的測驗工作而煩惱！

印了測驗卷又是浪費紙張，有環保問題。每次考完也要一張張的批改(或學生交換改)，也是費時耗事。如果單純只想讓學生練習，其實是可以讓學生在線上練習就好，透過平板也可以同時在教室中施測。

Google 表單可以拿來當作測驗工具了！

使用 Google 表單來設計題目，可以放入文字、圖片、影片，也可以讓學生知道自已的測驗分數，並且對於錯的地方也會獲得一些指正回饋。Google 表單也會為你做一些統計分析，可以是全體和個別的答題分析。

就用以下的例子來看看如何處理。

根據前二篇文章：

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

Excel-由清單挑選列出多組資料中的其中一組並分頁顯示(OFFSET,ROW,陣列公式)

如果把挑選資料的條件再加「日期」欄位，該如何處理：

參考下圖，每個月的每一天中，所有報表集合在一個清單，由第一個欄位決定所屬的日期。在摘要表中只要輸入一個日期，則會篩選出同一日期的所有資料。

最近 Pokémon Go 大流行，一般使用者都見識到了 VR(Virtual Reality)和AR(Augmented Reality)。除了 Pokémon Go 這個遊戲之外，其實你的手機上也有一些你已經在使用的 VR 和 AR 應用。

(1) Google 街景檢視

當你在 Google 街景檢視 App 中，點選了某一個 360 度環景圖，再點選右上角的 Cardboard 圖示。

根據前一篇文章：

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

網友想要以每頁只顯示 8 筆，然後以下拉式清單來切換頁數，該如何處理？

如下圖，每家店的進貨數量可能會超過 8 筆，想要在儲存格M1中使用下拉式清單來選取顯示頁數。

【公式設計與解析】

網友問到：在 Excel 的工作表中有一個各個商家進貨數量表(如下圖左)，如何透過下拉式清單選取商家後，可以自動建立該商家的進貨資料(如下圖右)？

在下圖中共有六個商家，對於不同商品有不同的進貨的數量，要列出有進貨的商品清單，該如何處理？

【公式設計與解析】

1. 定義名稱

網友根據下圖左的 Excel 資料表，想要列出如下圖右的摘要結果，該如何處理？

在下圖左的資料表中每個人員可以參加 3 個組別(沒有重覆)，要如何列出每個組的人員清單(如下圖右)？

【公式設計與解析】

儲存格E2：{=OFFSET($A$1,SMALL(IF(($B$2:$D$23=F$1),ROW($B$2:$D$23),