Stiahnite si ukážkový zošit
Tento tutoriál ukáže, ako je možné zreťaziť hodnoty buniek na základe kritérií pomocou funkcie TEXTJOIN v Exceli a Tabuľkách Google.
Funkcia TEXTJOIN
Používatelia Excelu 2022+ majú prístup k funkcii TEXTJOIN, ktorá spája niekoľko reťazcov textu a oddeľuje každý reťazec so zadaným oddeľovačom.
Poznámka: Používatelia Tabuliek Google môžu používať funkciu TEXTJOIN, ale majú mierne odlišný spôsob zadávania vzorcov poľa. Pozrite si neskoršiu časť na túto tému.
Tento príklad použije funkcie TEXTJOIN a IF vo vzorci poľa na vytvorenie textového reťazca oddeleného čiarkou Mená hráčov ktoré sa týkajú a Tím hodnota Červená
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "červená", B3: B8, "")) |
Používatelia Excelu 2022 budú musieť zadať tento vzorec ako funkciu poľa stlačením klávesov CTRL + SHIFT + ENTER. Používatelia novších verzií Excelu tento krok nemusia vykonať.
Aby sme vysvetlili, čo tento vzorec robí, rozoberme si ho na kroky:
Toto je náš konečný vzorec:
| 1 | = TEXTJOIN (",", TRUE, IF (C3: C8 = "červená", B3: B8, "")) |
Najprv sa do vzorca pridajú hodnoty rozsahu buniek:
| 1 | = TEXTJOIN (",", TRUE, IF ({"červená"; "modrá"; "modrá"; "červená"; "modrá"; "červená"} = "červená", {"A"; "B"; „C“; „D“; „E“; „F“}, „“)) |
Ďalej zoznam Tím názvy sa porovnáva s hodnotou Červená:
| 1 | = TEXTJOIN (",", TRUE, IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}, {"A"; "B"; "C"; "D"; "E"; "F"} , "")) |
Funkcia IF nahradí hodnoty TRUE hodnotou Meno hráčaa hodnoty FALSE s „“
| 1 | = TEXTJOIN (",", TRUE, {"A"; "" "" ";" D ";" ";" F "}) |
Funkcia TEXTJOIN potom kombinuje všetky hodnoty poľa do jedného textového reťazca. Inštruovali sme funkciu, aby ignorovala prázdne hodnoty a medzi každú hodnotu pridala text „,“. Výsledkom je konečný výsledok:
| 1 | = "A, D, F" |
TextJoin If - v programe Excel 2022
Pretože funkcia TEXTJOIN nie je k dispozícii pred verziou programu Excel 2022, musíme tento problém vyriešiť iným spôsobom. Funkcia CONCATENATE je k dispozícii, ale neberie rozsahy buniek ako vstupy ani neumožňuje operácie v poli, a preto sme povinní namiesto toho použiť pomocný stĺpec s funkciou IF.
Tento nasledujúci príklad ukazuje, ako použiť pomocný stĺpec na vytvorenie textového reťazca oddeleného čiarkami Mená hráčov ktoré sa týkajú a Tím hodnota Červená:
| 1 | = IF (C3 = "červená", B3 & ",", "") & D4 |
Prvým krokom v tomto prípade je použiť funkciu IF na replikáciu stavu Tím = Červená. Ak je podmienka splnená, Meno hráča hodnota je zapísaná a za ňou nasleduje zvolený oddeľovač „,“:
| 1 | = IF (C3 = "červená", B3 & ",", "") |
Ďalej môžeme vytvoriť stĺpec, ktorý vytvorí zoznam týchto hodnôt do jednej bunky, a to aj odkazom na bunku pod ňou:
| 1 | = D3 a E4 |
Tento vzorec používa znak & na spojenie dvoch hodnôt. Všimnite si toho, že funkciu CONCATENATE je možné použiť na vytvorenie úplne rovnakého výsledku, ale často sa dáva prednosť metóde &, pretože je kratšia a objasňuje, akú akciu vzorec vykonáva.
Tieto dva pomocné stĺpce je potom možné zlúčiť do jedného vzorca:
| 1 | = IF (C3 = "červená", B3 & ",", "") & D4 |
Bunka súhrnu môže potom odkazovať na prvú hodnotu v súbore Zoznam hráčov pomocný stĺpček:
| 1 | = D3 |
Ak konečná hodnota „,“ nie je potrebná, do vzorca by mala byť pridaná ďalšia vnorená funkcia IF:
| 1 | = IF (C3 = "červená", IF (D4 = "", B3, B3 & ","), "") & D4 |
Ďalšou súčasťou vzorca je nižšie uvedená funkcia IF:
| 1 | IF (D4 = "", B3, B3 & ",") |
Táto časť vzorca kontroluje hodnotu bunky pod ňou. Ak je prázdna, musí byť táto bunka posledná Meno hráča v zozname a meno je zapísané bez oddeľovača. Ak bunka nižšie obsahuje hodnotu, táto bunka nie je posledná Meno hráča v zozname, a tak sa pridá oddeľovač „,“.
TextJoinin Ak je v Tabuľkách Google
Tieto vzorce fungujú v Tabuľkách Google rovnako ako v Exceli, okrem toho, že na správne vyhodnotenie výsledkov je v Tabuľkách Google potrebné použiť funkciu ARRAYFORMULA. Toto je možné automaticky pridať stlačením klávesov CTRL + SHIFT + ENTER pri úprave vzorca:
| 1 | = ARRAYFORMULA (TEXTJOIN (",", TRUE, (IF (C3: C8 = "Red", B3: B8, ""))))) |
