☰

Выполнить функцию ВПР в Excel для нескольких строк данных с одинаковым значением поиска (первичный ключ)

tag-icon • tag-icon microsoft-excel
Выполнить функцию ВПР в Excel для нескольких строк данных с одинаковым значением поиска (первичный ключ)

Я хочу выполнить ВПР для имен, которые имеют несколько строк для одного и того же первичного ключа:

Deal ID (Primary Key) | Name
437                   | Tom Jones
437                   | Frank Thomas
437                   | Mary Smith

Vlookup вернет только первое имя в записи (Том Джонс). Я бы хотел, чтобы это выглядело так: Том Джонс, Фрэнк Томас, Мэри Смит все в одной строке.

решение1

Похоже, вы пытаетесь использовать Excel как реляционную базу данных, в чем он не так уж хорош. В довершение всего, если только за последние годы ничего не изменилось (я не имел удовольствия использовать что-либо позднее Excel 2010), нет функции листа, которая могла бы превратить диапазон или массив в строку с разделителями. Вам придется придумать функцию листа VBA, которая это делает.

Вы можете использовать комбинацию формулы массива и VBA, чтобы сделать то, что вам нужно. Прежде чем вы пойдете дальше, я настоятельно рекомендую вам использовать настоящую программу базы данных, если вы обнаружите, что делаете что-то подобное на регулярной основе. Я злоупотреблял Excel, выполняя простые задачи, подобные реляционным базам данных, в прошлом, но только простые. Если бы мне пришлось делать что-то более сложное, это было бы крайне болезненно.

Предположим, что ваш идентификатор для поиска находится в ячейке F1, а ваша таблица называется Table1, вы вводите следующее в качествеформула массива(используйте control-shift-enter вместо enter). SimpleCat— это функция конкатенации рабочего листа VBA.

=SimpleCat(IF(Table1[Deal ID (Primary Key)]=$F$1, Table1[Name], ""))

Для данного конкретного случая подойдет SimpleCatследующее:

Function SimpleCat(Args() As Variant) As Variant
    Dim a As Variant
    SimpleCat = ""
    For Each a In Args
        If a <> "" Then SimpleCat = SimpleCat & a & ", "
    Next
    If Len(SimpleCat) > 0 Then SimpleCat = Left$(SimpleCat, Len(SimpleCat) - 2)
End Function

Я оставляю это в качестве упражнения, чтобы вы могли расширить SimpleCatего и сделать более общим, поскольку наличие более полнофункциональной универсальной функции конкатенации полезно в арсенале любого человека.

Объяснение:

При оценке в качестве формулы массива сравнение всего столбца таблицы с одним значением даст массив TRUEs и FALSEs. Использование IFсоздаст новый массив, извлекая значения из правильной позиции массива, который вы используете на стороне значения, если true IF, и заполняя пустую строку, которую вы используете на стороне значения, если false IF. SimpleCatФункция помещает все непустые значения в этот новый массив вместе, разделяя их запятыми.

решение2

Две проблемы. Во-первых, это не первичный ключ; во-вторых, в Excel нет встроенной функции, которая даст вам нужный результат.

Первичный ключ должен быть уникальным идентификатором, привязанным к одной записи, и который никогда-никогда не повторяется. Именно для этого и предназначены большинство функций поиска в Excel, поэтому они останавливаются, когда находят совпадение — это экономит ресурсы, поскольку не нужно каждый раз просматривать весь диапазон, если совпадение уже найдено.

Единственный способ, который я вижу, чтобы сделать тот тип консолидации, который вы ищете здесь, вытягивая несколько значений в одну строку, это использовать макрос или пользовательскую функцию. Какой именно код вам нужен, будет зависеть от точного результата, который вы ищете, и, боюсь, выходит за рамки этого ответа.

решение3

Я бы взял эту функцию на себя...

Function ConcatRange(inputRange As Range, Optional delimiter As String) As String
    Dim oneCell As Range
    Dim usedRange As Range

    Set usedRange = Application.Intersect(inputRange.Parent.usedRange, inputRange.Cells)
    If Not (usedRange Is Nothing) Then
        For Each oneCell In usedRange
            If oneCell.Text <> vbNullString Then
                ConcatRange = ConcatRange & delimiter & Trim(oneCell.Text)
            End If
        Next oneCell
        ConcatRange = Mid(ConcatRange, Len(delimiter) + 1)
    End If
End Function

и модифицируем его, чтобы создать новую функцию, которая будет выглядеть примерно так:

Function ConcatIf(KeyRange as Range, KeyValue as Variant, DataColumnOffset As Integer)

где он будет проходить по каждой ячейке в KeyRange и, только если ее значение будет равно KeyValue, объединять значение изoneCell.Offset(0, DataColumnOffset)

Связанный контент