Algunas de las preguntas que surgen son bastante difíciles. Hoy tenemos una columna de celdas. Cada celda tiene algunas palabras, luego una fecha, luego algunas palabras más. El objetivo es llevar la parte de la fecha de ese texto a una nueva columna. Este es un episodio de duelo con ideas de Bill y Mike.
Ver video
- El enfoque súper amplio de Bill:
- Ponga los 12 meses en columnas separadas
- Utilice la función BUSCAR para ver si este mes está en el texto original
- Para encontrar la posición inicial mínima, use = AGREGAR (5,6,…
- Algunas fórmulas adicionales para buscar un número 2 o 3 posiciones antes del mes
- El enfoque de Mike:
- Utilice BUSCAR en lugar de ENCONTRAR. Buscar distingue entre mayúsculas y minúsculas, pero la búsqueda no.
- Cree una operación de matriz de argumentos de función especificando B13: B24 como Find_Text.
- La fórmula devuelve # ¡VALOR! Error, pero si presiona F2, F9, verá que está devolviendo una matriz.
- Las primeras 13 funciones en AGGREGATE no pueden manejar una matriz, pero las funciones 14-19 pueden manejar una matriz.
- 5 = MIN y 15 = SMALL (, 1) son similares, pero SMALL (, 1) funcionará con una matriz.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX y AGGREGATE pueden manejar argumentos de matriz de funciones sin Ctrl + Shift + Enter
- Mike fue más inteligente al ver si 2 caracteres antes del Inicio es un número, y luego tomó 3 caracteres antes. El espacio extra es eliminado por el TRIM ()
- Para obtener el título, use la función SUSTITUIR para deshacerse del texto de la fecha en la columna C
Transcripción del video
Bill Jelen: Oye, bienvenido. Es hora de otro Podcast de Dueling Excel. Soy Bill Jelen de. Me acompañará Mike Girvin de Excel Is Fun.
Este es nuestro Duelo # 170: Encontrar fechas
Oye, bienvenidos a todos. Tenía una gran pregunta aquí y no pude resolverla. Al menos no pude resolverlo fácilmente, así que fui con Mike Girvin y le dije: "Mike, oye, ¿tienes alguna forma de hacer esto?" Dijo: “Sí, tengo una manera de hacerlo. Hagamos un Duelo ".
Entonces, alguien en YouTube envió estos datos y cada celda en general tiene algo así como un título de documento seguido de una fecha. Querían dividir estos datos en el título del documento: qué es, qué es y luego cuál es la fecha. Pero las fechas son completamente malas. Como aquí, es el 20 de enero; pero aquí abajo, hay cosas en las que la fecha podría ser posterior a la celda, el 9 de abril. Muy bien, y no importa de qué manera sea, queremos encontrarlo. Y a veces hay dos citas y esto es completamente horrible y es una situación tan confusa de fechas y como es posible, ni siquiera aparece una cita, está bien. Entonces, aquí está mi intento. En el lado derecho, voy a poner las cosas que estoy buscando. Lo que realmente me gusta aquí es que nunca abreviaron el nombre del mes. Yo realmente,Realmente aprecio eso. Así que escriba enero y lo arrastraré hasta diciembre así, y para cada celda que quiero saber, ¿podemos encontrar = ENCONTRAR ese enero? Así que voy a presionar F4 una, dos veces para bloquearlo en solo una fila, en el texto de la Columna A, así. Presionaré F4 una, dos, tres veces para bloquearlo en la columna, está bien. Y aquí, nos dice que enero se encuentra en la posición 32 y durante los otros 11 meses, nos dice que no se encuentra en absoluto. En otras palabras, ahora estamos recibiendo el error de valor. Lo que necesito hacer allí es encontrar, necesito encontrar el valor mínimo ignorando todos los errores de valor. Entonces, muestre esta pequeña fórmula aquí = AGREGAR y construyamos esto desde cero, = AGREGAR, lo que queremos es el MIN, así que ese es el número 5,y luego ignorar los valores de error número 6 coma y luego todas estas celdas de enero a diciembre. Y lo que eso nos dirá es que nos dirá dónde ocurre el mes. Y en este caso, obtendremos 0, digamos que el mes no sucede en absoluto.
Muy bien ahora, demostremos el resto de esto. Entonces, para manejar la situación en la que aquí tenemos el 20 de enero o el 1 de noviembre, dije que lo primero que voy a hacer es mirar dónde comienza ese mes y regresar dos celdas, dos celdas, dos caracteres. , dos personajes. Y mira si eso es un número, no es así. Esa es mi columna aquí llamada Adjust2. Ajustar 2. Y esto es lo que vamos a hacer. Voy a decir, tome el MID de A2 comenzando donde en G2-2 para una longitud de 1, agregue 0 y pregunte, ¿es un número o no? Muy bien, eso es un número. Y luego, también buscaremos la situación en la que es una fecha de 2 dígitos, es decir, 20 de enero. Entonces eso se llama Ajustar3, retroceda 3 caracteres desde donde. Entonces está el Dónde, retroceda tres caracteres para una longitud de 1, agregue 0 y vea si eso 'un número, ¿de acuerdo? Luego vamos a ajustar y el Dónde ajustado dice SI. SI este extraño caso fue 0, solo vamos a poner un valor realmente grande 999; de lo contrario, vamos a ir de G2 y retroceder 3, si Adjust3 es Verdadero o retroceder 2 si Adjust2 es Verdadero, o si ninguno de ellos es Verdadero, dónde va a estar donde comienza el mes. Muy bien, ahora que sabemos que Ajustó dónde, haremos doble clic para copiarlo. Bueno, oye, es realmente fácil. Solo vamos a - para el Título, vamos a decir que tome la izquierda de A2, cuántos caracteres queremos. Queremos D2-1 porque ese es el -1 es deshacerse del espacio al final. Aunque supongo que el TRIM también se está deshaciendo del espacio al final.SI este extraño caso fue 0, solo vamos a poner un valor realmente grande 999; de lo contrario, vamos a ir de G2 y retroceder 3, si Adjust3 es Verdadero o retroceder 2 si Adjust2 es Verdadero, o si ninguno de ellos es Verdadero, dónde va a estar donde comienza el mes. Muy bien, ahora que sabemos que Ajustó dónde, haremos doble clic para copiarlo. Bueno, oye, es realmente fácil. Solo vamos a - para el Título, vamos a decir que tome la izquierda de A2, cuántos caracteres queremos. Queremos D2-1 porque ese es el -1 es deshacerse del espacio al final. Aunque supongo que el TRIM también se está deshaciendo del espacio al final.SI este extraño caso fue 0, solo vamos a poner un valor realmente grande 999; de lo contrario, vamos a ir de G2 y retroceder 3, si Adjust3 es Verdadero o retroceder 2 si Adjust2 es Verdadero, o si ninguno de ellos es Verdadero, dónde va a estar donde comienza el mes. Muy bien, ahora que sabemos que Ajustó dónde, haremos doble clic para copiarlo. Bueno, oye, es realmente fácil. Solo vamos a - para el Título, vamos a decir que tome la izquierda de A2, cuántos caracteres queremos. Queremos D2-1 porque ese es el -1 es deshacerse del espacio al final. Aunque supongo que el TRIM también se está deshaciendo del espacio al final.o si ninguno de ellos es Verdadero, el Dónde estará donde comienza el mes. Muy bien, ahora que sabemos que Ajustó dónde, haremos doble clic para copiarlo. Bueno, oye, es realmente fácil. Solo vamos a - para el Título, vamos a decir que tome la izquierda de A2, cuántos caracteres queremos. Queremos D2-1 porque ese es el -1 es deshacerse del espacio al final. Aunque supongo que el TRIM también se está deshaciendo del espacio al final.o si ninguno de ellos es Verdadero, el Dónde estará donde comienza el mes. Muy bien, ahora que sabemos que Ajustó dónde, haremos doble clic para copiarlo. Bueno, oye, es realmente fácil. Solo vamos a - para el Título, vamos a decir que tome la izquierda de A2, cuántos caracteres queremos. Queremos D2-1 porque ese es el -1 es deshacerse del espacio al final. Aunque supongo que el TRIM también se está deshaciendo del espacio al final.s el -1 es deshacerse del espacio al final. Aunque supongo que el TRIM también se está deshaciendo del espacio al final.s el -1 es deshacerse del espacio al final. Aunque supongo que el TRIM también se está deshaciendo del espacio al final.
Y luego, para la fecha, usaremos el MID. MID para- MID de A2 comenzando en el Dónde ajustado en D2 y salga 50 o lo que sea que crea que podría ser, y luego la función TRIM, y haremos doble clic para copiar eso.
Muy bien, la razón por la que me comuniqué con Mike es que dije, me pregunto si hay alguna manera de reemplazar estas 12 columnas con un solo formulario, en realidad estas 13 columnas con un solo formulario. ¿Hay alguna forma de que pueda hacer esto usando una fórmula de matriz? Y Mike, por supuesto, escribió ese gran libro, Ctrl + Shift + Enter, sobre fórmulas de matriz. Y probé algunas cosas diferentes y en mi mente, no había forma de que se pudiera hacer. Muy bien, pero ya sabes, vamos a preguntarle al experto. Mike, veamos qué tienes.
Mike Girvin: Gracias. Oye, y hablando de expertos, esto fue hecho de manera bastante experta. Usaste FIND, AGGREGATE, ISNUMBER (MID. Ahora, cuando me enviaste esta pregunta, seguí adelante y la resolví y es increíble lo similar que es mi solución a la tuya.
Muy bien, voy a pasar a esta hoja de aquí. Empezaré por averiguar dónde está la posición inicial en esta cadena de texto para cada mes en particular. Ahora, la forma en que lo voy a hacer es, oye, usar esta función de BÚSQUEDA. Ahora, usó FIND, yo uso SEARCH. En realidad, probablemente FIND sea mejor en esta situación porque FIND distingue entre mayúsculas y minúsculas, SEARCH no. Ahora, normalmente, lo que hacemos con ENCONTRAR o BÚSQUEDA, digo, oye, ve a ENCONTRAR, enero, coma dentro de esta cadena de texto más grande, así es como normalmente usamos BUSCAR Ctrl + Intro, y cuenta con su dedo: uno, dos, tres , cuatro cinco. Dice que el 32º personaje es donde se encuentra enero.
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.
Entonces, quiero agradecer a todos por pasar. Nos vemos la próxima vez para otro Dueling Excel Podcast de y Excel Is Fun.
Descargar archivo
Descarga el archivo de muestra aquí: Duel180.xlsm
