[[20211125224853]]　『グループ内における上位群抽出』（sai）　ページの最後に飛ぶ

『グループ内における上位群抽出』（sai）

以下のような参考表があるものとします
1行目　A列(名前)、B列(学年、6学年まで)、C列(組、各学年10組)、D列(点、100点満点)、E列(順位列)、F列(判定列)
2行目　田中太郎　3年　A組　99
3行目　鈴木一郎　1年　C組　33
4行目　山田三郎　5年　E組　12
　・
　・
　・
以下、学年・組・点数がバラバラで3,000人程度いるものとします(学年数及び組数は変動しうるものとします

知りたい数式
1　生徒全体の点数順位(降順)を求める数式
2　1における上位3/4に属するかを求める数式（属するならTRUE
3　学年及び組別の点数順位(降順)を求める数式
4　3における上位3/4に属するかを求める数式（属するならTRUE
　
条件
1　下位1/4は、順位母数を4で割り、小数点以下を切捨てた人数(値)とする
2　上位3/4に属する場合は、下位1/4に属さないものとする
　例）母数12人で、上から12点、11、10、9、8、7、6、5、4、4、2、1とした場合
　　下位1/4は本来3人であるが、4点の生徒は上位3/4でもあるので、下位1/4に該当しない
　　つまり2人(2点と1点)だけが下位1/4になります
　
自分では数式を以下のように考えました
※最初はPercentRankを使おうかと思ったものの、上手く思いつかなかったため断念しました
1　E2に入力する場合　=RANK.EQ(D:D,D:D,0)
2　1に続けてF2に入力する場合　=IF(E2<=(COUNT(E:E)-ROUNDDOWN(COUNT(E:E)/4,0),TRUE,"")
3　E2に入力する場合　{=SUM((B:B=B2)*(C:C=C2)*(D:D>D2))+1}
4　3に続けてF2に入力する場合　=IF(E2<=(COUNTIFS(C:C,C2,D:D,D2)-ROUNDDOWN(COUNTIFS(C:C,C2,D:D,D2)/4,0)),TRUE,"")
　
上記の式でも動くには動くのですが、データが大量だと並び替えなどのフィルター操作で大変重くなってしまいます
もし、より処理の軽い数式や、よりシンプルな数式があれば教示いただきたいです。よろしくお願いします

＜使用 Excel：Excel2007、使用 OS：Windows10 ＞

[[20211125143425]]『RANK関数について』（小松菜）

たぶんこの質問の回答の応用でうまくいくのではないかと思います。
（きまぐれおじさん） 2021/11/25(木) 23:41

 >もし、より処理の軽い数式や、よりシンプルな数式があれば教示いただきたいです。

 軽くなるかどうかわかりませんが…一案です。（ _ _）					
 QUARTILE関数なら、2007で使用出来ると思いますです。

 >上位3/4に属するかを求める数式（属するならTRUE					
 >条件2　上位3/4に属する場合は、下位1/4に属さないものとする

 条件の基準 例）母数12人で、上から12点、11、10、9、8、7、6、5、4、4、2、1とした場合					
 についてF列の入力式を検討してみました。

 書式：QUARTILE(配列,戻り値)					
 戻り値	戻り値を表す数値 (最小値 = 0、25% = 1、50% = 2、75% = 3、最大値 = 4)を指定します。

 因みに、点数でしても、順位で考えても結果は同じになるかと思います。					
 戻り値の指定により、<= か、>= で式を設定します。

 F2=IF($E2<=QUARTILE($E$2:$E$13,3),TRUE,"")					
 D15=QUARTILE(D2:D13,1)					
 E15=QUARTILE(E2:E13,3)

 ★サンプルテーブル

     |[A]        |[B] |[C]|[D]  |[E]  |[F]        					
 [1] |名前       |学年|組 |点数 |順位 |上位3/4判定					
 [2] |片瀬 友香  |1年 |A組|   12|    1|TRUE       					
 [3] |西脇 早織  |2年 |B組|   11|    2|TRUE       					
 [4] |米田 沙耶  |3年 |C組|   10|    3|TRUE       					
 [5] |永山 徹    |4年 |D組|    9|    4|TRUE       					
 [6] |船橋 愛梨  |5年 |E組|    8|    5|TRUE       					
 [7] |岸 優      |6年 |F組|    7|    6|TRUE       					
 [8] |池内 薫    |1年 |G組|    6|    7|TRUE       					
 [9] |久保 たかお|2年 |H組|    5|    8|TRUE       					
 [10]|小椋 洋介  |3年 |I組|    4|    9|TRUE       					
 [11]|堀越 俊二  |4年 |J組|    4|    9|TRUE       					
 [12]|上野 達士  |5年 |A組|    2|   11|           					
 [13]|斎藤 由樹  |6年 |B組|    1|   12|           					
 [14]|           |    |   |     |     |           					
 [15]|           |    |   |4.00 |9.00 |

 まだよ～く検証してないので使えなかったら... ごめんなさい (ToT)ゞ

（あみな） 2021/11/26(金) 10:26

きまぐれおじさん様、あみな様

レスありがとうございます
大変申し訳ございません、エクセルは2013と2016でした・・・

いただいた情報をもとに、まずはSUMPRODUCT関数にて試してみました
列参照にした場合は処理に時間がかかりすぎましたので、列指定にしたら幾分か改善しました
ただ、この場合は保守性に劣りそうなのでその傍らで以下を試したところハングアップもしなくなりましたので一応残します

それと、計算する中で以下の条件も考慮する必要がありましたのでそれも織り込んでいます
・点数が無い生徒は母数に含めない
・点数がゼロの生徒は母数に含める

1　=RANK.EQ(D:D,D:D,0)　（変わらず
2　=IF(D2="","",IF(E2<=(COUNTIF(D:D,"<>")-ROUNDDOWN(COUNTIF(D:D,"<>")/4,0)),"",TRUE))
3　=IF(D2="","",COUNTIFS(B:B,B2,C:C,C2,D:D,">"&D2)+1)
4　=IF(D2="","",IF(E2<=(COUNTIFS(B:B,B2,C:C,C2,D:D,"<>")-ROUNDDOWN(COUNTIFS(B:B,B2,C:C,C2,D:D,"<>")/4,0)),"",TRUE))

この数式に至ったあとであみな様からの提案をいただきました。
QUARTILEについては思いつかなかったため大変参考となりました。
手元のデータで試してみましたが、仰るとおり2については同じ結果を得られるようでしたのでご報告まで。
（sai） 2021/11/26(金) 19:48

 ＞それと、計算する中で以下の条件も考慮する必要がありましたのでそれも織り込んでいます
 ＞・点数が無い生徒は母数に含めない
 ＞・点数がゼロの生徒は母数に含める

 織り込んでいますとは？上位3/4に属するかを求める数式は、
 ご自身で自己解決しました...と言うことでしょうか？

 ＞上記の式でも動くには動くのですが、データが大量だと並び替えなどの
 ＞フィルター操作で大変重くなってしまいます

 動作が重いと言うのは、具体的に時間で言うとどれくらいの所要時間なのでしょうか？

 以前に他の方で、遅いからど～にかしてといった方と...お話しする機会がありましたが
 その方は、3万行位を抽出するのに、40～50秒位かかってしまってましたが。。
 同じように3万行の疑似データを作ってテストしたら、私の環境でも15秒前後かかりました。

 今回の、（sai）さんの場合はレコード数は…3000行程なのでそんなには
 かからないと思いますが…レイアウトが少し違うのと、抽出方法が違いますが
 私がテストした下記は内容と結果です。

 全ての抽出を、フィルターを使用せず関数だけでしています。

 ■Sheet1.元データから、■Sheet2.生徒全体順位を昇順抽出させた後に
 Sheet3.にて学年別抽出、Sheet4.にて組別で抽出した内容と時間です。

 ・Sheet3と4は、入力規制を使用して、選択を切り替えると欲しい
 学年別データと、各学年における組のデータを抽出させます。

 ・尚、下記のダウンロード画像は、サンプル画像なのでレコード数は30行に
 してあります。また背景色は重複の確認用としてあります。
 30行のサンプルなので、学年は1年～3年までとし。組は、A,B,C組だけにしてあります。

 ★疑似データは（生徒数3,000人を）を作ってテストした結果です。

 ■Sheet3.学年別抽出　約7～8秒程度
 ■Sheet4.組抽出　　　約3～4秒程度

 この程度なら一般的かなと思うのですが...参考になるかしら？^^;
 PCのスペックは、決してよくありませんww

 私が、「 ファイルなう 」と言うサイトへ… アップロードしたファイルです。 ( 下記4件のサンプル画像 )                                                      
  Windowsフォトビューアーで画像だけが閲覧可能です。

 ■Sheet1.元データ.JPG 94 KB
 ダウンロードURL:
 https://d.kuku.lu/2c5798af9

 ■Sheet2.生徒全体順位を昇順抽出.JPG 101 KB
 ダウンロードURL:
 https://d.kuku.lu/c855a10c4

 ■Sheet3.学年別抽出.JPG 90 KB
 ダウンロードURL:
 https://d.kuku.lu/f4cf9ffad

 ■Sheet4.組抽出.JPG 82 KB
 ダウンロードURL:
 https://d.kuku.lu/90fa6a1c3

 「 ファイルなう 」運営者情報         
  くくさま @kukusama                 
  https://twitter.com/kukusama

（あみな） 2021/11/27(土) 15:09

コメント返信：

[ 一覧(最新更新順) ]

[[20211125224853]] 『グループ内における上位群抽出』（sai） ページの最後に飛ぶ

[[20211125224853]]　『グループ内における上位群抽出』（sai）　ページの最後に飛ぶ