در نرم‌افزار اکسل برای محاسبه کردن میانگین، شمارش سلول‌ها و جمع زدن اعداد، می‌توان از توابع معمولی و همین‌طور توابع شرطی استفاده کرد. به عنوان مثال اگر بخواهیم سلول‌هایی حاوی عدد بزرگ‌تر از صفر را شمارش کنیم، تابع COUNTIFS یا COUNTIF به جای COUNT استفاده می‌شود و به همین‌صورت می‌توان از SUMIFS و AVERAGEIFS استفاده کرد. IFS در انتهای نام این توابع، به شرطی بودن اشاره می‌کند و حتی می‌توان به جای یک شرط، از چند شرط استفاده کرد. در حالت یک شرطی می‌توانید از AVERAGIF و SUMIF نیز استفاده کنید.
در این مقاله به روش محاسبه‌ی جمع شرطی، میانگین شرطی و شمارش شرطی در Excel مجموعه‌ی آفیس مایکروسافت می‌پردازیم.
به یک نمونه آمار ساده توجه کنید تا استفاده از توابع اشاره شده را با داده‌های موجود بررسی کنیم: در جدول زیر نام و برند برخی گوشی‌های رده‌اول و موقعیت انبار و قیمت فروش ذکر شده است:
استفاده از تابع جمع، شمارش و میانگین‌گیری در اکسل
برای شمارش تعداد گوشی‌های فروخته شده، می‌توان از تابع COUNT استفاده کرد. این تابع برای شمارش تعداد سلول‌ها کاربرد دارد. لذا به جای شمردن سلول‌های ردیف E، می‌توانید از این فرمول استفاده کنید:
=COUNT(E2:E16)
برای جمع زدن مقدار فروش نیز می‌توان از SUM استفاده کرد و ارقام موجود در سلول‌های ردیف E را جمع زد. فرمول موردنیاز به این صورت است:
=SUM(E2:E16)
و قیمت متوسط محصولاتی که فروخته شده نیز با تابع AVERAGE محاسبه می‌شود:
=AVERAGE(E2:E16)
لذا اگر جدول کوچکی برای بررسی آماری تعداد فروش، جمع فروش و متوسط قیمت ایجاد کنیم، نتیجه به این صورت است:
دقت کنید که در استفاده از این توابع، می‌بایست سلولی که عدد در آن درج شده، از نوع Number یا عددی باشد. به عبارت دیگر سلولی که عدد در آن تایپ شده اما فرمت آن متن ساده یا Text است، عدد محسوب نمی‌شود و در محاسبات لحاظ نمی‌شود.
لذا اگر در فرمول‌های ذکر شده، به جای رنج E2:E16 از E:E استفاده کنیم، نتیجه یکسان خواهد بود چرا که در تمام ستون E، یک سلول عنوان از نوع متنی و سایر سلول‌های غیرخالی از نوع عددی هستند.
اما اگر بخواهیم قیمت متوسط گوشی‌های فروخته شده از انبار یک کشور خاص مثل استرالیا یا آمریکا را محاسبه کنیم، چه باید کرد؟ در این حالت استفاده از توابع شرطی توصیه می‌شود. در ادامه به روش استفاده از هر یک از توابع موردنیاز می‌پردازیم.
آموزش شمارش شرطی با تابع COUNTIFS یا COUNTIF
در تابع COUNTIFS، اولین آرگومان، رنج یا محدوده‌ی سلول‌ها است. دومین آرگومان نیز شرط موردنظر است. بنابراین از فرمول زیر استفاده می‌کنیم تا تعداد گوشی‌های فروخته شده از انبار کشور USA شمارش شود:
=COUNTIFS(D2:D16,"USA")
همان‌طور که در فرمول فوق مشاهده می‌کنید، رنج سلول‌ها شامل سلول‌های ستون D که محل انبار است، می‌شود. اگر مقدار سلولی مساوی عبارت USA باشد، سلول شمارش می‌شود و در غیر این صورت شمارش نمی‌شود.
بدیهی است که می‌توانید از رنج D:D که معادل تمام ستون D است نیز استفاده کنید و نتیجه عدد ۶ خواهد بود.
محاسبه‌ی میانگین شرطی با تابع SUMIFS و SUMIF
تابع جمع زدن شرطی دارای سه آرگومان است. آرگومان اول، سلول‌های حاوی اعداد است که می‌بایست جمع زده شوند. آرگومان دوم سلول‌های حاوی داده‌ای است که در بررسی شرط استفاده می‌شوند و آرگومان سوم نیز شرط است.
در مثال ما، عددها در ستون E قرار گرفته و آنچه باید در شرط بررسی شود، در ستون D یا محل انبار است. اگر بخواهیم جمع فروش گوشی از انبار کشور USA را بررسی کنیم، فرمول به صورت زیر خواهد بود:
=SUMIFS(E2:E16,D2:D16,"USA")
با این فرمول اگر گوشی از انبار آمریکا فروخته شده باشد، قیمت آن به حاصل‌جمع اضافه می‌شود.
محاسبه‌ی میانگین به صورت شرطی با AVERAGEIFS یا AVERAGEIF
تابع میانگین‌گیری شرطی نیز درست مثل تابع جمع زدن شرطی است و سه آرگومان دارد. آرگومان اول همان سلول‌های ردیف E است که حاوی قیمت فروش است. آرگومان دوم سلول‌های لازم برای بررسی شرطی و آرگومان سوم به عنوان شرط کاربرد دارد.
بنابراین برای میانگین‌گیری از قیمت گوشی‌های فروخته شده در آمریکا، می‌توان از فرمول زیر استفاده کرد:
=AVERAGEIFS(E2:E16,D2:D16,"USA")
محاسبات شرطی با بررسی چند شرط در Excel
همان‌طور که در ابتدای مقاله اشاره کردیم، توابع شرطی که کلمه‌ی IFS در انتهای نامشان ذکر شده، می‌توانند چند شرط را نیز بررسی کنند و نتیجه را محاسبه کنند. به عنوان مثال اگر بخواهید میانگین قیمت گوشی‌هایی با برند Samsung که در USA فروخته شده را محاسبه کنید، می‌بایست به جای یک شرط از دو شرط استفاده کنید و تعداد AVERAGEIFS این کار را امکان‌پذیر می‌کند.
ترتیب آرگومان‌ها در حالت چندشرطی به این صورت است که ابتدا سلول‌های حاوی اعدادی که باید حاصل‌جمع یا میانگین یا ... محاسبه شود، ذکر می‌شود و سپس رنج اول و شرط اول و سپس رنج دوم و شرط دوم و به همین ترتیب شروط دیگر ذکر می‌شود.
در مورد تابع شمارش، رنج اول و شرط اول و رنج دوم و شرط دوم ذکر می‌شود. لذا تعداد گوشی‌های سامسونگ که در آمریکا فروخته شده، با فرمول زیر محاسبه می‌شود:
=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")
و برای محاسبه‌ی میانگین یا جمع اعداد، ابتدا سلول‌های ستون E و سپس رنج اول و شرط اول و در نهایت رنج دوم و شرط دوم ذکر می‌شود:
=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")
=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")
برای بررسی‌های تک‌شرطی می‌توانید از توابع ساده‌تر COUNTIF و SUMIF و AVERAGIF استفاده کنید که روش کار کاملاً مشابه است.
سیاره‌ی آی‌تی