パワークエリでファイルを読み込むと、ソースの下に「変更された型（Changed Type）」が自動的に作成されます。この機能、邪魔だなと思うことないでしょうか。
もちろん判定してくれることに否やはないんですが、必要ない時に勝手に追加されると、思いもよらないエラーを起こすことがあります。
このおせっかい機能、邪魔だなと思ったら設定で切ることができます。意外と毎回手で消していた方も多いんじゃないでしょうか。
［ファイル］→［オプションと設定］→［クエリのオプション］
→［データの読み込み］ → 型の検出の「非構造化ソースの列の型とヘッダーを検出しない」を選択 →［OK］
これで設定完了です。試しにテーブルを作ってみると、
確かに型の検出ステップが追加されません。
もし後になって、「やっぱり型を検出したいな」と思ったら、下記の手順で検出できます。
検出したい列を選択して（全選択なら Ctrl + A）
［変換］→［データ型の検出］こうすれば、指定列の型を一括で検出できます。
基本的な機能なので、普通に使う分にはなんてことのない話なんですが。区切り文字を使って列を分割する際の話です。
機能としては至極単純で、区切りたい列を選択して「列の分割」を選ぶだけです。
区切りたい列を選択した状態で右クリック →［列の分割］→［区切り記号による列の分割］
「コンマ」を選択して→［OK］
これだけです。
きれいに分割されました。「区切り記号を選択するか入力してください」の選択欄には、文字列の左からみて一番初めにくる記号が勝手に設定されますので、別の記号名が表示されている場合は、適宜修正してください。
たったこれだけなんですが、一点注意が必要です。
右ペインの「適用したステップ」に追加された「区切り記号による列の分割」ステップを選択して、数式バーを確認してください。画像のようなテーブルの場合、そこには下記のように表示されるはずです。
= Table.SplitColumn(
ソース,
"スキル",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"スキル.1", "スキル.2", "スキル.3", "スキル.4", "スキル.5"}
)
「スキル.1」から「スキル.5」まで表示されていますね。これ、実は固定なんです。
つまり、スキルをたくさん持っている人（６つ以上）が後からあらわれた場合、クエリを更新しても「スキル.6」列は勝手には追加されません。
予め列をたくさん作っておいて、後で消す方法もありますが、それでも不安ですよね。私の知人は両手で数えきれないほどの資格を持っています。そういう人が、今後あらわれないとも限りません。
その場合は「列名」のリストをデータを基に作っておくといいでしょう。
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
カンマ区切り = Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
スキル最大値 = List.Max(List.Transform(ソース[スキル], each List.Count(カンマ区切り(_)))),
見出し = List.Transform({1..スキル最大値}, each Number.ToText(_, "スキル0")),
列の分割 = Table.SplitColumn(ソース, "スキル", カンマ区切り, 見出し)
in
列の分割繰り返しを避ける目的で、「カンマ区切り」の関数を別ステップに分けていますが、やっていることはほとんど変わりません。「スキル最大値」でスキル数が最大何個になるか計算して、それに合わせて列の「見出し」を別に作っています。
このやり方なら、たくさんスキルを持っている人があらわれても、スキルの数に合わせて分割する列が増えてくれます。
「自分にとっての備忘録を、ですます調で書くなんて変だ」との思いで、断定調で２年ほど続けてみたんですが、既に内容が自分宛の記事ではなくなりつつあります。すごく書きづらくなってきたので、本日を機に常体から敬体に変えていきたいと思います。
古い記事も、徐々にですが変更していきます。以前の形に慣れていた方は違和感があるかもしれませんが、何卒お許しください。
それにしても生成AIは便利ですね。「この文章を敬体にしてください」と書いてから、文章を貼り付ければ、私のつたない文章でも読むに堪えるものに変換していってくれます。
何かしらの項目を基準にして集計する際、「A」「B」「C」……とある項目の内、「B」のデータが一つもないと、集計結果のテーブルは「A」「C」……と、歯抜け状態になってしまいます。そういう場合は、予め用意した「A」「B」「C」……テーブルに、集計結果を入れ込んでやればいいですね。その集計する項目が「月」だった場合、「月毎の月初テーブル」が必要になります。
というわけで、今回は「月ごとの月初テーブル」を作ってみます。
月初テーブルlet 年 = 2024, 月初リスト = List.Transform( {0..11}, each Date.AddMonths(#date(年, 1, 1), _) ), テーブル変換 = Table.FromColumns( {月初リスト}, type table[月初=date] ) in テーブル変換
このようになります。ここで「年」はセルから読み込んでも問題ありません。
もし「4月～翌3月」を表示させたい場合は、月初リストの「{0..11}」を「{3..14}」に変更すればいいです。
「M言語、わかんないよっ！」って人は次の手順でやってください。
「Alt + F12（Windows）」で Power Queryエディタを開き 左ペインで右クリック［新しいクエリ］→［その他のソース］→［空のクエリ］
数式バーに「= {1..12}」と入力
［変換］→［テーブルへの変換］→［OK］
出来上がったテーブルにカスタム列を追加します。
［列の追加］→［カスタム列］をクリック
新しい列名：日付 カスタム列：= Date.AddMonths(#date(2024, 1, 1), [Column1]-1) →［OK］
「Date.AddMonths」は「月数（Column1列の値-1） を加算した日付を返す関数です。
カスタム列が追加できたら、「Column1」列を削除すればおしまいです。4月開始にしたい時は「[Column1]-1」を「[Column1]+2」に変更してください。
続いて、月末列を追加したバージョンです。
月末追加バージョンlet 年 = 2024, 月初リスト = List.Transform( {0..11}, each Date.AddMonths(#date(年, 1, 1), _) ), 月末リスト = List.Transform( 月初リスト, Date.EndOfMonth ), テーブル変換 = Table.FromColumns( {月初リスト, 月末リスト}, type table[月初=date, 月末=date] ) in テーブル変換
これだけです。Date.EndOfMonth関数は引数省略したので「each」もいりません。
最初の表に戻りますが、これらの２つのテーブルを作成すると、あとはマージ……ではなくテーブルを縦結合してグループ化してあげましょう。そのほうが読み込みの負荷が少ないですし、何もない月に「0」を表示させるのも簡単だからです。
ソース = 月テーブル & 集計結果, 行のグループ化 = Table.Group(ソース, {"日付"}, {"個数", each List.Sum([個数]&{0}), Int64.Type})
以上です。
先日、数式の回で書きましたが、ピボットテーブルのほうが簡単なので、改めて書き直します。ひとまず「現金出納帳に残高列はなくても大丈夫だよ」ということだけは繰り返しておきます。
どうしても残高が必要なら「差引列」を作ってそれの「累計」を出せばいいんですが、入力表に数式を入れると碌なことがありません。必要がないなら抜いたほうが安全です。
では、ひとまず完成図から確認しておきましょう。
上の現金出納帳から差引残高入りの月別収支表を作りたい場合です。前回と同じことを書きますが、現在進行形の残高ならテーブルに集計行を入れればいいだけなので今回は割愛します。
ではまずピボットテーブルの挿入から。
テーブルを選択した状態で ［挿入］→［ピボットテーブル］をクリック
すると「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが開きますので、「テーブル/範囲」にテーブル名が表示されているのを確認しつつ、ピボットテーブルの挿入先を指定してください。
今回は「既存のワークシート」で「G1」を指定しています。
この状態です。
ここから先は、右ペインに表示された「ピボットテーブルのフィールド」に必要なフィールド（列名）をカーソルのドラッグで追加していけばいいです。
「日付」フィールドを「行」にドラッグ（勝手に月単位にグループ化される）
「入金」と「出金」フィールドを「値」にドラッグ
これで残高列以外はほぼ出来上がりです。あとはこのピボットテーブルに「残高」フィールドを追加していきます。
ピボットテーブルを選択した状態で
［ピボットテーブル分析］→［フィールド/アイテム/セット］
→［集計フィールド］をクリック
「集計フィールドの挿入」ダイアログボックスが開くので
名前：「残高」
数式：= 入金 - 出金
→［追加］→［OK］
これで完成……ではありません。このままでは残高列がただの月別の差引にしかなっていません。最後に計算の種類を「累計」にしてあげる必要があります。
残高列上で右クリック「値フィールドの設定」
「計算方法」タブをクリック
「計算の種類：累計」を選択
→［OK］
こんな感じです。あとはいらない総計列を削除したり見た目を整えたりすれば、本当の完成です。
指定フォルダに入っている Excelファイルで、単純に「ブックの中のこのセルだけ」を読み込んで結合したい場合の手順をまとめてみました。
まずは参照用のフォルダに同形式のファイルを保存します。全部同じシート名・同じ配置が前提ですのでご注意を。
今回は参照するセルも「F6」固定としています。位置が異なる場合は、値を抽出する操作が別途必要になりますが、ここでは割愛します。
［データ］→［データの取得］→［ファイルから］→［フォルダーから］
参照用のフォルダを選択して［開く］
［結合］→［データの結合と変換］をクリック
サンプルとなるシートを指定して［OK］
これでヘルパークエリが勝手に作成されます。ここからはエディタ画面での操作になります。
覚えておいて欲しいのですが、ヘルパークエリで編集するのは基本「サンプル ファイルの変換」クエリだけということです。他は触ると元に戻せなくなる可能性もありますので、理屈が分かっていない人はノータッチ絶対でお願いします。
「サンプル ファイルの変換」クエリを選択 欲しい値を選択
必要な値を選択したら「ドリルダウン」を実行します。
値を選択した状態で右クリック［ドリルダウン］
「ドリルダウン」は「より詳細なデータに掘り下げること」を意味する言葉ですが、クエリでは「値を取り出す」くらいの意味で使われています。
この状態のまま結合してもいいんですが、後の流れを考えるならテーブルに変換したおくほうが手間が少ないのでお勧めです。
数値ツール［変換］→［テーブルへの変換］
これで簡単にテーブルに変換できます。
ところがお気づきでしょうか（画面左下）。テーブルへの変換が済むと、結合用のクエリ（今回の場合「参照」クエリ）がエラー表示になってしまいます。エラーの内容を見ると
「Expression.Error: テーブル列 'Book1'がみつかりませんでした。」となっています。これは「さっきとテーブルの形が違うんだけど！？」という意味のエラーです。ただ下記の操作をすれば取り除けますのでご安心ください。
適用したステップの最後に表示されている「変更された型」を選択
左の「×」をクリック
これでエラーが解消されます。先ほど「テーブルへの変換」をしていなかった場合は、もうひとつ上のステップも削除する必要があります。この辺りは好みですね。
後はブックにデータを読み込めばおしまいです。
［ホーム］→［閉じて読み込む］→［閉じて次に読み込む］
［既存のワークシート］を選択
読み込みたいセルを指定して［OK］
これで完成です。長々と書きましたが、慣れてしまえば数分で終わる作業だったりします。
いきなりですが、家計簿／お小遣い帳／現金出納帳に「残高列」って必要でしょうか。ネットでテンプレートを検索すると、必ずといっていいほど「残高/差引残高」列が右端にひっついています。以前にも同じ突っ込みを入れたことがありますが、これって何のためにあるんでしょう。
もちろん「=SUM(OFFSET([@残高],-1,0),[@入金]-[@出金])」で済むといえば済むんですが、OFFSETを入れると再計算がわずらわしいことになります。それに残高列を作り始めると、（使ってもいないのに）見た目を気にして「まだ入力していない行の残高を見えないようにしたい」とか「日付の区切り目にだけ残高を表示したい」とか、注文を付け始めるんですよ。そうなると数式がどんどん長くなっていきます。そして行削除や行挿入で数式の抜け漏れが発生して、余計なエラーが出るんです。多分「印刷イメージ」先行で表を考えているせいだと思います。
もちろん紙の出納帳だったら分かりますよ。手書きで計算するなら１個ずつ計算しておいたほうが後でチェックもしやすいですもんね。でもそれは「手書き」で「手計算」だからですよね。そろばんや電卓片手にやってた時代の名残りです。現金残高って残金を合わせる時以外いりませんよね。目的から考えたら、必要な時にだけ残高が分かればそれでよくないですか？
現時点の残高が知りたいだけだったら、集計行に入金計と出金計を入れて「=入金計-出金計」とやれば残高なんて簡単に計算できます。
正直、これでいいんじゃないでしょうか。
日や月ごとにチェックするとしても、残高を確認するのは「入力し終わった後」の話なので、いちいち「入力しながら確認する」必要なんてないはずです。表計算では「入力表には数式を入れない／出力表には手入力しない」が原則なので、日ごとの残高は出力用の表に作るべきだと思います。
日毎の残高表を別表で作るのなら話は簡単で、
G2：=UNIQUE(テーブル1[日付]) H2：=SUMIF(テーブル1[日付],G2#,テーブル1[入金]) I2：=SUMIF(テーブル1[日付],G2#,テーブル1[出金]) J2：=SCAN(0,H2#-I2#,LAMBDA(x,y,x+y))
月毎だったらこう。
G2：=UNIQUE(テーブル1[日付]-DAY(テーブル1[日付])+1)
H2：=SUMIFS(テーブル1[入金],テーブル1[日付],">="&G2#,テーブル1[日付],"<="&G2#+32-DAY(G2#+32))
I2：=SUMIFS(テーブル1[出金],テーブル1[日付],">="&G2#,テーブル1[日付],"<="&G2#+32-DAY(G2#+32))
J2：=SCAN(0,H2#-I2#,LAMBDA(x,y,x+y))スピルで表示させるならこれだけです。いっそ数式をひとつにまとめたっていいです。
もっというなら、ピボットテーブルで月単位にグループ化して収支をそれぞれ集計して、｢フィールド／アイテム／セット」で「= 入金 - 出金」の集計フィールド（累計）を追加してやれば、数式を使わなくても残高は集計できます。
現金残高は、自分が現金を締めたタイミングにチェックできるようにしておけばいいです。残高列なんていりません。
