آموزش فرمول نویسی در اکسل: میانگین گیری شرطی با توابع AVERAGEIF و AVERAGEIFS (قسمت 12)

فهرست مطالب و زمان بندی ویدیو

اگر دنبال یک روش دقیق برای میانگین گیری شرطی در اکسل هستید، توابع AVERAGEIF و AVERAGEIFS دقیقا همان ابزارهایی هستند که سرعت تحلیل داده‌های شما را چند برابر می‌کنند. در این آموزش فرمول نویسی در اکسل (قسمت 12) قدم‌به‌قدم یاد می‌گیرید چطور میانگین را بر اساس یک شرط یا چند شرط محاسبه کنید تا گزارش‌ها و داشبوردهای شما حرفه‌ای‌تر و قابل اعتمادتر شوند.

در ادامه مقاله، مفاهیم و مثال‌های عملی مربوط به میانگین گیری شرطی در اکسل را بررسی می‌کنیم تا بتوانید از تابع AVERAGEIF و تابع AVERAGEIFS در سناریوهای واقعی استفاده کنید. پیشنهاد می‌شود برای یادگیری کامل‌تر، ویدیو را همزمان با اجرای مثال‌ها در فایل اکسل دنبال کنید.

آشنایی با توابع شرطی AVERAGEIF و AVERAGEIFS در اکسل

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

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

تابع AVERAGEIF چیست؟ (میانگین گیری شرطی با یک شرط)

AVERAGEIF میانگین اعداد را فقط برای سلول‌هایی محاسبه می‌کند که یک شرط مشخص را پاس کنند. مثلا میانگین مبلغ خریدهای بالای 1,000,000 یا میانگین فروش یک محصول خاص.

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

ساختار و آرگومان‌های AVERAGEIF + مثال‌های کاربردی

ساختار کلی تابع به شکل زیر است:

AVERAGEIF(range, criteria, [average_range])

range: محدوده‌ای که شرط روی آن بررسی می‌شود.
criteria: شرط (مثل “>=10” یا “تهران” یا یک سلول حاوی شرط).
average_range: محدوده‌ای که از آن میانگین گرفته می‌شود (اختیاری). اگر وارد نشود، اکسل از همان range میانگین می‌گیرد.

مثال 1: میانگین نمره‌های بالای 15 در ستون B:

=AVERAGEIF(B:B, ">15")

مثال 2: میانگین فروش (ستون C) برای شهر «تهران» (ستون A):

=AVERAGEIF(A:A, "تهران", C:C)

نکته مهم این است که در شرط‌های عددی معمولا باید عملگرها را داخل کوتیشن قرار دهید (مثل ">=100"). همچنین می‌توانید شرط را از داخل یک سلول بخوانید؛ مثلا اگر سلول E1 شامل «تهران» باشد:

=AVERAGEIF(A:A, E1, C:C)

تابع AVERAGEIFS چیست؟ (میانگین گیری با یک یا چند شرط)

وقتی علاوه بر یک شرط، شرط‌های دیگری هم دارید (مثلا «میانگین فروش محصول X در شهر تهران در ماه دی»)، بهتر است از AVERAGEIFS استفاده کنید. این تابع امکان میانگین‌گیری بر اساس چند معیار را به صورت همزمان فراهم می‌کند.

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

ساختار و آرگومان‌های AVERAGEIFS + مثال‌های چندشرطی

ساختار کلی تابع:

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

average_range: محدوده‌ای که می‌خواهیم از آن میانگین بگیریم.
criteria_range: محدوده بررسی شرط.
criteria: خود شرط.
می‌توانید شرط‌های بیشتری هم به همین ترتیب اضافه کنید.

مثال 1: میانگین فروش (ستون D) برای شهر «تهران» (ستون A) و محصول «A» (ستون B):

=AVERAGEIFS(D:D, A:A, "تهران", B:B, "A")

مثال 2: میانگین حقوق (ستون C) برای افرادی که «واحد فروش» هستند (ستون A) و حقوق‌شان بالاتر از 20,000,000 است (ستون C):

=AVERAGEIFS(C:C, A:A, "فروش", C:C, ">20000000")

اگر شرط شما ترکیبی و وابسته به یک سلول باشد، می‌توانید با اتصال متن شرط را بسازید. مثلا اگر در سلول E1 عدد 20000000 باشد:

=AVERAGEIFS(C:C, A:A, "فروش", C:C, ">"&E1)

نکات مهم و خطاهای رایج در AVERAGEIF/AVERAGEIFS

