تابع SUMIF بر اساس رنگ سلول در اکسل

تابع SUMIF بر اساس رنگ سلول در اکسل

تمام کسانی که کمی حرفه ای تر با نرم افزار اکسل کار کرده باشند با تابع SUMIF آشنا هستند. تابع SUMIF یک مقدار رو در یک محدوده جستجو میکنه و اگر مقدار مورد نظر در اون محدوده پیدا شد، عبارت متناظر رو از یک محدوده دیگه برمیگردونه. بعضی وقتها ممکنه پیش بیاد که ما بخوایم تابع SUMIF رو بر اساس رنگ سلول انجام بدیم. مثلاً تمام سلولهایی که رنگ اونها قرمز هست رو با هم جمع کنیم. اینکار توسط تابع SUMIF در اکسل امکان پذیر نیست. در این مطلب میبینیم که چطور با استفاده از یک تابع در VBA اینکار رو انجام بدیم.

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

SUMIF بر اساس رنگ سلول در اکسل

همونطور که میبینید در شیت بالا ما در ستون A رنگ های مختلفی داریم و در ستون B هم مقادیری هست. هدف ما اینه که تمام مقادیر ستون B که رنگ سلول متناظرشون در ستون A سبزهست رو با هم جمع کنیم.

افزودن تابع Sumifcolor به اکسل:

برای اینکار ابتدا روی یکی از شیت های فایل اکسلمون راست کلیک میکنیم و مثل تصویر زیر گزینه view code رو انتخاب میکنیم:

SUMIF بر اساس رنگ سلول در اکسل

با انتخاب این گزینه وارد پنجره Visual Basic Editor میشیم.

اگر با این پنجره زیاد آشنایی ندارید نگران نباشید. ما زیاد کاری باهاش ندارید. حالا از منوی Insert گزینه Module رو مثل تصویر زیر انتخاب میکنیم:

SUMIF بر اساس رنگ سلول در اکسل

حالا کد زیر رو در پنجره ای که باز شده کپی کنید:

 

