マスタ参照(エクセル)

2023年9月18日

本社のTです

拝啓、中秋の候、日中はまだまだ暑いですが、夜は涼しくなってきました、みなさまは如何お過ごしでしょうか。

串刺し集計、エラー表示の方法に続いて、マスタ又はテーブルの参照方法について紹介します。

マスタ/テーブル参照ができれば、エクセルの便利機能の60%は習得したことになると思います。

では早速ですが、マスタ/テーブル参照でよく使う、関数を以下に羅行します。

  1) SUMIFS関数

  2) VLOOKUP関数

  3) INDEX関数とMATCH関数の組み合わせ

  4) XLOOKUP関数 (Excel 2019以前は使用不可)

 中でも、SUMIFS関数が簡単ですので、この関数から説明したいと思います。

1. 1.SUMIFSによるマスタデータの抜き出し。
2. 
3. SUMIFS関数の本来の目的は、表の中から特定の条件のデータを抜き出して計を取ることですが、この「特定条件のデータ抜き出し」の機能のみを利用します。

関数は以下の通りです。

SUMIFS(C$3:C$14,$B$3:$B$14,$B18)

　　　　　　①　　　　②　　　      ③

 説明：『マスタの①C列の3行目から14行目までのデータを、当データの②Ｂ列の3行目から14行目までの項目を参照し、抜き出したいデータの③Ｂ列の18行目から20行目の項目を検索値としてC列の18行目から20行目にセットせよ。』という命令です。
当然ですが、マスタにはキー項目(B3～B14の項目)の重複がないため、合計されません。

 メリット   ：1) 比較的軽くPCに負荷がかかりません。
                   2) 関数が簡単ですので、取っつき易い。

 デメリット ：1) 抜き出したい項目が複数列に跨る時、列ごとに関数を組む必要がある。
                        (このケースの場合、列項目が年月でマスタと抜き出しデータの項目の並びが同一のため、関数                           コピーするだけでよい)

                    2) G列の18～20行目を見てください、マスタを参照すると値は「黄色」のはずですが、「0」と                            なっています。つまり、値は必ず数値でなければなりません。

2. 2.VLOOKUPによるマスタデータの抜き出し。
3. 

関数：VLOOKUP($B25,$B$3:$G$14,2,TRUE)

　　　　　　　　　①       ②            ③

説明：『①抜き出したいデータの検索値をB列の25行目から27行目として、マスタの②Ｂ列の3行目からG列の14行目までのマスタデータの②2列目のものを抜き出せ』という命令です。

メリット    ：1) SUMIFSよりは重いですが、PCにはあまり負荷がかかりません。

                   2) SUMIFSと違い、抜き出すデータは文字列でも可能。

デメリット ：1) マスタの行の並びをエクセル方式の昇順で並べ替える必要がある。

                       (マスタのA列の「元順序」が本来の並びです。)

                   2) ③列番号を指定する必要があり、抜き出したデータがマスタ上に複数  列もある時、列番後を                           都度指定する必要があります。めんどくさい!

                       (SMIIFSの場合は抜き出す表とマスタの列の並びが同じであれば関数   コピーするだけでよ                              い。)

3. INDEXとMATCHの組み合わせによる、マスタデータの抜き出し

関数：INDEX($C$3:$G$14,MATCH($B18,$B$3:$B$14,0),MATCH(C$17,$C$2:$G$2,0))                                            　   ①　　　　　　　　　②　　　③　　　　　　　　　 ④　　　　⑤　　　　　　　　　　　　　　　　　　　　　　　　　     

説明：①マスタのC列3行目からG列14行目までのデータを、抜き出したいデータの②列はB列18行目から20行目           までの項目を検査値として、③マスタのB列3行目からB列14行目までの項目を参照。④行はC列の17行目           からG列の17行目までの項目を検査値として、⑤マスタのＣ列2行目からG列2行目までの項目を参照し、            マスタの行列交差点の値を抜き出す。

メリット ：1) 抜き出しを列単位で行うのではなく、面で行えるので、抜き出しデータの列の並びがマスタと一致                      していなくてもよい。

                   (サンプルは抜き出しデータの列の並びをマスタと逆にして抜き出しました。)

デメリット：1) 関数が長くなり、重い。

2) 関数が長いため取っ付きにくい。

4) XLOOKUP関数によるマスタデータの抜き出し

　Excel2019以降のバージョンで使用可能のため、当社では対象外ですが、INDEX & MATCH関数の組み合わせ       を一つの関数にまとめたもので、INDEX & MATCH関数のデメリットを解消しているそうです。新しいPCが来     れば試してみましょう。

5) 長短比較

 敬　具