學不完．教不停．用不盡

網友問到在 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,,,),"")