Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak zřetězit hodnoty buněk na základě kritérií pomocí funkce TEXTJOIN v Excelu a Tabulkách Google.
Funkce TEXTJOIN
Uživatelé aplikace Excel 2022+ mají přístup k funkci TEXTJOIN, která slučuje více řetězců textu dohromady a odděluje každý řetězec zadaným odstraňovačem.
Poznámka: Uživatelé Tabulek Google mohou používat funkci TEXTJOIN, ale mají trochu jiný způsob zadávání vzorců pole. Viz pozdější část na toto téma.
Tento příklad použije funkce TEXTJOIN a IF ve vzorci pole k vytvoření textového řetězce odděleného čárkami Jména hráčů které se týkají a tým hodnota Červené
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Red", B3: B8, "")) |
Uživatelé aplikace Excel 2022 budou muset zadat tento vzorec jako funkci pole stisknutím kláves CTRL + SHIFT + ENTER. Uživatelé novějších verzí aplikace Excel tento krok nemusí dodržovat.
Abychom vysvětlili, co tento vzorec dělá, rozdělme si ho na kroky:
Toto je náš konečný vzorec:
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "Red", B3: B8, "")) |
Nejprve se do vzorce přidají hodnoty rozsahu buněk:
| 1 | = TEXTJOIN (",", PRAVDA, IF ({"červená"; "modrá"; "modrá"; "červená"; "modrá"; "červená"} = "červená", {"A"; "B"; „C“; „D“; „E“; „F“}, „“)) |
Dále seznam tým jména se porovnává s hodnotou Červené:
| 1 | = TEXTJOIN (",", TRUE, IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}, {"A"; "B"; "C"; "D"; "E"; "F"} , "")) |
Funkce IF nahradí hodnoty TRUE hodnotou Jméno hráčea FALSE hodnoty s „“
| 1 | = TEXTJOIN (",", TRUE, {"A"; ""; ""; "D"; ""; "F"}) |
Funkce TEXTJOIN pak kombinuje všechny hodnoty pole do jednoho textového řetězce. Instruovali jsme funkci, aby ignorovala prázdné hodnoty a mezi každou hodnotu přidala text „,“. Výsledkem je konečný výsledek:
| 1 | = "A, D, F" |
TextJoin If - v pre -Excel 2022
Protože funkce TEXTJOIN není k dispozici před verzí aplikace Excel 2022, musíme tento problém vyřešit jiným způsobem. Funkce CONCATENATE je k dispozici, ale nebere rozsahy buněk jako vstupy ani neumožňuje operace pole, a proto jsme povinni místo toho použít pomocný sloupec s funkcí IF.
Tento další příklad ukazuje, jak pomocí pomocného sloupce vytvořit textový řetězec oddělený čárkami Jména hráčů které se týkají a tým hodnota Červené:
| 1 | = IF (C3 = "Červená", B3 & ",", "") & D4 |
Prvním krokem v tomto příkladu je použít funkci IF k replikaci podmínky Tým = Červené. Pokud je podmínka splněna, Jméno hráče hodnota je zapsána a následována zvoleným oddělovačem „,“:
| 1 | = IF (C3 = "Červená", B3 & ",", "") |
Dále můžeme vytvořit sloupec, který vytvoří seznam těchto hodnot do jedné buňky také odkazem na buňku pod ní:
| 1 | = D3 a E4 |
Tento vzorec používá znak & ke spojení dvou hodnot dohromady. Všimněte si toho, že funkci CONCATENATE lze použít k vytvoření přesně stejného výsledku, ale často je upřednostňována metoda &, protože je kratší a objasňuje, jakou akci vzorec provádí.
Tyto dva pomocné sloupce pak lze sloučit do jednoho vzorce:
| 1 | = IF (C3 = "Červená", B3 & ",", "") & D4 |
Souhrnná buňka pak může odkazovat na první hodnotu v Seznam hráčů pomocný sloupec:
| 1 | = D3 |
Pokud konečná hodnota „,“ není požadována, měla by být do vzorce přidána další vnořená funkce IF:
| 1 | = IF (C3 = "Červená", IF (D4 = "", B3, B3 & ","), "") & D4 |
Další částí vzorce je níže uvedená IF funkce:
| 1 | IF (D4 = "", B3, B3 & ",") |
Tato část vzorce kontroluje hodnotu buňky pod ní. Pokud je prázdná, musí být tato buňka poslední Jméno hráče v seznamu a název je zapsán bez oddělovače. Pokud buňka níže obsahuje hodnotu, pak tato buňka není poslední Jméno hráče v seznamu, a tak je přidán oddělovač „,“.
TextJoin If if in Google Sheets
Tyto vzorce fungují v Tabulkách Google stejně jako v Excelu, kromě toho, že pro správné vyhodnocení výsledků je v Tabulkách Google nutné použít funkci ARRAYFORMULA. To lze automaticky přidat stisknutím kláves CTRL + SHIFT + ENTER při úpravách vzorce:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Red", B3: B8, ""))))) |
