Sumas condicionales con SUMAPRODUCTO

La función SUMAR.SI nos permite realizar sumas de acuerdo a una condición.
Ya hemos mostrado en este blog cómo hacer sumas condicionales con más de una condición
.
En esta nota veremos como enfrentarnos con situaciones especiales del tipo, por ejemplo, cómo sumar los cinco mayores números de una lista.

Por ejemplo, supongamos esta lista de números

Los cinco números mayores de la lista ( 94, 98, 93, 88, 87) suman 454. No hay forma en la cual podamos usar SUMAR.SI para sumar los números mayores de la lista. Pero si podemos hacerlo con SUMAPRODUCTO (o con fórmulas matriciales). EN nuestro caso

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)<=5)) donde lista es un nombre que define el rango A2:A21 SUMAPRODUCTO crea dos matrices. Una contiene todos los números del rango "lista" (A2:A21); la otra matriz contiene valores VERDADERO y FALSO producidos por la función JERARQUIA. Cuando el valor calculado es menor o igual a 5, es VERDADERO; en caso contrario, FALSO. Como ya hemos visto, Excel interpreta VERDADERO como 1 al emplearlo en una operación y FALSO como 0.

Para calcular los 5 menores podemos recurrir a la función CONTAR, para determinar cuantos miembros hay en el rango y así poder determinar cuales son los últimos 5 en orden decreciente

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)>CONTAR(lista)-5))

o esta mas sencilla, sin CONTAR

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista;1)<=5))

Si queremos sumar todos los números pares usamos la fórmula

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=0))

o

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)<>1))

Para los impares usamos

=SUMAPRODUCTO(lista*RESIDUO(lista;2))

que es el equivalente a

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=1))

pero nos ahorra dos paréntesis.

La función RESIDUO(número;2) da 0 si el número es par. Por eso la expresión RESIDUO(lista;2)=0 es VERDADERO si el número en la "lista" es par.

Para sumar los múltiplos de un determinado número, también podemos usarla función RESIDUO. Por ejemplo, para sumar todos los números que son múltiplos de 3 en nuestra lista, usamos la fórmula

=SUMAPRODUCTO(lista*(RESIDUO(lista;3)=0))

Otro uso de RESIDUO es sumar todos los valores que de las filas pares o impares. Por ejemplo, para sumar todos los valores del rango "lista" que están en filas pares, usamos

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)=0))

De la misma manera, para sumar los valores en filas impares podemos usar:

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)<>0))

Si queremos sumar cada tres filas, a partir de la fila 3 podemos usar esta fórmula

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);3)=0))

Si queremos sumar cada 3 filas, pero empezando de la primer fila del rango, usamos esta fórmula

=SUMAPRODUCTO((RESIDUO(FILA(lista);3)=2)*lista)

donde "2" es el número de la primer fila del rango.

La fórmula general es =SUMAPRODUCTO((RESIDUO(FILA(lista);n)=m)*lista)

Donde n es el "escalón" (número de filas que queremos saltear en la cuenta) y m es la fila de donde comenzamos a sumar.

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel

Crear un índice de hojas en un cuaderno de Excel.

Una de las consultas que recibo con cierta frecuencia es cómo crear un índice de las hojas de un cuaderno Excel.

Cuando queremos navegar de una a otra hoja del cuaderno podemos usar varios métodos, por ejemplo usar las flechas de navegación en la esquina inferior izquierda de la hoja o hacer un clic con el botón derecho del mouse sobre ellas para abrir el menú de navegación



Pero cuando tenemos muchas hojas en un cuaderno, por ejemplo una por semana del año, estos métodos resultan incómodos.
La solución es crear una hoja que haga de índice. Las entradas del índice son los nombres de las hojas, a las cuales le hemos incorporado un enlace (hyperlink), de manera que con un solo clic podamos navegar a la hoja deseada.

Cuando se trata de pocas hojas, podemos hacer esto en forma manual. Pero cuando tenemos un cuaderno con un gran número de hojas, lo mejor es utilizar una macro.
Esta macro, que pueden descargar aquí, nos ofrece la opción de crear una nueva hoja que contenga el índice




Si aceptamos la opción, la macro crea la nueva hoja, en la celda A1 pone el título "Índice" y a partir de la fila 3, pondrá los nombres de las hojas con un enlace a la celda A1 de cada una de ellas.



Si no aceptamos la opción de crear una nueva hoja, el índice es creado a partir de la celda activa del cuaderno.




Technorati Tags: MS Excel

Búsqueda aproximada en Excel – tercera nota

En las últimas dos notas vimos cómo encontrar en una lista el valor más cercano al valor buscado, o extraer el valor asociado a él.
La fórmula matricial que expusimos, y que he mejorado gracias al aporte de Natxo (ver comentario en la nota anterior), nos da el valor más cercano al valor buscado. A veces buscamos el mayor valor más cercano y a veces el menor. Por ejemplo, si observamos la tabla del ejemplo de la nota anterior



vemos que Pedro, con 76, es el más cercano al promedio, 76,2. Pero si buscamos quién tiene la calificación más cercana por encima del promedio, la respuesta es María con 79.

Esta fórmula matricial nos permite encontrar el valor más cercano por encima del valor buscado

={=INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-B9>=0;B2:B7;FALSO));SI(B2:B7-B9>=0;B2:B7;FALSO);FALSO))}

Esta fórmula crea una matriz con las diferencias entre los valores de la lista y el valor buscado; luego encuentra el menor que sea mayor a cero, es decir, el más cercano que sea mayor al valor buscado.

De la misma manera, esta fórmula no da el valor más cercano que es menor que el buscado

={INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-C9>=0;B2:B7;FALSO));SI(B2:B7-C9>=0;B2:B7;FALSO);FALSO))}

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel