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
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
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
