سبد خرید
0

سبد خرید شما خالی است.

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

تابع Sumif اکسل
۴.۳/۵ - (۱۵ امتیاز)

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

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

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

 

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

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

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

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

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

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

آرگومان اول: ستونی است که شرط ما در آن وجود دارد. ستون نام محصول یا محدوده 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مقدماتی

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

دیدگاه کاربران
  • tafakori 12 خرداد 1400 / 4:01 ب.ظ

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

    • حسنا خاکزاد 14 خرداد 1400 / 4:43 ب.ظ

      درود بر شما
      این با sumif نمیشه
      بسته به شرایط و نوع شرط ها (عددی متنی) میشه روش های مختلف فرمول نویسی استفاده کرد

  • پرستو موسوی 9 آذر 1399 / 3:20 ب.ظ

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

    • حسنا خاکزاد 9 آذر 1399 / 8:14 ب.ظ

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

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

  • زهرا 10 اردیبهشت 1399 / 9:20 ب.ظ

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

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

    • حسنا خاکزاد 10 اردیبهشت 1399 / 10:01 ب.ظ

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

      • زهرا 10 اردیبهشت 1399 / 10:08 ب.ظ

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

  • mehrdad 21 اسفند 1398 / 1:23 ب.ظ

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

    • حسنا خاکزاد 21 اسفند 1398 / 9:07 ب.ظ

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

  • سامان 18 اسفند 1398 / 9:51 ب.ظ

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

    • حسنا خاکزاد 19 اسفند 1398 / 11:47 ق.ظ

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

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

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

  • حسام 1 مهر 1398 / 10:50 ق.ظ

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

    • حسنا خاکزاد 1 مهر 1398 / 1:06 ب.ظ

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

      • حسام 1 مهر 1398 / 1:41 ب.ظ

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

        • حسنا خاکزاد 1 مهر 1398 / 1:50 ب.ظ

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

  • omid 5 خرداد 1398 / 6:26 ب.ظ

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

    • سامان چراغی 5 خرداد 1398 / 9:06 ب.ظ

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

  • الیاس نورزاده 23 اردیبهشت 1398 / 3:21 ب.ظ

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

    • حسنا خاکزاد 24 اردیبهشت 1398 / 10:07 ق.ظ

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

  • سعید 19 اسفند 1397 / 10:08 ب.ظ

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

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

    • حسنا خاکزاد 20 اسفند 1397 / 9:43 ق.ظ

      درود بر شما

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

      • سعید 20 اسفند 1397 / 2:51 ب.ظ

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

  • مهدی حسینی 11 اسفند 1397 / 1:02 ب.ظ

    خیلی ممنون

  • کاوه 12 بهمن 1397 / 11:58 ق.ظ

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

    • حسنا خاکزاد 13 بهمن 1397 / 9:18 ق.ظ

      درود بر شما

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

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

  • امیر 20 مرداد 1397 / 10:32 ق.ظ

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

    • حسنا خاکزاد 20 مرداد 1397 / 10:59 ق.ظ

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

      • امیر 20 مرداد 1397 / 2:40 ب.ظ

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

  • Hossein Madadi 19 اسفند 1396 / 12:22 ب.ظ

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

    • حسنا خاکزاد 19 اسفند 1396 / 1:55 ب.ظ

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

      موفق باشید

  • عباس احمدی 18 بهمن 1396 / 8:52 ق.ظ

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

    • سامان چراغی 18 بهمن 1396 / 1:50 ب.ظ

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

      • عباس احمدی 19 بهمن 1396 / 8:08 ق.ظ

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

  • احمدی 11 آذر 1396 / 8:08 ق.ظ

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

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

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

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

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

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

    • سامان چراغی 11 آذر 1396 / 9:18 ق.ظ

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

      • احمدی 12 آذر 1396 / 12:36 ب.ظ

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

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

  • آناهید 13 آبان 1396 / 2:58 ب.ظ

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

    • سامان چراغی 13 آبان 1396 / 3:35 ب.ظ

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

      دانلود فایل

ارسال دیدگاه

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

توسط
تومان