Uso de la función SUBTOTALES de MS Excel

Ya hemos hablado sobre el uso del comando Subtotales del menú Datos. Por lo general, esta es la forma más sencilla y segura de utilizar la función SUBTOTALES para consolidar datos.
Pero la función tiene algunas características que la tornan muy útil, en especial en combinación con el comando AUTOFILTRO.
La sintaxis de la función SUBTOTALES es la siguiente:

SUBTOTALES(núm_función;ref1,ref2,...)

Donde núm_función es el argumento que determina que tipo de operación efectuará SUBTOTALES

1 - PROMEDIO
2 - CONTAR
3 - CONTARA
4 - MAX
5 - MIN
6 - PRODUCTO
7 - DESVEST
8 - DESVESTP
9 - SUMA
10 - VAR
11 - VARP

Una característica importante de SUBTOTALES es que la función pasa por alto las filas ocultas. Esto es importante cuando sólo desea obtener el subtotal de los datos visibles que resulta de una lista filtrada.

En todos los ejemplos que aparecen en el archivo con ejemplos de la función SUBTOTALES, se puede apreciar la importancia de esta característica. Por ejemplo, si aplicamos AUTOFILTRO a una lista de ventas por productos, podemos obtener un total de cada producto al ocultar los restantes con el filtro usando la función SUMA (9). Lo mismo se puede hacer con PROMEDIO (1) y demás funciones.
Por ejemplo, si tenemos esta lista de edades de empleados por departamentos



Aplicando PROMEDIO (1), al auto filtrar la lista podemos ver la edad promedio de cada departamento:



Un uso interesante es con CONTARA (3). En el archivo podemos ver un ejemplo donde cada vez que filtramos la lista, el número de orden se mantiene correlativo. La función utilizada en la celda A7 es =SUBTOTALES(3,$C$7:C7) que luego copiamos al resto del rango.
Prestar atención a la notación del rango en el segundo argumento de la función: el primer miembro del rango ($C$7) es una referencia absoluta, mientras que el segundo es una referencia relativa que cambia al copiar la función a lo largo del rango.

Categorías: Funciones&Formulas_

Technorati Tags: SUBTOTALES, SUBTOTALS

Usar la función BUSCARV(VLOOKUP) de MS Excel con varios criterios.

Por lo general usamos la función BUSCARV(VLOOKUP) de MS Excel con un criterio de búsqueda, es decir, la búsqueda se efectúa a lo largo de una única columna.
Pero existen situaciones en las cuales debemos efectuar la búsqueda en más de una columna.
En este ejemplo del uso de BUSCARV con más de una columna de búsqueda (enlace para descargar el archivo), tenemos una tabla donde vemos las ventas de ciertos agentes por producto (una columna para cada producto):




En la celda B18 tenemos una fórmula que nos permite extraer las ventas de cada agente por producto
=BUSCARV(B16,$A$6:$D$13,COINCIDIR(B17,$B$5:$D$5,0)+1,0)



La fórmula combina las funciones BUSCARV (VLOOKUP) y COINCIDIR (MATCH). La función COINCIDIR le pasa a BUSCARV el valor del argumento "indicador de columnas", de acuerdo al valor presente en la celda B17 (aquí hemos utilizado validación de datos para generar una lista). La fórmula funciona de la siguiente manera:









Categorías: Funciones&Formulas_, LOOKUPS_

Technorati Tags: BUSCARV, VLOOKUP

Usar la función BUSCARV (VLOOKUP) de Excel con más de una tabla de referencia.

Hay situaciones en las cuales debemos usar la función BUSCARV (VLOOKUP) de MS Excel con más de una tabla de referencia. Es decir, necesitamos una fórmula que nos permita, de acuerdo a algún parámetro del valor buscado, decidir realizar la búsqueda en distintas matrices.
Para dar un ejemplo del uso de BUSCARV (VLOOKUP) con más de una tabla de referencia (para descargar el archivo apretar el enlace), veamos el caso de una empresa que paga comisiones a sus agentes de ventas de acuerdo al volumen de ventas y a la antigüedad del agente en la empresa.
En nuestro ejemplo, hay una tabla de comisiones para agentes con una antigüedad menor a 5 años, y otra para aquellos con más de 5 años en la empresa:



