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" بازیابی کنید. در اینجا نحوه انجام این کار آمده است:

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

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

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

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

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

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

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

لطفاً توجه داشته باشید که چون این فرمول بر اساس رنگ پس‌زمینه سلول‌ها است، هنگامی که رنگ پس‌زمینه سلول را تغییر می‌دهید، به‌طور خودکار بازخوانی نمی‌شود. برای دریافت مقدار به روز شده از فرمول، باید به برگه فرمول بروید و بر روی “ 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 بر اساس رنگ پس زمینه سلول می تواند یک افزودنی ارزشمند به جعبه ابزار اکسل شما باشد. این به شما امکان می دهد داده ها را بر اساس رنگ پس زمینه سلول بازیابی کنید، که می تواند در کارهای مختلف تجزیه و تحلیل داده ها مفید باشد. در حالی که به تنظیمات اولیه نیاز دارد، مزایای آن می تواند ارزش تلاش را داشته باشد.