تمام کسانی که کمی حرفه ای تر با نرم افزار اکسل کار کرده باشند با تابع SUMIF آشنا هستند. تابع SUMIF یک مقدار رو در یک محدوده جستجو میکنه و اگر مقدار مورد نظر در اون محدوده پیدا شد، عبارت متناظر رو از یک محدوده دیگه برمیگردونه. بعضی وقتها ممکنه پیش بیاد که ما بخوایم تابع SUMIF رو بر اساس رنگ سلول انجام بدیم. مثلاً تمام سلولهایی که رنگ اونها قرمز هست رو با هم جمع کنیم. اینکار توسط تابع SUMIF در اکسل امکان پذیر نیست. در این مطلب میبینیم که چطور با استفاده از یک تابع در VBA اینکار رو انجام بدیم.
خب برای شروع بیاید شیت زیر رو در نظر بگیرید:
همونطور که میبینید در شیت بالا ما در ستون A رنگ های مختلفی داریم و در ستون B هم مقادیری هست. هدف ما اینه که تمام مقادیر ستون B که رنگ سلول متناظرشون در ستون A سبزهست رو با هم جمع کنیم.
افزودن تابع Sumifcolor به اکسل:
برای اینکار ابتدا روی یکی از شیت های فایل اکسلمون راست کلیک میکنیم و مثل تصویر زیر گزینه view code رو انتخاب میکنیم:
با انتخاب این گزینه وارد پنجره Visual Basic Editor میشیم.
اگر با این پنجره زیاد آشنایی ندارید نگران نباشید. ما زیاد کاری باهاش ندارید. حالا از منوی Insert گزینه Module رو مثل تصویر زیر انتخاب میکنیم:
حالا کد زیر رو در پنجره ای که باز شده کپی کنید:
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
الان باید چیزی شبیه تصویر زیر داشته باشیم:
خب کار ما با Visual Basic Editor تموم شد و میتونیم این پنجره رو ببندیم.
استفاده از تابع SumifColor
حالا یه تابع به اسم SumifColor به اکسل ما اضافه شده که درست مثل تابع SUMIF عمل کنه. با این تفاوت که در تابع SUMIF شرط بر اساس مقدار سلول بود اما در این تابع شرط بر اساس رنگ سلول هست.
خب همونطور که گفتم ما میخواستیم سلول هایی که رنگ متناظرشون سبز هست رو با هم جمع کنیم پس در سلول D1 فرمول زیر رو مینویسیم:
=SumifColor(A1:A10;A8;B1:B10)
حالا باید شما هم مثل تصویر زیر مقدار 15 رو در سلول D1 داشته باشید. (جمع سلولهایی که رنگ ستون A اونها سبزه یعنی 12 و 3)
حالا بیاید فرمول بالا رو بررسی کنیم. در واقع این فرمول داره میگه که اگر هر یک از سلول های موجود در محدوده A1:A10 رنگشون مشابه رنگ سلول A8 بود، سلول متناظرش در محدوده B1:B10 رو با هم جمع کن.
امیدوارم این آموزش براتون مفید بوده باشه.
فایل نمونه:
عالی بود متشکرم
خواهش میکنم آرمین جان
سلام اقای اسماعیل پور عزیز
ی سوال داشتم این کد رو ک وارد میکنیم نباید به کل فایل های اکسل اضافه شه؟چرا فایل جدیدی ک باز میکنیم این تابع رو نداره ؟باید هربار و در هر فایل اکسل جدید .مجدد کد رو کپی و تابع رو اضافه کنیم؟
سلام رها جان
وقتی این کد رو در یک ماژول داخل فایل وارد میکنی فقط در همون فایل در دسترس هست. اگر بخوای همه جا در دسترس باشه باید رد فایل personal.xlsb اضافش کنی یا بضورت addin ایجادش کنی و بعد به اکسلت اضافش کنی
سلام
علت اینه در یک شیت کلیک راست میکنم و گزینه view code را نمایش نمیده چی میتونه باشه؟
سلام. احتمال میدم شما روی سلول های یک شیت راست کلیک میکنید. شما باید روی تب sheet یعنی جایی که اسم شیت رو نوشته راست کلیک کنید.
سلام
وقتی این کد رو در یک ماژول داخل فایل وارد میکنی فقط در همون فایل در دسترس هست. اگر بخوای همه جا در دسترس باشه باید رد فایل personal.xlsb اضافش کنی یا بضورت addin ایجادش کنی و بعد به اکسلت اضافش کنی
امکانش هست این مطلب را آموزش بدین؟
سلام، يه ديتا بيس اکسل دارم، هر چند تا ستون با يه شماره گزارش ميشه، من تابعي نوشتم که سطرهاي مثلا شماره 11 را در شيت ديگري زير هم رديف کنه ولي براي پرينت مشکل دارم، معمولا هر گزارش در چند برگ پرينت ميشه ولي هدر را فقط براي همه صفحات که شماره شيت را هم درج کنه نميتونم براش برنامه اي بنويسم، البته تنظيم هدر فوتر پرينتر را هم چک کردم بدردم نميخوره
سلام، يه ديتا بيس اکسل دارم، هر چند تا ستون با يه شماره گزارش ميشه، من تابعي نوشتم که سطرهاي مثلا شماره 11 را در شيت ديگري زير هم رديف کنه ولي براي پرينت مشکل دارم، معمولا هر گزارش در چند برگ پرينت ميشه ولي هدر را فقط براي همه صفحات که شماره شيت را هم درج کنه نميتونم براش برنامه اي بنويسم، البته تنظيم هدر فوتر پرينتر را هم چک کردم بدردم نميخوره
ببخشيد، هر چند تا سطر يک گزارش هست، در پيام قبلي اشتباهي نوشته ام ستون
سلام و سپاس بیکران استاد عزیز
مطابق دستور تان عمل کردم ولی SUMIFCOLORبه توابع اکسل اضافه نشد.لطفا راهنمایی بفرمایید
سلام چطور میشه به اکسل برای همیشه اضافه اش کرد؟
سلام دو راه برای اینکار وجود داره:
1- این کد رو در فایل personal.xlsb بنویسید.
2- این فایل رو به صورت add-in ذخیره کنید و به اکسل اضافه کنید
سلام اگر مقادیر داخل همون سلول های رنگی باشن و بخواییم مثلا رنگ های سبز رو جمع ببندیم باید چیکار کنیم؟
سلام. کافیه پارامتر سوم رو هم معادل پارامتر اول قرار بدید
سلام بنده با یک مشکلی روبرو شدم این فرمول SumifColor رو نوشتم اما نتونستم استفاده کنم به این علت که سطرهای جدول من یکی در میان طوسی رنگ هستن و برای همین رنگ اصلی رو تشخیص نمیده چکار کنم؟
سلام چگونه میتوانم از فایلهای اکسل موجود برای بازنشانی در نرم افزار حسابداری اقدام کنم
سلام. متاسفانه سوالتون رو دقیق متوجه نشدم. ممنون میشم یکم بیشتر توضیح بدید
very useful
ممنون مری عزیز
سلام ضمن تشکر من زمانی که از این تابع استفاده می کنم زمانیکه جمع سلولهای رنگی بیشتر از 2.147.483.647 می شود دیگه تابع جواب نمی ده لطفا راهنمایی فرمایید . باتکر
سلام علیرضا جان. برای اینکار باید نوع متغیر cSum رو به double یا variant تغییر بدیم. پست رو هم ویرایش کردم که این مشکل برای دیگران تکرار نشه. ممنون از توجهت
سلام. خیلی عالی بود . چطور میشه اتوآپدیت رو بهش اضافه کرد؟ در حال حاضر با تغییر کردن سلول های استفاده شده در تابع باید حتما در تمام سلول هایی که تابع استفاده شده وارد شوم و اینتر کنیم. آیا امکان داره مثل خود توابع اکسل با تغییر ورودی ها مقدار تابع هم اپدیت بشه؟
سلام علی جان. برای اینکار خط اول رو به شکل زیر تغییر بدید:
Public Function SumifColor(ColorRange As Range, CellColor As Range, SumRange As Range, Optional VolatileParameter As Variant)
و بعد موقع استفاده از تابع برای پپارامتر آخر تابع Now() رو بنویسید مثل زیر:
=SumifColor(A1:A10;A8;B1:B10,Now())
سلام. ممنون یاسین جان. من این کار رو انجام دادم ولی موقع استفاده پیام wrong data type میده
من چک کردم مجدد سعید جان. مشکلی نداشت و اجرا شد. شما از چه ورژن اکسلی استفاده میکنی؟ لطفاً یه بار دیگه کد رو بررسی کن که جایی رو اشتباه ننوشته باشی
سلام . از 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)
سلام علی جان. عجیبه چون من همین کد رو مجدد چک کردم و مشکلی وجود نداشت. فایلت رو برام بفرست بررسی میکنم
درود بر شما عالی بود
من این کار رو هم کردم ولی موقع تغییر رنگ، به رنگه Cell color به صورت خودکار جمع نمیشه ولی وقتی همون رنگ cell color رو به یه رنگ دیگه تغییر میدی از جمع مورد نظر به صورت خودکار کم میشه
؟؟؟؟؟؟؟؟
خیلی ممنونم. عالی بود
خواهش میکنم مونا جان
سلام
خدا خیرت بده
واقعا ممنونم ازین توضیحات
خیلی عالی
سلام مسعود جان. قربانت
سلام
اگر رنگ دهی بر اساس CONDITIONAL باشه چطور میشه شمارش رو انجام داد. چون این فرمول رو قبول نمیکنه.
سلام
من وقتی راست کلیک میکنم view codre رو نمیاره تواکسلم
و اکسلم 2010 هستش
سلام فرناز خانم
میشه بفرمایید که شما کجا رایت کلیک می کنید که نمیاره ؟؟!!
بایستی روی نام شیت ها در پایین رایت کلیک بفرمایید
اگر بازهم گزینه View Code موجود نبود
از منوی View اقدام به ضبط و مشاهده ماکرو نمایید
چنانچه آن هم موجود نبود
احتمال دارد که هنگام نصب آفیس ، از نصب VBA ، روی سیستم شما خودداری شده باشد
مجددا” اقدام به نصب آفیس بصورت کامل نمایید
بازهم چنانچه مشکلی بود ، با کلیک روی نام کامل من در بالا ، مطرح بفرمایید
موفق باشید – خانیکی
سلام دوست عزیزم کلید ALt+F11 رو بزنید و ادامه مراحل رو انجام بدین
سلام دوست عزیز
اگر سلول برا اساس CONDITIONAL رنگ شود چطور میتونم به هر رنگ یک امتیاز بدم مثلا اگر در یک سلول رنگ سبز بود در سلول کنار اون امتیاز 10 و اگر زرد بود امتیاز 5 و اگر قرمز بود امتیاز صفر را برای من نشان دهد . خیلی از وقتی که میگزاری سپاسگزارم
سلام
یاسین جان برای استفاده از رنگ فونت سلول به جای رنگ سلول چه راهکاری هست؟
سلام حمیدرضا جان
استاد اسماعیل پور مدتی است که مجال پاسخگویی به پرسشهای دوستان رو ندارند
اما راهکارش ساده است
کافیست که در تابع فوق بجای کلمه Interior کلمه Font رو جایگزین کنی
بهمین سادگی !!!
موفق باشی – خانیکی
سلام . من همین کار رو انجام دادم ولی وقتی اکسل میبندم و دوباره باز میکنم فرمول کار نمیکنه . چیکار باید کنم ؟
سلام من همه ی این کارها رو عینا انجام دادم ولی ارور value میده متاسفانه، دلیلش چی هست؟
به نام خدا – با سلام و عرض ادب – تشکر و ممنون – واقعا”جالب و کاربردی بود. در پناه حق و التماس دعا.
سلام من یه سوال داشتم
من 4ستون در 32 ردیف دارم که روزهای هفته و تاریخ های هرماه رو وارد کردم
روزهای شنبه تا چهارشنبه رنگ سلول سفید و پنجشنبه هارو زرد کردم(برای محاسبه اضافه کاری)
روزهای شنبه تا چهارشنبه ضریب نداره همون یک هست
روزهای پنجشنبه ضریب یک و نیم داره
میخوام فرمولی تعریف کنم که اگر روز عادی ساعت وارد شد (رنگ سلول سفید بود)ضرب در یک بشه در سطر بعدیش نشون بده
و اگر روز پنجشنبه ساعت وارد شد (رنگ سلول زرد بود)ضرب در یک و نیم بشه در سطر بعدیش نشون بده
یعنی شرط رنگی بودن بکار ببرم
هرچی فرمول نویسی کردم اشتباه بود لطفا کمکم کنین این فرمولو بنویسم
سلام وقت بخیر من این روش رو استفاده کردم ولی وقتی اکسل رو میبنم و دوباره باز میکنم کل مراحلم حذف میشه و sumifcolor حذف میشه
اکسل رو که میبندم کلا حذف میشه