میانگین وزنی اعداد با در نظر گرفتن اهمیت دادهها محاسبه میشود. دادهای که مهمتر است، با ضریب وزنی بیشتر در محاسبهی میانگین لحاظ میشود و دادهای که اهمیت کمی دارد، با ضریب کوچکتر در نظر گرفته میشود.
برای محاسبه کردن میانگین وزنی یا Weighted Average در نرمافزار اکسل میتوانید از دو تابع SUMPRODUCT و SUM استفاده کنید و یک فرمول بسیار ساده لازم است.
در این مقاله به روش محاسبهی میانگین وزنی در اکسل مجموعهی آفیس مایکروسافت میپردازیم.
میانگین وزنی یا Weighted Average چیست و چطور محاسبه میشود؟
در حالت عادی برای محاسبهی میانگین، همهی اعداد را جمع کرده و حاصلجمع را بر تعداد تقسیم میکنیم. در این حالت اهمیت هر داده مثل دیگر دادهها است و در واقع ضریب وزنی هر عدد، ۱ است.
در محاسبهی میانگین وزنی، برای اینکه برخی دادهها مهمتر و برخی دیگر اهمیت کمتر داشته باشند، از ضریب وزنی استفاده میشود. به این صورت که ابتدا هر داده در ضریب وزنی مربوط به آن ضرب میشود و سپس حاصلجمع محاسبه میشود. در نهایت حاصلجمع بر حاصلجمع ضرایب وزنی تقسیم میشود.
به عنوان مثال در آزمونهای تستی ۴ گزینهای، اهمیت هر پاسخ درست، ۳ واحد است و هر پاسخ غلط ۱ واحد. بنابراین ضریب وزنی ۳ و ۱ لحاظ میشود.
در محاسبهی نمره، امتیاز، برآیند و امور دیگر، همواره اگر فاکتورها اهمیت متفاوتی داشته باشند، میبایست به جای محاسبهی میانگین معمولی، از میانگین وزنی استفاده کرد تا تعادلی بین فاکتورها به وجود آید.
محاسبهی میانگین وزنی در اکسل
برای محاسبهی میانگین وزنی در Excel به دو تابع ساده نیاز داریم: تابع محاسبهی حاصلجمع اعداد که SUM است و تابع محاسبهی حاصلجمع حاصلضرب اعداد که SUMPRODUCT نام دارد.
مراحل کار را با یک مثال ساده توضیح میدهیم: فرض کنید ۶ آزمون برگزار شده و هر یک از آزمونها ۵ درصد نمرهی نهایی را تشکیل میدهد و ۲ آزمون دیگر نیز برگزار شده که اهمیت بیشتری دارند و ۲۰ درصد نمرهی کلی را تشکیل میدهد و در نهایت آزمون آخر ۳۰ درصد کل، ارزش دارد.
در ستون Item نام آزمونها و در ستون Score نمرات درصدی درج شده و در آخرین ستون نیز ضریب وزنی بر حسب درصد قرار گرفته و نام این ستون Weight است.
محاسبه حاصلجمع حاصلضرب اعداد دوتایی
برای اینکه اعداد دوتایی را ابتدا در هم ضرب کنیم و سپس مقادیر به دست آمده را جمع کنیم، از تابع SUMPRODUCT استفاده میشود. ابتدا سلولی که میخواهید خروجی تابع در آن قرار بگیرد را انتخاب کنید. سپس برای دسترسی به این تابع، در تب Formulas بالای صفحه و بخش Function Library روی Math & Trig کلیک کنید و سپس از منوی کرکرهای، SUMPRODUCT را انتخاب کنید.
اکنون در فیلد Array1 کلیک کنید و سپس با روش کلیک و درگ، سلولهای حاوی نمرات را انتخاب کنید.
میتوانید از روش تایپ کردن نیز استفاده کنید و سلول شروع و پایان را با دونقطهی بینشان تایپ کنید.
در مورد Array2 که در حقیقت دومین سری اعداد است، نیز با روش مشابه عمل کنید و این بار ضرایب را انتخاب کنید.
در نهایت روی OK کلیک کنید.
به این ترتیب هر عدد از ستون C در عدد کنار آن از ستون D ضرب میشود و سپس حاصلجمع محاسبه میشود.
محاسبهی جمع اعداد با SUM
گام بعدی استفاده از تابع جمع زدن یا SUM است. در سلول موردنظر کلیک کنید و سپس از بخش فرمولها و منوی کرکرهای Math & Trig، این تابع را انتخاب کنید.
تابع SUM نیز دو آرگومان دارد که البته هر یک میتواند یک عدد یا آرایهای از اعداد باشد.
در فیلد Number1 کلیک کنید و سپس سلولهای ستون D که حاوی ضرایب هستند را با کلیک و درگ انتخاب کنید.
سپس روی OK کلیک کنید.
جمع ضرایب محاسبه میشود.
میانگین وزنی اعداد
اکنون به یک فرمول ساده نیاز داریم که مقدار سلول حاصلجمع حاصلضربها را بر حاصلجمع ضرایب تقسیم کند. در مثال ما از فرمول زیر استفاده میشود:
=D13/D14
و به صورت مشابه میتوانید در صورت و مخرج، تابعها را به صورت کامل تایپ کنید و از فرمول زیر استفاده کنید:
=SUMPRODUCT(C3:C11,D3:D11)/SUM(D3:D11)
فرمولهای طولانی را میتوانید از طریق نوار فرمولها در بالای صفحه نیز ویرایش کنید.
با فشار دادن کلید Enter، میانگین وزنی محاسبه خواهد شد.
توجه کنید که در این مثال ضرایب وزنی بر حسب درصد ذکر شده اما اگر به جای عدد ۲۰ درصد، از 0.2 استفاده میشد نیز نتیجه یکسان بود.
howtogeekسیارهی آیتی
“سلام و خداقوت.
یکسری سلول داریم میانگین شون رو میخام حساب کنم. یعنی معدلی که مد نظرم هست. ولی با اینشرایط :
۱. مقادیر سلول ها نمره کمی و هم نمره کیفی توش هست. یعنی هم عدد هم حروف
۲. برخی سلول ها ممکنه خالی بگذاریم و خب تو میانگین نباس حساب بشن
۳. آیتم ها برای میانگین گیری مون دارای ضریب هستن
۴. علاوه بر معدل ، برای امتیاز دادن هم یکسری مشکلاتی هست. متلا هر رنج نمره ای امتیاز خودش رو داده. ۵ محدوده عدد از ۲۰ نمره
۵. مشکل برای محاسبه خط تیره یا خالی گذاشتن سلول هست و همچنین فرمول if فکرکنم اشتباه حساب میکنه و گیج میزنه یا قوانین نا نوشته شون رو نمیفهمم. مثلا تو فرمول SUBTOTAL یا AGGREGATE مثلا کلمه حضور رو صفر حساب میکنه و با ضریب اش مثلا حساب میکنه و امتیاز ها رو خراب نشون میده.تو معدل نمرات هم همینه
بنظرتون چه کنیم بهتره؟ ممنون از راهنمایی تون”
سلام و نور وخدا قوت
. چجور میشه که به اکسل بگم هر آدرسی که تو سلول b2 هست رو به همون آدرس برو و ماکرو از قبل نوشته شده رو اونجا اجرا کنه؟
مثلا یک جدول نمرات کلاسی دارم که این رو تو شیت ورود ، نمره ها رو نوشتم. حالا میخام از لیست کشویی تاریخی که مد نظرم هست رو انتخاب کنم و سپس ماکرو محترم بره اون تاریخ رو تو دیتا من بگرده و جا خالی رو با این جدول پر کنه .یعنی جدول رو کپی کنه ببره تو همون سلول و ستون مربوط به اون تاریخ که تو شیت دیتا از قبل آماده کرده بودم.
به عبارت دیگه: برای نمایش لیست و دیتا ام با آف ست تونستم نمایش بگذارم و با انتخاب تاریخ و نام دانش آموز نمرات اون روز یا هفته اش رو بیارم . ولی میخام وقتی تاریخ رو میزنم، اطلاعات و نمرات رو ثبت کنه و ورودی باشه.و گزینه ای باشه که بره تو لیست اصیلی ثبت کنه. این آف ست فقط نمایش میده.
یعنی وابستگی ماکرو به سلول اکسل.
ماکرو کپی اش رو نوشتم. و اتومات میره تو آخرین سلول هم پیست میکنه. ولی میخام تاریخی که دستی بهش میگم رو بره اونجا بریزه.
Range(“Range(“b2″).Value”).Select”
بعدش Selection رو پیست کنه مثلا