Excel 2021 para las oposiciones de RTVE: referencias, funciones y ejercicios resueltos

En esta guía:

• Visión general y esquema del tema
• Referencias relativas, absolutas y mixtas
• Anidación de funciones
• Resumen y trampas de examen
• Test de 15 preguntas

Visión general y esquema del tema

Este tema desarrolla el programa oficial de Ofimática avanzada: Microsoft Excel 2021, común a las especialidades de Gestión Comercial (tema 6) y Gestión Económica (tema 5). El examen no pide recordar menús de memoria, sino saber qué devuelve una fórmula, cómo se comporta al copiarla, qué herramienta resuelve un problema concreto y qué errores aparecen cuando algo se hace mal. Por eso el tema combina explicación, fórmulas de ejemplo con su resultado y «trampas de examen».

Convenciones que se usan en todo el tema

• Los nombres de las funciones se dan en español de España, que es la interfaz habitual en RTVE, con su nombre inglés entre paréntesis la primera vez: por ejemplo BUSCARX (XLOOKUP).
• En la configuración regional española el separador de argumentos es el punto y coma (;) y el separador decimal es la coma. Por eso se escribe =REDONDEAR(2,345;2) y no =ROUND(2.345,2).
• Los valores lógicos se llaman VERDADERO y FALSO, y los errores se muestran traducidos: #¡DIV/0!, #N/D, #¡VALOR!, #¡REF!, #¿NOMBRE?, #¡NUM!, #¡NULO!, #¡DESBORDAMIENTO! y #¡CALC!.

Tabla de datos de referencia

Casi todos los ejemplos usan la misma tabla, situada en el rango A1:F9 de una hoja llamada Ventas. Los datos ocupan las filas 2 a 9 en el orden mostrado (la fila 1 son los encabezados) y la columna F contiene la fórmula =D2*E2 copiada hacia abajo.

La suma de la columna F es 61.050. Conviene tener esta tabla delante al leer el resto del tema.

Esquema del programa

1. a. Funciones y fórmulas avanzadas: sintaxis, operadores, prioridad, nombres, funciones condicionales de agregación, SUMAPRODUCTO, LET y matrices dinámicas.
2. b. Referencias relativas, absolutas y mixtas, y su comportamiento al copiar fórmulas.
3. c. Anidación de funciones: cómo se construye, se lee y se depura una fórmula anidada.
4. d. Funciones lógicas, de búsqueda y de texto.
5. e. Tablas y tablas dinámicas: creación, elementos, campos calculados y segmentación de datos.
6. f. Validación de datos y formato condicional.
7. g. Macros: concepto, grabación, ejecución, formatos de archivo y seguridad.
8. h. Análisis de datos: estadística descriptiva, media móvil y regresión.

Trampa de examen: Excel 2021 es una versión «perpetua» (licencia de pago único). Incluye BUSCARX, LET, FILTRAR, ORDENAR, UNICOS y SECUENCIA, pero no incluye funciones que llegaron después solo a Microsoft 365, como LAMBDA, TEXTOANTES, TEXTODESPUES, DIVIDIRTEXTO o APILARV. Si una opción se apoya en ellas para «Excel 2021», es incorrecta.

Referencias relativas, absolutas y mixtas

Qué es una referencia

Una referencia identifica una celda o un rango por su columna y su fila. El estilo habitual es A1 (letra de columna y número de fila). Lo que distingue a los tipos de referencia es qué ocurre al copiar la fórmula a otra celda: Excel guarda internamente la posición relativa de la celda referenciada respecto a la que contiene la fórmula, salvo en las partes fijadas con el signo $.

Al editar una referencia, la tecla F4 recorre cíclicamente los cuatro estados: A1 → $A$1 → A$1 → $A1 → A1.

Copia de una fórmula relativa

Si en F2 está =D2*E2 y se copia a F5, la fórmula pasa a =D5*E5: se ha desplazado tres filas y las referencias se desplazan lo mismo. Si se copia de F2 a G2 (una columna a la derecha), se convierte en =E2*F2. Esta es la conducta deseada cuando la operación es «la misma para cada fila».

Referencia absoluta: un dato común a todas las filas

Supongamos que en H1 está el tipo de IVA (21 %) y queremos el importe con IVA en G2:G9. Si escribimos =F2*(1+H1) y copiamos hacia abajo, en G3 aparecerá =F3*(1+H2), y H2 está vacía: el resultado sería el importe sin IVA. La fórmula correcta es =F2*(1+$H$1). Para la fila 2 devuelve 12.000 · 1,21 = 14.520.

