贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

在 Excel 中有時會用到多表格的查詢(如下圖),你可以使用各種查詢的公式,例如:INDEX、VLOOKUP、OFFSET等,本例以3種不同的做法來練習在多個表格中查詢,也要看看如何簡化公式。前提是多個表格的格式要是一致的。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

若要在儲存格H2:J2中使用了3 個條件來搜尋(概念如下圖),可以看成是 3D 的搜尋。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)


【公式設計與解析】

1. 使用 INDEX 函數的參照形式

儲存格K2:

公式:=INDEX(($B$2:$F$4,$B$7:$F$9,$B$12:$F$14,$B$17:$F$19,$B$22:$F$24),
MATCH($I2,$A$2:$A$4,0),MATCH(J$2,$B$1:$F$1,0),MATCH(H2,{"甲班","乙班",
"丙班","丁班","戊班"},0))

INDEX 函數有兩種查表形式:

(1) array 之陣列形式(傳回指定儲存格或儲存格陣列的值)

(2) reference之參照形式(傳回指定儲存格的參照)。

本例使用 reference之參照形式,語法如下:

INDEX(reference, row_num, [column_num], [area_num])

Reference:一個或多個儲存格範圍的參照。(若是非相鄰的選取範圍做為 reference,則必須使用括號括住 reference)

Row_num:參照中要傳回參照的列數。

Column_num:參照中要傳回參照的欄數。

Area_num:在參照中選取範圍以傳回 Row_num 與 Column_num 的交集。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

公式中利用 MATCH 函數來查詢位置,例如:

(1) MATCH($I2,$A$2:$A$4,0)

查詢儲存格I2在儲存格A2:A4中的位置,傳回一個數值(本例傳回3)。

(2) MATCH(J$2,$B$1:$F$1,0)

查詢儲存格J2在儲存格B1:F1中的位置,傳回一個數值(本例傳回2)。

(3) MATCH(H2,{"甲班","乙班","丙班","丁班","戊班"},0)

查詢儲存格H2(丙班)在{"甲班","乙班","丙班","丁班","戊班"}陣列中的位置,傳回一個數值(本例傳回3)。


2. 使用 OFFSET 函數

儲存格K2:

公式:=OFFSET($A$1,MATCH($I2,$A$2:$A$4,0)+5*(MATCH(H2,{"甲班","乙班",
"丙班","丁班","戊班"},0)-1),MATCH(J$2,$B$1:$F$1,0))

(1) MATCH($I2,$A$2:$A$4,0)

查詢儲存格I2在儲存格A2:A4中的位置,傳回一個數值(本例傳回3)。

(2) 5*(MATCH(H2,{"甲班","乙班","丙班","丁班","戊班"},0)-1)

查詢儲存格H2(丙班)在{"甲班","乙班","丙班","丁班","戊班"}陣列中的位置,傳回一個數值(本例傳回3)。其中『5*』的用意是因為每個表格的位置相差 5 列。

(3) MATCH(J$2,$B$1:$F$1,0)

查詢儲存格J2在儲存格B1:F1中的位置,傳回一個數值(本例傳回2)。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)


3. 使用 INDEX+INDIRECT 函數配合定義名稱

先定義儲存格名稱。

(1) 選取儲存格A1:F4,按 Ctrl+F3 鍵,開啟「名稱管理員」對話框。

(2) 按下「新增」按鈕,名稱已自動帶入「甲班」,參照到已自動帶入「$A$1:$F$4」。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

(3) 按下「確定」按鈕,即新增一個名稱定義:甲班。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

(4)重覆步驟(1)至步驟(3),定義名稱:乙班、丙班、丁班、戊班。


儲存格K2:

公式:=INDEX(INDIRECT(H2),MATCH($I2,$A$2:$A$4,0)+1,MATCH
(J$2,$B$1:$F$1,0)+1)

(1) INDIRECT(H2)

利用 INDIRECT 函數將儲存格H2的內容轉換為一個儲存格範圍。(先前已定義名稱)

(2) MATCH($I2,$A$2:$A$4,0)+1

查詢儲存格I2在儲存格A2:A4中的位置,傳回一個數值(本例傳回3)。『+1』是因為定義名稱時是以儲存格A1為起始,多了這個儲存格,所以相對位置要多 1。

(3) MATCH(J$2,$B$1:$F$1,0)+1

查詢儲存格J2在儲存格B1:F1中的位置,傳回一個數值(本例傳回2)。『+1』是因為定義名稱時是以儲存格A1為起始,多了這個儲存格,所以相對位置要多 1。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

文章標籤

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

雖然 Flash 問題多多,但是現在還是有很多網站有在使用Flash功能,如果你使用 Google Chrome 時,無法看到 Flash 的相關內容,就必須要設定允許執行 Flash。

連線至含有 Flash 的網站,然後在 Chrome 網址列的左側,點選驚嘆號,再選取「Flash」,然後勾選要永遠禁止或是永遠允許該網站執行 Flash,或是每次都詢問是否執行。

這個位置還有其他資訊可以在此切換設定,例如:傳送位置資訊、接收通知訊息等。

在Chrome設定瀏覽器可以在網站中執行Flash

文章標籤

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

觀察下圖!圖裡的 Excel 工作表有個資料清單(本例中的儲存格有部分是以亂數產生數值),完全無法辨識儲存格中是否有為含有:公式、註解、設定格式化的條件、常數、資料驗證等。如果想要對這些項目進行操作時,還得先找出各個項目所屬的儲存格才能處理。

Excel-搜尋特殊的目標

還好,Excel 已內建搜尋這些項目的功能,可以用搜尋方式找到:公式、註解、設定格式化的條件、常數、資料驗證等

Excel-搜尋特殊的目標

(1) 搜尋含有「公式」的儲存格(和搜尋含有「常數」的儲存格互為相反)

Excel-搜尋特殊的目標

(2) 搜尋含有「註解」的儲存格

Excel-搜尋特殊的目標

(3) 搜尋含有「設定格式化的條件」的儲存格

Excel-搜尋特殊的目標

(4) 搜尋含有「常數」的儲存格(和搜尋含有「公式」的儲存格互為相反)

Excel-搜尋特殊的目標

(5) 搜尋含有「資料驗證」的儲存格

Excel-搜尋特殊的目標

以上這些都可以使用搜尋「特殊目標」來達到相同結果,而且可以搜尋空格、目前範圍、目前陣列、物件等。

Excel-搜尋特殊的目標

你也有可以來搜尋不同的內容的儲存格。例如:選取儲存格E1:F15,並選取儲存格E3。

Excel-搜尋特殊的目標

當搜尋「欄差異」,會選取以下四個和其他儲存格不同的儲存格。

Excel-搜尋特殊的目標

例如:選取儲存格A14:F15,並選取儲存格A15。

當搜尋「列差異」,會選取以下二個和其他儲存格不同的儲存格。

Excel-搜尋特殊的目標

若選取儲存格F1:F15,再搜尋「從屬參照」,則會找到公式中有參照其他儲存格者。

Excel-搜尋特殊的目標

若選取儲存格F1:F15,再搜尋「前導參照」,則會找有被公式參照的儲存格。

Excel-搜尋特殊的目標

文章標籤

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

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