エクセルのVLOOKUPに関して

質問

お世話になっております。

VLOOKUP関数になるのか他の関数になるのか分からないのですが
ご教授よろしくお願い致します。

A1セル　　　　　 A２セル　　　　　　A３セル
店番号　　　　　 商品名　　　　　　 担当
１　　　　　　　　　ア　　　　　　　　　 山口
２　　　　　　　　　イ　　　　　　　　　 大山
３　　　　　　　　　ウ　　　　　　　　 　斎藤
１　　　　　　　　　エ　　　　　　　　　 中村
１　　　　　　　　　オ　　　　　　　　　 山口

上記のような情報がシート１にあり、
シート２のA1セルに店番号を入力した時に
シート２のB列に同じ店番号を持つ商品名をB1、B2、B3...セルへとリスト化し、
C列に担当をC1、C2、C3...セルへとリスト化したいのですが、
どのような関数を使用すれば可能となりますでしょうか。
よろしくお願い致します。

回答No.4

tom04 · ベストアンサー

こんにちは！
極端にデータ量が多い場合はあまりオススメできませんが・・・
一例です。

↓の画像のB1セルに
=IF(COUNTIF(Sheet1!$A$1:$A$100,$A$1)<ROW(A1),"",INDEX(Sheet1!B$1:B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A$1,ROW($A$1:$A$100)),ROW(A1))))
これは配列数式になってしまいますので、Shift+Ctrl+Enterキーで確定！
この画面からB1セルにコピー＆ペーストする場合は、B1セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列方向・行方向にオートフィルでコピーすると、画像のような感じになります。
とりあえずSheet1の100行目まで対応できる数式です。

※　1000行程度であれば問題ないかもしれませんが、配列数式はPCにかなりの負担を掛けますので
データ量が多い場合は作業列を設けるなどの工夫をする、もしくはVBAにするといったコトを考える必要があると思います。

参考になりますかね？m(_ _)m

補足

ご返答有難うございました。
tom04さんのやり方でも出来ました！

最終的には数万単位のデータを予想してますのでVBAなども検討させて頂きます！
有難うございました。

回答No.8

shinkami

対象データが数万件ならデータベースのAccessですね。

自分は小さい事業所のシステム担当で1行を超えるとACCESSでやってもらっています。

もしAccessが使用可能なら試して下さい

1．テーブルを2つ定義します。
1-1.売上テーブル(仮称)
　売上ID　オートナンバー型　主キー
　店番号　数値型
　商品名　テキスト型
　担当　　テキスト型

1-2.　店テーブル
　店番号　数値型　主キー

２．売上テーブルと店テーブルに店番号でリレーション湿布を設定します。

３、元データをドラッグまたはインポートでテーブルにコピーします
3-1.元データ→売上テーブル
　売上IDはオートナンバー設定になっていますので自動採番です
3-2.元データ→店テーブル
　店番号は主キーになっていますので重複データは排除されます

以上で店テーブルを開いて左端の＋記号をクリックした状態です(店番号２，３は＋のまま）

数万件のデータなら数秒で結果が出ます。

尚、フォーム(画面設定）　レポート(報告書）でより良いシステムも可能でしょう

回答No.7

noname#204879

[No.2補足]へのコメント、
》 シート２のコピーしたセルに#NUM!エラーが出てしまいます。
》 （データがリスト化されたセル以外です）

「リスト化されたセル」の「「リスト化」とはどういうこと？
「#NUM!エラー」の件だけど、私が回答の冒頭に書いた「上の[条件付き書式]を設定」をチャンと実行しましたか？

回答No.6

myi333

難しい関数やVBAを使わなくても、ピボットテーブルで解決するような気がします。

回答No.5

tom04

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

＞最終的には数万単位のデータを予想してますので・・・

とありますので、VBAでの一例です。

Alt+F11キー　→　画面左下の「This Workbook」をダブルクリック　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
i = ws2.Cells(Rows.Count, 2).End(xlUp).Row
Range(ws2.Cells(1, 2), ws2.Cells(i, 3)).ClearContents
ws1.Columns("A:C").AutoFilter Field:=1, Criteria1:=ws2.Cells(1, 1)
i = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Range(ws1.Cells(2, 2), ws1.Cells(i, 3)).Copy Destination:=ws2.Cells(1, 2)
ws1.Select
Selection.AutoFilter
ws2.Activate
ws2.Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数ではないのでSheet1のデータ変更があってもすぐにSheet2に反映されません。
Sheet1のデータ変更があるたびにマクロを実行する必要があります。

お役に立ちますかね？m(_ _)m

回答No.3

shinkami

再考をお勧めします。
１．一つのセルには一つのデータしか入力出来ません
　敢て入力するとしたら
　A1には「店番号 1　2　3 1 1」
B1には「商品名 ア イ ウ エ オ」
C1には「担当 山口 大山 斎藤 中村 山口」
　となります。
　違いますよね！　添付のようなことと推察します。

２．シート1が添付のようなものとして、ご要望のように
　展開するのは大変難しいです。

解決策としては
　2-1.フィルタ機能活用
　2-2.並び替え
　2-3.ピボット機能活用

補足

ご返答有難うございました。
フィルター機能、並び替え以外で何かないかなと模索してました。
ピボットは試してないので是非やってみようと思います。

回答No.2

noname#204879

添付図参照

　　数式が　　　=ISERROR(B1)
　　フォント色　白
上の[条件付き書式]を設定したセル Sheet2!B1 に次の配列数式を入力して、此れを右および下方にドラッグ＆ペースト
{=INDEX(Sheet1!B$1:B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A$1,ROW(Sheet1!A$1:A$100)),ROW(Sheet1!A1)))}

【余談】
質問文中の表の最上行の「A1セル、A２セル、A３セル」は「Ａ列、Ｂ列、Ｃ列」の間違いでは？ご注意あれ！

補足

ご返答有難うございます。
ご指摘の通りA列、B列、C列です。
申し訳御座いませんでした。

まず、mike_gさんのやり方で無事リスト化出来ました！
有難うございました。

もう１点だけ教えて頂ければと存じ上げます。
画像添付できなかったので文章で失礼いたします。

シート２で数式を設定したセルを下方まで（B1:C100）コピーした場合、
シート１に(A1:C10)までしかデータが入っていないと
シート２のコピーしたセルに#NUM!エラーが出てしまいます。（データがリスト化されたセル以外です）

上記を解消するにはどうすればよろしいでしょうか。
よろしくお願い致します。

回答No.1

KURUMITO

シート2のＡ1セルに店番号を入力したのちにＢ1セルには次の式を入力し、式を確定する段階でＣｔｒｌキーとＳｈｉｆｔキーを押しながらＥｎｔｅｒキーを押します。これで入力した式の両側には｛　　｝の付いた式となります。Ｃ1セルにドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNTIF(Sheet1!$A$1:$XA$1,$A$1),"",IF(COLUMN(A1)=1,INDEX(Sheet1!$A$2:$XA$2,SMALL(IF(Sheet1!$1:$1=$A$1,COLUMN($A1:$XA1)),ROW(A1))),IF(COLUMN(A1)=2,INDEX(Sheet1!$A$3:$XA$3,SMALL(IF(Sheet1!$1:$1=$A$1,COLUMN($A1:$XA1)),ROW(A1))),"")))

Ａ1セルの店番号を変えることで表が変わりますね。

補足

ご返答有難うございました。
試してみた所、空白を返されてしまい、何も出てこないのですが（汗
私のほうが間違った手順で進めていたら申し訳御座いません。