この記事で分かること
本記事は以下の記事のExcel版です。同じ課題でもDAXとは違うアプローチが必要になります。
「同じ意味のマスタが複数あり、しかも空白だらけ、レポート作成も分析も出来ない」そんな状況に悩んだことはありませんか？
例えば本社・拠点・担当が各々3つの同じマスタを保有する状況です。マスタ毎にデータ取得方法が異なるため、データの信頼性が異なります。
本記事は、Power BI（DAX）のSWITCH関数で解決していたマスタ統合ロジックを、Excelにそのまま当てはめて何時間もハマった経験から生まれました。
最初は当然のようにSWITCH関数を使おうとしました。
しかし、Excelでは条件が1つしか設定できないことに気づき、TRUE / FALSE を無理やりネストしました。
そして出来上がったのは複雑な数式でした。
「もっと良い方法はないのか？」
調べなおすと、IFS関数こそが最適解だと気づきました。
本記事では、DAX版でSWITCHを使っていたマスタ統合と空白補完のロジックを、Excelでどう再設計するかを解説します。
単なる関数テクニックではなく、“DAXと同じ感覚でExcelを使ってしまう人”への警鐘を鳴らす意味も含まれています。
空白補完が必要になる実務シーン
IT 機器管理マスタを例にします。
同じマスタを 本社 IT 部門・各拠点・現場の IT 担当者 がそれぞれ管理しているケースです。
- 本社マスタ
基幹人事システムと連携しており、所属部門や役職、メールアドレスなどの信頼度は高い です。
一方で、承認ルートが長く、現場から遠いため、新規機器や用途の反映が遅れ、空白が多くなりがち です。 - 拠点マスタ
本社と現場の中間的な立ち位置で、情報の鮮度と正確性が混在します。 - 担当者マスタ
基幹システムにはアクセスできないため、人事情報は古くなりやすいです。
しかし早い現場対応が求められるため、空白のままでは困り、手入力で用途や追加情報を即時に埋めています。
本来は一元管理すべきですが、現実には部分最適なマスタ運用が続いている。
この状態では空白値が分析の邪魔になる のです。
Excel IFS関数で解決したい課題
状況整理
- 同じ ID を持つマスタが 3 種類存在します
（本社・拠点・担当） - 各マスタには「機器ID、部門、所有者、機器用途」があります
- 項目ごとに、信頼できるマスタが異なる のがポイントです
項目ごとの信頼度（高⇒低）は以下の通りです。
- 部門：本社 ⇒ 拠点 ⇒ 担当
- 所有者：本社 ⇒ 拠点 ⇒ 担当
- 用途：担当 ⇒ 拠点 ⇒ 本社
<イメージ：ZD01_本社作成マスタ＞
<イメージ：ZD02_拠点作成マスタ＞
<イメージ：ZD03_担当作成マスタ＞
やりたいこと
- 3 つのマスタを組み合わせて、空白のない統一マスタ を作りたい
- 同じ ID・同じ項目に複数の値がある場合、信頼度が最も高い値を優先 します
Excel IFS関数による答え
新しいシートに項目IDとヘッダ項目を追加し、B2列に以下の式を入力します。
=IFS(
ZD01_本社作成マスタ!B2<>””,ZD01_本社作成マスタ!B2,
ZD02_拠点作成マスタ!B2<>””,ZD02_拠点作成マスタ!B2,
ZD03_担当作成マスタ!B2<>””,ZD03_担当作成マスタ!B2,
TRUE,””
)
C2列も同様に入力し、D2列（用途）は以下の通り優先順を変更します。
=IFS(
ZD03_担当作成マスタ!D2<>””,ZD03_担当作成マスタ!D2,
ZD02_拠点作成マスタ!D2<>””,ZD02_拠点作成マスタ!D2,
ZD01_本社作成マスタ!D2<>””,ZD01_本社作成マスタ!D2,
TRUE,””
)
「項目ごとに優先順位を変えられる柔軟性」 がIFS関数の最大の強みです。
これで、空白のない統一マスタ（以下）が完成します。
Excel IFS関数の答えの解説
IFS関数の基本構文
- IFS(条件1,結果1,条件2,結果2,…,TRUE,既定値)
- 左の条件式から順に評価し、最初に真になった値を返す
今回の式のポイント
- <>”“ ：空白でないかを判定
- TRUE,”” ：すべて空白なら空文字(最後の式)
- 条件式の記載順＝信頼度が高い順
ExcelとDAXのSWITCH関数の違い
ExcelのSWITCH構文：一致判定のみ
=SWITCH(評価値,値1,結果1,値2,結果2,既定値)
DAXのSWITCH構文： 条件分岐が可能
＝SWITCH(TRUE(),条件1,結果1,条件2,結果2,既定値)
両者は似て非なるものです。ExcelではDAX>SWITCHに類似したIFS関数が最適です。
実務データに当てはめた考察
- ID1の部門、所有者
担当マスタの人事情報が古いため、本社または拠点の値を採用します。 - ID2の用途変更
用途変更を知っているのは担当だけなので、担当の値を採用します。 - ID3の新規用途
担当しか把握していないため、担当マスタで空白補充します。 - ID4の部門、所有者、用途（新規に追加した機器）
担当しか把握していないため、担当マスタで空白補充します。
まとめ
・複数マスタが存在する場面では、空白補完と信頼度の整理 が欠かせません
・IFS関数 を使うことで、優先順位を反映したマスタ統合 が可能になります
・項目ごとに信頼度評価順を変えられる点は、実務データと非常に相性が良いです
・SWITCH関数はExcelとPowerBIでは構文が異なることを認識しましょう。
Excel でのマスタ統合や分析精度向上に、ぜひ役立ててください。（終わり）
コメント