Excel 2007 複数条件での件数カウント

質問

至急でデータを作成する必要がありご教示いただけますと助かります。
Excel 2007において以下の状態、条件にて結果を抽出するための式（下記？を求める式）を教えてください。

【状態】
レベル　　　点数
（文字列）　（数値）　　
A3　　　　　9.1
A5　　　　　8.6
A7　　　　　7.6
A4　　　　　4.2
A6　　　　　3.1
A8　　　　　6.8
A2　　　　　2.1
A6　　　　　5.8
A3　　　　　6.8
【条件】
　　　　　　　A2, A3, A4の件数　　　A5, A6, A7,A8の件数
9.1～　　　　？　　　　　　　　　　　　？
7.5～9.0　　？　　　　　　　　　　　　？
5.7～7.4　　？　　　　　　　　　　　　？
4.0～5.6　　？　　　　　　　　　　　　？
～3.9　　　　？　　　　　　　　　　　　？

【文章にすると（一部）】
A2, A3, A4のレベルで9.1点以上の件数は？（回答1件）
A5, A6, A7, A8のレベルで5.7点以上、7.4以下の件数は？（回答2件）

以上、よろしくお願いいたします。

回答No.4

KURUMITO

作業列を作って簡単な関数を使って対応することでしょう。
レベルのデータがA2セルから下方に、点数がB2セルから下方に入力されているとします。
C列は作業列としてC2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(OR(A2="A2",A2="A3",A2="A4"),100+B2,200+B2))

お求めの表は次のようにします。
初めに条件を示す表ですがE2セルに9.1、F2セルは空、E3セルに7.5、F3セルに9、E4セルに5.7、F4セルに7.4、E5セルに4、F5セルに5.6、E6セルは空、F6セルに3.9とそれぞれ入力します。
G1セルにはA2,A3,A4の件数、H1セルにはA5,A6,A7,A8の件数とそれぞれ文字列を入力します。
そのごにG2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(E2:F2)=0,"",IF(E2="",COUNTIF(C:C,">=0"),COUNTIF(C:C,">="&(100+E2)))-IF(F2="",COUNTIF(C:C,">=200"),COUNTIF(C:C,">"&(100+F2))))

H2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(E2:F2)=0,"",IF(E2="",COUNTIF(C:C,">=200"),COUNTIF(C:C,">="&(200+E2)))-IF(F2="",0,COUNTIF(C:C,">"&(200+F2))))

回答No.3

kagakusuki

確認したいのですが、数値の欄が点数となっているいう事は、値に0.1未満の誤差などは存在せず、きっかり0.1刻みの値であり、5.65とか7.45といった、境界付近の値が入力される事はあり得ないと考えても宜しいのでしょうか？
　これが、キーボード入力された点数などではなく、（平均値等の）割り算を含む計算結果から得られた数値であったり、機械部品の寸法の様に元データに誤差が含まれている数値などの場合には、「3.9を超えて、4.0未満の値」や「5.6を超えて、5.7未満の値」、「7.4を超えて、7.5未満の値」、「9.0を超えて、9.1未満の値」等々は、どの条件に加えれば良いのかというも問題が生じますので、それらの境界付近の数値を質問者様はどのように扱いたいと御考えなのかという事関して、追加情報を御教え頂かなければ、正しい回答をする事が出来ません。
　ですから、取り敢えずの話として、0.1未満の端数は存在しない場合に関して、回答致します。
　(尚、0.1未満の端数が存在する場合に対応するExcelファイルも、一応、既に動作確認用に作成済みですので、もし、そちらの方が質問者様の使用目的に合っている場合には、補足欄等を使用して、御一報願います)

　今仮に、元データのレベルの名称がA列に入力されていて、点数はB列に入力されているものとします。
　それと、条件の数値範囲や「複数あるレベルの内、どれとどれのレベルを一緒のデータとして扱うのか」は、その都度変更になる事もあると思いますから、「範囲を指定する数値」や「一緒のデータとして扱うレベル」の名称を、決められたセルに入力し直す事で変更可能とする事に致します。
　まず、K1セルに「グループ1」、L2セルに「グループ2」と入力して下さい。
　そして、「グループ1」の下にあるK1、K2、K3の各セルに1つずつ、「A2」、「A3」、「 A4」という、1まとめにして扱うレベルの名称を入力し、
「グループ2」の下にあるL1、L2、L3、L4の各セルに1つずつ、「A5」、「A6」、「A7」、「A8」という、「グループ1」とは別の、1まとめにして扱うレベルの名称を入力して下さい。
　次に、
D2セルに　　9.1
D3セルに　　7.5　　　、F3セルに　　9.0
D4セルに　　5.7　　　、F4セルに　　7.4
D5セルに　　4.0　　　、F5セルに　　5.6
　　　　　　　　　　　　　　F6セルに　　3.9
という具合に、各範囲を指定する数値を入力して下さい。
　次に、E2セルに次の数式を入力して下さい。

=IF(COUNT($D2,$F2),"～","")

　次に、E2セルをコピーして、E3以下に貼り付けて下さい。
　次に、G1セルに次の数式を入力して下さい。

=K$1&""

　次に、G2セルに次の数式を入力して下さい。

=IF(AND(COUNT($D2,$F2),MATCH("*?",K:K,-1)>ROW($K$1)),SUMPRODUCT(COUNTIFS($A:$A,"="&INDEX(K:K,ROW(K$1)+1):INDEX(K:K,MATCH("*?",K:K,-1)),$B:$B,IF(ISNUMBER($D2),">="&$D2,"<>"),$B:$B,IF(ISNUMBER($F2),"<="&$F2,"<>"))*1),"")

【註】ANo.1様が

>SUMPRODUCT関数は配列数式になってしまいますので、データ量が極端に多い場合は作用列を使用するなどすて、別の方法を考える必要があると思います。

と仰っているのは、SUMPRODUCT関数による「繰り返し計算の回数」が多い場合の話であり、ANo.1様の関数ではA列・B列の1000行目まで繰り返し計算を行う（例え、データが入力されている行数が少ない場合でも、1000回繰り返し計算される）のに対し、
上記の私の関数は、A列・B列に値が入力されている行数には関わりなく、1つのグループに入力されているレベルの個数に等しい回数しか繰り返し計算を行わない様になっておりますので、計算処理に要する負荷が重くなる事はないと思います。

　次に、G1～G2の範囲をコピーして、H1～I2の範囲に貼り付けて下さい。
　次に、G2～I2の範囲をコピーして、同じ列の3行目以下に（指定範囲条件の行数を上回るのに十分な行数となるまで）貼り付けて下さい。

　これで、各グループ別に、各数値範囲の条件に合致しているデータの個数の一覧が、自動的に表示されます。
　又、D列やF列の条件を指定する各数値は、後から変更する事も可能ですし、条件の行数を増減する事も出来ます。（間にあるE列には「～」が自動表示されます）
　尚、この関数では、条件を指定する数値が入力されていない行には、何も表示されない様になっておりますから、G2～I2の範囲をコピーしたものを、最初のみ十分に下の方にまで貼り付けておきさえすれば、後から条件の行数を増減する際に、関数が入力されているセルを調整する必要は御座いません。
　又、K列～M列の2行目以下に入力するレベルの名称も、名称や入力する数を後から変更する事が出来ます。

回答No.2

MackyNo1

エクセル2007をお使いなら、計算負荷の少ないCOUNTIFS関数を使うことをお勧めします。

例えば、添付画像のレイアウトなら、D1セル（以上と表示されているセル）に上限数字よりも大きい数字の「100」を入力し、セルの書式設定で表示形式をユーザー定義にして「[=100]"以上"」と入力した表を作成しておきます（D2セル以下は区切りの数字を入力）。

E2セルに以下の式を入力し下方向にオートフィルコピーします。
=SUM(COUNTIFS($A$2:$A$100,{"A2","A3","A4"},$B$2:$B$100,">="&$D2,$B$2:$B$100,"<"&$D1))

同様にF2セルに以下の式を入力し（E2セルをコピーしてA2～A4の部分を修正する）下方向にオートフィルします。
=SUM(COUNTIFS($A$2:$A$100,{"A5","A6","A7","A8"},$B$2:$B$100,">="&$D2,$B$2:$B$100,"<"&$D1))

回答No.1

tom04

こんばんは！
一例です。

↓の画像で左側が元データ（Sheet1）で右側のSheet2に表示するとします。

Sheet2の表は画像のようにしておきます。
（A列以上～B列未満としていて、B2セルにはこれ以上はない！という数値を入れておきます）

Sheet2のC2セルに
=SUMPRODUCT((ISNUMBER(FIND(Sheet1!$A$1:$A$1000,C$1)))*(Sheet1!$B$1:$B$1000>=$A2)*(Sheet1!$B$1:$B$1000<$B2)*(Sheet1!$A$1:$A$1000<>""))
という数式を入れ列方向・行方向にオートフィルでコピーすると
画像のような感じになります。

※　とりあえず1000行目まで対応できる数式にしていますが、
SUMPRODUCT関数は配列数式になってしまいますので、データ量が極端に多い場合は
作用列を使用するなどすて、別の方法を考える必要があると思います。

参考になりますかね？m(_ _)m

お礼

早速ご教示いただき感謝です。

実はこの作業にはもう1段階ありまして、それも考慮に入れますと更に悩ましい状況です。
別途ご質問させていただきますが、その際もお知恵を拝借いただけますと幸いです。

まずは本件の御礼まで。