اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
info@excelpedia.net

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
  • درباره ما
    • Cart

      0

توابع اکسل

  • خانه
  • بلاگ
  • توابع اکسل
  • تابع Sumifs اکسل | تکنیک های کاربردی در اعمال شروط

تابع Sumifs اکسل | تکنیک های کاربردی در اعمال شروط

  • ارسال شده توسط حسنا خاکزاد
  • دسته بندی توابع اکسل
  • تاریخ ۳۰ بهمن ۱۳۹۵
  • نظرات ۲۶ دیدگاه ها
تابع Sumifs اکسل | آموزش
4.6 / 5 ( 12 امتیاز )

تابع Sumifs اکسل ، محاسبه جمع برخی از اطلاعات در یک مجموعه داده

همانطور که در آموزش تابع Sumif شرح دادیم، توابع جمع شرطی در یک محدوده، با توجه به شرط های مشخصی که از سوی ما تعیین می شود، عمل جمع را انجام می دهند. این توابع در اکسل خیلی مورد استفاده قرار می گیرند و تابع Sumif (برای یک شرط) و تابع Sumifs اکسل (برای بیش از یک شرط) برای این مسئله اختصاص داده شده است. مثلا اگر بخواهیم در یک بانک اطلاعاتی جمع فروش یک محصول خاص را استخراج کنیم. یا جمع ساعات مرخصی یک کارمند را از بین لیست مرخصی ها محاسبه کنیم، از Sumif استفاده میکنیم چرا که یک شرط را بررسی میکنیم. اما اگر بخواهیم جمع فروش یک محصول را در یک تاریخ خاص محاسبه کنیم، باید از Sumifs استفاده کنیم چرا که دو شرط داریم، یکی محصول و دیگری تاریخ مورد نظر.

نکته:
تابع Sumifs از نسخه ۲۰۰۷ به بعد به اکسل اضافه شده است. پس وجود یک شرط در تابع Sumifs هم معادل تابع Sumif هست (درواقع نیازی به وجود تابع Sumif نیست). اما تابع Sumif جهت حفظ سازگاری با نسخ قبلی در اکسل موجود است.

 

در ادامه آرگومان های تابع Sumifs را تشریح میکنیم:

Sum_Range: محدوده ای که عمل جمع بر روی آن انجام می شود.

Criteria_Range1: محدوده ای که شرط اول ما در آن موجود است.

Criteria1: شرط اول. (با ثبت شرط اول، آرگومان های مربوط به شرط های بعدی ظاهر می شوند.)

نکته:
دقت داشته باشید که جای آرگومان Sum-Range در تابع Sumif و Sumifs متفاوت است. همچنین، این آرگومان در تابع 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,”96/02/12″)

آرگومان پنجم: شرط دوم ما، یعنی تاریخ ۱۲/۰۲/۹۶ است. که هم می توان به سل ارجاع داد و هم مستقیم در تابع نوشت. به این صورت: “۱۲/۰۲/۹۶”

=SUMIFS(B2:B20,A2:A20,”محصول۲”,C2:C20,“96/02/12”)

مثال ۲: می خواهیم میزان فروش های بالای ۴۰۰۰۰ که در ماه بهمن سال ۹۵ ثبت شده اند را محاسبه کنیم.

در شکل ۲ مشاهده می کنید برای اینکه همه تاریخ های بهمن ماه ۹۵ را در محاسبه دخیل کنیم، از ترفند * که در تابع آموزش تابع Countifs شرح دادیم استفاده نمودیم.تابع Sumifs اکسل | جمع چند شرطی

 

شکل ۲- تابع Sumifs اکسل

حالا همین مسئله را با این شرط که هم فروش بالاتر از ۴۰۰۰۰ باشد و هم در بهمن همه سال ها اتفاق افتاده باشد ، حل کنید و سوالات خود را در ادامه همین پست مطرح بفرمایید.

حتما بخوانید:  تابع Sumif اکسل | محاسبه جمع شرطی در یک مجموعه داده

برچسب:متوسط

  • اشتراک گذاری:
profile avatar
حسنا خاکزاد
فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

مطلب قبلی

تابع Sumif اکسل | محاسبه جمع شرطی در یک مجموعه داده
۳۰ بهمن ۱۳۹۵

مطلب بعدی

مقایسه دو لیست در اکسل
۳۰ بهمن ۱۳۹۵

ممکن است همچنین دوست داشته باشید

