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:

Principios para calcular los días

En los cálculos realizados sobre días, siga el mismo procedimiento que con los demás cálculos. Excel registra las fechas en forma de números secuenciales llamados números de serie. Por ese motivo pueden agregarse, sustraerse e incluirse en otros cálculos.

De forma predeterminada, Excel para Windows inicia el calendario a partir de 1900 (para Macintosh el calendario se inicia en 1904). El 1 de enero de 1900 corresponde por tanto (en Excel para Windows) al número de serie 1, y el 1 de enero de 2005 es el 38 353, ya que desde el 1 de enero de 1900 han transcurrido 38 353 días.

Para utilizar una función específica de gestión de fechas y horas, puede activar la pestaña Fórmulas, hacer clic en el botón Fecha y hora del grupo Biblioteca de funciones y luego en la función que corresponda para utilizar el asistente.

AHORA()

Devuelve la fecha y la hora actuales con formato de fecha y hora, como por ejemplo: 09/02/2022 11:14.

AÑO(número_de_serie)

Devuelve el año, un número entero entre 1999 y 9999.

Esta función permite aislar el año de una fecha; ejemplo: la celda A1 contiene el valor 12/12/2021, la función =AÑO(A1) devuelve 2021.

DIA(número_de_serie)

Da el día del mes (un número entero entre 0 y 31).

Sigue el mismo principio que la función AÑO, aísla el día de una fecha cualquiera.

DIA.LAB(fecha_inicial;días;[vacaciones])

Devuelve el número de serie de la fecha antes o después del número de días laborables especificado (ver sección Calcular la fecha situada después de una cantidad de días laborables dada).

DIA.LAB.INTL(fecha_inicial;días;[fin_de_semana];[días_no_laborables])

Devuelve el número de serie de la fecha antes y después de un número especificado de días laborables con parámetros que identifican y cuentan los días de fin de semana.

DIAS(fecha_final;fecha inicial)

Calcula el número de días entre...

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.

Principios de cálculo de las horas

Al introducir una hora en una celda, Excel la guarda en forma de número decimal de 0 a 1 (1 no incluido) por cada periodo de 24 horas.

Para que Excel pueda reconocer la información como una hora y guardarla en forma de número decimal, deberá separar las diferentes partes de la hora con el signo dos puntos (:). Por ejemplo, 18 h 30 min y 43 segundos se escribe de acuerdo con la sintaxis 18:30:43. Si no desea incluir los segundos, introduzca 18:30

Ejemplo de hora	Valor registrado por Excel
00:00 (medianoche)	0
11:59	0,499305555555556
12:00 (mediodía)	0,5
15:00 (15 h)	0,625
18:00 (18 h)	0,75

Este concepto de hora propio de Excel permite aplicar cálculos aritméticos a las horas.

Ejemplo: para calcular la diferencia entre 18:00 (18 h) y 15:00 (15 h), Excel efectúa este cálculo:

= 0,75 - 0,625 = 0,125

Excel pone a su disposición numerosos formatos de hora predeterminados.

Calcular la diferencia entre dos horas

Para efectuar ese cálculo y representar el resultado en formato horario estándar, es decir, horas:minutos:segundos, puede usar la función...

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...

Usar las nuevas funciones de cálculo

Hay nuevas funciones que han llegado para contribuir a enriquecer la biblioteca de funciones de Excel. Algunas ya aparecían en la versíon 2019 y otras han aparecido en esta nueva versión 2021: CONCAT, UNIRCADENAS, VALORATEXTO, MATRIZATEXTO, SUMAR.SI.CONJUNTO, PROMEDIO.SI.CONJUNTO, MAX.SI.CONJUNTO, MIN.SI.CONJUNTO, CONTAR.SI.CONJUNTO, SI.CONJUNTO, FILTRAR, ORDENAR, ORDENARPOR, UNICOS, BUSCARX, COINCIDIRX, LET, MATRIZALEAT y SECUENCIA.

Las funciones SUMAR.SI.CONJUNTO, PROMEDIO.SI.CONJUNTO, MAX.SI.CONJUNTO, MIN.SI.CONJUNTO. CONTAR.SI.CONJUNTO se explican detalladamente en el capítulo Cálculos. La función BUSCARX se detalla en la sección Usar las funciones de búsqueda.

Fonción CONCAT

La función CONCAT (novedad 2019) permite concatenar textos. Será posible unir hasta 254 cadenas de texto. Esta función remplaza a la función CONCATENAR, es más simple y más corta de utilizar.