1) هم‌ابعاد بودن محدوده‌ها: در AVERAGEIFS باید محدوده average_range و تمام criteria_rangeها از نظر تعداد ردیف/ستون با هم هم‌خوان باشند؛ در غیر این صورت خطا می‌گیرید یا نتیجه اشتباه می‌شود.

2) متن و فاصله‌های اضافی: اگر شرط شما متنی است (مثل نام شهر یا نام محصول) وجود فاصله‌های اضافه در داده‌ها می‌تواند باعث شود نتیجه صفر یا خالی برگردد. در چنین مواردی پاکسازی داده‌ها (مثلا با TRIM) کمک می‌کند.

3) وقتی هیچ داده‌ای شرط را پاس نکند: ممکن است خروجی خطای #DIV/0! یا نتیجه غیرمنتظره ببینید، چون میانگینی وجود ندارد. برای مدیریت این حالت می‌توانید از IFERROR استفاده کنید:

=IFERROR(AVERAGEIF(A:A,"تهران",C:C),0)

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

آنچه در ویدیو گفته ام

سلام تو این ویدیو از آموزش توابع پایه شرطی دو تابع اوریج ایف و اوریج ایفس رو خدمتتون آموزش میدیم که با این دو تابع میانگین‌گیری می‌کنیم اما با یک یا چند شرطی که خودمون براش در نظر می‌گیریم. من پویا حیتی هستم و با آموزش فرمولنویسی در اکسل همراه من هست. تابع اوریج ایف که میانگین اعداد با یک شرط رو برای ما انجام میده. سینتکس این تابع به این صورت هستش که مساوی اوریج ایف رو می‌نویسیم و داخل پرانتز تا آرگومان از ما می‌گیره. آرگومان اول و دوم اجباری هستش و آرگومان سوم اختیاری. خب آرگومان اول رنج یا محدوده‌ای هستش که ما میخوایم بریم روش شرط رو چک بکنیم. آرگومان دوم کریتریا یا شرطمون هستش همون معیاری که میخوایم برای چک کردن انجام بدیم و محدوده بعدیمون که اوریج رنج هست محدوده‌ای هستش که ما میگیم که میانگین رو از این محدوده بگیر و به ما نهایتاً خروجی بده دوباره تو این موضوع نکته رو خدمتتون عرض بکنم که آرگومان سوم اختیاری هست و اگر آرگومان سوم رو شما وارد نکنید اون میانگین‌گیری رو از همون ون آرگومان اول انجام میده و از همون آرگومان اول که رنج هستش استفاده می‌کنه. خب برای اینکه بهتر با این تابه آشنا بشیم من قسمت محدودیت‌ها و نکات مهمشو خدمتتون بگم. یکی اینکه میانگین‌ها را فقط بر اساس یک شرط محاسبه می‌کنه. خب اوریج ایفس هستش که اون برای چند شرط استفاده می‌کنه؟ اینجا فقط ما یک شرط داریم. از عملگرهای منطقی و وایلد کارت‌ها پشتیبانی می‌کنه که باعث میشه که امکانات این نرم‌افزار و کاربردش افزایش پیدا بکنه. به طور خودکار سلول‌های خالی رو از محاسبه میانگین حذف می‌کنه. این نکته خیلی مهمه. ما تو خود تابع اوریج که توی جلسات قبلی خدمتتون آموزش دادیمم دقیق مثال حل کردیم که سلول‌هایی که خالی باشن شمرده نمیشن و در نتیجه توی محاسبات میانگین‌گیری ما هم لحاظ نمیشن. خیلی نکته مهمی هستش که باید بهش دقت بکنید. اگر هیچ سلولی معیار مشخص شده را برآورده نکنه خطای دیو بر صفر میده در به این صورت هستش که هیچی رو پیدا نکرده و این مشکل براش پیدا میشه که خب هیچی پیدا نکرده از اونورم هیچ تعدادی نداره پس صفر تقسیم بر صفر به ما خطای دیو بر صفر رو میده که نشون دهنده این هستش که این شرطی که ما اینجا گذاشتیم هیچ کدوم از این رنج ما رو ترو نکرده که حتی یک پارام پارامتر هم وارد بحث میانگین‌گیری ما بشه. مقادیر ترو و فالس هنگام محاسبه میانگین نادیده گرفته می‌شوند و تو اون قسمت شرطگذاریمون که داریم به حروف کوچک و بزرگ حساس نیست. این موضوعی که به حروف کوچک و بزرگ حساس نیستو ما توی مین مین ایف و مین ایس داشتیم. ماکس ایس داشتیم. سام ایف و سام ایفس داشتیم و توی قسمت کانت ایف و کانت ایفزم داریم. یعنی همه این توابع پایه شرطی این موضوع رو دارن که به حروف بزرگ کوچیک حساس نیستن. اینجاهم توابع مرتبطشو خدمتتون گذاشتم. بریم سراغ مثالی که می‌خوایم در این رابطه حل بکنیم. خب در مثال اول ما می‌خوایم میانگین فروش مردها رو به دست بیاریم. اول بریم خود تابع اوریجو بنویسیم. بذارین من این راه رو با همدیگه مرج بکنم. آت اچ ام سی رو می‌زنم و مرج میشه. خب اینجا یه دونه ساده می‌نویسم تابع اوریج و میزان فروش رو بهش میدم و این میزان فروش که ممیز هست میشه میانگین فروش کل آدم‌های من الان من فقط می‌خوام میانگین فروش مردها رو ببینم که بعد ببینم مردها بیشتر فروختن یا زن‌ها خب میام مساوی می‌ذارم می‌نویسم اوریج ایف خب میگه رنجی که می‌خوای شرط رو روش چک بکنی اول بده. ما می‌خوایم رو این چک بکنیم که مرد هستن و بعد مقادیر میانگین‌گیریو بهش بدیم. تو آرگومان دوم شرطمون رو بهش میدیم که اینجا باید مرد باشه توی دبل کوتنشن و حالا روی چه داده‌ای برای ما میانگین‌گیری انجام بده؟ این هم میشه لیستی که می‌خواد برای ما ازش میانگین‌گیریو انجام بده. مساوی خب الان برای ما میانگین فروش مردها شد یعنی دقیقاً اومده این‌هایی که جلوشون مرد هستن این چ۴ار تا دیتا رو با همدیگه جمع کرده و تقسیم بر کرده خب بریم برای خانم‌ها هم این موضوع رو انجام بدیم اوریج ایف پرانتز باز دوباره همین قسمت و این سری مساوی زن باشن و دوباره میزان فروشم رو بهش میدم. خب اینجا مشخص شد که میزان فروش زن‌ها میانگینش البته هستش و مردها و خیلی مثلاً اگه یک مدیری باشه خیلی راحت می‌تونه تصمیم‌گیری بکنه اینجا که زن‌ها تونستن فروش بیشتری رو میانگین داشته باشن. اما شاید یکی بخواد بگه خب این چقدر سخته که ما این کارو بکنیم. من یه مثال رو یه جور دیگه می‌زنم. اینجا رو می‌نویسم مرد، اینجا رو می‌نویسم زن. می‌خوام یه ذره فرمو جذاب‌تر باشن. می‌تونم بیام چیکار بکنم؟ این قسمتی که مرد رو داخل دبل کتنشنا گذاشتم این رو پاک بکنم. آرگوم دومو. آرگمان دوم بگم از اینجا بخونه. یعنی دقیقاً برو اینجا این رو بگرد. اگر مساوی این بود اینجا میانگینشو بگیر. خب برای زن‌ها چطور؟ اگر فرمول رو همینجوری بکشم بیاد تا پایین مطمئناً به مشکل خواهم خورد. ببینید الان عدد رو به من نشون دادا ولی اینجا چون باگ نداشت یا اینجا خالی بود خطا نداد یا اتفاقی براش نیفتاد ولی واقعیتش این کار اشتباهه. اگر جلسه اول آموزشو دیده باشین تو بحث ارجاعدهی ما این کارو نباید انجام بدیم. باید بیایم چیکار کنیم؟ بیایم تو همین فرمول بگیم که خب این بخش من که اجباری یعنی مطلق هستش این رو من باید ببندم با کلیدهای اف۴ می‌بندمش اینم که دوباره اجباری و مطلق هستش باید همینو داشته باشم اینم می‌بندمش ک که می‌خوام بکشم اینو بیا فرمولو بکشم پایین می‌خواد بشه زن اینو می‌ذارم باز بمونه حالا فرمولم رو از مرد به زن می‌کشم تا ببینید دوباره اون بازه‌های من تغییر نکرد هرچند توی قسمت قبلی هم که من این کارو کردم عدد نهایی همین بودا ولی تو این مثال اینجوری شد به خاطر اینکه من این عملاً ارجاعهام یه ذره اومده بود پایین هنوز زن روش نبود یعنی انگار که لیست زن‌ها کامل بازم انتخاب شده بود فقط پس دقت بکنید توی قسمت کپی کردن فرمولاتون حتماً به ارجاع‌های نسبی مطلق و مختلطتون دقت بکنید قسمت اول آموزش فرمول نویسی درک بسیار مهمه شاید بگم مهم‌ترین قسمت همون قسمتی بود که راجع به ارجاع‌ها صحبت کردیم. خب اما توی مثال سوم می‌خوایم راجع به میانگین فروش افراد بالای نمره یک مثالی بزنیم میام می‌نویسم مساوی اوریج ببخشید اوریج ایف پرانتز باز خب شرطمو میخوام روی نمره‌ها چک بکنم این شرطمه جایی که باید شرطمو چک بکنم و بزرگتر از رو توی آرگومان دوم توی قسمت شرطم بین د تا دبل کتنشن می‌ذارم. یه نکته‌ای بگم بچه‌ها توی فرمولنویستی ما کلاً هر چیزیو که می‌خوایم شبیه استرینگ عمل بکنیم یا اینجوری شرطی باشه باید بین د تا دبل کوتنشن داشته بش داشته باشیم. خیلی به ندرت پیدا میشه که ما داریم دبل کوتنشن تکی استفاده می‌کنیم. پس مثل پرانتز و باز و بسته که یه باز داریم یه بسته داریم ذهنیتتون اینجوری باشه. هر دبل کتنشنی که می‌ذاریمو یکیشو باز و یکیشو بسته در نظر بگیریم و باید این پکیج کلاً بسته باشه یعنی باز و بسته دبل کوتنشنو داشته باشین تا به مشکل برنخورین این معمولاً زمانی بیشتر به مشکل می‌خورین که فارسی و انگلیسی رو با هم دارین خیلی دقت بکنید کیبوردتونو به موقع انگلیسی و به موقع فارسی بکنید که فرمولاتون چپندر چلحاق واقعیتش نشه و کلافتتون نکنه خب تو قسمت سومم که میزان فروش میان سنگین فروش رو می‌خوایم و تمام اینجا اومد چیکار کرد بالای این سه تا بالای بودن همین تمام این سه تا دقیقاً اگه نگاه کنید سه تاشونم ۵۸۰ه این‌ها رو میانگیناشونو گرفت یه دونه دیگه هم باید بالای ما داشته باشیم نه فکر کنم من اشتباه دیدم این ۵۵۰ه من فک کرم صداش ۵۸۰ه تعجب کردم که چرا میانگین نشده دیدم نه یکیش ۵۵۰ه و تا ۵۸۰ه دقیقاً این میانگین این سه تا شده و درست انجام داده برای ما تابع اوریج ایپس میانگین اعداد با یک یا چند شرط رو برای ما انجام میده سینتکسی یا نوشتارش به این صورت هستش که سه تا آرگومان اجباری از ما داره می‌گیره و بقیه جفت آرگومان‌هایی که از ما می‌گیره اختیاری هست یعنی ما می‌تونیم تعداد شرطای زیادی رو بهش بدیم ولی یک شرطو باید حتماً بدیم آرگومان اول اورج رنج هستش که محدوده میانگینو میگه اصلاً میگه میانگینت رو من از کدوم محدوده بگیرم حالا که ما محدوده رو بهش دادیم میگیم که شرطها رو چهجوری چک کن میگیم رنج اول محدوده اول برای ارزیابی و در قسمت بعدی کریتیای یک میشه معیار یا شرطمون که باید روی همین محدوده که بهش دادیم چک بکنه از این به بعد دیگه ما باید جفت بهش بدیم دیگه یعنی رنج شرط رنج شرط کرد محدوده و معیاری که ما باید بهش بدیم باید جفت جفت باشه که بتونه این کارو برای ما انجام بده و زمانی اون میانگین‌گیری انجام میشه که اون معیار ما ترو باشه یعنی درست باشه و همه این‌ها وقتی درست باشن می‌تونه بره اون رنج رو انتخاب بکنه و میانگین‌گیریو برای ما انجام بده. خب اینجا یه چند تا مثالشو می‌بینیم. بیایم پایین‌تر. قسمت محدودیت‌ها و نکات مهمش. میانگین‌ها را بر اساس چندین شرط محاسبه می‌کنه. طبق معمول از عملگرهای منطقی و وایلد کارت‌ها پشتیبانی می‌کنه. به طور خودکار سلول‌های خالی رو از محاسبه میانگین حذف می‌کنه که دوباره این نکته رو ما تو قسمت اورج ایف هم خدمتتون گفتیم. با سلول‌هایی مثل تاریخ، اعداد و متن هم کار می‌کنن. فقط سلول‌هایی که همه شرایط رو برآورده بکنن در میانگین نهایی حساب میشن. همون چیزی که خدمتتون توی آموزش‌های قبلی هم دادم این‌ها از منطق اند استفاده می‌کنن. تو منطق اند همه چی باید ترو میشد تا جواب ما میشد ترو. حتماً به جلسه دوم آموزش اکسلمون مراجعه بکنید. اگر قسمت اند اور رو یه مقدار مشکل دارین تو اونجا توضیح دادیم که اند زمانی همش ترو میشه تو جوابش رو میشه که همه آیتم‌های داخلش تمام شرطای داخلش ترو باشن اینجام همون هستش اگر هیچ سلولی معیار مشخص شده رو برآورده نکنه که خطای تقسیم بر صفر یا دی بر صفر به ما برمی‌گردونه به حروف بزرگ و کوچکم حساس نیست خب بریم راجع بهش مثال بزنیم هرچند قبلشم توابع به مرتبطشو شما می‌تونید اینجا مشاهده بفرمایید. بریم سراغ مثالمون. در مثال اول می‌خوایم میانگین فروش مردهای گروه A رو داشته باشیم. خب میایم می‌نویسیم اینجا د تا شرط داریم. مساوی اوریج ایس ایفس و پرانتز باز. خب اول میگه رنج اوریجتو بگو. ما میزان میانگین فروش رو می‌خوایم دیگه. که خب پس باید از قسمت میایین میزان فروش‌هامونو بهش بدیم. خب حالا شرط اولمونو می‌خوایم بریم. خیلی دیگه اینجا تفاوتی نمی‌کنه چون ما گفتیم برای اینکه نهایتاً این اعداد بیان تو میانگین‌گیری قرار بگیرن باید همش تو رو باشه. پس چ اول چه مردها رو بگیریم چه گروهو بگیریم هیچ فرقی نمی‌کنه. خب من اول گروه رو می‌گیرم که باید برابر باشه با a و شرط دوممون که محدوده مرد و زن رو می‌گیرم که باید برابر باشه با مرد و پرانتزو می‌بندم و این میشه میانگین فروش مردهای گروه a یعنی این دیتا و اینکه نیست این دیتا و این دیتا یعنی این تا دیتا رو خودش گرفته با همدیگه جمع کرده و تقسیم بر کرده تا بتونه میانگین فروش این آدم‌ها رو به دست بیاره. حالا میریم میانگین فروش زن‌های گروه سی. خب خیلی سریع‌تر بنویسیم. اوریج ایکس پرانتز باز دوباره میانگین فروش رو می‌خوایم و این سری اینوری میریم. اول این رو انتخاب می‌کنیم و میگیم زن و گروه سی اینم انتخاب می‌کنیم و اینجا حتی به جای گروه سی می‌تونم یه دونه از این اینجا رو یه دونه از سلوها اینجا رو انتخاب بکنم اما خیلی اتفاق خاصی نمیفته یعنی می‌تونم بگم این برابر دی باشه حالا به صورت تستی داریم انجام میدیم شما با هم ببینید که امکان پذیر هست اینم پس شد میانگین فروش زنها های گروه سی که میشه این آیتم این که هیچی همین یه دونهست دیگه یعنی کلاً یه آیتم داشتیم جمع آیتمامون که میشه تقسیم بر یه دونه‌ای که داشتیم که خودش یه دونه هست میشه دوباره جوابمون خب توی مثال سوم میانگین فروش گروه آ با نمره بالای دوباره می‌نویسیم اوریج ایس چون د تا شرط داریم میانگین دوباره فروش رو می‌خوایم و شرط اولمون گروه آ باشه. من برای اینکه راحت‌تر باشه اینو می‌ذارم آ از همین سلول استفاده می‌کنم و نمره بالای این رو انتخاب می‌کنم و شرطم رو براش رو داخل د تا دبل کوتنشن می‌ذارم بزرگتر از و دبل کوتنشنمو می‌بندم. پرانتزمو می‌بندم و این هم شد جوابم که میشه این آیتم این آیتم اینکه آ ولی نیست همین د تا یعنی و رو به اضافه هم شده و تقسیم بر شده تا اینجا به ما میانگین اون شرطی که ما خودمون براش گذاشتیم رو برای ما محاسبه کنه و بهمون