Power Query
Microsoft365に追加された関数を使うと、今まで数式では手間がかかっていたことが簡単にできるようになった。 左のテーブルから右のようにグループで分けて名前を表示したい場合、UNIQUE関数で見出しを作って、E2に FILTER関数を入れて右にコピーして……とや…
他人が作った在庫管理表を見ると、大体の場合「在庫」という列が右端についている。 ただこれだけでは「在庫が減っているな／増えたな」は把握できても、「いつ仕入れたものがどれだけ残っているのか」は分からない。単価が変動するものの場合は「いつ仕入れ…
CSVファイルをクエリで読み込みたいんだけど、ファイル名が毎回違ってて困ってるんだよね、という人のためのページ。 どうして困るのか。それは、CSVファイルの読み込みを「テキストまたは CSVから」から行うからだ。 「テキストまたは CSVから」でファイル…
たまに整形されていないテーブルから何行か飛ばして抽出したいと思うことがあるかもしれない。「何行飛ばし」みたいな抽出の仕方は、ずれるととんでもないことになるので可能な限りは避けたいものである。とはいえフラグが無ければしょうがない。 上の画像で…
テーブルをランダムに並び替える方法は、前にやったんだけども、今回はリストでやってみる。 まずはリストを用意する。 リスト1 = {"A".."Z"}, ここから同じ行数の乱数リストを作る。 乱数リスト = List.Random(List.Count(リスト1)), ２つのリストをまとめ…
２つのテーブルを比較して変更箇所をチェックしたい時、ぱっと思い付くのは条件付き書式で変更箇所に色を入れる方法だろうか。条件付き書式は同じブック内でないと比較できないが、COMアドインの「Inquire」をアドイン（Excel2013以上）してやれば「ファイル…
今回は「テーブルの組み換えＡ」の完全な逆パターン。 こういう縦並びのデータを組み換えて、 グループ別で横並びに組み替える。 普通に横並びにするだけなら「行のグループ化＋連番追加＆列のピボット」で済むけども、「品名」と「数量」のように複数の列が…
入出力の区別がない表の場合、一覧表形式になっていることが多い。そしてそれがシート単位で増幅されていく悪循環に陥りやすい。 それは置いといて、これを列ごとに項目がまとまったテーブル情報に組み換えしたい時。横並びのデータが１種類だったら列のピボ…
例えば住所を地域別に分類したい時、アプローチの方法は色々ある。アイテムアクセスを使うか列のマージを使うか。一長一短あるので使いどころ次第かと思う。 最初に「住所一覧」と「地域区分」のテーブルがあるものとして、まずはそれぞれのクエリを作成する…
今回は「行のグループ化」のその４。その３はグループ化の際に「区切り文字を使って１つの文字列にまとめる」方法を紹介したけど、今回は個別に横に並べたい時の話。 これまたグループ化だけでは完結しないので、多少加工が必要になる。 まずは「区分」列で…
今回は「行のグループ化」のその３。その２はピボットテーブルではできない「個別の行数のカウント」を取り上げたけど、今回は機能には用意されていないことをやってみる。 このように区分（グループ）ごとに品目を取りまとめたい時。グループ化の操作には「…
前回これで最後と書いた癖に、もう一つ追加。 列のピボットで作成したマトリクス表に「集計行」を追加したい時どうするか。 「テーブルの最下行に１行追加ってどうやるの？」と悩んだ方は、Power Queryの便利な機能を一つ忘れている。「行列の入替」である。…
Excelで時々困るのが、「重複を省いて件数をカウントしたい」時。ダブっているものを１つとして計算したい時、ピボットテーブルでは単純に集計できない。 「田中太郎：3」、「鈴木一郎：2」になってくれないといけないのに、ピボットテーブルで集計すると個…
先日、質問掲示板に Excelで現金出納帳を作っている人の質問があったんだけど、その人の出納帳には右端に残高列があって、そこから月ごとの残高が計算できなくて困っているという質問だった。……いや計算に使えなくて困るくらいならそんな列いらんでしょうよ…
引き続き「列のピボット」。前回は縦軸用の番号列の作り方をやったけど、今回はｎ行一組のデータを、列のピボットを使って組み替えてみたい。 ｎ行（画像だと４行）で一組のデータ（もちろんデタラメ）があるとして、 これを１行ずつのデータに組み換える場…
前回の続き。そこでもちらっと書いたけど、列のピボットを実行する時には、必ず縦横方向の軸となる値が必要になる。 この画像の場合だと、テーブル1には横軸となる「班」列はあるものの、テーブル2の「連番」を構成する縦軸用の列がない。このまま列のピボッ…
今回は「列のピボット」を紹介する。Excelでいうピボットテーブル的な集計がしたい時に使う機能で、要は「列のピボット解除」の逆パターン。よく使う機能なんだけど、実のところ集計に使われることは少ないかと思う。 左表の「属性」列を横軸にして、右表の…
これ単独でページ作るのもどうかと思うけど、クエリを作る過程でインデックス列（連番列）があると後々の計算の都合がいい場面が多々ある。実際マウス操作で行う場合、これなしでは先に進めない処理がいくつもある。グループ別に組み換えする時とか。 その連…
例えば「1,2,3,5,6,8,10,11,12,17」みたいな書き方だと分かりにくいし、文字数も多くなる。「1-3,5-6,8,10-12,17」みたいにまとめたい時、どうすればいいか。いや数式でやってもいいんだけど（やる人いるかな）、あえてクエリでやる場合どうするかを考えてみ…
Excel上なら、TEXT関数で「"gggee年mm月dd日"」とすれば簡単に済む話なんだけど、Power Queryの Date.TeText関数は和暦には対応していない。 画像のような和暦表示の列を作りたい場合、カスタム列の追加で「= Date.ToText([日付], "gggee/MM/dd"」とすると、…
そもそも「クエリのマージって何」という人もいるはずなんで書いておくと、２つのクエリ（もしくはステップ）を、それぞれ対象となる列を指定して１つにまとめること。Excel的にいうなら「VLOOKUPで別テーブルのデータをひっぱってくること」だ。これいうと…
先週、絶対一致検索にアイテムアクセスが使えることを書いたけど、それなら近似値検索（近似一致検索）がしたい時はどうするのかという話に当然なるかと思う。 数式でやるなら簡単で、画像のような採点表がある場合、同じテーブルに「評価」列を作って、 =LO…
タイプリストからタイプレコードへの変換。あるいはタイプレコードからタイプリストへの変換。これってどうやるんだろうと色々試してみた。 TypeList = { {"日付", Date.Type}, {"テキスト", Text.Type}, {"数値", Int64.Type} } 例えばこんなリストがあると…
Poewr Queryで２つのクエリを紐づけする場合は「クエリのマージ」を使うのが一般的。そっちのほうが処理負担も少ない。 売上データの「ID」を使って、名前マスタの「名前」を結合する（ついでに集計する）場合、 ソース = 売上データ, クエリのマージ = Tabl…
置換リストを使ってデータを置き換えする場合、方法としては色々考えらえるけどリストから変換するのがいいかと思う。 画像のようなテーブルがある場合、ひとまず「氏名テーブル」と「置換テーブル」は読み込み済みとして、［ホーム］→［詳細エディタ］に下…
パワークエリで論理値（Logical型の「true」「false」）を数値化するのってどうやるんだろうと思って色々試していた。ワークシートの数式だったら「(A1="あ")*(B1="A")」みたいなことが簡単にできるのに。論理演算はできないみたい。 だめなやつ 計算 = ([Co…
年齢計算は初日を参入するので（年齢計算ニ関スル法律の第一項）、誕生日の前日には歳が繰り上がる必要がある。なので正確に計算する前に、起算日を生年月日の前日にしてしまったほうがいい。Excelでいうと、DATEDIF関数が初日不算入なので「開始日-1」か「…
Excel使う人の多くは「シート」で同じ形式の表を分けてしまう。これやると大半が失敗に繋がるんだけど、どうしてもこれが止められない人が多い。色々理由があるのは分かるんだけど、あえて「それは間違っている」と断言しておく。 とはいえやってしまったも…
テーブルに縦方向に入力したデータをグループ分けして横に並べたい時。 Excelを使っている人の多くは、この逆パターンで悩むことが多いんだけど、そっちは Power Queryだと「列のピボット解除」で一発解決するので、敢えて逆パターンを取り上げる。 横並びの…
土日の判定は簡単なんだけど、祝日の判定には祝日リストが必要。これは Excelと全く同じ。ただ国民の祝日一覧は「年」さえ指定すれば簡単に作れる。 祝日を判定するには祝日一覧がないとなんともならないので、まずはそこから。今回は当年に前後一年を加えた…
