2023年9月18日
本社のTです
拝啓、中秋の候、日中はまだまだ暑いですが、夜は涼しくなってきました、みなさまは如何お過ごしでしょうか。
串刺し集計、エラー表示の方法に続いて、マスタ又はテーブルの参照方法について紹介します。
マスタ/テーブル参照ができれば、エクセルの便利機能の60%は習得したことになると思います。
では早速ですが、マスタ/テーブル参照でよく使う、関数を以下に羅行します。
1) SUMIFS関数
2) VLOOKUP関数
3) INDEX関数とMATCH関数の組み合わせ
4) XLOOKUP関数 (Excel 2019以前は使用不可)
中でも、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」と なっています。つまり、値は必ず数値でなければなりません。
関数：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) 長短比較
関 数
難易度
抜き出し方法
抜き出しデータ
管理
SUMIFS
簡単
列単位
数値のみ
列項目の変更管理が厄介
VLOOKUP
普通
数値、文字列
同上
INDEX & MATCH
難しい
面単位で便利
列項目の変更管理が容易
敬 具
2023年9月18日
本社のTです
拝啓、中秋の候、日中はまだまだ暑いですが、夜は涼しくなってきました、みなさまは如何お過ごしでしょうか。
串刺し集計、エラー表示の方法に続いて、マスタ又はテーブルの参照方法について紹介します。
マスタ/テーブル参照ができれば、エクセルの便利機能の60%は習得したことになると思います。
では早速ですが、マスタ/テーブル参照でよく使う、関数を以下に羅行します。
1) SUMIFS関数
2) VLOOKUP関数
3) INDEX関数とMATCH関数の組み合わせ
4) XLOOKUP関数 (Excel 2019以前は使用不可)
中でも、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」と なっています。つまり、値は必ず数値でなければなりません。
関数：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) 長短比較
関 数
難易度
抜き出し方法
抜き出しデータ
管理
SUMIFS
簡単
列単位
数値のみ
列項目の変更管理が厄介
VLOOKUP
普通
列単位
数値、文字列
同上
INDEX & MATCH
難しい
面単位で便利
数値、文字列
列項目の変更管理が容易
敬 具