こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルのVLOOKUPに関して

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

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

A1セル      A2セル      A3セル
店番号      商品名       担当
1         ア          山口
2         イ          大山
3         ウ          斎藤
1         エ          中村
1         オ          山口

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

投稿日時 - 2012-04-27 09:59:02

QNo.7443788

困ってます

質問者が選んだベストアンサー

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

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

投稿日時 - 2012-04-27 14:09:18

補足

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

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

投稿日時 - 2012-04-27 14:37:17

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

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

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

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

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

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

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

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

以上で店テーブルを開いて左端の+記号をクリックした状態です(店番号2,3は+のまま)

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

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

投稿日時 - 2012-04-28 21:38:31

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

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

投稿日時 - 2012-04-28 15:19:38

ANo.6

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

投稿日時 - 2012-04-28 12:25:11

ANo.5

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

投稿日時 - 2012-04-27 14:53:41

ANo.3

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

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

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

投稿日時 - 2012-04-27 11:38:41

補足

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

投稿日時 - 2012-04-27 14:34:54

添付図参照

  数式が   =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セル、A2セル、A3セル」は「A列、B列、C列」の間違いでは?ご注意あれ!

投稿日時 - 2012-04-27 11:09:44

補足

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

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

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

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

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

投稿日時 - 2012-04-27 14:26:15

ANo.1

シート2のA1セルに店番号を入力したのちにB1セルには次の式を入力し、式を確定する段階でCtrlキーとShiftキーを押しながらEnterキーを押します。これで入力した式の両側には{  }の付いた式となります。C1セルにドラッグコピーしたのちに下方にもドラッグコピーします。

=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))),"")))

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

投稿日時 - 2012-04-27 10:37:51

補足

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

投稿日時 - 2012-04-27 14:01:11

あなたにオススメの質問