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