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 ($B$1); si pulsa F4 una segunda vez, solo se hará absoluta la referencia de la fila (B$1); si pulsa F4 una tercera vez, se hará absoluta la referencia de la columna (B$1) y, si pulsa una cuarta vez, la referencia se convierte en relativa (B1).
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...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 "Semestre 1" y "Semestre 2".
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 un paréntesis ) 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 de la barra de fórmulas.
Al seleccionar rangos de celdas con valores numéricos, Excel muesìtra, 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 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 resultados de función...
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. Por ejemplo:...
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 los valores...
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 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.
Insertar subtotales en una lista de datos
La operación consiste en agregar filas de subtotales a una lista que contiene valores numéricos (por ejemplo, una lista de productos con precios, una lista de pedidos con importes, etc.).
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... |
