Máximo de un Alfanumérico

"Tengo un listado en el que llevo el seguimiento de varias ordenes. Todas ellas están compuestas por un código único alfanumérico de 7 caracteres. Los tres primeros son siempre el texto GIO y los otros cuatro son números. Necesito hallar el código más alto en función de su número".

Partimos del siguiente ejemplo:

Al tratarse de entradas alfanuméricas (texto y números) excel las considera texto y, en consecuencia, no podemos utilizar directamente la función MAX. Podemos resolver el problema de diferentes maneras. Una muy sencilla es "trocear" las entradas para separar la parte de texto de la de número. Para ello generamos una columna de proceso:

En la celda D6 escribimos la fórmula:

=VALOR(DERECHA(H6;4))   y la copiamos hasta D23.

De esta manera estamos obteniendo los 4 dígitos con la función DERECHA, y convirtiendo dichos dígitos, que hasta aquí excel trata como texto, a números con la función VALOR: 

Nos situamos ahora en la celda B3 y escribimos la siguiente fórmula:

Lo que estamos haciendo es CONCATENAR el texto "GIO", con el que comienzan todos los códigos del listado, con el valor MÁXIMO  de los números:

Podemos concluir aplicando Formato Condicional al rango B6:B23 para que destaque el máximo valor, como se muestra en la imagen.

Dígitos Duplicados en Distinto Orden

"Tengo un listado de valores de tres dígitos y necesito detectar cuáles están duplicados en el mismo o distinto orden. Por ejemplo 123, 231, 456, 456, 564, etcétera". 

Partimos del siguiente ejemplo:

Preparamos ahora las siguientes tablas para "manipular" los datos iniciales. Aunque el enunciado habla de 3 dígitos, preparamos tablas para contemplar hasta 6 dígitos:

Nos situamos en E3 y escribimos la fórmula(que copiaremos hasta J3 y, finalmente, hasta J17):

=SI.ERROR(VALOR(EXTRAE($B3;E$2;1));"")

De esta manera estamos extrayendo cada dígito y reconvirtiéndolo en VALOR (ya que la función EXTRAE nos lo devuelve como texto):

 Una vez hecho esto, nos situamos en L3 y escribimos la fórmula:

=SI.ERROR(K.ESIMO.MENOR($E3:$J3;L$2);"")

y copiamos hasta Q2 y finalmente hasta Q17:

De esta manera hemos reordenado de menor a mayor los dígitos. Procedemos ahora a unirlos de nuevo con la función CONCATENAR (&). En S3 escribimos:

y copiamos hasta S17:

Ahora ya podemos proceder a contar los números que se repiten en más de una ocasión y "asociarlos" con sus "originales". Nos situamos en la celda C3 y escribimos la fórmula:

=SI(CONTAR.SI($S$3:$S$17;S3)>1;"Repetido";"")    y copiamos hasta C17:

Ordenar Texto con Fórmulas

"Tengo grandes listados donde me aparecen, en distintas columnas, Nombre; Apellido 1; Apellido 2. Quisiera que primero me presente la información con el formato Apellido1 Apellido 2, Nombre y, finalmente, que me los ordene de la A a la Z automáticamente".

Como siempre, partimos de un ejemplo:

Lo primero que vamos a hacer es CONCATENAR los apellidos y nombre como nos interesa. Nos situamos en la celda H3 y escribimos:

 y copiamos hasta la celda H16. De esta manera estamos concatenando primero el primer apellido; después un espacio en blanco; inmediatamente el segundo apellido; ahora una coma y un espacio en blanco y, finalmente, el nombre. De esta manera, en la celda H3, por ejemplo, aparecerá Arnaiz Guerra, Begoña:

Como no existe una función en excel que ordene directamente texto, vamos a utilizar una fórmula matricial para asignarle un número de orden a cada registro. Primero le doy nombre al rango H3:H16. Selecciono dicho rango y en el cuadro de nombres escribo ApNombre, y pulsamos enter para acabar. Vamos a G3 y escribimos:

 Pulsamos Ctrl+Shift+Enter y se mostrará como fórmula matricial:

Terminamos copiando esta fórmula hasta G16.

Excel es capaz de comparar textos por orden alfabético, de tal manera que si comprobamos si un texto es mayor, menor o igual que otro texto, excel nos devolverá un VERDADERO o FALSO. El menor valor es la A y el mayor la Z. Ejemplos:

El resultado de aplicar la fórmula matricial será: 

