Referencias en Excel: 10 ejercicios para oposiciones

Para resolver ejercicios de referencias en Excel, trata por separado la letra de columna y el número de fila. El signo dólar fija la parte que aparece justo después: en $C8 queda fija la columna; en C$8, la fila; en $C$8, ambas.

Este cuaderno contiene diez ejercicios originales para oposiciones, con solución desplegable y explicación del error que conviene evitar. Practica desplazamientos en las dos direcciones, rangos, una tabla de doble entrada y la diferencia entre copiar y mover. No reproduce preguntas de un examen oficial.

El curso del bloque específico de Administrativo C1 de La Rioja incluye un tema de Excel 2016. Las bases de esa convocatoria, anexo II, recogen Excel 2016 y fórmulas y funciones en la parte de informática. Su tercer ejercicio tiene veinte preguntas de ofimática en conjunto: no son veinte preguntas dedicadas exclusivamente a Excel. Puedes consultar la guía de Administrativo C1 de La Rioja para situar esta práctica.

Antes de copiar: cuatro formas de escribir una referencia

Referencia	Columna al copiar	Fila al copiar	Lectura útil
C8	Se ajusta	Se ajusta	Relativa en las dos partes
$C$8	Se conserva	Se conserva	Absoluta en las dos partes
$C8	Se conserva	Se ajusta	Mixta: columna fija
C$8	Se ajusta	Se conserva	Mixta: fila fija

La documentación de Microsoft explica este comportamiento en referencias relativas, absolutas y mixtas. La fijación se refiere a la copia de la fórmula: no equivale a proteger una celda contra ediciones.

Trabajaremos en estilo A1, dentro de una misma hoja, sin insertar ni eliminar filas o columnas. Cuando un caso diga «copiar», se pega la fórmula, no solo su valor. Las celdas de destino están dentro de los límites de la hoja. No se utilizan referencias estructuradas de tablas, nombres definidos ni vínculos entre libros.

El método de los dos desplazamientos

Primero calcula cuánto se mueve la celda que contiene la fórmula. Si copias de F6 a H9, avanzas dos columnas y tres filas. Ese es el desplazamiento que debes aplicar a cada parte relativa de sus referencias.

Después recorre cada referencia y decide qué puede moverse. No desplazas todas las letras por ver una columna nueva: una columna precedida de dólar conserva su letra. Lo mismo ocurre con las filas.

Por último comprueba la fórmula completa, incluidos ambos extremos de los rangos. Si una fórmula contiene dos referencias con distinto tipo de fijación, cada una sigue su propia regla.

Un control útil es expresar el desplazamiento con signo: derecha y abajo son positivos; izquierda y arriba, negativos. Así, ir de G9 a E6 significa dos columnas a la izquierda y tres filas hacia arriba. El caso 5 entrena precisamente ese recorrido.

10 ejercicios de referencias con solución

Escribe primero la fórmula que quedaría en el destino. No necesitas conocer los valores de las celdas salvo cuando el enunciado los proporciona. La pregunta principal es qué fórmula se copia, no qué número mostraría una hoja cuyos datos no conocemos.

1. Copiar hacia abajo

En D4 está =B4+C4. Copias la fórmula a D7. ¿Qué fórmula queda?

Ver solución del ejercicio 1

Fórmula al copiar: =B7+C7.

El desplazamiento es de cero columnas y tres filas hacia abajo. Las dos referencias son relativas: B y C conservan sus columnas porque no hay desplazamiento horizontal, y las filas pasan de 4 a 7. Que una letra no cambie en esta copia no convierte la referencia en absoluta.

2. Copiar hacia la derecha

Copias de E6 a G6 la fórmula =C6*D6. ¿Cuál obtienes?

Ver solución del ejercicio 2

Fórmula al copiar: =E6*F6.

Avanzas dos columnas sin cambiar de fila. C pasa a E y D a F; el 6 permanece por el desplazamiento vertical nulo. No se suman dos unidades a todos los caracteres de una dirección: columnas y filas se calculan por separado.

3. Un parámetro fijo

En C5 figura =A5*$H$2. Se copia a C8. Escribe la fórmula resultante.

Ver solución del ejercicio 3

Fórmula al copiar: =A8*$H$2.

A5 baja tres filas y se convierte en A8. La referencia $H$2 conserva columna y fila: los dos signos dólar fijan sus dos componentes. Solo el primer factor cambia. Fijar toda la fórmula impediría que cada fila usara su propio dato de la columna A.

4. Dos referencias mixtas a la vez

Copias =$B6*C$2 desde D6 hasta F9. ¿Qué fórmula queda?

Ver solución del ejercicio 4

Fórmula al copiar: =$B9*E$2.

Hay dos columnas a la derecha y tres filas abajo. En $B6, B está fija y la fila pasa a 9. En C$2, la columna pasa de C a E y la fila 2 queda fija. El resultado no lleva una sola regla común para ambos factores: cada signo dólar se interpreta en su posición.

5. Copiar hacia arriba y a la izquierda

En G9 tienes =E9+$C$4 y copias a E6. Determina la fórmula de destino.

Ver solución del ejercicio 5

Fórmula al copiar: =C6+$C$4.

Retrocedes dos columnas y tres filas. E9 se convierte en C6; $C$4 permanece fija. El error típico es sumar el desplazamiento cuando el destino está antes del origen. Señalar primero «−2 columnas, −3 filas» evita invertir el sentido.

6. Una tabla de doble entrada

La columna B contiene cantidades y la fila 2 contiene factores. En C3 se escribe =$B3*C$2 y se copia a D5. Allí, B5 vale 7 y D2 vale 12. ¿Qué fórmula y resultado corresponden?

Ver solución del ejercicio 6

Fórmula al copiar: =$B5*D$2.

La fórmula resultante toma la cantidad de B5 y el factor de D2: 7 × 12 = 84. La columna B permanece fija al desplazarse horizontalmente, y la fila 2 permanece fija al bajar. La otra parte de cada referencia sí cambia. Una referencia totalmente absoluta para los dos encabezados impediría recorrer correctamente la tabla.

7. Acumular desde un inicio fijo

En D5 está =SUMA($B$2:B5). Al copiarla a D8, ¿qué rango suma la fórmula?

Ver solución del ejercicio 7

Fórmula al copiar: =SUMA($B$2:B8).

El inicio $B$2 sigue fijo; el extremo B5 baja a B8. La fórmula suma desde B2 hasta B8, ambos incluidos. No suma únicamente las nuevas filas ni desplaza todo el rango a B5:B8. Este patrón permite ampliar un acumulado manteniendo su punto de partida.

8. Comparar copiar con cortar y pegar

En dos hojas independientes con los mismos datos, D4 contiene =B4/$B$10. En una copias la celda a F6; en la otra la mueves mediante cortar y pegar a F6. No realizas ninguna otra operación. ¿Qué fórmula queda en cada hoja?

Ver solución del ejercicio 8

Fórmula al copiar: =D6/$B$10.

Al copiar: =D6/$B$10, porque el desplazamiento es dos columnas a la derecha y dos filas abajo. Al mover mediante cortar y pegar: =B4/$B$10, sin ajustar esas referencias. Se está moviendo la celda que contiene la fórmula, no las celdas a las que apunta. Copiar el texto desde la barra de fórmulas o pegar solo valores serían operaciones distintas.

9. Cada extremo del rango tiene su regla

Copias desde H7 a J10 la fórmula =SUMA(B$2:$E7). ¿Cómo queda el rango?

Ver solución del ejercicio 9

Fórmula al copiar: =SUMA(D$2:$E10).

En el primer extremo, B cambia a D y la fila 2 queda fija. En el segundo, E queda fija y la fila 7 pasa a 10. Se obtiene el rango D2:E10. Un rango no tiene un único tipo de referencia aplicado en bloque: cada componente de sus extremos puede ser relativo o absoluto.

10. Una dirección escrita como texto

En F4 está =SI(A4="B2";$C4;D$1). La copias a H6. ¿Qué partes cambian y qué ocurre con "B2"?

Ver solución del ejercicio 10

Fórmula al copiar: =SI(C6="B2";$C6;F$1).

