學不完．教不停．用不盡

在 Excel 中有一個清單是依日期的起始和結束來分組，如何給予一個日期，來查詢其分組？

【設計與解析】

先為儲存格範圍命名，選取儲存格A3:c13，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：組別、起始、結束。

根據儲存格E4的日期來反查組別。

1. 使用 XMATCH 函數

關於 GUID 可以參考：https://zh.wikipedia.org/zh-tw/全局唯一標識符

本篇要利用亂數產生函數(RANDBETWEEN)和10進制轉換16進制函數(DEC2HEX)，來產生隨機的 GUID。

GUID 以 8-4-4-4-12 碼的組合顯示，以亂數產生的 GUID，只要按 F9 鍵即可產生新的一組。

【設計與解析】

(1) 產生8碼16進制亂數：

在 Excel 裡要計算算階層可以使用 FACT 函數、計算雙階層可以使用 FACTDOUBLE 函數。以下各例舉 4 種方式來運算階層。

【例一】計算階層 5!=5*4*3*2*1

(1) 公式：=5*4*3*2*1

(2) 公式：=FACT(5)

網友問到如何在 Excel 裡產生 QR Code 圖片？

由於 Excel 無法自動產生 QR Code 圖片，但是可以藉助 Google API 來協助，再加上 Excel 裡的 IMAGE 函數。

Google API 參考說明：

https://developers.google.com/chart/interactive/docs/gallery?hl=zh-tw

根網址：https://chart.googleapis.com/chart?

前一篇文章：Excel-依色彩排序(為資料分類)，介紹了依色彩排序。

本篇來介紹依色彩計算總和。

這是原始資料，其中儲存格已有設定色彩。

利用公式：=SUBTOTAL(9,D4:D27)，參數指定「9」計算總和。

在 Excel 裡依色彩排序是個不錯的做法！因為色彩總是可以讓人很直覺的做出區隔，色彩可以用來分類，而將相同色彩集合在一起，可以使用排序功能。如何依色彩排序？

在資料清單中選取「排序」，在排序對象中可以選取：儲存格色彩或字型色彩來排序。本例選取：儲存格色彩。

在此，必須將想要排序的色彩依序選取，並選取排序對象：儲存格色彩。(在此有5個色彩，必須做5次排序)

結果如下：

當你在 Excel 工作表裡選取某些儲存格，在狀態列上會顯示一些預設的運算結果。例如：平均值、項目個數、加總等。

這些資訊並非看完即丟，而是可以被保存。例如：點選在狀態列上的「加總」，則加總的結果會被複製到剪貼簿中。

狀態列上能顯示的內容很多，例如：

在先前的文章中已使用了 TEXTJOIN 函數 和 TEXTSPLIT 函數處理文字的分割和串接。

參考：Excel-分割字串和串接字串

本篇要介紹 TEXTBEFORE 函數和 TEXTAFTER 函數，也可以用來分割字串。

(1) 利用 TEXTJOIN 將儲存格B4:K4內容合併成一個儲存格

儲存格B6：=TEXTJOIN(",",,B4:K4)

在 Excel 中有許多的內建函數可以使用，但是每個人的需求因人而異，有些運算會重覆使用，因此有自訂函數的需求。Excel 中的 LAMBDA 函數配合名稱管理員即可以建立自訂函數。

或許先前你已會使用 LET 函數(可參考：Excel-解析LET函數)。利用 LET 函數可以在函數中建立可重覆使用的運算式。

而 LAMBDA 函數可以建立自訂函數，透過傳遞參數，即可傳回運算結果。而使用時不用知道函數如何運算。

例如：建立反轉字串的運算

在 Excel 裡如果想要重排一個陣列，可以使用 CHOOSECOLS 函數和 CHOOSEROWS 函數。

(1) 儲存格B11：=CHOOSECOLS(B4:G8,1,3,5)

指定抽取第1,3,5欄重組為新的陣列。

也可以使用這樣的公式，儲存格B11：=CHOOSECOLS(B4:G8,{1,3,5})

(2) 儲存格B18：=CHOOSEROWS(B4:G8,1,3,5)

