سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

جمع زدن سلول های رنگی در اکسل

جمع سلول های رنگی
۵/۵ - (۳ امتیاز)

جمع زدن سلول بر اساس رنگ

خیلی وقت ها پیش میاد که داده های موجود در اکسل رو بر اساس منطقی رنگ کردیم که از تمیز داده بشن (بصورت دستی یا با استفاده از Conditional formatting). حالا میخوایم بر اساس این رنگ ها محاسباتی رو مثل جمع، شمارش و … انجام بدیم. مثلا میخوایم جمع سلول های قرمز رنگ رو داشته باشیم.
همونطور که میدونیم، در اکسل تابعی که بتونه روی سلول های رنگی محاسبات انجام بده (مثلا جمع سلول های رنگی رو بده) وجود نداره. برای این کار یا باید از توابع ساخته شده یا User-defined استفاده بشه که باید کد VBA مربوطه رو داشته باشیم. یا از یک سری ترفندهای دیگه. در ادامه به تشریح روشهای مختلف برای انجام این نوع محاسبات ارائه خواهیم داد.

روش شمارش و یا جمع زدن مقادیر سلول های رنگی (که بصورت دستی رنگی شده اند)

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

داده های رنگ شده بصورت دستی

شکل ۱- داده های رنگ شده بصورت دستی

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

برای این کار کافیه وارد محیط VBA شده و کد زیر رو در یک ماژول وارد کرده و فایل رو ذخیره کنیم. برای مشاهده روش نوشتن تابع در محیط VBA مقاله مربوط به ایجاد فرمول در VBA رو مطالعه کنید.

کد VBA برای ایجاد تابع فراخوانی رنگ سلول مورد نظر رو در زیر می بینید. کافیه که این کد رو کپی کنیم و در یک ماژول قرار بدیم.

این کد برای فراخوانی رنگ یک سلول هست (رنگ پس زمینه). کد ایجاد تابع برای شمارش سلول های رنگی، در زیر آمده و کافیه مثل توابع قبلی، کد به اکسل اضافه بشه.

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

بعد از اینکه این کدها به اکسل اضافه شد و فایل بصورت XLSM ذخیره شد، همه توابع مورد نظر به اکسل اضافه میشن و قابل استفاده هستن.

نکته:
برای رنگ فونت هم همین کدها باید استفاده بشه، ولی بجای Interior.Color که نشون دهنده رنگ زمینه سلول هست، از عبارت Font.Color که به معنی رنگ فونت هست، استفاده بشه.

 

حالا طبق شکل ۲، از توابع اضافه دشه به اکسل استفاده میکنیم. تابع CountCellsByColor برای شمارش سلول های رنگی استفاده میشه که آرگومان اولش محدوده سلول های رنگی است و آرگومان دومش، رنگ مورد نظر.

=CountCellsByColor(rangecolor code)

استفاده از تابع ایجاد شده

شکل ۲- استفاده از تابع ایجاد شده برای شمارش سلول های رنگی

با همین روش و با استفاده از تابع SumCellsByColor میتونیم مقادیر موجود در سلول های رنگی رو جمع بزنیم.

SumCellsByColor(rangecolor code)

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

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

 

با استفاده از تابع GetCellColor میتونیم کد رنگ سلول مورد نظر رو فراخوان یکنیم که با این کار، میتونیم هر فرمول نویسی که خواستیم روی کدهای رنگ انجام بدیم. مثل Sumif, Countif و ….

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

شکل ۴- فراخوانی کد رنگ سلول با استفاده از تابع ایجاد شده

نکته مهم:
توجه داشته باشید که با تغییر رنگ سلول ها، نتیجه فرمول ها خودبخود آپدیت نمیشه و باید حتما F9 یا Formula/Calculation/Automatic رو بزنید.

 

این کار رو (فراخوانی کد رنگ سلول) با یکی از توابع پنهان اکسل هم میتونیم انجام بدیم. به اینصورت که در Name Manager مطابق شکل ۵، یک نام تعریف میکنیم و تابع زیر رو بهش اختصاص میدیم:

=get.cell(38,سلولی که رنگ داره)

تعریف یک نام و تخصیص تابع Get.Cell (بدون کد وی بی)

شکل ۵- تعریف یک نام و تخصیص تابع Get.Cell (بدون کد وی بی)

بعد روبروی سلول رنگ شده، = رو تایپ کرده و نام مورد نظر رو می نویسیم. مطابق شکل ۶، این Name کدی رو برای هر رنگ، نمایش میده که ما میتونیم از این کدها برای انجام هر نوع محاسباتی استفاده کنیم:

فراخوانی کد رنگ بدون VBA

شکل ۶- فراخوانی کد رنگ بدون VBA

روش شمارش و یا جمع زدن مقادیر سلول های رنگی (که بصورت دستی رنگی شده اند)

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

