用兩個條件匹配資料,這裡有8種方法

2022-06-23 16:23:43 字數 2636 閱讀 8076

來自:

工作中,我們常常遇到按照兩個條件進行資料匹配(或查詢)的情況。

▲ 表1:資料來源

例如,根據表1資料來源的日期和科目來匹配費用到表2,我們權且把這種匹配稱為二維到一維匹配。

▲ 表2:目標表

又或者反過來,也有可能根據一維表(下表3):

▲ 表3:

資料來源匹配費用資料到表4:

▲ 表4:目標表

我們也把這種匹配先稱之為一維到二維。

今天,我來介紹

一、二維**資料匹配的幾種方法。

01

二維到一維

方法1:index+match

首先,用match函式分別求出資料行的日期和科目在資料來源日期列(b3:b9)和科目行(c2:p2)的位置,然後在c3:p9這個區域利用index索引函式,找到指定行和列對應的數值。

方法2:vlookup+match

先用match找到科目所在列,再用vlookup根據日期來查詢(注意,第3個引數match的結果要加1,因為第1列是從b列算起的)。

方法3:hlookup+match

方法3與方法2類似,但是先用match找到日期所在行,再用hlookup根據科目來匹配費用。

方法4:offset+match

offset函式不僅可以返回一個區域,也可以返回一個單元格。所以,當offset最後兩個引數為1時,即可返回特定位置的資料。

offset函式可以根據偏移量返回資料,它有5個引數:

引數1:起始位置

引數2:向下移動x行

引數3:向右移動y列

引數4和引數5:從新的位置開始返回一個m行n列的區域

▲ 資料來源:b2:d51

反過來,從一維到二維,又有哪些方法呢?

方法1:sumproduct

公式:sumproduct(($b$3:$b$51=$b59)*($c$3:$c$51=c$58)*$d$3:$d$51)

利用sumproduct進行條件判斷,判斷資料來源日期和科目欄位中每個單元格是不是要查詢的日期和科目,對滿足條件的資料進行先乘積再求和,最後就得到費用。

由於存在查詢不到資料的情況(比如2010年職工薪酬),用sumproduct計算的結果為0,因此可以用if函式做一下處理,使結果為0的顯示為空。

方法2:vlookup+if

公式:,$b$3:$b$51&$c$3:$c$51,$d$3:$d$51),2,)}

利用if構造一個資料來源,用日期&科目作為查詢值進行查詢,這是陣列的用法,公式要加上大括號。

當查詢不到結果的時候,此公式會顯示錯誤值,因此再巢狀iferror進行處理。

方法3:sumifs

公式:sumifs($d$3:$d$51,$b$3:$b$51,$b93,$c$3:$c$51,c$92)

把匹配問題轉化成多條件求和問題(當然只能針對數值的情形,對文字不適用)。

方法4:自定義函式vlookups

公式:vlookupifs(2,$d$3:$d$51,$b$3:$b$51,$b82,$c$3:$c$51,c$81)

本例中,相當於我們根據日期和科目兩個條件去做查詢,不管用sumproduct還是sumifs,都是把匹配問題轉化成了條件求和問題,因此只能對查詢結果是數值的情形適用。如果是文字則不合適。

因此,我用vba編寫了一個自定義函式——vlookupifs,多條件匹配,可以根據多個條件來匹配目標(不管目標是數值還是文字都可適用)。

當然,自定義函式必須新增到載入巨集裡才可以在任何檔案中使用。