【EXCEL】条件＋上位○位までを集計したい

質問

以下のようなデータがあります
(列)a,b,c
2,aaa,1000
2,bbb,1500
2,ccc,800
3,ddd,1000
3,eee,1500
3,fff,800

a=コード、c=金額

ここで以下の条件で集計したいのです。
「コードが2のもので上位2位までの金額を集計したい」

このような事を関数で行うとどうなりますでしょうか。
調べましたがお手上げでした。
すみませんが宜しくお願いします。

回答No.4

Wendy02 · ベストアンサー

こんばんは。

＞例えばコードが3で上位30位までの金額を集計したい。と。

=SUMPRODUCT(LARGE(($A$1:$A$100=3)*($C$1:$C$100),ROW(A1:A30)))

というような具合になりますね。

＞SUMPRODUCTもROWもまだモノにしていませんが、これを期に理解したいと思います。

少し、『配列数式』のことを書いておきます。

SUMPRODUCT は、配列数式のおまじないのようなもです。

例えば、

=A1:A10 としたら、だいたいは　#VALUE!　というエラーになるはずです。
なぜか考えたことありますか？

空のセルの A1:A10 をマウスで範囲を取って、F9 を押してみると、
={0;0;0;0;0;0;0}　

（『{}』この中カッコが、配列の中に収まっているというサインです。）

というように見えます。その「;」 が、邪魔で、#VALUE!のエラーが出ています。
しかし、数値としての値は存在しています。それを SUMPRODUCT()で囲むと、きちんとした数値が出てきます。これは、Excelの計算用キャシュから、SUMPRODUCT()が、実体化させてワークシートに表していると考えたらよいかと思います。これは、セル１つ１つに、そのような入れ物があるようです。

　=SUMPRODUCT(LARGE(($A$1:$A$6=2)*($C$1:$C$6),ROW(A1:A2)))
以上の式から、SUMPRODUCTを取ると、

= LARGE(($A$1:$A$6=2)*($C$1:$C$6),ROW(A1:A2))
ですね。

これに、また、マウスで、「LARGE」から、最後尾の　「))」 までの範囲を取って、F9 を押すと、
　={1500;1000}

という値が見えるはずです。つまり、値は出ているけれど、実体化していないので、SUMPRODUCTをつけてあげます。

このほかにも、一旦、F2を押して、再度、SHIFT キーとCTRL キーを押しながら、ENTER を押すと、やはり、式が実体化して、同じ値が出てくるはずです。これを、『配列の確定』と呼んでいます。

つまり、
分解すると、
= SUM(LARGE(($A$1:$A$6=2)*($C$1:$C$6),1), LARGE(($A$1:$A$6=2)*($C$1:$C$6),2)) に他なりませんね。

このROW(A1:A2)のことを『インクリメント(Increment)』　と呼んでいます。
それから、($A$1:$A$6=2)*($C$1:$C$6)のように、高さ（A1:A6)のように、両方の中の範囲(高さ・幅）を合わせることを、『配列の共通部分参照』と呼んでいます。

=ROW(A1:A2)
も同じようにして、マウスで、範囲を取って、F9で見てください。
={1;2}
というようになっているはずです。
引数に、1,2,3,.... と数字を与えたいときに、このような方法を使います。

例えば、
　=SUMPRODUCT(ROW(A1:A10))
とすれば、55 という値が出てきます。それは、1～10までを足した計算です。

なお、以下は、私が、一番最初に、配列のことを知ったサイトです。（当時は、2000でしたが）

http://support.microsoft.com/default.aspx?scid=kb;ja;402181
[XL2002] n 行おきの合計を算出する方法

ただ、配列数式は、Excel 2000以下ですと、その制限があります。また、関数の中でも、MODEやTRANSPOSE, MINVERSE,MMULT など、配列しか取らないものもあります。

私の記憶の範囲では、SUMPRODUCT()だけが、配列の確定をしなくて済む関数のはずです。また、SUMPRODUCTの中は、条件式などは取れませんから、その場合は、配列の確定をしなくてはなりません。また、配列の確定をした場合は、若干、計算スピードが落ちるようです。

引数にも、LARGE, SMALL などのように、配列を引数に取れる関数もあれば、同じ仲間のRANKは、配列を数式に取れません。私は、その理由を、RANKのほうが出来た年代が古いからだと思っています。RANKやCOUNTIF などの検索範囲に配列が取れるとひじょうに便利になると思っていますが、改変する可能性は薄いようです。また、配列と共に良く使われる関数は、MATCH関数です。

最後に、入力規則や条件付き書式は、特殊な使い方にはなりますが、配列の確定せずに値を受け取ります。

例: 入力規則で、半角文字を制限させる

=AND(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>47,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<123)

長文、お付き合い下されたら、感謝いたします。
この機に、疑問に思うことは、質問されたほうがよいかと思います。

お礼

うっわー。。めちゃめちゃありがとうございます。
こんなにご丁寧にご説明されたのははじめてなので感激です。
一通り読まさせていただきました。
少し時間がかかりそうなのでまたじっくり読まさせていただきますね。

ちなみにオフィスの講師の方なのでしょうか？
（すみません、あまりにお詳しいので（笑））

すみません、もう少しお付き合いしていただけるならお願いします。
本題の件なのですが、例えば例にある
「コードが3で上位2位までの金額を集計」だった場合は、ROWの部分がROW(A4:A5)となると思うのですが、

このA4:A5という範囲が不明だった場合どうすれば良いのでしょうか。

データが多くコード2の件数も決まっていないので、次のコード3がどの行からになるのか不明なのです。

説明べたで申し訳ございません。

補足

すみません、勘違いしてました！！
なんとなく理解してきました。

今回の本題はこれで解決していますね。
（すみません・・）

ご丁寧にありがとうございました。

回答No.3

imogasi

配列数式で
Ａ列　　Ｂ列
a 12
b 34
c 5
a 6
b 3
c 4
a 23
a 12
s 11
d 1
=LARGE(IF(A1:A10="a",B1:B10),1)+LARGE(IF(A1:A10="a",B2:B11),2)
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲの３つのキーを同時に押します。
結果３５

お礼

レスありがとうございます。

正直、もう理解できません。

例えば金額が上位３０位までを集計する。という事も
これで可能なのでしょうか？

回答No.2

Wendy02

こんばんは。

=SUMPRODUCT(LARGE(($A$1:$A$6=2)*($C$1:$C$6),ROW(A1:A2)))

この式を加工する時、ROW(A1:A2)は、常に、A1 ～始ります。

お礼

レスありがとうございます。

SUMPRODUCTもROWもまだモノにしていませんが、これを期に理解したいと思います。

ちなみにここでいうROWはどういう働きをするのでしょうか？

補足

ごめんなさい補足です。

例に出したデータはあくまで例です。
現実はもっとデータがあります。

なので拡張性がほしいのです。

例えばコードが3で上位30位までの金額を集計したい。と。

何か良い方法があればご教授ください

回答No.1

oresama

コードが自然数、金額が4桁以下が前提で、
データが、A1：C6の範囲にある場合、

D1に
=IF(A1=2,A1,0)*10000+C1
以下D6までコピー

上位2位までの金額の集計を返したい任意のセルに、
=LARGE(D1:D6,1)-20000+LARGE(D1:D6,2)-20000
で、答えは出ます。

2をセル参照にして、（例えばE1）
20000をE1*10000と置換したら汎用性もでると思います。

お礼

レスありがとうございます。

まだ試さずにですがお先にお礼をさせていただきます。