也許在excel中你認為vlookup和lookup查找最牛、Sumifs求和最牛, 但它們都可以用其他函數所替換, 而最有一個函數卻無法用其他函數替代, 高手寫的公式中經常會有它的身影。
Indirect函數
一、Indirect函數簡介
1、基本用法:可以把一個字元運算式或名稱轉換為位址引用。 在excel也只有它可以實現這樣的功能。
【例1】儲存格中A1值是100
= A1 返回值100
= "A1" 返回的是字串 A1
= indirect("A1") 則返回100
= Indirect("A" & 1) 返回 100
2、用途:在引用位址中插入變數並用&連接起來, 然後用indirect函數把字串轉換成引用。 可以做到動態引用。
二、indriect函數應用
1、生成二級下拉式功能表
步驟1:設置資料來源區域。 就是把手機名稱和型號整理成如下圖格式備用, 存放的位置隨意。
步驟2:批量定義名稱。 選取手機名稱和型號區域後, 打開指定名稱視窗(excel2003版裡, 插入功能表 - 定義 - 指定, 07和10版 公式選項卡 - 定義的名稱組 - 根據所選內容創建), 選取視窗上的“首行”核取方塊。 如下圖所示。
步驟3:設置數據有效性。 選取型號列, 打開資料有效性視窗(打開方法見昨天的教程), 在來源中輸入=indirect(D5)
進行如下設置後, 二級聯動功能表設置完成。
2、多表合併
如下圖所示, 要求把每天的銷售明細合併到匯總表中。
日報
日報合併
公式:=INDIRECT(B$1&"!B"&ROW())
公式說明:
B$1&"!B"&ROW(), 根據ROW函數產生的行號, 生成儲存格位址。 例 公式在第2行時, ROW()結果是2, B$1&"!B"&ROW()的結果就是:
1!B2
3、多表查找
【例】工資表範本中, 每個部門一個表。
在查詢表中, 要求根據提供的姓名, 從銷售~綜合5個工作表中查詢該員工的基本工資。
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售";"服務";"人事";"綜合";"財務"}&"!a:a"),A2),{"銷售";"服務";"人事";"綜合";"財務"})&"!a:g"),7,0)
本例詳見:http://mp.weixin.qq.com/s?__biz=MjM5NDYyNzAzNQ==&mid=400305873&idx=1&sn=cb4b6c2576999016abebc3d184a1cde8#wechat_redirect
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售";"服務";"人事";"綜合";"財務"}&"!a:a"),A2),{"銷售";"服務";"人事";"綜合";"財務"})&"!a:g"),7,0)
本例詳見:http://mp.weixin.qq.com/s?__biz=MjM5NDYyNzAzNQ==&mid=400305873&idx=1&sn=cb4b6c2576999016abebc3d184a1cde8#wechat_redirect