學不完．教不停．用不盡

在 Excel 中有一個日期和數量的清單，如何依日期的季別來計算數量的總和？

先定義儲存格名稱：(1)日期：儲存格A5:A29、(2)數量：儲存格B5:B29。

1. 利用 FITER 數

儲存格E5：=SUM(FILTER(數量,INT((MONTH(日期)-1)/3)+1=ROW(1:1)))

複製儲存格E5，貼至儲存格E5:E8。

今年暑假較為忙碌，時間安排較為緊湊，因此決定參加跟團旅行，以節省時間並輕鬆享受旅程。儘管是跟團，仍然十分注重旅行前的準備工作。在這個數位時代，充分運用資訊工具變得至關重要，這有助於我更好地掌握整個行程的內容。

善用了資訊工具，透過手機應用程式和網站，先行細致了解了行程的每一個細節。仔細整理了旅程的路線規劃，標註停留的地點以及相關的停留時間。此外，我充分利用數位相機捕捉下美麗的風景，並在社交媒體上分享我的旅遊體驗。透過整理相片、記錄遊程中的所見所聞，能夠更豐富地保存這段美好的回憶。同時，也能夠將我的旅行經驗分享給其他有相同需求的人。這種分享不僅讓我回味旅程，也能為其他旅行者提供寶貴的參考。

本篇以到北海道旅遊為例，結合手邊常用的 Google 地圖和 Google 相簿等，整合旅程的時間、地點、相片，讓你的旅程有了回憶錄。

Google 地圖自訂清單整理景點

在 Excel 裡有一個資料清單，如何分別篩選文字或數字？如何在篩選結果中只取部分筆數計算總和？

首先，定義儲存格名稱。(1)資料：儲存格A6:B21、(2)繳費：儲存格B6:B21。

1. 篩選已繳費者

儲存格D6：=FILTER(資料,ISNUMBER(繳費))

利用 ISNUMBER 函數判斷繳費陣列中的每一個內容是否為數值。

在 Excel 的工作表中，常常需要處理日期的清單，例如：生日的清單。如何有效地按照月份進行排序呢？這時候，你可以運用 Excel 提供的 SORTBY 函數和 TEXT 函數，讓這一任務變得更加容易。

SORTBY 函數是一個強大的排序工具，它可以根據你指定的某一欄位進行排序，這在處理生日清單時特別有用。而 TEXT 函數則可以將日期轉換成文字格式，這樣你就可以根據月份進行排序。

你可以使用 TEXT 函數將日期欄位轉換成只顯示月份和日期的文字，然後再利用 SORTBY 函數根據這個轉換後的文字欄位進行排序。這樣一來，你就能輕鬆地按照月份對生日清單進行排序，使得相同月份的生日項目排列在一起。

定義儲存格名稱：

*生日：儲存格B6:B27

在 Excel 中的運算越來越接近程式的效果。你現在可以利用一系列最先的函數，例如 LAMBDA 函數、BYCOL 函數以及 BYROW 函數，來實現高度自定義的計算和處理。

這些新一代的函數使得 Excel 的運算能力更加靈活且強大，它們允許你以更複雜的方式處理數據，進行動態陣列的操作，甚至可以自定義函數來滿足特定的需求。LAMBDA 函數能夠讓你自行定義函數，這樣就不再受限於預設的函數庫，能夠根據具體的情況進行計算。而 BYCOL 函數和 BYROW 函數則能夠針對行或列進行操作，這使得動態陣列的應用更加方便。

以下要用「計算各班不及格人數超過5人者(不含5人)，共有幾班？」為例，說明 LAMBDA 函數和 BYCOL 函數如何巧妙搭配使用。

公式：=SUM(BYCOL(B3:G21,LAMBDA(col,1*(SUM(1*(col<60))>5))))

(1) 1*(SUM(1*(col<60))>5)

在 Excel 中，如果想要產生文字的隨機結果，可以運用特定的函數來實現，像是 RAND 或 RANDBETWEEN 函數。這些函數能夠生成數值的隨機結果，而後續的步驟則是基於這些隨機數值，在一個文字陣列中選擇對應的內容，以達成文字的隨機化。

