Cálculos avanzados

Efectuar cálculos con datos de tipo fecha

En este apartado, una vez abordados los principios de cálculo de fechas usados por Excel, procederemos a describir algunas funciones específicas al tratamiento de fechas a través de una serie de ejemplos:

Efectuar cálculos con datos de tipo hora

En este apartado, una vez abordados los principios de cálculo de horas usados por Excel, procederemos a describir métodos y fórmulas específicos del tratamiento de las horas a través de una serie de ejemplos.

Usar las funciones de búsqueda

Funciones BUSCARV

La función BUSCARV permite buscar un valor en la primera columna de una tabla (V = Vertical) y devuelve el valor contenido en la celda situada en la misma fila y en la columna especificadas.

 Elabore una tabla que agrupe los datos que se recuperarán después, al efectuar la búsqueda, y ordénela por orden creciente a partir de los datos de la primera columna. Dé un nombre a este rango de celdas si no desea seleccionarlo en el momento de crear la fórmula de cálculo.

 Haga clic en la celda en la que aparecerá el dato buscado de la tabla.

 Elabore la fórmula de cálculo respetando la sintaxis siguiente:

=BUSCARV(valor_buscado;matriz_tabla;indicador_columnas;[rango])

valor_buscado	Es el valor que la función buscará en la primera columna de la matriz tabla.
matriz_tabla	Es la tabla a partir de la cual se recuperarán los datos. Puede ser las referencias o el nombre de un rango de celdas.
indicador_columnas	Es el número de orden de la columna de la matriz_tabla que contiene el valor recuperado. La primera columna de la tabla es la columna 1.
rango	Es un valor lógico que permite efectuar una búsqueda exacta o aproximada a aquella buscada. Si el rango es VERDADERO o nulo, se muestra un dato igual o inmediatamente inferior al valor buscado. Si el rango es FALSO, solo se tiene en cuenta el valor buscado. Si no se encuentra el valor buscado, la función devuelve un mensaje de error #N/D.

En este ejemplo, la función BUSCARV busca la referencia exacta del producto (contenida en celda A4) en la tabla con los códigos de los productos (G2 a I7) y encuentra en ella la descripción, que sitúa en la segunda columna, y el precio del artículo, en la tercera.

 Confirme la fórmula pulsando la tecla Intro.

