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

SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS

Hola a todos:

Hoy vamos a seguir con las fórmulas, en concreto con el mundo de las sumas acumuladas y la problemática que surge en determinados casos.

Pongamos un ejemplo visual del problema:

SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS

Como podéis observar se trata de obtener el sumatorio de los importes en cada rango en función de si en la columna C hay datos (en este caso, fechas).

Esta tarea es posible que se pueda resolver con una fórmula matricial (tengo pendiente estudiarlo), sin embargo, lo podemos resolver con una combinación de varias fórmulas:

En concreto la fórmula que os propongo es la siguiente:

=SI(FILA(B2)>2;SI.ERROR(SI(C2<>"";SUMA($A$2:INDIRECTO("A"&FILA(B2)));"")-SUMA($B$2:INDIRECTO("B"&(FILA(C2)-1)));"");SI(C2<>"";SUMA($A$2:INDIRECTO("A"&FILA(B2)));""))

Es importante en esta fórmula tener presente el número de fila en el que estamos situados en cada momento, dado que la segunda celda (teniendo en cuenta que tenemos encabezados), la fórmula será diferente a las demás, por ello indico un condicional al inicio de la fórmula dependiendo de si la fila en la que estamos es mayor o menor de 2.

La idea es primero capturar el acumulado desde la fila A2 hasta la fila en la que estamos:

=SUMA($A$2:INDIRECTO("A"&FILA(B2)))

Y luego restar lo que ya hemos acumulado

-SUMA($B$2:INDIRECTO("B"&(FILA(C2)-1)))

De esta forma conseguimos el resultado requerido.

SUMAS ACUMULADAS POR RANGO SEGÚN CRITERIOS_1

Y eso es todo : )

Espero que os sea de utilidad!.

Descarga el archivo de ejemplo pulsando en: SUMAS ACUMULADAS POR RANGO SEGÚN 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!!

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

OBTENER EL VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)

Hola a todos:

Hoy toca un poco de formulación : )

Hace un tiempo ya desarrollé una matricial para obtener el valor de una celda según el valor máximo de otra celda en una matriz. Esto lo podéis encontrar en este post: OBTENER VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ

En ese ejemplo utilizábamos tan solo un criterio, el valor del ID y en base a eso encontrar el valor de la celda. Pues bien, imaginad que debemos tener en cuenta dos ID´s más (ID2 e ID3).

Esta es la base de cálculo:

OBTENER EL VALOR DE UNA CELDA SEGUN EL VALOR MAXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)

Un ejemplo de lo que necesitaríamos buscar sería, ID1=1321, ID2=1 e ID3=B

Si usamos un filtro veremos los parámetros, y al igual que en el post anterior, según los criterios de los ID tenemos que obtener el valor de Cantidad1 en función del valor máximo de Cantidad2, es decir 27

OBTENER EL VALOR DE UNA CELDA SEGUN EL VALOR MAXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)_1

Pues bien, para conseguir este resultado, utilizaremos la siguiente fórmula matricial:

{=INDIRECTO(DIRECCION(COINCIDIR(MAX(SI(A:A=H1;SI(B:B=H2;SI(C:C=H3;E:E))));E:E;0);4))}

Para comprender las funciones utilizadas y su funcionamiento os remito al post anterior. En este ejercicio, hemos ampliado únicamente la parte de la función que hace referencia a los criterios y con la que obtendremos el valor del campo Cantidad2: 830

{=MAX(SI(A:A=H1;SI(B:B=H2;SI(C:C=H3;E:E))))}

El resto de la matricial detectará la posición de la celda anterior.

El ejercicio quedaría así:

OBTENER EL VALOR DE UNA CELDA SEGUN EL VALOR MAXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE II)_2

Como habéis podido ver se trata “anidar” varios condicionales dentro de la función MAX y ejecutar matricialmente la función.

Recordad que para introducir las fórmulas matriciales debemos seleccionar la fórmula y presionar: CTRL + MAYUS + ENTRAR

Y esto es todo, espero que os sea de utilidad : )

Descarga el archivo de ejemplo pulsando en: OBTENER EL VALOR DE UNA CELDA SEGÚN EL VALOR MÁXIMO DE UNA MATRIZ CON VARIAS CONDICIONES (PARTE 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

SUMAR UN RANGO EN FILAS (EN HORIZONTAL) DEPENDIENDO DE UN VALOR

Hola a todos 🙂

Espero que todo os vaya bien!.

El post de hoy va a ser muy corto y será ampliación de un post anterior fruto de la consulta de un lector. En concreto el post es el siguiente: SUMAR UN RANGO DEPENDIENDO DE UN VALOR

En esa publicación lo que se expone es la forma de sumar un rango de números en función de un valor, o lo que es lo mismo, sumar utilizando un rango dinámico.

Lo que el lector solicitaba era poder hacer el mismo ejercicio pero estando el rango de la suma en una fila y no una columna, es decir, en horizontal. Pues bien, para solucionar esta duda, utilizaremos el siguiente ejemplo, donde debajo de cada mes se encuentra una cifra (que pueden ser datos de negocio, de facturación , etc):

SUMAR UN RANGO EN FILAS (EN HORIZONTAL) DEPENDIENDO DE UN VALOR

Y debemos introducir la siguiente fórmula para realizar la operación:

=SUMA(A2:INDIRECTO("F2C"&B4;0))

Para determinar la referencia al rango que se encuentra en la función INDIRECTO, debemos hacer uso del estilo de referencia F1C1 que nos permite indicar fila y columna. De modo que podemos vincular un valor a la columna y establecer la fila en el 2 (segunda fila). La fórmula traducida en el ejemplo es: =Suma(A2:K2)

Como podéis observar, la suma hasta el mes de Noviembre es de 894

Pero también podemos sumar un rango de varias filas, por ejemplo así:

SUMAR UN RANGO EN FILAS (EN HORIZONTAL) DEPENDIENDO DE UN VALOR2.jpg

Solo tenemos que indicar el rango a sumar en la fórmula indicando la fila 6 y la columna 11:

=SUMA(A2:INDIRECTO("F6C"&B8;0))

Y el resultado es de 7.214 🙂

Ya sé que es un post muy cortito, pero el hecho que me lo hayan planteado en una consulta, quiere decir que tiene utilidad.

Descarga el archivo de ejemplo pulsando en: SUMAR UN RANGO EN FILAS (EN HORIZONTAL) DEPENDIENDO DE UN VALOR

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

Donate Button with Credit Cards

¡¡Muchas gracias!!