تابع AVERAGEIFS اکسل و میانگین گیری چند شرطی

تابع AVERAGEIFS اکسل

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

قاعده کلی تابع AVERAGEIFS اکسل:

=AVERAGEIFS(شرط2[اختیاری],محدوده2[اختیاری],شرط1,محدوده1,محدوده میانگین گیری,…)

  • محدوده میانگین گیری: این پارامتر محدوده ای رو که میخوایم از سلول های اون میانگین بگیریم مشخص میکنه.
  • محدوده1: این پارامتر محدوده ای که قراره اولین شرطمون رو در اون چک کنیم مشخص میکنه. هر سلول این محدوده متناظر با یک سلول محدوده میانگین گیری هست و تعداد سلولهای موجود در این محدوده باید با تعداد سلولهای موجود در محدوده میانگین گیری برابر باشه. به عنوان مثال اگر ما برای پارامتر محدوده میانگین گیری مقدار A1:A10 رو تعیین کردیم میتونیم برای محدوده1 B2:B11 رو تعیین کنیم چون تعداد سلولهای این محدوده هم دقیقاً برابر 10 سلول هست، اما نمیتونیم A2:A12 رو تعیین کنیم چون تعداد سلول های اون از محدوده A1:A10 بیشتره.
  • شرط1: این پارامتر مشخص میکنه که سلول های موجود در محدوده1 باید واجد چه شرطی باشند. این پارامتر میتونه یک عدد مثل 10، یک متن مثل قطعه 1 یا یک عبارت مثل “0=” باشه. در صورتی که این پارامتر عبارت باشه باید حتما بین دو علامت نوشته بشه.
  • محدوده2: این پارامتر محدوده ای رو تعیین میکنه که میخوایم شرط دوم خودمون رو در اون چک کنیم. تعیین این پارامتر اختیاری هست. در صورتی که از این پارامتر استفاده نکنیم در واقع تابع AVERAGEIFS اکسل ما تبدیل به تابع AVERAGEIF میشه
  • شرط2: این پارامتر مشخص میکنه که سلول های موجود در محدوده2 باید در چه شرطی صدق کنند.
  • محدوده3، شرط3 و …: در صورتی که بخوایم بیش از یک دو شرط رو تعیین کنیم میتونیم.

فقط سلول هایی از محدوده میانگیری در محاسبه میانگین لحاظ میشند که سلول متناظر اونها در محدوده1 واجد شرط 1 باشه، سلول متناظر اونها در محدوده2 واجد عبارت شرطی 2 باشه باشه و الی آخر.

مثال استفاده از تابع AVERAGEIFS اکسل:

جدول زیر رو در اکسل خودتون وارد کنید:

سال نام محصول مشتری میزان خرید
1391 لپ تاپ نوع1 حسن زاده 29
1392 لپ تاپ نوع1 حسن زاده 24
1391 لپ تاپ نوع1 حسن زاده 18
1392 لپ تاپ نوع2 حسن زاده 47
1391 لپ تاپ نوع3 حسن زاده 29
1392 لپ تاپ نوع3 علوی 18
1392 لپ تاپ نوع3 علوی 17
1391 لپ تاپ نوع3 علوی 12
1391 لپ تاپ نوع1 تقوی 37
1391 لپ تاپ نوع2 تقوی 49
1392 لپ تاپ نوع1 تقوی 22
1392 لپ تاپ نوع1 تقوی 14

میخوایم با استفاده از تابع AVERAGEIFS اکسل میانگین خرید هر فاکتور سال 1391  آقای حسن زاده رو محاسبه کنیم.

1. تعیین محدوده میانگین گیری:

خب ازونجایی که ما قراره میانگین میزان خرید مشتری رو حساب کنیم و میزان خرید هر مشتری هم در ستون چهارم قرار داره پارامتر محدوده میانگین گیری ما برابر با ستون چهارم اکسل یعنی D:D خواهد بود.

2. تعیین محدوده1:

یکی از شرط ها این هست که مشتری آقای حسن زاده باشه. نام مشتریان در ستون سوم قرار داره. پس اولین محدوده ما C:C هست.

3. تعیین شرط1:

چون شرط ما اینه که نام مشتری آقای حسن زاده باشه عبارت “حسن زاده” رو به عنوان شرط1 و پارامتر سوم تابع AVERAGEIFS اکسل استفاده میکنیم.

. تعیین محدوده2:

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

3. تعیین شرط2:

برای چک کردن اینکه صرفاً خریدهای آقای حسن زاده در سال 1391 در محاسبه میانگین لحاظ بشه کافیه 1391 رو به عنوان شرط2 و پارامتر پنجم تابع AVERAGEIFS اکسل استفاده میکنیم.

در پایان تابع AVERAGEIFS اکل ما به شکل زیر کامل میشه:

=AVERAGEIFS(D:D;C:C;”حسن زاده”;A:A;1391)

اگر این فرمول در یکی از سلول های اکسل بنویسیم عبارت 25 به عنوان نتیجه تابع در سلول نمایش داده خواهد شد که میانگین خرید آقای حسن زاده در سال 1391 هست.

دانلود فایل مثال تابع  AVERAGEIFS اکسل:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

تمرین تابع  AVERAGEIFS اکسل:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

17 Comments on “تابع AVERAGEIFS اکسل و میانگین گیری چند شرطی”

  1. salehi1354

    چنانچه بخواهیم میانگین اعداد در یک ستون که بیشتر از 0 است را محاسبه نماییم چگونه فرمول آن را مینوسیم
    با تشکر

  2. آموزش ماکرو نویسی در اینجا

    سلام ، جناب صالحی – راهش خیلی ساده است ، اگه فرض کنیم که اعداتون ، در ستون A و در ردیفهای 10 تا 100 باشه ، کافیه فرمول زیر را کپی کرده و با آن کار کنید . موفق باشید – خانیکی
    =AVERAGEIF(A10:A100,”>0″)
    0919-302-6452

  3. ehsan

    با عرض سلام و خدا قوت ….
    من یک انتگرال دارم که دو تا مقدار اولیه آلفا و بتا را به عنوان ورودی های انتگرال میگیره(منظور کران بالا و پایین انتگرال) و انتگرال رو حساب میکنه ….. اصلا میشه انتگرال نوشت؟؟؟؟؟؟؟؟؟؟؟؟

    ممنون میشم پاسخ دهـــــــــــــــــــــــــــــــــــــید . . .

  4. SHIVA

    سلام.
    یک سری داده دارم که میخوام بین این داده هابعنوان مثال میانگین داده های بین 70تا 100 رو بدست بیارم،چه فرمولی باید براش بنویسم؟
    ممنون میشم زودتر کمکم کنین. با تشکر

    1. یاسین اسماعیل پور

      سلام شیوا جان. از همین تابه averageifs میتونی استفاده کنی:
      =averageifs(a1:a1000;a1:a1000;”>=70″;a1:a1000;”< =100")

  5. احسان

    سلام وقتتون بخير ي سوال در مورد average داشتم ، فرض كنيم يه سري نمره داريم كه از 10 تا 20 هستن ميخوايم با استفاده از AVERAGE بگيم كه نمراتي كه از 10 پايين تر هستن و اكسل بنويسه رد و از 10 بالاترو بنويسه قبول
    نمره نام
    15 علي
    12 رضا
    9 محمد
    14 ياسين
    20 عباس
    8 حسين

  6. sepehr

    سلام
    من میخام دو ستون رو به هم ربط بدم هرکاری هم کردم جواب نداد میتونین راهنماییم کنین؟
    مثال:
    ستون A شرح کالایه
    ستون B کد کالایه
    حالا در شیت دومم یک جدول دارم که میخام وقتی کد کالا رو میزنم شرح کالا بیاد که نمیاد…

  7. Zohreh

    سلام من میخواستم میانگین حقوق پرداختی حسابداران یک شرکت رو با تابع AVEREGEIFSحساب کنم میشه کمکم کنید

    1. یاسین اسماعیل پور

      سلام. من که کامل نحوه استفاده از تابع averageifs رو توضیح دادم. مشکلتون کجاست؟ لطفا جزئیات بیشتر بگید تا بتونم کمک کنم

  8. tahere

    سلام
    من یک سری داده دارم میخوام میانگین گیری کنم که بیشترین و کمترین مقدار در نظر گرفته شود؟

  9. ali

    سلام دوستان
    من 73225 دیتا دارم که نمودارش خیلی پهن و بد شکل میشه یه راه معرفی کنید بهم که بتونم نمودارمو مثل خط بکشم.
    ممنون

  10. eliiiiii

    سلام میشه نمونه فایل به نمودار بردن نمرات یک کلاس را در محیط اکسل برای دانلود بزارید. یا راهنمایی کنید چگونه میشه این کار رو انجام داد.

  11. محمد

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

  12. پرهام

    درود بر شما
    من با استفاده از AVEREGEIFS تونستم مسئلمو پیش ببرم مطابق فرمول زیر:
    =AVERAGEIFS(E2:E745,G2:G745,”7″,H2:H745,”90″)
    ولی مشکل اینجاست که اگر عدد 7 وجود نداشته باشه این رو #DIV/0! نشون میده. امکانی هست که هرجا عدد 7 و یا عدد 90 وجود نداشت عدد صفر رو نشون بده بجای این پیغام: #DIV/0!
    ممنون میشم راهنمائیم بفرمائید..

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *