2015年9月29日 星期二

Excel-查表應用(OFFSET,MATCH,COUNTIF,ROW)

網友問到在 Excel 中有一個如下圖(上)的一個基本資料表,如何藉由輸入發票號碼,來篩選出同一發票的所有客戶資料?(參考下圖(下))

【公式設計與解析】
1. 查詢客戶編號
儲存格C19:=OFFSET(B1,MATCH(A20,A2:A17,0),,,)
MATCH(A20,A2:A17,0):求儲存格A20的發票號碼在儲存格A2:A17範圍中的第幾列。
OFFSET(B1,MATCH(A20,A2:A17,0),,,):將上式傳回的第幾列,將值代入 OFFSET 函數取得儲存格的內容。
2. 查詢客戶名稱
儲存格E19:=OFFSET(C1,MATCH(A20,A2:A17,0),,,)
做法同「1. 查詢客戶編號」公式。
3. 查詢:產品編號、產品名稱、單價、數量
(1) MATCH($A$20,$A$2:$A$17,0)
做法同「1. 查詢客戶編號」公式。
(2) OFFSET(D$1,MATCH($A$20,$A$2:$A$17,0)+ROW(1:1)-1,,,)
MATCH($A$20,$A$2:$A$17,0)+ROW(1:1):用於向下複製公式時,每增加一個列號,其傳回值也會相對加 1。
因為相同發票號已排列在一起,所以將上式代入 OFFSET 函數取得對應的儲存格內容,當向下複製公式時,即可依序取得相同的發票的不同記錄之內容。
儲存格B21:=IF(ROW(1:1)<=COUNTIF($A$2:$A$17,$A$20), 第(2)式 ,"")
COUNTIF($A$2:$A$17,$A$20):計算同一個發票號碼共有幾筆記錄。
透過 IF 函數,將超過相同發票的記錄數者,予以顯示空白。
最後,複製儲存格B21,貼至儲存格B21:E27。

【補充說明】
完整公式/儲存格B21:=IF(ROW(1:1)<=COUNTIF($A$2:$A$17,$A$20),
OFFSET(D$1,MATCH($A$20,$A$2:$A$17,0)+ROW(1:1)-1,,,),"")

沒有留言:

張貼留言

檢視其他文章

好康東東