Как да комбинирам vlookup и sumif?

0 одобрения 0 неодобрения
попитан 2016 юни 29 в Excel от Badan експерт (249 точки)

Здравейте,

Имам следния въпрос към вас. Имам нужда от помощ в Ексел. Как мога да комбинирам vlookup и sumif, така че функцията да събира няколко стойности в една клетка?

Формула, която се опитвам да напиша, трябва да търси за определено число в дадена таблица и да връща всички стойности, които отговарят на числото, но не да ги сумира, а да ги събира (конкатенира) в една клетка, разделени със запетая.

Например в колони А и B имам:

AB
1ivan
2pesho
2stoyan
2dragan
3jivko

Искам срещу числата в колона C да видя всички думи в D, т.е. :

CD
1ivan
2pesho, stoyan, dragan
3jivko
 

Не успях да намеря решение на този проблем. Има ли функция, която може да връща всички стойности?

Мерси предварително!

1 отговор

0 одобрения 0 неодобрения
отговорени 2016 юни 29 от BgExcelAdmin цар (651 точки)

Здравей,

До колкото знам, няма вградена функция в ексел, която да направи това. Може да си създадеш такава функция, ето как се прави:

1. отваряш VBA editor в ексел (alt+ F11)

2. дясно копче на modules -> insert -> module

3. пействаш следния код:

Function MyVlookup(lookupValue As Variant, lookuprange As Range, resultsRange As Range) As String

    Dim s As String 'Results placeholder
    Dim sTmp As String  'Cell value placeholder
    Dim r As Long   'Row
    Dim c As Long   'Column
    Const strDelimiter = "|||"  'Makes InStr more robust

    s = strDelimiter
    For r = 1 To lookuprange.Rows.Count
        For c = 1 To lookuprange.Columns.Count
            If lookuprange.Cells(r, c).Value = lookupValue Then
                'I know it's weird to use offset but it works even if the two ranges
                'are of different sizes and it's the same way that SUMIF works
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next

    'Now make it look like CSV
    s = Replace(s, strDelimiter, ",")
    If Left(s, 1) = "," Then s = Mid(s, 2)
    If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

    MyVlookup = s 'Return the function

End Function

4. Вече имаш нова функция =MyVlookup()

5. Ако в колона C са стойностите с числата , в D1 пишеш следната формула и влачиш надолу:

=MyVlookup(C1,$A$1:$A$7,$B$1:$B$7)

6. За да запазиш файла с новата функция, трябва да запазиш документа като macro-enabled, т.е. file ->save as -> Excel macro enabled workbook

Това е всичко, успех!

3 коментари

коментиран 2016 юли 1 от Badan експерт (249 точки)
Мерси много, работи наистина! Вече ще знам как мога да си създам собствена формула :) (beer)
коментиран 2016 декември 30 от BgExcelAdmin цар (651 точки)
Радвам се, че ти е от полза. Бях правил подобна формула преди известно време, затова беше сравнително лесно да я адаптирам за този конкретен случай. Темата е много полезна, за всички които искат да знаят как се създава функция в Ексел. Така че ние ти благодарим за хубавия въпрос.
коментиран 2017 януари 6 от Иванка Тръмп
Темата е хубава, научих нещо интересно днес, благодарение на вас! Благодаря!
Добре дошли в българския форум за Ексел и Майкрософт офис! Тук можете да питате хора със знания и опит, както и да споделите знанията и опита си с другите.
...