Ofimática avanzada
PROYECTO DE
EXCEL
Jóvenes, encontrarán en el presente libro varias hojas que contienen la explicación y sintaxis de
las principales fórmulas vista en la clase de excel.
Encontrarán en cada hoja una parte teórica y abajo una parte para que practiquen cada fórmula
con base en la explicación dada.
zada
DE
explicación y sintaxis de
ractiquen cada fórmula
FORMATO GENERA
TEORIA:
Inclinar el texto
Alineación en el
sentido horizontal de Alineación en el sentido
la celda vertical de la celda
PRÁCTICA:
Aplique formato a la siguiente tabla de acuerdo a las especificaciones siguientes:
Tipo de letra arial 11.
Hacer lineas gruesas en los
encabezados y finas en el resto
de la tabla.
Ajustar el tamaño de las celdas
CUADRO DE VENTAS
NOMBRE PAIS DE ORIGEN VENTAS
Javier Honduras L 65,454.00
Ana Guatemala L 78,541.00
Lucia Mexico L 55,465.00
Angel Honduras L 56,647.00
Eugenio Guatemala L 56,598.00
Marcela Honduras L 12,365.00
GENERAL
Indica el estilo de la
celda seleccionada y
permite cambiarlo.
Disminuir
decimales
Estilo
Formato de Estilo millares
número. porcentual
Insertar título, centrado y
combinado
Centrar encabezados, negri
arial 10, mayúsculas,
sonbreado gris oscuro..
Totales en negrita, sombrae
gris claro.
Aplicar formato de moneda
RO DE VENTAS
SUELDO BASE TOTAL
L 5,000.00 L. 70,454.00
L 5,000.00 L. 83,541.00
L 5,000.00 L. 60,465.00
L 5,000.00 L. 61,647.00
L 5,000.00 L. 61,598.00
L 5,000.00 L. 17,365.00
La viñeta FUENTE y PORTAPAPELES funciona igual que en Word
Insertar título, centrado y
combinado
Centrar encabezados, negrita,
arial 10, mayúsculas,
sonbreado gris oscuro..
Totales en negrita, sombraeado
gris claro.
Aplicar formato de moneda.
ual que en Word
Inmovilice esta fil
En la pestaña vista, viñeta ventana
Abre una nueva ventana
Organiza las ventanas
abiertas
Inmoviliza seccio
Cree una división horizontal de la hoja en este punto
sta fila
Divide la ventana en el lugar donde
estemos ubicados
Inmoviliza secciones de la hoja Oculta/ muestra la hoja acti
en este punto.
onde
Maneja la forma de mostrar y
manipular las diferentes ventanas
abiertas
ulta/ muestra la hoja activa
SERIES DE RELLENO
TEORIA:
Muchas de las hojas que se crean con Excel requieren la
entrada de una serie de fechas o números secuenciales.
Por ejemplo, una hoja puede requerir que usted le ponga
titulo a las columnas con los 12 meses, desde Enero hasta
Diciembre, o numerar las filas de 1 a 100.
La característica de Auto llenado de Excel hace que esta
repetitiva tarea se vuelva corta.
Para crear series de Auto Relleno:
a) Ingresar los primeros dos b) Ingresar el primer número
números y después arrastra y después arrastrar y clic en
(indicando el patrón). “opciones de auto relleno”
Patrón: “de dos
en dos”
PRÁCTICA:
Crear las siguientes listas con sere de relleno:
Meses del año: Dias de la semana: Números de "dos en dos" hasta
Enero Lunes
Febrero Martes
Marzo Miércoles
Abril Jueves
Mayo Viernes
Junio Sábado
Julio Domingo
Agosto
Septiembre
Octubre
Noviembre
Diciembre
LENO
Meses del año
Serie Números
Fecha
Hora
Días de la semana
r el primer número
arrastrar y clic en
de auto relleno”
Rellena
correlativamente
úmeros de "dos en dos" hasta 20: Números correlativos del 1 al 10:
2 1
4 2
6 3
8 4
10 5
12 6
14 7
16 8
18 9
20 10
FÓRMULAS BÁSICAS
TEORIA:
Una fórmula es un conjunto de instrucciones matemáticas que se pueden u
Excel empiezan con un signo de (=).
Símbolo de Igual
Toda fórmula en
Excel inicia con el
signo:
=A1+A2
Referencias
Recuerden que pueden y deben utilizar paréntesis para asociar las o
PRACTICA:
Realice en las celdas en verde las operaciones indicadas.
Operación Campo1
Suma 20
Resta 40
Multiplicar 25
Dividir 25
Operación Campo 1
Sumar campo 1 + campo2, dividir 20
el total entre el campo 3
Multiplicar el campo 1 por el 40
campo 2 y al resultado sumarle el
campo 3
Dividir el campo 1 entre el campo 100
2 y al resultado restar el campo 3
Sumar el campo 2 y el campo 3 y el 2
resultado multiplicarlo por el
campo 1.
ÁSICAS
cas que se pueden usar para realizar cálculos en las hojas de trabajo de Excel. Todas l
Signo de suma
+A2
Referencias
para asociar las operaciones según los requerimientos
Campo2 Resultado
50 70
15 25
3 75
5 5
Campo2 Campo 3 Resultado
80 20
5
2 15
95
2 25
25
50 50
200
o de Excel. Todas las fórmulas en
CONCEPTOS BÁSICO
TEORIA:
Subtotal: es un resultado parcial generalmente de una sumatoria, e
falta sumar un valor..
Total: es la sumatoria ultima realizada en un proceso.
PRÁCTICA:
Calcule los siguiente:
PRODUCTO CANTIDAD PRECIO TOTAL
cuaderno 8 L. 200.00 L. 1,600.00
lapices 3 L. 350.00 L. 1,050.00
borrador 12 L. 400.00 L. 4,800.00
regla 15 L. 280.00 L. 4,200.00
zacapuntas 9 L. 260.00 L. 2,340.00
cartuchera 11 L. 150.00 L. 1,650.00
marcadores 20 L. 80.00 L. 1,600.00
lapiz carbon 2 L. 270.00 L. 540.00
minas 15 L. 120.00 L. 1,800.00
SUBTOTAL L. 19,580.00
10 % DESCUENTO L. 1,958.00
SUBTOTAL L. 17,622.00
15% IMPUESTO L. 2,643.30
TOTAL L. 20,265.30
BÁSICOS
te de una sumatoria, es decir, Porcentaje: Núm
una parte respecto a
Un porcentaje siempr
roceso.
Descuento: cantidad
precio original de un
rcentaje: Número o cantidad que representa la proporcionalidad de
parte respecto a un total que se considera dividido en cien unidades.
porcentaje siempre debe ir multiplicado por un valor.
El ISV corresponde
al 15% del subtotal
scuento: cantidad que restamos del
ecio original de un producto
ad de
ades.
REFERENCIAS
TEORIA:
1.- Referencia relativa a una celda: es aquella referencia que, al co
pegarla en otra ubicación, se ajusta automáticamente para
Por ejemplo, si escribimos la operación = A1*5 en la celda B1 y cop
fórmulas que se obtienen se ajustan tal y como podemos ver en la s
2.- Referencia absoluta a una celda: es aquella referencia que, al
en otra ubicación, no se ajusta sino que hace referenci
Para que una referencia a una celda sea absoluta, se deben introdu
letra y antes del número de una referencia relativa.
Es decir, la referencia absoluta a la celda A1, sería: $A$1
Así conseguiremos que al copiar y pegar siempre mantendremos la
PRACTICA:
Precio Cantidad Total
89 5 445
56 12 672
22 5 110
89 6 534
454 21 9534
874 55 48070
5 3 15
54 33 1782
54 55 2970
54 78 4212
5 22 110
Impuesto: 15%
Precio Cantidad Subtotal Impuesto Total
89 5 445 66.75 511.75
56 12 672 100.8 772.8
22 5 110 16.5 126.5
89 6 534 80.1 614.1
454 21 9534 1430.1 10964.1
874 55 48070 7210.5 55280.5
5 3 15 2.25 17.25
54 33 1782 267.3 2049.3
54 55 2970 445.5 3415.5
54 78 4212 631.8 4843.8
5 22 110 16.5 126.5
rencia que, al copiar la celda que la contiene y
camente para hacer referencia a otra celda.
celda B1 y copiamos la celda hacia abajo, las
emos ver en la siguiente imagen:
erencia que, al copiar la celda que la contiene y pegarla
ce referencia siempre a la misma celda.
se deben introducir los símbolos del dólar $ antes de la
$A$1
mantendremos la referencia a la misma celda:
FORMATO CONDICIO
TEORIA:
Para aplicar FORMATO CONDICIONAL de una celda:
La reg
maner
los 10
rango
Desp
celda
valor
del v
los v
Nos pe
valores
valor m
un rang
depend
Utiliza conjuntos de iconos los cuales son estos d
aplicados de acuerdo a la cantidad de
rangos definidos y que pueden ser tres,
cuatro y hasta cinco rangos.
El formato condicional es una herramienta útil para id
una hoja de cálculo.
PRÁCTICA:
Aplique formato condicional de acuerdo a lo que se solicita en cada caso:
DESCRIPCIÓN VALOR DE LA COMPRA IMPUESTO TOTAL
Compra 1 L. 2,324.00 L. 348.60 L. 2,672.60
Compra 2 L. 1,501.00 L. 225.15 L. 1,726.15
Aplique formato de ícon
Compra 3 L. 450.00 L. 67.50 L. 517.50 la Compra" , indicando e
Compra 4 L. 2,122.00 L. 318.30 L. 2,440.30 mayores a L 1,500.00, a
a L. 500.00 y el resto en
Compra 5 L. 656.00 L. 98.40 L. 754.40
Compra 6 L. 2,121.00 L. 318.15 L. 2,439.15
Compra 7 L. 233.00 L. 34.95 L. 267.95
Compra 8 L. 1,212.00 L. 181.80 L. 1,393.80
Compra 9 L. 212.00 L. 31.80 L. 243.80
Compra 10 L. 555.00 L. 83.25 L. 638.25
NDICIONAL
ONAL de una celda:
Resalta las celdas que cumple con una
condición especifica.
La regla 10 superiores permite resaltar de
manera inmediata las celdas que contengan
los 10 importes con mayor valor dentro del
rango de celdas.
Despliega una barra horizontal dentro de una
celda lo cual facilita la comparación de
valores. La longitud de cada barra dependerá
del valor de la celda y será relativa al resto de
los valores del rango.
Nos permite identificar visualmente los
valores numéricos que se aproximan tanto al
valor máximo como al valor mínimo dentro de
un rango. La tonalidad de cada celda
dependerá de su cercanía con cualquiera de
estos dos extremos.
rramienta útil para identificar patrones o tendencias en
Aplique formato de íconos a la columna "Valor de
la Compra" , indicando en color verde los valores
mayores a L 1,500.00, amarillo los valores mayores
a L. 500.00 y el resto en rojo.
AUTOSUMA
TEORIA:
Con Autosuma pueden
sumar rápidamente una
columna, fila o números.
Seleccionen una celda
situada junto a los números
que quiera sumar, haga clic
en Autosuma en la pestaña
Inicio, presione Entrar .
Cuando hagan clic en
Autosuma, Excel
especificará
automáticamente una
fórmula (que usa la función
SUMA) para sumar los
números.
PRACTICA:
Sume en la celda en verde, los siguientes valores utilizando la funciòn Autosuma:
4545 4241 45 545
545 21
454 21 577 886
21
54 12 4444 212
121 121
54 21
542 22
6315 4480
65211 878 66679
66 565 2094
545 47878 53079
CONTAR
TEORIA:
Usen la función CONTAR para obtener la cantidad de entradas en u
matriz de números. Por ejemplo, puede escribir la siguiente fórmula
A1:A20: =CONTAR(A1:A20). En este ejemplo, si cinco de las celdas d
resultado es 5.
Existen las siguientes variantes de la funciòn CONTAR:
=Contar Cuenta celdas con números.
Sintaxis
=ContarA Cuenta celdas que contengan da
Sintaxis
Cuenta celdas que NO conten
Sintaxis
Cuenta celdas que cumplan un c
=Contar.SI
Sintaxis
Sintaxis
PRACTICA:
Con base en la siguiente tabla, responda lo siguiente utilizando las variantes de la funciòn CONTAR
NOMBRE PAIS PARCIAL 1 PARCIAL 2 PARCIAL 3
Javier El Salvador 90 85 60
Ana Nicaragua 85 55 55
Wilmer 89 90 56
Astrid Nicaragua 87 78 100
Carlos 60 99 61
Lucia Honduras 60 60 88
Angel Honduras 60 90 77
Eugenio 88 100 55
Marcela El Salvador 78 88 99
TOTAL DE ALUMNOS 9
TOTAL DE ALUMNOS DE HONDURAS 2
TOTAL DE ALUMNOS DE EL SALVADOR 2
TOTAL DE ALUMNOS DE NICARAGUA 2
ALUMNOS SIN PAIS DE ORIGEN 3
de entradas en un campo de número de un rango o
siguiente fórmula para contar los números en el rango
nco de las celdas del rango contienen números, el
TAR:
con números.
que contengan datos
as que NO contengan datos
que cumplan un criterio
MIN/MAX
TEORIA:
La función MIN nos devuelve el valor mínimo de una list
función podemos ingresar directamente los números qu
que contiene los valores numéricos.
La función MAX nos devuelve el valor máximo de una lista
la función podemos ingresar directamente los números q
rango que contiene los valores numéricos.
PRÁCTICA:
Calcule los valores mínimos y máximos en las siguientes listas}
45 4545
12 4545
4545 233
212 3455
4545 878
421 21
545 884
21 5454
45 212
54 54
21 555
54 21
2
Valor mínimo 12 Valor mínimo 2
Valor máximo 4545 Valor máximo 5454
de una lista de valores omitiendo los valores lógicos y el texto.
números que deseamos evaluar o podemos indicar una referenc
de una lista de valores omitiendo los valores lógicos y el texto. C
números que deseamos evaluar o podemos indicar una referen
icos y el texto. Como argumentos de la
ar una referencia a una celda o el rango
cos y el texto. Como argumentos de
car una referencia a una celda o el
PROMEDIO
TEORIA:
La funciòn PROMEDIO, devuelve el promedio (media aritmética) d
Por ejemplo, si el intervalo A1:A20 contiene números, la fórmula =P
el promedio de dichos números.
Al igual que el resto de las formulas, hay dos formas de insertarla e
1)
2)=Promedio
PRÁCTICA:
PRÁCTICA:
Calcule en la celda en verde el promedio de los tres parciales de cada estudiante
NOMBRE PARCIAL 1 PARCIAL 2 PARCIAL 3 PROMEDIO
Javier 88 86 99 91
Ana 79 88 85 84
Lucia 70 73 81 75
Angel 87 73 79 80
Eugenio 96 88 91 92
Marcela 73 82 87 81
media aritmética) de los argumentos.
eros, la fórmula =PROMEDIO(A1:A20) devuelve
mas de insertarla en la celda deseada:
Promedio
SI
TEORIA:
Use la función SI, una de las funciones lógicas de excel, para dev
verdadera y otro si es falsa.
Por ejemplo: =SI(A2>B2,"Presupuesto excedido";"Correcto")
SINTAXIS:
PRACTICA:
Calcule loa valores indicados utilizando la función SI
NOMBRE PROMEDIO OBSERVACIÓN
Javier 89 APROBO
Ana 57 REPROBO
Lucia 99 APROBO
Angel 45 REPROBO
Eugenio 78 APROBO
Marcela 89 APROBO
En la casilla OBSERVACIÓN colocar:
APROBÓ MAYO QUE 60
REPROBÓ MENOR QUE 60
de excel, para devolver un valor si una condición es
"Correcto")
SI ANIDADA
TEORIA:
La siguiente imagen muestra el funcionamiento de la f
segunda acción, como en una función SI normal, la pr
funciones puedan ejecutar un máximo de tres accione
Función SI Anidada:
SINTAXIS: =SI(Condición 1, verdadero, Si( Co
PRACTICA:
Realice el cálculo del descuento según la tabla inferior. Aplique formato de moneda donde corresponda.
DESCRIPCIÓN VALOR DE LA COMPRA DESCUENTO TOTAL
Compra 1 L. 120.00 0 L. 120.00
Compra 2 L. 325.00 22.75 L. 302.25
Compra 3 L. 556.00 55.6 L. 500.40
Compra 4 L. 650.00 65 L. 585.00
Compra 5 L. 89.00 0 L. 89.00
Compra 6 L. 720.00 72 L. 648.00
Compra 7 L. 155.00 0 L. 155.00
Compra 8 L. 25.00 0 L. 25.00
Compra 9 L. 600.00 60 L. 540.00
Compra 10 L. 550.00 55 L. 495.00
El descuento aplicará de la siguiente forma:
Para compras menores de L. 200 0%
Para compras menores de L. 300 5%
Para compras menores qu L. 500 7%
Para compras mayores qu L. 500 10%
miento de la función SI anidada. Observen que la clave es que, e
normal, la primera función SI incluye una segunda función SI de
e tres acciones (EN DOS FUNCIONES ANIDADAS).
dadero, Si( Condición2, verdadero, Si( Condición 3, verd
clave es que, en lugar de ejecutar una
da función SI de manera que entre ambas
dición 3, verdadero, falso))
BUSCAR V
TEORIA:
Sintaxis:
PRÁCTICA:
En las celdas en color naranja deben mostrarse los datos indicados al ingresar el nombre el la celda verde.
Nombre Silvia
Telefono 126544
Foma de pago Contado
Total factura 655465
Nombre Apellido Teléfono Forma de pago Total factura
Juan Gálvez 122345 Contado L. 85,621.00
Maria Cruz 231245 Crédito L. 54,799.00
José Mercadal 325489 Contado L. 56,974.00
Sofia Bueso 124598 Contado L. 52,365.00
Daniela Alfaro 984565 Contado L. 598,475.00
Gissel Perdomo 329845 Crédito L. 123,655.00
Camila Corea 789845 Crédito L. 64,545.00
Santiago Martínez 458978 Contado L. 1,212.00
Silvia Gonzáles 126544 Contado L. 655,465.00
José Casco 126587 Crédito L. 12,212.00
Alexis Solís 987845 Crédito L. 323,232.00
Susan Ramírez 984513 Contado L. 4,545.00
VALIDACIÓN DE DAT
TEORIA:
Pueden usar la validación de datos para restringir el tipo de datos o lo
validación de datos es crear una lista desplegable.
Para crear una lista desplegable:
1 Seleccione el rango que desea validar.
Para validar por valores permitidos:
Para validar por valores permitidos:
1
Permitir solamente
números enteros menores
que 50
PRÁCTICA:
1.- En la siguiente tabla valide por medio de lista los valores del nombre y determine mediante la función BuscarV el tipo de pa
Nombre Forma de pago
Susan Contado
Alexis Crédito
José Contado
Silvia Contado
Santiago Contado
Camila Crédito
Gissel Crédito
Daniela Contado
Sofia Contado
José Contado
Maria Crédito
Juan Contado
2.-En la siguiente tabla valide la columna cantidad de manera que los datos permitidos esten comprendidos entre 5 y 60, intro
Artículo Cantidad
Art 1 8
Art 2 55
Art 3 10
Art 4 25
Art 5 30
Art 6 50
Art 7 15
Art 8 9
Art 9 12
Art 10 56
Art 11 32
Art 12 11
E DATOS
o de datos o los valores que los usuarios escriben en una celda. Uno de los uso
2 Pestaña Datos, herramientas de datos:. 3 Seleccionar
decir, los va
2
ión BuscarV el tipo de pago:
Lista para validación
Nombre Apellido Forma de pago
Juan Gálvez Contado
Maria Cruz Crédito
José Mercadal Contado
Sofia Bueso Contado
Daniela Alfaro Contado
Gissel Perdomo Crédito
Camila Corea Crédito
Santiago Martínez Contado
Silvia Gonzáles Contado
José Casco Crédito
Alexis Solís Crédito
Susan Ramírez Contado
ndidos entre 5 y 60, introduzca un mensaje de entrada y uno de error:
Uno de los usos más comunes de la
Seleccionar "Lista" y especificar el rago de la lista, es
decir, los valores que serán premitidos.
SI.ERROR
TEORIA:
Pueden usar la función SI.ERROR para interceptar y controlar errores en
especifica si una fórmula lo evalúa como un error; en caso contrario, devu
Resultado original de la fórmula #N/A
PRÁCTICA:
Valide todas las fórmulas en la siguiente tabla:
CODIGO DESCRIPCIÓN CANTIDAD P.U. TOTAL
301 Art 1 60 45 L 2,700.00
302 Art 2 5 65 L 325.00
303 Art 3 10 123 L 1,230.00
304 Art 4 25 54 L 1,350.00
305 Art 5 30 98 L 2,940.00
306 Art 6 50 75 L 3,750.00
307 Art 7 15 65 L 975.00
308 Art 8 8 12 L 96.00
309 Art 9 12 65 L 780.00
310 Art 10 56 65 L 3,640.00
311 Art 11 32 65 L 2,080.00
312 Art 12 11 23 L 253.00
controlar errores en una fórmula. SI.ERROR devuelve un valor que se
aso contrario, devuelve el resultado de la fórmula.
SINTAXIS:
Fórmula original, cualquier Valor si esa fórmula da
fórmula. un error.
CODIGO DESCRIPCION CANTIDAD P.U TOTAL
301 Art 1 60 L. 45.00 L. 2,700.00
302 Art 2 5 L. 65.00 L. 325.00
303 Art 3 10 L. 123.00 L. 1,230.00
304 Art 4 25 L. 54.00 L. 1,350.00
305 Art 5 30 L. 98.00 L. 2,940.00
306 Art 6 50 L. 75.00 L. 3,750.00
307 Art 7 15 L. 65.00 L. 975.00
308 Art 8 8 L. 12.00 L. 96.00
309 Art 9 12 L. 65.00 L. 780.00
310 Art 10 56 L. 65.00 L. 3,640.00
311 Art 11 32 L. 65.00 L. 2,080.00
312 Art 12 11 L. 23.00 L. 253.00
si esa fórmula da
rror.
FILTROS:
TEORIA:
Los filtros en Excel facilitan la búsqueda de un subconjunto de dato
Este subconjunto de datos o rango filtrado muestra las filas que cum
¿Cómo realizamos un filtro?
Filtro 1:
Por ejemplo, asumimos que en nuestra tabla queremos ver SOLAME
registros de los alumnos con promedio mayor que 90
Determinamos los campos involucrados en el filtro que se nos s
Para garantizar que el proceso funcione, copiamos el encabeza
involucrado en una celda aparte.
Establecemos bajo este encabezado el criterio solicitado, en nue
PRÁCTICA:
Con base en la siguiente tabla obtenga lo siguientes filtros:
1.- Ventas mayores que L 100,00.00
2.- Ventas al crédito
3.- Ventas menores que L 80,000.00
Nombre Apellido Teléfono Forma de pago Total factura
Alexis Solís 987845 Crédito L. 323,232.00
Camila Corea 789845 Crédito L. 64,545.00
Gissel Perdomo 329845 Crédito L. 123,655.00
José Casco 126587 Crédito L. 12,212.00
Maria Cruz 231245 Crédito L. 54,799.00
subconjunto de datos dentro de un rango para poder trabajar con el mismo.
tra las filas que cumplen el criterio que se especifique para una columna.
n filtro?
eremos ver SOLAMENTE los
que 90
el filtro que se nos solicita, en este caso: PROMEDIO.
piamos el encabezado correspondiente al campo
erio solicitado, en nuestro caso >90.
ar con el mismo.
una columna.
