جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
ربات پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfand_bot

خلاصه آمار

تبليغات

پربحث ترين ها

۵

شمارش و جمع زدن سلول‌ها براساس رنگ زمینه‌ی آن‌ها در اکسل

اکسل ماکرونویسی

اگر بخواهید تعداد سلول‌هایی را که رنگی کرده‌اید یا سلول‌هایی را که با فرمت شرطی رنگ شده‌اند را بشمارید و یا اینکه مجموع اعداد سلول‌های رنگی را محاسبه کنید، چه کار می‌کنید؟ بسته به تعداد سلول‌های رنگی، شمارش و جمع زدن دستی آن‌ها ممکن است بسیار زمان‌بر و همراه با خطا باشد. در ادامه مطلب با پی‌سی‌ترفند همراه باشید تا با روش‌های خودکار شمارش و جمع زدن سلول‌های رنگی با استفاده از امکانات اکسل آشنا شوید.

روش ۱) با استفاده از فرمول - ساخت تابع جدید بوسیله ماکرونویسی

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

فرض کنید در جدولی مشابه شکل زیر لیست سفارش‌های یک شرکت را دارید که در آن سلول‌ها در ستون Delivery برمبنای مقدار خود رنگ شده‌اند، سلول‌های Due in X Days نارنجی، Delivered سبز و Past Due قرمز هستند.

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

  • ساخت ماکرو:

برای این کار کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

Function GetCellColor(xlRange As Range)
'pctarfand.ir & tarfandha.blog.ir
    GetCellColor = xlRange.Cells(1, 1).Interior.Color
End Function
 
Function GetCellFontColor(xlRange As Range)
    GetCellFontColor = xlRange.Cells(1, 1).Font.Color
End Function
 
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByColor = cntRes
End Function
 
Function SumCellsByColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByColor = sumRes
End Function
 
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByFontColor = cntRes
End Function
 
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByFontColor = sumRes
End Function

فایل را با پسوند (Excel Macro-Enabled Workbook (.xlsm ذخیره کنید.

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

=CountCellsByColor(F2:F14,A17)

که در آن F2:F14 محدوده‌ای است که می‌خواهید سلول‌های رنگی در آن جمع زده شده و A17 سلولی است که دارای رنگ پس زمینه مورد نظر (در اینجا قرمز) است که می‌خواهید شمارش بر اساس آن انجام شود. با روش مشابهی می‌توانید فرمولی برای رنگ‌های دیگر ایجاد کنید.

اگر داده‌های عددی درون سلول‌های رنگ شده داشته باشید (مانند ستون Qty در شکل) می‌توانید به راحتی جمع را نیز برحسب رنگ محاسبه کنید که در این شکل نشان داده شده است:

همان طور که می‌بینید از فرمول

=SumCellsByColor(D2:D14,A17)

استفاده کرده‌ایم که در آن D2:D14 محدوده‌ و A17 سلول با الگوی رنگ مورد نظر است.

با روش مشابهی می‌توانید سلول‌هایی را جمع بزنید که دارای یک رنگ قلم (Font Color) خاص هستند که برای این کار به ترتیب از فرمول‌های CountCellsByFontColor و SumCellsByFontColor استفاده کنید.

 

منبع: techreaders.ir (ترجمه شده از سایت ablebits.com)

البته از کدهای دیگری نیز می‌توانید استفاده کنید که در منابع زیر ارائه شده است:


روش ۲) با استفاده از فیلتر کردن بر اساس رنگ

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

اما در مورد این حالت خاص یعنی نحوه فیلتر کردن بر اساس رنگ مثلا در شکل زیر، ابتدا ستونی که می‌خواهید براساس رنگ فیلتر شود انتخاب کنید.

سپس از تب Data، روی دکمه Filter کلیک کنید تا دکمه در کنار عنوان ستون ظاهر شود.

روی دکمه کلیک کرده و از لیست باز شده روی گزینه Filter by Color کلیک نموده و رنگ مورد نظرتان برای فیلتر کردن را انتخاب کنید.

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

پس از فیلتر کردن با انتخاب سلول‌های فیلتر شده می‌توانید تعداد و مجموع آن‌ها را در نوار وضعیت پائین اکسل مشاهده کنید و یا اینکه با استفاده از توابع AGGREGATE یا SUBTOTAL تعداد و یا مجموع آن‌ها را محاسبه کنید. نحوه بکارگیری این دو تابع در این سایت و این سایت آموزش داده شده است.


روش ۳) با استفاده از جستجو بر اساس رنگ

