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