شماره گذاری ردیف های فیلتر شده در اکسل

Question

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

راه ساده و سنتی برای انجام این کار این است که قبل از اعمال فیلتر، یک فرمول به ستون استفاده نشده اضافه کنید. به عنوان مثال، فرض کنید مقادیری که می خواهید فیلتر کنید در ستون A هستند که از سلول A3 شروع می شود و ستون استفاده نشده شما F است. سپس در سلول F3 باید فرمول زیر را وارد کنید:

=SUBTOTAL(103,$A$3:A3)

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

به همین سادگی، یک اشکال دارد: اگر فیلتر را حذف کنید، فرمول به روز می شود تا ردیف های قابل مشاهده را (دوباره) شماره گذاری کند. با این حال، اگر می‌خواستید اعداد روی سلول‌های مربوط به ردیف‌هایی که قبلاً فیلتر شده‌اند باقی بمانند، روش SUBTOTAL به درستی کار نخواهد کرد.

در این صورت رویکرد متفاوتی لازم است. در این مثال، فرض کنید که ستون استفاده نشده شما G است. و پس از اعمال فیلتر، اولین ردیف قابل مشاهده شما 4 است. بنابراین، شما باید موارد زیر را در سلول G4 وارد کنید:

=MAX($G$1:G3) + 1

توجه داشته باشید که محدوده ارائه شده برای تابع MAX با اولین سلول در ستون شروع می شود (مطمئن شوید که علائم دلار را درج کرده اید) و از طریق ردیف بالای سلولی که در آن فرمول را وارد می کنید امتداد می یابد. این فرمول را در تمام سلول های قابل مشاهده باقیمانده در ستون کپی کنید. شما اکنون یک تعداد متوالی از تمام ردیف های قابل مشاهده دارید. حتی اگر بعداً فیلتر را بردارید، این تعداد باقی می ماند.

تنها زمانی که این رویکرد به هم می‌خورد این است که فیلتر را حذف کنید، مقادیر ستون A را تغییر دهید و فیلتر را دوباره اعمال کنید یا تغییر دهید. این به این دلیل است که این رویکرد پیش‌فرض می‌گیرد که قبل از اضافه کردن فرمول‌ها، ستون G خالی باشد.

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

Sub NumberVisibleRows()
    Dim J As Long
    Dim r As Range

    J = 0
    For Each r In Selection.Rows
        Cells(r.Row, 7) = ""
        If Not r.Hidden Then
            J = J + 1
            Cells(r.Row, 7) = J
        End If
    Next r
End Sub

برای استفاده از ماکرو، فیلتر خود را اعمال کنید، ردیف‌هایی را که می‌خواهید ارزیابی شوند انتخاب کنید و سپس آن را اجرا کنید. شما می توانید با تغییر 7 در سلول ها (r.Row, 7) - دو نمونه وجود دارد - تغییر دهید که کدام ستون شماره گذاری شده است تا شماره ستون مورد نظر شما را منعکس کند.

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

Sub NumberVisibleRows2()
    Dim J As Long
    Dim r As Range
    Dim iStart As Long

    iStart = 2

    J = 0
    For Each r In ActiveSheet.UsedRange.Rows
        Cells(r.Row, 7) = ""
        If Not r.Hidden And r.Row >= iStart Then
            J = J + 1
            Cells(r.Row, 7) = J
        End If
    Next r
End Sub

برای اینکه این کار به درستی انجام شود، کافیست متغیر iStart را برابر با شماره ردیفی که می خواهید ماکرو به آن توجه کند، تنظیم کنید. بنابراین، در مثال بالا، ردیف اول نادیده گرفته می‌شود زیرا iStart برابر با 2 است. این به ماکرو اجازه می‌دهد تا هر ردیف سرصفحه‌ای را که ممکن است در داده‌های خود داشته باشید، نادیده بگیرد.