學不完．教不停．用不盡

本篇是要針對在 2015/9/23 的這一篇文章公式的改變：

Excel-根據雙條件來摘要資料(SUMPRODUCT,OFFSET,LARGE)

以下是要改用 Excel 2021 以上版本的 FILTER 函數來實作。

公式變的簡單易懂，用舊版的人撰寫公式較為辛苦，在這方面，Excel 是有進步的。

參考下圖，在 Excel 要將一個資料清單裡篩選出其中一組，如果採用 Excel 2021 以後版本的 FILTER 函數，則公式較簡短且簡單。如果使用傳統的陣列公式，除了冗長且不易理解。

【設計與解析】

選取儲存格A3:H28，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：品名、售價、忠店、孝店、仁店、愛店、信店、義店。

在儲存各J4中設定資料驗證為下拉式清單，來源為：=$C$3:$H$3

觀察下圖，在 Excel 裡兩欄的資料，要轉換矩陣式的統計表，該如何處理？

【設計與解析】

選取儲存格B3:C29，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：茶飲、特調。

本例提供4種公式來比較：

(1) 儲存格F4：=COUNTA(FILTER(茶飲,(茶飲=$E4)*(特調=F$3)))

在 Excel 裡的資料清單，很常會使用「分組計算」的功能，以下的三個公式使用不同的函數來表現，一方面互相比較，一方面融會貫通。

【設計與解析】

選取儲存格C3:D100，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：組別、數值。

1. 利用FILTER函數

在 Excel 裡有一個多欄的資料清單，如果想要將多欄轉換為單欄(參考下圖)，該如何處理？

首先，以 OFFSET 函數為主來設計。

【設計與解析】

1. 列出人員

在 Excel 裡，如果要列出非空白項目的清單，該如何設計？

本篇將會學到：

要比較篩選函數和以陣列公式模擬篩選等不同方式，來練習如何列出非空白項目的清單。

【設計與解析】

在 Excel 中的資料查詢和資料篩選，常會以欄位查詢資料內容，或是以資料內容求欄位名稱，試試以下的練習。

本篇可以學到：

1.單一欄位篩選與跨欄位篩選

2.欄位內容和欄位名稱交換查詢

延伸這篇：Excel-關於超連結的使用

在 Excel 裡，使用到網址超連結時，會自動轉換為 URL 編碼。

如果使用 HYPERLINK 函數產生超連結時，

儲存格C6：=HYPERLINK(A5,B5)

在 Excel 裡要使用陣列公式時，對於陣列內容為定數，則必須要輸入較長的文字，例如：{"甲";"乙";"丙";"丁";"戊";"己";"庚";"辛";"壬";"癸"}，通常在輸入文字時會帶來不便。

如今，使用 Excel 的 ARRAYTEXT 函數可以輕鬆轉換而得。

【設計與解析】

例如，

本篇要以 Excel 2021 的新增函數進行篩選、查詢、排序之綜合練習。會用到的函數：

(1) 篩選：FILTER

(2) 排序：SORT

(3) 查詢：XLOOKUP

(4) 取唯一值：UNIQUE