« 2025年9月 | トップページ | 2025年11月 »

2025年10月

複数ブック、複数シートをPower Queryで1つの表にする方法（シート整形関数クエリ）

1. 前置き

以前の記事

Power Queryでフォルダからデータを取得するとヘルパークエリが作られますが、この時作られるヘルパークエリは、ファイルを処理するクエリです。
シートがクロス表等で横項目の値が異なる場合は、単純にシートを展開(Table.ExpandTableColumn)できないので、クリック操作では処理ができません。
そこで、以前Power Query Mの詳細エディタでTable.Combineなどを使用し、縦結合するコードを記述する方法を記事にしました。

Power QueryでExcelの複数のシートを縦結合する方法
https://bigarrow.tea-nifty.com/blog/2025/02/post-b425f3.html

本時事で紹介する方法

手動でシート整形関数クエリを作成し、2つのカスタム列の追加で、ブックとシートを処理して結合します。
詳細エディタでコードを書くのは1ヵ所だけなので、上記記事のTable.Combineより操作が簡単です。

しかし、展開ボタンでTable.ExpandTableColumnが使われるので、シートの整形後の表の項目を揃える必要があります。
また、関数クエリ作成後は、関数クエリをPower Queryエディタのクリック操作で編集できなくなります。
これらを回避する場合は、7.関連記事にある 1ヘルパークエリ や Table.Combine を用いる方法 を使用してください。

2. 入力表

Sheet1 Sheet2
売上 A店 25年10月  
商品 1日 11日 21日
商品01 1 2 3
商品02 2 3 4
商品03 3 4 5
売上 A店 25年11月  
商品 1日 11日 21日
商品01 1 2 3
商品02 2 3 4
商品03 3 4 5

条件

  1. 店舗別のブックに1ヶ月1シートの売上実績表があります。
  2. 1シートの表は次の様に縦に商品、横に日付が並んだクロス表です。
  3. 年月と日はシリアル値で、表示形式は"yy年mm年"、"d日"です。
    （日付けが整数の場合は、Power Queryエディタの[カスタム列の追加]で式に「try #date(Date.Year([年月]), Date.Month([年月]), [属性]) otherwise null」と入力して、日付に変えてください。）
  4. 左上の売上数のセルアドレスはA1です。
  5. 1つのフォルダに各店舗の売上実績と集計ファイルがあります。それ以外のファイルはありません。
  6. 集計ファイル名にみに"集計"という文字を含みます。
  7. 売上実績ブックには、実績以外のシートは入っていません。

横項目は日付なので、シート毎に日付のシリアル値が異なります。なので、通常のフォルダーから取得する方法で"Content"列のBinaryを展開すると、異なるシリアル値のデータが縦に並び、列のデータが揃いません。

3. 出力表

合計/売上、年月

店舗 商品 25/10 25/11 25/12 総計
A店 商品01 6 9 12 27
  商品02 9 12 15 36
  商品03 12 15 18 45
A店 集計   27 36 45 108
B店 商品01 9 12 15 36
  商品02 12 15 18 45
  商品03 6 9 12 27
B店 集計   27 36 45 108
総計   54 72 90 216

4. 手順例

シートの整形クエリの作成