El informe mensual de ventas es el siguiente:



Para determinar el monto de la comisión que le corresponde a cada agente, de acuerdo al monto de las ventas y a la antigüedad, usamos la siguiente fórmula:

=BUSCARV(C6,SI(B6<=5,$A$15:$B$20,$D$15:$E$20),2)


La fórmula funciona anidando dentro de la función BUSCARV una función SI (IF) que de acuerdo al resultado dirija la búsqueda a una u otra tabla de referencia. También se puede ver que he dejado el argumento "ordenado" (el cuarto argumento de la función BUSCARV) en blanco, para obtener una búsqueda aproximada, como lo he explicado en la entrada "Valor del argumento range_lookup en la función Vlookup".






Categorías: Funciones&Formulas_, LOOKUPS_
Technorati Tags: BUSCARV, VLOOKUP

Predicción de un valor a partir de valores conocidos usando la función PRONOSTICO (FORECAST) de MS Excel.

Excel's FORECAST function post in English

En la primera entrada de este blog, había explicado como encontrar la intersección de dos líneas en un gráfico de Excel. Para hacer esto utilizamos la opción "línea de tendencia" (trendline) y el SOLVER.

Con Excel podemos, dada una tabla de valores, predecir un valor futuro a partir de valores existentes utilizando la función PRONOSTICO (FORECAST) (apretar el enlace para descargar el archivo).

La descripción de la función PRONOSTICO de acuerdo a la ayuda online de MS Excel es la siguiente:

Calcula o pronostica un valor futuro a través de los valores existentes. La predicción del valor es un valor y teniendo en cuenta un valor x. Los valores conocidos son valores x y valores y existentes, y el nuevo valor se pronostica utilizando regresión lineal. Esta función se puede utilizar para realizar previsiones de ventas, establecer requisitos de inventario o tendencias de los consumidores.

La sintaxis es: PRONOSTICO(x;conocido_y;conocido_x), donde

X es el punto de datos cuyo valor desea predecir.

Conocido_y es la matriz o rango de datos dependientes.

Conocido_x es la matriz o rango de datos independientes.

En nuestro ejemplo tenemos una tabla que muestra cantidades vendidas a cada nivel de precios:



Los valores de "precios" corresponden al argumento Conocido_X; los valores de "cantidad" corresponden a Conocido_y.
Para calcular la cantidad prevista si el precio fuera 8, aplicamos la siguiente fórmula: =PRONOSTICO(A11,B4:B10,A4:A10)



También podemos usar la técnica sugerida en mi primer entrada, averiguando la ecuación de la línea de tendencia (trendline) en el gráfico, y luego aplicándola como fórmula en la hoja de cálculo.
Para ver la ecuación de la línea de tendencia, creamos el gráfico a partir de la tabla y luego abrimos el menú "agregar línea de tendencia", apretando el botón derecho del mouse sobre la curva del gráfico.
Una vez que aparece la línea de tendencia, abrimos el menú "formato línea de tendencia":
- en la pestaña "tipo" elegimos "lineal"
- en la pestaña "opciones", chequeamos "presentar ecuación en el gráfico"


Finalmente insertamos la función de la línea de tendencia como formula en una celda de la hoja de cálculo.


En nuestro ejemplo en anotamos =-417.86*B16+10771 en la celda B17), donde la celda B16 contiene el valor para el cual queremos pronosticar el resultado.






Categorías: Funciones&Formulas_
Technorati Tags: Función PRONOSTICO, FORECAST Function

Funciones Matriciales (Array Formulas) en Excel – Nota II

