V tomto článku sa naučíme Ako zreťaziť alebo zlúčiť v programe Excel.
Čo je to zreťazenie v programe Excel?
Vo všeobecnosti existujú dva spôsoby, ako kombinovať údaje v tabuľkách programu Excel:
Zlúčiť bunky
Spojte hodnoty buniek
CONCATATATION v programe Excel
Ako používať funkciu CONCATENATE Excel
Funkcia CONCATENATE v programe Excel je navrhnutá tak, aby spájala rôzne časti textu alebo kombinovala hodnoty z niekoľkých buniek do jednej bunky.
Syntax programu Excel CONCATENATE je nasledovná:
| CONCATENATE (text1, [text2], …) |
Kde text je textový reťazec, odkaz na bunku alebo hodnota riadená vzorcom.
Príklady:
Spájanie hodnôt niekoľkých buniek
Najjednoduchší vzorec CONCATENATE na kombináciu hodnôt buniek A1 a B1 je nasledujúci:
| = CONCATENATE (A1, B1) |
Upozorňujeme, že hodnoty budú zlúčené bez oddeľovača, ako v riadku 2 na obrázku nižšie.
Ak chcete hodnoty oddeliť medzerou, do druhého argumentu zadajte „“, ako do riadka 3 na obrázku nižšie.
| = CONCATENATE (A1, "", B1) |
Ak chcete oddeliť zreťazené hodnoty s inými oddeľovačmi, ako sú čiarka, medzera alebo lomka, pozrite si vzorce programu Excel CONCATENATE so špeciálnymi znakmi.
Reťazenie textového reťazca a hodnoty bunky
Nie je dôvod, aby sa funkcia Excel CONCATENATE obmedzovala iba na spájanie hodnôt buniek. Môžete ho použiť aj na zreťazenie rôznych textových reťazcov, aby bol výsledok zmysluplnejší. Napríklad:
| = CONCATENATE (A1, "", B1, "dokončené") |
Vyššie uvedený vzorec informuje používateľa o tom, že určitý projekt je dokončený, ako v riadku 2 na obrázku nižšie. Upozorňujeme, že pred slovo „dokončené“ pridávame medzeru, aby sme oddelili zreťazené textové reťazce.
Prirodzene, môžete pridať textový reťazec aj na začiatok alebo do stredu vášho vzorca Concatenate:
| = CONCATENATE („Pozri“, A1, „“, B1) |
Medzi kombinované hodnoty sa pridá medzera (""), aby sa výsledok zobrazil ako "projekt 1" a nie "projekt1".
Spojenie textového reťazca a hodnoty vypočítanej podľa vzorca. Aby bol výsledok vrátený nejakým vzorcom pre používateľov zrozumiteľnejší, môžete ho zreťaziť pomocou textového reťazca, ktorý vysvetľuje, aká je hodnota v skutočnosti.
Na vrátenie aktuálneho dátumu môžete napríklad použiť nasledujúci vzorec:
| = CONCATENATE („Dnes je“, TEXT (DNES (), „dd-mmm-rr“)) |
Použitie CONCATENATE v programe Excel - veci, ktoré si treba zapamätať
Aby ste zaistili, že vaše vzorce CONCATENATE vždy poskytnú správne výsledky, pamätajte na nasledujúce jednoduché pravidlá:
Na to, aby funkcia Excel CONCATENATE fungovala, je potrebný aspoň jeden argument „text“.
V jednom vzorci CONCATENATE môžete zreťaziť až 255 reťazcov, celkom 8 192 znakov.
Výsledkom funkcie CONCATENATE je vždy textový reťazec, aj keď sú všetky zdrojové hodnoty čísla.
Excel CONCATENATE nerozpoznáva polia. Každý odkaz na bunku musí byť uvedený samostatne. Napríklad by ste mali napísať = CONCATENATE (A1, A2, A3) namiesto = CONCATENATE (A1: A3).
Ak je aspoň jeden z argumentov funkcie CONCATENATE neplatný, vzorec vráti #HODNOTU! chyba.
Operátor „&“ na zreťazenie reťazcov v programe Excel
V programe Microsoft Excel, & operátor je ďalším spôsobom, ako zreťaziť bunky. Táto metóda je veľmi užitočná v mnohých scenároch, pretože napísanie znaku ampersand (&) je oveľa rýchlejšie ako zadanie slova „zreťaziť“ 🙂
Podobne ako pri funkcii CONCATENATE, aj v programe Excel môžete použiť „&“ na kombináciu rôznych textových reťazcov, hodnôt buniek a výsledkov vrátených inými funkciami.
Príklady vzorcov programu Excel „&“
Ak chcete vidieť operátor zreťazenia v prevádzke, prepíšeme vyššie uvedené vzorce CONCATENATE:
Spojte hodnoty v A1 a B1:
= A1 a B1
Spojte hodnoty v A1 a B1 oddelené medzerou:
= A1 & "" & B1
Spojte hodnoty v A1, B1 a textovom reťazci:
= A1 a B1 & „dokončené“
Spojte reťazec a výsledok funkcie TEXT / DNES:
= "Dnes je" & TEXT (DNES (), "dd-mmm-rr")
Ako je znázornené na obrázku nižšie, funkcia CONCATENATE a operátor „&“ vracajú identické výsledky:
Operátor Excelu „&“ vs. CONCATENATE
Mnoho používateľov sa pýta, čo je efektívnejší spôsob zreťazenia reťazcov v programe Excel - funkcia CONCATENATE alebo operátor „&“.
Jediným zásadným rozdielom medzi operátormi CONCATENATE a „&“ je limit 255 reťazcov funkcie Excel CONCATENATE a žiadne také obmedzenia pri použití znaku ampersand. Okrem toho neexistuje žiadny rozdiel medzi týmito dvoma spôsobmi zreťazenia a neexistuje ani žiadny rozdiel v rýchlosti medzi vzorcami CONCATENATE a „&“.
A pretože 255 je skutočne veľké číslo a pri skutočných úlohách bude niekto len málokedy potrebovať kombinovať toľko strún, rozdiel sa scvrkáva na pohodlie a jednoduché používanie. Niektorým používateľom sa ľahšie čítajú vzorce CONCATENATE, ja osobne radšej používam metódu „&“. Jednoducho sa teda držte techniky spájania, s ktorou sa cítite pohodlnejšie.
Spojte bunky medzerou, čiarkou a inými znakmi
Vo svojich pracovných listoch môže byť často potrebné spojiť hodnoty spôsobom, ktorý obsahuje čiarky, medzery, rôzne interpunkčné znamienka alebo iné znaky, ako napríklad spojovník alebo lomka. Ak to chcete urobiť, jednoducho zahrňte požadovaný znak do svojho vzorca zreťazenia. Nezabudnite tento znak uzavrieť do úvodzoviek, ako ukazuje nasledujúci príklad.
Spojenie dvoch buniek s medzerou:
= CONCATENATE (A1, "", B1) alebo = A1 & "" & B1
Spojenie dvoch buniek čiarkou:
= CONCATENATE (A1, ",", B1) alebo = A1 & "," & B1
Spojenie dvoch buniek so spojovníkom:
= CONCATENATE (A1, "-", B1) alebo = A1 & "-" & B1
Nasledujúci obrázok obrazovky ukazuje, ako môžu výsledky vyzerať:
Spojte textové reťazce s prerušeniami riadkov
Ako je to uvedené v predchádzajúcom príklade, najčastejšie by ste oddelili zreťazené textové reťazce interpunkčnými znamienkami a medzerami. V niektorých prípadoch však môže byť potrebné hodnoty oddeliť prerušením riadka alebo návratom na koniec riadka. Bežným príkladom je zlučovanie poštových adries z údajov v samostatných stĺpcoch.
Problém je v tom, že nemôžete jednoducho napísať zalomenie riadka do vzorca ako obvyklý znak, a preto je potrebná špeciálna funkcia CHAR na dodanie zodpovedajúceho kódu ASCII do vzorca zreťazenia:
Vo Windows použite CHAR (10), kde 10 je ASCII kód pre Line feed.
V systéme Mac použite CHAR (13), kde 13 je ASCII kód pre návrat na koniec.
V tomto prípade máme adresné stĺpce v stĺpcoch A až F a dávame ich dohromady do stĺpca G pomocou operátora zreťazenia „&“. Zlúčené hodnoty sú oddelené čiarkou (","), medzerou ("") a zalomením riadka CHAR (10):
| = A2 & "" & B2 & CHAR (10) & C2 & CHAR (10) & D2 & "," & E2 & "" & F2 |
Poznámka. Keď na oddelenie zreťazených hodnôt použijete konce riadkov, musíte mať „Obtekanie textu"povolená, aby sa výsledok zobrazil správne. Ak to chcete urobiť, stlačením klávesov Ctrl + 1 otvorte dialógové okno Formát buniek, prepnite na kartu Zarovnanie a začiarknite políčko Zalamovať text.
Rovnakým spôsobom môžete oddeliť zreťazené reťazce s inými znakmi, ako napríklad:
Dvojité úvodzovky (") - CHAR (34)
Doplnková lomka (/) - CHAR (47)
Hviezdička (*) - CHAR (42)
Úplný zoznam Kódy ASCII je k dispozícii
Jednoduchší spôsob, ako zahrnúť tlačiteľné znaky do vzorca zreťazenia, je však jednoducho ich napísať do dvojitých úvodzoviek, ako sme to urobili v predchádzajúcom príklade.
Tak či onak, všetky štyri nižšie uvedené vzorce poskytujú identické výsledky:
Použite ktorýkoľvek z nižšie uvedených vzorcov:
| = A1 & CHAR (47) & B1
= A1 & "/" & B1 = CONCATENATE (A1, CHAR (47), B1) = CONCATENATE (A1, "/", B1) |
Ako zreťaziť stĺpce v programe Excel
Ak chcete v programe Excel spojiť dva alebo viac stĺpcov, zadajte do prvej bunky obvyklý vzorec zreťazenia a potom ho skopírujte nadol do iných buniek potiahnutím za úchyt výplne (malý štvorec, ktorý sa zobrazuje v pravom dolnom rohu vybraná bunka).
Ak chcete napríklad spojiť dva stĺpce (stĺpce A a B) oddeľujúce hodnoty medzerou, do bunky C2 zadajte nasledujúci vzorec a potom ho skopírujte do ostatných buniek. Keď pri kopírovaní vzorca potiahnete rukoväť výplne, ukazovateľ myši sa zmení na kríž, ako je to znázornené na obrázku nižšie:
Poznámka: Rýchly spôsob, ako skopírovať vzorec nadol do iných buniek v stĺpci, je vybrať bunku so vzorcom a dvakrát kliknúť na rukoväť výplne.
Upozorňujeme, že program Microsoft Excel určuje, ako ďaleko sa majú bunky skopírovať, potom, čo výplň zvládne dvojité kliknutie, na základe buniek uvedených vo vašom vzorci. Ak sú vo vašej tabuľke náhodou prázdne bunky, povedzme, že bunky A6 a B6 boli v tomto prípade prázdne, vzorec by sa skopíroval iba do riadka 5. V takom prípade budete musieť rukoväť výplne manuálne potiahnuť nadol, aby sa zreťazili celé stĺpce.
Alternatívny spôsob zreťazenia stĺpcov v programe Excel je použiť zodpovedajúcu možnosť doplnku Zlúčiť bunky.
Ako zreťaziť celý rad buniek v programe Excel
Kombinácia hodnôt z viacerých buniek môže vyžadovať určité úsilie, pretože funkcia Excel CONCATENATE neakceptuje polia a v každom argumente vyžaduje odkaz na jednu bunku.
Na zreťazenie niekoľkých buniek, povedzme A1 až A4, potrebujete jeden z nasledujúcich vzorcov:
| = CONCATENATE (A1, A2, A3, A4)
alebo = A1 a A2 a A3 a A4 |
Keď sa pripájate k pomerne malému rozsahu, nie je problém zadať všetky odkazy do panela vzorcov. Pridávanie veľkého rozsahu by bolo únavné, pretože by ste ručne zadávali odkazy na všetky bunky. Nasledujú 3 metódy zreťazenia rýchleho rozsahu v programe Excel.
Metóda 1. Stlačením klávesu CTRL vyberte viacero buniek, ktoré sa majú zreťaziť.
Ak chcete rýchlo vybrať niekoľko buniek, môžete stlačiť kláves CTRL a kliknúť na každú bunku, ktorú chcete zahrnúť do vzorca CONCATENATE. Tu sú podrobné kroky:
- Vyberte bunku, do ktorej chcete zadať vzorec.
- Zadajte = CONCATENATE (v tejto bunke alebo na paneli vzorcov.
- Stlačte a podržte Ctrl a kliknite na každú bunku, ktorú chcete zreťaziť.
- Uvoľnite tlačidlo Ctrl, do riadka vzorcov zadajte záverečnú zátvorku a stlačte kláves Enter.
Metóda 2. Dosiahnite rozsah pomocou funkcie TRANSPOSE
Keď potrebujete spojiť veľký rozsah pozostávajúci z desiatok alebo stoviek buniek, predchádzajúca metóda nie je dostatočne rýchla, pretože vyžaduje kliknutie na každú bunku. V tomto prípade je lepší spôsob, ako použiť funkciu TRANSPOSE na vrátenie poľa a potom ho naraz nahradiť jednotlivými odkazmi na bunky.
Do bunky, do ktorej chcete vytvoriť zreťazený rozsah, zadajte vzorec TRANSPOSE, napríklad:
= TRANSPOZÍCIA (A1: A10)
Na paneli vzorcov stlačením klávesu F9 nahradíte vzorec vypočítanými hodnotami. V dôsledku toho budete mať množstvo reťazcov, ktoré sa majú zreťaziť.
Zadajte = CONCATENATE (pred prvou hodnotou, potom zadajte koncovú zátvorku za poslednú hodnotu a stlačte Enter.
Poznámka: Bez ohľadu na to, akú metódu použijete, zreťazená hodnota v C1 je textový reťazec (všimnite si jeho zarovnanie vľavo v bunke), aj keď každá z pôvodných hodnôt je číslo. Dôvodom je, že funkcia CONCATENATE vždy vráti textový reťazec bez ohľadu na zdrojový typ údajov.
Metóda 3. Použite doplnok Zlúčiť bunky
Rýchly spôsob, ako zreťaziť v Exceli ľubovoľný rozsah, je použiť doplnok Zlúčiť bunky pre Excel s vypnutou možnosťou „Zlúčiť všetky oblasti vo výbere“, ako ukazuje postup Skombinujte hodnoty niekoľkých buniek do jednej bunky.
Spojte čísla a dátumy v rôznych formátoch
Keď zreťazíte textový reťazec s číslom alebo dátumom, možno budete chcieť výsledok formátovať odlišne v závislosti od vašej množiny údajov. Ak to chcete urobiť, vložte funkciu TEXT do vášho vzorca zreťazenia Excelu.
Funkcia TEXT (hodnota, formát_text) má dva argumenty:
V prvom argumente (hodnotu), zadáte číslo alebo dátum, ktoré sa majú previesť na text, alebo odkaz na bunku obsahujúcu číselnú hodnotu.
V druhom argumente (formát_text), zadáte požadovaný formát pomocou kódov, ktorým funkcia TEXT rozumie.
Na začiatku tohto tutoriálu sme už diskutovali o jednom takom vzorci, ktorý spája text a dátum.
Pripomeniem, že pri kombinácii a textový reťazec a dátum, Na zobrazenie dátumu v požadovanom formáte musíte použiť funkciu TEXT. Napríklad:
| = CONCATENATE („Dnes je“, TEXT (DNES (), „mm/dd/rr“))
alebo = "Dnes je" & TEXT (DNES (), "mm/dd/rr") |
Niekoľko ďalších príkladov vzorcov, ktoré spájajú a textová hodnota a číslo postupujte nižšie:
| = A2 & "" & TEXT (B2, "$#,#0,00") - zobrazí číslo s 2 desatinnými miestami a znakom $.
= A2 & "" & TEXT (B2, "0.#") - nezobrazuje ďalšie nuly a znak $. = A2 & "" & TEXT (B2, "#?/???") - zobrazí číslo ako zlomok. |
Ako rozdeliť bunky (opak CONCATENATE v programe Excel)
Ak hľadáte opak CONCATENATE v programe Excel, t.j. chcete rozdeliť jednu bunku do niekoľkých buniek, máte k dispozícii niekoľko možností:
Funkcia textu na stĺpce
Možnosť Flash Fill v Exceli 2013 a 2016
Vzorce (funkcie MID, RIGHT, LEFT)
Podrobné kroky ilustrované príkladmi vzorcov a snímkami obrazovky nájdete v návode Ako rozdeliť bunky v programe Excel.
Doplnok Zlúčiť bunky-spôsob, ako spojiť bunky v programe Excel bez vzorca
Vďaka doplnku Zlúčiť bunky, ktorý je súčasťou Ultimate Suite pre Excel, môžete efektívne vykonávať obe činnosti:
Zlúčte niekoľko buniek do jednej bez straty údajov.
Spojte hodnoty niekoľkých buniek do jednej bunky a oddeľte ich ľubovoľným oddeľovačom, ktorý si vyberiete.
Nástroj Zlúčiť bunky funguje so všetkými verziami Excelu od roku 2003 do roku 2016 a môže kombinovať všetky typy údajov vrátane textových reťazcov, čísel, dátumov a špeciálnych symbolov. Jeho dvoma kľúčovými výhodami sú jednoduchosť a rýchlosť - akékoľvek zreťazenie sa vykoná niekoľkými kliknutiami. A teraz vám to ukážem v akcii.
Skombinujte hodnoty niekoľkých buniek do jednej bunky
Ak chcete skombinovať obsah niekoľkých buniek, vyberte rozsah, ktorý chcete zreťaziť, a nakonfigurujte nasledujúce nastavenia:
Bunky do jednej v časti „Čo zlúčiť“;
V časti „Oddeľte hodnoty s“ vyberte požadovaný oddeľovač, v tomto prípade je to čiarka a medzera;
Vyberte si, kam chcete umiestniť výsledok, a hlavne
Zrušte začiarknutie políčka „Zlúčiť všetky oblasti vo výbere“. Je to táto voľba, ktorá určuje, či sa bunky zlúčia alebo sa hodnoty buniek spoja.
Skombinujte stĺpce po riadkoch
Ak chcete spojiť dva alebo viac stĺpcov, konfigurujte nastavenia Zlúčiť bunky podobne, ale zvoľte Stĺpce v časti „Čo zlúčiť“:
Spojte riadky po stĺpcoch
Ak chcete skombinovať údaje v každom jednotlivom riadku, stĺpci po stĺpci, rozhodnete sa zlúčiť Riadky, vyberte požadovaný oddeľovač (v tomto prípade zalomenie riadku), nakonfigurujte ďalšie nastavenia tak, ako chcete, a kliknite na tlačidlo Zlúčiť. Výsledok môže vyzerať podobne ako tento:
Poznámka :
V aplikáciách Excel 2016, Excel 2019, Excel Online a Excel Mobile je CONCATENATE nahradená funkciou CONCAT, ktorá má úplne rovnakú syntax. Napriek tomu, že funkcia CONCATENATE je zachovaná kvôli spätnej kompatibilite, odporúča sa namiesto toho použiť CONCAT, pretože spoločnosť Microsoft nedáva žiadne prísľuby, že CONCATENATE bude k dispozícii v budúcich verziách programu Excel.
Dúfam, že tento článok o tom, ako zreťaziť alebo zlúčiť v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Funkcia Excel REPLACE vs SUBSTITUTE: Funkcie REPLACE a SUBSTITUTE sú najviac nepochopenými funkciami. Na nájdenie a nahradenie daného textu používame funkciu SUBSTITUTE. Kde sa REPLACE používa na nahradenie niekoľkých znakov v reťazci …
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity : Zrýchlite sa so svojimi úlohami v Exceli. Tieto skratky vám pomôžu zvýšiť efektivitu práce v programe Excel.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.