2015/11/18 公開

重複する検索値はCOUNTIF関数で個別化しよう!EXCEL VLOOKUP術[Vol.4]後編

Pocket

Vlookup関数は、参照表内に同じ検索値が複数ある場合に、最初に一致した検索値しか引用できないという弱点があります。EXCEL VLOOKUP術[Vol.4]の前編では、COUNTIF関数を使えばこの問題を解決できること、そしてCOUNTIF関数の定義を説明しました。今回は、いよいよVlookupCOUNTIFを組み合わせる具体的な手順を紹介していきたいと思います。

COUNTIFを使って、重複するデータを唯一無二に加工

以下の表で、「取引先名」に従って右の表の「担当者名」を埋めていきたい場合、F列にVlookup関数を入力しても上手くいきません。Vlookup関数は、同じ値の検索値が複数ある場合に、検索範囲の一番上のデータしか取り出すことができないからです。
1

こうした場合、重複したデータのあるA列、E列のデータをそれぞれ唯一無二のものに加工して、重複が無い状態にすれば問題は解決されます。

さっそく手順スタート!
まずはA列、E列の「取引先名」の横にそれぞれ固有の番号を振り、重複するデータを固有化していきます。2つの表の左側にそれぞれ2つ作業用の列を加えましょう。
4

次に、A2のセルに=COUNTIF($C$2:C2,C2)という数式を入力し、A7までコピーします。
A列のセルに表示される値は、C列の取引先名がC列で何番目に出てきたかという数を示しています。これによって、C列の取引先名に固有の番号を付けたことになります。
5

同様に、G2のセルに=COUNTIF($I$2:I2,I2)という式を入力し、7行目までコピーします。
G列のセルに表示される値は、I列の取引先名がI列で何番目に出てきたかという数を示しています。これによって、I列の取引先名に個別の番号を付けたことになります。
6

さて、ここまでの作業で、C列、I列の取引先名に個別の番号をふることができました。
次は、いよいよ「Key」という作業列のセルにAND関数を使ってそれぞれ「No.+取引先名」を入力し、重複の無いデータを作ります。

まずは、B列から。
B2のセルに「=A2&C2」というAND関数を入力し、7行目までコピーします。これによって、A列の「No.」とC列の「取引先名」を組み合わせた値がB列に表示されるようになりました。
7

同様にH2のセルに、「=G2&I2」というAND関数を入力し、7行目までコピーします。これによって、G列の「No.」とI列の「取引先名」を組み合わせた値がH列に表示されるようになりました。
8

この上で、J2のセルに「=VLOOKUP(H2,B2:D7,3,0)」という式を入力します。
9

この式の意味するところは、「H2(検索値)と一致する値を、B2からD7までのセルの検索範囲で探し出し、検索範囲の3行目の値を返す」ということ。この式を一番下までコピーすれば、J列にそれぞれ個別の「担当者名」が表示されるようになります。
10

COUNTIF関数によって検索値である「取引先名」に個別の番号(登場回数)を付け、検索値と合体することで、唯一無二の新たな検索値を作り、その結果Vlookup関数が有効に働くようになりました。

関数は組み合わせ次第で可能性が広がる

単一の関数だけ覚えても、実際の業務にはあまり役に立たないということは往々にしてあるでしょう。しかし、それぞれの関数の特性を応用し、組み合わせれば様々な作業が可能になります。慣れてくれば、目的の作業を実現するために、どの関数をつかえば良いかという道筋を考えることが楽しくなるかも。

次回は、重複データの処理について、より実践的な実例を用いて説明します。前回学んだMATCH関数も活用するので、是非お楽しみに。

Excelでの人材管理・人事評価業務限界を感じていませんか?
会社の人材管理方法や人事評価業務について、以下のようなお悩みをお持ちではないですか?
  • 社員の顔と名前が一致しない
  • 人事評価の取りまとめがもう限界
  • 人材情報がバラバラで見たい情報が見つからない
  • システム導入にコストや時間をかけられない
紙やExcelでの人事業務の管理には限界があり、これらをひとまとめに解決する人材管理(人事評価)ツールに近年注目が集まっています。
顔写真が並ぶ、クラウド人材管理ツール 「カオナビ」では、7日間の無料トライアルを実施中。 シンプルで直感的なインターフェースをぜひ体感してみてください。

マネたまご マネたまをフォローすれば最新記事をお届けします!
運営会社 | Copyright © kaonavi, inc. All Rights Reserved.