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

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

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

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

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

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

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

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

忘れた頃に、前回のスピル表示のカレンダーの続き。Microsoft365じゃないバージョンでやるならどうするか。コツさえつかめば超簡単。 こういう形の七曜カレンダーは、「日曜始まり／月曜始まり」と「当月の前後の日を表示する／しない」で併せて４種類に大別…

何行か空けて参照する。 あるいは何行か詰めて参照する。 こういうリンクの機会は以外と多い。一定の行数を指定して数式でリンクしたい場合、以前だったらINDEX・ROW・COLUMN・INT・MOD辺りの関数を使ってごちゃごちゃとやっていたに違いない。 一定間隔を空…

確か昔、表計算大会で出題された問題だったような……。四桁区切りの数字（文字列）を数値化したい場合。 時代が進んで、どれだけ関数が増えても「こういうことされたら困る」はあんまり変わらなかったりする。 さて、当時のやり方を思い出しながら数式を書い…

１つのブックに複数のテーブルがあって、指定したテーブルの情報をどのテーブルから読み込んだか分かるように縦結合でまとめたい場合。 画像の「本支店名」列のことを考えないなら、結合したいテーブルを全部読み込んで、「Table.Combine」か「&」で繋ぐだけ…

前回の「住所から郵便番号を検索する」を更に応用してみる。郵便番号を検索するついでに、住所を「都道府県」「市区町村」「町域（地名）」で区切ってしまおう。 やることはあんまり変わらないので、説明はなし。今回も、ExcelAPIを使わせてもらう。 let url…

前回の反対パターン。住所から郵便番号を検索する方法。 今時（Microsoft365）は数式でもできなくはないんだけど…… =TEXT(WEBSERVICE("https://api.excelapi.org/post/zipcode?address="&ENCODEURL([@住所])),"000-0000") とりあえず今回は PowerQueryでやっ…

数式でやるなら郵便番号データをダウンロードしてきて検索すればいいんだけど、毎回それをやるのは面倒だし、かといって WebAPIを使う方法だといちいち再計算がかかるので都合が悪い。数十件くらいなら、Power Queryでやってしまえばいいかなと思う。 今回は…

フィルタで抽出した範囲の個数や合計を計算するのは、SUBTLTAL関数や AGGREGATE関数を使えば簡単にできる。ただこの絞った範囲の中で COUNTIF関数や SUMIF関数が使いたいといい始める人が時々いる。 例えばこういうパターン。「A」で絞った場合「3」となるの…

という訳で前回の数式版に続いてクエリ版。問題をランダムに抽出するだけでなく、正解以外の候補を含めてランダムに出題する。 これが完成形。マクロでやるなら制限時間を付けてボタン選択したら履歴を残して新しい問題を表示させて……みたいなこともできるけ…

前回のランダム抽出を更に応用してみよう。漢字をランダムに抽出して、正解を含めた４候補を表示させるにはどうするか。 これが完成形。 ひとまず、例によって基になる学年別漢字配当表は用意してあるものとする（テーブル名「学年別漢字配当表」）。 ここか…

いっぺんに全部書こうかと思ったけど、長くなるので何回かに分けて説明することにした。最終的にはランダムに何候補かを並べて出題する漢字の読みテストを作ろうと思う。 ひとまず Excel2021以上/Microsoft365では、ランダム抽出自体はとても簡単。基のデー…

以前にクエリでやったものの数式版。カタカナをひらがなに変換する方法。 手入力された文字列の場合、ふりがなを読み込めばいいだけなので、 A1のふりがなの設定を「ひらがな」にしてから =PHONETIC(A1) とすればいい。ただし実際には漢字が混じっている場合…

以前は面倒だったけど、Microsoft365ならそうでもないことの内の一つ。 「123ABC45DE6789F」のように「数字＋文字＋数字＋文字……」で繋がっている文字列を、「123」「ABC」「45」「DE」……と数字と文字で分割したい場合。 =LET( _ts1,TEXTSPLIT(A1,,SEQUENCE(…

むかーしむかし、まだインターネットが一般に普及したての頃。趣味を共有する新たなツールとして、皆がこぞってウェブサイトを立ち上げていた時代。世の中には一般人の拙いホームページがあふれかえっていた。 たくさんあるウェブサイトの中から、自分のホー…

例えば「今月の土日だけの日付リストが欲しい」と思った時、逆に「平日だけ」あるいは「月水金だけ」のように曜日を指定して日付リストを作りたい時は、WORKDAY.INTL関数を使うのがいい。Excel2010以上なら使える。 まずは例題。「開始日以降、最初の月曜日…

六曜（先勝・友引・先負・仏滅・大安・赤口）を数式で計算しようと思ったら、月齢計算が必要になるので、なかなかにめんどくさい。なのに Excelでカレンダーを作りたいという人に限って「六曜も表示したいなー」などと気軽にいい始めるのである。 こういう時…

前回とやることはほぼ同じ。今回はひらがなをカタカナに変換してみる。 今回も置換リストをまず用意。面倒なので先に作ったものをアップしておく。 置換リストlet BinaryText = "RdCxUUNBEMDQXhw7MNh3u78WIPDe3u+A/mEYC2Uahe/j4/L5fb893i7Xv3i+Xb6ur/fOe/fde…

Excelだったら JIS関数・ASC関数で簡単に済む話なんだけど、Power Queryには全角半角変換の機能や関数が現時点では用意されていない。テーブルに読み込んでからワークシート関数で変換してやってもいいっちゃいいけど、それではどうにも不格好である。 クエ…

本来、同形式の表はシート単位で分けてはいけないんだけども、印刷物感覚で表を作っている人は必ずといっていいほど、これをやってしまう。ひどい場合、何十枚どころか１００枚を超えてシートを増やしてしまう人もいる。こうなってしまったら手動でまとめる…

Power Queryで Excelの TODAY関数にあたるのは「DateTime.LocalNow」なのだが、抽出する時には別の関数を使うことが多い。「当日（に関連する情報）と一致するか」を返す関数が存在するからだ。 例えば本日のデータだけが欲しい時は、フィルタの抽出条件で「…

Microsoft365だと数式はスピルで結果が表示されるので、いちいち数式を右や下にコピーする必要がない。Excelでカレンダーを作る必要が本当にあるかどうかは別として、マンスリーカレンダー（七曜カレンダー）を作るのはとてつもなく簡単になった。 例えばこ…

Microsoft365に追加された関数を使うと、今まで数式では手間がかかっていたことが簡単にできるようになった。 左のテーブルから右のようにグループで分けて名前を表示したい場合、UNIQUE関数で見出しを作って、E2に FILTER関数を入れて右にコピーして……とや…