本篇例舉四種方式來練習：

1. 結合 CHOOSE+RANDBETWEEN 函數

公式：=CHOOSE(RANDBETWEEN(1,4),"甲","乙","丙","丁")

RANDBETWEEN 可以給予亂數起迄的數值，即可產生該範圍內的亂數值。

在 Excel 裡可以使用 RAND 函數來產生 0~1(不含) 之間的隨機亂數，如果想要用來產生亂數，但是又想要控制亂數產生的機率，該如何處理？

本例要使用 RAND 函數結合 MATCH 函數來解決這個問題，試試以下的實驗做法，例如：

將儲存格A1:T50，共1000個儲存格，設定公式：=MATCH(RAND(),$W$3:$W$9,1)

在儲存格W3:W9中已設定了控制機率的數字，可以在出現率欄位中換算得到其出現的比率。

依產生狀態計算 1~6 產生的數量和比率，從結果看來似乎大致符合想要的機率分佈。

你可以不斷的按 F9 鍵，即可重新產生一組結果。

在 Excel 裡，當你需要從一個陣列中提取部分內容時，TAKE 和 DROP 函數是非常有用的工具。這兩個函數可以協助你有效地掌握資料的部分內容，進而進行更深入的分析和處理。

TAKE 函數的功用在於從陣列的開頭提取指定數量的元素，這對於選取前幾個重要的資料非常有幫助。而 DROP 函數則是從陣列中刪除指定數量的元素，讓你能夠輕鬆排除不需要的資料，專注於分析核心內容。這兩個函數的組合使用，能夠讓你更加靈活地處理資料。你可以選擇從陣列的開頭提取一些關鍵資料，然後再使用 DROP 函數去掉不必要的部分，從而將你的資料整理得更加簡潔有序。

先理解其語法：

TAKE 語法：=TAKE(陣列, 列,[欄])

陣列：要取用列或欄的陣列。

列：要取用的列數，負值會自陣列的結尾開始取。

在 Excel 中，有一個資料清單，資料分散在三個欄位，如果希望根據這三個欄位的組合，列出不重複的選項內容並計算其數量，你可以巧妙運用 VSTACK 函數和 UNIQUE 函數來設計一個高效的公式。

VSTACK 函數的作用在於將多個資料範圍堆疊成一個單一的垂直範圍，這對於結合不同欄位的資料非常有用。接著，你可以利用 UNIQUE 函數，從這個合併的範圍中找出不重複的選項，這對於分析和統計資料具有重要意義。

1. 列出不重覆的項目

儲存格F6：=UNIQUE(VSTACK(B6:B15,C6:C15,D6:D15))

(1) VSTACK(B6:B15,C6:C15,D6:D15)

在 Excel 的資料處理中，當你需要從一個資料清單中找出「唯一值、未重複、重複 n 次」的數據時，你可以充分利用 FILTER 函數和 UNIQUE 函數這兩個強大的工具，以高效且直觀的方式達成目標。這兩個函數的結合不僅能夠讓你快速地進行數據篩選和分析，還能夠確保結果的準確性和完整性。

FILTER 函數的優勢在於它能夠根據指定的條件，將資料清單中符合要求的數據過濾出來。這對於查找重複值和計算特定次數出現的數據非常有用。而 UNIQUE 函數則能夠快速地找出資料清單中的唯一值，這在處理大量數據時尤為重要。

通過將這兩個函數結合使用，你可以輕鬆地實現對資料的多重操作，而且只需要一個公式即可完成。這不僅節省了時間，還減少了出錯的可能性。無論是在商業分析、數據處理還是其他領域，這種組合都能夠提升你的工作效率，幫助你更好地理解和分析資料。

【公式設計】

1. 列出唯一值清單

以往，你使用 Excel 中 VLOOKUP 的經驗，都是以資料第一個欄位為準，再求其對應的欄位回傳找到的內容。使用上有其限制而造成不便。而 Excel 2019 之後版本的 XLOOKUP 函數足以取代 VLOOKUP 和 HLOOKUP 的功能。

