Fórmulas en Microsoft Excel
Prof. Israel J. Ramírez
|
|
1. ¿
QUÉ ES UNA FÓRMULA EN EXCEL ?
Las fórmulas
en Excel son expresiones que se utilizan para realizar cálculos o procesamiento
de valores, produciendo un nuevo valor que será asignado a la celda en la cual
se introduce dicha fórmula. En una fórmula, por lo general, intervienen valores que se encuentran en una
o más celdas de un libro de trabajo. Las fórmulas están conformadas por
operadores de cálculo, operandos y, con frecuencia, por funciones. Para
introducir una fórmula en una celda, se debe entrar como primer carácter el
signo igual ( El signo igual = le indica a Excel que los caracteres que le
siguen constituyen una fórmula ). Cuando se escribe la fórmula no se deben
dejar espacios en blanco dentro de la misma.
Por ejemplo,
la fórmula =A5+A4*3 expresa que se multiplique el valor que contiene la celda A4 por el valor constante 3
y, a continuación, se le sume el valor que contiene la celda A5 al anterior resultado ( como se verá
mas adelante la multiplicación *
tiene mayor prioridad que la suma + ).
Si la celda A5 contiene el valor numérico 15,
la celda A4 contiene el valor numérico
4, entonces, esta fórmula al ser
calculado por el Excel producirá 27
como resultado final, el cual será asignado a la celda en la que se ingresó la
fórmula.
1.1. Componentes
de una fórmula
Una fórmula en Excel
puede contener cualesquiera de los siguientes elementos: referencias a celdas, constantes, operadores y funciones.
Por ejemplo, la
fórmula que permite calcular el área de la superficie que está dentro de una
circunferencia, A = p R2, se puede escribir en Excel como:
=PI()*B1^2
En esta fórmula podemos identificar los elementos nombrados
anteriormente:
- Referencias
a celdas: B1. Se hace referencia al valor que contiene la celda B1.
- Constantes: el valor 2.
Números o valores escritos directamente en una fórmula.
- Operadores: ^ y *. El operador ^ (acento circunflejo) eleva un número a una potencia, y el
operador * (asterisco) multiplica.
- Funciones: la función PI(). Esta
función al ser invocada devuelve el valor de pi: 3,141592...
2. OPERADORES
Los
operadores especifican el tipo de operación o procesamiento que se desea
realizar con los elementos de una fórmula. Microsoft Excel incluye cuatro tipos
diferentes de operadores: aritméticos, de
comparación, texto y de referencia.
2.1. Tipos de
operadores
- Operadores aritméticos: Se utilizan para
realizar las operaciones matemáticas básicas como suma, resta o multiplicación
(combinando los valores numéricos y generando resultados numéricos). Se pueden
utilizar los siguientes operadores aritméticos.
|
Operador aritmético
|
Significado
|
Ejemplo
|
|
+
(signo más)
|
Suma
|
A3+3
|
|
-
(signo menos)
|
Resta
Cambio de signo
|
B3-B1
-A1
|
|
* (asterisco)
|
Multiplicación
|
B3*C3
|
|
/
(barra diagonal o slash)
|
División
|
D3/3
|
|
%
(signo de porcentaje)
|
Porcentaje
|
20%
|
|
^
(acento circunflejo)
|
Exponenciación
|
C3^2
|
-
Operadores de comparación: Se utilizan cuando se
requiere comparar entre si dos valores. Como operadores de comparación tenemos los
siguientes:
|
Operador de comparación
|
Significado
|
Ejemplo
|
|
=
( igual )
|
Igual a
|
A1=B1
|
|
>
( mayor )
|
Mayor que
|
A1>B1
|
|
<
( menor )
|
Menor que
|
A1<B1
|
|
>=
( mayor o igual )
|
Mayor o igual que
|
A1>=B1
|
|
<=
( menor o igual)
|
Menor o igual que
|
A1<=B1
|
|
<>
(distinto)
|
Distinto de
|
A1<>B1
|
(
Cuando se comparan dos valores utilizando estos operadores de comparación, el
resultado es un valor lógico: VERDADERO o FALSO ).
- Operador
de concatenación de texto: Se utiliza el signo ( & “ampersand”
) para unir o concatenar una o varias cadenas de texto con el fin de generar un
solo elemento de texto.
|
Operador de
texto
|
Significado
|
Ejemplo
|
|
&
( "y" comercial)
|
Concatena
o une dos valores para generar un nuevo valor de texto continuo.
|
-
"Sierra"&" Nevada"
produce el valor “Sierra Nevada”
- A3&B3
crea un nuevo valor de texto formado
por
el valor de texto que contiene la
celda A3
concatenado o unido con el valor de
texto
que
contiene la celda B3
|
- Operadores
de referencia: Permiten utilizar rangos de celdas, produciendo
referencias a una lista de celdas, para ser utilizadas por otras operaciones en
los cálculos; por ejemplo, como argumentos en las funciones.
|
Operador de
referencia
|
Significado
|
Ejemplo
|
|
: (dos puntos)
|
Operador de rango que genera una
referencia a todas las celdas
que se encuentran entre dos referencias a
celdas.
|
B5:B15
Hace referencia a todas las celdas que se
encuentran en el rango B5 hasta B15
|
|
; (punto y coma)
|
Operador de unión que combina varias
referencias en una sola.
|
B5:B15;D5:D15
Hace referencia a las celdas que se
encuentran en el rango B5 hasta B15 más las celdas en el rango D5 hasta D15
|
|
(espacio)
|
Operador de intersección que genera una
referencia a celdas comunes a las
dos referencias.
|
B7:D7 C6:C8
Hace referencia a la celda C7
|
Una fórmula simple en Excel (
tiene un solo operador ), se puede representar de la forma siguiente:
= < operando_A > < operador > < operando_B >
2.2 Fórmulas
compuestas en Excel
No siempre
las fórmulas que se requieren utilizar son fórmulas simples ( fórmulas en las
que sólo se utiliza un operador ), ya que es mas frecuente necesitar fórmulas
en donde se requieren dos o mas operadores, lo cual implica cierta
dificultad tanto para expresar correctamente la fórmula, así como también, para
la evaluación de los diferentes operadores de manera que se obtenga el resultado
correcto, como por ejemplo, la
fórmula =PI()*B1^2 que se presentó anteriormente, es una fórmula
compuesta ya que tiene 2 operadores
aritméticos ( * y ^ ).
Una fórmula compuesta
se puede representar de la forma siguiente:
= <operando_A> <operador 1> <operando
B> <operador 2> <operando_C > …….
Cualquiera
de los operandos puede ser a su vez una
fórmula, esto es, puede estar formado por otros operandos y operadores.
2.2.1 Orden en
que Excel realiza las operaciones en las fórmulas
Como se
mencionó anteriormente, una fórmula está
compuesta por los elementos o valores que se van a procesar ( los operandos ),
combinados mediante los operadores. Excel realiza las operaciones especificadas
en la fórmula, indicadas por los operadores, de acuerdo a un orden que ya tiene
preestablecido. Para el cálculo de la fórmula Excel primero evalúa, de
izquierda a derecha, los distintos operadores que la conforman determinando en
que orden los debe procesar, para luego realizar los diferentes cálculos u operaciones
según el orden encontrado.
2.2.2 Precedencia de los operadores
Si se
combinan varios operadores en una única fórmula, Excel ejecutará las
operaciones en el orden correspondiente a cada operador según la tabla que se muestra
a continuación. Si una fórmula contiene operadores con la misma precedencia
(por ejemplo, si una fórmula contiene un operador de multiplicación y otro de
división), Excel realizará primero la operación que esté mas a la izquierda.
|
Orden
|
Operador
|
Descripción
|
|
1º
|
: (dos puntos)
(un solo espacio)
; (punto
y coma)
|
Operadores de referencia
|
|
2º
|
-
|
Cambio de signo (como en -A10)
|
|
3º
|
%
|
Porcentaje
|
|
4º
|
^
|
Exponenciación
|
|
|
* y /
|
Multiplicación y división
|
|
6º
|
+ y -
|
Suma y resta
|
|
7º
|
&
|
Une dos cadenas de texto
(concatenación)
|
|
8º
|
= <
> <= >= <>
|
Comparación
|
Tabla
de precedencia de los operadores
2.2.3 Uso de paréntesis
Para cambiar
el orden de evaluación de los operadores, se debe escribir entre paréntesis la
parte de la fórmula a la que se requiere cambiar el orden preestablecido, de
tal forma que ésta se procese antes que las demás. Los paréntesis se deben
colocar por pares, es decir, un paréntesis que abre y otro paréntesis que
cierra.
Por ejemplo,
la siguiente fórmula: =5+2*3
Produce como
resultado 11 porque Excel calcula la
multiplicación (5º lugar según la tabla) antes que la suma (6º lugar según la
tabla). La fórmula multiplica 2 por 3 y luego suma 5 al resultado.
Por el
contrario, si se utilizan paréntesis para cambiar el orden de evaluación, la
fórmula se puede escribir como:
=(5+2)*3
En este caso
Excel sumará 5 más 2 y luego multiplica
el resultado por 3, con lo que se obtiene 21.
En el
siguiente ejemplo, los paréntesis que rodean la primera parte de la fórmula le indican
a Excel que calcule primero la suma del valor que contiene la celda B4 más el
valor constante 25, y después divida el resultado obtenido entre la sumatoria
de los valores que contienen o se encuentran en las celdas D5, E5 y F5.
=(B4+25)/SUMA(D5:F5)
3 OPERANDOS EN LAS FÓRMULAS
En una fórmula los
operandos son los distintos valores que se utilizan para realizar las
operaciones especificadas por los distintos operadores. Estos operandos pueden
ser valores constantes, referencias a celdas, funciones.
3.1 Constantes
Una constante es un
valor que no se calcula ya que el mismo representa su valor. Por ejemplo, la
fecha 9-10-2008, el número 210 y el texto "Ganancias trimestrales"
son constantes. Una referencia a una celda, una fórmula, o un valor obtenido
como resultado de una fórmula, no son constantes.
Si se utilizan sólo
constantes en una fórmula en vez de referencias a celdas (por ejemplo,
=30+70+110), el resultado cambia sólo si modifica la fórmula, por lo que no
tiene sentido utilizar este tipo de fórmulas.
3.2 Referencias
a celdas
Una
referencia a celda o celdas, identifica una celda o un rango de celdas en una
hoja de cálculo e indica a Excel en qué celdas debe buscar los valores o los
datos que se requieren en una fórmula. En las referencias se puede utilizar
celdas de distintas partes de una hoja de cálculo. También puede hacerse
referencia a las celdas de otras hojas en el mismo libro de trabajo y a otros
libros de trabajo. Las referencias a celdas de otros libros de trabajo se
denominan vínculos.
3.2.1 Estilo de referencia A1
De forma
predeterminada, Excel utiliza el estilo de referencia A1, que se refiere a las columnas identificadas mediante letras (de
A a IV, para un total de 256 columnas) y a las filas identificadas mediante números
(del 1 al 65.536). Estas letras y números se denominan títulos o nombres de
fila y de columna. Para hacer referencia a una celda, se debe escribir la letra
de la columna seguida del número de fila. Por ejemplo, B2 hace referencia a la
celda que se encuentra ubicada en la intersección de la columna B y la fila 2.
|
|
Hace referencia
a:
|
|
A10
|
La celda que se encuentra en la columna A y
la fila 10
|
|
A10:A20
|
El rango de celdas de la columna A y de las
filas de la 10 a
la 20.
|
|
B15:E15
|
El rango de celdas de la fila 15 y de las
columnas B a E.
|
|
A10:E20
|
El rango de celdas de las columnas A a E y
de las filas 10 a
20.
|
|
5:5
|
Todas las celdas de la fila 5
|
|
5:10
|
Todas las celdas de las filas 5 a 10.
|
|
H:H
|
Todas las celdas de la columna H
|
|
H:J
|
Todas las celdas desde la columna H hasta
la columna J
|
3.2.2
Referencia a celdas en otra hoja
de cálculo
En el
siguiente ejemplo, la función PROMEDIO calcula el valor promedio de los valores
contenidos en el rango B1:B10 de la hoja de cálculo denominada Marketing del
mismo libro de trabajo.
Nombre
de la hoja
Referencia a una
celda o rangos de celdas de la hoja
=PROMEDIO(Marketing!B1:B10)
Separa la referencia de la
hoja y la referencia de las celdas
En este
ejemplo se puede observar la referencia a otra hoja de cálculo en el mismo
libro, donde el nombre de la hoja de cálculo seguido de un signo de exclamación
(!) preceden a la referencia de un rango de celdas.
3.2.3 Referencias
relativas y absolutas en las fórmulas
- Referencias relativas Una
referencia relativa de celda en una fórmula, siempre conserva la posición
relativa entre la celda que contiene la fórmula y la celda a la que hace
referencia. Si se traslada dicha fórmula de una celda a otra celda (se mueve o
se copia) se modifica la referencia de celda en la fórmula que se crea en la celda
destino. De forma predeterminada, cuando se ingresa una fórmula se utilizan
referencias relativas.
Cuando se
copia una fórmula de una celda a otra celda, la referencia a celdas en las
fórmula que se crea en la celda destino
se ajusta automáticamente; como por ejemplo, si la celda B2 contiene la fórmula =A1 (que hace una referencia relativa a
la celda A1) y se copia a la celda B3,
se modifica automáticamente la fórmula obteniéndose
en la celda B3 la nueva fórmula como =A2.
Fórmula,
con referencia relativa, copiada de la
celda B2 a la celda B3
- Referencias absolutas Una
referencia absoluta de celda en una fórmula, siempre hace referencia a la misma
celda ubicada en una columna y fila específica. Si se traslada dicha fórmula de
una celda a otra celda (se mueve o se copia) la referencia absoluta de celda en
la celda destino no varia. De forma predeterminada cuando se ingresa una
fórmula se utilizan referencias relativas y para cambiarlas a referencias absolutas, se debe anteponer el signo $ antes del nombre
de columna y del número de fila de la celda, por ejemplo $A$1.
Si una fórmula que contiene referencias absolutas
se copia a otra u otras celdas, la referencia absoluta no se modifica. Por
ejemplo, si la fórmula =$A$1, que
contiene una referencia absoluta a la celda A1, se copia de la celda B2
a la celda B3, la fórmula es la misma en ambas celdas.
Fórmula, con referencia absoluta, copiada de la celda B2 a la celda B3
- Referencias mixtas Una
referencia mixta de celdas tiene una columna absoluta y una fila relativa, o
una fila absoluta y una columna relativa. Una referencia de columna absoluta
adopta la forma $A1, $B1, etc.; mientras que una referencia de fila absoluta
adopta la forma A$1, B$1, etc. Si se
traslada dicha fórmula de una celda a otra celda (se mueve o se copia), se
cambia la referencia relativa y la referencia absoluta permanece invariable.
Si una
fórmula que contiene referencias mixtas se copia a otra u otras celdas, la
referencia relativa se modifica automáticamente y la referencia absoluta no se modifica.
Por ejemplo, si la fórmula =A$1, que contiene una referencia mixta
que indica que la fila no debe variar, se
copia de la celda B2 a la celda C3,
la nueva fórmula que contendrá la celda
C3 es entonces =B$1.
Fórmula,
con referencia mixta, copiada de la celda B2 a la celda C3
3.3 Funciones
Las
funciones son fórmulas predefinidas que proporciona Excel, las cuales ejecutan
cálculos utilizando los valores especificados (denominados argumentos) en un
orden determinado, para producir un
nuevo valor o grupo de valores. Las funciones pueden utilizarse para ejecutar
operaciones simples o complejas. Por ejemplo, la función REDONDEAR(A10;2) redondea un valor numérico que está en la
celda A10 hasta 2 posiciones decimales.
3.3.1
Estructura de las funciones
Todas las
funciones incluidas en Excel tienen la siguiente estructura:
Nombre(arg 1;arg 2;……;arg n)
La
estructura de una función comienza por el nombre de la función, un paréntesis
de
apertura, los argumentos de la función separados por punto y coma
y un paréntesis de cierre.
- Nombre de función.
Cada una de las funciones incluidas en Excel tiene un nombre único que las
diferencia unas de otras, este nombre es una cadena de caracteres alfabéticos,
por ejemplo la función PROMEDIO. En algunos casos el nombre incluye uno o más
puntos (.) dentro de la cadena de caracteres que lo conforman, como por ejemplo
la función PAGO.INT.ENTRE.
Para obtener una
lista de funciones disponibles, se selecciona una celda y se presiona
el
botón
para
insertar una función
en la barra de fórmulas
,
si está activa esta barra de fórmulas;
o
se abre
el menú
Insertar y a continuación se selecciona la opción
Función….
- Argumentos.
Los argumentos es una lista de valores separados por punto y coma (;), y pueden
ser números, referencias de celda, texto entre comillas, valores lógicos como
VERDADERO o FALSO, matrices, o valores de error como #N/A. Los argumentos
pueden ser también constantes, fórmulas u otras funciones. La cantidad de
argumentos que deban ingresarse a una función es definido por la función misma,
y cada uno de estos argumentos deberá ser un valor válido y del tipo
requerido por el correspondiente orden en que es especificado por la función.
Algunas funciones no necesitan argumentos como la función PI( ). Nótese que a
pesar de no tener argumentos esta función, es obligatorio colocarle tanto el
paréntesis de apertura como el de cierre.
Información sobre la estructura de la función. Cuando se escribe la
función, aparece la información sobre la estructura de esta función, con su sintaxis
y sus argumentos. Por ejemplo, al ingresar en una celda
=REDONDEAR(
aparecerá la descripción de esta función.
La información de la estructura de las funciones
sólo aparece para las funciones que vienen integradas
en Excel
.
En este ejemplo, el signo igual ( = ) se
ingresa antes del nombre de la función para indicar que la función está
incluida en una fórmula.
3.3.2 Funciones Anidadas
En algunos casos, puede ser necesario utilizar
una función como uno de los argumentos de otra función. Por ejemplo, la
siguiente fórmula utiliza la función SI
(=SI(arg1;arg2;arg3)), la cual compara el
resultado producido por la función PROMEDIO
(función anidada) con el valor 50, para
determinar cual valor se le va a asignar a la celda en la que se introduce la
fórmula.
Resultados
válidos Cuando se utiliza una función anidada como argumento, ésta
deberá devolver el mismo tipo de valor que el requerido por este argumento. Por ejemplo, si el argumento especifica
un valor VERDADERO o FALSO, la función
anidada deberá devolver VERDADERO o FALSO. Si éste no es el caso, Excel
mostrará el valor de error #¡VALOR!
Límites
del nivel de anidamiento Una fórmula puede contener como máximo
siete niveles de funciones anidadas. Si la Función B se utiliza como argumento
de la Función A, la Función B es una función de segundo nivel. En el ejemplo
anterior, la función PROMEDIO y la función SUMA son ambas funciones de segundo
nivel porque son argumentos de la función SI. Una función anidada dentro de la
función PROMEDIO será una función de tercer nivel, etc.
3.4 Introducir una fórmula
Como se
mencionó anteriormente, las fórmulas son expresiones que efectúan cálculos con
los valores que se encuentran en la misma hoja de cálculo o en otra hoja del
mismo libro de trabajo, para producir un nuevo valor que se va a asignar a la
celda en la cual se introduce la fórmula. Para introducir una fórmula en una
celda, se debe entrar como primer carácter el signo igual ( = ).
3.4.1 Introducir una fórmula sencilla
Una
fórmula sencilla o simple contiene un
solo operador y uno o dos operandos, ejemplos de estas fórmulas se muestran a
continuación:
|
Fórmula
|
Acción
|
|
=128+345
|
Suma 128 y 345
|
|
=5,25^2
|
Halla el cuadrado de 5,25
|
Para introducir estas fórmulas
se puede seguir el siguiente procedimiento:
- Seleccionar la celda en que desee introducir la
fórmula.
- Escribir el signo igual (=).
- Introducir la fórmula.
- Presionar ENTRAR.
Otra forma para ingresar una
fórmula, es utilizar la barra de fórmulas
si está activada.
3.4.2
Introducir
una fórmula que contenga referencias de celdas:
Las siguientes
fórmulas contienen referencias relativas de celdas. La celda que contiene la
fórmula se denomina celda dependiente cuando su valor depende de los valores de
otras celdas. Por ejemplo si la celda B2 contiene la fórmula =C2,
entonces se tiene que la celda B2 es dependiente de la celda C2.
|
Fórmula
|
Acción
|
|
=C2
|
Utiliza el valor de la celda C2
|
|
=Hoja2!B2
|
Utiliza el valor de la celda B2 de Hoja2
|
|
=-A10
|
Cambia de signo al valor numérico que se
encuentra en la celda A10
|
|
=A1+23
|
Suma al valor que contiene la celda A1 el
número 23
|
Para
introducir estas fórmulas se puede seguir cualquiera de los siguientes
procedimientos:
- Procedimiento 1
- Seleccionar la celda en que desee introducir la
fórmula.
- En la celda seleccionada o en la barra de fórmulas ,
escribir = (signo igual).
- Escribir a continuación la fórmula.
- Al finalizar
de escribir la fórmula se presiona ENTRAR o se presiona el botón
introducir
.
- Procedimiento 2
- Seleccionar la celda en que desee introducir la
fórmula.
- En la celda seleccionada o en la barra de fórmulas ,
escribir = (signo igual).
- Comenzar a escribir la fórmula y para ingresar las
referencias a celdas dentro de la fórmula, se selecciona una celda, un
rango de celdas, una ubicación de otra hoja de cálculo o una ubicación de
otro libro. Puede arrastrar el borde de la selección de celdas para mover
la selección, o bien arrastrar la esquina del borde para ampliar la
selección.
- Al finalizar
de escribir la fórmula se presiona ENTRAR o se presiona el botón
introducir .
Cuando se
introduce una fórmula que contiene referencia de celdas Excel muestra en colores diferentes las
distintas referencias. Por ejemplo, al introducir en la celda D9 la fórmula =(C9*B9)
se podrá observar algo como lo siguiente:
3.4.3 Introducir una fórmula
que contenga una función
Las
siguientes fórmulas contienen funciones.
|
Fórmula
|
Acción
|
|
=SUMA(A:A)
|
Suma todos los valores numéricos en la
columna A
|
|
=PROMEDIO(A1:B4)
|
Calcula el promedio de todos los valores
numéricos en el rango A1:B4
|
Para introducir en Excel una
fórmula que contenga funciones, como las mostradas en el cuadro anterior, se
puede seguir el siguiente procedimiento:
- Seleccionar la celda en que se desee introducir la
fórmula.
- Para iniciar la fórmula con la función, se debe
hacer clic en Insertar función
en la barra de fórmulas ,
o abrir el menú insertar y seleccionar la opción Función….
- Seleccionar la función que desee utilizar. Se puede
seleccionar de la lista de funciones que se muestra el nombre de la
función; si se hace difícil localizar el nombre de la función que
interesa, se puede seleccionar la
categoría a la cual pertenece la función o escribir una pregunta que
describa lo que desee hacer en el cuadro Buscar una función (por
ejemplo, al escribir "sumar números" devuelve la función SUMA).
- Luego se introducen los argumentos en su
correspondiente cuadro de diálogo que se muestran en la ventana Argumentos de función,
siguiendo las especificaciones indicadas para cada argumento. Para introducir
referencias de celdas como argumentos, se pueden escribir directamente con
el teclado o, como es más fácil y seguro, presionando el botón Contraer diálogo
,
para ocultar el cuadro de diálogo temporalmente, y se procede a
seleccionar la celda o celdas en la hoja de cálculo, después
se presiona el botón Expandir
diálogo 
,
para volver al cuadro de diálogo.
- Una vez que se tenga completa la fórmula se debe presionar
el botón aceptar, para que la fórmula se introduzca en la celda y se
calcule su resultado.
3.4.4 Introducir una fórmula
con funciones anidadas
Como se mencionó
anteriormente, una función anidada es aquella en que uno o varios de sus argumentos son a su vez funciones. Como por ejemplo:
=SI(PROMEDIO(F2:F5)>50;SUMA(G2:G5);0)
Esta fórmula produce como resultado la sumatoria de
un conjunto de números (SUMA) que se encuentran en el rango de celdas
comprendido desde la celda G2 hasta la celda G5 (G2:G5), sólo si el PROMEDIO de
otro conjunto de números, que se encuentran en el rango de celdas comprendido
desde la celda F2 hasta la celda F5 (F2:F5), es mayor que 50; en caso
contrario, produce como resultado el valor 0 (cero).
Para introducir esta
fórmula se puede seguir el siguiente procedimiento:
- Seleccionar la celda en que desee introducir la
fórmula.
- Para iniciar la fórmula con la función, se debe
hacer clic o presionar el botón Insertar función en
la barra de fórmulas .
- Seleccionar la función que se necesita utilizar
como función de primer nivel. Se puede seleccionar de la lista de
funciones que se muestra el nombre de la función correspondiente; si se
hace difícil localizar el nombre de la función, se puede seleccionar la categoría a la cual
pertenece la función o escribir una pregunta que describa lo que desee
hacer en el cuadro Buscar una función (por ejemplo, al escribir "sumar
números" devuelve la función SUMA).
- Se introducen los argumentos en su correspondiente
cuadro de diálogo que se muestran en la ventana Argumentos de función, siguiendo las especificaciones
indicadas para cada argumento.
- Para introducir referencias de celdas como
argumentos, se pueden escribir directamente con el teclado o, como es más
fácil y seguro, presionando el botón
Contraer diálogo ,
para ocultar el cuadro de diálogo temporalmente, y se procede a
seleccionar la celda o celdas en la hoja de cálculo, después
se debe presionar el botón Expandir
diálogo ,
para volver al cuadro de diálogo.
- Para escribir otra función como uno de los argumentos,
se introduce la función en el cuadro de argumento deseado. Por ejemplo, se
puede agregar SUMA(G2:G5) en el cuadro de edición valor_si_verdadero.
- Para cambiar las partes de la fórmula mostradas en
el cuadro de diálogo Argumentos de función, se debe hacer clic en
el nombre de función en la barra de fórmulas .
Por ejemplo, si se hace clic en SI, aparecerá la descripción de la
estructura de la función SI, con una lista de los argumentos requeridos
por esta función.
- Para
introducir la misma fórmula en un rango de celdas, se debe seleccionar en
primer lugar el rango, luego se introduce la fórmula y, a continuación, se
presionan simultáneamente las teclas <CTRL> y <ENTRAR>.
- Si se está familiarizado con los argumentos de una
función, se puede utilizar la información sobre funciones que aparece
después de escribir el nombre de la función y el paréntesis de apertura. Se
puede hacer clic en el nombre de la función para ver el tema de la Ayuda correspondiente a
la función o hacer clic en un nombre de argumento para seleccionar el
argumento correspondiente de la fórmula. Para ocultar la información sobre
funciones, en el menú Herramientas hacer clic en Opciones y
desactivar la casilla de verificación Información sobre herramientas de
funciones de la ficha o pestaña General.
4 COPIAR UNA FÓRMULA
Cuando se
trabaja con una hoja de cálculo, el objetivo primordial es expresar los
cálculos requeridos que llevan a la solución de un problema mediante una o más
fórmulas. Pero es frecuente que, por la naturaleza del problema, una fórmula
que se ha introducido en una celda se necesite copiarla a otra u otras celdas
donde se aplica la misma fórmula. Al copiarse una fórmula de una celda a otras
celdas, las referencias de celda pueden cambiar de acuerdo al tipo de
referencia que se utilice.
Para copiar
se pueden utilizar los siguientes procedimientos:
- Procedimiento 1
- Seleccionar la celda que contenga la fórmula a
copiar.
- Comprobar que las referencias de celda utilizadas
en la fórmula producirán el resultado deseado. Si se requiere, se debe cambiar
el tipo de referencia de celda, por ejemplo, si en una fórmula se necesita
que siempre se haga referencia a una misma celda, entonces esta referencia
se debe cambiar a referencia absoluta o mixta.
- Abrir el menú Edición, y elegir la opción Copiar.
- Luego seleccionar la celda o celdas en la que se desea
copiar la fórmula.
- Abrir de nuevo el menú Edición. Para copiar
la fórmula y el formato hacer clic en Pegar.
- Para copiar la fórmula solamente hacer clic en Pegado
especial y, a continuación, en Fórmulas.
- Procedimiento 2
Si la fórmula que se necesita
copiar va a ser copiada a una celda o rango de celdas que están contiguas
o
adyacentes a la misma, se puede copiar
la fórmula utilizando el cuadro de llenado o botón de relleno
(cuadrado negro pequeño situado en la esquina inferior
derecha de la
selección. 
Cuando se sitúa el cursor
del mouse (ratón) sobre el controlador de relleno, el apuntador cambia a
una cruz negra) 
.
- Seleccionar la celda que contenga la fórmula a
copiar.
- Comprobar que las referencias de celda utilizadas
en la fórmula producirán el resultado deseado. Si se requiere, se puede
cambiar el tipo de referencia de celda, por ejemplo, si en una fórmula se
necesita que siempre se haga referencia a una misma celda, entonces esta
referencia se debe cambiar a referencia absoluta o mixta.
- Colocar el cursor sobre el cuadro de llenado y, a
continuación, arrastrar el cuadro de
llenado, presionando el botón izquierdo del mouse o ratón, hasta la celda o rango de celdas en las que se desear
copiar la fórmula.
4.1 Procedimiento utilizado
por Excel para crear las fórmulas que se copian
Cuando se copia una
fórmula de una celda a otras u otras celdas el Excel aplica el siguiente
procedimiento:
1. Obtiene
el desplazamiento en columnas y filas que existe entre la celda origen y la
celda destino.
2. Para
crear la fórmula en la celda destino, utiliza como base la fórmula existente en
la celda origen o celda que se copia, modificando, en cada una de las referencia
de celdas que se encuentran en esta fórmula, la referencia a la columna sumándole o restándole el desplazamiento de
columna obtenido antes, y luego hace lo mismo con la referencia a la fila. Si
la referencia a columna o fila está escrita en forma absoluta, el
desplazamiento encontrado no es aplicado, esto es, la referencia a la columna o fila permanece igual o no es
modificada.
En el ejemplo
siguiente se explicará que sucede con las referencias de celda cuando se copia
una fórmula de la celda A1 a la celda C3:
Fórmula que
se copia de la celda A1 a la celda C3
Como se puede
observar, entre las celdas A1 y C3 hay un desplazamiento en columnas de 2 y un
desplazamiento de filas de 2.
-
Si la fórmula que se tiene en la celda A1 es =A2*B3 (columna relativa y fila
relativa), entonces la fórmula que se tendrá en la celda C3 es =C4*D5,
en donde se observa que a cada columna de las referencias de celda de la
fórmula original se le suma 2, y a cada fila también se le suma 2.
-
Si la fórmula que se tiene en la celda A1 es =A$2*B$3 (columna relativa y fila
absoluta), entonces la fórmula que se tendrá en la celda C3 es =C$2*D$3,
en donde se observa que a cada columna de las referencias de celda de la
fórmula original se le suma 2, y las filas permanecen sin variar o fijas ya que
están escritas como absolutas (el signo $ antes del número de fila).
-
Si la fórmula que se tiene en la celda A1 es =$A2*$B3 (columna absoluta y fila
relativa), entonces la fórmula que se tendrá en la celda C3 es =$A4*$B5,
en donde se observa que las columnas de las referencias de celda de la
fórmula original permanecen sin variar o
fijas ya que están escritas como absolutas (el signo $ antes del nombre de
columna) y a cada fila se le suma 2.
-
Si la fórmula que se tiene en la celda A1 es =$A$2*$B$3 (columna absoluta y fila
absoluta), entonces la fórmula que se tendrá en la celda C3 es =$A$2*$B$3,
en donde se observa que tanto las columnas y filas de las referencias de celda
de la fórmula original permanecen sin
variar o fijas ya que están escritas como absolutas (el signo $ antes del
nombre de columna).
-
Si la fórmula que se tiene en la celda A1 es =$A2*B$3, entonces la fórmula que se
tendrá en la celda C3 es =$A4*D$3.
-
Si la fórmula que se tiene en la celda A1 es =A2*$B$3, entonces la fórmula que se
tendrá en la celda C3 es =C4*$B$3.
-
Si la fórmula que se tiene en la celda A1 es =A2*B$3, entonces la fórmula que se
tendrá en la celda C3 es =C4*D$3.
-
Si la fórmula que se tiene en la celda A1 es =A$2*$B$3, entonces la fórmula que se
tendrá en la celda C3 es =C$2*$B$3.
|
Fórmula en C1
(origen)
|
Tipo de
referencias
|
Fórmula en C3
|
|
=A2*B3
|
(columna relativa y fila relativa)
|
=C4*D5
|
|
=A$2*B$3
|
(columna relativa y fila absoluta)
|
=C$2*D$3
|
|
=$A2*$B3
|
(columna absoluta y fila relativa)
|
=$A4*$B5
|
|
=$A$2*$B$3
|
(columna absoluta y fila absoluta)
|
=$A$2*$B$3
|
|
=$A2*B$3
|
(columna o fila absoluta o relativa)
|
=$A4*D$3
|
|
=A2*$B$3
|
(columna o fila absoluta o relativa)
|
=C4*$B$3
|
|
=A2*B$3
|
(columna o fila absoluta o relativa)
|
=C4*D$3
|
|
=A$2*$B$3
|
(columna o fila absoluta o relativa)
|
=C$2*$B$3
|
5. MODIFICAR O EDITAR UNA FÓRMULA
Sin
por alguna razón, una fórmula que ya se ha ingresado en una celda necesita ser
modificada, no es conveniente volver a escribir la fórmula desde el principio,
sino utilizar el procedimiento para modificación o edición del contenido de una
celda, el cual se muestra a continuación:
- Haga doble clic en la
celda que contiene la fórmula que desea modificar.
- Modifique el contenido de
la celda preferiblemente en la barra de fórmulas
- Realice las modificaciones
que sean necesarias (insertar,
cambiar o eliminar caracteres en la fórmula), utilizando para
desplazarse en la misma el cursor del mouse y/o las teclas de direccionamiento del
cursor.
- Para finalizar la edición
y aceptar las modificaciones
realizadas presione la tecla ENTRAR o
presione el botón introducir de
la barra de fórmulas.
(
para cancelar los cambios presione la tecla
ESCape o
presione el botón
).
6. ELIMINAR FÓRMULAS
Para eliminar fórmulas que se
encuentren en una hoja de cálculo, el procedimiento más directo y sencillo es
el siguiente:
- Seleccionar
la celda que contenga la fórmula.
- Presionar la tecla <SUPR>.
Se
pueden eliminar todas las fórmulas que están en un rango de celdas, para lo
cual se debe seleccionar primero el rango de celdas y luego se presiona la
tecla <SUPR>.
7. CORREGIR FÓRMULAS
Al igual que un
corrector gramatical, Excel emplea algunas reglas para comprobar si hay
problemas en las fórmulas. Estas reglas no garantizan que la hoja de cálculo no
tenga ningún problema, pero ayudan en gran medida a encontrar los errores más
comunes.
Los problemas se
pueden revisar de dos formas: de uno en uno, como con el corrector ortográfico,
o inmediatamente sobre la hoja de cálculo mientras se trabaja. Cuando Excel detecta
un problema, muestra un pequeño triángulo en la esquina superior izquierda de
la celda. Ambos métodos presentan las mismas opciones.
Celda que posiblemente tiene un
problema de fórmula
Si se selecciona la
celda que muestra este triángulo aparece al lado de la misma el
botón
que al hacer clic en él muestra un conjunto
de opciones. Se puede solucionar el problema utilizando alguna de las opciones
que aparecen u omitirlo. Si se omite, ya no volverá a aparecer en las
comprobaciones de errores subsiguientes. Sin embargo, todos los errores que
haya omitido se pueden restablecer para que vuelvan a mostrarse.
Es frecuente que en
una hoja de cálculo nos aparezca este indicador de error en celdas que con toda
seguridad sabemos que no tienen errores,
como cuando Excel detecta que al copiarse una fórmula a una celda, una
de las celdas contiguas a ésta contiene una fórmula que es diferente, lo cual
lo podríamos interpretar como una señal de precaución.
Más adelante se
explicará con detalle estos y otros
procedimientos para corregir errores.
8. EJEMPLOS
Y APLICACIONES
A
continuación se encuentran algunos ejemplos de cómo llevar una expresión
matemática a su correspondiente fórmula en Excel. Es el usuario del Excel quien
debe encontrar la forma correcta de expresar la fórmula para luego ingresarla
al Excel, ya que éste solamente se encarga de realizar las operaciones o
cálculos, según se lo indique el usuario mediante la fórmula ingresada, y en ningún
momento el Excel le va a indicar al usuario que el valor obtenido está
correcto, es decir, que este valor sería exactamente igual
al que se obtendría al calcular de forma
manual la expresión matemática correspondiente.
Como
primer paso, antes de ingresar la fórmula en la hoja de cálculo, el usuario
debe escribir está expresión matemática como una fórmula que se pueda utilizar
en la mayoría de los lenguajes de programación o en la mayoría de los programas
de aplicación como es el caso del Excel.
Siendo La primera dificultad escribir la
fórmula en una sola línea, donde no se
refleja el orden en que estamos acostumbrados a realizar las operaciones cuando
el cálculo lo realizamos de forma manual. Entonces, la fórmula la podríamos
escribir como:
A*B/RAIZ(C)/C-1/B-2*C/A
Si
en esta fórmula analizamos
el orden de
evaluación que se tiene establecido para cada uno de los operadores
, evaluando
los diferentes operadores de izquierda a derecha de la fórmula, y aplicando para
cada operador el orden que se tiene establecido para cada uno de éstos, como se
muestra en
la tabla de precedencia de
los operadores que se encuentra en la
página 4. Además se debe tener presente que cuando en una fórmula se tienen
varios operadores del mismo orden jerárquico, como por ejemplo dos o más
multiplicaciones o una multiplicación y una o más divisiones, primero se realizará el cálculo
que se corresponde al operador mas a la izquierda y luego se continuará con los
otros operadores.
En nuestro caso, al aplicar el procedimiento
descrito anteriormente, tenemos
lo siguiente:
A*B/RAIZ(C)/C-1/B-2*C/A
2
3
1 4 5 . .
. . . . . . .
Vemos
que el resultado que se obtendría al calcular la fórmula, tal cual se ha
escrito, estaría errado; ya que la división entre C, indicada por la operación 4, se estaría realizando fuera de
orden y, por lo tanto, alteraría el
resultado; por lo que se deben insertar paréntesis para cambiar este orden
(primero se debe calcular C-1/B y
luego realizar la división). Entonces al
agregar los paréntesis, la fórmula quedará como:
A*B/RAIZ(C)/(C-1/B)-2*C/A
4 5 3 6
2 1 9 7 8
En
esta fórmula, al realizar el usuario la evaluación del orden de los operadores,
primero se evalúan todos los operadores que están dentro de los paréntesis y
después los operadores que están fuera de los paréntesis. Esta fórmula, tal
cual está escrita, calcula correctamente el
valor de la expresión matemática, ya que las operaciones se realizan en el
orden requerido o necesario, que se corresponde con uno de los varios ordenes en que se pueden realizar los
cálculos de forma manual. Una expresión matemática para ser utilizada en el
computador puede ser escrita de varias formas equivalentes, ya que producen el
mismo resultado final cambiando solamente el orden en que se realizan las
operaciones.
En
este ejemplo podemos tener las siguientes formas de escritura de la fórmula que
calculan correctamente el valor correspondiente a la expresión matemática
indicada.
Forma 1:
A*B/RAIZ(C)/(C-1/B)-2*C/A (forma básica)
4 5 3
6 2 1 9 7 8
Forma
2: (A*B/RAIZ(C))/(C-1/B)-2*C/A
2 3 1 6 5 4 9 7 8
Forma
3: (A*B/RAIZ(C))/(C-1/B)-(2*C/A)
2 3 1 8 5 4 9 6 7
Forma 4: ((A*B/RAIZ(C))/(C-1/B))-(2*C/A)
2 3 1 6 5 4 9 7 8
Como
se indicó en el enunciado del ejercicio, la fórmula en Excel correspondiente
debe ser ingresada en la celda D3, con el valor de A en la celda D1, el valor de
B en la celda E1 y el valor de C
en la celda F1. Al hacer los cambios aquí señalados, podemos entonces escribir
las correspondientes fórmulas en Excel.
Forma 1: =D1*E1/RAIZ(F1)/(F1-1/E1)-2*F1/D1
Forma
2:
=(D1*E1/RAIZ(F1))/(F1-1/E1)-2*F1/D1
Forma 3: =(D1*E1/RAIZ(F1))/(F1-1/E1)-(2*F1/D1)
Forma 4: =((D1*E1/RAIZ(F1))/(F1-1/E1))-(2*F1/D1)
Sí
ingresamos en la celda D3 la
primera fórmula, utilizando 1
como valor de A (celda D1), 3 como valor de B (celda E1) y 4 como valor de C (celda F1), se obtiene como resultado el
valor -7,59090909
Si
se ingresan en otras celdas de la hoja de cálculo las otras tres fórmulas (por
ejemplo en las celdas D4, D5 y D6),
se observará que el resultado que
obtiene el Excel es el mismo para las cuatro fórmulas. Entonces, la única diferencia
que hay entre estas cuatro formas de expresar correctamente la fórmula en Excel
correspondiente a la expresión matemática que se está estudiando, es el orden en que el Excel realiza los
cálculos en cada una de ellas.
Para
observar el orden que sigue el Excel en el cálculo de las fórmulas, se utiliza la
herramienta Auditoría de fórmulas
opción Evaluar Fórmula, siguiendo el
siguiente procedimiento:
-
Seleccionar
la celda donde está la fórmula
-
Luego abrir el menú Herramientas
-
Seleccionar la opción Auditoría de fórmulas
-
Del nuevo menú que se presenta seleccionar Evaluar fórmula
-
Para que se vaya realizando cada operación paso a paso presionar el botón Evaluar
-
Para finalizar presionar el botón Cerrar
En
nuestro caso si se manda a Evaluar las
fórmulas en cada una de las celdas
donde se ingresaron, se observa que el orden en que el Excel realiza los
cálculos es diferente en cada de estas fórmulas, siendo la forma 4, la que tiene un orden de cálculo mas
semejante al orden que nos sirvió para llegar a la escritura correcta de la
fórmula.
La
utilización de la herramienta Auditoría
de fórmulas opción Evaluar Fórmula, es muy útil cuando se
está implementando una expresión matemática compleja, ya que permite observar
como Excel está realizando los cálculos
y, a partir de esto, determinar si una operación se está realizando fuera de
orden lo que llevaría a cálculos errados.
b. Escribir
la fórmula para la siguiente expresión matemática e indique
el orden cómo se debe evaluar dicha
fórmula para calcular el valor
correspondiente. La fórmula en Excel se
debe ingresar en la celda E5, y que la celda E1 contiene el valor de A, la celda
E2 el valor de B, la celda E3 el valor
de C.
Aplicando
el mismo procedimiento que se utilizó en el ejercicio a, llegamos a las
siguientes fórmulas que se corresponden
con la expresión matemática indicada.
Forma
1: (2*A-B/RAIZ(C))/(C+1/B)*B/4 (forma
básica)
2 4 3 1 7
6 5 8 9
Forma
2: (2*A-B/RAIZ(C))/(C+1/B)*(B/4)
2 4 3 1 8 6 5 9 7
Forma 3: ((2*A-B/RAIZ(C))/(C+1/B))*(B/4)
2 4 3 1 7 6 5 9 8
La
fórmula en Excel correspondiente debe ser ingresada en la celda E5, con el
valor de A en la celda E1, el valor
de B en la celda E2 y el valor de C en la celda E3.
Forma 1: =(2*E1-E2/RAIZ(E3))/(E3+1/E2)*E2/4
Forma 2: =(2*E1-E2/RAIZ(E3))/(E3+1/E2)*(E2/4)
Forma 3: =((2*E1-E2/RAIZ(E3))/(E3+1/E2))*(E2/4)
Al ingresar estás formulas en una
hoja de Excel, utilizando 2 como valor
de A, 5 como valor de B y 9 como valor de C, obtenemos para las tres formas
de escribir correctamente la fórmula el valor 0,31702899
c. La expresión matemática para el cálculo del
monto de la cuota postpagable de un
préstamo (la cuota se paga al final del período), viene dada como:
Escribir
la fórmula correspondiente a esta
expresión matemática e indicar el orden cómo
se debe evaluar dicha fórmula para
calcular el valor de la cuota
postpagable:
Forma 1:
VA*T*(1+T)^N/((1+T)^N-1) (forma básica)
6 7 1 5 8 2 3 4
¿Cómo copio un ejemplo?
