なんでもエクセル(Nandemo Excel)

何でもエクセルでやってしまう そこのあなた。ようこそ

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

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

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

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

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

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

方法1:アイテムアクセスを使う

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

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

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

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

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

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

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

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

方法3:展開前に 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) )

 

 

 

Power Query:要素数の異なる配列からテーブルを作る

個数の合わない配列(ジャグ配列)を、単純に1テーブルに変換しようとすると、エラーを起こしてしまう。例えば要素数が 10個の配列(例えば「{1..10}」)を 3分割した場合、最後の配列の要素数は 1つになる。これを Table.FromRowsでテーブルに変換しようとすると、最終行はエラー表示になる。


let     ソース = {1..10},     テーブル分割 = List.Split(ソース, 3),     テーブル変換 = Table.FromRows(テーブル分割) in     テーブル変換

これを防ごうと、足りない部分を「null」で埋めて……とやってもいいんだけど(自分は最初そうやろうとしていた)、List.Zipを使えばもっと簡単にエラーを回避できる。


let     ソース = {1..10},     テーブル分割 = List.Split(ソース, 3),     テーブル変換 = Table.FromColumns(List.Zip(テーブル分割)) in     テーブル変換

Microsoftの「List.Zip」のヘルプにも、ちゃんと「長さが異なるリスト(中略)を Zip 圧縮します」と書いてある。ヘルプはよく読まないとダメね。

learn.microsoft.com

Power Query:カタカナをローマ表記に変換する

「カタカナをローマ字表記に変換する」ネタは VBAでよく見かける。数式でも WebAPIの力を借りれば超絶簡単にできる。これをクエリでやってみよう。

単純には置換リストを作って置き換えればいいので、例外処理を除けばそんなに難しい話ではない。ただし、何をおいてもまずは変換リストを作る必要がある。

自分で作ってもいいけど、面倒なら下のコードを新規クエリの詳細エディタにコピー&ペーストでどうぞ。

変換リスト(ヘボン式基本)
Json.Document(Binary.Decompress(Binary.FromText("bZTNbuJAEITfhXMOZB0n7HG9gB0s2xFgUEhyGP+9Qd4/1Kw187m1F6Qu4Ot2d7k+Plaf38l66lcPq9XXw79qeNLn+HLX3t4bK6debq2cePlPlJMAyQhJAiQjJAmQDJD+V4AcGquKcWitKsQhErrN/wizagizaglrr453Nb9a2T0a2Y2hX47HnuXUy62VEy9HyORX6n7f5UtjVaEvO6sKcXm1qqa7YLgpPEqJmYc0tNs3VlW7/c6qard/tara7SN3dGEXR+xillMvt1ZOvIzhogUK+ih6oKCPogkK+qgLkBqQWU693Fo58TIgjwHyt2isnHq5tXLiZdjmJUBOgMxy6uXWyomXccchQEqabAiQkiYbAqTE47jncPVrY1WhrzurCnGNV3fRkO9wyKAkwKWcanzvc4X5oRpekoveYABlUwaeJs7Ae1bNKFEN3lo1zqj/b3E/zbONvF77uzFXlAeR14u3xUX1Ot1wSs17i7zOLXndZsnrNP8NPO0jB0/9c/DUP2eGqMZV1D9nmCgE4u8n7+tYCo+Q0faRLmoW2eMkB+AN162PeLXV+xj/Pnaq+Y6rBk+3OIKn3TAqtBtmhJ4FHh41fIXfa/oK/TV+BW9o/gre0O4qeE3z4rUcNE/B9FPCMW70PbwhPl7UXvwaXtO+anhN+6qZPKrB075q8DTPGTzt5wye9nM+MYV8/sBtmvAMt2miE9ymCU5wmy50YiL5LALQZxaAPmcA1AQlokQDlEwn1bCvBigRU27hT7dZ+NNpAYA9LezqdJ476usH"), Compression.Deflate))

文字数が多い順に置換するので、50音降順で並べている。

後は新規クエリを作成して入力表を読み込み、一文字ずつ置き換えしてから促音の処理を付け加えればいい。

ローマ字変換(基本)
let     ソース = 入力表,     変換列の追加 = Table.AddColumn(         ソース,         "ローマ字変換",         each List.Accumulate(             変換リスト,             [文字],             (x, y)=> Text.Replace(x, y{0}, y{1})         ),         type text     ),     変換列の編集 = Table.TransformColumns(         変換列の追加, {"ローマ字変換", each [             位置 = Text.PositionOf(_, "ッ"),             置換 = Text.ReplaceRange(_, 位置, 1, Text.Range(_, 位置+1, 1)),             条件選択 = if 位置=-1 then _ else 置換         ][条件選択]     }) in     変換列の編集

