Изтеглете примерната работна книга
Този урок ще покаже как да обедините стойностите на клетките въз основа на критерии, използвайки функцията TEXTJOIN в Excel и Google Sheets.
Функцията TEXTJOIN
Потребителите на Excel 2022+ имат достъп до функцията TEXTJOIN, която обединява множество низове текст заедно, разделяйки всеки низ с определен делимитер.
Забележка: Потребителите на Google Sheets могат да използват функцията TEXTJOIN, но имат малко по -различен начин за въвеждане на формули за масиви. Вижте по -късния раздел по тази тема.
Този пример ще използва функциите TEXTJOIN и IF във формула на масив, за да създаде текстов низ, разделен със запетая Имена на играчи които се отнасят до а Екип стойност на червен
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Red", B3: B8, "")) |
Потребителите на Excel 2022 ще трябва да въведат тази формула като функция на масив, като натиснат CTRL + SHIFT + ENTER. Потребителите на по -късни версии на Excel не трябва да следват тази стъпка.
За да обясним какво прави тази формула, нека я разделим на стъпки:
Това е нашата последна формула:
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Red", B3: B8, "")) |
Първо, стойностите на диапазона от клетки се добавят към формулата:
| 1 | = TEXTJOIN (",", TRUE, IF ({"Red"; "Blue"; "Blue"; "Red"; "Blue"; "Red"} = "Red", {"A"; "B"; "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 = "Red", B3 & ",", "") & D4 |
Първата стъпка в този пример е да използвате IF функция, за да повторите условието на Екип = Червен. Ако условието е изпълнено, Име на играча стойността се изписва и следва от избрания разделител „,“:
| 1 | = IF (C3 = "Червено", B3 & ",", "") |
След това можем да създадем колона, която изгражда списък с тези стойности в една клетка, като също така препращаме към клетката под нея:
| 1 | = D3 и E4 |
Тази формула използва знака & за свързване на две стойности заедно. Обърнете внимание, че функцията CONCATENATE може да се използва за създаване на абсолютно същия резултат, но методът & често се предпочита, тъй като е по -кратък и прави по -ясно какво действие изпълнява формулата.
След това тези две помощни колони могат да бъдат комбинирани в една формула:
| 1 | = IF (C3 = "Red", B3 & ",", "") & D4 |
Обобщена клетка може след това да се позовава на първата стойност в Списък на играчите помощна колона:
| 1 | = D3 |
Ако крайната стойност „,“ не се изисква, към формулата трябва да се добави допълнителна вложена IF функция:
| 1 | = IF (C3 = "Red", IF (D4 = "", B3, B3 & ","), "") & D4 |
Допълнителната част от формулата е функцията IF по -долу:
| 1 | IF (D4 = "", B3, B3 & ",") |
Тази част от формулата проверява стойността на клетката под нея. Ако тя е празна, тогава тази клетка трябва да е последната Име на играча в списъка и името е написано без разделител. Ако клетката по -долу съдържа стойност, тази клетка не е последната Име на играча в списъка и така се добавя разделителят „,“.
TextJoin Ако в Google Таблици
Тези формули работят по същия начин в Google Sheets, както и в Excel, с изключение на това, че функцията ARRAYFORMULA се изисква да се използва в Google Sheets, за да оцени правилно резултатите. Това може да бъде добавено автоматично чрез натискане на клавишите CTRL + SHIFT + ENTER, докато редактирате формулата:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Red", B3: B8, "")))) |
