エクセルマクロ●条件抽出と定型書式への書出し

質問

こんにちは。下記のような処理をエクセルマクロ（VB）で行いたいと思いますが、どなたかお知恵を拝借できますでしょうか。
下記に詳細を記しておりますが、まず、条件入力フォームの条件からSheet1のデータをSheet2に抽出します。そして、Sheet2の内容をSheet3の定型フォームに書き出したいです。
書き出す条件は、(1)月・商品・販売価格別の数量を入れます(2)原価計を入れます(3)定型フォームを保つためピボット処理はしません(4)オートフォーマットで罫線を入れます
条件入力フォームからSheet2への抽出し転記することは２週間位考えればできると思いますが、定型フォーム（Sheet3)への書き出し方法が皆目検討が点かず困っております。
できれば、条件入力もフォームからSheet2への抽出転記も含めてやり方を教えていただけますでしょうか。暑くて毎日大変ですが、よろしくお願いいたします。
■（Sheet1)
A　　　　　　B　　　　　　C　　　　　　　　D
売上月　商品名　販売価格　　原価(円)　・・・他項目多数
2010/4　魚　　 　　　1000　　　　　500
2010/4魚　　　　　　2000　　　500
2010/4酒　　　　　　2000　　　500
2010/5酒　　　　1000　　　　　500　
2010/5酒　　　　2000　　　　　1000　　　
2010/4肉　　　　　1000　　　　500
2010/4肉　　　　　1000　　　　1000
2010/5肉　　　　　2000　　　　1000
※以下12ヶ月分、約10000件の元データ
■（条件入力フォーム）
検索条件１：販売月-開始
2010/4
検索条件２：販売月-終了
2010/4
検索条件３：商品名
魚　（または検索条件４）
検索条件４：商品名
肉
検索条件５：販売価格
1000　　　※「1000、2000、5000、条件なし」から選択
＜検索条件６：原価＞
条件なし　※「500、1000、条件なし」　から選択
（Sheet2）
検索した行を抽出しSheet2へ転記
　　A　　　B　　　C　　　D
1　売上月　商品名　販売価格　原価(円)　
2　2010/4　　魚　　 1000　　　　500
3　2010/4　　肉　　　1000　　　　500
4　2010/4　　肉　　　1000　　　　1000
■（Sheet3)
定型フォームへの書き出し
売上月　商品名　販売価格　数量　原価計(円)・・・他項目多数
2010/4　　魚　　　1000 　　　　　　 1 　500
2010/4魚　　　2000　　　 　　　 *　　　*　　　　　　　　
2010/4　酒　　　1000　　　 　　　 *　　　*
2010/4酒　　　2000　　　　　　　　*　　　*
2010/4肉　　　1000　　　 　　　 2 　1500
2010/4肉　　　2000　　　　　　　　*　　　*　 　　　　
2010/5　 魚　　　1000　　　　　　　　*　　　*
2010/5魚　　　2000　　　　　　　　*　　　*
2010/5酒　　　1000　　　　　　　　*　　　*
2010/5肉　　　1000　　　　　　　　*　　　*
2010/5肉　　　2000　　　　　　　　*　　　*
※ピボットによる集計は行わない、*は空欄を意味します
※この後、エクセルの書式→オートフォーマットで罫線を入れる
どなたか方法を教えていただけますでしょうか。今週中にコードを作成したいのですが、何分やり方がわからず困っております。よろしくお願いします。（OS：WindowsXP、Office2003）

回答No.4

KURUMITO · ベストアンサー

その他項目多数が有ってそれらをどの様に取り扱って行くかわ別の問題としてここでは説明します。
シート１ではA１セルからD１セルまでに項目名の売上月から原価が、各データは２行目から下方にあるとします。
ところで検索のための条件ですがシート２でそれらの条件を入力します。
シート２のA１セルには販売月ー開始の項目名を入れB１セルに2010/4のように入力します。
A２セルには販売月ー終了と項目名を入れB２セルに2010/4のように入力します。
A３セルには商品名１と入力し、B３セルには魚などと入力します。
A４セルには商品名２と入力し、B４セルには肉などと入力します。
A５セルには販売価格と入力し、B５セルではリストの形で１０００、２０００，５０００、空白などが選べるようにします。
A６セルには原価と入力し、B６セルではリストの形で５００，１０００、空白が選べるようにします。
そこでこれらの条件を受けてシート１では作業列を設けシート１の例えばJ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(A2>=Sheet2!B$1,A2<=Sheet2!B$2),IF(AND(Sheet2!B$5<>"",Sheet2!B$6<>""),IF(AND(OR(B2=Sheet2!B$3,B2=Sheet2!B$4),C2=Sheet2!B$5,D2=Sheet2!B$6),A2*10000+COUNTIF(A$2:A2,A2),""),IF(AND(Sheet2!B$5<>"",Sheet2!B$6=""),IF(AND(OR(B2=Sheet2!B$3,B2=Sheet2!B$4),C2=Sheet2!B$5),A2*10000+COUNTIF(A$2:A2,A2),""),IF(AND(Sheet2!B$5="",Sheet2!B$6<>""),IF(AND(OR(B2=Sheet2!B$3,B2=Sheet2!B$4),D2=Sheet2!B$6),A2*10000+COUNTIF(A$2:A2,A2),""),IF(AND(Sheet2!B$5="",Sheet2!B$6=""),IF(OR(B2=Sheet2!B$3,B2=Sheet2!B$4),A2*10000+COUNTIF(A$2:A2,A2),""),"")))),"")

