excel - एक्सेल गठबंधन Vlookups




excel-formula (2)

मेरे पास दो फाइल हैं एक प्रोजेक्ट रजिस्टर जो कि एक प्रोजेक्ट पर महत्वपूर्ण जानकारी रखता है और दूसरा जोखिम लॉग है I

रजिस्टर और द रिस्क लॉग में प्रविष्टियों के बीच 1: मी रिश्ते हैं मुझे क्या करने की ज़रूरत है परियोजना रजिस्टर फ़ाइल के अंदर एक सेल में एक परियोजना जोखिम सभी को जोड़ता है।

दोनों फाइलों में मेल खाने वाला फ़ील्ड परियोजना आईडी फ़ील्ड है

क्या ऐसा कोई तरीका है जो मैं इसे vlookup संस्करण या एकाधिक नेस्टेड vlookups का उपयोग कर कर सकता हूं?



यहां मैंने उपयोगकर्ता-परिभाषित फ़ंक्शन दृष्टिकोण का उल्लेख किया है (एक अलग VLOOKUP- संस्करण से लिया गया है जो मैंने पहले ही बनाया था):

' Acts like VLOOKUP in a 1-to-many scenario by concatenating all values in matching rows
' instead of just returning the first match
Public Function VLOOKUP_MANY(lookup_value As String, lookup_range As Range, column_number As Integer, Optional delimiter As Variant) As Variant
    Dim vArr As Variant
    Dim i As Long
    Dim found As Boolean: found = False

    ' Set default delimiter
    If IsMissing(delimiter) Then delimiter = ", "

    ' Get values
    vArr = lookup_range.Value2

    ' If column_number is outside of the specified range, return #REF
    If column_number < LBound(vArr, 2) Or column_number > UBound(vArr, 2) Then
        VLOOKUP_MANY = CVErr(xlErrRef)
        Exit Function
    End If

    ' Search for matches and build a concatenated list
    VLOOKUP_MANY = ""
    For i = 1 To UBound(vArr, 1)
        If UCase(vArr(i, 1)) = UCase(lookup_value) Then
            VLOOKUP_MANY = VLOOKUP_MANY & delimiter & vArr(i, column_number)
            found = True ' Mark at least 1 result
        End If
    Next

    If found Then
        VLOOKUP_MANY = Right(VLOOKUP_MANY, Len(VLOOKUP_MANY) - Len(delimiter)) ' Remove first delimiter
    Else
        VLOOKUP_MANY = CVErr(xlErrNA) ' If no matches found, return #N/A
    End If
End Function

यह निर्दिष्ट मूल्य के लिए निर्दिष्ट श्रेणी में पहले कॉलम को खोजता है (वही VLOOKUP के समान है), लेकिन निर्दिष्ट कॉलम संख्या में मूल्यों को संमिलित करता है। जब कोई मैचों नहीं मिले, और # आरईएफ वापस आने पर कॉलम नंबर के लिए एक अमान्य मूल्य निर्दिष्ट होता है (जैसे आप कॉलम 5 चुनते हैं, लेकिन केवल 4-स्तंभ तालिका होती है)।

यदि आप उपयोगकर्ता-परिभाषित फ़ंक्शंस के बारे में नहीं जानते हैं - तो आप अपने कार्यपुस्तिका में एक मॉड्यूल के लिए वीबीई कोड में इस वीबीए कोड को कॉपी कर सकते हैं। हिट Alt + F11, स्क्रीन के शीर्ष पर Insert > Module पर जाएं, फिर इस कोड को रिक्त फ़ाइल में पेस्ट करें जो खुलता है। जब आप सहेजने के लिए जाते हैं, तो आपको अपना कार्यपुस्तिका मैक्रो-सक्षम (.xlsm) के रूप में कोड को काम करने के लिए सहेजना होगा - Excel आपको इस बारे में सहेज स्क्रीन में याद दिलाएगा।

पहले चेतावनी दीजिए: VLOOKUP के मुकाबले धीमी होने जा रहा है, इसके परिणामस्वरूप पूरे लुकअप रेंज को देखने के बजाय पहले मैच में इसे रोकने में सक्षम होने के बजाय