1つのシートをサンプルにしてデータベース形式に変更するクエリを作成します。シートの表の形により手順が異なります。以下の操作は、2.入力表の場合の手順例です。

  1. 集計ファイルを開き[データ]-[データの取得v]-[ファイルから>]-[Excelブックから]でExcelブックを選択し[インポート]、シートを選択し[データの変換]でPower Queryエディタに取込み、右の名前を'シート整形サンプル'に変更。

  2. 右の"変更された型"の左の×をクリックして削除。

  3. [列の追加]-[条件列]で次のように入力し[OK]。[]は列の選択で入力。
    新しい列名：カスタム
    列名         ：演算子              ：値   ：出力
    [Column1]：指定の値に等しい：売上：[Column2]
    それ以外の場合
    null

  4. [列の追加]-[条件列]で次のように入力し[OK]。
    新しい列名：カスタム.1
    列名         ：演算子              ：値   ：出力
    [Column1]：指定の値に等しい：売上：[Column3]
    それ以外の場合
    null

  5. "カスタム"列を選択し、Ctrlを押しながら"カスタム.1"列を選択し、[変換]-[フィルv]-[下へ]。

  6. [列の追加]-[条件列]で次のように入力し[OK]。
    新しい列名：カスタム.2
    列名         ：演算子              ：値   ：出力
    [Column1]：指定の値に等しい：商品：店舗
    それ以外の場合
    [カスタム]

  7. [列の追加]-[条件列]で次のように入力し[OK]。
    新しい列名：カスタム.3
    列名         ：演算子              ：値   ：出力
    [Column1]：指定の値に等しい：商品：年月
    それ以外の場合
    [カスタム.1]

  8. "カスタム"列を選択し、Ctrlを押しながら"カスタム.1"列を選択し、右クリックで[列の削除]。

  9. [ホーム]-[行の削除v]-[上位の行の削除]で行数に1を入力し[OK]。

  10. [変換]-[1行目をヘッダーとして使用v]-[1行目をヘッダーとして使用]。

  11. 右の最後の"変更された型"の左の×をクリックして削除。

  12. "年月"列を選択し、Ctrlを押しながら"店舗"と"商品"列を選択し、[変換]-[列のピボット解除v]-[その他の列のピボット解除]。

  13. 数式バーの"値"を"売上"に変更。

  14. [列の追加]-[カスタム列]で次のように入力し[OK]。
    新しい列名：日付
    カスタム列の式：= Date.AddDays(#date(1899, 12, 30), Number.FromText([属性]))

    解説：Excelの日付のシリアル値(整数)をPower Queryの日付形式に変換する式です。

  15. "属性"列を右クリックし[削除]。

  16. "年月"列を選択し[変換]-[抽出v]-[範囲]で開始インデックスに2、文字数に5を入れ[OK]。

  17. 適当な列を選択し、Ctrl+aで全列選択し、[変換]-[データ型の検出]。適切ではない型を右クリックし[型の変更]で再設定し[現在のものを置換]する。（本例では、[売上]を"10進数"に変更。）

注意：全てのシートが同じ項目を持つように成形してください。

修正方法："シート整形関数"クエリを作成後に"シート整形サンプル"クエリを修正した場合は、修正した箇所のコードを"シート整形関数"クエリに貼り付けてください。

シートの整形関数クエリの作成

前記のクエリを関数クエリに変更します。

  1. 左の"シート整形サンプル"クエリを右クリックし[複製]、右の名前を"シート整形関数"に変更。
  2. [ホーム]-[詳細エディタ]で次のように冒頭のコードを変更し[完了]。
let
    ソース = Excel.Workbook(File.Contents("サンプルExcelブックのフルパス名"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    追加された条件列 = Table.AddColumn(Sheet1_Sheet, "カスタム", each if [Column1] = "売上" then [Column2] else null),

↓

(_Table as table) =>
let
//    ソース = Excel.Workbook(File.Contents("サンプルExcelブックのフルパス名"), null, true),
//    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    Sheet1_Sheet = _Table,
    追加された条件列 = Table.AddColumn(Sheet1_Sheet, "カスタム", each if [Column1] = "売上" then [Column2] else null),

集計クエリの作成

  1. [ホーム]-[新しいソース]-[ファイル>]-[フォルダー]でフォルダを選択し[開く]-[データの変換]、右の名前を"集計"に変更。

  2. "Name"列の▼-[テキストフィルタ]-[次の値から始まらない]-[詳細設定]で次を入力し[OK]。
    および/または：列：演算子            ：値
    _____：Name：次の値で始まらない：~$
    および：Name：指定の値を含まない：集計
    および：Extension：指定の値に等しい：.xlsx

  3. "Content"とCtrlを押しながら"Name"を選択、右クリックし[他の列の削除]。

  4. [列の追加]-[カスタム列]で次のように入力し[OK]。
    新しい列名：カスタム
    カスタム列の式：= Excel.Workbook([Content], null, true)

解説：Excel.Workbook()は、Excelブックの内容（シート他）を返す関数です。これを次のステップで展開（Table.ExpandTableColumn）することにより、中身が行に展開されます。

  1. "カスタム"列の矢印アイコンをクリックし、"Data"、"Item"、"Kind"のみチェックを残し、"元の列名をプレフィックスとして使用します"のチェックを外し[OK]。

  2. "Kind"列の▼-[テキストフィルター]-[指定の値に等しい]で値にSheetと入力し[OK]。

  3. [列の追加]-[カスタム列]で次のように入力し[OK]。
    新しい列名：カスタム
    カスタム列の式：= シート整形関数([Data])

解説：展開したシートの内容を関数クエリで１つずつ整形します。これを後のステップで展開（Table.ExpandTableColumn）することにより、中身が行に展開＝整形した表が縦に並びます。

注意：Table.Combineではないので、最初のシートに無い項目が後のシートに合っても展開されません。

  1. "Content"列を選択し、Ctrlを押しながら"Data"と"Kind"列を選択し、右クリックで[列の削除]。

  2. "カスタム"列の矢印アイコンをクリックし、"元の列名をプレフィックスとして使用します"のチェックを外し[OK]。

  3. 適当な列を選択し、Ctrl+aで全列選択し、[変換]-[データ型の検出]。適切ではない型を右クリックし[型の変更]で再設定し[現在のものを置換]する。（本例では、[売上]を"10進数"に変更。）

  4. [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"接続の作成のみ"を選択し[OK]でExcelに戻る。

Excelシートに読み込む

  1. 右の"クエリと接続"の[クエリ]の"実績"を右クリックし[読み込み先…]で必要な形式を選択し[OK]。
    本例では"ピボットテーブル"と"このモデルをデータモデルに追加"を選択し[OK]。

  2. ピボットテーブルを選択し[ピボットテーブルのフィールド]-[ピボットテーブル]-[オプション]-[レイアウトと書式]で"更新時に列幅を自動調整する"のチェックを外す。

  3. [デザイン]-[レポートのレイアウト]-[表形式で表示]。

  4. [デザイン]-[小計v]-[すべての小計をグループの末尾に表示する]。

  5. "ピボットテーブルのフィールド"で次のように項目をドラッグする。
    列：[年月]。行：[店舗]、[商品]。Σ値：[売上]。

  6. データの変更、追加があれば、[データ]-[すべて更新v]-[すべて更新]で更新される。

5. サンプル

ダウンロード - 251020pqsh.zip

C:\Users\Public\Documents に展開します。C:\Users\Public\Documents\251020pqsh の下にファイルが展開されます。

6. 参考情報

表を整形する。ファイルを統合する。両方やらなくっちゃあならないってのがつらいところ、でもない。【Excel Power Query】
https://www.youtube.com/watch?v=PCThHqvQKIU
1シートの複数ブックの結合です。

【Excel パワークエリ】これを見ても、まだコピペでいいなんて言うヤツ……いねぇよなぁ
https://www.youtube.com/watch?v=UapvFSxv2mE
リスト（テータベース）形式の複数シートの複数ブックの結合です。

Power Query：複数のシートのデータを縦結合する（ヘルパークエリ編）
https://nandemo-xl.hatenablog.com/entry/2023/11/14/161634
1ブックの複数シートの結合です。

7. 関連記事

Power Queryで複数の表を結合する方法（まとめ） : 当ブログ : 2025/11/05
https://bigarrow.tea-nifty.com/blog/2025/11/post-341d5f.html

Power Queryで表をリスト形式に整形する方法（まとめ）: 当ブログ : 2025/11/06
https://bigarrow.tea-nifty.com/blog/2025/11/post-4925ca.html

複数ブック、複数シートをPower Queryで1つの表にする方法（1ヘルパークエリ）: 当ブログ : 2025/11/04
https://bigarrow.tea-nifty.com/blog/2025/11/post-d0f322.html
これがお勧めです。

Excel Power Query Table.Combine を用いた複数ブック、複数シートの縦結合 : 当ブログ : 2025/12/20
https://bigarrow.tea-nifty.com/blog/2025/12/post-60e0ad.htm
シート整形後の各シートの項目が異なっていても結合できますが、ブック名やシート名を残す場合は、少々コードが長くなります。

8. 余談

当初、パラメータの変換と関数の生成を用いてシート整形関数クエリを作成しようとしたのですが、この機能ではパラメータを1つしか渡せないので、クリック操作でヘルパークエリを作ることができませんでした。そこでChatGPTの力を借りて本記事の方法を作成しました。

その後、若干のコードを記述することにより、手動でヘルパークエリを作成する方法が分かりました。それが、1ヘルパークエリ  の記事です。ヘルパークエリを使用すると、関数クエリの動作をステップごとに確認し修正できるので保守性が良いです。本記事の関数クエリの仕組みを理解したうえで、1ヘルパークエリの方法を使うと、理解しやすいと思います。

2025年10月21日 (火) Excel | | コメント (0)

ExcelのピボットテーブルでTop3とその他合計と合計行を表示するメジャー

1. 前置き

Top3以外をその他で纏めることで短い集計表を作ります。その為の参考情報をいくつか紹介します。

1) MDXを使用する方法

セット、MDXを使用してTop5と合計を表示する方法です。MDXでその他が出せると便利そうですが、方法が分かりませんでした。

余談：セット&MDXとメジャーは相性が悪いかも知れません？　同時に使用して、メジャーを変更すると「クエリが実行されなかったか、データモデルにアクセスできませんでした。エラーメッセージは次の通りです。データベースの構造が変更されたので、現在のセッションが無効になりました。」という注意が表示されます。このエラーが出ると[すべて更新]ができないのですが、ファイルを保存終了して再起動すると[すべて更新]が動作します。
251013pterr

2) ピボットテーブルのグループ化、フィルターを使用する方法

初心者必見！Excelでトップ5の商品だけ表示＆残りを「その他」にまとめる集計テクニック【売上分析】
https://www.youtube.com/watch?v=C6ajWOYt8MQ

  1. ピボットテーブルのグループ化。一番簡単な方法です。でも、データが変わるとやり直しが必要です。
    251013p
  2. ピボットテーブルのフィルターのトップ10。その他と合計はワークシート式。
    251013t
  3. YouTubeのコメントにスピルを用いたワークシート式の方法が書かれています。
    251013w

3) メジャーでSWITCHを使用する方法（ここで紹介する方法）

次の方法を応用してTop3とその他と総計の3つのメジャーを切替表示する式をChatCPTに相談して作成したのが、ここで紹介する方法です。

  • 損益計算書(P&L)、貸借対照表(バランスシート)の作成方法 -071 : PowerBIちゃんねる : 2024/08/23
    https://www.youtube.com/watch?v=MAAvOSNdGl4
    複数のメジャーをSWITCHで切替て1つのメジャーでピボットテーブルに表示する方法です。

メリット

  1. スライサーやフィルターで動的に順位の対象を切り替えることができます。
  2. その他の内訳を簡単に展開できます。

デメリット

  1. メジャー(DAX)の知識が必要です。

251013m

4) Power Queryを使用する方法

