条件付きグループ集計

質問

関数で以下のようなことができるでしょうか。
ピボットテーブル, vbaは使用しない前提でお願いします。

以下のようなDataから、情報を得たいと考えます。

Data
感覚としては、A列がグループ番号, B列が当該グループの参加者の達成率のような感じです。

A B
1 50%
1 70%
1 0%
2 10%
2 35%
3 100%
3 70%
3 20%

入力: x[%]
出力
y1: B列がx[%]以上のレコードが含まれるグループ数
y2: 件数上位1グループの件数占有率

この場合、
x=100なら、出力y1=1, y2=100%
x=50なら、出力y1=2, y2=50%
x=10なら、出力y1=3, y2=14%

となります。
Dataのc列以降に関数等を入力して加工する分には構いません。

回答No.3

kagakusuki

SUMPRODUCT関数や配列式は、処理の対象としている行数が多くなりますと、処理が重くなるという短所があります。
　御質問の件の場合、使用するExcelのバージョンがExcel2007以降のものであり、尚且つ、作業列を使用しても構わないのでしたら、SUMPRODUCT関数や配列式を使う必要は御座いません。

　今仮に、xの値をE1セルに入力すると、G列を作業列として使用して、y1の値がE2セルに、y2の値がE3セルに、それぞれ表示される様にするものとします。

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

=IF($A2="","",IF(AND(COUNTIF($A$1:$A2,$A2)=1,COUNTIFS($A:$A,$A2,$B:$B,">="&$E$1)),COUNTIFS($A:$A,$A2,$B:$B,">="&$E$1),""))

　次に、G2セルをコピーして、G3以下に貼り付けて下さい。
　次に、E1セルとE3セル(E2セルは除く)の書式設定の表示形式を[パーセンテージ]に設定して下さい。
　次に、E2セルに次の関数を入力して下さい。

=COUNT($G:$G)

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

=IF(COUNT($G:$G),MAX($G:$G)/COUNTIF($B:$B,">="&$E$1),"")

　以上です。

　因みにこの方法の場合は、件数の多さで同数1位のグループが複数存在していても、1つのグループが占有している件数を基にした件数占有率が表示されます。

回答No.2

MarcoRossiItaly

ご要望のことができる関数はないので、数式を作る必要があります。作業列を用意しない場合は、次のとおり、できることはできますが難解です。したがって作業列を用意しない方法は別にお勧めではなく、参考のみですので、ベストアンサーは辞退します。

F1　50　など
F2　=ROUND(SUMPRODUCT(1/(COUNTIFS(A1:A30,A1:A30,B1:B30,">="&F1)+9^9*((A1:A30="")+(B1:B30="")+(B1:B30<F1)>0))),)
F3　=100*MAX(INDEX(COUNTIFS(A1:A30,A1:A30,B1:B30,">="&F1),))/COUNTIF(B1:B30,">="&F1)

なお上式においても、No.1 さんと同じく、x＝10 のとき y2≠14 です。

回答No.1

tom04

こんばんは！
なかなか回答がつかないようなので・・・

＞y2: 件数上位1グループの件数占有率
の解釈が違うかもしれません。
↓の画像でいえば、B列のデータでE1セルに入力した数値以上で最大値のグループのE1セル以上のデータ数を
B列全体のE1セル以上のデータ数で割った割合と解釈しました。
すなわち画像ではB列に50％以上のデータが4個存在し、その中で最大値のグループは100％の「3」になりますので
「3グループ」のデータで50％以上の数（2個）を全体の50％以上（4個）で割っています。

となるとE1セルに10％を入力した場合、ｙ2は14％にならないので外している可能性が高いのですが、
あえてたたき台として投稿してみます。

画像のように作業列を1列設けます。
作業列のC2セルに
=IF(AND(B2>=E$1,B2=MAX(IF(A$2:A$1000=A2,B$2:B$1000))),A2,"")
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
これをオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてE4セルに
=COUNT(C:C)

F4セルに
=SUMPRODUCT((B1:B1000>=E1)*(A1:A1000=OFFSET(C1,MATCH(MAX(B:B),B:B,0)-1,,1)))/COUNTIF(B:B,">="&E1)

という数式を入れています。

※　最大値が複数ある場合はめちゃくちゃな表示になると思います。
※　一発で解決！とはいかないと思いますが、とりあえずはこの程度で・・・m(_ _)m