यदि आप इसके बजाय एक सरणी सूत्र का उपयोग करने के लिए खुले हैं, तो बहुत बड़े डेटासेट के लिए इस प्रकार की कार्यक्षमता को गति देने के तरीके हैं ...

विभिन्न संस्करण जो कि लुकअप मूल्यों को स्टोर करने के लिए सरणी फ़ार्मुलों के कुछ लाभों को बढ़ाता है और बाद में कॉल करता है:

' Acts like VLOOKUP in a 1-to-many scenario by concatenating all values in matching rows
'  instead of just returning the first match
' Utilizes a dictionary to speedup multiple matches (great for array formulas)
Public Function VLOOKUP_MANY_ARRAY(lookup_values As Range, lookup_range As Range, column_number As Integer, Optional delimiter As Variant) As Variant
    Dim vHaystack As Variant, vNeedles As Variant
    Dim i As Long
    Dim found As Boolean: found = False
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    ' Set default delimiter
    If IsMissing(delimiter) Then delimiter = ", "

    ' Get values
    vHaystack = lookup_range
    vNeedles = lookup_values

    ' If column_number is outside of the specified range, return #REF
    If column_number < LBound(vHaystack, 2) Or column_number > UBound(vHaystack, 2) Then
        VLOOKUP_MANY_ARRAY = CVErr(xlErrRef)
        Exit Function
    End If

    ' Add values to a lookup dictionary
    For i = 1 To UBound(vHaystack, 1)
        If dict.Exists(UCase(vHaystack(i, 1))) Then
            dict.Item(UCase(vHaystack(i, 1))) = dict.Item(UCase(vHaystack(i, 1))) & delimiter & vHaystack(i, column_number)
        Else
            dict.Add UCase(vHaystack(i, 1)), vHaystack(i, column_number)
        End If
    Next

    Dim outArr As Variant
    If IsArray(vNeedles) Then ' Check number of lookup cells
        ' Build output array
        ReDim outArr(1 To UBound(vNeedles, 1), 1 To 1) As Variant

        For i = 1 To UBound(vNeedles, 1)
            If dict.Exists(UCase(vNeedles(i, 1))) Then
                outArr(i, 1) = dict.Item(UCase(vNeedles(i, 1)))
            Else
                outArr(i, 1) = CVErr(xlErrNA)
            End If
        Next
    Else
        ' Single output value
        If dict.Exists(UCase(vNeedles)) Then
            outArr = dict.Item(UCase(vNeedles))
        Else
            outArr = CVErr(xlErrNA)
        End If
    End If

    VLOOKUP_MANY_ARRAY = outArr
End Function

यह एक Dictionary बनाता है, जो एक विशेष संरचना है जो मूल्यों को देखने के लिए वास्तव में अच्छा है। इसे बनाने में थोड़ा अतिरिक्त ओवरहेड शामिल है, लेकिन एक बार आपके पास संरचना है, तो आप इसे बहुत जल्दी में देख सकते हैं। यह विशेष रूप से सरणी फ़ार्मुलों के साथ अच्छा होता है, जो मूल रूप से तब होता है जब सटीक समान सूत्र को कोशिकाओं के पूरे संग्रह में डाल दिया जाता है, तो फ़ंक्शन एक बार कार्यान्वित करता है और हर सेल के लिए मान देता है (केवल एक बार निष्पादित करने के बजाय, अलग से, कोशिकाओं के एक समूह के लिए )। इसे CTRL + SHIFT + ENTER के साथ एक सरणी सूत्र की तरह दर्ज करें, और पहली तर्क को सिर्फ एक के बजाय अपने सभी लुकअप मानों को देखें।

यह एक सरणी सूत्र के रूप में उपयोग किए बिना काम करेगा, लेकिन यह उस स्थिति में पहले फ़ंक्शन की तुलना में कुछ हद तक धीमी गति से होगा। हालांकि, यदि आप इसे किसी सरणी सूत्र में उपयोग करते हैं, तो आपको बड़ी गति से देखेंगे।





vlookup