Jak funguje nástroj Najít a nahradit v Excelu? Změna názvů sloupců z číselných na abecední
Tečka místo čárky u zlomkových čísel může mít významné důsledky při výpočtu v Excelu. Nejčastěji k takovým chybám dochází při importu dat do tabulky z jiných zdrojů.
Pokud mají zlomková čísla místo čárky tečku, program s nimi automaticky zachází jako s textovým datovým typem. Proto byste měli naformátovat a připravit svá importovaná data před prováděním matematiky a výpočtů.
Jak nahradit tečku čárkou v Excelu?
Vyberte a zkopírujte data z níže uvedené tabulky:
Nyní přejděte na svůj list a klikněte pravým tlačítkem na buňku A1. V zobrazené kontextové nabídce vyberte možnost „Vložit jinak“. V dialogovém okně vyberte Unicode Text a klikněte na OK.
Jak vidíte, Excel rozpoznává čísla pouze ve sloupci C. Hodnoty v tomto sloupci jsou zarovnány doprava. V dalších sloupcích vlevo. Ve všech buňkách je výchozí formát „Obecné“ a v buňkách D3, D5, E3, E5 je obecně zobrazen formát „Datum“. Data jsme zkopírovali pomocí speciálního vložení a všechny formáty původní tabulky byly odstraněny. Důvod je jediný – místo čárky je tečka. Tento datový typ není připraven a nelze jej použít pro výpočty.
Poznámka. Pokud zkopírujete data z jiných zdrojů bez speciálního vložení, formát se zkopíruje spolu s daty. V takových případech lze změnit formát buňky „Obecné“ (výchozí). Pak nelze vizuálně rozlišit, kde je rozpoznáno číslo a kde je text.
Všechny následné akce musí být provedeny od začátku. Smažte vše na listu nebo otevřete nový pro další práci.
Chcete-li nahradit tečku čárkou v importovaných datech, můžete použít 4 způsoby:
Metoda 1 nahrazení tečky čárkou v aplikaci Excel pomocí programu Poznámkový blok
Program Windows Notepad nevyžaduje použití složitých nastavení a funkcí, ale funguje pouze jako prostředník při kopírování a předběžné přípravě dat.
Program Poznámkový blok nahradil všechny tečky čárkami. Data jsou nyní připravena ke zkopírování a vložení do listu.
Jedná se o velmi jednoduchou, ale velmi účinnou metodu.
Metoda 2 dočasně změnit nastavení aplikace Excel
Před změnou tečky na čárku v aplikaci Excel řádně vyhodnoťte úkol. Možná by bylo lepší, aby program dočasně nakládal s tečkou jako s oddělovačem desetin ve zlomcích. Jen v nastavení určíme, že ve zlomkových číslech je oddělovač tečka místo čárky.
Chcete-li to provést, otevřete „Soubor“ - „Možnosti“ - „Upřesnit“. V části „Možnosti úprav“ byste měli dočasně zrušit zaškrtnutí políčka „Použít systémové oddělovače“. A v poli „Oddělovač zlomků“ odstraňte čárku a zadejte tečku.
Po provedení výpočtů důrazně doporučujeme vrátit nastavení na výchozí.
Pozor! Tato metoda bude fungovat, pokud provedete všechny změny před importem dat, a ne až poté.
Metoda 3: Dočasná změna nastavení systému Windows
Princip této metody je podobný předchozímu. Pouze zde měníme podobná nastavení ve Windows. V regionálním nastavení operačního systému je potřeba nahradit čárku tečkou. Nyní se pojďme dozvědět více o tom, jak to udělat.
Otevřete "Start" - "Ovládací panely" - "Regionální a jazykové nastavení". Klikněte na tlačítko „Upřesnit“. V okně, které se objeví, změňte první pole „Oddělovač celé číslo a zlomek“ - zadejte hodnotu, kterou potřebujeme. Pak OK a OK.
Pozor! Pokud tento soubor otevřete v jiném počítači, který má jiné místní nastavení systému, můžete zaznamenat problémy s výpočty.
Metoda 4: Použití funkce Najít a nahradit v aplikaci Excel.
Tato metoda je podobná první. Pouze zde používáme stejnou funkci z Poznámkového bloku, ale v samotném Excelu.
Při této metodě, na rozdíl od výše uvedeného, nejprve vložíme zkopírovanou desku na prázdný list papíru a poté ji připravíme pro provádění výpočtů a výpočtů.
Významnou nevýhodou této metody je složitost její implementace, pokud některá zlomková čísla s tečkou po vložení byla rozpoznána jako datum a ne jako text. Nejprve se tedy zbavíme datumů a pak se budeme zabývat textem a tečkami.
Všechna období se změnila na čárky. A text byl automaticky převeden na číslo.
Místo 4. a 5. bodu můžete použít vzorec s funkcemi v samostatném sloupci:
Vyberte například oblast buněk G2:I5, zadejte tento vzorec a stiskněte CTRL+Enter. A poté přesuňte hodnoty buněk v rozsahu G2:I5 do rozsahu D2:F5.
Tento vzorec najde bod v textu pomocí funkce FIND. Pak to druhá funkce změní na čárku. A funkce VALUE převede výsledek na číslo.
Funkce REPLACE( ) , anglická verze REPLACE(), nahradí určenou část znaků v textovém řetězci jiným textovým řetězcem. "Zadaná část znaků" znamená, že musíte zadat počáteční pozici a délku části řetězce, která má být nahrazena. Funkce se používá zřídka, ale má tu výhodu, že umožňuje snadné vkládání nového textu na určené místo v řádku.
Syntaxe funkce
NAHRADIT(zdrojový_text;start_pos;počet_znaků;nový_text)
Zdrojový_text- text, ve kterém je třeba nahradit některé znaky.
Start_pos- pozice přihlášení Zdrojový_text, od které jsou znaky nahrazeny textem nový_text.
Počet_znaků- počet znaků v Zdrojový_text, které jsou nahrazeny textem nový_text.
Nový_text- text, který nahrazuje znaky v Zdrojový_text.
Funkce REPLACE() vs SUBSTITUTE()
Funkce SUBSTITUTE() se používá, když potřebujete nahradit určitý text v textovém řetězci; funkce REPLACE() se používá, když potřebujete vyměnit žádný text začínající od určité pozice.
Při nahrazování určitého textu je použití funkce REPLACE() nepohodlné. Mnohem pohodlnější je použít funkci SUBSTITUTE().
Pusťte do cely A2 zadán řetězec Prodej (leden). Chcete-li nahradit slovo leden, na únor, napíšeme vzorce:
těch. funkce REPLACE() vyžadovala výpočet počáteční pozice slova leden(10) a jeho délka (6). To není pohodlné;
Navíc funkce REPLACE() nahradí z pochopitelných důvodů pouze jeden výskyt řetězce, funkce SUBSTITUTE() dokáže nahradit všechny výskyty nebo jen první, pouze druhý atd.
Vysvětlíme si to na příkladu. Pusťte do cely A2
zadán řetězec . Napíšeme vzorce:
=REPLACE(A2;10;6;"únor")
=SUBSTITUTE(A2; "leden","únor")
V prvním případě dostaneme řádek Tržby (únor), zisk (leden), ve druhém - Tržby (únor), zisk (únor).
Po napsání vzorce =SUBSTITUTE(A2; "leden","únor";2) dostaneme řetězec Tržby (leden), zisk (únor).
Funkce SUBSTITUTE() navíc rozlišuje velká a malá písmena. Po zapsání =SUBSTITUTE(A2; "LEDEN","ÚNOR") dostaneme řetězec beze změny Tržby (leden), zisk (leden), protože pro funkci SUBSTITUTE() není "LEDEN" totéž jako "leden".
Použití funkce pro vložení nového textu do řetězce
Funkce REPLACE() je užitečná pro vkládání nového textu do řetězce. Například je zde seznam produktových článků formuláře " ID-567(ASD)“, nutné před textem A.S.D. vložit nový text Micro aby to fungovalo" ID-567 (MicroASD)". K tomu si napíšeme jednoduchý vzorec:
=REPLACE(A2;8;0;"Mikro").
Je známo, že v normálním stavu jsou záhlaví sloupců v Excelu označena písmeny latinské abecedy. Ale v jednom okamžiku může uživatel zjistit, že sloupce jsou nyní označeny čísly. To se může stát z několika důvodů: různé druhy poruch v programu, vlastní neúmyslné akce, úmyslné přepnutí zobrazení jiným uživatelem atd. Ale bez ohledu na důvody, když taková situace nastane, otázka návratu zobrazení názvů sloupců do standardního stavu se stává aktuální. Pojďme zjistit, jak změnit čísla na písmena v aplikaci Excel.
Existují dvě možnosti, jak uvést panel souřadnic do obvyklé podoby. Jeden z nich se provádí prostřednictvím rozhraní Excel a druhý zahrnuje ruční zadání příkazu pomocí kódu. Pojďme se na oba způsoby podívat blíže.
Metoda 1: pomocí rozhraní programu
Nejjednodušší způsob, jak změnit zobrazení názvů sloupců z čísel na písmena, je použít přímé nástroje programu.
Nyní budou mít názvy sloupců v panelu souřadnic podobu, na kterou jsme zvyklí, to znamená, že budou označeny písmeny.
Metoda 2: Použití makra
Druhá možnost zahrnuje použití makra jako řešení problému.
Po těchto akcích se vrátí abecední zobrazení názvů sloupců listu, které nahradí číselnou možnost.
Jak vidíte, neočekávaná změna názvu souřadnic sloupců z abecedních na číselné by uživatele neměla zmást. Vše lze velmi snadno vrátit do předchozího stavu změnou nastavení Excelu. Volbu pomocí makra má smysl používat pouze v případě, že z nějakého důvodu nemůžete použít standardní metodu. Například kvůli nějakému selhání. Tuto možnost můžete samozřejmě využít pro experimentální účely, jen abyste viděli, jak tento typ přepínání funguje v praxi.
Dobré odpoledne, milí čtenáři tohoto blogu.
Mnoho lidí ví, co je vyhledávání v Excelu a jak jej používat. Zároveň začátečníkům tato operace trvá mnoho minut a je doprovázena hromadou zbytečných akcí. Bez slz se na to dívat nedá. Tato poznámka je určena pro všechny kategorie uživatelů. Níže je napsáno, jak rychle najít buňku (buňky) s potřebnými informacemi, i když sami jasně nerozumíte tomu, co hledáte.
Při práci s velkými tabulkami dříve nebo později přijde potřeba rychle vyhledávat data (název produktu, kód, příjmení, číslo atd.). Pro efektivní (rychlé a správné) vyhledávání by bylo správné použít speciální příkaz, pro který je na pásu karet efektně velké tlačítko.
Mimochodem, ikona dalekohledu označuje i vyhledávání v jiných programech. Pokud tedy potřebujete najít například nějakou frázi ve Wordu, klidně klikněte na dalekohled.
Příkaz hledání je navržen tak, aby automaticky detekoval buňky obsahující požadovanou kombinaci znaků. Data lze vyhledávat v určitém rozsahu, v celém listu nebo dokonce v celém sešitu. Prvním krokem je tedy určení rozsahu, kde bude Excel hledat. Rozsah se obvykle vybírá pomocí myši, ale pokud je velmi velký, pak odpovídající horké klávesy značně usnadňují život. Pokud je aktivní pouze jedna buňka, pak se vyhledávání standardně provádí na celém listu Excelu.
Dále zavolejte požadovaný příkaz Domů → Úpravy → Najít a vybrat → Najít(tlačítko z obrázku výše). Vyhledávání lze povolit i z klávesnice pomocí kombinace kláves Ctrl+F, což je mnohem pohodlnější. Otevře se před námi dialogové okno tzv "Najít a nahradit".
Jedno pole specifikuje informace (kombinaci znaků), které je třeba najít. Pokud nepoužíváte tzv žolíky (o kterých bude řeč níže), pak Excel vyhledá přesnou shodu zadaných znaků. Jsou dvě možnosti zobrazení výsledků hledání: zobrazit všechny výsledky najednou – tlačítko ; nebo zobrazit vždy jednu nalezenou hodnotu - tlačítko „Najít další“. Na obrázku výše je zvýrazněn červeným rámečkem.
Po spuštění vyhledávání Excel rychle naskenuje obsah listu (nebo zadaný rozsah), aby zjistil, zda je přítomna požadovaná kombinace znaků. Pokud je taková kombinace detekována, pak pokud je stisknuto tlačítko Excel vypíše všechny nalezené buňky.
Li ve spodní části okna Vyberte libovolnou hodnotu a poté stiskněte Ctrl+A, poté budou vybrány všechny odpovídající buňky v oblasti hledání.
Pokud je vyhledávání zahájeno pomocí tlačítka "Najít další", pak Excel zvýrazní nejbližší buňku, která odpovídá vyhledávacímu dotazu. Když znovu stisknete klávesu "Najít další"(nebo
Hledání nepřísného shody znaků
Někdy uživatel nezná přesnou kombinaci hledaných znaků, což hledání značně ztěžuje. Data mohou také obsahovat různé překlepy, mezery navíc, zkratky atd., což dále přispívá ke zmatku a téměř znemožňuje vyhledávání. Nebo může nastat opačná situace: příliš mnoho buněk odpovídá dané kombinaci a cíle hledání opět není dosaženo (kdo potřebuje 100500+ nalezených buněk?).
Velmi vhodné pro řešení těchto problémů vtipálci(zástupné znaky), které Excelu říkají o pochybných umístěních. Různé symboly mohou být skryty pod zástupnými znaky a Excel vidí pouze jejich relativní umístění ve vyhledávací frázi. Existují dva takové žolíky: hvězdička „*“ (libovolný počet neznámých symbolů) a otazník „?“ (jeden „?“ – jeden neznámý znak).
Pokud tedy potřebujete najít osobu jménem Ivanov ve velké databázi zákazníků, vyhledávání může vrátit několik desítek hodnot. To zjevně není to, co potřebujete. Do vyhledávání můžete přidat jméno, ale lze jej zadat různými způsoby: I.Ivanov, I. Ivanov, Ivan Ivanov, I.I. Ivanov atd. Pomocí žolíků můžete určit známou sekvenci symbolů bez ohledu na to, co je mezi nimi. V tomto příkladu stačí zadat do vyhledávacího pole i*ivanov a Excel najde všechny výše uvedené možnosti pro zaznamenání jména této osoby, přičemž všechny ignoruje P. Ivanov, A. Ivanov a tak dále. Tajemství spočívá v tom, že symbol „*“ říká Excelu, že pod ním mohou být skryty jakékoli symboly v libovolném množství, ale musíte hledat, co odpovídá symbolům a + ještě něco + Ivanov. Tato technika výrazně zvyšuje efektivitu vyhledávání, protože umožňuje pracovat nikoli s přesnými kritérii, ale s přibližnými.
Pokud je velmi obtížné porozumět informacím, které hledáte, můžete použít několik hvězdiček najednou. Takže v seznamu 1000 pozic pro hledaný výraz říkají * s * m * péče Rychle najdu pozici „Mol-ko d/sn mák. GARNIER Základní péče pro suché/citlivé 200 ml"(toto je zkrácený název pro “Garanier Basic Care odličovací mléko...”). Navíc je zřejmé, že z fráze "mléko" nebo "odličování" pátrání by nic nepřineslo. Často stačí zadat první písmena hledaných slov (která pravděpodobně existují) a oddělit je hvězdičkami, aby Excel ukázal zázraky hledání. Hlavní věc je, že pořadí znaků je správné.
Existuje další vtipálek – znak „?“. Pod ním se může skrývat pouze jeden neznámý symbol. Například zadáním vyhledávacího kritéria 1?6 , Excel najde všechny buňky obsahující sekvenci 106, 116, 126, 136 a atd. A pokud upřesníte 1??6 , pak buňky obsahující 1006, 1016, 1106, 1236, 1486 atd. Takže ten vtipálek "?" ukládá přísnější omezení pro vyhledávání, které zohledňuje počet chybějících znaků (rovný počtu zadaných otázek „?“).
Pokud neuspějete, můžete zkusit změnit hledaný výraz prohozením známých symbolů, jejich zkrácením, přidáním nových zástupných znaků atd. Obecně se uživatel rozhoduje, kterého žolíka si vybere, ale jejich použití rozhodně zvyšuje efektivitu práce. Vřele doporučuji. To však nejsou všechny nuance hledání. Jsou situace, kdy pozorně pozorujete buňku, kterou hledáte, ale z nějakého důvodu ji hledání nenajde. Níže vám řeknu, proč se to děje.
Pokročilé vyhledávání
Málokdo se otočí k tlačítku "Možnosti" v dialogovém okně "Najít a nahradit". Ale marně. Obsahuje mnoho užitečných funkcí, které pomáhají vyřešit některé problémy s vyhledáváním. Po kliknutí na uvedené tlačítko v okně "Najít a nahradit" Jsou přidána další pole, která dále prohlubují a rozšiřují hledané výrazy. Pojďme se na ně podívat popořadě.
S pomocí dalších parametrů může vyhledávání v Excelu jiskřit novými barvami v doslovném smyslu slova. Můžete tak hledat nejen dané číslo nebo text, ale také formát buňky (vyplněné určitou barvou, se zadanými okraji atd.).
Po stisknutí tlačítka "Formát" Vyskočí nám známé dialogové okno formátu buňky, pouze tentokrát nevytváříme, ale hledáme požadovaný formát. Formát také nelze nastavit ručně, ale vybrat ze stávajícího pomocí speciálního příkazu "Vyberte formát z buňky":
Takto lze najít například všechny sloučené buňky, což je jinak velmi problematické.
Najít formát je dobré, ale častěji musíte hledat konkrétní hodnoty. A zde Excel poskytuje další možnosti pro rozšíření a zpřesnění parametrů vyhledávání.
První rozevírací seznam "Vyhledávání" navrhuje omezit vyhledávání na jeden list nebo jej rozšířit na celou knihu.
Ve výchozím nastavení (pokud nepřejdete do parametrů) probíhá vyhledávání pouze na aktivním listu. Chcete-li zopakovat hledání na jiném listu, musíte všechny kroky provést znovu. A pokud je takových listů mnoho, pak hledání dat může zabrat hodně času. Pokud však vyberete "Rezervovat", pak bude vyhledávání probíhat napříč všemi listy aktivního sešitu najednou. Přínos je zřejmý.
Seznam "Prohlížet" s rozevíracími možnostmi podle řádků nebo sloupců, zřejmě zachovaných ze starších verzí, kdy vyhledávání vyžadovalo mnoho zdrojů a času. To teď není relevantní. Obecně to nepoužívám.
Další rozevírací seznam obsahuje skvělou možnost vyhledávání vzorce, hodnoty a také poznámky. Ve výchozím nastavení Excel vyhledává v vzorce nebo, pokud tam nejsou, v obsahu buňky. Pokud například hledáte příjmení Ivanov a toto příjmení je výsledkem vzorce (zkopírovaného ze sousedního listu), hledání nic nepřinese, protože buňka neobsahuje požadovaný seznam znaků. Ze stejného důvodu nebude možné najít číslo, které je výsledkem nějaké funkce. Proto se někdy pozorně podíváte na buňku, uvidíte hodnotu, kterou hledáte, ale Excel ji z nějakého důvodu nevidí. Nejedná se o závadu, jedná se o nastavení vyhledávání. Změňte tento parametr na "Z významy" a vyhledávání bude provedeno na základě toho, co se odráží v buňce, bez ohledu na obsah. Pokud například buňka obsahuje výsledek výpočtu 1/6 (jako hodnotu, nikoli vzorec) a formát odpovídá pouze 3 desetinným místům (tj. 0,167), hledejte znaky "167" při výběru " Vzorce" možnost pro tuto buňku nezjistí (skutečný obsah buňky je 0,166666...), ale při výběru "hodnoty" vyhledávání bude úspěšné (hledané znaky odpovídají tomu, co se odráží v buňce). A poslední položka na tomto seznamu je "poznámky". Vyhledávání se provádí pouze v poznámkách. Může to hodně pomoci, protože... poznámky jsou často skryté.
V dialogovém okně vyhledávání jsou další dvě zaškrtávací políčka "Zápasový případ" A "Celá buňka". Ve výchozím nastavení Excel ignoruje velká a malá písmena, ale můžete odlišit „Ivanov“ a „Ivanov“. Značka zaškrtnutí "Celá buňka" To může být také velmi užitečné, pokud hledáte buňku, která nemá zadaný fragment, ale zcela se skládá z hledaných znaků. Jak například najít buňky obsahující pouze 0? Běžné vyhledávání nebude fungovat, protože... Bude vydáno 10 i 100, ale pokud zaškrtnete políčko „Celá buňka“, vše půjde jako po másle.
Najít a nahradit data
Je jasné, že data se hledají z nějakého důvodu, ale za nějakým účelem. Tímto cílem je často nahradit požadovanou kombinaci (nebo formát) jinou. Chcete-li vyhledat a nahradit data, v okně "Najít a nahradit" musíte vybrat záložku "Nahrazení". Na pásu karet můžete okamžitě vybrat příslušný příkaz.
Nebo stiskněte klávesovou zkratku Ctrl+H.
Dialogové okno se zvětší o jedno pole, ve kterém jsou označeny nové znaky, které budou vloženy na místo nalezených znaků.
Pokud vám byl odeslán soubor a místo písmen ve sloupcích jsou čísla a buňky ve vzorcích jsou specifikovány podivnou kombinací čísel a písmen R a C, je snadné to opravit, protože se jedná o speciální funkce - referenční styl R1C1 v Excelu. Stává se, že se nainstaluje automaticky. To lze opravit v nastavení. Jak a proč je to užitečné? Přečtěte si níže.
Referenční styl R1C1 v Excelu. Když se ve sloupcích místo písmen objevila čísla
Pokud se místo názvů sloupců (A, B, C, D...) objevují čísla (1, 2, 3...), viz první obrázek - i to může zkušenému uživateli způsobit zmatek. Nejčastěji se to stane, když vám je soubor zaslán poštou. Formát je automaticky nastaven jako R1C1, from R ow=řetězec, C sloupec=sloupec
Pro programování ve VBA je vhodný tzv. linkový styl R1C1, tzn. pro psaní.
Jak to mohu změnit zpět na písmena? Přejděte do nabídky - Levý horní roh - Možnosti Excelu - Vzorce - sekce Práce se vzorci - Styl odkazu R1C1 - zrušte zaškrtnutí políčka.
Pro Excel 2003 Nástroje - Možnosti - Záložka Obecné - Styl vazby R1C1.
Jak můžeme použít R1C1?
Pro pochopení uveďme příklad
Druhou pohodlnou možností je zapsat si adresu v závislosti na buňce, do které ji zapisujeme. Přidejte do vzorce hranaté závorky
Jak vidíme, buňka vzorce je 2 řádky a 1 sloupec za buňkou záznamu
Souhlasíte, tato příležitost se může hodit.
k čemu to je?
Jak jsme již řekli, formát je vhodný pro programování. Například při psaní sčítání dvou buněk v kódu nemusíte vidět samotnou tabulku, bude pohodlnější napsat vzorec podle čísla řádku/sloupce nebo za tuto buňku (viz příklad výše).
Pokud je vaše tabulka tak obrovská, že počet sloupců přesahuje 100, bude pro vás pohodlnější vidět sloupec číslo 131 než písmena EA, jak se mi zdá.
Pokud si na tento typ odkazů zvyknete, pak bude hledání chyby při zapnutí režimu zobrazení mnohem jednodušší a přehlednější.
