EXCELのシートにおける複数条件照合

2022年10月9日

シート

式をセットする前のシート

緑色に塗りつぶしたA1:C1にある項目「あ」「い」「う」をオレンジ色に塗りつぶしたH1:I1にある項目「あ」「い」「う」と照合して３つ一致したら、オレンジ色に塗りつぶした項目「NO」「住所」「氏名」を緑色に塗りつぶした項目「NO」「住所」「氏名」にセットすること、すなわちG2→D2、K2→E2、L2→F2をしたいというものです。

式をセットした後のシート

セル D2:F3 に式をセットしました。

セットした式

配列数式を使うのが簡単だと思います。
表の前提条件
オレンジ色の表はマスターテーブルとして扱います。ですから「NO」は ID であり、またポジションに使いたいたいので数値で重複がないように行番号で設定します。そして「あ」「い」「う」の組み合わせに重複データはないというふうにします。

セル D2
{=SUM(IF((H$2:H$6=A2)＊(I$2:I$6=B2)＊(J$2:J$6=C2),G$2:G$6))}

これで「NO」を求めます。
合計を求めたいという訳でもないのに SUM 使っているのは、オレンジ色の範囲はマスターテーブルであり、「あ」「い」「う」の組み合わせに重複データはないことと、セル C3、J3、J5 は空ではなくスペースがあることの前提があり、1つしかマッチしないので SUM で構わないということです。SUM のほかに1つに集約できる関数があればそれを使ってもいいかもしれません。
「NO」すなわち行番号が求まりました。

セルE2
{=INDEX(K$2:K$6,SUM(IF((H$2:H$6=A2)＊(I$2:I$6=B2)＊(J$2:J$6=C2),G$2:G$6)),1)}
または
=INDEX(K$2:K$6,D2,1)

セルF2
{=INDEX(L$2:L$6,SUM(IF((H$2:H$6=A2)＊(I$2:I$6=B2)＊(J$2:J$6=C2),G$2:G$6)),1)}
または
=INDEX(L$2:L$6,D2,1)

配列数式

配列数式の入力は決りがあります。

例としてセル A1 に {=A1} と入力してもセル A1 の値・表示は {=A1} です。文字列として認識されています。

ではどうするのかというと。数式バーには
=INDEX(K$2:K$6,SUM(IF((H$2:H$6=A2)＊(I$2:I$6=B2)＊(J$2:J$6=C2),G$2:G$6)),1)
と入力します。

「Enter」キーだけではなく「Shift」「Ctrl」を押したままで「Enter」キーを押します。「SCE」とも言われています。こうすることにって式としてセルにセットされます。

式の複写

コピーする範囲をマウスをドラッグして選択します。この場合 D2:F2 を選択しています。

選択状態になったらキーボードの「Ctrl」キーを押したままで「C」キーを押します。すると選択した範囲が緑色の点線で囲まれます。そしてクリップボードにデータがコピーされ貼り付けられるのを待っています。

貼り付ける場所を選択します。この場合 D3 をクリックして選択しています。複数行選択したい場合は例えば3行目から6行目にコピーしたいのであればマウスで D3 から D6 までドラッグすればよいです。

選択した範囲の中で右クリックすると「メニューの検索」が表示されます。その中で「貼り付けのオプション」の「Fx」のアイコンをクリックします。

数式が張り付き値が表示されました。

名前定義を使う

マウスをドラッグして G2:G6 を選択状態にします。
EXCEL本体のメニューの中ほどにある「数式」タブをクリックして表示されたメニューの中に「定義された名前」があります。その中にある「名前の定義」をクリックします。

「新しい名前」を作成するフォームが開きます。よろしければ「OK」をクリックします。これで範囲「Sheet1!$G$2:$G$6」を名前「NO」とする対照表が作成されました。
同様に
範囲「Sheet1!$H$2:$H$6」を名前「あ」
範囲「Sheet1!$I$2:$I$6」を名前「い」
範囲「Sheet1!$J$2:$J$6」を名前「う」
範囲「Sheet1!$K$2:$K$6」を名前「住所」
範囲「Sheet1!$L$2:$L$6」を名前「氏名」
を作成します。