حذف چند عدد کوچکتر از یک لیست اعداد در اکسل 2010

Question

سلام دوستان
من یه معلمم، می خوام نمره مستمر دانش آموزامو با اکسل 2010 حساب کنم. اما محاسبه مستمر یه میانگین گیری ساده نیست. می خوام به تعداد فعالیتهای مفید هر دانش آموز، نمره های خرابشو حذف کنم. مثلا اگه دانش آموزی 3 تا فعالیت یا تحقیق داشته، سه نمره از نمره هاشو حذف کنم، بعد میانگین بگیرم.
تونستم فرمولشو به دست بیارم:
SUM(A2:R2)-SUM(SMALL(A2:R2,{1,2,3})))/(COUNT(A2:R2)-3)
این فرمول برا دانش آموزیه که سه تا نمره بدش رو می خوام حذف کنم.
ولی مشکلم اینجاست که این فرمول رو برای هر دانش آموز باید تغییر بدم و اکسل این امکان رو نداره که خودش تعداد عددهایی که باید حذف بشه رو از ستون تعداد فعالیتها بگیره.
از "رایانه کمک" هم پرسیدم کسی بلد نبود.
لطفا راهنماییم کنین.

Comments

خب الان روش شما برای حذف اعداد چی هست؟یعنی چه اعدادی رو دوست دارین حذف کنین؟ چه معیاری براش دارین؟

---

اعداد کمتر رو باید حذف کنم، یعنی دانش‌آموزی که سه تا فعالیت انجام داده باید سه نمره کمش رو حذف کنم.
مثلا اگه نمراتش اینا باشه: 12، 15، 18، 19، 10، 8، 16، 13
باید 8 و 10 و 12 حذف بشه.

Answer 1

فرض کنیم ستون A تا R همون طور که گفتین ستون نمره‌ها باشه

A  |  B  |  C  | ... |  R
10 | 13  | ... | ... |  10

در ستون S تعداد نمره‌هایی رو که قراره حذف بشه قرار می‌دیم

A  |  B  |  C  | ... |  R  |  S  |
10 | 13  | ... | ... |  10 |  3  |

در ستون T جدول n امین می‌نیمم رو حساب می‌کنیم و با ">" الحاق می‌کنیم

|  T  |
|  =">"&SMALL(A1:R1, S1)  |

این متنی شرطی رو تولید می‌کنه که می‌گه «بزرگ‌تر از n امین می‌نیمم»
حالا در ستون U می‌تونیم مجموع یا میانگین رو بر حسب شرط نوشته شده محاسبه کنیم

|  U  |
| =SUMIFS(A1:R1,A1:R1,T1)/(COUNT(A1:R1)-S1) |

Comments

این فرمول یه اشکال بزرگ داشت که من اول متوجه نشدم، نزدیک بود حق دانش آموزام ضایع بشه، مشکلش اینه که نمرات یه دانش آموزی ایناست:
12-10-15-16-20-17-12-8-12-13-14-12
من می خوام فقط 3 نمره کمتر رو حذف کنم یعنی 8 و 10 و 12 ولی با فرمول بالا تمامی 12ها حذف میشن.

---

درسته. در یک حالت خاص که مرز بین نمره‌های بالا و پایین تکراری باشه این مشکل پیش میاد. در اینجا این مرز نمره‌ی ۱۲ هست که ۳ بار تکرار شده. می‌تونین اون شرط رو به صورت زیر بسازین

=">"&SMALL(A1:R1, S1)

در این صورت اگر در مرز گفته شده عدد تکراری داشته باشین حذف نمی‌شه. یعنی ممکنه به جای سه نمره، دو نمره یا یک نمره یا حتی هیچ نمره‌ای حذف نشه.
فکر می‌کنم ببینم راه‌حلی برای این مشکل خاص هست یا نه