تعیین یک مقدار تک رخدادی در یک ستون

Question

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

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

=IF(AND(A1<>A2,A3<>A2),"single","")

فرمول را به تعداد سلول های لازم کپی کنید و به راحتی می توانید اولین سلولی را که دارای یک مقدار واحد در ستون A است، پیدا کنید.

همچنین می توانید از فرمول زیر در سلول B2 استفاده کنید:

=COUNTIF($A:$A,$A2)

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

=INDEX($A:$A,MATCH(1,$B:$B,0))

اگر ستون کمکی امکان پذیر نیست، می توانید به فرمول های آرایه تکیه کنید. هر یک از اینها اولین مقداری را که یک بار اتفاق می افتد نشان می دهد:

=INDEX(A2:A999,MATCH(1,COUNTIF(A2:A999,A2:A999),0))
=SMALL(IF(COUNTIF(A2:A999,A2:A999)=1,A2:A999,""),1)

به یاد داشته باشید که اینها فرمول های آرایه هستند، به این معنی که باید آنها را با استفاده از Ctrl+Shift+Enter وارد کنید. به علاوه، اگر مقدار واحدی در محدوده وجود نداشته باشد، فرمول یک خطای #N/A را برمی گرداند.

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

=MATCH(1,COUNTIF(A2:A999,A2:A999),0)+1

توجه داشته باشید که فرمول سلول های A2:A999 را بررسی می کند. از آنجایی که ردیف A1 حذف شده است، "+1" در انتهای فرمول مورد نیاز است. اگر ردیف سرصفحه ندارید، یا اگر داده های شما در یک ردیف به غیر از ردیف 2 شروع می شوند، باید فرمول را مطابق با آن تنظیم کنید.

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

شکل 1. کادر محاوره ای سلول های تکراری.

1. سلول هایی را که می خواهید بررسی کنید انتخاب کنید.
2. در حالی که تب صفحه اصلی نوار نمایش داده می شود، روی گزینه Conditional Formatting در گروه Styles کلیک کنید. اکسل پالتی از گزینه های مربوط به قالب بندی شرطی را نمایش می دهد.
3. قوانین سلول های برجسته را انتخاب کنید. اکسل حتی گزینه های بیشتری را نمایش می دهد.
4. مقادیر تکراری را انتخاب کنید. اکسل کادر محاوره ای مقادیر تکراری را نمایش می دهد. (شکل 1 را ببینید.)
5. با استفاده از لیست کشویی در سمت چپ کادر محاوره ای، Unique را انتخاب کنید.
6. از لیست کشویی در سمت چپ کادر محاوره ای استفاده کنید تا نحوه قالب بندی مقادیر تک رخدادی را مشخص کنید.
7. روی OK کلیک کنید.

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

اگر رویکرد کلان را ترجیح می دهید، می توانید از یک ماکرو مانند موارد زیر استفاده کنید:

Sub FirstUnique()
    Dim c As Range
    Dim sMsg As String
    Dim bLone As Boolean

    If Selection.Cells.Count > 1 Then
        For Each c In Selection.Cells
            bLone = False
            If c.Row = 1 Then
                If c <> c.Offset(1, 0) Then bLone = True
            Else
                If c <> c.Offset(-1, 0) And _
                   c <> c.Offset(1, 0) Then bLone = True
            End If
            If bLone Then
                sMsg = "First single-occurrence value found "
                sMsg = sMsg & "at " & c.Address & vbCrLf
                sMsg = sMsg & "Value: " & c
                MsgBox sMsg
                Exit For
            End If
        Next c
    Else
        sMsg = "You must select at least 2 cells."
        MsgBox sMsg
    End If
End Sub

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