Noen av spørsmålene som kommer inn er ganske vanskelige. I dag har vi en kolonne med celler. Hver celle har noen ord, så en dato, så noen flere ord. Målet er å trekke datodelen til teksten til en ny kolonne. Dette er en duelleringsepisode med ideer fra Bill og Mike.
Se på video
- Bills superbrede tilnærming:
- Sett alle 12 månedene i separate kolonner
- Bruk FINN-funksjonen for å se om denne måneden er i originalteksten
- For å finne minimum startposisjon, bruk = AGGREGATE (5,6,…
- Noen få ekstra formler for å lete etter et nummer 2 eller 3 stillinger før måneden
- Mike's tilnærming:
- Bruk SØK i stedet for FINN. Funn er store og små bokstaver, søk er ikke.
- Opprett en funksjonsargument array-operasjon ved å spesifisere B13: B24 som Find_Text.
- Formelen returnerer #VALUE! Feil, men hvis du trykker på F2, F9, vil du se at den returnerer en matrise.
- De første 13 funksjonene i AGGREGATE kan ikke håndtere en matrise, men funksjonene 14-19 kan håndtere en matrise.
- 5 = MIN og 15 = SMALL (, 1) er like, men SMALL (, 1) vil fungere med en matrise.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX og AGGREGATE kan håndtere funksjonsmatriser uten Ctrl + Shift + Enter
- Mike var smartere ved å se om 2 tegn før Start er et tall, og deretter ta 3 tegn før. Den ekstra plassen elimineres av TRIM ()
- For å få tittelen, bruk SUBSTITUTE-funksjonen for å kvitte deg med datoteksten i kolonne C
Videoutskrift
Bill Jelen: Hei, velkommen tilbake. Det er på tide med nok en Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg blir med av Mike Girvin fra Excel Is Fun.
Dette er vår duell nr. 170: Finn datoer
Hei, velkommen tilbake alle sammen. Jeg hadde et så stort spørsmål her, og jeg kunne ikke løse det. I det minste kunne jeg ikke løse det så jeg gikk ut til Mike Girvin og sa: "Mike, hei, har du en måte å gjøre dette på?" Han sa: “Ja, jeg har en måte å gjøre det på. La oss gjøre en duell. ”
Så noen på YouTube sendte inn disse dataene og hver eneste celle generelt har noe som en dokumenttittel etterfulgt av en dato. De ønsket å dele disse dataene inn i dokumenttittelen: hva det er, hva tingen er og deretter hva datoen er. Men datoene er helt onde. Som her er det 20. januar; men her nede er det ting der datoen kan være etter cellen 9. april. Greit, og uansett hvilken vei det er, vil vi finne det. Og noen ganger er det to datoer, og dette er bare helt forferdelig, og at det bare er en blandet situasjon med datoer og som mulig, ikke engang en dato dukker opp, ok. Så her er forsøket mitt. Ute på høyre side skal jeg legge de tingene jeg ser etter. Det jeg virkelig liker her er at de aldri forkortet månedsnavnet. Jeg virkelig,setter stor pris på det. Så skriv inn januar, så drar jeg hit til desember slik, og for hver celle jeg vil vite, kan vi finne = FINN den januar. Så jeg skal trykke F4 en, to ganger for å låse den ned til bare en rad, i teksten der borte i Kolonne A, slik. Jeg trykker på F4 en, to, tre ganger for å låse den ned til kolonnen, ok. Og her forteller det oss at januar er funnet i posisjon 32, og for de andre 11 månedene vil den fortelle oss at den ikke er funnet i det hele tatt. Med andre ord får vi Verdifeil nå. Det jeg trenger å gjøre der er at jeg trenger å finne, jeg må finne minimumsverdien som ignorerer alle verdifeilene. Så, skjul denne lille formelen her = AGGREGATE, og la oss bygge dette bare fra bunnen av, = AGGREGATE, det vi ønsker er MIN, så det er tallet 5,og deretter ignorere feilverdiene nummer 6 komma og deretter alle disse cellene fra januar til desember. Og det som skal fortelle oss er at det kommer til å fortelle oss hvor måneden skjer. Og i dette tilfellet får vi 0, si at måneden ikke skjer i det hele tatt.
Greit nå, la oss vise resten av dette. Så, for å håndtere situasjonen der vi har 20. januar eller 1. november, sa jeg det første jeg skal gjøre er at jeg skal se på hvor den måneden starter og gå tilbake to celler, to celler, to tegn , to tegn. Og se om det er et tall, ikke så. Det er kolonnen min her, Adjust2. Juster2. Og her er hva vi skal gjøre. Jeg skal si, ta MID av A2 og start den der i G2-2 for en lengde på 1, legg til 0 til den og spør, er det et tall eller ikke? Greit, så er det et tall. Og så vil vi også se etter situasjonen der det er en tosifret dato, så 20. januar. Så det kalles Adjust3, gå tilbake tre tegn fra hvor. Så det er Where, gå tilbake tre tegn for lengden på 1, legg 0 til den, og se om det 'et nummer, ok? Så skal vi justere og Justert hvor sier HVIS. HVIS det er denne rare saken var 0, skal vi bare sette en virkelig stor verdi 999; Ellers skal vi gå fra G2 og enten gå tilbake 3, hvis Adjust3 er sant eller gå tilbake 2 hvis Adjust2 er sant, eller hvis ingen av dem er sanne, kommer Hvor å være der måneden starter. OK, nå som vi vet at den justerte hvor, dobbeltklikker vi for å kopiere den ned. Vel, hei nå, det er veldig enkelt. Vi skal bare - for tittelen skal vi si ta til venstre for A2, hvor mange tegn vi vil ha. Vi vil ha D2-1 fordi det er -1 er å bli kvitt plassen på slutten. Selv om jeg antar at TRIM også blir kvitt plassen på slutten.HVIS det er denne rare saken var 0, skal vi bare sette en virkelig stor verdi 999; Ellers skal vi gå fra G2 og enten gå tilbake 3, hvis Adjust3 er sann eller gå tilbake 2 hvis Adjust2 er sann, eller hvis ingen av dem er sanne, kommer Hvor å være der måneden starter. OK, nå som vi vet at den justerte hvor, dobbeltklikker vi for å kopiere den ned. Vel, hei nå, det er veldig enkelt. Vi skal bare - for tittelen skal vi si ta til venstre for A2, hvor mange tegn vi vil ha. Vi vil ha D2-1 fordi det er -1 er å bli kvitt plassen på slutten. Selv om jeg antar at TRIM også blir kvitt plassen på slutten.HVIS det er denne rare saken var 0, skal vi bare sette en virkelig stor verdi 999; Ellers skal vi gå fra G2 og enten gå tilbake 3, hvis Adjust3 er sant eller gå tilbake 2 hvis Adjust2 er sant, eller hvis ingen av dem er sanne, kommer Hvor å være der måneden starter. OK, nå som vi vet at den justerte hvor, dobbeltklikker vi for å kopiere den ned. Vel, hei nå, det er veldig enkelt. Vi skal bare - for tittelen skal vi si ta til venstre for A2, hvor mange tegn vi vil ha. Vi vil ha D2-1 fordi det er -1 er å bli kvitt plassen på slutten. Selv om jeg antar at TRIM også blir kvitt plassen på slutten.eller hvis ingen av dem er sanne, kommer Hvor å være der måneden starter. OK, nå som vi vet at den justerte hvor, dobbeltklikker vi for å kopiere den ned. Vel, hei nå, det er veldig enkelt. Vi skal bare - for tittelen skal vi si ta til venstre for A2, hvor mange tegn vi vil ha. Vi vil ha D2-1 fordi det er -1 er å bli kvitt plassen på slutten. Selv om jeg antar at TRIM også blir kvitt plassen på slutten.eller hvis ingen av dem er sanne, kommer Hvor å være der måneden starter. OK, nå som vi vet at den justerte hvor, dobbeltklikker vi for å kopiere den ned. Vel, hei nå, det er veldig enkelt. Vi skal bare - for tittelen skal vi si ta til venstre for A2, hvor mange tegn vi vil ha. Vi vil ha D2-1 fordi det er -1 er å bli kvitt plassen på slutten. Selv om jeg antar at TRIM også blir kvitt plassen på slutten.s -1 er å kvitte seg med plassen på slutten. Selv om jeg antar at TRIM også blir kvitt plassen på slutten.s -1 er å kvitte seg med plassen på slutten. Selv om jeg antar at TRIM også blir kvitt plassen på slutten.
Og så for datoen skal vi bruke MID. MID for- MID av A2 starter ved Justert Hvor i D2 og går ut 50 eller hva som helst du tror det muligens kan være, og deretter TRIM-funksjonen, og vi vil dobbeltklikke for å kopiere den ned.
Greit nå, grunnen til at jeg nådde ut til Mike er at jeg sa, jeg lurer på om det er en måte at jeg kan erstatte disse 12 kolonnene med en enkelt form, faktisk disse 13 kolonnene med en enkelt form. Er det noen måte jeg kan gjøre dette ved hjelp av en Array-formel? Og Mike skrev selvfølgelig den flotte boka, Ctrl + Shift + Enter, om matriseformler. Og jeg prøvde noen forskjellige ting, og i tankene mine var det ingen måte at det kunne gjøres. Greit, men du vet, la oss spørre eksperten. Så Mike, la oss se hva du har.
Mike Girvin: Takk. Hei, og snakker om ekspert, dette ble ganske faglig gjort. Du brukte FINN, AGGREGATE, ISNUMBER (MID. Nå, da du sendte dette spørsmålet til meg, gikk jeg videre og løste det, og det er utrolig hvor lik løsningen min er din.
Ok, jeg skal gå over til dette arket her. Jeg skal begynne med å finne ut hvor startposisjonen i denne tekststrengen er for hver spesielle måned. Nå som jeg skal gjøre det, skal jeg, hei, bruke denne SØK-funksjonen. Nå, du brukte FINN, jeg bruker SØK. Sannsynligvis er FINN bedre i denne situasjonen fordi FINN er store og små bokstaver, SØK er det ikke. Nå, normalt, hva vi gjør med FINN eller SØK, sier jeg, hei, gå FINN, januar, komma i denne større tekststrengen, det er slik vi normalt bruker SØK Ctrl + Enter, og den teller på fingeren: en, to, tre , fire fem. Det står at den 32. karakteren er der den fant januar.
Nå, i stedet for å gjøre det i mange celler på tvers av kolonnene, skal jeg treffe F2, komme opp hit og FIND_TEXT. Legg merke til at vi ga den 1 vare, SØK ga oss 1 svar. Men hvis jeg markerer hele kolonnen med månedsnavn, legger jeg mange ting der i stedet for et enkelt element. Dette er et funksjonsargument. Vi legger inn en rekke elementer, og det betyr at vi gjør en Function Argument Array-operasjon. Hver gang du gjør det, forteller du funksjonen, hei, gi meg 12 svar, 1 for hver måned. Nå vil dette levere en matrise, så hvis jeg prøver å skrive inn dette og kopiere ned, kommer det ikke til å fungere.
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.
Så jeg vil takke alle for at de var innom. Vi sees neste gang for en annen Dueling Excel Podcast fra og Excel er gøy.
Last ned fil
Last ned eksempelfilen her: Duel180.xlsm
