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

021-47625755
info@excelpedia.net

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

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

      0

توابع اکسل

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

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

  • ارسال شده توسط سامان چراغی
  • دسته بندی توابع اکسل
  • تاریخ ۲۵ بهمن ۱۳۹۵
  • نظرات ۳۶ دیدگاه ها
تابع Sumif اکسل
4.3 / 5 ( 15 امتیاز )

آموزش تابع Sumif اکسل

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

نکته خیلی مهم
در این دو تابع، نکته ای که اهمیت دارد این است که بتوانیم شروط و محدوده های مربوط به آنها را به درستی تشخیص دهیم

 

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

Range: محدوده ای که شرط مورد نظر ما در آن وجود دارد.
Criteria: شرط مورد نظر.
[Sum_Range]: محدوده ای که عمل جمع بر روی آن انجام می شود. این آرگومان اختیاری است و زمانی که Range و Sum_Range مشترک است، می توانیم آن را در فرمول وارد نکنیم.

با ذکر چند مثال این تابع را شرح می دهیم:

مثال ۱: بانک اطلاعاتی مربوط به فروش محصولات مختلف و مبالغ فروش در تاریخ های مختلف موجود است. می خواهیم جمع فروش محصول ۲ محاسبه کنیم. طبق شکل ۱ تابع Sumif را می نویسیم.

تابع Sumif اکسل - نحوه ثبت تابع sumif

شکل ۱- تابع Sumif اکسل – نحوه ثبت تابع sumif

حتما بخوانید:  تعریف متغیر ها و ثابت ها در وی بی (VBA) | قسمت دوم

آرگومان اول: ستونی است که شرط ما در آن وجود دارد. ستون نام محصول یا محدوده A2:A20 انتخاب می شود.

=SUMIF(A2:A20,E2,B2:B20)

آرگومان دوم: شرط ما، یعنی کلمه محصول۲ است. که هم می توان به سل ارجاع داد و هم مستقیم در تابع نوشت. به این صورت: “محصول۲”

=SUMIF(A2:A20,E2,B2:B20)

آرگومان سوم: ستونی که عملیات جمع بر روی آن انجام می شود. ستون میزان فروش یا محدوده B2:B20 انتخاب می شود.

=SUMIF(A2:A20,E2,B2:B20)

نکته خیلی مهم
محدوده های Range و Sum range حتما باید هم اندازه و هم تراز باشند. یعنی از یک ردیف شروع شده و به یک ردیف ختم شود.

 

مثال ۲: در بالا گفتیم که آرگومان سوم تابع Sumif اختیاری است و می تواند در فرمول وجود نداشته باشد. به مثال زیر دقت کنید.

می خواهیم ببینیم جمع فروش های بیش از ۴۰۰۰۰ چقدر است. در این حالت فرمول به شرح زیر تغییر میکند:

=SUMIF(B2:B20,”>40000”)

در شکل ۲ مشاهده میکنید که آرگومان Sum-Range حذف شده است. چرا که عمل جمع قرار است روی همان محدوده شرط اعمال شود. پس می تواند حذف شود.

تابع Sumif اکسل - بدون آرگومان اختیاری

شکل ۲- تابع Sumif اکسل – بدون آرگومان اختیاری

حالا می خواهیم جمع فروش محصول ۲ را در تاریخ های معینی محاسبه کنیم. با توجه به اینکه بیش از یک شرط داریم، باید از Sumifs استفاده کنیم. حتما آموزش مربوط به Sumifs، که نکات کاربردی را در بر می گیرد را مطالعه کنید.

برچسب:تابع Sumif, مقدماتی

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

مطلب قبلی

نمایش پیش بینی در اکسل | ترفند نمودار خطی
۲۵ بهمن ۱۳۹۵

مطلب بعدی