このほかにシート１のK２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,MAX(K$1:K1)+1,INDEX(K$1:K1,MATCH(B2,B$1:B1,0))))

シート１のL２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(K2="","",A2*10000+K2)

そこでシート２の条件B1からB6のデータを満足する抽出のデータをシート２の９行目から下方に表示させるとしてシート２のA８セルには売上月、B８セルには商品名、C８セルには販売価格、D８セルには原価（円）と項目名を入力し、A９セルには次の式を入力した後でD９セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNT(Sheet1!$J:$J),"",IF(INDEX(Sheet1!$A:$I,MATCH(SMALL(Sheet1!$J:$J,ROW(A1)),Sheet1!$J:$J,0),COLUMN(A1))=0,"",INDEX(Sheet1!$A:$I,MATCH(SMALL(Sheet1!$J:$J,ROW(A1)),Sheet1!$J:$J,0),COLUMN(A1))))

なお、A９セルから下方のセルを選択して右クリックし、「セルの書式設定」から「表示形式」の「ユーザー定義」で　ｙｙｙｙ/m と入力します。

なお、シート３を作成するために必要な作業列としてシート２のJ９セルには次の式を入力して下方にオートフィルドラッグします。

=A9&B9&C9

シート３は定型フォームでA１セルに売上月、B１セルに商品名、C１セルに数量、D１セルに原価計の項目名を入力します。
A２セルには次の式を入力したのちにB２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNT(Sheet1!$L:$L),"",INDEX(Sheet1!$A:$C,MATCH(SMALL(Sheet1!$L:$L,ROW(A1)),Sheet1!$L:$L,0),COLUMN(A1)))

C列およびD列にデータを入力するために作業列としてシート３のJ２セルには次の式を入力して下方にオートフィルドラッグします。

=A2&B2&C2

その後にC２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(J2="","",IF(AND(COUNTIF(J$2:J2,J2)=1,COUNTIF(Sheet2!$J:$J,J2)>0),COUNTIF(Sheet2!$J:$J,J2),""))

D２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(D2="","",SUMIF(Sheet2!J$9:J$10000,J2,Sheet2!D$9:D$10000))

それぞれのシートで作業列が目障りでしたらその列を選択して右クリックし、「非表示」を選択すればよいでしょう。

お礼

VBで考えているのですが、もし自分で無理な場合は、アドバイスいただいた方法でやろうと思います。とても早くご解答いただきありがとうございます。まだ思案中でございます。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

＃2です。
写真が古かったのと条件表の文字がつぶれているので再投稿

お礼

ありがとうございます。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

抽出ができるなら、作業列がいらなくなるが数式で出してみた

販売月=売上月 として条件表を書いてみた(添付図参照)
また、シリアル値として入力している
なお、原価欄は入力していない

作業列1
E2セルに =A2&B2&C2

作業列2 条件表に合致する項目なのかを検証した
F15セル 0
F16セル =DCOUNT($A$15:$C16,,$A$11:$E$13)
下へオートフィル

数量
=IF(F15=F16,0,COUNTIF($E$2:$E$9,A16&B16&C16))
下へオートフィル

原価
=IF(D16=0,0,SUMIF($E$2:$E$9,A16&B16&C16,$D$2:$D$9))
下へオートフィル

空欄処理は書式設定、または、VBAで処理できると思います。

お礼

ありがとうございます。

回答No.1

imogasi

（１）Sheet1からSheet2作成
多条件の抜き出しで、エクセルVBA程度では難しいと思いますが、どのようにした、または仕様としてますか？
私の考えるところでは、フィルタオプションの設定をVBA化して使うのを思いつきますが。
これも原データと条件、抽出結果が別シートに亘る場合注意が必要です。
（２）Sheet2からSheet3の作成。
掲示データ例の数がアンバランスで聞きたいことがよく伝わらない。
（１）行数はSheet2<Sheet3のように増えることはありますか。
（２）列数的に計算項目は別にして（例　単価X数量＝総金額の用なのは別にして）在りますか。
（３）Sheet3では中間集計行的なものは増えてますか
これら（１）（２）（３）のようなことを説明して無いのでよくわからない。
データ例を挙げるのも大切だが、やることで特徴的な事項も説明を加えること。

私から言うとSheet2からSheet3咲くせいなど、データの代入だけの問題で質問に当たらない（＞どなたかお知恵を拝借できますでしょうかー＞知恵というものは要らない）と思うが、反論をしてください。

お礼

私の説明の仕方が悪かった部分もあるので、もう一度検証してみます。ありがとうございます。