تابع 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,”۹۶/۰۲/۱۲″)
آرگومان دوم: ستونی است که شرط اول ما در آن وجود دارد. یعنی ستون نام محصول.
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,”۹۶/۰۲/۱۲″)
آرگومان سوم: شرط اول ما، یعنی کلمه محصول۲ است. که هم می توان به سل ارجاع داد و هم مستقیم در تابع نوشت. به این صورت: “محصول۲”
=SUMIFS(B2:B20,A2:A20,“محصول۲”,C2:C20,”۹۶/۰۲/۱۲″)
آرگومان چهارم: ستونی است که شرط دوم ما در آن وجود دارد. یعنی ستون تاریخ.
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,”۹۶/۰۲/۱۲″)
آرگومان پنجم: شرط دوم ما، یعنی تاریخ ۱۲/۰۲/۹۶ است. که هم می توان به سل ارجاع داد و هم مستقیم در تابع نوشت. به این صورت: “۱۲/۰۲/۹۶”
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,“۹۶/۰۲/۱۲”)
مثال ۲: می خواهیم میزان فروش های بالای ۴۰۰۰۰ که در ماه بهمن سال ۹۵ ثبت شده اند را محاسبه کنیم.
در شکل ۲ مشاهده می کنید برای اینکه همه تاریخ های بهمن ماه ۹۵ را در محاسبه دخیل کنیم، از ترفند * که در تابع آموزش تابع Countifs شرح دادیم استفاده نمودیم.
شکل ۲- تابع Sumifs اکسل
حالا همین مسئله را با این شرط که هم فروش بالاتر از ۴۰۰۰۰ باشد و هم در بهمن همه سال ها اتفاق افتاده باشد ، حل کنید و سوالات خود را در ادامه همین پست مطرح بفرمایید.
سلام وقت بخیر من وقتی فرمول sumifs رئ داخل داده ای که تو جدول وارد کردم مینویسم پسغام خطای spill بهم میده ممنون میشم راهنمایی کننید
؟
درود
معمولا خطای Spill به دلایل زیر ایجاد میشه:
محدوده‌های متداخل:
اگر محدوده‌ای که فرمول قصد نوشتن نتیجه در آن را دارد، با داده‌های دیگر یا حتی فرمول‌های دیگر همپوشانی دارد، ممکن است این خطا رخ دهد. بررسی کنید که محدوده‌ای که فرمول سعی در نوشتن در آن دارد خالی باشد.
استفاده نادرست از توابع ماتریسی:
اگر توابع ماتریسی که خروجی‌های چند سلولی دارند را در جایی که فضای کافی برای نمایش پاسخ وجود ندارد، وارد کنید، خطای SPILL دریافت خواهید کرد.
درست وارد نشدن محدوده‌ها:
اگر در فرمول SUMIFS از جداول استفاده می‌کنید، اطمینان حاصل کنید که از ارجاعات ساختاریافته (Structured References) استفاده کنید و مطمئن شوید تمامی محدوده‌ها درست وارد شده‌اند.
این موارد رو بررسی کنید.
تهران شعبه ۱ محصول ۱ بهار ۱۲۱۲
تهران شعبه ۱ محصول ۲ بهار ۲۴۵۳
تهران شعبه ۱ محصول ۳ بهار ۱۳۱۶
تهران شعبه ۱ محصول ۱ تابستان ۴۹۵۵
تهران شعبه ۱ محصول ۲ تابستان ۲۱۳۱
تهران شعبه ۱ محصول ۳ تابستان ۱۵۶۴
تهران شعبه ۱ محصول ۱ پاییز ۱۷۶۳
تهران شعبه ۱ محصول ۲ پاییز ۳۲۱۲
تهران شعبه ۱ محصول ۳ پاییز ۴۲۱۵
تهران شعبه ۱ محصول ۱ زمستان ۲۱۵۳
تهران شعبه ۱ محصول ۲ زمستان ۳۲۱۱
تهران شعبه ۱ محصول ۳ زمستان ۶۴۵۹
تهران شعبه ۲ محصول ۱ بهار ۱۶۴۵
تهران شعبه ۲ محصول ۲ بهار ۳۴۵۶
تهران شعبه ۲ محصول ۳ بهار ۴۲۱۶
تهران شعبه ۲ محصول ۱ تابستان ۵۲۱۳
تهران شعبه ۲ محصول ۲ تابستان ۱۲۱۴
تهران شعبه ۲ محصول ۳ تابستان ۱۲۴۵
تهران شعبه ۲ محصول ۱ پاییز ۲۴۶۵
تهران شعبه ۲ محصول ۲ پاییز ۲۷۸۹
تهران شعبه ۲ محصول ۳ پاییز ۳۲۱۵
تهران شعبه ۲ محصول ۱ زمستان ۳۲۵۵
تهران شعبه ۲ محصول ۲ زمستان ۲۶۷۸
تهران شعبه ۲ محصول ۳ زمستان ۲۲۴۴
تهران شعبه ۳ محصول ۱ بهار ۱۹۴۵
تهران شعبه ۳ محصول ۲ بهار ۴۲۱۳
تهران شعبه ۳ محصول ۳ بهار ۷۵۶۲
تهران شعبه ۳ محصول ۱ تابستان ۱۲۳۳
تهران شعبه ۳ محصول ۲ تابستان ۱۳۱۵
تهران شعبه ۳ محصول ۳ تابستان ۱۶۷۸
تهران شعبه ۳ محصول ۱ پاییز ۲۴۷۹
تهران شعبه ۳ محصول ۲ پاییز ۱۶۳۲
تهران شعبه ۳ محصول ۳ پاییز ۱۵۶۹۸
تهران شعبه ۳ محصول ۱ زمستان ۴۲۱۱
تهران شعبه ۳ محصول ۲ زمستان ۲۶۹۷
تهران شعبه ۳ محصول ۳ زمستان ۳۴۴۴
تبریز شعبه ۱ محصول ۱ بهار ۱۵۶۴
تبریز شعبه ۱ محصول ۲ بهار ۱۷۶۳
تبریز شعبه ۱ محصول ۳ بهار ۳۲۱۲
تبریز شعبه ۱ محصول ۱ تابستان ۴۲۱۵
تبریز شعبه ۱ محصول ۲ تابستان ۲۱۵۳
تبریز شعبه ۱ محصول ۳ تابستان ۳۲۱۱
تبریز شعبه ۱ محصول ۱ پاییز ۲۱۳۱
تبریز شعبه ۱ محصول ۲ پاییز ۱۵۶۴
تبریز شعبه ۱ محصول ۳ پاییز ۱۷۶۳
تبریز شعبه ۱ محصول ۱ زمستان ۱۳۱۶
تبریز شعبه ۱ محصول ۲ زمستان ۴۹۵۵
تبریز شعبه ۱ محصول ۳ زمستان ۲۱۳۱
تبریز شعبه ۲ محصول ۱ بهار ۱۲۴۵
تبریز شعبه ۲ محصول ۲ بهار ۲۴۶۵
تبریز شعبه ۲ محصول ۳ بهار ۲۷۸۹
تبریز شعبه ۲ محصول ۱ تابستان ۳۲۱۵
تبریز شعبه ۲ محصول ۲ تابستان ۳۲۵۵
تبریز شعبه ۲ محصول ۳ تابستان ۲۶۷۸
تبریز شعبه ۲ محصول ۱ پاییز ۱۲۱۴
تبریز شعبه ۲ محصول ۲ پاییز ۱۲۴۵
تبریز شعبه ۲ محصول ۳ پاییز ۲۴۶۵
تبریز شعبه ۲ محصول ۱ زمستان ۴۲۱۶
تبریز شعبه ۲ محصول ۲ زمستان ۵۲۱۳
تبریز شعبه ۲ محصول ۳ زمستان ۱۲۱۴
تبریز شعبه ۳ محصول ۱ بهار ۱۶۷۸
تبریز شعبه ۳ محصول ۲ بهار ۲۴۷۹
تبریز شعبه ۳ محصول ۳ بهار ۱۶۳۲
تبریز شعبه ۳ محصول ۱ تابستان ۱۵۶۹۸
تبریز شعبه ۳ محصول ۲ تابستان ۴۲۱۱
تبریز شعبه ۳ محصول ۳ تابستان ۲۶۹۷
تبریز شعبه ۳ محصول ۱ پاییز ۱۳۱۵
تبریز شعبه ۳ محصول ۲ پاییز ۱۶۷۸
تبریز شعبه ۳ محصول ۳ پاییز ۲۴۷۹
تبریز شعبه ۳ محصول ۱ زمستان ۷۵۶۲
تبریز شعبه ۳ محصول ۲ زمستان ۱۲۳۳
تبریز شعبه ۳ محصول ۳ زمستان ۱۳۱۵
سلام.وقت بخیر.ممنون میشم راهنمایی بفرمایید
فرمول واسه محاسبه فروش محصول ۱ و ۳ در تهران و تبریز چطور نوشته میشه؟
درود
ابتدا با استفاده از Text to Column بر اساس Space ستون ها رو جدا کنید. در مرحله آخر فرمت ستون آخر را روی عدد بذارید و پس از تفکیک ستون شعبه و عدد شعبه را مجددا بهم متصل کنید. نهایتا یا روی آن Pivot بزنید یا با استفاده از Sumif این مقدار رو محاسبه کنید.
دقت شود که این کار با Power Query هم انجام میشه.
با سلام خدمت اساتید گرامی؛
مشکل من اینه که می خوام توی criteria_range فرمول SUMIFS در یک شیت دیگه، فرمول های LEN و MID رو وارد کنم تا فیلد تاریخ رو به سال و ماه بشکونم. نمونه فرمول
(A2;[تاریخ]A1;table1;[تاریخ]table1;[مبلغ دریافتی]sumifs(table1=
سپاسگزارم
سلام ی فایل داشتم میخوام با فرمول SUMIFS از شیت دیگه جمع بزنم، فرمول که میزنم تو شیت جدید جمعش ی عدد میاد، همون اعداد تو شیتی که اعداد هست جمعش ی عدد دیگه میشه . اگر امکانش هست فایلش هم بفرستم چک کنین . ممنون میشم
درود
امکان بررسی فایل نداریم
میتونید سوالتون و داخل گروه تلگرامی مطرح کنید
سلام وقت بخیر
دریک شیت چندین Table با کالاهای مختلف و مقدار مصرف متفاوت داریم
حال اگر بخواهیم در یک جدول در شیت دیگر با زدن کد کالای خاص مجموع مقادیر مصرف از چند table دیگر را بیاورد.
سلام
برای انجام این کار دو راه دارید:
1- استفاده از Power Query و ادغام Query های ساخته شده از روی جداول
2- جمع 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;””
در این رابطه ۲ سوال داشتم:
1. چرا فقط علامت کوچک تر مساوی را در ” گذاشته است؟
2. نقش & چیست؟
3. برای آدرس مطلق یک سلول مگر نباید بنویسیم ۳$I$ ؟ وقتی به این شکل می نویسم خطا می دهد. و فقط به شکل بالا جواب می دهد.
ممنون
درود بر شما
۱- همه چیز غیر از تابع، سلول و عدد باید داخل “” باشه….
2- & کار اتصال رو انجام میدم یعنی میگه بزرگتر از محتوای اون سلول
3- مطلق کردن، ۴ حالت داره که در این پست کاملا توضیح داده شده:
https://excelpedia.net/cell-address/
سلام ، وقت بخیر، لطفا برای همین مثالی که در مورد sumifs زدید به عنوان مثال برای فاصله بین دو تاریخ ۹۶/۰۲/۱۲ الی ۹۶/۰۲/۱۶ جواب بدست بیارید ممنون میشم جواب من را بدید
باتشکر
درود بر شما. این موضوع یکی از سوالات مهم هست. خدمت شما:
البته حتما باید تاریخ ها بصورت عدد نوشته بشه (عین همین شیوه که داخل فرمول نوشته شده)
پیشنهاد میکنم حتما این پست رو بخونید.
کامل توضیح داده شده:
https://excelpedia.net/excel-jalali-date/
سلام وقت بخیر. بجای اینکه “۹۶۰۲۱۲” در تابع sumif بنویسیم، دو سلول مثلا k1 و k2 معرفی کنیم و کاربر تاریخ را خودش وارد کند. چکار باید کرد، ؟ به جای آن دو “k2” بنویسیم ؟ گویا جواب نمیده. تشکر
درود بر شما
چرا ج میده
باید به این شکل تغییر بدید:
“>”&K2
حالا همین مسئله را با این شرط که هم فروش بالاتر از ۴۰۰۰۰ باشد و هم در بهمن همه سال ها اتفاق افتاده باشد ، حل کنید و سوالات خود را در ادامه همین پست مطرح بفرمایید.
امیدوارم جواب درست داده باشم؟؟
یکی از راه های خوب یادگیری اکسل همینه که مسائل مختلف رو خودتون تعریف کنید و سعی کنید بهترین روش رو برای حل اون مسئله پیدا کنید. این یه جور بازی هست که نتیجش یادگیری عمیق اکسله.
بله کاملا درسته
مرسی
عالی بود