Neka su pitanja koja dolaze prilično teška. Danas imamo kolonu stanica. Svaka ćelija ima neke riječi, zatim datum, pa još neke riječi. Cilj je povući datumski dio tog teksta u novi stupac. Ovo je dvobojna epizoda s idejama Billa i Mikea.
Gledaj video
- Billov superširok pristup:
- Stavite svih 12 mjeseci u zasebne stupce
- Upotrijebite funkciju FIND da biste vidjeli je li ovaj mjesec u izvornom tekstu
- Da biste pronašli minimalni početni položaj, upotrijebite = AGGREGATE (5,6,…
- Nekoliko dodatnih formula za traženje broja 2 ili 3 prije mjeseca
- Mikeov pristup:
- Upotrijebite SEARCH umjesto FIND. Find razlikuje velika i mala slova, Search ne.
- Stvorite operaciju polja argumenata funkcije tako što ćete odrediti B13: B24 kao Find_Text.
- Formula vraća #Vrijednost! Pogreška, ali ako pritisnete F2, F9, vidjet ćete da vraća niz.
- Prvih 13 funkcija u AGREGATE-u ne mogu obraditi niz, ali funkcije 14-19 mogu obraditi niz.
- 5 = MIN i 15 = SMALL (, 1) su slični, ali SMALL (, 1) će raditi s nizom.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX i AGGREGATE mogu obrađivati argumente niza funkcija bez Ctrl + Shift + Enter
- Mike je bio pametniji gledajući jesu li 2 znaka prije Start broja, a zatim je zgrabio 3 znaka prije. Dodatni prostor uklanja TRIM ()
- Da biste dobili Naslov, upotrijebite funkciju ZAMJENA da biste se riješili teksta Datum u stupcu C
Prijepis videozapisa
Bill Jelen: Hej, dobrodošao natrag. Vrijeme je za još jedan dvobojni Excel Podcast. Ja sam Bill Jelen iz. Pridružit će mi se Mike Girvin iz Excela je zabavno.
Ovo je naš dvoboj # 170: Traženje datuma
Hej, dobrodošli svima. Ovdje sam imao tako sjajno pitanje i nisam ga mogao riješiti. Barem to nisam mogao lako riješiti pa sam otišao do Mikea Girvina i rekao sam, "Mike, hej, imaš li načina da to učiniš?" Rekao je, „Da, moram to učiniti. Napravimo dvoboj. "
Dakle, netko s YouTubea poslao je ove podatke i općenito za svaku pojedinu ćeliju ima nešto poput naslova dokumenta nakon kojeg slijedi datum. Željeli su te podatke razbiti u naslov dokumenta: što je to, što je stvar i koji je datum. Ali datumi su potpuno zli. Kao i ovdje, 20. siječnja je; ali ovdje dolje postoje stvari gdje bi datum mogao biti nakon ćelije, 9. travnja. U redu, i bez obzira na koji način želimo to pronaći. A ponekad postoje dva datuma, a to je potpuno užasno i da je takva pomiješana situacija s datumima i što je više moguće, čak se i ne pojavljuju datumi, u redu. Dakle, evo mog pokušaja. S desne strane stavit ću stvari koje tražim. Ono što mi se ovdje stvarno sviđa jest to što nikad nisu skraćivali naziv mjeseca. Ja zbilja,stvarno cijenim to. Upišite dakle u siječanj, a ja ću se tako povući ovdje do prosinca, a za svaku ćeliju koju želim znati možemo li pronaći = PRONAĐI taj siječanj. Pa ću pritisnuti F4 jedan, dva puta da ga zaključam na samo jedan red, u tekstu tamo u stupcu A, tako. Pritisnut ću F4 jedan, dva, tri puta da se zaključa na stupac, u redu. I evo, govori nam da se siječanj nalazi na poziciji 32, a tijekom ostalih 11 mjeseci reći će nam da ga uopće nema. Drugim riječima, sada dobivamo pogrešku Vrijednost. Ono što tamo moram učiniti jest to što moram pronaći, moram pronaći minimalnu vrijednost zanemarujući sve pogreške vrijednosti. Dakle, otkrijte ovu malu formulu ovdje = AGREGATE i napravimo to samo od nule, = AGGREGATE, ono što želimo je MIN, dakle to je broj 5,a zatim zanemarite vrijednosti pogreške broj 6 zarez, a zatim sve ove stanice od siječnja do prosinca. A ono što će nam reći, to će nam reći gdje se mjesec događa. A u ovom ćemo slučaju dobiti 0, recimo da se mjesec uopće ne događa.
Dobro, otkrijemo ostatak ovoga. Dakle, kako bih riješio situaciju kada ovdje imamo 20. siječnja ili 1. studenog, rekao sam da ću prvo što ću učiniti pogledati gdje počinje taj mjesec i vratiti se dvije stanice, dvije stanice, dva znaka , dva znaka. I pogledajte je li to broj, ne baš tako. To je moja ovdje navedena kolumna, Adjust2. Podesi2. I evo što ćemo učiniti. Reći ću, uzmite MID od A2, započnite ga gdje u G2-2 za duljinu od 1, dodajte mu 0 i pitajte je li to broj ili nije? Dobro, je li to broj. A onda ćemo također potražiti situaciju kada je to dvoznamenkasti datum, dakle 20. siječnja. Dakle, to se zove Adjust3, vratite se 3 znaka odakle. Dakle, tu je Gdje, vratite se tri znaka duljine 1, dodajte joj 0 i provjerite je li to 'sa brojem, u redu? Tada ćemo se prilagoditi i Prilagođeno mjesto kaže AKO. AKO je ovaj čudan slučaj 0, stavit ćemo stvarno veliku vrijednost 999; u suprotnom, vratit ćemo se iz G2 i vratit ćemo se 3, ako je Adjust3 True, ili 2, ako je Adjust2 True, ili ako ništa od toga nije True, Gdje će biti tamo gdje mjesec počinje. U redu, sada kad znamo da je to Prilagođeno mjesto, dvaput ćemo kliknuti da to kopiramo. Pa, hej, stvarno je lako. Samo ćemo - za naslov ćemo reći skrenite lijevo od A2, koliko znakova želimo. Želimo D2-1, jer je to -1 da se riješimo prostora na kraju. Iako se valjda i TRIM na kraju rješava prostora.AKO je ovaj čudan slučaj 0, stavit ćemo stvarno veliku vrijednost 999; u suprotnom, vratit ćemo se iz G2 i vratit ćemo se 3, ako je Adjust3 True, ili 2, ako je Adjust2 True, ili ako ništa od toga nije True, Gdje će biti tamo gdje mjesec počinje. U redu, sada kad znamo da je to Prilagođeno mjesto, dvaput ćemo kliknuti da to kopiramo. Pa, hej, stvarno je lako. Samo ćemo - za naslov ćemo reći skrenite lijevo od A2, koliko znakova želimo. Želimo D2-1, jer je to -1 da se riješimo prostora na kraju. Iako se valjda i TRIM na kraju rješava prostora.AKO je ovaj čudan slučaj 0, stavit ćemo stvarno veliku vrijednost 999; u suprotnom, vratit ćemo se iz G2 i vratit ćemo se 3, ako je Adjust3 True, ili 2, ako je Adjust2 True, ili ako ništa od toga nije True, Gdje će biti tamo gdje mjesec počinje. U redu, sada kad znamo da je to Prilagođeno mjesto, dvaput ćemo kliknuti da to kopiramo. Pa, hej, stvarno je lako. Samo ćemo - za naslov ćemo reći skrenite lijevo od A2, koliko znakova želimo. Želimo D2-1, jer je to -1 da se riješimo prostora na kraju. Iako se valjda i TRIM na kraju rješava prostora.ili ako ništa od toga nije Istina, Gdje će biti tamo gdje mjesec počinje. U redu, sada kad znamo da je to Prilagođeno mjesto, dvaput ćemo kliknuti da to kopiramo. Pa, hej, stvarno je lako. Samo ćemo - za naslov ćemo reći skrenite lijevo od A2, koliko znakova želimo. Želimo D2-1, jer je to -1 da se riješimo prostora na kraju. Iako se valjda i TRIM na kraju rješava prostora.ili ako ništa od toga nije Istina, Gdje će biti mjesto gdje mjesec počinje. U redu, sada kad znamo da je to Prilagođeno mjesto, dvaput ćemo kliknuti da to kopiramo. Pa, hej, stvarno je lako. Samo ćemo - za naslov ćemo reći skrenite lijevo od A2, koliko znakova želimo. Želimo D2-1, jer je to -1 da se riješimo prostora na kraju. Iako se valjda i TRIM na kraju rješava prostora.s -1 je riješiti se prostora na kraju. Iako se valjda i TRIM na kraju rješava prostora.s -1 je riješiti se prostora na kraju. Iako se valjda i TRIM na kraju rješava prostora.
A onda ćemo za datum upotrijebiti MID. MID for - MID od A2, počevši od Prilagođenog mjesta u D2 i izađite 50 ili što već mislite da bi to moglo biti, a zatim funkcija TRIM, a mi ćemo dvaput kliknuti da to kopiramo.
U redu, razlog zbog kojeg sam kontaktirao Mikea je taj što se, pitam se, postoji li način na koji bih mogao zamijeniti ovih 12 stupaca jednim obrascem, zapravo ovih 13 stupaca jednim obrascem. Mogu li na neki način to učiniti pomoću array formule? I Mike je, naravno, napisao onu sjajnu knjigu, Ctrl + Shift + Enter, o Array formulama. Pokušao sam nekoliko različitih stvari i u mojim mislima to nikako nije moglo biti učinjeno. U redu, ali znate, idemo pitati stručnjaka. Pa Mike, da vidimo što imaš.
Mike Girvin: Hvala ,. Hej, a kad smo već kod stručnjaka, ovo je prilično stručno napravljeno. Koristili ste FIND, AGREGATE, ISNUMBER (MID. Sad, kad ste mi poslali ovo pitanje, ja sam nastavio i riješio ga, i nevjerojatno je koliko je moje rješenje slično vašem.
Dobro, idem na ovaj list ovdje. Za početak ću otkriti gdje je početni položaj u ovom tekstualnom nizu za svaki pojedini mjesec. E sad, način na koji ću to učiniti je da, hej, upotrijebim ovu funkciju SEARCH. E sad, vi ste koristili FIND, ja koristim SEARCH. Zapravo je vjerojatno FIND bolji u ovoj situaciji jer je FIND osjetljiv na velika i mala slova, SEARCH nije. Sad normalno ono što radimo bilo s PRONAĐI ili PRETRAŽIVANJEM, kažem, hej, idemo PRONAĐI, siječanj, zarez unutar ovog većeg tekstualnog niza, tako obično koristimo SEARCH Ctrl + Enter i to se računa na prst: jedan, dva, tri , četiri pet. Kaže da je 32. lik tamo gdje je pronašao siječanj.
Sada, umjesto da to radim u mnogim ćelijama preko stupaca, udarit ću F2, doći ovdje i FIND_TEXT. Primijetite da smo mu dali 1 stavku, TRAŽENJE nam je dalo 1 odgovor. Ali ako istaknem cijeli stupac s nazivima mjeseci, sada umjesto jedne stavke stavljam tamo mnoge stavke. Ovo je argument funkcije. Stavljamo niz predmeta, pa to znači da radimo operaciju argumenta Argument funkcije. Kad god to učiniš, kažeš funkciji, hej, daj mi 12 odgovora, 1 za svaki mjesec. Sad će ovo isporučiti niz, pa ako pokušam to unijeti i kopirati, to neće uspjeti.
Well, let's go down to any particular cell, F2 and then F9 to look that yes, in fact, it is delivering an array, and look at that. It looks like I F2 up here, forgot to lock it. So I'm going to click on that and F4, Ctrl+Enter, double click and send it down, F2, F9. There we go, that's that array. There's exactly 12 answers and there's the 34 and the 55. Now, from this array, since we always want the actual first month, not the second month, we want whatever the MIN is because those are number of characters in from the left. So I'm going to click Escape, come up to the top F2. I'm going to use the AGGREGATE function. Hey, we would like to use AGGREGATE 5 but no matter how hard you try if you have an array operation and we do here, if you try to put any function 1 to 13, it just doesn't work. But no problem, we have SMALL, so number 15 comma. Any one of those functions 14 to 19, they understand array operations. And once you select 14 or above, this is the screen tip you're working off, not this bottom one with the references. Alright, comma.
The second options here we want to Ignore errors, comma. That number 6 will then tell AGGREGATE to look through here and ignore the errors. It will only see the numbers. And this is one of five functions in Excel: LOOKUP some product, CHI SQUARE TEST, AGGREGATE, and INDEX that actually have a special argument that can handle Array operations without doing any special key stroke. So there is the Array, comma, and then for K we simply put A1. That's our way of getting them in. Close parentheses, Ctrl+Enter, double click and send it down. And so that tells us the position where it found the first month name from this list.
Now, we'll deal with the NUM error at the very end in our final formula. Now, we are going to have to take these and notice that sometimes there's a number before the month and sometimes, like down here in December, there is not. So I'm going to do the same thing did. I'm going to go back two characters and check whether it is a letter or in this case a number =MID, there’s the text, comma, the starting position. Well, I want to start at 32 in this case and -2 to go back to and comma. I get exactly one character. Now, if I close parenthesis MID LEFT RIGHT they all deliver text, double click and send it down and we want to check if it's a number. So watch this, the whole column is highlighted. Active cell at the top, I'm going to hit F2. We could do any Math operation to convert text numbers back to number so I'm going to add 0, Ctrl+Enter to populate this edited formula down through the column. Ctrl+Enter. Now, we can ask the question: Is the returned item a number? F2. So now I say ISNUMBER, close parenthesis, Ctrl+Enter. So now we have a pattern of Trues and Falses. Now, remember we need to get the starting position and for 32 we're definitely going to have to subtract 3 and start at that 20 but notice down here, we don't want to subtract any. So our logical test if I hit F2, that will simply be put into the IF Logical Test Argument. If that comes out True comma then I want to jump back 3 comma. Otherwise I want to jump back 0, close parenthesis, Ctrl+Enter to populate that all the way down. Now we can take this number and subtract the number over here to give us our starting position. Active cell at the top F2, I'm putting this inside of MID. There's the text, comma. And can you believe it? All of this to get the start number. So I'm going to click on that B2 and subtract our IF, come to the end comma and I'm just going to put a big number in here, 100, some big number big enough to get all the way to the end, close parenthesis and Ctrl+Enter to populate that all the way down. It looks like we have some extra spaces and that makes sense because right here we went back three, so no problem. Active cell at the top, F2, I'm going to use the haircut function, the diet function. No, the TRIM function to remove extra spaces except for single spaces between words. Come to the end, close parenthesis, Ctrl+Enter to populate that all the way down.
Now, I have the date, oh, except for the NUM. Now, I could come to the top and use IF error but then it would run all of these plus that cell right there and for a small data set, it doesn't matter at all; but, with the goal of efficiency, I'm going to say IF(ISNUMBER and I'm going to click on that cell, that way close parenthesis, comma. The trigger for whether we run the formula is only based on that instead of the entire formula. If that comes out True, we want to run the formula, comma. Otherwise, double quote double quote. That zero link text string will show nothing. Ctrl+Enter, double click and send it down. And now, all we need to do is get the Title. Well, I already have the text that I don't want in here so I'm going to use the SUBSTITUTE function. SUBSTITUTE, there's the text, comma. The old text, it's that right there, comma, the new text. Hey, I want to take that and SUBSTITUTE in nothing. There's our zero link text string, Ctrl+Enter, double click and send it down.
Now, I'm going to come over here to column B, right click, Hide and there we go. Alright, throw it back to.
Bill Jelen: Hey, Mike, that is brilliant and I know exactly, exactly where I went wrong. Right here in row 12 when the formula returned the #VALUE error, you pressed F2, F9 to see that it's returning an array. When I got the #VALUE error, I just swore a little bit and said, why isn't this working? Never thought of pressing F2, F9, alright. Also, like that, of course, MIN and SMALL(,1) are the same but the difference is SMALL(,1) will work with an array in the AGGREGATE function. That was a beautiful, beautiful trick. And then, I went through that whole hassle to look at 2 characters before and 3 characters before. You were smart enough to say, “Hey, we're going to go 2 characters before and if so, go back 3 characters.” Worst case you get a space for that extra space and eliminated by the TRIM. And then the cherry on top, using SUBSTITUTE function to get rid of the Date text in column C. What a brilliant, brilliant way to go, alright.
Dakle, želim zahvaliti svima što su navratili. Vidimo se sljedeći put za još jedan dvobojni Excel podcast od i Excel je zabavan.
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Duel180.xlsm
