
哈嘍,大家好呀!今天我們的話題是交叉查詢。
什麼是交叉查詢呢?我們先來看一個例子:
在表格中,我們安排了50 名同學組成了一個方陣,這個方陣一共有 5 列縱隊,10 排橫隊。
現在,只要教官喊出排數和列數,相應的同學就要高聲答「到」。
比如教官喊道:「第4 排第3 列」!
「到!」施美麗同學大聲喊道。
這就是一個典型的交叉查詢問題。
那麼,如何在表格中根據行列標題快速查詢出結果呢?
通常情況下,大家的第一反應是使用函數嵌套公式來完成。
常用的公式組合有以下四種:難道就沒有簡單一點的方法嗎?
誒嘿,還真有!
只需要兩步,不需要復雜的函數公式,一個空格就能搞定。
❶ vlookup+match
公式如下:
=VLOOKUP(I3,A2:F11,MATCH(I2,A1:F1,0))
❷ index+match
公式如下:
=INDEX(B2:F11,MATCH(I3,A2:A11,0),MATCH(I2,B1:F1,0))
❸ offset+match
公式如下:
=OFFSET(A1,MATCH(I3,A2:A11,0),MATCH(I2,B1:F1,0),1,1)
❹ indirect+match
公式如下:
=INDIRECT("R"&MATCH(I3,A1:A11,0)&"C"&MATCH(I2,A1:F1,0),FALSE)
是不是覺得頭很大?這些公式一個比一個長,一個比一個複雜!
01、批量命名
首先給方陣的每排每列都取一個名字,常規的命名方法是選中對應的行或列,然後在名稱框輸入一個名字,如下圖:
但是,這麼多的行和列,逐一設置太麻煩了,我們用更簡單的方法:
❶ 選中整個表格區域(包括行列標題)。
❷ 點擊公式選項卡中的「根據所選內容創建」按鈕。
❸ 確認首行和最左列複選框打勾,點擊確定。
這樣,我們就完成了批量命名。
在公式選項卡的【名稱管理器】中,我們可以查看這些名字和對應的引用區域。
02、編寫公式
太神奇了!
相信不用我多說,你也能發現,這個公式究竟有多麼簡單。
輸入等於號,再輸入對應的行和列的名稱,兩個名稱之間敲一個空格,公式就寫好了。
注意:在函數公式中,和冒號一樣,空格也是一種引用符號。與冒號不同的是,冒號引用的是兩個單元格之間的區域;而空格,引用的是兩個區域相互重合的部分。
好了,到這裡,我們已經基本上完成了交叉查詢的任務了。
不過還有一點缺憾:現在的行列名,都是手工輸入的,離我們最開始展示的效果,還差一點點。
我們再嘗試一下,使用單元格引用來輸入這個公式:
輸入等於號,點擊上方的列名稱單元格,敲一下空格,再點擊行名稱單元格,按下回車鍵。
很遺憾,我們翻車了!
為什麼手動輸入行列名稱可以成功,直接引用單元格內容卻失敗了呢?
因為手寫輸入的時候,公式會將這些名稱識別為我們命名的區域;而直接引用單元格,公式會把這些名稱識別為文本。
所以,這裡我們還缺少一個關鍵要素,它是一個函數,你能猜出來是什麼函數嗎?
不賣關子了!我們缺少的,正是 indirect 函數。
indirect 函數可以將文本轉換為一個指定的引用。
用人話解釋,就是indirect 函數可以將文本轉換為一個地址(前提是地址有效),因為我們一開始就對每行和每列都進行了命名,所以這些地址都是有效的,我們只需要用indirect 函數來引用這些地址名稱,就可以發揮他們真正的效用了。
所以,這個公式我們還要稍加修改。
非常簡單,只需要在兩個單元格前面,分別加上indirect 函數,就可以了。
怎麼樣,你學會了嗎?
03、寫在最後
我們最後再把所有5 種交叉查詢的方法都列示出來:
❶ vlookup+match
公式如下:
=VLOOKUP(I3,A2:F11,MATCH(I2,A1:F1,0))
❷ index+match
公式如下:
=INDEX(B2:F11,MATCH(I3,A2:A11,0),MATCH(I2,B1:F1,0))
❸ offset+match
公式如下:
=OFFSET(A1,MATCH(I3,A2:A11,0),MATCH(I2,B1:F1,0),1,1)
❹ indirect+match
公式如下:
=INDIRECT("R"&MATCH(I3,A1:A11,0)&"C"&MATCH(I2,A1:F1,0),FALSE)
❺ 批量命名+ indirect
公式如下:
=INDIRECT(I2) INDIRECT(I3)
要用哪一種,就由你來決定咯!
本文來自微信公眾號:秋葉Excel (ID:excel100),作者:張開元 編輯:竺蘭
#我按了一下空格竟發現了交叉查詢最簡單的一種方法
發佈留言