前言:如果評誰是Excel最牛X的函式,蘭色肯定推選Indirect函式,因為其他函式大多可以被某他函式替代,而indirect獨特的作用在Excel中獨此一個,無可替代。而且它應用非常廣泛。蘭色這次花費了三天時間,整理出了indirect函式從入門+初級+進階+高階應用的全系列教程,希望對想全面學習indirect函式的同學們有所幫助。今天是入門篇+初級篇。
一、Indirect函式入門篇
1、作用
返回
文字字串所指定的引用
所謂文字字串,是指看似是引用,卻是文字型別的。如:
兩邊
帶雙引號
的引用地址。
=“A1”
=“Sheet!A1”
=“[工資表。xlsx]Wifi資訊圖!$J$3”
返回引用
,是把上面文字型別的轉換為可以返回值的引用。
下面的公式返回的是字元“A1”,並不是A1單元格的值100
=“A1”
而外面套上indiect函式則可以
把
字串A1
轉換為
引用A1
=INDIRECT(“A1”)
2、語法
=indirect(ref_text,[a1])
語法說明:
ref_text:就是前面提到的文字型引用字串
a1:引用的字串樣式。Excel單元格引用有兩種方式,一種是字母+行數,另一種是R1C1樣式(R後數字是行數,C後數字是列數)。當[a1]的值為true、1或省略時表示為A1樣式引用,當值為FALSE或時表示R1C1引用樣式。
[ ] :帶中括號的引數表示它可以有,也可以省略。
【例1】
引用單元格C5的值
=INDIRECT(“c5”,TRUE)
或
=INDIRECT(“c5”,1)
或
=INDIRECT(“c5”)
也可以表示為:
=INDIRECT(“R5C3”,FALSE)
或
=INDIRECT(“R5C3”,)
二、Indirect函式初級篇
引用單元格這麼簡單的公式,被indirect弄的這麼複雜,有什麼用?原因就在於:地址字串中可以插入
變數
1、在
單元格
引用地址中插入變數
【例2】
如下圖所示,根據D2單元格行數,從A列提取數字。
E2公式:
=INDIRECT(“A”&D2)
這兒字母后不再是固定的數字,而是一個可變的值(根據D2的值變數而變化)
如果借用隨機函式,就可以實現隨機出題/抽獎的功能了。
【例3】設定公式從A列隨機抽出一位幸運者。
=INDIRECT(“A”&RANDBETWEEN(1,18))
2、在
工作表名稱
中插入變數
如果把工作表名稱作為變數,那麼就可以利用indirect動態引用不同的工作表的值。前天蘭色分享的Vlookup多表取值是不是也懂了?
【例4】Vlookup從多表取值
B3公式:
=IFERROR(VLOOKUP($A3,INDIRECT(B$2&“!A:B”),2,0),“”)
公式向右複製時,B$2會變為C$2, D$2。。。。。這樣就可以根據第2行的值,從對應名稱的工作表中區域中查詢。
3、在
工作簿名稱
中插入變數
如果把工作簿名稱設定為變數,就可以從不同的Excel檔案中動態取數了。
【例5】如下圖所示,用vlookup根據A列的月份和產品,從本路徑下的1~3月銷售的檔案中查詢銷量。
=VLOOKUP(B2,INDIRECT(“[”&
A2
&“銷量。xlsx]Sheet1!$A:$B”),2,0)
4、把
定義的名稱
作為變數
當字串是定義的名稱時,用inidect函式也可以把字串轉換為定義的名稱。
還記得那個已為大眾所熟悉的二級下拉選單公式嗎?
【例6】根據A列的品牌名稱,在B列生成對應的型號下拉選單。
先定義名稱
然後選取要設定二級下拉選單的區域,設定資料有效性公式
=indirect(a2)
這兒a2的值只是字串,用indirect可以把字串轉換為定義的名稱。
蘭色說:如果你以為掌握了今天的內容就學會了indirect函式,就太天真了。本篇只是indirect函式的初級用法,在下一集“進階+高階”篇中,你才會見識這個Excel中最牛函式的強大之處。
工作中最常用的Excel函式公式,全印在一張超大的滑鼠墊上(送40集配套影片),點我檢視詳情