Nekatera vprašanja, ki prihajajo, so precej težka. Danes imamo stolpec celic. Vsaka celica ima nekaj besed, nato datum, nato še nekaj besed. Cilj je povleči datumski del besedila v nov stolpec. To je dvobojna epizoda z idejami Billa in Mikea.
Oglejte si video
- Billov superširok pristop:
- Vseh 12 mesecev postavite v ločene stolpce
- S funkcijo FIND poiščite, ali je ta mesec v izvirnem besedilu
- Za iskanje najnižjega začetnega položaja uporabite = AGREGATE (5,6,…
- Nekaj dodatnih formul za iskanje številk 2 ali 3 pred mesecem
- Mikeov pristop:
- Namesto NAJDI uporabite ISKANJE. Najdi razlikuje med velikimi in malimi črkami, Iskanje pa ne.
- Ustvarite funkcijo argumentacije argumenta z določitvijo B13: B24 kot Find_Text.
- Formula vrne #VALUE! Napaka, vendar če pritisnete F2, F9, boste videli, da vrne matriko.
- Prvih 13 funkcij v AGREGATE ne more obdelati matrike, funkcije 14-19 pa lahko obdelajo matriko.
- 5 = MIN in 15 = SMALL (, 1) sta si podobni, SMALL (, 1) pa bo deloval z matriko.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX in AGGREGATE lahko obdelujejo argumente matrike funkcij brez Ctrl + Shift + Enter
- Mike je bil pametnejši, ko je pogledal, ali sta dva znaka pred začetkom številka, nato pa je prej zajel 3 znake. Dodaten prostor odstrani TRIM ()
- Če želite dobiti naslov, uporabite funkcijo SUBSTITUTE, da se znebite besedila Date v stolpcu C
Video zapis
Bill Jelen: Hej, dobrodošli nazaj. Čas je za še en dvobojni podcast Excel. Sem Bill Jelen iz. Pridružil se mi bo Mike Girvin iz Excela je zabavno.
To je naš dvoboj št. 170: Iskanje datumov
Hej, dobrodošli nazaj vsi. Tukaj sem imel tako čudovito vprašanje in ga nisem mogel rešiti. Vsaj tega nisem mogel zlahka rešiti, zato sem šel k Miku Girvinu in rekel: "Mike, hej, ali lahko to storiš?" Rekel je: "Da, imam način, kako to narediti. Naredimo dvoboj. «
Torej, nekdo v YouTubu je poslal te podatke in vsaka posamezna celica na splošno ima nekaj podobnega naslovu dokumenta, ki mu sledi datum. Te podatke so želeli razbiti v naslov dokumenta: kaj je to, kaj je stvar in kakšen je datum. A datumi so povsem hudi. Tako kot tukaj je 20. januarja; tukaj spodaj pa obstajajo stvari, pri katerih je datum morda po celici, 9. aprila. V redu in ne glede na to, kako to želimo, ga želimo najti. In včasih sta dva datuma in to je povsem grozno in da gre za tako zmedeno situacijo datumov in, če je le mogoče, se datum sploh ne prikaže. Torej, tukaj je moj poskus. Na desno stran bom postavil stvari, ki jih iščem. Tu mi je zelo všeč, da nikoli niso okrajšali imena meseca. Jaz res,res cenim to. Torej vnesite januar in tako bom povlekel sem do decembra in za vsako celico, ki jo želim vedeti, lahko najdemo = FIND tisti januar. Torej bom pritisnil F4 dvakrat, da ga zaklenem na samo vrstico, v besedilu tam v stolpcu A, tako. Enkrat, dvakrat, trikrat bom pritisnil F4, da ga priklenem na stolpec, v redu. In tu nam sporoča, da je januar v položaju 32, preostalih 11 mesecev pa nam bo povedal, da ga sploh ni. Z drugimi besedami, zdaj dobimo napako Value. Kar moram tam narediti, moram najti, najti moram najmanjšo vrednost, pri čemer ne upoštevam vseh napak vrednosti. Torej, odkrijte to majhno formulo tukaj = AGREGATE in zgradimo to čisto iz nič, = AGREGATE, kar želimo je MIN, to je številka 5,in nato prezrite vrednosti napak številka 6 vejica in nato vse te celice od januarja do decembra. In kar nam bo povedalo, nam bo povedalo, kje se mesec zgodi. In v tem primeru bomo dobili 0, recimo, da se mesec sploh ne zgodi.
V redu, odkrijmo ostalo. Torej, da bi rešili situacijo, ko imamo tukaj 20. januarja ali 1. novembra, sem rekel, da bom najprej pogledal, kje se začne ta mesec, in se vrnil dve celici, dve celici, dva znaka , dva znaka. In poglejte, ali je to številka, ne tako. To je moj stolpec tukaj, Adjust2. Prilagodite 2. In tukaj bomo naredili nekaj. Rekel bom, vzemite MID A2, zaženite ga kjer v G2-2 za dolžino 1, dodajte mu 0 in vprašajte, ali je to številka ali ne? V redu, tudi to je številka. Nato bomo poiskali tudi situacijo, ko gre za dvomestni datum, torej 20. januarja. Tako se imenuje Adjust3, vrnite se za 3 znake od kje. Torej obstaja Kje, vrnite se tri znake za dolžino 1, dodajte mu 0 in preverite, ali je to "Sa številka, v redu? Potem se bomo prilagodili in Prilagojeni kje pravi IF. ČE je ta čuden primer 0, bomo postavili res veliko vrednost 999; v nasprotnem primeru se bomo vrnili iz G2 in se vrnili nazaj 3, če je Adjust3 True, ali pa 2, če je Adjust2 True, ali če nobena od teh ni True, bo Kje tam, kjer se začne mesec. V redu, zdaj, ko vemo, da je to Prilagojeno kje, dvakrat kliknemo, da to kopiramo. No, hej, zdaj je res enostavno. Pravkar bomo - za naslov bomo rekli levo od A2, koliko znakov želimo. D2-1 želimo, ker je to -1, da se na koncu znebite prostora. Čeprav mislim, da se tudi TRIM na koncu znebi prostora.ČE je ta čuden primer 0, bomo postavili res veliko vrednost 999; v nasprotnem primeru se bomo vrnili iz G2 in se vrnili nazaj 3, če je Adjust3 True, ali pa 2, če je Adjust2 True, ali če nobena od teh ni True, bo Kje tam, kjer se začne mesec. V redu, zdaj, ko vemo, da je to Prilagojeno kje, dvakrat kliknemo, da to kopiramo. No, hej, zdaj je res enostavno. Pravkar bomo - za naslov bomo rekli levo od A2, koliko znakov želimo. D2-1 želimo, ker je to -1, da se na koncu znebite prostora. Čeprav mislim, da se tudi TRIM na koncu znebi prostora.ČE je ta čuden primer 0, bomo postavili res veliko vrednost 999; v nasprotnem primeru se bomo vrnili iz G2 in se vrnili nazaj 3, če je Adjust3 True, ali pa 2, če je Adjust2 True, ali če nobena od teh ni True, bo Kje tam, kjer se začne mesec. V redu, zdaj, ko vemo, da je to Prilagojeno kje, dvakrat kliknemo, da to kopiramo. No, hej, zdaj je res enostavno. Pravkar bomo - za naslov bomo rekli levo od A2, koliko znakov želimo. D2-1 želimo, ker je to -1, da se na koncu znebite prostora. Čeprav mislim, da se tudi TRIM na koncu znebi prostora.ali če nobeno od teh ni True, Kje bo, kjer se začne mesec. V redu, zdaj, ko vemo, da je to Prilagojeno kje, dvakrat kliknemo, da to kopiramo. No, hej, zdaj je res enostavno. Pravkar bomo - za naslov bomo rekli levo od A2, koliko znakov želimo. D2-1 želimo, ker je to -1, da se na koncu znebite prostora. Čeprav mislim, da se tudi TRIM na koncu znebi prostora.ali če nobeno od teh ni True, Kje bo, kjer se začne mesec. V redu, zdaj, ko vemo, da je to Prilagojeno kje, dvakrat kliknemo, da to kopiramo. No, hej, zdaj je res enostavno. Pravkar bomo - za naslov bomo rekli levo od A2, koliko znakov želimo. D2-1 želimo, ker je to -1, da se na koncu znebite prostora. Čeprav mislim, da se tudi TRIM na koncu znebi prostora.s -1 je, da se na koncu znebite prostora. Čeprav mislim, da se tudi TRIM na koncu znebi prostora.s -1 je, da se na koncu znebite prostora. Čeprav mislim, da se tudi TRIM na koncu znebi prostora.
In potem za datum, bomo uporabili MID. MID for - MID od A2, ki se začne pri Prilagojenem mestu v D2 in gremo ven 50 ali karkoli že mislite, da bi to lahko bilo, nato pa funkcija TRIM in dvakrat kliknemo, da to kopiramo.
Prav zdaj, razlog, da sem se obrnil na Mikea, je ta, da sem rekel: zanima me, ali bi lahko teh 12 stolpcev zamenjal z enim obrazcem, pravzaprav teh 13 stolpcev z enim obrazcem. Ali lahko to nekako storim s pomočjo formule Array? In Mike je seveda napisal tisto čudovito knjigo Ctrl + Shift + Enter o formulah Array. Poskusil sem nekaj različnih stvari in v mislih nikakor nisem mogel storiti. V redu, ampak veste, gremo vprašati strokovnjaka. Torej Mike, poglejmo, kaj imaš.
Mike Girvin: Hvala. Hej, in ko smo že pri strokovnjakih, je bilo to precej strokovno narejeno. Uporabili ste FIND, AGREGATE, ISNUMBER (MID. Ko ste mi to vprašanje poslali naprej, sem ga rešil in neverjetno je, kako podobna je moja rešitev vaši.
V redu, prešel bom na ta list tukaj. Začel bom z ugotovitvijo, kje je začetni položaj v tem besedilnem nizu za posamezen mesec. Zdaj, kot bom to storil, bom, hej, uporabil to funkcijo SEARCH. Zdaj ste uporabili FIND, jaz pa SEARCH. Pravzaprav je verjetno FIND v tej situaciji boljši, ker FIND razlikuje med velikimi in malimi črkami, ISKANJE pa ne. Zdaj običajno tisto, kar počnemo bodisi z FIND bodisi ISKANJE, pravim, hej, pojdi FIND, januar, vejica znotraj tega večjega besedilnega niza, tako običajno uporabljamo SEARCH Ctrl + Enter in šteje na prst: ena, dve, tri , štiri, pet. Pravi, da je 32. znak tam, kjer je našel januar.
Zdaj, namesto da bi to počel v številnih celicah v stolpcih, bom zadel F2, prišel sem gor in FIND_TEXT. Obvestilo smo mu dali 1 element, ISKANJE pa 1 odgovor. Če pa izpostavim celoten stolpec z imeni mesecev, zdaj namesto enega samega predmeta vstavim veliko elementov. To je argument funkcije. Vstavimo matriko elementov, kar pomeni, da izvajamo operacijo argumenta argumenta funkcije. Vsakič, ko to storiš, poveš funkciji, hej, daj mi 12 odgovorov, po 1 za vsak mesec. Zdaj bo to prineslo matriko, tako da če poskusim to vnesti in kopirati, ne bo šlo.
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.
Torej, rad bi se zahvalil vsem, da so se ustavili. Naslednjič se vidimo za še en dvoboj Excel Podcast od in Excel Is Fun.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Duel180.xlsm
