Textové funkcie a vzorce v Exceli. Práca s reťazcami v Exceli

• menšie alebo rovné znamienko<=»;

• znak „nerovná sa“.<>».

Operátor zreťazenia textu

Na tento účel sa používa špeciálny znak „&“ (ampersand). S ním môžete spájať rôzne fragmenty do jedného celku – rovnaký princíp ako pri funkcii „CONNECT“. Tu je niekoľko príkladov:

1. Ak chcete skombinovať text v bunkách, musíte použiť nasledujúci kód.

1. Aby ste medzi ne vložili nejaký znak alebo písmeno, musíte použiť nasledujúcu konštrukciu.

1. Môžete kombinovať nielen bunky, ale aj bežné znaky.

Akýkoľvek text okrem odkazov musí byť uzavretý v úvodzovkách. V opačnom prípade vzorec vyvolá chybu.

Upozorňujeme, že úvodzovky sú použité presne ako na obrázku.

Na definovanie odkazov môžete použiť nasledujúce operátory:

• na vytvorenie jednoduchého prepojenia na požadovaný rozsah buniek stačí zadať prvú a poslednú bunku tejto oblasti a medzi nimi symbol „:“;
• na kombinovanie odkazov použite znak ";";
• ak je potrebné určiť bunky, ktoré sú v priesečníku niekoľkých rozsahov, potom sa medzi odkazy umiestni „medzera“. V tomto prípade sa zobrazí hodnota bunky "C7".

Pretože iba to spadá pod definíciu "priesečníka množín." Toto je názov tohto operátora (medzera).

Použitie odkazov

Pri práci v editore Excel môžete používať odkazy rôzneho druhu. Väčšina začínajúcich používateľov však vie, ako používať len tie najjednoduchšie z nich. Naučíme vás správne zadávať odkazy všetkých formátov.

Jednoduché odkazy A1

Tento typ sa spravidla používa najčastejšie, pretože je oveľa pohodlnejšie ich skladať ako ostatné.

• stĺpce - od A do XFD (nie viac ako 16384);
• riadky - od 1 do 1048576.

Tu je niekoľko príkladov:

• bunka na priesečníku riadku 5 a stĺpca B - "B5";
• rozsah buniek v stĺpci B od riadku 5 po riadok 25 - "B5:B25";
• rozsah buniek v riadku 5 začínajúci od stĺpca B po F - "B5:F5";
• všetky bunky v riadku 10 - "10:10";
• všetky bunky v riadkoch 10 až 15 - "10:15";
• všetky bunky v stĺpci B - "B:B";
• všetky bunky v stĺpcoch od B do K - "B:K";
• rozsah buniek od B2 do F5 je "B2-F5".

Vzorce niekedy používajú informácie z iných hárkov. Funguje to nasledovne.

Druhý hárok obsahuje nasledujúce údaje.

Ak je v názve listu medzera, musí byť vo vzorci uvedená v jednoduchých úvodzovkách (apostrofoch).

Absolútne a relatívne väzby

Editor Excel pracuje s tromi typmi odkazov:

• absolútna;
• príbuzný;
• zmiešané.

Zvážme ich pozornejšie.

Všetky vyššie uvedené príklady patria k relatívnym bunkovým adresám. Tento typ je najobľúbenejší. Hlavnou praktickou výhodou je, že editor počas migrácie zmení referencie na inú hodnotu. V súlade s tým, kde presne ste skopírovali tento vzorec. Pri výpočte sa bude brať do úvahy počet buniek medzi starou a novou pozíciou.

Predstavte si, že tento vzorec potrebujete natiahnuť na celý stĺpec alebo riadok. Písmená a čísla v adresách buniek nebudete manuálne meniť. Funguje to nasledovne.

1. Zadajte vzorec na výpočet súčtu prvého stĺpca.

1. Stlačte klávesové skratky Ctrl + C. Ak chcete preniesť vzorec do ďalšej bunky, musíte tam ísť a stlačiť Ctrl + V .

Ak je tabuľka veľmi veľká, je lepšie kliknúť na pravý dolný roh a bez uvoľnenia prsta natiahnuť ukazovateľ na koniec. Ak je údajov málo, kopírovanie pomocou klávesových skratiek je oveľa rýchlejšie.

1. Teraz sa pozrite na nové vzorce. Zmena indexu stĺpca prebehla automaticky.

Ak chcete, aby sa pri prenose vzorcov zachovali všetky odkazy (teda aby sa automaticky nemenili), musíte použiť absolútne adresy. Sú špecifikované ako "$B$2".

V dôsledku toho vidíme, že nenastali žiadne zmeny. Všetky stĺpce zobrazujú rovnaké číslo.

Tento typ adresy sa používa, keď je potrebné opraviť iba stĺpec alebo riadok a nie všetky naraz. Môžete použiť nasledujúce konštrukcie:

• $D1, $F5, $G3 - na upevnenie stĺpikov;
• D$1, F$5, G$3 – na opravu riadkov.

S takýmito vzorcami pracujte len v prípade potreby. Napríklad, ak potrebujete pracovať s jedným konštantným riadkom údajov, ale meniť iba stĺpce. A čo je najdôležitejšie - ak sa chystáte vypočítať výsledok v rôznych bunkách, ktoré nie sú umiestnené pozdĺž tej istej čiary.

Faktom je, že keď skopírujete vzorec na iný riadok, čísla v odkazoch sa automaticky zmenia o počet buniek z pôvodnej hodnoty. Ak použijete zmiešané adresy, všetko zostane na svojom mieste. Toto sa vykonáva nasledujúcim spôsobom.

1. Ako príklad použijeme nasledujúci výraz.

1. Presuňme tento vzorec do inej bunky. Radšej nie na ďalšom a na inom riadku. Teraz môžete vidieť, že nový výraz obsahuje rovnaký reťazec (4), ale iné písmeno, pretože to bolo jediné, ktoré bolo relatívne.

3D odkazy

Pojem "trojrozmerný" zahŕňa tie adresy, v ktorých je uvedený rozsah listov. Príklad vzorca vyzerá takto.

V tomto prípade bude výsledok zodpovedať súčtu všetkých buniek "A5" na všetkých hárkoch, počnúc od 1 do 4. Pri zostavovaní takýchto výrazov je potrebné dodržať nasledujúce podmienky:

• polia nemôžu používať takéto odkazy;
• trojrozmerné výrazy je zakázané používať tam, kde existuje priesečník buniek (napríklad operátor "medzera");
• Pri vytváraní vzorcov s 3D adresami môžete použiť nasledujúce funkcie: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNT, COUNT, MIN, MAX, MIN, MAX, VARR, PRODUCT, VARV, VAR.V a DISPA.

Ak porušíte tieto pravidlá, uvidíte nejakú chybu.

Odkazy vo formáte R1C1

Tento typ odkazov sa líši od "A1" tým, že číslo je dané nielen riadkom, ale aj stĺpcom. Vývojári sa rozhodli nahradiť bežné zobrazenie touto možnosťou pre pohodlie v makrách, ale dajú sa použiť kdekoľvek. Tu je niekoľko príkladov takýchto adries:

• R10C10 je absolútny odkaz na bunku, ktorá sa nachádza na desiatom riadku desiateho stĺpca;
• R - absolútny odkaz na aktuálny (v ktorom je uvedený vzorec) odkaz;
• R[-2] – relatívna väzba na čiaru, ktorá sa nachádza o dve pozície nad touto čiarou;
• R[-3]C – relatívny odkaz na bunku, ktorá sa nachádza o tri pozície vyššie v aktuálnom stĺpci (kam ste sa rozhodli zapísať vzorec);
• RC je relatívny odkaz na bunku, ktorá je päť buniek vpravo a päť riadkov pod aktuálnou bunkou.

Použitie mena

Program Excel na pomenovanie rozsahov buniek, jednotlivých buniek, tabuliek (bežných a súhrnných), konštánt a výrazov vám umožňuje vytvárať vlastné jedinečné názvy. Zároveň pri práci so vzorcami nie je pre editora žiadny rozdiel – všetkému rozumie.

Názvy môžete použiť na násobenie, delenie, sčítanie, odčítanie, výpočet úrokov, pomery, odchýlky, zaokrúhľovanie, DPH, hypotéky, pôžičky, odhady, časové výkazy, rôzne tlačivá, zľavy, platy, odpracované roky, splátky anuity, prácu so vzorcami VLOOKUP , "VVD", "PRIEBEŽNÉ VÝSLEDKY" a pod. To znamená, že môžete robiť, čo chcete.

Hlavnou podmienkou je iba jedna vec - toto meno musíte určiť vopred. Inak sa o tom Excel nič nedozvie. Toto sa vykonáva nasledujúcim spôsobom.

1. Vyberte stĺpec.
2. Vyvolajte kontextové menu.
3. Vyberte "Dať meno".

1. Zadajte požadovaný názov pre tento objekt. V tomto prípade musíte dodržiavať nasledujúce pravidlá.

1. Uložte kliknutím na tlačidlo "OK".

Rovnakým spôsobom môžete priradiť názov akejkoľvek bunke, textu alebo číslu.

Informácie v tabuľke môžete použiť ako pomocou mien, tak aj pomocou bežných odkazov. Takto vyzerá štandard.

A ak sa pokúsite vložiť naše meno namiesto adresy "D4: D9", uvidíte nápovedu. Stačí napísať pár znakov a uvidíte, čo sa (z mena) hodí najviac.

V našom prípade je všetko jednoduché - „stĺpec_3“. A predstavte si, že takýchto mien budete mať veľké množstvo. Nebudete si môcť všetko zapamätať.

Používanie funkcií

Existuje niekoľko spôsobov, ako vložiť funkciu do editora Excel:

• ručne;
• pomocou panela nástrojov;
• pomocou okna Vložiť funkciu.

Pozrime sa bližšie na každú metódu.

V tomto prípade je všetko jednoduché - na zadávanie vzorcov do špeciálneho riadku alebo priamo do bunky použijete svoje ruky, svoje vlastné znalosti a zručnosti.

Ak nemáte pracovné skúsenosti v tejto oblasti, potom je lepšie najprv použiť ľahšie metódy.

V tomto prípade je potrebné:

1. Prejdite na kartu "Vzorce".
2. Kliknite na ľubovoľnú knižnicu.
3. Vyberte požadovanú funkciu.

