前編に引き続きまして、Excel関数だけで可変するリストのサンプルを作りながら説明していきます。 いよいよ核心です。
前編内容は振り返りませんので、こちらで確認してください。
- 【後編目次 ページ内リンク】
- 可変する範囲に名前をつける
- 全半角関係なく文字検索をする(FIND,ASC,JIS,INDEX＋MATCH)
- 表示リスト作成のため、該当データを集める準備(MATCH+OFFSET)
- 表示リスト作成(INDEX)
- リスト範囲と項目選択範囲の設定
- ドロップダウンリスト作成(フォームコントロール、入力規制）
- リストから選択されたデータの表示（INDEX＋MATCH）
- 業務に応用する方法について
１．可変する範囲に名前をつける
前編で作った「データベース範囲」に名前をつけます。
「リスト範囲」も一緒に作りたいところですが、実際に表示するリストを作成しないとわかりにくいので、こちらは表示リストを作成してから設定、説明をします。
名前の定義は、関数式が長くなりそうなときに、式を整理したり、よく使う範囲指定を、簡略化して使いやすくしたりと、とても便利ですが、頻繁に変更しないような関数式で使うと、いざ変更するときになって、逆に式の内容がわかりづらくなったり、シートを再編集して設定範囲がずれても気づきにくいなど、それなりに欠点もあるので、正直、好き嫌いは分かれますよね。「名前の定義は嫌いだから、ここは飛ばして読もう」と思った方、ちょっと待った！ここは飛ばして通れませんよ（笑）
今回のサンブルで、ドロップダウンリストを表示するために、フォームコントロール、入力規制を使いますが、どちらも式を直接入力できません。
このことから、可変リスト自体をあきらめた方は多いのではないでしょうか？私はそうでした。
でも、実は、「名前をつけた式」は入力できるのです！
なので可変式のドロップダウンリストのためには、ここは避けて通れません。
そういうわけで、「名前の管理」の使い方をご紹介します。
図のようにリボンから選択できます。選択するのは「名前の管理」「名前の定義」どちらでもよいですが、ここでは「名前の管理」から選択します。次のようにウインドウを操作してください。
「新しい名前」ウインドウが出たら、「名前」と「参照範囲」に入力します。「名前」の下の「範囲」は、設定する名前の有効範囲です。ブックにすれば、同じ名前と参照でブック内ならどこでも使えて、シートを選択すれば、選択したシート内だけで使える名前と参照になります。今回は「ブック」にしておいてください。
それでは、「データベース範囲」を登録していきますが、、、このExcelのウインドウは、どれも編集が難しい。。。と私はいつも思います。「参照範囲」に式を入れたいのですが、入力中にカーソル使うとセルが移動してしまい、編集のたびに「あ”っ〜！！」ってなります。なので、、、私のやり方で紹介しますので、一旦、名前ウインドウは全部閉じてください（笑）
それではまず、下の画像のように「パラメータ」シートの空いているセルを使って、前編で作った「データベース範囲」の式を絶対参照（＄をつけてセルを固定させる）で完成させてください。
もし、サンプル通りシートを作っていた場合の式は下のようになります。
=OFFSET(INDIRECT($C$8&"!"&$C$9$),$C$10,$C$11,$C$12,$C$13)
確定すると結果は「#VALUE!」エラーになると思います。そうしたらセルの中の「式だけ」コピーしてください。数式バーでもよいです。
ここで先程の「新しい名前」ウインドウを出します。でましたら、「参照範囲」に式をコピペしましょう！こうすれば入力補助機能に邪魔されずに入力できます。次に「名前」を自分でわかるように入力してください。ここでは「data_area」にしておきます。入力が完了したら「OK」を押してください。
そうすると下のように「名前の管理」ウインドウに戻って登録した名前の情報がでてきます。
参照範囲を見ると、勝手にパラメータ!とシート名がついてしまってます。これは操作しているシート名が自動で付いただけですのでこのままで問題ありません。
数式に名前の設定をする時、シート名は必須で省略できないからです。シートは相対参照にできないということですね。
これで関数式の名前登録の仕方はわかりましたでしょうか？？
あと数回やらないといけませんので、よく確認しておいてください。また、先程、関数式のコピペ用に作ったセルはもう使いませんので、消しておいてください。
２．全半角関係なく文字検索をする(FIND,ASC,JIS,INDEX＋MATCH)
それでは、リスト用にデータを絞り込むため、各データの検索結果の判定をします。
プログラミングでよく使われる繰り返し処理が関数では使えません。
なので、すべてのデータを判定するためには同じ数の関数式が必要になります。この関数式を「データ」シートの、データのすぐとなりの列に入れて、判定をすれば、検索範囲の整合性や結果がよくわかりますが、このサンプルでは「データ」シートの列（項目）はExcelの限界まで増やせるようにしてあるので「データ」シートはデータ以外入れられません。なので、「リスト」シートのA列を使って判定するようにします。関数の結果は見にくくなりそうですが、シートを分けるので関数が壊されるリスクは減るかもしれません。
また、アルファベットや数字、カナのような全角、半角がある文字の検索は、人が見たら同じ意味でも、PCでは違うものと判定してしまいます。データ入力時、もしくは検索文字入力時に「データを揃えるように気をつける」という運用をしている現場をよくみましたが、複数の人がファイルを使うと、「気をつける」だけでは、全半角が揃わないことが多いです。
こういう時は、データ、検索キーワード、どちらがどちらでも、比較する前にデータ同士の形を揃えてから比較するようにすれば解決します。半角揃えならばASC(対象データ)全角揃えならばJIS(対象データ)を使います。どちらでもお好みで使ってください。変換できないものは変換されずにそのまま比較されるので、どちらでも問題ありません。日本語比較が主体なので全角揃え（JIS)でも、ローマ字表記も見かけるグローバルな時代ですので半角揃え（ASC)でもいいと思います。サンプルでは半角にしています。
そして、文字が含まれているかどうかの判定方法は、いくつかありますが、主にFIND関数を使う方法を紹介します。この関数は対象の文字列の中に検索する文字列が指定の場所から数えて何番目にあるか？を調べる関数です。この関数の目的である「何番目にあるか？」は今回関係ありませんが、検索文字列が含まれていれば、この関数は必ず1以上の数値を返してきます。そこを利用してIF関数を使って判断します。
それでは「リスト」シートのA列の項目行の下、2行目（A２セル）に次のように入力し、確定したら下方向へ「データ」シートに 登録されているデータ数以上になるようにオートフィルします。
=IF(AND(表示!$B$3<>"",FIND(ASC(表示!$B$3),ASC(INDEX(data_area,ROW(),MATCH(表示!$A$3,データ$1:$1,0))),1)>0),1)
関数長いですね！！なんか難しそうですが、IF関数（条件分岐）の中のANDのカッコ内を置き換えて見てみると、
あら、簡単ですね！条件が２つとも一致したら「１」、それ以外は省略？？
このようにIF関数は不一致を省略できます。その場合、不一致の結果はどうなるかといいますと「FALSE」（違う）と表示されます。
このサンプルでは、「検索文字列がデータと一致したかどうか？」だけ知りたいので、一致したときの処理だけにしています。
そして、IF関数で２つ以上の判定条件をつけるときのルールは上の図の通りです、ANDかORで条件をカッコでくくればいいだけです。条件はカンマ区切りで増やせます。
次に、条件１と条件２を順番にみていきます。
条件１は
表示!$B$3<>""となっています。これは表示シートのB3セルが「空白ではない」という条件です。
検索する文字の入力がなければ、検索結果は「FALSE」（該当なし）にするということです。
あとで説明しますが、条件2 では、「入力されたデータと一致するか？」にするので、「空白」とデータは一致しないはずだから、この条件は無くてもよさそうに思えます。ですが、この条件判定が無いと、検索文字が空白の場合、すべてデータが一致してしまい、全データがリスト化してしまいます。それでは困るので入れた条件です。""で表す空白は、すべてのデータにあると言われればあるのでしょうね。
なんか、、、はじめからそうなることを知っていて、最初からキレイに作ったみたいに聞こえるかもしれませんが、こういう自作するものというのは、作ってみて、試しに動かしてみて、不具合があれば対処する。この繰り返しで完成させていくのが普通です。はじめからキレイに作れるわけないので、とりあえず作ってみて「試してみる」がとても大事ですよ。
つぎは条件２です。
FIND(ASC(表示!$B$3),ASC(INDEX(data_area,ROW(),MATCH(表示!$A$3,データ$1:$1,0))),1)>0長いですね。
また外側から置き換えて見てみましょう。
わかりますかね？なかなか説明が上達しなくてすいません。先に説明してますが、FIND関数で入力された文字がデータの中にあるかを判定させています。そして、全半角を無視できるように、どちらもASC関数で半角変換です。また、図のようにFIND関数は何文字目から検索を始めるかを設定します。今回は全部検索したいので当然１文字目からにしています。
だだし、この方法ですと、FIND関数が文字を見つけられなかった時、「#VALUE!」エラーが出ます。今回はシートを別にして目に触れませんし、文字がある場合のみにスポットを当ててますので、まあ、これでいいですが、「#VALUE!」エラーはカッコ悪くない？？と思われる方や、検索判定自体を表示させたいときは不向きです。恐ろしく長くなりますが、もう一手エラー判定の関数をいれるか、それよりは短くできますが、上で説明したFIND関数よりは長い、SUBSTITUTE関数（文字の入れ替え）を使って文字数を比べる手を使います。せめての救いは、入れ替えの方法を取るとIF関数のANDは不要になります。長いけど考え方はスマートです（笑）お好きな方法でどうぞ。
いちおう、文字の入れ替え関数式だけ。。。
=IF(LEN(ASC(INDEX(data_area,ROW(),MATCH(表示!$A$3,データ$1:$1,0))))>LEN(SUBSTITUTE(ASC(INDEX(data_area,ROW(),MATCH(表示!$A$3,データ$1:$1,0))),ASC(表示!$B$3),"")),1)長っ！（笑）きりがないので説明はなしということで。。。ご希望あればコメントでお知らせください。
それでは検索する文字、検索される文字について説明します。
検索する文字については簡単ですね。「表示」シートの検索文字列のセルを参照するだけですから、
表示!$B$3だけです。気にする点は、オートフィルされても移動しないように絶対参照にします。
それでは検索される文字を抜き出す関数です。これはもうよく使う方法のINDEX＋MATCHが使えれば何のことは無いです。これ便利なので詳しく説明します。まだまだ終わりませんよ（笑）お付き合いください。知っている方は飛ばし読みしてください。
まずはINDEX関数の使い方です。
このように指定した範囲内の座標でデータを抜き出せます。気をつけるのは範囲内の座標なので、Excel側の列、行ではないことです。このへん間違えないようにしましょう。
つぎはMATCH関数の使い方です。
ちょっとゴチャゴチャしてわかりにくいでしょうか。
やっていることは、範囲内で一致するものを探して、何番目にあるかを調べてくれる関数です。これもINDEX同様に範囲内の位置なので、Excelの列、行ではありません。また、MATCHで選択する範囲は、１行（列）にしなければうまく動きません。
さあ、この２つを合体させるとすごく便利になるのですが、気が付きましたでしょうか？
○ INDEX関数は縦と横の座標を別々に設定できましたよね？
○ MATCH関数は行か列１行の中で一致する場所を示せますね？
そうなんです。INDEXの縦と横の設定それぞれに、MATCH関数で検索する式を入れてしまえば、表になっているデータを縦横検索できるということです！
これ便利でしょ〜？私も、店舗売上を横に年、縦に月でまとめられている表から、年月検索させて予算表に表示させるとか、複数店管理なら店舗名と年月で検索させてPLに表示させるとかで使ってましたね〜。なつかしい（笑）
それでは、このサンプルでやっていることを説明します。
式の意味はこのようになっています。
ここで使っている、ROW関数は現在の縦位置を知る便利な関数（ちなみに横位置はCOLUMN関数）で、「データ」と「リスト」は、シートを分けているだけで、検索するために参照するデータの縦位置は同じでよいので、「現在の行数」で設定しています。さらに、「表示」シートの検索項目リストは、入力規制のリストで、「データ」シートの「項目」行を参照（これは後で設定します）しており、規制されているので、他の値は受け付けません。
なので、MATCH関数を使って、選択された項目名でデータの項目行を検索しても、もともとデータ項目行の項目名リストなので必ず一致が得られ、空白以外の不一致は出てこないという仕組みです。
また、MATCH関数では行全体を指定していますので、データベース範囲の横幅とは違いがあります。しかし、MATCHで示す位置は「端から何番目」ですので、データ開始位置が一緒ならば、順番は変わらないわけです。
さて！これで検索項目に入力された文字列が入っているデータ位置に「１」が表示されるようになりました。検索文字数も限定していないので、「イトウ」のように検索文字列を増やせば表示リストは絞り込めるようになります。
ただ、この方法は「検索文字が含まれる」が条件ですので、電話帳のような左から検索は期待できません。なので、「イトウ」と検索して「イトウ」さんを絞り込んだつもりでも、「サイトウ」さんも出てきます。もし、電話帳のような左端からの検索を考えている方は、次のようにLEFT関数を使ってください。
=IF(AND(表示!$B$2<>"",ASC(LEFT(INDEX(data_area,ROW(),MATCH(表示!$A$3,データ$1:$1,0)),LEN(表示!$B$2)))=ASC(表示!$B$2)),1)LEN関数で入力文字数を調べ、同数の文字列を左端から抜きだして比べるようにしました。こうすれば「イ」でも「イトウ」でも左から検索できます。名前の検索ならこちらのほうがしっくりきますかね。こちらもお好きな設定で組んでください。
３．表示リスト作成のため、該当データを上詰めする準備(MATCH+OFFSET)
さあ、今回の一番大事な箇所です。これが表現できればもう完成も同然です。
先程の「2.全半角関係なく文字検索をする」で、検索判定して「１」となったデータをそのままリストにしたいですが、バラバラに散らばりすぎて、表示用リストには程遠いですね。最低限、リストにするには、該当データだけを「上に詰める」ことができないといけません。
それでは、「リスト」シートのB列「データ位置」を使って、該当データが何行目にあるのかを上詰めで表示できるようにしていきます。まず、一番上の項目行のすぐ下の２行目（B２セル）に次のように入力します。
=MATCH(1,A:A,0) やっていることは、列A(A:A)の中に「１」と完全一致[0]するデータは何番目か？
と調べています。
一度、動作確認をしてみましょう。「表示」シートの検索項目を「カナ」、文字列に「イ」を入れて、サンプルデータと見比べてみると、
このように、最初の「１」のデータは「3行目」だよと検索できました。これで、最初のデータ位置は一番上にきました。あら簡単。
でも、、、MATCH関数でその次のデータをどうやって検索しましょうか？？
MATCH関数は１番目しか検索できない上、「１」のデータの個数は決まってないので、２番目、３番目・・・と「１」がなくなるまで探していかなければなりません。プログラミングのループみたいなことが関数でできるのか？！
ここで！ちょっと考え方を変えます。「２番めのデータを検索する」と考えずに、次の「１」を探すために「検索を再開する」と考えます。「２番めを探す方法」ではなく、「検索を再開する位置を探す方法」に変えるわけです。
１回目のデータ位置は、最初から順番に検索して「１」を見つけたデータ位置です。つまり、最初から１回目のデータ位置までは、「検索が終わっている」と考えられます。
ということは、データ検索の再開は、この１個めのデータ位置の１つ後からということになります。
このように、1番目しか探せないなら、検索開始位置をズラして選択範囲を変えて探せばいいわけです。
おおっ！「選択範囲を変える」は前にでてきましたね。はい、OFFSET関数の再登場です！
さきほど入力した１回目のMATCH関数のすぐ下（B3セル）に次のように入力して下方向にデータ数分オートフィルしてください。
=MATCH(1,OFFSET($A$1,SUM($B$2:B2),0,パラメータ!$C$12-SUM($B$2:B2),0)
関数の説明は前にしてますので、詳しくしませんが、ここでちょっと分かりづらいかな？と私が思う点は、ズラす行数を累計にしていることと、選択範囲の高さを同じだけ減らす設定方法です。
先程、説明したとおり、２回め以降の検索開始位置は、前回ヒットしたデータ位置の次の位置にズラすことを繰り返していくわけです。それを頑張って図にまとめてみると、
このようになります。MATCH関数で得られた検索結果の数字というのは、「選択範囲の中での順番」です。検索開始位置は検索回数が増えていくごとにズレていきますが、基準セルは固定してますので、ズレは検索回数分考えねばならないということになります。つまり累計になるわけです。これはループするときの式の作り方とよく似ていて、オートフィルして同じ結果になるように設計した結果ですね。基準セルを変えていく方法もありますが、どっちにしても累計は使わねばならないですかね。
また、選択する高さ（行数）ですが、図のように総データ数は変わらないものですから、検索が終わっていけば、当然同じように検索対象数は減っていきます。なので総数からズラす行数を引いているわけです。
ただ、データ行分オートフィルしているので、すべての「１」を抽出したあと、該当データが発見できなくなるので「#N/A」エラーになってしまいます。ここも裏方シートなのでそのままでよいですが、エラー回避するには、関数の大外使ってもう一手多くエラー判断させるなどしてください。
どうでしょうか？伝わりましたでしょうか？
これで検索に引っかかったデータの位置が上詰めで集まりました。あと少しです。長いですが休憩しながらお付き合いください。
４．表示リスト作成(INDEX)
さあ、リストアップするデータの位置がわかりましたので、あとは表示させるだけです。
データ一覧の中で該当する場所がわかっているのであれば、INDEX関数が簡単に解決してくれます。
それでは先程の列のとなり、「リスト」シートのC列「リスト」を使って表示させます。
項目行の下（C２セル）に次のように入力し、下方向にデータ数分オートフィルします。
=IF(ISERROR(B2),"",INDEX(data_area,SUM($B$2:B2),1) & " " & INDEX(data_area,SUM($B$2:B2),2))
できました。
このように、選択させるようなリスト表示は、同じデータで区別がつかなくならないように、重複しないデータとセットで表示するといいです。特に名前は、同姓同名も結構いますからね。
また、リストにしたときにエラー表示がリストに入らないように、IF関数で検索結果の場所指定（B列）がエラーなら空白を表示するようにしています。
そして、INDEXの設定は、可変するデータベース範囲で、縦座標をデータ位置の累計、横座標を直接番号指定にしてます。それをid[1]と名前[2]で作成し、&を使って、スペース間に入れ、つなげて表示しています。
この方法が一番簡単ですが、絶対参照のようになりませんので、データベース側で項目の入替えや削除があると、表示リストが変わってしまいます。それに対応させるには、直接番号指定をやめて、文字列判定で説明したようにMATCH関数を使えばできます。パラメータシートにも表示シートの項目選択の仕組みを作って、MATCH関数で検索するようにすれば、よりいいでしょう。
5.リスト範囲の設定
表示リストが完成しましたので、「パラメータ」シートのリスト範囲を次のように入力し、名前設定をしましょう。これでドロップダウンリストが可変できるようになります。
画像が見にくいかもしれませんので、下に詳細をのせます。リストの数は関数で可変させます。
| リスト作成 シート名 | リスト |
|---|---|
| 起点セル | C1 |
| 縦移動 | 1 |
| 横移動 | 0 |
| リストの数 | =COUNTIF(リスト!C:C,"* *") |
| 幅（列） | 1 |
実は、この「リストの数」が曲者です。正直、説明もうまくできるか心配（笑）。この上の表の式は、数える方法はいくつかある中で、一番短いものです。
式の意味は、「何かしらの文字に挟まれた、半角スペースがあるデータの数を数える」です。
しかし、この方法は、データの形に決まりがある場合にしか使えません。サンプルでは、idと名前で半角スペースを挟んでリスト作成しています。このように必ず形に「半角スペース」が入る決まりがあるので使える手です。もし、idだけや名前だけのリストにした場合、形が偶然一致するリストしかカウントできないものになってしまいます。
ちなみに、*（アスタリスク）はワイルドカードと言って、「文字数も形もわからないけど文字全般のかわり」みたいな意味です。関数で使える場面はCOUNTIFやSUNIFなどに限られてます。VBAではlikeと合わせてよく使いますけど、個人的に関数ではあまり使いませんね。つかえる場所が限定されてて、どこで使えるかしっかり覚えてないので、、、いちいち調べるのめんどくさい（笑）
では、データの形が限定できない場合はどうするか？ですが、まず、数えたい場所がどうなっているのかを考えて、どの条件でカウントされるのかを列記してみます。
このように、カウント対象のC列は、関数でリストを作っているため、何も入力されていない空白と関数で作った空白の２種類の空白が存在しています。そして、やっかいなことに、関数で作った空白は、空白でもあり、空白でもないのです。なのでリストだけ数えたいとなると、
=COUNTA(リスト!C:C)-COUNTIFS(リスト!C:C,"",リスト!C:C,"<>")-1こんな感じになります。意味としては、
「なにか入力されているデータ数ー関数で作った空白ー項目行数」
となります。関数で作った空白の条件はCOUNTIFSで複数条件にして、""の空白であることと、"<>"なにか入力があることの2つの矛盾条件が揃っているものとなっています。
当たり前ですが、PC側から見ると矛盾してないんですよ。まあ、プログラミングの話でよくある、""とNULLは違うとか、空白が入力されているとか、屁理屈みたいなことです。PCの都合もわかってやってください（笑）
ね？やっかいで、わけわからん曲者でしょ？（笑）
そして、もう一つ簡単にカウントする方法があります。それは、A列の「１」を数える方法です。なるほど、リスト対象は必ず「１」になっているはずなので、これなら簡単で間違いがなさそうです！。。。。。はい、ホントにそう思いたい。。。残念ながら、リストと違う可能性は、オートフィルが一部されてない場合など、ゼロにはできないですね。しかも、その場合、余分な空白リストがでてしまい、選択もできてしまうなど、余計なリスクが出てきます。もし、うまく判定ができてない場合でも、表示リストを直接数える方法なら、余分なリストが表示される可能性が一番少ないので、やはり表示リストを数える方法が無難でしょう。こっちは人の手によるリスクがあるってことです。曲者じゃ！であえ！であえ！（笑）
サンプルではやめましたが、実用する場合、「パラメータ」シートに、A列の「１」とC列の「表示リスト」を別々にカウントさせ、差がでたらアラート出すとかすると、不具合を察知できる運用が可能でしょうね。
さて、リスト範囲の入力が終わりましたので、データベース範囲でやったように、リスト範囲も名前をつけておいてください。サンプルでは下のように名前をlist_areaにしています。
このまま一緒に、「表示」シートの検索項目選択リスト用の可変エリアも設定しましょう。この上の画像の3行目にsearch_areaと載ってますね。これです。リスト範囲の設定と同じく、また式に名前をつけます。サンプルでは名前をsearch_areaにします。参照範囲に入力する式はつぎのようになります。
=OFFSET(データ!$A$1,0,0,1,パラメータ!$C$13)です。これは「データ」シートの項目行をリスト化する式です。
意味は。「データシートのA1を起点にして、縦横をずらさず、範囲の高さは１列、範囲の横幅は、データの項目数を数えたパラメータシートのセルを参照する」です。パラメータシートつくっておくとこういう時、便利です。
こいったパラメータシートで管理しているようなことを、ちょうと今紹介している名前の管理でする方もいらっしゃいますよ。うまくブラックボックス化できるのでスマートですよね〜！私は忘れっぽくて、スキルもペラッペラなので、シートで簡単に変更できるようにしないと維持できない人でしたが（笑）
さあ、次はいよいよリストを作成しま〜す！
６．ドロップダウンリスト作成(フォームコントロール、入力規制）
さあ、やっとドロップダウンリストを作成します。
「表示」シートを開いて、リボンの開発タブを選んでください。開発タブの表示がない方は表示しましょう。表示の仕方は過去の記事の下の方にある「VBAの使い方」ボタンを押して確認してください。
もし、開発タブ自体が規制されている場合、入力規制リスト一択です。コントロールの説明は飛ばして読んでください。
それでは下のようにコントロールの挿入を選択します。
そうすると、絵柄でボタンがいろいろ表示されますので、下のように「コンボボックス」を選んでください。カーソルがのるとコンボボックスとでるのでわかると思います。
選択するとオートシェイプの図形のように、カーソルが十字マークになり、クリックドラッグで位置と大きさを決めながら挿入できます。
位置と大きさを決めたら、コンボボックスを右クリックしてメニューを出し、コントロールの書式設定を選び、ウインドウを表示させます。
そうしたら、上の図のように「入力範囲」にリスト範囲につけた名前を入力します。サンプルではlist_areaでしたので、同じように入れています。
次に、「リンクするセル」のシートマークをクリックします。
このようにウインドウが変わってシート操作が可能になるので、「パラメータ」シートを選んで、表の「選択されたリスト位置」の横（C4セル）を選択します。するとバー状になったウインドウに「パラメータ!$C$4」と出ると思います。そうしましたら、×マークでウインドウを閉じます。
すると、設定内容が入力された状態で、最初のウインドウに戻ります。
確認してOKボタンで確定です。これでコンボボックスの完成です。
このウインドウに「ドロップダウンリストの行数」とありますが、これはリストを選択したときに表示されるドロップダウンの行数です。リストのデータ数を制限できるわけではありません。ただの表示設定です。
次は、入力規制でリストを作ります。
まず、先に設定したいセルを選択しなければなりません。「表示」シートのリストを設定するセルを選択します。サンプルではB12セルになります。
選択できたら、「データタブ」の「入力規制」を選択します。
すると上のようにウインドウが出るので、
「入力値の種類」は「リスト」を選び、「元の値」にリスト範囲につけた名前を入力します。このサンプルではlist_areaですね。
できましたら、OKボタンで終了です。
同じように「表示」シートの「項目」も設定します。ここでの名前は先程リストの範囲のあとに付けた名前を入れてください。このサンプルではsearch_areaでした。
こんな感じになりましたか？
これで可変リストの完成です！！
７．リストから選択されたデータの表示（INDEX＋MATCH）
リストだけあってもねぇ。。。。ということで、リストを選択したあと、リストボックスの下に値を反映させる方法を紹介します。
まず、コントロールのリストを選ぶとどんな動きをするかと言いますと、
このように、先程設定した、リンクするセルに、リスト番号が出る仕組みです。
勘のいい方は気づかれたかもしれませんね。そうなんです。可変リストのリスト番号って、データには全く関係ない数字なので、直接使えません！（笑）前編の冒頭で言った「とても面倒な方法」の意味がわかりましたでしょうか？
多分、、「アプリ側で並び替えとかフィルター使ってよ〜」ってマイクロソフトさんが言ってるんですわ（笑）
冗談はさておき、、、、順番に考えていきますか。
まず、リスト番号から追えるデータは、「リスト」シートのC列にあるid+名前ですね。リスト番号を使ってリストデータを得るには、INDEX関数で指定してやればいけます。
=INDEX(リスト!C:C,パラメータ!$C$4,1)こんな式になります。でも、idと名前が合体しているデータを使って検索はできませんよね。
なので、分割しましょう。リストデータの左から見ていって、最初に出てくる半角スペースより左側がidです。
では、半角スペースの位置を導くには、
=FIND(" ",[さっきのINDEXの式],1)これで半角スペースの位置が判明します。
では、リストデータからidだけ抜き出すには、
=LEFT([さっきのINDEXの式],[さっきのFINDの式−１])FINDの式から１を差し引くのは、LEFTで抜き出す文字数は、半角スペースの１個手前までだからですね。
これでidを出しました。でも、このidは文字列っぽいですね。文字の数字と数値データは別物だっ！！とPCがきっと怒るので、数値に変換します。
=VALUE([さっきのLEFTの式])これで数字になりました。idを抜き出すだけでこれなので、「パラメータ」シートに一回出しておきます。
「パラメータ」シートの「選択されたデータ位置」の横、C５セルに次のように式を入れます。
=IF(OR($C$4="",$C$19=0),"",VALUE(LEFT(INDEX(リスト!C:C,$C$4,1),FIND(" ",INDEX(リスト!C:C,$C$4,1),1)-1)))
エラー表示を回避する為、リスト番号が無いか、リスト数が０の時は、空白にする条件をつけてあります。
ささっ！くじけずにどんどんいきますよ！（笑）
このid数値を使って、INDEX+MATCH関数でデータから該当のものをリストボックスの下に表示させましょう。
「表示」シートの「id」横のB7セルに次の式を入れてください。
=IF(OR(ISERROR(パラメータ!$C$5),パラメータ$C$5=""),"",INDEX(data_area,MATCH(パラメータ$C$5,データ!A:A,0),MATCH($A7,データ!$1:$1,0)))この関数式でお初にお目にかかるのはISERROR関数ですね。これはカッコ内の処理がエラーかどうか調べる関数です。エラーならTRUE(正しい）になります。これがしれっとIF関数に２つ以上の条件に入ってます。そして、今までの集大成！INDEX関数で縦横検索が登場です。
この式の意味は、
「パラメータシートのidのセルに値が無いか、エラーなら空白を表示して、それ以外なら、データベース範囲内で、縦座標がidが一緒の位置、横座標が項目名とこのセルの左側の項目名が一緒の位置のデータを表示」
となります。
では、このまま下方向に「住所」までオートフィルしてください。これでコントロール側は完成です。
まだ気力は残っていますか？？もう私はダメみたいです。先に行ってくれ、、、、
で、、、入力規制側ですね。
実は、こっちは入力規制リストで選択したデータをそのままデータとして使えます。コントロールは、一回リストデータを探さないとダメでしたので、リストのセルを直接使えるはちょっと楽ですね。
考え方は、idを抜き出すところからコントロールのときと一緒です。
では、、、こちらは一気にいってみましょう。解説はコントロールの方を見てください（笑）
=IF($B$12="","",INDEX(data_area,MATCH(VALUE(LEFT($B$12,FIND(" ",$B$12,1)-1)),データ!A:A,0),MATCH($A14,データ!$1:$1,0)))これで「住所」までオートフィルしてください。
これで入力規制のリストも完成しました。
長かったですね。
８．業務に応用する方法について
こんな面倒くさい方法でリストを作ろうとしているのですから、応用術の１つぐらいなければムダでしかないですよね。
データからリストを作り、データを抽出できました。このデータに名前をつければ、Excelでつくった書類に飛ばすことは簡単になります。 例えば、このサンプルの「名前」データにName_kanjiと名前設定をします。
テンプレートサイトからもらった、業務報告書をブックに差し込み、名前のところに=Nまで入力すると、
関数みたいに入力候補に出てきてくれます。選択して確定すれば、
これでリストを変更すれば書類側も変わります。
書類フォーマットが同じものなら、ファイルを何枚も持たずに、データベースでそのデータだけ持っていれば、リストを選ぶだけで表示できます。作成する手間も軽減できますね。
いかがでしたでしょうか？無理くり関数だけでなんとかしました。
こんなにダラダラ長く書いて誰が読むんだ（笑）と何度かくじけましたが、なんとしてもこれだけ読んで完成できるようにするんだ！と勝手に自分を励まして書きました。１人でもいいので「参考になった」と思ってくれる人がいることを祈りまして、次からは短く書こう（笑）と思います！！もし、ここまで読んだ人いるならすごい！！！！本当にありがとうございます！！
