Excel avanzado (Microsoft Excel 2019): fórmulas, búsqueda, tablas dinámicas, macros y análisis de datos

1.1 La fórmula

Toda fórmula comienza por =. Sus operadores, por orden de precedencia:

Orden	Operadores
1	Operadores de referencia: dos puntos (rango), espacio (intersección) y punto y coma (unión)
2	− (negación: el signo menos delante de un valor)
3	% (porcentaje)
4	^ (exponenciación)
5	* y /
6	+ y −
7	& (concatenación de texto)
8	= <> > < >= <= (comparación)

Los paréntesis alteran ese orden: lo que va entre ellos se calcula primero. Un efecto que sorprende: como la negación va antes que la potencia, =-2^2 da 4 y no −4; para obtener −4 hay que escribir =-(2^2).

Modo de cálculo. Por defecto Excel recalcula solo (automático). En Fórmulas → Opciones para el cálculo puede ponerse en manual, y entonces los resultados no se actualizan hasta pulsar F9 (recalcular las fórmulas pendientes de todos los libros abiertos; Mayús+F9, solo la hoja activa; Ctrl+Alt+F9 fuerza todas).

1.2 Los tres tipos de referencia

Es el concepto que más rendimiento da en el examen y en el trabajo real:

Tipo	Escritura	Al copiar la fórmula
Relativa	A1	Se adapta: cambian fila y columna
Absoluta	$A$1	No cambia ni fila ni columna
Mixta	$A1 o A$1	Se fija solo lo precedido por el símbolo

La tecla F4 recorre las cuatro combinaciones sobre la referencia seleccionada.

En la siguiente figura se ilustra la diferencia visual y de comportamiento entre estos tipos de referencia:

Ejemplo. Para multiplicar una columna de importes por un tipo de IVA situado en B1, la fórmula debe ser =A2*$B$1. Si se escribe =A2*B1 y se copia hacia abajo, la segunda referencia se desplaza a B2, B3… y el resultado es basura. Es el error número uno de las hojas de cálculo mal construidas.

Las referencias mixtas son imprescindibles cuando se copia en dos direcciones, como en una tabla de doble entrada: se fija la columna en un factor y la fila en el otro.

1.3 Referencias a otras hojas y libros

• A otra hoja: =Hoja2!A1.
• Referencia tridimensional, a la misma celda en varias hojas consecutivas: =SUMA(Hoja1:Hoja3!A1).
• A otro libro: la referencia incluye el nombre del archivo entre corchetes; si el libro está cerrado, incorpora también la ruta.

1.4 Nombres definidos

Un nombre asigna una etiqueta a una celda, un rango, una constante o una fórmula. Se gestionan con el Administrador de nombres.

• Ámbito: puede ser de libro —utilizable en cualquier hoja— o de hoja.
• Reglas: no pueden contener espacios, deben empezar por letra, guion bajo o barra invertida, y no pueden parecerse a una referencia de celda válida.
• Ventaja: =Ventas*IVA es legible y se mantiene solo; =B2*$H$1 exige recordar qué hay en cada celda.

1.5 Los errores y cómo tratarlos

Error	Causa habitual
#¡DIV/0!	División por cero o por celda vacía
#N/D	Valor no encontrado en una búsqueda
#¡VALOR!	Tipo de dato incorrecto: texto donde se espera número
#¡REF!	Referencia destruida: se ha eliminado la celda a la que apuntaba
#¿NOMBRE?	Nombre o función mal escritos
#¡NUM!	Argumento numérico no válido
#¡NULO!	Intersección de rangos que no se cruzan

Se controlan con SI.ERROR, que devuelve un valor alternativo cuando la fórmula falla, y con las funciones de información ESERROR, ESERR y ESNOD; para el error #N/D, SI.ND devuelve un valor alternativo. Conviene no abusar de SI.ERROR: oculta el error, pero no lo corrige, y una hoja llena de ceros que en realidad son fallos es peor que una hoja con errores visibles.

Referencia circular: una fórmula que se refiere a sí misma, directa o indirectamente. Excel avisa y devuelve cero salvo que se habilite el cálculo iterativo.