Microsoft Office Excel 2007
Fórmulas
Fórmulas básicas
Fórmulas aplicando tipos de celdas
Fórmulas utilizando nombres de rango
Fórmulas utilizando celdas Para desarrollar los siguientes casos active la hoja “Referencias” del archivo operadores.
Caso 1
Un cliente realiza 2 compras en el mismo establecimiento. Se pide calcular el importe total. Fórmula: ______________________ _________________________________ ___________
Caso 2
Un cliente compra un determinado producto en una tienda comercial. Se pide calcular el total a pagar conociendo el precio y la cantidad. Fórmula: ______________________ _________________________________ ___________
Caso 3
En la lotería El Gordo sale ganador el premio mayor, equivalente a 14500 soles, este monto será repartido entre 13 personas. Se pide calcular el monto que recibirá cada persona. Fórmula: ______________________ _________________________________ ___________
Caso 4
En el siguiente caso se tiene como dato el Subtotal de la factura 000024. Se pide calcular el impuesto y el total de la factura. Fórmula IGV: _______________________________ _______________________________ Fórmula Total: ______________________________ ______________________________
Venta de computadoras
Para desarrollar el siguiente ejercicio active la hoja “Venta” del archivo operadores.
Aplique las siguientes fórmulas:
Subtotal: Cantidad * 1450 soles. Fórmula: ___________________ ___________________________ ________
Copie la fórmula hacia abajo para completar la columna Subtotal.
Descuento: Equivale al 1.2% del precio por cada computadora Fórmula: ___________________ ___________________________ ________
Copie la fórmula hacia abajo para completar la columna Descuento.
IGV: Se calculará del siguiente modo: (subtotal – descuento) * IGV. Fórmula: ______________________ ___________________________ _____
Copie la fórmula hacia abajo para completar la columna IGV.
Neto: Se calculará del siguiente modo: subtotal + IGV – Descuento Fórmula: ___________________ ___________________________ ________
Copie la fórmula hacia abajo para completar la columna Neto.
Ganancias y Pérdidas Para desarrollar el siguiente ejercicio active la hoja “EEGGPP” del archivo
operadores.
Realice:
Cree las siguientes fórmulas: o
Utilidad bruta: Ventas netas + Costos de ventas
o
Gastos de operaciones : Sumatoria de los 3 tipos de gastos
o
Utilidad de operación: Utilidad bruta + Gastos de operaciones
o
Utilidad antes del impuesto a la renta: Utilidad de operación +
Ingresos Varios + Intereses Ganados + Gastos Extraordinarios o
Impuesto a la renta: Utilidad antes del impuesto a la renta * 19%
o
Utilidad de libre disposición: Utilidad antes del impuesto a la renta –
Impuesto a la renta.
Guarde los cambios en el libro.
Fórmulas aplicando tipos de celdas Una referencia a una celda podemos definirla como una llamada que hacemos al contenido de una celda estando situado en otra. Entre los tipos de celda que posee Excel se tienen:
Celdas relativas: (Ej. A15, CF55, Z100, etc.)
Celdas absolutas: La fila y columna lleva el símbolo del dólar. (Ej. $X$48, $T$40, $P$16, etc.)
Celdas Mixtas: Bien la fila o la columna lleva el símbolo del dólar (Ej. B$20, $B20, $R61, etc.)
Ejercicio 1 Abra el archivo “Celdas”, y realice los siguientes cálculos en la hoja producción:
FALTANTE = Producción Diaria – Producción
Solución: Para resolver este ejercicio nos preguntaremos ¿Qué dato se encuentra fuera de la tabla?
En nuestro caso, producción diaria esta fuera de la tabla, entonces esta celda llevará los símbolos de dólar.
Escriba la siguiente fórmula en la celda D6:
=$C$3 – C6
% PRODUCIDO = Producción / Producción Diaria
Escriba la siguiente fórmula en la celda E6: = C6 / $C$3
PAGO = 1.5 soles por cada polo producido
Fórmula: ............................................. ................................................................... ............................................ .......................................... ....................
TOTAL = Pago + Refrigerio
Escriba la siguiente fórmula en la celda G6: = F6 + $F$3
Celda que esta fuera de la tabla
Ejercicio 2 En la hoja Merca-Lider del archivo “Celdas” realice los siguientes cálculos:
Total : Cantidad * Precio
Utilidad : Total * utilidad ( Celda B6)
Descuento : Total * Descuento ( Celda E7)
IGV : (Total + Utilidad – Descuento) * IGV
Neto: Total + Utilidad + IGV - Descuento
Actualice el archivo.
Ejercicio 3 En la hoja Comercial del archivo “Celdas” realice los siguientes cálculos:
Comisión: Ventas * Porcentaje de comisión
Total : Comisión + Básico
Actualice el archivo.
Ejercicio 4 En la hoja MegaPlus del archivo “Celdas” realice los siguientes cálculos:
Cuota Inicial: Precio * Porcentaje de Pago
Cuota Mensual: (Precio – Cuota Inicial) * (1 + Interés) / N° de Cuotas
Monto Total: (Cuota Mensual * N° N° de cuotas) +Cuota +Cuota Inicial
Actualice el archivo.
Trabajando con Hojas 1. Abra el archivo FAXMA. 2. Obtén un consolidado de las cantidades vendidas de los meses de enero y febrero. Muestre el consolidado en la hoja “ Total”.
Enero Total Febrero
CONSOLIDADO
Fórmulas con referencias de Hojas
Para obtener la sumatoria de las fotocopias realizadas en los meses de enero y febrero, realice lo siguiente: Active la hoja “Total” o Seleccione la celda B5 y escriba: =Enero!C5 + Febrero!C5 o Copie la fórmula para completar la columna “ Fotocopias” o
Practique
¿Cuál es la fórmula para obtener el total de impresiones? ………………………………………………………………………………………………………………
¿Cuál es la fórmula para obtener el total de escaneados? ………………………………………………………………………………………………………………
Fórmulas utilizando nombres de rango 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. Ejercicio 1: En este ejercicio Microsoft Office Excel creara nombres de rango utilizando como referencia los encabezados de tabla.
Abra el archivo System-Start:
Realice los siguientes cálculos en la hoja Planilla:
Cree nombres de rangos con los rótulos de tabla. Seleccione la tabla (F7:J19), active la ficha Fórmulas. Haz clic sobre la opción: “Crear desde la selección ”. En la ventana mostrada sólo debe estar activa la opción: Fila Superior acepte la ventana.
Utilice nombres de rangos en las fórmulas:
La comisión se calculará multiplicando la cantidad de autos vendidos por S/. 35 Fórmula de comisión: =autos*35
El descuento será el 1.2% del básico Fórmula del descuento: =basico*1.2%
Practique:
¿Cuál es la fórmula para calcular el neto? Utilice nombres de rango. …………………………………………………………………………………
Hoja “Resumen” Active la hoja “Resumen”
En la celda C4 deseamos calcular el monto total del básico. Para resolver este ejercicio utilicemos la siguiente fórmula:
=suma(basico) Nombre de Rango Nota: La función Suma determina la sumatoria de un rango de celdas)
Utilizando el criterio anterior, resuelva los siguientes ejercicios:
¿Cuál es la fórmula para calcular el total de autos vendidos? ……………………………………………………………………………………..……
¿Cuál es la fórmula para calcular el monto total de comisiones? ……………………………………………………………………………………..……
¿Cuál es la fórmula para calcular el monto total de descuento? ……………………………………………………………………………………..……
¿Cuál es la fórmula para calcular el monto total del neto? ……………………………………………………………………………………..……
Ejercicio 2: En este ejercicio crearemos nombres de rango utilizando el cuadro de nombres.
Abra el archivo “Productos “Productos””
Asignando nombres de rango:
Seleccione el rango: D5:D14, haga clic en el cuadro de nombres Escriba Precio y luego presione ENTER Luego de haber creado el nombre, escribamos la siguiente formula en la celda D15: =suma(precio).
Asigne los siguientes nombres: Rango: E5:E14 Descuento Rango: F5:F14 Total
¿Cuál es la fórmula en la celda E15? ……………………………………….. ……………………………………….. ¿Cuál es la fórmula en la celda F15? ……………………………………….. ………………………………………..
Ejercicio Propuesto 1 1. Complete los paréntesis con R (celda relativa), A (celda absoluta) o M (celda mixta) A$46
(
)
Q49
(
)
C16
(
)
$AH$4814 (
)
$T$21
(
)
B$4
(
)
U15
(
)
$W$46
(
)
AB$49 (
)
H6
(
)
$R$500 (
)
X$800
(
)
2. Si en la celda C2 existe la fórmula =G8+$D$4 y se copia a C3... ¿Cuál de las siguientes opciones es la correcta?
a) En C3 aparece =G9 + $D4
c) En C3 aparece =G7 + D3
b) En C3 aparece =G9 + $D$5
d) En C3 aparece =G9 + $D$4
Ejercicio Propuesto 2: Celdas Absolutas y Relativas
Cree y complete la siguiente tabla utilizando fórmulas:
Ejercicio Propuesto 3 Abra el archivo Registro de Compras , el libro registra las compras diarias que se realizan a los proveedores. Se nos pide realizar los siguientes cálculos:
Importe Bruto: Cantidad * precio unitario
Importe del descuento: Importe bruto * porcentaje de descuento
Importe de Gastos Vinculados (Gastos de flete y gastos de envió):
Importe bruto * porcentaje gastos vinculados
Valor de Compra: Importe bruto – descuento + Gastos vinculados
Precio de Compra: Valor de compra + IGV
Forma de Pago: Es el calculo de la forma de pago asignado para la compra:
Contado: Precio de compra * porcentaje al contado.
Factura: Precio de compra * porcentaje de factura.
Letra: Precio de compra * porcentaje de letra.
Precio Unitario: Valor de compra / cantidad
Realice:
Guarde los cambios en el documento.
Ejercicio Propuesto 4 Abra el archivo Banco Mundial, y considere las siguientes especificaciones para completar las tablas:
Hoja “Afiliaciones” Aplique las siguientes fórmulas:
Total de clientes atendidos: Es la suma de los clientes atendidos en los 5 meses. Total de clientes afiliados: Es la suma de los clientes afiliados en los 5 meses. El % de clientes afiliados se calculará dividiendo el total de afiliados entre el total de atendidos.
Hoja “Comisiones” Aplique las siguientes fórmulas:
Las comisiones de cada mes se calculará multiplicado 7.5 soles por cada cliente afiliado. Halle el total de comisiones de los 5 meses.
Hoja “Planilla” Aplique las siguientes fórmulas:
Básico: Será 800 soles para cada agente.
Comisiones: Es el importe calculado en la hoja camisones (Mes de Mayo).
Movilidad: Será 50 soles para cada agente.
Descuento: Sera 20 soles por falta.
Total: Sueldo + Comisiones + Movilidad - Descuento