エクセルのVLOOKUPに関して

Question

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

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

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

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

tom04 · Accepted Answer

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

↓の画像の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

shinkami · Answer

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

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

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

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

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

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

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

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

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

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

mike_g · Answer

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

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

myi333 · Answer

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

tom04 · Answer

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

shinkami · Answer

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

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

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

mike_g · Answer

添付図参照

数式が　　　=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３セル」は「Ａ列、Ｂ列、Ｃ列」の間違いでは？ご注意あれ！

KURUMITO · Answer

シート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セルの店番号を変えることで表が変わりますね。

エクセルのVLOOKUPに関して

こんにちは！

この回答への補足

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

[No.2補足]へのコメント、

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

No.4です！

再考をお勧めします。

この回答への補足

添付図参照

この回答への補足

シート2のＡ1セルに店番号を入力したのちにＢ1セルには次の式を入力し、式を確定する段階でＣｔｒｌキーとＳｈｉｆｔキーを押しながらＥｎｔｅｒキーを押します。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング