¿Qué es una tabla de datos en Excel?

Las tablas de datos en Excel se utilizan para comparar variables y sus impactos en el resultado y los datos generales, la tabla de datos es un tipo de herramienta de análisis hipotético en Excel y está presente en la pestaña de datos en el análisis hipotético, esta herramienta solicita una entrada de fila y una tabla de entrada de columna para hacer una tabla de datos y el impacto se calcula mediante una variable o una tabla de datos de dos variables.

Tipos de tabla de datos en Excel

Tabla de datos de una variable
Tabla de datos de dos variables

1) Tabla de datos de una variable en Excel

El requisito básico para la tabla de datos es crear una base o modelo de prueba. Debe indicar a su tabla de datos qué fórmulas de su modelo de datos desea probar. La tabla de datos de una variable es la más adecuada cuando desea ver cómo cambia el resultado final cuando cambia las variables de entrada.

Ejemplo

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

El Sr. Murali dirige una fábrica llamada ABC Pvt Ltd. Además, está estimando los ingresos para el año 2019. La siguiente tabla muestra los ingresos para el año 2018 y los ingresos estimados en diferentes niveles de incremento.

La tabla anterior muestra que los ingresos para el año 2018 son de 15 lakh USD y se espera un crecimiento mínimo del 12% para el próximo año. Ahora, Murali quiere una tabla de datos, que muestra la tabla de crecimiento de ingresos a diferentes tasas de incremento. Quiere la tabla de escenarios en el siguiente formato.

Aplique la técnica de la tabla de datos para obtener los resultados deseados.

Paso 1: cree este formato de tabla de datos en un archivo de Excel. Los ingresos estimados para 2019 se muestran en la celda B5.

Paso 2: Seleccione la celda D8 y proporcione un enlace a la celda B5 (celda de ingresos estimados). Ahora la celda D8 muestra los ingresos estimados para 2019.

Paso 3: seleccione el rango de C8 a D19.

Paso 4: haga clic en la pestaña Datos > Análisis hipotético > Tabla de datos

Paso 5: Aparecerá el cuadro de diálogo Tabla de datos. En la celda de entrada de columna , seleccione la celda B3 (que contiene el porcentaje de tasa de crecimiento mínimo).

Dado que es una tabla de datos de una variable, omita la celda de entrada de fila. La razón por la que hemos seleccionado la celda B3 porque en base a las diferentes tasas de crecimiento vamos a crear los escenarios. Ahora, la tabla de datos entiende que al 12% los ingresos estimados son 15 lakh de USD. Del mismo modo, creará escenarios para 12,5%, 13,5%, 14,5% y así sucesivamente.

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

Ahora el rango D9: D19 muestra algunos valores nuevos. A partir de la tabla, está muy claro que los ingresos estimados con una tasa de crecimiento del 12.5% serán 16.875 lakh USD y los ingresos estimados del 14.5% serían 17.175 lakh USD y así sucesivamente.

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

2) Tabla de datos de dos variables en Excel

Podemos usar una tabla de datos de dos variables para analizar escenarios si dos variables cambian a la vez. Para esto, necesitamos dos rangos de posibles valores de entrada para la misma fórmula. Eso significa que aquí afectará tanto a Fila como a Columna.

Ejemplo

Ahora siga adelante y examine este ejemplo de tabla de datos de dos variables.

El Sr. Murali estaba pensando en el crecimiento de los ingresos a diferentes ritmos. Del mismo modo, planea otorgar descuentos a sus clientes para aumentar las oportunidades de ventas.

La siguiente tabla muestra el plan de Murlali para aumentar el crecimiento el próximo año. Quiere estimar los ingresos en diferentes niveles de crecimiento con diferentes tasas de descuento.

Paso 1: cree una tabla de datos anterior en Excel.

Paso 2: En la celda, B6 coloca la siguiente fórmula para calcular los ingresos eventuales después del descuento.

= B2 + (B2 * B3) - (B2 * B4)

Primero, agregará la tasa de crecimiento del año anterior y deducirá la tasa de descuento.

La celda D9 contiene la referencia a la celda B6.

Ahora, la tabla anterior muestra la posible tasa de crecimiento de D10 a D18 (Valor de columna) y una posible tasa de descuento de E9 a J9 (Valor de fila).

Paso 3: Seleccione el rango D9: J18.

Paso 4: haga clic en la pestaña Datos > Análisis hipotético > Tabla de datos

Paso 5: Aparecerá el cuadro de diálogo Tabla de datos. En la celda de entrada de columna , seleccione la celda B3 (que contiene el porcentaje de tasa de crecimiento mínimo) y en la celda de entrada de fila, seleccione la celda B4.

Junto con la fórmula en la celda D9 (se refiere a la celda B6 ), Excel sabe que debería reemplazar la celda B4 con 2.5% (celda E9), y la celda B3 con 12.5% (celda D10) y así sucesivamente para otras también.

Paso 6: haga clic en Aceptar.

Ahora, si Murali apunta a un crecimiento del 13,5% con una tasa de descuento del 2,5%, los ingresos serán de 16,65 lakh USD. Si Murali tiene como objetivo lograr 17 lakh USD, el descuento máximo que puede dar el próximo año es del 3% y puede generarle ingresos de 17.025 lakh USD.

Así de útil es la tabla de datos para crear diferentes modelos de escenarios para ayudar en el proceso de toma de decisiones.

Notas importantes:

Una tabla de datos le mostrará cómo al cambiar ciertos valores en su función puede cambiar el resultado de la fórmula.
Almacena los resultados de muchos escenarios variables en una tabla para que pueda finalizar el mejor escenario para su negocio o proyecto. Los resultados se escriben en formato de tabla.
Es una fórmula de matriz, que permite múltiples cálculos en una sola ubicación.
Una vez que haya calculado los valores a través de la tabla de datos, no puede deshacer la acción (Ctrl + Z). Sin embargo, puede eliminar manualmente todos los valores de la tabla.
No se le permite modificar una sola celda en el modelo de datos. Debido a que es una matriz, debe eliminar todo.
Sería muy confuso seleccionar la celda de entrada de Fila y la celda de entrada de columna. Debe seleccionar las celdas correctamente para obtener resultados precisos.
La tabla de datos en Excel no necesita actualizarse todo el tiempo, a diferencia de la tabla dinámica.