1. Hneď potom sa objaví okno „Argumenty a funkcie“ s už vybranou funkciou. Stačí zadať argumenty a uložiť vzorec pomocou tlačidla "OK".

Sprievodca náhradou

Môžete to aplikovať takto:

1. Aktivujte ľubovoľnú bunku.
2. Kliknite na ikonu "Fx" alebo použite klávesovú skratku SHIFT + F3.

1. Ihneď potom sa otvorí okno "Vložiť funkciu".
2. Tu uvidíte veľký zoznam rôznych funkcií zoradených podľa kategórií. Okrem toho môžete použiť vyhľadávanie, ak nemôžete nájsť požadovanú položku.

Stačí zabodovať slovo, ktoré dokáže opísať, čo chcete robiť, a editor sa pokúsi zobraziť všetky vhodné možnosti.

1. Vyberte ľubovoľnú funkciu z navrhovaného zoznamu.
2. Ak chcete pokračovať, musíte kliknúť na tlačidlo "OK".

1. Potom budete požiadaní o „Argumenty a funkcie“. Môžete to urobiť ručne alebo jednoducho vybrať požadovaný rozsah buniek.
2. Ak chcete použiť všetky nastavenia, musíte kliknúť na tlačidlo "OK".

1. V dôsledku toho uvidíme číslo 6, aj keď to už bolo pochopiteľné, pretože okno Argumenty a funkcie zobrazuje predbežný výsledok. Údaje sa okamžite prepočítajú, keď sa zmení ktorýkoľvek z argumentov.

Používanie vnorených funkcií

Ako príklad použijeme vzorce s logickými podmienkami. Aby sme to urobili, budeme musieť pridať nejaký druh tabuľky.

Potom postupujte podľa pokynov nižšie:

1. Kliknite na prvú bunku. Vyvolajte okno "Vložiť funkciu". Vyberte funkciu „Ak“. Kliknite na "OK" pre prilepenie.

1. Potom budete musieť vytvoriť nejaký logický výraz. Musí byť napísané v prvom poli. Môžete napríklad pridať hodnoty troch buniek v jednom riadku a skontrolovať, či je súčet väčší ako 10. V prípade „pravda“ zadajte text „Viac ako 10“. Pre falošný výsledok - "Menej ako 10". Potom kliknite na OK, aby ste sa vrátili do pracovného priestoru.

1. V dôsledku toho vidíme nasledovné - editor uviedol, že súčet buniek v treťom riadku je menší ako 10. A to je správne. Náš kód teda funguje.

1. Teraz musíte nakonfigurovať nasledujúce bunky. V tomto prípade náš vzorec jednoducho siaha ďalej. Ak to chcete urobiť, musíte najskôr presunúť kurzor do pravého dolného rohu bunky. Po zmene kurzora musíte kliknúť ľavým tlačidlom myši a skopírovať ho úplne dole.

1. Výsledkom je, že editor prepočítava náš výraz pre každý riadok.

Ako môžete vidieť, kópia bola veľmi úspešná, pretože sme použili relatívne odkazy, o ktorých sme hovorili predtým. Ak potrebujete opraviť adresy v argumentoch funkcií, použite absolútne hodnoty.

Existuje niekoľko spôsobov, ako to urobiť: použite riadok vzorcov alebo špeciálneho sprievodcu. V prvom prípade je všetko jednoduché - kliknite na špeciálne pole a manuálne zadajte potrebné zmeny. Ale písať tam nie je veľmi pohodlné.

Jediné, čo môžete urobiť, je zväčšiť vstupné pole. Ak to chcete urobiť, stačí kliknúť na označenú ikonu alebo stlačiť kombináciu klávesov Ctrl + Shift + U.

Stojí za zmienku, že toto je jediný spôsob, ak vo vzorci nepoužívate funkcie.

V prípade používania funkcií je všetko oveľa jednoduchšie. Ak chcete upraviť, postupujte podľa pokynov nižšie:

1. Aktivujte bunku so vzorcom. Kliknite na ikonu "Fx".

1. Potom sa zobrazí okno, v ktorom môžete veľmi pohodlným spôsobom zmeniť argumenty funkcie, ktorú potrebujete. Okrem toho tu môžete presne zistiť, aký bude výsledok prepočtu nového výrazu.

1. Ak chcete uložiť vykonané zmeny, použite tlačidlo "OK".

Ak chcete výraz odstrániť, postupujte takto:

1. Kliknite na ľubovoľnú bunku.

1. Kliknite na tlačidlo Odstrániť alebo Backspace. V dôsledku toho bude bunka prázdna.

Presne rovnaký výsledok môžete dosiahnuť pomocou nástroja Vymazať všetko.

Možné chyby pri zostavovaní vzorcov v editore Excel

Nasledujúce sú najčastejšie chyby, ktorých sa používatelia dopúšťajú:

• Výraz využíva obrovské množstvo hniezd. Nemalo by ich byť viac ako 64;
• cesty k externým zošitom sú špecifikované vo vzorcoch bez úplnej cesty;
• Otváracie a zatváracie zátvorky sú nesprávne umiestnené. Preto sú v editore v riadku vzorcov všetky zátvorky zvýraznené inou farbou;

