C.F.P.

Luis Cáceres Graziani

Creación y edición de fórmulas
20 de Octubre de 2023
Instructor: Ccari Guerra Julio Alexander [Link]
Objetivos:
• Desarrollar operaciones con fórmulas
• Conocer las referencias de celdas
• Administrar rangos de celdas [Link]
Introducción a la creación de fórmulas

• Las fórmulas son ecuaciones que efectúan

cálculos con los valores de la hoja de
cálculo. Una fórmula comienza por un signo
igual (=). Por ejemplo, la siguiente fórmula
multiplica 2 por 3 y, a continuación, suma 5
al resultado =5+2*3

Fuente: Información general sobre fórmulas en Excel - Soporte

técnico de Microsoft

[Link]
Estructura de una fórmula

A considerar:
1. Toda fórmula inicia con el símbolo igual =
2. Conocer la precedencia de operadores.
3. Evitar en lo necesario el uso de constantes.

[Link]
Operadores

• Los operadores especifican el tipo de cálculo que

desea realizar en los elementos de una fórmula,
como suma, resta, multiplicación o división. En este
artículo, aprenderá el orden predeterminado en el
que los operadores actúan en función de los
elementos de un cálculo. También aprenderá a
cambiar este orden usando paréntesis.
Tipos de operadores
• Existen cuatro tipos de operadores de cálculo:
1. Operadores aritméticos
2. Operadores de comparación
3. Operadores concatenación de texto
4. Operadores de referencia.

Fuente: Usar operadores de cálculo en fórmulas de Excel - Soporte técnico de Microsoft

[Link]
Operadores Aritméticos

• Para realizar operaciones Operador aritmético Significado Ejemplo

matemáticas básicas como
+ (signo más) Suma =3+3
suma, resta o multiplicación (o
Resta =3–1
para combinar números) y – (signo menos)
Negación =–1
generar resultados numéricos,
* (asterisco) Multiplicación =3*3
use los operadores aritméticos
/ (barra oblicua) División =3/3
de esta tabla.
% (signo de porcentaje) Porcentaje =20 %

^ (acento circunflejo) Exponenciación =2^3

[Link]
Operadores de Comparación

• Con los operadores de la tabla Operador de comparación Significado Ejemplo

siguiente, puede comparar dos = (signo igual) Igual a =A1=B1
valores. Cuando se comparan
> (signo mayor que) Mayor que =A1>B1
dos valores mediante estos
operadores, el resultado es un < (signo menor que) Menor que =A1<B1
valor lógico, ya sea
VERDADERO o FALSO. >= (signo mayor o igual que) Mayor o igual que =A1>=B1

<= (signo menor o igual que) Menor o igual que =A1<=B1

<> (signo distinto de) No es igual a =A1<>B1

[Link]
Operadores concatenación de texto

• Use la y comercial (&) para Operador de texto Significado Ejemplo

unir o concatenar una o más & ("y" comercial) Conecta o concatena ="North"&"wind"
dos valores para
cadenas de texto para generar generar un valor de
un único fragmento de texto. texto continuo.

[Link]
Operadores de Referencia.

Operador de referencia Significado Ejemplo

• Combine rangos de celdas
para los cálculos con estos : (dos puntos) Operador de rango, que =SUMA(B5:B15)
genera una referencia a
operadores. todas las celdas entre
dos referencias, estas
incluidas

, ; (coma o punto y coma) Operador de unión, que =SUMA(B5:B15;D5:D15)

combina varias
referencias en una sola.

(espacio) Operador de =SUMA(B7:D7 C6:C8)

intersección, que genera
una referencia a las
celdas comunes a las
dos referencias.

[Link]
Orden en que Excel ejecuta las operaciones en las fórmulas

• En algunos casos, el orden en el que se

realiza el cálculo puede afectar al valor
devuelto de la fórmula, por lo que es
importante comprender el orden y cómo
puede cambiar el orden para obtener los
resultados que espera ver.

[Link]
Orden de cálculo

• Las fórmulas calculan los valores en un

orden específico. Las fórmulas de Excel
siempre comienzan por un signo igual
(=). El signo igual indica a Excel que los
caracteres siguientes constituyen una
fórmula. Después de este signo igual,
puede haber una serie de elementos que
se van a calcular (los operandos), que
están separados por operadores de
cálculo. Excel calcula la fórmula de
izquierda a derecha, según el orden
específico de cada operador de la
fórmula.

