エクセルVBAについて教えてください

質問

エクセル2003

シート1

　　　　A　　　　　　　B　　　　　　C
1　　3月1日 A 100　　　　　*A列はカレンダーコントロールより選択としています
2　　4月1日 B 100
3　　3月1日 C 200　　　　　*B列はコンボボックスより選択としています
4　　3月1日 D 200
5　　4月1日 E 300　　　　　*C列は直接入力としています
6　　4月1日 F 300
7　　3月1日 G 100
8　　4月1日 H 200
9　　3月1日 I 200
10 　4月1日 J 100

上記シート1の表のC列を下記シート2のC列に条件集計する

シート2

　　　A　　　　　　　B　　　　　　　C
1　　3月1日　　　A～E　　　　　500　　　　*選択した日付ごと及びA・B・C・D・Eの集計　
2　　3月1日　　　F～J　　　　　300　　　　*選択した日付ごと及びF・G・H・I・Jの集計　　　　
3　　4月1日　　　A～E　　　　　400　　　　*選択した日付ごと及びA・B・C・D・Eの集計　　　
4　　4月1日　　　F～J　　　　　600　　　　*選択した日付ごと及びF・G・H・I・Jの集計　

すいませんが上記コードを教えてください　困ってます
よろしくお願いします

回答No.6

kagakusuki · ベストアンサー

>マクロを使って行うよりも関数で対応するのが計算速度も速く、

　それでは、回答No.5で挙げさせて頂いたワークシート関数を応用して高速化したVBAは如何でしょうか。

Sub Macro2()

