SUMPRODUCT関数について

質問

SUMPODUCT関数を使ったとき、並び替えをすると参照する値がずれて困っています。

たとえば、ある会に参加した人を調べたいとします。

　　　　A　　　　　　B　　　　　　C　　　　　　　　　　　
1　　　参加日　　名前　　　出身　　　　　　　　　　
2　　　12/1　　　　　aaa　　　東京　　　　　　　　　　
3　　　　　　　　　　　bbb　　　千葉　　　　　　　　　　
4　　　　　　　　　　　ccc　　　埼玉　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
・
・
・　　　　　　　　　　　　　　　　　　　　　　　

50 12/4　　　　　　kkk　　　千葉　　　　　　　　　
51　　　　　　　　　　　ppp　　 　静岡

　　E　　　　　　F　　　　　　G
　　名前　　　出身 　 参加数
1　　aaa　　　　東京　　　　１
2　　bbb　　　　千葉　　　　1
3　　ccc　　　　埼玉　　　　2
・
・
50　kkk　　　　千葉　　　　　1
51　ppp　　　　静岡　　　　　2　　　　　　　　　　　　　　　　　　　　　　

A・B列にデータの一覧が入っていて、この中から「aaa」さんかつ「東京」が何回参加したかを調べたいのでG列に、＝SUMPRODUCT(($A$1:$A$2000=E2)*($B$2:$B$2000=F2))という数式を入れています。
A～C列のデータは今は２００くらいなんですが、今後も増えていく予定で２０００は行く予定です。

A～C列はその日ごとの参加者が書いてあるデータ欄で
E～Gは参加者の一覧（集計欄）にしています。

ランダムに名前を打っているのであいうえお順に並び替えるために
E～G列を名前優先で並びかえをするとG列の参照範囲がずれてしまいます。
これがずれないようにすることはできないでしょうか？

参加者を数える作業は会が終わったごとにするので随時A・Bのデータが増えていくことになります。
excel2003を使用しています。

参照範囲がずれないようする方法がありましたらお聞きしたいです。
また、いまいちどうやって集計するのが効率的なのかわからず、考えた末この方法にしたのですが
もっと簡単に集計できる方法がありましたら教えていただきたいです＞＜

よろしくお願いいたします。

回答No.3

kagakusuki

>INDEXやROWは使ったことないので解読するのに苦労しています・・笑

　まず、INDEX関数は

INDEX(範囲, 行番号, [列番号])

という形式で表される関数で、「範囲」の所で指定したセル範囲内において、上から数えて「行番号」番目にあたる行の中の、左から数えて「列番号」番目の所にあるセルを指定する関数です。
　そして、範囲の所に1列のみ（或いは1行のみ）の直線上に並んだセル範囲を指定した場合には、 指定したセル範囲の幅（或いは高さ）は1セルしかありませんから、[列番号]は1以外にはありえない事になりますので、[列番号]を省略して、

INDEX(直線状の範囲, 行番号)

という形式で記述する事が出来ます。
　例えば、「範囲」にA列全体を指定し、「行番号」に2をして

INDEX(A:A,2)

とした場合、A列において上から2番目の所にあるセル、即ち、A$2セルを指定したのと同じ事になります。
　又、「範囲」の指定方法を絶対参照にして、

INDEX($A:$A,2)

とした場合には、$A$2と指定したのと同じ事になります。

　次に、ROW関数は、関数の括弧内に指定したセルの行番号を返す関数です。
　例えば、

ROW(D3)

とした場合には、D3セルが存在している行である3行目の行番号である3という数値が返されます。
　そして、ROW関数の括弧内にセル番号を何も入力せずに、

ROW()

とした場合には、その関数を入力しているセルの行番号が返されます。
　つまり、

ROW()

という関数を、例えばI2セルに入力した場合には、I2セルの行番号である2が返されますし、G5セルに入力した場合には、G5セルの行番号である5が返される事になります。

　さて、そうしますと、例えばI2セルに

INDEX($A:$A,ROW())

と入力しますと、ROW関数の部分は2が返されますから、

INDEX($A:$A,ROW())
　↓
INDEX($A:$A,ROW(G2))
　↓
INDEX($A:$A,2)
　↓
$A2

という事になり、$A2セルを指定した事と同じ事になります。
　一方、同じ関数を例えばG5セルに入力した場合には、$A5セルを指定した事と同じ事になります。
　つまり、

INDEX($A:$A,ROW())

という関数は、A列における同じ行のセルを指定したのと同じ事になります。
　同様に、例えば

INDEX($B:$B,ROW())

とした場合には、B列における同じ行のセルを指定したのと同じ事になります。
　又、

INDEX($A:$A,ROW()-1)

とした場合には、A列における1行上の行のセルを指定したのと同じ事になります。

　処で、

INDEX($A:$A,ROW())

という関数の中には、列番号が指定されているだけで、特定のセル或いは行などは何も指定されていません。
　そのため、この関数を例えばG5セルに入力した場合には、$A5セルを参照している事になるからと言って、並べ替え等を行った際に、A5セルの位置がずれてしまい、例えば3行下のA8セルに変わってしまったとしましても、

ROW()

という部分が勝手に、

ROW()+3

