Excelで、条件に一致する複数のレコードを取得して、テキスト結合する方法
おはようございます、いつきです。
最近Excelの改修依頼で、以下のようなケースがあったので、備忘録ついでに記事にしました。
特定の条件にマッチする複数のレコードを取得し、複数ある場合は「/」区切りで出力したい。
たとえば、イベントカレンダーを作りたい場合で、同日に複数開催があるようなケースになります。
イベントリストの作成
まず、出力用のカレンダーに反映させる、イベントリストを作成していきます。
今回は、開催日と時間、イベントタイトル、会場を列として用意しました。
また、それらの情報を元にカレンダーに表示させるテキストを数式で用意しています。
=[@タイトル]&"("&TEXT([@開始時間],"hh:mm")&"~"&TEXT([@終了時間],"hh:mm")&")"表示用のカレンダーの準備
次に表示用のカレンダーを準備していきます。
今回は日付と、会場によって出力を変更したいので、変数としてそれらを入力するセルを用意しています。
あとは、カレンダーっぽく外見を整えて完了です。
【TIPS】よく参照するセルには名前を付けよう
Excelではセルに名前を付けて、それを数式の中で利用することができます。Excelをメンテナンスすることを考慮するとあとで見返せるようにしておくことをおススメします。
今回は、開始日に「startdate」、会場に「state」とつけています。※日本語で名前をつけることも可能です。
同じシート内だと辛うじて見つけることも可能ですが、シートが分かれると「あれ、この値って何を参照しているの…？」となりがちです。
=startdate-WEEKDAY(startdate)+1 //名前あり
=j2--WEEKDAY(j2)+1 //名前なし
表示用カレンダーに値を反映
それでは早速表示用のカレンダーに値を反映させていきます。
今回は、FILTER関数で取得した値をTEXTJOIN関数で結合します。
💡FILTER関数とは？（by ChatGTP）
Excelでデータの中から特定の条件に合うものを選んだり、表示したりするフィルターです。例えば、たくさんの動物がいるリストがあるとしましょう。その中から、猫だけを見たい場合、FILTE関数を使えば、猫だけが表示されるようになります。
FILTER関数で出力したいデータを取得
FILTER関数を使って、まずは表示用のテキストを取得します。この数式では、イベントリストから、指定した開催日と会場に一致する行を抽出し、表示テキストの値を出力しています。（開催日は2023/09/10、会場は東京）
=FILTER(eventlist[表示テキスト],(A3=eventlist[開催日])*(state=eventlist[会場]),""))
//state（会場）=東京
//開催日=2023/09/10
TEXTJOIN関数で、文字列を結合
この取得した結果をTEXTJOIN関数を使って文字列結合していきます。TEXTJOIN関数のテキスト部分に上記のFILTER関数の数式をいれます。
カレンダー表示でわかりやすくするために、「/」で区切った後に「改行（CHAR（10））」をいれています。
=TEXTJOIN("/"&CHAR(10),TRUE,FILTER(eventlist[表示テキスト],(G3=eventlist[開催日])*(state=eventlist[会場]),""))
カレンダーの利用
これで準備が整ったので、あとは開始日や会場を変更するだけです。
一応イベントリストに祝日行を追加しているので、条件付き書式などで祝日の場合は色を変えるなどしても良いかもしれません。
システム導入に時間がかかるときの一時しのぎ
今回はExcelでイベントリストから、カレンダーを作成するケースを例に特定の条件にマッチする複数のレコードを取得し、複数ある場合は「/」区切りで出力する方法を紹介しました。
ただ、実際はもっと細かい要望などもあり、こんなにきれいにできないと思います。