〜
とても面倒な方法ですから、あえてこんなことする人はいないだろう、、、と思っていたのですが、意外とVBAが使えない状況って多くないですか？会社で禁止されていたり、使えないようにされていたりと。。。
需要は多くなくとも、きっと必要な人がいる！！と勝手に決めつけて書くことにしました。私も何度か仕事で使っていた方法です。しかし、この方法ですと完全なリスト化ではないため、どうしても壊れやすさや不便さ、不安定さが残ります。運用は自分用、広げても同部署内、同エリア内など狭い範囲にとどめておいたほうがよいかと思います。この仕組で作った資料を外に出すときは、関数や設定が残らない工夫をしておきましょう！
この記事はサンプルの作り方を順番に説明する感じで進めていきます。なるべく１つでまとめたかったのですが、内容がとても長くなり、編集が難しくなってしまいました。そのため、前編、後編の２つに分けております。
内容ですが
前編 : Excelサンプルファイルの設計・仕様と前準備
後編 : 可変リスト作成のための関数配置と業務運用への応用
となっております。
前編、後編とおして手順どおりに作成すれば、サンプルが完成するようにしたつもりですが、もし、分かりづらい点、ご質問などありましたらコメントください。ページに追記させていただきます。
- 【前編目次 ページ内リンク】
- どうやってつくるか(設計)
- どんな動きができるか(仕様)
- データの数と重複を調べる(COUNT,COUNTIF）
- セルを関数で範囲指定する方法(OFFSET,INDIRECT)
1.どうやってつくるか（設計）
EXCELブック形式でシート4枚を使います。シートの内訳は「表示」「データ」「リスト」「パラメータ」の４枚で、シート名もこのままです。各シートは次のような作りになっています。
まずは、「表示」シートです。
図のように作成してください。このサンプルではフォームコントロールのコンボボックス（上）と入力規制のリスト（下）で可変するリストを作ります。どちらもあとで関数とともに設定します。
次に、「データ」シートです。
このようにご準備ください。ここではサイトで生成した架空データに通番を振って100件準備しました。作成する際、項目名、項目数は変えても大丈夫です。
そして、「リスト」と「パラメータ」シートです。
この２枚はあとで詳細設定しますので、その時に作っても大丈夫です。シートだけ準備しておいてください。
画像ですと読み取りにくいので、何が書いてあるか下に書いておきます。
◆◆◆「リスト」シート◆◆◆
「検索判定」、「データ位置」、「リスト」、「重複」
の４項目をページの最上行に記入。
◆◆◆「パラメータ」シート◆◆◆
【動作状態】
現在データ数
id状態
選択されたリスト位置
選択されたデータ位置
データ検索状態
【データベース範囲】
データベース シート名
起点セル
縦移動
横移動
データ最終行
幅（列)
【リスト範囲】
リスト作成 シート名
起点セル
縦移動
横移動
リストの数
幅（列）
のように３つの表が縦に並んでます。各項目名は関数に影響しませんので、ご自身でわかりやすい名前で大丈夫です。
2.どんな動きができるか(仕様)
スクショを使って、このサンプルでどんなことができるか説明していきます。
まず、基本の動きです。
検索項目をリストから選んで、文字を入力すると、
このように、データ一覧を設定された条件で検索し、その結果をフォームコントロールと入力規制それぞれにリスト表示し、リストが選択されたら該当データを表示します。
検索項目のリストは、データ項目行から自動的に作成し、入力規制で表示しますので、意図しない項目は入りづらくなっています。
それでは、検索項目を「カナ」から「id」に変えてみます。
このようにフォームコントロールと入力規制の挙動が少し違いますが、検索内容に連動してリストはちゃんと更新されます。
実運用では好きな方をご採用ください。私個人の感想ですが、コントロールの方は見た目キレイでしっかり連動する安定運用ですが、エラー表示が出やすい。
入力規制はセルに組み込むのでデザイン制約が強く、リストらしさがないが、エラー表示は回避しやすい。でも使い方を間違えるとファイルクラッシュの原因になることがある。
どちらも一長一短です。開発タブ自体が使えない場合は入力規制一択ですね。
次にデータ項目を増やしてみます。
このように自動で項目をリスト化し、その項目で検索をかけることができます。
id列はデータを検索する起点にしているので削除できませんが、その他は列の間に新たに項目を入れても、削除して項目を減らしても対応できるようにしました。
次に、レコードデータを１件追加してみます。
このようにデータ数がリストシートのリスト作成用関数の数を上回ると、パラメータの検索状況に「異常」表記されます。
これは、データが増えたらそれに合わせて関数も増やす方法を思いつかなかったので、アラートを出して、手動修正してもらうことで回避をしています。
なので、「異常」アラートが出たら、「リスト」シートのリスト作成用関数が入った４列を追加データ数以上、下方向にオートフィルしなければなりません。データ数よりリスト作成用関数行が多くなるとアラートは正常に戻るようにしてあります。
次に、このサンプルでは、リスト選択したあとにidでデータ検索して探していますので、idは重複しないデータでなければ正常に動きません。
では、重複データがある場合どうなるかやってみます。
このようにアラートが出るようにしましたが、表示は選択とは違うデータを参照してしまいます。これを修正するには、
「リスト」シートをチェックして重複が２以上になっている箇所のデータの位置がid重複の原因となっているので、データを直接確認し、手入力で修正してもらうようになります。これも先程のリスト作成用関数とデータ数の比較と同様にパラメータ確認ができるだけで、自動ガードはついてません。
これを回避しようと考えると、「id」だけでなく、もう１、２項目が一致するかどうかで判断させるようにすれば、idが重複していても正常に検索できる可能性はぐんと上がります。ですが、正常に検索できない可能性は0ではないので、やはりデータ区別するための重複しないデータを準備する運用にしておいたほうが良いかと思います。
ここまでがこのサンプルでできることです。
いかがでしょうか？「関数だけでもある程度できるんだ」と思われるか「手間の割に不便だな」と思われるかわかりませんが、私は、やはり関数だけで実運用は厳しく、VBAが使えない場合のみの手段かなと思っています。
3.データの数と重複を調べる(COUNT,COUNTIF）
「パラメータ」と「リスト」シートを使って関数を入れます。
まず、登録されているデータ総数を数えます。「パラメータ」の「現在のデータ数」の横のセルに次の式を入力します。
=COUNT(データ!A:A)
ということなので、関数の意味としては、
「データシートのA列の数値データの数」
ということになります。
データシートのA列は「id」です。このサンプルでは通番をつかっておりすべて数値にしています。 そして項目名は「id」と文字列です。なので列全体の数値データ合計は、項目名以外の登録データ総数ということになります。
もし、文字列データも含んでカウントする場合は、COUNTA関数を使ってください。ただ、列全体指定ですと、項目行を含んでしまうので、項目行分をマイナスしたり、参照先の式で項目行を加味するなど工夫すればいいです。
次に「id」に重複があるかチェックするための関数を入れます。
「リスト」の「重複」のすぐ下（項目行すぐ下）に次の式を入れます。
=COUNTIF(データ!A:A,データ!A2)
ということなので、関数の意味としては、
「データシートのA列の中で、データシートのA２セルと値が一致するデータ個数」
ということになります。データシートA列（項目「id」）全体の中に、A列２行目のid番号と一致するデータを数えていますので、重複があればカウントされます。このまま下方向オートフィルすると、範囲は列全体なので変化せず、条件になっているセル（A2）が相対参照になってますので、A3,A4,A5・・・と順番に行を下移動し、すべてのデータを判定することになります。
プログラミングで言うループ処理の代わりになります。関数ではループはできませんので、すべてのデータを判定する式がデータ数分必要になります。また、順番に条件とあうものを数えるだけですので、同じ「id」同士、相互で数えることになります。なので重複が２つの場合、カウントが２になるものが２つになり、３つの場合は当然３になるもが３つになることになります。ここでは重複があるかどうかわかればいいので、結果は１より大きいかどうかで判断できます。
それでは、その判断を「パラメータ」シートへ表示できるように、「id状態」の横へ次の式を入力します。
=IF(COUNTIF(リスト!D:D,">" & 1)>0,"重複あり","重複なし")
図で説明しましたが理解できましたでしょうか？
関数が合体してますね。そして、COUNTIF関数では「より多い」の条件がでてきました。これ覚えておくと便利です。
関数は中から読んでいきます。まず、COUNTIF関数は図のように「リストD列の中で１より大きいものを数える」です。外側ではIF関数でCOUNTIF関数の結果が0より大きければ、「重複あり」と表示し、それ以外、すなわち0なら「重複なし」と表示すると書いてあります。これで重複チェックのパラメータは完成です。
動作説明のところで「重複あり」のときは赤の塗りつぶしの黄色フォントに変化していました。これは「条件付き書式」機能を使っています。やたらめったらに使うと重くなるので避けたほうがよいですが、注意が必要な箇所に使うと大変効果があります。
簡単に使い方を載せておきます。便利なものなので使いましょう。
続いて、データがしっかり検索されているかを「パラメータ」シートへ表示できるように、「データ検索状態」の横へ次のように入力します。
=IF(COUNTA(リスト!A:A)>=COUNTA(データ!A:A),"正常","異常")
まだ「リスト」シートのA列には何も入れてませんが、あとでここに条件検索判定のための関数式が入ります。先程の重複と同様に、すべてのデータを検索しなければならないので、データ数分必要な関数式になります。ひとまず「リスト」シートのA列には判定式があるものとしてお考えください。
リストA列に入力されている関数式がデータ数以上のときは「正常」未満のときは「異常」と表示されるようになります。なぜ関数式とデータ数が「一致」ではなく、「以上」という条件にしているかといいますと、関数式よりデータが多くなれば検索できなくなるので困りますが、関数式が多くなっても、検索条件に空白データはないので、リスト作成上困ることはありません。
それに、判定用の関数を多めに準備しておきたい場合もありませんか？例えば、自分以外の人も運用するファイルで、自分が休み中にデータが増えてもに対応できるようにしたい場合などどうでしょうか？さらに、データが消されて、データ数が関数式を下回る場合も「一致」ではなくなります。結果、正常に検索できているのに「異常」と表示されてしまいます。こういったことを考えていくと、「以上」としておいたほうが良さそうですね。
また、先程の「id状態」と同様に、異常のときは書式が変わるようにサンプルでは設定しています。こういった警告用の条件書式をつけておくと自分以外の人が使ってもわかりやすくなります。それにシステムっぽいですね。
さて、これで完全にデータの管理ができるかといえば、そうでもありません。
重複の場合は、列全体とデータを比べていますから、項目名とデータが一致する可能性がありますし、データが検索されているかのチェックも大量に空白データがデータの間に入った場合、検索漏れがでる可能性があります。そうした場合、それぞれの心配事を確認してガードする方法を入れたらよいかと思いますが、、、、、アプローチを変えることも手です。例えば、重複の場合は、項目名に記号を入れるなどでデータと一致しないように項目名側を工夫したり、検索データのチェックではデータベース側の運用方法を決めるなどです。こういった仕組みを管理する上では、想定される不具合はすべて排除しなければなりませんが、対処方法はいろいろな角度から考えて、管理しやすくて簡単な方法を選べばよいと思います。
また、このサンプルでは「A:A」と列を直接指定していますので、A列は移動できても、削除できないものになります。重複しない基準項目としているので、このようにしていますが、後で基準項目を変えたい時などは不便かもしれません。そのようなときは、「パラメータ」シートに項目を作るなど工夫をしましょう。
4.セルを関数で範囲指定する方法(OFFSET,INDIRECT)
可変するリストを関数だけで作るために、とても重要なポイントです。「可変する」ということは、増減する項目やデータにあわせてセルの選択範囲が変化しないと実現できません。
まずは、範囲指定をするために使う関数の使い方を説明していきます。
1つはINDIRECT関数。
これは文字列からセルを指定することができる関数です。例えば普通にA1セルの値を表示したいと思ったとき、
=A1と簡単にかけますね。これをINDIRECT関数に置き換えると、
=INDIRECT（”A1”）となります。あれ？逆に面倒な気がしませんか？
全くそのとおりで、直接指定するなら、上の直接入力のほうが簡単ですね。
でも、、、このカッコ内を変える方法が取れるなら、、、なんとっ！！可変可能になるじゃないかっ！！
すみません。文がうるさくて。図で説明しますと、
セルの値を使って指定する
わかりづらいですかね？
INDIRECT関数は、”A1”という文字列をセルに変換したんです。イコールでB1セルを参照すれば、結果は「A１」という文字列になりますが、INDIRECT関数でセル変換されたのでA1セルの値「ここだよ」が表示されたわけです。さらに指定項目を増やせば
セルの値を使ってシートを超えて指定する
このようにシート名を書けばシートも超えられますし、ブック名もつければブックすら超えられます。もちろん範囲指定もできます。
自由に指定できることが伝わったでしょうか？
それでは、Excelでの文字列ルールに沿って指定してみます。
- 文字列は、”（ダブルクォーテーション）でくくる。
- 文字と文字の連結には「＆」を使う
数値のセル値を使って指定
文字列のセル値を使って指定
どうでしょうか？指定するイメージ取れますでしょうか？
では、実際に可変するイメージでSUM関数を例に少しだけ応用してみると、
SUMの範囲がデータ数によって可変する
この場合、カウントする範囲がA列全体なので、A6以降に数字が入れば自動的にカウントアップされ、SUMの合計範囲が連動して広がります。
可変リストの範囲にも応用できそうな気がしませんか？？
注意；
これは説明用なので可変合計というには不備だらけです。データの間に空白が入ればおかしなことになります。なので列全体を合計するだけならば=SUM(A:A)と列指定、条件によってで集計する必要があるならSUMIFをおすすめします。
では！これを応用して、、、と言いたいところですが、このままで足りない点があります。それは範囲の横（列）移動です。
列は大多数のみなさんはアルファベットですよね？あれ？計算でアルファベットは可変できなくね？？となりそうです。
しかし、そこはちゃんと求める方法はあります。INDIRECTの参照形式をR1C1形式にするとか、ADDRESS関数と組み合わせて使うとか、アルファベットだってA〜Zだけなら、まあ計算できないこともないし、と、方法を選ばなくてはならないぐらいあります。ただ範囲指定をこのままINDIRECT関数だけに頼ると関数式が長くなります。
そこで！もう一つの方法です。セルの範囲を決めることができる関数があります。それがOFFSET関数です。
早速、使い方です。
これで伝わりますでしょうか？関数だけ見ると数字だけの暗号みたいでわかりにくいですが、図のように分解してみると、基準からどれだけ離れた場所に、どれだけの大きさの範囲を選択するか？を決めることができる関数だとわかります。これならリストを範囲指定するのにもってこいです！
ちょっとまって、、、じゃあ、INDIRECT関数の説明は不要だったんじゃない？？
と思われたかもしれませんが、、、、
このOFFSET関数に設定する「基準セル」は文字列では動かないんです。
はい？一体何言ってるの？？さっぱりわからん（笑）となりますよね。
なので、今回のサンプルで説明してみます。
実は、「パラメータ」シートのデータベース範囲の表はOFFSET関数用に作った表です。この値を使って、いくつか例をあげます。
ということです。式の中に直接入力して指定することはできますが、シート名とセルの位置をセルに入力して参照しようとすると、文字列なのでうまく行かないのです。そこで、文字列をセルに変えてくれるINDIRECT関数の登場なのです。
ちょっとわかりにくいですよね。伝わらなかったらすみません。
このままデータベース範囲を可変できるように考えてみたいと思います。
まず、先程使った表を改めてOFFSET関数に入れて考えてみると、
このようになります。では、可変するために自動化しなければならない箇所を考えます。
データが可変する可能性ですので、まず、データの総数はすぐわかりますね。あともう１つ。それは項目数です。これも増えたり、減ったりします。なので、OFFSET関数でいうと、「範囲の高さ」と「範囲の幅」の２つになります。ここを関数で表せれば自動化できるということになります。それでは、範囲の高さを関数で表してみます。
「項目行数」＋「データ総数」です。ということは、先にデータ総数を求めているので、項目行数を足し算すればよいです。なので、「パラメータ」シートで言えば、C2セルにデータ総数が入っていますので
=C2＋１とすれば解決です。
ですが、項目数は１行のままでしょうか？２行にされませんか？
そのような心配がある時は、
=COUNTA(データ!A:A)にしましょう。空白が入り込まなければこれでいけます。
じゃあ、空白が入ったらどうするの？項目の１行目と２行目が結合されたらどうなるの。。。。こういう心配性な人はシステムつくるには向いているでしょうね（笑）
そういうときは基準セルをデータの始まりに持っていくか、項目行数を別に入力させる仕組みにしておいて、縦にズラす数として使うなど工夫をしたらいいでしょう。
また、後で作成する「リスト」シートの結果とデータの数を比較し、項目３で作ったチェック機能のように、パラメータで状態表示させれば、なお、ガードが強化されると思います。このように関数だけで全自動化するのは限界があり、どうしても手作業のメンテナンスが必要になります。なので、完全ガードというより、どうやってメンテナンス漏れを防ぐのか？と考え方を変えて作成すると良いかと思います。
次に「範囲の幅」です。これは、項目数（横[列数]）をカウントすれば解決します。
=COUNTA(データ!1:1)項目は文字列を含むので、空白でないデータ数を数えることができるCOUNTA関数を使いましょう。列全体指定と同様に、行全体も行数:行数で指定できます。
このサンプルの場合、関数の結果は「４」になります。
それ以外は固定しても可変リストは動きます。
ということは、式に直接入力して指定してもよいのですが、、あとで微調整するときに、「あれ？変更する関数はこれで全部だったっけ？？」と、私はよくなります。そしてうまく動かないなんてことも多々あります。
なので！変えるかもなぁという箇所は、先に変えやすいように準備しておくと便利です。このサンプルでは、次のように「パラメータ」シートに入力します。
| データベース シート名 | データ |
|---|---|
| 起点セル | A1 |
| 縦移動 | 0 |
| 横移動 | 0 |
| データ最終行 | =C2+1 |
| 幅（列） | =COUNTA(データ!1:1) |
となります。「データ最終行」は先程説明したように必要な方は工夫してください。
次に「リスト範囲」を考えていこうと思いますが、、、、、申し訳ない。この先は後編へ持っていきます。
それでは、前編はここまでです。可変リストを作成するための準備しかできませんでしたが、後編ではしっかり作成していきます。つまらない話を長々とお付き合いいただきましてありがとうございます。さらに後編も読んでいただければ嬉しく思います。
そして、「参考になった！」と思っていただける人が一人でもいればいいな〜と思います。
