Ediciones ENI Editorial | líder en informática
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:
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 el número de serie de la fecha y de la hora del día.
AÑO(número_de_serie)
Convierte un número de serie en año.
DIA(número_de_serie)
Convierte un número de serie en día del mes.
DIA.LAB(fecha_inicial;días;[días_no_laborables])
Devuelve el número de serie de la fecha antes o después del número de días laborables especificado.
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 las dos fechas.
DIAS.LAB(fecha_inicial; fecha_final;[días_no_laborables])
Devuelve el número de días laborables enteros comprendidos entre dos fechas.
DIAS.LAB.INTL(fecha_inicial;fecha_final;[fin_de_semana];[días_no_laborables])
Devuelve el número de días laborables enteros comprendidos entre dos fechas usando parámetros que identifican los días...
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 una función de búsqueda
La función 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_buscar_en;indicador_columnas;ordenado)
|
valor_buscado |
Es el valor que la función busca en la primera columna de la tabla correspondiente a indicador_columnas. |
|
matriz_buscar_en |
Es la tabla a partir de la cual se recuperarán los datos. Este argumento puede ser las referencias de un rango de celdas o el nombre de un rango de celdas. |
|
indicador_columnas |
Es el número de la columna de la tabla (matriz_buscar_en) que contiene el valor buscado. La primera columna de la tabla es la columna 1. |
|
ordenado |
Es un valor lógico que permite efectuar una búsqueda exacta o aproximada a aquella buscada. Si el valor_ordenado es VERDADERO o nulo, se muestra un dato igual o inmediatamente... |
Usar funciones en el texto
Concatenar funciones de tipo texto
La función CONCAT 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.
La sintaxis es como sigue:
=CONCAT(Texto1;Texto2;[Texto3], etc.)
En este ejemplo, la función CONCAT muestra los datos de las celdas A2, B2 y C2 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 permite mostrar los textos de varias celdas unos después de otros y separados por el carácter o los caracteres especificados.
La 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).
Usar variables en una fórmula
La función LET está destinada a facilitar la escritura de algunas fórmulas complejas porque permite declarar y asignar valores a variables situadas en el interior de una fórmula. Así se definen pares de nombres y valores asociados que se usarán en un cálculo.
La función LET admite un máximo de 126 pares.
La sintaxis es como sigue:
=LET(nombre1;nombre_valor1;cálculo_o_nombre2;[nombre2 / valor2];...)
|
nombre1 |
Corresponde al nombre que se atribuirá al valor o al cálculo. Este nombre debe empezar por una letra. |
|
nombre_valor1 |
Corresponde al valor o al cálculo que se asignará a nombre1. |
|
cálculo |
Corresponde al cálculo a realizar con ayuda de los nombres y de los valores asignados. |
En este ejemplo, la función LET permite simplificar la escritura de la función FILTRO y modificar con rapidez el nombre del vendedor Hervé en Marie introduciendo una sola vez el valor de la variable X:
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 el capítulo 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
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...
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...
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 y, en lugar de confirmar pulsando Intro o CtrlIntro, hágalo con 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), Fecha (C2:C11).
Las fórmulas matriciales se han introducido en G2, G6 y G10:
|
G2 |
{=SUMA(SI(Cliente="Antonio Puente";1;0))} Con esta fórmula, en el rango de celdas Cliente buscamos la entrada "Antonio Puente"; si la condición se verifica, Excel agrega 1, de lo contrario Excel agrega 0. El mismo resultado podría obtenerse con la función NB.SI. |
|
G6 |
{=SUMA(Importe*(Cliente="Terencio Petit"))} Con esta fórmula, solicitamos que se calcule la suma de los importes correspondientes al cliente Terencio Petit: SUMA(Importe: *(el cliente se llama Terencio Petit: Cliente="Terencio... |