En una las primeras entradas que publiqué en este blog, di una explicación general sobre las fórmulas matriciales (array formulas) en Excel.
Esa entrada se limitaba a mostrar un aspecto del uso de las funciones matriciales. El objetivo de esta nota es ampliar la explicación sobre esta importante herramienta de Excel.
Una matriz es un conjunto ordenado de elementos. En términos de Excel, una matriz puede ser un rango contenido en una columna (o fila), que es una matriz unidimensional, o en un rango rectangular, lo que la convierte en una matriz bidimensional.
En el ejemplo de la explicación general sobre fórmulas matriciales, una matriz era el rango A2:A12 la otra el rango B2:B12. En ese ejemplo usamos la fórmula matricial para calcular un promedio ponderado. Este es uno de los usos posibles de las funciones matriciales, que llamaremos "unicelular", ya que le resultado de la fórmula aparece en una sola celda.
Las funciones matriciales de Excel también pueden usarse para obtener el resultado de un cálculo en varias celdas simultáneamente, lo que llamaremos fórmulas matriciales "multicelulares". Por ejemplo, podemos rescribir el ejemplo de la nota anterior de la siguiente manera:



Como pueden ver en el ejemplo (descargar el ejemplo de fórmulas y constantes matriciales aquí), la fórmula es la misma a lo largo del rango C6:C16, pero el resultado en cada celda es el producto de las celdas de la fila correspondiente.
Este tipo de fórmulas matriciales no parece tener ninguna ventaja sobre las fórmulas matriciales "unicelulares". Tal vez una de las ventajas sea que Excel no permite borrar parcialmente estas fórmulas, y por lo tanto es un buen método de proteger las fórmulas en las hojas de cálculo.

Un aspecto más interesante es la posibilidad de crear matrices constantes en Excel. Al crear una matriz de este tipo, los datos son almacenados en la memoria del computador, y no en rangos de la hoja de cálculo.
Para crear una matriz de constantes, escribimos los miembros de la matriz entre signos "{" y "}", separados por ";" (matriz horizontal) o por "\" (matriz vertical). Por ejemplo: {1;2;3;4;5} genera una matriz horizontal de cinco miembros


Aclaración: la ayuda de Excel en español sostiene que para generar una matriz orientada horizontalmente habrá que separar los miembros con comas; para generar una matriz vertical, la separación será hecha con punto y coma ";". En la versión de Excel de este ejemplo (XL 2002), los separadores al usar el lenguaje Español, son los indicados más arriba. Al abrir el mismo cuaderno con el lenguaje Inglés, Excel reemplaza los separadores por ";" y "\".
Una forma más cómoda de trabajar con matrices de constantes es asociarlas a un nombre, como he mostrado en mi entrada sobre uso de nombres en MS Excel. Por ejemplo, si creamos el nombre "Semana" que contenga una matriz con los días de la semana {"lunes","martes","miércoles","jueves", "viernes","sábado","domingo"}. Estas matrices pueden luego ser empleadas en fórmulas, tanto matriciales como fórmulas comunes.
Por ejemplo, si usamos el nombre "semana" que acabamos de definir en esta fórmula =INDEX(semana,2), obtenemos como resultado "martes".

Matrices, matrices nominadas y fórmulas matriciales, son elementos indispensables para convertirse en usuario avanzado de Excel.






Categorías: Formulas Matriciales_

Technorati Tags: Fórmulas Matriciales, Constantes Matriciales

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

Usar Excel BUSCAR (LOOKUP) para encontrar el último valor en un rango

Usar Excel BUSCAR (LOOKUP) para encontrar el último valor en un rango

Últimamente he estado participando en el foro Excel de http://www.exceluciones.com

Una de las preguntas que han surgido allí, y que aparecen a menudo en varios foros, es qué fórmula nos permitiría encontrar el último valor de un rango. La idea es que tenemos, por ejemplo, un rango en una columna donde vamos agregando valores, por ejemplo el rango A1:A20. Si agregamos un valor en la celda A21, queremos una fórmula que de cómo resultado el valor de A21.
La solución "clásica" es utilizar la función BUSCAR (LOOKUP), poniendo como argumento el número más elevado que Excel acepta en una celda (al valor 1E+307) y como segundo argumento el rango donde debemos encontrar el último valor (hay que prestar atención que estamos buscando el último valor en el sentido de orden y no de tamaño).
El funcionamiento de la función BUSCAR (LOOKUP) es tal que si el valor buscado es mas alto que cualquier valor disponible en el rango de la búsqueda esta función da como resultado el ultimo valor que encuentren.

