Загрузите образец книги
В этом руководстве будет показано, как объединить значения ячеек на основе критериев с помощью функции TEXTJOIN в Excel и Google Sheets.
Функция TEXTJOIN
Пользователи Excel 2022+ имеют доступ к функции TEXTJOIN, которая объединяет несколько строк текста вместе, разделяя каждую строку указанным разделителем.
Примечание: Пользователи Google Таблиц могут использовать функцию TEXTJOIN, но имеют несколько иной способ ввода формул массива. См. Следующий раздел по этой теме.
В этом примере будут использоваться функции TEXTJOIN и IF в формуле массива для создания текстовой строки, разделенной запятыми. Имена игроков которые относятся к Команда значение красный
| 1 | = TEXTJOIN (","; ИСТИНА; ЕСЛИ (C3: C8 = "Красный"; B3: B8; "")) |
Пользователям Excel 2022 потребуется ввести эту формулу как функцию массива, нажав CTRL + SHIFT + ENTER. Пользователям более поздних версий Excel не нужно выполнять этот шаг.
Чтобы объяснить, что делает эта формула, давайте разберем ее на шаги:
Это наша окончательная формула:
| 1 | = TEXTJOIN (","; ИСТИНА; ЕСЛИ (C3: C8 = "Красный"; B3: B8; "")) |
Сначала в формулу добавляются значения диапазона ячеек:
| 1 | = TEXTJOIN (",", ИСТИНА, ЕСЛИ ({"Красный"; "Синий"; "Синий"; "Красный"; "Синий"; "Красный"} = "Красный", {"A"; "B"; "C"; "D"; "E"; "F"}, "")) |
Далее список Команда имена сравниваются со значением красный:
| 1 | = TEXTJOIN (",", ИСТИНА, ЕСЛИ ({ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА}, {"A"; "B"; "C"; "D"; "E"; "F"} , "")) |
Функция ЕСЛИ заменяет значения ИСТИНА на Имя игрока, и значения FALSE с ""
| 1 | = TEXTJOIN (",", ИСТИНА, {"A"; ""; ""; "D"; ""; "F"}) |
Затем функция TEXTJOIN объединяет все значения массива в одну текстовую строку. Мы проинструктировали функцию игнорировать пустые значения и добавлять текст «,» между каждым значением. Это дает окончательный результат:
| 1 | = "A, D, F" |
TextJoin If - в версиях до Excel 2022
Поскольку функция TEXTJOIN недоступна до версии Excel 2022, нам нужно решить эту проблему другим способом. Функция CONCATENATE доступна, но не принимает диапазоны ячеек в качестве входных данных и не разрешает операции с массивами, поэтому вместо этого мы должны использовать вспомогательный столбец с функцией IF.
В следующем примере показано, как использовать вспомогательный столбец для создания текстовой строки, разделенной запятыми. Имена игроков которые относятся к Команда значение Красный:
| 1 | = ЕСЛИ (C3 = "Красный", B3 & ",", "") & D4 |
Первым шагом в этом примере является использование функции ЕСЛИ для воспроизведения условия Команда = Красный. Если условие выполнено, Имя игрока записывается значение, за которым следует выбранный разделитель «,»:
| 1 | = ЕСЛИ (C3 = "Красный", B3 & ",", "") |
Затем мы можем создать столбец, который объединяет список этих значений в одну ячейку, также ссылаясь на ячейку под ним:
| 1 | = D3 и E4 |
В этой формуле для соединения двух значений используется символ &. Обратите внимание, что функцию CONCATENATE можно использовать для получения точно такого же результата, но часто предпочтительнее использовать метод &, поскольку он короче и дает более четкое представление о том, какое действие выполняет формула.
Затем эти два вспомогательных столбца можно объединить в одну формулу:
| 1 | = ЕСЛИ (C3 = "Красный", B3 & ",", "") & D4 |
Затем итоговая ячейка может ссылаться на первое значение в Список игроков вспомогательный столбец:
| 1 | = D3 |
Если последнее значение «,» не требуется, в формулу следует добавить дополнительную вложенную функцию ЕСЛИ:
| 1 | = ЕСЛИ (C3 = "Красный", ЕСЛИ (D4 = "", B3, B3 & ","), "") & D4 |
Дополнительная часть формулы - это функция ЕСЛИ ниже:
| 1 | ЕСЛИ (D4 = "", B3, B3 & ",") |
Эта часть формулы проверяет значение ячейки под ней. Если он пуст, то эта ячейка должна быть последней. Имя игрока в списке и имя пишется без разделителя. Если ячейка ниже содержит значение, значит, эта ячейка не последняя. Имя игрока в списке, поэтому добавляется разделитель «,».
TextJoin If в Google Таблицах
Эти формулы работают в Google Таблицах так же, как и в Excel, за исключением того, что для правильной оценки результатов в Google Таблицах необходимо использовать функцию ARRAYFORMULA. Его можно автоматически добавить, нажав клавиши CTRL + SHIFT + ENTER при редактировании формулы:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", ИСТИНА, (ЕСЛИ (C3: C8 = "Красный", B3: B8, "")))) |