XLOOKUP 函數已經成為了一個極為強大且易於使用的工具，可以有效地替代傳統的 VLOOKUP 函數。這項改進不僅使搜尋和查找資料變得更加便捷，還為使用者提供了更多的彈性和控制。

相較於 VLOOKUP 函數，XLOOKUP 函數的優勢在於其靈活性和功能豐富性。你可以輕鬆地指定多個搜尋條件，進行精確的查找，並且能夠處理不同大小的資料範圍。此外，XLOOKUP 函數還支援在錯誤情況下返回自定義的結果，大大提升了處理例外情況的能力。當你需要處理大量搜尋資料時，常見的情況是搜尋所需的時間也會隨之增加。然而，如果你渴望縮短這段搜尋時間，可以藉由指定 XLOOKUP 函數運用二分搜尋法（BINARY）來加速搜尋過程。

XLOOKUP 函數提供了從頭部或尾部開始搜尋的選項，同時也支援遞增或遞減的二分搜尋法。這種方法在資料量大的情況下尤其有效，因為使用二分搜尋能夠迅速找到所需資料，並大幅縮短搜尋所需時間。然而，在使用二分搜尋前，你需要確保原始資料已經按遞增或遞減排序。

XLOOKUP 函數的這些功能不僅能夠加速搜尋過程，還能在大型資料集中提高效率。這種方法的運用能夠幫助你更快地找到所需資料，特別是在處理龐大資料量時，而前提是你的資料必須事先經過適當的排序。這將有助於優化你的資料處理流程，並在處理大數據時保持高效率。

例如，根據 XLOOKUP 的語法：

在 Excel 的工作表中，當你擁有一個包含數據清單的表格時（如下圖所示），你可能會想要在每列中計算小計，而且只希望使用一個儲存格中的公式就能完成這項任務。這個挑戰可以透過動態陣列公式來解決，其中包括了一些強大的函數，如 BYROW 函數和 LAMBDA 函數。

BYROW 函數允許你對指定的數據範圍進行逐列運算，將每一列的值傳入自定義的 LAMBDA 函數中進行計算。這意味著你可以在一個儲存格中輸入一個 BYROW 函數，並在其中使用 LAMBDA 函數來計算每列的小計。這種方法極大地簡化了計算過程，並且能夠在單個儲存格中完成整個計算過程。

舉例來說，假設你想要計算每列的總和。你可以使用 BYROW 函數來遍歷每列的值，然後在 LAMBDA 函數中使用 SUM 函數來計算總和。這樣一來，你只需要在一個儲存格中輸入 BYROW 函數，就能夠自動計算每列的總和，無需手動處理每一列。

這種動態陣列公式的使用方式不僅提高了計算效率，還讓複雜的任務變得更加簡單和直觀。通過 BYROW 函數和 LAMBDA 函數，你可以在 Excel 中輕鬆實現一個儲存格計算多列小計的功能，這將大大提升你的資料分析和處理效率。

定義儲存格名稱，資料：儲存格A3:H12。

在 Excel 裡，FILTER 函數是用來篩選資料的好用工具，要如何利用 FILTER 函數取出清單中的後 n 項或後 n 項？

以下的範例以 n=3 為例：

先定義名稱：

日期：儲存格B3:B27

數量：儲存格C3:C27

在 Excel 的資料處理中，經常需要根據特定字元的位置來提取其前後的文字。這在許多情況下都非常有用，比如從 Email 地址中提取使用者 ID 和伺服器名稱。在處理這類任務時，Excel 提供了 TEXTBEFORE 函數和 TEXTAFTER 函數，讓操作變得輕鬆而高效。

TEXTBEFORE 函數允許你基於指定的分隔符或字符，從文字串中提取出該字符之前的部分。例如，你可以使用 TEXTBEFORE 函數來從 Email 地址中獲取使用者的 ID 部分。同樣地，TEXTAFTER 函數則允許你提取指定字符之後的文字。這些函數的運用不僅使得提取特定內容變得簡單，也大大節省了手動處理的時間和可能出現的錯誤。

