はじめに

お仕事柄、Excelには大変お世話になっているので、普段使っているExcelの入力規則を使いつつ、
簡単な食卓カレンダーを作ってみようと。
もういくつ寝ると～的なやつをイメージしてみた感じです。

できたもの

こんな感じで。
見た目は単純なリストですが、入力規則を使って選択されたイベントに対応する日付や食べたいものリストを動的に選択できるようになっています。
入力規則の動的リスト作成って色んな人がもう既に記事にされていますが気にしない！

使っている数式

IF
OFFSET
MATCH
COUNTIF
COUNTA

データの入力規則について

Excelの入力規則でリストを作る場合、『VLOOKUP』なんかが大活躍することも多いかと思いますが､『VLOOKUP』だと参照先の項目表に自由度が持たせられないので、色々とこうしたいな～というところがやりにくかったり。
(基準となるキー項目は一番左の列に置かないといけないとか、複数キーを持たせたいときはどうするとか)

そんなときに活躍してくれるのが『OFFSET』だったりするわけです。この辺りの情報はQiitaにも沢山投稿されていますね。~~（つまり二番煎じ？？）~~

ちなみに『食卓カレンダー』のワークシートから入力規則で参照している『項目』ワークシートの内容はこんな感じ。

『VLOOKUP』だと「イベント」、「日付」、「食べるもの」なんかはまとめて表にする必要がありますが、『OFFSET』を使えばそれぞれで切り分けられるという利点。
お仕事的な話でいくと、例えば「イベント」、「日付」、「食べるもの」の各項目を試験工程の「大区分」、「中区分」、「小区分」なんかに置き換えて試験項目一覧なんかをつくれますよね～的な。

実際に使っている入力規則

■イベント名

=OFFSET(項目!$B$3,0,0,COUNTA(項目!$B$3:$B$100),1)

■日付

=IF($B3<>"",OFFSET(項目!$F$3,MATCH($B3,項目!$E$3:$E$100,0)-1,0,COUNTIF(項目!$E$3:$E$100,$B3),1),$A$1)

■何を食べたい？？？

=IF($C3<>"",OFFSET(項目!$J$3,MATCH($B3,項目!$I$3:$I$100,0)-1,0,COUNTIF(項目!$I$3:$I$100,$B3),1),$A$1)

■採決

=OFFSET(項目!$M$3,0,0,COUNTA(項目!$M$3:$M$100),1)

入力規則の『COUNTA』や『COUNTIF』、『MATCH』でそれぞれの項目数よりも大きい値（$B$3:$B$100とか）を持たせている理由は、それぞれに対応する項目が増えたときに数式を再度変更しなくてもいいようにという理由からです。
つまり、突然イベントが増えた場合でも『項目』ワークシートの「イベント」の下に項目を追加するだけで入力規則の数式は変えなくて大丈夫！という手抜きテクニック。
なので、この幅は「$B$3:$B$100」でなく「$B$3:$B$1000」なんかでもいいわけですが、数式の計算コストは気にしておく方がいいかもですね。

補足

上記の数式を見れば一目瞭然ですが、『日付』と『何が食べたい？？？』の列の入力規則では、『IF』を使って前列の入力状態に応じて入力できるデータ内容を分けています。
つまり、『イベント名』が入力されていないと『日付』は入力できず、また『日付』が入力されていないと『何が食べたい？？？』も入力できません。

こんな感じで。何故こういう規則にしたかは、完全に個人の趣味嗜好です。

さいごに

お仕事でExcelを使う機会が大変多いので、その仕事で覚えた入力規則のやり方を使って動的にリスト作成を行い、食卓カレンダーみたいなものを作ってみました。
何やらOffice 2019からはVLOOKUPが高速化される¹とかあったりするので、入力規則周りはまた色んなテクニックが生まれる気もしますね。今後のExcelさんの活躍に期待？！

https://www.microsoft.com/en-us/microsoft-365/blog/2018/09/24/bringing-ai-to-excel-4-new-features-announced-today-at-ignite/ ↩