Calcular promedio ponderado en tablas dinámicas

Si queremos calcular un promedio ponderado en una tabla dinámica de Excel, lo más probable es que intentemos hacerlo creando un campo calculado. Esto es lo que intentó uno de mis lectores sólo para descubrir que SUMAPRODUCTO no da los resultados esperados cuando se usa en un campo calculado.

Vamos a describir el problema (y luego mostrar la solución). Supongamos esta tabla de datos

Queremos calcular la venta promedio por operación de cada vendedor, es decir, el promedio de las ventas ponderado por la cantidad de operaciones que cada uno efectuó. La idea es obtener los resultados que aparecen en la columna “ponderado” (nótese la diferencia en relación al promedio simple)

Para realizar el cálculo en una tabla dinámica (lo que es muy conveniente si el tamaño y los componentes de nuestros datos cambian con frecuencia) podríamos estar tentados a crear un campo calculado con esta fórmula

=SUMAPRODUCTO('Total de Ventas','Nro. De Ventas' )/SUMA('Nro. De Ventas' )

Pero, como señalo más arriba, esta fórmula no funciona en campos calculados de tablas dinámicas.

La solución es crear un campo auxiliar (promedio ponderado) con esta fórmula


=(D3*E3)/SUMAR.SI($C$3:$C$11,C3,$E$3:$E$11)

Ahora podemos crear la tabla dinámica, donde el promedio ponderado para cada vendedor será calculado en el campo Promedio Ponderado usando la operación Suma

Como ven, la tabla no muestra la fila de totales. Esto se debe a que el total acumulado que mostraría la tabla dinámica para el campo “promedio ponderado” sería incorrecto:

Si queremos mostrar los totales para las columnas “Ventas” y “Nro. De Ventas”, que están calculados correctamente, podemos ocultar el total de “Promedio Ponderado” dándole a la fuente el mismo color que el fondo de la celda volviéndolo de esta manera “invisible”

Suma interna de los dígitos de un número con Excel - ampliación

En mi nota anterior sobre la suma interna de los dígitos de un número, propuse esta fórmula para la tarea

=SUMAPRODUCTO(EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1)*1)

También podemos usar

=SUMAPRODUCTO(--EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1))

si preferimos usar la doble negación para convertir los valores VERDADERO o FALSO en 1 o 0.

El problema con esta fórmula es que cuando el resultado es un número de dos dígitos, tenemos que volver a aplicarla, ya sea anidando dos fórmulas o usando celdas auxiliares.

Con esta fórmula podemos realizar la suma interna de los dígitos de un número en una única operación:

=1+RESIDUO(SUMAPRODUCTO(--EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1))-1,9)

Esta fórmula se base en el artificio matemático "Prueba del nueve" y fue propuesta por Rick Rothstein en un comentario en el sitio de Chandoo.

Fórmulas con condiciones múltiples en Excel

Supongamos un formulario en Excel donde controlamos un proceso de aprobación de créditos compuesto de tres partes: presentación, proceso y aprobación

Al introducir una fecha en la celda A4, la celda Estatus (D4) muestra la etapa en que se encuentra el proceso; al introducir una fecha en B4, el valor de Estatus cambia a “En proceso” y finalmente al introducir una fecha en C4, Estatus muestra “Aprobado”



La solución más inmediata es crear una fórmula con la función SI

=SI(A4<>"",SI(B4<>"",SI(C4<>"","Aprobado","En proceso"),"Presentado"),"")

Esta fórmula tiene varios problemas. El primero es que el uso de SI anidado nos pone serias limitaciones a la cantidad de condiciones que podemos procesar. Si bien en Excel 2007 o 2010 podemos anidar hasta 64 niveles de SI (en comparación a los 7 niveles en Excel Clásico), por encima de las 3 o 4 condiciones la fórmula se vuelve compleja y difícil de manejar.

Podemos superar este problema creando una fórmula con la función ELEGIR

=ELEGIR(CONTARA(A4:C4)+1,"","Presentado","En proceso","Aprobado")

Otro problema con estas fórmulas es que no toman en cuenta errores. Por ejemplo, que introduzcamos una fecha en “Proceso” sin que hayamos puesta una fecha anterior en “Presentación”.

Para superar este inconveniente podemos usar esta fórmula que combina ELEGIR con MMULT y una matriz de constantes

=ELEGIR(MMULT((A4:C4<>0)*1,{4;2;1})+1,"","Error 3","Error 2","Error 2","Presentado","Error 1","En proceso","Aprobado")



MMULT multiplica los miembros de la matriz (A4:C4<>0) por los valores de la matriz {4;2;1} determinando así el valor de la variable de ELEGIR. Este video muestra el proceso de cálculo de la fórmula cuando sólo la celda A4 contiene una fecha



También esta fórmula tiene un inconveniente. Es difícil de mantener, por ejemplo, si queremos agregar condiciones o cambiar valores.

La mejor solución, y que está inspirada en una respuesta de Ron Coderre en uno de los foros en los que participo, es usar una tabla auxiliar para determinar los valores y SUMAPRODUCTO con BUSCARV (CONSULTAV en Excel 2010)

La fórmula es (o parece!) sencilla

=CONSULTAV(SUMAPRODUCTO(((A4:C4)<>"")*{1,10,100}),$I$4:$J$11,2,0)

Como en la fórmula anterior, multiplicamos dos matrices para obtener un valor que usamos en CONSULTAV (o BUSCARV en versiones anteriores a Excel 2010) para obtener el texto correspondiente en Estatus



Para determinar los valores de la columna Indicador, usamos un sistema “pseudo-binario”. Esta imagen explica el método

Cada fila en la tabla es una de las posibilidades; en cada posibilidad indicamos con un 1 si la celda contiene una fecha; luego sumamos las celdas de la fila donde la columna Fecha 1 le asignamos el valor 1 (unidades), la columna Fecha 2 el valor 10 (decenas) y a la columna Fecha 3 el valor 100 (centenas).

El archivo con los ejemplos se puede descargar aquí.

Sumar cada enésimo valor de una serie con Excel

Recibí esta consulta: dado un rango de valores, ¿cómo hacer para sumar los valores de cada quinta fila con Excel?

Supongamos estos valores en el rango A1:A20

Esta fórmula suma cada quinto valor en el rango

=SUMAPRODUCTO((RESIDUO(FILA(A1:A20),5)=0)*A1:A20)

Podemos generalizar la fórmula reemplazando la constante 5 de la función RESIDUO por una referencia a una celda

=SUMAPRODUCTO((RESIDUO(FILA(A1:A20),D1)=0)*A1:A20)

Podemos cambiar el valor de D1 para ver el resultado de sumar cada n filas



Esta fórmula funciona de la siguiente manera:

RESIDUO(FILA(A1:A20),D1)=0 da como resultado VERDADERO sólo si el número de fila es múltiplo de 5 (o el número que hayamos ingresado en D1)

Luego al multiplicar los valores de la serie por los correspondientes valores VERDADERO o FALSO obtenemos

Suma interna de los dígitos de un número con Excel

Parece ser que la onda mística que barre el mundo en los últimos años ha llegado también a algunos de mis amigos.

A uno de ellos se le ha dado por la numerología, que según Wikipedia viene a ser algo así como

una práctica adivinatoria utilizando los números. Es un conjunto de creencias o tradiciones que pretende establecer una relación mística entre los números, los seres vivos y las fuerzas físicas o espirituales.

Mi amigo quería desarrollar un modelo en Excel que le ayudara a hacer los cálculos necesarios según la doctrina adivinatoria. Básicamente los siguientes:

• Dado que a cada letra del alfabeto le corresponde un número, debemos sumar los valores de las letras del nombre (propio y apellido por separado) y totalizarlo reduciéndolo a un número de una sola cifra
• Dada la fecha de nacimiento, sumar los dígitos que la componen y totalizarlos tal como hicimos con el nombre.

Puesto en términos de Excel si a la letra A le corresponde el número 1 y a la letra N el 14, el nombre Ana equivale a 1 +14 = 15 y a su vez 1 + 5 = 6.

El desafío es hacerlo sin macros, por supuesto. Para realizar la suma interna de los dígitos de un número podemos usar esta fórmula

=SUMAPRODUCTO(EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1)*1)

Vamos a explicar esta fórmula por pasos:

=FILA(INDIRECTO("1:" & LARGO(A2))) crea una vector vertical con una serie de número que empieza en 1 y termina en el número que coincide con el largo del número cuyos dígitos queremos sumar. En nuestro ejemplo el resultado es FILA(1:5) es decir el vector {1,2,3,4,5}

EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1) equivale a EXTRAE(A2, {1,2,3,4,5},1), es decir, extraemos el primer dígito, luego el segundo y así sucesivamente hasta el quinto. Recordemos que SUMAPRODUCTO es una función matricial.

Finalmente SUMAPRODUCTO multiplica cada miembro del vector creado con EXTRAE por 1 y los suma, dando el resultado, 15. Esta multiplicación es necesaria ya que la función EXTRAE da como resultado textos, no valores numéricos.

Para reducir el resultado de dos dígitos a una única cifra tenemos que volver a aplicar la fórmula. Esto puede hacerse en otra celda

o anidando las funciones

Si el resultado del primer cálculo diera un número de tres cifras, la fórmula anidada sería demasiado larga. Por lo tanto dividir el cálculo en varias celdas parece ser la mejor solución.

Actualización: una solución más eficiente puede leerse en esta nota.

Para aplicar esta fórmula a letras, debemos primero convertirlas a sus equivalentes numéricos. Suponiendo que el número equivalente sea el número de orden en el alfabeto, podemos construir esta lista

Incluir el rango en un nombre ("alfabeto") y usar BUSCARV para calcular el valor de cada letra.

Por comodidad y para no complicar las fórmulas vamos a dividir el nombre en varias celdas poniendo cada letra en celda contiguas. Creamos una tabla de dos columnas por 20 filas; en la primer columna ponemos las letras del nombre y en la segunda columna calculamos el valor numérico de la letra con BUSCARV

Para ocultar los resultados #N/A y los bordes donde no hay letras usamos formato condicional

Para sumar los valores de cada letra usamos la fórmula matricial

={SUMA(SI(ESERROR(C2:C21),0,C2:C21))}

Y en las celdas G2 y F2 usamos las fórmulas con la función SUMAPRODUCTO que mostramos más arriba.

Finalmente podemos ocultar las columnas G y H y mostrar el resultado final

Así nos enteramos que Jorge vale 3, sobre cuyo significado no tengo la más pálida idea.

Contar condicional de números con celdas en blanco

En ciertas situaciones Excel puede llevarnos a cometer errores difíciles de detectar. Este ejemplo es la “vida real”, es decir, de un compañero de trabajo.

Supongamos que tenemos un rango de nueve celdas, cinco de las cuales contienen números y las cuatro restantes en blanco

La fórmula =SUMA(B2:B10) da como resultado 150. Es decir, las celdas en blanco son consideradas ceros o ignoradas por la función SUMA.

Lo mismo sucede si usamos la función CONTAR para contar cuántos números hay en el rango. =CONTAR(B2:B10) da como resultado 5. Lo mismo con función CONTARA.

Todo esto nos puede llevar a pensar que si queremos contar cuántos números distintos de cero hay el rango, todo lo que tenemos que hacer es usar la función CONTAR.SI de esta manera

=CONTAR.SI(B2:B9,"<>0")

Esta fórmula da como resultado 9. Este resultado es un tanto extraño. Veamos qué pasa si creamos una columna auxiliar con la fórmula =B2<>0 (que copiamos a los largo del rango)

Vemos que obtenemos cinco VERDADERO y cuatro FALSO. Es decir, podríamos esperar que el resultado de CONTAR.SI en nuestro caso sea 5, pero como vemos es 9.

En nuestro caso, es muy fácil percibir que el resultado no es el esperado, pero si analizamos un rango grande, digamos mil celdas, es más que probable que aceptemos el resultado como correcto.

En caso de tener que contar condicionalmente números en un rango que contiene o puede contener celdas en blanco, podemos usar alguna de estas soluciones:

# usar la función CONTAR.BLANCO para “corregir el resultado de CONTAR.SI

=CONTAR.SI(B2:B10,"<>0")-CONTAR.BLANCO(B2:B10)

# usar la fórmula matricial

={SUMA(--(B2:B10<>0))}

(recordemos una vez más que las fórmulas matriciales deben ser introducidas apretando simultáneamente Ctrl+Mayúsculas+Enter)

# usar la función SUMAPRODUCTO:

=SUMAPRODUCTO(--(B2:B10<>0))

Technorati Tags: MS Excel

Sumar y contar con varias condiciones en Excel:SUMAR.SI, CONTAR.SI y SUMAPRODUCTO

