「カタカナをローマ字表記に変換する」ネタは VBAでよく見かける。数式でも WebAPIの力を借りれば超絶簡単にできる。これをクエリでやってみよう。 単純には置換リストを作って置き換えればいいので、例外処理を除けばそんなに難しい話ではない。ただし、何…

簡単かと思ってやってみたら、意外と苦戦するかもしれない。「重複を省いて抽出」ではなく「重複している行を抽出」したい場合、どうすればいいか。 こういう時、エクセル感覚だと「それぞれの行に、一つ上までの行を参照する COUNTIF関数を仕込んで……」みた…

クエリの中で複数のキーを指定して抽出したい時、抽出用リストがある場合はそれを利用するにこしたことはない。 例えば「リストの値と等しい行を抽出したい」場合は、Table.SelectRowsの条件を下記のように記述すればいい。 each List.Contains( {"中央区", …

空いている行を上の値で埋めるのは「下方向にフィル」で簡単にできるけど、その逆はどうだろうか。もちろん「上方向にフィル」という意味ではなくて。 つまり先頭の値以外を表示しない。「ピボットテーブルでやれ」とか「クエリでやることか」とかいわれると…

指定行数分だけ繰り返し表示をしたい場合。それも「1」「2」「3」「1」「2」「3」……ではなく「1」「1」「1」「2」「2」「2」……としたい時はどうするか。 これが１列しかない場合はそれほど難しくはない。例えば４行繰り返したい場合なら、 =TOCOL(A:A,SEQUEN…

複数のブックを統合するのに標準機能でやると、出現するのがヘルパークエリなんだけども、これが苦手な人も多いのではないかと思う。でも慣れれば超便利なので、是非とも仕組みを理解して活用して欲しい。もちろん他の用途にも応用できる。 今回は「複数のシ…

複数条件の配列計算に MMULTを使う人は時々いるかと思う（いる……よね？）。使った方がすっきりすることも多いのだが、いかんせん知らない人が見てもチンプンカンプンになること請け合いの方法なのが玉に瑕である。 こういう表で、「区分」と「補助区分」を使…

ソースの読み込みを条件式で切り替えた場合、該当しないほうのデータは読み込まれるか。答えはノー。つまり読み込む情報を小分けにしておいて、必要に応じて切り替えて使ったほうが処理は断然早くなる。 例えば A列が1行、B列が100万行のテーブルがあるとす…

勤続年数を「何年何ヶ月」と表示したいからと、DATEDIF関数の「Y」と「YM」で計算したものを「&」で繋いで文字列で表現する人が時々いる。 =DATEDIF(入社日,現在,"Y")&"年"&DATEDIF(入社日,現在,"YM")&"ヶ月" やんのはいいけど、こんなものどうやって計算に…

皆さんは、クエリでソースを読み込もうとしたらエラーが起きたことはないだろうか。私はよくある。 固定のパスでフォルダを参照していると、ふとした拍子にリンクが切れてしまうことがあるからだ。 こんな時は、慌てずに右上の「設定の編集」ボタン、もしく…

「10桁以上の数値からなる日時を表現した数値」があったら、それは「UNIX時間（タイムスタンプ）」で間違いない。桁数が多い場合もあるが気にする必要はない。この仕組みは単純なもので、「1970年1月1日」を「0」として経過秒数を協定世界時（UTC）で表現し…

Excelを利用している人の中には、「入力で楽して、計算で困る」人が結構な数いる。月ごとにシートを分けて、Excel方眼紙で表を作っちゃうような人はその部類である。 これは、とある人が入力したアンケート結果の記録である。 何かというと「問１は『1』、問…

Power Queryは、ファイル内のデータを読み込む以外にも色んな使い道がある。例えばフォルダコネクタも便利な機能のひとつだ。フォルダのパスを指定して、サブフォルダを含めたファイルのリストを作成したい場合に使える。 例えば、Cドライブ直下に「親フォル…

画像で見た方が分かりやすいと思うので、早速だが下のような表があるものとする。 「日」の列と「時刻」の行で構成されたマトリクス表から「日時」と「名前」の表に組み替えしたい場合。何となく「列のピボット解除」をすれば良さそうなのは分かると思う。 …

以前とある業務で、引き継ぎ相手に Web送信用のCSVファイルを作らせて、自作ファイルと比較しようと思った時のこと。チェックしようとして気付いたのだが、何故か引き継ぎ相手が作ったファイルは私のものとは行の並びが違っていた。送信するデータの順番自体…

２列の比較ってよくある話なんだけど、COUNTIF関数を入れた作業セルをずらっと並べる方法はさすがにもうやめたほうがいい。 添付のように、A・B列を比較して、A列にしかない「10」と「14」を抜き出す場合。絶対にやってはいけないのが、FILTER関数で条件指定…

ピボット解除は便利なんだけど、それはあくまで見出しが１行の場合に限られる。元の表が一覧表の場合、得てして見出しが複数行にわたる場合もあるだろう。そういう場合、どうすればまともな形に組み替えできるか。 ２行とは限らず、３～４行に分かれている場…

昔から、PC用語を前半部分だけに省略表現する人が好きになれない。 「ドロップダウンリスト」を「ドロップダウン」や「プルダウン」のように略されると気持ちが悪くなる。「リスト」であることが重要なのに。 閑話休題。複数のドロップダウンリストを組み合…

数式で画像を検索したいという要望は昔からあるけど、表計算ソフトでこれを実現するのはなかなか手間である。 マクロは抜きにして、昔の Excelだと図形にリンクした番地のセルを表示させる方法 や、あるいはグラフの系列に画像を埋め込んで該当するものだけ…

表計算の世界で、同じ形式の表をシート単位で分けてしまうのは悪手以外の何ものでもない。ところが、Excelに不慣れな人は必ずといっていいほどこれをやってしまう。 例えば、A列に「名前」、B列に「値」が入力されている表が「Sheet1」から「Sheet10」まで並…

前回の逆。横並びのデータを縦に組み替える。 とはいえ、これクエリだと超簡単。何故なら「ピボット解除」があるから。 まずはテーブルを読み込む。 作った表にテーブル書式を設定（テーブル名「テーブル1」）。 「テーブル1」を選択した状態で ［データ］→…

縦並びのデータを区分を指定して横方向に組み替えしたいという質問は、度々目にするんだけど、グループ化したテーブルに連番列を追加する操作が必要なので、マウスを使った操作だけでは難しい。マウス操作中心でやる方法は、以前に書いたので割愛。 というこ…

検索用の条件テーブルを使って一致するデータを抜き出す場合、単純にはテーブル同士をマージしてやればいい……んだけども、マージが絶対に必要という訳ではない。 というわけで、まずはマージを使って「表1」から「表2」と複数条件で一致するデータを抜き出し…

何列かで１組になっているデータを縦方向に組み替える。クエリで一度書いたネタなのだが、これを数式でやるとどうなるか考えてみた。 まずは一応、クエリ版をおさらい。 let ソース = Excel.CurrentWorkbook(){[Name="データ範囲"]}[Content], ヘッダーへの…

日付などで時々あるかもしれないけど、連続する日付をまとめて表記したい時。「３つ以上連続する数字」は「-」で間の数字をまとめて、それ以外は「,」で区切る。 こんな感じ。「2」「3」は連続する数が２つだから「,」で区切っている。なかなか難しそうに思…

表に見やすさを求める人にしばしば見られるのだが、入力表なのにセル結合を入れてしまったり、同じデータが下に並んでいる時に先頭行以外を省略してしまったりする。こういう表を作ってしまってから「集計どうやったらいいの？」と悩む人も多い。 いわゆるこ…

読み込んだデータが 24時間以上を含む時刻データ（表示形でいうと「[h]:mm」）だった場合、クエリで読み込むと number型になってしまう。 ただシリアル値のままだと使いづらいので、期間型（duration型）に変換してしまおう。 期間型に変換 = Table.Transfor…

２つの文字列を比較して、その中で共通している部分（の中で最も長い部分）を表示させいたい。そんな質問を掲示板で見かけて、Excel2019だっていうので、それなりに苦労して回答したら、最後の最後に「365でもいいや」みたいな返事が出てきた。いや、それな…

「何かの組み合せの全パターンが欲しい」といわれたら、どんな関数を使うだろうか。もしピンポイントな関数が思い浮かばなくても、パターンをよくイメージすれば何とかなるかもしれない。今回は配列を使った計算の流れを説明したい。 例えば候補が３つのパタ…

クエリを読み込んだ出力結果のテーブルの行数を固定にしたいと思う人が少なからずいるらしい。おそらく出力用の書式に直接クエリからデータを書き出しているのだろうと思う。本来の使い方としてはおかしいんだろうけど、ここは「なんでもエクセル」なので考…