سه فرمول پرکاربرد در اکسل^(Excel) که محاسبات ساده ریاضی را انجام می دهند عبارتند از COUNT , SUM و AVERAGE . چه در حال مدیریت بودجه مالی در اکسل^(Excel) باشید یا صرفاً تعطیلات بعدی خود را پیگیری کنید، احتمالاً قبلاً از یکی از این عملکردها استفاده کرده اید.

در این مقاله، ما به بررسی اصول اولیه این سه تابع و مشابه‌های مرتبط و مفید آن‌ها می‌پردازیم: COUNTIFS ، SUMIFS و AVERAGEIFS^{(SUMIFS and AVERAGEIFS)} .

Excel COUNT، SUM و AVERAGE

برای اینکه بدانید چند گوشی موبایل فروخته ایم، می توانیم به سرعت از فرمول COUNT مطابق شکل زیر استفاده کنیم:^(COUNT)

=COUNT(E2:E16)

از طرف دیگر، برای بدست آوردن مجموع فروش هایی که انجام داده ایم، می توانیم از فرمول SUM مطابق شکل زیر استفاده کنیم:^(SUM)

=SUM(E2:E16)

در نهایت، برای اطلاع از میانگین فروش ما برای همه گوشی‌ها، می‌توانیم از فرمول AVERAGE به صورت زیر استفاده کنیم:^(AVERAGE)

=AVERAGE(E2:E16)

نتیجه باید به صورت زیر باشد:

فرمول های COUNT^(COUNT) , SUM و AVERAGE^{(SUM and AVERAGE formulas)} فقط برای رکوردهایی کار می کنند که مقدار سلول^{(cell value)} در قالب عددی باشد^{(number format)} . هر رکوردی در محدوده فرمول^{(formula range)} (به عنوان مثال E2:E16 در این مثال) که در قالب شماره^{(number format)} نباشد نادیده گرفته خواهد شد.

بنابراین، لطفاً مطمئن شوید که تمام سلول‌های موجود در فرمول COUNT ، SUM و AVERAGE همگی به‌عنوان ^{(SUM and AVERAGE formula)}شماره^(Number) قالب‌بندی شده‌اند ، نه متن^(Text) . سعی کنید از همان فرمول استفاده کنید، اما به جای E2:E16 ، E:E را به عنوان محدوده استفاده کنید . همان نتیجه قبلی را برمی‌گرداند زیرا هدر (یعنی قیمت فروش^{(Sale Price)} ) را که در قالب متن است نادیده می‌گیرد.

حال، اگر بخواهیم تعداد فروش، مقدار کل فروش و میانگین میزان فروش هر گوشی را فقط برای گوشی های فروخته شده در ایالات متحده^(USA) بدانیم، چه ؟ اینجاست که COUNTIFS ، SUMIFS و AVERAGEIFS^{(SUMIFS and AVERAGEIFS)} نقش مهمی دارند. فرمول زیر را رعایت کنید:

COUNTIFS

تفکیک فرمول:

1. =COUNTIFS( – “=” شروع یک فرمول در سلول را نشان می دهد و COUNTIFS اولین بخش از تابع اکسل است^{(Excel function)} که ما از آن استفاده می کنیم.
2. D2:D16 - به گستره ای از داده ها اشاره^(Refers) دارد تا بررسی شود که آیا معیارهای گنجانده شدن در فرمول شمارش^{(count formula)} را برآورده می کند یا خیر .
3. "USA" - معیارهایی^(Criteria) برای جستجو در محدوده داده^{(data range)} مشخص شده ( D2:D16 )
4. ) – بسته شدن براکت^{(Closing bracket)} نشان دهنده پایان فرمول است.

فرمول 6 را برمی گرداند که تعداد فروش محصولات ارسال شده از انبار ایالات متحده^{(USA warehouse)} است.

SUMIFS

تفکیک فرمول:

