اکسل محاسبه میانگین چند سلول را به یک کار بسیار آسان تبدیل کرده است - فقط از تابع استفاده کنید میانگین (میانگین). اما اگر برخی از ارزش ها وزن بیشتری نسبت به سایرین داشته باشند، چه؟ به عنوان مثال، در بسیاری از دروس، آزمون ها وزن بیشتری نسبت به تکالیف دارند. برای چنین مواردی باید محاسبه کرد میانگین وزنی.
اکسل تابعی برای محاسبه میانگین وزنی ندارد، اما تابعی وجود دارد که بیشتر کار را برای شما انجام می دهد: SUMPRODUCT (محصول جمع). و حتی اگر قبلاً از این ویژگی استفاده نکرده اید، در پایان این مقاله مانند یک حرفه ای از آن استفاده خواهید کرد. روشی که ما استفاده می کنیم در هر نسخه اکسل و همچنین صفحات گسترده دیگر مانند Google Sheets کار می کند.
میز را آماده می کنیم
اگر قرار است میانگین وزنی را محاسبه کنید، حداقل به دو ستون نیاز دارید. ستون اول (ستون B در مثال ما) شامل نمرات هر تکلیف یا آزمون است. ستون دوم (ستون C) شامل اوزان است. وزن بیشتر به معنای تأثیر بیشتر تکلیف یا آزمون در نمره نهایی است.
برای اینکه بفهمید وزن چیست، می توانید آن را به عنوان درصدی از نمره نهایی خود در نظر بگیرید. در واقع، این مورد نیست، زیرا در این مورد وزن ها باید تا 100٪ جمع شوند. فرمولی که در این درس تحلیل خواهیم کرد همه چیز را به درستی محاسبه می کند و به مقداری که وزن ها به آن اضافه می شود بستگی ندارد.
فرمول را وارد می کنیم
حالا که جدول ما آماده است، فرمول را به سلول اضافه می کنیم B10 (هر سلول خالی انجام خواهد داد). مانند هر فرمول دیگری در اکسل، ما با علامت مساوی (=) شروع می کنیم.
بخش اول فرمول ما تابع است SUMPRODUCT (محصول جمع). آرگومان ها باید در پرانتز قرار بگیرند، بنابراین آنها را باز می کنیم:
=СУММПРОИЗВ(
=SUMPRODUCT(
سپس، آرگومان های تابع را اضافه کنید. SUMPRODUCT (SUMPRODUCT) می تواند چندین آرگومان داشته باشد، اما معمولاً از دو آرگومان استفاده می شود. در مثال ما، اولین آرگومان محدوده ای از سلول ها خواهد بود. B2:B9الف که حاوی نمرات است.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
آرگومان دوم محدوده ای از سلول ها خواهد بود C2: C9، که حاوی اوزان است. این آرگومان ها باید با نقطه ویرگول (کاما) از هم جدا شوند. وقتی همه چیز آماده شد، براکت ها را ببندید:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
حالا بیایید قسمت دوم فرمول خود را اضافه کنیم که نتیجه محاسبه شده را بر تابع تقسیم می کند SUMPRODUCT (SUMPRODUCT) با مجموع اوزان. بعداً در مورد اینکه چرا این مهم است بحث خواهیم کرد.
برای انجام عملیات تقسیم، فرمول وارد شده را با علامت ادامه می دهیم / (میز مستقیم)، و سپس تابع را بنویسید مبلغ (جمع):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
برای عملکرد مبلغ (SUM) ما فقط یک آرگومان را مشخص می کنیم - محدوده ای از سلول ها C2: C9. فراموش نکنید که پس از وارد کردن آرگومان، پرانتز را ببندید:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
آماده! پس از فشار دادن کلید وارد، اکسل میانگین وزنی را محاسبه می کند. در مثال ما، نتیجه نهایی خواهد بود 83,6.
چگونه کار می کند
بیایید هر بخش از فرمول را تجزیه کنیم و از تابع شروع کنیم SUMPRODUCT (SUMPRODUCT) برای درک نحوه عملکرد آن. عملکرد SUMPRODUCT (SUMPRODUCT) حاصل ضرب امتیاز هر مورد و وزن آن را محاسبه می کند و سپس تمام محصولات حاصل را جمع می کند. به عبارت دیگر، تابع مجموع محصولات را پیدا می کند، از این رو نام آن را پیدا می کند. بنابراین برای تکالیف 1 85 را در 5 ضرب کنید و برای امتحان 83 را در 25 ضرب کنید.
اگر تعجب می کنید که چرا باید مقادیر را در قسمت اول ضرب کنیم، تصور کنید که هر چه وزن کار بیشتر باشد، تعداد دفعات بیشتری باید برای آن نمره در نظر بگیریم. مثلا، وظیفه 2 5 بار شمرد و امتحان نهایی - 45 بار از همین رو امتحان نهایی تاثیر بیشتری در نمره نهایی دارد.
برای مقایسه، هنگام محاسبه میانگین حسابی معمول، هر مقدار فقط یک بار در نظر گرفته می شود، یعنی همه مقادیر دارای وزن برابر هستند.
اگر می توانید زیر کاپوت یک عملکرد را نگاه کنید SUMPRODUCT (SUMPRODUCT)، دیدیم که در واقع او به این باور دارد:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
خوشبختانه، ما نیازی به نوشتن فرمول طولانی نداریم زیرا SUMPRODUCT (SUMPRODUCT) همه این کارها را به صورت خودکار انجام می دهد.
یک تابع به خودی خود SUMPRODUCT (SUMPRODUCT) تعداد زیادی − را به ما برمی گرداند 10450. در این مرحله، بخش دوم فرمول وارد عمل می شود: /SUM(C2:C9) or /SUM(C2:C9)، که نتیجه را به محدوده نرمال نمرات برمی گرداند و پاسخ را می دهد 83,6.
بخش دوم فرمول بسیار مهم است زیرا به شما امکان می دهد محاسبات را به طور خودکار تصحیح کنید. به یاد داشته باشید که وزن ها نباید تا 100٪ جمع شوند؟ همه اینها به لطف بخش دوم فرمول است. به عنوان مثال، اگر یک یا چند مقدار وزنی را افزایش دهیم، بخش دوم فرمول به سادگی بر مقدار بزرگتر تقسیم می شود و دوباره به پاسخ صحیح می رسد. یا می‌توانیم وزن‌ها را بسیار کوچک‌تر کنیم، مثلاً با تعیین مقادیری مانند 0,5, 2,5, 3 or 4,5و فرمول همچنان به درستی کار خواهد کرد. عالی است، درست است؟