Peter Holmes
0 
4568
563
Microsoft Excel je skvělý pro práci s čísly i textem - ale pokud používáte oba ve stejné buňce, můžete narazit na nějaké potíže. Naštěstí můžete extrahovat čísla nebo text z buněk a pracovat s daty efektivněji. Představujeme několik možností v závislosti na formátu, ve kterém jsou vaše data aktuálně.
Čísla uložená jako text
Toto je běžná situace a naštěstí je velmi snadné se s ní vypořádat. Někdy jsou buňky obsahující pouze čísla nesprávně označeny jako text, což zabraňuje aplikaci Microsoft Excel v jejich použití v operacích. Na obrázku níže vidíte, že buňky ve sloupci A jsou uloženy jako text, jak je označeno zeleným příznakem v každé buňce, stejně jako rámeček formátu, přičemž obě jsou zvýrazněna níže.
Převést na číslo
Chcete-li problém vyřešit, stačí kliknout do buňky, kliknout na varovné pole a vybrat “Převést na číslo.” To je všechno k tomu! To lze provést na více buňkách jejich výběrem všech a kliknutím na varovné pole, ale to pravděpodobně není tak efektivní jako následující metoda.
Text do sloupců
Pokud máte mnoho buněk, které je třeba opravit, může použití této metody trvat obrovské množství času. Chcete-li tento proces urychlit, můžete použít text aplikace Microsoft Excel do sloupců Jak převést textové soubory s oddělovačem do tabulek Excel Jak převést textové soubory s oddělovačem do tabulek Excel Pomocí aplikace Microsoft Excel můžete importovat data z textového souboru do listu. Průvodce importem textu vám pomůže importovat data z oddělovaného textu a uspořádat je úhledně. funkce. Vyberte čísla, která chcete převést, přejděte na Data> Text do sloupců, a pomocí průvodce zajistěte, aby čísla vyšla správně (z větší části stačí kliknout) další a Dokončit bez jakéhokoli nastavení).
Text bude převeden na čísla a jste připraveni jít. Všimněte si, že to funguje současně pouze pro jeden sloupec, takže pokud máte mnoho sloupců, možná budete chtít jít s touto konečnou metodou.
Vložit jinak
Aby tato metoda fungovala, musíte do buňky zadat číslo 1 (je důležité, aby to bylo ve formátu čísla). Vyberte tuto buňku a zkopírujte ji. Nyní vyberte všechna čísla, která chcete převést do formátu čísel, a stiskněte Úpravy> Vložit jinak. Vybrat “Násobit” z části Operace a stiskněte OK.
Všechna vybraná čísla budou převedena do formátu Obecné, který rozpoznává čísla. Všechny textové buňky budou také převedeny do formátu Obecné, což by nemělo způsobit žádné problémy.
Extrahování čísel nebo textu z buněk smíšeného formátu
Nyní se dostáváme k těžké části: získávání čísel z buněk, které obsahují více formátů vstupu. Pokud máte číslo a jednotku (jako “7 lopat,” jak máme níže), narazíte na tento problém. Abychom to vyřešili, podíváme se na několik různých způsobů, jak rozdělit buňky na čísla a text, což vám umožní pracovat s každým jednotlivě. První metoda je trochu těžkopádná, ale funguje velmi dobře na malých datových sadách.
VLEVO / VPRAVO A VYHLEDÁVÁNÍ
Hlavní funkce, kterou zde použijeme, je VLEVO, která vrací znaky z levé strany z buňky. Jak vidíte v našem datovém souboru výše, máme buňky s jedno-, dvou- a třímístným číslem, takže musíme vrátit buňky, které jsou zcela vlevo, dva nebo tři znaky. Kombinací LEFT s funkcí SEARCH 4 Efektivně vyhledávací funkce Excel k prohledávání tabulek 4 Efektivně vyhledávací funkce Excel k prohledávání tabulek Prohledávání velké tabulky Excel není vždy snadné. Použijte vyhledávací vzorce pro úsporu času a prohledávání tabulek efektivně. , můžeme vrátit vše vlevo od prostoru. Zde je funkce:
= VLEVO (A1, SEARCH ("", A1, 1))Tím se vše vrátí vlevo od prostoru. Použití úchytky výplně Jak ušetřit čas v Excelu pomocí úchytky výplně Jak ušetřit čas v Excelu pomocí úchytky výplně Zvládnutí úchytky výplně je prvním krokem k tomu, abyste se stali průvodcem Excelu. použít vzorec na zbytek buněk, to je to, co dostaneme (vzorec můžete vidět na panelu funkcí v horní části obrázku):
Jak vidíte, nyní máme všechna čísla izolovaná, takže s nimi lze manipulovat. Chcete také izolovat text? Funkci RIGHT můžeme použít stejným způsobem:
= PRÁVO (A1, LEN (A1) -SEARCH ("", A1, 1))Vrací x znaky z pravé strany buňky, kde x je celková délka buňky mínus počet znaků vlevo od mezery.
Nyní lze text také manipulovat. Chcete je znovu kombinovat? Stačí použít funkci CONCATENATE se všemi buňkami jako vstupy:
= KONCATENÁT (E1, F1)
Tato metoda samozřejmě funguje nejlépe, pokud máte pouze čísla a jednotky a nic jiného. Pokud máte jiné buněčné formáty, možná budete muset být kreativní se vzorci, aby všechno fungovalo správně. Pokud máte obří dataset, bude to stojí za čas potřebný k tomu, aby se vzorec vymyslel!
Text do sloupců
Funkce Text to Columns je užitečná pro sloupce pouze pro čísla, ale může také usnadnit život, pokud máte buňky smíšeného formátu. Vyberte sloupec, se kterým chcete pracovat, a stiskněte Data> Text do sloupců. Poté můžete pomocí průvodce vybrat oddělovač (mezera je obvykle nejlepší) a nechat sloupec rozdělit tak, jak chcete..
Pokud máte pouze jednociferné a dvoumístné číslo, může být užitečná i volba Pevná šířka, protože rozdělí pouze první dva nebo tři znaky buňky (pokud chcete, můžete vytvořit několik dělení, ale uložím úplné vysvětlení rozdělení pevné šířky pro další článek).
Pokud soubor dat obsahuje mnoho sloupců a raději nepoužíváte text do sloupců pro každý z nich, můžete snadno získat stejný efekt pomocí rychlého exportu a importu. Nejprve exportujte tabulku jako soubor s hodnotami oddělenými čárkami (CSV). Klikněte na Soubor> Uložit jako ... a uložte soubor jako soubor CSV.
Nyní otevřete novou tabulku a klikněte na Soubor> Importovat… Vyberte svůj soubor CSV a pomocí Průvodce importem rozdělte svá data do dvou sloupců (použijete stejné kroky jako v Průvodci převodem textu na sloupce). Výběrem “Prostor” v části Oddělovače řeknete aplikaci Microsoft Excel, aby data rozdělila všude, kde najde mezeru, která izoluje čísla a text.
Až budete hotovi, klikněte na Dokončit a dostanete novou tabulku, ve které jsou sloupce rozděleny do dvou. Samozřejmě, pokud máte v buňce více než jeden prostor, skončíte s více než dvěma sloupci, jak můžete vidět zde:
Bohužel pro tuto metodu neexistuje dobré řešení; stačí spojit buňky zpět dohromady.
Složitější situace
S výše uvedenými strategiemi byste měli být schopni extrahovat čísla nebo text z většiny buněk smíšeného formátu, které vám způsobují potíže. I když tomu tak není, můžete je pravděpodobně kombinovat s některými výkonnými textovými funkcemi obsaženými v aplikaci Microsoft Excel a získat tak znaky, které hledáte. Existují však mnohem komplikovanější situace, které vyžadují složitější řešení.
Například jsem našel příspěvek na fóru, kde někdo chtěl získat čísla z řetězce jako “45t * & 65 /”, aby skončil s “4565.” Jedním ze způsobů, jak to udělat, byl následující plakát:
= SUMPRODUCT (MID (0 & A1, LARGE) (INDEX (ISNUMBER (- MID (A1, ROW ($ 1: 25 $)), 1)) * ROW ($ 1: 25 $), 0), ROW ($ 1: 25 $)) + 1, 1) * 10 ^ ROW ($ 1: 25 $) / 10)
Abych byl upřímný, ani netuším, jak to funguje. Ale podle příspěvku na fóru to vyřadí čísla ze složitého řetězce čísel a dalších znaků. Jde o to, že s dostatečným časem, trpělivostí a úsilím můžete extrahovat čísla a text z téměř všeho! Musíte jen najít ty správné zdroje Potřebujete pomoc s Excel Formulas? 7 zdrojů, které je třeba konzultovat, potřebujete pomoc s formulemi aplikace Excel? 7 Zdroje pro konzultace Excel je zlatý standard tabulky. Pokud jste povinni používat Excel a přesto se s ním musíte seznámit, tyto zdroje vás rychle seznámí se základy a dalšími informacemi.. .
Jakou strategii používáte?
Nyní, když máte lepší představu o tom, jak získat čísla a písmena z buněk smíšeného formátu, chceme vědět, jaké další strategie jste použili k tomu samému. Zašlete své bláznivé vzorce, procesy nebo cokoli jiného, co by mohlo být užitečné v komentářích, abychom se od nich mohli všichni poučit! A pro více zábavy v Excelu si přečtěte, jak oddělit křestní a příjmení v Excelu Jak oddělit křestní jméno a příjmení v Excelu Jak oddělit křestní jméno a příjmení v Excelu Práce se jmény v Microsoft Excelu? Zde je užitečný návod, který vám ukáže, jak oddělit křestní a příjmení v Excelu. .