آیا می خواهید تجزیه و تحلیل داده ها را در اکسل ساده کنید؟ نحوه استفاده از تابع SUMIFS برای جمع بندی سریع معیارهای خاص در صفحات گسترده خود را بیاموزید.
لیست توابع اکسل یکی از پویاترین عناصر در تجزیه و تحلیل داده ها است. از عملیات محاسباتی فانتزی گرفته تا منطق شرطی پیشرفته، برای هر نوع کاربری چیزی کمی وجود دارد.
تابع SUMIFS یکی از بسیاری از توابع موجود برای کاربرانی است که مایل به انجام تجزیه و تحلیل دقیق بر اساس منطق شرطی هستند. اگر با تابع SUMIF در اکسل آشنا هستید، متوجه خواهید شد که تابع SUMIFS کاملاً شبیه همتای خود است. در اینجا چیزی است که باید در مورد استفاده از تابع SUMIFS در اکسل بدانید.
نحو یک تابع SUMIFS
تابع SUMIFS در اکسل نقطه اوج توابع SUM و IF است. مقادیر موجود در یک سلول را بر اساس معیارهای خاص جمع می کند. می توانید شرایط مربوط به تاریخ، اعداد و متن را ایجاد کنید. علاوه بر این، از عملگرهای منطقی برای دستکاری شرایط خود و گسترش بیشتر آنها استفاده کنید.
برای تسلط بر این فرمول های منطقی، باید بدانید که چگونه از تابع IFS استفاده کنید، قبل از اینکه آن را با هر توابع جمع آوری ترکیب کنید.
اکسل دارای توابع منطقی دیگری مانند COUNTIF، COUNTIFS، SUMIF، MAXIFS، MINIFS و بسیاری دیگر است. هنگام کار با هر یک از این توابع، باید اطمینان حاصل کنید که دستور درست را در فرمول ها ارسال می کنید.
در اینجا نحو تابع SUMIFS آمده است:
=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)
جایی که:
- sum_range: محدوده/ستون برای جمع بر اساس معیارهای شما
- range1: Range1 ستونی را مشخص می کند که فرمول شما اولین مجموعه معیارها را در آن پیدا می کند
- معیار1: ملاک 1 به شرط اول اشاره دارد
- range2: Range2 ستونی را تعریف می کند که فرمول شما مجموعه معیارهای دوم را در آن پیدا می کند
- معیار2: ملاک 2 به شرط دوم اشاره دارد
نحوه استفاده از تابع SUMIFS در اکسل
اکنون که سینتکس از بین رفته است، درک اجزای مختلف تابع و اینکه چگونه می‌توانید به بهترین شکل از آن استفاده کنید ضروری است. تابع SUMIF کاربردهای زیادی در دنیای داده دارد، اما این بدان معنا نیست که تابع SUMIFS دور است. شما می توانید چندین شرط را در فرمول SUMIFS بگذرانید و تابع SUMIF را یک درجه بالا ببرید.
آماده سازی داده ها
فرض کنید یک مجموعه داده حاوی اطلاعات محصولات فروخته شده در یک کارخانه اداری دارید. برخی از ستون های داده مربوطه شامل تاریخ سفارش، دسته بندی، رنگ، قیمت فروش، مقدار فروخته شده و کل فروش است. همه فرمول ها بر اساس مراجع داده موجود در این داده های نمونه خواهند بود.
تاریخ سفارش
دسته بندی
رنگ
قیمت فروش
تعداد
کل فروش
08-11-2016
تلفن ها
مشکی
907.152
5442.912
1395/06/12
کلاسورها
سبز
18.504
55.512
11-10-2015
لوازم خانگی
رنگ زرد
114.9
574.5
11-10-2015
جداول
رنگ قهوه ای
1706.184
15355.656
09-06-2014
تلفن ها
قرمز
911.424
3645.696
09-06-2014
کاغذ
سفید
15.552
46.656
09-06-2014
کلاسورها
مشکی
407.976
1223.928
09-06-2014
لوازم خانگی
رنگ زرد
68.81
344.05
09-06-2014
کلاسورها
سبز
2.544
7.632
09-06-2014
ذخیره سازی
نارنجی
665.88
3995.28
09-06-2014
ذخیره سازی
نارنجی
55.5
111
15-04-2017
تلفن ها
مشکی
213.48
640.44
05-12-2016
کلاسورها
سبز
22.72
90.88
22-11-2015
لوازم خانگی
سبز
60.34
422.38
22-11-2015
صندلی
قهوه ای تیره
71.372
142.744
11-11-2014
فن آوری
قابل اجرا نیست
1097.544
7682.808
13/05/2014
مبلمان
نارنجی
190.92
954.6
1. کار با شرایط متن
اگر می خواهید هزینه کل Green Binders را از این لیست محاسبه کنید، می توانید از فرمول SUMIFS به صورت زیر استفاده کنید:
=SUMIFS(F2:F18, B2:B18, "Binders",C2:C18, "Green")
ابتدا باید ستونی را که می خواهید مجموع ها را از آن دریافت کنید، تعریف کنید. در این مورد، ستون فروش کل است. محدوده ستونی را که دارای مقدار Binders است (ستون A) تعریف کنید. بخش بعدی فرمول به معیارها نیاز دارد، Binders.
اولین معیار تعیین شده است. برای تعریف بخش بعدی که رنگ است، باید قانون مشابهی را دنبال کنید. شما باید ستونی را که جزئیات رنگ را در خود نگه می دارد و به دنبال آن رنگ (سبز) را که می خواهید به دنبال آن بگردید قرار دهید.
اکسل مقادیر را بر اساس شرایط مشخص شده محاسبه می کند و نتیجه 154.024 را می دهد.
2. کار با شرایط عددی
در مثال بالا، شرایط متن (Color = سبز و Category = Binders) را تعریف کردید. حالا مرحله بعدی کار با شرایط عددی است. بیایید این را با یک مثال توضیح دهیم:
اگر می خواهید کل فروش محصولات با قیمت فروش بین 500 تا 1000 را محاسبه کنید. در اینجا می توانید فرمول را بسازید:
=SUMIFS(E2:E18, D2:D18, ">500", D2:D18, "<1000")
اولین محدوده فرمول بالا مرجع ستون کل فروش است. در قسمت بعدی باید ستونی را برای معیار اول تعریف کنید که مقادیر بالای 500 را می گیرد. در قسمت آخر باید معیار نهایی را تعریف کنید که مقادیر زیر 1000 را می گیرد.
نتیجه نهایی 13083.888 است.
3. کار با شرایط تاریخ
می‌توانید از تابع SUMIFS برای جمع‌آوری ارزش‌های شرایط تاریخ پایه استفاده کنید. برای مثال، اگر مجموعه‌ای از ستون‌ها با تاریخ دارید و می‌خواهید کل فروش را در یک بازه تاریخ پیدا کنید، می‌توانید از فرمول برای استخراج سریع نتیجه استفاده کنید.
=SUMIFS(F2:F18, A2:A18, ">1/1/2015", A2:A18, "<1/1/2016")
مانند فرمول های دیگر بالا، می توانید از تاریخ ها برای دستکاری داده های خود و جمع آوری کل ارزش های فروش استفاده کنید. قسمت اول فرمول به ستون فروش کل اشاره دارد. در بخش بعدی، باید محدوده تاریخ را تعریف کنید. برای دستکاری تاریخ ها در فرمول SUMIFS باید از عملگرهای منطقی استفاده کنید.
از آنجایی که مرجع شامل تاریخ های بین 01-01-2015 و 01-01-2016 می شود، نتیجه 16495.28 است.
4. استفاده از جستجوهای Wildcard
در نهایت، آخرین روش برای استفاده با تابع SUMIFS وجود دارد. شما می توانید به درستی تابع جستجوی حروف عام را با عملکرد SUMIFS چند منظوره ترکیب کنید تا مطابق با عناصر خاص پیدا کنید. برای مثال، فرض کنید می‌خواهید مجموع مقادیری را که شامل حروف ind در ستون Category و حروف la در ستون Color هستند، پیدا کنید.
تابع SUMIFS اکسل هر ستون مشخص شده را فیلتر می کند و به دنبال حروف مشخص شده می گردد. هر مقدار منطبق خلاصه می شود و نتیجه نمایش داده می شود. برای استفاده از جستجوی عام، می توانید از علامت * (آستریکس) یا ? نماد (علامت سوال) در فرمول شما.
در اینجا نحوه نوشتن فرمول آمده است:
=SUMIFS(F2:F18, B2:B18, "*ind*", C2:C18, "*la*")
نتیجه 1223.928 است.
استفاده کارآمد از تابع SUMIFS در اکسل
با وجود استفاده گسترده از تابع SUMIFS در اکسل، بسیاری از تحلیلگران همچنان بر پتانسیل واقعی این تابع قدرتمند تکیه می کنند. راه‌های زیادی وجود دارد که می‌توانید از این عملکرد چند وجهی در گزارش‌دهی و تحلیل منظم خود استفاده کنید.
مهم نیست که شما یک فریلنسر، یک مغازه دار یا یک تحلیلگر داده باشید، به احتمال زیاد همیشه راهی برای شما وجود خواهد داشت که از این عملکرد حداکثر استفاده را ببرید.