数式／関数
２つのテーブルをマージして必要な値を展開する。クエリでやれば超簡単なんだけども、これをあえて数式でやってみる。 因みにクエリでやるなら「大分類」でマージして「値」を展開するだけ。マウスの操作だけで読み込みまでいける。書くまでもないけど一応コ…
やってみて「あれ？」と思ったので記事にすることにした。画像のように空白行で区切られたデータの横に連番を振りたい時。 先頭が必ず空白行で、範囲に予め数式を入れておけばいいのなら =IF(A1="","",SUM(INDEX(B:B,ROW()-1),1)) 数式を下方向にコピー これ…
子どもが産まれたばかりなもので、カウプ指数から肥満度を計算するのに数式でどうやるか考えてみた。 カウプ指数の計算は、BMI値と同じなんだけど判定基準が異なる。なので月齢に応じた対応表がまずは必要になる。 こういう表を作る時に注意したいのは、「後…
住所から郵便番号を検索したり、郵便番号から住所を検索したりは、WebAPIの力を借りれば比較的簡単にできるようになった。今回はそれらの合わせ技。手入力された住所情報を「都道府県名」「市区町村名」「地名」「番号」「それ以外」に分割する。 まずはテー…
指定行数分だけ繰り返し表示をしたい場合。それも「1」「2」「3」「1」「2」「3」……ではなく「1」「1」「1」「2」「2」「2」……としたい時はどうするか。 これが１列しかない場合はそれほど難しくはない。例えば４行繰り返したい場合なら、 =TOCOL(A:A,SEQUEN…
複数条件の配列計算に MMULTを使う人は時々いるかと思う（いる……よね？）。使った方がすっきりすることも多いのだが、いかんせん知らない人が見てもチンプンカンプンになること請け合いの方法なのが玉に瑕である。 こういう表で、「区分」と「補助区分」を使…
勤続年数を「何年何ヶ月」と表示したいからと、DATEDIF関数の「Y」と「YM」で計算したものを「&」で繋いで文字列で表現する人が時々いる。 =DATEDIF(入社日,現在,"Y")&"年"&DATEDIF(入社日,現在,"YM")&"ヶ月" やんのはいいけど、こんなものどうやって計算に…
「10桁以上の数値からなる日時を表現した数値」があったら、それは「UNIX時間（タイムスタンプ）」で間違いない。桁数が多い場合もあるが気にする必要はない。この仕組みは単純なもので、「1970年1月1日」を「0」として経過秒数を協定世界時（UTC）で表現し…
Excelを利用している人の中には、「入力で楽して、計算で困る」人が結構な数いる。月ごとにシートを分けて、Excel方眼紙で表を作っちゃうような人はその部類である。 これは、とある人が入力したアンケート結果の記録である。 何かというと「問１は『1』、問…
２列の比較ってよくある話なんだけど、COUNTIF関数を入れた作業セルをずらっと並べる方法はさすがにもうやめたほうがいい。 添付のように、A・B列を比較して、A列にしかない「10」と「14」を抜き出す場合。絶対にやってはいけないのが、FILTER関数で条件指定…
昔から、PC用語を前半部分だけに省略表現する人が好きになれない。 「ドロップダウンリスト」を「ドロップダウン」や「プルダウン」のように略されると気持ちが悪くなる。「リスト」であることが重要なのに。 閑話休題。複数のドロップダウンリストを組み合…
数式で画像を検索したいという要望は昔からあるけど、表計算ソフトでこれを実現するのはなかなか手間である。 マクロは抜きにして、昔の Excelだと図形にリンクした番地のセルを表示させる方法 や、あるいはグラフの系列に画像を埋め込んで該当するものだけ…
縦並びのデータを区分を指定して横方向に組み替えしたいという質問は、度々目にするんだけど、グループ化したテーブルに連番列を追加する操作が必要なので、マウスを使った操作だけでは難しい。マウス操作中心でやる方法は、以前に書いたので割愛。 というこ…
何列かで１組になっているデータを縦方向に組み替える。クエリで一度書いたネタなのだが、これを数式でやるとどうなるか考えてみた。 まずは一応、クエリ版をおさらい。 let ソース = Excel.CurrentWorkbook(){[Name="データ範囲"]}[Content], ヘッダーへの…
日付などで時々あるかもしれないけど、連続する日付をまとめて表記したい時。「３つ以上連続する数字」は「-」で間の数字をまとめて、それ以外は「,」で区切る。 こんな感じ。「2」「3」は連続する数が２つだから「,」で区切っている。なかなか難しそうに思…
表に見やすさを求める人にしばしば見られるのだが、入力表なのにセル結合を入れてしまったり、同じデータが下に並んでいる時に先頭行以外を省略してしまったりする。こういう表を作ってしまってから「集計どうやったらいいの？」と悩む人も多い。 いわゆるこ…
２つの文字列を比較して、その中で共通している部分（の中で最も長い部分）を表示させいたい。そんな質問を掲示板で見かけて、Excel2019だっていうので、それなりに苦労して回答したら、最後の最後に「365でもいいや」みたいな返事が出てきた。いや、それな…
「何かの組み合せの全パターンが欲しい」といわれたら、どんな関数を使うだろうか。もしピンポイントな関数が思い浮かばなくても、パターンをよくイメージすれば何とかなるかもしれない。今回は配列を使った計算の流れを説明したい。 例えば候補が３つのパタ…
忘れた頃に、前回のスピル表示のカレンダーの続き。Microsoft365じゃないバージョンでやるならどうするか。コツさえつかめば超簡単。 こういう形の七曜カレンダーは、「日曜始まり／月曜始まり」と「当月の前後の日を表示する／しない」で併せて４種類に大別…
何行か空けて参照する。 あるいは何行か詰めて参照する。 こういうリンクの機会は以外と多い。一定の行数を指定して数式でリンクしたい場合、以前だったらINDEX・ROW・COLUMN・INT・MOD辺りの関数を使ってごちゃごちゃとやっていたに違いない。 一定間隔を空…
確か昔、表計算大会で出題された問題だったような……。四桁区切りの数字（文字列）を数値化したい場合。 時代が進んで、どれだけ関数が増えても「こういうことされたら困る」はあんまり変わらなかったりする。 さて、当時のやり方を思い出しながら数式を書い…
フィルタで抽出した範囲の個数や合計を計算するのは、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でカレンダーを作りたいという人に限って「六曜も表示したいなー」などと気軽にいい始めるのである。 こういう時…
Microsoft365だと数式はスピルで結果が表示されるので、いちいち数式を右や下にコピーする必要がない。Excelでカレンダーを作る必要が本当にあるかどうかは別として、マンスリーカレンダー（七曜カレンダー）を作るのはとてつもなく簡単になった。 例えばこ…
