vlookupを使って番号ごとにまとまった分類表を作り、番号を表示させたい

お店で毎日発売される商品を、発売されるたびに１～100番まで番号で分類し分類表に追加する作業をしています。
図のように、作成した分類表に存在する商品名をF列に入力すると、番号が表示されるようにしたいのですが、タテに１～100までの分類表を作成すると長くなりすぎて、「８８番の欄に一品追加したい」と思った時などいちいち場所を探してとても大変です。
また、各番号の商品が今後いくつ発売されるかなど分からないため、その都度行を挿入したりととても不便を感じています。
Vlookの検索範囲にもなり、番号ごとにもまとまっている見やすい表を作るにはどうしたらいいでしょうか。どなたか知恵をお貸し下さい。

質問者からの補足コメント

• No.2様のお礼内容に記載の画像を添付しました。

  
  No.2の回答に寄せられた補足コメントです。 補足日時：2017/02/19 19:07

No.5 回答者： yuji3690 回答日時： 2017/02/19 23:07

すべての式を入力し終っているならば、COLUMN以外は、列の挿入で移動しても、移動後のセルを参照してくれます。

列の挿入後に式を入れる場合は、参照セルを調整する等の対応が必要です。

1列ずつ挿入する方法は分からないです。
列全体を選択する所（列名が書かれている所）を右クリックしてキーボードのI、1つ横で右クリックしてキーボードのI、と繰り返すか、
表自体を作り直して、それに合わせた式に直すか、
のどちらかでしょう。

