tutorial 3 excel avanzado

Escuela Nacional de Estadística e Informática

EXCEL AVANZADO

Cajamarca – Perú

2014

Instituto Nacional de Estadística e Informática Escuela Nacional de Estadística e Informática

Excel Avanzado

[email protected] Numero de Pagina: 2 Total de Paginas:31

CONSOLIDAR DATOS EN EXCEL

Imagina que después de recopilar la información sobre las ventas de nuestros productos en diferentes países tenemos un libro de Excel donde cada hoja tiene la información de un mes en específico.

Necesito consolidar la información en una sola hoja para poder crear mi reporte, pero ¿cómo puedo hacerlo de una manera sencilla? Para consolidar datos en Excel tenemos el comando Consolidar que nos ayuda a obtener el resultado deseado de una manera fácil. Supongamos que la información de las 3 hojas de Excel es la siguiente.

Comando Consolidar en Excel

Para consolidar los datos crearé una nueva hoja y haré clic en el botón Consolidar que se encuentra en la ficha Datos dentro del grupo Herramientas de datos.


Excel Avanzado


Se mostrará el cuadro de diálogo Consolidar en donde se deberá especificar cada uno de los rangos de las diferentes hojas. Puedes hacer clic en el botón de selección de referencia para seleccionar los rangos fácilmente.

Una vez especificada la referencia se debe pulsar el botón Agregar. De la misma manera se deben agregar cada una de las hojas hasta tener todas las referencias enlistadas. Finalmente debo marcar los cuadros de selección de rótulos en la Fila superior y en Columna Izquierda de manera que los datos sean presentados adecuadamente.


Excel Avanzado


Al pulsar el botón Aceptar, Excel realizará la consolidación de los datos y los colocará en la nueva hoja de Excel.


Excel Avanzado


De esta manera la información que antes estaba en diferentes hojas de Excel ha sido consolidada correctamente en una sola hoja.

LA FUNCIÓN SUBTOTALES EN EXCEL

La función SUBTOTALES en Excel nos ayuda a calcular el subtotal de una lista de valores. Lo interesante es que podemos indicar a la función SUBTOTALES el tipo de operación que deseamos aplicar sobre los valores.

Sintaxis de la función SUBTOTALES

Núm_función (obligatorio): Valor numérico (del 1 al 11) que indica el tipo de función que debe ser utilizada en el cálculo como SUMA, CONTAR, PROMEDIO, etc.

Ref1 (obligatorio): El rango que contiene los valores. Ref2 (opcional): Un segundo rango con valores adicionales a considerar. Este

argumento es opcional y hasta el argumento 254 para proveer rangos adicionales.

A continuación la tabla de valores numéricos que indican las operaciones que deseamos realizar con la función SUBTOTALES:


Excel Avanzado


Ejemplo de la función SUBTOTALES

Tengo una lista de valores en el rango A1:A10 y deseo utilizar la función SUBTOTALES para calcular el promedio para lo cual utilizaré la siguiente fórmula:

Observa que al especificar el número 1 como el primer argumento de la función SUBTOTALES estamos indicando que se utilice la función PROMEDIO. Si por otro lado quisiera obtener la suma de dos listas de números puedo utilizar la función SUBTOTALES de la siguiente manera:


Excel Avanzado


Filas ocultas con la función SUBTOTALES

Un posible inconveniente de la función SUBTOTALES es que al momento de ocultar alguna de las filas que tienen valores incluidos en la operación se tiene un impacto en el resultado. En la siguiente imagen puedes observar un ejemplo donde hago una cuenta de los elementos del rango A1:A10.

Sin embargo, si oculto las filas 4 y 5, la función SUBTOTALES no refleja ningún cambio y sigue tomando en cuenta las filas ocultas:


Excel Avanzado


Si queremos pasar por alto los valores ocultos podemos hacer uso de un identificador de función diferente para el primer argumento de la función SUBTOTALES:

Para el ejemplo anterior, si utilizamos el valor 102 en lugar de 2 como el primer argumento de la función SUBTOTALES obtendremos el siguiente resultado:


