Power Query：抜けている日付を補って表示する

画像の通りなんだけども、元のテーブルに値のない日付を追加して表示したい場合どうするか。

この場合、まずは「4/1」から「4/7」までが連番になっている日付テーブルを新規に作ったほうがいい。元のテーブルは「テーブル1」として読み込み済みだとして、

日付テーブル

let
    開始日 = List.Min(テーブル1[日付]),
    終了日 = List.Max(テーブル1[日付]),
    日付リスト = List.Transform(
        {Number.From(開始日)..Number.From(終了日)},
        Date.From
    ),
    テーブル変換 = Table.FromColumns(
        {日付リスト},
        type table[日付=date]
    )
in
    テーブル変換

これで日付テーブルが完成。

後はテーブル1に日付テーブルをマージして値列を展開して……いや待った待った。そんなことをする必要はないよね。全体の読み込みが発生する「マージ」とか「並べ替え」とかはなるべく使わないようにしたい。

こういう時は、縦結合してグループ化したほうが無駄がない。

くれぐれも日付テーブルが先ね。でないとグループ化した時に順番がおかしくなるから。

結合テーブル

let
    ソース = 日付テーブル & テーブル1,
    行のグループ化 = Table.Group(
        ソース, {"日付"},
        {"値", each List.Sum([値]), type nullable number}
    )
in
    行のグループ化

「null」ではなく「0」を返したい時は「List.Sum([値])」を「List.Sum([値]&{0})」とすればいい。

Power Query：セル内改行された値を行単位で分割する

表計算の世界で「やったら後で後悔することランキング」をとったら、絶対上位３以内に入ってくるのが「セルに複数の情報を入力する」だと思う。因みに後の２つは「同じ形式の表をシート単位で量産する」と「セル結合で入力を省略する」で間違いない。

都道府県マスタが左のような形で入力されている場合、右の表の形に組み替えしないといけない。

左の表が「テーブル1」として読み込み済みとして、まずはマウス操作中心でやってみる。

［ホーム］→［列の分割］→［区切り記号による分割］

特に指定しなければ、この時点で文字列の中でもっとも左側にある記号が勝手に区切り文字として指定される。今回の場合は強制改行記号の「#(lf)」が入る。「lf」は「Line Feed（改行）」の意味。修正するのは分割する列の数。

［区切り記号］→「カスタム」「#(lf)」
［分割後の列数］を多めの数（ここでは「10」）に変更する
→［OK］

すると改行記号が列方向に分割される。

ここまでくれば後は簡単。先頭列を指定して、その他の列をピボット解除すればいい。

「区分」列を選択して右クリック
［その他の列のピボット解除］

後は「属性」列を削除するだけ。以上。

これでいいといえばいいんだけども、M言語で記述すればもっとすっきりする。

別案
let
    ソース = テーブル1,
    リスト変換 = List.Transform(
        Table.ToRows(ソース),
        each List.Zip({
            {_{0}},
            Text.Split(_{1}, "#(lf)")
        })
    ),
    テーブル再変換 = Table.FromRows(
        List.Combine(リスト変換),
        type table Type.TableRow(Value.Type(ソース))
    ),
    下方向にフィル = Table.FillDown(
        テーブル再変換, {"区分"}
    )
in
    下方向にフィル

こんな感じ。このやり方だと、複数列にも対応させやすい。

こういう形の表だと、最初の方法では面倒なことになるけど、

別案2

let
    ソース = テーブル1,
    リスト変換 = List.Transform(
        Table.ToRows(ソース),
        each List.Zip({
            {_{0}},
            Text.Split(_{1}, "#(lf)"),
            Text.Split(_{2}, "#(lf)")
        })
    ),
    テーブル再変換 = Table.FromRows(
        List.Combine(リスト変換),
        type table Type.TableRow(Value.Type(テーブル1))
    ),
    下方向にフィル = Table.FillDown(
        テーブル再変換, {"料理名"}
    )
in
    下方向にフィル

これで済む。「リスト変換」のリストに列を増やしただけ。

誰もこんな作り方しないって？　いやいや、ありえなような表を作っちゃうのが Excelユーザーだから。

数式/関数：マージと展開を数式でやってみる

２つのテーブルをマージして必要な値を展開する。クエリでやれば超簡単なんだけども、これをあえて数式でやってみる。

因みにクエリでやるなら「大分類」でマージして「値」を展開するだけ。マウスの操作だけで読み込みまでいける。書くまでもないけど一応コードも書いておく。

クエリ版
let
    ソース = 表1,
    クエリのマージ = Table.NestedJoin(
        ソース, {"大分類"},
        表2, {"大分類"},
        "表2"
    ),
    緩衝テーブル = Table.Buffer(
        クエリのマージ
    ),
    マージ列の展開 = Table.ExpandTableColumn(
        緩衝テーブル, "表2", {"値"}
    )
in
    マージ列の展開

これを数式でやろうと思うと、Microsoft365前提でもそれなりに面倒ではある。

数式版
=REDUCE(
    HSTACK(表1[#見出し],"値"),
    SEQUENCE(ROWS(表1[大分類])),
    LAMBDA(
        x,y,
        LET(
            _c1,INDEX(表1,y,0),
            _c2,INDEX(表1,y,1),
            _flt,FILTER(表2[値],_c2=表2[大分類]),
            VSTACK(x,IFERROR(HSTACK(_c1,_flt),_c1))
        )
    )
)

OFFSET関数は嫌いなので REDUCE関数の第二引数には行番号の配列を入れた。FILTERで抽出した結果を VSTACKで縦結合している。

それにしても、LET関数を LAMBDA関数の中に書くのって何となく変な感じがするね。

数式/関数：空白行で仕切って連番を入れる

やってみて「あれ？」と思ったので記事にすることにした。画像のように空白行で区切られたデータの横に連番を振りたい時。

先頭が必ず空白行で、範囲に予め数式を入れておけばいいのなら

=IF(A1="","",SUM(INDEX(B:B,ROW()-1),1))
数式を下方向にコピー

これでいい。ただオートフィルコピーではなく、スピルで表現してみようと思ったのでやってみたら……「#VALUE!」エラーが出てしまった。

失敗例

=SCAN(
    "",
    TAKE(A:A,XMATCH("*?",A:A,2,-1)),
    LAMBDA(x,y,IF(y="","",SUM(x,1)))
)

原因を調べてみると「SUM(x,1)」で初期値の「""」が数値化されていないことが分かった。なんで？

「=SUM(A1,1)」とした時は、「A1」に文字列が入っていてもちゃんと「1」と答えを返すのに。疑問に思いつつも、「SUM(x,1)」を「SUM(0&x,1)」としたら上手くいった。

因みに Googleスプレッドシートの場合はこれでもダメで、ちゃんと数値化の演算を入れる必要がある。

Googleスプレッドシートの場合

=SCAN(
    "",
    A1:A,
    LAMBDA(x,y,IF(y="","",SUM(--(0&x),1)))
)

横着するなという話ではあるんだけど、通常と違う仕様になっていると若干とまどうものがある。

Power Query：マージ列の展開時に行の順番がずれないようにする

クエリのマージを実行した後で、結合した列を展開（Table.ExpandTableColumn）した時に、行の順番がずれてしまったことはないだろうか。

例えば下のようにテーブル1とテーブル2をマージして「テーブル2」列から必要な列を展開すると、

「名前」列が元の順番と変わってしまう場合がある。

仕様に文句をいっても仕方がないので、これが起きないようにするにはどうすればいいか考えてみた。

まず一つ目の方法。そもそも「クエリのマージ」を使わずにアイテムアクセスで必要情報を呼び出してやったらどうか。

方法１：アイテムアクセスを使う

［列の追加］→［カスタム列］
= try テーブル2{[名前=[名前]]}[ID] otherwise null
→［OK］

当たり前だが、カスタム列の追加なのでこの場合は行の順番が崩れない。

ただしこのやり方はパフォーマンスがかなり落ちる。ついでにいうと複数列展開したい時に不便である。

という訳で次の方法。必要行を抽出する関数をカスタム列に入れ込んでやったらどうか。

方法２：関数を使って抽出する
［列の追加］→［カスタム列］
= (x)=> Table.SelectRows(テーブル2, each [名前]=x[名前])
→［OK］
表示された式の「each (x)=>」を「(x)=>」に修正する。

やはりこれでも順番はずれない。

この方法、関数を工夫すれば複数列の展開にも対応させられる。ただし、Table.SelectRowsを使うのでデータ量が多くなるとこれまたメモリの消費が激しい。

という訳で３つ目の方法。そもそも展開した時に行がずれるのは、マージした時のいらない処理が再評価されているからなんだから、一度 Bufferをかましてやればいいんじゃなかろうか。

方法３：展開前に Table.Bufferをかませる

    緩衝テーブル = Table.Buffer(ソース)

この後に列を展開。実験結果は下記の通り。

ほらやっぱりずれない。

これ多分、事前に「インデックス列を追加する」でも防げるかも？

数式/関数：月齢を指定してカウプ指数を計算する

子どもが産まれたばかりなもので、カウプ指数から肥満度を計算するのに数式でどうやるか考えてみた。

カウプ指数の計算は、BMI値と同じなんだけど判定基準が異なる。なので月齢に応じた対応表がまずは必要になる。

こういう表を作る時に注意したいのは、「後でどういう計算をするか」を先に考えてから作るってこと。時々ネットで見つけた表を見たまんまで作ってから「さて、どうしようか」と悩み出す人がいる。後で計算に使うんだから「生後3か月～16ヶ月未満」みたいな文字列で書いてしまってはいけない。計算に使うなら「数値で入力」「単位は統一」は絶対。後者については小学校の算数で習ったはずなのに、大人になると忘れてしまう人が結構いる。

これ絶対。「ヶ月」も表示形式のユーザー定義で表現しよう。

今回は判定表にテーブル書式（テーブル名「T_判定表」）を設定しているけど、テーブル書式の先頭行は必ず文字列になってしまうので「見出し行」はあえて非表示にしている。

対応表ができたら、次は判定表を用意する。これも文字列だけだと使いづらいかもしれないので、必要あろうがなかろうが横に数値を入れておこう。少なくとも入れて損することはない。

ここまでできたら最後に計算表を作る。

Z3に月齢、Z4に体重、Z5に身長を入力するセルを用意する。分かりやすいようセルに名前定義しておこう。

カウプ指数（BMI値）
=IF(COUNT(月齢,体重,身長)<3,"",ROUND(体重/身長^2*10,0))

続いて判定。せっかくテーブル書式を設定しても、古いバージョンだとこういう時、ところどころセル番地での参照が入ってしまう。

判定
=IF(
    OR(月齢<3,月齢>60),
    "測定範囲外",
    INDEX(
        W4:W8,
        INDEX(
            B4:T10,
            MATCH(月齢,A4:A10),
            MATCH(カウプ指数,B3:T3)
        )
    )
)

Microsoft365ならこんな感じで範囲を指定できるので便利。

判定（Microsoft365の場合）
=IF(
    OR(月齢<3,月齢>60),
    "測定範囲外",
    INDEX(
        T_判定リスト[判定],
        INDEX(
            DROP(T_対応表,2,1),
            MATCH(月齢,DROP(TAKE(T_対応表,,1),1)),
            MATCH(カウプ指数,DROP(TAKE(T_対応表,1),,1))
        )
    )
)

数式/関数：住所を分割する

住所から郵便番号を検索したり、郵便番号から住所を検索したりは、WebAPIの力を借りれば比較的簡単にできるようになった。今回はそれらの合わせ技。手入力された住所情報を「都道府県名」「市区町村名」「地名」「番号」「それ以外」に分割する。

まずはテーブルの「住所」列に入力された住所情報から郵便番号を検索する。

郵便番号
=IF([@住所]="","",WEBSERVICE(
    "https://api.excelapi.org/post/zipcode?address="&ENCODEURL([@住所])
))

ExcelAPIの力を借りれば、ここはすんなり終わる。同様に郵便番号から住所を検索して「都道府県名」「市区町村名」「地名」をそれぞれ表示させる。

都道府県名
=IF([@住所]="","",WEBSERVICE(
    "https://api.excelapi.org/post/address?zipcode="&[@郵便番号]&"&parts=1"
))
市区町村名
=IF([@住所]="","",WEBSERVICE(
    "https://api.excelapi.org/post/address?zipcode="&[@郵便番号]&"&parts=2"
))
地名
=IF([@住所]="","",WEBSERVICE(
    "https://api.excelapi.org/post/address?zipcode="&[@郵便番号]&"&parts=3"
))

やること一緒なんで、ここまではさらっと。最後が若干面倒で、番号とそれ以外を分割する。古いバージョンだとこれにやたらと手間がかかるんだけど、Microsoft365ならそれほどでもない。

番号
=LET(
    _x,REPLACE([@住所],1,LEN(CONCAT(テーブル1[@[都道府県]:[地名]])),""),
    _y,TEXTSPLIT(_x,VSTACK(SEQUENCE(10,,0),"-","丁目","番","号","番地"),,TRUE),
    IFERROR(INDEX(TEXTSPLIT(_x,_y),1),_x)
)

ここまでくれば最後は簡単。REPLACE関数で左からそれまでの文字数分だけ取り除いてやればいい。

ビルマンション名他
=REPLACE([@住所],1,LEN(CONCAT(テーブル1[@[都道府県]:[番地]])),"")

Microsoft365の場合ならテーブルを使わずに全部スピルで表示させられなくもない。

一括で分割する場合
=LET(
    _rng,A2,
    _url,"https://api.excelapi.org/post/",
    _add2zip,WEBSERVICE(_url&"zipcode?address="&ENCODEURL(_rng)),
    _zip2add,WEBSERVICE(_url&"address?zipcode="&_add2zip&"&parts="&{1,2,3}),
    _x,REPLACE(_rng,1,LEN(CONCAT(_zip2add)),""),
    _y,TEXTSPLIT(_x,VSTACK(SEQUENCE(10,,0),"-","丁目","番","号","番地"),,TRUE),
    _add1,IFERROR(INDEX(TEXTSPLIT(_x,_y),1),_x),
    _add2,REPLACE(_x,1,LEN(_add1),""),
    HSTACK(_zip2add,_add1,_add2)
)