在 Excel 裡要串接不同陣列(表格)，可以使用 VSTACK 函數或是 HSTACK 函數。

1. 依垂直方向串接陣列

使用 VSTACK 函數可以將多個陣列依垂直方向串接。

(1) 儲存格F7：=VSTACK(B3:D5,F3:H5)

原始內容為空白者會顯示「0」。

先前有幾篇文章提及在 Excel 裡，文字的串接是利用 TEXTJOIN 函數，如果要分割字串，可以使用 TEXTSPLIT 函數。

(1) 儲存格A5：=TEXTJOIN(",",,A3:J3)

TEXTJOIN 函數用以串接字串。將儲存格A3:J3的內容依序串接成一個字串。

(2) 儲存格A7：=TEXTSPLIT(A5,",")

TEXTSPLIT 函數用以分割字串。將儲存格A5的內容依指定分割方式置於多個儲存格。

本篇要利用 DROP 函數和 TAKE 函數來取用和去除陣列(表格)中的欄或列來組成新的表格。

【設計與解析】

1. 去除陣列中的第1列和第1欄

儲存格B11：=DROP(B3:F8,1,1)

利用 DROP 函數移除陣列中的第1列和第1欄。

如果要將一列或一欄轉換為多列或多欄組成新的表格，可以使用 WRAPCOLS 函數和 WRAPROWS 函數。

1. 將一列轉換為多列或多欄

儲存格A5：=WRAPCOLS(A3:J3,4,"X")

儲存格G5：=WRAPROWS(A3:J3,4,"X")

儲存格A12：=WRAPCOLS(A3:J3,5,"X")

儲存格G12：=WRAPROWS(A3:J3,3,"X")

在 Excel 裡將一個表格裡的資料陣列轉換為一欄或是一列是常見的需求，目前在 Microsoft 365 中有提供 TOROW 函數和 TOCOL 函數來完成，如果沒有 Microsoft 365 者，也可以透過 TEXTJOIN 函數來完成。

【延伸閱讀】

 Excel-將表格中的內容轉成一欄和轉成一列組成新的表格

 Excel-將一列(一欄)轉換為多列或多欄組成新的表格

常有網友會詢問關於抓取股票資訊的問題，因為我沒有在接觸股票，所以對這些議題較為陌生。但是研究動態陣列時，倒是知道有一個函數和歷史股價資訊有關可以運用。

在 Excel 裡有一個函數：STOCKHISTORY，可以用來抓取歷史的股價相關資訊。(特別聲明：圖中資訊無法保證其正確性。)

下圖是由 Google 搜尋顯示的結果：

【設計與解析】

在工作中使用雙螢幕有很多方便之處，雙螢幕之下可以設定在單一螢幕或是延伸桌面，而延伸桌面就有主副之分。

因為主要的工作列內容，例如：喇叺圖示、網際網路存取圖示、通知區域的各種圖示等，預設只能顯示在主螢幕(第1螢幕)的工作列上，如何能將主要工作列內容顯示在延伸桌面上？

利用以下的操作：

1.取消工作列的鎖定(在工作列上按右鍵，取消選取「鎖定所有工作列」)。

2.拖曳主螢幕工作列至延伸桌面。

在 Excel 裡要建立萬年月曆的方法有很多，可以設計各式各樣的公式來達成。以下要利用操作樞紐分析表方式而非建立公式的方式來完成。

【設計與解析】

1. 建立整年日期表

(1) 建立「日期」清單

儲存格J6：=ROW(INDIRECT(DATE(K5,1,1)&":"&DATE(K5,12,31)))

網友問到：如下圖，如何計算日期區間裡每天的人數。

在下圖中有一個人員清單，每個人員都有一個起始、結束的日期，例如：計算每天公出（請假）的人數、飯店每天的住宿人數等。

【設計與解析】

選取儲存格B4:C14，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：起始、結束。

1. 列出日期

網友問到：在 FILTER 函數中使用條件 AND 和 OR 運算的問題。

本篇以一個實例，對照這兩個邏輯運算的使用。

【設計與解析】

先定義名稱：

選取儲存格A3:E:27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、店名、產品、代碼、銷售額。