忘れた頃に、前回のスピル表示のカレンダーの続き。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関数を入れて右にコピーして……とや…

他人が作った在庫管理表を見ると、大体の場合「在庫」という列が右端についている。 ただこれだけでは「在庫が減っているな／増えたな」は把握できても、「いつ仕入れたものがどれだけ残っているのか」は分からない。単価が変動するものの場合は「いつ仕入れ…

CSVファイルをクエリで読み込みたいんだけど、ファイル名が毎回違ってて困ってるんだよね、という人のためのページ。 どうして困るのか。それは、CSVファイルの読み込みを「テキストまたは CSVから」から行うからだ。 「テキストまたは CSVから」でファイル…

たまに整形されていないテーブルから何行か飛ばして抽出したいと思うことがあるかもしれない。「何行飛ばし」みたいな抽出の仕方は、ずれるととんでもないことになるので可能な限りは避けたいものである。とはいえフラグが無ければしょうがない。 上の画像で…

テーブルをランダムに並び替える方法は、前にやったんだけども、今回はリストでやってみる。 まずはリストを用意する。 リスト1 = {"A".."Z"}, ここから同じ行数の乱数リストを作る。 乱数リスト = List.Random(List.Count(リスト1)), ２つのリストをまとめ…

２つのテーブルを比較して変更箇所をチェックしたい時、ぱっと思い付くのは条件付き書式で変更箇所に色を入れる方法だろうか。条件付き書式は同じブック内でないと比較できないが、COMアドインの「Inquire」をアドイン（Excel2013以上）してやれば「ファイル…

今回は「テーブルの組み換えＡ」の完全な逆パターン。 こういう縦並びのデータを組み換えて、 グループ別で横並びに組み替える。 普通に横並びにするだけなら「行のグループ化＋連番追加＆列のピボット」で済むけども、「品名」と「数量」のように複数の列が…

入出力の区別がない表の場合、一覧表形式になっていることが多い。そしてそれがシート単位で増幅されていく悪循環に陥りやすい。 それは置いといて、これを列ごとに項目がまとまったテーブル情報に組み換えしたい時。横並びのデータが１種類だったら列のピボ…

例えば住所を地域別に分類したい時、アプローチの方法は色々ある。アイテムアクセスを使うか列のマージを使うか。一長一短あるので使いどころ次第かと思う。 最初に「住所一覧」と「地域区分」のテーブルがあるものとして、まずはそれぞれのクエリを作成する…