2016年10月13日 星期四

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

網友問到的 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。
公式原理同上。

沒有留言:

張貼留言

好康東東