Alcune delle domande che arrivano sono piuttosto difficili. Oggi abbiamo una colonna di celle. Ogni cella ha alcune parole, quindi una data, quindi altre parole. L'obiettivo è trascinare la parte della data di quel testo in una nuova colonna. Questo è un episodio di duello con idee di Bill e Mike.
Guarda un video
- L'approccio super ampio di Bill:
- Metti tutti i 12 mesi in colonne separate
- Usa la funzione TROVA per vedere se questo mese è nel testo originale
- Per trovare la posizione iniziale minima, utilizzare = AGGREGATO (5,6, …
- Alcune formule extra per cercare un numero 2 o 3 posizioni prima del mese
- L'approccio di Mike:
- Usa CERCA invece di TROVA. Trova distingue tra maiuscole e minuscole, Cerca no.
- Creare un'operazione su array di argomenti di funzione specificando B13: B24 come Find_Text.
- La formula restituisce #VALORE! Errore, ma se premi F2, F9, vedrai che sta restituendo un array.
- Le prime 13 funzioni in AGGREGATE non possono gestire un array, ma le funzioni 14-19 possono gestire un array.
- 5 = MIN e 15 = SMALL (, 1) sono simili, ma SMALL (, 1) funzionerà con un array.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX e AGGREGATE possono gestire gli argomenti dell'array di funzioni senza Ctrl + Maiusc + Invio
- Mike è stato più intelligente cercando di vedere se 2 caratteri prima dell'inizio è un numero, e poi afferrando 3 caratteri prima. Lo spazio extra viene eliminato dal TRIM ()
- Per ottenere il titolo, utilizzare la funzione SOSTITUISCI per eliminare il testo della data nella colonna C.
Trascrizione del video
Bill Jelen: Ehi, bentornato. È il momento per un altro Podcast Dueling Excel. Sono Bill Jelen di. Sarò affiancato da Mike Girvin di Excel Is Fun.
Questo è il nostro duello n. 170: Trovare le date
Ehi, bentornati a tutti. Avevo una domanda così grande qui e non sono riuscito a risolverla. Almeno non sono riuscito a risolverlo facilmente, quindi sono andato da Mike Girvin e ho detto: "Mike, ehi, hai un modo per farlo?" Ha detto: “Sì, ho un modo per farlo. Facciamo un duello. "
Quindi, qualcuno su YouTube ha inviato questi dati e ogni singola cella in generale ha qualcosa di simile al titolo di un documento seguito da una data. Volevano suddividere questi dati nel titolo del documento: cos'è, cos'è e poi qual è la data. Ma le date sono completamente cattive. Come qui, è il 20 gennaio; ma quaggiù, ci sono cose in cui la data potrebbe essere dopo la cella, il 9 aprile. Va bene, e non importa da che parte sia, vogliamo trovarlo. E a volte ci sono due date e questo è completamente orribile e che è solo una situazione confusa di date e il più possibile, non hanno nemmeno una data che si presenti, va bene. Quindi, ecco il mio tentativo. Sul lato destro, metterò le cose che cerco. Quello che mi piace davvero qui è che non hanno mai abbreviato il nome del mese. Io davverolo apprezzo davvero. Quindi digita gennaio e trascinerò qui a dicembre in questo modo, e per ogni cella che voglio sapere possiamo trovare = TROVA quel gennaio. Quindi premerò F4 una, due volte per bloccarlo solo su una riga, nel testo laggiù nella colonna A, in questo modo. Premerò F4 una, due, tre volte per bloccarlo nella colonna, va bene. E qui, ci sta dicendo che gennaio si trova nella posizione 32 e per gli altri 11 mesi, ci dirà che non è stato trovato affatto. In altre parole, ora stiamo ottenendo l'errore Value. Quello che devo fare è che devo trovare, ho bisogno di trovare il valore minimo ignorando tutti gli errori di valore. Quindi, mostra questa piccola formula qui = AGGREGATE e costruiamola da zero, = AGGREGATE, quello che vogliamo è il MIN, quindi è il numero 5,e quindi Ignora i valori di errore numero 6 virgola e quindi tutte queste celle da gennaio a dicembre. E quello che ci dirà è che ci dirà dove si svolgerà il mese. E in questo caso, otterremo 0, diciamo che il mese non accade affatto.
Va bene ora, scopriamo il resto di questo. Quindi, per gestire la situazione in cui qui abbiamo il 20 gennaio o il 1 novembre, ho detto che la prima cosa che ho intenzione di fare è guardare dove inizia quel mese e tornare indietro di due celle, due celle, due caratteri , due personaggi. E vedi se è un numero, non è così. Questa è la mia colonna qui chiamata, Adjust2. Adjust2. Ed ecco cosa faremo. Sto per dire, prendi il MID di A2 iniziando da dove in G2-2 per una lunghezza di 1, aggiungi 0 e chiedi, è un numero o no? Va bene, anche questo è un numero. E poi, cercheremo anche la situazione in cui è una data a 2 cifre, quindi il 20 gennaio. Quindi si chiama Adjust3, torna indietro di 3 caratteri da dove. Quindi c'è il Dove, torna indietro di tre caratteri per una lunghezza di 1, aggiungi 0 e vedi se "un numero, va bene? Quindi andremo ad aggiustare e il Dove aggiustato dice IF. Se questo caso strano era 0, metteremo solo un valore molto grande 999; altrimenti, andremo da G2 e torneremo 3, se Adjust3 è True, o torneremo 2 se Adjust2 è True, o se nessuno di questi è True, il Where sarà dove inizia il mese. Va bene, ora che sappiamo che quella posizione corretta, faremo doppio clic per copiarla. Bene, ehi ora, è davvero facile. Stiamo solo per - per il titolo, diremo di prendere a sinistra di A2, quanti caratteri vogliamo. Vogliamo D2-1 perché è -1 per sbarazzarci dello spazio alla fine. Anche se immagino che anche il TRIM stia eliminando lo spazio alla fine.Se questo caso strano era 0, metteremo solo un valore molto grande 999; altrimenti, andremo da G2 e torneremo indietro 3, se Adjust3 è True o torneremo indietro 2 se Adjust2 è True, o se nessuno di questi è True, il Where sarà dove inizia il mese. Va bene, ora che sappiamo che quella posizione corretta, faremo doppio clic per copiarla. Bene, ehi ora, è davvero facile. Stiamo solo per - per il titolo, diremo di prendere a sinistra di A2, quanti caratteri vogliamo. Vogliamo D2-1 perché è -1 per sbarazzarsi dello spazio alla fine. Anche se immagino che anche il TRIM si stia liberando dello spazio alla fine.Se questo caso strano era 0, metteremo solo un valore molto grande 999; altrimenti, andremo da G2 e torneremo 3, se Adjust3 è True, o torneremo 2 se Adjust2 è True, o se nessuno di questi è True, il Where sarà dove inizia il mese. Va bene, ora che sappiamo che quella posizione corretta, faremo doppio clic per copiarla. Bene, ehi ora, è davvero facile. Stiamo solo per - per il titolo, diremo di prendere a sinistra di A2, quanti caratteri vogliamo. Vogliamo D2-1 perché è -1 per sbarazzarci dello spazio alla fine. Anche se immagino che anche il TRIM si stia liberando dello spazio alla fine.o se nessuno di questi è vero, dove sarà dove inizia il mese. Va bene, ora che sappiamo che quella posizione corretta, faremo doppio clic per copiarla. Bene, ehi ora, è davvero facile. Stiamo solo per - per il titolo, diremo di prendere a sinistra di A2, quanti caratteri vogliamo. Vogliamo D2-1 perché è -1 per sbarazzarci dello spazio alla fine. Anche se immagino che anche il TRIM stia eliminando lo spazio alla fine.o se nessuno di questi è vero, il Dove sarà dove inizia il mese. Va bene, ora che sappiamo che quella posizione corretta, faremo doppio clic per copiarla. Bene, ehi ora, è davvero facile. Stiamo solo per - per il titolo, diremo di prendere a sinistra di A2, quanti caratteri vogliamo. Vogliamo D2-1 perché è -1 per sbarazzarsi dello spazio alla fine. Anche se immagino che anche il TRIM stia eliminando lo spazio alla fine.s il -1 serve per sbarazzarsi dello spazio alla fine. Anche se immagino che anche il TRIM stia eliminando lo spazio alla fine.s il -1 serve per sbarazzarsi dello spazio alla fine. Anche se immagino che anche il TRIM stia eliminando lo spazio alla fine.
E poi per la data, useremo il MID. MID for- MID di A2 a partire da Adjusted Where in D2 e uscire da 50 o qualsiasi altra cosa pensi che possa essere, quindi la funzione TRIM, e faremo doppio clic per copiarla.
Va bene ora, il motivo per cui ho contattato Mike è che ho detto, mi chiedo se ci sia un modo per sostituire queste 12 colonne con una singola forma, in realtà queste 13 colonne con una singola forma. C'è un modo per farlo usando una formula di matrice? E Mike, ovviamente, ha scritto quel fantastico libro, Ctrl + Maiusc + Invio, sulle formule di matrice. E ho provato diverse cose e nella mia mente, non c'era modo che potesse essere fatto. Va bene, ma sai, andiamo a chiedere all'esperto. Allora Mike, vediamo cosa hai.
Mike Girvin: Grazie ,. Ehi, e parlando di esperto, questo è stato fatto in modo abbastanza esperto. Hai usato FIND, AGGREGATE, ISNUMBER (MID. Ora, quando mi hai inviato questa domanda, sono andato avanti e l'ho risolta ed è incredibile quanto la mia soluzione sia simile alla tua.
Va bene, vado a questo foglio qui. Inizierò cercando di capire dove si trova la posizione iniziale in questa stringa di testo per ogni mese particolare. Ora il modo in cui lo farò è, ehi, utilizzare questa funzione di RICERCA. Ora, hai usato TROVA, io uso RICERCA. In realtà probabilmente TROVA è migliore in questa situazione perché TROVA fa distinzione tra maiuscole e minuscole, RICERCA no. Ora normalmente quello che facciamo con TROVA o RICERCA, dico, ehi, vai TROVA, gennaio, virgola all'interno di questa stringa di testo più grande, è così che normalmente usiamo CERCA Ctrl + Invio, e conta sul suo dito: uno, due, tre , quattro cinque. Dice che il 32esimo personaggio è dove ha trovato gennaio.
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.
Quindi, voglio ringraziare tutti per essere passati. Ci vediamo la prossima volta per un altro Podcast di Dueling Excel da ed Excel Is Fun.
Download file
Scarica il file di esempio qui: Duel180.xlsm