[Link]
Prioridad de operadores
Operador Descripción
: (dos puntos) Operadores de referencia
• Si se combinan varios operadores en una (un solo espacio)
única fórmula, Excel ejecutará las . (punto)
operaciones en el orden que se indica en – Negación (como en –1)
la siguiente tabla. Si una fórmula % Porcentaje
contiene operadores con la misma ^ Exponenciación
prioridad (por ejemplo, si una fórmula *y/ Multiplicación y división
contiene un operador de multiplicación y +y- Suma y resta
otro de división), Excel evaluará los & Conecta dos cadenas de
operadores de izquierda a derecha. texto (concatenación)
= Comparación
<>
<=
>=
<>

[Link]
Uso de paréntesis
• Para cambiar el orden de evaluación, escriba entre paréntesis la parte de la fórmula que se calculará en primer lugar. Por ejemplo,
la siguiente fórmula da como resultado el valor de 11, porque Excel calcula la multiplicación antes que la suma. La fórmula multiplica
primero 2 por 3 y después suma 5 al resultado.

=5+2*3
• Por el contrario, si usa paréntesis para cambiar la sintaxis, Excel suma 5 y 2 y, a continuación, multiplica el resultado por 3 para
producir 21.

=(5+2)*3
• En el ejemplo siguiente, los paréntesis que encierran la primera parte de la fórmula obligarán a Excel a calcular B4+25 en primer
lugar y, a continuación, dividirá el resultado por la suma de los valores de las celdas D5, E5 y F5.

=(B4+25)/SUMA(D5:F5)

[Link]
Crear o modificar una referencia de celda
• Una referencia de celda se refiere a una Esta fórmula:
=C2
Hace referencia a:
Celda C2
Y devuelve:
El valor de la celda C2.
celda o un rango de celdas en una hoja =A1:F4 Celdas A1 a F4 Valores de todas las
de cálculo y se puede usar en una celdas, pero se debe
fórmula de manera que Microsoft Office presionar
Ctrl+Mayús+Entrar una
Excel pueda encontrar los valores o vez escrita la fórmula.
datos que desea que calcule la fórmula. Nota: esta funcionalidad
no funciona en Excel para
• Puede usar una referencia de celda en la Web.
una o varias fórmulas para hacer =Activo-Pasivo Las celdas denominadas El valor de la celda Pasivo
referencia a: Activo y Pasivo restado del valor de la
celda Activo.
• Datos de una celda en la hoja de
cálculo. {=Semana1+Semana2} Los rangos de celda La suma de los valores de
• Datos que se encuentran en distintas denominados Semana1 Y
Semana2
los rangos de celda
Semana1 y Semana2
áreas de la hoja de cálculo. como una fórmula
matricial.
• Datos que se encuentran en celdas de
=Hoja2!B2 Celda B2 de Hoja2 El valor de la celda B2 de
otras hojas de cálculo en el mismo libro. Hoja2

[Link]
Referencia Relativa
• Una referencia relativa señala a una celda, por ejemplo, • Al copiar la fórmula la posición de cada referencia ha ido
B10 o G5. Esta referencia hace que la dirección de las cambiando(posición relativa).
celdas cambie al momento de copiar una fórmula o una
función.

• Por ejemplo, en el siguiente caso para calcular el Total

Pacientes hay que sumar la cantidad de pacientes del
Turno1 y Turno2 , ingresando la siguiente fórmula en la
celda E5, =C6+D6 pulsar ENTER y luego copiar la fórmula
hasta E11.

• Observe el resultado final:

[Link]
Referencia Absoluta
• En las referencias absolutas, la dirección de la celda se • El primer resultado debe ser: 5400 y observe un error y
mantiene fija; si copia la fórmula, está siempre señalará la resultados inconsistentes.
misma celda. Para lograr que una celda sea absoluta, debe
escribir la dirección de la celda con el formato de
$Columna$Fila. Por ejemplo, $B$10 o $G$5.

• Para calcular el Monto Total, se debe multiplicar el Total

Pacientes(E6) por la Consulta(C3). La fórmula ingresada
en la celda: F6 es =E6*C3 al pulsar ENTER y copiar la
fórmula hasta F11

• Observe como al ver las fórmulas la posición de la celda

Consulta(C3) al copiar ha ido cambiando(posición relativa)

[Link]
 • Ahora deberá modificar la primera fórmula así =E6*$C$3 • Observe resultado final correcto:
,con esta fórmula se está indicando que la celda C3 al
copiar no cambiará de posición(posición absoluta), copiar
ahora hasta F11.

• Al visualizar las fórmulas observe como la posición de la celda $C$3

se mantiene en todo el rango copiado

