Funkce CONCATENATE v Excelu – návod k použití s ​​příklady

Čas od času může být nutné spojit hodnoty obsažené v různých buňkách do jedné. Obvykle se k tomu používá symbol &. Jeho funkčnost je však poněkud omezená, protože nemůže zřetězit více řetězců.

Tato jednoduchá funkce je nahrazena její funkčnější verzí – STsEPIT. Ve skutečnosti v moderních verzích Microsoft Office tato funkce již není, je zcela nahrazena funkcí KROK. Zatím je stále použitelný, je přibalen kvůli zpětné kompatibilitě, ale po čase už nemusí být. Proto se doporučuje používat funkci v Excelu 2016, Online a novějších verzích KROK.

Funkce CONCATENATE – podrobný popis

funkce STsEPIT odkazuje na text. To znamená, že se používá k provádění operací s textovými hodnotami. Současně můžete zadat argumenty v různých formátech: textové, číselné nebo jako odkazy na buňky. 

Obecně platí, že pravidla pro používání této funkce jsou následující:

1. K oddělení argumentů se používá středník. Pokud se uživatel rozhodne použít jiné znaky, pak bude zobrazení výsledkem v uvozovkách.
2. Pokud se jako argument funkce použije hodnota v textovém formátu a zadá se přímo do vzorce, musí být uzavřena v uvozovkách. Pokud existuje odkaz na takovou hodnotu, pak nejsou vyžadovány uvozovky. Totéž platí pro číselné hodnoty. Pokud potřebujete do řetězce přidat číslici, pak uvozovka není povinná. Pokud tato pravidla porušíte, zobrazí se následující chyba – #NAME?
3. Pokud potřebujete přidat mezeru mezi připojené prvky, je nutné ji přidat jako samostatný textový řetězec, tedy v uvozovkách. Takhle: " " .

Nyní se podíváme blíže na syntaxi této funkce. Je velmi jednoduchý. 

Syntax

Takže ve skutečnosti existuje pouze jeden argument – ​​jedná se o textový řetězec, který by měl být vložen. Každý argument, jak již víme, je oddělen středníkem. Můžete zadat až 255 argumentů. Oni sami jsou duplikováni svým vlastním způsobem. První argument je povinný. A jak již víme, argumenty můžete zadat ve třech formátech: text, číslo a odkaz. 

Aplikace funkce CONCATENATE

Počet oblastí použití funkce STsEPIT obrovský. Vlastně se dá aplikovat téměř všude. Podívejme se na některé z nich podrobněji:

1. Účetnictví. Účetní má například za úkol zvýraznit řadu a číslo dokladu a poté tato data vložit jako jeden řádek do jedné buňky. Nebo je třeba k sérii a číslu dokladu přidat, kdo jej vystavil. Nebo uveďte několik účtenek v jedné buňce najednou. Ve skutečnosti existuje celá řada možností, můžete je vyjmenovat donekonečna. 
2. Kancelářské zprávy. Zejména pokud potřebujete poskytnout souhrnná data. Nebo spojit jméno a příjmení.
3. Gamifikace. Jedná se o velmi oblíbený trend, který se aktivně využívá ve vzdělávání, rodičovství, ale i ve věrnostních programech různých společností. Proto v oblasti vzdělávání a podnikání může být tato funkce také užitečná. 

Tato funkce je součástí standardní sady, kterou by měl znát každý uživatel Excelu. 

Inverzní funkce CONCATENATE v Excelu

Ve skutečnosti neexistuje žádná taková funkce, která by byla zcela opačná k funkci „CONCATENATE“. K provádění dělení buněk se používají další funkce, jako např LEVSIMV и PRÁVO a PSTR. První extrahuje určitý počet znaků z levé části řetězce. Druhý je vpravo. ALE PSTR může to udělat z libovolného místa a skončit na libovolném místě. 

Možná budete muset provádět i složitější úkoly, ale existují pro ně samostatné vzorce. 

Běžné problémy s funkcí CONCATENATE

Na první pohled funkce STsEPIT docela jednoduché. V praxi se ale ukazuje, že možných problémů je celá řada. Pojďme se na ně podívat blíže. 