に変化してしまう訳ではありませんし、ROW関数の所で返されている5という値は、A5セルの行番号ではなく、関数が入力されているG5セルの行番号なのですから、A5セルの位置がずれたとしても影響される事はありません。

　従いまして、この方式でセルを指定しますと、並べ替えや、セルの切り取り・挿入等のセルの位置が変わってしまう様な操作を行っても、上下方向のずれに関しては影響を受ける事はまずなくなる訳です。
　SUMPODUCT関数に限らず、関数を用いている場合には、セルの位置がずれてしまうと正しい結果が得られなくなる事が多いので、この手法でセルを指定すると便利です。

　尚、1番上の項目名が入力されているセルの位置がずれる事はまずありませんので、$B$1等は、そのままセル番号を指定しています。

　解読に苦労されておられるとの事ですが、
「このROW関数が2行目のセルに入力されているから、この部分の数値は2になるので、INDEX($A:$A,ROW())の部分はA2セルを参照していて…」
などという事を一々考える様な事はなさらずに、
「INDEX($A:$A,ROW())という記述の所は同じ行のA列のセルである」
と考えれば良いと思います。

回答No.2

kagakusuki

済みません、色々と間違えておりました。

>＝SUMPRODUCT(($A$1:$A$2000=E2)*($B$2:$B$2000=F2))

という事ですが、実際には、名前が入力されているのはA列ではなくB列であり、出身が入力されているのはB列ではなくC列なのですね。

　そうなりますと、SUMPRODUCT関数を使う方法において、G2セルに入力する関数は、次の様な関数となります。

=IF(OR(INDEX($E:$E,ROW())="",INDEX($F:$F,ROW())=""),"",SUMPRODUCT(($B$1:INDEX($B:$B,2000)=INDEX($E:$E,ROW()))*($C$1:INDEX($C:$C,2000)=INDEX($F:$F,ROW()))))

　又、作業列を使う方法において、I2セルに入力する関数は、次の様な関数となり、

=IF(OR(INDEX($B:$B,ROW())="",INDEX($C:$C,ROW())=""),"",INDEX($B:$B,ROW())&"★"&INDEX($C:$C,ROW()))

G2セルに入力する関数は、次の様な関数となります。

=IF(OR(INDEX($E:$E,ROW())="",INDEX($F:$F,ROW())=""),"",COUNTIF($I:$I,INDEX($E:$E,ROW())&"★"&INDEX($F:$F,ROW())))

お礼

ご回答ありがとうございました。
私が列を間違えていたためわざわざ訂正してくださってありがとうございます＞＜
複雑な関数になるのですね。。。
INDEXやROWは使ったことないので解読するのに苦労しています・・笑

この数式は、もしA~C列をならびかえたときはまたずれてしまうのでしょうか？？

回答No.1

kagakusuki

参照範囲がずれない様にするだけでしたら、次の様な方法があります。

=IF(OR(INDEX($E:$E,ROW())="",INDEX($F:$F,ROW())=""),"",SUMPRODUCT(($A$2:INDEX($A:$A,2000)=INDEX($E:$E,ROW()))*($B$2:INDEX($B:$B,2000)=INDEX($F:$F,ROW()))))

或いは

=IF(OR(INDIRECT("E"&ROW())="",INDIRECT("F"&ROW())=""),"",SUMPRODUCT((INDIRECT("A2:A2000")=INDIRECT("E"&ROW()))*(INDIRECT("B2:B2000")=INDIRECT("F"&ROW()))))

　只、SUMPODUCT関数は繰り返し計算を行う関数で、計算対象とする行範囲に含まれている行数が増える程、繰り返しの回数も多くなるため、余り行数を多くしますと計算処理に要するコンピュータの負荷が大きくなり、計算を終了するまでに要する時間が長くなり過ぎるという短所があります。

　ですから、

>A～C列のデータは今は２００くらいなんですが、今後も増えていく予定で２０００は行く予定です。

という事であれば、SUMPODUCT関数を使わずに済ませる方法に切り替えた方が宜しいのではないかと思います。

　その様な場合には、一般的には、適当な列を作業列として使用する方法が使われます。
　作業列とは、計算処理がしやすいようにデータの前処理を行うための関数が入力されている列とでも考えて頂ければよいかと思います。
　例えば、I列を作業列として使用するものとした場合、まず、I2セルに次の様な関数を入力して下さい。

=IF(OR(INDEX($E:$E,ROW())="",INDEX($F:$F,ROW())=""),"",INDEX($E:$E,ROW())&"★"&INDEX($F:$F,ROW()))

　次に、I2セルをコピーして、I3以下に貼り付けて下さい。
　次に、G2セルに次の様な関数を入力して下さい。

=IF(OR(INDEX($E:$E,ROW())="",INDEX($F:$F,ROW())=""),"",COUNTIF($I:$I,INDEX($E:$E,ROW())&"★"&INDEX($F:$F,ROW())))

　そして、G2セルをコピーして、G3以下に貼り付けて下さい。

　これで、SUMPODUCT関数と同じ結果を、SUMPODUCT関数を使うよりも高速で求める事が出来ます。
　尚、上記の例では、I列を作業列として使用しておりますが、別シートの列を作業列として使用しますと、印刷の際等において無用の表示内容が印刷されてしまう事を避ける事が出来ます。