تابع 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,”96/02/12″)
آرگومان چهارم: ستونی است که شرط دوم ما در آن وجود دارد. یعنی ستون تاریخ.
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,”96/02/12″)
آرگومان پنجم: شرط دوم ما، یعنی تاریخ ۱۲/۰۲/۹۶ است. که هم می توان به سل ارجاع داد و هم مستقیم در تابع نوشت. به این صورت: “۱۲/۰۲/۹۶”
=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,“96/02/12”)
مثال ۲: می خواهیم میزان فروش های بالای ۴۰۰۰۰ که در ماه بهمن سال ۹۵ ثبت شده اند را محاسبه کنیم.
در شکل ۲ مشاهده می کنید برای اینکه همه تاریخ های بهمن ماه ۹۵ را در محاسبه دخیل کنیم، از ترفند * که در تابع آموزش تابع 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 دیگر را بیاورد.
سلام
برای انجام این کار دو راه دارید:
۱- استفاده از Power Query و ادغام Query های ساخته شده از روی جداول
۲- جمع Sumif های مختلف در یک سلول که از روی هر جدول نوشته میشه.
سلام من یه سوال دارم.
وقتتی یسری دیتا در یک شیت دارم که روی اونها شروط خاصی دارم میخوام براساس اون شرایط یسری جمع مثلا در ستون اعداد ائن محصوص هام زده بشه اما در شیت دیگر. از چه تابعی استفاده میشه؟
من هرچی از تابع sumifs ,و ارجاع اون به شیت اطلاعاتم استفاده کردم نتئنستم موفق بشم. کسی میتونه کمکم کنه/؟
درود بر شما
از همون تابع sumif باید استفاده کنید
برای لینک به یک شیت دیگه مقاله زیر رو بخونید ببینید کجا رو دارید اشتباه انجام میدید
https://excelpedia.net/excel-external-link/