SUMPRDUCT関数、INDEX関数、ROW関数を使って複数条件でセルを参照

 こんにちは。伊川(@naonaoke)です。

今回は、VLOOKUP関数で、検索条件が複数のときに、どのように参照するのかを紹介します。

今回のブログは、こんな人にお勧め

・複数の条件でセルを参照できない

・関数を組み合わせて、いろいろなセルを参照したい

VLOOKUP関数の弱点

VLOOKUP関数は、便利な関数なのですが、検索条件が、唯一のキー(値)ではないと反応してくれません。

例えば、こんな状況です。


上の図のような名簿があったとします。

このときに、伊川 直助が会社を無断欠勤した場合に、連絡をとりたいとします。

問題点

伊川 直助が二人いるので、VLOOKUP関数では、参照できません。

VLOOKUP関数を使って解決する方法


上の図のように、唯一の値を作業セルに作成して、VLOOKUP関数を作成するのが、1例となります。

しかし、作業セルがあるというのは、格好悪いです。

その解決方法が、今回紹介する方法です。

スポンサーリンク

今回実現したいこと


このように、部署と名前    で、合致したものを表示したいとします。

そして作業セルは使わないものとします。

この場合は、表示したいのは、電話番号になります。

スポンサーリンク

作成手順

INDEX関数を挿入


Sheet2のセルA3に、【=in】と入力します。

そうすると、INDEX関数をExcelが自動で表示しますので、INDEX関数を選択します。


現時点で、Excelの数式バーは、このようになっています。

表示したい列を選択


Sheet1のC列を選択します。

そして、C列を選択後、【,:カンマ】を入力します。


現時点で、Excelの数式バーは、このようになっています。

SUMPRODUCT関数を挿入


【su】と数式バーに入力すると、Excelが、SUMPRODUCT関数を自動で表示します。

SUMPRDOUCT関数を選択します。


その後に、さらに【(:カッコ開く】を付けたしてください。


現時点で、Excelの数式バーは、このようになっています。

Sheet1のA列を選択


そして、選択後、【=】を入力します。


現時点で、Excelの数式バーは、このようになっています。

Sheet2のセルA1を選択


Sheet2のセルA1を選択後に、【)*(】を付けます。


現時点で、Excelの数式バーは、このようになっています。

Sheet1のB列を選択

さらに条件を追加します。

Sheet1のB列を選択します。

選択後、数式バーで【=】を入力します



現時点で、Excelの数式バーは、このようになっています。

Sheet2のセルA2を選択

Sheet2のセルA2を選択後、【)*】を付けます。



現時点で、Excelの数式バーは、このようになっています。

ROW関数を挿入


【RO】と入力すると、Excelが自動で、ROW関数を表示しますので、ROW関数を選択します。

Sheet1のA列を選択


現時点で、Excelの数式バーは、このようになっています。

そして【)))】として、カッコの数を合わせます。

完成


まとめ

作業セルがなくても複数条件で検索することができました。

今回は、少し特殊なやり方です。

SUMPRODUCT関数が今回の主役でしょうか。

SUMPRODUCT関数
配列の対応する要素間の積をまず計算し、さらにその和を返します。
ただし、SUMPRODUCT関数は数値以外の配列要素は、0であると見なされます。
SUMPRODUCT関数で複数条件の件数をカウントしたり、合計を出すことができます。
SUMPRODUCT関数を用いて、1行/1列おきに計算することもできます。

Excelのバージョンがどんどん、更新されるにつれ、歴史的な役目が終わったような気がしますが、

VLOOKUPに匹敵するくらい重要な関数ですので、みなさんのスキルの一部にしてくさい。

補足
動画で紹介している、1個飛ばしの足し算は、割愛します。
また、動画の中で、N#Aのエラーが出る場面がありますが、SUMPRODUCT関数は、N#Aの関数は表示しません。
私が操作ミスで、数式自体を間違えたと思います。
この場でお詫び申し上げます。
サンプルファイルNo7はです

 

スポンサーリンク

Twitterでフォローしよう

おすすめの記事