Ahora ya disponemos de un número de orden para cada registro. Procedemos ahora a preparar el cuadro para la salida final de información:

Seleccionamos el rango G3:H16 y le damos el nombre OrdenNombres. Vamos a K3 y escribimos la fórmula (que luego copiaremos hasta K16):

 

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:

Parejas Aleatorias sin Repetición

"Tengo dos grupos de 10 personas y quiero hacer 10 parejas aletorias pero sin que se repita ninguna persona (Ejemplo: pareja 1: el 1 con el 12; pareja 2: el 7 con el 19, etcétera. No valdría el 1 con el 5; el 1 con el 7; etcétera)".

Vamos allá. Lo solucionaremos con dos funciones, a saber: ALEATORIO y JERARQUIA. Empezamos generando una tabla de 20 valores aleatorios en dos columnas de 10 cada una:

Seleccionamos el rango H3:I12 y, con el rango seleccionado, escribimos la fórmula =ALEATORIO()  y terminamos pulsando Ctrl + Enter. De esta manera rellenamos todo el rango de una sola vez:

Seguidamente, preparamos la tabla de las distintas parejas como, por ejemplo, se muestra a continuación:

 Nos situamos en la celda C3 y escribimos la fórmula:

=JERARQUIA(H3;$H$3:$H$12)  y copiamos hasta la celda C12. De esta manera hemos obtenido un número de manera aleatoria y sin repetición entre el 1 y 10.

En la celda D3 escribimos la fórmula:

=JERARQUIA(I3;$I$3:$I$12)+10  y copiamos hasta la celda D12. Hemos hecho lo mismo que en el caso anterior pero al sumarle 10 en la fórmula estamos obteniendo ahora un número de manera aleatoria entre el 11 y el 20.

Y problema resuelto. Cada vez que pulsemos F9 estaremos generando una nueva combinación. Como sugerencia se podría utilizar la función CONCATENAR (&) para presentar el resultado unido y con texto. La fórmula en F3 sería:

=JERARQUIA(H3;$H$3:$H$12)&" con "&JERARQUIA(I3;$I$3:$I$12)+10

Promedio Móvil de x Meses

"Tengo un histórico con la cifra de ventas mensual y me gustaría poder calcular el promedio de ventas de los últimos x meses hasta la fecha de hoy".

No problemo. Lo resolveremos haciendo uso de las funciones SI.ERROR, PROMEDIO, DESREF, COINCIDIR, BUSCARV y HOY. Supongamos que tenemos la siguiente tabla con la fecha y su cifra de ventas. Preparamos además la entrada de datos, es decir, el número variable de meses para calcular el promedio:

Empezamos calculando la fecha actual. Para ello nos ponemos en la celda C4 y escribimos la fórmula  =HOY()

Seleccionamos el rango E3:E32 y le damos el nombre Datomes (como siempre lo podemos crear haciendo clic en el Cuadro de nombres, a la izquierda de la barra de fórmulas, escribiendo dicho nombre y pulsando Enter). Nos situamos ahora en C6 y escribimos la fórmula:

=SI.ERROR(PROMEDIO(DESREF(F2;COINCIDIR(BUSCARV(C4;Datomes;1);

Datomes);;-C2;));"No disponible")

Con la función BUSCARV localizamos la fecha actual en nuestra tabla de ventas. Al anidarla dentro de la función COINCIDIR, obtendremos el número de fila en el que se encuentra dicha fecha. A su vez, COINCIDIR se encuentra anidada dentro de la función DESREF y es el argumento de fila, esto es, partiendo de la referencia de celda F2 tiene que contar tantas filas como devuelva COINCIDIR(BUSCARV(C4;Datomes;1). Ya tenemos la fila referente al mes corriente, ahora nos queda indicarle desde qué mes queremos realizar el cálculo del promedio, es decir, de los últimos 12 meses, de los últimos 6 meses, etcétera. Esto lo resolvemos con el argumento alto de la función DESREF. En concreto tendrá que retroceder desde la fila de la fecha actual hasta el número de meses que le indiquemos en la celda de entrada C2, y por ello le ponemos signo negativo a dicha referencia (-C2).

Le he añadido la función SI.ERROR para que si indicamos un número de meses demasiado elevado no aparezca el error #¡REF! sino que aparezca un texto un poco más estético del tipo "No disponible":

Se me olvidaba... Para conseguir que el texto del rótulo de la media(B6) sea variable, es decir, que cambie en función del número de meses que escribamos en C2, tenemos que poner la siguiente fórmula en B6:  ="Media "&C2&" meses:"

Suma Dependiente de Varios Criterios

Descargar el Archivo

"Tengo un tabla con varios conceptos: zona; importe vendido; fecha de venta; etcétera. Necesito obtener una lista de registros únicos con la suma de ventas para cada uno de ellos poniéndole como condicionante extra que esté comprendido entre una fecha determinada y otra (que uno mismo pueda modificar en todo momento)."

Se puede solucionar por varias vías. La más sencilla es con Tablas Dinámicas pero también por medio de formulación haciendo uso de la función SUMAR.SI.CONJUNTO

Partimos del siguiente ejemplo:

Solución Con Tablas Dinámicas

Nos situamos en cualquier celda de la tabla del ejemplo y vamos a la ficha Insertar / Tabla dinámica. En la ventana que se abre le damos a aceptar  y estaremos en disposición de montar nuestra tabla dinámica. Como campo de fila ponemos Zona y como campo de columna ponemos Fecha. Finalmente como datos ponemos Unidades y el resultado obtenido será el siguiente:

Hacemos clic con el botón derecho del ratón encima de Fecha y seleccionamos Agrupar / Meses (si queremos ver otro desglose temporal haremos clic en otra opción como trimestral, semestral, etcétera):

Ya sólo nos quedaría filtrar el campo Fecha con la fecha inicial y final que nos interese:

Tras pulsar Aceptar obtendremos la información por zona y por mes para las fechas indicadas en el filtro:

Solución Con Fórmulas

Volviendo a nuestro ejemplo original, lo primero que vamos a hacer es Crear Nombres. Seleccionamos el rango B2:D26 y vamos a la ficha Fórmulas y seleccionamos Crear desde la selección. Se abrirá una ventana y marcamos Fila superior :

En nuestro cuadro de nombres (a la izquierda de la barra de fórmulas) aparecerán ahora los nombres creados, es decir, Zona, Fecha y Unidades, que utilizaremos a continuación en nuestra fórmula.

Generamos ahora una lista con los criterios que podremos utilizar; la zona de entrada de fechas y una lista de registros únicos, a saber:

Seleccionamos H3 y H4 y vamos a la ficha Datos y abrimos Validación de datos. Dentro de la ventana que se abre seleccionamos Permitir / Lista y en Origen marcamos el rango K2:K6 y pulsamos Aceptar. De esta manera tenemos ya listas desplegables en las celdas H3 y H4 con los criterios aplicables a las fechas. Introducimos las fechas deseadas en I3 e I4, por ejemplo el 1/1/2012 y el 1/6/2012. En H3 introducimos el criterio >= y en H4 el criterio <=. De esta manera le pediremos que nos muestre el detalle por zonas de las unidades vendidas entre dichas fechas:

Ahora vamos a aplicar la función SUMAR.SI.CONJUNTO para solucionar el problema. Esta función nace en la versión 2007 y lo que hace es sumar las celdas que cumplan con varios criterios. Su sintaxis es la siguiente:

SUMAR.SI.CONJUNTO(rango_suma; rango_criterios1; criterios1; [rango_criterios2; criterios2]; ...)

* Rango_suma: Argumento obligatorio. Una o más celdas para sumar, incluidos números o nombres, rangos o referencias de celda. Se omiten los valores en blanco o de texto.
* Rango_criterios1: Obligatorio. El primer rango en el que se evalúan los criterios asociados.
* Criterios1: Obligatorio. Los criterios en forma de número, expresión, referencia de celda o texto que define qué celdas del argumento rango_criterios1 se agregarán. Por ejemplo, los criterios se pueden expresar como 32, ">32", B4, "manzanas" o "32".
* Rango_criterios2; criterios2; … Opcionales. Rangos adicionales y sus criterios asociados. Se permiten hasta 127 pares de rangos/criterios.

Nos situamos en la celda H8 y escribimos la siguiente fórmula:

=SUMAR.SI.CONJUNTO(Unidades;Zona;G8;Fecha;$H$3&$I$3;Fecha;$H$4&$I$4)

Lo que le estamos pidiendo a excel con esta fórmula es que sume las unidades que dentro del rango zona (B3:B26) cumplan con el criterio G8 (en nuestro ejemplo La Coruña) y que además cumplan con los dos criterios de fecha indicados en la fórmula. Para evitar escribir datos dentro de la fórmula o en las celdas de fecha hemos utilizado & (CONCATENAR) para hacer más flexible el modelo. El resultado es el deseado:

Años, Meses y Días Transcurridos entre Fechas

"Necesito calcular los años, meses y días transcurridos desde una fecha concreta hasta hoy".

La solución es bastante sencilla utilizando, en términos de John Walkenbach, la función misteriosa de Excel, esto es, SIFECHA. Dicha función no aparece en la lista de funciones desplegables de la categoría Fecha y Hora. Tampoco aparece en el cuadro de diálogo Insertar Función, por lo que tendremos que introducirla manualmente ¿Por qué? Los caminos de Microsoft son inescrutables... Lo cierto es que se trata de una función muy útil que paso a describir:

=SiFECHA(Fecha_Inicial;Fecha_Final;Argumento_tiempo)

Los dos primeros argumentos no requieren explicación mientras que el tercer argumento se trata de un código que representa la unidad de tiempo que nos interesa. A saber:

"Y" Devolverá el número de años completos entre fecha inicial y fecha final.

"M" Devolverá el número de meses completos totales entre fecha inicial y fecha final.

"D" Devolverá el número de días totales entre fecha inicial y fecha final.

"YM" Devolverá los meses transcurridos entre las fechas y que no completen un año.

"MD" Devolverá los días del mes entre fechas que no completen un mes.

"YD" Devolverá los días entre fechas que no completen un año.

Partimos del siguiente ejemplo y entrada de datos:

Nos situamos en la celda C5 y escribimos la fórmula que calculará el número de años enteros transcurridos entre la fecha inicial y la fecha final:

=SIFECHA($C$2;$C$3;"Y")

Una vez hecho esto, y dado que hemos colocado referencias absolutas a la fecha inicial y a la fecha final, podemos copiar hacia abajo hasta la celda C7 y simplemente modificar después el tercer argumento en la fórmulas de C6 y C7. A saber:

En C6 =SIFECHA($C$2;$C$3;"YM") que nos devuelve el número de meses transcurridos entre las fechas y que no completan un año.

En C7 =SIFECHA($C$2;$C$3;"MD") que nos devuelve el número de días que no completan un mes.

Si queremos que aparezca el resultado completo en una sola celda entonces deberemos utilizar la función CONCATENAR (usaremos el operador &) para unir las distintas partes de la ecuación. En la celda B9 escribimos la siguiente fórmula:

A continuación puedes ver el resultado de aplicar las distintas opciones de argumento de tiempo en nuestro ejemplo:

Cálculo de Número de Días

"Necesito calcular, cada día, cuántos días llevan transcurridos desde el 1 de enero del año en curso y los días que faltan para concluir el año ¿hay alguna función que realice estos cálculos?"

No existe ninguna función directa que realice estos cálculos, pero la solución es bien sencilla utilizando las funciones FECHA y AÑO. Supongamos que deseamos contar con la siguiente información actualizada diariamente:

1. la fórmula de B2 es muy sencilla:

=HOY()

2. La fórmula de B3 es igualmente sencilla utilizando la función NUM.DE.SEMANA Esta función tiene tan sólo dos argumentos NUM.DE.SEMANA(Num_de_serie;Tipo). El primer argumento se refiere a la fecha de la que queremos saber el número de semana del año que le corresponde. El segundo argumento sirve para determinar el tipo de semana, esto es, si la semana comienza el domingo, en cuyo caso escribiremos el valor 1, o si queremos considerar semanas cuyo primer día sea el lunes, en cuyo caso el valor de este argumento será 2. Así las cosas, la fórmula resultante será:

=NUM.DE.SEMANA(B2;2)

3. Para calcular los días transcurridos desde el 1 de enero del año en curso hasta la fecha actual, utilizamos la siguiente fórmula:

=B2-FECHA(AÑO(B2);1;1) Si queremos añadir un texto descriptivo al resultado podemos utilizar el operador & (CONCATENAR):

=B2-FECHA(AÑO(B2);1;1)&" días desde principio del año"

4. Finalmente para saber cuántos días restan para concluir el año en curso:

=FECHA(AÑO(B2);12;31)-B2 Añadiendo un texto descriptivo sería:

=FECHA(AÑO(B2);12;31)-B2&" días para terminar el año"

Si queremos mantener esta información siempre visible en la hoja, podemos utilizar la opción de Inmovilizar Paneles. Para ello nos situamos en la celda A7 y vamos al menú Ventana/Inmovilizar Paneles. Aparecerá un borde superior en toda la fila 7 quedando inmovilizadas las celdas que se encuentren por encima de dicha linea.