Tema 3: Fórmulas, Tipos de Referencias (Relativas, Absolutas y Mixtas) y Funciones Oficiales de Cálculo

3.1. ESTRUCTURA DE FÓRMULAS Y OPERADORES EN EXCEL 2016

Una fórmula en Microsoft Excel es una secuencia de valores constantes, referencias a celdas, nombres definidos, funciones u operadores que genera un nuevo valor a partir de datos existentes[cite: 5]. Toda fórmula debe comenzar obligatoriamente con el signo igual (=) o el signo más (+) (Excel convierte automáticamente un inicio con + o - antecediendo un igual)[cite: 5]. Si se introduce texto u operandos sin este prefijo, Excel lo interpretará como una cadena de texto plano o un valor constante numérico[cite: 5].

Anatomía de una Fórmula

Las fórmulas se componen de los siguientes elementos estructurales[cite: 5]:

• Constantes: Valores numéricos o cadenas de texto escritas directamente (ej. 100, "Aprobado")[cite: 5]. El texto literal siempre debe ir delimitado por comillas dobles ("")[cite: 5].
• Referencias a celdas: Direcciones que apuntan al contenido de otras celdas (ej. A1, $B$5)[cite: 5].
• Operadores: Símbolos que especifican el tipo de cálculo numérico, lógico o textual a realizar[cite: 5].
• Funciones: Fórmulas predefinidas que ejecutan cálculos complejos utilizando valores específicos denominados argumentos[cite: 5].

Tipos de Operadores y Jerarquía de Precedencia Completa

En las pruebas tipo test de oposición se evalúa con frecuencia el orden exacto de resolución interna de operaciones complejas cuando no existen paréntesis expresos[cite: 5]. En caso de operadores con el mismo nivel de prioridad, Excel evalúa la expresión estrictamente de izquierda a derecha[cite: 5].

---

3.2. TIPOS DE REFERENCIAS A CELDAS (RELATIVAS, ABSOLUTAS Y MIXTAS)

El comportamiento de las referencias al copiar, cortar y pegar fórmulas es una materia fija en las pruebas objetivas de la USAL[cite: 5]. Una referencia identifica una celda o rango de celdas en una hoja de cálculo[cite: 5]. El símbolo del dólar ($) actúa como un "fijador" que bloquea el desplazamiento de la columna (letra) o de la fila (número) cuando la fórmula es copiada a otra celda[cite: 5].

1. Referencia Relativa (Ejemplo: A1)

No contiene el símbolo $[cite: 5]. Representa una posición relativa respecto a la celda que contiene la fórmula[cite: 5]. Al copiar la celda hacia otra ubicación, la referencia **se desplaza automáticamente** exactamente el mismo número de filas y columnas que se haya movido la celda de destino[cite: 5].

2. Referencia Absoluta (Ejemplo: $A$1)

Lleva el signo $ fijando tanto la columna como la fila[cite: 5]. Representa un punto fijo e inamovible en la hoja[cite: 5]. Al copiar la fórmula a cualquier otra celda, la referencia **permanece exactamente inalterada**[cite: 5]. En la edición de fórmulas, presionar la tecla F4 conmuta cíclicamente los tipos de referencias[cite: 5]:

A1 → (F4) → $A$1 → (F4) → A$1 → (F4) → $A1 → (F4) → A1

3. Referencia Mixta (Ejemplos: $A1 o A$1)

Consiste en fijar únicamente la columna o la fila[cite: 5]:

• $A1 (Columna fija, Fila relativa): La columna A permanece bloqueada al copiar horizontalmente, pero la fila varía libremente si la fórmula se copia hacia arriba o hacia abajo[cite: 5].
• A$1 (Columna relativa, Fila fija): La fila 1 permanece bloqueada al copiar verticalmente, pero la columna varía si la fórmula se copia a izquierda o derecha[cite: 5].

4. Referencias Tridimensionales (3D) y a Otras Hojas o Libros

Permiten hacer referencia a celdas situadas en otras hojas de trabajo dentro del mismo libro o en libros externos[cite: 5]:

• Misma hoja: =A1[cite: 5]
• Otra hoja del mismo libro: =Hoja2!A1 (Si el nombre de la hoja contiene espacios, debe ir encerrado entre comillas simples: ='Datos Generales'!A1)[cite: 5].
• Rango Tridimensional (3D): Hace referencia a la misma celda o rango a lo largo de un bloque de hojas consecutivas[cite: 5]. Ejemplo: =SUMA(Hoja1:Hoja4!A1) suma la celda A1 de las hojas Hoja1, Hoja2, Hoja3 y Hoja4[cite: 5].
• Otro libro de trabajo: ='[Presupuesto.xlsx]Hoja1'!$A$1[cite: 5]

