エクセル | の学校 |
8.一覧 | 9.HOME |
1.Top | 2.Last |
『出席率 第1位と第2の平均値を出す』(・・・9)
困っています。よろしくお願いいたします。
会員数|出席|第1回出席率 143 |39 |27% 会員数|出席|第2回出席率 143 |23 |16% 会員数|出席|第3回出席率 144 |25 |17% 会員数|出席|第4回出席率 147 |25 |17% 会員数|出席|1位2位出席率
上記の数値が1行目にタイトル、2行目に数値が入りA列〜o列まで横並びになっています 1〜4回出席率のうち上位2位の平均を出したいです。 平均の平均を出すのではなく。上位2位を足した会員数、上位2位を足した 出席数で出席率を出したいです。 M2〜O2に入る式を教えてください。
< 使用 Excel:Microsoft365、使用 OS:Windows11 >
__A___ __B_ __C________ __D___ __E_ __F________ __G___ __H_ __I________ __J___ __K_ __L________ __M___ __N_ __O_________ 1 会員数 出席 第1回出席率 会員数 出席 第2回出席率 会員数 出席 第3回出席率 会員数 出席 第4回出席率 会員数 出席 1位2位出席率 2 143 39 27% 143 23 16% 144 25 17% 147 25 17% 291 64 22%
[M2] =SUM(take(SORT(FILTER(A2:L2,A1:L1="会員数",0),,-1,TRUE),,2)) [N2] =SUM(take(SORT(FILTER(A2:L2,A1:L1="出席",0),,-1,TRUE),,2)) [O2] =N2/M2
(まる2021) 2024/04/23(火) 05:46:30
まる2021さま、さっそくありがとうございます。M2の合計がどうしても正解の数値プラス1になってしまうのですが。。 (・・・9) (・・・9) 2024/04/23(火) 09:53:47
これは出席率の上位2回の会員数と出席数を合計しそれで計算したい、なのか 会員数の上位2回の合計と出席数の上位2回の合計で計算したい、のどちらだろうか?
前者の場合、例だと第何回と第何回から計算することになるのだろうか? つまり同率が複数ある場合の順位の決め方は? (ねむねむ) 2024/04/23(火) 10:35:19
自分が質問文から解釈した内容は以下の通りです。 解釈が、間違っているなら、再説明を、お願いします。
会員数を大きい順に並べると {147,144,143,143} 上位2つの合計は「147+144」=「291」 (まる2021) 2024/04/23(火) 10:42:44
説明不足でした。全部で4回開催のうち出席率の1位、2位の平均を出したいのです。 なので上記例だとc列とL列が出席率1位と2位です。 よってm2には、143+147=290、n2は、39+25が計算される関数を知りたいです。
(・・・9) 2024/04/23(火) 11:09:04
理解しました。只、質問例だと
第3回出席率:25/144=17.36% 第4回出席率:25/147=17.01%
で上記例だとC列とI列が出席率1位と2位でになるのでは? 又、ねむねむさんも聞いていますが、同率が複数ある場合の順位の考え方についても説明が必要です。 (まる2021) 2024/04/23(火) 11:32:13
まる様上記例の件大変失礼いたしました。私の計算ミスでした。 ねむねむ様にもご質問いただきました件、参加率同率の場合、参加者人数が 多い方を優先させたいと思います。 お手数をおかけしますがよろしくお願いいたします。
(・・・9) 2024/04/23(火) 15:01:24
第1回出席率と第3回出席率が上位2つで合計
__A___ __B_ __C________ __D___ __E_ __F________ __G___ __H_ __I________ __J___ __K_ __L________ __M___ __N_ __O_________ 1 会員数 出席 第1回出席率 会員数 出席 第2回出席率 会員数 出席 第3回出席率 会員数 出席 第4回出席率 会員数 出席 1位2位出席率 2 143 39 27.27% 143 23 16.08% 144 25 17.36% 147 25 17.01% 287 64 22.30%
[M2] =SUM(CHOOSECOLS(TAKE(SORT(SORT(VSTACK(A2:C2,D2:F2,G2:I2,J2:L2),1,-1),3,-1),2),1)) [N2] =SUM(CHOOSECOLS(TAKE(SORT(SORT(VSTACK(A2:C2,D2:F2,G2:I2,J2:L2),2,-1),3,-1),2),2)) [O2] =N2/M2
(まる2021) 2024/04/23(火) 15:24:26
まる様、ありがとうございました。今後回数が11回まで増える場合もこの式のままで大丈夫でしょうか? (今後の回数お伝えしていなくてごめんなさい。) (・・・9) 2024/04/23(火) 15:33:41
指定範囲を増やすのはできますが後ろの、),1,-1),3,-1),2),1))など、このままでもよろしいのかの確認でした。関数が分かっていないのにすいません。 (・・・9) 2024/04/23(火) 15:36:58
VSTACK(A2:C2,D2:F2,G2:I2,J2:L2)の中を増やしていけば、 後ろの、),1,-1),3,-1),2),1))などは、そのままでOKです。 SORT関数について、調べてみて下さい。
表のレイアウトが変わってもいいなら↓のようにしてテーブルにしておけば 開催回数が増えても、数式は変更なしでいけますが....それは無理ですか?
__A__ __B___ __C_ __D___ 1 開催 会員数 出席 出席率 2 第1回 143 39 27.27% 3 第2回 143 23 16.08% 4 第3回 144 25 17.36% 5 第4回 147 25 17.01%
(まる2021) 2024/04/23(火) 16:05:08
ありがとうございます。 予め、11回分まで、選択範囲を増やしたところm2とn2の結果が0になってしまったので質問でした。空白だと0になってしまいますかね。 (・・・9) 2024/04/23(火) 16:14:08
if関数を駆使して、まだ未開催の箇所を0に設定したところ数字が出てまいりました。まる2021さま ねむねむ様長い間、ありがとうございました。 (・・・9) 2024/04/23(火) 16:22:58
こんなのも。
A B C D E F G H I J K L M N O 1 会員 出席 1回率 会員 出席 2回率 会員 出席 3回率 会員 出席 4回率 会員 出席 1位2位率 2 143 39 27.27% 143 23 16.08% 145 29 20.00% 150 30 20.00% 293 69 23.55%
N2:O2は空欄 M2 =LET(a,BYCOL(TAKE(SORT(WRAPROWS(A2:L2,3),{3,2},-1),2,2),SUM),HSTACK(a,PRODUCT(a^{-1,1}))) (んなっと) 2024/04/23(火) 16:48:04
横に並べた表より、こういう表のほうが見やすいし計算もしやすくないですか?
A B C D E F G H I 1 開催 会員数 出席数 出席率 ランク rank2以上 会員数 出席 出席率 2 第1回 143 39 27.27% 1 287 64 22.30% 3 第2回 143 23 16.08% 4 4 第3回 144 25 17.36% 2 5 第4回 147 25 17.01% 3 6 以下追記できます。
D2: =C2/B2 E2: =RANK(D2,$D$2:$D$10) G2: =SUMIF($E$2:$E$10,"<=2",B2:B10) H2: =SUMIF($E$2:$E$10,"<=2",C2:C10) I2: =H2/G2 (Excel初心者) 2024/04/23(火) 20:56:09
]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.