Dim lr1 As Long
Dim lr2 As Long
If Application.WorksheetFunction.Count(Sheets("Sheet1").Columns("C:C")) = 0 Then Exit Sub
lr1 = Application.WorksheetFunction.Match(9E+307, Sheets("Sheet1").Columns("C:C"))
With Sheets("Sheet2")
If Application.WorksheetFunction.Count(.Columns("A:A")) = 0 Then Exit Sub
lr2 = Application.WorksheetFunction.Match(9E+307, .Columns("A:A"))
.Range("C1:C" & .Rows.Count).ClearContents
.Range("IV1:IV" & lr1).FormulaR1C1 = "=IF(OR(Sheet1!RC1="""",Sheet1!RC2=""""),"""",Sheet1!RC1&""◆""&Sheet1!RC2)"
.Range("C1:C" & lr2).FormulaR1C1 = _
"=IF(AND(RC1<>"""",COUNTIF(RC2,""*?～*?"")),SUMIF(C256,"">=""&RC1&""◆""&LEFT(RC2,FIND(""～"",RC2)-1),Sheet1!C3)-SUMIF(C256,"">""&RC1&""◆""&REPLACE(RC2,1,FIND(""～"",RC2),),Sheet1!C3),"""")"
.Range("C1:C" & lr2).Value = .Range("C1:C" & lr2).Value
.Columns("IV:IV").Clear
End With

End Sub

回答No.7

kagakusuki

>前者のほうですが
>B列にはいろいろな文字列が入る予定です
>及び重複もありえます

　それでしたら、回答No.2において2つ目に挙げさせて頂いた関数と、3つ目に挙げさせて頂いた関数、及び、回答No.4、回答No.5、回答No.6の方法の何れかを使う事で、集計する事が出来ます。（但し、回答No.2において3つ目に挙げさせて頂いた関数は、Excel2007以降のバージョン用ですので、質問者様が御使いになられているExcelでは使う事が出来ません）

　尚、上記の何れの方法に関しても注意が必要な点がありまして、B列に入力されている値が数字であった場合には、必ずしも、大きな数字の方が大きな値と判定されるとは限りません。
　例えば、B1セルに「100」、B2セルに「30」と入力されていた場合、先頭の文字が、B1セルでは「1」、B2セルでは「5」となっていますので、B2セルの方が大きな値が入力されていると判定されてしまいます。（辞書などのアイウエオ順と同じ判定方法です）
　ですから、B列に数字が入っている場合には、注意なさって下さい。

回答No.5

kagakusuki

回答No.3様の方法を試してみました処、Sheet2のB列の値が「B～I」等の場合には正しい集計結果が得られない様です。
　もし、作業列を設ける方法で集計を行うのでしたら、次の様な方法は如何でしょうか？

　まず、適当な使用していないシート（ここでは仮にSheet3とします）のA1セルに次の関数を入力して下さい。

=IF(OR(Sheet1!$A1="",Sheet1!$B1=""),"",Sheet1!$A1&"◆"&Sheet1!$B1)

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。

　次に、Sheet2のSheet2のC1セルに次の関数を入力して下さい。

=IF(AND($A1<>"",COUNTIF($B1,"*?～*?")),SUMIF(Sheet3!$A:$A,">="&$A1&"◆"&LEFT($B1,FIND("～",$B1)-1),Sheet1!$C:$C)-SUMIF(Sheet3!$A:$A,">"&$A1&"◆"&REPLACE($B1,1,FIND("～",$B1),),Sheet1!$C:$C),"")

　次に、Sheet2のC1セルをコピーして、Sheet2のC2以下に貼り付けて下さい。

　以上です。

回答No.4

kagakusuki

確認したいのですが、Sheet2のB列において、例えば「E～H」となっている場合には、
「Sheet1のB列の値が、"E"以上で、且つ、"H"以下となっている行のC列の値のみを集計する」
のでしょうか、それとも、
「Sheet1のB列の値が、"E"となっている行から、"H"となっている行にかけてのC列の値のみを集計する」
のでしょうか、どちらなのでしょうか？
　例えば、以下の様な場合、

Sheet1
　　　　A列　　 B列　 C列
1　　3月1日　　F　　 101
2　　3月1日　　E　　 102
3　　3月1日　　A　　 104
4　　3月1日　　B　　 108
5　　3月1日　　C　　 116
6　　3月1日　　D　　 132
7　　3月1日　　I　　 　164
8　　3月1日　　H　　 228
9　　3月1日　　G　　 356

Sheet2
　　　　A列　　　 B列　 　C列
1　　3月1日　　E～H

前者の考え方ですと、「Sheet1のB列の値が、『E,F,G,H』となっている行のにおけるC列の値」が集計されますから、Sheet2のC1セルの値は787になりますし、
後者の考え方ですと、「Sheet1のB列の値が『Eとなっている、2行目』から『Hとなっている、8行目』にかけてのC列の値」が集計されますから、Sheet2のC1セルの値は954になります。
　この事から解ります様に、どの様に集計するのかによって、値は変化してしまいますので、Sheet1のB列の値が、順序良く並んでいない場合には、どの様に集計すべきなのかに関する追加情報を、この回答欄の下の辺りにある[補足する]ボタンをクリックして、御知らせ願います。

　因みに、前者の場合の集計方法を行うVBAの一例は、以下の様なものです。

Sub Macro()

Dim lr1 As Long
Dim lr2 As Long
Dim i As Long
Dim k As Long
Dim fs As String
Dim ls As String
Dim n As Long
If Application.WorksheetFunction.Count(Sheets("Sheet1").Columns("C:C")) = 0 Then Exit Sub
lr1 = Application.WorksheetFunction.Match(9E+307, Sheets("Sheet1").Columns("C:C"))
With Sheets("Sheet2")
If Application.WorksheetFunction.Count(.Columns("A:A")) = 0 Then Exit Sub
lr2 = Application.WorksheetFunction.Match(9E+307, .Columns("A:A"))
.Range("C1:C" & .Rows.Count).ClearContents
For i = 1 To lr2
n = 0: fs = "": ls = ""
If .Range("A" & i) <> "" And .Range("B" & i).Value Like "*?～*?" Then
fs = Left(.Range("B" & i), InStr(.Range("B" & i), "～") - 1)
ls = Mid(.Range("B" & i), InStr(.Range("B" & i), "～") + 1)
End If
For j = 1 To lr1
If fs = "" Or ls = "" Then Exit For
If Sheets("Sheet1").Range("A" & j) = .Range("A" & i) _
And Sheets("Sheet1").Range("B" & j) >= fs _
And Sheets("Sheet1").Range("B" & j) <= ls _
Then
n = n + Sheets("Sheet1").Range("C" & j)
End If
Next j
.Range("C" & i).Value = n
Next i
End With

End Sub

お礼

前者のほうですが
B列にはいろいろな文字列が入る予定です
及び重複もありえます

質問の表は簡単なサンプルです

誠にすみません　説明不足でした。

かなり初心者ですのでコードに説明コメントがあれば助かります

回答No.3

KURUMITO

マクロを使って行うよりも関数で対応するのが計算速度も速く、しかもデータが新たに入力されれば、何の操作も必要なく、即座に対応できる点など優れています。マクロは関数を使っても対応できないようなケースで考えるのがよいでしょう。
関数を使うといってもＳＵＭＰＲＯＤＵＣＴのような配列関数を使うことはデータの数が多くなれば計算速度が遅くなることになります。複雑なケースでは作業列を使って出来るだけ簡単な式で対応することがおすすめです。
例えば次のようにします。
シート1ではお示しのデータがＡからＣ列の2行目から下方に入力されているとします。
Ｄ列を作業列としてＤ2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",A2*100+IF(CODE(B2)<70,1,2))

この式ではＣＯＤＥ関数を使っています。ＡからＥまでの文字ではＣＯＤＥ関数では65から69となります。
ＦからＪまでの文字では70から74となります。
シート2ではお示しのような表が2行目から下方に入力されているとしたらＣ2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",SUMIF(Sheet1!D:D,A2*100+IF(COUNTIF(B2,"A*"),1,2),Sheet1!C:C))

回答No.2

kagakusuki

ANo.1です。
　申し訳御座いません、少々寝ぼけている様で、先程の回答における関数の参照先が出鱈目なものになっておりました。
　C1セルに入力する関数は、正しくは以下の通りです。

　Sheet1のB列中に、「Sheet2のB列で指定した範囲の先頭の文字（御質問文のSheet2の2行目の例で言えば「F」）」と「Sheet2のB列で指定した範囲の末尾の文字（御質問文のSheet2の2行目の例で言えば「J」）」が必ず含まれていて、且つ、Sheet2のB列中に同じ文字が重複して入力されてはいない、という事が保障されている場合の関数

=IF(OR($A1="",ISERROR(1/COUNTIF(Sheet1!$B:$B,LEFT($B1,FIND("～",$B1)-1))/COUNTIF(Sheet1!$B:$B,REPLACE($B1,1,FIND("～",$B1),)))),"",SUMIF(INDEX(Sheet1!$A:$A,MATCH(LEFT($B1,FIND("～",$B1)-1),Sheet1!$B:$B,0)):INDEX(Sheet1!$A:$A,MATCH(REPLACE($B1,1,FIND("～",$B1),),Sheet1!$B:$B,0)),$A1,INDEX(Sheet1!$C:$C,MATCH(LEFT($B1,FIND("～",$B1)-1),Sheet1!$B:$B,0)):INDEX(Sheet1!$C:$C,MATCH(REPLACE($B1,1,FIND("～",$B1),),Sheet1!$B:$B,0))))

　Sheet1のB列中に、「Sheet2のB列で指定した範囲の先頭の文字（御質問文のSheet2の2行目の例で言えば「F」）」や「Sheet2のB列で指定した範囲の末尾の文字（御質問文のSheet2の2行目の例で言えば「J」）」が含まれていない事もあり得たり、Sheet2のB列中に同じ文字が重複して入力されている事もあり得る場合の関数

=IF(OR($A1="",ISERROR(1/COUNTIF(Sheet1!$B:$B,">="&LEFT($B1,FIND("～",$B1)-1))/COUNTIF(Sheet1!$B:$B,"<="&REPLACE($B1,1,FIND("～",$B1),)))),"",SUMPRODUCT(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C))*(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$C:$C))=$A1)*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9E+307,Sheet1!$C:$C))>=LEFT($B1,FIND("～",$B1)-1))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9E+307,Sheet1!$C:$C))<=REPLACE($B1,1,FIND("～",$B1),))))

　ExcelのバージョンがExcel2007以降である場合の関数

=IF(OR($A1="",ISERROR(1/COUNTIF(Sheet1!$B:$B,">="&LEFT($B1,FIND("～",$B1)-1))/COUNTIF(Sheet1!$B:$B,"<="&REPLACE($B1,1,FIND("～",$B1),)))),"",SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A1,Sheet1!$B:$B,">="&LEFT($B1,FIND("～",$B1)-1),Sheet1!$B:$B,"<="&REPLACE($B1,1,FIND("～",$B1),)))

お礼

B列にはいろいろな文字列が入る予定です
及び重複もありえます

質問の表は簡単なサンプルです

誠にすみません　説明不足でした。

回答No.1

kagakusuki

VBAを使わずとも、ワークシート関数でも集計する事が出来ます。
　もし、Sheet1のB列中に、「Sheet2のB列で指定した範囲の先頭の文字（御質問文のSheet2の2行目の例で言えば「F」）」と「Sheet2のB列で指定した範囲の末尾の文字（御質問文のSheet2の2行目の例で言えば「J」）」が必ず含まれていて、且つ、Sheet2のB列中に同じ文字が重複して入力されてはいない、という事が保障されている場合には、
まず、Sheet2のC1セルに次の関数を入力して下さい。

=IF(OR($G1="",ISERROR(1/COUNTIF($B:$B,LEFT($H1,FIND("～",$H1)-1))/COUNTIF($B:$B,REPLACE($H1,1,FIND("～",$H1),)))),"",SUMIF(INDEX($A:$A,MATCH(LEFT($H1,FIND("～",$H1)-1),$B:$B,0)):INDEX($A:$A,MATCH(REPLACE($H1,1,FIND("～",$H1),),$B:$B,0)),$G1,INDEX($C:$C,MATCH(LEFT($H1,FIND("～",$H1)-1),$B:$B,0)):INDEX($C:$C,MATCH(REPLACE($H1,1,FIND("～",$H1),),$B:$B,0))))

　そして、Sheet2のC1セルをコピーして、Sheet2のC2以下に貼り付けて下さい。

　尚、もし、Sheet1のB列中に、「Sheet2のB列で指定した範囲の先頭の文字（御質問文のSheet2の2行目の例で言えば「F」）」や「Sheet2のB列で指定した範囲の末尾の文字（御質問文のSheet2の2行目の例で言えば「J」）」が含まれていない事もあり得たり、Sheet2のB列中に同じ文字が重複して入力されている事もあり得る場合には、Sheet2のC1セルに入力する関数を次の様なものとして下さい。

=IF(OR($G1="",ISERROR(1/COUNTIF($B:$B,">="&LEFT($H1,FIND("～",$H1)-1))/COUNTIF($B:$B,"<="&REPLACE($H1,1,FIND("～",$H1),)))),"",SUMPRODUCT($C$1:INDEX($C:$C,MATCH(9E+307,$C:$C))*($A$1:INDEX($A:$A,MATCH(9E+307,$C:$C))=$G1)*($B$1:INDEX($B:$B,MATCH(9E+307,$C:$C))>=LEFT($H1,FIND("～",$H1)-1))*($B$1:INDEX($B:$B,MATCH(9E+307,$C:$C))<=REPLACE($H1,1,FIND("～",$H1),))))

　それから余談ですが、ExcelのバージョンがExcel2007以降である場合には、次の様な関数とする事も出来ます。

=IF(OR($G1="",ISERROR(1/COUNTIF($B:$B,">="&LEFT($H1,FIND("～",$H1)-1))/COUNTIF($B:$B,"<="&REPLACE($H1,1,FIND("～",$H1),)))),"",SUMIFS($C:$C,$A:$A,$G1,$B:$B,">="&LEFT($H1,FIND("～",$H1)-1),$B:$B,"<="&REPLACE($H1,1,FIND("～",$H1),)))