miércoles, 24 de diciembre de 2014

Detectar Códigos Alfanuméricos Pares

"Tengo más de 500 entradas en una columna de un código compuesto de letras y, al final, 4 dígitos. Necesito localizar cuáles de esos códigos son pares y que me escriba en una columna anexa dichos dígitos (sólo los que son pares)".

Antes de meterme en materia me permitiréis que siendo hoy el día que es os felicite a todos, primero por tener la paciencia de leerme de vez en cuando y, segundo y sobre todo, porque hoy sea un día que podáis disfrutar en familia y no dejéis que os lo estropee nadie (ni siquiera los políticos con o sin coleta...)

Partimos del siguiente ejemplo: (ups! Se me ha "colao" un señor de barba blanca en el ejemplo y haciendo publicidad para mi hermano Santi...)

Nos situamos en D3 y escribimos la siguiente fórmula que copiamos hasta D15 y que paso a desmenuzar a continuación:

=SI(N(ES.PAR(VALOR(DERECHA(B3;1))))=0;"";VALOR(DERECHA(B3;4)))

DERECHA(B3;1)   esta parte de la fórmula extrae 1 dígito empezando por la derecha del texto existente en B3. Aunque se trata "visualmente" de un número, excel lo trata como texto por formar parte precisamente de una cadena de texto. Para convertirlo en número utilizamos la función VALOR, a saber: VALOR(DERECHA(B3;1)).

Una vez hecho esto, procedemos a comprobar si el dígito que acabamos de extraer es par o no. Para ello utilizamos la función ES.PAR, ES.PAR(VALOR(DERECHA(B3;1)))  que nos devolverá el resultado VERDADERO o FALSO. Para convertir este VERDADERO ó FALSO en 1 ó 0 utilizamos la función N (también podríamos poner dos signos negativos consecutivos -- en vez de dicha función)  N(ES.PAR(VALOR(DERECHA(B3;1)))).

Ya sólo nos queda anidar esta fórmula dentro de un condicional para que si el último dígito no es un número par (y por lo tanto la fórmula N(ES.PAR(VALOR(DERECHA(B3;1)))) será igual a 0) no escriba nada o, en caso contrario, que escriba los 4 dígitos del código como valor:  VALOR(DERECHA(B3;4)).

El resultado final es el que se muestra a continuación:
Feliz Navidad a todos y recordad: Para ser feliz hay que venir a pasar la Navidad al Balneario de Mondariz!!

miércoles, 3 de diciembre de 2014

Buscar la última Entrada de un Concepto Repetido

"Tengo una tabla con nombres de vendedores y, en la siguiente columna, las unidades vendidas en cada pedido. Necesito buscar la última entrada realizada de un vendedor concreto pero sólo consigo obtener la primera entrada (con la función BUSCARV)".

Para resolver este problema vamos a trabajar con varias funciones, a saber: INDICE, COINCIDIR, CONTAR.SI y CONCATENAR (&). Partimos del siguiente ejemplo:
Lo que queremos conseguir es que al introducir en C2 el nombre del vendedor excel nos devuelva el último valor existente de dicho comercial. Evidentemente, si utilizamos la función BUSCARV nos va a devolver el primer valor que se encuentre en la tabla del vendedor que le indiquemos. Aunque también se podría resolver con esta función, vamos a solucionarlo de otra manera que se me antoja "más elegante". Lo primero que hacemos es dar nombre a las dos columnas de datos. Seleccionamos el rango B6:C22 y en la ficha Fórmulas/Nombres Definidos pulsamos Crear desde la selección. En la ventana que se abre elegimos crear nombres a partir de los valores de la Fila superior. De esta manera, el rango B6:B22 pasa a denominarse Vendedor y el C6:C22 Unidades.

Ahora generamos una columna auxiliar para generar un número de orden de los distintos vendedores. Nos situamos, por ejemplo, en la celda F7 y escribimos la fórmula:

=B7&CONTAR.SI($B$7:B7;B7)  y la copiamos hasta F22.

La parte de CONTAR.SI($B$7:B7;B7) lo que hace es ir generando un contador para cada vendedor. Utilizando como ejemplo el primero, Pedro Flores, cada vez que aparezca en el rango de vendedores le irá sumando una unidad. Al primer Pedro Flores le asigna el 1 al segundo un 2 y así sucesivamente. Y esto para cada vendedor. Lo que hacemos con la parte de la fórmula B7& es preceder a este número de orden del nombre del vendedor y los concatenamos. De esta manera obtendremos Pedro Flores1, Pedro Flores2, Pedro Flores3, Joaquín Voz1, etcétera. Es decir, el nombre unido (concatenado) al número de orden:

Este paso me proporciona un nombre unido al número máximo de repeticiones de dicho nombre. Es decir, si Pedro Flores aparece, como es el caso, 4 veces entonces sé que el último valor de Pedro Flores será el asociado a Pedro Flores4.
Sólo nos queda una fórmula más para obtener nuestro objetivo. Nos situamos en C4 y escribimos:

=INDICE(unidades;COINCIDIR(C2&CONTAR.SI(vendedor;C2);F7:F22;0))

Veamos por partes esta fórmula:

C2&CONTAR.SI(vendedor;C2)  Une el nombre introducido en la celda de entrada C2 al número máximo de repeticiones del mismo dentro de la columna de Vendedor. En nuestro ejemplo el resultado será Pedro Flores (dato de C2) y el número 4, es decir, Pedro Flores4.

COINCIDIR(C2&CONTAR.SI(vendedor;C2);F7:F22;0)   Ahora buscamos este resultado (Pedro Flores4) dentro del rango F7:F22  con la función COINCIDIR. Con esta función lo que obtendremos es el número de fila en el que se encuentra dicho dato. En nuestro ejemplo el resultado de este "trozo" de fórmula será 16. Sabiendo el número de fila en el que se encuentra, ya sólo me queda incorporar este resultado a la función INDICE:
=INDICE(unidades;COINCIDIR(C2&CONTAR.SI(vendedor;C2);F7:F22;0))  para que busque dentro de la columna Unidades la fila 16 y me devuelva el valor: