Excelのマクロに関する質問です。良い回答は、500~1000ポイント差し上げます。

各シートの抽出条件を[リスト一覧]シートからデータを抽出したい。
※抽出条件は、[都道府県]と[品質]が一致したとき
※抽出したい項目は、[送料]等の項目

[リスト一覧]シート
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    元払い

マクロでの回答は、ソースも記述願います。
どうか宜しくお願いします。

回答の条件
  • 1人10回まで
  • 13歳以上
  • 登録:2012/02/12 03:46:49
  • 終了:2012/02/12 23:23:09

ベストアンサー

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492012/02/12 06:20:20

ポイント900pt

ワークシート関数だけで完結させる例

リスト一覧

 ABC
3都道府県送料品質
4青森元払いAA
5岩手着払いAAA
6愛媛相談AA
7愛知元払いAAA

りんご

 ABCDEF
3都道府県品質入金出金損害金送料
4青森AA5001000300ここに後述の式を入れる
5岩手AAA1000300100これ以下はコピーすれば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の動作が遅くなる場合がありますし、
後者は即座に見ることができないという欠点がそれぞれあります)

最後に一言、
マクロは環境によって動かなくなる場合も少なからずありますし、
手作業を代替するのがマクロ本来の役割なので、
計算関連はワークシート関数で完結しておくことをお勧めしておきます

その他の回答(2件)

id:easygoings No.1

easygoings回答回数153ベストアンサー獲得回数202012/02/12 04:59:02

ポイント50pt

ちょっと前提条件がずれているかもしれませんが・・・
[りんご]シートや、[みかん]シートに入力したとき、
[リスト一覧]シートから、データを拾ってくれば良いのですよね?

関数だけで回答します。

【作業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関数が、複数の列のセルを検索することができないためです。
参考になれば。

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492012/02/12 06:20:20ここでベストアンサー

ポイント900pt

ワークシート関数だけで完結させる例

リスト一覧

 ABC
3都道府県送料品質
4青森元払いAA
5岩手着払いAAA
6愛媛相談AA
7愛知元払いAAA

りんご

 ABCDEF
3都道府県品質入金出金損害金送料
4青森AA5001000300ここに後述の式を入れる
5岩手AAA1000300100これ以下はコピーすれば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の動作が遅くなる場合がありますし、
後者は即座に見ることができないという欠点がそれぞれあります)

最後に一言、
マクロは環境によって動かなくなる場合も少なからずありますし、
手作業を代替するのがマクロ本来の役割なので、
計算関連はワークシート関数で完結しておくことをお勧めしておきます

id:taknt No.3

きゃづみぃ回答回数13539ベストアンサー獲得回数11982012/02/12 07:52:04

ポイント50pt
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
他2件のコメントを見る
id:windofjuly

データが増えるに従ってループ回数が極端に上がっていくって問題が第一にあります

リスト一覧の件数が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 10:11:37
id:taknt

別に数千件程度なら 速度を気にしないでもいいと思いますし、
初心者にも わかりやすい、不具合を出しにくいものが いいと思います。

当然、遅くて使い物にならない ということに ならない限り、
メンテしやすく わかりやすい プログラムにすべきでしょう。

2012/02/12 12:33:58

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません