Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
1 Asignatura: INFORMÁTICA Profesor: Cadoni, Jorge Pieza Nro. 14 del “Puzzle”. Excel. Clase 1 14.1 Análisis de Hipótesis: Búsqueda de objetivos. Trabajar con Buscar Objetivos. Tablas de datos. Tablas de datos de una variable. Tablas de datos de dos variables. Búsqueda de objetivos. ¿En qué consiste Buscar objetivo en Excel? En encontrar un resultado deseado mediante la modificación del valor de una determinada celda de la que va a depender el valor deseado. Empecemos la clase, tomando un café… y mientras… te invito a ver el video denominado “Buscar objetivos” que nos mostrará cómo “empezar” a usar la herramienta de importación de archivos de Excel 2016. ¿Vamos a verlo? Después de visualizar el video, comencemos a aprender paso a paso y a reafirmar conceptos de lo que has visualizado recientemente. Empezaré con un ejemplo sencillo si bien te comento otros ejemplos en los que puedes usar esta herramienta en Excel: Piensa… Siempre resolvemos problemas en los cuales conocemos los datos y buscamos información a partir de operar de algún modo con esos datos. ¿Qué ocurre si conocemos el resultado de un cálculo y CASI todos los valores que le dan origen? ¿Cómo calculamos esos valores desconocidos? 3 + X - 4 = 4 ¿Cuánto vale X? ¿Y si son dos los valores desconocidos? ¿Y si son 3… o MÁS? A veeerrrr…. ¿Qué es el Análisis de Hipótesis? https://www.youtube.com/watch?v=OpBBdfXcBLw https://www.youtube.com/watch?v=OpBBdfXcBLw 2 • Imagina que quieres comprar un nuevo mobiliario para tu oficina que vale 1.200 dólares y lo vas a pagar a plazos, el número de cuotas de pago que estableces con el vendedor es de 24 meses, que a un tipo de interés del 10% te sale una cuota a pagar de 55 dólares. Pero resulta que no puedes pagar esa cantidad mensual, solo puedes hacer frente al pago de 30 dólares al mes. Esto alargará los pagos a realizar. • Con Buscar objetivo encontraras rápidamente la respuesta acerca de en cuantas cuotas terminarás de pagar la compra. • Otro ejemplo: Recibes un sueldo bruto al año de 20.000 dólares y el descuento para los impuestos es del 15% (obviando otro tipo de descuentos). Imagina que puedes pedir una reducción del impuesto para que aumente la cantidad liquida a percibir, si quisieras recibir 18.500 dólares líquidos al mes con Buscar Objetivo podrás calcular qué tipo de retención, o mejor dicho, cuánto de retención se aplicaría. Ejemplo sencillo: Vendes una determinada mercancía que cuesta 20 dólares, la cantidad vendida en el último año ha sido de 500 unidades, con ello has tenido unas ventas de 10.000 dólares, que es el resultado de multiplicar 20x500=10.000. Ahora quieres saber cuántas unidades has de vender para obtener unas ventas de 15.000 dólares. Pasos: • · Abre una hoja de cálculo Excel en blanco. • · Escribe en la columna A lo siguiente: 1. En A1: Precio 2. En A2: Cantidad 3. En A3: Ventas • · Escribe en la columna B: 1. En B1: 20 2. En B2: 500 3. En B3: La fórmula B1xB2, te tiene que aparecer el resultado que es 10.000 • A continuación has de hacer clic en la pestaña Datos. • Después en el grupo de trabajo llamado Herramientas de análisis, haz clic en el triángulo negro al lado de Análisis Y si. • Se despliegan una serie de opciones, pulsa en Buscar objetivo. • Se abre una ventana en la que vas a colocar los datos de la siguiente manera: 1. En Definir la celda: haz clic sobre la celda B3 que es la que queremos que se modifique y alcance la cifra de 15.000 dólares de ventas. 2. En Con el valor: Escribe 15.000. 3. En Para cambiar la celda: Haz clic en B2, que es donde está el dato sobre el número de unidades a vender. • Aceptar. La ventana cambia de apariencia al igual que los datos de la hoja de cálculo. Ahora puedes comprobar que para obtener 15.000 dólares de ventas has de vender 750 unidades, manteniendo constante el precio en 20 dólares la unidad. 3 Puedes hacer otro ejemplo, (usa Deshacer para volver a la situación de partida), con estos mismos datos, por ejemplo, calcula cuanto tendrías que subir el precio para obtener la cifra de ventas de 15.000 dólares, si en este caso las unidades vendidas no cambian; resultado: 30 dólares la unidad. Haz este ejercicio y comprueba, para ti, que sabes usar la Herramienta Buscar Objetivo. Nota: Buscar objetivo sólo funciona con un valor de entrada. Si deseas aceptar más de una entrada de valor; por ejemplo, el importe del préstamo y el importe del pago mensual de un préstamo, usa el complemento Solver, que lo veremos más adelante. Ejemplo paso a paso Veamos el ejemplo anterior, paso a paso. Dado que vas a calcular la tasa de interés del préstamo necesaria para cumplir con los objetivos, usa la función pago. La función pago calcula un importe de pago mensual. En este ejemplo, el importe del pago mensual es el objetivo que buscas. Preparar la hoja de cálculo 1. Abre una nueva hoja de cálculo en blanco. 2. En primer lugar, agrega algunos de los rótulos en la primera columna para facilitar la lectura de la hoja de cálculo. a. En la celda A1, escribe Importe del préstamo. b. En la celda A2, escribe Período en meses. c. En la celda A3, escribe Tipo de interés. d. En la celda A4, escribe pago. 3. A continuación, agrega los valores que conoces. a. En la celda B1, escribe 100.000. Este es el importe del préstamo que deseas obtener. b. En la celda B2, escribe 180. Esta es la cantidad de meses que deseas emplear para saldar el préstamo. 4. A continuación, agregue la fórmula para el que tiene un objetivo. Por ejemplo, use la función pago: a. En la celda B4, escribe =PMT(B3/12,B2,B1). Esta fórmula calcula el importe del pago. En este ejemplo, deseas pagar cada mes de 900 $. La fórmula hace referencia a las celdas B1 y B2, que contienen valores que especificaste en los pasos anteriores. La fórmula también hace referencia a la celda B3, que es donde deseas que buscar objetivo ponga la tasa de interés. La fórmula divide el valor de B3 por 12, ya que especifica un pago mensual y la función pago supone una tasa de interés anual. Dado que no hay ningún valor en la celda B3, Excel presupone una tasa de interés de 0% y, con los valores de ejemplo, devuelve un pago de 555.56 $. Puedes ignorar ese valor por ahora. 4 Usar Buscar objetivo para determinar el tipo de interés 1. En la pestaña Datos, en el grupo Herramientas de datos, haz clic en Análisis de hipótesis y, a continuación, en Buscar objetivo. 2. En el cuadro definir la celda, escribe la referencia de la celda que contiene el fórmula que deseas resolver. En el ejemplo, esta referencia es la celda B4. 3. En el cuadro valor, escribe el resultado de la fórmula que desees. En el ejemplo, esta sería -900. Ten en cuenta que este número es negativo, porque representa un pago. 4. En el cuadro cambiando la celda, escribe la referencia de la celda que contiene el valor que deseas ajustar. En el ejemplo, esta referencia es la celda B3. 5. Haz clic en Aceptar. Buscar objetivo se ejecuta y produce un resultado, como se muestra en la siguiente figura. 6. Por último, dar formato a la celda objetivo (B3) para que muestre el resultado como un porcentaje. a. En la pestaña Inicio, en el grupo Número, haz clic en la opción Porcentaje. b. Haz clic en Aumentar decimales o en Disminuir decimales para establecer la cantidad de posiciones decimales. TABLAS 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. Tablas de datos de 1 y de 2 variables Te invito a ver DOS videos. Uno denominado “Tablasde Datos” que puedes visualizar desde el link marcado en color azul y otro que visualizarás desde el ícono cinematográfico que puedes ver a continuación ¿Vamos a verlo? https://www.youtube.com/watch?v=kZCDrUPAE2Y https://www.youtube.com/watch?v=kZq6-qfmkzE 5 Tablas 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: Para crear la tabla de datos debo seleccionar el rango de celdas tal como se muestra en la imagen de la derecha 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. 6 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. 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 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. Tablas de datos de dos variables 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: 7 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. 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: 8 Al hacer clic en el botón Aceptar se generará la tabla de datos correspondiente: En esta tabla de datos podemos observar lo que esperábamos: mientras mayor es el plazo, los pagos mensuales se reducen y mientras 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 deberás utilizar la Herramienta Solver.
Compartir