Excel - ako uchovať iba číslice z bunky, nie písmená.
Pozeraj video
- Skontrolujte stĺpec a zistite, či sú v kóde nejaké číslice
- Metóda vyúčtovania 1:
- Flash Fill
- Mike Method:
- Použite funkciu SUBSTITUTE s konštantou poľa.
- Nebudete musieť používať Ctrl + Shift + Enter, pretože ide o konštantu poľa
- Týmto sa odstráni jedna číslica po druhej
- Pomocou funkcie AND zistite, či sa každá položka vo výslednom poli rovná pôvodnej položke
- Bill metóda 3:
- Pomocou funkcie VBA skontrolujte číslice
Prepis videa
Bill: Hej. Vitaj späť. Je čas na ďalší podcast Dueling Excel. Som Bill Jelen z. Doplní ma Mike Girvin z ExcelIsFun. Toto je naša epizóda 186: áno, ak existujú nejaké číslice v bunke. Dnešnú otázku posiela Jen, obsahuje 13 000 riadkov údajov, musí sa pozrieť cez bunku, ak je niektorým znakom číslica, označte ju ako Áno, inak nie. V poriadku. Mike, dúfam, že máš nejaký úžasný spôsob, ako to urobiť, pretože nie.
Budem používať bleskovú výplň a ako bleskovú výplň jej tu dám vzor s niekoľkými písmenami a číslami. Chcem sa uistiť, že som zahrnul všetky možné čísla, aby som rozumel tomu, čo robím, a k tomu 0. Takže existujú pôvodné údaje a potom si ich nechám opraviť pomocou bleskovej výplne a v pevnej verzii sa zbavíme všetkých číslic. Pozriem sa teda, či je tam nejaká číslica. Ak existuje, tak sa toho zbavte a potom stlačením klávesovej skratky CONTROL + E blikajte výplň. To, čo by sme mali mať, je teraz iba písmeno, iba písmená.
A potom je otázka, či sa to zmenilo? Takže = AK toto je = to, čo hľadáme, znamená to, že nedošlo k žiadnym zmenám, žiadnym čísliciam, potom hovoríme Nie, inak Áno, tak a dvakrát kliknite, skopírujte to, v poriadku, a potom tam čokoľvek s číslom Nie nemá číslice. Zbavíme sa nášho pôvodného riadku a akonáhle to skopírujeme, CONTROL + C, ALT + E, S, V a môžeme sa zbaviť flash výplne. Dobre, Mike. Pozrime sa, čo máte. (= IF (A2 = B2, „Nie“, „Áno“))
Mike: Páni ,. To musí byť najúžasnejšie kreatívne jedinečné využitie bleskovej výplne, aké som kedy videl. Vymysleli ste text, vybrali ste skutočné čísla, CONTROL + E, ktorý okamžite vyňal všetky čísla a doručil nový textový reťazec bez čísel, a potom ste urobili IF. Úplne nádherná.
Dobre. Prídem k tomuto hárku práve tu a budem používať funkciu SUBSTITUTE. Teraz, SUBSTITUTE, mu poviem, aby sa pozrel na ten text priamo tam a OLD_TEXT, ktorý chcem nájsť a odstrániť, teda všetky číslice. Takže vytvorím konštantu poľa (1, 2, 3, 4, 5 všetkých číslic a). Teraz je to konštanta poľa a sedí v OLD_TEXTe, pretože tam nedávam jednu položku, ale namiesto nej vkladám veľa položiek. Toto je operácia s poľom argumentov funkcie. Existuje 10 rôznych predmetov, ktoré SUBSTITUTE dajú pokyn, aby doručili 10 samostatných položiek, a to správne, a ak niektorú z nich nájde, čo chcem? „“. To povedie funkcii, aby tam nič nedávala,). (= NÁHRADA (A2; (1,2,3,4,5,6,7,8,9,0), „“))
Teraz je môj kurzor na konci. Keď stlačím kláves F9, je to isté, pretože je tam iba 0, všetky sú si úplne rovnaké, okrem tej poslednej. V prípade posledného z nich SUBSTITUTE našiel 0 a na svoje miesto nedával nič. Teraz, CONTROL-Z, CONTROL-ENTER a idem to skopírovať priamo sem, F2 a F9. Takže ak ideme na 6, je tu verzia 5, tam odstránila 5, tam odstránila 6 a tam odstránila 8, takže budú 1, 2, 3 rôzne položky, ktoré sa líšia. Iba vtedy, keď sa všetky položky budú presne rovnať pôvodným položkám, zistí nám, že neexistujú žiadne číslice. ÚNIK.
Vrátim sa späť na vrchol. Znie to ako F2, toto je logický test AND. Funkcia AND. Chcem skontrolovať, či každá z týchto položiek v tomto výslednom poli je = k pôvodnej položke. Keď sú všetky pravdivé, povedie mi to, že v tomto textovom kruhu nie sú žiadne čísla. ), CONTROL + ENTER, dostanem FALSE, pretože jednému z nich interne chýba hodnota 0. Toto skopírujem sem. Tento samozrejme získa TRUE - rovnako ako tieto - pretože všetky interne generované položky, ak mám práve tu F2, všetky tieto, F9, sú presne rovnaké ako pôvodné. ÚNIK. Teraz prichádzam na vrchol. Mimochodom, nemusel som používať CONTROL + SHIFT + ENTER, pretože keď vo svojom vzorci poľa použijete túto konštantu poľa, potom nemusíte používať CONTROL + SHIFT + ENTER. (= AND (NÁHRADA (A2; (1,2,3,4,5,6,7,8,9,0), „“) = A2)))
Dobre. Idem na začiatok. To je môj logický test. Ak všetky z nich budú pravdivé,, hodnota ak je pravda, v hodnote „NIE“, inak zadajte ÁNO, “). CONTORL + ENTER. Dvakrát na to kliknite. Budem musieť dvakrát kliknúť na tento a poslať ho dole. Dobre. To bola trochu zábava so SUBSTITUTE, konštantou poľa, logickým testom AND a IF, ale, poviem vám, stále nemôžem uveriť tomu flash fill, ako ste ho v podstate použili na extrakciu všetkých čísla z toho. Dobre, hodím vám to späť, pán Excel. (= IF (AND (SUBSTITUTE (A2; (1,2,3,4,5,6,7,8,9,0), „“) = A2), „nie“, „áno“))
Bill: No, ten vzorec so SUBSTITUTE a konštantou poľa a AND, to je úžasné. Musel som použiť bleskovú výplň, pretože som nemohol prísť na to. To je vynikajúce. Teraz mám tretiu cestu. Poďme sa na to pozrieť.
Teraz je tu spôsob, ako by som to skutočne vyriešil, iba trochu VBA. ALT + F11 teda prepnem na VBA, INSERT, MODULE a potom napíšem tento kód. Vytvoríme novú funkciu s názvom HASNUMBERS a odovzdáme ju hodnote bunky a začneme slovom ALPHA. Pozeráme sa na každý jeden znak, a ak je tento kód, ak je (kód ASC - 06:35) tohto znaku medzi 48 až 57, potom hovoríme, že je to ČÍSLA, EXIT FUNKCIA, a stále pokračujeme. Správne, tak to vyzerá, kým nenájde číslicu. Keď to urobí, vráti HASNUMBERS. Tu teda povieme = HASNUMBERS, ukážeme na túto bunku a dvojitým kliknutím ju skopírujeme nadol. Kedykoľvek tam uvidí číslicu, získa ČÍSLA, ALPHA, ktoré je možné ľahko vyriešiť. (= Má čísla (A2))
Dobre, rýchla rekapitulácia epizódy. Cieľ: skontrolujte stĺpec, či sú v kóde znakov nejaké číslice, teda kód v bunke. Použil som výplň blesku na odstránenie číslic a potom funkciu dĺžky, aby som zistil, či sa zmenila alebo nie. Mike mal vynikajúci vzorec, funkcia SUBSTITUTE s konštantou poľa. Nepotrebujete kombináciu klávesov CONTROL + SHIFT + ENTER. Musíte odstrániť jednu číslicu po druhej a potom pomocou funkcie AND pozrieť všetkých 10 výsledkov a zistiť, či sa každá z nich rovná pôvodnej položke. Brilantný spôsob, ako ísť, a potom, moja alternatíva, pomocou funkcie VBA skontrolujte číslice.
No hej. Chcem poďakovať všetkým, že sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste od a ExcelIsFun.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Duel186.xlsm