• názvy kníh a listov sa neuvádzajú v úvodzovkách;
• čísla sú použité v nesprávnom formáte. Napríklad, ak potrebujete zadať 2 000 $, stačí zadať 2 000 a vybrať vhodný formát bunky, pretože symbol $ program používa na absolútne odkazy;

• požadované argumenty funkcie nie sú špecifikované. Všimnite si, že voliteľné argumenty sú uzavreté v hranatých zátvorkách. Všetko bez nich je nevyhnutné pre plnú prevádzku formuly;

• Rozsahy buniek sú nesprávne. Ak to chcete urobiť, musíte použiť operátor ":" (dvojbodka).

Chybové kódy pri práci so vzorcami

Pri práci so vzorcom sa môžu zobraziť nasledujúce možnosti chýb:

• #HODNOTA! - Táto chyba naznačuje, že používate nesprávny typ údajov. Pokúšate sa napríklad použiť text namiesto číselnej hodnoty. Samozrejme, Excel nedokáže vypočítať súčet medzi dvoma frázami;
• #NÁZOV? - podobná chyba znamená, že ste urobili preklep v pravopise názvu funkcie. Alebo sa pokúšate zadať niečo, čo neexistuje. To nemôžeš. Okrem toho môže byť problém aj v inom. Ak ste si istí názvom funkcie, skúste sa na vzorec pozrieť bližšie. Možno ste zabudli na zátvorku. Okrem toho musíte vziať do úvahy, že fragmenty textu sú uvedené v úvodzovkách. Ak všetko ostatné zlyhá, skúste výraz zložiť znova;
• #NUMBER! - zobrazenie takejto správy znamená, že máte nejaký problém s argumentmi alebo s výsledkom vzorca. Napríklad sa ukázalo, že číslo je príliš veľké alebo naopak - malé;
• #DIV/0! – Táto chyba znamená, že sa pokúšate napísať výraz, ktorý sa delí nulou. Excel nemôže vrátiť späť matematické pravidlá. Preto sú tu aj takéto akcie zakázané;
• #N/A! - Editor môže zobraziť túto správu, ak niektorá hodnota nie je k dispozícii. Ak napríklad použijete funkcie SEARCH, SEARCH, MATCH a Excel nenašiel fragment, ktorý hľadáte. Alebo neexistujú žiadne údaje a vzorec nemá s čím pracovať;
• Ak sa pokúšate niečo vypočítať a Excel napíše slovo #REF!, potom je v argumente funkcie použitý nesprávny rozsah buniek;
• #PRÁZDNY! - táto chyba sa zobrazí, ak máte nekonzistentný vzorec s prekrývajúcimi sa rozsahmi. Presnejšie, ak v skutočnosti neexistujú žiadne takéto bunky (ktoré sú na priesečníku dvoch rozsahov). Pomerne často sa táto chyba vyskytuje náhodou. V argumente stačí nechať jednu medzeru a editor to bude vnímať ako špeciálny operátor (hovorili sme o tom už skôr).

Pri úprave vzorca (bunky sú zvýraznené) uvidíte, že sa v skutočnosti nepretínajú.

Niekedy môžete vidieť veľa # znakov, ktoré úplne vypĺňajú šírku bunky. V skutočnosti tu nie je žiadna chyba. To znamená, že pracujete s číslami, ktoré sa nezmestia do tejto bunky.

Aby ste videli hodnotu tam obsiahnutú, stačí zmeniť veľkosť stĺpca.

Okrem toho môžete použiť formátovanie buniek. Ak to chcete urobiť, musíte vykonať niekoľko jednoduchých krokov:

1. Vyvolajte kontextové menu. Vyberte možnosť Formátovať bunky.

1. Zadajte typ „Všeobecné“. Na pokračovanie použite tlačidlo „OK“.

Vďaka tomu bude Excel editor schopný preložiť toto číslo do iného formátu, ktorý sa zmestí do tohto stĺpca.

Príklady použitia vzorcov

Editor Microsoft Excel vám umožňuje spracovávať informácie akýmkoľvek spôsobom, ktorý vám vyhovuje. Existujú na to všetky potrebné podmienky a príležitosti. Pozrime sa na niekoľko príkladov vzorcov podľa kategórií. To vám uľahčí zistiť.

Ak chcete vyhodnotiť matematické schopnosti programu Excel, musíte vykonať nasledujúce kroky.

1. Vytvorte tabuľku s niektorými podmienenými údajmi.

1. Ak chcete vypočítať sumu, zadajte nasledujúci vzorec. Ak chcete pridať iba jednu hodnotu, môžete použiť operátor sčítania („+“).

1. Napodiv, v editore Excel nemôžete odniesť pomocou funkcií. Na odpočet sa používa obvyklý operátor „-“. V tomto prípade bude kód nasledujúci.

1. Ak chcete určiť, koľko percent je prvé číslo od druhého, musíte použiť túto jednoduchú konštrukciu. Ak chcete odčítať niekoľko hodnôt, budete musieť pre každú bunku napísať "mínus".

Všimnite si, že symbol percent je umiestnený na konci, nie na začiatku. Navyše pri práci s percentami nie je potrebné dodatočne násobiť 100. Deje sa tak automaticky.

1. Excel môže pridávať, berúc do úvahy niekoľko podmienok naraz. Môžete vypočítať súčet buniek prvého stĺpca, ktorého hodnota je väčšia ako 2 a menšia ako 6. A rovnaký vzorec možno nastaviť aj pre druhý stĺpec.

=SUMIFS(B3:B9;B3:B9;">2"B3:B9,"<6") =SUMIFS(C3:C9,C3:C9;">2"C3:C9,"<6")

1. Môžete tiež spočítať počet prvkov, ktoré spĺňajú určitú podmienku. Napríklad, nechajte Excel vypočítať, koľko čísel máme väčších ako 3.

=COUNTIF(B3:B9;">3") =COUNTIF(C3:C9,">3")

1. Výsledok všetkých vzorcov bude nasledujúci.

Matematické funkcie a grafy

Pomocou Excelu môžete vypočítať rôzne funkcie a zostaviť z nich grafy a potom vykonať grafickú analýzu. Takéto techniky sa spravidla používajú pri prezentáciách.

Ako príklad si skúsme zostaviť grafy pre exponent a nejakú rovnicu. Pokyn bude nasledovný:

1. Vytvorme si tabuľku. V prvom stĺpci budeme mať pôvodné číslo "X", v druhom - funkciu "EXP", v treťom - zadaný pomer. Dalo by sa urobiť kvadratické vyjadrenie, ale potom by výsledná hodnota na pozadí exponentu na grafe prakticky zanikla.

Ako sme už povedali, rast exponentu je oveľa rýchlejší ako rast obvyklej kubickej rovnice.

Podobne je možné graficky znázorniť akúkoľvek funkciu alebo matematický výraz.

Všetko popísané vyššie je vhodné pre moderné programy rokov 2007, 2010, 2013 a 2016. Starý editor Excel je výrazne horší, pokiaľ ide o funkcie, množstvo funkcií a nástrojov. Ak otvoríte oficiálnu pomoc od spoločnosti Microsoft, uvidíte, že navyše označujú, v ktorej verzii programu sa táto funkcia objavila.

Vo všetkých ostatných ohľadoch vyzerá všetko takmer úplne rovnako. Ako príklad si vypočítajme súčet niekoľkých buniek. Na to potrebujete:

1. Zadajte niektoré údaje na výpočet. Kliknite na ľubovoľnú bunku. Kliknite na ikonu "Fx".

1. Vyberte kategóriu "Matematika". Nájdite funkciu "SUM" a kliknite na "OK".
1. Môžete skúsiť prepočítať v akomkoľvek inom editore. Proces bude úplne rovnaký.

Záver

V tomto návode sme hovorili o všetkom, čo súvisí so vzorcami v editore Excel, od najjednoduchších až po veľmi zložité. Každá časť bola doplnená podrobnými príkladmi a vysvetleniami. Deje sa tak preto, aby boli informácie dostupné aj pre úplných figurín.

Ak vám niečo nevychádza, tak niekde robíte chybu. Možno máte preklepy vo výrazoch alebo nesprávne odkazy na bunky. Hlavná vec, ktorú treba pochopiť, je, že všetko je potrebné zaviesť veľmi opatrne a opatrne. Všetky funkcie navyše nie sú v angličtine, ale v ruštine.

Okrem toho je dôležité pamätať na to, že vzorce musia začínať symbolom "=" (rovná sa). Mnoho začínajúcich používateľov na to zabúda.

Vzorový súbor

Aby sme vám uľahčili prácu s vyššie popísanými vzorcami, pripravili sme špeciálny demo súbor, v ktorom boli skompilované všetky vyššie uvedené príklady. Môžete to urobiť z našej stránky úplne zadarmo. Ak počas tréningu použijete pripravenú tabuľku so vzorcami na základe údajov, ktoré ste vyplnili, výsledky dosiahnete oveľa rýchlejšie.

Video návod

Ak vám náš popis nepomohol, skúste si pozrieť nižšie priložené video, ktoré podrobnejšie hovorí o hlavných bodoch. Možno robíte všetko správne, ale niečo vám chýba. S pomocou tohto videa by ste si mali poradiť so všetkými problémami. Dúfame, že vám tieto návody pomohli. Sledujte nás častejšie.

Excel ponúka veľké množstvo funkcií, pomocou ktorých môžete spracovávať text. Rozsah textových funkcií nie je obmedzený len na text, možno ich použiť aj s bunkami obsahujúcimi čísla. V rámci tejto lekcie sa pozrieme na príklady 15 najbežnejších funkcií Excelu z kategórie Text.

PRIPOJIŤ

Na zreťazenie obsahu buniek v Exceli spolu s operátorom zreťazenia môžete použiť funkciu textu PRIPOJIŤ. Postupne spája hodnoty zadaných buniek v jednom riadku.

dolná čiara

Ak Excel potrebuje urobiť všetky písmená malými písmenami, t.j. preveďte ich na malé písmená, na pomoc príde textová funkcia dolná čiara. Nenahrádza znaky, ktoré nie sú písmenami.

KAPITÁL

Textová funkcia KAPITÁL robí všetky písmená veľkými, t.j. prevedie ich na veľké písmená. Ako aj dolná čiara, nenahrádza znaky, ktoré nie sú písmenami.

