[[20250527161256]]　『上位と下位10％を除いた合計を出したい』（明日は雨）　ページの最後に飛ぶ

『上位と下位10％を除いた合計を出したい』（明日は雨）

　　A　　　　B
1　鈴木　　15000
2　田中　　　125
3　佐藤　　　　8
4　本田　　　658
5　池田　　　741
6　木村　　　700
7　永井　　12345
8　坂田　　　121
9　上田　　　666
…　…　　　　…
1000　東　　　15

上記のように1000件のリストがあります。
そのうち上位と下位10%を除いたB列900件の合計を出したいです。

条件として、
10%（100件）の内訳　B列の上位50件、下位50件は除く
残り900件の合計を出したい

以上の条件で関数で算出することは可能でしょうか。
目的は異常値を省いた中間の平均値を知りたい為です。

お力添えをいただきますと助かります。
何卒よろしくお願い致します。

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

 =SUM(DROP(DROP(SORT(A1:A1000),50),-50))
（はてな） 2025/05/27(火) 16:56:30

（はてな）様
ありがとうございます。
ご返信いただいた関数で算出できました。

自動的にリスト件数の10%（上位・下位）を除いた合計を自動的に算出できるのでしょうか。
今回いただいた関数に「50」を含めない関数を教えていただけると助かります。
すみませんがよろしくお願い致します。
（明日は雨） 2025/05/27(火) 17:22:25

 はてなさんの数式をアレンジして汎用的に。

 =LET(
 範囲,B1:B1000,
 パーセント,10,
 除外件数,INT(ROWS(TOCOL(範囲,1))*パーセント*0.01),
 SUM(DROP(DROP(SORT(TOCOL(範囲,1)),除外件数/2),-除外件数/2))
 )

 範囲とパーセントを変えるだけで汎用的にいけるはずなんですが・・・。

（名無し） 2025/05/27(火) 17:38:06

 >目的は異常値を省いた中間の平均値を知りたい為です。
 統計の分野では、標準偏差を使った方法がよく使われます。

 Excelでの異常値検出を簡単に行う方法
https://newstower.jp/10253/
（まる2021） 2025/05/27(火) 18:03:09

（名無し）様　お返事遅くなり申し訳ございませんでした。
（まる2021）様　ご参考に送ってくださりありがとうございます。

結果、単純ではありますが、一度に計算式を組むのではなく
一旦　列の件数を（COUNTA)で出し（1000）、ROUNDDOWNで10%件数を出しました。

B1セルに、=COUNTA(B1:B1000)　列の件数
C1セルに、=ROUNDDOWN(N1*0.1,0)　10%（小数点以下切り捨て）を設定

D1セルに　=SUM(DROP(DROP(SORT(B1:B1000),C1),-C2))

ここまでは良かったのですが、厄介なことに（B1:B1000）の範囲に「空白セル」があります。
そのため空白までも列の数に含まれてしまうため、間違った答えが出てきます。

例えば…　B列に2件空白があった場合

　　A　　　　B　　　→　　　C列（B列を降順で並び替え）
1　鈴木　　15000　　　　　　1000　上位1位
2　田中　　　125　　　　　　900
3　佐藤　　　空白　　　　　　800　
4　本田　　　658　　　　　　 700
5　池田　　　741　　　　　　 600
6　木村　　　700　　　　　　 500
7　永井　　12345　　　　　　 400
8　坂田　　　空白　　　　　 300　　下位1位
9　上田　　　678　　　　　　空白
10 中田　　　600　　　　　　空白　　←下位1位とみなされてしまう

本来求めたい数は8件のうち10%（上位1000・下位300それぞれ1件←今回は切り上げました）を除いた数
C2からC8の合計「3,900」になるはずが、
空白から下位と判断され「4,200」が算出されます。

空白を含まないように　こちら下記の計算式に追加できるのか教えていただけると幸いです。=SUM(DROP(DROP(SORT(B1:B1000),C1),-C2))

長くなりまして申し訳ありません。

（明日は雨） 2025/05/29(木) 17:00:05

 =LAMBDA(rng,tr,
    LET(
      list,  SORT(FILTER(rng,rng<>"")),
      n,     INT(COUNT(list)*tr/2),
      dlist, DROP(DROP(list,n),-n),
      AVERAGE(dlist)
    )
 )(B1:.B1000,10%)
 とか
（´・ω・`） 2025/05/29(木) 17:53:27

 先に私が提案した数式にも入ってますが、TOCOL関数を挟んでみてはいかがですか？

 =SUM(DROP(DROP(SORT(TOCOL(B1:B1000,1)),C1),-C2))

 ただし、本当の空白じゃないとうまくいきません。
 空文字列（計算式の結果での""とか）は不可です。

（名無し） 2025/05/30(金) 09:43:43

（´・ω・`）様　ありがとうございます。お返事が遅くなりました。
高度な組み方で追いつけず…申し訳ありません。

（名無し）様　ありがとうございます。
TOCOL関数初めて使いました。「空白は無視する」の1　ですね。
こちらの関数で設定したところ、上手く答えが出るようになりました。
こちらを使わせていただきたいと思います。

皆様、ご協力いただきありがとうございました。
（明日は雨） 2025/05/30(金) 13:41:08

コメント返信：

[ 一覧(最新更新順) ]

[[20250527161256]] 『上位と下位10％を除いた合計を出したい』（明日は雨） ページの最後に飛ぶ

[[20250527161256]]　『上位と下位10％を除いた合計を出したい』（明日は雨）　ページの最後に飛ぶ