ORDENAR VALORES UTILIZANDO FÓRMULAS

Hola a todos:

Estos días he recibido multitud de consultas y hoy me gustaría escribir un post muy sencillo sobre la posibilidad de ordenar valores utilizando únicamente fórmulas.

En la consulta inicial me preguntan cómo se pueden ordenar una serie de valores siempre numéricos tanto de menor a mayor como a la inversa, de mayor a menor.

Bien, esto lo podemos hacer con una sencilla fórmula, veamos los datos:

ORDENAR VALORES UTILIZANDO FORMULAS

En este caso es una relación de personas con una cifra que ha alcanzado (de lo que sea, nos vale como ejemplo).

La cuestión es ahora cómo ordenar esos números sin utilizar el filtro o el comando de ordenar de la cinta de opciones.

Pues bien, podemos utilizar la siguiente función:

=K.ESIMO.MAYOR($B$2:B10;FILAS($B$2:B2))

Con  K.esimo.Mayor vamos a obtener el número mayor de un rango, en este caso, al mover la fórmula nos va a ir mostrando el mayor según el rango seleccionado.

Y el resultado será el siguiente:

ORDENAR VALORES UTILIZANDO FORMULAS_1

Como podéis ver, conseguir ordenar el rango de Mayor a Menor. Si necesitamos invertirlo y hacerlo de Menor a Mayor, utilizaremos K.esimo.Menor.

Pero el lector me solicitó si también podía conseguir que se mostrase en otra columna los nombres relacionados con los números ordenados. Y en efecto, sí podemos obtenerlos, y lo vamos a hacer con la siguiente fórmula:

=INDIRECTO(DIRECCION(COINCIDIR(K.ESIMO.MAYOR($B$2:B10;FILAS($B$2:B2));$B$2:B10;0)+1;1))

Dado que con la fórmula inicial podemos ordenar los números en relación a la lista inicial, con la función Coincidir podemos obtener la fila en la que se encuentra nuestro número antes de ordenarlo, y  si sabemos la fila, con la función Dirección podemos obtener la celda que nos interesa de la columna A para obtener el nombre.

Así:

ORDENAR VALORES UTILIZANDO FORMULAS_3

He indicado con flechas en rojo la dirección con la que obtenemos las celdas de referencia que nos interesa.

Ahora, para obtener el nombre, utilizaremos la función indirecto:

ORDENAR VALORES UTILIZANDO FORMULAS_4

Y así, finalmente obtenemos toda la información que necesitamos.

Bien!!

Pero a los pocos días el mismo lector me solicitó si de alguna manera podría incluir rangos dinámicos en ambas fórmulas para no tener que ir actualizando los rangos a mano cada vez que el listado se fuese incrementando.

Pues eso también se puede realizar, la manera es incluyendo la función Desref en las dos fórmulas, y lo haremos sustituyendo en ambas fórmulas la parte que hace referencia a los rangos por:

DESREF($B$2; 0; 0; CONTARA(B:B))

Y así ya podemos obtener la misma información, pero con rangos dinámicos:

Para ordenar:

=K.ESIMO.MAYOR(DESREF($B$2; 0; 0; CONTARA(B:B));FILAS($B$2:B2))

Para obtener los nombres:

=INDIRECTO(DIRECCION(COINCIDIR(K.ESIMO.MAYOR(DESREF($B$2; 0; 0; CONTARA($B:$B));FILAS($B$2:B2));DESREF($B$2; 0; 0; CONTARA($B:$B));0)+1;1);1)

En resumen:

ORDENAR VALORES UTILIZANDO FORMULAS_5

Y eso es todo!. Como podéis observar, lo hemos conseguido.

Es importante comentar que está fórmula es solo válida para datos numéricos y en los que no existen duplicados. Si contamos con duplicados tendríamos que trabajar con una columna auxiliar en la que introducir algún elemento para “desempatar” esos datos, por ejemplo añadiendo una cifra muy pequeña a los números originales.

Descarga el archivo de ejemplo pulsando en: ORDENAR VALORES UTILIZANDO FÓRMULAS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

CONTAR REGISTROS ÚNICOS CON VARIOS REGISTROS II

Hola a todos:

Hace un tiempo escribí un post en el que, mediante fórmulas, contaba registros únicos teniendo en cuenta varios registros: CONTAR REGISTROS ÚNICOS CON VARIOS CRITERIOS.

Pero lo cierto es que en el post, solamente utilizaba un criterio activo en la fórmula. Por dicho motivo he recibido varias consultas solicitando una ampliación de fórmula con un nuevo criterio.

Pues bien, siguiendo el post anterior, he añadido un nuevo criterio al Excel (Criterio2):

