前回これで最後と書いた癖に、もう一つ追加。 列のピボットで作成したマトリクス表に「集計行」を追加したい時どうするか。 「テーブルの最下行に１行追加ってどうやるの？」と悩んだ方は、Power Queryの便利な機能を一つ忘れている。「行列の入替」である。…

Excelで時々困るのが、「重複を省いて件数をカウントしたい」時。ダブっているものを１つとして計算したい時、ピボットテーブルでは単純に集計できない。 「田中太郎：3」、「鈴木一郎：2」になってくれないといけないのに、ピボットテーブルで集計すると個…

先日、質問掲示板に Excelで現金出納帳を作っている人の質問があったんだけど、その人の出納帳には右端に残高列があって、そこから月ごとの残高が計算できなくて困っているという質問だった。……いや計算に使えなくて困るくらいならそんな列いらんでしょうよ…

引き続き「列のピボット」。前回は縦軸用の番号列の作り方をやったけど、今回はｎ行一組のデータを、列のピボットを使って組み替えてみたい。 ｎ行（画像だと４行）で一組のデータ（もちろんデタラメ）があるとして、 これを１行ずつのデータに組み換える場…

前回の続き。そこでもちらっと書いたけど、列のピボットを実行する時には、必ず縦横方向の軸となる値が必要になる。 この画像の場合だと、テーブル1には横軸となる「班」列はあるものの、テーブル2の「連番」を構成する縦軸用の列がない。このまま列のピボッ…

今回は「列のピボット」を紹介する。Excelでいうピボットテーブル的な集計がしたい時に使う機能で、要は「列のピボット解除」の逆パターン。よく使う機能なんだけど、実のところ集計に使われることは少ないかと思う。 左表の「属性」列を横軸にして、右表の…

これ単独でページ作るのもどうかと思うけど、クエリを作る過程でインデックス列（連番列）があると後々の計算の都合がいい場面が多々ある。実際マウス操作で行う場合、これなしでは先に進めない処理がいくつもある。グループ別に組み換えする時とか。 その連…

名前だけでは分かりにくいけど、クエリ内で何かを集計したい時に使う機能。 Power Queryでよく使う機能といったら、「クエリのマージ」「列のピボット解除」「行のグループ化」「インデックス列の追加」「列のピボット」の５つ。今回はその３つ目。 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"」とすると、…

マトリクス表（縦横配置の一覧表）を１列１情報の形に組み替えするには、クエリの「列のピボット解除」がとにかく便利。操作も簡単なので、クエリを使うなら絶対に覚えておきたい。 「テーブル1」があるとして（データ範囲を名前定義する形でもいい）、ここ…

そもそも「クエリのマージって何」という人もいるはずなんで書いておくと、２つのクエリ（もしくはステップ）を、それぞれ対象となる列を指定して１つにまとめること。Excel的にいうなら「VLOOKUPで別テーブルのデータをひっぱってくること」だ。これいうと…

先週、絶対一致検索にアイテムアクセスが使えることを書いたけど、それなら近似値検索（近似一致検索）がしたい時はどうするのかという話に当然なるかと思う。 数式でやるなら簡単で、画像のような採点表がある場合、同じテーブルに「評価」列を作って、 =LO…

データをランダムに並べ替えるには「重複しないランダム値」が必要で、Excelのワークシート関数なら「=RAND()」を並べれば簡単に作れる（表示された RAND関数の結果は絶対に重複しない）。なので、Power Queryにも「Number.Random」があるから、同じくカスタ…

タイプリストからタイプレコードへの変換。あるいはタイプレコードからタイプリストへの変換。これってどうやるんだろうと色々試してみた。 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と全く同じ。ただ国民の祝日一覧は「年」さえ指定すれば簡単に作れる。 祝日を判定するには祝日一覧がないとなんともならないので、まずはそこから。今回は当年に前後一年を加えた…

除外リストを元にして、不一致のものだけクエリで抽出してみよう。これができれば、この逆パターンももちろんできるようになる。 この手の処理は、単純には「クエリのマージ」を実行するだけでできる。 サンプル画像の場合は「商品テーブル」から「除外テー…

作成したリストを微妙に編集したい時、「List.Transform」を知らないといきなり立ち往生することになってしまう。 例えば数値のリストを文字列のリストに変えたい時。 リスト = {1..10}, 文字列変換 = List.Transform(リスト, Text.From) これだけなんだけど…

左の表から右のような集計表を作りたい場合。「そもそも入力表にセル結合なんていれんなっ！」といいたい気持ちをぐっと押さえてクエリでデータクレンジングしてしまおう。 まずこういう表にはテーブル書式なんて設定されていないので、クエリで利用する時は…

クエリでやるのがそこそこ面倒なのが「上のセル（もしくは下のセル）との比較」である。累計は「上のセルの値を足していく」処理なので、どうしても上下位置を指定して値を取得する必要がある。安直な方法だと処理が重くなるので注意。 今時、Microsoft365な…

List.Accumulateや List.Generateで初期値として「見出しが同じ空っぽのテーブル」が欲しい時がある。それの作り方。 空のテーブルを作る方法はいくつかある。基になるテーブルがあるなら、Table.FirstNで「0」行を指定しても作れるけど、今回はタイプリスト…

ご存知とは思うけど、日付から「日曜日」のような曜日名を返すには「Date.DayOfWeekName」を使えばいい。これはマウスの操作だけでできる。 日付列を選択して ［列の追加］→［日］→［曜日名］ 上記は日付列の型が「Date」でも「DateTime」でも問題ない。 で…

最下行の値や最右列の値を取得したい時。 VBAなら「Cells(Rows.Count,1).End(XlUp).Row」でいいわけだけど、数式だと若干面倒。昔からある方法だと 近似値検索の仕様を逆手に取って =AGGREGATE(14,6,MATCH({1E15,"ーー"},A:A),1) とする方法もあるにはあるけ…

土日祝日を除外したｎ営業日後の日付を返したい場合、Ｍ言語だとワークシート関数の WORKDAY関数に該当するものがないので、それなりに苦労するかもしれない。 とはいえ、「年間（別に何なんでもいいけど）のリスト」を用意して「規定の曜日」と「国民の祝日…