تابع Sumifs اکسل | تکنیک های کاربردی در اعمال شروط
تابع Sumifs اکسل ، محاسبه جمع برخی از اطلاعات در یک مجموعه داده
همانطور که در آموزش تابع Sumif شرح دادیم، توابع جمع شرطی در یک محدوده، با توجه به شرط های مشخصی که از سوی ما تعیین می شود، عمل جمع را انجام می دهند. این توابع در اکسل خیلی مورد استفاده قرار می گیرند و تابع Sumif (برای یک شرط) و تابع Sumifs اکسل (برای بیش از یک شرط) برای این مسئله اختصاص داده شده است. مثلا اگر بخواهیم در یک بانک اطلاعاتی جمع فروش یک محصول خاص را استخراج کنیم. یا جمع ساعات مرخصی یک کارمند را از بین لیست مرخصی ها محاسبه کنیم، از Sumif استفاده میکنیم چرا که یک شرط را بررسی میکنیم. اما اگر بخواهیم جمع فروش یک محصول را در یک تاریخ خاص محاسبه کنیم، باید از Sumifs استفاده کنیم چرا که دو شرط داریم، یکی محصول و دیگری تاریخ مورد نظر.
تابع Sumifs از نسخه ۲۰۰۷ به بعد به اکسل اضافه شده است. پس وجود یک شرط در تابع Sumifs هم معادل تابع Sumif هست (درواقع نیازی به وجود تابع Sumif نیست). اما تابع Sumif جهت حفظ سازگاری با نسخ قبلی در اکسل موجود است.
در ادامه آرگومان های تابع Sumifs را تشریح میکنیم:
Sum_Range: محدوده ای که عمل جمع بر روی آن انجام می شود.
Criteria_Range1: محدوده ای که شرط اول ما در آن موجود است.
Criteria1: شرط اول. (با ثبت شرط اول، آرگومان های مربوط به شرط های بعدی ظاهر می شوند.)
دقت داشته باشید که جای آرگومان Sum-Range در تابع Sumif و Sumifs متفاوت است. همچنین، این آرگومان در تابع Sumifs اختیاری نیست و حتما باید درج شود.
با ذکر چند مثال این تابع را شرح می دهیم:
مثال ۱: بانک اطلاعاتی مربوط به فروش محصولات مختلف و مبالغ فروش در تاریخ های مختلف موجود است. می خواهیم جمع فروش محصول ۲ را در تاریخ ۱۲/۰۲/۹۶ محاسبه کنیم. طبق شکل ۱ تابع Sumifs را می نویسیم.
شکل ۱- نحوه ثبت تابع Sumifs اکسل
آرگومان اول: ستونی که عملیات جمع بر روی آن انجام می شود. ستون میزان فروش یا محدوده B2:B20 انتخاب می شود.
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,”96/02/12″)
آرگومان دوم: ستونی است که شرط اول ما در آن وجود دارد. یعنی ستون نام محصول.
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,”96/02/12″)
آرگومان سوم: شرط اول ما، یعنی کلمه محصول۲ است. که هم می توان به سل ارجاع داد و هم مستقیم در تابع نوشت. به این صورت: “محصول۲”
=SUMIFS(B2:B20,A2:A20,“محصول۲”,C2:C20,”96/02/12″)
آرگومان چهارم: ستونی است که شرط دوم ما در آن وجود دارد. یعنی ستون تاریخ.
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,”۹۶/۰۲/۱۲″)
آرگومان پنجم: شرط دوم ما، یعنی تاریخ ۱۲/۰۲/۹۶ است. که هم می توان به سل ارجاع داد و هم مستقیم در تابع نوشت. به این صورت: “۱۲/۰۲/۹۶”
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,“۹۶/۰۲/۱۲”)
مثال ۲: می خواهیم میزان فروش های بالای ۴۰۰۰۰ که در ماه بهمن سال ۹۵ ثبت شده اند را محاسبه کنیم.
در شکل ۲ مشاهده می کنید برای اینکه همه تاریخ های بهمن ماه ۹۵ را در محاسبه دخیل کنیم، از ترفند * که در تابع آموزش تابع Countifs شرح دادیم استفاده نمودیم.
شکل ۲- تابع Sumifs اکسل
حالا همین مسئله را با این شرط که هم فروش بالاتر از ۴۰۰۰۰ باشد و هم در بهمن همه سال ها اتفاق افتاده باشد ، حل کنید و سوالات خود را در ادامه همین پست مطرح بفرمایید.
سلام ی فایل داشتم میخوام با فرمول SUMIFS از شیت دیگه جمع بزنم، فرمول که میزنم تو شیت جدید جمعش ی عدد میاد، همون اعداد تو شیتی که اعداد هست جمعش ی عدد دیگه میشه . اگر امکانش هست فایلش هم بفرستم چک کنین . ممنون میشم
درود
امکان بررسی فایل نداریم
میتونید سوالتون و داخل گروه تلگرامی مطرح کنید
سلام وقت بخیر
دریک شیت چندین Table با کالاهای مختلف و مقدار مصرف متفاوت داریم
حال اگر بخواهیم در یک جدول در شیت دیگر با زدن کد کالای خاص مجموع مقادیر مصرف از چند table دیگر را بیاورد.
سلام
برای انجام این کار دو راه دارید:
۱- استفاده از Power Query و ادغام Query های ساخته شده از روی جداول
۲- جمع Sumif های مختلف در یک سلول که از روی هر جدول نوشته میشه.
سلام من یه سوال دارم.
وقتتی یسری دیتا در یک شیت دارم که روی اونها شروط خاصی دارم میخوام براساس اون شرایط یسری جمع مثلا در ستون اعداد ائن محصوص هام زده بشه اما در شیت دیگر. از چه تابعی استفاده میشه؟
من هرچی از تابع sumifs ,و ارجاع اون به شیت اطلاعاتم استفاده کردم نتئنستم موفق بشم. کسی میتونه کمکم کنه/؟
درود بر شما
از همون تابع sumif باید استفاده کنید
برای لینک به یک شیت دیگه مقاله زیر رو بخونید ببینید کجا رو دارید اشتباه انجام میدید
https://excelpedia.net/excel-external-link/
با سلام و خسته نباشید، ممنون از اطلاعات که در سایت گذاشتید.
من سوالی داشتم خدمتتان ، من یک ستون تاریخ از جنس date دارم و یک ستون هم مبلغ های مختلف در این تاریخ ها،
در یک شیت دیگر ، کدی میخوام بنویسم که در بازه زمانی X تا Y جمع مبلغ ها را به من برگردونه
[…] مطالعه بیشتر به مقاله”تابع Sumifs اکسل ، محاسبه جمع برخی از اطلاعات در یک مجموعه …” مراجعه […]
سلام.
من برای حل یک سوال به مشکل خوردم امیدوارم شما بتونید کمکم کنید اونم این بود که من دو ستون دارم از اعداد ۱ تا ۱۰۰ به ترتیب و در ستون دیگه که به این اعداد مربوطه و روبروش قرار گرفته ۱۰۰ عدد دیگه وجود داره
حالا من میخوام به طور ثابت همیشه از عدد مثلا بیستم(متغیر) تا اخرین عدد جمع بزنم اما نمیخوام عدد (۲۰) رو توی فرمول بیارم و میخوام اگه امکان داره عدد ۲۰ رو توی یک سلول بنویسم و ادرس اون سلول رو توی فرمول بگذارم تا سریعتر به نتیجه برسم چون کارم به سرعت عمل بالایی نیاز داره.
مثل (“sumif(A1:A100;”>20
اما میخوام بجای عدد (۲۰) ادرس یک سلول رو بدم تا عدد ۲۰ رو از اون تو بخونه و مستقیم بگذاره توی فرمول اما نمیشه
شما برای مشکل من هم راهکار دارید؟؟؟
سلام، از فرمول زیر استفاده کنید:
با فرض اینکه عددی که میخواید در سلول C1 نوشته شده باشد:
این فرمول جمع اعداد بزرگتر از مقداری که در سلول C1 نوشته شده رو در ستون A به شما میده. اما اگر قصد دارید که جمع اعداد متناظرش در ستون روبه رویی رو بدست بیارید باید عبارت B1:B100 رو در آرگومان آخر تابع SUMIF قرار بدید.
سلام
ممنون از مطالب مفیدتون
می خاستم بپرسم مثلا در همین مثالی که خودتون زدید اگر بخواهیم جمع فروش محصول ۲ را در تاریخ ۱۲/۰۲/۹۶ یا ۲۵/۰۲/۹۶ محاسبه کنیم. شکل تابع Sumifs چگونه میشه؟ منظورم قرار دادن شزط یا بین دو عبارت در یکی از خانه های شرط تابع است.
ممنون میشم راهنمایی کنید
درود بر شما
مشابه این سوال در لینک زیر حل شده
https://excelpedia.net/excel-jalali-date/
سلام خسته نباشید من وقتی عدد میدم در شرط تابع، تابع عمل میکند ولی وقتی به یک سلول ارجاع میدم تابع عمل نمیکند میخاستم ببینم ممکنه راهنمایی بفرمایید؟
درود بر شما
قاعدتا نباید مشکلی باشه.
تابعی که نوشتید رو بذارید تا بررسی بشه
برای ارجاع به یک سلول باید مابین شرط و سلول مورد نظر از & استفاده کنید
(sumifs(a1:a10,b1:b10,”>”&c1,d1:d10,”<"&e1=
تابع بالا محدوده a1 تا a10 را به شرطی که محدوده b1 تا b10 بزرگتر از مقدار داده شده در سلول c1 و محدوده d1 تا d10 کوچکتر از مقدار داده شده در سلول e1 باشد جمع می زند.
خودم هم خیلی دنبال این نکته بودم تا تونستم راه حل رو پیدا کنم
سلام. فرض کنید من میخواهم با استفاده از تابع sumifs جمع فروش محصولات خودم رو در ماه های مختلف سال بدست بیاورم. برای این منظور ماه مربوطه را در یک سلول مینویسم و مقدار فروش کالای x در آن ماه در سلول جلوی آن می آید. من میخواهم که اگر نام ماه را ننوشتم تابع sumifs از آن شرط عبور کند و فروش کل سال آن کالا را برای من حساب کند. چگونه این کار ممکن است؟
سلام
کافیه در آرگومان مربوط به تعریف شرط ماه، از تابع IF استفاده کنید که بررسی کنه چیزی درون سلول ماه نوشته شده یا نه و … .
در واقع خروجی تابع IF تعیین میکنه که چه چیزی به عنوان شرط در تابع Sumifs استفاده بشه.
سلام. درک نمیکنم چطوری باید if رو بنویسم چون من ۱۲ ماه دارم. چکار کنم که در خروجی if من ۱۲ ماه سال در نظر گرفته شود تا تابع sumifs بتواند فروش تمام ماه ها را برگرداند
درود
باید در criteria که اسم ماه نوشته میشه این فرمول رو بنویسید:
با فرض اینکه شرط در سلول A1 نوشته بشه.
در واقع داریم میگیم، اگر سلول A1 خالی بود، علامت <> بذار. این علامت یعنی همه. در غیر اینصورت، همون A1 رو بذار.
سلام
تابعی در یک فایل دارم که تابع sumifs را به شکل زیر نوشته است:
SUMIFS(F$3:F$42;D$3:D$42;H6;C$3:C$42;”>=”&H$3;C$3:C$42;””
در این رابطه ۲ سوال داشتم:
۱. چرا فقط علامت کوچک تر مساوی را در ” گذاشته است؟
۲. نقش & چیست؟
۳. برای آدرس مطلق یک سلول مگر نباید بنویسیم ۳$I$ ؟ وقتی به این شکل می نویسم خطا می دهد. و فقط به شکل بالا جواب می دهد.
ممنون
درود بر شما
۱- همه چیز غیر از تابع، سلول و عدد باید داخل “” باشه….
۲- & کار اتصال رو انجام میدم یعنی میگه بزرگتر از محتوای اون سلول
۳- مطلق کردن، ۴ حالت داره که در این پست کاملا توضیح داده شده:
https://excelpedia.net/cell-address/
سلام ، وقت بخیر، لطفا برای همین مثالی که در مورد sumifs زدید به عنوان مثال برای فاصله بین دو تاریخ ۹۶/۰۲/۱۲ الی ۹۶/۰۲/۱۶ جواب بدست بیارید ممنون میشم جواب من را بدید
باتشکر
درود بر شما. این موضوع یکی از سوالات مهم هست. خدمت شما:
البته حتما باید تاریخ ها بصورت عدد نوشته بشه (عین همین شیوه که داخل فرمول نوشته شده)
پیشنهاد میکنم حتما این پست رو بخونید.
کامل توضیح داده شده:
https://excelpedia.net/excel-jalali-date/
حالا همین مسئله را با این شرط که هم فروش بالاتر از ۴۰۰۰۰ باشد و هم در بهمن همه سال ها اتفاق افتاده باشد ، حل کنید و سوالات خود را در ادامه همین پست مطرح بفرمایید.
امیدوارم جواب درست داده باشم؟؟
یکی از راه های خوب یادگیری اکسل همینه که مسائل مختلف رو خودتون تعریف کنید و سعی کنید بهترین روش رو برای حل اون مسئله پیدا کنید. این یه جور بازی هست که نتیجش یادگیری عمیق اکسله.
بله کاملا درسته
مرسی
عالی بود