اما من پیشنهاد میکنم که از منطق خود این ابزار برای فرمول نویسی استفاده بشه. چون رنگی کردن با استفاده از ابزار Conditional formatting تابع منطق هست مثلا تکراری ها رو رنگ میکنه، یا مثلا داده های بزرگتر از ۱۰ و ….

ما میتونیم با استفاده از همین شرط ها، فرمول نویسی خودمون رو انجام بدیم و نتایج مورد نظر مثل جمع و تعداد سلول ها رو داشته باشیم.

اما اگر به هر دلیلی این روش پاسخگو نبود، میتونیم از کد زیر برای جمع و شمارش سلول های رنگی استفاده کنیم. البته این یک ماژول هست و باید اجرا بشه هر بار. تابع نیست که به اکسل اضافه بشه:

فرض کنیم در داده های زیر که با استفاده ابزار Conditional formatting رنگی شده اند، میخواهیم تعداد سلول های رنگی و جمع آنها رو حساب کنیم.

انتخاب محدوده رنگی شده و سلول مورد نظر برای شمارش

شکل ۷- انتخاب محدوده رنگی شده و سلول مورد نظر برای شمارش

بعد از اینکه کد رو به فایل اکسل اضافه کردیم، طبق زیر عمل میکنیم:

  • محدوده رنگی رو انتخاب میکنیم. (ستون C در شکل ۷)
  • کلید Ctrl رو نگه میداریم و روی سلولی که رنگ مورد نظر ما رو داره کلیک میکنیم. (سلول C2 در شکل ۷)
  • کلید ترکیبی Alt+F8 رو میزنیم و لیست ماکروهای موجود در اکسل نمایش داده میشه.
  • ماکروی مورد نظر یعنی SumCountByConditionalFormat رو انتخاب میکنیم و Run رو میزنیم.
  • نتیجه در یک msgbox نمایش داده میشه. (شکل ۸)

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

شکل ۸- نتیجه جمع و تعداد سلول هایی که با فرمت سلول C2 رنگی شدن

همه این کدها (Function و Sub) و تنظیمات Name Manager در فایل اکسل زیر موجود هست. میتونید دانلود کنید و آموزش بالا رو روی داده های این فایل امتحان کنید.

دانلود فایل این آموزش

برای دانلود فایل این آموزش روی لینک زیر کلیک کنید (جهت دانلود مستقیم به حساب کاربری خود وارد شوید)

کلیدواژه : پیشرفته
آواتار
144

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

دیدگاه کاربران
  • mohamdi ۳۱ شهریور ۱۳۹۹ / ۱۲:۴۷ ب٫ظ

    سلام خسته نباشید
    میشه راهنماییم کنید در اکسل چگونه میتوانم حاصل عدد منفی (-۱) را با عدد قرمز یا سلول قرمز نشان دهم ، وهمینطور بالعکس حاصل عدد مثبت (۱) را باعدد سبز یا سلول سبز نشان دهم؟

    • سامان چراغی ۳۱ شهریور ۱۳۹۹ / ۴:۴۶ ب٫ظ

      سلام
      برای انجام این کار میتونید از Conditional Formatting یا فرمول نویسی در Format Cell استفاده کنید.

    • mohamdi ۱ مهر ۱۳۹۹ / ۱۰:۰۶ ق٫ظ

      ممنونم از راهنماییتون…

  • Sadat ۱۹ مرداد ۱۳۹۹ / ۹:۵۷ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۹ مرداد ۱۳۹۹ / ۱۰:۰۹ ق٫ظ

      درود
      خب چه داده ای؟ از کجا اتومات پر بشه؟!
      اگر منظورتون شمردن نام هر کشتی هست از countif استفاده کنید

  • رضا سائلی ۲۹ بهمن ۱۳۹۸ / ۴:۴۷ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۷ اسفند ۱۳۹۸ / ۱۰:۴۲ ق٫ظ

      درود
      اینکه نمیشه رو باید توضیح بدید
      چرا نمیشه؟ مشکل کجاست؟

  • عليرضا ۳ آبان ۱۳۹۸ / ۲:۳۵ ب٫ظ

    خانم حسنا مرسی از زحماتت فقط یه مشکل داریم اونم اینکه آپدیت انجام نمیشه چیکار کنم

  • اسدی ۱۷ تیر ۱۳۹۸ / ۱۲:۲۰ ب٫ظ

    مطلب جمع زدن سلول های رنگی در اکسل ، که تهیه کردید خوبه اما خیلی سخت توضیح دادید. با کد زیر خیلی راحت میشه مقادیر موجود در سلول های رنگی رو جمع زد و با یکم دست کاری میشه به هدف های مختلفی دست پیدا کرد مثل شمارش اعداد رنگی و …ی.
    Function sumCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
    xcolor = criteria.Interior.ColorIndex
    For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
    CountCcolor = CountCcolor + datax.Value
    End If
    Next datax
    End Function

  • Meysam Hosseini ۲۶ خرداد ۱۳۹۸ / ۸:۳۰ ب٫ظ

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

ارسال دیدگاه

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

توسط
تومان