El tema de hoy: sumar y contar con más de una condición. Ya he escrito sobre este tema, pero mi intención hoy es dar una idea general del método.
Excel ofrece dos funciones para sumar o contar condicionadamente: SUMAR.SI (SUMIF en la versión inglesa) y CONTAR.SI (COUNTIF). Estas funciones aceptan sólo una condición y su uso es sencillo.

La cuestión es cómo sumar o contar cuándo deben cumplirse varias condiciones.
Veamos un ejemplo sencillo de contar con una condición. Tenemos una lista de nombres, sexo y edades, y queremos saber cuántas personas hay en la lista mayores de 30 años.



Como pueden ver, la formula en la celda C13 es bien sencilla: =CONTAR.SI(C2:C11,">30")

Pero digamos que ahora queremos saber cuántas personas mayores de 30 años y de sexo masculino hay en la lista. La función CONTAR.SI no acepta dos condiciones. La solución es utilizar fórmulas matriciales o SUMAPRODUCTO como ya he mostrado.
Cómo funciona esto? Cuando Excel evalúa una expresión de tipo A1= B1 puede dar uno de dos resultados: FALSO (FALSE en inglés) o VERDADERO (TRUE). Falso está asociado con la cifra 0 (cero) y VERDADERO con el 1 (uno).
Cuando establecemos la condición "mayor de 30" Y "masculino", la palabra Y está asociada a la operación de multiplicar.
Como ejemplo, he escrito en el rango de celdas D2:D11 la fórmula =Y(C2>30,B2="masculino")



Ahora he agregado en el rango E2:E11 la formula =(C2>30)*(B2="masculino")



Como se puede ver, si sumamos todo el rango E2:E11 obtendremos el resultado 2, que es el número de personas que cumplen con las dos condiciones.
En lugar de efectuar la operación en dos etapas, primero evaluar cada miembro y luego sumar los resultados, podemos hacerla de una vez usando la función SUMAPRODUCTO de esta manera:
=SUMAPRODUCTO(((C2:C11)>30),((B2:B11)="masculino"))


la multiplicación por 1 dentro del paréntesis se hace para convertir los valores FALSO y VERDADERO a valores numéricos (0 y 1).

Otra alternativa es usar la función SUMA en una fórmula matricial, esto es, ingresándola apretando al mismo tiempo Control+Shift+Enter.

La fórmula es: ={SUMA(((C2:C11)>30)*((B2:B11)="masculino")*1)}



De esta manera podemos combinar varias condiciones que deben cumplirse simultáneamente.

En el caso de condiciones incluyentes, digamos, personas mayores de 40 o menores de 30, usaremos el operador "+" (sumar) en ligar de "*" (multiplicar):
={SUMA((((C2:C11)<30)+((c2:c11)>40))*1)}





Categorías: Funciones&Formulas_, Formulas Matriciales_

La función SUMAPRODUCTO, una explicación básica

En una nota anterior he mostrado como calcular un promedio ponderado utilizando la función SUMAPRODUCTO (SUMPRODUCT en la versión inglesa de Excel), sin entrar en demasiado detalle sobre cómo funciona.
Esta función se comporta como las funciones matriciales, pero sin la necesidad de pulsar Ctrl+Shift+Enter para anotarla.
Con SUMAPRODUCTO se pueden hacer de una vez cálculos que de otra manera habría que realizar en varios pasos.
La forma más sencilla de explicar esta función es con un ejemplo. Esta función multiplica vectores (Excel en castellano los llama matrices), que son listas de valores que se encuentran en distintos rangos. La condición necesaria es que todos los rangos tengan el mismo "tamaño".
Así, si tenemos en una hoja una lista de productos en la columna A, los precios de estos productos en la columna B y las cantidades vendidas en al columna C, la fórmula =SUMAPRODUCTO(B2:B11,C2:C11) nos dará como resultado el total vendido.

Sin esta función tendríamos que calcular cada producto por separado y luego sumarlos en otra celda. Es decir, 10 fórmulas para multiplicar y otra para sumar las multiplicaciones, en lugar de una única fórmula.

Pero con SUMAPRODUCTO se puede hacer mucho más. Por ejemplo, contar cuántos productos de nuestra lista hay entre dos precios determinados, o cuántos productos han producido ventas por encima de cierto monto. Estas técnicas los mostraré en la próxima nota.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: SUMAPRODUCTO, SUMPRODUCT

Categorías: Funciones&Formulas_, Formulas Matriciales_