検索値を表示させる関数

質問

教えてください。
　製品の構成シートですが、元データがばらばらでまとめるのに困ってます。

シートＢ
　　　ＡＷ　　　　ＡＹ　　　　ＡＺ　　　　　ＢＢ　　　　　ＢＣ　　　　ＢＥ　　　
4　　種類　　　使用数1　　種類　　　使用数2　　種類　　　使用数3
5　　船　　　　　　2　　　　　電車　　　　　1　　　　飛行機　　　　2
6　　車　　　　　　3　　　　　船　　　　　　0　　　　　電車　　　　　1
7　　電車　　　　0　　　　　飛行機　　　　0　　　　　船　　　　　　2
8　　飛行機　　　1　　　　　車　　　　　　　1　　　　　車　　　　　　3

シートＡ
　　　Ａ　　　　　Ｂ　　　　　　Ｃ　　　　　　　　　Ｄ　　　　　　　Ｅ　　　　　　　Ｆ
2　　種類　　　場所　　合計使用数　　　使用数１　　　使用数2　　　使用数3
3　　車　　　　道路　　　　　6　　　　　　　　　3　　　　　　　　1　　　　　　　3　
4　　電車　　　線路　　　　2　　　　　　　　　0　　　　　　　　　1　　　　　　　1
5　　飛行機　　空　　　　　3　　　　　　　　　1　　　　　　　　　0　　　　　　　2
6　　船　　　　　海　　　　　4　　　　　　　　　2　　　　　　　　　0　　　　　　　2

　シートＢでのＡＹ、ＢＢ，ＢＥでの使用数をシートＡの列Ｄ，Ｅ、Ｆに表示させたい
　のですが、どのような関数をシートＡのＤ3、E4、Ｆ5のセルに入れれば表示
　しますか？

　もう一つありますが、シートＡからの検索です。
　　　　シートＣ 　　　　　　　
　　　　Ｄ　　　　Ｅ　　　　Ｆ　　　　　　　　　　　
　1　　コード　　名称　　略名　
　2　　(1)　　　　青　　　　ｂ
　3　　(2)　　　　赤 　　　 ｊ
　4　　(3)　　　　黄 　　　 f
　5　　(7)　　　　黒　　　　ｑ

　　　　　シートＤ
　　　　Ｂ　　　　　　　　　　　　Ｋ
　1　　コード 　　　　 　　略名
　2　　(7) 　　　　　 　　　　q
　3 　(3)　　　　　　　　　　　f
　4 　(1) 　　　　　　　　　　ｂ

シートＣの略名をシートＤに検索し、表示させたいのですが、
シートＤのＫ2セルにはVLOOKUPをどのようにつかえばよいのですか？
3000点位の検索があります。

回答No.5

noname#204879

シートＡ!D2: =VLOOKUP($A2,OFFSET(シートＢ!$AW$4,1,(COLUMN(A1)-1)*3,4,3),3,FALSE)
ただし、シートＡ!C2 の「使用数１」は、シートＢ!AY4 の「使用数1」に変更する必要あり（末尾の数字がそれぞれ全角、半角になって統一されていない！）。

回答No.4

imogasi

