Как найти все ссылки на ячейки в строке, содержащие значение, и отобразить их в одной ячейке?

Question

Эта проблема:

У меня есть лист Excel, который я использую для довольно большой матрицы прослеживаемости требований, используя столбцы для номеров тестов QA и строки для требований. Я предоставил возможность показывать количество тестовых случаев, которые были использованы против одного требования, располагая ячейку рядом с требованием, используя COUNTIF для "x" для каждой строки. Хотя это покажет количество тестов, выполненных в соответствии с этим требованием, оно НЕ покажет фактический номер используемого теста QA. Это иногда требует от пользователя прокрутки влево / вправо, чтобы найти точные номера тестовых примеров (в заголовках столбцов), которые были использованы против этого требования.

• Например: Требование 2.1.1 показывает "5" как число тестов, использованных против него. Пользователь должен прокрутить вправо, пока не найдет все X в ячейке, и записать / запомнить номер QA в столбце, в котором он находится.

Я включил скриншот, чтобы показать, что я имею в виду.

Что я ищу:

Я хотел бы добавить столбец рядом с "E" (тот, который считает х), который будет отображать каждый номер теста QA (заголовки столбцов G5:HR5), который имеет "X" для данного требования / строки.

Пример: Требование 1.1.2 имеет четыре x в этом ряду, обозначенных четырьмя различными тестовыми примерами, использованными для этого требования (QA-466, QA-467, QA-468 и QA-470). Я хотел бы, чтобы где-то в строке была ячейка с требованием 1.1.2 "QA-466 QA-467 QA-468 QA-470".

У меня такое ощущение, что мне понадобится больше, чем несколько скрытых ячеек, чтобы запустить некоторые формулы, чтобы сделать эту работу, и что в какой-то момент я буду использовать REFERENCE и OFFSET... но где и как?

Answer 1

Если у вас OFFICE 365 Excel, используйте TEXTJOIN() в качестве формулы массива в F9:

=TEXTJOIN(" ",TRUE,IF(G9:HR9="x",$G$5:$HR$5,""))

Будучи формулой массива, она должна быть подтверждена с помощью Ctrl-Shift-Enter вместо ввода при выходе из режима редактирования. Если все сделано правильно, то Excel поставит {} вокруг формулы.

Небольшой пример:

---

НОТА

TEXTJOIN был введен в ОФИС 365 Excel.

Если у вас нет функции TEXTJOIN, поместите следующее в модуль, прикрепленный к книге. И используйте формулу, как описано выше:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN2 = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function