El análisis Y si … de Excel ofrece una tabla de datos. Este es un mal nombre. Debería llamarse análisis de sensibilidad. Es genial. Lea sobre esto aquí.
Goal Seek le permite encontrar el conjunto de entradas que conducen a un resultado particular. A veces, desea ver muchos resultados diferentes de varias combinaciones de entradas. Siempre que solo tenga dos celdas de entrada para cambiar, la tabla de datos proporciona una forma rápida de comparar alternativas.
Utilizando el ejemplo de pago del préstamo, digamos que desea calcular el precio para una variedad de saldos de capital y para una variedad de términos.
Asegúrese de que la fórmula que desea modelar esté en la esquina superior izquierda de un rango. Coloque varios valores para una variable en la columna de la izquierda y varios valores para otra variable en la parte superior.
En la pestaña Datos, seleccione Análisis Y si …, tabla de datos.
Tiene valores en la fila superior de la tabla de entrada. Quiere que Excel conecte esos valores en una determinada celda de entrada. Especifique esa celda de entrada como la celda de entrada de fila.
Tiene valores a lo largo de la columna de la izquierda. Quieres que los enchufes en otra celda de entrada. Especifique esa celda como la celda de entrada de columna.
Al hacer clic en Aceptar, Excel repetirá la fórmula en la columna superior izquierda para todas las combinaciones de la fila superior y la columna izquierda. En la imagen a continuación, puede ver 60 pagos de préstamos diferentes según varios resultados.
Tenga en cuenta que formateé los resultados de la tabla para que no tuvieran decimales y usé Inicio, Formato condicional, Escala de color para agregar el sombreado rojo / amarillo / verde.
Aquí está la gran parte: esta mesa está "en vivo". Si cambia las celdas de entrada a lo largo de la columna izquierda o la fila superior, los valores de la tabla se volverán a calcular. A continuación, los valores a la izquierda se centran en el rango de $ 23K a $ 24K.
Gracias a Owen W. Green por sugerir mesas.
Ver video
- Tres herramientas hipotéticas en Excel
- Ayer - Búsqueda de objetivos
- Hoy: una tabla de datos
- Excelente para problemas de dos variables
- Trivia: la función de matriz TABLE no se puede ingresar manualmente, no funcionará
- Use una escala de colores para colorear las respuestas
- ¿Qué pasa si tienes 3 variables para cambiar? Escenarios? ¡No! Copiar hoja de trabajo
- Las tablas son lentas de calcular: modo de cálculo para todas las tablas excepto las
- Gracias a Owen W. Green por sugerir este consejo.
Transcripción del video
Aprenda Excel del podcast, episodio 2034 - ¡Qué pasaría si con una tabla de datos!
Estoy publicando un podcast de este libro completo, haga clic en la "i" en la esquina superior derecha para acceder a la lista de reproducción.
Hoy vamos a hablar sobre la segunda herramienta en Análisis Y si …, ayer hablamos de Búsqueda de Objetivos, hoy vamos a cubrir una Tabla de Datos. Así que tenemos este pequeño modelo aquí, este es un modelo pequeño, 3 celdas de entrada, una fórmula. Pero este modelo podría ser cientos de celdas de entrada, miles de filas, siempre y cuando se reduzca a una respuesta final, y queremos modelar esta respuesta para varios valores diferentes de 2-3 (?) Celdas de entrada. Por ejemplo, tal vez estemos interesados en ver diferentes autos, así que en cualquier lugar desde 20000 en adelante, así que pondré 20 y 21000 allí, agarro el controlador de relleno y lo arrastro, lo reduzco a 28000. En la parte superior vemos ' Estamos considerando diferentes términos, por lo que un préstamo a 36 meses, un préstamo a 42 meses, un préstamo a 48 meses, 54, 60, 66 e incluso 72.
Muy bien, este próximo paso es completamente opcional, pero realmente me ayuda a pensar en esto, siempre cambio los colores de los valores en la parte superior y los valores en la izquierda. Y lo realmente importante aquí es que esa celda de la esquina, esa celda de la esquina tan importante, tiene que ser la respuesta que estamos tratando de modelar, de acuerdo. Por lo tanto, debe comenzar a seleccionar desde esa celda de la esquina con la respuesta y luego seleccionar todas las filas y todas las columnas. Entonces entramos en Datos, Análisis Y si … y una Tabla de datos, y aquí se solicitan dos cosas, y así es como lo pensaría. Dice que hay un montón de elementos diferentes en la fila superior de la tabla, quiero tomar esos elementos, uno por uno, y conectarlos al modelo, ¿dónde deberíamos ingresar? Entonces, estos elementos, estos son términos, deben ir a la celda B2. Y entonces,hay un montón de elementos a lo largo de la columna de la izquierda, queremos tomarlos, uno a la vez, y conectarlos a B1, así, está bien y hacemos clic en Aceptar, BAM, ejecuta este modelo una y otra y otra vez .
Ahora solo un poco de limpieza aquí, siempre entro y hago Home, y probablemente 0 lugares decimales, así. Y tal vez un poco de formato condicional, escalas de color y vayamos con números rojos para números grandes y números verdes para pequeños, solo para darme una, ya sabes, una forma de rastrear esto visualmente. Ahora parece que si estamos apuntando a $ 425, estamos, ya sabes, en este lugar o en este lugar, o ya sabes, tal vez aquí, todos nos acercaremos a los $ 425. Entonces puedo ver cuáles son las diversas probabilidades, nuestras diversas combinaciones, para llevarnos a esos valores.
Ahora, un par de cosas, esta parte dentro de aquí, es en realidad una fórmula de matriz grande, entonces = TABLE (B2, B1), la entrada de fila y columna. Esto es curioso, no está permitido escribir esto, solo puede crearlo usando Datos, Análisis Y si …, tiene que usar ese cuadro de diálogo. Si intenta escribir esa fórmula, presione Ctrl + Shift + Enter, no funcionará, ¿verdad? Entonces, es una función en Excel, pero si eres lo suficientemente inteligente como para escribirla, lástima que no funcionará, pero se recalcula constantemente. Entonces, si determinamos que solo estamos viendo términos de 48, y queremos ver en grupos de 3 o algo así, cuando cambio estos números, todo eso es cálculo. En este caso, solo está haciendo una fórmula para cada uno, pero imagina que si estuviéramos haciendo 100 fórmulas, esto se ralentiza drásticamente. Así que aquí bajo Fórmulas, allá 'En realidad, es una opción Opciones de cálculo, automático o manual, hay una tercera que dice "Sí, vuelva a calcular todo excepto las tablas de datos, no siga recalculando la tabla de datos". Porque esto puede suponer un gran lastre para los tiempos de cálculo.
Muy bien ahora, las tablas de datos son increíbles cuando tienes dos variables para cambiar, pero tenemos tres variables para cambiar. ¿Y si hubiera diferentes tasas de interés, recomiendo ir al Administrador de escenarios? NO, ¡NUNCA recomiendo ir al Administrador de escenarios! En este caso tenemos 9x7, son 63 escenarios diferentes que calculamos aquí, para crear 63 escenarios diferentes de Scenario Manager tomaría 2 horas, es horrible. No cubro esto en el libro “MrExcel XL”, porque son los 40 mejores consejos. Esto probablemente esté en mi libro "Power Excel" con 567 misterios de Excel resueltos, pero estoy seguro de que me quejé de lo miserable que es usarlo, no me verán hacer el Administrador de escenarios aquí. Si realmente tuviéramos que hacer esto para varias velocidades diferentes, lo mejor que se puede hacer es simplemente Ctrl-arrastrar, tomar esta hoja, Ctrl-arrastrar, Ctrl-arrastrar,Mantenga presionada la tecla Ctrl y luego cambie las tasas en cada hoja. Entonces, si pudiéramos obtener un 5% o un 4.75% o algo así y así sucesivamente, no hay una manera fácil de configurar eso para 3 variables en el Administrador de escenarios. Muy bien, "40 mejores consejos de Excel de todos los tiempos", todo en este libro, puede comprar el libro, haga clic en esa "i" en la esquina superior derecha.
Resumen del episodio de hoy: Hay tres herramientas Y si … en Excel, ayer hablamos de Goal Seek, hoy la tabla de datos. Es increíble para problemas de 2 variables, mañana verá uno con un problema de 1 variable. La función de matriz de tabla no se puede ingresar manualmente, no funcionará, debe usar Datos, Análisis hipotético, Tabla de datos. Usé una escala de colores, Inicio, Formato condicional, Escalas de colores, para colorear las respuestas. Si tienes 3 variables para cambiar, ¿haces escenarios? No, solo haga copias de la hoja de trabajo o copias de la tabla, son lentas de calcular, especialmente con un modelo complejo. Hay un modo de cálculo para Automático para todos excepto para las tablas, y Owen W. Green sugirió incluir esta función en los libros.
Así que gracias a él y gracias a ti por pasar, ¡nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2034.xlsx