PORIADNY

Textová funkcia PORIADNY zmení prvé písmeno každého slova na veľké a všetky ostatné skonvertuje na malé.

Každé prvé písmeno, ktoré nasleduje po nepísmenovom znaku, sa tiež skonvertuje na veľké.

DLSTR

VĽAVO a VPRAVO

Textové funkcie LEFT A SPRÁVNY vráti daný počet znakov, začínajúc od začiatku alebo od konca reťazca. Medzera sa počíta ako znak.

PSTR

Textová funkcia PSTR vráti zadaný počet znakov počnúc od zadanej pozície. Medzera sa počíta ako znak.

PRESNÉ

Funkcia PRESNÉ umožňuje porovnať dva textové reťazce v Exceli. Ak sa presne zhodujú, vráti sa TRUE, v opačnom prípade FALSE. Táto textová funkcia rozlišuje veľké a malé písmená, ale ignoruje rozdiely vo formátovaní.

Ak veľkosť písmen pre vás nehrá veľkú úlohu (stáva sa to vo väčšine prípadov), môžete použiť vzorec, ktorý jednoducho skontroluje rovnosť dvoch buniek.

TRIM

Odstráni z textu všetky nadbytočné medzery okrem jednotlivých medzier medzi slovami.

V prípadoch, keď je ťažké sledovať prítomnosť ďalšej medzery na konci alebo začiatku riadku, sa táto funkcia stáva jednoducho nevyhnutnou. Obrázok nižšie ukazuje, že obsah buniek A1 a B1 je úplne rovnaký, ale nie je to tak. V bunke A1 sme schválne dali na koniec slova medzeru navyše excel. V dôsledku toho funkcia PRESNÉ vrátil hodnotu FALSE.

Aplikovaním funkcie TRIM na hodnotu bunky A1 z nej odstránime všetky nadbytočné medzery a získame správny výsledok:

Funkcia TRIM užitočné použiť na údaje, ktoré sa importujú do hárkov programu Excel z externých zdrojov. Takéto údaje veľmi často obsahujú medzery navyše a rôzne netlačiteľné znaky. Ak chcete z textu odstrániť všetky netlačiteľné znaky, musíte použiť funkciu TLAČIŤ.

OPAKOVAŤ

Funkcia OPAKOVAŤ zopakuje textový reťazec zadaný počet krát. Reťazec je uvedený ako prvý argument funkcie a počet opakovaní ako druhý.

NÁJSŤ

Textová funkcia NÁJSŤ nájde výskyt jedného reťazca v inom reťazci a vráti pozíciu prvého znaku hľadanej frázy vzhľadom na začiatok textu.

Táto funkcia rozlišuje malé a veľké písmená...

... a môže začať prezerať text od určenej pozície. Na obrázku nižšie vzorec začína skenovať od štvrtého znaku, t.j. písmeno c " r“. Ale aj v tomto prípade sa pozícia postavy berie do úvahy vzhľadom na začiatok prezeraného textu.

VYHĽADÁVANIE

Textová funkcia VYHĽADÁVANIE veľmi podobný funkcii NÁJSŤ, ich hlavný rozdiel je v tom VYHĽADÁVANIE nerozlišuje veľké a malé písmená.

SUBSTITUTE

Nahradí určený text alebo znak požadovanou hodnotou. V textovej funkcii programu Excel SUBSTITUTE sa používajú, keď je vopred známe, ktorý text je potrebné nahradiť, a nie jeho umiestnenie.

Nasledujúci vzorec nahrádza všetky výskyty slova „Excel“ slovom „Word“:

Nahrádza iba prvý výskyt slova „Excel“:

Odstráni všetky medzery z textového reťazca:

NAHRADIŤ

Nahradí znaky nachádzajúce sa na známom mieste v reťazci požadovanou hodnotou. V textovej funkcii programu Excel NAHRADIŤ sa používajú vtedy, keď je známe, kde sa text nachádza, pričom samotný nie je dôležitý.

Vzorec v nižšie uvedenom príklade nahrádza 4 znaky začínajúce od siedmej pozície hodnotou „2013“. V našom príklade vzorec nahradí „2010“ za „2013“.

Nahrádza prvých päť znakov textového reťazca, t.j. slovo "Excel" na "Word".

To je všetko! Zoznámili sme sa s 15 textovými funkciami programu Microsoft Excel a pozreli sme sa na ich činnosť na jednoduchých príkladoch. Dúfam, že vám táto lekcia prišla vhod a odniesli ste si z nej aspoň trochu užitočných informácií. Veľa šťastia a úspechov pri učení Excelu!

Pomerne často je pri práci v Exceli potrebné vložiť vysvetľujúci text vedľa výsledku výpočtu vzorca, čo uľahčuje pochopenie týchto údajov. Samozrejme, môžete si vybrať samostatný stĺpec pre vysvetlenia, ale nie vo všetkých prípadoch je pridávanie ďalších prvkov racionálne. V Exceli však existujú spôsoby, ako spojiť vzorec a text do tej istej bunky. Pozrime sa, ako to možno urobiť pomocou rôznych možností.

Ak sa len pokúsite vložiť text do jednej bunky s funkciou, potom pri takomto pokuse Excel zobrazí chybové hlásenie vo vzorci a nedovolí vám takéto vloženie. Existujú však dva spôsoby, ako vložiť text vedľa výrazu vzorca. Prvým je použitie ampersandu a druhým je použitie funkcie PRIPOJIŤ.

Metóda 1: Použitie znaku ampersand

Najjednoduchší spôsob, ako vyriešiť tento problém, je použiť symbol ampersand ( & ). Tento znak vytvára logické oddelenie údajov, ktoré vzorec obsahuje, od textového výrazu. Pozrime sa, ako sa táto metóda dá aplikovať v praxi.

Máme malú tabuľku, v ktorej sú fixné a variabilné náklady podniku uvedené v dvoch stĺpcoch. Tretí stĺpec obsahuje jednoduchý sčítací vzorec, ktorý ich sumarizuje a poskytuje celkový súčet. Musíme pridať vysvetľujúce slovo za vzorec v tej istej bunke, kde sú zobrazené celkové náklady "ruble".

Prirodzene, nie je potrebné robiť všetky tieto kroky. Práve sme ukázali, že pri bežnom vstupe bez druhého ampersandu a úvodzoviek s medzerou sa údaje vzorca a textu zlúčia. Správny priestor môžete nastaviť aj pri vykonávaní druhého odseku tejto príručky.

Pri písaní textu pred vzorec sa držíme nasledujúcej syntaxe. Hneď za znakom "=" otvorte úvodzovky a napíšte text. Potom zatvorte úvodzovky. Vložili sme znak ampersand. Potom, ak potrebujete zadať medzeru, otvorte úvodzovky, vložte medzeru a zatvorte úvodzovky. Klikneme na kľúč Zadajte.

Ak chcete napísať text spolu s funkciou, a nie s bežným vzorcom, všetky kroky sú úplne rovnaké, ako je opísané vyššie.

Metóda 2: Použitie funkcie CONCATENATE

Funkciu môžete použiť aj na vloženie textu spolu s výsledkom výpočtu vzorca PRIPOJIŤ. Tento operátor je navrhnutý tak, aby v jednej bunke spojil hodnoty zobrazené v niekoľkých prvkoch hárku. Patrí do kategórie textových funkcií. Jeho syntax je nasledovná:

CONCATENATE(text1; text2; ...)

Celkovo môže mať tento operátor od 1 predtým 255 argumenty. Každý z nich predstavuje buď text (vrátane čísel a akýchkoľvek iných znakov) alebo odkazy na bunky, ktoré ho obsahujú.

Pozrime sa, ako táto funkcia funguje v praxi. Zoberme si napríklad rovnakú tabuľku, len do nej pridajte jeden stĺpec navyše "Celkové náklady" s prázdnou bunkou.

1. Vyberte prázdnu bunku stĺpca "Celkové náklady". Kliknite na ikonu "Vložiť funkciu" umiestnený naľavo od riadku vzorcov.



2. Prebieha aktivácia Funkcionári. Presun do kategórie "text". Ďalej vyberte názov "PRIPOJIŤ" a kliknite na tlačidlo OK.



3. Spustí sa okno Argumenty operátora PRIPOJIŤ. Toto okno pozostáva z polí s názvom "text". Ich počet dosahuje 255 , ale pre náš príklad potrebujeme iba tri polia. Do prvého umiestnime text, do druhého odkaz na bunku obsahujúcu vzorec a do tretieho opäť umiestnime text.

   Nastavte kurzor do poľa "Text1". Dajte do slova "Celkom". Textové výrazy môžete písať bez úvodzoviek, pretože program ich zapíše sám.

   Potom ideme na pole "Text2". Nastavte tam kurzor. Tu musíme uviesť hodnotu, ktorú vzorec zobrazuje, čo znamená, že by sme mali dať odkaz na bunku, ktorá ho obsahuje. Dá sa to urobiť jednoduchým ručným zadaním adresy, ale je lepšie umiestniť kurzor do poľa a kliknúť na bunku obsahujúcu vzorec na hárku. Adresa sa automaticky zobrazí v poli argumentov.

   V teréne "Text3" zadajte slovo "ruble".

   Potom kliknite na tlačidlo OK.



4. Výsledok sa zobrazí vo vopred vybranej bunke, ale ako vidíme, rovnako ako v predchádzajúcej metóde sú všetky hodnoty zapísané spolu bez medzier.



5. Ak chcete tento problém vyriešiť, znova vyberte bunku obsahujúcu operátor PRIPOJIŤ a prejdite na riadok vzorcov. Tam za každým argumentom, teda za každou bodkočiarkou, pridajte nasledujúci výraz:

   Medzi úvodzovkami musí byť medzera. Vo funkčnom riadku by sa vo všeobecnosti mal objaviť nasledujúci výraz:

   CONCATENATE("Celkom";" ";D2;" ";"ruble")

   Klikneme na kľúč ENTER. Teraz sú naše hodnoty oddelené medzerami.