[Link]
Rango de celdas
• Si utiliza nombres, sus fórmulas serán
mucho más fáciles de entender y
mantener. Puede definir un nombre para
un rango de celdas, una función, una
constante o una tabla. Una vez que haya
adoptado la práctica de utilizar nombres
en su libro, podrá actualizar, auditar y
administrar esos nombres con facilidad.

Fuente: Definir y usar nombres en fórmulas - Soporte técnico

de Microsoft

[Link]
 1. Vaya a la hoja: Eventos Final
2. Primero ingresar una pequeña fórmula en la celda: G4 para calcular el
Costo, así:

3. Luego seleccione dicha celda (G4), vaya al cuadro de nombre e

ingrese: Costo y pulse ENTER.

[Link]
 4. Luego asigne el nombre: Ticket a la celda: G5 y Tcambio a la celda: i3
5. También puede crear nombres con los títulos de la primera fila o columna de un rango, ahora seleccione el rango: C8:G13.
6. Luego vaya a la ficha: Fórmulas y haga clic en el comando: Crear desde la selección del grupo: Nombres definidos

7. Ahora debe seleccionar: Fila superior para que la primera fila (8) de la selección sea el nombre de cada rango vertical, luego clic en
Aceptar

[Link]
 8. Para visualizar los nombres creados vaya a la ficha: Formulas y haga clic en el
comando: Administrador de nombres del grupo: Nombres definidos

9. Cerrar el Administrador de nombres.

Observe en la parte posterior los botones: Modificar, Eliminar desde donde puede
editar algún nombre como por ejemplo su mismo nombre o la referencia de la celda.

[Link]
Usando los nombres de rango en fórmulas
Calcular Ingresos: Es resultado de la multiplicación de Participantes x el valor del Ticket.

1. Haga clic en la celda: D9 e ingrese la siguiente fórmula: =Participantes*Ticket luego pulsar ENTER.

Observe el resultado: Si en caso no se copia la fórmula, copie de D9 a D13

[Link]
 Realizar los siguientes cálculos:
• Egresos: Es resultado de una multiplicación de Participantes x Costo, así =Participantes*Costo
• Utilidad: Es resultado las diferencia entre Ingresos y Egresos así =Ingresos-Egresos
• En USA: Es el resultado de la división de Utilidad con el Tipo de Cambio, así: =Utilidad/Tcambio

Hasta ahora el resultado debe ser así:

Para calcular el Total de la fila 14, seleccione el rango: C14:G14 y luego haga clic en el comando: Autosuma de la ficha
Inicio, grupo: Modificar o Edición.

[Link]
Luego aplicar los formatos de número contabilidad de S/. y $ . para que el resultado sea así:

[Link]
Creando nombres de Rangos con grandes volúmenes de información
Puede crear nombres de grandes cantidades de información a partir de una tabla, lista, cuadro, base de datos y posteriormente utilizar dichos
nombres para resumir.

1. Vaya a la hoja: Atención y seleccione el rango de datos: B14:J18

2. Luego vaya a la ficha Fórmulas y haga clic en el comando: Crear desde la selección.
3. En el cuadro: Crear nombres a partir de la selección solo seleccione: Fila superior y clic en Aceptar.

4. Luego ingresar al Administrador de nombres (CTRL. + F3) y seleccione el nombre:

No_Tratados y haga clic en Modificar…

[Link]
5. Modificar el nombre por: SinTratar clic en Aceptar y Cerrar todo.

[Link]
Usar nombres de Rango en Funciones
Una vez creados los nombres, los usaremos en fórmulas y funciones.

1. Vaya a la hoja: Resumen1 y seleccione la celda: C6

2. Ahora ingresar la siguiente fórmula: =Suma(Adultos), observe que Excel reconoce el nombre creado y muestra el siguiente resultado:

Otra forma de ingresar funciones con rango es usando el cuadro: Pegar Nombre:

3. En la celda: C7 ingrese =Suma(ahora, pulse la tecla de función: F3. Se muestran los nombres de rango definido. Seleccione el rango
llamado: Niños, pulse Enter para aceptar la fórmula.

Observe el resultado:

[Link]
 4. Realizar los otros totales en la hoja actual aplicando la función SUMA y los nombres
necesarios, el resultado debe ser así ->

5. Ahora vaya a la hoja: Resumen2 y para hallar los promedios use la función:
PROMEDIO.
6. Para hallar el Nro de folios recibidos use la función CONTAR y el rango: Folio
7. Para hallar el Primer Folio use la función MIN y el rango: Folio
8. Para hallar el Último Folio use la función MAX y el rango: Folio
9. Modificar a 1 decimal los resultados de los promedios, así:

[Link]
[Link]