Una tabla de datos variables en Excel

¿Qué es la tabla de datos de una variable en Excel?

Una tabla de datos variable en Excel significa cambiar una variable junto con múltiples opciones y obtener los resultados para múltiples escenarios.

¿Cómo crear una tabla de datos de una variable en Excel? (con ejemplos)

Puede descargar esta plantilla de Excel de tabla de datos de una variable aquí - Plantilla de Excel de tabla de datos de una variable

Ejemplo 1

Estás tomando un préstamo de Rs. 2, 50.000 durante 2 años. Está hablando con el oficial de préstamos sobre la tasa de interés.

Debe analizar a diferentes tasas de interés cuál es el pago mensual que debe pagar para liquidar el préstamo. Para fines de cálculo, tome la tasa base del 12% anual.

  • Paso 1: Calcule la EMI mensual usando la función PMT.

  • Paso 2: Ahora cree una tabla de escenarios como se muestra en la siguiente imagen.

  • Paso 3: Seleccione la celda E9 y proporcione un enlace a la celda B6 (Cantidad EMI). Ahora la celda E9 muestra la EMI mensual.

  • Paso 4: seleccione el rango de D9 a E22.

  • Paso 5: Haga clic en la pestaña Datos y luego  en Análisis Y si ... y Tabla de datos

  • Paso 6: Aparecerá el cuadro de diálogo Tabla de datos. En la celda de entrada de columna , seleccione la celda B5 (que contiene la tasa de interés básica del préstamo).

Una tabla de datos variables en Excel, siempre ignoramos la celda de entrada de FILA o la celda de entrada de columna. Depende de la estructura de nuestra mesa. Si nuestra tabla de escenarios, las diferentes tasas de interés son verticales, ignoramos la celda de entrada de fila y si las tasas de interés de nuestra tabla de escenarios son horizontales, ignoramos la celda de entrada de la columna. En este ejemplo, he ignorado la celda de entrada Fila porque las diferentes tasas de interés de la tabla de escenarios están en forma vertical.

  • Paso 7: Ahora haga clic en el botón Aceptar para crear diferentes escenarios.

Ahora el rango E10: E22 muestra algunos valores nuevos. De la tabla, está muy claro que el EMI mensual con una tasa de interés del 12.5% ​​será de 11,827 INR y el EMI mensual con una tasa de interés del 13.5% será de 11,944 INR y así sucesivamente.

Así es como funciona una tabla de datos variables en Excel. También puede mostrar esto en un gráfico.

Ejemplo # 2

Suponga que es gerente de ventas de una empresa. De la dirección, ha recibido un objetivo de ventas mensual de 1, 70, 00 USD de su equipo. La siguiente tabla muestra el objetivo de ventas para 6 miembros. Debe analizar cuál debería ser su nivel de eficiencia para alcanzar el objetivo de 1,7 lakh USD en un mes.

El objetivo general de sus equipos es de 2,04 mil rupias. No está seguro de qué porcentaje de eficiencia deben aportar para lograr el objetivo establecido por la dirección.

Su equipo puede dar un nivel de eficiencia máximo del 90% y usted ha calculado los ingresos totales con un nivel de eficiencia del 90%.

Con un nivel de eficiencia del 90%, su equipo puede lograr ingresos totales de 1,83 lakh USD en un mes. Necesita cuál debería ser el nivel de eficiencia para lograr el objetivo de ingresos establecido por la dirección.

Cree una tabla de escenarios como se muestra en la siguiente imagen.

  • Paso 1: debajo de la tabla, debe crear un archivo de Excel.

Esta tabla muestra a diferentes niveles de eficiencia cuáles serán los ingresos.

  • Paso 2: Seleccione la celda H3 y proporcione un enlace a la celda B11 (en la celda de ingresos de nivel de eficiencia del 90%). Ahora la celda H3 muestra los ingresos del nivel de eficiencia del 90%.

  • Paso 3: seleccione el rango de G3 a H12

  • Paso 4: Ahora busque la Tabla de datos en la sección Análisis Y si ...

  • Paso 5: Una vez que haga clic en Tabla de datos, debemos dar un enlace en la celda de entrada de columna , seleccione la celda B10 (que contiene el porcentaje de eficiencia).

Para la celda de entrada de Columna he dado el enlace a la celda B10 porque en base a los diferentes niveles de eficiencia vamos a crear los escenarios. Ahora, la tabla de datos entiende que al 90% los ingresos serán de 1,83 lakh USD. De manera similar, creará escenarios para 100%, 95%, 90%, 85%, 80% y así sucesivamente.

  • Paso 6: haga clic en Aceptar para crear diferentes escenarios.

Ahora la gama G3: H12 muestra escenarios. La dirección ha fijado el objetivo de 1,70 lakh USD para este mes. Para lograr tantos ingresos, su equipo debe desempeñarse a un nivel de eficiencia de al menos el 85%.

De esta manera, puede utilizar la Tabla de datos para crear diferentes análisis y elegir un escenario adecuado para lograr los objetivos.

Cosas para recordar

  • Una vez que se han creado los escenarios, no podemos deshacer la acción. Seguirá siendo el mismo.
  • Ni siquiera podemos modificar los valores de las celdas porque se convierte en una fórmula matricial de Excel y es necesario eliminar todo, no uno por uno.
  • En una tabla de datos variables, omita siempre la celda de entrada Fila si el escenario se mostrará en forma vertical. Si el escenario se va a mostrar en forma horizontal, deje la celda de entrada Columna.
  • Puede cambiar el valor en la base de datos principal para ver los resultados en tiempo real en diferentes tipos de alternativas.