V tomto článku se naučíme Jak zřetězit nebo sloučit v aplikaci Excel.
Co je to zřetězení v Excelu?
Obecně existují dva způsoby, jak kombinovat data v tabulkách aplikace Excel:
Spojit buňky
Zřetězte hodnoty buněk
CONCATATATION v Excelu
Jak používat funkci CONCATENATE Excel
Funkce CONCATENATE v aplikaci Excel je navržena tak, aby spojovala různé části textu dohromady nebo kombinovala hodnoty z několika buněk do jedné buňky.
Syntaxe aplikace Excel CONCATENATE je následující:
| CONCATENATE (text1, [text2], …) |
Kde text je textový řetězec, odkaz na buňku nebo hodnota řízená vzorcem.
Příklady:
Zřetězení hodnot několika buněk
Nejjednodušší vzorec CONCATENATE pro kombinaci hodnot buněk A1 a B1 je následující:
| = CONCATENATE (A1, B1) |
Hodnoty budou spojeny dohromady bez jakéhokoli oddělovače, jako v řádku 2 na níže uvedeném snímku obrazovky.
Chcete -li hodnoty oddělit mezerou, zadejte do druhého argumentu "", jako do řádku 3 na níže uvedeném snímku obrazovky.
| = CONCATENATE (A1, "", B1) |
Chcete -li oddělit zřetězené hodnoty s jinými oddělovači, jako je čárka, mezera nebo lomítko, přečtěte si vzorce Excel CONCATENATE se speciálními znaky.
Zřetězení textového řetězce a hodnoty buňky
Neexistuje žádný důvod, aby byla funkce Excel CONCATENATE omezena pouze na spojování hodnot buněk. Můžete jej také použít ke zřetězení různých textových řetězců, aby byl výsledek smysluplnější. Například:
| = CONCATENATE (A1, "", B1, "dokončeno") |
Výše uvedený vzorec informuje uživatele o dokončení určitého projektu, jako v řádku 2 na níže uvedeném snímku obrazovky. Všimněte si prosím, že před slovo „dokončeno“ přidáme mezeru, abychom oddělili zřetězené textové řetězce.
Přirozeně můžete také přidat textový řetězec na začátek nebo doprostřed vzorce Concatenate:
| = CONCATENATE („Viz“, A1, „“, B1) |
Mezi kombinované hodnoty se přidá mezera (""), aby se výsledek zobrazil spíše jako "Projekt 1" než "Projekt1".
Zřetězení textového řetězce a hodnoty vypočítané podle vzorce. Aby byl výsledek vrácený nějakým vzorcem pro uživatele srozumitelnější, můžete jej zřetězit pomocí textového řetězce, který vysvětluje, jaká hodnota vlastně je.
Aktuální datum můžete například vrátit pomocí následujícího vzorce:
| = CONCATENATE („Dnes je“, TEXT (DNES (), „dd-mmm-rr“)) |
Používání CONCATENATE v Excelu - věci, které si musíte zapamatovat
Aby vaše vzorce CONCATENATE vždy poskytovaly správné výsledky, pamatujte na následující jednoduchá pravidla:
Funkce Excel CONCATENATE vyžaduje alespoň jeden argument "text", aby fungoval.
V jednom vzorci CONCATENATE můžete spojit až 255 řetězců, celkem 8 192 znaků.
Výsledkem funkce CONCATENATE je vždy textový řetězec, i když jsou všechny zdrojové hodnoty čísla.
Excel CONCATENATE nerozpoznává pole. Každý odkaz na buňku musí být uveden samostatně. Například byste měli napsat = CONCATENATE (A1, A2, A3) namísto = CONCATENATE (A1: A3).
Pokud je alespoň jeden z argumentů funkce CONCATENATE neplatný, vzorec vrátí #HODNOTU! chyba.
Operátor "&" pro zřetězení řetězců v aplikaci Excel
V aplikaci Microsoft Excel, & operátor je další způsob, jak spojit buňky. Tato metoda je velmi užitečná v mnoha scénářích, protože psaní znaku ampersand (&) je mnohem rychlejší než psaní slova „zřetězit“ 🙂
Podobně jako u funkce CONCATENATE můžete v aplikaci Excel použít „&“ ke kombinaci různých textových řetězců, hodnot buněk a výsledků vrácených jinými funkcemi.
Příklady vzorců "&" v Excelu
Abychom viděli operátor zřetězení v akci, přepíšeme výše popsané vzorce CONCATENATE:
Zřetězte hodnoty v A1 a B1:
= A1 a B1
Zřetězte hodnoty v A1 a B1 oddělené mezerou:
= A1 & "" & B1
Zřetězte hodnoty v A1, B1 a textovém řetězci:
= A1 a B1 & "dokončeno"
Zřetězte řetězec a výsledek funkce TEXT / DNES:
= "Dnes je" & TEXT (DNES (), "dd-mmm-rr")
Jak je ukázáno na obrázku níže, funkce CONCATENATE a operátor „&“ vrací shodné výsledky:
Operátor Excelu „&“ vs. CONCATENATE
Mnoho uživatelů se ptá, co je to efektivnější způsob, jak zřetězit řetězce v aplikaci Excel - CONCATENATE funkce nebo operátor "&".
Jediným zásadním rozdílem mezi operátory CONCATENATE a „&“ je limit 255 řetězců funkce Excel CONCATENATE a žádná taková omezení při použití ampersandu. Kromě toho neexistuje žádný rozdíl mezi těmito dvěma způsoby zřetězení, ani neexistuje žádný rychlostní rozdíl mezi vzorci CONCATENATE a "&".
A protože 255 je opravdu velké číslo a v reálných úkolech bude někdo jen málokdy potřebovat kombinovat tolik řetězců, rozdíl se scvrkává na pohodlí a snadné použití. Někteří uživatelé považují vzorce CONCATENATE za čitelnější, já osobně dávám přednost použití metody „&“. Jednoduše se tedy držte techniky zřetězení, se kterou se cítíte lépe.
Spojte buňky mezerou, čárkou a dalšími znaky
Ve vašich listech může být často nutné spojit hodnoty způsobem, který obsahuje čárky, mezery, různá interpunkční znaménka nebo jiné znaky, jako je spojovník nebo lomítko. Chcete -li to provést, jednoduše zahrňte požadovaný znak do vzorce zřetězení. Nezapomeňte tento znak uzavřít do uvozovek, jak ukazuje následující příklady.
Zřetězení dvou buněk mezerou:
= CONCATENATE (A1, "", B1) nebo = A1 & "" & B1
Zřetězení dvou buněk čárkou:
= CONCATENATE (A1, ",", B1) nebo = A1 & "," & B1
Zřetězení dvou buněk spojovníkem:
= CONCATENATE (A1, "-", B1) nebo = A1 & "-" & B1
Následující snímek obrazovky ukazuje, jak mohou výsledky vypadat:
Zřetězte textové řetězce se zalomením řádku
Nejčastěji byste zřetězené textové řetězce oddělili interpunkčními znaménky a mezerami, jak ukazuje předchozí příklad. V některých případech však může být nutné hodnoty oddělit přerušením řádku nebo návratem na začátek řádku. Běžným příkladem je sloučení poštovních adres z dat v samostatných sloupcích.
Problém je v tom, že nemůžete jednoduše zadat konec řádku ve vzorci jako obvyklý znak, a proto je potřeba speciální funkce CHAR, která dodá odpovídající kód ASCII do vzorce zřetězení:
Ve Windows použijte CHAR (10), kde 10 je ASCII kód pro Line feed.
V systému Mac použijte CHAR (13), kde 13 je kód ASCII pro návrat na začátek.
V tomto příkladu máme adresní kusy ve sloupcích A až F a dáváme je dohromady ve sloupci G pomocí zřetězení operátor "&". Sloučené hodnoty jsou odděleny čárkou (","), mezerou ("") a zalomením řádku CHAR (10):
| = A2 & "" & B2 & CHAR (10) & C2 & CHAR (10) & D2 & "," & E2 & "" & F2 |
Poznámka. Při použití zalomení řádků k oddělení zřetězených hodnot musíte mít „Obtékání textu"povolena možnost, aby se výsledek zobrazil správně. Chcete -li to provést, stisknutím Ctrl + 1 otevřete dialogové okno Formát buněk, přepněte na kartu Zarovnání a zaškrtněte políčko Zalomit text.
Stejným způsobem můžete oddělit zřetězené řetězce s jinými znaky, například:
Dvojité uvozovky (") - CHAR (34)
Dopředu lomítko (/) - CHAR (47)
Hvězdička (*) - CHAR (42)
Úplný seznam Kódy ASCII je k dispozici
Ačkoli jednodušší způsob, jak zahrnout tisknutelné znaky do vzorce zřetězení, je jednoduše je zadat do uvozovek, jako jsme to udělali v předchozím příkladu.
Ať tak či onak, všechny čtyři níže uvedené vzorce poskytují shodné výsledky:
Použijte některý z níže uvedených vzorců:
| = A1 & CHAR (47) & B1
= A1 & "/" & B1 = CONCATENATE (A1, CHAR (47), B1) = CONCATENATE (A1, "/", B1) |
Jak zřetězit sloupce v aplikaci Excel
Chcete -li v aplikaci Excel zřetězit dva nebo více sloupců, stačí do první buňky zadat obvyklý vzorec pro zřetězení a poté jej zkopírovat dolů do jiných buněk přetažením úchytu výplně (malý čtvereček, který se zobrazí v pravém dolním rohu vybraná buňka).
Chcete -li například spojit dva sloupce (sloupec A a B) oddělující hodnoty mezerou, zadejte do buňky C2 následující vzorec a poté jej zkopírujte dolů do jiných buněk. Když přetahujete úchyt pro kopírování vzorce, ukazatel myši se změní na kříž, jak ukazuje obrázek níže:
Poznámka: Rychlý způsob, jak zkopírovat vzorec dolů do jiných buněk ve sloupci, je vybrat buňku se vzorcem a poklepat na úchyt výplně.
Vezměte prosím na vědomí, že aplikace Microsoft Excel určuje, jak daleko se mají buňky kopírovat, poté, co výplň zpracovává dvojité kliknutí, na základě buněk, na které odkazuje váš vzorec. Pokud jsou ve vaší tabulce náhodou prázdné buňky, řekněme, že buňky A6 a B6 byly v tomto příkladu prázdné, vzorec by byl zkopírován pouze do řádku 5. V takovém případě budete muset ručně přetáhnout úchyt výplně dolů, aby se zřetězily celé sloupce.
Alternativním způsobem zřetězení sloupců v aplikaci Excel je použít odpovídající možnost doplňku Sloučit buňky.
Jak zřetězit řadu buněk v aplikaci Excel
Kombinace hodnot z více buněk může vyžadovat určité úsilí, protože funkce Excel CONCATENATE nepřijímá pole a v každém argumentu vyžaduje odkaz na jednu buňku.
Chcete -li spojit několik buněk, řekněme A1 až A4, potřebujete některý z následujících vzorců:
| = CONCATENATE (A1, A2, A3, A4)
nebo = A1 a A2 a A3 a A4 |
Když se připojujete k poměrně malému rozsahu, není problém zadávat všechny odkazy do řádku vzorců. Přidávání velkého rozsahu by bylo zdlouhavé a ruční zadávání odkazů na každou buňku. Níže najdete 3 způsoby zřetězení rychlého dosahu v aplikaci Excel.
Metoda 1. Stisknutím klávesy CTRL vyberte více buněk, které mají být zřetězeny.
Chcete -li rychle vybrat několik buněk, můžete stisknout klávesu CTRL a kliknout na každou buňku, kterou chcete zahrnout do vzorce CONCATENATE. Zde jsou podrobné kroky:
- Vyberte buňku, do které chcete vzorec zadat.
- Zadejte = CONCATENATE (v této buňce nebo v řádku vzorců.
- Stiskněte a podržte Ctrl a klikněte na každou buňku, kterou chcete spojit.
- Uvolněte tlačítko Ctrl, do řádku vzorců zadejte závěrečnou závorku a stiskněte Enter.
Metoda 2. K získání dosahu použijte funkci TRANSPOSE
Když potřebujete spojit obrovský rozsah sestávající z desítek nebo stovek buněk, předchozí metoda není dostatečně rychlá, protože vyžaduje kliknutí na každou buňku. V tomto případě je lepší použít funkci TRANSPOSE k vrácení pole a poté jej nahradit jedním odkazem na buňku najednou.
Do buňky, kam chcete vygenerovat zřetězený rozsah, zadejte vzorec TRANSPOSE, například:
= TRANSPOSE (A1: A10)
Na řádku vzorců stisknutím klávesy F9 nahradíte vzorec vypočítanými hodnotami. V důsledku toho budete mít řadu čísel, která chcete zřetězit.
Zadejte = CONCATENATE (před první hodnotou, poté zadejte závěrečnou závorku za poslední hodnotu a stiskněte Enter.
Poznámka: Bez ohledu na metodu, kterou použijete, je zřetězená hodnota v C1 textový řetězec (všimněte si jeho zarovnání doleva v buňce), ačkoli každá z původních hodnot je číslo. Důvodem je, že funkce CONCATENATE vždy vrací textový řetězec bez ohledu na zdrojový datový typ.
Metoda 3. Použijte doplněk Sloučit buňky
Rychlý způsob, jak zřetězit libovolný rozsah v aplikaci Excel, je použít doplněk Sloučit buňky pro Excel s vypnutou možností „Sloučit všechny oblasti ve výběru“, jak je ukázáno v článku Sloučení hodnot několika buněk do jedné buňky.
Spojte čísla a data v různých formátech
Když zřetězíte textový řetězec s číslem nebo datem, možná budete chtít výsledek formátovat odlišně v závislosti na vaší datové sadě. Chcete -li to provést, vložte funkci TEXT do vzorce zřetězení aplikace Excel.
Funkce TEXT (hodnota, formát_text) má dva argumenty:
V prvním argumentu (hodnota), zadáte číslo nebo datum, které chcete převést na text, nebo odkaz na buňku obsahující číselnou hodnotu.
V druhém argumentu (formát_text), zadáte požadovaný formát pomocí kódů, kterým funkce TEXT rozumí.
Jeden takový vzorec jsme již probrali na začátku tohoto kurzu, který spojuje text a datum.
Připomenu, že při kombinaci a textový řetězec a datum, musíte použít funkci TEXT k zobrazení data v požadovaném formátu. Například:
| = CONCATENATE („Dnes je“, TEXT (DNES (), „mm/dd/rr“))
nebo = "Dnes je" & TEXT (DNES (), "mm/dd/rr") |
Několik dalších příkladů vzorců, které spojují a textová hodnota a číslo následujte níže:
| = A2 & "" & TEXT (B2, "$#,#0,00") - zobrazí číslo se 2 desetinnými místy a znakem $.
= A2 & "" & TEXT (B2, "0.#") - nezobrazuje další nuly a znak $. = A2 & "" & TEXT (B2, "#?/???") - zobrazí číslo jako zlomek. |
Jak rozdělit buňky (opak CONCATENATE v aplikaci Excel)
Pokud hledáte opak CONCATENATE v aplikaci Excel, tj. Chcete rozdělit jednu buňku na několik buněk, máte k dispozici několik možností:
Funkce Text do sloupců
Možnost Flash Fill v Excelu 2013 a 2016
Vzorce (funkce MID, RIGHT, LEFT)
Podrobné kroky ilustrované příklady vzorců a snímky obrazovky najdete v tutoriálu Jak rozdělit buňky v Excelu.
Sloučit doplněk buněk-způsob, jak zřetězit buňky v aplikaci Excel, bez vzorce
S doplňkem Sloučit buňky, který je součástí Ultimate Suite pro Excel, můžete efektivně provádět obojí:
Sloučit několik buněk do jedné bez ztráty dat.
Spojte hodnoty několika buněk do jedné buňky a oddělte je libovolným oddělovačem, který si vyberete.
Nástroj Sloučit buňky funguje se všemi verzemi aplikace Excel od roku 2003 do roku 2016 a může kombinovat všechny datové typy včetně textových řetězců, čísel, dat a speciálních symbolů. Jeho dvěma klíčovými výhodami jsou jednoduchost a rychlost - jakékoli zřetězení se provádí několika kliknutími. A teď vám to ukážu v akci.
Zkombinujte hodnoty několika buněk do jedné buňky
Chcete -li zkombinovat obsah několika buněk, vyberte rozsah, který chcete zřetězit, a nakonfigurujte následující nastavení:
Buňky do jedné pod „Co sloučit“;
V části „Oddělit hodnoty s“ vyberte požadovaný oddělovač, v tomto příkladu je to čárka a mezera;
Vyberte si, kam chcete umístit výsledek, a hlavně
Zrušte zaškrtnutí možnosti „Sloučit všechny oblasti ve výběru“. Je to tato volba, která určuje, zda jsou buňky sloučeny nebo jsou hodnoty buněk zřetězeny.
Sloučit sloupce řádek po řádku
Chcete -li spojit dva nebo více sloupců, konfigurujete nastavení Sloučit buňky podobným způsobem, ale vyberte Sloupce v části „Co sloučit“:
Spojte řádky sloupec po sloupci
Chcete-li kombinovat data v každém jednotlivém řádku, sloupec po sloupci, rozhodnete se sloučit Řádky, vyberte požadovaný oddělovač (v tomto případě konec řádku), nakonfigurujte další nastavení tak, jak chcete, a stiskněte tlačítko Sloučit. Výsledek může vypadat podobně jako tento:
Poznámka :
V Excelu 2016, Excel 2019, Excel Online a Excel Mobile je CONCATENATE nahrazen funkcí CONCAT, která má přesně stejnou syntaxi. Ačkoli je funkce CONCATENATE zachována pro zpětnou kompatibilitu, doporučuje se místo toho použít CONCAT, protože společnost Microsoft nedává žádné sliby, že CONCATENATE bude k dispozici v budoucích verzích aplikace Excel.
Doufám, že tento článek o tom, jak zřetězit nebo sloučit v aplikaci Excel, je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
Funkce Excel REPLACE vs SUBSTITUTE: Funkce REPLACE a SUBSTITUTE jsou nejvíce nepochopenými funkcemi. K nalezení a nahrazení daného textu používáme funkci SUBSTITUTE. Kde se REPLACE používá k nahrazení řady znaků v řetězci…
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity : Získejte rychleji své úkoly v aplikaci Excel. Tyto zkratky vám pomohou zvýšit efektivitu práce v Excelu.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.