Text-Functions-Cover
توابع ویرایش متن انگلیسی در اکسل
۱۷ مرداد, ۱۳۹۹
Replace Function Cover-min
آموزش کار با تابع Replace
۱۲ فروردین, ۱۳۹۹
Mid Function Cover-min
تابع Mid و چند کاربرد در اکسل
۱ دی, ۱۳۹۸

    26 نظر

  1. profile avatar
    معین
    ۱۸ اسفند ۱۳۹۸
    پاسخ

    سلام ی فایل داشتم میخوام با فرمول SUMIFS از شیت دیگه جمع بزنم، فرمول که میزنم تو شیت جدید جمعش ی عدد میاد، همون اعداد تو شیتی که اعداد هست جمعش ی عدد دیگه میشه . اگر امکانش هست فایلش هم بفرستم چک کنین . ممنون میشم

    • profile avatar
      حسنا خاکزاد
      ۱۹ اسفند ۱۳۹۸
      پاسخ

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

  2. profile avatar
    احمد
    ۲۶ بهمن ۱۳۹۸
    پاسخ

    سلام وقت بخیر
    دریک شیت چندین Table با کالاهای مختلف و مقدار مصرف متفاوت داریم
    حال اگر بخواهیم در یک جدول در شیت دیگر با زدن کد کالای خاص مجموع مقادیر مصرف از چند table دیگر را بیاورد.

    • profile avatar
      سامان چراغی
      ۲۶ بهمن ۱۳۹۸
      پاسخ

      سلام
      برای انجام این کار دو راه دارید:
      1- استفاده از Power Query و ادغام Query های ساخته شده از روی جداول
      2- جمع Sumif های مختلف در یک سلول که از روی هر جدول نوشته میشه.

  3. profile avatar
    سمیه
    ۶ دی ۱۳۹۸
    پاسخ

    سلام من یه سوال دارم.
    وقتتی یسری دیتا در یک شیت دارم که روی اونها شروط خاصی دارم میخوام براساس اون شرایط یسری جمع مثلا در ستون اعداد ائن محصوص هام زده بشه اما در شیت دیگر. از چه تابعی استفاده میشه؟
    من هرچی از تابع sumifs ,و ارجاع اون به شیت اطلاعاتم استفاده کردم نتئنستم موفق بشم. کسی میتونه کمکم کنه/؟

    • profile avatar
      حسنا خاکزاد
      ۷ دی ۱۳۹۸
      پاسخ

      درود بر شما
      از همون تابع sumif باید استفاده کنید
      برای لینک به یک شیت دیگه مقاله زیر رو بخونید ببینید کجا رو دارید اشتباه انجام میدید

      https://excelpedia.net/excel-external-link/

  4. profile avatar
    جمالی فر
    ۳ مهر ۱۳۹۸
    پاسخ

    با سلام و خسته نباشید، ممنون از اطلاعات که در سایت گذاشتید.
    من سوالی داشتم خدمتتان ، من یک ستون تاریخ از جنس date دارم و یک ستون هم مبلغ های مختلف در این تاریخ ها،
    در یک شیت دیگر ، کدی میخوام بنویسم که در بازه زمانی X تا Y جمع مبلغ ها را به من برگردونه

  5. profile avatar
    حبیب
    ۹ اسفند ۱۳۹۷
    پاسخ

    سلام.
    من برای حل یک سوال به مشکل خوردم امیدوارم شما بتونید کمکم کنید اونم این بود که من دو ستون دارم از اعداد 1 تا 100 به ترتیب و در ستون دیگه که به این اعداد مربوطه و روبروش قرار گرفته 100 عدد دیگه وجود داره
    حالا من میخوام به طور ثابت همیشه از عدد مثلا بیستم(متغیر) تا اخرین عدد جمع بزنم اما نمیخوام عدد (20) رو توی فرمول بیارم و میخوام اگه امکان داره عدد 20 رو توی یک سلول بنویسم و ادرس اون سلول رو توی فرمول بگذارم تا سریعتر به نتیجه برسم چون کارم به سرعت عمل بالایی نیاز داره.
    مثل (“sumif(A1:A100;”>20
    اما میخوام بجای عدد (20) ادرس یک سلول رو بدم تا عدد 20 رو از اون تو بخونه و مستقیم بگذاره توی فرمول اما نمیشه
    شما برای مشکل من هم راهکار دارید؟؟؟

    • profile avatar
      سامان چراغی
      ۹ اسفند ۱۳۹۷
      پاسخ

      سلام، از فرمول زیر استفاده کنید:
      با فرض اینکه عددی که میخواید در سلول C1 نوشته شده باشد:

      1
      SUMIF(A1:A100,">" & C1)

      این فرمول جمع اعداد بزرگتر از مقداری که در سلول C1 نوشته شده رو در ستون A به شما میده. اما اگر قصد دارید که جمع اعداد متناظرش در ستون روبه رویی رو بدست بیارید باید عبارت B1:B100 رو در آرگومان آخر تابع SUMIF قرار بدید.

  6. profile avatar
    حمیدرضا اسحاقی
    ۱ آبان ۱۳۹۷
    پاسخ

    سلام
    ممنون از مطالب مفیدتون
    می خاستم بپرسم مثلا در همین مثالی که خودتون زدید اگر بخواهیم جمع فروش محصول ۲ را در تاریخ ۱۲/۰۲/۹۶ یا 25/۰۲/۹۶ محاسبه کنیم. شکل تابع Sumifs چگونه میشه؟ منظورم قرار دادن شزط یا بین دو عبارت در یکی از خانه های شرط تابع است.
    ممنون میشم راهنمایی کنید

    • profile avatar
      حسنا خاکزاد
      ۱ آبان ۱۳۹۷
      پاسخ

      درود بر شما
      مشابه این سوال در لینک زیر حل شده

      https://excelpedia.net/excel-jalali-date/

  7. profile avatar
    احسان
    ۲۱ تیر ۱۳۹۷
    پاسخ

    سلام خسته نباشید من وقتی عدد میدم در شرط تابع، تابع عمل میکند ولی وقتی به یک سلول ارجاع میدم تابع عمل نمیکند میخاستم ببینم ممکنه راهنمایی بفرمایید؟

    • profile avatar
      حسنا خاکزاد
      ۲۳ تیر ۱۳۹۷
      پاسخ

      درود بر شما
      قاعدتا نباید مشکلی باشه.
      تابعی که نوشتید رو بذارید تا بررسی بشه

    • profile avatar
      saeid
      ۱۸ بهمن ۱۳۹۷
      پاسخ

      برای ارجاع به یک سلول باید مابین شرط و سلول مورد نظر از & استفاده کنید
      (sumifs(a1:a10,b1:b10,”>”&c1,d1:d10,”<"&e1=
      تابع بالا محدوده a1 تا a10 را به شرطی که محدوده b1 تا b10 بزرگتر از مقدار داده شده در سلول c1 و محدوده d1 تا d10 کوچکتر از مقدار داده شده در سلول e1 باشد جمع می زند.

      خودم هم خیلی دنبال این نکته بودم تا تونستم راه حل رو پیدا کنم

  8. profile avatar
    سهیل
    ۱۰ فروردین ۱۳۹۷
    پاسخ

    سلام. فرض کنید من میخواهم با استفاده از تابع sumifs جمع فروش محصولات خودم رو در ماه های مختلف سال بدست بیاورم. برای این منظور ماه مربوطه را در یک سلول مینویسم و مقدار فروش کالای x در آن ماه در سلول جلوی آن می آید. من میخواهم که اگر نام ماه را ننوشتم تابع sumifs از آن شرط عبور کند و فروش کل سال آن کالا را برای من حساب کند. چگونه این کار ممکن است؟

    • profile avatar
      سامان چراغی
      ۱۰ فروردین ۱۳۹۷
      پاسخ

      سلام
      کافیه در آرگومان مربوط به تعریف شرط ماه، از تابع IF استفاده کنید که بررسی کنه چیزی درون سلول ماه نوشته شده یا نه و … .
      در واقع خروجی تابع IF تعیین میکنه که چه چیزی به عنوان شرط در تابع Sumifs استفاده بشه.

      • profile avatar
        سهیل
        ۱۱ فروردین ۱۳۹۷
        پاسخ

        سلام. درک نمیکنم چطوری باید if رو بنویسم چون من 12 ماه دارم. چکار کنم که در خروجی if من 12 ماه سال در نظر گرفته شود تا تابع sumifs بتواند فروش تمام ماه ها را برگرداند

        • profile avatar
          سامان چراغی
          ۱۲ فروردین ۱۳۹۷
          پاسخ

          درود
          باید در criteria که اسم ماه نوشته میشه این فرمول رو بنویسید:
          با فرض اینکه شرط در سلول A1 نوشته بشه.

          1
          =If(A1="","<>",A1)

          در واقع داریم میگیم، اگر سلول A1 خالی بود، علامت <> بذار. این علامت یعنی همه. در غیر اینصورت، همون A1 رو بذار.

  9. profile avatar
    gh
    ۷ اسفند ۱۳۹۶
    پاسخ

    سلام
    تابعی در یک فایل دارم که تابع sumifs را به شکل زیر نوشته است:
    SUMIFS(F$3:F$42;D$3:D$42;H6;C$3:C$42;”>=”&H$3;C$3:C$42;””
    در این رابطه 2 سوال داشتم:
    1. چرا فقط علامت کوچک تر مساوی را در ” گذاشته است؟
    2. نقش & چیست؟
    3. برای آدرس مطلق یک سلول مگر نباید بنویسیم 3$I$ ؟ وقتی به این شکل می نویسم خطا می دهد. و فقط به شکل بالا جواب می دهد.
    ممنون

    • profile avatar
      حسنا خاکزاد
      ۷ اسفند ۱۳۹۶
      پاسخ

      درود بر شما

      1- همه چیز غیر از تابع، سلول و عدد باید داخل “” باشه….
      2- & کار اتصال رو انجام میدم یعنی میگه بزرگتر از محتوای اون سلول
      3- مطلق کردن، 4 حالت داره که در این پست کاملا توضیح داده شده:

      http://excelpedia.net/cell-address/

  10. profile avatar
    عباس
    ۸ تیر ۱۳۹۶
    پاسخ

    سلام ، وقت بخیر، لطفا برای همین مثالی که در مورد sumifs زدید به عنوان مثال برای فاصله بین دو تاریخ 96/02/12 الی 96/02/16 جواب بدست بیارید ممنون میشم جواب من را بدید
    باتشکر

    • profile avatar
      حسنا خاکزاد
      ۹ تیر ۱۳۹۶
      پاسخ

      درود بر شما. این موضوع یکی از سوالات مهم هست. خدمت شما:

      1
      =SUMIFS(B2:B20,C2:C20,"<960216",C2:C20,">960212")

      البته حتما باید تاریخ ها بصورت عدد نوشته بشه (عین همین شیوه که داخل فرمول نوشته شده)

      پیشنهاد میکنم حتما این پست رو بخونید.
      کامل توضیح داده شده:
      https://excelpedia.net/excel-jalali-date/

  11. profile avatar
    mmmahdih
    ۲۳ خرداد ۱۳۹۶
    پاسخ

    حالا همین مسئله را با این شرط که هم فروش بالاتر از ۴۰۰۰۰ باشد و هم در بهمن همه سال ها اتفاق افتاده باشد ، حل کنید و سوالات خود را در ادامه همین پست مطرح بفرمایید.

    1
    =SUMIFS(B2:B20;B2:B20;">40000";C2:C20;"*/11/*")

    امیدوارم جواب درست داده باشم؟؟

    • profile avatar
      سامان چراغی
      ۲۹ خرداد ۱۳۹۶
      پاسخ

      یکی از راه های خوب یادگیری اکسل همینه که مسائل مختلف رو خودتون تعریف کنید و سعی کنید بهترین روش رو برای حل اون مسئله پیدا کنید. این یه جور بازی هست که نتیجش یادگیری عمیق اکسله.
      بله کاملا درسته

  12. profile avatar
    mmahdih
    ۲ اردیبهشت ۱۳۹۶
    پاسخ

    مرسی
    عالی بود

نظر بدهید لغو پاسخ

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

از طریق ایمیل، مطالب آموزشی درباره اکسل و برنامه نویسی وی بی و سایر رویدادهای مهم را دریافت خواهید کرد.
  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

آخرین مطالب

نصب Power Query
فعال کردن Power Query در ورژن های مختلف اکسل
۰۷اسفند۱۳۹۹
3 روش برای شناسایی و اصلاح هایپرلینک معیوب در اکسل
۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل
۰۱اسفند۱۳۹۹
مدیریت لینک
مدیریت لینک ها در اکسل
۲۷بهمن۱۳۹۹
جستجو پیشرفته با Wild Card
جستجو پیشرفته در اکسل با استفاده از Wildcard
۲۲بهمن۱۳۹۹

وجود یک منبع جامع و به روز که بدونی همیشه پشتیبانته برای یادگیری ضروریه. اکسل پدیا رو برای همین به وجود آوردیم.

پاسخ سوالات شما

اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

عضویت در خبرنامه
با عضویت در خبرنامه، ایمیل‌های آموزشی درباره اکسل و برنامه نویسی وی بی را دریافت خواهید کرد.

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

info@excelpedia.net

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

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