1. Ve výsledném řetězci se zobrazí nabídky. Chcete-li se tomuto problému vyhnout, musíte jako oddělovač použít středník. Ale, jak je uvedeno výše, toto pravidlo neplatí pro čísla.
2. Slova jsou si velmi blízká. K tomuto problému dochází, protože osoba nezná všechny nuance použití funkce STsEPIT. Chcete-li slova zobrazit samostatně, musíte k nim přidat mezeru. Nebo jej můžete vložit přímo za textový argument (jak uvnitř buňky, tak pokud text zadáte samostatně do vzorce). Například takto: =CONCATENATE("Ahoj", "milá"). Vidíme, že zde byla na konec slova „Ahoj“ přidána mezera. 
3. #NÁZEV? To znamená, že pro textový argument nebyly zadány žádné uvozovky. 

Doporučení pro použití funkce

Aby byla práce s touto funkcí efektivnější, musíte zvážit několik důležitých doporučení:

1. Používejte & co nejvíce. Pokud potřebujete spojit pouze dva textové řádky, není k tomu potřeba používat samostatnou funkci. Tabulkový procesor tedy poběží rychleji, zejména na slabých počítačích s malým množstvím paměti RAM. Příkladem je následující vzorec: =A1 a B1. Je to podobné jako u vzorce =SEKCE(A1,B1). Zejména první možnost je jednodušší při ručním zadávání vzorce.
2. Pokud je nutné zkombinovat měnu nebo datum s textovým řetězcem a také informace v jiném formátu, než jsou uvedeny výše, musíte je nejprve zpracovat pomocí funkce TEXT. Je navržen tak, aby převáděl čísla, data, symboly na text.

Jak vidíte, pochopit tyto nuance není vůbec těžké. A vyplývají z informací výše. 

Běžná použití pro funkci CONCATENATE

Takže obecný vzorec je: CONCATENATE([text2];[text2];…). Vložte svůj text na vhodná místa. Je důležité si uvědomit, že požadavek na přijatý text je následující: musí být menší než délka pole, do kterého se zadává hodnota. Jako atributy můžete použít nejen předdefinované hodnoty, ale také informace v buňkách a také výsledky výpočtů pomocí jiných vzorců.

V tomto plánu není žádné povinné doporučení používat data pro zadávání v textovém formátu. Ale konečný výsledek se zobrazí ve formátu „Text“.

Existuje několik způsobů, jak zadat funkci: jeden ruční a několik poloautomatických. Pokud jste začátečník, pak se doporučuje použít metodu zadávání argumentů v dialogovém okně. Zkušenější uživatelé programu mohou vzorce zadávat i ručně. Zpočátku se to bude zdát nepohodlné, ale ve skutečnosti ještě nebylo vynalezeno nic efektivnějšího než zadávání z klávesnice. 

Mimochodem, doporučení pro používání Excelu obecně: vždy se naučte klávesové zkratky. Pomohou vám ušetřit spoustu času.

Ale zatímco jste začátečník, budete muset použít speciálně vytvořené okno.

Jak to tedy nazvat? Když se podíváte na řádek pro zadání vzorce, tak vlevo od něj je takové malé tlačítko „fx“. Pokud jej stisknete, zobrazí se následující nabídka. Musíme vybrat požadovanou funkci ze seznamu.

Poté, co vybereme požadovanou funkci, otevře se okno pro zadání argumentů. Jeho prostřednictvím lze nastavit rozsah nebo ručně zadat text, odkaz na buňku. 

Pokud zadáte data ručně, provede se zadání počínaje znakem „rovná se“. Tedy takto:

=CONCATENATE(D2;”,”;E2)

Po všech námi provedených operacích uvidíme ve výsledné buňce text „21.09“, který se skládá z několika částí: číslo 21, které se nachází v buňce indexované jako D2 a řádek 09, který je v buňce E2 . Aby byly odděleny tečkou, použili jsme ji jako druhý argument. 

Vazba jména

Pro názornost se podívejme na příklad, který popisuje, jak vázat jména. 

Řekněme, že máme takový stůl. Obsahuje informace o jménu, příjmení, městě, stavu zákazníků. Naším úkolem je spojit jméno a příjmení a získat celé jméno. 

Na základě této tabulky chápeme, že odkazy na jména by měly být uvedeny ve sloupci B a příjmení – A. Samotný vzorec bude napsán v první buňce pod nadpisem „Celé jméno“.