1. =SUMIFS( – “=” دوباره شروع فرمول را نشان می دهد.
2. E2:E16 - به گستره ای از داده هایی اشاره^(Refers) دارد که ما می خواهیم جمع آوری کنیم، یعنی قیمت فروش^{(sale price)} در مثال ما.
3. D2:D16 - به گستره ای از داده ها اشاره^(Refers) دارد تا بررسی شود که آیا معیارهایی را که باید در مقدار کل گنجانده شود مطابقت دارد یا خیر.
4. "USA" - معیارهایی^(Criteria) برای جستجو در محدوده داده^{(data range)} مشخص شده ( D2:D16 )
5. ) – بسته شدن براکت^{(Closing bracket)} نشان دهنده پایان فرمول است.

این فرمول کل فروش $6,050 را نشان می دهد که برای محصولات ارسال شده از انبار ایالات متحده^{(USA warehouse)} انجام شده است.

AVERAGEIFS

تفکیک فرمول:

1. =AVERAGEIFS( – “=” شروع فرمول را نشان می دهد.
2. E2:E16 - به گستره ای از داده هایی اشاره^(Refers) دارد که می خواهیم میانگین آن ها را محاسبه کنیم. در این مثال، می‌خواهیم میانگین میزان فروش همه گوشی‌های فروخته شده در ایالات متحده^(USA) را بدست آوریم .
3. D2:D16 - به گستره ای از داده ها اشاره^(Refers) دارد تا بررسی شود که آیا معیارهای گنجانده شدن در فرمول میانگین را برآورده می کند یا خیر.
4. "USA" - معیارهایی^(Criteria) که باید در محدوده داده های^{(data range)} مشخص شده جستجو کنید
5. ) – براکت بسته^{(Closing bracket)} که انتهای فرمول را نشان می دهد.

این فرمول نشان می دهد که ما این محصول را به قیمت حدود $1,008 برای هر گوشی در ایالات متحده فروختیم.

هر سه فرمول می توانند بیش از یک معیار داشته باشند. به عنوان مثال، اگر بخواهیم ارقام مشابه (یعنی COUNT ، SUM و AVERAGE ) را برای محصولات فروخته شده در ایالات متحده^(USA) ، اما به طور خاص فقط برای  برند سامسونگ^(Samsung) بدانیم، فقط باید محدوده داده را اضافه کنیم تا بررسی شود و معیارهای آن بررسی شود.

لطفاً^(Please) مثال زیر را ببینید که در آن یک معیار دوم به بررسی معیارهای اولیه اضافه شده است. ( متن آبی^{(Blue text)} نشان دهنده معیار اول و قرمز نشان دهنده معیار دوم است)

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")

=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

متوجه خواهید شد که اکسل^(Excel) همچنین دارای  فرمول های COUNTIF^(AVERAGEIF) ، SUMIF و AVERAGEIF^(COUNTIF) بدون پسوند "S" است. آنها مشابه COUNTIFS ، SUMIFS و AVERAGEIFS استفاده می شوند . با این حال، کسانی که پسوند "S" در فرمول ندارند محدودیت دارند که فقط یک معیار را در هر فرمول مجاز کنند.

از آنجایی که نحو کمی متفاوت است، توصیه می‌کنم از  COUNTIFS ، SUMIFS و AVERAGEIFS فقط استفاده کنید، زیرا در صورت لزوم می‌توان از آن برای یک یا چند معیار استفاده کرد. لذت بردن!

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Τhe three most commonly used formulas in Excel that perform simple mathematical calculations are COUNT, SUM and AVERAGE. Whether you are managing a financial budget in Excel or simply keeping track of your next vacation, you’ve probably used one of these functions before.

In this article, we’re going to go through the basics of these three functions and their relevant and useful counterparts: COUNTIFS, SUMIFS and AVERAGEIFS.

Excel COUNT, SUM and AVERAGE

To know how many mobile phones that we have sold, we can quickly use the COUNT formula as shown below:

=COUNT(E2:E16)

On the other hand, to get the total amount of sales that we have made, we can use the SUM formula as shown below:

=SUM(E2:E16)

Lastly, to find out the average sales that we made for all phones, we can use the AVERAGE formula as below:

=AVERAGE(E2:E16)

The result should be as below:

