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 1Abra 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° de cuotas) +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”.

Fórmulas con referencias de Hojas

Para obtener la sumatoria de las fotocopias realizadas en los meses de enero y febrero, realice lo siguiente:

o 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”

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?………………………………………………………………………………………………………………

Enero

Febrero

CONSOLIDADO

Total

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)

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”

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? ………………………………………..

Nombre de Rango

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

b) En C3 aparece =G9 + $D$5

c) En C3 aparece =G7 + D3

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