در اکسل میانگین سلولهای مختلف فقط با استفاده از تابع AVERAGE و بسیار آسان محاسبه میشه. اما اگه بعضی از مقدارها “وزن” بیشتری نسبت به دیگران داشته باشن باید میانگین وزنی رو محاسبه کنید.
گر چه اکسل تابع مخصوصی برای محسابه میانگین وزنی نداره، اما دارای یک تابع هست که این کار رو براتون انجام میده یعنی SUMPRODUCT. حتی اگه قبلا از SUMPRODUCT استفاده نکردین در پایان این مقاله روش استفاده از اون رو یاد میگیرید. روشی که استفاده میکنیم در تمام نسخههای اکسل و سایر برنامه های کاربردی مثل Google Sheets کار میکنه.
تنظیم صفحه گسترده
برای محاسبه میانگین وزنی حداقل به دو ستون نیاز دارید. ستون اول (در مثال ستون b) شامل نمرات هر درس یا آزمون و ستون دوم (ستون c) شامل وزنها هست. وزن بیشتر باعث میشه که درس یا آزمون تاثیر بیشتری بر نمره نهایی داشته باشه.
حتی میتونید از وزن به عنوان درصد نمره نهایی استفاده کنید. در این حالت ممکن هست مقدار وزن به بیشتر از ۱۰۰ درصد هم برسه، اما مشکلی نیست چون فرمول بدون توجه به وزن کار میکنه.
وارد کردن فرمول
اکنون که صفحه گسترده تنظیمشده فرمول را به سلول B10 ( یا هر سلول خالی ) اضافه میکنیم. فرمول را با تایپ کردن علامت برابر (=) شروع کنید.
اولین بخش از فرمول ما تابع SUMPRODUCT هست. چون مقدار در پرانتز قرار میگیره یک پرانتز باز کنید:
)SUMPRODUCT =
سپس مقادیر رو به این فرمول اضافه میکنیم. SUMPRODUCT میتونه چندین مقدار داشته باشه، اما معمولاً دو مقدار دارد. در مثال ما اولین مقدار محدوده سلولی B2 : B9 (سلولهایی که شامل نمرات هستند) خواهد بود:
SUMPRODUCT( B2 : B9 =
دومین مقدار محدوده سلول C2 : C9 هست ( سلولهایی که دارای وزن هستند). برای جدا کردن دو مقدار باید از کاما استفاده کنید. در آخر یک پرانتز بسته قرار بدین:
( SUMPRODUCT( B2 : B9 , C2 : C9 =
حالا قسمت دوم این فرمول رو اضافه میکنیم. این بخش SUMPRODUCT رو بر مجموع وزنها تقسیم میکنه.
با تایپ یک / (slash) تقسیم رو شروع کرده و بعد تابع SUM رو تایپ کنید:
) SUMPRODUCT(B2:B9, C2:C9)/SUM =
حالا فقط به یک مقدار برای تابع SUM نیاز داریم: محدوده سلول C9: C2. به خاطر داشته باشید که بعد از وارد کردن مقدار پرانتز رو ببندید:
(SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9 =
هنگامی که Enter رو فشار بدین اکسل میانگین وزنی رو محاسبه میکنه. در مثال ما نمره نهایی ۸۳.۶ هست.
این تابع چگونه کار میکند؟
بیاید به قسمتهای مختلف این فرمول نگاه کنیم. از تابع SUMPRODUCT شروع میکنیم. SUMPRODUCT مقدار هر درس رو در وزنش ضرب میکنه. بعد همه مقادیر بدست آمده رو با هم جمع میکنه. مثالا برای درس Test در جدول عدد ۸۵ رو در ۲۳ و برای درس Assignment1 عدد ۸۵ رو در ۵ ضرب میکنه که در آخر مقادیر این ضربها با هم جمع میشن.
اگه در این فکر هستید که چرا مقادیر مورد نیاز باید اول ضرب بشن، به این موضوع توجه کنید که مقادیر با وزن بالاتر بیشتر شمرده میشن و تاثیر بیشتری دارند. برای مثال Assignment 2 برابر ۵ بار اما آزمون نهایی ۴۵ بار شمرده میشه. به همین دلیل است که آزمون نهایی تاثیر بیشتری روی نمره نهایی داره. در واقع یک میانگین “عادی” هر مقدار را یکبار محاسبه میکنه، بنابراین هر کدام دارای وزن یکسان هستند. اگه تابع SUMPRODUCT رو دقیقتر نگاه کنید میبینید که عبارت زیر در حال محاسبه است:
(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9) =
خوشبختانه ما مجبور نیستیم این فرمول طولانی رو دستی بنویسیم چون SUMPRODUCT این کار رو به صورت خودکار برامون انجام میده.
تابع SUMPRODUC به تنهایی یک عدد بزرگ مثل ۱۰۴۵۰ به ما میده که خیلی معقول نیست. در این جا بخش دوم فرمول یعنی (sum/(c2:c9 تاثیر گذاشته و مقدار نرمالتری بهمون میده یعنی ۸۳.۶
بخش دوم فرمول واقعا ً مفید هست چون اجازه میده فرمول به طور خودکار محاسبات خودش رو تصحیح کنه. به یاد داشته باشید که وزنها نیازی به اضافه شدن بصورت درصد ندارند. برای مثال اگه یک یا چند وزن رو افزایش بدیم بخش دوم فرمول به سادگی بر تعداد بیشتری تقسیم میشه و در نهایت پاسخ صحیح رو بر میگردونه. حتی میتوانیم وزنها رو بسیار کوچکتر کرده و مقادیر خیلی کمتری مثل ۰.۵, ۲.۵ و ۳.۰ قرار بدهیم.