こんにちは。
最近、友達が経営しているお店向けのシフト表を改善しています。
シフト表で出勤させる従業員を入力させるのですが、そこは当然ドロップダウンリスト形式です。ただし、その選択肢は、別途メンテナンスさせるメンバー情報から動的に形成するつもりでいるのですが、こちらの思惑どおりにメンバー情報をメンテしてくれるとは限りません（従業員メンテ機能をガチガチに統制すればいいのでしょうが、そんな柔軟じゃないUIにはしたくないので💦）。
特に、空白を入力された場合に、ドロップダウンリストにも空白が反映されてしまう課題を解決したいと考え、今回の備忘録に至りました。
■ 動作環境
OS: Windows 8.1（64ビット）
Excel: 2013（32ビット）
■ 完成イメージ
□ シフト表
月別のシフト表では、各メンバーを入力（ドロップダウンリストからの選択式）して、それぞれの勤務日、勤務形態を入力します。また、ドロップダウンリストの選択肢は、別途メンテナンスしてもらうメンバー情報から動的に形成します。
□ メンバー情報
従業員やアルバイトなどのメンバー登録、削除を行う管理シートです。
サンプル図のとおり、上野さんの前後に空の行（図の赤枠部）があります。ITに不慣れな人達に対して、自由にメンテしてもらう運用のため、こんなことも当たり前のように起きますよね💦
ただし、シフト表の完成イメージをご覧いただいたように、上野さんの前後に空行は無く、選択肢が形成されていましたよね！
■ メンバー情報への仕掛け
まずは、ドロップダウンリストの材料となるメンバー情報へ仕掛けを埋め込みます。
自由入力な「氏名」欄は、どのセルに入力しても、行を空けてもいい仕様です。
その入力内容に対して、C列（図の赤枠部）には次の式を設定します。
＜C6＞ =IF(D6="", "", 1)
氏名（同一行D列）が空なら空（の文字列）、空じゃなければ1を設定します。
＜C7＞ =IF($D7="", "", MAX(C$6:C6) + 1) ※以降のセルへオートフィル
氏名（同一行D列）が空なら空（の文字列）、空じゃなければC6から自身の一つ前セルまでの間での最大値に1を足した数字を設定します。
このようにすることで、氏名が入力された行に、連続の番号が振られます。
（図のとおり、田町さんが4、上野さんが5、神田さんが6…と、空行があっても番号が連続になっていますよね！）
■ 作業リストの準備
作業領域を使って、さきほど仕掛けた番号を活用して、氏名一覧を整形します。
Excel上のどの領域に作成しても良いのですが、専用のシートとして準備で作成すれば隠すこと（非表示化）が簡単なので、専用のシートへ作成しました。
A列は、番号値で1から連番を入力します。
B列は、次の式を設定します。
＜B2＞ =IF(ISERROR(VLOOKUP($A2, '＠メンバー'!$C$6:$D$55, 2, FALSE)), "", VLOOKUP($A2, '＠メンバー'!$C$6:$D$55, 2, FALSE)) ※以降のセルへオートフィル
長文で分り辛いと思いますので、2回も登場する以下の式から紐解いてみます。
=VLOOKUP($A2, '＠メンバー'!$C$6:$D$55, 2, FALSE)
同一行A列の番号を使って、メンバー情報へ仕掛けた番号を検索します。見つかったらその氏名を設定します。
VLOOKUP関数は、検索できなかった場合、エラーが発生します。
よって、IF関数を使って、エラー発生有無をISERROR関数で調査しながら、エラーなら空（の文字列）、エラーでなければVLOOKUP関数の戻り値（氏名）を設定します。
こーすることで、空行（空のセル）があったところは詰まって、氏名一覧が整形されました。
最後に、氏名欄（$B$2:$B$32）へ「氏名」という名前を定義しておきます。
■ ドロップダウンリストへ設定
あとはメンバーを選択させたい箇所にドロップダウンリストを設定していきます。
ドロップダウンリストに設定するセルを選択します。
「データ」タブを選択します。
「データの入力規則」を選択します。
「データの入力規則」ダイアログボックスで「入力値の種類」欄にて『リスト』を選択します。
「元の値」欄へ次の式を設定します。
=OFFSET('＠作業シート'!$B$2, 0, 0, COUNTIF(氏名,"?*"), 1)
基準: 作業リストの氏名欄の先頭セル（$B$2）です。
高さ: 作業リストの氏名一覧は、（整形したことで）先頭から氏名が隙間なく詰めて整列しているので、氏名が記載されている個数分がそのままOFFSET関数にて指定するべき高さとなります。 よって、曖昧検索（"?*"）を指定したCOUNTIF関数の戻り値をそのまま指定します。 （「?」は1桁の任意の文字列、「*」は0桁以上の任意の文字列です。）
幅: 一列なので1を指定します。
■ まとめ
いかがでしたでしょうか？
次回以降も、苦労した点を備忘録しようと思います。