Cuando tiene una fórmula compleja que depende de múltiples variables y desea comprender cómo el cambio de esas entradas afecta los resultados de manera eficiente, una tabla de datos de análisis Y si en Excel es una herramienta poderosa. Le permite ver todos los resultados posibles con un vistazo rápido. Aquí hay una guía paso a paso sobre cómo crear una tabla de datos en Excel. Además, discutiremos algunos puntos clave para usar tablas de datos de manera efectiva y demostraremos otras operaciones como eliminar, editar y recalcular tablas de datos.

¿Qué es la tabla de datos en Excel?

Crear tabla de datos de una variable

• Tabla de datos orientada a columnas
• tabla de datos orientada a filas
• Múltiples fórmulas para una tabla de datos de una variable

Crear tabla de datos de dos variables

Puntos clave utilizando las tablas de datos.

Otras operaciones para usar tablas de datos

• Eliminar una tabla de datos
• Editar resultado de la tabla de datos
• Recalcular la tabla de datos manualmente

¿Qué es la tabla de datos en Excel?

En Excel, una tabla de datos es una de las herramientas de análisis What-If que le permite experimentar con diferentes valores de entrada para fórmulas y observar los cambios en la salida de la fórmula. Esta herramienta es increíblemente útil para explorar diversos escenarios y realizar análisis de sensibilidad, especialmente cuando una fórmula depende de múltiples variables.

Hay dos tipos de tablas de datos en Excel:

Tabla de datos de una variable: Esto le permite analizar cómo los diferentes valores de una variable afectarán una fórmula. Puede configurar una tabla de datos de una variable en un formato orientado a filas o a columnas, dependiendo de si desea variar su entrada en una fila o en una columna.

Tabla de datos de dos variables: este tipo le permite ver el impacto de cambiar dos variables diferentes en el resultado de una fórmula. En una tabla de datos de dos variables, varía los valores tanto en una fila como en una columna.

Crear tabla de datos de una variable

Crear una tabla de datos de una variable en Excel es una habilidad valiosa para analizar cómo los cambios en una sola entrada pueden afectar varios resultados. Esta sección lo guiará a través del proceso de creación de tablas de datos de una variable orientadas a columnas, filas y múltiples fórmulas.

¡Asombroso! ¡Usando pestañas eficientes en Excel como Chrome, Edge, Firefox y Safari!
¡Ahorre el 50% de su tiempo y reduzca miles de clics del mouse todos los días!

---

Tabla de datos orientada a columnas

Una tabla de datos orientada a columnas es útil cuando desea probar cómo los diferentes valores de una sola variable impactan la salida de una fórmula, con los valores de las variables enumerados en una columna. Consideremos un ejemplo financiero simple:

Suponga que está considerando un préstamo de $50,000, que planea pagar en un lapso de 3 años (equivalente a 36 meses). Para evaluar qué pago mensual sería asequible en función de su salario, le interesa explorar cómo las distintas tasas de interés afectarían la cantidad que debe pagar cada mes.

Paso 1: configurar la fórmula base

Para calcular el pago, aquí estableceré el interés en 5%. En la celda B4, ingrese la fórmula PMT, calcula el pago mensual en función de la tasa de interés, el número de períodos y el monto del préstamo. Ver captura de pantalla:

= -PMT($B$1/12, $B$2*12, $B$3)

Paso 2: enumere las tasas de interés en una columna

En una columna, enumere las diferentes tasas de interés que desea probar. Por ejemplo, enumere los valores del 4% al 11% en incrementos del 1% en una columna y deje al menos una columna en blanco a la derecha para los resultados. Ver captura de pantalla:

Paso 3: crear la tabla de datos

1. En la celda E2, ingresa esta fórmula: +/- 4 KHz.
   
   Note: B4 es la celda donde se encuentra su fórmula principal, esta es la fórmula cuyos resultados desea que cambien a medida que varía la tasa de interés.
   
2. Seleccione el rango que incluye su fórmula, lista de tasas de interés y celdas adyacentes para los resultados (por ejemplo, seleccione D2 a E10). Ver captura de pantalla:
   
3. Vaya a la cinta, haga clic en el Datos pestaña, haga clic en Y si el análisis > Tabla de Datos, ver captura de pantalla:
   
4. En Tabla de Datos cuadro de diálogo, haga clic en el Celda de entrada de columna (ya que estamos usando una columna para los valores de entrada) y seleccione la celda variable a la que se hace referencia en su fórmula. En este ejemplo, seleccionamos B1 que contiene la tasa de interés. Por último, haz clic OK botón, ver captura de pantalla:
   
5. Excel llenará automáticamente las celdas vacías adyacentes a cada uno de sus valores variables (diferentes tasas de interés) con los resultados correspondientes. Ver captura de pantalla:
   
6. Aplique el formato de número deseado a los resultados (Moneda) según su necesidad. Ahora, la tabla de datos orientada a columnas se creó correctamente y puede examinar rápidamente los resultados para evaluar qué montos de pago mensual serían asequibles para usted cuando cambie la tasa de interés. Ver captura de pantalla:
   

tabla de datos orientada a filas

Crear una tabla de datos orientada a filas en Excel implica organizar los datos de manera que los valores de las variables aparezcan en una fila en lugar de en una columna. Tomando el ejemplo anterior como referencia, procedamos con los pasos para completar la creación de una tabla de datos orientada a filas en Excel.

Paso 1: enumere las tasas de interés en una fila

Coloque los valores de las variables (tasas de interés) en una fila, asegurándose de que haya al menos una columna vacía a la izquierda para la fórmula y una fila vacía debajo para los resultados, vea la captura de pantalla:

Paso 2: crear la tabla de datos

1. En la celda A9, ingrese esta fórmula: +/- 4 KHz.
   
2. Seleccione el rango que incluye su fórmula, lista de tasas de interés y celdas adyacentes para los resultados (por ejemplo, seleccione A8 a I9). Luego, haga clic Datos > Y si el análisis > Tabla de Datos.
3. En Tabla de Datos cuadro de diálogo, haga clic en el Celda de entrada de fila (ya que estamos usando una fila para los valores de entrada) y seleccione la celda variable a la que se hace referencia en su fórmula. En este ejemplo, seleccionamos B1 que contiene la tasa de interés. Por último, haz clic OK botón, ver captura de pantalla:
   
4. Aplique el formato de número deseado a los resultados (Moneda) según su necesidad. Ahora, se crea la tabla de datos orientada a filas, vea la captura de pantalla:
   

Múltiples fórmulas para una tabla de datos de una variable

Crear una tabla de datos de una variable con múltiples fórmulas en Excel le permite ver cómo el cambio de una sola entrada afecta varias fórmulas diferentes a la vez. En el ejemplo anterior, ¿qué sucede si desea ver el cambio en las tasas de interés tanto de los reembolsos como del interés total? A continuación se explica cómo configurarlo:

Paso 1: agregue una nueva fórmula para calcular el interés total

En la celda B5, ingrese la siguiente fórmula para calcular el interés total:

=B4*B2*12-B3

Paso 2: organizar los datos de origen de la tabla de datos

En una columna, enumere las diferentes tasas de interés que desea probar y deje al menos dos columnas en blanco a la derecha para los resultados. Ver captura de pantalla:

Paso 3: cree la tabla de datos:

1. En la celda E2, ingrese esta fórmula: +/- 4 KHz para crear una referencia al cálculo de reembolso en los datos originales.
2. En la celda F2, ingrese esta fórmula: +/- 5 KHz para crear una referencia al interés total en los datos originales.
   
3. Seleccione el rango que incluye sus fórmulas, lista de tasas de interés y celdas adyacentes para el resultado (por ejemplo, seleccione D2 a F10). Luego, haga clic Datos > Y si el análisis > Tabla de Datos.
4. En Tabla de Datos cuadro de diálogo, haga clic en el Celda de entrada de columna (ya que estamos usando una columna para los valores de entrada) y seleccione la celda variable a la que se hace referencia en su fórmula. En este ejemplo, seleccionamos B1 que contiene la tasa de interés. Por último, haz clic OK botón, ver captura de pantalla:
   
5. Aplique el formato de número deseado a los resultados (Moneda) según su necesidad. Y puede ver los resultados de cada fórmula en función de los distintos valores de las variables.
   

Crear tabla de datos de dos variables

Una tabla de datos de dos variables en Excel muestra el impacto de diferentes combinaciones de dos conjuntos de valores de variables en el resultado de una fórmula, lo que demuestra cómo las alteraciones en dos entradas de una fórmula afectan simultáneamente su resultado.

Aquí, he dibujado un boceto simple para ayudarlo a comprender mejor la apariencia y estructura de una tabla de datos de dos variables.

Basándonos en el ejemplo para crear una tabla de datos de una variable, sigamos aprendiendo cómo crear una tabla de datos de dos variables en Excel.

En el siguiente conjunto de datos, tenemos la tasa de interés, el plazo del préstamo y el monto del préstamo, y hemos calculado el pago mensual utilizando esta fórmula: =-PMT($B$1/12, $B$2*12, $B$3) también. Aquí, nos centraremos en dos variables clave de nuestros datos: tasa de interés y monto del préstamo, para observar cómo los cambios en ambos factores afectan simultáneamente los montos de pago.

Paso 1: configurar las dos variables cambiantes

1. En una columna, enumere las diferentes tasas de interés que desea probar. ver captura de pantalla:
   
2. En una fila, ingrese los diferentes valores del monto del préstamo justo encima de los valores de la columna (comenzando desde una celda a la derecha de la celda de la fórmula), vea la captura de pantalla:
   

Paso 2: crear la tabla de datos

1. Coloque su fórmula en la intersección de la fila y la columna donde enumeró los valores de las variables. En este caso, ingresaré esta fórmula: +/- 4 KHz en la celda E2. Ver captura de pantalla:
   
2. Seleccione el rango que incluye los montos de su préstamo, las tasas de interés, la celda de fórmula y las celdas donde se mostrarán los resultados.
   
3. Luego, haz clic Datos > Y si el análisis > Tabla de Datos. En el cuadro de diálogo Tabla de datos:
   • En Celda de entrada de fila , seleccione la referencia de celda a la celda de entrada para los valores de las variables en la fila (en este ejemplo, B3 contiene el monto del préstamo).
   • En Celda de entrada de columnal cuadro, seleccione la referencia de celda a la celda de entrada para los valores de las variables en la columna (B1 contiene la tasa de interés).
   • Y luego, haga clic en OK Botón.
     
4. Ahora, Excel completará la tabla de datos con los resultados para cada combinación de monto del préstamo y tasa de interés. Proporciona una vista directa de cómo las diferentes combinaciones de montos de préstamos y tasas de interés afectan el pago mensual, lo que la convierte en una herramienta valiosa para la planificación y el análisis financieros.
   
5. Finalmente, deberás aplicar el formato numérico deseado a los resultados (Moneda) según tu necesidad.
   

Puntos clave utilizando las tablas de datos.

• La tabla de datos recién creada debe estar en la misma hoja de trabajo que los datos originales.
• La salida de la tabla de datos depende de la celda de fórmula en el conjunto de datos de origen, y cualquier cambio en esta celda de fórmula actualizará automáticamente la salida.
• Una vez que los valores se han calculado utilizando la tabla de datos, no se pueden deshacer con Ctrl + Z. Sin embargo, puede seleccionar manualmente todos los valores y eliminarlos.
• La tabla de datos genera fórmulas de matriz, por lo que las celdas individuales dentro de la tabla no se pueden modificar ni eliminar.

Otras operaciones para usar tablas de datos

Una vez que haya creado una tabla de datos, es posible que necesite operaciones adicionales para administrarla de manera efectiva, incluida la eliminación de la tabla de datos, la modificación de sus resultados y la realización de nuevos cálculos manuales.

Eliminar una tabla de datos

Debido a que los resultados de la tabla de datos se calculan con una fórmula matricial, no puede eliminar un valor individual de la tabla de datos. Sin embargo, sólo puede eliminar toda la tabla de datos.

Seleccione todas las celdas de la tabla de datos o solo las celdas con los resultados y luego presione Borrar tecla en el teclado.

Editar resultado de la tabla de datos

De hecho, no es posible editar celdas individuales dentro de una tabla de datos directamente, ya que contienen fórmulas de matriz que Excel genera automáticamente.

Para realizar cambios, normalmente necesitará eliminar la tabla de datos existente y luego crear una nueva con las modificaciones deseadas. Esto implica ajustar su fórmula base o los valores de entrada y luego configurar la tabla de datos nuevamente para reflejar estos cambios.

Recalcular la tabla de datos manualmente

Normalmente, Excel vuelve a calcular todas las fórmulas de todos los libros abiertos cada vez que se realiza un cambio. Si una tabla de datos grande que contiene numerosos valores variables y fórmulas complejas hace que su libro de Excel se ralentice.

Para evitar que Excel realice cálculos automáticamente para todas las tablas de datos cada vez que se realiza un cambio en el libro, puede cambiar el modo de cálculo de Automático a Manual. Por favor haga lo siguiente:

Visite la Fórmulas ficha y, a continuación, haga clic Opciones de cálculo > Automático excepto tablas de datos, ver captura de pantalla:

Una vez que modifique esta configuración, cuando vuelva a calcular todo su libro de trabajo, Excel ya no actualizará automáticamente los cálculos en sus tablas de datos.

Si necesita actualizar su tabla de datos manualmente, simplemente seleccione las celdas donde se muestran los resultados (celdas que contienen las fórmulas TABLE()) y luego presione F9 clave.

Si sigue los pasos descritos en esta guía y tiene en cuenta los puntos clave, podrá utilizar de manera eficiente las tablas de datos para sus necesidades de análisis de datos. Si está interesado en explorar más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales. por favor haga clic aquí para acceder a ellos. ¡Gracias por leer y esperamos brindarle más información útil en el futuro!