Mõned sissetulevad küsimused on üsna keerulised. Täna on meil lahtrid. Igas lahtris on mõned sõnad, siis kuupäev, siis veel mõned sõnad. Eesmärk on tõmmata selle teksti kuupäevaosa uude veergu. See on duelliepisood Bill ja Mike ideedega.
Vaata videot
- Billi ülilai lähenemine:
- Pange kõik 12 kuud eraldi veergudesse
- Funktsiooni LEIA abil saate kontrollida, kas see kuu on originaaltekstis
- Minimaalse lähtepositsiooni leidmiseks kasutage = AGGREGATE (5,6,…
- Paar lisavalemit, et otsida enne kuud number 2 või 3
- Mike'i lähenemine:
- LEIA asemel kasutage OTSI. Leid on tõstutundlik, Otsing mitte.
- Looge funktsiooni argumendi massiivi toiming, määrates funktsiooni Find_Text väärtuseks B13: B24.
- Valem tagastab väärtuse #VALUE! Viga, kuid kui vajutate F2, F9, näete, et see tagastab massiivi.
- AGGREGATE'i esimesed 13 funktsiooni ei saa massiivi käsitseda, kuid funktsioonid 14-19 saavad massiivi käsitseda.
- 5 = MIN ja 15 = SMALL (, 1) on sarnased, kuid SMALL (, 1) töötab massiiviga.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX ja AGGREGATE saavad funktsioonimassiivi argumente käsitleda ilma klahvikombinatsioonidena Ctrl + Tõst + Enter
- Mike oli targem, uurides, kas 2 tähemärki enne Starti on number, ja haarates siis enne 3 tähemärki. Lisaruumi kõrvaldab TRIM ()
- Pealkirja saamiseks kasutage veerus C kuupäeva tekstist vabanemiseks funktsiooni SUBSTITUTE
Video ärakiri
Bill Jelen: Hei, tere tulemast tagasi. Aeg on veel üks Dueling Exceli podcast. Olen Bill Jelen pärit. Minuga liitub Mike Girvin Excel Is Funist.
See on meie duell nr 170: kuupäevade leidmine
Tere, tere tulemast kõik tagasi. Mul oli siin nii tore küsimus ja ma ei suutnud seda lahendada. Vähemalt ei suutnud ma seda lihtsalt lahendada, nii et läksin välja Mike Girvini juurde ja ütlesin: "Mike, hei, kas sul on võimalus seda teha?" Ta ütles: "Jah, mul on võimalus seda teha. Teeme duelli. "
Niisiis, keegi YouTube'is saatis need andmed ja igal lahtril üldiselt on midagi sellist nagu dokumendi pealkiri, millele järgneb kuupäev. Nad tahtsid need andmed jaotada dokumendi pealkirjaks: mis see on, mis asi on ja siis mis kuupäev on. Kuid kuupäevad on täiesti kurjad. Nagu siin, on ka 20. jaanuar; kuid siin on asju, kus kuupäev võib olla pärast lahtrit, 9. aprill. Olgu, ja ükskõik, mis suunas see ka pole, tahame selle leida. Ja mõnikord on seal kaks kuupäeva ja see on lihtsalt täiesti kohutav ning et see on selline lihtsalt kuupäevade segane olukord ja kui võimalik, siis pole isegi kuupäeva üles näidata, eks. Nii, siin on minu katse. Paremal pool välja panen otsitud asjad. Mis mulle siin väga meeldib, pole nad kunagi kuu nime lühendanud. Ma tõesti,tõesti hindan seda. Nii et sisestage jaanuar ja ma venitan siia detsembrisse niimoodi ja iga lahtriga, mida ma tahan teada saada, leiame = LEIA see jaanuar. Nii et ma vajutan klahvi F4 üks, kaks korda, et see lihtsalt reale lukustada. Vajutan klahvi F4 üks, kaks, kolm korda, et see veeru külge lukustada, olgu. Ja siin ütleb see meile, et jaanuar on leitud positsioonil 32 ja ülejäänud 11 kuud ütleb see meile, et seda pole üldse leitud. Teisisõnu saame nüüd vea Väärtus. Mida ma pean seal tegema, pean leidma, pean leidma minimaalse väärtuse, ignoreerides kõiki väärtuse vigu. Niisiis, peitke see väike valem siin = AGGREGATE ja ehitame selle lihtsalt nullist, = AGGREGATE, mida me tahame, on MIN, nii et see on number 5,ja siis ignoreerige vea väärtusi koma 6 ja seejärel kõiki neid lahtrid jaanuarist detsembrini. Ja see, mis meile ütleb, on see, mis ütleb meile, kus kuu juhtub. Ja sel juhul saame 0, ütleme, et kuud ei juhtu üldse.
Hea küll, peidame ülejäänud osa varjamatuks. Niisiis, et lahendada olukorda, kus meil on 20. jaanuar või 1. november, ütlesin esimese asjana, et vaatan, kust see kuu algab, ja lähen tagasi kaks lahtrit, kaks lahtrit, kaks märki , kaks tähemärki. Ja vaadake, kas see on number, mitte nii. See on minu siinne veerg nimega Adjust2. Reguleeri2. Ja siin me teeme. Ma ütlen, et võtke A2 MID, alustades sellest, kus G2-2-s on pikkus 1, lisage sellele 0 ja küsige, kas see on number või mitte? Hästi, nii on ka number. Ja siis otsime ka olukorda, kus on kahekohaline kuupäev, seega 20. jaanuar. Nii et seda nimetatakse Adjust3, minge tagasi 3 tähemärki kust. Nii et seal on kus, minge tagasi kolme tähemärgi pikkuseks 1, lisage sellele 0 ja vaadake, kas see on "sa number, olgu? Siis hakkame kohanema ja Kohandatud koht ütleb KUI. KUI see kummaline juhtum oli 0, paneme lihtsalt tõeliselt suure väärtuse 999; vastasel juhul läheme G2-st ja läheme kas tagasi 3-le, kui korrigeerimine 3 on tõene, või 2-le tagasi, kui korrigeerimine 2 on tõene, või kui ükski neist pole tõene, siis kus kuu algab, kus asub. Hästi, nüüd, kui teame, et see kohandatud koht, topeltklõpsake selle kopeerimiseks. Noh, hei nüüd, see on tõesti lihtne. Me lihtsalt läheme - Pealkirja jaoks ütleme, et võtke A2-st vasakule, mitu tähemärki me tahame. Soovime D2-1, sest see on -1, et vabaneda lõpus olevast ruumist. Kuigi küllap vabaneb ka TRIM lõpuks ruumist.KUI see kummaline juhtum oli 0, paneme lihtsalt tõeliselt suure väärtuse 999; vastasel juhul läheme G2-st ja läheme kas tagasi 3-le, kui korrigeerimine 3 on tõene, või 2-le tagasi, kui korrigeerimine 2 on tõene, või kui ükski neist pole tõene, siis kus kuu algab, kus asub. Hästi, nüüd, kui teame, et see kohandatud koht, topeltklõpsake selle kopeerimiseks. Noh, hei nüüd, see on tõesti lihtne. Me lihtsalt läheme - Pealkirja jaoks ütleme, et võtke A2-st vasakule, mitu tähemärki me tahame. Soovime D2-1, sest see on -1, et vabaneda lõpus olevast ruumist. Kuigi küllap vabaneb ka TRIM lõpuks ruumist.KUI see kummaline juhtum oli 0, paneme lihtsalt tõeliselt suure väärtuse 999; muidu läheme G2-st ja läheme kas tagasi 3-le, kui korrigeerimine 3 on tõene, või 2-le tagasi, kui korrigeerimine 2 on tõene, või kui ükski neist pole tõene, siis kus algab kuu, algab see. Hästi, nüüd, kui teame, et see kohandatud koht, topeltklõpsake selle kopeerimiseks. Noh, hei nüüd, see on tõesti lihtne. Me lihtsalt läheme - Pealkirja jaoks ütleme, et võtke A2-st vasakule, mitu tähemärki me tahame. Soovime D2-1, sest see on -1, et vabaneda lõpus olevast ruumist. Kuigi küllap vabaneb ka TRIM lõpuks ruumist.või kui ükski neist pole tõsi, siis kus algab kuu, algab. Hästi, nüüd, kui teame, et see kohandatud koht, topeltklõpsake selle kopeerimiseks. Noh, hei nüüd, see on tõesti lihtne. Me lihtsalt läheme - Pealkirja jaoks ütleme, et võtke A2-st vasakule, mitu tähemärki me tahame. Tahame D2-1, sest see on -1, et vabaneda lõpus olevast ruumist. Kuigi küllap vabaneb ka TRIM lõpuks ruumist.või kui ükski neist pole tõsi, siis kus algab kuu, algab. Hästi, nüüd, kui teame, et see kohandatud koht, topeltklõpsake selle kopeerimiseks. Noh, hei nüüd, see on tõesti lihtne. Me lihtsalt läheme - Pealkirja jaoks ütleme, et võtke A2-st vasakule, mitu tähemärki me tahame. Soovime D2-1, sest see on -1, et vabaneda lõpus olevast ruumist. Kuigi küllap vabaneb ka TRIM lõpuks ruumist.s -1 on vabaneda lõpus olevast ruumist. Kuigi küllap vabaneb ka TRIM lõpuks ruumist.s -1 on vabaneda lõpus olevast ruumist. Kuigi küllap vabaneb ka TRIM lõpus olevast ruumist.
Ja siis kuupäeva jaoks kasutame MID-i. MID for- MID A2, mis algab kohandatud kohast D2-s ja kustub 50 või mis iganes, mis teie arvates võiks olla, ja seejärel funktsioon TRIM ja topeltklõpsame selle kopeerimiseks.
Hästi, põhjus, miks ma Mike poole pöördusin, on see, et ma ütlesin: huvitav, kas oleks võimalus asendada need 12 veergu ühe vormiga, tegelikult need 13 veergu ühe vormiga. Kas saaksin seda mingil viisil kasutada massiivi valemi abil? Ja Mike kirjutas muidugi selle suurepärase raamatu Ctrl + Shift + Enter massiivi valemitele. Ja ma proovisin mõnda erinevat asja ja minu meelest polnud seda kuidagi võimalik teha. Hästi, aga tead, lähme küsime eksperdi käest. Nii et Mike, vaatame, mis teil on.
Mike Girvin: aitäh ,. Hei, ja kui rääkida asjatundjast, siis see oli päris asjatundlikult tehtud. Kasutasite funktsiooni FIND, AGGREGATE, ISNUMBER (MID. Nüüd, kui selle küsimuse mulle saatsite, läksin edasi ja lahendasin selle ning on hämmastav, kui sarnane on minu lahendus teie lahendusega.
Hästi, ma lähen siin selle lehe juurde. Alustan sellest, et välja selgitada, kus on selle tekstistringi iga konkreetse kuu alguspositsioon. Nüüd, kuidas ma seda tegema hakkan, kasutan seda funktsiooni OTSI. Nüüd kasutasid sa LEIDA, mina otsin. Tõenäoliselt on selles olukorras FIND parem, kuna FIND on tõstutundlik, SEARCH mitte. Tavaliselt, mida me teeme kas LEIA või OTSI, ma ütlen, et hei, mine LEIA, jaanuar, koma selles suuremas tekstistringis, nii me tavaliselt kasutame otsingu Ctrl + Enter ja see loeb sõrmele: üks, kaks, kolm , neli, viis. Seal on öeldud, et 32. märk on see, kust ta jaanuari leidis.
Selle asemel, et seda teha paljudes veergude lahtrites, taban ma klahvi F2, tulen siia ja FIND_TEXT. Pange tähele, et andsime sellele 1 üksuse, otsinguga saadi 1 vastus. Aga kui ma toon välja kogu kuu nimede veeru, siis nüüd panen ühe elemendi asemel sinna palju üksusi. See on funktsiooni argument. Paneme üksuste massiivi sisse ja see tähendab, et teeme funktsiooni Argumentide massiiv operatsiooni. Iga kord, kui seda teete, ütlete funktsioonile, et hei, anna mulle 12 vastust, 1 iga kuu kohta. Nüüd edastab see massiivi, nii et kui proovin selle sisestada ja alla kopeerida, ei toimi see.
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.
Niisiis, ma tahan tänada kõiki peatumast. Järgmisel korral näeme teist Dueling Exceli Podcastit ja Excel on lõbus.
Laadige fail alla
Laadige näidisfail alla siit: Duel180.xlsm