Excel Avanzado


En este caso la función SUBTOTALES ha pasado por alto los valores de las filas ocultas. Es importante mencionar que esta funcionalidad de no tomar en cuenta los valores ocultos solamente funcionará con filas ocultas y no con columnas ocultas.

Recuerda que la función SUBTOTALES en Excel siempre aplicará la función indicada sobre los rangos de celdas proporcionados como argumentos.

SUBTOTALES EN EXCEL

Cuando tienes una tabla de datos con una gran cantidad de información, los subtotales en Excel nos pueden ayudar a comprender e interpretar mejor la información. Excel permite agregar subtotales de una manera muy sencilla.

Supongamos la siguiente tabla de datos de donde quiero obtener los subtotales de ventas por cada mes:


Excel Avanzado


Lo primero que debo hacer es ordenar los datos por la columna sobre la cual se obtendrán los subtotales. Para este ejemplo ordenaré los datos por la columna Mes. La columna se ordena haciendo clic en cualquier celda de la columna y posteriormente seleccionado el comando Ordenar de más antiguos a más recientes que se encuentra dentro de la ficha Inicio.

La tabla quedará ordenada por mes.


Excel Avanzado


El botón Subtotal en Excel

Para realizar la inserción de los subtotales en Excel debo pulsar el comando Subtotal que se encuentra en la ficha Datos dentro del grupo Esquema.

Excel mostrará el cuadro de diálogo Subtotales.

Me debo asegurar de que la primera lista desplegable tenga seleccionada la opción Mes ya que indica que para cada cambio de


Excel Avanzado


mes se insertará un subtotal. Además utilizaré la función Suma y la columna Ventas que aparecen seleccionadas. Al hacer clic en Aceptar se insertarán los subtotales.

Observa cómo Excel ha insertado una fila nueva que contiene el subtotal para cada mes. Además a la izquierda de la hoja de cálculo Excel coloca controles adicionales que son útiles para ocultar o mostrar los grupos de datos de acuerdo a los subtotales. Al hacer clic en alguno de ellos el grupo correspondiente se expande o se contrae:


Excel Avanzado


ANALISIS Y SI O ANALISIS DE HIPOTESIS

Evaluando escenarios con Excel

Los escenarios nos permiten analizar un resultado generado en base a un conjunto de celdas variables. Los escenarios en Excel permiten un máximo de 32 variables, pero podemos crear tantos escenarios como sea necesario.

Para este ejemplo utilizaré el caso de un préstamo personal en donde me interesa conocer la cantidad que debo pagar dependiendo el plazo elegido. Para ello utilizo la función PAGO y en las celdas superiores he colocado los argumentos de dicha función de la siguiente manera:


Excel Avanzado


El Administrador de escenarios en Excel

Ahora me interesa saber cómo cambia la mensualidad si cambio el plazo de pago. Para iniciar con la creación de escenarios debo pulsar el botón Análisis Y si que se encuentra en la ficha Datos y dentro de las opciones mostradas seleccionar

Administrador de escenarios.

Al seleccionar esta opción se mostrará el cuadro de diálogo Administrador de escenarios y lo primero que debemos hacer es pulsar el botón Agregar para mostrar el cuadro de diálogo Agregar escenario.

En este cuadro de diálogo comenzaré por asignar un nombre a mi escenario, que en este caso será 12 Meses. En el cuadro de texto Celdas cambiantes debo seleccionar aquellas celdas que afectan el resultado de la fórmula PAGO, que es el rango $B$1:$B:$3. Al oprimir el botón Aceptar se mostrará un nuevo cuadro de diálogo llamado Valores del escenario que me permitirá ingresar los valores específicos del escenario recién creado. Es posible cambiar todos los valores, pero para este ejemplo solamente modificaré el plazo que tendrá el valor 12:


Excel Avanzado


Ya que voy a agregar escenarios adicionales oprimiré el botón Agregar y Excel mostrará de nueva cuenta el cuadro de diálogo Agregar escenario y volveré a repetir estos mismos pasos para crear nuevos escenarios para los plazos de 18, 36 y 48 meses. Una vez que he terminado de crear el último escenario debes pulsar el botón Aceptar para regresar al Administrador de escenarios donde podrás ver una lista de todos los