En este ejemplo vemos que cuando el valor del primer argumento (1000) es mayor que el valor del último valor en el rango (172), la fórmula da como resultado 172. Si el valor del primer argumento de BUSCAR (LOOKUP) es, por ejemplo 126, el resultado de la fórmula será 118.

LOOKUPS_

Technorati Tags: BUSCAR, LOOKUP

Función JERARQUIA (RANK) en Excel – Nota adicional

En una entrada anterior he mencionado como usar la función JERARQUIA (RANK en la versión inglesa). Allí dimos la siguiente definición:

JERARQUIA (RANK): Devuelve la jerarquía de un número en una lista de números. La
jerarquía de un número es su tamaño en comparación con otros valores de la
lista. (Si ordenara la lista, la jerarquía del número sería su posición).

Pero a veces hay situaciones en las cuales dos miembros de la lista reciben el mismo ranking, como en este caso




Como podemos ver hay "empate" entre las celdas C5 y C6. Ambas reciben el ranking 14, y el 15 no existe.

Si queremos que en caso de "empate" el ranking se establezca por orden de aparición, podemos utilizar la siguiente fórmula:

=JERARQUIA(B4,sales)+CONTAR.SI($C$4:C4,C4)-1

Si copiamos esta fórmula en todo el rango de la columna D, veremos este resultado




En nuestra fórmula, la función CONTAR.SI (COUNTIF) evalúa cuantas veces aparece el número de ranking de la celda en el rango. Si aparece sólo una vez, el resultado de CONTAR.SI será 0 y el resultado será idéntico al de la fórmula original. Si el número de ranking ya figura en el rango, el resultado de la fórmula será incrementado en 1 por CONTAR.SI

El cuaderno con el ejemplo se puede descargar aquí


Categorías: Funciones&Formulas_,

Technorati Tags: RANK, JERARQUIA

Consolidación de datos con Tablas Dinámicas (Pivot Tables).

En anteriores entradas ya he tratado el tema de consolidación de datos en Excel:
1 – Consolidar Datos con Excel
2 – Consolidar datos con Subtotales
3 - Comparar listas con consolidación de datos

Otro método de consolidar datos en Excel es usando tablas dinámicas (pivot tables).
Consideremos el siguiente ejemplo (para descargar el archivo con el ejemplo apretar aquí). Tenemos un cuaderno Excel con una hoja por cada agente de ventas. En cada una de estas hojas anotamos las ventas por mes y por producto de cada agente.

Para consolidar las ventas de todos los agentes (en nuestro caso habrá sólo dos) en una sola hoja de cálculo usando tablas dinámicas, procedemos de la siguiente manera:
1 – Abrimos una hoja en blanco (en nuestro ejemplo será "consolidado") y activamos el menú Datos--->Informe de Gráficos y Tablas Dinámicos. En el diálogo que se abre elegimos la opción "rangos de consolidación múltiples"



2 – En el siguiente paso debemos instruir a Excel cuantos campos de página queremos. Para nuestro ejemplo elegimos "crear un solo campo de página"



3 – En el tercer paso debemos informar a Excel dónde se encuentran los rangos que contienen los datos. Hacemos esto apunto a los rangos en las hojas adecuadas (agente1 y agente2) y apretando el botón "agregar"



4 – Finalmente debemos decirle a Excel dónde ubicar la tabla dinámica (que consolida los datos)- En nuestro ejemplo la ubicamos en la celda A6



Apretamos "finalizar" y obtenemos la tabla dinámica.

Esta tabla muestra los totales para cada producto y por cada mes de todos los agentes de ventas. Si queremos ver los datos de un agente en particular apretamos el botón "Página 1" y elegimos entre "elemento 1" (agente 1) y "elemento 2" (agente 2).

Dado que los encabezamientos de las columnas tienen un formato de fecha, podemos agrupar los datos en trimestres en forma automática. Para hacer esto damos los siguientes pasos:
1 – Hacemos clic con el botón derecho del mouse en el encabezamiento del campo "columna", y apretamos "agrupar"



2 – Seleccionamos "trimestres" y cancelamos la selección de "meses" (con un clic del mouse).



3 – Apretamos "aceptar" y la tabla se convierte en un reporte trimestral




Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: Consolidación de Datos, Tablas Dinámicas