【エクセルVBA】2つの条件からデータを抽出して、表を作る方法

質問

エクセル2003VBAでの質問というか、回答を知りたいのです・・・

例えばエクセルで下記のようなデータがあり
　A列　　　　　　B列　　　　C列
1 JJJAAA 　　　　 あ 　　　1000
2 KKKBBB 　　　　い 　　　2000
3 KKKCCC 　　　　う 　　　1000
4 LLLBBB 　　　　あ 　　　1000
5 LLLDDD 　　　　い 　　　1000
6 MMMEEE 　　　　う 　　　2000
7 MMMAAA 　　　　あ 　　　2000
8 NNNEEE 　　　　あ 　　　3000

下記のような、

A列　　　　　 B列 C列　D列
1 　　　 　　 　あ　　い　　う　
2 JJJAAA
3 KKKBBB
4 KKKCCC
5 LLLBBB
6 LLLDDD
7 MMMEEE
8 MMMAAA
8 NNNEEE

表枠が出来上がった中に数字を入れていく（データの入らないマスは空白）、
ようなものを作りたいのですが、全く出来ませんでした。

どなたか教えていただけないでしょうか？
回答を見て勉強させてください。
よろしくお願いします。

回答No.4

rukuku · ベストアンサー

＞なんとかVBAでご回答いただけないでしょうか。
VBAなら以下のようになります。
条件は、
　・元データのシート名がSheet1
　・出力先のシートが同じブックのSheet2
　・出力先のシートには、「見出し」として１行目とA列の値が入力済み
とします。

Sub test()
　Dim i As Long
　Dim Gyo, Retsu As Range

　ThisWorkbook.Activate
　Worksheets("Sheet2").Select
　With Worksheets("Sheet1")
　　For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
　　　Set Gyo = [A:A].Find(.Cells(i, "A"), lookat:=xlWhole)
　　　Set Retsu = [1:1].Find(.Cells(i, "B"), lookat:=xlWhole)
　　　If Not (Gyo Is Nothing Or Retsu Is Nothing) Then Cells(Gyo.Row, Retsu.Column) = .Cells(i, "C")
　　Next i
　End With
End Sub

お礼

さっそく試してみて出来ました。
No.3のかたが言っていたようにFindを使うんですね。
教えていただいたコードをただ貼り付けて使うんではなく、
これから理解していこうと思います。

わがままな質問にお付き合いくださいまして
ありがとうございました。

回答No.5

rukuku

補足です。

imogasiさんがおっしゃる
＞これらは２つのシートが関係しているので、これをどう区別して扱うか知ってないとならない。
は、
　Worksheets("Sheet2").Select
　With Worksheets("Sheet1")
　…
End With
で解決しています。
シートを特に指定しない場合には、Sheet2が採用されます。
シートを指定した場合にはそのシートがシートが採用されますが、Worksheets("Sheet1")も頻繁に出てきますので、表記を簡単にするために
With Worksheets("Sheet1")
を使って、頭に“.”がついていたら、「Worksheets("Sheet1")」が省略されたものとする、という指示を出しています。

また、
＞最終行まで繰り返すということで、入れ子になったfor～next
最終行の取得は「決まり文句」です。

かつては
[A65536].End(xlUp).Row
を使いました（バージョン2003以前に対応です）。
バージョン2007で使用できる行数が増えましたので、2007とそれ以前の、両方のバージョンに対応できるよう
Cells(Rows.Count, "A").End(xlUp).Row
としました。
もっとも、どちらも、「扱える最後の行までデータを入力しない」ということを前提としています。

＞処理がまだ頭の中ですんなり思い描けないでいます。
これは、アドバイスできません。
慣れるしかありません。
p(^^)q

回答No.3

imogasi

VBAのコードを回答者に書かせる要望だが、規約違反。自分で勉強し、もう少し論点を絞って質問のこと。
エクセル関数でも出来る課題であって、VBAで出来ないなら関数で我慢するのが普通だと思う。
ーー
まず行としてA列に原データに出てくるJJJAAA 等の、重複無く、漏れの無い一覧データは出来ているのか。
同じくB列の、あいう・・も重複無く、漏れの無い一覧データは出来ているのか。
これらも、データーフィルターフィルタオプションー「重複するレコードは無視する」で作れる。
これもVBAでやらないと気がすまないのか。
原データB列の場合も、重複無く、漏れの無い一覧データは「重複するレコードは無視する」で作って、形式を選択して貼り付けで「行列を入れ替える」で第１行目に横方向に並べられる。
これがイヤならVBAでどうすればよいか考えるのが第１の課題だ。
また処理が進むと同時に重複の無いリストを作って行くロジックも考えられる。
またA+B列でデータはユニークなのかどうか質問に書いてないのは
こういう処理の経験の無さをうかがわせる。ユニークでなければ
同じのが現れると足し算するのか。
ーー
上記が出来てしまえば、A列のコード（JJJAAAなど）を、Sheet2のA列データのどこ（何行目）になるかFindメソッドで見つけ、また原データのB列の「あ、い、う」はSheet２の第１行のどこ（何列目）に有るかをFindメソッドで探す。
Sheet2のi行、j列に有ることがわかれば、Cells(i,ｊ)=「原データC列」と書けば仕舞い。
少し正確には　Worksheets("Sheet2").cells(i,j)=)=「原データC列」
これを最終行まで各行繰り返す。
これらは２つのシートが関係しているので、これをどう区別して扱うか知ってないとならない。
ーー
本課題はVBAの中級の課題だと思う。上記で何のことを言っているかピンとこないなら、この課題は質問者にはやるには早すぎると言うことと思う。

お礼

回答ありがとうございます。

＞データーフィルターフィルタオプションー「重複するレコードは無視する」で作れる。

フィルタオプションで重複のないデータを抽出することは
マクロの記録を参考にできました。

＞A+B列でデータはユニークなのかどうか

説明不足ですみませんでした。
原データ側に漏れはなく、A列＋B列はユニークなデータです。

Findを使用する方法があるのですね、調べてみます。
最終行まで繰り返すということで、入れ子になったfor～next
の処理がまだ頭の中ですんなり思い描けないでいます。。。

回答No.2

rukuku

こんばんは

VBAでも、関数でもないのですが「ピボットテーブル」はいかがでしょうか
この機能を使うには、まず、タイトル行を挿入しておいてください。

データのある範囲を元に、ピボットテーブルを作ったら、
「行のフィールド」にA列
「列のフォールド」にB列
「データアイテム」にC列
をそれぞれドラッグ＆ドロップします。

総計が不要ならばオプションで
「列の総計」「行の総計」のチェックを外してください。

お礼

回答ありがとうございます。

出来ました。ピポットテーブルも表を作るのに便利ですね。

ただ今回はVBAでの方法を募集しています。
VBAの勉強がしたいものでして・・・
なんとかVBAでご回答いただけないでしょうか。

回答No.1

tom04

こんばんは！
VBAではないので、希望に添えない場合は、無視してください。

関数を使った方法で
↓の画像のように、Sheet1のデータをSheet2に表示させるようにしています。

作業列を使っていますので、目障りであれば作業列を非表示にしてみてください。

まず、Sheet1の作業列のD2セルに
=A2&B2
としてオートフィルで下へコピー

次にSheet2のB2セルに
=IF(ISERROR(INDEX(Sheet1!$C$2:$C$9,MATCH($A2&B$1,Sheet1!$D$2:$D$9,0))),"",INDEX(Sheet1!$C$2:$C$9,MATCH($A2&B$1,Sheet1!$D$2:$D$9,0)))
という数式を入れて、列方向と行方向にオートフィルでコピーすると
↓の画像のような表になります。

以上、参考になれば幸いですが、
他に良い方法があれば軽く読み流してくださいね。m(__)m

お礼

すいません、関数なら私もなんとか出来るんですよ＾＾；
今回はマクロで表を作成したいものでして・・・

でも、わざわざありがとうございました。