Tabla de datos en Excel (análisis de hipótesis de una y dos variables)

Crea una tabla de datos en Excel para probar de golpe muchos valores en una fórmula: de una y de dos variables, cuota de préstamo, beneficio y más. Guía paso a paso.

fxBlog

Tabla de datos en Excel (análisis de hipótesis de una y dos variables)

Crea una tabla de datos en Excel para probar de golpe muchos valores en una fórmula: de una y de dos variables, cuota de préstamo, beneficio y más. Guía paso a paso.

Listo·Alejandro·18 min de lectura·Actualizado 4 Nov 2026

Imagina a Lucía delante de la oferta de un préstamo. Cambia el tipo de interés en una celda, anota la cuota, lo vuelve a cambiar, anota otra cuota, prueba con otro plazo y al cuarto intento ya no recuerda cuál de los números iba con cuál. Es el tipo de tarea que Excel puede hacer por ti en un solo gesto.

La tabla de datos en Excel prueba de golpe una lista entera de valores (tipos de interés, precios, plazos, aportaciones) y muestra cómo cambia el resultado de una fórmula con cada uno, sin copiar la fórmula y sin tocar la celda original. Forma parte de las herramientas de análisis de hipótesis, junto con Buscar objetivo y los escenarios. Más abajo la montamos paso a paso con tres ejemplos de dinero, todos con datos ficticios.

Tabla de datos en Excel (análisis de hipótesis de una y dos variables)
Tabla de datos en Excel (análisis de hipótesis de una y dos variables)

Practícalo en un archivo real. La plantilla «Simulador de Ahorro a Largo Plazo» lo usa con datos de ejemplo; descárgala y cambia las cifras por las tuyas.

⬇️ Descargar la plantilla (gratis)
Hoja Escenarios con cuatro filas de rentabilidad (0, 2, 4 y 7 por ciento) con capital final, total aportado e intereses, y la evolución de los primeros años
Captura real de la plantilla «Simulador de Ahorro a Largo Plazo», con datos de ejemplo. Ver la plantilla completa.

Prueba muchos valores de golpe y ahorra el cambia-mira-apunta

Una tabla de datos calcula el resultado de una fórmula para toda una lista de valores de entrada en una sola operación. En vez de escribir un tipo de interés, mirar el resultado, borrarlo y probar otro, preparas una lista con todo lo que quieres explorar (del 2 % al 8 %, por ejemplo) y Excel rellena el resultado de cada uno. Siempre depende de una o dos celdas de entrada: las celdas concretas de tu hoja que la fórmula usa como variable. Con una variable hablamos de tabla de una variable; con dos a la vez (tipo de interés y plazo, por ejemplo), de tabla de dos variables.

La gran ventaja sobre copiar y pegar fórmulas a mano es que la tabla queda enlazada a las celdas originales: si cambias la fórmula o los datos de partida, todo se recalcula solo. A cambio, tiene reglas de colocación muy estrictas, que son las que verás en el siguiente apartado.

Sin tabla de datos

  • Escribes un valor en la celda de entrada y lees el resultado.
  • Lo apuntas a mano en otro sitio antes de probar el siguiente.
  • Si cambia la fórmula, hay que repetir todas las pruebas.
  • Es fácil confundir qué cifra correspondía a qué valor.

Con tabla de datos

  • Escribes la lista de valores una sola vez.
  • Excel calcula todos los resultados y los coloca en una cuadrícula.
  • Si cambias la fórmula o los datos de partida, la tabla se actualiza sola.
  • Cada resultado queda pegado a su valor de entrada.

Los tres ingredientes de cualquier tabla

Antes de abrir el cuadro de diálogo, comprueba que tienes sobre la hoja tres cosas: una fórmula que da el resultado que te interesa, una o dos celdas de entrada de las que depende esa fórmula, y una o dos listas de valores que quieres probar. Si falta alguna, la tabla no sale. Y si la fórmula no depende de la celda de entrada que indiques, Excel devolverá el mismo resultado en todas las filas, porque no hay nada que cambiar.

La ruta exacta dentro del menú

La tabla de datos vive en el mismo menú que el resto de herramientas de análisis de hipótesis: Datos → Previsión → Análisis de hipótesis → Tabla de datos. En ese mismo desplegable están también Buscar objetivo y el Administrador de escenarios. Esta ruta es idéntica en Excel 2016, 2019, 2021 y en Microsoft 365; lo único que varía un poco según la versión es el icono de Previsión.

💡
Cuándo tiene sentido una tabla de datosÚsala cuando quieras ver el efecto de varios valores de entrada sobre un único resultado (o dos con pequeños trucos): la cuota de un préstamo, un margen de beneficio, un ahorro final. Si lo que buscas es el único valor de entrada que produce un resultado exacto, la herramienta correcta es Buscar objetivo.

Monta tus tablas de una y de dos variables sin equivocarte de esquina

Toda tabla de datos exige el mismo gesto final: seleccionar el rango, abrir el cuadro de diálogo y rellenar las celdas de entrada. Lo que cambia entre una y dos variables es dónde va la fórmula. Esa posición no es un capricho estético: si la fórmula no está en la celda exacta, Excel no sabe qué resultado tiene que repetir para cada valor.

Una variable: valores en columna o en fila

Con los valores en columna (uno debajo de otro), la fórmula va en la celda una fila por encima del primer valor y una columna a la derecha de la columna de valores. Con los valores en fila (uno junto a otro), va una fila por debajo y una columna a la izquierda del primer valor.

  1. Escribe en una columna (o en una fila) la lista de valores que quieres probar, por ejemplo distintos tipos de interés.
  2. En la celda de la esquina que corresponda, escribe la fórmula que quieres evaluar, referenciando la celda de entrada real de tu hoja.
  3. Selecciona todo el rango: los valores de entrada más la celda donde está la fórmula.
  4. Ve a Datos → Previsión → Análisis de hipótesis → Tabla de datos.
  5. Si los valores están en columna, rellena solo Celda de entrada (columna) con la celda que la fórmula usa como variable. Si están en fila, rellena solo Celda de entrada (fila).
  6. Pulsa Aceptar. Excel rellena todos los resultados.

Veamos un esquema pequeño con los datos del préstamo de 20.000 € a 10 años que usaremos más abajo. Escribes los tipos de interés en una columna (3 %, 4 %, 5 % y 6 %) y, una fila por encima del primer valor y una columna a su derecha, la fórmula PAGO. Seleccionas el rango, abres la tabla de datos e indicas como celda de entrada (columna) la celda donde normalmente escribes el interés. Al aceptar, la columna de la derecha se llena con las cuotas de cada tipo:

Tipo de interés (valores en columna)Cuota mensual (resultado de la tabla)
3%193,12 €
4%202,49 €
5%212,13 €
6%222,04 €

Es la misma información que la fila de 10 años de la tabla de dos variables del apartado de ejemplos, pero con una sola entrada. Si mañana quieres ver otro plazo, cambias el plazo en tu hoja y la tabla se recalcula.

📌
La celda de entrada no es la de la fórmulaLa celda de entrada que pide el cuadro no es la celda donde has escrito la fórmula ni donde están los valores de la lista: es la celda original de tu hoja que la fórmula usa como variable, por ejemplo donde normalmente escribes el tipo de interés a mano. Excel la sustituye temporalmente por cada valor de la lista, calcula y copia el resultado en la tabla.

Dos variables: la fórmula, en la esquina superior izquierda

Una tabla de dos variables combina una lista en fila (arriba) con otra en columna (a la izquierda) para mostrar todas las combinaciones posibles de una única fórmula. Es la versión más usada en préstamos, presupuestos y previsiones de ahorro, porque casi siempre hay dos variables que interesa cruzar. La fórmula se coloca en la esquina superior izquierda, donde se cruzan ambas listas. Es la única situación en la que va en esa esquina.

  1. En la esquina superior izquierda del rango, escribe la fórmula que quieres analizar.
  2. A la derecha de esa celda, en la misma fila, escribe la lista de valores de la primera variable (por ejemplo, los tipos de interés).
  3. Debajo de la fórmula, en la misma columna, escribe la lista de valores de la segunda variable (por ejemplo, los plazos en años).
  4. Selecciona todo el rango, incluyendo la fórmula, la fila de valores y la columna de valores.
  5. Ve a Datos → Previsión → Análisis de hipótesis → Tabla de datos.
  6. Rellena Celda de entrada (fila) con la celda que corresponde a los valores escritos en fila, y Celda de entrada (columna) con la que corresponde a los valores escritos en columna.
  7. Pulsa Aceptar. Excel calcula una cuadrícula completa, con un resultado por cada combinación.

Antes de aceptar, comprueba que la fórmula depende realmente de las dos celdas de entrada que vas a indicar y que ambas están en la misma hoja que la tabla. El resto de datos fijos (importe del préstamo, costes fijos, capital inicial) pueden ser referencias normales, porque la tabla no copia la fórmula por la cuadrícula: lo que hace es sustituir los valores en las celdas de entrada. Si dudas sobre cuándo anclar una celda con el signo $, repasa las referencias absolutas y relativas en Excel.

⚠️
Comprueba la selección antes de aceptarRevisa que has seleccionado todo el rango: la fórmula de la esquina, la fila de valores y la columna de valores (en dos variables). Si eliges solo los valores sin la celda de la fórmula, el resultado sale vacío o repetido en todas las celdas.

Elige en segundos entre tabla de datos, Buscar objetivo, escenarios y Solver

Excel ofrece cuatro herramientas para preguntas del tipo «¿y si…?», y es fácil confundirlas. Las tres primeras viven en el menú de Análisis de hipótesis; Solver es un complemento que se activa aparte (Datos → Análisis → Solver). Lo que las distingue es la pregunta que responde cada una.

HerramientaPara qué sirveVariables de entradaResultado
Tabla de datosProbar muchos valores a la vez y ver el resultado de una fórmula para cada uno1 o 2Una cuadrícula completa de resultados
Buscar objetivoEncontrar el valor de entrada exacto que produce un resultado concreto1Un único valor
EscenariosGuardar y comparar conjuntos completos de supuestos con nombre propioVarias a la vez, sin límite fijoResumen con varios escenarios guardados
SolverOptimizar un resultado (maximizar, minimizar o igualar) ajustando varias variables con restriccionesVarias, con restriccionesLa combinación óptima de variables

Dicho en una frase por herramienta: la tabla de datos para ver muchos resultados a la vez; Buscar objetivo cuando ya conoces el resultado que quieres y necesitas averiguar la entrada; los escenarios para guardar y comparar supuestos con nombre propio; y Solver cuando el problema tiene restricciones y quieres que Excel encuentre la combinación óptima.

Cuándo conviene un escenario en lugar de una tabla

La tabla de datos cruza listas: todos los valores de una con todos los de otra, sobre una única fórmula. Un escenario guarda en cambio un conjunto completo de supuestos con un nombre («prudente», «optimista») que puede tocar muchas celdas a la vez. Si tu pregunta es «qué cuota sale con cada interés y cada plazo», la tabla de datos es la respuesta. Si es «qué pasa en el supuesto optimista, con precios, costes, volumen y gastos distintos a la vez», piensa en escenarios.

El límite: máximo dos variables

La limitación más importante de la tabla de datos es que solo admite una o dos variables de entrada. No existe una versión de tres variables: el cuadro de diálogo solo ofrece Celda de entrada (fila) y Celda de entrada (columna), y no hay forma de añadir un tercer campo.

⚠️
Si necesitas más de dos variablesLa tabla de datos deja de ser la herramienta adecuada. Puedes crear varias tablas de dos variables dejando fija la tercera en distintos valores, usar los escenarios para guardar combinaciones completas, o recurrir a Solver si además quieres que Excel busque la combinación óptima con restricciones.

Tres usos con dinero: cuota del préstamo, beneficio del negocio y ahorro final

Los tres ejemplos siguientes comparten el mismo mecanismo (una fórmula en la esquina y dos listas de valores), pero responden a preguntas distintas. Todos usan datos ficticios y sirven para practicar; sustituye las cifras por las tuyas.

Cuota de un préstamo

Fórmula PAGO, con el tipo de interés en fila y el plazo en años en columna. Doce combinaciones de un vistazo.

Beneficio de un negocio

Precio de venta en columna y unidades vendidas en fila. Muestra dónde se pierde y dónde se gana.

Ahorro final

Fórmula VF, con la aportación mensual en columna y el interés en fila, proyectado a 10 años.

Cuota mensual según tipo de interés y plazo

Imagina que tienes un préstamo de 20.000 € y quieres ver cómo cambia la cuota mensual con el tipo de interés anual y el plazo. La cuota la calcula la función PAGO, con una fórmula del estilo =PAGO(interés/12; plazo*12; -importe) que referencia las celdas donde guardas interés y plazo.

Coloca la fórmula PAGO en la esquina superior izquierda, escribe los tipos de interés en la fila superior (3 %, 4 %, 5 % y 6 %) y los plazos en la columna izquierda (5, 10 y 15 años). Al lanzar la tabla con la celda del interés como celda de entrada (fila) y la del plazo como celda de entrada (columna), obtienes las doce cuotas.

Plazo / Interés3%4%5%6%
5 años359,37 €368,33 €377,42 €386,66 €
10 años193,12 €202,49 €212,13 €222,04 €
15 años138,12 €147,94 €158,16 €168,77 €

Importes calculados para un préstamo de 20.000 € con cuotas mensuales. Mira la tabla con ojo analítico: en un plazo de 10 años, pasar del 3 % al 6 % añade 28,92 € a la cuota (de 193,12 € a 222,04 €), mientras que alargar de 5 a 15 años con el 3 % la baja de 359,37 € a 138,12 €. Sustituye importe, interés y plazo por los de tu caso y la tabla recalculará cada cuota. Es una herramienta para comparar cifras, no una recomendación de qué préstamo contratar.

Beneficio según precio de venta y unidades vendidas

Supón un coste unitario de 6 € y unos costes fijos de 2.000 €. La fórmula del beneficio sería =(precio-coste_unitario)*unidades-costes_fijos. La pones en la esquina superior izquierda, escribes los precios en la columna (10 €, 12 € y 15 €) y las cantidades en la fila (500, 1.000 y 1.500), y lanzas la tabla con el precio como entrada de columna y las unidades como entrada de fila.

Precio / Unidades5001.0001.500
10 €0 €2.000 €4.000 €
12 €1.000 €4.000 €7.000 €
15 €2.500 €7.000 €11.500 €

Fíjate en la fila de 10 € con 500 unidades: el beneficio sale exactamente en 0 €, el punto de equilibrio para ese precio (2.000 € de costes fijos entre 4 € de margen por unidad son 500 unidades). Con el precio a 12 € ese umbral baja a unas 334 unidades, y con 15 € a unas 223. Ver estos umbrales de un vistazo, sin recalcular nada a mano, es lo que más aporta la tabla frente a probar números sueltos.

Ahorro final según aportación e interés

Para previsiones de ahorro la tabla combina bien con la función VF (valor futuro). Si aportas una cantidad fija cada mes durante 10 años a un interés anual determinado, la fórmula del ahorro final tiene la forma =VF(interés/12; años*12; -aportación). La lógica completa del cálculo está en el artículo sobre interés compuesto en Excel. Con la aportación mensual en columna y el interés anual en fila, sale la cuadrícula del ahorro proyectado. Si solo quieres variar la aportación y dejar fijo el interés, basta una tabla de una variable; para cruzar ambas, necesitas la de dos.

Aportación mensual / Interés2%4%6%
50 €6.636 €7.362 €8.194 €
100 €13.272 €14.725 €16.388 €
150 €19.908 €22.087 €24.582 €

Son una simulación a 10 años con aportaciones mensuales constantes y tipo fijo; no representan rentabilidades garantizadas ni constituyen asesoramiento financiero. Aun así, la tabla enseña algo útil: al duplicar la aportación se duplica el resultado, mientras que subir el interés del 2 % al 6 % con 100 € al mes aporta unos 3.116 € extra a los 10 años (de 13.272 € a 16.388 €), sobre un total aportado de 12.000 €. Cambia el horizonte, la aportación o el tipo en tu hoja y la tabla se actualizará al volver a lanzar el análisis de hipótesis.

Cinco tropiezos que arruinan una tabla de datos, y cómo esquivarlos

  • La fórmula no está en la celda que toca. Es la causa de casi todas las tablas que no funcionan. En una variable con valores en columna va una fila por encima y una columna a la derecha del primer valor; en dos variables, en la esquina superior izquierda. Si va en cualquier otro sitio, o seleccionas el rango sin incluirla, Excel no puede relacionar entradas y resultado.
  • La celda de entrada está mal elegida o en otra hoja. Debe ser la celda que la fórmula usa realmente como variable y estar en la misma hoja que la tabla; si no, Excel avisa de que la referencia de entrada no es válida.
  • Intentas editar una sola celda del resultado. El resultado es una matriz que ocupa todo el rango a la vez, así que Excel responde «No se puede cambiar parte de una tabla». Para borrarlo o moverlo, selecciona todo el rango de resultados.
  • Dejas el cálculo en automático con una tabla enorme. Cada cambio en la hoja recalcula cientos o miles de combinaciones y el archivo se ralentiza.
  • Das por hecho que caben tres variables. Solo caben dos; para más hay que combinar tablas, escenarios o Solver.

Por qué ves {=TABLA(fila;columna)} en la barra de fórmulas

Al terminar, el rango de resultados queda como un bloque único generado con la función {=TABLA(fila;columna)}, que ocupa todas las celdas de resultado a la vez. No es un error ni una fórmula rota: es la manera en que Excel identifica internamente los resultados de una tabla de datos. No puedes escribirla a mano; solo aparece cuando generas la tabla desde el menú de análisis de hipótesis.

Cómo congelar los resultados como valores

Si quieres editar un resultado concreto o dejar de depender de las celdas de entrada, conviértelo en valores normales:

  1. Selecciona todo el rango de resultados de la tabla de datos.
  2. Cópialo con Ctrl+C.
  3. Pega con Pegado especial → Valores sobre el mismo rango o en otra ubicación.
  4. A partir de ese momento cada celda es independiente y puedes editarla o borrarla suelta.

Cómo controlar el recálculo con F9

En Archivo → Opciones → Fórmulas → Opciones de cálculo puedes elegir Automático excepto en tablas de datos. Con esa opción, el resto del libro se recalcula en tiempo real y las tablas solo se actualizan cuando tú lo pides. Para pedirlo, pulsa F9: recalcula todo el libro, incluidas las tablas pendientes. Si solo te interesa la hoja activa, usa Mayús+F9.

💡
Antes de guardar, recalculaSi trabajas con las tablas de datos en cálculo manual, pulsa F9 justo antes de guardar o de enviar el archivo, para que quien lo reciba vea resultados que coinciden con los datos actuales y no con una versión desactualizada.

Si trabajas en Google Sheets

Google Sheets no tiene un equivalente nativo: no existe un menú que sustituya una celda de entrada por una lista de valores y devuelva una cuadrícula. Es una de las diferencias más citadas entre ambas hojas de cálculo para el análisis financiero. Las salidas son artesanales:

  1. Construir la cuadrícula a mano: escribir la fórmula en cada celda de resultado, adaptada para referenciar su fila y su columna con funciones como INDIRECTO o con referencias mixtas.
  2. Usar ARRAYFORMULA combinada con funciones como PAGO o VF para generar varias combinaciones en una sola fórmula matricial.
  3. Recurrir a complementos de análisis de escenarios del Marketplace de Google Workspace, aunque ninguno replica exactamente el comportamiento de Excel.
  4. Si el archivo se comparte con alguien que tiene Excel, crear la tabla de datos allí y pasar a Sheets solo los resultados calculados.

Ninguna es tan directa como el menú de Excel: en Sheets siempre implican escribir a mano la lógica de la cuadrícula, mientras que en Excel el programa sustituye los valores y calcula por ti.

Una rutina de comprobación antes de fiarte de la tabla

Cuando una tabla te devuelva cifras, dedica un minuto a verificarla. Elige una combinación cualquiera, escribe a mano esos valores en las celdas de entrada de tu hoja y comprueba que el resultado de la fórmula coincide con el que muestra la tabla en esa casilla. Si coincide, el montaje es correcto. Después vuelve a poner en las celdas de entrada tus valores habituales. Esta prueba te protege de la trampa más común: una tabla bien formada, pero con las celdas de entrada cruzadas (el plazo donde iba el interés), que da números plausibles y equivocados.

Ocho preguntas incómodas sobre la tabla de datos que casi nadie se atreve a hacer

¿Qué es exactamente una tabla de datos en Excel?
Una herramienta de análisis de hipótesis que calcula el resultado de una fórmula para toda una lista de valores de entrada en una sola operación, sin copiar la fórmula ni repetir el cálculo a mano para cada valor.
¿Cuántas variables admite?
Como máximo dos: una en fila y otra en columna. Para tres o más hay que combinar varias tablas de dos variables o recurrir a los escenarios o a Solver.
¿En qué se diferencia de Buscar objetivo?
La tabla de datos prueba muchos valores de entrada y muestra todos los resultados a la vez. Buscar objetivo hace el camino contrario: parte de un resultado que quieres conseguir y calcula el único valor de entrada necesario.
¿Por qué me salen resultados repetidos o errores?
Casi siempre porque la fórmula no está en la celda exacta que le corresponde, porque la celda de entrada indicada no es la que realmente usa la fórmula, o porque esa celda está en otra hoja: debe estar en la misma hoja que la tabla.
¿Puedo borrar o editar una sola celda del resultado?
No. El resultado es una matriz {=TABLA(…)} que ocupa todo el rango, así que para borrarla o moverla hay que seleccionarlo entero. Para editar celdas sueltas, copia el resultado y pégalo como valores con Pegado especial.
¿Cómo evito que ralentice mi archivo?
Cambia el modo de cálculo a Automático excepto en tablas de datos en Archivo → Opciones → Fórmulas, y pulsa F9 cuando quieras actualizar los resultados.
¿Existe la tabla de datos en Google Sheets?
No de forma nativa. Hay que recrear la cuadrícula con fórmulas, referencias mixtas o ARRAYFORMULA, o calcularla en Excel y pasar solo los resultados.
¿Funciona con funciones como PAGO o VF?
Sí, es uno de los usos más habituales. Sirve con cualquier fórmula, financieras incluidas, siempre que dependa de las celdas de entrada que uses en la tabla.

En definitiva: cuando le cojas el truco a colocar la fórmula en la esquina correcta, la tabla de datos te ahorra mucho tiempo en cada comparación rápida de cifras. Y si tu duda no es «qué pasa con muchos valores» sino «qué valor necesito para llegar a un resultado», salta a Buscar objetivo.

¿Comparas escenarios de números? Nuestras plantillas ya traen las simulaciones montadas para que no empieces en blanco.

Ver la plantilla de contabilidad

Excel® es una marca registrada de Microsoft Corporation. Este sitio no está afiliado a Microsoft.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *