با سلام خدمت کاربران عزیز آی تی سواد
با فرض اینکه شما یک جدول مانند تصویر زیر دارید، اکنون می خواهید بررسی کنید که آیا مقدار مشخص شده با فرمول در ستون A وجود دارد یا خیر و سپس مقدار مربوطه را به همراه رنگ زمینه در ستون B برگردانید چگونه می توان اینکار را انجام دهید؟ روش موجود در مقاله می تواند به شما در حل مشکل کمک کند.
لطفاً برای جستجوی یک مقدار و برگرداندن مقدار متناظر آن همراه با رنگ پس زمینه در Excel به صورت زیر عمل کنید.
۱٫ در شیتی که می خواهید vlookup را وارد کنید، روی برگه شیت کلیک راست کرده و از منوی زمینه View Code را انتخاب کنید. تصویر را ببینید:
۲٫ در پنجره Microsoft Visual Basic for Applications ، لطفاً کد زیر را در پنجره Code کپی کنید.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Sub Worksheet_Change(ByVal Target As Range) Dim I As Long Dim xKeys As Long Dim xDicStr As String On Error Resume Next Application.ScreenUpdating = False xKeys = UBound(xDic.Keys) If xKeys >= 0 Then For I = 0 To UBound(xDic.Keys) xDicStr = xDic.Items(I) If xDicStr <> "" Then Range(xDic.Keys(I)).Interior.Color = _ Range(xDic.Items(I)).Interior.Color Else Range(xDic.Keys(I)).Interior.Color = xlNone End If Next Set xDic = Nothing End If Application.ScreenUpdating = True End Sub |
۳٫ سپس روی Insert Module کلیک کرده و کد زیر را در پنجره Module کپی کنید.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Public xDic As New Dictionary Function LookupKeepColor (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long) Dim xFindCell As Range On Error Resume Next Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole) If xFindCell Is Nothing Then LookupKeepColor = "" xDic.Add Application.Caller.Address, "" Else LookupKeepColor = xFindCell.Offset(0, xCol - 1).Value xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address End If End Function |
۴- پس از قرار دادن دو کد ، سپس روی Tools >References کلیک کنید. سپس تیک گزینه Microsoft Script Runtime را در پنجره References – VBAProject علامت بزنید. تصویر را ببینید:
۵- کلیدهای Alt + Q را فشار دهید تا از پنجره Microsoft Visual Basic for Applications خارج شوید و به شیت موردنظر برگردید.
۶٫ یک سلول خالی در مجاورت مقدار جستجو انتخاب کنید ، و سپس فرمول(LookupKeepColor(H3;A2:B4;2= را در نوار فرمول وارد کنید ، و سپس کلید Enter را فشار دهید.
نکته: در فرمول ، H3 شامل مقداری است که شما جستجو می کنید ، A2:B4 محدوده جدول است و عدد ۲ به این معنی است که مقدار مربوطه در ستون دوم جدول قرار دارد . براساس نیاز آنها را تغییر دهید.
ترجمه شده از سایت www.extendoffice.com/documents/excel
ممنون از همراهی شما کاربران عزیز آی تی سواد
پانوشت:در صورتی که نوشته من مورد پسندتان قرار گرفت ،در شبکه های اجتماعی به اشتراک بگذارید، این بهترین تشکر خواهد بود.
چطور این روش رو به چند سلول تعمیم بدیم
یعنی بجای اینکه مقادیر یک سلول رو نمایش بده مقادیر چند سلول رو نمایش بده
سلام این روش با ساخت فرمول هست ، در فرمول نمی توان چند خروجی جواب باشد.
از راه های دیگر استفاده کنید