Ediciones ENI Editorial | líder en informática
Cálculos
Usar zonas con nombre en las fórmulas
Esta función permite reemplazar una referencia de rangos de celda por la zona con nombre correspondiente en una fórmula. Recuerde que las celdas o rangos de celdas con nombre se gestionan como referencias absolutas cuando se copian fórmulas.
Empiece a introducir la fórmula y deténgase al llegar al nombre.
Haga clic en el botón Utilizar en la fórmula del grupo Nombres definidos de la pestaña Fórmulas.
Aparece la lista con las zonas a las que se ha puesto nombre previamente (véase Rangos con nombre - Poner nombre a los rangos de celdas):
Haga clic en el nombre correspondiente al rango de celdas que desea insertar en la fórmula.
Continúe y concluya la fórmula.
También es posible introducir el nombre directamente en la fórmula, en lugar de las referencias de celda.
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...
Usar las fórmulas condicionales
Este tipo de fórmulas permite mostrar un valor o efectuar un cálculo según una o varias condiciones.
Función SI simple
Active la celda en la que desea introducir la fórmula y ver el resultado.
Utilice la función SI para efectuar una prueba lógica (VERDADERO o FALSO) sobre el valor de una celda o en el resultado de otra fórmula; en función del resultado de la prueba, la función SI lleva a cabo una acción si el resultado es verdadero, u otra, si el resultado es falso.
La sintaxis de la función SI es la siguiente:
=SI (prueba_lógica;[valor_si_verdadero];[valor_si_falso])
La fórmula introducida en I4 se ha copiado en I5, I6, I7 e I8. En este ejemplo, probamos el valor de la celda Stock final (H4): =SI(H4<=1000;"Hacer pedido";"En espera"). Si el contenido de la celda H4 es inferior o igual a 1000, el texto "Hacer pedido" se mostrará en la celda de resultado; en caso contrario, lo hará el texto "En espera".
Observe que, si invertimos la prueba, el resultado es el mismo siempre que se invierta también el valor si FALSO y el valor si VERDADERO: =SI(H4>1000;"En espera";"Hacer pedido")
Para mostrar un resultado solo si la condición es VERDADERA, puede no especificar el argumento FALSO, por ejemplo: =SI(H4<1000;"Hacer pedido"); en este caso, si la condición no se verifica, dado que la acción no se ha definido en la fórmula, Excel muestra el valor FALSO.
Para dejar vacío el contenido una de las celdas...
Combinar el operador O o Y en una fórmula condicional
En la función SI, solo se puede hacer una pregunta en la parte Prueba_logica. Para poder hacer varias preguntas, puede usar los operadores O/Y que se anidarán dentro de la función SI.
Utilice el operador O o Y según el caso:
-
si deben verificarse varias condiciones al mismo tiempo:
=SI(Y(cond1;cond2;... ;condn); acción que se debe realizar si las n condiciones se cumplen; acción que debe realizarse si por lo menos una de las condiciones no se cumple)
-
si por lo menos una de las condiciones debe ser verdadera:
=SI(O(cond1;cond2;... ;condn); acción que debe realizarse si por lo menos una condición se cumple; acción que debe realizarse si ninguna condición se cumple)
(letra e de la fuente Webdings) en la celda
de la columna Coche; en caso
contrario, la celda se queda vacía.
Contar las celdas que responden a uno o a varios criterios específicos (CONTAR.SI)
Función CONTAR.SI
La función CONTAR.SI, cuya sintaxis es =CONTAR.SI(rango_de_celdas;criterios), permite contar el número de celdas que responden a uno o varios criterios.
|
Rango_de_celdas |
Corresponde al rango de celdas que contiene el criterio buscado. |
|
Criterios |
Corresponde al criterio buscado para el recuento de las celdas correspondientes. |
Como ejemplo de esta función, hemos calculado el número de días en los que ha caído más de 5 mm de lluvia.
Haga clic en la celda en la que desea mostrar el resultado.
Comience a introducir el principio de la fórmula =CONTAR.SI(
Haciendo clic y arrastrando, seleccione el rango de celdas que incluyen los datos que interesan para el cálculo.
Por supuesto, también puede introducir la referencia al rango de celdas celdas o su nombre en caso de que se le haya asignado uno.
Introduzca el punto y coma (;) para indicar el cambio de argumento.
Luego, introduzca el criterio; este puede estar compuesto:
-
de un número: en ese caso se escribe el valor directamente. Por ejemplo: =CONTAR.SI(C2:C18;5) para buscar únicamente la cantidad de lluvia igual a 5.
-
de una referencia de celda. Por ejemplo: =CONTAR.SI(C2:C18;C2) para buscar la cantidad incluida en la celda C2.
-
de una expresión: hay que escribir la expresión entre comillas....
Hacer estadísticas condicionales con un criterio
Función SUMAR.SI
La función SUMAR.SI, cuyo principio es muy similar al de la función CONTAR.SI (véase Contar las celdas que responden a uno o a varios criterios específicos, permite añadir las celdas de un rango que responden a un criterio dado.
La sintaxis de la función es: =SUMAR.SI(rango_de celdas;criterio;rango_para_sumar)
|
Rango_de_celdas |
Corresponde al rango de celdas donde se encuentra el criterio buscado. |
|
Criterio |
Corresponde al criterio que se busca para añadir las celdas que cumplan el criterio. |
|
Rango_a_añadir |
Este argumento se puede omitir si el argumento Rango_de_celdas contiene los valores a añadir; en caso contrario, el argumento corresponde al rango de celdas que se añadirá si las celdas corresponden al criterio. |
Como ejemplo de esta función, hemos calculado en F17 la suma de la cantidad de agua únicamente si el valor diario es superior a 5 mm, usando solo los dos primeros argumentos de la función, ya que el rango de celdas que contiene el criterio y el que contiene los valores que se han de calcular son los mismos (C2 a C18). En F18, hemos calculado la suma de la cantidad de agua que ha caído en domingo usando los tres argumentos de la función, ya que el rango de celdas que contiene el criterio “domingo” (B2 a B18) no es el mismo que el rango de celdas que contienen...
Realizar estadísticas condicionales con varios criterios
Función SUMAR.SI.CONJUNTO
El propósito de esta función es sumar valores en función de uno o varios criterios. Como para la función CONTAR.SI.CONJUNTO puede usar varios rangos distintos con la función SUMAR.SI.CONJUNTO.
La sintaxis es la siguiente: =SUMAR.SI.CONJUNTO(rango_para_sumar;rango_criterio1;criterio1;rango_criterio2;criterio2...)
Puede observar la posición del rango de celdas para sumar que se encuentra al principio de la sintaxis de la función SUMAR.SI.CONJUNTO al contrario que la función SUMAR.SI, que coloca este rango al final de la función.
Función PROMEDIO.SI.CONJUNTO
Esta función tiene una estructura y funcionamiento similares a la función SU-MAR.SI.CONJUNTO. Además, permite calcular la media de los valores que cumplen uno o varios criterios.
La sintaxis es la siguiente: =PROMEDIO.SI.CONJUNTO(rango_promedio;rango_criterio1;criterio1;rango_criterio2;criterio2...)
Funciones MAX.SI.CONJUNTO y MIN.SI.CONJUNTO
La función MAX.SI.CONJUNTO permite obtener el valor mayor de un rango de celdas que cumplen uno o varios criterios.
La función MIN.SI.CONJUNTO permite obtener el valor menor de un rango de celdas que cumplen uno o varios criterios.
La sintaxis es la siguiente:
=MAX.SI.CONJUNTO(rango_max;rango_criterios1;criterios1;[rango_criterios2];[criterios2]...)
=MIN.SI.CONJUNTO(rango_min;rango_criterios1;criterios1;[rango_criterios2];[criterios2]...)...
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.
Función FILTRAR
Esta función, una novedad de la versión 2021, filtra un rango de datos en función de uno o varios criterios y devuelve varios resultados.
Su sintaxis es la siguiente:
=FILTRAR(array; include;[if_empty])
|
array |
Corresponde a la lista de datos para filtrar. |
|
Include |
Zona de definición del criterio que incluye el rango de celdas (donde se encuentra el criterio buscado) junto con un comparador (= > < <= >= <>) y el criterio. |
|
[if_empty] |
Valor para devolver si el filtro no encuentra ningún resultado. |
En el ejemplo de arriba, la finalidad es recuperar la información de los productos de la categoría Cítricos. El array corresponde...
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 
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...
