Excel 3MIS (3 Months In Service) 計算
1. 前置き
某Tier1がサプライヤーの品質評価に3MISという指標を使っているらしいです? 一般的なワイブルチャートの高さと傾きを簡易的に評価する指標かも知れません?（間違っているのかも知れません?）
ネット検索によるとSP-3MISは、Supplier Performance 3 Months In Servecの略で、エンドユーザーに渡ってから3ヵ月以内に発生した同一納入月の部品の不良率らしいです?
- Automotive Warranty Management: Key Terms Explained
https://www.automotivequal.com/automotive-warranty-management-basic-terms/
3MISの説明が少しあります。
これをExcelで算出する方法を考えてみました。但し、品質の作り込みは納入時では無く生産時ですので、部品の生産日ベースの不良率にしました。某Tier1の基準は納入月だと思うので、Tier1のSP-3MISとは異なります。
納車後3ヵ月経過していない場合は値が小さくなるので、納車後3ヵ月前未満は計算しないようにしました。でも、部品の生産日ベースだと、仮に納車開始から3ヵ月経過したとしても、そのロットが全て納車済みということはありません。なので納車日ベースにしない限り、必ず初期の3MISは小さく出ることに注意してください。
実際の不良は少ないので、月毎の変動が大きくなります。そこで、3ヵ月移動平均も算出しました。単純に移動平均を計算すると生産終了後の月も計算してしまいます。ピボットテーブルにする場合、小計や総計に3ヵ月移動平均を表示する意味が無いです。これらを表示しない式にしたので、計算要否判定の式が長くなりました。
もっと良い方法があるかも知れません。検証を殆どしていないので、使用される場合は式に間違いがないかを確認し、検証をお願いいたします。
2. 入力
1) 部品生産データ
年月ごとの生産数の表です。
ファイル名 : 生管*.xlsx（*は任意の文字）
| ◢ | A | B | C | D | E | F | 省略 | AY | AZ |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 生管数量 | 24/03 | 24/04 | 24/05 | 28/02 | 28/03 | |||
| 2 | 顧客 | 機種 | 項目 | 24/03 | 24/04 | 24/05 | 28/02 | 28/03 | |
| 3 | A | a | 生産数 | 100 | 100 | ||||
| 4 | B | b | 生産数 | ||||||
| 5 | A | a | 納入数 | 100 | 100 | ||||
| 6 | B | b | 納入数 |
- 1行目のyy/mmは、Excelの日付のシリアル値です。これは、集計に使いません。
- 2行目のyy/mmは、=TEXT(D1, "yy/mm") のテキストです。これを集計に使用します。
- 項目の納入数は、本例では使用しません。納入年月ベースで3MISを集計する場合の予備行です。
- 同じ形のシートであれば、複数のファイルに分かれていてもOKです。但し、1ファイル1シートにしてください。重複が無いようにしてください。普通は1年分が1ファイルになっているかと思います。
- 本例では顧客を使用していません。使用する場合は、顧客ディメンジョンテーブルを作成して、リレーションしてください。
2) 市場サービスデータ
本例では.csvのテキストファイル(UTF-8)とします。Excelで開いた場合のレイアウトを次に示します。
ファイル名 : Service*.csv（*は任意の文字）
| ◢ | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | 市場不良 | ||||||
| 2 | 発生日 | 顧客 | 機種 | 納車日 | 納入日 | 生産日 | 数量 |
| 3 | 2024/4/21 | A | a | 2024/4/21 | 2024/4/11 | 2024/4/1 | 1 |
| 4 | 2024/5/21 | A | a | 2024/5/21 | 2024/5/11 | 2024/5/1 | 1 |
| 5 | 2024/6/21 | A | a | 2024/6/21 | 2024/6/11 | 2024/6/1 | 1 |
| 途中省略 | |||||||
| 117 | 2028/1/21 | A | a | 2025/1/21 | 2025/1/11 | 2025/1/1 | 1 |
| 118 | 2028/2/21 | A | a | 2025/2/21 | 2025/2/11 | 2025/2/1 | 1 |
| 119 | 2028/3/21 | A | a | 2025/3/21 | 2025/3/11 | 2025/3/1 | 1 |
本例では、顧客と機種と生産日を'生管'テーブルと合わせていますが、実際に入手できるデータは異なるので、整形処理をしてください。この時、元データを直接修正するのではなく、Power Queryで整形してください。
- 不要な行を削除してください。
- 部品生産データと同じ顧客列を追加してください。
- 部品生産データと同じ機種列を追加してください。または、列を追加せずに、変換テーブルをディメンジョンテーブルにしてPower Pivotでリレーションで参照しても良いです。
- 製番から生産日の列を追加してください。
- 同じ形の.csvあれば、複数のファイルに分かれていてもOKです。重複が無いようにしてください。普通は1月分が1ファイルになっていると思います。
3) xMISデータ
集計ブックにテーブルで作成します。
'xMIS'テーブル
| xMIS | xMISid |
|---|---|
| 03MIS | 3 |
| 06MIS | 6 |
| 12MIS | 12 |
| 24MIS | 24 |
| 36MIS | 36 |
| 合計 | 999 |
- xMISが集計する月数をxMISidで指定します。
- xMISid=999は、全期間の合計の判断に使用します。メジャーの中で使用します。
- この表を変更する場合は、後述の計算列のDAX式も変更が必要です。
4) 機種データ
集計ブックにテーブルで作成します。部品生産データと市場サービスデータの対応を取るためのディメンジョンテーブルにします。
'機種'テーブル
| 顧客 | 機種 | 上市月数 |
|---|---|---|
| A | a | 0 |
| A | b | 1 |
- 機種の重複が無いようにしてください。（機種をリレーションのキーとします。）
- 顧客と機種は、Power Queryで整形後の部品生産データと市場サービスデータと同じにします。
- 上市月数は、部品の生産日から納車日までの月数を入れてください。これは、評価可能な経過月数に達していない場合に、不良数や不良率を計算しないことにより、ピボットグラフの線を表示しないようにするためのものです。実際のこの月数はバラツキますが、何時から不良率を表示するのかという観点で決めます。不良率の精度を重視するなら大きな値を入れ、表示時期の早さを重視するなら小さな値を入れます。
5) 締日
集計ブックにテーブルで作成します。
'締日'テーブル
| 日付 |
|---|
| 2028/3/31 |
- 最新の市場実績日を入力します。
不良が無い場合に、0件という不良データが無くても、この日付まで市場不良が0件の実績があったとして、xMISの計算要否を決めます。 - これより新しい日付の市場不良データがあれば、その日をxMISの計算要否の判断に使用します。
3. 出力
ピボットテーブル
| 機種 | xMIS | 値 | 24/1Q | 24/1Q 集計 | ||
|---|---|---|---|---|---|---|
| 24/04 | 24/05 | 24/06 | ||||
| a | 03MIS | M不良率 | 1.00% | 1.00% | 1.00% | 1.00% |
| M不良率3 | 1.00% | 1.00% | 1.00% | |||
| 06MIS | M不良率 | 2.00% | 2.00% | 2.00% | 2.00% | |
| M不良率3 | 2.00% | 2.00% | 2.00% | |||
| 12MIS | M不良率 | 2.00% | 2.00% | 2.00% | 2.00% | |
| M不良率3 | 3.00% | 3.00% | 3.00% | |||
| 24MIS | M不良率 | 4.00% | 4.00% | 4.00% | 4.00% | |
| M不良率3 | 4.00% | 4.00% | 4.00% | |||
| 36MIS | M不良率 | 5.00% | 5.00% | 5.00% | 5.00% | |
| M不良率3 | 5.00% | 5.00% | 5.00% | |||
| 合計 | M不良率 | 6.00% | 6.00% | 6.00% | 6.00% | |
| M不良率3 | 6.00% | 6.00% | 6.00% | |||
- 不良率の小計は、その期間の平均不良率です。
- 不良率3は、3ヵ月移動平均です。3ヵ月移動平均不良率 = 3ヵ月の不良数 ÷ 3ヵ月の生産数
ピボットグラフ
4. 手順
1-1) ファクトテーブルの元データがあるフォルダの取得
- xMIS集計ブックを開き、[データ]-[データの取得v]-[ファイルから>]-[フォルダーから]で取得するファイルがあるフォルダを選択し、[開く]-[データの変換]でPower Queryエディタに取込み、右の名前を'フォルダ'に変更する。 ⇒ フォルダの下のサブフォルダも含むファイルリストができる。
- [Name]列の▼-[テキストフィルター>]-[次の値で始まらない]-[詳細設定]-[句の追加]で次のように入力し[OK]。 ⇒ ~$から始まる作業ファイルや集計を含む集計ファイルが消える。
| および/または | 列 | 演算子 | 値 | 解説 |
|---|---|---|---|---|
| Name | 次の値で始まらない | ~$ | 作業ファイルを除く | |
| および | Name | 指定の値を含まない | mis | 集計ファイルを除く |
| および | Name | 指定の値を含まない | 集計 | 集計ファイルを除く |
1-2) 生管（クロス表）部品生産データの取得と整形と結合
- 左の'フォルダ'クエリを右クリックし[参照]、右の名前を'生管'に変更。 ⇒ '生管'クエリが生成される。
- [Name]列の▼-[テキストフィルター>]-[指定の値を含む]-[詳細設定]で次のように入力し[OK]。 ⇒ 部品生産ファイルに絞り込む。
| および/または | 列 | 演算子 | 値 | 解説 |
|---|---|---|---|---|
| Name | 指定の値を含む | 生管 | 部品生産ファイルに絞り込む | |
| および | Extension | 指定の値に等しい | .xlsx | Excelブックに絞り込む |
-
[Content]列を右クリックし[他の列の削除]。 ⇒ ファイルの内容以外の列が削除される。
-
[Content]列の↓↓をクリックし、フォルダアイコンを選択し[OK]。 ⇒ 1つのファイルを処理するためのヘルパークエリが別に生成され、複数のファイルが縦に並ぶ。ファイルの内容は未展開。
-
右に生成された'サンプルファイル'、'ファイルの変換'、サンプル ファイルの変換'クエリの名前の後ろに"生管"を追加する。 ⇒ この作業は必須では無いが、名前で何を処理するヘルパークエリかが判別できるようになる。次に1ファイルを整形をするヘルパークエリを編集する。
-
'サンプル ファイルの変換生管'クエリを選択し、[KIND]列の▼-[テキストフィルター>]-[指定の値に等しい]で次のように入力し[OK]。 ⇒ Sheetに絞り込む。（印刷範囲などの情報を除く）
| 列 | 演算子 | 値 | 解説 |
|---|---|---|---|
| Kind | 指定の値に等しい | Sheet | Sheet以外を除く |
- [Data]列を右クリックし[他の列の削除]。 ⇒ ファイルの内容以外の列が削除される。
- [Data]列の矢印アイコン(展開)をクリックし、"元の列名をプレフィックスとして使用します"のチェックを外し[OK]。 ⇒ ファイルの内容が展開される。（複数のシートがある場合は、単純に縦結合される。）
- [Column3]の▼-[空の削除]。 ⇒ 項目列の項目が無い不要な行が消え、1行目が列項目名になる。
- [変換]-[1行目をヘッダーとして使用v]-[1行目をヘッダーとして使用]。 ⇒ 1行目がヘッダになる。
- 自動生成された"変更された型"のステップを削除。
- yy/mm形式の年月以外の列をCtrlキーを押しながら全て選択し、[変換]-[列のピボット解除v]-[その他の列のピボット解除]。 ⇒ yy/mm列が縦1列に並び、リスト形式の表になる。
- [項目]列を選択し、[変換]-[列のピボット]で次のように入力し[OK]。 ⇒ 異なる値の種類を持つ項目が横に分離される。
値列 : [値]
詳細設定オプション-値の集計関数 : [集計しない]
※ もし列のピボットでエラーが出る場合は、列のピボット解除の前のクロス表の状態でnullを0に置換してください。
- [列の追加]-[カスタム列]で次のように入力し[OK]。 ⇒ [日付]列が追加される。
新しい列名 : 日付
カスタム列の式 : =
#date(
2000 + Number.FromText(Text.BeforeDelimiter([属性], "/")),
Number.FromText(Text.AfterDelimiter([属性], "/")),
1
)
解説 : yy/mm形式のテキストからPower Queryの日付形式の列を作ります。
-
[属性]列を右クリックし[削除]。 ⇒ 不要なyy/mm列が削除される。1ファイルを整形する関数クエリが完成する。次にファイルを結合するクエリを編集する。
-
左の"生管"クエリを選択し、エラーになっている右の最後の"変更された型"のステップを削除する。
-
中央の任意のセルを選択しCtrl+aで全列を選択し、[変換]-[データ型の検出]。型が正しくない列名の左の型アイコンをクリックし、適切な型を選び、[現在のものを置換]。 ⇒ 正しい方が設定される。【注意】原則としてany型(ABC123)を残さないこと。残すと例えば数字の1と文字列の1が異なるものとして処理される。
1-3) 市場（リスト）市場サービスデータの取得と整形と結合
- 左の'フォルダ'クエリを右クリックし[参照]、右の名前を'市場'に変更。 ⇒ '市場'クエリが生成される。
- [Name]列の▼-[テキストフィルター>]-[指定の値を含む]-[詳細設定]で次のように入力し[OK]。 ⇒ 市場ファイルに絞り込む。
| および/または | 列 | 演算子 | 値 | 解説 |
|---|---|---|---|---|
| Name | 指定の値を含む | Service | 市場ファイルの名前に絞り込む | |
| および | Extension | 指定の値に等しい | .csv | .csvファイルに絞り込む |
-
[Content]列を右クリックし[他の列の削除]。 ⇒ 内容以外の列が削除される。
-
[Content]列の↓↓(ファイルの結合)をクリックし、[OK]。 ⇒ 1つのファイルを処理するためのヘルパークエリが別に生成され、複数のファイルが結合される。
-
右に生成された'サンプルファイル'、'ファイルの変換'、サンプル ファイルの変換'クエリの名前の後ろに"市場"を追加する。
-
'サンプル ファイルの変換市場'クエリを選択する。（もし、右に"ソース"と"ナビゲーション"以外のステップがあれば、そのステップを削除する。 ⇒ 自動生成された不要なステップが削除される。）
-
[ホーム]-[行の削除v]-[上位の行の削除]で行数に1を入れ[OK]。 ⇒ 不要な1行目が消える。
-
[変換]-[1行目をヘッダーとして使用v]-[1行目をヘッダーとして使用]。 ⇒ ヘッダが設定される。1ファイルを整形する関数クエリが生成される。次にファイルを結合するクエリを編集する。（もし、"変更された型"が自動生成されたら、適切な型を設定し直すか、"変更された型"のステップを削除する。）
-
左の"市場"クエリを選択し、右の最後の"変更された型"のステップを削除する。
-
中央の任意のセルを選択しCtrl+aで全列を選択し、[変換]-[データ型の検出]。型が正しくない列名の左の型アイコンをクリックし、適切な型を選び、[現在のものを置換]。 ⇒ 正しい型が設定される。
-
[ホーム]-[閉じて読み込むv]-[閉じて次に読み込む...]で、"接続の作成のみ"を選び[OK]でExcelに戻ります。
-
"クエリと接続"ペインの'生管'と'市場'クエリをそれぞれ右クリックし、[読み込み先...]で"このデータをデータモデルに追加する"を選択し、[OK]。 ⇒ データモデルに追加される。
1-4) 各種設定テーブルの取得
- xMISのテーブルの１つのセルを選択し、[データ]-[テーブルまたは範囲から]でPower Queryエディタに取込む。
- 型が正しくない列名の左の型アイコンをクリックし、適切な型を選び、[現在のものを置換]。 ⇒ 正しい型が設定される。（日付の型は、時刻が無いものにすする）
- [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"接続の作成のみ"と"このデータをデータモデルに追加する"を選択し[OK]でExcelに戻る。 ⇒ データモデルに追加される。
同様の手順で、'締日'と'機種'テーブルをデータモデルに追加する。
1-5) 日付テーブルの生成
Excelの場合は、Power Query Mで日付テーブルを生成します。（ワークシートで作成し、データモデルに追加しても良いです。）
- [データ]-[データの取得v]-[その他のデータソースから>]-[空のクエリ]でPower Queryエディタに空のクエリを生成し、右の名前を'日付'に変更します。
- [ホーム]-[詳細エディタ]で次のコードを上書きし、[OK]。
let
Source1 = 市場, // [日付]列を持つクエリ名を設定
Source2 = 生管, // [日付]列を持つクエリ名を設定
// ── 集計対象期間の決定 ──────────────
MinDate = Date.StartOfMonth(List.Min({List.Min(Source1[生産日]), List.Min(Source2[日付])})),
MaxDate = Date.EndOfMonth(List.Max({List.Max(Source1[発生日]), List.Max(Source2[日付]), List.Max(締日[日付])})), // 年度末日まで生成しない場合の式
// MaxDateRaw = List.Max({List.Max(Source1[日付]), List.Max(Source2[日付])}), // 年度末日まで生成する場合の式
// FiscalYearEndYear = if Date.Month(MaxDateRaw) >= 4 then Date.Year(MaxDateRaw) + 1 else Date.Year(MaxDateRaw),
// MaxDate = #date(FiscalYearEndYear, 3, 31),
// ── 日単位カレンダーの生成 ──────────────────────
DateList = List.Dates(MinDate, Duration.Days(MaxDate - MinDate) + 1, #duration(1, 0, 0, 0)),
CalendarTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"日付"}),
型設定済 = Table.TransformColumnTypes(CalendarTable, {{"日付", type date}}),
// ── 会計年度(4月始まり)の基準日・年・月を1回だけ算出 ──
// EDATE([@日付], -3) 相当：3ヶ月前にずらすことで
// 会計年度の「年」と「月番号(4月=1～翌3月=12)」を取得
会計基準日追加 = Table.AddColumn(型設定済, "会計基準日", each Date.AddMonths([日付], -3), type date),
会計年追加 = Table.AddColumn(会計基準日追加, "会計年", each Date.Year([会計基準日]), Int64.Type),
会計月追加 = Table.AddColumn(会計年追加, "会計月", each Date.Month([会計基準日]), Int64.Type),
// ── 表示用列の追加 ──────────────────────────────
// 年：YEAR([@日付])
列_年 = Table.AddColumn(会計月追加, "年", each Date.Year([日付]), Int64.Type),
// 月：TEXT([@日付],"m月") ※ゼロ埋めなし
列_月 = Table.AddColumn(列_年, "月", each Text.From(Date.Month([日付])) & "月", type text),
// 年月：TEXT([@日付],"yy/mm") ※ゼロ埋めあり
列_年月 = Table.AddColumn(列_月, "年月", each
let
yy = Text.PadStart(Text.From(Date.Year([日付]) - 2000), 2, "0"),
mm = Text.PadStart(Text.From(Date.Month([日付])), 2, "0")
in
yy & "/" & mm,
type text),
// 四半期.：INT((MONTH(x)-1)/3)+1 & "Q" ※年度なし
列_四半期ドット = Table.AddColumn(列_年月, "四半期.", each
let
四半期番号 = Number.IntegerDivide([会計月] - 1, 3) + 1
in
Text.From(四半期番号) & "Q",
type text),
// 四半期：(YEAR(x)-2000) & "/" & 四半期番号 & "Q" ※ゼロ埋めなし
列_四半期 = Table.AddColumn(列_四半期ドット, "四半期", each
let
四半期番号 = Number.IntegerDivide([会計月] - 1, 3) + 1
in
Text.From([会計年] - 2000) & "/" & Text.From(四半期番号) & "Q",
type text),
// 半期：(YEAR(x)-2000) & "/" & 半期番号 & "H" ※ゼロ埋めなし
列_半期 = Table.AddColumn(列_四半期, "半期", each
let
半期番号 = Number.IntegerDivide([会計月] - 1, 6) + 1
in
Text.From([会計年] - 2000) & "/" & Text.From(半期番号) & "H",
type text),
// 期：IF(MONTH(x)<7,"上期","下期") ※年度なし
列_期 = Table.AddColumn(列_半期, "期", each
if [会計月] < 7 then "上期" else "下期",
type text),
// 年度：(YEAR(x)-2000) & "年度" ※ゼロ埋めなし
列_年度 = Table.AddColumn(列_期, "年度", each
Text.From([会計年] - 2000) & "年度",
type text),
// 年度.：YEAR(x) ※西暦そのまま、年度なし表示との組み合わせ用
列_年度ドット = Table.AddColumn(列_年度, "年度.", each [会計年], Int64.Type),
// 内部計算用の一時列を削除し、ご指定の列順に並べ替え
不要列削除済 = Table.RemoveColumns(列_年度ドット, {"会計基準日", "会計年", "会計月"}),
最終結果 = Table.ReorderColumns(不要列削除済,
{"日付", "年月", "四半期", "半期", "年度", "年度.", "四半期.", "期", "月", "年"})
in
最終結果
補足 : このクエリは'生管'と'市場'と'締日'クエリの日付の範囲の日付テーブルを生成します。年度始まりを4月としています。最初のSource1とSource2とMaxDateの行にクエリ名を設定します。
参考 : CalendarTableまでのコードを詳細エディタで作成し、型設定済からをPower Queryエディタの主にカスタム列の追加を使用して作成することもできます。
- [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む...]で、"接続の作成のみ"と"このデータをデータモデルに追加する"を選び[OK]でExcelに戻ります。 ⇒ データモデルに追加される。
2) '市場'データモデルに[xMISid]列を追加（計算列）
- 次のいずれかの方法でデータモデルを表示します。
a. [Power Pivot]-[管理]-[ホーム]-[表示]-[データビュー]。
b. [データ]-[データモデルv(緑アイコン)]-[データモデルの管理]-[ホーム]-[表示]-[データビュー]。 - 下の'市場'タブを選択します。
- 列項目の[列の追加]をWクリックし名前を"xMISid"に変更します。
- 上の数式バーに次のコードを入力し、✓をクリックします。（数式バーの右端のアイコと下端のドラッグで表示行数を広げることができます。）
=VAR m=DATEDIFF('市場'[納車日], '市場'[発生日], DAY) / 30
RETURN
SWITCH(
TRUE,
m<=3, 3,
m<=6, 6,
m<=12, 12,
m<=24, 24,
m<=36, 36,
999
)
解説 : 1ヶ月を30日として月数を計算します。DATEDIFFで納車日から発生日までの日数を求め、/30で月数に換算しています。月の長さをSWITCH関数で各MISの月数に区分分けしています。このSWITCHの第1引数をTRUEにする使い方はDAX固有の使い方で、ワークシート関数のIFS()に似ています。999は月数では無く合計の識別子として使用します。
参考
DAXのDATEDIFF(,,MONTH)は、月またぎの回数を月数とするので、3MISの計算には使用できません。
Excelのワークシート関数DATEDIF(,,"M")は、開始日と同日に達した回数を月数とするので、3MISの計算には使用できません。
参考
-
追加した列のことを計算列と呼びます。計算列の式は、DAX式です。メジャーのDAX式と異なり、ピボットテーブルのコンテキストの影響を受けません。直接参照する列名には行コンテキストが掛かり、同一行の値を示します。なのでメジャーと異なり集計関数が不要です。関数内の列名は、行コンテキストが無く表全体を指します。
-
計算列は、Power Queryのカスタム列の追加に似ています。
-
Power Query MにはDATEDIFF/DATEDIFに相当する関数が無いので、本記事では計算列を使用しています。Power Query Mで実装したい場合は、DATEDIFに相当する関数クエリを使います。ネットで検索すると、関数クエリが見つかります。
-
Power Query：DATEDIF関数を作ってみる : なんでもエクセル（Nandemo Excel）: 2022/08/22
https://nandemo-xl.hatenablog.com/entry/2022/08/22/104719 -
DATEDIFFもどきを作る｜Power Query : @tanuki_phoenix(tanuki_PQ) : 2020/09/07
https://qiita.com/tanuki_phoenix/items/a0f2151b6f882fd0907a
-
3) リレーションの作成
次のいずれかの方法でリレーションを設定します。
a. [Power Pivot]-[管理]-[ホーム]-[表示]-[ダイアグラムビュー]。
b. [データ]-[データモデルv(緑アイコン)]-[データモデルの管理]-[ホーム]-[表示]-[ダイアグラムビュー]。
c. [データ]-[データモデルv(緑アイコン)]-[リレーションシップ]-[編集]。
ファクトテーブル *--<--1 ディメンジョンテーブル(関連)
'生管'[日付] *--<--1 '日付'[日付]
'市場'[生産日] *--<--1 '日付'[日付]
'生管'[機種] *--<--1 '科目'[機種]
'市場'[機種] *--<--1 '科目'[機種]
'市場'[xMISid] *--<--1 'xMIS'[xMISid]
'締日'[日付] : リレーションを取らない
ダイアグラムビューでは、(多)から1へ項目をドラッグする。
リレーションシップでは、上段が(多)で下段の関連が1になるようにテーブルと項目を設定する。
参考 : このデータ構造は2つのスタースキーマが合体した構造で、コンステレーションスキーマ（Constellation Schema）と呼ばれるらしいです。 By Gemini
3-1) ピボットテーブルの生成
[挿入]-[ピボットテーブルv]-[データモデルから]で、ピボットテーブルを生成します。
3-2) メジャーの作成
- 次のいずれかの方法でメジャーを作成します。
a. ① ピボットテーブルを選択し、右に"ピボットテーブルのフィールド"を表示。② メジャーを作成するテーブル('予算'または'実績)を右クリックし[メジャーの追加]。
b. [Power Pivot]-[管理]でメジャーを書くテーブルの下の空白セルを選択。数式バーにメジャーを入力する。
c. [データ]-[データモデル(緑アイコン)v]-[データモデルの管理]で、メジャーを書くテーブルの下の空白セルを選択。数式バーにメジャーを入力する。
解説 : メジャーの計算範囲と計算条件
- xMIS制限
納車から3ヵ月以下では、6MISと3MISは同じ値になります。しかし、これは6MISの値が良い訳ではなく、6MISの評価期間を満了していないためです。なので、誤解を避けるため6MISの値をBLANKにする式を入れています。 - 最終生産月制限
最終生産月の翌月の3ヵ月移動平均不良率は、翌月のデータが無いので2ヶ月分で計算されます。しかし、生産が無い月の不良率は意味が無いので、これをBLANKにする式を入れています。 - 小計・総計
3ヵ月移動平均不良率の小計・総計はその区間の直近月の値と同じになり、小計・総計区間の不良率ではありません。誤解を避けるため、これをBLANKにする式を入れています。なお、小計・総計区間の不良率は、単月の不良率のメジャーで計算されます。
| メジャー | 期間 | xMIS制限 | 最終生産月制限 | 小計・総計 |
|---|---|---|---|---|
| M不良数 | 現在のコンテキスト | あり | 生産数で判定 | 表示 |
| M不良率 | 現在のコンテキスト | ― | ― | 表示 |
| M不良数3 | 直近3か月 | あり | あり | 非表示 |
| M生産数3 | 直近3か月 | ― | あり | 非表示 |
| M不良率3 | 直近3か月 | ― | あり | 非表示 |
'生管'に作るメジャー
M生産数3:=VAR CurrentDate = MAX('日付'[日付]) // 年月コンテキストの日付の最大値(実質月末日になる)
VAR LastProductionDate = EOMONTH(
CALCULATE(MAX('生管'[日付]), ALL('日付')),
0
) // コンテキストの機種における生産実績の最終月末日
RETURN
IF(
HASONEVALUE('日付'[年月]) // 単月コンテキストのみ計算
&&
CurrentDate <= LastProductionDate, // 最終生産月以前を計算
CALCULATE( // 直近3か月の生産数を集計
SUM('生管'[生産数]),
DATESINPERIOD('日付'[日付], CurrentDate, -3, MONTH)
)
)解説 : 3ヵ月移動平均不良率の分母となる3ヵ月間の生産数を計算します。CULCULATE関数によりコンテキストをDATESINPERIOD関数の範囲に変更し、そのコンテキストの合計をSUM関数で求めています。第3引数(偽の式)が無いIF()は、偽の時に何も返しません。BLANK()と同じ処理になります。
長い式ですが、生産数の計算をしているのは、最後のCALUCULATE関数だけで、その他は、計算の要否を判定する式です。
'市場'に作るメジャー
M不良数:=VAR CurrentMIS = MAX('xMIS'[xMISid]) // コンテキストのxMIS
VAR LastServiceDate = MAX(
CALCULATE(MAX('市場'[発生日]), ALL('市場')),
MAX('締日'[日付])
) // 最新市場不良有効日
RETURN
IF(
CurrentMIS = 999 // 合計は計算
||
IF(
MIN('日付'[日付]) <= LastServiceDate, // 計算開始日が最新市場不良有効日以前の場合のみ処理
// (最新市場不良有効日 ~ 計算開始日)の月越え数 - 上市月数 >= xMISの期間 なら計算｡小計も計算するので計算開始日で判定
DATEDIFF(MIN('日付'[日付]), LastServiceDate, MONTH) - MAX('機種'[上市月数]) >= CurrentMIS
),
CALCULATE( // コンテキストのxMIS以下の不良数を合計
SUM('市場'[数量]),
FILTER(ALL('xMIS'), 'xMIS'[xMISid] <= CurrentMIS)
) + 0 // 不良が無い場合は0
)解説 : これはコンテキストの期間の不良数の合計です。xMISは下位のxMISを含むので、FILTER関数でxMISidが小さい下位の範囲を含むコンテキストに変更してSUM関数で合計します。
xMISの有効性は、(最新市場不良有効日 - 計算開始日)の月数 - 上市月数 >= コンテキストのxMISの月数 が成立する条件で判断しています。(最新市場不良有効日 - 計算開始日)の月数は、DATEDIFF(,,MONTH)で計算される月またぎの回数です。不良率は小計も計算しますが、小計区間に1つでもxMISが有効な月が含まれていれば、小計を出すためにコンテキストの開始日で有効性を判断します。
例 : 「最新市場不良有効日3月 - 計算開始日1月 - 上市月数0」は、「3-1-0=2 < 3MISの月数3」なので計算しない。即ち、1月は3月時点では2ヶ月経過で、3MISの3ヵ月を満たしていないので、3MISを計算しません。
長い式ですが、不良数の計算をしているのは、最後のCALUCULATE関数だけで、その他は、計算の要否を判定する式です。
M不良率:=DIVIDE([M不良数], SUM('生管'[生産数]))解説 : コンテキストの期間の不良率。DIVIDEは割り算をする関数で、分母が0の時エラーを表示しません。実務では分子を1,000,000倍して、ppmにした方が良いです。
M不良数3:=VAR CurrentDate = MAX('日付'[日付]) // 年月コンテキストの日付の最大値(実質月末日になる)
VAR CurrentMIS = MAX('xMIS'[xMISid]) // コンテキストのxMISid
VAR LastProductionDate = EOMONTH(
CALCULATE(MAX('生管'[日付]), ALL('日付')),
0
) // コンテキストの機種における生産実績の最終月末日
VAR LastServiceDate = MAX(
CALCULATE(MAX('市場'[発生日]), ALL('市場')),
MAX('締日'[日付])
) // 最新市場不良有効日
RETURN
IF(
HASONEVALUE('日付'[年月]) // 単月コンテキストのみ計算
&&
CurrentDate <= LastProductionDate // 最終生産月以前を計算
&&
( // xMISの有効性の判定
CurrentMIS = 999 // 合計は計算
||
IF(
CurrentDate <= LastServiceDate, // 計算終了日が最新市場不良有効日以前の場合のみ処理
// (最新市場不良有効日 ~ 計算終了日)の月越え数 - 上市月数 >= xMISの期間 なら計算｡移動平均は単月コンテキストのみ計算するので計算終了日で判定
DATEDIFF(CurrentDate, LastServiceDate, MONTH) - MAX('機種'[上市月数]) >= CurrentMIS
)
),
CALCULATE(
SUM('市場'[数量]), // 3ヶ月間の不良数を合計
FILTER(ALL('xMIS'), 'xMIS'[xMISid] <= CurrentMIS), // 下位xMISを含む
DATESINPERIOD('日付'[日付], CurrentDate, -3, MONTH) // 当月を含む3ヶ月間
) + 0 // 不良が無い場合は0
)解説 : 3ヵ月の移動平均不良率の分子となる3ヵ月の不良数の合計。式の仕組みは、[M生産数3]の3ヵ月の処理と部分と[M不良数]の下位xMISの処理を組み合わせたものです。これに、最終生産月制限を加えています。
xMISの有効性判定は計算終了日を用いています。これは、移動平均が単月コンテキストしか計算しないので、小計のコンテキストの開始日が当月より前になることに配慮する必要が無いからです。
その他は、式中のコメントを参照してください。
長い式ですが、不良数の計算をしているのは、最後のCALUCULATE関数だけで、その他は、計算の要否を判定する式です。
M不良率3:=DIVIDE([M不良数3], [M生産数3])解説 : 3ヵ月の移動平均不良率。割り算の分母である[M生産数3]がBLANKなら、BLANKを返すので、計算するかどうかの判断は[M生産数3]の中のみで良い。
12) ピボットテーブルの設定
デバッグ用の集計一覧
デバッグ用に不良率以外の値も表示するピボットテーブルを作成します。報告書などには、不良率のみ表示したピボットテーブル、ピボットグラフを作成してください。
- 次のように項目を設定します。
- 列 : '日付'[四半期]、'日付'[年月]。
- 行 : '機種'[機種]、'xMIS'[xMIS]、Σ値。
- Σ値 : '生管'[生産数]、[M不良数]、[M不良率]、[M生産数3]、[M不良数3]、[M不良率3]。
- [ピボットテーブル分析]-[オプション]-[レイアウトと書式]で、"更新時に列幅を調整する"のチェックを外す。
- [ピボットテーブル分析]-[オプション]-[表示]で、"データのないアイテムを行/列に表示する"のチェックを外す。
- [デザイン]-[レポートのレイアウト]-[アウトライン形式で表示]または[表形式]。（表形式だと小計が下に表示される）
- [デザイン]-[総計v]-[行と列の集計を行う]。
- 表の"四半期"を右クリックし、["四半期"の小計]をクリックして✓マークを付ける。
- [デザイン]-[ピボットテーブルスタイル]で、ベースとするスタイルを右クリックし[複製]。ユーザー設定のスタイルを右クリックし、[変更]でスタイルを変更。[デザイン]-[ピボットテーブルスタイル]で、ユーザー設定したスタイルをクリック。
- 最初の列 : 右罫線
- 見出し行 : 下罫線; 網掛け
- 小計列 1 : 右計算、網掛け
- 小計行 1 : 上下罫線
- 列小見出し 1 : 上罫線(細)
- 列小見出し 2 : 上罫線(細)
- 行小見出し 1 : 上罫線(細)
- 行小見出し 2 : 上罫線(細)
- 総計列 : 右罫線(太); 網掛け
- 総計行 : 上下罫線(細,太); 網掛け
参考 : ピボットテーブルのスタイルの自由度は低いです。手動でセルの書式設定しても、更新で書式がクリアされる場合があります。自由に書式設定したい場合は、GETPIVOTDATAを使用して別のワークシート表で書式設定したほうが良いです。
ピボットグラフ用のピボットテーブルとピボットグラフの作成
- 集計一覧のピボットテーブルがあるシートをコピーします。
- 次のように項目を設定します。
- フィルター : '機種'[機種]
- 列 : 'xMIS'[xMIS]、Σ値。
- 行 : '日付'[年月]。
- Σ値 : [M不良率] または [M不良率3]。
-
[ピボットテーブル分析]-[オプション]-[表示]で、"データのないアイテムを行/列に表示する"のチェックを外す。
-
[デザイン]-[レポートのレイアウト]-[表形式]。
-
[デザイン]-[総計v]-[行と列の集計を行わない]。
-
表の"四半期"を右クリックし、["四半期"の小計]をクリックして✓マークを外す。
-
[ピボットテーブル分析]-[ピボットグラフ]-[折れ線]-[マーカー付折れ線]-[OK]
-
機種フィルターで1機種を選択
別条件のピボットテーブルとピボットグラフの作成
- 前記ピボットテーブルとピボットグラフがあるシートをコピー。または、ピボットテーブルとピボットグラフ全体を含むセル範囲を切り取り、別の場所に貼り付ける。
- 機種フィルターやΣ値の項目を変更。
5. 注意
不良品の製番の製造日がリワークなどの入庫ずれなどで、生産数のデータの製造日と合わず、不良品の製番から求めた製造日の生産数が0の場合、不良率は表示されません。後述のサンプルファイルの2025年5月の機種aがその例です。
これにより、サンプルの単月の推移グラフの時間軸から2025年5月が抜け落ちていますが、グラフでは気づきません。
3ヵ月移動平均は、3ヶ月間生産0が続いていないのでグラフに表示されまが、3ヶ月間生産0が続けば単月と同じことが起きます。
この間違いに気づく為に、サンプルではデバッグ用の'一覧シート'を設けています。これを見ると25/05の不良数が1なのに、生産数が空白であることから、間違いに気づきます。
ピボットテーブルの[オプション]-[表示]で"データのないアイテムを行/列に表示する"のチェックを入れると、データが無いことに気づきやすくなりますが、余計な項目も表示されます。この場合は、メジャーで余計な項目の非表示処理をするより、ピボットテーブルのフィルターやスライサーやワークシートの非表示機能で不要な部分を非表示にした方が、短いメジャーになります。
この例以外の事も含め間違いに気づく為には、メジャーのDAX式の動作を理解していることが必要です。メジャーは、コンテキストを理解していないと想定外の値を出すことがあるので、コンテキストを認識してください。
本メジャーの作成にChatGPTを利用しましたが、AI/LLMの式は抜けや冗長な部分が多くあり、かなりの手修正を入れています。コンテキストが分からない時に、AI/LLMを活用することは有効です。その際は、入力と出力の項目とリレーションとメジャーをインプットします。
6. サンプル
C:\Users\Public\Documents\260823_3mis に展開した各ファイルを置いてください。
- 集計1.xlsx : 3MISの集計ブックです。
- 生管.xlsx : 部品の生産データです。
- Service.csv : 市場サービスデータです。UTF-8です。
- Service.xlsx : テストデータService.csvを作成するための元データです。集計.xlsxでは使いません。
- 月数テスト.xlsx : 月数の計算式のテストをしたファイルです。
7. 参考
-
Excel Power Pivot 生産実績表と不良実績表から不良率の推移表を作成:2026/08/07:当ブログ
https://bigarrow.tea-nifty.com/blog/2026/08/post-a1a7c6.html -
Power Queryで複数の表を結合する方法（まとめ）：2025/11/06:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/11/post-341d5f.html -
Power Queryで表をリスト形式に整形する方法（まとめ）:2025/11/16:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/11/post-4925ca.html
最近のコメント