contar registros unicos con varios registros ii

Pues bien, el objetivo es contar aquellas áreas donde el criterio y criterio2 sea >=1

La fórmula que voy a utilizar es la siguiente (matricial):

{=CONTAR(1/FRECUENCIA(SI(Hoja1!$A$2:$A$19=F1;SI(Hoja1!$D$2:$D$19>=1;SI(Hoja1!$B$2:$B$19>=1;COINCIDIR(Hoja1!$C$2:$C$19;Hoja1!$C$2:$C$19;0))));FILA($A$2:$A$19)-FILA(DESREF($A$2:$A$19;;;1;))+1))}

Esta fórmula la podemos aplicar para obtener los tres conceptos que necesitamos. Este es el resultado:

contar registros unicos con varios registros ii_

Efectivamente, para el primer caso, tenemos 3 únicos, lo podemos ver utilizando el filtro:

contar registros unicos con varios registros ii_1

Contariamos cada color como un registro único, recordando que los criterios son >=1 en la columna B y D. El registro en blanco no se tiene en cuenta dado que B es igual a 0.

Os recuerdo que las matrices se introducen: seleccionando la fórmula y luego presionando CTRL + MAYUS + ENTRAR.

Y esto es todo, espero que con esta ampliación sea de mayor utilidad.

Descarga el archivo de ejemplo pulsando en: CONTAR REGISTROS ÚNICOS CON VARIOS REGISTROS II

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

DETECTAR VALOR DE LA PRIMERA Y ÚLTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

Hola a todos!

Hace unos días recibí una consulta en la que me solicitaban el poder capturar el valor de una celda en función de si la celda de la fila inferior tenía datos.

Por ejemplo, imaginad que tenemos dos filas, en la primera unas fechas y en la segunda unos importes, pues bien necesitamos extraer las fechas de la primera y última celda con datos en la fila de los importes:

DETECTAR VALOR DE LA PRIMERA Y ULTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

Para resolver esta necesidad, (que con una macro sería muy sencillo), con una fórmula lo podemos hacer con una función matricial.

Para extraer la primera fecha, el 3 de enero:

{=DESREF(INDICE(B2:O2;COINCIDIR(FALSO;(B2:O2="");0));-1;0)}

Y para extraer la última fecha, el 11 de enero:

{=DESREF(INDICE((B2:O2);;MAX(SI((B2:O2)<>"";COLUMNA(B2:O2)))-1);-1;0)}

En ambos casos, primero detectamos tanto el valor inicial o el final, sin tener en cuenta las celdas vacías, esto nos dará el número de columna, luego simplemente usamos la función desref para indicar que queremos la fila anterior (-1).

El resultado es el siguiente:

DETECTAR VALOR DE LA PRIMERA Y ULTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR1

Como podéis comprobar, las fórmulas funcionan perfectamente.

Y eso ha sido todo, espero que os resulte de utilidad:

Descarga el archivo de ejemplo pulsando en: DETECTAR VALOR DE LA PRIMERA Y ÚLTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

CONTAR REGISTROS ÚNICOS CON VARIOS CRITERIOS

Hola a todos:

Hoy voy a trabajar un poco con formulación clásica de Excel. En concreto vamos a contar registros únicos en función de varios criterios. Para ello he preparado una sencilla tabla con unos datos que nos van a servir de ejemplo:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS

Imaginad que queremos contar los registros únicos de la columna C que dependan de “Dirección de Area 1 y que el criterio de la columna B sea mayor o igual a 1. Es decir:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS1

Qué los registros únicos serán 3, las Subdirecciones Zona 1.1, 1.2 y 1.3

Para poder realizar esto con una fórmula vamos a utilizar la siguiente función matricial:

{=CONTAR(1/FRECUENCIA(SI(Hoja1!$A$2:$A$18="Dirección Area 1";SI(Hoja1!$B$2:$B$18>=1;COINCIDIR(Hoja1!$C$2:$C$18;Hoja1!$C$2:$C$18;0)));FILA($A$2:$A$18)-FILA(DESREF($A$2:$A$18;;;1;))+1))}

Como podéis ver, estamos usando varios fórmulas, CONTAR, FRECUENCIA, SI, COINCIDIR, FILA y DESREF.

El resultado es el siguiente:

CONTAR REGISTROS UNICOS CON VARIOS CRITERIOS2

Obviamente, la fórmula se puede modificar variando e incluyendo nuevos criterios, solo debéis realizar algunas pruebas.

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Descarga el archivo de ejemplo pulsando en: CONTAR REGISTROS ÚNICOS CON VARIOS CRITERIOS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!