因みにCOUNTIF*ROWと言った感じ(ROWはCOLUMNの行バージョンです)にして、列全体をSUMで合計する事で、
№3さんへの、お礼に書いた様な表からでも同様の検索はできましたよ(汗
行列が逆になっただけですからね。
VLOOKUPの行列逆バージョンとしてHLOOKUPというものもあります。

No.4 回答者： tatsu99 回答日時： 2017/02/19 23:01

>提出用に必要なのはこのような番号ごとにまとまった表(お礼欄記載)

>１　Tシャツ　　セーター　　スカート・・・
>２　ハンカチA　ハンカチB　ハンカチC・・・
>３
>４
>５
>理想はこの表からVlookで質問欄添付EF列のように番号をひろいたい
>それができないから添付のBC列のような作業を現状している
>それが不便だからなんとかならないか
>という意味でございます

この表がSheet1にあるとします。添付の図を参照。
この表からVlookで質問欄添付EF列のように番号をひろいたい・・この表がSheet2のA,B列にあるとします。（添付の図を参照）

A2のセルへ
=INDEX(Sheet1!$A$1:$A$101,MAX(IF(Sheet1!B$2:$Z$101=B2,ROW($B$2:$B$101))))
と入力し、Shift+Ctrl+Enterキーを押下してください。（配列数式です）
それをオートフィルで下へコピーしてください。
それで、番号が求められます。

Sheet1の最大行は１０１行
Sheet1の最大列はZ列
の前提です。もし、その前提が変わる場合は、１０１とZを適切に変えてください。

No.3 回答者： chonami 回答日時： 2017/02/19 11:36

No.1のお礼の内容が理解できません。

別シートって何ですか？
添付と違うことをしているならそれをきちんと書いてもらわないと意味がないですよね？

この回答へのお礼

別で作業しているのが現状＝質問欄にのせた表のことです

提出用に必要なのはこのような番号ごとにまとまった表(お礼欄記載)

１　Tシャツ　　セーター　　スカート・・・
２　ハンカチA　ハンカチB　ハンカチC・・・
３
４
５

理想はこの表からVlookで質問欄添付EF列のように番号をひろいたい
それができないから添付のBC列のような作業を現状している
それが不便だからなんとかならないか

という意味でございます

お礼日時：2017/02/19 13:49

No.2 回答者： yuji3690 回答日時： 2017/02/19 09:12

そうですねぇ。

変えるとしても、縦にずっとスクロールするのが、横に100列スクロールするのに変わるくらいですが、セルを追加する必要はなくなりますね。

※注意:同じ商品名のものは複数存在しないものとします。
　元々VLOOKUP使ってるので問題ないでしょうが。

同じシート内でも可能ですが、用途毎でシートを3つに分けて説明します。

まず、シート名:分類表　に、
B1=COLUMN()-1
として、C1〜CW1にコピーします。
これによって1～100の番号が入ります。
各列の2行目以降には1行目に表示された番号に一致する分類番号の商品を入力してください。

次に、シート名:判定　に、
B1=COLUMN()-1
B2=COUNTIF(分類表!B:B,表示用!$B2)*B$1
A2=SUM(B2:CW2)
としてください。表示用というシートは次で作ります。
これにより、B2には「分類表の同じ列に、表示用の同じ行に入力された商品名があれば」1行目のデータ=分類番号を表示します。なければ0です。
それを1行分合計したもの(つまり該当する分類番号)をA2に表示します。
B1はC1〜CW1に、B2はC2〜CW2に、それぞれコピーし、
必要な行数（検索商品の予定数。余裕を持って多めに作っておけば良い）まで2行目をコピーしてください。

最後は先程出た、シート名:表示用　に
A1=番号表示
B1=検索商品
A2=判定!A2
として、B2に商品名を入力してください。
商品名が増える毎にA列に式をコピーしてください。
A列に0と表示される場合は、入力データが一致していない＝検索商品又は分類表の商品名の入力ミス、もしくは判定シートの式を入力したセルを超える数の検索を行おうとしている、のどちらかだと思います。

これでいけると思うのですが、動作確認をおねがいします。
なお、シート毎に分かりやすくしたつもりなので、一部内容の重複した表示が含まれています。

この回答へのお礼

すごい！！ありがとうございます！見事に番号がきちんと表示されました！！
ちなみに、補足で画像を添付したのですが、最終的にこのような形の分類表ができればなぁと思っております。
このように分類表シートだけに行と列を挿入しても、他の２つのシートの計算式に関しては、
回答で書いて下さったそのままの計算式でいじらずに使うことは可能でしょうか？
(試しに表示用シートに数個商品を入れてみたところ、そのまま使えそうな気はしたのですが、途中で計算式がずれたり
しないかが不安です・・)
また、図のように補足事項(値段など)を書き込む欄も隣に欲しいため、間に１列ずつ挿入していきたいのですが、
１～１００番まで一気に一列ずつ挿入する方法はありますでしょうか？
(１～１００番までの番号に関しては、関数を使わずに普通に番号をベタ打ちで入力してみました。)
お忙しい中恐れ入ります。。

お礼日時：2017/02/19 19:18

No.1 回答者： chonami 回答日時： 2017/02/19 09:02

>「８８番の欄に一品追加したい」

何もバカ正直に88番の欄に入力しなくても、手近な場所に行を挿入してデータを入力してから、分類表の番号で昇順並べ替えすればいいのでは？

番号ごとに空白行を入れているみたいですが、一覧表を作成するのにそんなことはしない方がいいですよ。
新しい番号を入れるように空けているのかと思うのですが、それがなければ普通に並べ替えできますよね？

この回答へのお礼

ご回答ありがとうございます。
番号ごとにまとまった一覧表は毎月提出する必要もあり、
今まではタテ軸に１～１００番まで作り、それぞれ番号の行にヨコにヨコに商品名を追加・・・という表を作っていたのですが、この表ではここからVlookで番号を拾うことができないため、
別でVlookに飛ばすためのシートを作成しているのが現状です。
なにせ商品が多く２回も入力するのが手間なので、一回の作業で一覧表＋Vlookで検索できる表を作りたいと思い、
試行錯誤しています・・・

お礼日時：2017/02/19 10:59