COUNT, SUM and AVERAGE formulas will only work for records where the cell value is in number format. Any record within the formula range (i.e. E2:E16 in this example) not in the number format will be ignored.

So, please ensure that all cells within the COUNT, SUM and AVERAGE formula are all formatted as Number, not Text. Try to use the same formula, but with E:E as the range instead of E2:E16. It will return the same result as before because it ignores the header (i.e. Sale Price), which is in text format.

Now, what if we want to know number of sales, total amount of sales and the average amount of sales per phone, just for those sold in USA? This is where COUNTIFS, SUMIFS and AVERAGEIFS play an important role. Observe the formula below:

COUNTIFS

Formula breakdown:

1. =COUNTIFS( – The “=” indicates the beginning of a formula in the cell and COUNTIFS is the first part of the Excel function that we are using.
2. D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the count formula.
3. “USA” – Criteria to look for in the data range specified (D2:D16)
4. ) – Closing bracket indicating the end of the formula.

The formula returns 6 which is the number of sales for products shipped from the USA warehouse.

SUMIFS

Formula breakdown:

1. =SUMIFS( – The “=” indicates the beginning of the formula again.
2. E2:E16 – Refers to range of data that we would like to total, i.e. sale price in our example.
3. D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the total amount.
4. “USA” – Criteria to look for in the data range specified (D2:D16)
5. ) – Closing bracket indicating the end of the formula.

The formula shows $6,050 total sales that were made for products shipped from the USA warehouse.

AVERAGEIFS

Formula breakdown:

1. =AVERAGEIFS( – The “=” indicate the beginning of formula.
2. E2:E16 – Refers to range of data that we would like to average. In this example, we want to get the average amount of sales for all phones sold in the USA.
3. D2:D16 – Refers to range of data to check to see if it satisfies the criteria to be included in the average formula.
4. “USA” – Criteria to look for in the data range specified
5. ) – Closing bracket indicating the ends of the formula.

The formula shows we sold the product for around $1,008 per phone in USA.

All three formula can take more than one criteria. For example if we want to know the same figures (i.e. COUNT, SUM and AVERAGE) for products sold in USA, but specifically only for the Samsung brand, we just need to add the data range to be checked followed by its criteria.

Please see example below where a second criteria is added to the initial criteria checks. (Blue text indicates the first criteria and red indicates the second criteria)

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")

=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

You will notice that Excel also has COUNTIF, SUMIF and AVERAGEIF formulas without the suffix “S”. Those are used similar to COUNTIFS, SUMIFS and AVERAGEIFS. However, those without the suffix “S” in the formula have the limitation of only allowing one criteria per formula.

As the syntax is slightly different, I would recommend using COUNTIFS, SUMIFS and AVERAGEIFS only as it can be used for either one criteria or more, if necessary. Enjoy!

Related posts

• اضافه کردن Regression Trendline خطی به Excel Scatter Plot

• چگونه می توان یک Histogram در Excel

• چگونه به صورت امن Password Protect Excel File

• چگونه به ایجاد یک Flowchart در Word and Excel

• چگونه Create Labels در Word از Excel Spreadsheet

• نحوه مقایسه دو فایل اکسل و برجسته کردن تفاوت ها

• یک کاربرگ اکسل را در Word Doc قرار دهید

• نحوه فیلتر کردن داده ها در اکسل

• نحوه ایجاد چک لیست در اکسل

• چگونه برای ایجاد Distribution List در Outlook

• چگونه به ایجاد Gantt Charts در Microsoft Excel

• چگونه به ایجاد یک Drop Down List در Excel

• قالب بندی سلول ها با استفاده از قالب بندی شرطی در اکسل

• آموزش مبانی مایکروسافت اکسل – آموزش استفاده از اکسل

• چگونه به Group Worksheets در Excel

• 40 بهترین میانبر صفحه کلید مایکروسافت اکسل

• تنظیم خودکار عرض ستون و ارتفاع ردیف در اکسل

• نحوه یافتن مقادیر منطبق در اکسل

• نحوه حذف، شکستن یا شکستن رمز عبور فراموش شده اکسل XLS

• نحوه اضافه کردن و چاپ تصاویر پس زمینه اکسل