這些功能在許多情境下都能發揮作用，不僅限於 Email 地址。當你需要從文字串中提取特定部分時，這些函數可以幫助你快速完成任務。無論是進行資料清理、分析還是報告生成，TEXTBEFORE 和 TEXTAFTER 函數都是你的有力助手，讓 Excel 資料處理更加高效和準確。

如果你的 Excel 版本並沒有 TEXTBEFORE 函數和 TEXTAFTER 函數，仍可以有其他函數可以完成。

在 Excel 中使用 FILTER 函數執行篩選工作，非常的方便實用。要如何在查詢出現錯誤時，不要顯示錯誤訊息，而是顯示指定內容？

當使用 FILTER 函數來篩選資料時，正常狀況下依條件篩選顯示對應的結果。但是，如果依條件沒有篩選到任何內容時，例如：

儲存格G3：=FILTER(人數,(班級=E3)*(認證=F3))

則會顯示錯誤訊息，本例為：#CALC!，雖然這是正常的狀況，但是如果能讓顯示的訊息更具親和性會比較好。所以，可以改成：

儲存格G9：=FILTER(人數,(班級=E9)*(認證=F9),"無資料")

在 Excel 中的工作表裡有一個三個欄位的資料清單，內容有部分缺漏。如何快速刪除資料不全者？

在此，只要一個函數(FILTER)，即可搞定喔！

刪除資料不全者

在 Excel 裡有一個日期清單，該如何使用篩選函數依月份和星期來篩選日期？

定義名稱：

人員：儲存格A3:A24、日期：儲存格B3:B24、數量：儲存格C3:C24、

資料：儲存格A3:C24

1. 依月份篩選

在 Excel 中，動態陣列函數的出現確實為數據處理帶來了更多便利。在利用動態陣列函數產生結果後，你可能會想進一步運用這些結果來完成其他任務。下面將介紹如何在動態陣列函數中有效使用其產生的結果，提升數據處理效率。一旦你使用動態陣列函數（例如：FILTER、SORT、UNIQUE等）獲得結果，本篇要介紹，如何使用動態陣列函數產生的結果？

選取儲存格A2:C18，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：班級、認證、人數。

1. 列出不重複班級：=UNIQUE(班級)

利用 UNIQUE 函數列出不重複班級，這是一個動態陣列結果。

2. 列出不重複認證：=TRANSPOSE(UNIQUE(認證))

在 Excel 裡有一個日期清單，其中的日期會有所重覆，要如何計算不重複的日期數目、並列出不重複的日期和對應的數量？

在 Excel 中，若存在日期清單且其中日期可能重複，你可能想知道如何有效地計算不重複的日期數量，並將這些獨特的日期以及其對應的出現次數列出。這種情況下，你可以使用各種函數和技巧來完成這個任務，讓你的資料分析更加精確和有力。

一種方法是使用「進階篩選」功能，選取日期列，然後去除重複項，這樣你就能夠獲得不重複的日期列表。接著，使用「計數函數」（如COUNTIF）來計算每個獨特日期在原始清單中的出現次數，從而得到對應的數量。這種方法適合相對簡單的資料。

如果資料複雜，你可以運用例如「UNIQUE」和「COUNTIFS」來更有系統地達成目標。UNIQUE 函數可幫助你找出不重複的日期，而 COUNTIFS 函數則能計算每個日期的出現次數。

這些方法將讓你能夠快速、準確地分析不重複的日期並顯示對應的數量，從而更好地瞭解你的資料。無論是簡單的清單還是複雜的資料，透過這些技巧，你將能夠有效地進行資料處理和分析。

本篇要使用的是：INT 函數、UNIQUE 函數、SORT 函數的應用。

Excel 中的 LAMBDA 函數可以配合名稱管理員即可以建立自訂函數。

參考這篇文章：Excel-建立可以重複使用的自訂函數(LAMBDA,LET)

LAMBDA 函數也可以搭配特定函數使用而不用到名稱管理員，本篇要針對這些函數來做練習。例如：

REDUCE 函數

透過對每個值套用 LAMBDA 函數並在累計值中傳回總計值，將陣列縮減為累計值。

MAP 函數