Windows8 & Office2013 Excel2013

Print Friendly, PDF & Email

3つの条件を満たす値を求めるには

投稿日:2017年1月7日 更新日:

Excelでリストの中から、3つの条件を満たす値を求める方法です。
先日、職場で ちょっと難しい課題にぶつかってしまいました。
リストの中から3つの条件を満たす値を求めるには?
VLOOKUP関数や、MATCH関数とINDEX関数を組み合わせて・・・といろいろ頭を悩ませました。リストは毎回可変するし、縦と横だけならいいのですが、A列とB列で一致するものを求めて行を確定しなければなりません。一行目の項目も毎回変わるので求める列をMATCH関数検索するしかありません。

SUMPRODUCT関数・INDEX関数・MATCH関数を使って求める

こういう時は、SUMPRODUCT関数を使うと便利です。もちろん、INDEX関数とMATCH関数も使います。
表

セルB16には、下のように数式を入力しました。

=INDEX(A1:G11,SUMPRODUCT((A2:A11=B13)*(B2:B11=B14)*ROW(A2:A11)),MATCH(B15,1:1,0))

INDEX関数の関数の引数ダイアログボックスです。
[配列]にはリストの範囲ですね。
[行番号]はSUMPRODUCT関数とROW関数で求めます。
[列番号]はMATCH関数で求めます。
関数の引数ダイアログボックス

行番号は、SUMPRODUCT((A2:A11=B13)*(B2:B11=B14)*ROW(A2:A11))で求めています。
数式

このSUMPRODUCT関数って面白いです。私は知らなくてビックリしました。
興味がある方は、[SUMPRODUCT関数]で検索して調べてみてください。

MATCH関数とINDEX関数で求める

ふと、思い付いたのですが、A列とB列をくっつけた値をC列に表示すれば、MATCH関数とINDEX関数を組み合わせて求めることもできますね。以下のページで解説しています。
INDEX関数とMATCH関数 / 行と列の交差するセルの値を求める

列を追加した表

求める値は同じになります。
INDEX関数とMATCH関数


【追記】
A列とB列をくっつけた値をC列に表示しなくても、MATCH関数とINDEX関数を組み合わせて求めることができます!下の式で求めることができますね!いろいろ勉強させていただきました。

=INDEX(A2:G11,MATCH(B13&B14,INDEX(A2:A11&B2:B11,),),MATCH(B15,1:1,0))

関数の引数ダイアログボックス

表

データ範囲が可変したり、検索する文字列がリストにない場合など、その時の用途によって使い分けてみてください。エラー表示の対処には、IFERROR関数が便利です。エラーの場合の値を指定する

VLOOKUP関数のエラー回避

Excelでデータを参照する場合、VLOOKUP関数を使います。 下の左の表、セルC2からセルC7には、VLOOKUP関数が入力されていて、 ...

続きを見る

リスト範囲は、名前を付けて置くと便利です。また、リスト範囲が可変する場合は名前定義の参照範囲に数式を入力して範囲が変化しても対応できるようにしておくと便利です。
縦横の範囲が可変する名前定義

参考Officeのバージョンによって操作が異なる場合があります。以下の記事で、お使いのOfficeのバージョンを確認する方法を解説しています。

Office(Word、Excel、PowerPointなど)のバージョンを確認する方法

Microsoft Officeも時を得て、大きく様変わりしてきました。トラブルを解決しようとするとき、あるいは、何かの操作を探すときなど、 ...

続きを見る

解決できないことは【teratail】に質問してみましょう。

アドセンス記事内広告1個

おすすめ記事と広告

こんな記事も読まれています

表を削除して文字列だけ残すには

表は削除したいけど、セル内の文字列は残したいということがあります。 こういう場合は、[表の解除]ボタンを使います。 表の解除 まず、表内でク ...

2つの列の差分を求める(重複しない一意の値を求める)

Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。 たとえば、下のようにA列には分析用データとして別 ...

ピボットテーブルで日付のグループ化ができない

Excelでピボットテーブルを作成して、日付フィールドで[月]ごとのグループ化をしようとしてもできないことがあります。これは、見た目は日付に ...

コマンドまたはアクション'レコードの削除'は無効です

Access2013で、帳票フォームを作成し、レコードを削除できるように[削除]ボタンを作成しました。 ですが、[削除]ボタンをクリックする ...

コンボボックスでレコードの抽出

Accessの分割フォームのデータシートビューには、Excelのフィルターと同じようにフィルタボタンがあり、Excelの操作と同じように様々 ...

-Windows8 & Office2013 Excel2013

Copyright © 2001-2017 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2017 AllRights Reserved Powered by AFFINGER4.