ابتدا محدوده‌‌ی مورد نظرتان را انتخاب کنید. سپس کلید Ctrl + F را بفشارید تا پنجره Find and Replace باز شود.‌روی تب Find کلیک کنید. روی دکمه Format کلیک کرده و گزینه Choose Format From Cell را انتخاب کنید تا یک رنگ گیر (Color picker) ظاهر شود. سپس روی سلولی که دارای رنگ زمینه مورد نظرتان است کلیک کنید تا رنگ آن به عنوان رنگ جستجو انتخاب شود.

* اگر دکمه Format وجود ندارد روی دکمه Options کلیک کنید تا ظاهر شود.

سپس روی دکمه Find All کلیک کنید تا نتیجه جستجو در قسمت پائین پنجره جستجو نمایش داده شود. اکنون می‌توانید همه نتایج جستجو را انتخاب کنید (Ctrl + A) و تعداد آن را در پائین پنجره مشاهده کنید.


روش ۴) با استفاده از افزونه

منبع: extendoffice.com

 

عضویت در خبرنامه:

نظرات (۵)

  1. سلام؛
    با سپاس از اطلاعات مفیدی که در اختیار عموم قرار می دهید.
    تمامی مراحلی که در این مقاله قید شد را انجام دادم ولی بعد از خارج شدن از برنامه و باز نمودن دوباره آن، فرمول برای شمارش خانه ها رنگی از کار میفته و خطا میده.
    با اینکه این ماژول همچنان در ا« صفحه موجود است ولی اجرا نمیشه. گویا باید قسمتی برای Run شدن اتوماتیک آن وجود داشته باشه که بی اطلاع هستم. لطفا راهنمایی بفرمایید.
    من از اکسل 2007 استفاده می کنم.
    • پاسخ:

      سلام
      اگر منظورتان قابل استفاده بودن در همه فایل‌ها است به این مطلب مراجعه کنید.
  2. سلام
    عذر میخوام اگه سوال واضح نبود
    منظورم اینه من روشی پیدا کردم که داده های تکراری رو پیدا میکنه با استفادعه از conditional formatting
    میخوام ببینم میشه هر داده تکراری یک رنگ خاص بشه یا نه؟؟
    مثلا اگر چند داده تکراری x  داریم رنگ قرمز بشه ، داده های تکراری  y رنگ سبز و...
    آیا امکان اینکار وجود داره ؟؟؟؟
    ممنون
    • پاسخ:

      ظاهرا با استفاده از ابزارهای اکسل چنین امکانی وجود ندارد مگر اینکه با ماکرونویسی بتوان انجام داده که من اطلاعی ندارم.
  3. سلام
    خیلی ممنون از کمک و راهنمائیتون.مشکلم حل شد.
    برای داده های تکراری اینکه بخواهیم یک رنگ بشن و همچنین تعداد این داده ها هم چطوری مشه؟؟
    باید ماکرو بنویسم؟؟
    ممنون
    • پاسخ:

      سلام
      سوالتان را دقیق‌تر مطرح کنید. یعنی همه داده‌های تکراری همرنگ بشوند؟ یک تکرار هر عدد با تکرار عدد دیگر رنگ متفاوتی داشته باشد؟

ارسال نظر

قبل از ارسال نظر به نکات زیر توجه کنید:

۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.

۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی
X بستن