Unele dintre întrebările care apar sunt destul de dificile. Astăzi, avem o coloană de celule. Fiecare celulă are câteva cuvinte, apoi o dată, apoi alte cuvinte. Scopul este de a trage porțiunea de dată a textului respectiv într-o nouă coloană. Acesta este un episod duelat cu idei de la Bill și Mike.
Urmăriți videoclipul
- Abordarea super largă a lui Bill:
- Puneți toate cele 12 luni în coloane separate
- Utilizați funcția FIND pentru a vedea dacă această lună este în textul original
- Pentru a găsi poziția minimă de pornire, utilizați = AGGREGATE (5,6, …
- Câteva formule suplimentare pentru a căuta un număr de 2 sau 3 poziții înainte de lună
- Abordarea lui Mike:
- Folosiți SEARCH în loc de Găsiți. Găsirea este sensibilă la majuscule și minuscule, Căutarea nu.
- Creați o operație de matrice de argumente funcțională specificând B13: B24 ca Find_Text.
- Formula returnează #VALUE! Eroare, dar dacă apăsați F2, F9, veți vedea că returnează o matrice.
- Primele 13 funcții din AGGREGATE nu pot gestiona o matrice, dar funcțiile 14-19 pot gestiona o matrice.
- 5 = MIN și 15 = SMALL (, 1) sunt similare, dar SMALL (, 1) va funcționa cu o matrice.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX și AGGREGATE pot gestiona argumentele matricei de funcții fără Ctrl + Shift + Enter
- Mike a fost mai inteligent căutând să vadă dacă 2 caractere înainte de Start este un număr, apoi a apucat 3 caractere înainte. Spațiul suplimentar este eliminat de TRIM ()
- Pentru a obține titlul, utilizați funcția SUBSTITUTE pentru a scăpa de textul Date din coloana C
Transcriere video
Bill Jelen: Hei, bine ai revenit. Este timpul pentru un alt podcast Dueling Excel. Sunt Bill Jelen din. Mi se va alătura Mike Girvin de la Excel Is Fun.
Acesta este duelul nostru # 170: Găsirea datelor
Bine ai venit înapoi tuturor. Am avut o întrebare atât de grozavă aici și nu am putut să o rezolv. Cel puțin nu am putut rezolva cu ușurință, așa că am ieșit la Mike Girvin și i-am spus: „Mike, hei, ai vreo modalitate de a face asta?” El a spus: „Da, am o modalitate de a o face. Hai să facem un duel. ”
Deci, cineva de pe YouTube a trimis aceste date și fiecare celulă în general are ceva de genul titlului unui document urmat de o dată. Au vrut să împartă aceste date în titlul documentului: ce este, care este chestia și apoi care este data. Dar datele sunt complet rele. Ca aici, este 20 ianuarie; dar aici jos, există lucruri în care data ar putea fi după celulă, 9 aprilie. Bine, și indiferent pe ce cale este, vrem să o găsim. Și, uneori, există două întâlniri și acest lucru este complet oribil și că este o situație atât de amestecată de date și, cât posibil, nici măcar nu apare o dată, bine. Deci, iată încercarea mea. În partea dreaptă, voi pune lucrurile pe care le caut. Ceea ce îmi place cu adevărat aici este că nu au abreviat niciodată numele lunii. Eu chiar,apreciez cu adevărat asta. Așa că tastați în ianuarie și voi trage aici până în decembrie așa, și pentru fiecare celulă pe care vreau să o știu putem găsi = GĂSI acea ianuarie. Așa că voi apăsa F4 una, de două ori pentru a o bloca la doar un rând, în textul de acolo din Coloana A, așa. Voi apăsa F4 de una, de două, de trei ori pentru a o fixa pe coloană, bine. Și aici, ne spune că ianuarie se găsește în poziția 32 și, pentru celelalte 11 luni, ne va spune că nu se găsește deloc. Cu alte cuvinte, primim eroarea Value acum. Ce trebuie să fac acolo este că trebuie să găsesc, trebuie să găsesc valoarea minimă ignorând toate erorile de valoare. Deci, dezvăluie această mică formulă aici = AGGREGATE și haideți să construim asta doar de la zero, = AGGREGATE, ceea ce vrem este MIN, deci acesta este numărul 5,apoi ignorați valorile de eroare numărul 6 virgulă și apoi toate aceste celule din ianuarie până în decembrie. Și ceea ce ne va spune este că ne va spune unde se întâmplă luna. Și în acest caz, vom obține 0, să spunem că luna nu se întâmplă deloc.
Bine acum, să dezvăluim restul. Deci, pentru a rezolva situația în care avem 20 ianuarie sau 1 noiembrie, am spus că primul lucru pe care îl voi face este să mă uit de unde începe luna respectivă și să revin înapoi două celule, două celule, două caractere , două personaje. Și vezi dacă acesta este un număr, nu este așa. Aceasta este coloana mea numită aici, Ajustare2. Ajustare2. Și iată ce vom face. Voi spune, luați MID-ul A2 începând cu unde în G2-2 pentru o lungime de 1, adăugați 0 la el și întrebați, este un număr sau nu? Bine, așa este un număr. Și apoi, vom căuta și situația în care este o dată din 2 cifre, deci 20 ianuarie. Deci, asta se numește Ajustare3, întoarceți-vă cu 3 caractere din locul unde. Deci, există Unde, întoarceți trei caractere înapoi pentru lungimea de 1, adăugați 0 la el și vedeți dacă asta 'un număr, bine? Apoi, ne vom ajusta și Unde ajustat spune IF. DACĂ este ciudat acest caz a fost 0, vom pune doar o valoare foarte mare 999; în caz contrar, vom merge de la G2 și vom merge înapoi 3, dacă Ajustare3 este Adevărat sau înapoi 2 dacă Ajustare2 este Adevărat, sau dacă niciuna dintre acestea nu este Adevărată, Unde va fi locul în care începe luna. În regulă, acum, când știm că acel Unde ajustat, vom face dublu clic pentru a-l copia. Ei bine, hei acum, este foarte ușor. Vom pur și simplu - pentru Titlu, vom spune că luăm stânga A2, câte caractere dorim. Vrem D2-1 pentru că acesta este -1 este să scapi de spațiu la final. Deși cred că și TRIM scapă de spațiu la final.DACĂ este ciudat acest caz a fost 0, vom pune doar o valoare foarte mare 999; în caz contrar, vom merge de la G2 și vom merge înapoi 3, dacă Ajustare3 este Adevărat sau înapoi 2 dacă Ajustare2 este Adevărat, sau dacă niciuna dintre acestea nu este Adevărată, Unde va fi locul în care începe luna. În regulă, acum, când știm că acel Unde ajustat, vom face dublu clic pentru a-l copia. Ei bine, hei acum, este foarte ușor. Vom pur și simplu - pentru titlu, vom spune că luăm stânga A2, câte caractere dorim. Vrem D2-1 pentru că acesta este -1 este să scapi de spațiu la final. Deși cred că și TRIM scapă de spațiu la final.DACĂ este ciudat acest caz a fost 0, vom pune doar o valoare foarte mare 999; în caz contrar, vom merge de la G2 și vom merge înapoi 3, dacă Ajustare3 este Adevărat sau înapoi 2 dacă Ajustare2 este Adevărat, sau dacă niciunul dintre acestea nu este Adevărat, Unde va fi de unde începe luna. În regulă, acum, când știm că acel Unde ajustat, vom face dublu clic pentru a-l copia. Ei bine, hei acum, este foarte ușor. Vom pur și simplu - pentru Titlu, vom spune că luăm stânga A2, câte caractere dorim. Vrem D2-1 pentru că acesta este -1 este să scapi de spațiu la final. Deși cred că și TRIM scapă de spațiu la final.sau dacă niciuna dintre acestea nu este adevărată, unde va fi locul unde începe luna. În regulă, acum, când știm că acel Unde ajustat, vom face dublu clic pentru a-l copia. Ei bine, hei acum, este foarte ușor. Vom pur și simplu - pentru Titlu, vom spune că luăm stânga A2, câte caractere dorim. Vrem D2-1 pentru că acesta este -1 este să scapi de spațiu la final. Deși cred că și TRIM scapă de spațiu la final.sau dacă niciuna dintre acestea nu este adevărată, unde va fi locul unde începe luna. În regulă, acum, când știm că acel Unde Ajustat, vom face dublu clic pentru a-l copia. Ei bine, hei acum, este foarte ușor. Vom pur și simplu - pentru Titlu, vom spune că luăm stânga A2, câte caractere dorim. Vrem D2-1 pentru că acesta este -1 este să scapi de spațiu la final. Deși cred că și TRIM scapă de spațiu la final.s -1 este să scapi de spațiu la final. Deși cred că și TRIM scapă de spațiu la final.s -1 este să scapi de spațiu la final. Deși cred că și TRIM scapă de spațiu la final.
Și apoi pentru dată, vom folosi MID. MID for- MID de A2 începând de la Unde ajustat în D2 și ieșiți 50 sau orice credeți că ar putea fi, apoi funcția TRIM și vom face dublu clic pentru a copia asta.
Bine acum, motivul pentru care am contactat-o pe Mike este că am spus, mă întreb dacă există o modalitate prin care aș putea înlocui aceste 12 coloane cu o singură formă, de fapt aceste 13 coloane cu o singură formă. Există vreo modalitate prin care aș putea face acest lucru folosind o formulă Array? Și Mike, desigur, a scris acea carte grozavă, Ctrl + Shift + Enter, pe formulele Array. Și am încercat câteva lucruri diferite și, în mintea mea, nu exista nicio modalitate prin care să se poată face. În regulă, dar știi, hai să întrebăm expertul. Deci Mike, hai să vedem ce ai.
Mike Girvin: Mulțumesc. Hei și vorbind de expert, acest lucru a fost făcut destul de expert. Ai folosit FIND, AGGREGATE, ISNUMBER (MID. Acum, când mi-ai trimis această întrebare, am continuat și am rezolvat-o și este uimitor cât de asemănătoare este soluția mea cu a ta.
Bine, voi trece la această foaie aici. Voi începe cu a afla unde este poziția de pornire în acest șir de text pentru fiecare lună specială. Acum, așa cum o voi face, voi folosi, hei, această funcție de CĂUTARE. Acum, ai folosit FIND, eu folosesc SEARCH. De fapt, probabil că FIND este mai bun în această situație, deoarece FIND este sensibil la majuscule, CĂUTAREA nu. Acum, în mod normal, ceea ce facem fie cu GĂSIRE, fie cu CĂUTARE, zic, hei, du-te GĂSIT, ianuarie, virgulă în acest șir de text mai mare, așa folosim în mod normal SEARCH Ctrl + Enter și contează pe degetul său: unu, doi, trei , patru cinci. Se spune că cel de-al 32-lea personaj este locul în care a găsit ianuarie.
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.
Așadar, vreau să mulțumesc tuturor că au trecut pe aici. Ne vedem data viitoare pentru un alt Dueling Excel Podcast de la și Excel Is Fun.
Descărcare fișier
Descărcați exemplarul de fișier aici: Duel180.xlsm
