محاسبه میانگین وزنی در اکسل – از صفر تا صد + مثال

«میانگین وزنی» یا «میانگین موزون» (Weighted Average) روشی برای محاسبه میانگین اعداد با توجه به وزن یا فراوانی آن‌ها در محاسبات آماری و معدل‌گیری است. این نوع میانگین را زمانی استفاده می‌کنیم که مقدار هر داده اهمیت متفاوتی در نتیجه محاسبه داشته باشد. به‌عنوان مثال، اگر نمره پایان ترم یک دانشجو ۷۰ درصد و نمره پروژه او ۳۰ درصد در نمره نهایی تاثیر داشته باشد، استفاده از میانگین معمولی برای محاسبه معدل منطقی نیست و لازم است از میانگین وزنی استفاده کنیم. برای محاسبه میانگین وزنی در اکسل توابع مختلفی به کار می‌رود که در این مطلب از مجله فرادرس روش کار با آن‌ها را همراه مثال یاد می‌گیریم.

توابع مورد استفاده برای محاسبه میانگین وزنی در اکسل

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

1. ترکیب چند تابع SUM
2. ترکیب تابع SUM و تابع SUMPRODUCT

فرمول‌نویسی با هر یک از این توابع بسیار ساده است و با آشنایی در نحوه صحیح نوشتن فرمول، می‌توانیم میانگین وزنی را به‌دست آوریم. با این حال از آنجا که نکات ظریفی در فرمول‌نویسی اکسل به خصوص در تعداد داده‌های زیاد وجود دارد، برای جلوگیری از ایجاد خطای محاسباتی لازم است مهارت کافی را در استفاده از توابع اکسل به‌دست آوریم. بنابراین پیشنهاد می‌کنیم در کنار مطالعه ادامه بحث، فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل در فرادرس را با تمرکز بر درس دوم و سوم آن مشاهده کنید.

حال در ادامه این آموزش، نحوه استفاده از توابع اکسل برای محاسبه میانگین وزنی را همراه مثال یاد می‌گیریم.

محاسبه میانگین وزنی در اکسل با تابع SUM

تابع SUM یکی از ساده‌ترین روش‌ها برای محاسبه میانگین وزنی در اکسل است. در این روش کافی است مجموع حاصل‌ضرب هر عدد در وزن یا فراوانی آن را بر مجموع وزن‌ها تقسیم کنیم. فرمول کلی محاسبه به شکل زیر است.

=SUM(values_range* weights_range) / SUM(weights_range)

در این فرمول تعریف هر یک از آرگومان‌ها به شرح زیر است.

• values_range : محدوده اعداد مورد نظر برای محاسبه میانگین در جدول
• weights_range : محدوده وزن یا فراوانی در جدول

هر چند این فرمول بسیار ساده است، اما برای یادگیری ترفندهای تکمیلی کار با این تابع پیشنهاد می‌کنیم فیلم آموزش رایگان جمع در اکسل در فرادرس را مشاهده کنید.

مثال

فرض می‌کنیم نمرات یک دانشجو و میزان تاثیر آن در معدل او به شرح جدول زیر است.

برای محاسبه میانگین وزنی با تابع SUM مطابق این اعداد فرمول زیر را می‌نویسیم.

=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6,)/SUM(C2:C6)

در نهایت میانگین وزنی به‌راحتی محاسبه می‌شود.

همان‌طور که مشخص است، محاسبه میانگین اعداد به روش معمولی با تابع AVERAGE به‌خاطر در نظر نگرفتن ضریب تاثیر اعداد، فاصله زیادی با میانگین وزنی دارد.

محاسبه میانگین وزنی در اکسل با تابع SUMPRODUCT

در محاسبه میانگین وزنی با تابع SUMPRODUCT فرمول کلی زیر را استفاده می‌کنیم.

=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)

در این فرمول مانند تابع SUM هر یک از آرگومان‌ها به شکل زیر تعریف می‌شوند.

• values_range : محدوده اعداد مورد نظر برای محاسبه میانگین در جدول
• weights_range : محدوده وزن یا فراوانی در جدول

تابع SUMPRODUCT کاربردهای دیگری در نیز دارد که برای آشنایی بیشتر پیشنهاد می‌کنیم فیلم آموزش رایگان تابع SUMPRODUCT در اکسل در فرادرس را مشاهده کنید.

