Pomocou programu Excel môžete rozdeliť informácie na menšie časti. Vyhľadanie potrebných údajov a manipulácia s nimi je dôležitým cieľom pre mnohých používateľov aplikácie Excel.
Ak máte celé meno osoby, možno budete musieť vyladiť iba jeho meno alebo priezvisko. Ak napríklad posielate svojim zákazníkom priateľský automatizovaný e-mail, musíte použiť ich krstné mená, aby to nevyznelo neosobne. Ak hľadáte zoznam respondentov prieskumu, môže byť dôležité použiť iba ich priezviská alebo ich priezviská skryť, aby ste zachovali anonymitu.
Vďaka Excel je tento proces jednoduchý a môžete zvoliť veľa rôznych prístupov. Tu je návod, ktorý vám pomôže vytvoriť samostatné stĺpce Meno a Priezvisko pomocou vzorcov. Venujeme sa aj problematike stredných mien.
Excel vzorce na rozdelenie mien na časti
Kde začínate
Oddelenie krstných mien
Toto je všeobecný vzorec:
= LEFT (bunka, FIND (, bunka, 1) -1)
Ak ho chcete vykonať, nahraďte ho bunka s ukazovateľom bunky, ktorý obsahuje prvé celé meno, ktoré chcete rozdeliť. V tomto príklade chcete vybrať B2 a zadajte vzorec:
= DOĽAVA (A2; FIND (; A2,1) -1)
Je však dôležité poznamenať, že na niektorých zariadeniach používa tento vzorec namiesto čiarok bodkočiarky. Ak vám teda vyššie uvedený vzorec nefunguje, možno budete musieť použiť nasledujúcu verziu:
= LEFT (bunka; FIND (; bunka; 1) -1)
V príklade by ste použili:
= DOĽAVA (A2; FIND (; A2; 1) -1)
Teraz môžete jednoducho presunúť rukoväť výplne na koniec stĺpca Meno.
Funkcia LEFT umožňuje oddeliť reťazec začínajúci od ľavého konca textu. HĽADAŤ časť tohto vzorca vyhľadá prvú medzeru v celom mene, takže dostanete časť svojho celého mena, ktorá je pred prázdnym miestom.
Rozdelené krstné mená teda zostávajú pohromade, rovnako tak aj krstné mená, ktoré obsahujú špeciálne znaky. Váš stĺpec Celé meno ale nebude obsahovať stredné mená ani stredné začiatočné písmená.
Čiarka alebo bodkočiarka?
Prečo nie je vzorec pre všetkých rovnaký?
Pre mnohých používateľov programu Excel používajú funkcie programu Excel na oddelenie vstupných údajov čiarky. Ale na niektorých zariadeniach sú regionálne nastavenia odlišné.
Ak chcete zistiť, ktorý symbol používa váš Excel, jednoducho začnite písať vzorec. Keď začnete vstupovať = DOĽAVA ( , uvidíte kurzorový text, ktorý navrhne správne formátovanie.
Oddelenie priezviska
Rovnakým spôsobom postupujte pri oddeľovaní priezvisk. Tentokrát by ste mali použiť PRAVÝ vzorec, ktorý oddeľuje reťazce začínajúce od pravej strany.
Potrebný vzorec je:
= VPRAVO (bunka, LEN (bunka) - VYHĽADÁVANIE (#, SUBSTITUTE (bunka,, #, LEN (bunka) - LEN (SUBSTITUTE (bunka,,)))))
V príklade vyššie by ste v bunke C2 použili nasledujúci vzorec:
=RIGHT(A2, LEN(A2) – SEARCH(#, SUBSTITUTE(A2, , #, LEN(A2) – LEN(SUBSTITUTE(A2, , )))))
Možno budete musieť znova prepnúť z čiarky na bodkočiarku, čo znamená, že budete musieť použiť:
=RIGHT(A2; LEN(A2) – SEARCH(#; SUBSTITUTE(A2; ; #; LEN(A2) – LEN(SUBSTITUTE(A2; ; )))))
Rozdelené priezviská a priezviská so špeciálnymi znakmi zostanú nedotknuté.
Prečo je tento vzorec zložitejší ako vzorec pre krstné mená? Je ťažšie oddeliť priezviská a stredné iniciálky od priezvisk.
Ak chcete, aby boli stredné mená a iniciály uvedené spolu s priezviskami, môžete použiť vzorec:
= VPRAVO (bunka, LEN (bunka) - VYHĽADÁVANIE (, bunka))
alebo:
= VPRAVO (A2, LEN (A2) - VYHĽADÁVANIE (, A2))
alebo:
= VPRAVO (A2; LEN (A2) - VYHĽADÁVANIE (; A2))
Čo však v prípade, že chcete oddeliť druhé mená? Toto je menej časté, ale môže to byť užitočné vedieť.
Oddeľovanie stredných mien
Vzorec pre stredné mená je nasledovný:
ako prepínať stĺpce v exceli
= MID (bunka, SEARCH (, bunka) + 1, SEARCH (, bunka, SEARCH (, bunka) +1) - SEARCH (, bunka) -1)
Vo vyššie uvedenom príklade získate:
= MID (A2, SEARCH (, A2) + 1, SEARCH (, A2, SEARCH (, A2) +1) - SEARCH (, A2) -1)
Ak váš Excel používa bodkočiarky, vzorec je:
= MID (A2; SEARCH (; A2) + 1; SEARCH (; A2; SEARCH (; A2) +1) - SEARCH (; A2) -1)
Po zadaní vzorca potiahnite rukoväť výplne nadol. Do vyššie uvedeného príkladu je pridaný stĺpec Stredné meno:
Ak celé meno neobsahuje stredné meno ani začiatočné písmeno, v tomto stĺpci získate nulové hodnoty, ktoré sa môžu zobraziť ako #HODNOTA !. Ak chcete namiesto #HODNOTA! Získať prázdne bunky, môžete použiť funkciu IFERROR.
Potom sa váš vzorec stane:
= IFERROR (MID (bunka, SEARCH (, bunka) + 1, SEARCH (, bunka, SEARCH (, bunka) +1) - SEARCH (, bunka) -1), 0)
alebo:
= IFERROR (MID (A2, SEARCH (, A2) + 1, SEARCH (, A2, SEARCH (, A2) +1) - SEARCH (, A2) -1), 0)
alebo:
= IFERROR (MID (A2; SEARCH (; A2) + 1; SEARCH (; A2; SEARCH (; A2) +1) - SEARCH (; A2) -1); 0)
Jeden prístup k oddeleniu viacerých stredných mien
Čo sa stane, ak má niekto na vašom zozname viac stredných mien? Pomocou vyššie uvedeného vzorca sa získa iba ich prvé stredné meno.
Ak chcete vyriešiť tento problém, môžete vyskúšať iný prístup k oddeľovaniu stredných mien. Ak máte vytvorené stĺpce s menom a priezviskom, môžete ich jednoducho odrezať. Všetko, čo zostane, sa bude počítať ako druhé meno.
Tento vzorec je:
=TRIM(MID(cell1,LEN(cell2)+1,LEN(cell1)-LEN(cell2&cell3)))
Bunka1 tu označuje ukazovateľ bunky v stĺpci Celé meno, bunka 2 označuje ukazovateľ bunky v stĺpci Meno, zatiaľ čo bunka3 označuje ukazovateľ bunky v stĺpci Priezvisko. Vo vyššie uvedenom príklade dostaneme:
=TRIM(MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2&D2)))
alebo:
=TRIM(MID(A2;LEN(B2)+1;LEN(A2)-LEN(B2&D2)))
Ak pôjdete s týmto vzorcom, nebudete sa musieť starať o nulové hodnoty.
Quick Recap
Tu sú vzorce, ktoré môžete použiť na rozdelenie celých mien na časti:
Krstné mená: = LEFT (bunka, FIND (, bunka, 1) -1)
Priezviská: = VPRAVO (bunka, LEN (bunka) - VYHĽADÁVANIE (#, SUBSTITUTE (bunka,, #, LEN (bunka) - LEN (SUBSTITUTE (bunka,,)))))
Stredné mená: = IFERROR (MID (bunka, SEARCH (, bunka) + 1, SEARCH (, bunka, SEARCH (, bunka) +1) - SEARCH (, bunka) -1), 0)
Alternatívny vzorec pre stredné mená: =TRIM(MID(cell1,LEN(cell2)+1,LEN(cell1)-LEN(cell2&cell3)))
Oddelenie mena a priezviska bez použitia vzorcov
Ak nemáte chuť vypisovať veľa vzorcov, ktoré by mohli byť zadané nesprávne, využite zabudovaného Sprievodcu prevodom textu na stĺpce v Exceli.
- Uistite sa, že Údaje karta sa vyberie z ponuky v hornej časti a zvýrazní sa stĺpec, ktorý chcete previesť.
- Potom kliknite na Text do stĺpcov .
- Ďalej sa uistite Vymedzené je vybratá a kliknite na Ďalšie
. - Teraz vyberte Vesmír z možností a kliknite na Ďalšie .
- Potom zmeňte Destinácia do$ B $ 2a kliknite Skončiť.
Konečný výsledok by mal vyzerať takto.
.
Konečný výsledok by mal vyzerať takto.
Záverečné slovo
Existuje mnoho ďalších spôsobov, ako vyriešiť tento problém v programe Excel. Ak žiadna z dostupných možností neurobí to, čo potrebujete, urobte ďalší prieskum.
Používanie vzorcov je pomerne jednoduché a nezávisí to od verzie programu Excel, ktorú používate. Ale bohužiaľ, stále sa môžete stretnúť s chybami.
Napríklad, ak celé meno osoby začína jej priezviskom, rozdelí sa to nesprávne. Vzorce budú mať problémy aj s priezviskami, ktoré obsahujú predpony alebo prípony, ako napríklad le Carré alebo van Gogh. Ak meno niekoho končí na Jr., bude uvedené v zozname ako jeho priezvisko.
Existujú však úpravy, ktoré môžete pridať na vyriešenie týchto problémov hneď, ako sa objavia. Práca so vzorcami vám dáva flexibilitu, ktorú potrebujete na zvládnutie týchto zložitostí.