MATCH関数と、INDEX関数を組み合わせる関数の中級問題。
大体ほとんどの解説書に載っている。
例データ
A1:G５
4 種類 使用数1 種類 使用数2 種類 使用数3
5 船 2 電車 1 飛行機 2
6 車 3 船 0 電車 1
7 電車 0 飛行機 0 船 2
8 飛行機 1 車 1 車 3
ーーー
例えばA10:G14に下記のような表の項目見出し等手作業で作ると言う前提なら（ここが質問ではっきり書いてない。考えが甘い。関数ではこちらのほうが難しい。）
- 種類 場所 合計使用数 使用数１ 使用数2 使用数3
- 車
- 電車
- 飛行機
- 船
ーーー
式　E11に　=INDEX($B$2:$G$9,MATCH($B11,$B$2:$B$9,0),(COLUMN()-5)*2+2)
と入れてE14まで式複写。
F11に
=INDEX($B$2:$G$9,MATCH($B11,$D$2:$D$9,0),(COLUMN()-5)*2+2)
と入れてF14まで式複写。
G11に
=INDEX($B$2:$G$9,MATCH($B11,$F$2:$F$9,0),(COLUMN()-5)*2+2)
と入れてG14まで式複写
ーーー
結果
- 種類 場所 合計使用数 使用数１ 使用数2 使用数3
- 車 3 1 3
- 電車 0 1 1
- 飛行機 1 0 2
- 船 2 0 2
あとD列の横合計など常識なので省略
問題はE,F,G列で式が違う。E11の式を２次元的に複写するのはとりあえず略。列のほうはやや簡単なので上記式でも取り入れ済み。

回答No.3

tom04

No.2です！
たびたびごめんなさい。

先ほど投稿したあとで質問を読み直していたところ
SheetBの列で抜けているところがあるのですね？

こちらで勝手にSheetBを作ってそのまま数式にしていますので
たぶんまともに表示されないと思います。

前回の数式は↓の画像データを元にしての数式になりますので
参考にしていただければ幸いです。

どうも失礼しました。m(__)m

お礼

何度も有難うございます。

先ほどのをヒントに出来ました。
助かりました。

回答No.2

tom04

こんにちは！
最初の質問に関しては、SUMIF関数
次の質問に関しては、VLOOKUP関数でOKだと思いますよ。

↓の画像で説明させていただきます。
尚、データとしては質問内容どおりの行数しかないので
行番号等はアレンジしてみてください。
最初の使用数の件に関しては
D2セル　=SUMIF(SheetB!$AW$2:$AX$5,A2,SheetB!$AX$2:$AX$5)
E2セル　=SUMIF(SheetB!$AY$2:$AZ$5,A2,SheetB!$AZ$2:$AZ$5)
F2セル　=SUMIF(SheetB!$BA$2:$BB$5,A2,SheetB!$BB$2:$BB$5)
C2セル　=SUM(D2:F2)
としてC2～F2セルを範囲指定　→　オートフィルマークコピー又はオートフィルの（＋）マークでダブルクリック。

次の質問は
SheetDのK2セルに
=VLOOKUP(B2,SheetC!$D$2:$F$5,3,0)
としてオートフィル
これで希望に近い形の物ができるのではないでしょうか？
尚、エラー処理は行っていませんのでよろしくお願いします。

以上、参考になれば幸いです。m(__)m

回答No.1

okdeath

こんにちわ。
シートＢをシートＡについかについてですが、手軽なのはピボットテーブルだとおもいます。
データをこのように縦にしてください。（データを拝見すると、たいした手間じゃないように見えるので）
使用数　種類　　　使用数
使用数１　船　　　　2
使用数１　車　　　　3
使用数１　電車　　　0
使用数１　飛行機　　1
使用数２　電車　　　1
使用数２　船　　　　0
使用数２　飛行機　　0
使用数２　車　　　　1
使用数３　飛行機　　2
使用数３　電車　　　1
使用数３　船　　　　2
使用数３　車　　　　3

そして、列フィールドにタイトル
行フィールドに種類
値のところに使用数を入れてください。
最後に使用数を右クリックしてメニューを開き、「フィールドの設定」が合計になっているか確認してください。
下にサンプル図をつけます。

シートＤのvlookupについて
Ｋ２：=VLOOKUP(B2,$D$2:$F$5,3,0)
これを必要な場所まで下にコピペでｏｋです。

お礼

参考意見ありがとうございました。

補足

回答有難うございます。
VLOOKUPの件は無事ｏｋでした。

シートＢをシートAに検索表示ですが、元データーの日々更新とそのままシートＡを印刷したいと思います、データは3000件程です。
関数での検索は無理でしょうか？