學不完．教不停．用不盡

網友問到的 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)}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。

複製儲存格F2，貼至儲存格F2:H22。

(1) INDIRECT(F$1)

利用 INDIRECT 函數將儲存格F1的內容轉換為儲存格位址。例如：儲存格F1(「A服務」)，轉換為儲存格B2:B27。(先前已定義名稱範圍)

(2) IF(INDIRECT(F$1)="V",ROW(日期),999)

在陣列公式中，判斷在儲存格範圍內的儲存格內容是否為「V」，若是，則傳回日期陣列的列號(利用 ROW 函數)，若否，則傳回『999』。(這只是一個很大的數字，只要比儲存格範圍最大值大即可。)

(3) SMALL(IF(INDIRECT(F$1)="V",ROW(日期),999),ROW(1:1))

利用第(2)式所傳回的日期陣列，利用 SMALL 函數由小到大，依序取出日期對應的列號。(當公式向下複製時，會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。)

(4) OFFSET($A$1,第(3)式-1,0)

根據第(3)式取得的日期列號，代入 OFFSET 函數，即可找出對應的A欄內容(日期)。

或許，你的資料清單長成下圖這樣：

【公式設計與解析】

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

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

{=OFFSET($A$1,SMALL(IF(服務=D$1,ROW(日期),999),ROW(1:1))-1,0)}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。

複製儲存格D2，貼至儲存格D2:F22。

公式原理同上。