在數(shù)據(jù)表中如何進行一對多查詢

曾憲豐

【摘 要】 在數(shù)據(jù)處理當中，我們常常要對數(shù)據(jù)進行一對多查詢，并將結果提取出來，使用INDEX索引函數(shù)和SMALL排序函數(shù)正好能實現(xiàn)這個功能。

【關鍵詞】 數(shù)據(jù)查詢 ?IF函數(shù) ?INDEX函數(shù) ?SMALL函數(shù)

下面我就講講在數(shù)據(jù)表中進行一對多查詢的典型用法，可以說這個公式相當于一個萬能公式。

一、判斷函數(shù)IF的應用

這里我引用一個簡單的數(shù)據(jù)源作為例子，復雜的數(shù)據(jù)只要變換公式里相應的參數(shù)就可以了。

數(shù)據(jù)源如下：

A～E列是部分教師的信息，要根據(jù)G2單元格指定的職稱，提取出所有職稱為“高級教師”的教師姓名。

H2單元格輸入以下公式，按住Shift+Ctrl不放，按回車，再將公式向下拖動到出現(xiàn)空白單元格為止：

=INDEX（A：A，SMALL（IF（D$2：D$14=G$2，ROW（$2：$14），4^8），ROW（A1）））&""

二、排序SMALL函數(shù)的應用

SMALL函數(shù)的作用是返回一組數(shù)值中的第n個最小值，比如公式SMALL（D：D，2），就是返回D列中的第二個最小值。

在這個例子中，SMALL（IF（D$2：D$14=G$2，ROW（$2：$14），4^8），ROW（A1）），SMALL函數(shù)用IF函數(shù)的計算結果作為第一參數(shù)，要在這個內(nèi)存數(shù)組中提取第n個最小值，這里的n由誰來指定呢？就是公式最后部分的ROW（A1）。

ROW（A1）的作用是返回A1單元格的行號，結果是1。當公式向下復制時，參數(shù)會依次變成ROW（A2）、ROW（A3）、……，也就是得到從1開始、依次遞增的序號1、2、3……n。最終的目的是給SMALL函數(shù)一個動態(tài)的參數(shù)，依次從內(nèi)存數(shù)組中提取出第1至n個最小值的序列。

在這里，SAMLL函數(shù)是先取出內(nèi)存數(shù)組中的第1個最小值，也就是2。

這個2的作用是什么呢？現(xiàn)在該輪到INDEX函數(shù)上場了。

三、索引INDEX函數(shù)的應用

INDEX函數(shù)的作用是根據(jù)指定的位置信息，從數(shù)據(jù)區(qū)域返回對應位置的內(nèi)容。剛剛的2就是位置信息，INDEX函數(shù)從D列中返回第二個單元格的內(nèi)容，結果就是第一個符合條件的姓名“劉昌”。

公式向下復制到H3單元格，ROW（A1）變成了ROW（A2），返回A2的行號2，SMALL函數(shù)再從內(nèi)存數(shù)組中提取第2個最小值，是8。這時INDEX函數(shù)就返回D列中的第8個單元格中的內(nèi)容“陳青”。

以此類推，如果所有符合條件的行號都提取完了，公式還向下復制，這個時候SMALL函數(shù)返回的結果就是4^8，也就是65536，INDEX函數(shù)就返回D列第65536個單元格中的內(nèi)容。

四、拓展

這個公式還能用在多對多查詢，通常分為兩種情況：一是要提取出同時符合多個條件的所有記錄;二是要提取出多個條件符合其一的所有記錄。

比如，要提取出上面例子中性別為“女”，職稱為“一級教師”的所有教師姓名。這里的判斷條件就變成性別和職稱兩個條件了，兩個條件要同時符合，公式該怎么用呢？

I2單元格輸入以下公式，按住Shift+ctrl不放，按回車，再將公式向下拖動到出現(xiàn)空白單元格為止：

=INDEX（A：A，SMALL（IF（（B$2：B$14=G$2）×（D$2：D$14=H$2），ROW（$2：$14），4^8），ROW（A1）））&""

公式中大部分和前面的幾乎是一樣的，有所不同的地方就是if判斷中的條件，這里用的是（B$2：B$14=G$2）×（D$2：D$14=H$2），也就是把多個條件分別寫到括號內(nèi)，再用乘號把多個條件對應相乘。只有這幾組條件同時符合了，對應相乘后的結果才是1，否則相乘結果是0。

在IF函數(shù)的第一參數(shù)中，0的作用相當于邏輯值FALSE，不等于0的數(shù)值則相當于邏輯值TRUE。也就是兩個條件同時符合了，就返回對應的行號，否則返回65536。

至于多對多查詢的第二種情況是多個條件符合其一。

總結

在數(shù)據(jù)處理當中，要善于利用函數(shù)和公式的組合，完成對數(shù)據(jù)更復雜的處理，呈現(xiàn)更直觀的效果。我們也不用害怕復雜的公式，通過“剝洋蔥”式的分析，我們就能對公式的運行了如指掌;再通過實例操作，就能熟練掌握高階公式的應用了。