在數據表中如何進行一對多查詢

曾憲豐

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

【關鍵詞】 數據查詢 ?IF函數 ?INDEX函數 ?SMALL函數

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

一、判斷函數IF的應用

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

數據源如下：

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

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

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

二、排序SMALL函數的應用

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

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

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

在這里，SAMLL函數是先取出內存數組中的第1個最小值，也就是2。

這個2的作用是什么呢？現在該輪到INDEX函數上場了。

三、索引INDEX函數的應用

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

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

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

四、拓展

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

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

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

=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），也就是把多個條件分別寫到括號內，再用乘號把多個條件對應相乘。只有這幾組條件同時符合了，對應相乘后的結果才是1，否則相乘結果是0。

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

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

總結

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