Power Queryで作った売上日報を更新したら、代理店のすぐ下に置いたはずの内訳が、別の場所に移っていました。
「一度ちゃんとできたのに、どうして？」
ChatGPTと相談しながら作った表です。ようやく形になったと思っていたので、並び順が崩れたときは戸惑いました。
私の場合は、並べ替えに使う列の優先順位を変えると直りました。Excelでデータを更新した後も、希望する順番で表示されることを確認できました。
この記事では、実際に見直した数式と、確認した場所を図で紹介します。数式が苦手な方も、まずは「どの条件を先に使っているか」に注目してみてください。
※ 代理店名・金額・一部の列名は、公開用に置き換えています。図は架空データによる説明用です。実際のExcel画面をそのまま掲載したものではありません。
完成したはずの売上日報。更新したら内訳が別の場所に
私が作っていたのは、日々の売上を代理店別にまとめるExcel売上日報です。
その中で、ある代理店の売上に含まれる内訳を、その代理店のすぐ下に表示するようにしていました。
たとえば「A代理店」の下に「A代理店の内訳」があれば、全体の売上と、その一部を続けて確認できます。ところが、更新を続けているうちに、その並びが崩れてしまいました。
図1：起きた問題を架空データで再現。内訳はA代理店の売上に含まれるため、別の売上として足しません。
今回困ったのは、売上の抽出や集計そのものではなく、表示する順番です。
内訳が離れていると、「これはどの代理店の内訳？」と確認し直すことになります。見やすくするために作ったはずなのに、これでは困ります。
そこで、並び順がおかしくなった画面をChatGPTに送り、どこを確認すればよいか相談しました。
ChatGPTと確認して見つけたのは、並べ替えの設定
案内に沿ってPower Queryエディターを開き、「最終売上集計」というクエリを確認しました。
クエリは、データを取り込んだり加工したりする手順のまとまりです。今回は、完成する売上表に関係するクエリを開いています。
右側の「適用したステップ」から調べたのが、**「並べ替えられた行」**でした。
ここを選ぶと、数式バーに Table.Sort という数式が表示されていました。表を並べ替えるための関数です。Microsoft公式のTable.Sort解説でも、複数の列を使った並べ替えが紹介されています。
数式には、次の3列が指定されていました。
| 列名 | 何を決めるための列か |
|---|---|
| 最後表示 | 特定の代理店を一覧の最後に置く |
| 並び用売上 | 代理店と内訳を、ひとまとまりとして並べる |
| 内順 | そのまとまりの中で、代理店の後に内訳を置く |
名前だけを見ると難しく感じますが、どれも「希望する順番に並べるための目印」です。
そして、今回見直したのは、この目印を使う順番でした。
「内訳かどうか」より先に「どのまとまりか」を決める
修正前は、「最後表示」→「内順」→「並び用売上」の順でした。
これを、「最後表示」→「並び用売上」→「内順」に変更しました。
図2：変更したのは、2番目と3番目の条件。表の列を左右に動かす操作ではありません。
複数列の並べ替えでは、まず最初の条件で順番を決めます。その値が同じ行の中で、次の条件を使います。 さらに同じなら、3番目の条件を使います。これはMicrosoft公式の複数列の並べ替え説明でも確認できます。
つまり、内訳を表す目印を先に使うと、代理店の行と内訳の行が先に分かれてしまうことがあります。
今回必要だったのは、先に代理店と内訳を同じまとまりとして並べ、その中で「代理店→内訳」の順番を決めることでした。
架空の数字で見ると、仕組みが分かりやすい
ここからは、仕組みを理解するための例です。私の実際の補助列に入っている値や、値の作り方を示すものではありません。
図3：「並び用売上」は並べるための値。表示する売上金額とは役割が異なります。
この例では、A代理店の売上は30万円、その内訳は8万円です。
ただし、並べるための「並び用売上」には、どちらにも30万円を設定しています。同じ値を持たせて、続けて表示するためです。
そのうえで、「内順」をA代理店は0、内訳は1にします。
すると、並び用売上が同じ30万円の行の中で、0→1の順になり、A代理店の下に内訳が来ます。
逆に「内順」を先に使うと、0の代理店が先に集まり、1の内訳が後になります。図1の修正前のように、別の代理店が間に入るわけです。
数式の順番だけでなく、列に入っている値もセットで確認することが大切です。
実際に変更したTable.Sortの数式
修正前は、次の設定でした。列名の一部は記事用に変更しています。
= Table.Sort(
追加されたカスタム2,
{
{"最後表示", Order.Ascending},
{"内順", Order.Ascending},
{"並び用売上", Order.Descending}
}
)修正後はこちらです。
= Table.Sort(
追加されたカスタム2,
{
{"最後表示", Order.Ascending},
{"並び用売上", Order.Descending},
{"内順", Order.Ascending}
}
)変更したのは、「内順」と「並び用売上」の位置です。小さい順・大きい順の指定は変えていません。
Order.Ascending：小さい順に並べる指定Order.Descending：大きい順に並べる指定
追加されたカスタム2 は、並べ替える直前のステップ名です。自分のExcelで使う場合は、自分のクエリにある名前を使います。
また、「最後表示」などの列名はこの記事の表に合わせた名前です。数式を丸ごと貼るのではなく、自分の列名と、各列の役割を確認してから見直してください。
なお、「最初は正しく表示されていたのに、どの変化をきっかけに崩れたのか」までは特定できていません。今回確認できたのは、優先順位の変更によって希望する並びに戻ったことです。
自分のExcelで確認する場所と修正手順
同じように困っている方は、次の順で確認してみてください。以下はWindows版Excelを想定した案内です。表示名や配置はバージョンによって異なる場合があります。
1．対象のクエリを開く
変更する前に、ブックのコピーを保存しておくと元の状態と見比べられます。
Excelの「データ」タブから「クエリと接続」を開きます。対象のクエリを右クリックして「編集」を選ぶと、Power Queryエディターが開きます。
私の場合のクエリ名は「最終売上集計」でした。自分の表では、出力している表に関係するクエリを選んでください。
2．「並べ替えられた行」と数式バーを確認する
右側の「適用したステップ」で、並べ替えを行っているステップを選びます。数式バーに Table.Sort があるかを確認します。
図4：説明用に簡略化した再現図。数式バーが見えない場合は「表示」タブから表示を確認します。
「適用したステップ」が見えない場合は、「表示」タブの「クエリの設定」も確認してください。
3．列の値を見てから、優先順位を変更する
代理店と内訳を続けて表示したいなら、次の2点を確認します。
- 同じまとまりにしたい行に、同じ並べ替え用の値があるか
- まとまりを決める条件より先に、代理店と内訳を分ける条件が置かれていないか
今回の私は、数式バーで「並び用売上」を「内順」より前へ移しました。
変更を確定したら、エディター上で希望する順番になったか確認します。途中のステップを見ている場合は、最後のステップの表示も確認してください。
4．Excelで更新した後も確かめる
希望どおりに並んだら、「ホーム」から「閉じて読み込む」でExcelへ戻り、出力された表を確認します。
さらに、Excelの「データ」タブから「すべて更新」を実行し、更新が完了してから、もう一度代理店と内訳の位置を確認します。
図5：確認のゴールは、エディターの見た目だけでなく、更新後の出力結果です。
私の表では、修正後に希望する並びへ戻り、Excelで更新した後も、内訳が指定した代理店の直下に表示されました。
一度できていた表だけに、また崩れるのではと気になりましたが、更新後の表示まで確認できて、ようやく安心できました。
ただし、今回確認したデータでの結果です。新しい代理店や内訳が加わった場合などは、その条件でも確認が必要です。
優先順位を直しても、並び順がおかしいときは？
同じ症状でも、確認すべき場所が同じとは限りません。
| 確認すること | 見るポイント |
|---|---|
| 補助列の値 | 代理店と内訳で、同じまとまりを表す値になっているか |
| 数値の扱い | 金額や順番の列が、数値ではなく文字列になっていないか |
| 同じ値の代理店 | 別の代理店にも同じ並び用売上があり、内訳と混ざる条件になっていないか |
| 並べ替え後の処理 | 後ろにグループ化・マージ・重複削除などがないか |
特に、別の代理店の「並び用売上」も同額になる場合は、売上の値だけでは代理店ごとのまとまりを区別できません。代理店を識別する列など、まとまりを区別する条件を「内順」より前に加える設計も検討します。全体の並びにどう影響するか、実データで確認してください。
また、Microsoftは、グループ化・マージ・重複削除などによって、それ以前の並び順が保持される保証はないと説明しています。そのような処理の後に、表示用の並べ替えを行うことが確認候補になります。詳しくはMicrosoft公式「一般的な問題：並べ替えの保持」を参照してください。
これは、今回見直した「条件の優先順位」とは別の話です。「Power Queryは更新すると必ず順番が崩れる」と考えるのではなく、自分のクエリの条件と処理の順番を確認していきます。
ChatGPTと直せた。でも、最後は更新して確かめる
今回、数式を一から書き直したわけではありません。並べ替えに使う列の順番を見直すことで、希望する表に戻せました。
相談するときは、取引先名や金額などを伏せた画面と一緒に、「どうなっているか」「本当はどう並べたいか」「今の数式」を伝えると、確認したい点が明確になります。
AIが出した案を入れて終わりにせず、自分の表で直ったか、更新しても正しく表示されるかまで確かめる。 今回の経験で、ここまでが修正なのだと実感しました。
一度完成した表がおかしくなると、がっかりします。それでも、全部やり直す前に、まずは「並べ替えられた行」を開いてみてください。今回のように、一部分の見直しで直せることもあります。
売上日報を作った全体の流れは、ChatGPT×Power Queryで売上日報を効率化した実践記事で紹介しています。
