تابع AVERAGEIFS در اکسل از خانواده توابع آماری (Statistical) است. عملکرد این تابع مشابه تابع میانگین شرطی AVERAGEIF است. تفاوت آنها در این است که تابع AVERAGEIFS میانگین اعداد را بر اساس چند شرط محاسبه میکند. یعنی اگر تمام شرطهای موردنظر برقرار باشند، اعداد را با هم جمع میکند و حاصل جمع آنها را بر تعداد آنها تقسیم میکند.
برای یادگیری بهتر میتوانید دو آموزش زیر را هم مطالعه کنید:
ساختار تابع AVERAGEIFS در اکسل
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2])
- Average_range: محدوده سلولهایی که میانگین آنها در صورت برقرار بودن شرط محاسبه میشود.
- Criteria_range1: اولین محدودهای که باید توسط شرط مربوط به خود، ارزیابی شود.
- Criteria1: اولین شرطی که باید بررسی شود. شرط شما میتواند شامل عدد، تاریخ، عملگر منطقی، آدرس سلول، کاراکترهای Wild card باشد.
نکته: ۳ آرگومان اول تابع ضروری هستند. Average_range2 و Criteria2 و محدودهها و شرطهای دیگر اختیاری هستند. در فرمولهای AVERAGEIFS میتوانید از ۱۲۷ جفت محدوده و شرط استفاده کنید.
مثال: عملکرد تابع AVERAGEIFS در اکسل
فرض کنید میخواهید یک تحقیق انجام دهید. به تعدادی افراد در رنج سنی ۴۵ تا ۷۵ پرسش نامه دادهاید و از آنها خواستهاید که پرسش نامه را پر کنند. حالا با توجه به پرسش نامهها میخواهید، میانگین سن افرادی که فشار خون آنها بالاتر از ۱۲۰ است و دیابت و بیماری قلبی دارند، به دست آورید. ابتدا باید دادههای خود را در اکسل وارد کنید. من هم این کار را انجام دادهام.
روی سلول نتیجه کلیک کنید. سپس در نوار فرمول نرمافزار روی fx کلیک کنید (یا اینکه کلیدهای Shift+F3 را فشار دهید)
پنجره Insert Function باز میشود. از دسته توابع آماری (Statistical) تابع AVERAGEIFS را پیدا کنید و روی OK کلیک کنید.
پنجره Function Arguments باز میشود. در کادر Average_range کلیک کنید. سلولهای A2 تا A8 را انتخاب کنید. سپس در کادر Criteria_range1 کلیک کنید. سلولهای B2 تا B8 را انتخاب کنید. در کادر Criteria1 کلیک کنید و شرط “۱۲۰<” را تایپ کنید. در کادر Average_range2 کلیک کنید و سلولهای C3 تاC8 را انتخاب کنید. در کادر Criteria2 “دارد” را تایپ کنید. در کادر Average_range3 کلیک کنید و سلولهای D3 تا D8 را انتخاب کنید. در کادر Criteria3 کلیک کنید و “دارد” را انتخاب کنید. در نهایت OK را بزنید.
نکته: اگر این دو کاراکتر به عنوان یک حرف به کار برده شوند، باید قبل از آنها علامت”~” را درج کنید. ولی زمانی که به عنوان یک کاراکتر Wild card به کار میروند، نیازی برای به کار بردن علامت “~” قبل از علامت * و ؟ نیست.
یادآوری: Wildcard کاراکتری است که در جستجوها، جانشین یک یا چند کاراکتر دیگر میشود. شما میتوانید از دو کاراکتر زیر در تابع AVERAGEIFS استفاده کنید.
- علامت ستاره(*): برای نشان دادن چند کاراکتر استفاده میشود.
- علامت سوال (؟): برای نشان دادن یک کاراکتر در یک قسمت مشخص استفاده میشود.
- علامت تیلدا(~): وقتی میخواهیم به اکسل بگوییم که علامت ستاره یا سوال استفاده شده، یک کاراکتر معمولی است و آن را به عنوان یک کاراکتر Wildcard در نظر نگیرد. برای مثال رشته “Po~?yan” مترادف با “Po?yan” در نظر گرفته میشود نه “Pooyan” یا “Pouyan” .
چند نکته مهم درباره تابع AVERAGEIFS
- اگر سلولی در محدوده شرطها خالی باشد، AVERAGEIFS آن را نادیده میگیرد.
- فقط در صورتی که تمام شرطها برقرار باشند، تابع AVERAGEIFS میانگین را محاسبه میکند.
- بر خلاف تابع AVERAGEIF، در تابع AVERAGEIFS باید محدوده شرطها دقیقا برابر محدوده Average_range باشند. اگر محدودهها برابر نباشند تابع AVERAGEIFS خطای#VALUE! را برمیگرداند.
- اگر محتوای سلولها به یک عدد قابل ترجمه نباشند، تابع AVERAGEIFS آن را نادیده میگیرد.
- اگر سلولی وجود ندارد که تمام شرطها را برآورده کند، تابع AVERAGEIFS خطای #DIV/0 را برمیگرداند.
جمعبندی
در این آموزش تابع AVERAGEIFS را توضیح دادیم و برخی نکات را درباره این تابع گفتیم. این تابع آخرین تابع از خانواده AVERAGE بود. امیدوارم از توابع پرکاربرد این خانواده استفاده کنید و به نتایج موردنظرتان برسید.
سلام ، مثال ساده و قابل درکی آوردید ، عالی بود ،
من خودم مدرس کاربرد نرم افزار اکسل در مالی و بازرگانی هستم ،
سه تا کتاب در این رابطه نوشتم ، اگر علاقمند به اکسل کاربردی هستید ، پیشنهاد می کنم آنها را مطالعه فرمائید.
سلام. ممنونم از توجه و دقت نظر شما.
چشم حتما.
ببخشید اگه شرط ما دو تا باشه چه باید کنیم. مثلا یه لیست داریم که ستون۱ سال هست، ستون۲ نام هس، ستون۳ نمره هست. اگه بخوایم میانگین نمره یه نفر رو تو سالهای مختلف بگیریم به شیوه ای که شما گفتید میشه انجام داد. اما اگه بخوایم مثلا یک فرد میانگین نمره اش رو توی سه تا سال مشخص بگیریم چه باید کرد؟ یعنی محدوده ستون۳ نمره رو به عنوان محدوده میانگین گیری مشخص میکنیم، ستون۲ محدوده شرط اول نام و ستون۱ محدوده شرط دوم انختاب میکنیم اما برای شرط دوم چند تا شرط داریم. میخوایم بگیم میانگین نمرات آقای محمدی توی سالهای ۱۳۹۰ و ۱۳۹۱ و ۱۳۹۲٫ به این شکل چظوری باید شرط رو تعریف کنیم؟
سلام
میخواهم تابع AVERAGEIFS مقادیر شرط ها را از سلول های دیگر بخواند، لطفا راهنمایی بفرمایید. سپاس