Función SUMAPRODUCTO en Excel (SUMPRODUCT) cuando los rangos tienen distinta orientación (función TRANSPONER).

En notas anteriores vimos una explicación básica de la función SUMAPRODUCTO y como utilizar SUMAPRODUCTO para contar condicional con varias condiciones.
El uso de SUMAPRODUCTO supone que se cumplen dos condiciones obligatorias:
1 – Los rangos son del mismo tamaño, es decir tienen la misma cantidad de miembros
2 – Los rangos tienen la misma orientación, filas o columnas.

A veces sucede que una de las matrices es un rango en una columna y otra matriz es un rango en una fila.

Para sobreponerse a este problema, sin tener que rehacer las hojas de cálculo, podemos utilizar la función TRANSPONER.

La definición de la función TRANSPONER en la ayuda de Excel es la siguiente:

Devuelve un rango de celdas vertical como un rango horizontal o viceversa.
TRANSPONER debe introducirse como una fórmula matricial en un rango que tenga el
mismo número de filas y columnas, respectivamente, que el número de columnas y
filas en una matriz. Utilice TRANSPONER para cambiar la orientación vertical y
horizontal de una matriz en una hoja de cálculo.

Es importante notar que se trata de una función matricial (array function).

Veamos el uso de TRANSPONER con SUMAPRODUCTO con un ejemplo (apretar el link para decargar el cuaderno)

Supongamos que tenemos esta tabla en una hoja de cálculos



Aquí el cálculo del total del inventario es sencillo, ya que las dos matrices (cantidad y precios) están orientadas en el mismo sentido
=SUMAPRODUCTO(B4:B13,C4:C13)
Pero supongamos que la matriz de Precios esté en un rango en una fila (en nuestro ejemplo en el rango A17:K17). En este caso usamos la función TRANSPONER para convertir la matriz de precios de fila a columna. La fórmula es
={SUMAPRODUCTO(B4:B13,TRANSPONER(B17:K17))}
Debemos prestar atención a los símbolos "{" y "}" al principio y al final de la fórmula. Ya que TRANSPONER es una función matricial, SUMAPRODUCTO debe ser anotada como tal, es decir apretando Ctrl+Shift junto con Enter.
Ya que hemos convertido a SUMAPRODUCTO en una función matricial, podemos utilizar la función SUMA, en forma matricial, de la siguiente manera:
={SUMA((B4:B13)*TRANSPONER(B17:K17))}










Categorías: Funciones&Formulas_, Formulas Matriciales_


Technorati Tags: SUMAPRODUCTO, TRANSPONER

Como crear un histograma en MS Excel

Supongamos una lista de nombres y sus correspondientes edades.



Si queremos agrupar los nombres en la lista de acuerdo a ciertos intervalos de edad y contar cuantos miembros hay en cada intervalo, podemos utilizar formulas de Excel. Pero una alternativa más práctica es utilizar la función Histograma del complemento Análisis ToolPak. Para utilizar esta función tenemos que tener instalado el complemento Análisis ToolPak.
Para instalarlo hay que activar el menú Herramientas--->Complementos y chequear la opción Análisis ToolPak



Una vez instalado el complemento, el menú Data Analysis aparecerá en el menú Herramientas.
Veamos como crear un histograma en Excel usando Data Analysis con un ejemplo (cliquear el link para descargar el archivo).

El primer paso es crear un "bin range", esto es, un rango que contiene los valores límites de los intervalos.



Luego activamos el menú Data Analysis y elegimos la función Histogram



En el diálogo que se abre, entramos los rangos de los datos y del "bin range" así como las definiciones de resultado (ubicación de la tabla en la misma hoja de cálculo o en otra hoja, gráficos, etc.).



Finalmente apretamos "OK" y casi inmediatamente Excel producirá una tabla de frecuencias.







Categorías: Manejo de Datos_, Varios_

Technorati Tags: Histograma

Excel - Agregando controles en hojas de cálculo - Nota II

En la entrada Excel - Agregando controles en hojas de cálculo vimos como insertar controles directamente en la hoja de cálculos. De esta manera podemos disfrutar de la funcionalidad de estos controles sin ningún tipo de programación (macros – Vba).
El uso de controles no sólo da un aspecto "profesional" a los modelos que desarrollamos en las hojas de cálculo, sino que también nos permite controlar el ingreso de datos y el uso de las fórmulas.
En el ejemplo de esta entrada veremos como usar los controles "barra de desplazamiento" y "control de número".
Los principios generales para el uso de controles son:
1 – activar la barra "Formularios"
2 – Insertar el control requerido en la ubicación deseada
3 – Abrir el menú "formato de control". Para hacer esto hay que activar/seleccionar el control y apretar Ctrl+1 (o el menú Formato--->Control)

A partir de aquí veremos las definiciones de los controles del ejemplo. Nuestro ejemplo es un modelo sencillo para calcular el pago de un crédito dados los siguientes datos: tasa de interés anual, término del crédito en años, y monto del crédito:

Para "controlar" los valores del término, utilizamos el control "control de números"; para controlar los valores del monto del préstamo utilizamos el control "barra de deslazamiento".
Las definiciones son las siguientes:

Control de números: controla el número de períodos

Las definiciones son evidentes. Hay que prestar atención a la definición "Vincular con celda". En esta celda aparece el dato elegido por el usuario al apretar el control. Esta celda es uno de los argumentos en la función que calcula el monto del pago.

Barra de desplazamiento: controla el monto del informe


También aquí las definiciones son evidentes. Como en el caso anterior los resultados de activar el control son registrados en la celda C10, la cual funciona como argumento en la fórmula que controla el cálculo del monto del pago:

=PAGO(C8,C9,-C10)



Categorías: Funciones&Formulas_, Varios_

Technorati Tags: Controles en Excel