فیلتر مقادیر بر اساس رنگ سلول

با درود

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

روش اول: استفاده از ابزار Sort

سلول‌های حاوی اطلاعات را انتخاب کنید.

روی منوی Data کلیک کنید.

از منوی Sort، گزینه Sort by color را انتخاب کنید.

رنگ سلول‌هایی که می‌خواهید فیلتر کنید را با استفاده‌ از add Level انتخاب کنید.

روی OK کلیک کنید.

روش دوم: استفاده از تابع Filter با استفاده از فرمت زیر:

=FILTER(range, (cell_range/font_color) = "color1", (cell_range/font_color) = "color2")

در این فرمول، "range" با محدوده داده های شما جایگزین شود، "cell_range" با بازه سلولی که شما می خواهید رنگ آنها را بررسی کنید، "font_color" با نوع مشخص کننده رنگ متن ("font" بررسی رنگ متن و "color" بررسی رنگ پس زمینه سلول ) و "color1" و "color2" با نام رنگ هایی که شما می خواهید روی آنها فیلتر کنید جایگزین شود.

تابع FILTER از توابع جستجو اکسل می باشد که از آفیس 2021 و یا ورژن 365 به اکسل اضافه شده است و می توان گفت قابلیت فیلتر پیشرفته را به راحتی به توابع اکسل اضافه کرده است .

تذکر: بستگی به ورژن اکسل مورد استفاده و همچنین معماری هر فایل اکسل موارد بالا قابل استفاده است.

سلام،

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

کد VBA زیر را در نظر بگیرید:

Sub getCellColor()
    colNum = 1
    headRow = 1
    
    ''get last column and row of data
    RowCount = Cells(Rows.Count, colNum).End(xlUp).Row
    lastCol = Cells(headRow, Columns.Count).End(xlToLeft).Column
    
    ''set header for new column
    Cells(headRow, lastCol + 1).Value = colorCodes
    
    ''get backround color codes and write in new column
    For i = headRow + 1 To RowCount
        colorCode = Cells(i, colNum).DisplayFormat.Interior.Color
        Cells(i, lastCol + 1).Interior.Color = colorCode
        Cells(i, lastCol + 1).Value = colorCode
    Next i
    
    ''is done
    MsgBox "Done!"
End Sub

در کد بالا ابتدا مقدار colNum را با شماره ستونی که می‌خواهید رنگ‌ها را روی آن فیلتر کنید تغییر دهید (شماره ستون نه اسم ستون، مثلا ستون A شماره 1 است و به همین ترتیب)

سپس headRow را با شماره سطری که عنوان ستون‌های شما است (و داده‌های شما از سطر بعدی شروع می‌شود) جایگزین کنید و سپس کل کد را یکجا کپی کنید.

در فایل اکسل ALT+F11 صفحه کلید بزنید ودر پنجره VBA باز شده، از سمت چپ روی اسم شیت مدنظر دوبار کلیک کرده و در صفحه سفید و خالی باز شده، کل کد را paste دهید.

در همین صفحه یکبار F5 صفحه کلید را بزنید (یا دکمه فلش سبزرنگ در نوار ابزار صفحه) تا کد اجرا شود. الان در شیت مدنظر یک ستون جدید حاوی کد رنگ‌ها است که می‌توانید مانند فیلترهای عادی اکسل روی مقادیر این ستون (اعداد کد رنگ) فیلتر انجام دهید.

توجه: قبل از ذخیره فایل، کدهای VBA را حذف کنید یا فایل را با فرمت جدید xlsm ذخیره کنید تا خطا دریافت نکنید.

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