Mapeo Mecánico de Desplazamiento (Ejemplo Práctico de Examen)

Supongamos la fórmula =$A2*B$1+$C$2 ubicada originalmente en la celda B2[cite: 5]. Si esta fórmula se copia y pega en la celda D4, el cálculo de transformación es el siguiente[cite: 5]:

• Desplazamiento horizontal: De columna B a columna D (+2 columnas hacia la derecha)[cite: 5].
• Desplazamiento vertical: De fila 2 a fila 4 (+2 filas hacia abajo)[cite: 5].

Transformación término a término:

• $A2: Columna $A queda fija ($A)[cite: 5]. Fila 2 incrementa +2 → $A4[cite: 5].
• B$1: Columna B incrementa +2 (pasa a D)[cite: 5]. Fila $1 queda fija ($1) → D$1[cite: 5].
• $C$2: Absoluta total[cite: 5]. No cambia en ningún eje → $C$2[cite: 5].

Resultado final en D4: =$A4*D$1+$C$2[cite: 5]

---

3.3. FUNCIONES OFICIALES MÁS EVALUADAS EN LA USAL

Una función es una fórmula predefinida que opera sobre uno o más valores (argumentos) en un orden determinado y devuelve un resultado[cite: 5]. La sintaxis estándar es: =NOMBRE_FUNCION(argumento1; argumento2; ...)[cite: 5]. En la versión en español de Excel, los argumentos se separan obligatoriamente por punto y coma (;)[cite: 5].

1. Funciones Matemáticas y Estadísticas

• =SUMA(número1; [número2]; ...): Suma todos los números comprendidos en un rango o lista de argumentos[cite: 5]. Ignora celdas vacías, valores lógicos y texto[cite: 5].
• =PROMEDIO(número1; [número2]; ...): Devuelve la media aritmética de los argumentos numéricos contenidos en el rango[cite: 5].
• =MAX(número1; ...) / =MIN(número1; ...): Devuelven el valor máximo o mínimo respectivamente de un conjunto de datos[cite: 5].
• =CONTAR(rango): Cuenta exclusivamente las celdas que contienen valores numéricos (incluye fechas)[cite: 5].
• =CONTARA(rango): Cuenta todas las celdas no vacías dentro del rango (incluye texto, números, valores lógicos y valores de error)[cite: 5].
• =CONTAR.BLANCO(rango): Cuenta el número de celdas totalmente vacías dentro del rango especificado[cite: 5].
• =ENTEROS(número) o =ENTERO(número): Redondea un número hacia abajo hasta el entero más próximo[cite: 5].
• =REDONDEAR(número; num_decimales): Redondea un número al número de decimales especificado (al más cercano)[cite: 5].
• =SUMAR.SI(rango; criterio; [rango_suma]): Suma las celdas de un rango que cumplen con un criterio específico[cite: 5]. Si se omite rango_suma, se suman las celdas del propio rango[cite: 5].
  Ejemplo: =SUMAR.SI(A1:A10; ">50"; B1:B10)[cite: 5]
• =CONTAR.SI(rango; criterio): Cuenta el número de celdas de un rango que cumplen con la condición o criterio dado[cite: 5].
  Ejemplo: =CONTAR.SI(B1:B20; "Aprobado")[cite: 5]

2. Funciones Lógicas

• =SI(prueba_lógica; valor_si_verdadero; [valor_si_falso]): Evalúa una condición lógica[cite: 5]. Si es verdadera devuelve la segunda expresión; si es falsa devuelve la tercera[cite: 5]. Pueden anidarse hasta 64 funciones SI[cite: 5].
  Ejemplo: =SI(A1>=5; "Apto"; "No Apto")[cite: 5]
• =Y(lógico1; [lógico2]; ...): Devuelve VERDADERO únicamente si todos los argumentos o condiciones evaluadas son verdaderos[cite: 5]. En cuanto uno sea falso, devuelve FALSO[cite: 5].
• =O(lógico1; [lógico2]; ...): Devuelve VERDADERO si al menos uno de los argumentos o condiciones evaluadas es verdadero[cite: 5]. Devuelve FALSO solo si todos son falsos[cite: 5].
• =NO(lógico): Invierte el valor lógico de su argumento (transforma VERDADERO en FALSO y viceversa)[cite: 5].
• =SI.ERROR(valor; valor_si_error): Evalúa una expresión[cite: 5]. Si la expresión no genera un error, devuelve su resultado normal; si genera cualquier tipo de error (como #¡DIV/0! o #N/D), devuelve la alternativa especificada[cite: 5].

3. Funciones de Búsqueda y Referencia

• =BUSCARV(valor_buscado; matriz_buscar_en; indicador_columnas; [ordenado]): Busca un valor específico en la **primera columna situada a la izquierda** de una tabla o matriz y devuelve el valor que se encuentra en la misma fila dentro de la columna indicada por indicador_columnas[cite: 5].
  • valor_buscado: Elemento que se intenta localizar en la columna 1[cite: 5].
  • matriz_buscar_en: Rango de celdas que contiene los datos[cite: 5].
  • indicador_columnas: Número entero que indica qué columna de la matriz entregará el dato resultante (la primera columna de la matriz es la número 1)[cite: 5].
  • [ordenado] (o rango): Argumento lógico opcional[cite: 5].
    • FALSO (o 0): Busca una **coincidencia exacta**[cite: 5]. Si no la encuentra, devuelve el error #N/D[cite: 5]. No requiere que la tabla esté ordenada[cite: 5].
    • VERDADERO (o 1, o si se omite): Busca una **coincidencia aproximada**[cite: 5]. Si no encuentra el valor exacto, devuelve el valor inmediatamente inferior más cercano[cite: 5]. Requiere que la primera columna esté ordenada de forma ascendente[cite: 5].
• =BUSCARH(valor_buscado; matriz_buscar_en; indicador_filas; [ordenado]): Realiza una búsqueda idéntica a BUSCARV pero en sentido horizontal, buscando el valor en la **primera fila superior** de la matriz y devolviendo el contenido de la fila especificada por indicador_filas[cite: 5].
• =COINCIDIR(valor_buscado; matriz_buscada; [tipo_de_coincidencia]): Devuelve la **posición relativa** de un elemento dentro de un rango de una sola fila o columna[cite: 5].
• =INDICE(matriz; núm_fila; [núm_columna]): Devuelve el valor o la referencia a un valor localizado en la intersección de una fila y una columna específicas dentro de una tabla[cite: 5].
• =HIPERVINCULO(ubicación_enlace; [nombre_descriptivo]): Crea un acceso directo que abre un documento almacenado en un disco local, en un servidor de red o una dirección URL de Internet[cite: 5].

4. Funciones de Texto

• =CONCATENAR(texto1; ...) o =CONCAT(texto1; ...): Une varias cadenas de texto en una sola[cite: 5]. Equivalente al operador &[cite: 5].
• =MAYUSC(texto) / =MINUSC(texto): Convierte todas las letras de una cadena de texto a mayúsculas o minúsculas respectivamente[cite: 5].
• =NOMPROPIO(texto): Convierte a mayúscula la primera letra de cada palabra dentro de una cadena de texto[cite: 5].
• =Largo(texto): Devuelve el número total de caracteres que componen una cadena de texto (incluyendo espacios en blanco)[cite: 5].
• =IZQUIERDA(texto; [num_caracteres]): Devuelve el número de caracteres especificado desde el inicio (extremo izquierdo) de una cadena de texto[cite: 5].
• =DERECHA(texto; [num_caracteres]): Devuelve el número de caracteres especificado desde el final (extremo derecho) de una cadena de texto[cite: 5].
• =EXTRAE(texto; posición_inicial; núm_caracteres): Devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que se indique[cite: 5].

5. Funciones de Fecha y Hora

En Excel, las fechas son tratadas internamente como **números de serie secuenciales** empezando por el 1 de enero de 1900 (valor 1)[cite: 5]. Las horas se representan como fracciones decimales de un día de 24 horas[cite: 5].

• =HOY(): Devuelve la fecha actual del sistema operativo[cite: 5]. **No requiere argumentos**[cite: 5]. Se actualiza automáticamente al recalcular la hoja[cite: 5].
• =AHORA(): Devuelve la fecha y la hora actuales del sistema operativo[cite: 5]. **No requiere argumentos**[cite: 5].
• =DIA(fecha) / =MES(fecha) / =AÑO(fecha): Extraen respectivamente el número de día (1-31), el número de mes (1-12) o el año de un valor de fecha válido[cite: 5].

---

3.4. CÓDIGOS DE ERROR COMUNES EN EXCEL

Cuando Excel no puede evaluar correctamente una fórmula o función, muestra un código de error antecedido por el símbolo de almohadilla (#)[cite: 5]. Conocer la causa técnica de cada uno es esencial para resolver las preguntas del examen[cite: 5]: