Excel - kako v celici obdržati samo številke, črk pa ne.
Oglejte si video
- Preverite stolpec in preverite, ali so v kodi številke
- Metoda računa 1:
- Flash Fill
- Mikeova metoda:
- Uporabite funkcijo SUBSTITUTE z Array Constant.
- Ctrl + Shift + Enter vam ne bo treba uporabljati, ker gre za konstanto matrike
- S tem boste odstranili po eno številko
- S funkcijo AND preverite, ali je vsak element v nastali matriki enak prvotnemu elementu
- Metoda računa 3:
- Za preverjanje številk uporabite funkcijo VBA
Video zapis
Bill: Hej. Dobrodošel nazaj. Čas je za še en dvobojni podcast Excel. Sem Bill Jelen iz. Pridružil se mi bo Mike Girvin iz ExcelIsFun. To je naša epizoda 186: da, če obstajajo številke v celici. Današnje vprašanje je poslala Jen, ima 13.000 vrstic podatkov, mora pogledati celico, če je katerikoli znak številka, ga označi kot Da, sicer Ne. V redu. No, Mike, upam, da imaš en čudovit način za to, ker jaz ne.
Uporabil bom flash fill in pri flash fill mu bom dal tukaj vzorec z nekaj črkami in številkami. Želim zagotoviti, da vključim vse možne številke, samo da bo razumelo, kaj počnem, in 0 tako. Torej, obstajajo izvirni podatki, nato pa jih bom popravil flash fill, v fiksni različici pa se bomo znebili vseh številk. Torej, pogledal bom in videl, če je kakšna številka. Če je, se je tako znebite in nato pritisnite tipko CONTROL + E, da bliskavico zapolnite, in zdaj bi morali imeti samo črke, samo črke.
In potem je vprašanje, ali se je spremenilo? Torej = ČE je to = tisto, kar iščemo, potem to pomeni, da ni bilo sprememb, nobenih številk, potem rečemo Ne, sicer Da, tako, in dvokliknite, kopirajte to dol, v redu in potem karkoli s št tam nima številk. Znebimo se prvotne vrstice in ko jo kopiramo, CONTROL + C, ALT + E, S, V in se lahko znebimo bliskavice. V redu, Mike. Poglejmo, kaj imate. (= IF (A2 = B2, "Ne", "Da"))
Mike: Vau. To mora biti najbolj neverjetna kreativna edinstvena uporaba bliskavice, ki sem jo kdaj videl. Izumili ste besedilo, izvlekli dejanske številke, CONTROL + E in takoj izvlekli vse številke in dostavili nov besedilni niz brez številk, nato pa ste opravili IF. Popolnoma lepo.
Vredu. Prišel bom do tega lista tukaj in uporabil bom funkcijo SUBSTITUTE. Zdaj, NAMESTITEV, bom rekel, naj si ogleda to besedilo tam, in OLD_TEXT, ki ga želim najti in odstraniti, no, to so vse številke. Torej, ustvaril bom matrično konstanto (1, 2, 3, 4, 5 vse številke in). Zdaj je to konstanta matrike in sedi v OLD_TEXT, ker tja ne dam niti enega elementa, ampak namestim kup elementov. To je funkcija argumentirane matrike. Tukaj je 10 različnih predmetov, ki bodo SUBSTITUTU naročili, naj dostavi 10 ločenih predmetov, in če kaj od teh najde, kaj želim? »«. To bo funkciji povedalo, da tja nič ne da,). (= NAMESTITEV (A2, (1,2,3,4,5,6,7,8,9,0), “”))
Zdaj je moj kazalec na koncu. Ko pritisnem tipko F9, zagotovo, ker je samo 0, so vsi ti popolnoma enaki, razen zadnje. V zadnjem primeru je NAMESTITEV našel 0 in nič ni postavil na svoje mesto. Zdaj pa CONTROL-Z, CONTROL-ENTER in kopiral ga bom do sem, F2 in F9. Torej, če gremo na 6, obstaja različica 5, tam je odstranil 5, tam je odstranil 6, tam pa 8, tako da bo 1, 2, 3 različnih elementov, ki se razlikujejo. Šele ko so vsi elementi popolnoma enaki prvotnemu, nam bo povedal, da ni nobenih številk. POBEG.
Vrnil se bom na vrh. Sliši se kot, F2, to je logični test IN. IN funkcija. Želim preveriti, ali je vsak posamezen element v tem rezultatskem nizu = prvotnemu elementu. Ko bodo vse resnične, mi bo povedal, da v besedilnem obroču ni številk. ), CONTROL + ENTER, dobim FALSE, ker enemu od njih notri manjka 0. To bom kopiral tukaj spodaj. Ta bo seveda dobil TRUE - enako pri teh - ker so vsi interno generirani elementi, če sem tukaj F2, so vsi ti, F9, popolnoma enaki izvirniku. POBEG. Zdaj sem prišel na vrh. Mimogrede, ni mi bilo treba uporabiti CONTROL + SHIFT + ENTER, ker ko uporabite to konstanto matrike v formuli matrike, vam ni treba uporabljati CONTROL + SHIFT + ENTER. (= IN (NAMESTITEV (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2))
Vredu. Prišel bom na začetek. To je moj logični test. Če se vsi izkažejo za resnične,, vrednost true če je vrednost »NE«, sicer vpišite DA, «). CONTORL + ENTER. Dvakrat kliknite na to. Moral bom dvakrat klikniti tega in ga poslati dol. Vredu. To je bilo malo zabavno z NAMESTO, konstanto matrike, logičnim testom AND in IF, toda, povem vam, še vedno ne morem verjeti, da je flash fill, kako ste ga uporabili, v bistvu izvlekel vse številke iz tega. V redu, vrnil vam ga bom nazaj, MrExcel. (= ČE (IN (NAMESTITEV (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2), “Ne“, „Da“))
Bill: No, formula s SUBSTITUTE in konstanto matrike ter AND, to je neverjetno neverjetno. Moral sem uporabiti flash fill, ker tega nisem mogel ugotoviti. To je briljantno. Zdaj imam tretjo pot. Oglejmo si to.
Zdaj je to način, kako bi to resnično rešil, le malo VBA. Torej, ALT + F11 preklopim na VBA, INSERT, MODULE in nato vtipkam to kodo. Ustvarili bomo novo funkcijo, imenovano HASNUMBERS, ki jo bomo prenesli na vrednost celice in začeli bomo z besedo ALFA. Ogledamo si vsak posamezen znak in če je ta koda, če je (ASC koda - 06:35) tega znaka med 48 in 57, rečemo, da je ŠTEVILA, IZHOD FUNKCIJA in nadaljujemo. Tako, tako, izgleda, dokler ne najde številke. Ko se to zgodi, vrne HASNUMBERS. Torej, tukaj bomo rekli = HASNUMBERS, pokažite na to celico in dvokliknite, da to kopirate. Kadar koli tam zagleda številko, bo ŠTEVILA, ALFA, enostavno razvrstil. (= Številke has (A2))
V redu, hiter povzetek epizode. Cilj: preverite stolpec in preverite, ali v kodi znakov obstajajo številke, koda v celici. Uporabil sem flash fill za odstranjevanje številk, nato pa funkcijo dolžine, da vidim, ali se je spremenila ali ne. Mike je imel briljantno formulo, funkcijo NAMESTITVE s konstanto matrike. Ne potrebujete CONTROL + SHIFT + ENTER. Odstraniti morate po eno števko in nato s funkcijo AND pogledati vseh 10 rezultatov, da preverite, ali je enaka prvotni postavki. Briljantna pot, nato pa, moj nadomestni, uporabite funkcijo VBA za preverjanje številk.
No, hej. Vsem se želim zahvaliti, da so se ustavili. Naslednjič se vidimo za še eno oddajo iz programa ExcelIsFun.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Duel186.xlsm
