查找和引用的Excel函數

在使用Excel時很多情況下，我們需要把個數不定的工作表數據匯總在一張工作表上，以便進行動態的跟蹤分析：或者是把幾個相關聯的Excel工作表數據匯總在一起，此時，我們就需要使用有關查找和引用的Excel函數。

一般情況下，每個月的數據保存在每張工作表中，而且隨著時間的推移，工作表也逐步增加。是否可以制作一個動態的匯總表格，隨著工作表數目的增加或減少，把這些工作表數據顯示在一個匯總工作表上呢？

圖1所示是截止到某月的各個月份的利潤表，現在要求把這些月份工作表數據匯總到一張工作表上，以便于進一步分析利潤表各個項目的變化趨勢。

圖1

各個月份利潤表會隨著時間的推移而增加。例如，目前是7個月份的數據，那么“匯總表”工作表中就顯示7個月的數據匯總；如果又增加了8月和9月份的數據，那么“匯總表”工作表上就顯示9個月的數據匯總。

對于這樣的多工作表匯總（實質上就是跨工作表數據查詢）問題。使用INDIRECT函數是最方便的?？紤]到“匯總表”工作表的A列結構與每個分表的結構完全一樣。并且每個工作表的名字分別是“01月”、“02月”、“03月”等，而“匯總表”工作表第一行的標題文字也是“01月”、。02月”、“03月”等。這樣就可以充分利用標題文字和工作表名稱來創建高效查詢公式了。

激活工作表“匯總表”。在單元格B2中輸入公式：

=INDIRECT（B$1＆"!B"&ROW（））

將其向右復制到單元格M2.然后選擇單元格區域B2：M2.將其向下復制到第17行。就得到了各個工作表的匯總數據，如圖2所示。

圖2

在這個公式中，字符串“B$1＆"!B'&ROW（）”構建了對某個工作表單元格的引用。例如。對于單元格B2.該字符串是“01月1B2".使用INDIRECT函數將這個字符串轉換為真正的單元格地址引用。即可得到工作表“01月”的B2單元格中的數據。

但是，當工作表不存在時，公式就會得到錯誤的結果。例如目前只有7個月的數據。在“匯總表”工作表中|列以后就是錯誤值“#REF!”。為了不顯示這個錯誤值。使表格整潔美觀，可以使用條件格式來隱藏這些錯誤值。

選擇單元格區域B2:M17.單擊“開始”選項卡，在“樣式”功能組中選擇“條件格式”|“新建規則”命令。打開“新建格式規則”對話框。在“選擇規則類型”列表中選擇“使用公式確定要設置格式的單元格”選項。然后在“編輯規則說明“選項組中輸入計算公式“=ISERROR（B2）”，單擊“格式”按鈕。打開“設置單元格格式”對話框，將字體顏色設置為白色。條件格式設置情況如圖3所示。

圖3

這樣，如果又增加了8月和9月份的數據。那么“匯總表”工作表中就會顯示9個月的數據匯總，如圖4所示。

圖4

查找和引用的Excel函數除此以外，這種使用INDIRECT函數匯總多個工作表數據的方法還有一個優點，就是不受各個Excel工作表先后順序的影響，也就是說，各個Excel工作表的先后顧序是可以任意調整的。