Před zadáním vzorce si pamatujte, že funkce nepřiřadí více informací, než zadal uživatel. Pokud tedy potřebujete přidat oddělovače, otazníky, tečky, pomlčky, mezery, musíte je zadat jako samostatné argumenty.

V našem příkladu potřebujeme oddělit jméno a příjmení mezerou. Proto musíme zadat tři argumenty: adresu buňky obsahující křestní jméno, mezeru (nezapomeňte ji uvést v uvozovkách) a adresu buňky obsahující příjmení. 

Po definování argumentů je zapíšeme do vzorce v příslušném pořadí. 

Je velmi důležité věnovat pozornost syntaxi vzorce. Vždy jej začínáme rovnítkem, poté otevřeme závorky, vypíšeme argumenty, oddělíme je středníkem a poté závorky zavřeme.

Někdy můžete mezi argumenty vložit pravidelnou čárku. Pokud je použita anglická verze Excelu, vloží se čárka. Pokud -jazyková verze, pak středník. Po stisknutí klávesy Enter se objeví sloučená verze.

Nyní už zbývá jen jednoduše pomocí značky automatického vyplňování vložit tento vzorec do všech ostatních buněk v tomto sloupci. Díky tomu máme celé jméno každého klienta. Mise splněna.

Úplně stejným způsobem můžete propojit stát a město.

Propojení čísel a textu

Jak již víme, pomocí funkce STsEPIT můžeme zřetězit číselné hodnoty s textovými hodnotami. Řekněme, že máme tabulku s údaji o zásobách zboží na prodejně. V tuto chvíli máme 25 jablek, ale tento řádek je rozložen do dvou buněk. 

Potřebujeme následující konečný výsledek.

V tomto případě potřebujeme také tři argumenty a syntaxe je stále stejná. Ale zkusme dokončit úkol mírně zvýšené složitosti. Předpokládejme, že potřebujeme napsat složitý řetězec „Máme 25 jablek“. Proto musíme ke třem existujícím argumentům přidat ještě jeden řádek „Máme“. Konečný výsledek vypadá takto.

=CONCATENATE(“Máme “;F17;” “;F16)

Na přání může uživatel přidat téměř tolik argumentů, kolik chce (v rámci výše uvedeného limitu).

Připojení funkcí VLOOKUP a CONCATENATE

Pokud používáte funkce VPR и STsEPIT dohromady z toho může vzniknout velmi zajímavá a hlavně funkční kombinace. Pomocí funkce VPR provádíme vertikální vyhledávání na stole podle určitého kritéria. Poté můžeme zjištěné informace doplnit do již existujícího řádku.

Řekněme, že máme takový stůl. Popisuje, jaké zboží se aktuálně nachází na prvním a druhém skladě. 

Potřebujeme zjistit cenu určité položky v určitém skladu. K tomu slouží funkce VPR. Před použitím si ale musíte stůl nejprve trochu připravit. VPR vypisuje data doleva, takže je potřeba vložit další sloupec nalevo od tabulky s původními daty. 

Poté data zřetězíme. 

To lze provést buď pomocí tohoto vzorce:

=B2&»/»&C2

Nebo tak.

=CONCATENATE(B2;”/”;C2)

Proto jsme spojili dva sloupce dohromady pomocí lomítka jako oddělovače mezi těmito dvěma hodnotami. Dále jsme tento vzorec přenesli na celý sloupec A. Dostaneme takovou tabulku.

Dále vezmeme následující tabulku a naplníme ji informacemi o produktu vybraném návštěvníkem. Potřebujeme získat informace o ceně zboží a čísle skladu z první tabulky. To se provádí pomocí funkce VPR.

Dále vyberte buňku K2, a napište do něj následující vzorec. 

{=ВПР(G2&»/»&H2;A2:E6;5;0)}

Nebo to lze zapsat přes funkci STsEPIT.

{=ВПР(СЦЕПИТЬ(G2;»/»;H2);A2:E6;5;ЛОЖЬ)}

Syntaxe je v tomto případě podobná tomu, jak byla provedena kombinace informací o čísle a skladu. 

Musíte zahrnout funkci VPR pomocí kombinace kláves „Ctrl“ + „Shift“ + „Enter“.

Jak vidíte, vše je jednoduché.