次の方法を応用して、メジャーではなくPower Queryで作成する方法です。

  • 【Excel】ピボットテーブルで総勘定元帳から損益計算書(PL)を作るなんて簡単。そんなふうに考えていた時期が俺にもありました。
    https://www.youtube.com/watch?v=pjhkmd3qdL0

この方法でもTop3、その他、総計を出すことができます。
251013qp

※ サンプルファイルには、2)、4)の作例も入っています。2)、4)の手順は、Power Queryエディタでステップを見てください。

2. 入力表

テーブル名:'売上'

日付 店舗 商品 売上
25/10 A店 商品01 1
25/10 A店 商品02 2
25/10 A店 商品03 3
25/10 A店 商品04 4
25/10 A店 商品05 5
25/11 A店 商品01 6
25/11 A店 商品02 5
25/11 A店 商品03 4
25/11 A店 商品04 3
25/11 A店 商品05 2
25/12 A店 商品01 1
25/12 A店 商品02 2
25/12 A店 商品03 3
25/12 A店 商品04 4
25/12 A店 商品05 5

テーブル名:'商品区分'

区分
Top3
その他

3. 出力表

順位は総計売上の順位を使用します。日付をスライサーやフィルタで絞ることで、順位対象となる総計の範囲を変更することができます。

次のピボットテーブルをメジャーで作成します。