6. Prvý stĺpec môžete skryť, ak chcete. "Celkové náklady" s pôvodným vzorcom, aby nezaberal miesto na hárku navyše. Len jeho odstránenie nebude fungovať, pretože to preruší funkciu PRIPOJIŤ, ale je celkom možné odstrániť prvok. Kliknite ľavým tlačidlom myši na sektor súradnicovej lišty stĺpca, ktorý má byť skrytý. Potom sa zvýrazní celý stĺpec. Kliknite na výber pravým tlačidlom myši. Spustí sa kontextové menu. Vyberte v ňom položku. "skryť".



7. Potom, ako vidíme, stĺpec, ktorý nepotrebujeme, je skrytý, ale údaje v bunke, v ktorej sa funkcia nachádza PRIPOJIŤ zobrazené správne.

Excel ponúka svojim používateľom až 3 funkcie na prácu s veľkými a malými písmenami v texte (veľké a malé písmená). Tieto textové funkcie zväčšujú a zmenšujú písmená alebo menia iba prvé písmeno v slove na veľké.

Vzorce s textovými funkciami Excelu

Najprv si vezmime príklad 3 textových funkcií programu Excel:

1. UPPER – Táto textová funkcia zmení všetky písmená v slove na veľké, veľké.
2. LOWER – Táto funkcia konvertuje všetky textové znaky na malé a malé písmená.
3. PROPER - funkcia zmení len prvé písmeno v každom slove na veľké, veľké.

Ako môžete vidieť na príklade na obrázku, tieto funkcie vo svojich argumentoch nevyžadujú nič iné ako pôvodné textové údaje, ktoré by mali byť prevedené v súlade s požiadavkami používateľa.

Napriek tak širokému spektru funkcií Excel stále potrebuje funkciu, ktorá dokáže veľké začiatočné písmeno len v prvom slove vo vete, a nie v každom slove. Na vyriešenie tohto problému si však môžete vytvoriť svoj vlastný vzorec pomocou rovnakých a iných textových funkcií programu Excel:

Ak chcete vyriešiť tento populárny problém, musíte použiť ďalšie textové funkcie programu Excel vo vzorci: LEFT, RIGHT a LONG.

Princíp vzorca na nahradenie prvého písmena vo vete

Ak sa bližšie pozriete na syntax vyššie uvedeného vzorca, je ľahké nahradiť, že pozostáva z dvoch častí spojených operátorom &.

Ľavá strana vzorca používa dodatočnú funkciu LEFT:

Úlohou tejto časti vzorca je zmeniť prvé písmeno na veľké v pôvodnom textovom reťazci bunky A1. Vďaka funkcii LEFT môžete získať určitý počet znakov počnúc ľavou stranou textu. Funkcia vyžaduje vyplnenie 2 argumentov:

1. Text – odkaz na bunku s pôvodným textom.
2. Počet_znakov - počet vrátených znakov z ľavej strany (od začiatku) zdrojového textu.

V tomto príklade potrebujete získať iba prvý 1 znak z pôvodného textového reťazca v bunke A1. Výsledný znak sa potom prevedie na veľké veľké písmeno.

Pravá strana vzorca za operátorom & je v princípe veľmi podobná ľavej strane, len rieši iný problém. Jeho úlohou je previesť všetky textové znaky na malé písmená. Musíte to však urobiť, aby ste nezmenili prvé veľké písmeno, za ktoré je zodpovedná ľavá strana vzorca. Namiesto funkcie LEFT na pravej strane vzorca sa používa funkcia RIGHT:

Textová funkcia RIGHT funguje inverzne s funkciou LEFT. Vyžaduje tiež oprášenie dvoch argumentov: zdrojového textu a počtu znakov. Ale vráti určitý počet písmen prijatých z pravej strany zdrojového textu. V tomto prípade však nemôžeme určiť pevnú hodnotu ako druhý argument. Dopredu totiž nepoznáme počet znakov v zdrojovom texte. Okrem toho sa dĺžka rôznych reťazcov zdrojového textu môže líšiť. Preto musíme vopred vypočítať dĺžku riadku textu a od výslednej číselnej hodnoty odpočítať -1, aby sme nezmenili prvé veľké písmeno v riadku. Koniec koncov, prvé písmeno je spracované ľavou stranou vzorca a už bolo prevedené na požiadavky používateľa. Preto by nemala byť ovplyvnená žiadnou funkciou z pravej strany vzorca.

Na automatický výpočet dĺžky zdrojového textu slúži funkcia Excel text - DLSTR (dešifruje sa ako dĺžka reťazca). Táto funkcia vyžaduje na vyplnenie len jeden argument – ​​odkaz na zdrojový text. Vo výsledku výpočtu vráti číselnú hodnotu, za funkciou =LSTR(A1) odčítame -1. Čo nám dáva možnosť neovplyvniť prvé veľké písmeno pravou stranou vzorca. Výsledkom je, že funkcia RIGHT vráti textový reťazec bez prvého znaku pre funkciu LOWER, ktorá zmení všetky textové znaky na malé malé písmená.

V dôsledku spojenia oboch častí vzorca s operátorom & dostaneme krásnu textovú vetu, ktorá sa podľa pravidiel začína prvým veľkým písmenom. A všetky ostatné písmená sú malé až do konca vety. Bez ohľadu na dĺžku textu pomocou rovnakého vzorca dostaneme správny výsledok.