各シートの抽出条件を[リスト一覧]シートからデータを抽出したい。
※抽出条件は、[都道府県]と[品質]が一致したとき
※抽出したい項目は、[送料]等の項目
[リスト一覧]シート
3 都道府県 送料 品質
4 青森 元払い AA
5 岩手 着払い AAA
6 愛媛 相談 AA
7 愛知 元払い AAA
---集計後のイメージ---
[りんご]シート
3 都道府県 品質 入金 出金 損害金 送料
4 青森 AA 500 1000 300 元払い
5 岩手 AAA 1000 300 100 着払い
[みかん]シート
3 都道府県 品質 入金 出金 損害金 送料
4 愛媛 AA 300 900 100 相談
5 愛知 AAA 900 800 500 元払い
マクロでの回答は、ソースも記述願います。
どうか宜しくお願いします。
ワークシート関数だけで完結させる例
リスト一覧
A | B | C | |
---|---|---|---|
3 | 都道府県 | 送料 | 品質 |
4 | 青森 | 元払い | AA |
5 | 岩手 | 着払い | AAA |
6 | 愛媛 | 相談 | AA |
7 | 愛知 | 元払い | AAA |
りんご
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
3 | 都道府県 | 品質 | 入金 | 出金 | 損害金 | 送料 |
4 | 青森 | AA | 500 | 1000 | 300 | ここに後述の式を入れる |
5 | 岩手 | AAA | 1000 | 300 | 100 | これ以下はコピーすればOK |
F4に以下の式を入力します
=INDEX(リスト一覧!$B$4:$B$10,SUMPRODUCT((リスト一覧!$A$4:$A$10=$A4)*(リスト一覧!$C$4:$C$10=$B4)*ROW(リスト一覧!$A$4:$A$10))-3)
F5以下に式をコピーします
F4の式をコピーして、みかんシートにも同様に貼り付けすれば良いようにしてあります
($はコピペしても変化しないようにするための符号です)
式の解説
SUMPRODUCT((リスト一覧!$A$4:$A$10=$A4)*(リスト一覧!$C$4:$C$10=$B4)*ROW(リスト一覧!$A$4:$A$10)
リスト一覧シートの$A$4:$A$10の範囲で、りんごシートのA4(青森)と合致し、
かつ、リスト一覧シートの$C$4:$C$10の範囲で、りんごシートのB4(AA)と合致するものの行番号を返しなさい
太字で示した3箇所の10について、実際にデータが存在するのは7行目までですが、
今後増えることも考慮して100や1000など多めにしておきましょう
INDEX(リスト一覧!$B$4:$B$10,上の式-3)
リスト一覧!$B$4:$B$10の中で当該行のB列の値を返しなさい
ただし、リスト一覧シートは4行目から書かれているので上の式で得られた行番号から3を引いておくこと
以上、マクロを使わない例です
どうしても、
マクロにしなければならない理由があるということでしたら、
その理由によって作るべき方法も変わってきます
たとえば、
都道府県や品質を書き加えたり変更した時点で即座にマクロが起動して、
その行の送料などの欄を埋めたいですか?
それとも、必要な時にマクロを起動して埋めるのでもいいのですか?
(前者はExcelの動作が遅くなる場合がありますし、
後者は即座に見ることができないという欠点がそれぞれあります)
最後に一言、
マクロは環境によって動かなくなる場合も少なからずありますし、
手作業を代替するのがマクロ本来の役割なので、
計算関連はワークシート関数で完結しておくことをお勧めしておきます
ちょっと前提条件がずれているかもしれませんが・・・
[りんご]シートや、[みかん]シートに入力したとき、
[リスト一覧]シートから、データを拾ってくれば良いのですよね?
関数だけで回答します。
【作業1】
[リスト一覧]シートに作業列を作ります。
作業列は、[都道府県]-[品質]になるように文字を結合します。
A4セルなら =B4&"-"&D4 でA列にコピーします。
邪魔なら、非表示にしてください。
都道府県(検索や抜き出すデータ)より前(左側)に作るのがポイントです。
[リスト一覧]シート
3 作業列 都道府県 送料 品質
4 青森-AA 青森 元払い AA
5 岩手-AAA 岩手 着払い AAA
6 愛媛-AA 愛媛 相談 AA
7 愛知-AAA 愛知 元払い AAA
【作業2】
[りんご]シートと[みかん]シートのデータを拾ってきたい
セルに以下のVLOOKUP関数でデータを拾ってきます。
F4セル(送料)なら
=VLOOKUP(A4&"-"&B4,リスト一覧!$A$4:$D$7,3,FALSE)
[リスト一覧]シートにデータが無いと、#N/Aエラーになりますので、
気になるようなら、ERROR.TYPE関数などで弾いて、
=IF(ISERROR(VLOOKUP(A4&"-"&B4,リスト一覧!$A$4:$D$7,3,FALSE)),"",VLOOKUP(A4&"-"&B4,リスト一覧!$A$4:$D$7,3,FALSE))
などとすればよいかと。
作業列を作るのは、VLOOKUP関数が、複数の列のセルを検索することができないためです。
参考になれば。
ワークシート関数だけで完結させる例
リスト一覧
A | B | C | |
---|---|---|---|
3 | 都道府県 | 送料 | 品質 |
4 | 青森 | 元払い | AA |
5 | 岩手 | 着払い | AAA |
6 | 愛媛 | 相談 | AA |
7 | 愛知 | 元払い | AAA |
りんご
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
3 | 都道府県 | 品質 | 入金 | 出金 | 損害金 | 送料 |
4 | 青森 | AA | 500 | 1000 | 300 | ここに後述の式を入れる |
5 | 岩手 | AAA | 1000 | 300 | 100 | これ以下はコピーすればOK |
F4に以下の式を入力します
=INDEX(リスト一覧!$B$4:$B$10,SUMPRODUCT((リスト一覧!$A$4:$A$10=$A4)*(リスト一覧!$C$4:$C$10=$B4)*ROW(リスト一覧!$A$4:$A$10))-3)
F5以下に式をコピーします
F4の式をコピーして、みかんシートにも同様に貼り付けすれば良いようにしてあります
($はコピペしても変化しないようにするための符号です)
式の解説
SUMPRODUCT((リスト一覧!$A$4:$A$10=$A4)*(リスト一覧!$C$4:$C$10=$B4)*ROW(リスト一覧!$A$4:$A$10)
リスト一覧シートの$A$4:$A$10の範囲で、りんごシートのA4(青森)と合致し、
かつ、リスト一覧シートの$C$4:$C$10の範囲で、りんごシートのB4(AA)と合致するものの行番号を返しなさい
太字で示した3箇所の10について、実際にデータが存在するのは7行目までですが、
今後増えることも考慮して100や1000など多めにしておきましょう
INDEX(リスト一覧!$B$4:$B$10,上の式-3)
リスト一覧!$B$4:$B$10の中で当該行のB列の値を返しなさい
ただし、リスト一覧シートは4行目から書かれているので上の式で得られた行番号から3を引いておくこと
以上、マクロを使わない例です
どうしても、
マクロにしなければならない理由があるということでしたら、
その理由によって作るべき方法も変わってきます
たとえば、
都道府県や品質を書き加えたり変更した時点で即座にマクロが起動して、
その行の送料などの欄を埋めたいですか?
それとも、必要な時にマクロを起動して埋めるのでもいいのですか?
(前者はExcelの動作が遅くなる場合がありますし、
後者は即座に見ることができないという欠点がそれぞれあります)
最後に一言、
マクロは環境によって動かなくなる場合も少なからずありますし、
手作業を代替するのがマクロ本来の役割なので、
計算関連はワークシート関数で完結しておくことをお勧めしておきます
Sub jikko() Dim aa(2) As Worksheet Dim a1 As Worksheet Set a1 = Worksheets("リスト一覧") Set aa(1) = Worksheets("りんご") Set aa(2) = Worksheets("みかん") For c = 4 To a1.Cells(a1.Rows.Count, "A").End(xlUp).Row 都道府県 = a1.Cells(c, "A") 送料 = a1.Cells(c, "B") 品質 = a1.Cells(c, "C") For c1 = 1 To UBound(aa) For c2 = 4 To aa(c1).Cells(aa(c1).Rows.Count, "A").End(xlUp).Row If aa(c1).Cells(c2, "A") = 都道府県 And aa(c1).Cells(c2, "B") = 品質 Then aa(c1).Cells(c2, "F") = 送料 End If Next c2 Next c1 Next c End Sub
データが増えるに従ってループ回数が極端に上がっていくって問題が第一にあります
2012/02/12 10:11:37リスト一覧の件数が47都道府県x品質3種類(AからAAA)=141行と仮定しても、
(りんご2行+みかん2行)x141行=564回の比較を行うことになりますし、
(りんご30行+みかん10行)x141行=5640回の比較を行うことになります
りんご、みかん、以外にも増えたら・・・
ループの回数が増えすぎるとExcelが落ちることだってあります
VBAで行うならば、Findを利用するなり、
ワークシート関数を呼び出すなりしたほうが速度面でも安定性面でも良いですし、
VBAで一括処理するのではなく、
ワークシート関数で随時処理のほうがさらに良いです
ここからは余談ですが、ほかにも無駄な点はいくつかあります
ひとつだけあげておきます
たとえば下記のような記述はループ回数分だけ関数を呼び出していることになります
For c = 4 To a1.Cells(a1.Rows.Count, "A").End(xlUp).Row
For c1 = 1 To UBound(aa)
ループ途中で変わることが無いようなものはループ前に一度だけ関数を呼び出すのが常道です
ce = a1.Cells(a1.Rows.Count, "A").End(xlUp).Row
For c = 4 to ce
c1e = UBound(aa)
For c1 = 1 To c1e
論理的にコードを書かれるid:Mookさんや、実践的なコードを書かれるid;SALINGERさん、
状況に応じて変わる私windofjulyなど人のコードなど、VBAのサンプルには事欠かないと思いますので、
いろいろと見て学習されると良いでしょう
別に数千件程度なら 速度を気にしないでもいいと思いますし、
2012/02/12 12:33:58初心者にも わかりやすい、不具合を出しにくいものが いいと思います。
当然、遅くて使い物にならない ということに ならない限り、
メンテしやすく わかりやすい プログラムにすべきでしょう。