区分 商品 25/10 25/11 25/12 総計
Top3 商品05 5 2 5 12
商品04 4 3 4 11
商品03 3 4 3 10
Top3 集計 15 20 15 50
その他 商品02 2 5 2 9
商品01 1 6 1 8
その他 集計 3 11 3 17
総計 15 20 15 50

上記ピボットテーブルの表示形式を表形式にして、小計の表示をせず、その他と総計の展開を止めると、次のような表になります。これが最終出力表です。

区分 商品 25/10 25/11 25/12 総計
Top3 商品05 5 2 5 12
商品04 4 3 4 11
商品03 3 4 3 10
その他 3 11 3 17
総計 15 20 15 50

4. 手順

1. テーブルの作成

Excelで1.入力表に示す表を作成し、範囲選択をして[挿入]-[テーブル]でテーブル化し、[テーブルデザイン]の名前をそれぞれ'売上'、'商品区分'に変更します。

2. カレンダーテーブル(日付テーブル)の作成とデータモデルの追加

  1. '商品区分'テーブルを選択し、[Power Pivot]-[データモデルに追加]で、データモデルに追加されます。
    ※ [Power Pivot]が表示されない時は、[データ]-[データモデル]-[データモデルの管理]でPower Pivotを有効化してください。

  2. '売上'テーブルを選択し、[データ]-[テーブルまたは範囲から]でPower Queryエディタに取込みます。

  3. "日付"列を右クリックし、[型の変更]-[日付]-[現在のものを置き換え]。

  4. 左の'売上'クエリを右クリックし[参照]、右の名前を'カレンダ'に変更します。

  5. "日付"列を右クリックし、[他の列の削除]。

  6. "日付"列を右クリックし、[重複の削除]。

  7. [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"接続の作成のみ"と"このモデルをデータモデルに追加する"を選択し[OK]。Excelに戻ります。

※ 期や年度があるカレンダーテーブル(日付テーブル)を作成すると、期や年度で絞り込み表示ができます。

Excel Power Query Mでカレンダを作成し、メジャーで前の値との差を計算
https://bigarrow.tea-nifty.com/blog/2025/06/post-b14054.html

3. リレーションの作成

[データ]-[データモデル]-[リレーションシップ]-[新規作成]で次のように入力し[OK]-[閉じる]。

テーブル：データモデルのテーブル:売上、列：日付
関連テーブル：データモデルのテーブル:カレンダ、関連列：日付

または、次のようにPower Pivot for Excelでリレーションを作成します。

[Power Pivot]-[管理]-[ダイアグラムビュー]で'売上'[日付]を'カレンダ'[日付]にドラッグする。

※ 商品区分はメジャー内の式で区別するので、商品区分と売上をリレーションしません。この為、黄色の「テーブル間のリレーションシップが必要である可能性があります。」との注意が出ますが、無視してください。

 

4. ピボットテーブルの作成

[挿入]-[ピボットテーブル]-[データモデルから]-[OK]でピボットテーブルを生成します。

 

5. メジャーの作成

  1. ピボットテーブルをクリックし、右の"ピボットテーブルのフィールド"の[すべて]ですべてのテーブルを表示します。
  2. 橙の円柱アイコンがある'売上'を右クリックし[メジャーの追加]で次の6つメジャーをそれぞれ入力して[OK]。値の説明は入力不要です。

次のDAX式の:の前がメジャーの名前で、:の後が数式です。

売上

売上の合計を求める式です。

M売上:=SUM('売上'[売上])

商品ごとの総売上

商品ごとの売上の総計を求める式です。

M商品総売上:=
CALCULATE(
    [M売上],
    ALLSELECTED('カレンダ'[日付]) // [日付]のフィルタを解除、[商品]別の総売上を求める
//  ALLEXCEPT('売上', '売上'[商品]) // [商品]以外の[日付]フィルタ等を解除、[商品]別の総売上を求める
)

'カレンダ'テーブルを使用すると、'カレンダ'[日付]をスライサーやフィルターで絞り込むことにより、順位を求める売上の日付を限定することができます。
全日付の総売上で順位を決めるのであれば、限定された範囲内で日付のコンテキストを解除するALLSELECTED('カレンダ'[日付])は不要です。その代わりにALLEXCEPT('売上', '売上'[商品])を使用して、'売上'のフィルタコンテキストを解除します。

全商品の中で順位づけ

商品ごとの総計の順位を求める式です。同順位がある場合の扱いは、RANKX()関数の第5引数で選択してください。

M全体順位:=
RANKX(
    ALLSELECTED('売上'[商品]), // 全商品の中の順位を求める
    [M商品総売上], // 商品の総売上の順位を求める
    ,
    DESC, // 降順
    DENSE // 同順位の次は+1とし、スキップしない
)

上位3商品だけを抽出

上位3位までの商品だけの売上を求める式です。

M売上_Top3:=
VAR _Top3 = // 3位以内の商品フィルタ
FILTER(
    ALLSELECTED('売上'[商品]), // 全商品の中で
    [M全体順位] <= 3 // 3位以内を抽出
)
RETURN
CALCULATE(
    [M売上],
    KEEPFILTERS(_Top3) // 3位以内のフィルタ AND ピボットテーブルの[商品]コンテキスト
)

KEEPFILTERSの解説

_Top3は3位以内の商品を返します。この例ですと全体順が3位以内の{商品03,商品04,商品05}を返します。
もしKEEPFILTERSが無いと、この3商品がCALCULATE関数のフィルタコンテキストになるので、この3商品の[M売上](=3商品の合計)がそれぞれの商品の売上として返ってしまいます。
KEEPFILTERSを使うと、CALCULATE関数はピボットテーブルの[商品]コンテキストと_Top3のANDのフィルタを掛けます。ピボットテーブルの[商品]が_Top3にあれば、ピボットテーブルの商品の[M売上]のみが返ります。ピボットテーブルの[商品]が_Top3に無ければ、ANDが成立しないので何も返しません。

それ以外の商品

その他の商品の売上を求める式です。

M売上_その他:=
VAR _Others = // 3位外の商品フィルタ
FILTER(
    ALLSELECTED('売上'[商品]), // 全商品の中で
    [M全体順位] > 3 // 3位外を抽出
)
RETURN
    CALCULATE(
    [M売上],
    KEEPFILTERS(_Others) // 3位外のフィルタ AND ピボットテーブルの[商品]コンテキスト
)

KEEPFILTERSの働きは、M売上_Top3と同じです。

区分テーブルからの切替

これをΣ値に設定することで、M売上_Top3、M売上_その他、M売上、を'商品'[区分]で切り替えて表示するメジャーです。

M商品区分:=
=
VAR sel =
IF (
    HASONEVALUE('商品区分'[区分]), // [区分]が1つか?
    VALUES('商品区分'[区分]), // [区分]が1つなら[区分]を返す
    "総計" // [区分]が複数なら"総計"を返す
)
RETURN
SWITCH( // selの値で返すメジャーを切り替える
    TRUE(),
    sel = "Top3", [M売上_Top3],
    sel = "その他", [M売上_その他],
    sel = "総計", [M売上] // 総計はフィルタを掛けずに売上をそのまま返す
)

各メジャーの関係

[ピボット行] 商品区分 - 商品
↓
[M商品区分]＝ピボットテーブルのΣ値に設定するメジャー：商品区分によりSWITCHで表示するメジャーを切替える
↓
├─ [M売上_Top3]
│ ├─ KEEPFILTERS [M全体順位] ≤ 3
│ │              └─ [M商品総売上]
│ │                  ├─ ALLSELECTED([日付])
│ │                  └─ [M売上]
│ └─ [M売上]
├─ [M売上_その他]
│ ├─ KEEPFILTERS [M全体順位] > 3
│ │              └─ [M商品総売上]
│ │                  ├─ ALLSELECTED([日付])
│ │                  └─ [M売上]
│ └─ [M売上]
└─ [M売上]

6. ピボットテーブルの設定

  1. ピボットテーブルを選択し[ピボットテーブル分析]-[ピボットテーブル]-[オプション]-[レイアウトと書式]で"更新時に列幅を自動調整する"のチェックを外し[OK]。
  2. [ピボットテーブル分析]-[ピボットテーブル]-[オプション]-[表示]で"データのないアイテムを行に表示する"のチェックを外し[OK]。
  3. [デザイン]-[レポートのレイアウト]-[表形式]。
  4. [デザイン]-[小計]-[小計を表示しない]。
  5. [デザイン]-[総計]-[行と列の集計を行う]。
  6. 右の"ピボットテーブルのフィールド"で次のように項目をドラッグする。
    列：'カレンダ'[日付]。行：'商品区分'[区分]、'売上'[商品]。Σ値：[M商品区分]。
  7. ピボットテーブルの商品の▼-[その他の並べ替えオプション]で"降順"と[M商品区分]を選択し[OK]。
  8. ピボットテーブルの"その他"と"総計"をそれぞれ右クリックし[展開/折りたたみ]-[折りたたみ]。

5. サンプル

ダウンロード - 251013pt.xlsx

1.前置きの2)、4)の作例も入っています。

