Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako spojiti vrijednosti ćelija na temelju kriterija pomoću funkcije TEXTJOIN u Excelu i Google tablicama.
Funkcija TEXTJOIN
Korisnici programa Excel 2022+ imaju pristup funkciji TEXTJOIN, koja spaja više nizova teksta zajedno, odvajajući svaki niz s navedenim razdjelnikom.
Bilješka: Korisnici Google tablica mogu koristiti funkciju TEXTJOIN, ali imaju malo drugačiji način unosa formula polja. Pogledajte kasnije odjeljak o ovoj temi.
Ovaj primjer će koristiti funkcije TEXTJOIN i IF u formuli niza za stvaranje tekstualnog niza odvojenog zarezima Imena igrača koji se odnose na a Tim vrijednost Crvena
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Crveno", B3: B8, "")) |
Korisnici programa Excel 2022 morat će unijeti ovu formulu kao funkciju niza pritiskom na CTRL + SHIFT + ENTER. Korisnici kasnijih verzija programa Excel ne moraju slijediti ovaj korak.
Da bismo objasnili što ova formula radi, podijelimo je na korake:
Ovo je naša konačna formula:
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Crveno", B3: B8, "")) |
Prvo se formuli dodaju vrijednosti raspona ćelija:
| 1 | = TEXTJOIN (",", TRUE, IF ({"Crveno"; "Plavo"; "Plavo"; "Crveno"; "Plavo"; "Crveno"} = "Crveno", {"A"; "B"; "C"; "D"; "E"; "F"}, "")) |
Slijedi popis Tim imena uspoređuje se s vrijednošću Crvena:
| 1 | = TEXTJOIN (",", TRUE, IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}, {"A"; "B"; "C"; "D"; "E"; "F"}) , "")) |
IF funkcija zamjenjuje TRUE vrijednosti sa Igračevo ime, i FALSE vrijednosti s ""
| 1 | = TEXTJOIN (",", TRUE, {"A"; ""; ""; "D"; ""; "F"}) |
Funkcija TEXTJOIN tada kombinira sve vrijednosti niza u jedan tekstualni niz. Uputili smo funkciju da zanemaruje prazne vrijednosti i da dodaje tekst “,” između svake vrijednosti. To daje konačni rezultat:
| 1 | = "A, D, F" |
TextJoin If - u pred -Excelu 2022
Budući da funkcija TEXTJOIN nije dostupna prije verzije programa Excel 2022, moramo riješiti ovaj problem na drugačiji način. Funkcija CONCATENATE je dostupna, ali ne uzima raspone ćelija kao ulaze niti dopušta operacije niza, pa smo umjesto toga dužni koristiti stupac pomoćnik s funkcijom IF.
Ovaj sljedeći primjer pokazuje kako koristiti pomoćni stupac za stvaranje tekstualnog niza odijeljenog zarezima Imena igrača koji se odnose na a Tim vrijednost Crvena:
| 1 | = IF (C3 = "Crveno", B3 & ",", "") & D4 |
Prvi korak u ovom primjeru je korištenje IF funkcije za repliciranje uvjeta Tim = Crvena. Ako je uvjet ispunjen, Igračevo ime vrijednost se upisuje i slijedi odabrani graničnik ",":
| 1 | = IF (C3 = "Crveno", B3 & ",", "") |
Zatim možemo stvoriti stupac koji sastavlja popis ovih vrijednosti u jednu ćeliju tako što ćemo referencirati ćeliju ispod nje:
| 1 | = D3 i E4 |
Ova formula koristi znak & za spajanje dvije vrijednosti. Imajte na umu da se funkcija CONCATENATE može koristiti za stvaranje potpuno istog rezultata, ali često se preferira metoda & jer je kraća i čini jasnije koju radnju formula izvodi.
Ove dvije pomoćne kolone tada se mogu kombinirati u jednu formulu:
| 1 | = IF (C3 = "Crveno", B3 & ",", "") & D4 |
Ćelija sažetka tada može referencirati prvu vrijednost u Popis igrača pomoćni stupac:
| 1 | = D3 |
Ako konačna vrijednost "," nije potrebna, dodatnu ugniježđenu IF funkciju treba dodati u formulu:
| 1 | = IF (C3 = "Crveno", IF (D4 = "", B3, B3 & ","), "") & D4 |
Dodatni dio formule je IF funkcija u nastavku:
| 1 | IF (D4 = "", B3, B3 & ",") |
Ovaj dio formule provjerava vrijednost ćelije ispod nje. Ako je prazna, onda ova ćelija mora biti posljednja Igračevo ime na popisu, a naziv je napisan bez graničnika. Ako ćelija ispod sadrži vrijednost, onda ta ćelija nije zadnja Igračevo ime na popisu i tako se dodaje razdjelnik “,”.
TextJoin If u Google tablicama
Ove formule rade isto u Google tablicama kao i u Excelu, samo što je funkcija ARRAYFORMULA potrebna za korištenje u Google tablicama za ispravnu procjenu rezultata. To se može automatski dodati pritiskom na tipke CTRL + SHIFT + ENTER tijekom uređivanja formule:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Crveno", B3: B8, "")))) |
