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

تابع 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 اکسل:

[private role=specialsubscriber]AVERAGEIFS.sample.worksheet.ir[/private]

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

[private role=specialsubscriber]با استفاده از تابع AVERAGEIFS اکسل، برای مثال بالا میانگین خرید آقای حسن زاده رو در سال 1392 برای لپتاپ نوع 1 محاسبه کنید.[/private]

49 Comments
  • karimi
    Posted at 14:02h, 23 می پاسخ

    باسلام، من میخوام توی یک سری زمانی میانگین بگیرم، ولی به صورت چهارتا چهارتا. مثلا 1تا 4، 5تا8، 9تا12 و…
    برای این موضوع راه حلی دارید راهنمایی بفرمایید؟
    ممنون میشم اگر پاسخ بدید.

  • ZHALEH AHMADIIRAEI
    Posted at 21:46h, 16 نوامبر پاسخ

    سلام دوست گرامی. من میخوام میانگین یک ستون دیتا در حدود 40هزازرتاست رو درروزهای مختلف سالهای مختلف بگیرم از چه دستوری باید استفاده کنم؟

  • اردشیر
    Posted at 23:35h, 22 می پاسخ

    سلام.
    میخوام میانگین اولین دو عدد در یک ستون رو بگیرم.
    به شرط اینکه اون دو عدد مثلا از یک مقداری بیشتر باشه(مثلا 50 بیشتر باشه)
    ممنون
    10
    20
    30
    51
    40
    100
    60
    200

  • saeed
    Posted at 08:59h, 13 می پاسخ

    سلام و عرض ادب
    من چندتا امتحان گرفتم، یکی از 8 نمره، یکی از 10 نمره و یکی از 12 نمره
    حالا میخوام اکسل این سه نمره با هم جمع بزنه و میانگین از 20 نمره بهم بده؟

  • عیسی حقیری
    Posted at 02:17h, 25 مارس پاسخ

    در میانگین نمرات بعضی دروس یک نمره ای هستند (مانند انضباط پایانی ندارد)و بعضی از دروس دو نمره ای هستند
    میانگین چگونه بگیرم

  • کارگر
    Posted at 22:46h, 29 اکتبر پاسخ

    سلام شبتون بخیر
    من یک سری داده برای میزان درامد دارم که به صورت طبقه بندی هست (زیر دو میلیون، 2 تا 4 میلیون ، 4 تا 6 میلیون و …) چطور میتونم میانگین و انحراف معیارش رو توس اکسل بگیرم؟
    ممنون

  • mohammad
    Posted at 07:58h, 27 ژوئن پاسخ

    سلام
    ممنون بخاطر مطالب و آموزش حرفه‌ای تون.
    من یه جدول دارم که دو ستون نوع شغل(رسمی / قراردادی / پیمانکاری) و سن رو داره حالا من میخوام به تفکیک میانگین هر نوع شغلی رو بگیرم و میانگین سن رسمی و قراردادی رو هم با هم بگیرم.
    یعنی خروجی م اینها رو داشته باشم:
    1. میانگین سن برای نوع شغلی رسمی = XX
    2. میانگین سن برای نوع شغلی قراردادی= XX
    3. میانگین سن برای نوع شغلی پیمانکاری= XX
    4. میانگین سن برای نوع شغلی رسمی و قراردادی = XX

  • ،mohammad
    Posted at 21:09h, 10 آوریل پاسخ

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

    • یاسین اسماعیل پور
      Posted at 23:41h, 10 آوریل پاسخ

      سلام محمد جان. برای اینکار باید از ترکیب مطلق و نسبی استفاده کنید. فرض کنید مقادیرتون در ستون A قرار دارند. فرمول زیر رو در b1 بنویسید و تا انتها کپی کنید:
      =average($A$1:A1)

  • محمد
    Posted at 15:16h, 14 مارس پاسخ

    سلام دوست عزیز
    دوست عزیز من یه 12 سلول رو میخوام avreage بگیرم به شرطی که اعداد بزرگتر از صفر باشد .
    یعنی اگر تو 3 سلول اعدادبزرگتر از صفر بود فقط میانگین این سه عدد را محاسبه کند
    ممنون میشم راهنمایی بفرمایید.

    • یاسین اسماعیل پور
      Posted at 17:03h, 09 آوریل پاسخ

      سلام. برای فرض کنید این 12 سلول توی A1 تا A12 باشند. برای اینکار از فرمول زیر استفاده کنید:
      =Averageif(A1:A12,”>3″,A1:A12)

      • sama
        Posted at 12:56h, 21 جولای پاسخ

        سلام،در تکمیل صحبت های ایشون اگر در یک ردیف مثلا همین 12 سلول همه مقدار 0 داشته باشد و بخواهیم به جای نشان دادن خطای Div /0 به ما عدد صفر 0 را در پاسخ نشان دهد چه باید کرد؟چون فرمول تعمیم داده میشه

      • sama
        Posted at 09:22h, 02 آگوست پاسخ

        ممکنه لطفا پاسخ بدیذ

  • محمد
    Posted at 14:58h, 04 دسامبر پاسخ

    سلام و احترام.
    نحوه محاسبه مجموع پنج عدد نزدیک به میانگین در یک بازه عددی به چه صورت است؟
    ممنون از سایت فوق العاده شما.

  • ebrahimi
    Posted at 12:51h, 04 اکتبر پاسخ

    باسلام
    لطفا راهنمایی فرمایید چنانچه یک مبلغی را برای یک نوع جنس با قیمتهای مختلف خریداری میشه را در یک ستون نمایش داده شود بطورمثال :
    Aمبلغ خرید
    Bقیمت خرید
    Cتعدادخریداری شده
    Dمیانگین قیمت خرید
    وهمچنانکه خریدها ادامه داشته باشه میانگین نیز محاسبه شود ونیازی به اینکه هربار برای بحث میانگین فرمول تعریف شود نباشه

  • هانیه
    Posted at 01:33h, 13 سپتامبر پاسخ

    باعرض سلام میشه لطفا راهنماییم کنید من سلول های باشماره تکراری زیاددارم میخوام سلول های تکراری رو شماره گذاری کنه مثلا سلول 1 عدد 1 سلول 2 عدد 2و… جلوش بیفته و سلولی که شماره تکراری نداره 1 باشه ممنون

  • سالار
    Posted at 12:02h, 26 ژوئن پاسخ

    میشه به زبان ساده بفرمایید منظور از این فرمول چیه”
    AVERAGEIFS( ‘[950801b.xlsx]1’!$J:$J, ‘[950801b.xlsx]1’!$A:$A, B$1, ‘[950801b.xlsx]1’!$J:$J, “> 15”, ‘[950801b.xlsx]1’!$J:$J, “< 50" )

    • یاسین اسماعیل پور
      Posted at 17:36h, 30 ژوئن پاسخ

      سلام. این فرمول داره معدل مقادیر موجود در ستون J رو میگیره با این شرط که فقط اعداد بزرگتر از 15 وکوچکتر از پنجاه در معدل گیری لحاظ بشن و یه شرط دیگه روی ستون A که چون کل شیتتون رو ندارم نمیدونم دقیقا چیه

  • mehran
    Posted at 23:29h, 22 می پاسخ

    با سلام
    من 30000تا داده دارم و میخوام در یک ستون برای هر 20 داده یک میانگین بدست بیارم چکار باید بکنم. فرمولشو چجوری باید بنویسم؟
    لطفا راهنمایی کنید

    • یاسین اسماعیل پور
      Posted at 10:56h, 23 می پاسخ

      سلام. برای اینکار باید از ترکیب دو تابع offset و row استفاده کنید. فرض کنید مقادیرتون در ستون a قرار دارند. حالا فرمول زیر رو در b1 بنویسید و کپی کنید تا پایین:
      =AVERAGE(OFFSET($A$1:$A$20,(ROW()-1)*20,0))

  • میترا
    Posted at 10:55h, 12 می پاسخ

    سلام
    اگر در یک کارنامه بخواهیم بر اساس یک کد ، معدل اعداد متناسب را به دست بیاوریم ، چه فرمولی باید بنویسیم .
    برای توضیح بیشتر:
    کد های استانی موجود است . در یک ستون
    در ستون دیگری امتیازاتی ثبت شده
    ما باید بتوانیم کد استان را جستجو کنیم و بر اساس آن معدل امتیازات را بدست آوریم .
    ممنونم اگر راهنمایی فرمایید .

  • مجید
    Posted at 09:30h, 08 نوامبر پاسخ

    باسلام
    روش میانگین گیری اعداد نمونه زیر چگونه است
    65%
    65.2%
    118%
    118.2%

    • یاسین اسماعیل پور
      Posted at 15:04h, 10 نوامبر پاسخ

      سلام
      فرض کنید این اعداد در سلول های a1 تا a4 قراردارند. فرمول شما میشه:
      =average(a1:a4)

  • Homa
    Posted at 13:59h, 15 آگوست پاسخ

    سلام
    من یک سری داده برای 40 سال دارم که به صورت روزانه هست. حالا میخوام میانگین بودجه روزانه ام رو (که مقادیرش در ستون B هست)، نه برای تمام روزهای این بازه بلکه فقط برای ماه‌ مشخصی در این 40 سال داشته باشم.(تاریخ ها ستون A هست)
    چه‌طور باید شرط بنویسم که میانگین مقادیری که میخوام رو فقط در بازه‌ی این ماه حساب کنه برام.
    ممنون. لطف می‌کنید اگر پاسخ دهید.

  • حسین
    Posted at 07:30h, 15 آگوست پاسخ

    سلام. برای تعریف از پیش تعیین شده از میانگین و قرار دادن در hyperlink از ستون داده هایی که هر سل خودش میانگین یک ردیف دیگر است و تعداد آنها بین 50 تا 500 متغیر است، از AVERAGE A استفاده میکنم اما خروجی آن عدد نيست. چکار کنم؟

  • rezchro99
    Posted at 11:41h, 16 جولای پاسخ

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

    • محمد
      Posted at 14:54h, 04 دسامبر پاسخ

      سلام و احترام.
      نحوه محاسبه پنج عدد نزدیک به میانگین در یک بازه عددی چگونه است؟
      ممنون از سایت فوق العاده تون.

  • سینا
    Posted at 16:39h, 01 نوامبر پاسخ

    سلام . اگر یک سری متغیر کیفی داشته باشیم ( مثلا وضعیت جوی: ابری ، برفی ، آفتابی و بارانی ) و به هر کدام یک عدد نسبت دهیم (1و2و3و4) و بخواهیم برای آنها میانگین و انحراف معیار بگیرم ( 1 ابری و 0 در غیر این صورت )و به همین ترتیب برای گروه های دیگر ( 2 برفی 0 در غیر این صورت ) باید در اکسل از چه مراحلی استفاده کرد

  • محمد صالحی
    Posted at 13:20h, 06 آگوست پاسخ

    برای میانگین تاریخ شمسی، بعد از تبدیل کردن number به Date، کافیه مثلاً 1395 رو بکنید 1995، یعنی به 3 رو تبدیل به 9 کنید، بعد از تابع AVERAGE، میانگین سل های مورد نیاز تون رو حساب کنید.
    جواب مورد نظر تون به دست میاد و بعدش فقط نیاز هست که 1995 جواب رو دوباره برگردونید به 1395.
    همین 🙂

  • پرهام
    Posted at 22:05h, 23 مارس پاسخ

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

    • hesam
      Posted at 13:10h, 16 نوامبر پاسخ

      iferor

      • یاسین اسماعیل پور
        Posted at 15:29h, 18 نوامبر پاسخ

        مرسی حسام جان

  • محمد
    Posted at 00:24h, 28 فوریه پاسخ

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

  • eliiiiii
    Posted at 13:41h, 28 ژانویه پاسخ

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

  • ali
    Posted at 19:41h, 02 دسامبر پاسخ

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

  • tahere
    Posted at 10:20h, 22 نوامبر پاسخ

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

  • Zohreh
    Posted at 10:59h, 24 اکتبر پاسخ

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

    • یاسین اسماعیل پور
      Posted at 14:01h, 31 اکتبر پاسخ

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

  • sepehr
    Posted at 14:50h, 17 سپتامبر پاسخ

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

    • یاسین اسماعیل پور
      Posted at 00:34h, 19 سپتامبر پاسخ

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

  • afsan
    Posted at 17:38h, 12 سپتامبر پاسخ

    مرسی واقعا عالیییییییییی

  • احسان
    Posted at 11:36h, 23 آگوست پاسخ

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

    • مر
      Posted at 15:54h, 25 دسامبر پاسخ

      این خب میانگینه مگه ؟!

  • SHIVA
    Posted at 20:15h, 09 ژوئن پاسخ

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

    • یاسین اسماعیل پور
      Posted at 20:33h, 09 ژوئن پاسخ

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

  • ehsan
    Posted at 09:48h, 06 ژوئن پاسخ

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

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

  • آموزش ماکرو نویسی در اینجا
    Posted at 13:32h, 21 می پاسخ

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

  • salehi1354
    Posted at 11:03h, 21 می پاسخ

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

Post A Comment