Public Function SumifColor(ColorRange As Range, CellColor As Range, SumRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For i = 1 To ColorRange.Count
If ColorRange(i).Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(SumRange(i), cSum)
End If
Next i
SumifColor = cSum
End Function

الان باید چیزی شبیه تصویر زیر داشته باشیم:

تابع SUMIF بر اساس رنگ سلول در اکسل

خب کار ما با Visual Basic Editor تموم شد و میتونیم این پنجره رو ببندیم.

استفاده از تابع SumifColor

حالا یه تابع به اسم SumifColor به اکسل ما اضافه شده که درست مثل تابع SUMIF عمل کنه. با این تفاوت که در تابع SUMIF شرط بر اساس مقدار سلول بود اما در این تابع شرط بر اساس رنگ سلول هست.

خب همونطور که گفتم ما میخواستیم سلول هایی که رنگ متناظرشون سبز هست رو با هم جمع کنیم پس در سلول D1 فرمول زیر رو مینویسیم:

 

=SumifColor(A1:A10;A8;B1:B10)

حالا باید شما هم مثل تصویر زیر مقدار 15 رو در سلول D1 داشته باشید. (جمع سلولهایی که رنگ ستون A اونها سبزه یعنی 12 و 3)

SUMIF بر اساس رنگ سلول در اکسل

حالا بیاید فرمول بالا رو بررسی کنیم. در واقع این فرمول داره میگه که اگر هر یک از سلول های موجود در محدوده A1:A10 رنگشون مشابه رنگ سلول A8 بود، سلول متناظرش در محدوده B1:B10 رو با هم جمع کن. 

امیدوارم این آموزش براتون مفید بوده باشه.

فایل نمونه:

[private]sumifcolor[/private]

 

46 Comments
  • عزتی
    Posted at 11:26h, 16 آگوست پاسخ

    سلام وقت بخیر من این روش رو استفاده کردم ولی وقتی اکسل رو میبنم و دوباره باز میکنم کل مراحلم حذف میشه و sumifcolor حذف میشه

    • عزتی
      Posted at 11:27h, 16 آگوست پاسخ

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

  • محمد مومنی
    Posted at 07:44h, 19 می پاسخ

    سلام من یه سوال داشتم
    من 4ستون در 32 ردیف دارم که روزهای هفته و تاریخ های هرماه رو وارد کردم
    روزهای شنبه تا چهارشنبه رنگ سلول سفید و پنجشنبه هارو زرد کردم(برای محاسبه اضافه کاری)
    روزهای شنبه تا چهارشنبه ضریب نداره همون یک هست
    روزهای پنجشنبه ضریب یک و نیم داره
    میخوام فرمولی تعریف کنم که اگر روز عادی ساعت وارد شد (رنگ سلول سفید بود)ضرب در یک بشه در سطر بعدیش نشون بده
    و اگر روز پنجشنبه ساعت وارد شد (رنگ سلول زرد بود)ضرب در یک و نیم بشه در سطر بعدیش نشون بده
    یعنی شرط رنگی بودن بکار ببرم
    هرچی فرمول نویسی کردم اشتباه بود لطفا کمکم کنین این فرمولو بنویسم

  • علیرضا نعیمی
    Posted at 10:54h, 03 می پاسخ

    به نام خدا – با سلام و عرض ادب – تشکر و ممنون – واقعا”جالب و کاربردی بود. در پناه حق و التماس دعا.

  • mahya
    Posted at 10:20h, 20 آوریل پاسخ

    سلام من همه ی این کارها رو عینا انجام دادم ولی ارور value میده متاسفانه، دلیلش چی هست؟

  • پریا
    Posted at 12:41h, 16 آوریل پاسخ

    سلام . من همین کار رو انجام دادم ولی وقتی اکسل میبندم و دوباره باز میکنم فرمول کار نمیکنه . چیکار باید کنم ؟

  • حمیدرضا
    Posted at 11:22h, 28 ژانویه پاسخ

    سلام
    یاسین جان برای استفاده از رنگ فونت سلول به جای رنگ سلول چه راهکاری هست؟

    • محمد قاسم خانیکی
      Posted at 16:58h, 30 ژانویه پاسخ

      سلام حمیدرضا جان
      استاد اسماعیل پور مدتی است که مجال پاسخگویی به پرسشهای دوستان رو ندارند
      اما راهکارش ساده است
      کافیست که در تابع فوق بجای کلمه Interior کلمه Font رو جایگزین کنی
      بهمین سادگی !!!
      موفق باشی – خانیکی

  • ابراهیم
    Posted at 13:46h, 03 اکتبر پاسخ

    سلام دوست عزیز
    اگر سلول برا اساس CONDITIONAL رنگ شود چطور میتونم به هر رنگ یک امتیاز بدم مثلا اگر در یک سلول رنگ سبز بود در سلول کنار اون امتیاز 10 و اگر زرد بود امتیاز 5 و اگر قرمز بود امتیاز صفر را برای من نشان دهد . خیلی از وقتی که میگزاری سپاسگزارم

  • farnaz
    Posted at 15:34h, 21 سپتامبر پاسخ

    سلام
    من وقتی راست کلیک میکنم view codre رو نمیاره تواکسلم
    و اکسلم 2010 هستش

    • محمد قاسم خانیکی
      Posted at 08:38h, 22 سپتامبر پاسخ

      سلام فرناز خانم
      میشه بفرمایید که شما کجا رایت کلیک می کنید که نمیاره ؟؟!!
      بایستی روی نام شیت ها در پایین رایت کلیک بفرمایید
      اگر بازهم گزینه View Code موجود نبود
      از منوی View اقدام به ضبط و مشاهده ماکرو نمایید
      چنانچه آن هم موجود نبود
      احتمال دارد که هنگام نصب آفیس ، از نصب VBA ، روی سیستم شما خودداری شده باشد
      مجددا” اقدام به نصب آفیس بصورت کامل نمایید
      بازهم چنانچه مشکلی بود ، با کلیک روی نام کامل من در بالا ، مطرح بفرمایید
      موفق باشید – خانیکی

    • چراغی
      Posted at 10:56h, 03 آوریل پاسخ

      سلام دوست عزیزم کلید ALt+F11 رو بزنید و ادامه مراحل رو انجام بدین

  • Ali Jorjani
    Posted at 12:48h, 11 جولای پاسخ

    سلام
    اگر رنگ دهی بر اساس CONDITIONAL باشه چطور میشه شمارش رو انجام داد. چون این فرمول رو قبول نمیکنه.

  • Masoud
    Posted at 00:03h, 21 ژوئن پاسخ

    سلام
    خدا خیرت بده
    واقعا ممنونم ازین توضیحات
    خیلی عالی

    • یاسین اسماعیل پور
      Posted at 00:18h, 21 ژوئن پاسخ

      سلام مسعود جان. قربانت

  • مونا پزشکی
    Posted at 12:58h, 14 ژوئن پاسخ

    خیلی ممنونم. عالی بود

    • یاسین اسماعیل پور
      Posted at 11:10h, 16 ژوئن پاسخ

      خواهش میکنم مونا جان

  • علی سعیدی
    Posted at 01:32h, 03 می پاسخ

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

    • یاسین اسماعیل پور
      Posted at 15:10h, 04 می پاسخ

      سلام علی جان. برای اینکار خط اول رو به شکل زیر تغییر بدید:
      Public Function SumifColor(ColorRange As Range, CellColor As Range, SumRange As Range, Optional VolatileParameter As Variant)
      و بعد موقع استفاده از تابع برای پپارامتر آخر تابع Now() رو بنویسید مثل زیر:
      =SumifColor(A1:A10;A8;B1:B10,Now())

      • علی سعیدی
        Posted at 11:29h, 19 می پاسخ

        سلام. ممنون یاسین جان. من این کار رو انجام دادم ولی موقع استفاده پیام wrong data type میده

        • یاسین اسماعیل پور
          Posted at 12:47h, 19 می پاسخ

          من چک کردم مجدد سعید جان. مشکلی نداشت و اجرا شد. شما از چه ورژن اکسلی استفاده میکنی؟ لطفاً یه بار دیگه کد رو بررسی کن که جایی رو اشتباه ننوشته باشی

          • علی سعیدی
            Posted at 21:30h, 21 می

            سلام . از 2019 استفاده میکنم.
            Public Function SumifColor(ColorRange As Range, CellColor As Range, SumRange As Range, Optional VolatileParameter As Variant)
            این هم دستوری که اجرا نمیشه
            =sumifColor(G3:G22,C24,G3:G22,NOW())
            ولی این یکی که قبلا بود اجرا میشه ولی باید با هر تغییر داده مجددا اجرا بشه
            =sumifColor(G3:G22,C24,G3:G22)

          • یاسین اسماعیل پور
            Posted at 04:01h, 28 می

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

      • essaq
        Posted at 17:31h, 23 اکتبر پاسخ

        درود بر شما عالی بود
        من این کار رو هم کردم ولی موقع تغییر رنگ، به رنگه Cell color به صورت خودکار جمع نمیشه ولی وقتی همون رنگ cell color رو به یه رنگ دیگه تغییر میدی از جمع مورد نظر به صورت خودکار کم میشه
        ؟؟؟؟؟؟؟؟

  • alireza
    Posted at 11:02h, 30 مارس پاسخ

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

    • یاسین اسماعیل پور
      Posted at 21:46h, 31 مارس پاسخ

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

  • مری
    Posted at 16:20h, 12 مارس پاسخ

    very useful

    • یاسین اسماعیل پور
      Posted at 15:16h, 09 آوریل پاسخ

      ممنون مری عزیز

  • سیدمنصور علمایی
    Posted at 00:08h, 02 نوامبر پاسخ

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

    • یاسین اسماعیل پور
      Posted at 19:20h, 02 نوامبر پاسخ

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

  • حاتمی
    Posted at 12:49h, 18 آگوست پاسخ

    سلام اگر مقادیر داخل همون سلول های رنگی باشن و بخواییم مثلا رنگ های سبز رو جمع ببندیم باید چیکار کنیم؟

    • یاسین اسماعیل پور
      Posted at 14:29h, 18 آگوست پاسخ

      سلام. کافیه پارامتر سوم رو هم معادل پارامتر اول قرار بدید

      • محسن
        Posted at 16:00h, 07 مارس پاسخ

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

  • taranom
    Posted at 12:02h, 01 آگوست پاسخ

    سلام چطور میشه به اکسل برای همیشه اضافه اش کرد؟

    • یاسین اسماعیل پور
      Posted at 16:58h, 03 آگوست پاسخ

      سلام دو راه برای اینکار وجود داره:
      1- این کد رو در فایل personal.xlsb بنویسید.
      2- این فایل رو به صورت add-in ذخیره کنید و به اکسل اضافه کنید

  • SAEID
    Posted at 22:51h, 05 آوریل پاسخ

    سلام و سپاس بیکران استاد عزیز
    مطابق دستور تان عمل کردم ولی SUMIFCOLORبه توابع اکسل اضافه نشد.لطفا راهنمایی بفرمایید

  • Imani
    Posted at 00:51h, 08 فوریه پاسخ

    ببخشيد، هر چند تا سطر يک گزارش هست، در پيام قبلي اشتباهي نوشته ام ستون

  • Imani
    Posted at 00:49h, 08 فوریه پاسخ

    سلام، يه ديتا بيس اکسل دارم، هر چند تا ستون با يه شماره گزارش ميشه، من تابعي نوشتم که سطرهاي مثلا شماره 11 را در شيت ديگري زير هم رديف کنه ولي براي پرينت مشکل دارم، معمولا هر گزارش در چند برگ پرينت ميشه ولي هدر را فقط براي همه صفحات که شماره شيت را هم درج کنه نميتونم براش برنامه اي بنويسم، البته تنظيم هدر فوتر پرينتر را هم چک کردم بدردم نميخوره

  • Arsalan
    Posted at 00:48h, 08 فوریه پاسخ

    سلام، يه ديتا بيس اکسل دارم، هر چند تا ستون با يه شماره گزارش ميشه، من تابعي نوشتم که سطرهاي مثلا شماره 11 را در شيت ديگري زير هم رديف کنه ولي براي پرينت مشکل دارم، معمولا هر گزارش در چند برگ پرينت ميشه ولي هدر را فقط براي همه صفحات که شماره شيت را هم درج کنه نميتونم براش برنامه اي بنويسم، البته تنظيم هدر فوتر پرينتر را هم چک کردم بدردم نميخوره

  • سعید
    Posted at 11:03h, 13 ژانویه پاسخ

    سلام
    وقتی این کد رو در یک ماژول داخل فایل وارد میکنی فقط در همون فایل در دسترس هست. اگر بخوای همه جا در دسترس باشه باید رد فایل personal.xlsb اضافش کنی یا بضورت addin ایجادش کنی و بعد به اکسلت اضافش کنی
    امکانش هست این مطلب را آموزش بدین؟

  • saeid
    Posted at 10:41h, 13 ژانویه پاسخ

    سلام
    علت اینه در یک شیت کلیک راست میکنم و گزینه view code را نمایش نمیده چی میتونه باشه؟

    • یاسین اسماعیل پور
      Posted at 16:46h, 01 جولای پاسخ

      سلام. احتمال میدم شما روی سلول های یک شیت راست کلیک میکنید. شما باید روی تب sheet یعنی جایی که اسم شیت رو نوشته راست کلیک کنید.

  • raha
    Posted at 14:53h, 08 ژانویه پاسخ

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

    • یاسین اسماعیل پور
      Posted at 16:13h, 08 ژانویه پاسخ

      سلام رها جان
      وقتی این کد رو در یک ماژول داخل فایل وارد میکنی فقط در همون فایل در دسترس هست. اگر بخوای همه جا در دسترس باشه باید رد فایل personal.xlsb اضافش کنی یا بضورت addin ایجادش کنی و بعد به اکسلت اضافش کنی

  • Armin amin k
    Posted at 18:11h, 07 ژانویه پاسخ

    عالی بود متشکرم

    • یاسین اسماعیل پور
      Posted at 16:21h, 08 ژانویه پاسخ

      خواهش میکنم آرمین جان

Post A Comment