Daži no uzdotajiem jautājumiem ir diezgan grūti. Šodien mums ir šūnu kolonna. Katrā šūnā ir daži vārdi, pēc tam datums, pēc tam vēl daži vārdi. Mērķis ir šī teksta datuma daļu novirzīt uz jaunu kolonnu. Šī ir duelis epizode ar Bill un Mike idejām.
Skatīties video
- Bila super plašā pieeja:
- Visus 12 mēnešus ievietojiet atsevišķās kolonnās
- Izmantojiet funkciju ATRAST, lai redzētu, vai šis mēnesis ir oriģinālajā tekstā
- Lai atrastu minimālo sākuma pozīciju, izmantojiet = AGGREGATE (5,6,…
- Dažas papildu formulas, lai meklētu skaitļa 2 vai 3 pozīcijas pirms mēneša
- Maika pieeja:
- Izmantojiet MEKLĒT, nevis ATRAST. Meklēt ir reģistrjutīga, meklēšana nav.
- Izveidojiet funkcijas argumentu masīva darbību, norādot B13: B24 kā Find_Text.
- Formula atgriež vērtību #VALUE! Kļūda, bet, nospiežot F2, F9, redzēsiet, ka tas atgriež masīvu.
- Pirmās 13 funkcijas AGGREGATE nevar apstrādāt masīvu, bet funkcijas 14-19 var apstrādāt masīvu.
- 5 = MIN un 15 = SMALL (, 1) ir līdzīgi, bet SMALL (, 1) darbosies ar masīvu.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX un AGGREGATE var apstrādāt funkciju masīva argumentus bez Ctrl + Shift + Enter
- Maiks bija gudrāks, meklējot, vai 2 rakstzīmes pirms Starta ir skaitlis, un pēc tam satverot 3 rakstzīmes. Papildu vietu izslēdz TRIM ()
- Lai iegūtu nosaukumu, izmantojiet funkciju SUBSTITUTE, lai atbrīvotos no datuma teksta C slejā
Video atšifrējums
Bils Jelens: Hei, laipni gaidīti. Ir pienācis laiks kārtējam Dueling Excel Podcast. Es esmu Bils Jelen no. Man pievienosies Maiks Girvins no Excel Is Fun.
Šis ir mūsu duelis # 170: Datumu atrašana
Sveiki, laipni aicināti visi. Man šeit bija tik lielisks jautājums, un es to nevarēju atrisināt. Vismaz es to nevarēju viegli atrisināt, tāpēc es izgāju pie Maika Žirvina un teicu: "Maik, hei, vai tev ir veids, kā to izdarīt?" Viņš teica: „Jā, man ir veids, kā to izdarīt. Uztaisīsim dueli. ”
Tātad, kāds pakalpojumā YouTube nosūtīja šos datus un katrā šūnā kopumā ir kaut kas līdzīgs dokumenta nosaukumam, kam seko datums. Viņi vēlējās sadalīt šos datus dokumenta nosaukumā: kas tas ir, kāda ir lieta un pēc tam kāds ir datums. Bet datumi ir pilnīgi ļauni. Tāpat kā šeit, ir 20. janvāris; bet šeit ir lietas, kurās datums varētu būt pēc šūnas, 9. aprīļa. Labi, un neatkarīgi no tā, kādā veidā tas ir, mēs vēlamies to atrast. Dažreiz ir divi datumi, un tas ir vienkārši briesmīgi, un ka tas ir tik vienkārši sajaukts datumu stāvoklis un, cik iespējams, pat nav datuma, labi. Tātad, šeit ir mans mēģinājums. Labajā pusē es ievietošu meklētās lietas. Kas man šeit ļoti patīk, tie nekad nav saīsināti mēneša nosaukumā. ES tiešām,tiešām to novērtēju. Tāpēc ierakstiet janvārī, un es šeit vilkšu līdz decembrim, un vai par katru šūnu, kuru es vēlos uzzināt, vai mēs to varam atrast = ATRAST tajā janvārī Tāpēc es nospiedīšu F4 vienu, divas reizes, lai bloķētu to tikai rindā, tekstā, kas atrodas tur A slejā. Es nospiedīšu F4 vienu, divas, trīs reizes, lai to labi nofiksētu līdz kolonnai. Un šeit tas mums saka, ka janvāris ir atrodams 32. pozīcijā un pārējos 11 mēnešus tas mums paziņos, ka tas vispār nav atrasts. Citiem vārdiem sakot, tagad mēs saņemam kļūdas vērtību. Tas, kas man jādara, ir jāatrod, jāatrod minimālā vērtība, ignorējot visas vērtības kļūdas. Tātad, paslēpiet šo mazo formulu šeit = AGGREGATE un izveidosim to tikai no nulles, = AGGREGATE, ko mēs vēlamies, ir MIN, tāpēc tas ir skaitlis 5un pēc tam ignorējiet kļūdas vērtību skaitli 6 komatu un pēc tam visas šīs šūnas no janvāra līdz decembrim. Un tas, kas mums pastāstīs, ir tas, kas mums pateiks, kur mēnesis notiek. Un šajā gadījumā mēs saņemsim 0, teiksim, mēnesis vispār nenotiek.
Labi tagad, paslēpīsim pārējo. Tātad, lai risinātu situāciju, kurā mums ir 20. janvāris vai 1. novembris, es teicu, ka pirmā lieta, ko es darīšu, ir tas, ka es apskatīšu, kur sākas šis mēnesis, un atgriezīšos divas šūnas, divas šūnas, divas rakstzīmes , divas rakstzīmes. Un redziet, vai tas ir skaitlis, nevis tā. Tā ir mana sleja, ko šeit saucu - Adjust2. Pielāgot2. Un lūk, ko mēs darīsim. Es teikšu: paņemiet A2 MID, sākot no tā, kur G2-2 garumā 1, pievienojiet 0 un vaicājiet, vai tas ir skaitlis vai nē? Labi, tāpat ir skaitlis. Tad mēs meklēsim arī situāciju, kad tas ir divciparu datums, tātad 20. janvāris. Tātad, ko sauc par Adjust3, atgriezieties 3 rakstzīmes no vietas. Tātad tur ir Kur, atgriezieties trīs rakstzīmes garumā 1, pievienojiet tam 0 un pārbaudiet, vai tas irsa numurs, labi? Tad mēs pielāgosimies un Pielāgotajā vietā teikts JA. JA tas ir šis dīvainais gadījums bija 0, mēs vienkārši uzliksim patiešām lielu vērtību 999; pretējā gadījumā mēs ejam no G2 un vai nu atgriezīsimies atpakaļ 3, ja Pielāgot3 ir taisnība, vai atgriezīsimies atpakaļ 2, ja Pielāgot2 ir Patiesi, vai arī, ja neviens no tiem nav Patiesība, Kur būs, kur mēnesis sākas. Labi, tagad, kad mēs zinām, ka pielāgotā vieta, mēs divreiz noklikšķināsim, lai to nokopētu. Nu, hei tagad, tas ir patiešām viegli. Mēs tikai ejam - attiecībā uz virsrakstu mēs teiksim, paņemiet kreiso pusi no A2, cik rakstzīmes mēs vēlamies. Mēs vēlamies D2-1, jo tas ir -1, lai atbrīvotos no vietas beigās. Lai gan es domāju, ka TRIM arī atbrīvojas no vietas beigās.JA tas ir šis dīvainais gadījums bija 0, mēs vienkārši uzliksim patiešām lielu vērtību 999; pretējā gadījumā mēs ejam no G2 un vai nu atgriezīsimies atpakaļ 3, ja Pielāgot3 ir taisnība, vai atgriezīsimies atpakaļ 2, ja Pielāgot2 ir Patiesi, vai arī, ja neviens no tiem nav Patiesība, Kur būs, kur mēnesis sākas. Labi, tagad, kad mēs zinām, ka pielāgotā vieta, mēs divreiz noklikšķināsim, lai to nokopētu. Nu, hei tagad, tas ir patiešām viegli. Mēs vienkārši ejam - attiecībā uz virsrakstu mēs teiksim, paņemiet kreiso pusi no A2, cik rakstzīmes mēs vēlamies. Mēs vēlamies D2-1, jo tas ir -1, lai atbrīvotos no vietas beigās. Lai gan es domāju, ka TRIM arī atbrīvojas no vietas beigās.JA tas ir šis dīvainais gadījums bija 0, mēs vienkārši uzliksim patiešām lielu vērtību 999; pretējā gadījumā mēs ejam no G2 un vai nu atgriezīsimies atpakaļ 3, ja Pielāgot3 ir taisnība, vai atgriezīsimies atpakaļ 2, ja Pielāgot2 ir Patiesība, vai arī, ja neviens no tiem nav Patiesība, kur notiks mēnesis. Labi, tagad, kad mēs zinām, ka pielāgotā vieta, mēs divreiz noklikšķināsim, lai to nokopētu. Nu, hei tagad, tas ir patiešām viegli. Mēs vienkārši ejam - attiecībā uz virsrakstu mēs teiksim, paņemiet kreiso pusi no A2, cik rakstzīmes mēs vēlamies. Mēs vēlamies D2-1, jo tas ir -1, lai atbrīvotos no vietas beigās. Lai gan es domāju, ka TRIM arī atbrīvojas no vietas beigās.vai ja neviens no tiem nav patiess, kur notiks mēnesis. Labi, tagad, kad mēs zinām, ka pielāgotā vieta, mēs divreiz noklikšķināsim, lai to nokopētu. Nu, hei tagad, tas ir patiešām viegli. Mēs vienkārši ejam - attiecībā uz virsrakstu mēs teiksim, paņemiet kreiso pusi no A2, cik rakstzīmes mēs vēlamies. Mēs vēlamies D2-1, jo tas ir -1, lai atbrīvotos no vietas beigās. Lai gan es domāju, ka TRIM arī atbrīvojas no vietas beigās.vai ja neviens no tiem nav patiess, kur notiks mēnesis. Labi, tagad, kad mēs zinām, ka pielāgotā vieta, mēs divreiz noklikšķināsim, lai to nokopētu. Nu, hei tagad, tas ir patiešām viegli. Mēs vienkārši ejam - attiecībā uz virsrakstu mēs teiksim, paņemiet kreiso pusi no A2, cik rakstzīmes mēs vēlamies. Mēs vēlamies D2-1, jo tas ir -1, lai atbrīvotos no vietas beigās. Lai gan es domāju, ka TRIM arī atbrīvojas no vietas beigās.s -1 ir atbrīvoties no vietas beigās. Lai gan es domāju, ka TRIM arī atbrīvojas no vietas beigās.s -1 ir atbrīvoties no vietas beigās. Lai gan es domāju, ka TRIM arī atbrīvojas no vietas beigās.
Pēc tam datumam mēs izmantosim MID. MID for- MID A2, sākot no pielāgotās vietas D2 un izejiet 50 vai jebkuru citu, kas, jūsuprāt, varētu būt, un pēc tam TRIM funkciju, un mēs veicam dubultklikšķi, lai to nokopētu.
Labi, iemesls, kāpēc es sazinājos ar Maiku, ir tas, ka es teicu, nez vai ir veids, kā es varētu aizstāt šīs 12 kolonnas ar vienu veidlapu, patiesībā šīs 13 kolonnas ar vienu veidlapu. Vai es varu kaut kādā veidā to izdarīt, izmantojot masīva formulu? Un Maiks, protams, uzrakstīja šo lielisko grāmatu Ctrl + Shift + Enter par Array formulām. Es izmēģināju dažas dažādas lietas, un, manuprāt, to nevarēja izdarīt. Labi, bet zini, iesim pajautāt ekspertam. Tātad Maiks, paskatīsimies, kas jums ir.
Maiks Girvins: Paldies ,. Hei, un, runājot par ekspertu, tas tika izdarīts diezgan prasmīgi. Jūs izmantojāt FIND, AGGREGATE, ISNUMBER (MID. Tagad, kad nosūtījāt šo jautājumu man, es turpināju to atrisināt, un ir pārsteidzoši, cik mans risinājums ir līdzīgs jūsu.
Labi, es šeit pārietu uz šo lapu. Es sākšu ar to, lai noskaidrotu, kur ir sākuma pozīcija šajā teksta virknē katram konkrētajam mēnesim. Tagad, kā es to darīšu, es izmantoju šo MEKLĒŠANAS funkciju. Tagad jūs izmantojāt ATRAST, es izmantoju MEKLĒŠANU. Faktiski, iespējams, šajā situācijā labāk ir atrast, jo FIND ir reģistrjutīgs, bet MEKLĒJUMS nav. Tagad parasti tas, ko mēs darām ar ATRAST vai MEKLĒT, es saku, hei, ej ATRADI, janvāris, komats šajā lielākajā teksta virknē, tā mēs parasti izmantojam MEKLĒŠANAS Ctrl + Enter, un tas skaitās uz pirksta: viens, divi, trīs , četri, pieci. Tajā teikts, ka 32. raksturs ir tas, kur tas atrada janvāri.
Now, instead of doing it in many cells across the columns, I'm going to hit F2, come up here and the FIND_TEXT. Notice we gave it 1 item, SEARCH gave us 1 answer. But if I highlight the entire column of month names, now instead of a single item I put many items in there. This is a Function Argument. We're putting an array of items in and so that means we're doing a Function Argument Array operation. Any time you do that, you tell the function, hey, give me 12 answers, 1 for each month. Now this will deliver an array so if I try to Enter this and copy down it's not going to work.
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.
Tātad, es vēlos pateikties visiem, kas apstājās. Tiksimies nākamreiz, lai skatītu citu Dueling Excel Podcast no un Excel ir jautri.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Duel180.xlsm
