Excel - jak zachovat pouze číslice z buňky, nikoli písmena.
Sledovat video
- Zkontrolujte sloupec, abyste zjistili, zda jsou v kódu nějaké číslice
- Metoda vyúčtování 1:
- Flash Fill
- Mike Metoda:
- Použijte funkci SUBSTITUTE s konstantou pole.
- Nebudete muset používat Ctrl + Shift + Enter, protože se jedná o maticovou konstantu
- Tím se odstraní jedna číslice po druhé
- Pomocí funkce AND zjistěte, zda se každá položka ve výsledném poli rovná původní položce
- Metoda vyúčtování 3:
- Pomocí funkce VBA zkontrolujte číslice
Přepis videa
Bill: Hej. Vítej zpět. Je čas na další podcast Dueling Excel. Jsem Bill Jelen. Přidá se ke mně Mike Girvin z ExcelIsFun. Toto je naše epizoda 186: ano, pokud existují nějaké číslice v buňce. Dnešní otázku odesílá Jen, má 13 000 řádků dat, musí se podívat do buňky, pokud je některý znak číslicí, označte jej jako Ano, jinak ne. Dobře. Miku, doufám, že máš nějaký úžasný způsob, jak to udělat, protože já ne.
Budu používat bleskovou výplň a v bleskové výplni jí dám vzor s několika písmeny a čísly. Chci se ujistit, že zahrnu všechna možná čísla, aby rozuměla tomu, co dělám, a 0 takhle. Takže, jsou tu původní data, a pak si nechám flash fill opravit za mě a v pevné verzi se zbavíme všech číslic. Podívám se tedy, jestli je tam číslice. Pokud existuje, zbavte se to takhle a poté stisknutím klávesové zkratky Ctrl + E blikejte výplň, a to, co bychom měli mít, jsou nyní pouze písmena, jen písmena.
A pak otázka zní, změnilo se to? Takže = KDYŽ je to = to, co hledáme, pak to znamená, že nedošlo k žádným změnám, žádným číslicím, pak říkáme Ne, jinak Ano, takhle, a poklepejte, zkopírujte to dolů, v pořádku a pak vše, co má Ne, tam nemá žádné číslice. Zbavíme se našeho původního řádku a jakmile to zkopírujeme, CONTROL + C, ALT + E, S, V a můžeme se zbavit flash fill. Dobře, Mike. Uvidíme, co máte. (= IF (A2 = B2, „Ne“, „Ano“))
Mike: Páni ,. To musí být nejúžasnější kreativní jedinečné použití flash fill, které jsem kdy viděl. Vymysleli jste text, vytáhli skutečná čísla, CONTROL + E a ten okamžitě extrahoval všechna čísla a doručil nový textový řetězec bez čísel a pak jste udělali IF. Absolutně krásná.
V pořádku. Přijdu k tomuto listu právě tady a budu používat funkci SUBSTITUTE. Nyní, SUBSTITUTE, řeknu mu, aby se podíval na ten text právě tam, a OLD_TEXT, který chci najít a odstranit, jsou to všechny číslice. Takže vytvořím konstantu pole (1, 2, 3, 4, 5 všech číslic a). Teď je to konstanta pole a sedí v OLD_TEXTu, protože tam nedávám jedinou položku, ale místo toho vkládám spoustu položek. Toto je operace s polem argumentu funkce. Zde je 10 různých položek, které nařídí SUBSTITUTE, aby doručil 10 samostatných položek, správně, a pokud najde jednu z těchto položek, co chci? "". To řekne funkci, aby tam nic nedala,). (= NÁHRADA (A2; (1,2,3,4,5,6,7,8,9,0), „“))
Můj kurzor je na konci. Když jsem stiskl klávesu F9, určitě, protože je tu pouze 0, všechny jsou přesně stejné, kromě té poslední. U posledního našel SUBSTITUT 0 a na své místo nic nevložil. Nyní, CONTROL-Z, CONTROL-ENTER a já to zkopíruji až sem, F2 a F9. Takže pokud půjdeme k 6, je tu verze 5, odstranila tam 5, odstranila tam 6 a odstranila tam 8, takže tam budou 1, 2, 3 různé položky, které se liší. Pouze když jsou všechny položky přesně stejné jako původní položka, řekne nám, že neexistují žádné číslice. UNIKNOUT.
Vrátím se nahoru. Zní to jako F2, toto je logický test AND. Funkce AND. Chci zkontrolovat, zda každá z těchto položek v tomto výsledném poli je = k původní položce. Až budou všechny pravdivé, řekne mi, že v tomto textovém kruhu nejsou žádná čísla. ), CONTROL + ENTER, dostávám NEPRAVDA, protože jednomu z nich interně chybí hodnota 0. Zkopíruji to sem. Tenhle samozřejmě získá PRAVDU - stejně jako u těchto - protože všechny interně generované položky, pokud zde mám F2, všechny tyto, F9, jsou přesně stejné jako originál. UNIKNOUT. Nyní přicházím nahoru. Mimochodem, nemusel jsem používat CONTROL + SHIFT + ENTER, protože když použijete tuto maticovou konstantu ve svém maticovém vzorci, nemusíte používat CONTROL + SHIFT + ENTER. (= AND (SUBSTITUTE (A2; (1,2,3,4,5,6,7,8,9,0), “”) = A2))
V pořádku. Jdu na začátek. To je můj logický test. Pokud všechny z nich vyjdou jako pravdivé, hodnota, pokud je to pravda, v „NE“, jinak vložte ANO, “). CONTORL + ENTER. Poklepejte na to. Budu muset dvakrát kliknout na tento a poslat ho dolů. V pořádku. To byla trochu zábava s SUBSTITUTE, maticovou konstantou, logickým testem AND a IF, ale, řeknu vám co, stále nemohu uvěřit tomu, že flash fill, jak jste to použili, v podstatě extrahoval všechny čísla z toho. Dobře, hodím vám to zpět, pane Excel. (= IF (AND (SUBSTITUTE (A2, (1,2,3,4,5,6,7,8,9,0), „“) = A2), „Ne“, „Ano“))
Bill: No, ten vzorec s SUBSTITUTE a konstantou pole a AND, to je úžasné. Musel jsem použít bleskovou výplň, protože jsem tohle nemohl přijít. To je skvělé. Nyní mám třetí způsob. Pojďme se na to podívat.
Nyní je to způsob, jak bych to opravdu vyřešil, jen trochu VBA. Takže dělám ALT + F11, abych přepnul na VBA, INSERT, MODULE a potom zadejte tento kód. Vytvoříme novou funkci nazvanou HASNUMBERS a předáme ji hodnotě buňky a začneme slovem ALPHA. Podíváme se na každý jednotlivý znak, a pokud je tento kód, pokud je (kód ASC - 06:35) tohoto znaku mezi 48 až 57, řekneme, že je to ČÍSLA, KONEC FUNKCE, a prostě pokračujeme. Dobře, tak to vypadá, dokud nenajde číslici. Když to udělá, vrátí HASNUMBERS. Tady tedy řekneme = HASNUMBERS, ukážeme na tuto buňku a dvojitým kliknutím to zkopírujeme dolů. Kdykoli tam uvidí číslici, získá ČÍSLA, ALPHA, snadno je vyřeší. (= Má čísla (A2))
Dobře, rychlá rekapitulace epizody. Cíl: zkontrolujte sloupec, abyste zjistili, zda jsou v kódu znaků nějaké číslice, kód v buňce. Použil jsem výplň blesku k odstranění číslic a poté funkci délky, abych zjistil, zda se změnila nebo ne. Mike měl brilantní vzorec, funkci SUBSTITUTE s konstantou pole. Nepotřebujete CONTROL + SHIFT + ENTER. Musíte odebrat jednu číslici po druhé a poté pomocí funkce AND sledovat všech 10 výsledků a zjistit, zda se každá z nich rovná původní položce. Brilantní způsob, jak jít, a pak, můj záložní, použijte funkci VBA ke kontrole číslic.
No, hej. Chci poděkovat všem, že se zastavili. Uvidíme se příště na dalším netcastu od a ExcelIsFun.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Duel186.xlsm