6. 余談

ChatGPTの利用経緯

最初入力表と出力表だけ与えてメジャーを作成させたのですが、繰り返しNGを指摘してもメジャーで正解が得られず、ピボットテーブルのグループ化など別の方法の提案を受けました。現時点では、VARに使用できない変数名(日本語や予約語)を使用したり、Excelでは使えないDAX関数を回答することが多いですが、その点は指摘をすれば直してくれます。
そこで、Power BIのYouTubeにあったPLを'区分'テーブルとSWITCHで求める方法を採用したメジャー式を分かる範囲で作成し、その修正を依頼したところ、正解に辿り着きました。そこから、総計の出し方やスライサー対応を自分で行ったものが、今回のメジャーです。
ChatGPTが回答したKEEPFILTERSは使用したことが無く、意味不明でしたので、ChatGPTに解説してもらいました。これによりCALCULATE関数の理解が深まりました。

スライサーやフィルターなどで自由に順位の対象を可変させる必要が無いのであれば、他の方法の方が良いかも知れません。

7. 参考

  • ChatGPTでTop5とそれ以外をその他で表示する方法を聞いてみた : Power BIちゃんねる : 2026/04/16
    https://www.youtube.com/watch?v=15L5c-UQcEw
    式を詳しく見ていないのですが、このままではExcelで使えません。しかし、Excelで使える関数に置き換えれば、この方がシンプルな気がします。ピボットグラフにするなら、この方法が必要でしょう。ExcelではエラーになるとChatGPTに伝えれば、修正してくれると思います。課題は、動的に変化するDAX表をExcelで実現するかかも知れません。DAXクエリを使うのでしょうか？

2025年10月17日 (金) Excel | | コメント (0)

« 2025年9月 | トップページ | 2025年11月 »