条件に合致する値のみのリストボックへの表示について

質問

お世話になります。

エクセルを用い、会社の貸出管理をしたいと思っております。

その際、貸出可能な機器のみをリストボックスにて選択可能な
状態にすることは可能でしょうか。

可能な場合、どのような方法で可能になるのでしょうか。

ご回答の程、よろしくお願いいたします。

【現状】
添付の画像の背面にあるシートのような形で管理自体を行う予定。
A列の設定：添付の画像の前面にあるシートのリスト項目と名前の定義を使い、
　　　　　　　 「外付HDD_社外用_ポータブル」などをリストボックスで選択可能に。
B列の設定：INDIRECT関数を使い、A列で選択した項目に対応した機器名を
　　　　　　　　リストボックスで選択可能に。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.5

MackyNo1 · ベストアンサー

D列に返却日が入力されていないセルが現在使用できない機器ということになりますので、実際に使用できる機器のリストを別のセル範囲に作成することになります。

たとえばSheet1のD列（1000行目まで）が空白のデータの機器名の一覧を取得するには、リスト用データの入力されたシート（たとえばN2セル）に以下の式を入力して10個ほど（最大貸出数プラスアルファ）オートフィルコピーしてください。

=INDEX(Sheet1!B:B,SMALL(INDEX((Sheet1!$D$2:$D$1000<>"")*1000+ROW($D$2:$D$1000),),ROW(A1)))&""

次にA列の機器名で使用されていないデータを抽出すには以下の式をO2セルに入力して、右方向にデータ数分および下方向に10個程度オートフィルコピーしてください。

=INDEX(A:A,SMALL(INDEX(COUNTIF($N$2:$N$10,A$2:A$11)*100+ROW($A$2:$A$11),),ROW(A1)))&""

なお上記の数式はN2:N10セルに貸し出し中の機器名が表示されているパターンで、1つのkリストが最大10件までのパターンの式になっています。

このような表で、これまでの方法で、それぞれ名前定義すれば、ご希望のドロップダウンリストを作成することができます（簡便に作成するなら、数式タブの「選択範囲から作成」で「上端行」にチェックを入れれば一括して設定できます）。

ちなみに、名前定義で数式を使用すれば、ドロップダウンリストに空白セルを表示しないように設定することもできます。

お礼

おかげさまで管理ファイルが完成しました。

単体での関数で普段しようするものは何となく分かるのですが、
関数を組み合わせ、しかも、普段は使用しないものも使うと
なると難しくて仕様がありません。

回答No.6

keithin

画像が消去されてしまったので、ご利用のエクセルのバージョンが判らなくなってしまいました。
エクセル２００７以降をご利用の前提で回答します。ご相談投稿では、普段あなたがご利用のソフトのバージョンまでしっかり明記する事を憶えて下さい。

基本方針：
１．貸出数と返却数が合ってない機体が貸し出し中だと判断できます。
２．再掲：
＞基本的には機器台帳の方に、（たとえば５行目以下を使って）「貸出可能な機器リスト」を計算式で並べておきます。

添付図：
シート２のＢ８には
=IF(OR(B2="",COUNTIFS(Sheet1!$A:$A,B$1,Sheet1!$B:$B,B2,Sheet1!$C:$C,"<>")>COUNTIFS(Sheet1!$B:$B,B2,Sheet1!$D:$D,"<>")),"",ROW(B2))
右に下にコピー

シート２のＢ１４には
=IF(ROW(B1)>COUNT(B$8:B$12),"",INDEX(B:B,SMALL(B$8:B$12,ROW(B1))))
右に下にコピー

シート２のＡ１４：Ａ１８に機器名「あれ」と名前を定義
シート２のＢ１４：Ｂ１８に機器名「これ」と名前を定義
シート２のＣ１４：Ｃ１８に機器名「それ」と名前を定義
シート１の入力規則は、既に出来ているやり方で設定します。

お礼

色々とご教授ありがとうございました。

おかげさまで管理ファイルが完成しました。

ソフトのバージョンのことは失念していました。
申し訳ありません。

回答No.4

keithin

「リストボックス」とか言葉遣いが間違ってるので、ＶＢＡでという回答が返ってきます。

基本的には機器台帳の方に、（たとえば５行目以下を使って）「貸出可能な機器リスト」を計算式で並べておきます。そちらが出来れば、そこから「入力規則のリスト」でINDIRECT関数を使い取ってくる手順は一緒です。
画像が小さくて何が書いてあるのか見えませんので（あなたには見えますか？だったらごめんなさい）具体的に「貸出可能なリストを計算する数式」は回答しませんが、必要ならそちらの詳細（既出回答で指摘されているように、どれは貸し出し中でどれはフリーだとどこをどう見たら判断できるのか、なども含め）を詳しく書き添えて、ご相談を出しなおしてみて下さい。

補足

エクセルではリストボックスとは言わないとは知りませんでした。

現状の詳細は回答NO.3の補足に記載したので、
お知恵を拝借できれば、ありがたいです。

回答No.3

lv4u

>>VBAではなく、関数での解決は可能でしょうか。

私が作る必要があったときは、関数での解決ができるようなシンプルな条件ではありませんでしたし、それ以降の複雑な処理もありましたのでＶＢＡで作成しました。

質問者さんは、【現状】に書かれた方法で、すでにリストボックスが可能になっていると読み取れますから、その関数を拡張されればいいんじゃあないですか？

補足

他の回答者さんが、おっしゃっているようにリストボックス
ではなく、リスト化をした状態です。
そのため、VBAは全く作成していません。

関数を拡張し、リストを取捨選択させる方法
（IF、COUNTIF、MATCH、VLOOKUP等）を自分なりに
考えてみたのですが、上手くいかず、お知恵を拝借したく
質問した次第です。

回答No.2

MackyNo1

＞【現状】
B列の設定：INDIRECT関数を使い、A列で選択した項目に対応した機器名をリストボックスで選択可能に。

現状で対応する機器はリストボックスで表示できているということは、さらに対応した機器の中でさらに使用中の機器はリストから除外したいという意味でしょうか？

その場合は、貸し出し中かどうかわかるデータは（通常は貸出日、返却日、貸出機器などのデータ）どの部分に表示されているのでしょうか。

補足

仰るとおり、対応した機器の中からさらに使用中の機器を
除外したリストを選択できるようにしたいです。

内容の分からない画像を消去し、他に差し替えようと思った
のですが、消去しかできなかったため、画像自体なくなって
しまったので、以下の文章で重複する部分もありますが、
説明します。

【現状】
～貸出管理シート～
A列：リスト用シート（※）の１行目に名前を定義し、
　　 データの入力規則を使い、貸出機器の種類を
　　 リストで選択できるようにした。
B列：リスト用シートのB列以降のそれぞれに名前を定義し、
　　 データの入力規則とINDIRECT関数を使い、A列で
　　 選択した値に対応した機器管理番号をリストで選択
　　 できるようにした。
C列：貸出日を入力
D列：返却日を入力

～リスト用シート～
１行目：貸出機器種類を記入
　　　 （「外付Blu_rayドライブ」など）
B列以降：機器管理番号を記入
　　　 （「S0001」など）

回答No.1

lv4u

>>可能な場合、どのような方法で可能になるのでしょうか。

ＶＢＡでプログラムを記述します。

補足

VBAではなく、関数での解決は可能でしょうか。