質問させてください。

質問

質問させてください。

画像のようにVLOOKUP関数で検索できるシート？のようなものを作成しているのですが、
部品名を入れると
個数と保管場所が出てくるのですが、この個数を書き変えた場合、反映できるようにしたいのです。

部品名がA1にあると想定していただけると助かります。

このとき、商品検索のネジと入力すると個数に3、保存箇所に第一工場と表示されます。

ここでやりたいのが、画像のようにデータ数が少ないのであれば、元データの数値をいじればいいのですが、元データ量が多い場合、できるのであれば、検索した時に書き換えられればベストなんです。

何かいい方法はないでしょうか？

まぁ、エクセルの検索で調べて、そこのセル付近まで飛んでしまえば、いいのですが、これは最終手段にしたので、できれば関数などでやりたいと考えています。

以上、宜しくお願い致します。

回答No.5

tom04 · ベストアンサー

No.2・3・4です。
交換日記状態ですが・・・

データと表示したい表を同じ列に！ということをお望みのようですが、
数式を入れたセル自身が数式の範囲指定内にある場合は「循環参照」になってしまいます。

出来れば別の列に表を作成する方が望ましいのですが、どうしても同じ列に！というのであれば
↓の画像のように元データより下の行に表を作成し、数式の範囲指定の領域は数式を入れるセル自身を含まないようにしなければならないと思います。

とりあえず、元データが2000行まであるとして、表示したいデータは2001行目からになるとします。
その場合は2001行目は見出し行になってしまいますので、数式は2002行目に入れることになりますよね。

その場合の数式ですが範囲指定も2000行目までとする必要があります。
↓の画像ですと、B2002セルに
=IF($A2002="","",VLOOKUP($A2002,$A$1:$D$2000,5-COLUMN(A1),0))

という数式を入れ、隣のC2002までコピー

D2002セルには
=IF(A2002="","",HYPERLINK("#D"&MATCH(A2002,$A$1:$A$2000,0),A2002&"の個数セルに飛びます。"))

という数式を入れ、B2002～D2002を範囲指定しD2002セルのフィルハンドルで下へコピーします。

結局このような方法でしか対応できないと思います。

尚、今回の数式は1行目から範囲指定していますので、前回までの数式とは少し違うということが
理解してもらえれば良いと思います。
（前回までは2行目からの範囲指定だったので「1」をプラスしたりして、参照する行を調整していました。

以上、今回も長くなってしまいましたが
参考になれば幸いです。m(__)m

お礼

何度もありがとうございます。

やはり、表の下にしかできないですよね。
私も色々やってみましたが、循環参照になってしまい、出来ませんでした。

本当に助かりました。

ありがとうございます。

回答No.4

tom04

No.2・3です！
またまた・・・失礼します。

データ量の件ですが、データ量を気にせず列全てに対応する数式にしてみました。
前回の表の配置をそのまま使わせてもらいます。

G2セルに
=IF($F2="","",VLOOKUP($F2,$A:$D,5-COLUMN(A1),0))
として隣のH2セルまでオートフィルでコピー

I2セルを
=IF($F2="","",HYPERLINK("#D"&MATCH($F2,A:A,0),F2&"の個数セルに飛びます。"))

という数式にします。

最後にG2～I2セルを範囲指定し適当な行までオートフィルでコピーではどうでしょうか？

これで元データがExcelの最終行まであっても対応できます。

以上、何度もごめんなさいね。m(__)m

お礼

わざわざありがとうございます!!!!
大変参考になりました。

最後の最後で申し訳ないですが、
この表を出来たら縦に並べたいのですが、
今のままやってみるとリンク先がうまく表示されません。

どこをいじればいいのでしょうか？

本当に何度も申し訳ありません。

回答No.3

tom04

No．2です！
再びお邪魔します。

別Sheetではなくて、同一Sheetに表があるわけですよね？
その場合はもう少し簡単な数式になります。

画像では何行目の何列目か判らないので勝手に↓のようにもう一度表を作ってみました。

尚、余計なお世話かもしれませんがG・H列の数式も書いておきます。

G2セルに
=IF($F2="","",VLOOKUP($F2,$A$2:$D$1000,5-COLUMN(A1),0))
として隣のH2セルまでオートフィルでコピーします。

そして、I2セルに
=IF($F2="","",HYPERLINK("#D"&MATCH($F2,$A$2:$A$1000,0)+1,F2&"の個数セルに飛びます。"))

という数式を入れています。（とりあえず元データの1000行目まで対応できます）
最後にG2～I2セルを範囲指定し、I2セルのフィルハンドルで下へオートフィルでコピーすると
画像のような感じになります。

これでF列に商品名を入力するとその個数と保存場所が表示され、I列をクリックすると
その商品の個数のセルがアクティブになりますので、そこで数値を変更できると思います。

以上、長々と書きましたが
お役にたちますかね？m(__)m

お礼

わざわざありがとうございます。

大変参考になりました。

無事出来ました。

最後に厚かましいようですが、
1000以上のデータを検索範囲にする場合は、

G2セルに
=IF($F2="","",VLOOKUP($F2,$A$2:$D$1000,5-COLUMN(A1),0))

=IF($F2="","",HYPERLINK("#D"&MATCH($F2,$A$2:$A$1000,0)+1,F2&"の個数セルに飛びます。"))

の1000という値を使用する範囲まで設定すればいいんですよね？

回答No.2

tom04

こんばんは！
参考になるかどうか判りませんが・・・
一つの案です。

↓の画像でSheet2のD2セルに
=IF(A2="","",HYPERLINK("#sheet1!D"&MATCH(A2,Sheet1!$A$2:$A$1000,0)+1,A2&"の個数セルへ飛びます。"))

という数式を入れ、オートフィルで下へコピーしています。
これでD列をクリックするとSheet1の「商品名」の「個数」セルがアクティブになるはずです。
（とりあえず、Sheet1の1000行目までデータがあっても対応できる数式です。）

以上、お役に立てば良いのですが
的外れなら読み流してくださいね。m(__)m

補足

回答ありがとうございます。

tom04さんの案だとシートを2つ使うということですよね？

ちょっと試してみます。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

基本的にVBAを使います。
マクロを使って抽出したり、検索したり、
逆に引っ張ってきたものを元に戻すようにしたりして使用します。

ところで、部品名に重複はないのでしょうか？
ないという前提のもと、関数でできる代案です。
D3セルに
=HYPERLINK("#Sheet1!"&ADDRESS(MATCH($A3,Sheet1!$A:$A,0),4),"個数修正")
下へオートフィル
D3セルをクリックすると対象のセルへジャンプします

お礼

無事解決することができました。

ありがとうございます。

補足

回答ありがとうございます。

部品名の重複はありません。
最終的には型式をいれて検索できるようにする予定です。