[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『集計計算の方法』（天使）
以前、集計計算のやり方を教えていただいたのですが、項目を下げたいので、行挿入すると、計算しなくなりました。列の挿入は、問題ないのですが、行を挿入すると計算しません。計算式を見直しても原因が分かりません。以前の質問内容は下記の通りです。
＊ABCのデータをFGHにまとめたい。関数による計算式を教えて下さい！
A B C D E F G H
1 品番 厚み 重さ 品番 厚み 重さ
2 AA 0.5 13 AA 0.3 29
3 AA 0.3 13 AA 0.5 13
4 BA 0.3 14 BA 0.2 11
5 HB 0.6 16 BA 0.3 14
6 HB 0.6 14 DD 0.2 14
7 DD 0.2 14 HB 0.6 30
8 AA 0.3 16
9 BA 0.2 11
10
11
12
−式−
F2=IF(COUNT(INDEX(0/((MATCH($A$2:$A$100&"_"&$B$2:$B$100,$A$2:$A$100&"_"&$B$2:$B$100,0)=ROW($A$1:$A$99))),))<ROW($A1),"",
INDEX(A$2:A$100,SMALL(INDEX((MATCH($A$2:$A$100&"_"&$B$2:$B$100,$A$2:$A$100&"_"&$B$2:$B$100,0)<>ROW($A$1:$A$99))*9^99+
ROW($A$1:$A$99),),ROW($A1)))&"")
G列にフィルコピー、必要分フィルダウン。
H2=IF(F2="","",SUMPRODUCT((A$2:A$100=F2)*(B$2:B$100=--G2),C$2:C$100))
必要分フィルダウン。
・ マクロならばこんな感じでできます
Sub Angel()
Dim tbl, i As Long, ky
With CreateObject("Scripting.Dictionary")
tbl = Range("A1").CurrentRegion.Resize(, 3).Value
For i = 2 To UBound(tbl, 1)
ky = tbl(i, 1) & "_" & tbl(i, 2)
If .Exists(ky) Then
.Item(ky) = .Item(ky) + tbl(i, 3)
Else
.Add ky, tbl(i, 3)
End If
Next
Range("F:H").ClearContents
Range("F1:H1").Value = Range("A1:C1").Value
i = 1
For Each ky In .Keys
i = i + 1
Range("F" & i).Value = Split(ky, "_")(0)
Range("G" & i).Value = Val(Split(ky, "_")(1))
Range("H" & i).Value = .Item(ky)
Next
Range("F1", Range("H" & Rows.Count).End(xlUp)).Sort _
Key1:=Range("F1"), _
Order1:=xlAscending, _
Key2:=Range("G1"), _
order2:=xlAscending, _
Header:=xlYes
End With
Erase tbl
End Sub
どこかで見たことがあると思ったら、↓これですね？ [[20090507073921]]『品番を製品厚み毎にまとめたい』（天使） 具体的にどこに行挿入して、どのように上手くいかなかったのか、そのときの数式なども提示していただけると 解決に結びつくと思います。 (ROUGE)
A B C D E F G H
1
2
・
・
11 品番 厚み 重さ 品番 厚み 重さ
12 AA 0.5 13 AA 0.3 29
13 AA 0.3 13 AA 0.5 13
14 BA 0.3 14 BA 0.2 11
15 HB 0.6 16 BA 0.3 14
16 HB 0.6 14 DD 0.2 14
17 DD 0.2 14 HB 0.6 30
18 AA 0.3 16
19 BA 0.2 11
20
11
12
Ｆ１２のマスに
=IF(COUNT(INDEX(0/((MATCH($A$12:$A$110&"_"&$C$12:$C$110,$A$12:$A$110&"_"&$C$12:$C$110,0)=ROW($A$11:$A$109))),))<ROW($A11),"",
INDEX(A$12:A$110,SMALL(INDEX((MATCH($A$12:$A$110&"_"&$C$12:$C$110,$A$12:$A$110&"_"&$C$12:$C$110,0)<>ROW($A$11:$A$109))*9^99+
ROW($A$11:$A$109),),ROW($A11)))&"")
教えていただいた式をこのように変えてみました。しかし、何も表示しません。
太字部分が違うところです。見比べてみてください。(ROUGE)
=IF(COUNT(INDEX(0/((MATCH($A$12:$A$110&"_"&$C$12:$C$110,$A$12:$A$110&"_"&$C$12:$C$110,0)=ROW($A$1:$A$99))),))<ROW($A1),"",
INDEX(A$12:A$110,SMALL(INDEX((MATCH($A$12:$A$110&"_"&$C$12:$C$110,$A$12:$A$110&"_"&$C$12:$C$110,0)<>ROW($A$1:$A$99))*9^99+
ROW($A$1:$A$99),),ROW($A1)))&"")
式
=IF(COUNT(INDEX(0/((MATCH($G$13:$G$64&"_"&$N$13:$N$52,$G$13:$G$52&"_"&$N$13:$N$52,0)=ROW($G$2:$G$100))),))<ROW($G1),"",
INDEX(G$13:G$52,SMALL(INDEX((MATCH($G$13:$G$52&"_"&$N$13:$N$52,$G$13:$G$52&"_"&$N$13:$N$52,0)<>ROW($G$1:$G$99))*9^99+
ROW($G$1:$G$99),),ROW($G1)))&"")
どこが悪いのかわかりません。
それと、疑問なのですが、太字の箇所は、どうしてこのような数字になるのですか？出来れば教えて下さい。
そうですね。。。数式の意味を理解することが近道ですよね。
説明は大の苦手ですが、試しに書いてみます。
数式が長いので、途中で挫折するかもしれません（笑）が、よろしくお願いします。
ここで取り上げるサンプルは、表のサンプルもあるため、元の数式にします。
ただし、100行までだと説明が非常に面倒なので、10行までに変更しています。
F2=IF(COUNT(INDEX(0/((MATCH($A$2:$A$10&"_"&$B$2:$B$10,$A$2:$A$10&"_"&$B$2:$B$10,0)=ROW($A$1:$A$9))),))<ROW($A1),"",
INDEX(A$2:A$10,SMALL(INDEX((MATCH($A$2:$A$10&"_"&$B$2:$B$10,$A$2:$A$10&"_"&$B$2:$B$10,0)<>ROW($A$1:$A$9))*9^99+
ROW($A$1:$A$9),),ROW($A1)))&"")
◆ステップ１◆
まず上記数式をIF関数の論理式、真の部分、偽の部分の３つに分けます。
論理式
COUNT(INDEX(0/((MATCH($A$2:$A$10&"_"&$B$2:$B$10,$A$2:$A$10&"_"&$B$2:$B$10,0)=ROW($A$1:$A$9))),))<ROW($A1)
真の部分
"" のみ
偽の部分
INDEX(A$2:A$10,SMALL(INDEX((MATCH($A$2:$A$10&"_"&$B$2:$B$10,$A$2:$A$10&"_"&$B$2:$B$10,0)<>ROW($A$1:$A$9))*9^99+ROW($A$1:$A$9),),ROW($A1)))&""
見ていただくと分かるとおり、真の部分については単に空白を入れるということしか行っていません。
◆ステップ２◆
論理式の部分を紐解いていきます。
COUNT(INDEX(0/((MATCH($A$2:$A$10&"_"&$B$2:$B$10,$A$2:$A$10&"_"&$B$2:$B$10,0)=ROW($A$1:$A$9))),))<ROW($A1)
まず、もともとの問題としては、A列とB列を組み合わせて項目としているところです。
そのため、A列とB列を組み合わせてユニーク（単一の）なキーを作成してあげる必要があります。
その処理が、 $A$2:$A$10&"_"&$B$2:$B$10 というものです。
このキーを元にMATCH関数でそのキーが何行目にあるかを特定しています。
ところで、この数式では配列という概念を使用しています。
配列というのはデータの集合体を扱うための概念で、上記論理式ではMATCH関数の第1引数と最初のROW関数で使っています。
なお、INDEX関数は配列化するための関数です。
INDEX関数の第1引数に配列を入れて、カンマ（,）で区切り、第2引数以降を省略（あるいは0を入れる）すると、配列になります。
MATCH($A$2:$A$10&"_"&$B$2:$B$10,$A$2:$A$10&"_"&$B$2:$B$10,0)
ようやく上記数式の説明の土台の説明が終了しました。
で、上記数式はなにをしているかというと、A2&"_"&B2、A3&"_"&B3、・・・、A10&"_"&B10という値が、
それぞれ A2:A10&"_"B2:B10 のどの部分に一致するかを調べています。
具体的な値があると分かりやすいので、スレの一番始めの表を用いますと、
[A] [B] [C]
[1] 品番 厚み 重さ
[2] AA 0.5 13
[3] AA 0.3 13
[4] BA 0.3 14
[5] HB 0.6 16
[6] HB 0.6 14
[7] DD 0.2 14
[8] AA 0.3 16
[9] BA 0.2 11
[10]
A2:A10&"_"&B2:B10 は配列としては次のようになります。
{"AA_0.5","AA_0.3","BA_0.3","HB_0.6","HB_0.6","DD_0.2","AA_0.3","BA_0.2","_"}
検索値もそれぞれ同様です。
"AA_0.5" がどこに一致するか --> 1番目 --> 戻り値は 1
"AA_0.3" がどこに一致するか --> 2番目 --> 戻り値は 2
"BA_0.3" がどこに一致するか --> 3番目 --> 戻り値は 3
"HB_0.6" がどこに一致するか --> 4番目 --> 戻り値は 4
"HB_0.6" がどこに一致するか --> 4番目 --> 戻り値は 4
"DD_0.2" がどこに一致するか --> 6番目 --> 戻り値は 6
"AA_0.3" がどこに一致するか --> 2番目 --> 戻り値は 2
"BA_0.2" がどこに一致するか --> 8番目 --> 戻り値は 8
"_" がどこに一致するか --> 9番目 --> 戻り値は 9
上記は配列での処理ということになりますので、戻り値は {1,2,3,4,4,6,2,8,9} と書けます。
これでMATCH関数の戻り値が特定されました。
で、元の数式でMATCH関数の右をちらっとみてみると、ROW関数（ROW($A$1:$A$9)）があります。
これもINDEX関数の中に入っているので、配列ということになります。
したがって、配列としては次のようになります。
{ROW(A1),ROW(A2),・・・,ROW(A9)}
すなわち、{1,2,3,4,5,6,7,8,9} ということになります。
＝で結ばれていますので、配列同士を比較する数式になっています。
{1,2,3,4,4,6,2,8,9}={1,2,3,4,5,6,7,8,9}
↓
{1=1,2=2,3=3,4=4,4=5,6=6,2=7,8=8,9=9}
↓
{True,True,True,True,False,True,False,True,True}
さらに、上記配列を分母として、分子を0とした計算を行っています。
0/((MATCH($A$2:$A$10&"_"&$B$2:$B$10,$A$2:$A$10&"_"&$B$2:$B$10,0)=ROW($A$1:$A$9)))
~~この部分。
したがって、0/{True,True,True,True,False,True,False,True,True} の計算が必要です。
四則演算では True=1、False=0 として扱われますので、
0/{True,True,True,True,False,True,False,True,True}
↓
0/{1,1,1,1,0,1,0,1,1}
↓
{0,0,0,0,#DIV/0!,0,#DIV/0!,0,0}
となります。
その外側にあるINDEX関数は配列化のための関数なので、特別なことをしていません。
次にさらに外側にある関数がCOUNT関数ですので、
COUNT({0,0,0,0,#DIV/0!,0,#DIV/0!,0,0})
ということになります。
COUNT関数は数値をカウントする関数ですので、エラー値以外の0の個数を返します。
COUNT({0,0,0,0,#DIV/0!,0,#DIV/0!,0,0})=7
この数値とROW関数との比較で、数値がROW関数の戻り値より少ない場合には空白（""）を返しなさいという
論理式になっています。
ROW関数は行数を返す関数ですので、ROW($A1)としてフィルダウンする場合、1からの連番になります。
これで論理式の説明を終了しますが、この説明でROW関数の引数の入れ方が分かったのではないでしょうか？
最初のROW関数はA列である必要はありませんが、1行目からデータ個数分下の範囲を入れる。
2個目のROW関数には1行目のアドレスを入れる。
このことが重要です。
ステップ３は偽の部分の説明ですが、まずはステップ２までで。
(ROUGE)#Fatigue
=IF(COUNT(INDEX(0/((MATCH($G$13:$G$64&"_"&$N$13:$N$52,$G$13:$G$52&"_"&$N$13:$N$52,0)=ROW($G$2:$G$100))),))<ROW($G1),"",INDEX(G$13:G$52,SMALL(INDEX((MATCH($G$13:$G$52&"_"&$N$13:$N$52,$G$13:$G$52&"_"&$N$13:$N$52,0)<>ROW($G$1:$G$99))*9^99+ROW($G$1:$G$99),),ROW($G1)))&"")
私なりに考えたのですが、説明からすると、ROW($G$1:$G$99),),ROW($G1)))&"")のこの部分が違うと思うのですがどうでしょう？しかし、考えてROW関数を変えてみるのですが、計算されません。
最初のMATCH関数の引数、$G$13:$G$64 は $G$13:$G$52 では？
ROW($G$2:$G$100)、ROW($G$1:$G$99) --> ROW($G$1:$G$40) では？（全部で3箇所）
この部分を変更してみてください。
(ROUGE)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.