cómo crear y usar una tabla de datos en Excel

el tutorial muestra cómo usar tablas de datos para el análisis hipotético en Excel. Aprenda cómo crear una tabla de una y dos variables para ver los efectos de uno o dos valores de entrada en su fórmula, y cómo configurar una tabla de datos para evaluar varias fórmulas a la vez.

ha construido una fórmula compleja que depende de múltiples variables y desea saber cómo cambiar esas entradas cambia los resultados., En lugar de probar cada variable individualmente, haga una tabla de datos de análisis hipotético y observe todos los resultados posibles con un vistazo rápido.

• tabla de datos en Excel
• Crear una tabla de datos variables
• Hacer dos tablas de datos variables
• tabla de datos para evaluar múltiples fórmulas
• tablas de datos de Excel-3 cosas que debe saber
• eliminar una tabla de datos en Excel
• editar los resultados de la tabla de datos
• recalcular la tabla de datos manualmente

¿qué es una tabla de datos en Excel?,

en Microsoft Excel, una tabla de datos es una de las herramientas de análisis hipotético que le permite probar diferentes valores de entrada para fórmulas y ver cómo los cambios en esos valores afectan a la salida de fórmulas.

Las Tablas de datos son especialmente útiles cuando una fórmula depende de varios valores, y le gustaría experimentar con diferentes combinaciones de entradas y comparar los resultados.

actualmente, existen una tabla de datos variables y dos tablas de datos variables. Aunque está limitado a un máximo de dos celdas de entrada diferentes, una tabla de datos le permite probar tantos valores de variables como desee.,

cómo crear una tabla de datos variables en Excel

Una tabla de datos variables en Excel permite probar una serie de valores para una sola celda de entrada y muestra cómo esos valores influyen en el resultado de una fórmula relacionada.,

para ayudarle a comprender mejor esta característica, vamos a seguir un ejemplo específico en lugar de describir pasos genéricos.

supongamos que está considerando depositar sus ahorros en un banco, que paga un 5% de interés que se suma mensualmente. Para verificar diferentes opciones, ha creado la siguiente calculadora de interés compuesto donde:

• B8 contiene la fórmula FV que calcula el saldo de cierre.
• B2 es la variable que desea probar (inversión inicial).,

Y ahora, vamos a hacer un simple análisis What-If para ver cuáles serán sus ahorros en 5 años dependiendo de la cantidad de su inversión inicial, que van desde $1,000 a 6 6,000.

Estos son los pasos para crear una tabla de datos de una sola variable:

1. introduzca los valores de la variable en una columna o en una fila. En este ejemplo, vamos a crear una tabla de datos orientada a columnas, por lo que escribimos nuestros valores de variables en una columna (D3:D8) y dejamos al menos una columna en blanco a la derecha para los resultados.,
2. escriba su fórmula en la celda una fila arriba y una celda a la derecha de los valores de la variable (E2 en nuestro caso). O bien, vincule esta celda a la fórmula del conjunto de datos original (Si decide cambiar la fórmula en el futuro, solo necesitará actualizar una celda). Elegimos la última opción e ingresamos esta fórmula simple En E2: =B8
   
   Tip., Si desea examinar el impacto de los valores de la variable en otras fórmulas que se refieren a la misma celda de entrada, ingrese las fórmulas adicionales a la derecha de la primera fórmula, como se muestra en este ejemplo.
3. seleccione el rango de la tabla de datos, incluyendo su fórmula, celdas de valores variables y celdas vacías para los resultados (D2:E8).,
4. vaya a la pestaña Datos > Grupo Herramientas de Datos, Haga clic en el botón Análisis hipotético y, a continuación, haga clic en tabla de Datos
5. En la ventana de diálogo Tabla de Datos, Haga clic en el cuadro celda de entrada de columna (porque nuestros valores de inversión están en una columna) y seleccione la celda variable a la que se hace referencia en su fórmula. En este ejemplo, seleccionamos B3 que contiene el valor de inversión inicial.
   
6. haga clic en Aceptar, y Excel llenará inmediatamente las celdas vacías con resultados correspondientes al valor de la variable en la misma fila.,
7. aplique el formato de número deseado a los resultados (moneda en nuestro caso), ¡y ya está listo!

ahora, puede echar un vistazo rápido a su tabla de datos de una variable, examinar los posibles saldos y elegir el tamaño de depósito óptimo:

tabla de datos orientada a filas

el ejemplo anterior muestra cómo configurar una tabla de datos vertical u orientada a columnas en Excel., Si prefiere un diseño horizontal, esto es lo que debe hacer:

1. escriba los valores de la variable en una fila, dejando al menos una columna vacía a la izquierda (para la fórmula) y una fila vacía debajo (para los resultados). Para este ejemplo, ingresamos los valores de la variable en las celdas F3: J3.
2. ingrese la fórmula en la celda que es una columna a la izquierda de su primer valor de variable y una celda debajo (E4 en nuestro caso).,
3. haga una tabla de datos como se discutió anteriormente, pero ingrese el valor de entrada (B3) en el cuadro de celda de entrada de fila:
   
4. haga clic en Aceptar, y tendrá el siguiente resultado:
   

cómo hacer una tabla de datos variables de dos en Excel

una tabla de datos de dos variables muestra cómo varias combinaciones de 2 conjuntos de valores de variables afectan el resultado de la fórmula. En otras palabras, muestra cómo cambiar dos valores de entrada de la misma fórmula cambia la salida.,

los pasos para crear una tabla de datos de dos variables en Excel son básicamente los mismos que en el ejemplo anterior, excepto que ingresa dos rangos de posibles valores de entrada, uno en una fila y otro en una columna.

para ver cómo funciona, usemos la misma calculadora de interés compuesto y examinemos los efectos del tamaño de la inversión inicial y el número de años en el balance. Para hacerlo, Configure su tabla de datos de esta manera:

1. ingrese su fórmula en una celda en blanco o vincule esa celda a su fórmula original., Asegúrese de tener suficientes columnas vacías a la derecha y filas vacías debajo para acomodar los valores de sus variables. Como antes, vinculamos la celda E2 a la fórmula FV original que calcula el saldo: =B8
2. escriba un conjunto de valores de entrada debajo de la fórmula, en la misma columna (valores de inversión en E3:E8).
3. ingrese el otro conjunto de valores de variables a la derecha de la fórmula, en la misma fila (número de años en F2:H2).,

   en este punto, su tabla de datos de dos variables debe ser similar a esta:
   

4. seleccione Todo el rango de la tabla de datos, incluida la fórmula, la fila y la columna de los valores de la variable y las celdas en las que aparecerán los valores calculados. Seleccionamos el rango E2: H8.,
5. cree una tabla de datos de la forma ya familiar: pestaña Datos > botón de análisis What-If > tabla de datos
6. En el cuadro celda de entrada de fila, ingrese la referencia a la celda de entrada para los valores de la variable en la fila (en este ejemplo, es B6 que contiene el valor Years).
7. En el cuadro celda de entrada de columna, ingrese la referencia a la celda de entrada para los valores de la variable en la columna (B3 que contiene el valor de inversión inicial).
8. haga Clic en ACEPTAR.,n Nuestro caso), y analizar los resultados:

   

tabla de datos para comparar múltiples resultados

Si desea evaluar más de una fórmula al mismo tiempo, construya su tabla de datos como se muestra en los ejemplos anteriores, e ingrese las fórmulas adicionales:

• A la derecha de la primera fórmula en caso de una tabla de datos vertical organizada columnas
• debajo de la primera fórmula en caso de una tabla de datos horizontal organizada en filas

para que la tabla de datos «multi-fórmula» funcione correctamente, todas las fórmulas deben referirse a la misma celda de entrada.,

como ejemplo, agreguemos una fórmula más a nuestra tabla de datos de una variable para calcular el interés y ver cómo se ve afectado por el tamaño de la inversión inicial. Esto es lo que hacemos:

1. en la celda B10, Calcule el interés con esta fórmula: =B8-B3
2. organice los datos de origen de la tabla de datos como lo hicimos anteriormente: valores variables en D3:D8 y E2 vinculados a B8 (fórmula de equilibrio).,
3. agregue una columna más al rango de la tabla de datos (columna F) y vincule F2 a B10 (fórmula de interés):
   
