プルダウンリストで選ぶと、複数の項目（部品）が別シートに表示する方法を教えてください。

質問

業務で使うシートを作りたいのですが、下記の内容についてご教示ください。
ある機械の整備項目（5年目整備、10年目整備、その他）をプルダウンリストにして、どれか選択すると、別シートに記載した選択した整備項目に必要な複数の部品が表示できるようにしたいのですが、
具体的には
A2をプルダウンリストにし、
別表を下記のように作成し、
B7～B11=5年目整備　　　　C7～C11=(軸受などの各部品）
B12～B17=10年目整備　　　C12～C17=(モータなどの部品）
B18～B19=その他　　　　　C18～C19=（その他部品）
適当な各セルに必要な部品が表示できるようにしたいので、
A3～A10=VLOOKUP(A2,B7:C19,2)としたのですが、うまくいきません。
(1)どこがおかしいか、
(2)別の良い方法はないか教示願います。

回答No.5

noname#52504

#3です。

#4のお礼の状況を再現し、お礼にある式をそのまま入れてみたのですが、
私の環境ではきちんと動きました。

うまく行かないにしても、対象の数をカウントしてから検索しているので
対象がないはずがない⇒#N/Aが返るはずはないのですが…。

－２というのは、
MATCHが1始まりでカウントするのに対し、
OFFSETは0始まりで指定する必要があるので、
その兼ね合いで返すデータの位置を微調整しているだけです。
仮になくても位置がずれるだけで大勢に影響はありません。

データと数式を再度確認してみてください。

数式については、数式の一部分を選択してF9キーを押すことで、
その部分がどのような値を返すかがわかります。

F21が5年目整備のとき、F22の数式の各部分について、
(ROW()-ROW($F$21))　：1
COUNTIF($R$22:$R$85,$F$21)　：19
MATCH($F$21,$R$22:$R$85,0)　：1
が返っているかどうかをチェックしてみてください。

お礼

私の入力ミスでした。
無事に完成しました。今は上司に見てもらいさらに、追加の機能
をつけるか指示待ち中です。
何度も同じ質問してすみませんでした。

回答No.4

noname#52504

#3です。

１．挿入＞名前＞定義で、別表の部品群それぞれについて名前をつける。
　_5年目整備：=Sheet2!$C$7:$C$11
　_10年目整備：=Sheet2!$C$12:$C$17
　_その他：=Sheet2!$C$18:$C$19
　※"名前"の先頭は数字にできないので、アンダーバーをつけます。

２．主シートのA3～A10を同時に選択して、下記のように入力し
　　配列数式としてまとめて確定(Ctrl+Shift+Enter)する。

　=INDIRECT("_"&$A$2)

３．主シートのA3～A10を同時に選択して、書式＞条件付書式　で下記のように設定する。

　数式が／=ISERROR(A3)／書式：フォント白

(Excel2003で動作確認済)

******************************************************************************
２では、配列数式を使う代わりに
　=INDEX(INDIRECT("_"&$A$2),ROW()-ROW($A$2))
とする方法もあります。

また、その場合は、条件付書式を使って空白欄を処理する代わりに、
　=IF(ISERROR(INDEX(INDIRECT("_"&$B$2),ROW()-ROW($B$2))),"",INDEX(INDIRECT("_"&$B$2),ROW()-ROW($B$2)))

とすることもできます。　
******************************************************************************

この方法だと、数式自体は比較的シンプルになりますが、
　>>別表を変更したときはその都度名前の範囲も変更しなければならない<<
という欠点があり、別表が頻繁に動く場合には不向きです。

実際の運用に応じてご検討ください。

お礼

何度も質問すみません。
A3～A10：
=IF((ROW()-ROW($A$2))>COUNTIF(Sheet2!$B$7:$B$19,$A$2),"",OFFSET(Sheet2!$C$7,MATCH($A$2,Sheet2!$B$7:$B$19,0)-2+(ROW()-ROW($A$2)),0))
(Excel2003で動作確認済)

略解
・ROW()-ROW($A$2)：自セルの行番号-A2セルの行番号　⇒　Ｎ番目の部品
・COUNTIF(Sheet2!$B$7:$B$19,$A$2)：部品の数よりもＮが多ければ空白を返す。
・MATCH($A$2,Sheet2!$B$7:$B$19,0)：その整備項目の最初の部品は別表の何番目か　⇒　Ｍ番目
・別表の先頭セルからＮ＋Ｍずらしたセルの値を返す（要微調整）

という解答をいただいたのですが、諸事情で
(1)整備項目が増えた（3パターン→4パターン）
(2)各整備内容の部品点数が増えた。
(3)部品表の位置がずれた。
Ｒ22～Ｒ40＝5年目整備　　　　Ｓ22～Ｓ40＝各部品
Ｒ41～Ｒ60＝10年目整備　　　　Ｓ41～Ｒ60＝各部品
Ｒ61～Ｒ75＝オーバーホール　　Ｓ61～Ｓ75＝各部品
Ｒ75～Ｒ85＝その他　　　　　　Ｓ75～Ｓ85＝その他
(4)プルダウンはＦ21に作った。

前回同様、適当な各セルに必要な部品が表示できるようにしたいので、
解答を参考に
Ｆ22～Ｆ42＝IF((ROW()-ROW($F$21))>COUNTIF($R$22:$R$85,$F$21),"",OFFSET($S$22,MATCH($F$21,$R$22:$R$85,0)-2+(ROW()-ROW($F$21)),0))
としたのですが、Ｆ22～Ｆ42全てに＃Ｎ／Ａと表示されます。
おそらくは、関数にでてくる　-2　が効いているのだと思います。
どうすれば、うまく表示できるのか再度教示願います。

前回、
A3～A10：
=IF((ROW()-ROW($A$2))>COUNTIF(Sheet2!$B$7:$B$19,$A$2),"",OFFSET(Sheet2!$C$7,MATCH($A$2,Sheet2!$B$7:$B$19,0)-2+(ROW()-ROW($A$2)),0))
としたときの　-2　の意味も教示願います。

回答No.3

noname#52504

(1)どこがおかしいのか
VLOOKUPは、最初にHITした行についてただ一つの値を返します。
例えば、A5の式について言えば、
「同じ式が続いてて自分は３番目だから３回目にHITした行について返そう」
などとは考えてくれません。

ご要望の処理をVLOOKUPでやるのは無理があると思われます。

(2)別の良い方法はないか
色々考えては見ましたが、いずれにしろ一筋縄ではいきませんね。
素朴にやるとしてもこんな感じでしょうか。

以下、別表がSheet2にあるとして、

A3～A10：
=IF((ROW()-ROW($A$2))>COUNTIF(Sheet2!$B$7:$B$19,$A$2),"",OFFSET(Sheet2!$C$7,MATCH($A$2,Sheet2!$B$7:$B$19,0)-2+(ROW()-ROW($A$2)),0))
(Excel2003で動作確認済)

略解
・ROW()-ROW($A$2)：自セルの行番号-A2セルの行番号　⇒　Ｎ番目の部品
・COUNTIF(Sheet2!$B$7:$B$19,$A$2)：部品の数よりもＮが多ければ空白を返す。
・MATCH($A$2,Sheet2!$B$7:$B$19,0)：その整備項目の最初の部品は別表の何番目か　⇒　Ｍ番目
・別表の先頭セルからＮ＋Ｍずらしたセルの値を返す（要微調整）

他にも
・部品群毎に名前をつけてINDIRECTで参照
・別表の配置を複数列にしてOFFSETで参照
・配列数式で処理
その他いろいろアプローチがあるでしょうが、取り合えずご参考まで。

お礼

>VLOOKUPは、最初にHITした行についてただ一つの値を返します。
例えば、A5の式について言えば、
「同じ式が続いてて自分は３番目だから３回目にHITした行について返そう」
正しく上記のように考えていました。急ぎではないので出来れば、
INDIRECT()関数を使った方法を教えていただけませんか？

（INDIRECT()関数は学生自分にちょく*2使ってましたので）
ひとつのセルについて関数が深くなりすぎると、後々エラーチェックするのに手間になり、私の力では解決不可能になるかもしれませんので。
ちなみに上に「(1)業務外の時間に作成するように(2)2機種（あと1機種分）作るように」言われていますので、締切りはあってないようなものなのでこれからもアドバイスよろしくお願いします。

回答No.2

noname#79209

基本的な疑問として、
各整備時期の整備項目が最大6項目ぐらいしかないようなので、
何もドロップダウンリストを使わずとも
シートを3枚使って各整備時期用のシートを作成し、
それぞれに必要な項目を羅列するだけで良いように思いますが...

お礼

早急な回答ありがとうございます。
確かに整備内容ごとに各シートを作ることも考えたのですが、
今回の表については、ツールのカメラ機能で最終的に報告書の内容や部品の発注リストなどにも、反映できればと考えているのでできれば、質問で挙げたような内容で出来ないかと考えています。
何か他に方法があればご教示ください。

回答No.1

maron--5

◆下のＵＲＬがお役に立つのでは？

参考URL：

http://www.kenzo30.com/ex_kisopoint/onepoint_sonota3.htm#Q4

お礼

早急な対応ありがとうございます。
私の力不足もあり、まだうまくいきません。
この参考を例に考えてみたのですが、例えば、佐々木を選んだ時に営業区域の１つを表示するのではなく、全て表示する方法はありませんか？