فیلم آموزش تابع SUMPRODUCT در اکسل برای انجام محاسبات + کاربرد و مثال‌ (رایگان) در فرادرس

کلیک کنید

مثال

این بار در مثال فرضی قبلی میانگین وزنی را با فرمول =SUMPRODUCT(B2:B6, C2:C6)/SUM(C2:C6) محاسبه می‌کنیم. نتیجه نهایی تعیین معدل به شکل زیر است.

همان‌طور که مشخص است، میانگین محاسبه شده در این حالت باز هم دقیق‌تر از میانگین معمولی با تابع AVERAGE و برابر روش اول است.

مسیر تکمیل مهارت فرمول‌نویسی محاسبه میانگین در فرادرس

توابع مربوط به میانگین یکی از ابزارهای محاسباتی در اکسل هستند که برای تحلیل‌های مالی و معدل‌گیری بسیار کاربرد دارند. مواردی که تا این بخش از مطلب بیان کردیم، مثال‌های ساده‌ای هستند که برای کارهای مقدماتی مورد استفاده قرار می‌گیرند. اما برای محاسبات پیشرفته آماری نیاز به یادگیری مباحث تکمیلی داریم که فیلم‌های آموزشی فرادرس مسیر مناسبی در این خصوص هستند.

پیشنهاد اول مشاهده فیلم‌های منتخب آموزشی زیر است.

• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش محاسبات آماری در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس

پیشنهاد دوم انتخاب موارد مطابق نیاز از دو مجموعه فیلم آموزش زیر است.

• مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس
• مجموعه فیلم آموزش اکسل کاربردی در فرادرس

نکات مهم در محاسبه میانگین وزنی با توابع اکسل

میانگین وزنی ابزاری قدرتمند برای محاسبه معدل، قیمت تمام شده کالاها و به‌ویژه تحلیل‌های آماری است.در محاسبه میانگین وزنی با تابع SUM و تابع SUMPRODUCT در اکسل لازم است به نکات زیر توجه کنیم.

فیلم آموزش محاسبات آماری در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

• مجموع مقادیر وزنی در جدول اکسل لازم نیست همیشه برابر عدد ۱۰۰ باشد.
• مقادیر وزنی می‌توانند با توجه به میزان اهمیت یا فراوانی در قالب عدد صحیح مثبت هم نوشته شوند و نیازی به تبدیل آن‌ها به درصد نداریم. به‌عنوان مثال اگر تکالیف یک دانش‌آموز مانند تصویر زیر باشد، به‌راحتی با استفاده از ترکیب دو تابع SUM و تابع SUMPRODUCT میانگین وزنی را می‌توانیم محاسبه کنیم.

• اگر وزن یک داده در جدول بسیار بزرگ‌تر از سایر اعداد باشد، میانگین وزنی به سمت داده‌ای که وزن بیشتری دارد، نزدیک است. یعنی تاثیر این داده در نتیجه نهایی بیشتر خواهد بود.
• در انتخاب بین محاسبه میانگین ساده با تابع AVERAGE یا میانگین وزنی لازم است به اهمیت داده‌ها توجه کنیم. اگر داده‌های جدول فراوانی، تکرار یا اهمیت متفاوتی دارند، برای محاسبه دقیق از میانگین وزنی استفاده می‌کنیم. در غیر این صورت لازم است با توجه به نیاز یکی از توابع محاسباتی میانگین در اکسل را استفاده کنیم.

در مطلب زیر از مجله فرادرس ده روش مختلف برای محاسبه میانگین با اکسل را توضیح داده‌ایم.

جمع‌بندی پایانی

میانگین وزنی در محاسبه معدل برای اعدادی که وزن و تاثیر متفاوتی در نتیجه نهایی دارند، استفاده می‌شود. در اکسل ترکیب دو تابع SUM و تابع SUMPRODUCT برای محاسبه میانگین وزنی کاربرد دارد. در این مطلب از مجله فرادرس نحوه محاسبه میانگین وزنی در اکسل با هر دو تابع را همراه مثال‌هایی ساده یاد گرفتیم. با این حال برای تکمیل مهارت به‌خصوص در محاسبات پیچیده آماری علاقه‌مندان می‌توانند فیلم آموزش توابع و فرمول‌نویسی در اکسل در فرادرس را مشاهده کنند.