تابع AVERAGEIF اکسل و نحوه استفاده از آن

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

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

خب اول ببینیم این تابع چه پارامترهایی داره و چطور باید نوشته بشه:

AVERAGEIF( محدوده میانگین گیری[اختیاری] ,شرط, محدوده چک کردن شرط)

آشنایی با پارامترهای تابع AVERAGEIF اکسل:

  • محدوه چک کردن شرط: این پارامتر مشخص میکنه که ما میخوایم شرطمون رو در چه سلولهایی چک کنیم.
  • شرط: این پارامتر مشخص میکنه که سلول های موجود در محدوده مورد نظر در صورت داشتن چه شرطی باید در میانگین گیری لحاظ بشن. این پارامتر میتونه یک عدد مثل 10، یک متن مثل قطعه 1 یا یک عبارت مثل “0=” باشه. در صورتی که این پارامتر عبارت باشه باید حتما بین دو علامت نوشته بشه.
  • محدوده میانگین گیری: تعیین این پارامتر اختیاری هست. از این پارامتر زمانی استفاده میکنیم که محدوده چک کردن شرط با محدوده ای که میخوایم میانگینش رو بگیریم متفاوت باشه. مثلاً ما بخوایم میانگین نمرات ( ستون نمرات محدوده میانگین گیری هست) یک دانش آموز خاص (ستون نام دانش آموز محدوده چک کردن شرط هست) رو بدست بیاریم. اگر محدوده میانگین گیری و محدوده چک کردن شرط یکسان باشه نیازی به تعریف این نیست. مثلا بخوایم میانگین نمراتی ( ستون نمرات محدوده میانگین گیری هست) که بالاتر از 15 هستند (همون ستون نمرات محدوده چک کردن شرط هست) رو بدست بیاریم.

مثالی برای نحوه استفاده از تابع AVERAGEIF اکسل:

جدول زیر رو در نظر بگیرید:

A B C
1 دانشجو درس نمره
2 محمدی ریاضی 18
3 حسنی ریاضی 16
4 رضایی ریاضی 13
5 جوان ریاضی 17
6 نقوی ریاضی 20
7 محمدی شیمی 13
8 حسنی شیمی 20
9 رضایی شیمی 20
10 جوان شیمی 17
11 نقوی شیمی 17
12 محمدی انگلیسی 20
13 حسنی انگلیسی 19
14 رضایی انگلیسی 12
15 جوان انگلیسی 13
16 نقوی انگلیسی 15

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

اولین قدم اینه که محدوده چک کردن شرط رو تعیین کنیم.

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

تعیین شرط: ما میخوایم معدل آقای حسنی رو بدست بیاریم پس شرط ما اینه که ستون نام دانشجو برابر با حسنی باشه. بنابراین پارامتر دوم تابع ما “=حسنی” هست.

محدوده میانگین گیری: ما میخوایم میانگین نمرات رو به عنوان معدل حساب کنیم. نمرات در ستون C قرار دارند پس محدوده میانگین گیری ما ستون C هست. بنابراین پارامتر سوم ما C:C خواهد بود.

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

AVERAGEIF(A:A;”=حسنی”;C:C)

امیدوارم این مطلب هم مورد استفاده شما قرار گرفته باشه.

دانلود فایل مربوط به تابع AVERAGIF اکسل:

AVERAGEIF.sample.worksheet.ir