Ejemplo resuelto: en G2 se escribe =F2/$F$10, donde F10 contiene el total =SUMA(F2:F9) = 61.050. Al copiar a G9, la fórmula es =F9/$F$10, que da 4.500 / 61.050 = 0,0737 (7,4 %). Sin los dólares, en G9 se leería =F9/F17, una celda vacía, y aparecería #¡DIV/0!.

Referencias mixtas: la tabla de doble entrada

El caso clásico es una tabla de multiplicar o una tabla de precios con descuentos. Con los precios en A2:A5 y los porcentajes de descuento en B1:E1, la fórmula de B2 debe multiplicar «el precio de su fila» por «el descuento de su columna»:

`` B2: =$A2*(1-B$1) ``

Al copiarla a D4 se convierte en =$A4*(1-D$1): la columna A queda fija porque los precios siempre están en A, y la fila 1 queda fija porque los descuentos siempre están en la fila 1. Con una sola fórmula se rellena toda la tabla.

Ejemplo resuelto: en C3 hay =$A3*B$1. Se copia a E6. El desplazamiento es de 2 columnas y 3 filas. $A3 mantiene la A y cambia la fila: $A6. B$1 cambia la columna dos posiciones y mantiene la fila: D$1. Resultado: =$A6*D$1.

Copiar frente a mover

Cuando se copia una celda (Ctrl+C / Ctrl+V o con el controlador de relleno), las referencias relativas se ajustan. Cuando se mueve (cortar y pegar o arrastrar el borde), la fórmula conserva exactamente las mismas referencias. Además, si se mueven las celdas referenciadas, las fórmulas que apuntan a ellas se actualizan solas. Si se eliminan filas o columnas referenciadas, aparece #¡REF!.

Referencias a otras hojas y libros

• Otra hoja: =Ventas!F2 o, si el nombre tiene espacios, ='Ventas 2026'!F2.
• Otro libro: =[Presupuesto.xlsx]Hoja1!$B$4. Al cerrar el libro de origen se muestra la ruta completa.
• Referencia 3D: =SUMA(Enero:Diciembre!B5) suma la celda B5 de todas las hojas situadas entre Enero y Diciembre.

Referencias estructuradas en tablas

Dentro de una tabla de Excel (apartado e) las fórmulas usan nombres de columna: =[@Unidades]*[@Precio] en lugar de =D2*E2. El símbolo @ significa «la fila actual». Fuera de la tabla, =SUMA(Ventas[Importe]) suma la columna entera y se amplía sola al añadir filas.

Trampa de examen: las referencias estructuradas a una columna completa (Ventas[Importe]) se comportan como absolutas al copiar la fórmula, pero al arrastrar en horizontal con el controlador de relleno se desplazan a la columna siguiente. Para fijarlas en arrastres horizontales se escribe Ventas[[Importe]:[Importe]].

El estilo F1C1

En Archivo › Opciones › Fórmulas puede activarse el estilo de referencia F1C1, donde las celdas se identifican por fila y columna numéricas y las relativas se expresan con corchetes: F[1]C[-2] es «una fila más abajo y dos columnas a la izquierda». Las macros grabadas usan este estilo internamente (FormulaR1C1), por eso conviene reconocerlo.

Anidación de funciones

Concepto

Anidar es usar una función como argumento de otra. Excel calcula de dentro hacia fuera: primero la función más interna y su resultado pasa a la siguiente. Excel 2021 admite hasta 64 niveles de anidación y hasta 255 argumentos por función, aunque una fórmula legible rara vez necesita más de tres o cuatro niveles.

Método para construir una fórmula anidada

1. Escribir en lenguaje natural lo que se quiere: «si la búsqueda falla, mostrar "No existe"; si no, el importe redondeado a miles».
2. Resolver por separado la pieza interna en una celda auxiliar y comprobar su resultado.
3. Sustituir la referencia a la celda auxiliar por la fórmula interna, sin el signo =.
4. Comprobar el número de paréntesis: Excel colorea cada pareja y, al cerrar uno, resalta su apertura.

Ejemplo resuelto: =REDONDEAR(PROMEDIO(SI(A2:A9="Norte";F2:F9));-3). La parte SI(A2:A9="Norte";F2:F9) devuelve una matriz con 12.000, FALSO, 3.750, FALSO, FALSO, 6.000, FALSO, FALSO. PROMEDIO ignora los valores lógicos dentro de matrices y calcula (12.000 + 3.750 + 6.000) / 3 = 7.250. REDONDEAR(…;-3) redondea a miles: 7.000. En Excel 2021 esta fórmula funciona sin Ctrl+Mayús+Intro gracias a las matrices dinámicas.

Ejemplos habituales de anidación

Herramientas para depurar

• Fórmulas › Evaluar fórmula ejecuta la fórmula paso a paso y muestra el resultado de cada parte subrayada.
• Seleccionar una parte de la fórmula en la barra y pulsar F9 muestra su resultado (luego Esc para no sustituirla).
• Rastrear precedentes y Rastrear dependientes dibujan flechas entre celdas relacionadas.
• La información de herramientas de la función (la etiqueta que aparece al escribir) indica en negrita qué argumento se está escribiendo.

Trampa de examen: cuando se anida una función que devuelve un error, el error se propaga hacia fuera salvo que una función exterior lo capture (SI.ERROR, SI.ND). =SUMA(5;#N/D) devuelve #N/D, no 5.

Anidación de SI frente a SI.CONJUNTO

Antes de Excel 2019 la forma de evaluar varios tramos era anidar SI. Con una comisión del 2 % hasta 5.000 €, del 3 % hasta 10.000 € y del 5 % por encima:

`` =SI(F2<=5000;2%;SI(F2<=10000;3%;5%)) =SI.CONJUNTO(F2<=5000;2%;F2<=10000;3%;VERDADERO;5%) ``

Ambas devuelven 5 % para la fila 2 (12.000 €) y 3 % para la fila 5 (7.200 €). El orden de las condiciones es decisivo: se devuelve el valor de la primera condición verdadera.

Resumen y trampas de examen

Ideas clave por apartado

• a. Fórmulas: prioridad negación › % › ^ › / › + − › & › comparación. En «.CONJUNTO» el rango de agregación va primero y todos los criterios se combinan con Y. LET define variables y su último argumento es el cálculo. Las matrices dinámicas se derraman; #¡DESBORDAMIENTO! indica celdas ocupadas y #¡CALC! un filtro vacío sin si_vacío*.
• b. Referencias: F4 alterna A1 → $A$1 → A$1 → $A1. El $ fija lo que va detrás. Copiar ajusta las relativas; mover no.
• c. Anidación: se evalúa de dentro hacia fuera; hasta 64 niveles; los errores se propagan salvo que se capturen; se depura con Evaluar fórmula y F9.
• d. Lógicas: SI sin tercer argumento devuelve FALSO; SI.CONJUNTO sin coincidencia devuelve #N/D; SI.ND solo captura #N/D.
• d. Búsqueda: BUSCARV y COINCIDIR son aproximados por defecto; BUSCARX y COINCIDIRX son exactos por defecto. BUSCARX devuelve a la izquierda, busca desde el final y tiene argumento si_no_se_encuentra.
• d. Texto: ENCONTRAR distingue mayúsculas, HALLAR no. SUSTITUIR cambia por contenido y REEMPLAZAR por posición. TEXTO devuelve texto que SUMA ignora.
• e. Tablas: SUBTOTALES(109;…) excluye filas ocultas; las referencias estructuradas se amplían solas.
• e. Tablas dinámicas: hay que actualizar (Alt+F5); el campo calculado opera sobre sumas; la segmentación puede controlar varias tablas dinámicas de la misma caché; la escala de tiempo solo trabaja con fechas.
• f. Validación: solo Detener impide el dato; no controla lo pegado. Formato condicional: la fórmula se escribe para la celda activa y =$F2>… colorea filas enteras.
• g. Macros: VBA; .xlsx no guarda macros; PERSONAL.XLSB las hace globales; Alt+F8 ejecutar, Alt+F11 editor; no se pueden deshacer; la opción predeterminada es deshabilitar con notificación.
• h. Análisis: el complemento genera valores estáticos; media móvil con intervalo 3 por defecto y #N/D iniciales; en regresión primero y, luego x.

Las diez trampas más preguntadas

Test: 15 preguntas explicadas

Preguntas reales de la app sobre esta materia. Elige una respuesta y verás la explicación.

Dónde entra en tu especialidad

• Temario y test de Comercial y Audiencias: tema 6
• Temario y test de Económica: tema 5

Fuentes

• Microsoft Learn · Referencia de VBA para Excel
• Microsoft Soporte · Funciones de Excel (por categoría)
• Microsoft Soporte · Función BUSCARX