تابع AVERAGE در اکسل — معرفی، کاربرد و نکات پیشرفته

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

نحو پایه تابع AVERAGE

نحو کلی:

=AVERAGE(number1, [number2], ...)

مثال ساده:

=AVERAGE(A1:A10)

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

مثال عملی

• میانگین نمرات دانش‌آموزان: =AVERAGE(B2:B21)
• میانگین فروش ماهانه به‌جز سلول‌های خالی: =AVERAGE(C2:C13)

تفاوت AVERAGE با AVERAGEA و میانگین شرطی

مثال AVERAGEA:

=AVERAGEA({1,2,TRUE,"text",4})

در این مثال، مقدارهای TRUE به عنوان 1 و “text” به عنوان 0 حساب می‌شوند و میانگین بر این اساس محاسبه می‌شود.

میانگین شرطی — AVERAGEIF و AVERAGEIFS

اگر بخواهید میانگین را بر اساس شرط حساب کنید، از AVERAGEIF (یک شرط) یا AVERAGEIFS (چند شرط) استفاده کنید.

=AVERAGEIF(A2:A100, ">100", B2:B100)

این فرمول میانگین مقادیر در B2:B100 را محاسبه می‌کند تنها برای ردیف‌هایی که مقدار متناظرشان در A2:A100 بزرگتر از 100 باشد.

=AVERAGEIFS(C2:C100, A2:A100, "East", B2:B100, ">50")

این فرمول میانگین مقادیر در C2:C100 را محاسبه می‌کند برای ردیف‌هایی که در A مقدار “East” و در B مقدار بزرگتر از 50 دارند.

نکته: حذف صفرها یا سلول‌های خاص از میانگین

گاهی لازم است صفرها را نادیده بگیرید (مثلاً صفر به معنی ثبت‌نشده است). راه‌های معمول:

• استفاده از AVERAGEIF برای حذف صفر: =AVERAGEIF(A1:A10, “0”)
• استفاده از فرمول آرایه‌ای برای شرط‌های پیچیده: =AVERAGE(IF(range0, range)) (در نسخه‌های قدیمی اکسل با Ctrl+Shift+Enter)

=AVERAGEIF(A1:A10,"0")

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

میانگین وزنی

اگر هر مقدار وزن متفاوتی دارد، از SUMPRODUCT به همراه SUM استفاده کنید:

=SUMPRODUCT(values, weights) / SUM(weights)

مثال:

=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)

این فرمول میانگین وزنی مقادیر B2:B6 با اوزان C2:C6 را محاسبه می‌کند. SUMPRODUCT حاصل ضرب زوجی را جمع می‌کند و سپس بر مجموع اوزان تقسیم می‌کند.

مدیریت خطاها و مقادیر نامعتبر

اگر در بازه خطا وجود دارد، AVERAGE خطا را برمی‌گرداند. برای مدیریت از IFERROR/AGGREGATE/SUBTOTAL استفاده کنید.

=IFERROR(AVERAGE(A1:A10), "خطا در داده‌ها")

مثال AGGREGATE برای نادیده گرفتن خطاها و ردیف‌های پنهان (Excel 2010+):

=AGGREGATE(1, 6, A1:A100)

در AGGREGATE، عدد 1 به معنی تابع AVERAGE است و گزینه 6 به معنی نادیده گرفتن خطاها. این راهکار برای دیتاست‌هایی با خطای #DIV/0! یا دیگر خطاها مفید است.

استفاده از SUBTOTAL برای گزارش‌ها

اگر می‌خواهید میانگین تنها روی ردیف‌های قابل مشاهده (مثلاً بعد از فیلتر) محاسبه شود، از SUBTOTAL استفاده کنید:

=SUBTOTAL(1, A2:A100)

تابع SUBTOTAL با کد 1 میانگین را محاسبه می‌کند و ردیف‌های فیلترشده یا پنهان را نادیده می‌گیرد (بسته به نوع مخفی‌سازی).

نکات عملی و بهترین‌ شیوه‌ها

• از AVERAGEA تنها وقتی که می‌خواهید مقادیر منطقی یا متن را به‌صورت عددی در نظر بگیرید استفاده کنید.
• برای میانگین شرطی پیچیده، AVERAGEIFS کارایی و خوانایی بهتری نسبت به فرمول‌های آرایه‌ای دارد.
• برای گزارش‌های پویا و فیلترشده از SUBTOTAL یا AGGREGATE استفاده کنید تا نتایج با فیلترها همگام شوند.
• همیشه پیش از محاسبه میانگین، داده‌های پرت و ورودی‌های اشتباه را بررسی کنید—یک یا دو مقدار پرت می‌تواند میانگین را به‌طور قابل توجهی تغییر دهد.

نمونه کاربرد در کسب‌وکار

در داشبورد فروش: میانگین فروش روزانه، میانگین فروش به ازای هر بازاریاب (با AVERAGEIFS) و میانگین ارزش سفارش (با SUMPRODUCT برای وزن) از نمونه‌های رایج هستند. استفاده از SUBTOTAL به شما اجازه می‌دهد تا میانگین‌ها با اعمال فیلتر زمانی یا منطقه‌ای به‌روز شوند.

جمع‌بندی

تابع AVERAGE ابزاری ساده ولی قدرتمند برای محاسبه میانگین در اکسل است. آشنایی با توابع مشابه (AVERAGEA, AVERAGEIF, AVERAGEIFS, AGGREGATE, SUBTOTAL) و تکنیک‌های مدیریت صفر، خطا و میانگین وزنی به شما امکان می‌دهد تحلیل‌های دقیق‌تر و گزارش‌های قابل اتکاتری بسازید.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال