• استفاده از Vlookup اکسل با استفاده از رنگ پس زمینه سلول

    با استفاده از تابع قدرتمند Vlookup اکسل رنگ پس زمینه سلول رو تغییر دهید و محیط رنگی در پروژه اکسل خود بسازید

    VLOOKUP بر اساس رنگ پس زمینه سلول

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

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

    در زیر کد VBA برای ایجاد VLOOKUP توسط تابع رنگ پس‌زمینه سلول وجود دارد.
    Function myvlookup(r1 As Range, r2 As Range, n As Integer, a As Boolean) As Variant
    Application.Volatile
    Dim cel As Range
    Dim i As Integer
    i = 0
    For Each cel In r2
     If cel.Interior.Color = r1.Interior.Color Then
     myvlookup = cel.Offset(0, n - 1).Value
     i = i + 1
     If a = False Then
     Exit For
     End If
     End If
    Next
    If i = 0 Then myvlookup = "#N/A"

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

    • کد بالا را کپی کرده و با فشردن کلیدهای Alt+F11 ویرایشگر ویژوال بیسیک را باز کنید.
    • با فشار دادن Alt+I+M یک ماژول جدید وارد کنید.
    • کد را در ماژول قرار دهید.
    • کتاب کار را به عنوان یک کتاب کار دارای قابلیت ماکرو ذخیره کنید.
    • پس از انجام این مراحل، می توانید از تابع myvlookup در کاربرگ خود برای بازیابی داده ها بر اساس رنگ پس زمینه سلول استفاده کنید.

    چگونه از این تابع در کاربرگ اکسل استفاده کنیم؟

    فرض کنید جدولی از داده های فروش در محدوده "A1:B15" دارید و می خواهید داده های فروش را از ستون B بر اساس رنگ پس زمینه سلول ها در محدوده "D2:D4" بازیابی کنید. در اینجا نحوه انجام این کار آمده است:

     

     

    استفاده از Vlookup اکسل با استفاده از رنگ پس زمینه سلول

    فرمول زیر را در سلول E2 وارد کنید. این فرمول به اکسل می‌گوید که مقدار را در سلول D2 جستجو کند و مقدار مربوطه را از ستون B بازیابی کند.
    فرمول را در سلول‌های E3 و E4 کپی کنید تا داده‌های فروش برای هر سلول در محدوده D2:D4 بازیابی شود.

    =myvlookup(D2,$A$1:$B$15,2,0)

     

     

    استفاده از Vlookup اکسل با استفاده از رنگ پس زمینه سلول

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

    همانطور که می بینید در تصویر زیر مقدار موجود در ستون E اولین مقادیر یافت شده است.

    استفاده از Vlookup اکسل با استفاده از رنگ پس زمینه سلول

    برای بدست آوردن مقداری که در آخرین موجود است، باید از 1 به جای 0 در فرمول myvlookup استفاده کنیم

    =myvlookup(D2,$A$1:$B$15,2,1)

    استفاده از Vlookup اکسل با استفاده از رنگ پس زمینه سلول

    حالا آخرین مقدار آن رنگ پس زمینه سلول را می گیریم.

    استفاده از Vlookup اکسل با استفاده از رنگ پس زمینه سلول

    لطفاً توجه داشته باشید که چون این فرمول بر اساس رنگ پس‌زمینه سلول‌ها است، هنگامی که رنگ پس‌زمینه سلول را تغییر می‌دهید، به‌طور خودکار بازخوانی نمی‌شود. برای دریافت مقدار به روز شده از فرمول، باید به برگه فرمول بروید و بر روی “ Ccalate Now ” کلیک کنید یا کلید میانبر F9 را فشار دهید تا فرمول دوباره محاسبه شود.

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

     

    سوالات متداول:

    هدف VLOOKUP با رنگ پس زمینه سلول چیست؟

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

    چگونه می توانم یک تابع تعریف شده توسط کاربر (UDF) برای VLOOKUP با رنگ پس زمینه سلول ایجاد کنم؟

    1. برای ایجاد یک UDF برای VLOOKUP با رنگ پس‌زمینه سلول، این مراحل را دنبال کنید:
    2. کد VBA ارائه شده را کپی کنید.
    3. ویرایشگر ویژوال بیسیک را با فشار دادن Alt+F11 باز کنید .
    4. با فشار دادن Alt+I+M یک ماژول جدید وارد کنید .
    5. کد را در ماژول قرار دهید.
    6. کتاب کار را به عنوان یک کتاب کار با قابلیت ماکرو ذخیره کنید.

    چگونه از تابع myvlookup در کاربرگ اکسل خود استفاده کنم؟

    برای استفاده از تابع myvlookup در کاربرگ اکسل، فرمول "=myvlookup(D2,$A$1:$B$15,2,0)" را در سلول E2 وارد کنید. فرمول را طوری تنظیم کنید که با محدوده و ستون های سلولی مناسب برای مجموعه داده خاص شما مطابقت داشته باشد. در صورت نیاز فرمول را به سلول های دیگر کپی کنید.

    چگونه می توانم مقادیر بازیابی شده توسط تابع myvlookup را هنگام تغییر رنگ پس زمینه به روز کنم؟

    از آنجایی که تابع myvlookup بر اساس رنگ پس‌زمینه سلول است، هنگامی که رنگ پس‌زمینه را تغییر می‌دهید، به‌طور خودکار بازخوانی نمی‌شود. برای به‌روزرسانی مقادیر، به تب Formula بروید و روی «Calculate Now» کلیک کنید یا برای محاسبه مجدد فرمول، F9 را فشار دهید.

    آیا می توانم از تابع myvlookup برای بازیابی آخرین مقدار مطابق با رنگ پس زمینه سلول استفاده کنم؟

    بله، می‌توانید از تابع myvlookup برای بازیابی آخرین مقدار منطبق با رنگ پس‌زمینه سلول با تغییر آخرین آرگومان در فرمول از 0 به 1 استفاده کنید، مانند این: "=myvlookup(D2,$A$1:$B$15, 2،1)».

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

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

    نظرات ارسال شده ارسال نظر جدید
    برای تبادل نظر، می بایست در سایت وارد شوید

    ورود به سایت
تماس سبد خرید بالا