Объединение значений из нескольких совпадений в одну ячейку

Question

У меня есть таблица, которую я пытаюсь настроить следующим образом:

A    | B       | C       | D         | ... | G        | H
Date   Trainer   Trainee   Procedure         Employee   Credentials
...    Aster     Lisa      A                 Aster      A
...    Frank     Lisa      B                 Frank      C, A
...    Frank     Aster     A                 Lisa       A, B, C
...    Lark      Frank     C                 Lark       
...    Lark      Frank     A
...    Frank     Lisa      C

Мой вопрос заключается в том, какую формулу я могу использовать, чтобы столбец учетных данных содержал объединенный список всех процедур, чья запись обучаемого соответствует записи сотрудника? В идеале не должно быть необходимости сортировать данные, так как они уже отсортированы по дате.

Answer 1

Я предлагаю решение, которое будет работать в LibreOffice Calc 5.4. Он использует встроенную функцию TEXTJOIN, которая доступна только в последних версиях LibreOffice Calc. Поэтому, если вы используете более старую версию, попробуйте обновить до v5.4.

В этом примере пример данных находится в ячейках C2:D8. Кроме того, оставьте первую строку в качестве строки заголовка, чтобы формула работала правильно, так как первая строка указывается в решении для правильной работы.

Имена стажеров помещаются в ячейки E2:E6.

Теперь в F2 введем следующую формулу.

=IFERROR(TEXTJOIN(",",1,IFERROR(INDEX($D$2:$D$8,N(IF(1,IF($C$2:$C$8=E2,ROW($D$2:$D$8)-ROW($D$1),9^99)))),"")),"")

Нажмите CTRL + SHIFT + ENTER (CSE) на панели формул, чтобы создать формулу массива. Без CSE формула не будет работать правильно.

Теперь скопируйте и вставьте эту формулу для всех применимых ячеек с F3 по F6. Это проверено на ОС Windows, должно работать нормально, даже если вы работаете в Linux.

Поскольку в вашем теге упоминается LibreOffic Calc, это решение немного специфично для LibreOffice Calc. Если вы используете MS Excel, TEXTJOIN доступен только в Office 2016, и я полагаю, версия Office 365. В этом случае, если TEXTJOIN не доступен изначально, вам нужно кодировать его в VBA.

Answer 2

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

Тем не менее, я выбрал бы использование Base с таким движком, как MySQL. Запрос будет использовать GROUP_CONCAT() как описано на https://stackoverflow.com/a/194875/5100564. Данные могут быть перемещены в базу путем перетаскивания выделения из Calc на значок таблиц.

Более новые версии HSQL также поддерживают GROUP_CONCAT. Однако старый HSQL 1.8, который поставляется со встроенным LibreOffice, этого не делает.