Завантажте зразок робочої книги
У цьому посібнику буде показано, як об’єднати значення клітинок на основі критеріїв за допомогою функції CONCAT у Excel та Google Таблицях.
Функція CONCAT
Користувачі Excel 2022+ мають доступ до функції CONCAT, яка використовується для об’єднання кількох рядків в один рядок.
Примітки:
- У нашому першому прикладі використовується функція CONCAT, тому вона недоступна для користувачів Excel до Excel 2022. Див. Наступний розділ цього підручника про те, як відтворити цей приклад у старих версіях Excel.
- Користувачі Google Таблиць також мають доступ до функції CONCAT, але на відміну від Excel, вона дозволяє об’єднати лише два значення або посилання на клітинки та не дозволяє вводити діапазони комірок. Дивіться наступний розділ про те, як цей приклад можна досягти у Google Таблицях, використовуючи замість цього функцію TEXTJOIN.
У цьому прикладі будуть використані функції CONCAT та IF у формулі масиву для створення текстового рядка Імена гравців які стосуються а Команда значення Червоний
| 1 | = CONCAT (IF (C3: C8 = "Червоний", B3: B8, "" |
Користувачам Excel 2022 потрібно буде ввести цю формулу як функцію масиву, натиснувши CTRL + SHIFT + ENTER. Користувачам пізніших версій Excel не потрібно виконувати цей крок.
Щоб пояснити, що робить ця формула, давайте розберемо її на кроки:
Ось наша остаточна формула:
| 1 | = CONCAT (IF (C3: C8 = "Червоний", B3: B8, "" |
Спочатку до формули додаються значення діапазону комірок:
| 1 | = CONCAT (IF ({"Червоний"; "Синій"; "Синій"; "Червоний"; "Синій"; "Червоний"} = "Червоний", {"A"; "B"; "C"; "D" ";" E ";" F "}," " |
Далі список Команда імена порівнюються зі значенням Червоний:
| 1 | = CONCAT (IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}, {"A"; "B"; "C"; "D"; "E"; "F"}, "" |
Функція IF замінює значення TRUE на Ім'я гравцята значення FALSE з ""
| 1 | = CONCAT ({"A"; ""; ""; "D"; ""; "F" |
Потім функція CONCAT об'єднує всі значення масиву в один текстовий рядок:
| 1 | = "АПД" |
Додавання роздільників або ігнорування порожніх значень
Якщо потрібно додати розмежувальні значення або текст між кожним значенням, або щоб функція ігнорувала порожні значення комірок, замість цього можна використовувати функцію TEXTJOIN.
Прочитайте нашу статтю TEXTJOIN If, щоб дізнатися більше.
Об’єднайте If - у попередній версії Excel 2022
Оскільки функції CONCAT та TEXTJOIN недоступні до версії Excel 2022, нам потрібно вирішити цю проблему іншим способом. Функція CONCATENATE доступна, але не приймає діапазони клітинок як вхідні дані або не дозволяє операції з масивом, тому ми повинні використовувати замість цього стовпчик -помічник із функцією IF.
У наступному прикладі показано, як використовувати допоміжний стовпець для створення текстового рядка Імена гравців які стосуються а Команда значення Червоний:
| 1 | = IF (C3 = "Червоний", B3, "" & D4 |
Першим кроком у цьому прикладі є використання функції IF для відтворення умови Команда = Червоний:
| 1 | = IF (C3 = "Червоний", B3, "" |
Далі ми можемо створити стовпець, який збирає список цих значень в одну клітинку, також посилаючись на клітинку під нею:
| 1 | = D3 та E4 |
Ця формула використовує символ & для об'єднання двох значень разом. Зауважте, що функцію CONCATENATE можна використати для створення точно такого ж результату, але метод & часто вважається кращим, оскільки він коротший і дає зрозуміти, яку дію виконує формула.
Потім ці два допоміжних стовпці можна об’єднати в одну формулу:
| 1 | = IF (C3 = "Червоний", B3, "" & D4 |
Підсумкова клітинка може посилатися на перше значення в Список гравців допоміжний стовпець:
| 1 | = D3 |
Об’єднайте If у Таблицях Google
Користувачам Google Таблиць слід використовувати функцію TEXTJOIN для об’єднання значень на основі умови.
Для отримання додаткової інформації про використання функції TEXTJOIN.
У цьому прикладі буде використано функції TEXTJOIN та IF для створення текстового рядка Імена гравців які стосуються а Команда значення Червоний
| 1 | = ARRAYFORMULA (TEXTJOIN ("", FALSE, IF (C3: C8 = "Червоний", B3: B8, "" |
Оскільки ця формула вимагає введення масивів для діапазонів комірок, функцію ARRAYFORMULA слід додати до формули, натиснувши CTRL + SHIFT + ENTER.
Для отримання додаткової інформації про використання функції TEXTJOIN для виконання конкатенації на основі умови.
