رنگ آمیزی نام های شرکت یکسان در اکسل

Question

Connie یک کاربرگ دارد که در هر سلول ستون B نام شرکت‌ها وجود دارد. آنها تحت عنوان منطقه (شمال شرقی، غربی، و غیره) در ستون A گروه‌بندی می‌شوند. او می‌خواهد قالب‌بندی شرطی را برای نام‌های شرکت اعمال کند تا اگر یک نام در بیش از یک منطقه ظاهر می شود، با استفاده از رنگ پس زمینه یا متن نشان داده می شود که یافتن شرکت های منطبق را آسان می کند. این به این معنی است که اگر یک شرکت به صورت قرمز فرمت شود، هیچ شرکت دیگری نباید قرمز ظاهر شود (باید به صورت رنگ متفاوتی مانند آبی یا سبز ظاهر شود). کانی مطمئن نیست که چگونه این را تنظیم کند یا اینکه حتی می توان آن را با قالب بندی شرطی انجام داد.

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

شکل 1. کادر محاوره ای New Formatting Rule.

1. سلول های حاوی نام شرکت شما را انتخاب کنید.
2. تب Home روبان را نمایش دهید و روی ابزار Conditional Formatting در گروه Styles کلیک کنید.
3. قانون جدید را انتخاب کنید. اکسل کادر محاوره ای New Formatting Rule را نمایش می دهد.
4. روی گزینه Format Only Unique یا Duplicate Values ​​در بالای کادر محاوره ای کلیک کنید.
5. اطمینان حاصل کنید که Duplicate در لیست کشویی در نیمه پایین کادر محاوره ای انتخاب شده است. (شکل 1 را ببینید.)
6. روی دکمه Format کلیک کنید و قالب بندی را تغییر دهید تا نشان دهد که چگونه می خواهید نام شرکت های تکراری ظاهر شود.
7. روی OK کلیک کنید تا کادر محاوره ای New Formatting Rule بسته شود.

در این مرحله همه موارد تکراری شما باید با هر قالبی که در مرحله 6 انتخاب کرده اید مطابقت داشته باشند. تنها مشکل این است که همه موارد تکراری به یک شکل قالب بندی می شوند. به عبارت دیگر، اگر دو شرکت دارید (شرکت ABC و شرکت DEF) و موارد تکراری برای آن شرکت‌ها وجود دارد، همه آنها به یک شکل قالب‌بندی می‌شوند - قالب‌بندی متفاوتی را برای دو شرکت مشاهده نخواهید کرد.

البته، می‌توانید به راحتی از قابلیت‌های فیلتر اکسل برای جدا کردن شرکت‌های تکراری، شرکت‌های غیرتکراری یا نام شرکت‌های فردی استفاده کنید. این ممکن است ساده‌ترین راه برای «به صفر رساندن» در شرکت‌هایی باشد که می‌خواهید مکان یابی کنید.

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

=AND(ISNUMBER(FIND("ABC Company",B1)),COUNTIF($B$1:$B$99,"ABC Company")>1)

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

در واقع پیچیده است. متأسفانه، راه آسان تری برای استفاده از قالب بندی شرطی وجود ندارد. با این حال، می توانید از قالب بندی شرطی صرف نظر کنید و از یک ماکرو برای برجسته کردن موارد تکراری خود استفاده کنید. ساده‌ترین ماکرو «خودکار» که می‌توانیم به آن برسیم (جایی که نیازی به دانستن نام‌های تکراری قبل از موعد نیست) این است که طیفی از سلول‌ها را بررسی می‌کند و رنگ سلول داخلی را بر اساس نام‌های شرکت تکراری تنظیم می‌کند.

Sub ColorCompanyDuplicates()
    Dim x As Integer
    Dim y As Integer
    Dim lRows As Long
    Dim lColNum As Long
    Dim iColor As Integer
    Dim iDupes As Integer
    Dim bFlag As Boolean

    lRows = Selection.Rows.Count
    lColNum = Selection.Column
    iColor = 2

    For x = 2 To lRows
        bFlag = False
        For y = 2 To x - 1
            If Cells(y, lColNum) = Cells(x, lColNum) Then
                bFlag = True
                Exit For
            End If
        Next y
        If Not bFlag Then
            iDupes = 0
            For y = x + 1 To lRows
                If Cells(y, lColNum) = Cells(x, lColNum) Then
                    iDupes = iDupes + 1
                End If
            Next y
            If iDupes > 0 Then
                iColor = iColor + 1
                If iColor > 56 Then
                    MsgBox "Too many duplicate companies!", vbCritical
                    Exit Sub
                End If
                Cells(x, lColNum).Interior.ColorIndex = iColor
                For y = x + 1 To lRows
                    If Cells(y, lColNum) = Cells(x, lColNum) Then
                        Cells(y, lColNum).Interior.ColorIndex = iColor
                    End If
                Next y
            End If
        End If
    Next x
End Sub

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

توجه داشته باشید که ماکرو ویژگی ColorIndex را از هر تکراری که پیدا می‌کند را تنظیم می‌کند و وقتی مجموعه جدیدی از نام‌های تکراری شرکت را پیدا می‌کند، متغیر مورد استفاده برای تنظیم ویژگی را افزایش می‌دهد. برای همه نام‌های شرکتی که هیچ تکراری برای آنها وجود ندارد، ویژگی ColorIndex سلول تغییر نمی‌کند. این به این معنی است که محدودیتی برای تعداد شرکت‌هایی که می‌توانند علامت‌گذاری شوند وجود دارد - ColorIndex فقط می‌تواند بین 0 تا 56 باشد. مقادیر در واقع توسط ماکرو از 3 تا 56 تخصیص داده می‌شوند، بنابراین فقط می‌توان 54 گروه از شرکت‌ها را قالب‌بندی کرد. .