[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数条件の集計まとめ』（勉強中）
初めまして。
下記について、ピボットではなく、関数 及び マクロ、それぞれ出来ないか考えていますがうまくいきません。ご教授頂けますと幸いです。
シートが3つあります。(比較1・比較2・集計)
比較1シートと比較2シートの構造は全く同じです。（例として比較1シートを載せます）
＜比較1＞
A B C D E F G H I
1 項目 チーム No. 予備 8/1 8/2 8/3 8/4 8/5
2 AA a 1 100 1,000 500
3 AA a 2 500 100
4 AA d 3 100 3,000
5 BB c 2 3,000 200
6 BB c 1 100
7 BB a 3 200 500
8 CC c 1 200 200
9 CC a 3 100 100 500 1,000
10 CC d 1
11 DD d 3 100 200
12 DD c 1 200
13 DD c 3 500
A列の項目は集計には関係ありません。B列のチーム、C列のNo.に応じた足し算を集計シートに実施したいというのが最終的なやりたいことです。
集計シートは下記のように、A列〜D列にチーム、E列〜G列にNo. を入力して、その入力した様々なパターンで集計したいです。
集計シートのA〜D列には集計したいチームを記入（複数の場合はB, C, D列に記載）
集計シートのE〜G列には集計したいNo.を記入（複数の場合はF, G列に記載)
outputとしてはH列に8/1の比較1シートの値を見て、集計シートのA〜G列の条件に一致するものを足し算したい。
Ｉ列には、同様に比較2シートでも足し算してきたい。
J列には、比較1-比較2の差分として、=IFERROR(H3-I3,H3)という計算式を入れている。
書ききれませんでしたが、K列以降には同じように、8/2, 8/3…と計算したい。
＜集計シートの例＞
A B C D E F G H I J
1 ﾁｰﾑ1 ﾁｰﾑ2 ﾁｰﾑ3 ﾁｰﾑ4 No.1 No.2 No.3 8/1 8/1 8/1
2 比較1 比較2 差分
3 a 1 2 3 900 200 700
4 a b c d 2 3,500 2,000 1,500
5 b c 2 3 3,500 3,000
6 c 1 3 1,000 1,000 -200
自分で考えた計算式は、
H3 =SUMPRODUCT(SUMIFS(比較1!$E:$E,比較1!$B:$B,集計!$A3:$D3,比較!$C:$C,集計!$E3:$G3))
としましたが、うまく結果が合いません。恐らく、チーム1-No.1, チーム2-No.2, チーム3-No.3 という風にデータの列がそろっている条件は拾ってきているようなのですが、片方が空欄だと計算されないのか?とか、
条件がそろっている場合でも、一度計算したものが下の方に再び登場するとその分は拾っていない？等、良く分かっていません。
何か良い案はありますでしょうか？
また、関数以外にも、最終的にはマクロでも出来たらやってみたいのですが、
このような複雑な条件ですと難しいでしょうか？
何卒、ご教授の程、宜しくお願いいたします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
コメントがつかないようなので前座です。
当初の式が機能しない理由は、比較対象値がふたつあり、
SUMIFSは、それらの直積（カルテシアン積）に対応するものを返すわけではなく、
( チーム==チーム1 かつNo. == No.1 )
または( チーム==チーム2 かつNo. == No.2 )
または( チーム==チーム3 かつNo. == No.3 )
にマッチした結果の合計しか返さないからでしょう。
一向に垢抜けしない案ですが、こんな形にしてはどうですか。
<<集計シートのレイアウト>> 式が簡単なコピーで対応できるように、レイアウトを以下のように変更します。
A B C D E F G H I J
1 ﾁｰﾑ1 ﾁｰﾑ2 ﾁｰﾑ3 ﾁｰﾑ4 No.1 No.2 No.3 8/1 8/2 8/3
2
3 a 1 2 3 900
4 200
5 700 (以下、左に同じ）
6 a b c d 2 3,500
7 2,000
8 1,500
H3 =SUMPRODUCT(SUMIFS(比較1!E:E,比較1!$B:$B,$A3:$D3,比較1!$C:$C,$E3))
+SUMPRODUCT(SUMIFS(比較1!E:E,比較1!$B:$B,$A3:$D3,比較1!$C:$C,$F3))
+SUMPRODUCT(SUMIFS(比較1!E:E,比較1!$B:$B,$A3:$D3,比較1!$C:$C,$G3))
H4 =SUMPRODUCT(SUMIFS(比較2!E:E,比較2!$B:$B,$A3:$D3,比較2!$C:$C,$E3))
+SUMPRODUCT(SUMIFS(比較2!E:E,比較2!$B:$B,$A3:$D3,比較2!$C:$C,$F3))
+SUMPRODUCT(SUMIFS(比較2!E:E,比較2!$B:$B,$A3:$D3,比較2!$C:$C,$G3))
H5 = H3 - H4
・H3:H5を右に日数分だけコピー ・H3:5行目最終列（3行）をまとめて、下に、ケース毎領域に一回だけコピー
＞また、関数以外にも、最終的にはマクロでも出来たらやってみたいのですが、 ＞このような複雑な条件ですと難しいでしょうか？ マクロのほうがより複雑なものにも対応できると思います。可能でしょう。 まあ、そこまですることもないでしょうね。 （γ） 2021/09/05(日) 13:51
昨日私が考えてうまくいかなかった下記式ですが、
H3 =SUMPRODUCT(SUMIFS(比較1!$E:$E,比較1!$B:$B,集計!$A3:$D3,比較!$C:$C,集計!$E3:$G3))
やはり、複数条件に対してOR条件にするのは無理なのかな…と思いました。
下記のように、例えばチーム名は1つ、No.が3つ というケースですと、うまくいきます。
H3 =SUMPRODUCT(SUMIFS(比較1!$E:$E,比較1!$B:$B,集計!$A3,比較!$C:$C,集計!$E3:$G3))
また、SUMPRODUCTではなく、SUM(SUMIFS〜 にして、OR条件の所を{ }で指定しようとしてもダメでした。
{ ｝内はセル参照したいので、〜CHOOSE({1;2;3;4},$A3:$D3)〜にしてみたり、色々試してみましたが、
どれもことごとくダメで(笑)
SUMPRODUCTのANDは「*」、ORは「+」の考えで計算出来ないかとも考えましたが、そうすると、考えられるすべての組み合わせで「+」と「*」を設定しないといけないのか？
等々、完全に深みにはまってしまいました。
関数ではやはり厳しいでしょうか。
また、マクロでもぜひやってみたいのですが、どなたか詳しい方がいらっしゃいましたら、宜しくお願いいたします。
（勉強中） 2021/09/05(日) 13:53
申し訳ありません。確認前に13:53の文を送信してしまいました。
ありがとうございます。
教えて頂いた内容を理解し、ご提示して頂いた内容で正しく出力できました。
（チーム名をまとめて指定する方法、No.をまとめて指定する方法、双方で試してみましたがどちらも値が一致しました。）
大変ありがとうございました。OR条件の理解が足りていませんでした。今後気を付けたいと思います。
尚、実際はNo.が3つではなく10個あったり、Outputもご提示して頂いたものでないと式のコピーが大変であるというのは重々承知なのですが、現状Outputフォームを変える事ができません。
となりますと、現実的なのはマクロを使用するべきでしょうか。
どなたかこのようなケースのマクロの組み方をご存じの方がいらっしゃいましたら、引き続きよろしくお願いいたします。
（勉強中） 2021/09/05(日) 14:18
マクロはどれくらい作成された経験がありますか？
単純な繰り返しですけどねえ。
（γ） 2021/09/05(日) 14:48
マクロはここ1か月ほど本やネットで調べたりして、まだ全然理解出来ていないレベルです。Loopや IFが何とか、、配列等はネットで見てなるほど、このようにやるのか…とおぼろげな感じであります。。
（勉強中） 2021/09/05(日) 15:17
あとは、初心者的な考えですと、集計シートのRange("H3:H8").Formula= "今回教えて頂いた関数"
にして、式の範囲部分は変数等使用して出来たりしないか…とか思ってしまいますが、
マクロでやるならそのような使い方は皆さんはしないのでしょうか？
（勉強中） 2021/09/05(日) 19:57
こんばんは ＾＾ 連想配列、便利ですね。(#^ ^#)v 差引等する時はよく 使いますです。r1c1とかで書けばバリ簡単ですよ＾＾； x=8 .Cells(3, x).Resize(UBound(w2, 1), 1).FormulaR1C1 = "=rc[-2]-rc[-1]" x = x + 1 な感じで。でわ。。。m(_ _)m （隠居Ｚ） 2021/09/05(日) 20:50
最初にユーザー定義関数を示しておきます。 手作業であれば、これで十分なはずです。 表を当初提案どおりにすれば、これで終わりです。 簡単なことは簡単に済ませた方がよいと思います。
使い方は、 =SUMIFS2(比較1!E:E,比較1!$B:$B,$A3:$D3,比較1!$C:$C,$E3:$G3) といった書き方です。
Function SUMIFS2(sumRange As Range, cond1R As Range, rng1 As Range, _
cond2R As Range, rng2 As Range) As Long
Dim v As Long
Dim r As Range
For Each r In rng2
v = v + Application.SumProduct(Application.SumIfs(sumRange, cond1R, rng1, cond2R, r))
Next
SUMIFS2 = v
End Function
（γ） 2021/09/06(月) 07:55
参考までに、マクロ案です。
(1)標準モジュールにコピー (2)条件の数(下記のn1,n2)は手動でセットしてください。（手抜き） (3)日付や、比較1,比較2，差などの見出しはセットされているものとします。 (4)testを実行すると、結果が"集計"シートに書き込まれる（はず）。
Option Explicit
Const n1 As Long = 4 ' 条件指定に用いる チームの数 ■要修正 Const n2 As Long = 3 ' 条件指定に用いる No の数 ■要修正
Dim wsT As Worksheet '集計シート Dim ws1 As Worksheet 'データシート Dim ws2 As Worksheet Dim mat() As Long '結果配列 Dim num_of_days As Long Dim dic1 As Object Dim dic2 As Object
Sub test()
Dim wslastRow As Long
Dim ws1LastColumn As Long
Dim wsTlastRow As Long
Dim v1 As Variant
Dim v2 As Variant
Dim k As Long
Dim j As Long
Set wsT = Worksheets("集計")
Set ws1 = Worksheets("比較1")
Set ws2 = Worksheets("比較2")
Set dic1 = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")
wsTlastRow = wsT.Cells(Rows.Count, "A").End(xlUp).Row
ws1LastColumn = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
num_of_days = ws1LastColumn - 4
ReDim mat(1 To wsTlastRow - 2, 1 To num_of_days * 3)
' 比較1，比較2の値を配列に持つ
wslastRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row
v1 = ws1.Range(ws1.Cells(3, 1), ws1.Cells(wslastRow, 4 + num_of_days)).Value
wslastRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row
v2 = ws2.Range(ws2.Cells(3, 1), ws2.Cells(wslastRow, 4 + num_of_days)).Value
'集計の各パターンについて繰り返し
For k = 3 To wsTlastRow
dic1.RemoveAll
dic2.RemoveAll
'条件をもとにdicを作成
For j = 1 To n1
If wsT.Cells(k, j) <> "" Then dic1(CStr(wsT.Cells(k, j).Value)) = Empty
Next
For j = 1 To n2
If wsT.Cells(k, n1 + j) <> "" Then dic2(CStr(wsT.Cells(k, n1 + j).Value)) = Empty
Next
Call myCount(k, v1, 1) '条件にあった比較1データを抽出し､matに加算
Call myCount(k, v2, 2) '条件にあった比較2データを抽出し､matに加算
Next
Call 差額算出
'結果書込
wsT.Cells(3, n1 + n2 + 1).Resize(UBound(mat, 1), UBound(mat, 2)) = mat
End Sub
Function myCount(k As Long, v As Variant, pos As Long)
'k行目にあるパターンについて、条件に合う数値をshの各データから検索し、
'結果を、配列matのpos列から3列ごとに加算
Dim s1 As String
Dim s2 As String
Dim j As Long, p As Long
'条件にあったデータをmatに加算
For j = LBound(v, 1) To UBound(v, 1)
s1 = v(j, 2)
s2 = v(j, 3)
If dic1.Exists(s1) Then ' チーム条件
If dic2.Exists(s2) Then ' No条件
For p = 1 To num_of_days
mat(k - 2, pos + 3 * (p - 1)) _
= mat(k - 2, pos + 3 * (p - 1)) + v(j, 4 + p)
Next
End If
End If
Next
End Function
Function 差額算出()
Dim j&, k&
For j = 1 To UBound(mat, 1)
For k = 1 To num_of_days
mat(j, 3 + 3 * (k - 1)) = mat(j, 1 + 3 * (k - 1)) - mat(j, 2 + 3 * (k - 1))
Next
Next
End Function
（γ） 2021/09/06(月) 07:56
今まだ出先で実際に試せていませんが、マクロのコードもこの後じっくり解読させて頂いて、今後連想配列はぜひ完全マスター出来るようにしていきたいなと思っております。
本当にありがとうございます。教えて頂いた考え方を今後使いこなせるように、頑張っていきたいと思います。
（勉強中） 2021/09/06(月) 19:31
＞また、配置は中間表をつくって、最終の表から、それを参照させれば済む話です。
これは以下のようなことです。
A B C D E F G H I J K L M
1
2 ﾁｰﾑ1 ﾁｰﾑ2 ﾁｰﾑ3 ﾁｰﾑ4 No.1 No.2 No.3 8/1日 8/2 8/3 8/4 8/5
3 a 1 2 3 900 200 1000 1000 1500
4 a b c d 2 3500 100 0 200 0
5 b c 2 3 3500 0 0 200 0
6 c 1 3 1000 200 0 0 0
7
8 a 1 2 3 90 20 100 100 150
9 a b c d 2 350 10 0 20 0
10 b c 2 3 350 0 0 20 0
11 c 1 3 100 20 0 0 0
12
13 8/1 8/2
14 a 1 2 3 900 90 810 200 20 180
15 a b c d 2 3500 350 3150 100 10 90
16 b c 2 3 3500 350 3150 0 0 0
17 c 1 3 1000 100 900 200 20 180
（なお、比較2データは、比較1*0.1とした検証用のため仮数値）
11行目までは、ユーザー定義関数SUMIFS2を使えば作成できますね。
どうしても3列おきにしたいということなら、 H14: =INDEX($H$3:$L$6,ROW($A1),1+INT((COLUMN()-8)/3)) I14: =INDEX($H$8:$L$11,ROW($A1),1+INT((COLUMN()-9)/3)) J14: =H14-I14 などとしておいて H14:J14をH14:V17へコピーペイストするだけです。
(1)ユーザー定義関数の使用 (2)作業領域を躊躇わずに使用すること (3)それぞれは簡単な部品を組み合わせること こうした考え方をとれば、一足飛びに完成品にしようと悩むよりも簡単ですし、 思考の節約になるはずです。
なお、マクロでのdictionaryはこのケースで必須なわけではなく、 ワークシート関数 COUNTIFを使って、>0 で判定させたり、 Application.MATCH を使って、エラーになるかならないかで判定してもよいと思います。 色々な方法があります。
# 貼付処理まちがえていたので修正。(0:12)
（γ） 2021/09/06(月) 23:56
関数にしても、マクロにしてもγ様のような柔軟な発想が必須ですね。
それぞれの事例で何が適切な方法なのか、まだよく理解出来ていない初心者で駆け出しですが、
教えて頂いた事無駄にしないように、また引き出しを増やしていけるように、頑張っていきたいと思います。
大変、ありがとうございましたm(_ _)m！！
（勉強中） 2021/09/07(火) 04:46
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.