4. seleccione el rango extendido de la tabla de datos (D2:F8).
5. abra el cuadro de diálogo Tabla de datos haciendo clic en la pestaña Datos > análisis hipotético > tabla de datos
6. En el cuadro celda de entrada de columna, proporcione la celda de entrada (B3) y haga clic en Aceptar.,

Voilà, ahora puede observar los efectos de los valores de sus variables en ambas fórmulas:

tabla de datos en Excel – 3 cosas que debe saber

para usar efectivamente las tablas de datos en Excel, tenga en cuenta estos 3 hechos simples:

1. para que una tabla de datos se cree correctamente, las celdas de entrada deben estar misma hoja que la tabla de datos.,
2. Microsoft Excel utiliza la función TABLE(row_input_cell, colum_input_cell) para calcular los resultados de la tabla de datos:
   • en la tabla de datos de una variable, se omite uno de los argumentos, dependiendo del diseño (orientado a columnas o orientado a filas). Por ejemplo, en nuestra tabla de datos horizontal de una variable, la fórmula es =TABLE(, B3) donde B3 es la celda de entrada de columna.
   • en la tabla de datos de dos variables, ambos argumentos están en su lugar. Por ejemplo, =TABLE(B6, B3) donde B6 es la celda de entrada de fila y B3 es la celda de entrada de columna.,

   la función TABLE se introduce como una fórmula de matriz. Para asegurarse de esto, seleccione cualquier celda con el valor calculado, mire la barra de fórmulas y observe los {corchetes rizados} alrededor de la fórmula. Sin embargo, no es una fórmula de matriz normal, no puede escribirla en la barra de fórmulas ni editar una existente. Es solo «para mostrar».

3. debido a que los resultados de la tabla de datos se calculan con una fórmula de matriz, las celdas resultantes no se pueden editar individualmente. Solo puede editar o eliminar toda la matriz de celdas como se explica a continuación.,

cómo eliminar una tabla de datos en Excel

como se mencionó anteriormente, Excel no permite eliminar valores en celdas individuales que contienen los resultados. Cada vez que intente hacer esto, aparecerá un mensaje de error «no se puede cambiar parte de una tabla de datos».

sin embargo, puede borrar fácilmente toda la matriz de los valores resultantes. Así es como:

1. dependiendo de sus necesidades, seleccione Todas las celdas de la tabla de datos o solo las celdas con los resultados.
2. Pulse la tecla Supr.

Hecho!, 🙂

cómo editar los resultados de la tabla de datos

dado que no es posible cambiar parte de una matriz en Excel, no puede editar celdas individuales con valores calculados. Solo puede reemplazar todos esos valores con uno propio realizando estos pasos:

1. seleccione Todas las celdas resultantes.
2. elimine la fórmula de la tabla en la barra de fórmulas.
3. escriba el valor deseado y presione Ctrl + Enter.,

esto insertará el mismo valor en todas las celdas seleccionadas:

Una vez que la fórmula de la tabla haya desaparecido, la tabla de datos anterior se convierte en un rango habitual, y puede editar cualquier celda individual normalmente.

cómo recalcular manualmente la tabla de datos

si una tabla de datos grande con múltiples valores de variables y fórmulas ralentiza su Excel, puede deshabilitar los recalculados automáticos en esa y en todas las demás tablas de datos.,

para esto, vaya a fórmulas pestaña > grupo de cálculo, haga clic en Opciones de cálculo botón, y luego haga clic Automático excepto Tablas de datos.

Esto desactivará los cálculos automáticos de la tabla de datos y acelerará los cálculos de todo el libro de trabajo.

para recalcular manualmente su tabla de datos, seleccione las celdas resultantes, es decir, las celdas con fórmulas TABLE (), y presione F9.

así es como se crea y utiliza una tabla de datos en Excel., Para echar un vistazo más de cerca a los ejemplos discutidos en este tutorial, le invitamos a descargar nuestro ejemplo de tablas de datos de Excel. Gracias por leer y estaría feliz de verte de nuevo la próxima semana!

• análisis y Si con Buscar Objetivo de Excel
• ¿Cómo utilizar Solver en Excel: paso a paso con ejemplos
• como grupo de filas en Excel