همه چیز در مورد توابع averageif و averageifs
تابع averageif چیست و در کجا استفاده می شود؟
حل مثال های مختلف و کاربردی از تابع averageif
تابع averageifs چیست و کاربرد آن چیست؟
حل مثال های مختلف و کاربردی از تابع averageifs
تابع averageif چیست و چه کاربردی دارد؟
تابع averageif ترکیبی از تابع If و تابع average (میانگین)می باشد .
و میانگین اعدادی که یک شرط خاصی دارند را در اکسل محاسبه می کند .
فرمول تابع بشکل زیر می باشد :
AVERAGEIF(range;criteria;[average_range])=
(میانگین اعداد ;شرط;محدوده شرط)AVERAGEIF=
دو آرگومان اول این تابع اجباری است (Range و Average_range) و آرگومان آخرکه در داخل کلوشه نوشته شده اختیاری است .
براي آشنايي با کاربرد If هاي تو در تو در محاسبات ماليات بر حقوق مي توانيد از کاربرد اکسل در حسابداري استفاده نماييد .
حل مثال های مختلف و کاربردی از تابع averageif
برای مثال می خواهیم میانگین نمراتی را بدست آوریم که تعداد واحد آنها 3 واحدی می باشد .
افرادی که واحد مربوط به آنها 3 است شامل محمد ، امیر محمد و محمد است که در شکل فوق با رنگ زرد مشخص شده است.
روش کار با تابع میانگین شرطی
سلولی که قرار است میانگین در آن محاسبه شود را انتخاب می کنیم .
عملگر = را نوشته و نام تابع Averageif را می نویسیم . پرانتز را باز می کنیم .
ابتدا محدوده شرط رادر قسمت Range با درگ انتخاب سپس علامت “;” و یا “,” (بستگی به ورژن نرم افزار اکسل دارد در ورژن 2016 از “;” استفاده می شود )
سپس شرط تابع را Criteria وارد می کنیم .
در مرحله بعد بازه اعدادی که میانگین آنها قرار است محاسبه شود در قسمت Average_Range وارد می کنیم و پرانتز را می بندیم .
و در آخر دکمه Enter ازکی بورد را فشار می دهیم تا نتیجه میانگین شرطی را ببینیم.
range:
در این بازه مقادیری را انتخاب می کنیم که شرط تابع روی این محدوده اعمال شده است .
در این مثال ، ستون تعداد واحد را از خانه D2 تا D8 را با درگ انتخاب می کنیم.
این آرگومان اجباری می باشد .
ورودی این آرگومان می تواند مقادیر متنی و عددی و … را به عنوان ورودی بپذیرد.
criteria:
شرط تابع را دراین قسمت می نویسیم .
در این مثال ، شرط تابع کسانی که تعداد واحد آنها 3 است .
پس عدد 3 را در این قسمت می نویسیم .
این آرگومان اجباری می باشد .
نکته :درقسمت شرط می توانیم از مقادیر عددی ، متنی،عملگر مقایسه ای کوچکتر(“>”)،کوچکتر مساوی (“=>”)،بزرگتر(“<“)، بزرگتر مساوی (“=<“)، مساوی(“=”)، نامساوی(“<>”) و توابع استفاده کنیم .
نکته : هنگام استفاده از مقادیر متنی و عملگرهای مقایسه ای حتما باید مقادیر در بین ” ” قرار بگیرند مانند “محمد” ،”13>”
نکته : هنگام استفاده از مقادبر عددی در شرط نیازی به استفاده از ” ” نمی باشد. همانند مثال بالا که تعداد واحد 3 شرط است ودر بین ” ” قرار نگرفته است .
average_range:
بازه ای را انتخاب می کنیم که میانگین اعداد آن بازه قرار است محاسبه می شود .
در مثال فوق ، ستون نمره را از خانه C2 تا C8 را با درگ انتخاب می کنیم .
این آرگومان اختیاری است.اما در این مثال باید حتما مقداری را وارد کنیم .
نکته: در صورتیکه بازه مقادیر آرگومان اول (Rang) و آرگومان آخر(Average_range) یکسان باشند می توانیم آرگومان آخر را ننویسیم .
در نهایت فرمول تابع فوق بصورت زیر کامل می شود .
AVERAGEIF(D2:D8;3;C2:C8)=
مثال دوم : میانگین نمرات افرادی که نمره کوچکتر و مساوی 14 دارند را می خواهیم محاسبه کنیم .
افرادی که داری این شرط می باشند به ترتیب محمد، علی،حمید هستند که در شکل فوق با رنگ سبز مشخص شده است .
روش کار با تابع میانگین شرطی :
ابتدا سلول مناسب مثلا D12 را انتخاب می کنیم عملگر مساوی را نوشته و عبارت Averageif را می نویسیم.
پرانتز را باز می کنیم و برای آرگومان های تابع به ترتیب زیر ،مقادیر مناسب را وارد می کنیم .
و در آخر پرانتز را می بندیم و از کی بورد دکمه Enter را می زنیم .
Range:
در این قسمت بازه مقادیر شرط را وارد می کنیم که در اینجا شامل ستون نمرات می باشد.
و از خانه C2 تا C8 را با درگ انتخاب می کنیم .
criteria:
در این قسمت شرط تابع را وارد می کنیم.شرط تابع مقادیر کوچکتر و مساوی 14 می باشد .
و بصورت “14=>” می نویسیم.
چون در مثال فوق از عملگر های مقایسه ای استفاده شده باید آنها را در بین ” ” بنویسیم .
Average_range:
مقدار این آرگومان در این مثال همانند آرگومان اول (Range) از خانه C2 تا C8 می باشد پس می توانیم آن را خالی بگذاریم .
زیرا این آرگومان اختیاری است .
در پایان فرمول تابع بصورت زیر می شود :
AVERAGEIF(C2:C8;”<=14″)=
که بصورت زیر هم صحیح می باشد :
عملگر مقایسه ای را در بین “=>” می نویسیم و با عملگر & آن را به 14 وصل می کنیم .
AVERAGEIF(C2:C8;”<=”&14)=
اگر هم در نوار فرمول روی Fx کلیک کنیم می توانیم مانند شکل زیر ،تمام آرگومان های تابع را کامل مشاهده کنیم .
مثال سوم :میانگین نمرات افرادی که نام آنها شامل محمد می شوند را محاسبه کنید ؟
افرادی که دارای شرط فوق هستند طبق شکل به رنگ نارنجی مشخص شده است.
روش کار با تابع میانگین شرطی :
ابتدا سلول مناسب مثلا D10 را انتخاب می کنیم عملگر مساوی را نوشته و عبارت Averageif را تایپ می کنیم.
پرانتز را باز می کنیم و آرگومان های تابع را به ترتیب زیر، وارد می کنیم .
و در پایان بعد از پر کردن مقادیر آرگومان ها پرانتز را می بندیم و دکمه Enter از کی بورد را می فشاریم.
Range:
محدوده ای که شرط روی این محدوده اعمال می شود .
در مثال فوق ، شامل ستون نام از خانه B2 تا B8 می باشد .
Criteria:
کسانی که نام آنها شامل محمد است یعنی قبل و بعد از نام محمد ممکن است اسامی دیگری مثل محمد علی و یا امیر محمد و… باشد.
پس شرط فوق را بصورت “*محمد*” می نویسیم یعنی می تواند قبل و بعد از نام محمد ، اسامی دیگری قرار گیرد و در کل محمد جزیی از اسامی می باشد.
نکته :هر جایی که از عملگر * استفاده شده به این معنی است که هر تعداد کاراکتر می تواند بجای علامت * بکار رود در این مثال * قبل و بعد از نام محمد نوشته شده است یعنی هر تعداد حرف می تواند قبل و بعد از نام محمد بکار رود .
هر علامت * می تواند جایگزین هر تعداد کاراکتر شود .
Average_range:
شامل بازه ای از مقادیر است که می خواهیم معدل آنها را محاسبه کنیم.
در مثال فوق ،شامل ستون نمره ازخانه C2 تا C8 را انتخاب می کنیم .
و چون بازه مربوط به این آرگومان با مقادیر بازه آرگومان اول (Range)فرق می کند باید حتما این آرگومان ،مقادیر ورودی بگیرد و نمی تواند مانند مثال قبل خالی باشد .
در پایان فرمول مثال فوق بصورت زیر کامل می شود .
(C2:C8;”*محمد*”;B2:B8)AVERAGEIF=
مقادیر متنی در قسمت شرط باید حتما در بین ” ” باشند .
تابع averageifs چیست و کاربرد آن چیست؟
تابع averageifs همانند تابع Averageif اکسل است با این تفاوت که می تواند تا چندین شرط (127شرط ) را به آن اعمال کرد در صورتیکه تابع Averageif فقط یک شرط به آن اعمال می شود .
فرمول تابع میانگین چند شرطی بصورت زیر می باشد :
Averageifs(Average_range;Criteria_range1;Criteria1;[Criteria_range2;Criteria2];…)=
(….;[شرط2 ;محدوده شرط2 ];شرط 1;محدوده شرط 1,محدوده میانگین)Averageifs=
3 آرگومان اول اجباری است و بقیه آرگومان ها که داخل کلوشه هستند ،اختیاری می باشد .
حل مثال های مختلف و کاربردی از تابع averageifs
برای مثال میانگین نمراتی را می خواهیم محاسبه کنیم که تعداد واحد آنها یک و نام آنها 4 حرفی باشد و نمرات آنها بزرگتر مساوی 16 باشد
افرادی که شروط فوق را دارند با رنگ سبز به نام های حمید و مجید در شکل مشخص شده اند .
روش کار با تابع میانگین چند شرطی:
سلولی که قرار است میانگین چند شرطی در آن محاسبه شود را انتخاب می کنیم .
عملگر = را نوشته و نام تابع Averageifs را می نویسیم پرانتز را باز می کنیم .
و آرگومان های تابع را بصورت زیر وارد می کنیم.
و در آخر پرانتز را بسته و از کی بورد دکمه اینتر را می زنیم
Average_range:
محدوده ای که شامل میانگین اعداد می باشد.
در این مثال معدل نمرات ، شامل ستون نمره از خانهC2 تا C8 است .
Criteria_range1:
شامل محدوده ای که شرط اول تابع روی آن اعمال می شود.
یعنی افرادی که تعداد واحد آنها یک است پس ستون تعداد واحد از خانه D2 تا D8 را انتخاب می کنیم .
Criteria1:
شرط اول تابع را وارد می کنیم یعنی عدد 1 را در این قسمت می نویسیم.
Criteria_range2:
شامل محدوده ای که شرط دوم تابع روی آن اعمال می شود.
شرط دوم تابع در مورد اسامی که 4 حرفی هستند پس محدوده آن شامل ستون نام ازخانه B2 تا B8 می باشد.
Criteria2:
شرط دوم تابع را در این قسمت وارد می کنیم.
یعنی افرادی که نام آنها 4 حرفی است و به صورت “????” می نویسیم .
هر علامت سوال”?” می تواند جایگزین یک حرف شود .
پس برای نام های 4 حرفی چهار تا علامت سوال می نویسیم .
نکته: اگر شرط شامل تعداد کاراکتر معین مثلا 4 حرفی یا 6 حرفی باشد از ؟ استفاده می کنیم
هر علامت سوال جایگرین یک کاراکتر می تواند باشد.
Criteria_range3:
شرط سوم درباره نمرات بزرگتر مساوی 16 می باشد پس محدوده این شرط شامل ستون نمره از خانه C2 تا C8 می باشد .
Criteria3:
شرط سوم تابع نمرات بزرگتر مساوی 16 است که به صورت “16=<” می نویسیم.
البته به این صورت هم می توان نوشت 16&”=<”
در پایان فرمول مثال فوق بصورت زیر تکمیل می شود .
AVERAGEIFS(C2:C8;D2:D8;1;B2:B8;”????”;C2:C8;”>=16″)=
و در آخر روی سلولی که میانگین چند شرطی را محاسبه کردیم کلیک کنیم و روی دکمه fx از نوار فرمول کلیک کنیم تا تمام آرگومان های تابع را مانند شکل زیر ببینیم .