El rango de celdas G2:I7 se ha tomado como referencia absoluta en la fórmula (símbolos...

Usar funciones de texto

En 2024 se han añadido tres nuevas funciones a las ya existentes: TEXTOANTES, TEXTODESPUES y DIVIDIRTEXTO.

Función TEXTOANTES

La función TEXTOANTES (novedad de la versión 2024) permite extraer el texto de una cadena que se encuentra antes de un determinado carácter.

 Su sintaxis es:

=TEXTOANTES(Texto;Delimitador;[Número_instancia];[Modo_búsqueda];[Coincidencia_final];[Si_no_encuentra])

Texto	Texto (normalmente una referencia de celda) en el que se realiza la búsqueda. Argumento obligatorio.
Delimitador	Texto que indica el punto antes del cual se debe extraer el texto. Argumento obligatorio.
Número_instancia	Número de ocurrencia del delimitador tras el cual se quiere extraer el texto. Por defecto, 1.
Modo_búsqueda	Determina si se distingue entre mayúsculas y minúsculas. Por defecto, 0 (sensible a mayúsculas); usar 1 para no distinguir.
Coincidencia_final	Si es 1 y no se encuentra el delimitador, se toma el final del texto como delimitador. Por defecto, 0 (no se considera el final).
Si_no_encuentra	Valor que se devuelve si no hay coincidencia. Por defecto, #N/A.

En este ejemplo, la columna B contiene la descripción de una serie de productos y se desea extraer en la columna C el tipo de producto, que corresponde a la información anterior al primer guion:

Función TEXTODESPUES

La función TEXTODESPUES (novedad de la versión 2024) es complementaria a TEXTOANTES y permite extraer el texto que aparece tras un determinado carácter.

 Tiene la misma sintaxis que TEXTOANTES:

=TEXTODESPUES(Texto;Delimitador;[Número_instancia];[Modo_búsqueda];[Coincidencia_final];[Si_no_encuentra])

La combinación de TEXTOANTES y TEXTODESPUES permite seleccionar fácilmente fragmentos de texto intercalados dentro de una cadena: En este ejemplo, la función TEXTODESPUES extrae...

Crear funciones personalizadas

En 2024, se introdujo la función LAMBDA, que permite crear funciones personalizadas y reutilizables asignándoles un nombre fácil de recordar, sin necesidad de usar VBA, macros o JavaScript. Una vez creada, la función personalizada está disponible en todo el libro y se utiliza igual que las funciones nativas de Excel.

Conviene seguir una serie de pasos para asegurarse de que la función LAMBDA devuelve el resultado deseado.

En este ejemplo se realiza un cálculo sencillo del precio con IVA a partir del precio sin impuestos y del tipo de IVA:

El segundo paso consiste en definir la función LAMBDA.

Asignar un nombre a un cálculo intermedio

La función LET, incorporada en 2021, se utiliza para asignar un nombre al resultado de un cálculo con el objetivo de almacenar cálculos intermedios o definir nombres dentro de una fórmula.

Para usar la función LET, tiene que definir elementos compuestos de un binomio: nombre y valor_asociado (hasta 126 binomios posibles) y de un cálculo que usa todos los elementos.

Ejemplos de uso:

En este ejemplo, la facturación de 2024 se multiplica por el coeficiente para obtener el objetivo de 2025. Evidentemente, lo más fácil habría sido escribir = C2*1,5, pero este ejemplo es práctico para comprender bien la lógica de esta función. Aquí, coef es el nombre del elemento y 1,5 es el valor dado a este elemento, después el nombre se usa en la fórmula...

Consolidar datos

Esta función permite combinar valores de varios rangos de datos ubicados en diferentes hojas de cálculo (para reunirlos, por ejemplo).

Generar una tabla de doble entrada

Con el fin de ilustrar el uso de una tabla de doble entrada, queremos conocer el valor de los pagos para un capital prestado fijo de 15 000 €, un número variable de mensualidades y tipos de interés también variables.

Atención, la tabla preparada no debe estar unida a los elementos iniciales y el primer dato variable de fila debe estar situado una fila más arriba y una columna más a la derecha que el primer dato variable de columna.

En este ejemplo, en la celda A11 hemos usado la función PAGO para calcular el importe de las cuotas mensuales correspondiente a la devolución de un préstamo basándonos en el número de pagos mensuales y en un tipo de interés constante. Asimismo, hemos usado la función ABS para efectuar este cálculo en valor absoluto.

Usar las nuevas funciones de tablas dinámicas

Tradicionalmente, las funciones de Excel muestran su resultado en una sola celda. En este ejemplo, la fórmula ubicada en la celda B9 devuelve su resultado en esa misma celda:

En 2019, Microsoft introdujo un nuevo tipo de fórmula: las tablas propagadas o tablas dinámicas (dynamic arrays en inglés, que no deben confundirse con las tablas dinámicas de resumen o tablas cruzadas dinámicas). La particularidad de estas funciones es que, a diferencia de las fórmulas clásicas, el resultado de las tablas dinámicas puede llenar automáticamente varias celdas. En este ejemplo, la función DIVIDIRTEXTO se encuentra en la celda A5, toma el texto de la celda A2 y muestra su resultado en el rango A5:C8, el cual aparece delimitado por un borde azul:

El mecanismo por el cual el resultado ocupa varias celdas se denomina propagación y es automático. La celda que contiene la fórmula es la celda principal, mientras que las celdas adyacentes son las celdas de desbordamiento.

Las celdas de desbordamiento tienen características particulares: