بابلز لرن

تابع AVERAGEIFS یکی از توابع کاربردی اکسل برای محاسبه میانگین مجموعه‌ای از مقادیر است که هم‌زمان با چند شرط مشخص فیلتر شده‌اند. این تابع از نسخه‌های جدید اکسل (معمولاً از Excel 2007 به بعد) در دسترس است و زمانی که بخواهید میانگین را براساس بیش از یک شرط محاسبه کنید، بسیار مفید است.

ساختار (Syntax) و پارامترها

نکته: در برخی نسخه‌های محلی‌شده اکسل نام توابع ممکن است متفاوت نمایش داده شود؛ اما در اکثر نسخه‌های بین‌المللی نام انگلیسی AVERAGEIFS استفاده می‌شود.

قوانین و رفتار مهم

• AVERAGEIFS فقط سلول‌های عددی در average_range را میانگین می‌گیرد. سلول‌های خالی یا متنی نادیده گرفته می‌شوند.
• اگر هیچ سلولی با شرایط مطابقت نداشته باشد، نتیجه خطای #DIV/0! خواهد بود.
• طول هر criteria_range باید با هم و با average_range هم‌اندازه باشد؛ در غیر این صورت اکسل خطا می‌دهد.
• توابع آدرس‌دهی کامل ستون (مثل A:A) ممکن است کارایی را کاهش دهند؛ بهتر است بازه‌ها محدود یا به جدول‌های اکسل تبدیل شوند.

مثال‌های عملی

فرض کنید جدولی دارید با ستونه‌ای برای نام درس در A، نمره در B و ترم در C.

=AVERAGEIFS(B2:B100, A2:A100, "ریاضی", C2:C100, "ترم1")

این فرمول میانگین نمرات در ستون B را محاسبه می‌کند اما فقط برای ردیف‌هایی که ستون A برابر “ریاضی” و ستون C برابر “ترم1” باشند.

اگر بخواهید شرط عددی داینامیک باشد (مثلاً میانگین نمرات بالاتر از مقدار در سلول D1):

=AVERAGEIFS(B2:B100, B2:B100, ">"&D1, A2:A100, "فیزیک")

در این مثال از الحاق متن “>”&D1 برای ساخت شرط دینامیک استفاده شده است؛ اکسل مقدار سلول D1 را می‌گیرد و آن را به عملگر مقایسه‌ای متصل می‌کند.

استفاده از کاراکترهای عام (Wildcards) و تاریخ

• می‌توانید از “*” و “?” در معیارهای متنی استفاده کنید، مثلاً میانگین برای اسامی که با “م” شروع می‌شوند:

=AVERAGEIFS(B2:B100, A2:A100, "م*")

“م*” تمام مقادیری را که با “م” آغاز شده‌اند مطابقت می‌دهد.

• برای شرط‌های تاریخ، بهتر است از تابع DATE یا مرجع سلولی استفاده کنید:

=AVERAGEIFS(C2:C100, B2:B100, ">=" & DATE(2024,1,1), B2:B100, "<=" & DATE(2024,12,31))

این مثال میانگین مقادیری در C را برای تاریخ‌های بین اول ژانویه و 31 دسامبر 2024 محاسبه می‌کند (درصورتی‌که ستون B شامل تاریخ باشد).

مدیریت خطا و زمانی که نتیجه وجود ندارد

در صورت عدم تطابق هیچ ردیفی، AVERAGEIFS خطای تقسیم بر صفر برمی‌گرداند. برای جلوگیری از نمایش خطا می‌توان از IFERROR استفاده کرد:

=IFERROR(AVERAGEIFS(B2:B100, A2:A100, "شیمی"), "داده‌ای وجود ندارد")

این فرمول در صورت نبودن ردیف مطابق پیغام «داده‌ای وجود ندارد» را نمایش می‌دهد.

مثال پیشرفته — جدول فروش با چند شرط

فرض کنید جدول فروش دارید با ستون Product، Region، Sales و Date. می‌خواهید میانگین فروش محصولات “A” در منطقه غرب و در بازه زمانی مشخص را محاسبه کنید:

=AVERAGEIFS(Sales, Product, "A", Region, "West", Date, ">="&StartDate, Date, "<="&EndDate)

در اینجا Sales، Product، Region و Date نام محدوده‌های نام‌گذاری‌شده یا بازه‌های واقعی در شیت هستند؛ StartDate و EndDate سلول‌هایی هستند که کاربر تعیین می‌کند.

جایگزین‌ها و نکات پیشرفته

• اگر نسخه اکسل شما از توابع دینامیک پشتیبانی کند، می‌توانید از FILTER و AVERAGE ترکیبی استفاده کنید: AVERAGE(FILTER(…)) برای کنترل بیشتر. این روش در Excel 365 بسیار انعطاف‌پذیر است.
• برای شرایط پیچیده (مثل OR بین شروط)، گاهی بهتر است از AVERAGE همراه با آرایه شرطی استفاده کنید: =AVERAGE(IF((شرط1)+(شرط2), range)) که در نسخه‌های قدیمی باید با Ctrl+Shift+Enter وارد شود.

=AVERAGE(IF((A2:A100="Math")*(B2:B100>80), C2:C100))

این فرمول آرایه‌ای میانگین مقادیر C را محاسبه می‌کند زمانی که A رشته “Math” و B بزرگ‌تر از 80 باشد. در اکسل‌های قدیمی باید با Ctrl+Shift+Enter تأیید شود؛ در نسخه‌های جدید به‌صورت خودکار آرایه‌ها را پشتیبانی می‌کنند.

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

• از بازه‌های محدوده‌ای به‌جای اشاره به کل ستون استفاده کنید تا سرعت محاسبه بهتر شود.
• در صورت محاسبات بسیار بزرگ، تبدیل دیتای خود به Excel Table (Ctrl+T) و استفاده از نام‌های جدول می‌تواند خوانایی و سرعت را افزایش دهد.
• اگر از توابع volatile یا محاسبات پیچیده زیاد استفاده کنید، زمان بازسازی شیت طولانی می‌شود. بررسی و ساده‌سازی شروط می‌تواند کمک کند.

خلاصه

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

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

ارسال