Ediciones ENI Editorial | líder en informática
Cálculos
Descripción del principio en que se basan las fórmulas de cálculo
Las fórmulas de cálculo efectúan cálculos con los valores contenidos en la hoja.
Las fórmulas empiezan con un signo de igual (=).
Las fórmulas pueden contener los siguientes elementos:
-
referencias de celda,
-
operadores de cálculo:
Operadores matemáticos:
+ para la suma
- para la resta
/ para la división
* para la multiplicación
% para calcular un porcentaje (p. ej.: =5% da como resultado 0,05)
^ para elevar a la potencia (p. ej.: =2^3 da como resultado 8)
Operadores de comparación: el resultado es un valor lógico: VERDADERO o FALSO:
= igual a (p. ej.: =30=40 da como resultado FALSO)
< inferior a (p. ej.: =28<35 da como resultado VERDADERO)
<= inferior o igual a
> superior a
>= superior o igual a
<> diferente de
Operadores de concatenación de texto: & concatena cadenas de caracteres:
(p. ej.: ="Oeste"&" y "&"Norte" da como resultado "Oeste y Norte").
Los operadores de referencia permiten combinar rangos de celdas:
: (dos puntos). P. ej.: B1:C4 representa el rango de celdas de B1 a C4.
; (punto y coma). P. ej.: B1;C4 representa la celda B1 y la celda C4.
-
las constantes, es decir, los valores que no se calculan y, por tanto, no cambian (por ejemplo, el número 1210 o el texto "Totales trimestrales"...
Crear una fórmula de cálculo simple
Se trata de elaborar un cálculo que integre las referencias de celdas, los operadores de cálculo y las constantes.
Haga clic en la celda donde desea introducir la fórmula y ver el resultado.
Escriba un signo de igual =.
Elabore la fórmula teniendo en cuenta estas instrucciones:
-
para integrar el contenido de una celda, haga clic en la celda en cuestión o introduzca su referencia (por ejemplo, C4);
-
para integrar un operador de cálculo o una constante, introduzca el dato correspondiente (por ejemplo, * para multiplicar);
-
si utiliza varios operadores, defina, si es preciso, el orden de prioridad para reagrupar valores usando paréntesis.
Observará la evolución de la fórmula en la barra de fórmulas. En este caso se trata de una fórmula que permite calcular el coste total de un artículo, IVA incluido.
Confirme la fórmula pulsando la tecla Intro o haciendo clic en la herramienta 
de la barra de fórmulas.
Convertir en absoluta una referencia de celda en una fórmula
Esta técnica permite fijar la referencia de una celda en una fórmula, de forma que no se modifique al copiar la fórmula.
Empiece a teclear la fórmula y deténgase cuando se seleccione la celda cuya referencia desea hacer absoluta.
Pulse la tecla F4.
La referencia de la celda muestra entonces el carácter $ delante de la referencia de columna y del número de fila.
Al pulsar la tecla F4, obtendrá una referencia absoluta de celda; si pulsa F4 una segunda vez, solo se hará absoluta la referencia de la fila; si pulsa F4 una tercera vez, se hará absoluta la referencia de la columna.
Pulse la tecla F4 tantas veces como sea necesario para convertir en absoluto el elemento deseado.
Si es preciso, acabe de introducir la fórmula y confirme.
En nuestro ejemplo, hemos copiado la celda E4 en las celdas E5 a E10; observe que la referencia absoluta (B1) permanece fija en las diversas fórmulas, contrariamente a las otras celdas. En este ejemplo, únicamente podría haberse fijado la referencia a la fila, ya que hemos copiado hacia abajo.
de la pestaña Fórmulas -
grupo Auditoría de fórmulas).Introducir una fórmula multihoja
Esta técnica permite insertar en una hoja fórmulas (llamadas fórmulas 3D) que hacen referencia a celdas de una o varias hojas diferentes.
Active la celda en la que desea que aparezca el resultado.
Introduzca el signo =
Empiece a escribir la fórmula y, cuando le parezca oportuno, haga clic en la ficha de la hoja, seleccione la celda o celdas que desee y concluya la fórmula.
Confirme.
En este caso, la celda E3 de esta hoja suma el contenido de las celdas E3 de las hojas "Semestre1" y "Semestre2".
También es posible elaborar fórmulas multilibro. Para ello deberán estar abiertos todos los libros que intervengan en el cálculo. Para desplazarse hasta una celda ubicada en una hoja de otro libro, pulse el botón Cambiar ventanas de la pestaña Vista (o el icono del archivo en la barra de tareas) a fin de activar el libro correspondiente.
Usar las funciones de cálculo
Active la celda en la que desea que aparezca el resultado.
Haga clic en la herramienta Insertar función
, situada en la barra de fórmulas, o en
la pestaña Fórmulas,
o pulse MayúsF3.
En el cuadro de diálogo Insertar función, abra la lista desplegable O seleccionar una categoría si desea ver una categoría concreta de funciones.
La categoría Usadas recientemente muestra una lista con las funciones usadas por usted y también con las más habituales. La categoría Todo muestra todas las funciones disponibles.
Para buscar una función concreta existen dos posibilidades: introducir en el cuadro Buscar una función el nombre exacto de la función o bien una descripción de lo que desea hacer con ella y luego confirmar la búsqueda pulsando el botón Ir o la tecla Intro.
Haga clic en la función buscada dentro del cuadro Seleccionar una función para seleccionarla.
Al seleccionar una función, su sintaxis y su descripción aparecen en la parte inferior del cuadro.
Si es preciso, haga clic en el vínculo Ayuda sobre esta función para consultar la ayuda de Excel referente a la función seleccionada.
Haga clic en Aceptar para activar el cuadro de diálogo Argumentos de función.
Para definir los argumentos de la función:...
Usar la opción Autocompletar para introducir funciones
Esta operación permite introducir una función sin necesidad de pasar por el Asistente para funciones, pero contando con la ayuda de Excel para limitar los errores de sintaxis y de tecleo.
Active la celda en la que desea introducir la fórmula y ver el resultado.
Introduzca el signo = (igual) y las primeras letras de la función.
Al introducir la primera letra, Excel muestra la lista de funciones que empiezan por esa letra en concreto.
Continúe introduciendo el nombre de la función o haga doble clic en el nombre que aparece en la lista e indique los argumentos.
A medida que vaya tecleando aparecerán una serie de etiquetas que le guiarán en la elaboración de la fórmula.
No olvide concluir la fórmula introduciendo el signo ) y confirme pulsando la tecla Intro.
Sumar un conjunto de celdas
Active la celda en la que desea que aparezca el resultado.
Haga clic en la herramienta Suma
del grupo Edición (pestaña Inicio) o utilice el método
abreviado Alt =.
Este botón se encuentra también en la pestaña Fórmulas - grupo Biblioteca de funciones.
Excel muestra una función integrada llamada SUMA() y propone sumar por defecto el grupo de celdas situadas encima o a la izquierda de la celda de resultado (en este caso, E3 a E11).
Si la selección de celdas no le resulta conveniente, modifíquela haciendo clic y arrastrando hasta que la selección incluya las celdas deseadas.
Confirme pulsando Intro o
haga clic en la herramienta Introducir.
Al seleccionar rangos de celdas con valores numéricos, Excel muestra, de forma predeterminada, la suma de dichos valores en la barra de estado.
Usar funciones estadísticas simples
Active la celda en la que desea que aparezca el resultado.
Abra la lista de la herramienta Suma automática del grupo Edición (pestaña Inicio) o del botón Autosuma del grupo Biblioteca de funciones (pestaña Fórmulas).
Haga clic en la función deseada:
|
Promedio |
Calcula el promedio de un conjunto de celdas con valores numéricos. |
|
Contar números |
Calcula el número de celdas con valores numéricos de un conjunto de celdas. |
|
Máx. |
Extrae el valor máximo de un conjunto de celdas con valores numéricos. |
|
Mín. |
Extrae el valor mínimo de un conjunto de celdas con valores numéricos. |
Excel muestra la función correspondiente a la elección efectuada y selecciona un grupo de celdas adyacentes.
Si la selección de celdas no le resulta conveniente, modifíquela haciendo clic en una celda para seleccionarla o haciendo clic y arrastrando para seleccionar un rango de celdas.
Pulse la tecla Intro o
haga clic en la herramienta para confirmar la fórmula de cálculo.
Al seleccionar rangos de celdas con valores numéricos, además de su suma correspondiente, verá en la barra de estado el promedio de los valores. En una selección cualquiera verá también el número de celdas que no están vacías. Para ver otros...
Crear una fórmula condicional simple
Este tipo de fórmula permite mostrar un valor o efectuar un cálculo según una o varias condiciones.
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...
Crear una fórmula condicional anidada
Cuando un resultado de condición de prueba implica más de dos posibilidades, se pueden anidar fórmulas condicionales:
En este caso, la fórmula se escribe así: =SI(prueba_lógica1;valor si VERDADERO; SI(prueba_lógica2;valor si VERDADERO;valor si FALSO)). La fórmula condicional número 2 se halla anidada en la primera.
Usando este método, es posible anidar varias condiciones unas en otras.
En este ejemplo, se han anidado dos condiciones en una tercera: si el Stock final (H4) es inferior a 100, se muestra el texto "Pedido urgente"; si el Stock final está comprendido entre 100 y 500, se muestra el texto "Hacer pedido"; si está comprendido entre 500 y 1000, se muestra el texto "Stock comprendido entre 500 y 1000"; si no se cumple ninguna de estas condiciones, se muestra el texto "Stock > 1000".
Observe que al final de la fórmula hay un paréntesis para cerrar cada condición.
Para facilitar la escritura de la fórmula, puede separar los argumentos insertando saltos de línea mediante las teclas AltIntro.
La inserción de saltos de línea en una fórmula no tiene ninguna incidencia en el resultado.
Para imbricar varias condiciones, también es posible utilizar la nueva función SI.CONJUNTO (véase Usar la función SI.CONJUNTO...
Combinar el operador O o Y en una fórmula condicional
Para poner varias condiciones, puede utilizar 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.
Usar la función SI.CONJUNTO
La función SI.CONJUNTO apareció en 2019, es similar a las funciones SI anidadas y permite evaluar hasta un máximo de 127 condiciones.
A continuación podemos ver la sintaxis:
=SI.CONJUNTO(prueba_lógica1;valor_si_verdadero1;[prueba_lógica2;valor_si_verdadero2]...)
En este ejemplo, si la edad del niño es menor de 10 años, aparece la categoría Benjamín; si la edad es menor de 14, aparece la categoría Infantil y si la edad es mayor o igual a 14, aparece la categoría Cadete:
Usar la función CAMBIAR
La función CAMBIAR, que apareció en 2019, evalúa un valor y muestra el resultado correspondiente al valor buscado. Permite evaluar hasta un máximo de 126 condiciones.
A continuación podemos ver la sintaxis:
=CAMBIAR(expresión;valor1;resultado1;[predeterminado_o_valor2;resultado2];[predeterminado_o_valor3;resultado3]...)
|
expresión |
Valor a evaluar. |
|
valor |
Valor buscado. |
|
resultado |
Valor a devolver en caso de correspondencia. |
|
predeterminado |
Valor a devolver en caso de no correspondencia |
En este ejemplo, si el valor en la columna Opción es 1, aparecerá el texto de la celda E2 (Natación); si el valor es 2, aparecerá el texto de la celda E3 (Equitación) y si no se encuentra ninguna correspondencia aparecerá el valor de la celda E4 (Atletismo):
Contar las celdas que responden a un criterio específico (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 entre comillas; 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. Por ejemplo: CONTAR.SI(C2:C18;”>5”)...
Calcular la suma de un rango que responde a un criterio (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 un criterio específico (CONTAR.SI)), 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_para_sumar |
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 los valores...
Usar las funciones MAX.SI.CONJUNTO y MIN.SI.CONJUNTO
La función MAX.SI.CONJUNTO obtiene el mayor valor de un rango de celdas que cumple uno o varios criterios.
La función MIN.SI.CONJUNTO obtiene el valor más pequeño de un rango de celdas que cumple uno o varios criterios.
A continuación podemos ver la sintaxis:
=MAX.SI.CONJUNTO(rango_max;rango_criterios1;criterios1;[rango_criterios2];[criterios2]...)
=MIN.SI.ENS(rango_min;rango_criterios1;criterios1;[rango_criterios2];[criterios2]...)
|
rango_max/min |
Es el rango de celdas que contienen los valores buscados. |
|
rango_criterios |
Es el rango de celdas que contiene el criterio. |
|
criterios |
Escriba el criterio que busca entre comillas. |
En este ejemplo, en G3, la función MAX.SI.CONJUNTO muestra la cantidad máxima de hijos de las mujeres casadas. En G7, la función MIN.SI. CONJUNTO muestra la cantidad mínima de hijos de las mujeres casadas:
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 el capítulo 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.
Insertar filas de estadísticas
La operación consiste en agregar filas de subtotales.
Ordene la tabla en función de la columna que albergará los grupos con los cuales se elaborarán los subtotales.
Seleccione la tabla en la que se van a insertar las filas de estadísticas, incluyendo los títulos de las columnas.
Active la pestaña Datos y haga clic en el botón Subtotal del grupo Esquema.
Seleccione la columna que contiene los grupos con los que se hará el cálculo estadístico en la lista Para cada cambio en.
Luego seleccione el cálculo que desea efectuar en la lista Usar función.
|
Suma |
Calcula la suma. |
|
Cuenta |
Calcula el número de elementos. |
|
Promedio |
Calcula el promedio. |
|
Máx. |
Destaca el valor máximo. |
|
Mín. |
Destaca el valor mínimo. |
|
Producto |
Multiplica los valores. |
|
Contar números |
Determina el número de valores comprendidos en la lista de los argumentos. |
|
Desvest |
(De una serie de números) calcula la desviación estándar de los valores respecto a la media. |
|
Desvestp |
Calcula la desviación estándar de una población a partir de la totalidad de la población. |
|
Var |
Calcula la varianza, que es igual al cuadrado de la desviación estándar, sobre la base de una muestra. |
|
Varp |
Calcula la varianza sobre la base de la totalidad de la población. |
Por último...