A4 pasa a C6, $C4 pasa a $C6 y D$1 pasa a F$1. El texto "B2" permanece igual: las comillas indican una cadena, no una referencia a la celda B2. La función compara el contenido de C6 con ese texto y elige una de las dos ramas. No sustituye automáticamente direcciones dentro de cadenas.

Comprueba el tipo de operación

Microsoft distingue expresamente mover y copiar una fórmula. En el caso 8 no cambian los datos de origen ni la estructura de la hoja: solo cambia la operación aplicada a la celda de la fórmula.

Otra posibilidad es pegar valores. En ese caso se pega el resultado disponible, no una fórmula con referencias que se ajusten. Saber qué valor se pegaría requiere conocer el resultado de origen; no basta con mirar una dirección de celda.

El caso 10 añade una distinción de sintaxis: el texto entre comillas pertenece al contenido literal de una fórmula. La introducción de Microsoft a las fórmulas separa referencias, constantes, funciones y operadores. Aquí usamos SI y SUMA con nombres en español y punto y coma en SI; la configuración regional y el idioma pueden cambiar esa escritura.

Cómo practicarlo en el programa

Introduce una de las fórmulas en su celda de origen y cópiala al destino indicado dentro de una hoja de práctica vacía. Revisa la barra de fórmulas: dos expresiones distintas pueden mostrar el mismo número si los datos coinciden, por lo que el resultado visible no basta para comprobar una referencia.

Para el caso 6, coloca además 7 en B5 y 12 en D2; después de copiar debe obtenerse 84. Para el caso 8 usa hojas separadas, porque copiar primero y mover después en la misma hoja ya sería otra secuencia de operaciones.

Mientras editas una referencia, F4 permite alternar sus tipos en Excel para Windows, según la documentación enlazada. En Mac, Microsoft documenta Comando + T para cambiar las combinaciones. No conviertas un atajo de una plataforma en una regla universal: también puedes escribir los signos dólar directamente.

Qué repasar según tu error

Si fallaste 1 o 2, calcula por separado desplazamiento horizontal y vertical. Si fallaste 3 o 4, señala qué parte aparece después de cada dólar. Si fallaste 5, anota el signo del desplazamiento antes de transformar referencias.

Los fallos en 6, 7 y 9 suelen revelar que se intenta fijar todo por precaución: explica qué debe conservarse y qué debe recorrer filas o columnas. En 8 y 10, identifica primero la operación y los tipos de elementos de la fórmula; aplicar una regla de copia donde no corresponde produce una respuesta incorrecta aunque la aritmética sea buena.

Después integra estos ejercicios en el tema de Excel del curso de Administrativo C1 de La Rioja. El curso tiene un alcance concreto dentro del programa: esta práctica trabaja referencias de celdas y no sustituye el estudio de Word, Access ni los demás contenidos de Excel.

Preguntas frecuentes

¿Qué diferencia hay entre referencia relativa, absoluta y mixta?

Al copiar una fórmula, una referencia relativa ajusta columna y fila; una absoluta conserva ambas; una mixta conserva solo la columna o solo la fila. El signo dólar fija la parte que aparece inmediatamente después.

¿Qué fija la referencia $B7?

Fija la columna B, pero mantiene relativa la fila 7. Al copiar una fórmula dos filas hacia abajo, esa referencia se convierte en $B9; al copiarla solo horizontalmente, conserva $B7.

¿Qué diferencia hay entre B$7 y $B$7?

B$7 conserva la fila 7 y permite que cambie la columna al copiar. $B$7 conserva tanto la columna B como la fila 7 durante la copia de la fórmula.

¿Copiar y mover una fórmula cambian igual sus referencias?

No. Al copiar se ajustan las partes relativas de las referencias. Al mover la celda de la fórmula mediante cortar y pegar, sus referencias permanecen iguales en el supuesto aquí estudiado, sin otras modificaciones de la hoja.

¿Una referencia absoluta queda protegida contra cualquier cambio?

No. Los signos dólar controlan el ajuste de columna y fila al copiar la fórmula. No protegen la hoja, no impiden editar la fórmula y no garantizan que la referencia permanezca idéntica ante operaciones estructurales como insertar filas.