70 thoughts on “تابع AVERAGEIF اکسل و نحوه استفاده از آن”

  1. جواد مسعودی فر

    با سلام و عرض ادب
    این تابع در اکسل ۲۰۱۳ هست ولی در اکسل های ۲۰۱۰ و ۲۰۰۷ همچنین تابعی نداریم و برای استفاده در اکسل های ۲۰۱۰ و ۲۰۰۷ می بایست از توابع برداری استفاده کرد تا نتیجه ای مانند نتیجه تابع AverageIf حاصل بشه .

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

      سلام جواد جان. فکر میکنم اشتباه میکنیا. ما در اکسل 2010 و اکسل 2007 هم این تابع رو داریم.

      1. سلام آقا یاسین خسته نباشی من میخوام از تابع if در اکسل استفاده کنم مشابه نمونه های موجود هر کاری میکنم خطا میده چکار کنم ممنون

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

          سلام مهدی جان. فایلی که خطا داره رو از طریق فرم تماس برام بفرست بررسی میکنم

  2. سلام اقای اسماعیل پور میش ایمیلتون بدید من فردا امتحان دارم و هیچی بلد نیستم:(

  3. سلام
    اگه بخوام تو قسمت شرط بخوام که اعدادی که صفر نیستن رو چک کنه باید چه دستوری بنویسم ؟

  4. جعفر سهرابی 09143215689

    با سلام حضور استاد گرامی ما میخواهیم برای کارمندان با داشتن شرطهائی تخفیف ساعات کاری اعطا کنیم
    مثلا کارمندی که صفر تا چهار سال سابقه دارد از تخفیف 1 ساعت در هفته برخوردار میشود
    و همچنین اگر همین کارمند با سختی کار 8 تا 25 درصد بخش مربوطه انجام وظیفه کنه شامل 0تا 375 امتیاز و از کسر ساعت نیم ساعته در هفته بهره مند میشه
    و این قانون بهره وری است
    سئوال اینست که اگر ما بخواهیم لیست کارمندان را در جدولی لیست کرده وداده های سابقه و سختی کار بخش را به آن بدهیم آیا اکسل درمحاسبه ساعت کسری کار ماهانه ما را یاری میکنه ؟
    و آیا میتوان تعریف کرد که اکسل دامنه ای از اعداد را مد نظر قرار دهد مثلا در یک سلول بنویسیم که اگر این سلول از 0تا300 باشد در سلول دیگر عدد 2 بنویسد؟

    1. جناب سهرابی عزیز ، سلام خدمت شما و تمامی همکاران زحمتکشتان
      توانایی نرم افزار قدرتمند اکسل بیش از این مواردست!!
      قطعاً اکسل شما را تنها نخواهد گذاشت و همچون گذشته یاری تان خواهد کرد!!
      فرض کنیم شما جدولی را نیاز داشته باشید که شامل ستونهای زیر باشد :
      شماره ردیف ، کد کارمندی ، نام و نام خانوادگی کارمند ، سابقه کاری ، سختی کار ، امتیاز سختی کار ، تخفیف سابقه ، تخفیف سختی ، جمع تخفیف ها
      طبق توضیحات شما ، تا ستون سختی کار ، اطلاعات ورودی محسوب میشود ، اما ستونهای بعدی طبق شرایط و ضوابطی که شما
      تعیین میکنید ، قابل محاسبه خواهد بود
      برای محاسبه ستونهای بعدی ، حداقل سه راه متفاوت وجود دارد :
      1- با فرمول نویسی در سلولهای مورد نظر و استفاده از توابع و اعدادثابت و ضرایب مربوطه
      2- درج اعداد و ضرایب شرایط و ضوابط در جدولی دیگر و خواندن نتیجه از آن جدول
      3- محاسبه نتیجه با تعریف تابع User Defined با ماکرونویسی
      من فکر میکنم ، روش دوم برای دوستان ملموس تر و فراگیری و انجام آن از همه ساده تر باشد
      شما جداول فوق را تنظیم نمایید ، شرایط و ضوابط و اعداد و ضرایب خود را ، بررسی و بطور دقیق تر بیان نمایید
      امیدوارم بتوانم در راهنمایی های بعدی و دقیق تر بازهم پاسخگوی شما باشم
      موفق باشید – خانیکی
      0919-302-6452

  5. با سلام. ميخام معدل يك سري از اعداد را بگيرم ولي اينكه بزرگترين و كوچكترين عدد حساب نشود. چه فرمولي بدم؟؟

  6. با سلام. ميخام معدل يك سري از اعداد را بگيرم که شامل نمرات چندین امتحانی بوده که گرفته شده ولي اينكه نمره امتحان رو با تاثیر 80 درصد و نمره حل تمرین رو با تاثیر 20 درصد جمع بزنه و نمره نهایی رو بیرون بده. چه فرمولي بدم؟؟

  7. سلام
    ممنون از توضیحات عالیتون
    من میخوام در شرط گذاری رنگ قرار دهم مثلا خانه هایی که کوچکتر از100 هستند قرمز در غیر اینصورت آبی شوند ممنون میشم راهنمایی کنید

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

  9. سلام.5 ستون 20 ردیفه با محتوای درصد داریم که جمع هر ردیف حتما 100 درصد هست ، اگربخواهیم در اکسل میانگینی از این ستونها باز به درصد داشته باشیم چه راهی هست ؟ ممنون میشم راهنمایی بفرمایید.

  10. سلام.خسته نباشید.
    میشه بگید میانگین سنی رو چطور میشه گرفت .مثلامیانگین سن افراد از 9 ماه تا 93 سال رو چطوری میشه به دست اورد.ممنون و تشکر.

  11. سلام.خسته نباشید.
    میانگین سنی رو میخوام از 20روزه و چند ماهه و چند ساله بگیرم.ببینید منظورم اینه وقتی میزنم 10خوب باید بین روز و ماه وسال تمایز باشه یاباید جدا حساب کنم؟اینشو نمیدونم چطور ثبت کنم. نمیدونم منظورم خوب گفتم یا نه.

  12. با سلام و خسته نباشید من یک فایل ششصد هزار رکوردی دارم که میخوام میانگین سه عدد متوالی رو بگیرم،مثلا میانگین ردیف 1 تا 3 بعد میانگین 4 تا 6 بعد 7 تا 9و…میشه بگین چطوری این کارو انجام بدم؟مرسی ممنون

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

  14. سلام خسته نباشید
    اگه بخواهیم تعداد درس مشروط شده .تعداد دروس بانمره عالی .درس گذرانده بالاترین نمره را بگیریم چجوری است

  15. سلام
    ببخشید من همین فرمول رو برای اسامی فارسی که به کار میبرم، خطای !0/DIV# رو میده ولی وقتی از اسامی انگلیسی استفاده کنم خطا نمیده!
    خواستم بدونم راه حلی برای رفع این مشکل وجود داره؟ در ضمن ورژن نرم افزار اکسلم 2016 هست.

  16. با عرض سلام
    خواهشمندم در خصوص نحوه کسر تاریخ راهنمایید فرمایید
    مثلا گواهی ارزش افزوده شرکتی تا تاریخ 96/03/01 اعتبار دارد وحال که 95/11/06 میباشد چند روز از آن اعتبارگواهی باقی مانده
    ممنون

  17. با سلام و خسته نباشید.
    میخواستم بدونم نحوه درصد گیری یک پارامتر خاص چگونه است؟
    مثلا من یک سند دارم که تعداد مختلف میوه در آن تکرار شده است.مثل سیب،گلابی،انار و…
    حالا می خواستم بدونم چند درصد مثلا میوه سیب در آن سند وجود دارد؟

  18. امیرحسین

    سلام.می خواستم بدانم چگونه میتوانم کاری کنم که معدل نهایی ای که در اکسل بدست می آورم بالاتر از 20 نشود و خود اکسل این کار را انجام دهد.باتشکر

  19. سلام میشه من و کمک کنید
    میخوام یه فرمول بنویسم که میانگین چندتا عدد و بنویسه بعد اگر بالای نیم بود با بالا رند کنه اگه نه به پایین رند کنه مثلا 1.67بشه 1.7و 1.33 بشه 1 میشه راهنمایی کنید چجوری بنویسم؟

  20. با عرض سلام و خسته نباشید به شما
    میخاستم بپرسم در یک جدول کارنامه ای که معدلش رو با استفاده از فرمول بدست میارم بعد از معدل باید “وضعیت” رو بدست بیارم ولی نمیدونم چجوری وضعیت بدست میاد!؟

  21. سلام خسته نباشيد ميخواستم متوسط وزن يك سري عدد رو به صورت +_ بدست بيارم.البته اينجا نميشه درست تايپ كرد + بالاي _ هست.خيلي ممنونم

  22. عابد ذکریایی

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

  23. سلام از اینکه تخصصتان را در اختیار دیگران می گزارید متشکرم
    لطفا فرمولی بدهید که بتوان مثلا 20 % عدد 4 میلیون را از مبلغ 6 میلیون را بدهد

  24. سلام
    آیا این امکان در اکسل وجود دارد که بتوان عدد میانگین “منطقی” از بین تعدادی عدد که احتمال دارد فاصله بین بعضی از آن اعداد از مابقی بیشتر یا کمتر باشد بدست آورد
    مثال :
    1.000.000
    2.500.000
    600.000
    4.000.000
    10.000.000
    از بین اعداد مذکور دو عدد 600.000 و 10.000.000 فاصله زیادی با مابقی اعداد دارند حال میخواهم با فرمولی فقط میانگین بین اعداد نزدیک به هم محاسبه گردد که در مثال بالا میانگین 2.500.000 خواهد شد

  25. مریم جوانمردی

    سلام خسته نباشید ببخشید من میخواستم برای اکسل یک شرط بزارم مثلا اعدادی که بالای ۱۴هستند کنارشون یک سمبل سبز رنگ بزاره اگر نمره زیر ۱۰گرفته اند یک ایکن دیگه میشه راهنمایی کنید باید چیکار کنم؟ممنون

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

      سلام
      برای اینکار باید از conditional formatting استفاده کنید. آموزشش در سایت موجود هست

  26. درود و وقت به خیر
    لطفا در صورت امکان سوالات من رو پاسخ بدید:
    اول اینکه چطور میشود در یک جدول شرطی رو تعریف کرد که حاصل کسر عدد بر صفر یا صفر بر عدد رو بصورت صفر جواب بده(فرض کنیم در =C2/C3 یکی از مقادیر C2 یا C3 صفر باشند می خوام جواب این کسر صفر نوشته بشه و خطا نده)
    دوم اینکه در فرمول AVERAGEIF اگر “محدوه چک کردن شرط” شامل خطای Div یا Value بود، فرمول از اون سلول ها در محاسبه ی میانگین صرف نظر کنه
    خیلی ممنونم

    1. می بخشید در سوال اولم منظورم تقسیم عدد صفر بر صفر و عدد بر صفر بود
      باز هم متشکرم

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

      سلام. مشکلتون رو میتونید با تابع iferror حل کنید. آموزشش تو سایت هست میتونید مطالعه کنید

      1. از ISNUMBER و IF استفاده کردم اما فکر میکنم IFERROR بهتر باشه
        ممنونم از پاسخگویی شما

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

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

      شما باید از تابع sumproduct استفاده کنید و و نتیجه رو بر جمع وزن ها تقسیم کنید

  28. سلام خسته نباشید میانگین اعداد بیشتر از ۲۰ رو چطوری محاسبه کنم؟

  29. ولی متاسفانه نتونستم انجام بدم اگه برام توضیح بدین ممنون میشم

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

      خواهش میکنم.فرض کنید اعداد شما در ستون a قرار داشته باشه. در اینصورت میشه:
      =averageif(a:a;”>20″)

      1. سلام خسته نباشید
        اگر در اکسل بخوایم از تابع Averageif استفاده کنیم و بگوییم میانگین چند سلول را به جز سلول هایی که صفر هستند را بگیرد چگونه باید این فرمول را نوشت ؟ ممنون میشم فرمول را بنویسید

  30. مصطفی زاهدی راد

    با سلام
    اگر بخواهیم از جدول بالا میانگین حسنی و رضایی را باهم محاسبه کنیم چه کنیم؟

  31. سلام آقای اسمایل پور. غرض از مزاحمتم.. امکانش هست نحوه محاسبه انتروپی شبکه در اکسل رو ازتون راهنماییم بگیرم؟.یعنی، فقط میشه بگید چه جوری میتونم در اکسل یک شاخص رو محاسبه کنم بدون استفاده از ماشین حساب؟! ممنون میشم

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

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

  32. سلام لطفا بفرمایید چگونه در میانگین گرفتن یک مقدار خاص رو مستثنی کنیم؟ ینی مثلا هروقت مقدار صفر بود جمع نشه در میانگین یاهروقت * گذاشتم خودش حساب نکنه

  33. سلام و وقت بخیر
    می خواستم برای کسانی که در رتبه ۱ تا هستند تخفیف قائل بشم. به نظرتون از چه تابعی باید استفاده کنم. آیا تابع ترکیبی است؟

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

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top