単純にはこれでおしまい。後は「『OO』は『O』に変換」のような例外処理を追加していけばいい。ヘボン式なら例外処理は後回しにして、リストを作って置換するだけでいい。

ローマ字変換ヘボン式
let     設定 = [         ヘボンA = {{"NB", "MB"}, {"NP", "MP"}, {"NM", "MM"}},         ヘボンB = {{"OO", "O"}},         ヘボンC = {{"OU", "OH"}},         ヘボンD = {{"UU", "U"}},         ヘボンE = {{"CCH", "TCH"}}     ],     置換 = (A as list, B as text)=>         List.Accumulate(A, B, (x, y)=> Text.Replace(x, y{0}, y{1})),     ソース = 入力表,     ローマ字変換列の追加 = Table.AddColumn(         ソース,         "ローマ字変換",         each List.Accumulate(             変換リスト,             [文字],             (x, y)=> Text.Replace(x, y{0}, y{1})         ),         type text     ),     ローマ字変換列の編集 = Table.TransformColumns(         ローマ字変換列の追加, {"ローマ字変換", each [             促音変換 = [                 位置 = Text.PositionOf(_, "ッ"),                 文字 = Text.Range(_, 位置+1, 1),                 置換 = Text.ReplaceRange(_, 位置, 1, 文字),                 条件選択 = if 位置=-1 then _ else 置換             ][条件選択],             ヘボン設定A = 置換(設定[ヘボンA], 促音変換),             ヘボン設定B = 置換(設定[ヘボンB], ヘボン設定A),             ヘボン設定C = 置換(設定[ヘボンC], ヘボン設定B),             ヘボン設定D = 置換(設定[ヘボンD], ヘボン設定C),             ヘボン設定E = 置換(設定[ヘボンE], ヘボン設定D)         ][ヘボン設定E]}     ),     列の選択 = Table.SelectColumns(ローマ字変換列の編集, {"ローマ字変換"}) in     列の選択集

訓令式の場合は、「ん」の後に母音(もしくは「Y」)がきた時は「'(アポストロフィ)」を入れる必要があるので「ん」の処理だけ後に回す必要がある。なので訓令式用の変換リストでは「ん」をわざと除いて、置換を後回しにする。

変換リスト(訓令式基本)
Json.Document(Binary.Decompress(Binary.FromText("bZTRbqpQEEX/xefeREup9vESFRoCNoKa2tuHg8Af3P+v+5TMWZz2xWS2umaY2eyPj8W//8lyvC0eFn8Wnw/fZf+kz2F9F9/eD7GcevkUy4mX/wY5MUhGSGKQjJDEIBkgt0eDXAGZ5NTLp1hOvBwg3eZXyCTHkEn+AVl6ebjL+SWW3SqS3WAtc7Sc5NTLp1hOvBwgo1+se7nL50OsCn3exaoQ59dY1XRnDDfao5SYuU+t3f4Qq2q338Wq2u1fY1Xt9oE7ONvFEbuY5NTLp1hOvIzhghEKuikYoaCbghEKuqkzSE03dQap6abOIDUhK4O0hKwM0hKyMkhLN60N0tCSa4M0tOTaIA0grjdISZP1Bilpst4gJSHPdvXLIVaFvuxiVYhLuLoLhnyHQ3rlAS7lVON7ny5MEdXwklz0BgMoojLwNHEG3rNqBopq8JaqcUb9f4v7aZ5t4N18LjBbVAfeTbwtLqrX6YpTat5r4HVuzus2c16n+a/gaR85eOqfg6f+OTNENa6i/jnDRCEQfj+Kh1TRuhEnogfYMOrkeKV13CPeZTU7hr8PnWq+1KrB0/KP4GkZzAYtg6Gg4WHaQcNX+L2mr9Bf41cwg+avYAYtq4K5NG8Bc2megnGnmvmiGmYQv4C5xK9hLu2rhrm0r5pRoxo87asGT/O04Gk/LXjaT8vUUQ1zab4W5tI8Dcyl/g3Mpfs0DCDV4PmEAs+nCnjqXyI41L9kFqmGWdW/RCi5mTndZmZOp6cH7GnmVafb3FGfXw=="), Compression.Deflate))

他はやることは同じで変換する内容が違ってくるだけ。長音がある時は「̂(サーカムフレックス)」を入れるルールがあるけど、これもリストの置換で問題なし。

ローマ字変換訓令式
let     設定 = [         訓令A = {             {"ん", "ン"}, {"ンA", "N'A"}, {"ンI", "N'I"}, {"ンU", "N'U"},             {"ンE", "N'E"}, {"ンO", "N'O"}, {"ンY", "N'Y"}, {"ン", "N"}         },         訓令B = {             {"A-", "Â"}, {"I-", "Î"}, {"U-", "Û"},             {"E-", "Ê"}, {"O-", "Ô"}         },         訓令C = {{"Î", "II"}}     ],     置換 = (A as list, B as text)=>         List.Accumulate(A, B, (x, y)=> Text.Replace(x, y{0}, y{1})),     ソース = 入力表,     ローマ字変換列の追加 = Table.AddColumn(         ソース,         "ローマ字変換",         each List.Accumulate(             変換リスト,             [文字],             (x, y)=> Text.Replace(x, y{0}, y{1})         ),         type text     ),     ローマ字変換列の編集 = Table.TransformColumns(         "ローマ字変換", each [             促音変換 = [                 位置 = Text.PositionOf(_, "ッ"),                 文字 = Text.Range(_, 位置+1, 1),                 置換 = Text.ReplaceRange(_, 位置, 1, 文字),                 条件選択 = if 位置=-1 then _ else 置換             ][置換],             訓令設定A = 置換(設定[訓令A], 促音変換),             訓令設定B = 置換(設定[訓令B], 訓令設定A),             訓令設定C = 置換(設定[訓令C], 訓令設定B)         ][訓令設定C]}     ),     列の選択 = Table.SelectColumns(ローマ字変換列の編集, {"ローマ字変換"}) in     列の選択集

以上。必要か分からない設定は分けてあるので、いらなければレコードの処理から設定を抜けばいい。設定用のテーブルを別に作って、切り替えできるように作ってもいいと思う。

Power Query:重複している行を抽出する

簡単かと思ってやってみたら、意外と苦戦するかもしれない。「重複を省いて抽出」ではなく「重複している行を抽出」したい場合、どうすればいいか。

こういう時、エクセル感覚だと「それぞれの行に、一つ上までの行を参照する COUNTIF関数を仕込んで……」みたいな発想になりがちだが、これを Power Queryに持ち込むと碌なことにならない。間違っても、List.Generateや List.Accumulateを使ってはいけない。こういう時は「最初にグループ化」がマストなのである。

例によって左側のテーブルは「テーブル1」としてクエリを作成済みとする。

let
    ソース = テーブル1,
    行のグループ化 = Table.Group(
        ソース, {"列1"}, {"テーブル", each _, type table}
    ),
    リスト抽出 = List.Select(
        行のグループ化[テーブル], each Table.RowCount(_)>1
    ),
    テーブル変換 = Table.Combine(リスト抽出)
in
    テーブル変換

グループ化については「行のカウント」でやると抽出はしやすいものの、後で基準となる列以外はどうするんだという話になる。今回は「すべての行」をグループ化するのがいいかと思う。

この方法なら数十万件でも数秒で抽出できる。COUNTIFの作業列を並べるような方法は今後使わなくていい。

Power Query:リストを使って抽出する

クエリの中で複数のキーを指定して抽出したい時、抽出用リストがある場合はそれを利用するにこしたことはない。

例えば「リストの値と等しい行を抽出したい」場合は、Table.SelectRowsの条件を下記のように記述すればいい。

each List.Contains(
    {"中央区", "西淀川区", "東淀川区", "住吉区", "東住吉区"}, [行政区名]
)

ただこれを「中央」「淀川」「住吉」を指定して、「リストの値を含む行を抽出したい」場合は、List.Containsでは上手くいかない。

この場合、ひとつには抽出条件に List.Accumulateを使う方法が考えられる。

(x)=> List.Accumulate(
    List.Transform(
        {"中央", "淀川", "住吉"},
        each x[支店名]="東京" and Text.Contains(x[行政区名], _)
    ),
    false,
    (x,y)=>x or y
)

これでいいんだけど、なんとなくしっくりこない。

ちょっと考え方を変えて、Splitter.SplitTextByAnyDelimiterで「リストの文字列で区切れるかどうか」をチェックしてみたらどうだろうか。

each [支店名]="東京"
    and List.Count(
        Splitter.SplitTextByAnyDelimiter(
            {"中央", "淀川", "住吉"}
        )([行政区名])
    )>1

こっちのほうが分かりやすい気がする。

learn.microsoft.com