تابع Sumifs اکسل | تکنیک های کاربردی در اعمال شروط
۲۵ بهمن ۱۳۹۵

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

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

    36 نظر

  1. profile avatar
    پرستو موسوی
    ۹ آذر ۱۳۹۹
    پاسخ

    سلام و ممنون از توضیحات خوب شما.
    من باید مجموع مقدار فروش برای تعداد زیادی کد شناسه رو محاسبه کنم که یعنی اول باید کدها یوتیک بشن و بعد جمع فروش هر کد محاسبه و ثبت بشه. چکار باید کنم؟

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

      درود بر شما
      اول لیست یونیک رو تهیه کنید
      مثلا با remove duplicate
      این مقاله رو بخونید
      https://excelpedia.net/remove-duplicate/

      بعد هم از sumif استفاده کنید

  2. profile avatar
    زهرا
    ۱۰ اردیبهشت ۱۳۹۹
    پاسخ

    سلام خسته نباشین من یک تابع دارم نمیتونم مشکلم رو حل کنم لطفا راهنماییم کنید (sumif(a2:10,”jib”,d2:d10

    زمانی که یک سطر اضافه میکنم مثلا میخوام d2:d11 a2:a11 رو محاسبه کنه، فرمولم به همین شکل پایین باقی میمونه و مقادیر سطر جدید رو محاسبه نمیکنه یعنی فرمولم آپدیت نمیشه
    (sumif(a2:a10,”jib”,d2:d10

    • profile avatar
      حسنا خاکزاد
      ۱۰ اردیبهشت ۱۳۹۹
      پاسخ

      درود
      یا محدوده اضافه شده باید بین سطرها باشه (نه اخرین سطر)
      یا اینکه محدوده ها رو table کنید. این مقاله رو هم بخونید

      • profile avatar
        زهرا
        ۱۰ اردیبهشت ۱۳۹۹
        پاسخ

        سپاس از محبتتون

  3. profile avatar
    mehrdad
    ۲۱ اسفند ۱۳۹۸
    پاسخ

    با سلام
    من فرمول زیر را برای یک مجموعه داده می خواهم برای ستونهای مختلفی مانند G و ….. انجام دهم . در واقع شرط اول مربوط به سال و شرط دوم مربوط به روزهای هر ماه هست. حالا می خواهم مجموع بارندگی روزهای هر ماه، به صورت ماهانه را برای هر سال جمع نماییم. به عبارت دیگر یک ستون مثلا برای سال 1982 که در ردیف اول جمع روزهای ماه اول و در ردیف های بعد جمع روزهای ماههای بعدی قرار بگیرند. و همینطور برای سال بعد در ستون دوم این عملیات انجام شود.
    حالا چگونه می توانم وقتی فرمول زیر را در سلول اول ستون مربوط به سال 1982 اجرا می کنم برای سلول پایینی هم ان را ادامه بدهم که فقط شرط دو که اینجا عدد 1 هست بشود و 2، 3 و …..
    یا در ستون دوم که برای سال 1983 خواهد شد فقط شرط اول از 1982 بشو 1983 و …..
    SUMIFS(G2:G13149,A2:A13149,1982,B2:B13149,1)
    بسیار ممنون میشوم راهنمایی بفرمایید.

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

      درود
      اگر درست برداشت کرده باشم، سوال اینه که چطور با درگ کردن عدد 1 بشه 2 و …
      برای اینطور مساول از توابع row() و column() بسته به جهت درگ کردن باید استفاده کنید
      مثلا بجای عدد 1، بذارید row(A1) این یعنی عدد 1 و وقتی درگ میکنید پایین میشه 2 و ….
      برای 1982 هم بنویسید: 1981+Row(A1)

  4. profile avatar
    سامان
    ۱۸ اسفند ۱۳۹۸
    پاسخ

    سلام
    خدا قوت و با تشکر از آموزش هاتون در زمینه اکسل.
    یک سوال
    فرض:میخواهیم در سلول H۲ جمع اعداد داخل سلولهای A2D2F2 را بنویسد به شرط اینکه B2E2G2 برابر( الف )باشد.
    و اگر B2E2G2 برابر (ب) بود در i2 جمع آنها را بنویسد.
    در واقع خانه های که در این ردیف متنشان الف است را شناسایی و عدد کنار آن را در یک سلول جمع کند و خانه هایی که ب هستند را در خانه ای دیگر جمع کند.

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

      این فرمول رو در H2 بنویسید:

      1
      =IF(and(B2="الف",E2="الف",G2="الف"),A2+D2+F2,"")

      همین فرمول رو برای شرط “ب” در سلول I2 هم بنویسید

      این مقاله رو هم بخونید
      https://excelpedia.net/and-or-function/

  5. profile avatar
    حسام
    ۱ مهر ۱۳۹۸
    پاسخ

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

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

      درود بر شما
      از همین sumif باید استفاده کنید
      توضیحات در مقاله بالا هست

      • profile avatar
        حسام
        ۱ مهر ۱۳۹۸
        پاسخ

        سلام جواب نمی دهد مجبور هستم برای تک تک اونها vlookupبزنیم

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

          پس سوال رو درست مطرح نکردید. فرمودید جمع فروش برای هر فرد. این یعنیsumif
          vlookup جمع انجام نمیده
          جستجو میکنه

  6. profile avatar
    omid
    ۵ خرداد ۱۳۹۸
    پاسخ

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

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

      سلام
      احتمالا اعدادی که در فایل هست به صورت متن هستند. که اگر اینطور باشه باید یک مثلث سبز رنگ کنار این سلول ها نشون میده. برای درست کردنش این سلول ها رو انتخاب کنید و گزینه Convert to Number رو بزنید.

  7. profile avatar
    الیاس نورزاده
    ۲۳ اردیبهشت ۱۳۹۸
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۲۴ اردیبهشت ۱۳۹۸
      پاسخ

      درود بر شما
      بسته به اینکه چه ساختاری داره و تاریخ چه جنسی هست و ….
      باید از Sumif استفاده کنید

  8. profile avatar
    سعید
    ۱۹ اسفند ۱۳۹۷
    پاسخ

    با سلام و عرض ادب
    می خواهیم در یک سطر، محتویات سلولهای بین یک سلول تا سلول دیگری در همان سطر، را با هم جمع کنیم. اما سلول های شروع و پایان متغیر و بصورت تابع باشند. چگونه می توان اینکار را انجام داد؟
    از ترکیب توابع Sum و Address و Match استفاده کردم اما نتیجه ای حاصل نشد:

    1
    =SUM((ADDRESS(6,MATCH('ورود داده ها'!$O$5,A5:BT5,0),1,1)):x6)

    از راهنمایی که می فرمایید صمیمانه سپاسگزارم.

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

      درود بر شما

      به اینصورت باید تغییر بدید:

      1
      =SUM(indirect(ADDRESS(6,MATCH('ورود داده ها'!$O$5,A5:BT5,0),1,1)&":x6"))

      • profile avatar
        سعید
        ۲۰ اسفند ۱۳۹۷
        پاسخ

        بسیار عالی بود.
        ممنون و سپاسگزارم.

  9. profile avatar
    مهدی حسینی
    ۱۱ اسفند ۱۳۹۷
    پاسخ

    خیلی ممنون

  10. profile avatar
    کاوه
    ۱۲ بهمن ۱۳۹۷
    پاسخ

    من یک شرط خاص دارم میخوام ببینم میشه با این تابع نوشتش یا نه
    من 3 ستون دارم که توی ستون سوم دو حالت داره که یا یک است یا صفر و این شرط به این صورت است که اگر مقدار ستون سوم 1 بود مقدار ستون اول و ستون دوم باهم جمع شود اما اگر ستون سوم 0 بود فقط مقدار ستون اول رو قرار بده و ستون دوم جمع نشود.

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

      درود بر شما

      شما باید از if استفاده کنید

      1
      =if(C1=1,A1+B1,A1)

      مقاله زیر رو مطالعه کنید:
      https://excelpedia.net/if-function/

  11. profile avatar
    امیر
    ۲۰ مرداد ۱۳۹۷
    پاسخ

    با سلام و احترام
    من چندتا if تو در تو نوشته ام و در آخر یه شرط میخوام بنویسم که اگر اون اتفاق افتاد همه ی شرط های قبلی خنثی و عدد صفر نمایش داده شود
    ممنون میشم اگر کمک بفرمایید.

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

      درود بر شما
      یک if دیگه اضافه کنید و همه اون if ها رو در قسمت value false بنویسید
      در قسمت value true هم صفر

      • profile avatar
        امیر
        ۲۰ مرداد ۱۳۹۷
        پاسخ

        بسیار سپاسگزار و ممنونم

  12. profile avatar
    Hossein Madadi
    ۱۹ اسفند ۱۳۹۶
    پاسخ

    سلام
    مطالبی که در سایت گذاشتید برای من خیلی مفید بود.
    ممنون وهمیشه موفق باشید

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

      درود بر شما
      زنده باشید، لطف دارید

      موفق باشید

  13. profile avatar
    عباس احمدی
    ۱۸ بهمن ۱۳۹۶
    پاسخ

    جناب آقای چراغی
    سلام علیکم
    در نوشتن یک تابع سئوال داشتم .
    چنانچه امکان دارد کمکم کنید.لطفا
    طرح مسئله :
    در شیت 1 : اطلاعات در یک جدول موجود است که شامل ستونهای (ردیف ، تاریخ ، نام ) می باشد.
    می خواهم در جدول دیگر با درج یکی از ردیف های موجود در جدول اول ، نام نیز فراخوانی شود.
    توابع زیادی مثل , index,find,mach,sumif,sumifs,lookup,vlookup,hlookup را امتحان کردم ولی موفق نشدم.
    فایل مربوطه را برایتان به آدرس ایمیل ارسال نمودم . لطفا در صورت امکان تابع مورد نظر را در فایل اکسل نوشته و برایم ایمیل نمائید.
    قبلا از شما سپاسگزاری می کنم. احمدی

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

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

      • profile avatar
        عباس احمدی
        ۱۹ بهمن ۱۳۹۶
        پاسخ

        جناب آقای سامان چراغی
        با سپاس فراوان
        با راهنمایی شما مشکل حل شد.
        شاد و موفق باشید.

  14. profile avatar
    احمدی
    ۱۱ آذر ۱۳۹۶
    پاسخ

    جناب آقای چراغی
    سلام علیکم

    در نوشتن یک تابع سئوال داشتم .
    چنانچه امکان دارد کمکم کنید.لطفا

    طرح مسئله :
    در شیت 1 : اطلاعات در یک جدول موجود است که شامل ستونهای (ردیف ، شماره سند ، تاریخ ، کد حساب ، مبلغ ) می باشد.

    می خواهم مجموع اعداد ستون مبلغ مندرج در جدول را با توجه به کد مورد نظر در سلولی نشان دهد. که با فرمول SUMIFS توانستم.
    می خواهم مجموع اعداد ستون مبلغ مندرج در جدول را در یک بازه زمانی در سلولی نشان دهم . که با فرمول SUMPRODUCT توانستم.

    مشکل اینجاست که نمی توانم دو فرمول را باهم ترکیب کنم. یعنی مجموع فروش کد حساب مورد نظر در بازه زمانی مشخص.

    فایل مربوطه را برایتان به آدرس ایمیل ارسال نمودم . لطفا در صورت امکان تابع مورد نظر را در فایل اکسل نوشته و برایم ایمیل نمائید.
    قبلا از شما سپاسگزاری می کنم. احمدی

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

      سلام جناب احمدی
      کافیه در فرمولی که با Sumproduct نوشتید شرط کد رو اضافه کنید، با توجه به فایلتون نتیجه فرمول به صورت زیر میشه:

      1
      =SUMPRODUCT((L2:L17>=I13)*(L2:L17<=I16),O2:O17*(N2:N17=C12))

      • profile avatar
        احمدی
        ۱۲ آذر ۱۳۹۶
        پاسخ

        جناب آقای سامان چراغی
        با سلام و دورد
        نمی دانم چگونه مراتب تشکر خودم را ابراز نمایم.
        انشاء ا.. همراه صحیح ، سالم ، موفق و شاد باشی.
        احمدی

        سئوال دیگری نیز داشتم چنانچه زحمتی نیست آن را هم راهنمایی فرمائید.

  15. profile avatar
    آناهید
    ۱۳ آبان ۱۳۹۶
    پاسخ

    سلام
    چطورمیتونم با sumif جمع خانه های رنگی رو حساب کنم ؟
    این فرمول رو نوشتم جواب نداد SUMIF(B4:AE4؛ “white”)

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

      سلام
      شرایطی که تو تابع Sumif قابل استفاده هست به محتوای سلول برمیگرده نه رنگ و یا سایر خصوصیات سلول.
      برای انجام جمع شرطی بر اساس رنگ سلول میتونید از تابعی که تو فایل زیر گذاشته شده استفاده کنید:

      دانلود فایل

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

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

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

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

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

آخرین مطالب

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

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

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

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

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

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

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

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

021-47625755

info@excelpedia.net

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

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

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

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