escenarios creados:

Probar los escenarios creados

Ya con los escenarios creados será suficiente seleccionar alguno de ellos y oprimir el botón Mostrar para aplicar los valores del escenario al resultado de la fórmula PAGO. Observa cómo va cambiando el resultado de la celda B4 de acuerdo al escenario elegido:


Excel Avanzado


Para este ejemplo solamente he modificado los valores del plazo en cada escenario, sin embargo se podrían crear escenarios donde cambie tanto el plazo como la tasa de interés. Las combinaciones posibles son muchas y solamente estarán restringidas a tus necesidades de análisis.

Una desventaja de los escenarios es que tienes que crear manualmente cada uno de ellos y podría llegar a ser un trabajo muy laborioso. En caso de que tuvieras que realizar un análisis de dos variables te recomiendo considerar la opción de Tablas de datos ya que evitará la creación manual de escenarios.

BUSCAR OBJETIVO

Excel 2010 incluye varias herramientas de análisis de datos y Buscar objetivo es una técnica utilizada para encontrar fácilmente el número que cumple las condiciones necesarias para alcanzar un objetivo.

Esta herramienta te ayudará a hacer muchas pruebas de valores en una fórmula hasta encontrar el valor exacto que cumpla con las condiciones establecidas. Es decir, si sabemos el resultado de una fórmula, pero no estamos seguros del valor de uno de sus argumentos, entonces Buscar objetivo nos ayudará a probar


Excel Avanzado


diversos escenarios hasta encontrar el valor exacto que necesitamos. Por ejemplo, supongamos que tenemos el siguiente problema.

Tengo un número que multiplicado por otro número X me deberá dar el resultado mostrado. Aunque este es un problema muy fácil de resolver matemáticamente será de utilidad para mostrar la funcionalidad de Buscar objetivo. Empezaré por definir una fórmula importante. En la celda C3 colocaré la fórmula que se deberá cumplir para obtener el resultado deseado, es decir, =B1*B2 deberá dar como resultado 439482.

Por ahora el resultado en C3 será cero, porque no hay valor en la celda B2 sin embargo, al momento de ir buscando el valor adecuado para B2, el valor de la celda C3 llegará a ser el mismo que B3 cuando se haya encontrado el valor correcto.

El comando Buscar objetivo

Ahora deberás hacer clic en la ficha Datos y seleccionar Análisis Y si donde se desplegará un menú de opciones y deberás elegir Buscar objetivo.


Excel Avanzado


De inmediato se mostrará el cuadro de diálogo Buscar objetivo donde deberás llenar los siguientes cuadros de texto.

Definir la celda. Es la celda que contiene la fórmula Con el valor. Este es el número que deseamos como resultado de la fórmula. Para cambiar la celda. La celda que será modificada hasta tener el resultado

deseado.

Una vez que se han establecido los parámetros debes hacer clic en el botón Aceptar y Excel comenzará a realzar los cálculos necesarios. Cuando haya encontrado un resultado adecuado se mostrará el cuadro de dialogo Estado de la búsqueda de objetivo.

Este cuadro de diálogo simplemente notifica que se ha llegado al objetivo modificando la celda especificada. Al cerrar el cuadro de diálogo podrás observar el resultado. Para nuestro ejemplo Excel fue haciendo diversas pruebas hasta llegar al resultado de 520.0970 que es el número que multiplicado por 845 nos dará el resultado esperado.


Excel Avanzado


TABLA DE DATOS

Las tablas de datos forman parte de las herramientas de Análisis Y si que nos permiten cambiar el valor de algunas celdas para ver cómo es afectado el resultado original. Una tabla de datos analiza un conjunto de valores y determina posibles resultados.

Tabla de datos de una variable

El ejemplo más sencillo de una tabla de datos es aquél que utiliza una variable para realizar los cálculos. Supongamos el siguiente escenario:

