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

Question

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

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

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

Answer 1

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

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

Предполагая, что ваш ID для поиска находится в ячейке 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 чтобы сделать его более общим, так как наличие более полнофункциональной универсальной функции конкатенации полезно в любом наборе инструментов.

Объяснение:

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

Answer 2

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

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.Смещение (0, DataColumnOffset)

Answer 3

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

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

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