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

Excel-資料清單轉換(OFFSET,INDIRECT,ROW,陣列公式)

【公式設計與解析】

首先,選取儲存格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欄內容(日期)。


或許,你的資料清單長成下圖這樣:

Excel-資料清單轉換(OFFSET,INDIRECT,ROW,陣列公式)

【公式設計與解析】

首先,選取儲存格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。

公式原理同上。

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()