Su sintaxis es como sigue: =CONCAT(Texto1;Texto2;[Texto3], etc.)

En este ejemplo, la función CONCAT muestra los datos de las celdas A3, B3 y C3 separadas por un espacio y seguidas del símbolo de moneda €:

Si el separador de cadenas de caracteres es idéntico, es preferible usar la función UNIRCADENAS.

Fonción UNIRCADENAS

La función UNIRCADENAS (novedad 2019) permite mostrar los textos de varias celdas unos después de otros y separados por el carácter o los caracteres especificados.

Su sintaxis es como sigue: UNIRCADENAS(delimitador, ignorar_vacío, Texto1, [Texto2],...)

Delimitador	Para este argumento, especifique el carácter o caracteres que se usarán para separar las cadenas de texto.
Ignorar_vacío	Escriba VERDADERO para no tomar en cuenta las celdas vacías del rango seleccionado o FALSO en caso contrario.
Texto1,Texto2, etc.	Seleccione los diferentes rangos de celdas que albergan los datos que se desea mostrar.

En este ejemplo, la función UNIRCADENAS muestra los datos de las celdas C3, B3 y A3 separados por un espacio; las celdas vacías se ignoran (argumento VERDADERO).

Función LET

La función LET, una novedad de 2021, se usa para asignar un nombre a un resultado de cálculo con la finalidad de guardar los cálculos intermedios o de definir nombres en el interior de una fórmula.

Para usar la función...

Consolidar datos

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

Antes de iniciar la consolidación, compruebe los siguientes puntos:

Cada rango de datos de origen debe estar ubicado en una hoja de cálculo distinta; ningún rango de origen debe estar ubicado en la hoja de cálculo sobre la cual se va a situar la consolidación.
Asegúrese de que las tablas que se van a consolidar tienen la misma estructura (el mismo número de filas y de columnas, el mismo tipo de datos en las celdas) y que están colocadas en las mismas celdas de las distintas hojas.
Si lo desea, asigne un nombre a los rangos de datos de origen (véase Rangos con nombre - Poner nombre a los rangos de celdas).

Active la primera celda de destino de la consolidación.

Active la pestaña Datos y haga clic en la herramienta Consolidar images/IC-067.png del grupo Herramientas de datos.

Seleccione la Función de síntesis que debe usarse para consolidar los datos; para sumar los datos de las distintas tablas, escoja Suma.

Si los datos que desea consolidar se hallan en otro libro, haga clic en el botón Examinar, localice el libro correspondiente, selecciónelo y haga clic en Aceptar.

Si los datos que desea consolidar se sitúan en el libro activo, efectúe estas operaciones en todos los rangos...

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.

Introduzca los elementos iniciales del cálculo que se debe realizar (tipo de interés, duración del préstamo, importe del préstamo, en nuestro ejemplo).

Introduzca los encabezados y las filas de la tabla que corresponden a los parámetros 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.

Introduzca la fórmula de cálculo en la intersección de la fila y la columna y confirme.

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.

Seleccione el rango de celdas que comprende la fórmula de cálculo hasta la última celda de la tabla.

Active la pestaña Datos, haga clic en el botón...

Usar una fórmula matricial

Esta fórmula tiene la particularidad de efectuar varios cálculos y devolver resultados simples o múltiples. Una fórmula matricial solo puede intervenir en dos (o más) conjuntos de valores llamados comúnmente argumentos matriciales. Estos deben tener el mismo número de filas y columnas.

Proceda igual que para efectuar un cálculo simple, pero en lugar de trabajar por celdas, trabaje por rangos de celdas. Para validar esta fórmula, desde la versión 2021 solo tiene que pulsar Intro (en las versiones anteriores era obligatorio usar la combinación de teclas CtrlMayúsIntro.

Veamos el uso de una fórmula matricial a través de tres ejemplos diferentes:

Para simplificar la lectura de las fórmulas, hemos puesto nombre a tres zonas: cliente (A2:A11), importe (B2:B11) y fecha (C2:C11).

¿Cuántas veces aparece Antonio Puente?

Para esta fórmula, buscamos en el rango de celdas cliente si aparece "Antonio Puente". Si se verifica la condición, Excel añade 1, si no Excel añade 0.

Hay que señalar que podríamos haber obtenido el mismo resultado con la función CONTAR.SI.

¿Cuál es el importe total de Terencio Petit?

Mediante esta fórmula, solicitamos calcular la suma de los importes SUMA (importe correspondiente al cliente Terencio Petit: *(cliente="Terencio...