業務で使うシートを作りたいのですが、下記の内容についてご教示ください。
ある機械の整備項目(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)別の良い方法はないか教示願います。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
#3です。
#4のお礼の状況を再現し、お礼にある式をそのまま入れてみたのですが、
私の環境ではきちんと動きました。
うまく行かないにしても、対象の数をカウントしてから検索しているので
対象がないはずがない⇒#N/Aが返るはずはないのですが…。
-2というのは、
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
- 回答日時:
#3です。
1.挿入>名前>定義で、別表の部品群それぞれについて名前をつける。
_5年目整備:=Sheet2!$C$7:$C$11
_10年目整備:=Sheet2!$C$12:$C$17
_その他:=Sheet2!$C$18:$C$19
※"名前"の先頭は数字にできないので、アンダーバーをつけます。
2.主シートのA3~A10を同時に選択して、下記のように入力し
配列数式としてまとめて確定(Ctrl+Shift+Enter)する。
=INDIRECT("_"&$A$2)
3.主シートのA3~A10を同時に選択して、書式>条件付書式 で下記のように設定する。
数式が/=ISERROR(A3)/書式:フォント白
(Excel2003で動作確認済)
******************************************************************************
2では、配列数式を使う代わりに
=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セルの行番号 ⇒ N番目の部品
・COUNTIF(Sheet2!$B$7:$B$19,$A$2):部品の数よりもNが多ければ空白を返す。
・MATCH($A$2,Sheet2!$B$7:$B$19,0):その整備項目の最初の部品は別表の何番目か ⇒ M番目
・別表の先頭セルからN+Mずらしたセルの値を返す(要微調整)
という解答をいただいたのですが、諸事情で
(1)整備項目が増えた(3パターン→4パターン)
(2)各整備内容の部品点数が増えた。
(3)部品表の位置がずれた。
R22~R40=5年目整備 S22~S40=各部品
R41~R60=10年目整備 S41~R60=各部品
R61~R75=オーバーホール S61~S75=各部品
R75~R85=その他 S75~S85=その他
(4)プルダウンはF21に作った。
前回同様、適当な各セルに必要な部品が表示できるようにしたいので、
解答を参考に
F22~F42=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))
としたのですが、F22~F42全てに#N/Aと表示されます。
おそらくは、関数にでてくる -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
- 回答日時:
(1)どこがおかしいのか
VLOOKUPは、最初にHITした行についてただ一つの値を返します。
例えば、A5の式について言えば、
「同じ式が続いてて自分は3番目だから3回目に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セルの行番号 ⇒ N番目の部品
・COUNTIF(Sheet2!$B$7:$B$19,$A$2):部品の数よりもNが多ければ空白を返す。
・MATCH($A$2,Sheet2!$B$7:$B$19,0):その整備項目の最初の部品は別表の何番目か ⇒ M番目
・別表の先頭セルからN+Mずらしたセルの値を返す(要微調整)
他にも
・部品群毎に名前をつけてINDIRECTで参照
・別表の配置を複数列にしてOFFSETで参照
・配列数式で処理
その他いろいろアプローチがあるでしょうが、取り合えずご参考まで。
>VLOOKUPは、最初にHITした行についてただ一つの値を返します。
例えば、A5の式について言えば、
「同じ式が続いてて自分は3番目だから3回目にHITした行について返そう」
正しく上記のように考えていました。急ぎではないので出来れば、
INDIRECT()関数を使った方法を教えていただけませんか?
(INDIRECT()関数は学生自分にちょく*2使ってましたので)
ひとつのセルについて関数が深くなりすぎると、後々エラーチェックするのに手間になり、私の力では解決不可能になるかもしれませんので。
ちなみに上に「(1)業務外の時間に作成するように(2)2機種(あと1機種分)作るように」言われていますので、締切りはあってないようなものなのでこれからもアドバイスよろしくお願いします。
No.2
- 回答日時:
基本的な疑問として、
各整備時期の整備項目が最大6項目ぐらいしかないようなので、
何もドロップダウンリストを使わずとも
シートを3枚使って各整備時期用のシートを作成し、
それぞれに必要な項目を羅列するだけで良いように思いますが...
早急な回答ありがとうございます。
確かに整備内容ごとに各シートを作ることも考えたのですが、
今回の表については、ツールのカメラ機能で最終的に報告書の内容や部品の発注リストなどにも、反映できればと考えているのでできれば、質問で挙げたような内容で出来ないかと考えています。
何か他に方法があればご教示ください。
No.1
- 回答日時:
早急な対応ありがとうございます。
私の力不足もあり、まだうまくいきません。
この参考を例に考えてみたのですが、例えば、佐々木を選んだ時に営業区域の1つを表示するのではなく、全て表示する方法はありませんか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
テキストボックスの文字列を上...
-
txtはおそらく,フォントやサイ...
-
office365 のアプリについて
-
http://oshiete.goo.ne.jp/qa/1...
-
「大谷翔平」をローマ字で書くには
-
txtはフォント,サイズ関係ない...
-
ワード レイアウトの崩れ
-
ワードやエクセルのファイルの...
-
ワードってスマホから見ると、...
-
ワードで途中のページからペー...
-
ワードが白と黒が反転してしま...
-
ワードの行✖️文字数について。 ...
-
42×34でレイアウト設定して文字...
-
パワーワードとは?
-
txtって、ページの概念なくなり...
-
ワードの文字の大きさについて
-
ワードの保存について
-
どのフォントを使用しているの?
-
フォントについて フォントは購...
-
ワードやエクセルのリボンについて
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
http://oshiete.goo.ne.jp/qa/1...
-
フォントについて フォントは購...
-
ワードの文字の大きさについて
-
どのフォントを使用しているの?
-
ワードが白と黒が反転してしま...
-
ワード レイアウトの崩れ
-
ワードやエクセルのリボンについて
-
ワードの保存について
-
一番最初にワード開いた時文字...
-
ワード PDF化した際、背景の...
-
ワードでルビを振ると「単語が...
-
再起動が必要です
-
42×34でレイアウト設定して文字...
-
ワードってスマホから見ると、...
-
最近MacBookに変えました。 マ...
-
Office2012の起動が遅い
-
ワードで大量の図形を一括でグ...
-
A4サイズ横位置というのは ワー...
-
参照用数字配列がいつの間か変...
-
MicrosoftのOfficeに代わるソフ...
おすすめ情報