En este ejemplo estoy realizando una proyección de ventas para el año 2011 basándome en las ventas del año 2010 y esperando una tasa de crecimiento del 2.5%. Lo que deseo hacer es saber cuál sería la proyección de ventas para el 2011 si la tasa de crecimiento fuera diferente. Para hacer este análisis colocaré las tasas de crecimiento que deseo utilizar de la siguiente manera:


Excel Avanzado


Para crear la tabla de datos debo seleccionar el rango de celdas tal como se muestra en la imagen anterior y entonces ir a la ficha Datos, y dentro del grupo Herramientas de datos pulsar el botón Análisis Y si para posteriormente seleccionar la opción Tabla de datos.

Se mostrará el cuadro de diálogo Tabla de datos y en la caja de texto Celda de entrada (columna) se debe seleccionar la celda B2 que es la celda que contiene el porcentaje de crecimiento.


Excel Avanzado


Al hacer clic en el botón Aceptar se llenarán las celdas contiguas a las tasas de crecimiento con el valor de la proyección de ventas correspondiente a cada una de las tasas.


Excel Avanzado


Excel ha creado la tabla de datos en el rango B5:B13 y de esta manera puedo analizar las diferentes proyecciones de ventas para una tasa de crecimiento diferente. Una vez que he terminado de analizar la información, si intento eliminar alguna de las celdas pertenecientes al rango de la tabla de datos, Excel desplegará un mensaje advirtiendo que no se puede cambiar parte de una tabla de datos. Si deseas eliminar la tabla de datos deberás primero seleccionar el rango completo antes de oprimir la tecla suprimir.

TABLA DE DATOS DE DOS VARIABLES

En el artículo anterior mostré los beneficios de las tablas de datos como una opción de Análisis Y si. En esta ocasión revisaremos un ejemplo clásico sobre el uso de una tabla de datos para realizar un análisis de dos variables.

El escenario es el siguiente. Supongamos que quiero solicitar un préstamo personal a un banco, estoy haciendo una investigación con varios bancos y cada uno de ellos me ha dado una tasa de interés diferente así como diversos plazos de pago. El primero de los casos que investigué es el siguiente:

Tabla de datos de dos variables

En lugar de hacer el cálculo para cada opción de tasa de interés y plazo, acomodaré los datos adecuadamente para formar una tabla de datos. En las filas especificaré las diferentes tasas de interés y en las columnas los posibles plazos.


Excel Avanzado


Para crear la tabla de datos de dos variables seleccionaré el rango B4:G11 y debo ir a la ficha Datos y pulsar el botón Análisis Y si para seleccionar la opción Tabla de datos. Dentro del cuadro de diálogo deberé elegir cada una de las celdas de entrada para la tabla de datos de la siguiente manera:

Al hacer clic en el botón Aceptar se generará la tabla de datos correspondiente:


Excel Avanzado


En esta tabla de datos podemos observar lo que esperábamos, entre mayor es el plazo los pagos mensuales se reducen y entre mayor es la tasa de interés el pago mensual se incrementa. Con esta información puedo analizar y elegir adecuadamente la opción que más se acomoda a mis posibilidades de pago mensual.

Las tablas de datos son una herramienta que nos ayuda en el análisis de nuestra información, la única desventaja es que solamente se puede hacer el análisis de dos variables como máximo. En caso de que te encuentres en una situación que requiera de un análisis de tres o más variables recomiendo utilizar los Escenarios o también utilizar Excel Solver.

ACTIVAR EXCEL SOLVER

Solver es un complemento de Excel que nos ayuda a trabajar con modelos de negocio y nos permite resolver problemas lineales y no lineales. En esta ocasión mostraré cómo activar este complemento en Excel 2010.

Cómo activar Solver en Excel

Solver está incluido dentro de Excel pero se encuentra desactivado de manera predeterminada. Para poder habilitarlo debes ir a la ficha Archivo y elegir Opciones y se mostrará el cuadro de diálogo Opciones de Excel donde deberás seleccionar Complementos.


Excel Avanzado


En el panel derecho encontrarás el complemento llamado Solver. Para activarlo debes hacer clic en el botón Ir de la sección Administrar.


Excel Avanzado


Se mostrará el cuadro de diálogo Complementos y deberás marcar la casilla de verificación de Solver y aceptar los cambios.

Para utilizar el complemento Solver debes ir a la ficha Datos y Excel habrá creado un nuevo grupo llamado Análisis el cual contendrá el comando Solver.

Al hacer clic sobre ese comando se mostrará el cuadro de diálogo Parámetros de Solver el cual nos permitirá configurar y trabajar con el complemento recién instalado.


Excel Avanzado


En el próximo artículo mostraré un ejemplo práctico sobre cómo utilizar este complemento de Excel en nuestro análisis de datos.

UTILIZANDO EXCEL SOLVER

Si tienes la necesidad de realizar un pronóstico que involucra más de una variable, puedes utilizar Solver en Excel. Este complemento ayudará a analizar escenarios de negocio multivariable y de optimización.


Excel Avanzado


Ejemplo de uso de Solver

El ejemplo es el siguiente. Tengo un establecimiento de venta de pizzas que ofrece dos tipos de pizza tradicionales, Pepperoni ($30) y Vegetariana ($35) además de la pizza especial Suprema ($45). No sabemos cuál es el potencial de ingresos del establecimiento y tampoco el énfasis que se debería de dar a cada tipo de pizza para maximizar las ventas.

Antes de realizar el análisis debemos considerar las siguientes condiciones. Dada nuestra capacidad de producción solamente podemos elaborar 150 pizzas al día. Otra condición es que no podemos exceder de 90 pizzas tradicionales (Pepperoni y Vegetariana) y además, al no haber muchos vegetarianos en el área, estimamos vender un máximo de 25 pizzas vegetarianas al día. Otra condición a considerar es que solamente podemos comprar los ingredientes necesarios para producir 60 pizzas Suprema por día.

Con esta información elaboraré la siguiente hoja de Excel:

Observa que en los datos están representadas todas las reglas de negocio del establecimiento. Para cada tipo de pizza he colocado el total de pizzas a vender (por ahora en cero), el subtotal de cada una, así como el total de ventas que esta formado por la suma de los subtotales. Además bajo el título Restricciones he colocado las condiciones previamente mencionadas.

Algo muy importante es establecer las equivalencias para las restricciones. Por ejemplo, una restricción es que el total de pizzas no puede exceder de 150, pero


Excel Avanzado


Excel no necesariamente sabe lo que significa “Total de pizzas”, así que he destinado una celda para especificar que el total de pizzas es la suma de las celdas B2+B6+B10. Lo mismo sucede para explicar lo que significa Pizzas Tradicionales.

Los datos ya están listos para utilizar Solver, así que debes ir a la ficha Datos y hacer clic en el comando Solver donde se mostrará el cuadro de diálogo Parámetros de Solver.


Excel Avanzado


En nuestro ejemplo lo que queremos maximizar son las ventas totales por lo que en el cuadro de texto Establecer objetivo está especificada la celda $E$1 y por supuesto seleccioné la opción Máx. El otro parámetro importante son las celdas de variables que en nuestro ejemplo son las pizzas a vender para cada uno de los diferentes tipos.

Finalmente observa cómo en el cuadro de restricciones están reflejadas las condiciones de venta del establecimiento. Pon especial atención a la manera en que se han utilizado las equivalencias que son las celdas $E$10 y $E$11.

Todo está listo para continuar. Solamente debes hacer clic en el botón Resolver y Excel comenzará a calcular diferentes valores para las celdas variables hasta encontrar el valor máximo para las ventas totales. Al término del cálculo se mostrará el cuadro de diálogo Resultados de Solver.

Solamente haz clic en Aceptar para ver los resultados en la hoja de Excel.


Excel Avanzado


Excel ha hecho los cálculos para saber que, con las restricciones establecidas, tendremos un valor máximo de venta total de $5,525. Ahora fácilmente podrías cambiar los valores de las restricciones y volver a efectuar el cálculo con Solver para observar el comportamiento en las ventas.

tutorial 3 excel avanzado

Documents