1

Diplomado de Excel
Objetivos:
 Definir Microsoft Excel
 Describir las partes del Entorno de Microsoft Excel
 Aplicar formulas
1. Microsoft Excel
Es una potente herramienta
que puedes usar para crear y
aplicar formato a hojas de
cálculo, y para analizar
y compartir información para
tomar decisiones mejor fundadas. La nueva interfaz orientada a obtener
resultados, la visualización de datos enriquecida y las vistas de tabla dinámica
permiten crear, de un modo más sencillo, gráficos de aspecto profesional y fácil
uso.
Microsoft Excel reúne 3 conceptos básicos como Hoja de Cálculo, Base de
Datos y Gráficos.
2. Ingresar a Excel
A. Presionar la combinación de teclas <> + <R>
B. Se muestra la
siguiente ventana
en la cual se
debe ingresar el
comando excel
C. Presionar la tecla
<ENTER>

También se puede acceder al programa usando el acceso directo que puede

estar sobre su Escritorio de Windows, solo se debe hacer doble clic sobre el
icono

Marco Aurelio Porro Chulli

 2

3. Entorno de Trabajo de Microsoft Excel

Marco Aurelio Porro Chulli

 3

4. Operaciones con el Teclado en Excel

5. Tipos de Datos que maneja Excel

A. Números: Para introducir números puede incluir
los caracteres 0, 1, 2, 3, 4, 5, 6, 7, 8,9 y los signos
especiales + - ( ) / %.
Los signos (+) delante de los números se ignoran,
y para escribir un número negativo éste tiene que
ir precedido por el signo (-).
Al escribir un número entre paréntesis, Excel lo interpreta como un número
negativo, lo cual es típico en contabilidad.
El carácter E o e es interpretado como notación científica. Por ejemplo, 3E5
equivale a 300000 (3 por 10 elevado a 5).
Se pueden incluir los puntos de miles en los números introducidos como
constantes.
Cuando un número tiene una sola coma se trata como una coma decimal.
Si al finalizar un número se escribe Pts, Excel asigna formato Moneda al número
y así se verá en la celda, pero en la barra de fórmulas desaparecerá dicho
símbolo.

Marco Aurelio Porro Chulli

 4
Si introducimos el símbolo % al final de un número, Excel lo considera como
símbolo de porcentaje.
Si introduces fracciones tales como 1/4, 6/89, debes escribir primero un cero
para que no se confundan con números de fecha.
Si un número no cabe en su celda como primera medida se pasa
automáticamente a anotación científica.
Por defecto los números aparecen alineados a la derecha en la celda.

B. Fecha y Hora: Para introducir una fecha u hora, no tienes más que escribirla de
la forma en que deseas que aparezca.
Al igual que los números (ya que realmente lo
son), las fechas y las horas también aparecen
alineados a la derecha en la celda.
Cuando introduzcas una fecha comprendida
entre los años 1929 y 2029, sólo será necesario
introducir los dos últimos dígitos del año, sin embargo para aquellas fechas que
no estén comprendidas entre dicho rango, necesariamente deberemos introducir
el año completo.
Ejemplos:
1/12/99 1-12-99 2:30 PM 14:30 1/12/99 14:30
C. Texto
Para introducir texto como una constante,
selecciona una celda y escribe el texto.
El texto puede contener letras, dígitos y otros
caracteres especiales que se puedan
reproducir en la impresora. Una celda puede
contener hasta 16.000 caracteres de texto.
Si un texto no cabe en la celda puedes utilizar todas las adyacentes que están
en blanco a su derecha para visualizarlo, no obstante el texto se almacena
únicamente en la primera celda.
El texto aparece, por defecto, alineado a la izquierda en la celda.
6. Ingresar Valores en Celda
A. Digitar el dato y luego presionar la tecla <ENTER> o también las Teclas de
Dirección ( Teclas de Desplazamiento)

Marco Aurelio Porro Chulli

 5

7. Modificar Datos en una Celda

A. Seleccionar la celda adecuada.
B. Activar la Barra de Fórmulas pulsando la tecla de función <F2> o ir
directamente a la barra de fórmulas haciendo clic en la parte del dato a
modificar.
C. Modificar la información.
D. Luego de modificado el dato presionar la tecla <ENTER>
8. Guardar un Libro de Excel
A. Presionar la combinación de teclas <CONTROL> + <G> aparece:

B. Ingresar el Nombre de Archivo y luego hacer clic en el Botón Guardar

Importante
Otra manera de guardar el libro es usando el icono de Guardar
dicho icono se encuentra en la Barra de Herramienta Rápida

Marco Aurelio Porro Chulli

 6

9. Caso Práctico
La empresa Comercial MK-NO necesita realizar su listado de artículos que
comercializa le requiere a usted para poder administrarlo con Excel teniendo
que ingresar los datos descritos en la figura:

Marco Aurelio Porro Chulli

 7

Luego pide calcular lo siguiente:

A. Ganancia que es del 20% del Precio de Compra.
B. Precio de Venta que resulta de la suma del Precio de Compra y
la Ganancia.
C. Capital Neto que se obtiene de la multiplicación del Precio de
Compra y el Stock.
D. Capital Bruto que se obtiene de la multiplicación del Precio de
Venta y el Stock.
Solución:
A. GANANCIA Celda D7 =C7*25%

B. PRECIO DE VENTA Celda E7 =C7+D7

=C7*F7
C. CAPITAL NETO Celda G7

=E7*F7
D. CAPITAL BRUTO Celda H7
Para ingresar la FECHA y la HORA se realiza:
E. Fecha Celda H2 Presionar <CONTROL> + <,>

F. Hora Celda H3 Presionar <CONTROL><SHIFT> + <:>

Marco Aurelio Porro Chulli

 La empresa Comercial MK-NO necesita obtener el pago de sueldos de sus empleados para cual usa el formato que tiene en la figura. Luego de ingresar los
datos se pide calcular las siguientes operaciones:
Descuentos
 AFP que es 12% del Salario
 Seguro que es 8% del Salario
 Total Descuentos es la suma de AFP y el Seguro
Bonificaciones
 Aguinaldo es del 50% del Salario
Ejercicio Propuesto

 Especial que es 20% del Salario

 Total Bonificaciones es la suma de Aguinaldo y el Especial
Neto Pagar resulta del Salario menos el Total Descuentos mas el Total Bonificaciones

Marco Aurelio Porro Chulli

8
 9

Diplomado de Excel
Objetivos:
 Abrir un libro de Excel
 Conocer y aplicar Referencia de Celda Relativa, Absoluta y Mixta
1. Abrir un Libro de Excel existente
A. Presionar la combinación de teclas <CONTROL> + <A> aparece:

B. Seleccionar el nombre del libro que se desea abrir y luego hacer clic en el Botón
Abrir
Importante
Otra manera de abrir un libro existente es usando el icono de
Abrir dicho icono se encuentra en la Barra de Herramienta Rápida

2. Fórmulas
Es una secuencia formada y ordenada por valores constantes, referencias a
otras celdas, nombres, funciones, u operadores. Se considera como una
técnica básica para el análisis de datos.
La fórmula se escribe en la Barra de Fórmulas y debe empezar siempre por el
signo =.
Los distintos tipos de operadores que se pueden utilizar en una fórmula son:

Marco Aurelio Porro Chulli

 10

A. Operadores Aritméticos: Se emplean para producir resultados

numéricos. Ejemplo: + - * / % ^
B. Operador Tipo Texto: Se emplea para concatenar celdas que
contengan texto. Ejemplo: &
C. Operadores Relacionales: Se emplean para comparar valores y
proporcionar un valor lógico (verdadero o falso) como resultado de
la comparación. Ejemplo: < > = <= >= <>
D. Operadores de Referencia: Indican que el valor producido en la
celda referenciada debe ser utilizado en la fórmula. En Excel
pueden ser:
 Operador de rango indicado por dos puntos (:): Se emplea para
indicar un rango de celdas. Ejemplo: C101:G208
 Operador de unión indicado por una coma (,): Une los valores de
dos o más celdas. Ejemplo: B41,U55,ZZ8
3. Referencias
Cuando hacemos usos de fórmulas y funciones casi es seguro que pongamos
referencias a celdas o conjunto de celdas. Una dirección de una celda
contiene un valor al cual se hace referencia. Existen 3 tipos de referencia:
A. Relativa: Las referencias de filas y columnas cambian si se copia la
fórmula en otra celda, es decir se adapta a su entorno porque las
referencias las hace con respecto a la distancia entre la formula y
las celdas que forman parte de la fórmula. Esta es la opción que
ofrece Excel por defecto.
B. Absoluta: Las referencias de filas y columnas no cambian si se
copia la formula a otra celda, las referencias a las celdas de la
formula son fijas.
C. Mixta: Podemos hacer una combinación de ambas referencias,
podemos hacer que las filas sean relativas y las columnas
absolutas o viceversa.

Para cambiar el tipo de referencia de una celda se usa la tecla de función

<F4> al presionar esta tecla se agrega automáticamente el símbolo $ en
la dirección de la celda antes de la columna y de la fila. Por ejemplo si a la
celda cuya dirección es B45 se le aplica el cambio de referencia quedaría
$B$45

Marco Aurelio Porro Chulli

 11

4. Caso Práctico
La constructora PICAPIEDRA S.A.C. necesita controlar los pagos mensuales de sus
obreros a los cuales se les paga por día trabajado. Se sugiere usar el siguiente
formato:

Marco Aurelio Porro Chulli

 12

Los cálculos a realizar son:

 El Pago Bruto que resulta de la multiplicación de los Días Trabajados y el
Pago por Día.
 La Alimentación es de 15 por cada día trabajado.
 El Pago Neto Soles que resulta de la suma del Pago Bruto y la
Alimentación.
 El Pago Neto Dólares que resulta de la división del Pago Neto Soles y el
cambio de dólar actual.
Solución
Pago Bruto Celda H10 =F10*G10
Alimentación Celda I10 =F10*15
Pago Neto Soles Celda J10 =H10+I10
Pago Neto Dólares Celda K10 =J10 / 2.59

Importante
En este ejemplo se muestra el uso de las FÓRMULAS y las
REFERENCIAS RELATIVAS ya que al copiar las formulas a las
siguientes celdas vemos que la columna se mantiene y el número de la
fila cambia.

Marco Aurelio Porro Chulli

 13

Además la constructora necesita tener un listado de precios de sus diversos

servicios que brinda. Se brinda el servicio de acuerdo al tipo de zona que puede ser
Rural o Residencial

Marco Aurelio Porro Chulli

 14

Los cálculos a realizar son:

 Los descuentos para la zona Rural es 12% y la Residencial es del 7% del
Precio.
 El Precio Real :
 Rural que resulta del Precio menos el descuento Rural
 Residencial que resulta del Precio menos el descuento
Residencial
Solución
Descuento
Rural Celda D10 =C10*$G$3
Residencial Celda E10 =C10*$G$4
Precio Real
Rural Celda F10 =C10-D10
Residencial Celda G10 =C10-E10

Importante
En este ejemplo se muestra el uso de las REFERENCIAS ABSOLUTAS
donde vemos que al calcular el DESCUENTO RURAL se tiene que
cambiar a una REFERENCIA ABSOLUTA o FIJA usando la tecla de
función <F4> lo mismo se aplica en el DESCUENTO RESIDENCIAL
luego solo se aplica las REFERENCIAS RELATIVAS para el PRECIO
REAL

Marco Aurelio Porro Chulli

 15

Finalmente la empresa está realizando una proyección de construcciones en la

zona Norte del país el cual se estima en dólares. Los datos se toman con respecto
al año actual la proyección se calcula de los años 2013 al 2020.

Marco Aurelio Porro Chulli

 16

Los cálculos a realizar son:

 La Proyección del 2013 en la ciudad Tumbes resulta de la multiplicación
del dato del año 2012 y el % de Proyección respectivo (10.5%) más el
dato del año actual.
 La Proyección del 2013 en la ciudad Piura resulta de la multiplicación del
dato del año 2012 y el % de Proyección respectivo (14%) más el dato del
año actual.
 La Proyección del 2013 en la ciudad Chiclayo resulta de la multiplicación
del dato del año 2012 y el % de Proyección respectivo (16%) más el dato
del año actual.
 La Proyección del 2013 en la ciudad Trujillo resulta de la multiplicación del
dato del año 2012 y el % de Proyección respectivo (13.5%) más el dato
del año actual.

Solución
2013 - Tumbes Celda C7 =(B7*$B$14)+B7
2013 - Piura Celda C8 =(B8*$B$15)+B8
2013 -Chiclayo Celda C9 =(B9*$B$16)+B9
2013 - Trujillo Celda C10 =(B10*$B$17)+B10

Importante
En este ejemplo se muestra el uso de las REFERENCIAS MIXTAS donde
vemos que al calcular el 2013 - Tumbes se tiene en la fórmula una
REFERENCIA ABSOLUTA y luego se suma una REFERENCIA
RELATIVA.

Marco Aurelio Porro Chulli

 17

Diplomado de Excel
Objetivos:
 Definir y usar Funciones
 Aplicar las Funciones Básicas
1. Función
Son fórmulas predeterminadas que pueden ejecutar cálculos usando valores
específicos, llamados argumentos en orden determinado. Las funciones
permiten realizar operaciones sencillas como complejas.
Microsoft Excel tiene un conjunto de funciones organizadas por categorías, los
cuáles permiten resolver problemas en diversos campos como las estadísticas,
matemáticas, financieras, etc.
Tiene 3 elementos
A. Estructura de una función: Comienza con el símbolo igual (=),
seguido por el nombre de la función, un paréntesis de apertura, los
argumentos de la función separados por comas y un paréntesis de
cierre.
B. Nombre de función: Permite invocar las funciones predefinidas.
Para poder acceder a la lista de funciones disponibles, se debe
ubicar en la celda y presionar SHIFT + F3
C. Argumentos: Pueden ser números, textos, valores lógicos, valores
de error. Pueden ser también constantes, formulas u otras
funciones.
A continuación se describen las funciones básicas:
A. =SUMA: Permite sumar un rango de celdas predeterminadas, se
puede usar referencias de rango o nombres de rango.
=SUMA(Rango)
Ejemplos:
=SUMA(A50:A65) Referencias de Celdas
=SUMA(Salarios) Nombres de Rango
B. =PROMEDIO: Permite obtener el promedio aritmético de los
valores de un rango de celdas.
=PROMEDIO(Rango)
Ejemplo:
=PROMEDIO(C15:C20) Referencias de Celdas

Marco Aurelio Porro Chulli

 18

C. MAX(Rango): Obtiene el valor más alto de un rango

=MAX(Rango)
Ejemplo:
=MAX(D25:D38) Referencias de Celdas
D. MIN(Rango): Obtiene el valor más bajo de un rango
=MIN(Rango)
Ejemplo:
=MIN(Notas) Nombres de Rango
E. CONTARA(Rango): Cuenta las celdas de un rango específico,
considerando valores nulos, de error y textos.
=CONTARA(Rango)
Ejemplo:
=CONTARA(CA15:CA50) Referencias de Celdas
F. CONTAR (Rango): Cuenta las celdas de un rango específico,
considerando solo valores numéricos.
=CONTAR(Rango)
Ejemplo:
=CONTAR(Alumnos) Nombres de Rango

2. Caso Práctico
Se pide ingresar el siguiente cuadro y luego escribir las funciones básicas para
resolver los siguientes ejercicios.

Marco Aurelio Porro Chulli

 19

Marco Aurelio Porro Chulli

 20

 Obtener los totales por Lugares Turísticos y por Mes.

 Los requerimientos solicitados en la parte baja del cuadro.
Solución
TOTALES x Lugar Celda N5 =SUMA(B5:M5)
TOTALES x Mes Celda B9 =SUMA(B5:B8)
Ingreso Mayor en el Mes de Julio Celda F12 =MAX(H5:H8)
Ingreso Menor en el Mes de Octubre Celda F13 =MIN(K5:K8)
Ingreso Menor de los 3 Primeros Meses Celda F14 =MIN(B5:D8)
Promedio de Ingresos en el Mes de Celda F15 =PROMEDIO(M5:M8)
Diciembre
Promedio de Ingresos en los Meses de Celda F16 =PROMEDIO(I5:J8)
Agosto y Septiembre

Marco Aurelio Porro Chulli

 21

Diplomado de Excel
Objetivos:
 Manejo y aplicación de Rango de Nombres
 Aplicar las Funciones Estadística Básicas
1. Funciones Estadísticas Básicas
Excel te proporciona, además de las funciones estadísticas básicas, un
conjunto de herramientas de análisis que te permitirán realizar análisis
estadísticos más complejos.
Para ir por pasos, observaremos primero las funciones estadísticas más
utilizadas y luego nos adentraremos en los análisis estadísticos más
complejos.
A continuación se describen las funciones estadisticas básicas:
A. =[Link]: Permite contar las celdas de un rango que cumplan
con un criterio específico.
= [Link] (Rango, Criterio)
Ejemplo:
Contar a los alumnos de Sexo Masculino (M)
=[Link] (G15:G23, ”M”)
B. =[Link]: Permite sumar de acuerdo a un criterio específico, esto
quiere decir se sumara un conjunto de valores siempre y cuando se
cumpla el criterio.
= [Link] (Rango, Criterio, RangoSuma)
Ejemplo:
Obtener la suma de los salarios de los empleados del área de Ventas
=[Link] (F7:F23, ”Ventas”, D7:D23)
C. =[Link]: Permite obtener el promedio (media aritmética) de
todas las celdas de un rango que cumplen unos criterios
determinados.
= [Link] (Rango, Criterio, RangoPromedio)
Ejemplo:
Obtener el promedio de sueldos de los Vendedores
= [Link](E7:E23,”Vendedor”,D7:D23)

Marco Aurelio Porro Chulli

 22

2. Caso Práctico

Marco Aurelio Porro Chulli

 23

Los requerimientos solicitados a lado derecho del cuadro.

Solución

Cantidad de Empleados x Sexo

Femenino Celda K9 =[Link](Sexo,"F")
Masculino Celda K10 =[Link](Sexo,"M")
Cantidad de Empleados x Situación
Estable Celda K13 =[Link](Situacion,J13)
Contratado Celda H14 =[Link](Situacion,J14)
Total de Salarios x Area
Ventas Celda K17 =[Link](Area,J17,Salario)
Almacén Celda K18 =[Link](Area,J18,Salario)
Marketing Celda K19 =[Link](Area,J19,Salario)
[Link] Celda K20 =[Link](Area,J20,Salario)
TOTAL Celda K21 =SUMA(K17:K20)
Promedio de Salarios x Area
Gerencia Celda K24 =[Link](Area,"Gerencia",Salario)
Administración Celda K25 =[Link](Area,"Administración",Salario)
Contabilidad Celda K26 =[Link](Area,"Contabilidad",Salario)

Marco Aurelio Porro Chulli

 24

3. Ejercicios Propuestos

A. Obtener el Total de los Salarios de los Empleados que trabajan en el Área

de Administración.
B. Cantidad de Empleados de ganan un salario superior o igual a 2500.
C. Obtener el Promedio de Sueldos de los Empleados Varones.
D. Obtener los Porcentajes de los Empleados por cada Área
E. Cantidad de Empleados que trabajan en las Áreas de Marketing y Ventas.
F. Obtener el Total de los Salarios de los Empleados cuyo Cargo es Cajero.
G. Total de Empleados
H. Obtener el Promedio de Sueldos de los Empleados Estables.
I. Obtener el Total de los Salarios de los Empleados Contratados.
J. Promedio de Salarios
K. Obtener los Porcentajes de los Empleados por Sexo
L. Obtener el Promedio de los Salarios de los Empleados cuyo Cargo es
Químico Farmacéutico.
M. Obtener el Salario más alto y el más bajo.
N. Cantidad de Empleados que tengan un salario menor a 2000.
O. Obtener la MODA de los Salarios.

Marco Aurelio Porro Chulli

 25

Diplomado de Excel
Objetivos:
 Manejo y aplicación de Funciones de Texto
 Creación de Códigos Autogenerados
1. Funciones de Texto
Permite manejar los datos de texto (cadenas) en una hoja de cálculo.
Excel tiene una gran cantidad de funciones de texto que se pueden utilizar para
concatenar, comparar, al igual que varias funciones de formato, como IGUAL
que determina si dos cadenas de texto son exactamente iguales, o MINUSC
que convierte todos los caracteres de una cadena a minúsculas.
Si has usado Excel durante algún tiempo, podrás
haber notado que Excel es una excelente herramienta
para manipular texto y números.
A continuación se describen las funciones de texto:
Asuma que el valor de la celda A15 es COMPUTACION E INFORMATICA
A. LARGO: Devuelve la longitud del texto.
Sintaxis
= LARGO (Texto)
Ejemplo:
Contar los caracteres de la celda A15
=LARGO (A15) el valor que muestra es 25
B. MAYUSC: Permite convertir el texto a mayúscula.
Sintaxis
= MAYUSC (Texto)
Ejemplo:
Convertir el texto de la celda A15 a Mayúscula
= MAYUSC (A15)
C. MINUSC: Permite convertir el texto a minúscula.
Sintaxis
= MINUSC (Texto)
Ejemplo:
Convertir el texto de la celda A15 a Minúscula
= MINUSC (A15)

Marco Aurelio Porro Chulli

 26

D. CARACTER: Devuelve el carácter especificado por el número de

código correspondiente al código de caracteres ASCII.
Sintaxis
= CARACTER (Número)
Ejemplo:
Convertir el número de la tabla ASCII 76
= CARACTER (76) se devuelve la letra L
E. CODIGO: Devuelve el código ASCII del primer carácter del texto
pasado como parámetro.
Sintaxis
= CODIGO (Texto)
Ejemplo:
Convertir el carácter al código de la tabla ASCII
= CODIGO (“L”) se devuelve la letra 76
F. NOMPROPIO: Convierte a mayúscula la primera letra del texto.
Sintaxis
=NOMPROPIO (texto)
Ejemplo:
Convertir el texto tipo título
=NOMPROPIO (A15) el valor que devuelve es Computación e Informática
G. IZQUIERDA: Devuelve el número de caracteres especificados por la
izquierda de un texto.
Sintaxis
= IZQUIERDA (Texto, Num_caracteres)
Ejemplo:
Obtener los 3 primeros caracteres de la celda A15
=IZQUIERDA (A15, 3) el valor que devuelve es COM
H. DERECHA: Devuelve el número de caracteres especificados por la
derecha de un texto.
Sintaxis
= DERECHA (Texto, Num_caracteres)
Ejemplo:
Obtener los 2 primeros caracteres de la celda A15
=DERECHA (A15, 2) el valor que devuelve es CA

Marco Aurelio Porro Chulli

 27

I. EXTRAE: Devuelve los caracteres indicados de un texto desde un

número de posición dado.
Sintaxis
= EXTRAE (Texto, Posicion_Inicial, Num_caracteres)
Ejemplo:
Obtener el 4º, 5º y 6º carácter de la celda A15
=EXTRAE (A15, 4,3) el valor que devuelve es PUT
J. HALLAR: Encuentra una cadena dentro de un texto.
Sintaxis
= HALLAR (Texto_buscado, Texto, Num_inicial)
Ejemplo:
Ubicar la cadena IO en la celda A15
=HALLAR ("IO",A15,1) el valor que devuelve es 9
K. REEMPLAZAR: Reemplaza parte de una cadena de texto por otra.
Sintaxis
=REEMPLAZAR (texto_original, num_inicial, núm_de_caracteres, texto_nuevo)
Ejemplo:
Reemplazar la cadena E INFORMATICA por el de Y SISTEMAS
=REEMPLAZAR (A15,13,13,"Y SISTEMAS")
L. REPETIR: Repite el texto un número de veces determinado.
Sintaxis
=REPETIR (texto, núm_de_veces)
Ejemplo:
Repetir el texto que se encuentra en la celda A15 seis veces
=REPETIR (A15, 6)

Marco Aurelio Porro Chulli

 28

2. Caso Práctico
Una empresa de comunicaciones necesita generar los codigos para sus diversos
equipos celulares que comercializa.

Para generar dicho código el encargado requiere la siguiente información:

 Las tres primeras letras del Modelo
 Las 2 ultimas letras de la Marca
 El año de la Fecha de Ingreso

Solución
CODIGO AUTOGENERADO Celda C13
=IZQUIERDA(B8,3)&DERECHA(B6,2)&AÑO(E6)

Marco Aurelio Porro Chulli

 29

Ademas la empresa a implementado su correo corporativo para los empleados y

tambien requiere automatizas la creación de cuentas

Marco Aurelio Porro Chulli

 30

Para generar dicho código el encargado requiere la siguiente información:

 Las dos primeras letras del Nombre
 Las dos primera letras del Apellido Materno
 Las tres primeras letras del Area donde trabaja
 El mes de la Fecha de Nacimiento
 El texto @[Link]
Solución
Cuenta de Correo Celda A8
=MINUSC(IZQUIERDA(C8,2)&EXTRAE(B8,HALLAR("
",B8,1)+1,2)&IZQUIERDA(E8,3)&MES(F8))&"@[Link]"

3. Ejercicios Propuestos

A. Obtener las tres últimas letras del Apellido Paterno.

B. Tomar el tercer cuarto y quinto carácter del Área
C. Obtener solo el nombre de la dirección más no el número.
D. Obtener las 4 primeras letras del Cargo uniéndolo con la longitud de letras
del Nombre.
E. Las 2 primeras letras y la última letra del Área.
F. Obtener solo el Apellido Materno más el día de la Fecha de Nacimiento.
G. Convertir el Apellido a Mayúscula
H. Inserta el símbolo # en la dirección antes del número.
I. Unir el Nombre y el Apellido considerando la coma (,) como separador
entre ambos.
J. Generar un código autogenerado ( la formula lo indica usted)

Marco Aurelio Porro Chulli

 31

Diplomado de Excel
Objetivo:
 Manejo y aplicación de Funciones de Fecha y Hora
1. Funciones de Fecha y Hora
Las funciones de fecha y hora permiten hacer cálculos
cronológicos, de vencimientos y todos aquellos
relacionados con la variable tiempo (años, meses, días,
horas, minutos, segundos, etc).
A continuación se describen las funciones de fecha y
hora:
A. AHORA: Esta función nos devuelve la fecha y la hora actual del
sistema con formato de fecha y hora.
Sintaxis
= AHORA ()
Ejemplo:
= AHORA () el valor que devuelve 25/01/2013 11:50
B. AÑO: Permite convertir un número de serie en un valor de año.
Sintaxis
= AÑO (núm_de_serie)
Ejemplo:
Si la celda F20 tiene el valor 21/07/2006
= AÑO (F20) el valor que devuelve es 2006
C. MES: Permite convertir un número de serie en un valor de mes.
Sintaxis
= MES (núm_de_serie)
Ejemplo:
Si la celda F40 tiene el valor 15/08/2011
= MES (F40) el valor que devuelve es 8
D. DIA: Permite convertir un número de serie en un valor de día del
mes.
Sintaxis
= DIA (núm_de_serie)
Ejemplo:
Si la celda F60 tiene el valor 18/01/2007
= MES (F60) el valor que devuelve es 18

Marco Aurelio Porro Chulli

 32

E. DIAS360: Permite convertir el número de días entre dos fechas a

partir de un año de 360 días.
Sintaxis
= DIAS360 (fecha_inicial, fecha_final, [método])
Ejemplo:
Si la celda A20 tiene el valor 1/1/2008 y la celda A22 tiene el valor 01/02/2008
= DIAS360 (A20,A22) se devuelve el valor 30
F. [Link]: Devuelve el número de serie de la fecha equivalente al
número indicado de meses anteriores o posteriores a la fecha inicial.
Sintaxis
=[Link] (fecha_inicial,meses)
Ejemplo:
Si se tiene el valor 24/09/2011 en la celda G12
= [Link](G12,2) se devuelve el valor 24/11/2011
G. [Link]: Devuelve el número de serie del último día del mes,
anterior o posterior a la fecha_inicial del número de mes indicado.
Sintaxis
= [Link] (fecha_inicial, meses)
Ejemplo:
Si se tiene el valor 24/07/2011 en la celda G12
=[Link] (G12, 1) el valor que devuelve es 31/08/2011
H. [Link]: Devuelve el número de todos los días laborables
existentes entre dos fechas.
Sintaxis
= [Link](fecha_inicial,fecha_final,festivos)
Ejemplo:
Si la celda A20 tiene el valor 10/01/2011 y la celda A22 tiene el valor 03/01/2011
=[Link] (A20,A22) el valor que devuelve es el Número de días laborables entre
las fechas inicial y final anteriores (108)

Marco Aurelio Porro Chulli

 33

I. [Link]: Devuelve un número que representa una fecha que es el

número de días laborables antes o después de una fecha (la fecha
inicial).
Sintaxis
= [Link](fecha_inicial,días_lab,festivos)
Ejemplo:
Si la celda A10 tiene el valor 10/01/2011 y la celda A11 tiene el valor 151
=[Link] (A10,A11) Fecha 151 días laborables posterior a la fecha inicial
(30/4/2011)
J. HOY: Devuelve el número de serie correspondiente al día actual.
Sintaxis
= HOY ()
Ejemplo:
= HOY() el valor que devuelve 25/01/2013
K. HORA: Convierte un número de serie en un valor de hora.
Sintaxis
= HORA (núm_de_serie)
Ejemplo:
Si en la celda F21 se tiene el valor 13:14
=HORA (F17) el valor que devuelve es 13
L. MINUTO: Convierte un número de serie en un valor de minuto.
Sintaxis
= MINUTO (núm_de_serie)
Ejemplo:
Si en la celda F23 se tiene el valor 8:37 a.m.
=MINUTO (F23) el valor que devuelve es 37
M. SEGUNDO: Convierte un número de serie en un valor de segundo.
Sintaxis
=SEGUNDO (núm_de_serie)
Ejemplo:
Si en la celda F25 se tiene el valor [Link] a.m.
= SEGUNDO (F25) el valor que se devuelve es 18

Marco Aurelio Porro Chulli

 34

2. Caso Práctico
CLORITRANS S.A. es una empresa de transporte que tiene registrado a sus
empleados en una hoja de calculo y necesita obtener tanto la edad como la
antigüedad.

Marco Aurelio Porro Chulli

 35

Solución
Fecha Celda H2
=AHORA()
Código Generado Celda A6
=MAYUSC(IZQUIERDA(B6,3)&AÑO(D6))
Edad Celda G6
=AÑO(HOY())-AÑO(D6)
Antigüedad Celda H6
=AÑO(HOY())-AÑO(F6)
Además necesita registrar el control de entrada y salida de sus empleados. Se
registra su hora de ingresa asi como la hora de salida y Excel debe calular la
cantidad de horas trabajadas tambien debe obtener la suma de las horas trabajadas
en el dia.

Marco Aurelio Porro Chulli

 36

Solución
Tiempo Trabajo Celda D6
=C6-B6
Código Generado Celda D20
=SUMA(D6:D18)
Se tiene que aplicar el formato [h]:mm

3. Ejercicios Propuestos

A. Obtener el año de la fecha actual.

B. Calcular los minutos de la hora actual.
C. Obtener los días laborables entre la fecha 01/01/2013 al 02/15/2013
D. Obtener el mes de la fecha actual.
E. Obtener la hora del sistema.
F. Calcular la fecha final desde el inicio de un proyecto que es el 01/02/2013
y se tiene 200 días laborables.
G. Obtener el día de la fecha actual.
H. Mostrar los segundos de la hora actual.

Marco Aurelio Porro Chulli

 37

Diplomado de Excel
Objetivo:
 Manejo y aplicación de la Función Lógica SI
1. Función Lógica SI
Permite evaluar una condición establecida como argumento (prueba lógica), si
el resultado de ésta es verdadero, la celda presenta el segundo de los
argumentos, si es falso presentará el tercero.
Sintaxis
=SI (prueba lógica, valor si es verdadero, valor si es falso)
Ejemplo:
A. Considerando que la celda B45 tiene el valor 20 que es la edad de la
persona.
=SI (B45>=18,”Mayor de Edad”,”Menor de Edad”)
Si la condición (EDAD de la persona es mayor o igual a 18 años)
luego de ser evaluada resulta VERDADERO se muestra el mensaje
MAYOR DE EDAD si la condición resulta FALSO se muestra el
mensaje MENOR DE EDAD
B. Considerando que la celda D45 tiene el valor de 1500 que es el sueldo del
empleado.
=SI (D45<2000,3.5%,8%)*D45
Si la condición (SUELDO es menor a 2000) es VERDADERO se
calcula el 3.5% del sueldo si la condición es FALSO se calcula el 8%
del sueldo.
La Función SI cuando evalúa varias condiciones se le conoce como SI
ANIDADAS esto quiere decir que en un función SI encontramos otra función SI
dentro de ella y solo puede evaluar o usar hasta 7 funciones SI.
Sintaxis
SI(prueba lógica1, valor si es verdadero, SI(prueba lógica2, valor si es
verdadero,SI(prueba lógica3, valor si es verdadero ,valor si es falso)))
Ejemplo:
A. Considerando que en la celda D10 tiene el valor de 500 y las las celdas
F20 tiene el valor de C se desea calcular el descuento que es de acuerdo
a la letra que tiene la celda F20 si es A el descuento es del 5% si la letra
es B el descuento es del 8% y para C el descuento a calcular es del
12.5% del monto que esta en la celda D10.
=SI (F20=”A”,5%,SI(F20=”B”,8%,12.5%))*D10

Marco Aurelio Porro Chulli

 38

B. Considerando que en la celda A50 tiene el valor de 2 se desea mostrar el

nombre de la especialidad de acuerdo al código de especialidad:
Código Especialidad Especialidad
1 Computación
2 Administración
3 Contabilidad
4 Gastronomia
=SI(A50=1,”Computación”,SI(A50=2,”Administración”,SI(A50=3,”Contabilidad”,”Gastronomia”)))

2. Caso Práctico
La empresa de comunicaciones LAN-Server necesita realizar los calculos para el
pago de sus empleados.
Los calculos a realizar es:
 El sueldo se obtiene multiplicando las Horas Trabajadas y el Valor x Hora.
 La escolaridad es de 300 soles solo si el empleado tiene hijos.
 La Bonificación es del 30% si la situación del Empleado es Estable sino la
bonificación será del 10% del Sueldo.
 El Descuento es de 250 soles si el sueldo es superior o igual a 2500 si
fuera menor el descuento es de 100 soles.

Marco Aurelio Porro Chulli

 39

Marco Aurelio Porro Chulli

 40

Solución
Fecha Celda K2
=AHORA()
Sueldo Celda G6
=E6*F6
Escolaridad Celda H6
=SI(D6>0,300,0)
Bonificación Celda I6
=SI(C6="Estable",30%,10%)*G6
Descuento Celda J6
=SI(G6>2500,250,100)
Sueldo Neto Celda K6
=SUMA(G6:I6)-J6
Además la empresa LAN-Server tienen inversiones en hoteles y necesita controlar
el pago de los diferentes huesped que llegan al hotel.
Se debe tener en cuenta que se tiene los siguientes tipos de habitación con sus
respectivos precios como se muestra en el siguiente cuadro:

Código Habitación Descripción Precio x Día

SM Simple 80
DB Doble 120
MT Matrimonial 140
TR Triple 200
ST Suite 180
Se debe ingresar el nombre del huésped, la fecha de ingreso, la fecha de salida, el
tipo de habitación que ha ocupado y se debe calcular los días y el monto a pagar
(resulta de la multiplicación de la cantidad de días y el precio por día).
Si tiene una cantidad de días mayor a 7 tiene un descuento del 10% sino el
descuento será de 2% del monto a pagar.
Luego se debe calcular el monto neto a pagar que resulta del monto a pagar menos
el descuento calculado.

Marco Aurelio Porro Chulli

 41

Solución

Fecha Celda B22

=AHORA()
Cantidad de Dias Celda B11
=E9-B9
Descripción Celda B14
=SI(A14="SM","Simple",SI(A14="DB","Doble",SI(A14="MT","Matrimonial",SI(A14="
TR","Triple","Suite"))))

Marco Aurelio Porro Chulli

 42

Precio x Día Celda E14

=SI(A14="SM",80,SI(A14="DB",120,SI(A14="MT",140,SI(A14="TR",200,180))))
Monto a Pagar Celda E16
=B11*E14
Descuento Celda E18
=SI(B11>7,10%,2%)*E16
Monto Neto a Pagar Celda E20
=E16-E18

3. Ejercicio Propuesto

En el siguiente formato se debe calcular el promedio por cada ítem de evaluación

PROM1 para los CONTROLES se promedian los 3 controles al igual que el ítem
PROM2 para los TRABAJOS. Luego se debe calcular el PROMEDIO que resulta del
promedio de PROM1, PROM2 y EXA. UNID. Finalmente mostrar el mensaje
APROBADO o DESAPROBADO dependiendo del PROMEDIO que tenga el
alumno. Si el PROMEDIO es mayor a 12 se debe mostrar APROBADO sino debe
mostrar DESAPROBADO.

Marco Aurelio Porro Chulli

 43

Diplomado de Excel
Objetivo:
 Manejo y aplicación de la Función Lógica Y
1. Función Lógica Y
Permite evaluar 2 o más pruebas lógicas o condiciones si al menos una de las
condiciones resulta FALSO la función dará como resultado FALSO todas las
condiciones deben ser VERDADERO para que resulta VERDADERO.
En conclusión devuelve VERDADERO si todos los argumentos se evalúan
como VERDADERO; devuelve FALSO si uno o más argumentos se evalúan
como FALSO.
Sintaxis:
=Y (valor_lógico1, [valor_lógico2],...)
Ejemplo:
A. Se tiene el valor ESTABLE en la celda F10 y en la celda G10 el valor 2800
se desea calcular el 5% del salario solo si el empleado tiene su situación
CONTRATADO y un salario supeior a 2000 si no fuera asi el calculo del
bono es de 2% del salario.
=SI (Y (F10=”CONTRATADO”, G10>2000) ,5%,2%)*G10
En este caso la primera condición(F10=”CONTRATADO”) resulta FALSO y la
segunda condición(G10>2000) resulta VERDADERO al analizar la FUNCION Y
da como resultado FALSO por lo tanto se calculara el 2% del salario
B. Se tiene el valor 1.78 en la celda A5, el valor 70.40 en la celda B5 y el
valor Soltero en la celda C5. Se debe mostrar el mensaje de ACTO o el
mensaje NO APTO si la persona tiene una talla mayor a 1.70 un peso
inferior a 75 y su estado civil es Soltero.
=SI (Y (A5>1.70, B5<75, C5=”Soltero”),”APTO”,”NO APTO”)
En este caso la primera condición(A5>1.70) resulta VERDADERO y la segunda
condición( B5<75) resulta VERDADERO y la tercera condición ( C5=”Soltero”)
resulta VERDADERO al analizar la FUNCION Y da como resultado VERDADERO
por lo tanto se debe mostrar el mensaje APTO.

Marco Aurelio Porro Chulli

 44

2. Caso Práctico
La empresa de apuestas GANA-AUTOGOL necesita obtener la situación de los
partidos que se dan semanalmente en el mundo.

Marco Aurelio Porro Chulli

 45

Se debe obtener:
 El obtener la SITUACION del partido que puede ser GANO, PERDIO o
EMPATO de acuerdo la cantidad de goles del partido.
 El PREMIO que es de acuerdo al siguiente cuadro:
Situación Estado Premio
GANO L (Local) 25%
GANO V (Visita) 50%
EMPATO L (Local) Se le devuelve la apuesta
EMPATO V (Visita) JUEGO AL AZAR
PERDIO L(Local) o V(Visita) 0
Solución
Fecha Celda J2
=AHORA()
Situación Celda I8
=SI(F8>H8,"GANO",SI(F8<H8,"PERDIO","EMPATO"))
Premio Celda G6
=SI(Y(C8="L",I8="GANO"),D8*125%,SI(Y(C8="V",I8="GANO"),D8*150%,SI(Y(C8=
"V",I8="EMPATO"),D8,SI(Y(C8="L",I8="EMPATO"),"JUGADA AL AZAR",0))))

Marco Aurelio Porro Chulli

 46

3. Ejercicio Propuesto

Genera el CODIGO con los siguientes datos:

 Primeras letras de los Apellidos del Vendedor 3º y 4º letra de la
SITUACION
 Número de Caracteres de la SUCURSAL
Luego:
 La COMISION1 es del 10% si las Ventas son mayores a 50000 y se ha
realizado en la Sucursal Norte sino la comisión será del 5%.
 La COMISION2 es del 2% solo si la Situación del Empleado es
Contratado, las Ventas son inferiores a 30000 y se ha realizado en la
Sucursal Sur sino no se calculara ninguna comisión.

Marco Aurelio Porro Chulli

 47

Diplomado de Excel
Objetivo:
 Manejo y aplicación de la Función Lógica O
1. Función Lógica O
Permite evaluar 2 o más pruebas lógicas o condiciones si al menos una de las
condiciones resulta VERDADERO la función dará como resultado
VERDADERO, todas las condiciones deben ser FALSO para que resulta
FALSO.
En conclusión devuelve VERDADERO si al menos un argumento se evalúa
como VERDADERO; devuelve FALSO todas los argumentos se evalúan como
FALSO.
Esta función también se suele utilizar conjuntamente con la función SI. Con ella
también podremos realizar varias preguntas dentro del SI y la parte que está
en el argumento reservado para cuando la pregunta es VERDADERA, sólo se
realizará en el caso que cualquiera de las respuestas a las preguntas dentro de
la O sea VERDADERA.
Sintaxis:
=O (valor_lógico1, [valor_lógico2],...)
Ejemplo:
A. Se tiene el valor Soltero en la celda J15 y en la celda K15 el valor 750 se
desea calcular el 12% del salario si el empleado es Casado o tiene u
salario igual o superior al básico si no fuera asi el calculo del bono es de
5% del salario.
=SI (O (J15=”Casado”, K15>=750) ,12%,5%)*K15
En este caso la primera condición(J15=” Casado”) resulta FALSO y la segunda
condición(K15>=750) resulta VERDADERO al analizar la FUNCION O da como
resultado VERDADERO por lo tanto se calculara el 12% del salario
B. Se tiene el valor Si en la celda A50, el valor 10 en la celda B50 y el valor
Sistemas en la celda C50. Se debe mostrar el mensaje de ASCENDIDO o
el mensaje EN ESPERA si el empleado tiene titulo, una antigüedad
superior a 10 años o trabaja en el area de Marketing.
=SI (O(A50>”Si”, B50>10, C50=”Marketing”),”ASCENDIDO”,” EN ESPERA”)
En este caso la primera condición (A50>”Si”,) resulta VERDADERO, la
segunda condición (B50>10) resulta VERDADERO y la tercera condición
(C50=”Marketing”) resulta FALSO al analizar la FUNCION O da como resultado
VERDADERO por lo tanto se debe mostrar el mensaje ASCENDIDO.

Marco Aurelio Porro Chulli

 48

2. Caso Práctico
El Colegio Particular LIBERTADORES necesita tener un control de asistencia y
promedio para determinar su situación en el Centro Pre-Universitario

Marco Aurelio Porro Chulli

 49

Se debe obtener:
 El Total de Asistencia el cual debe contabilizar las asistencia (A)
 El Porcentaje de Asistencia
 La Situación el cual debe ser bajo las siguientes condiciones:
o Tiene más del 80% de Asistencia
o Promedio superior a 14
Si cumple una de las condiciones debe mostrar la situación APROBADO y
sino es asi debe mostrar DESAPROBADO
Solución
Fecha Celda N2
=AHORA()
Total Asisten. Celda K8
=[Link](C8:J8,"A")
% Asisten. Celda L8
=K8/CONTARA(C8:J8)
Situación Celda N8
=SI(O(L8>80%,M8>14),"Aprobado","Desaprobado")

Marco Aurelio Porro Chulli

 50

3. Ejercicio Propuesto

Marco Aurelio Porro Chulli

 51

Diplomado de Excel
Objetivo:
 Manejo y aplicación de la Función BuscarV
1. Función BUSCARV
Esta función nos permite buscar un valor en una primera columna de una
matriz, una vez localizado nos muestra dentro de la misma fila el valor que
contiene la columna que deseamos obtener.
Normalmente esta búsqueda Excel la hace pensando que esta primera
columna está ordenada. Si los valores nolo estuvieran tenemos que
indicárselo para que pueda encontrar el dato.
Si la tabla no está ordenada deberemos escribir Falso en el argumento que
hemos llamado Ordenado
Sintaxis:
=BUSCARV (Valor_buscado, Matriz_buscar_en, Indicador_columnas, Ordenado)
Ejemplo
Un docente tiene una tabla con las notas de un alumno puestas en números y
quiere completarla poniendo las notas en palabras.

Para dicho proceso cuenta con otra tabla de equivalencias

Marco Aurelio Porro Chulli

 52

Lo que se necesita es colocar su correspodiente Calificacion de acuerdo a la

Tabla de Equivalentes.
Lo primero que se tiene que hacer es seleccionar la Tabla de Equivalentes con
los encabezados (Nota y Calificación) y asignarle un NOMBRE al Rango de
Celdas seleccionadas, por ejemplo CALIFICACIONES.
Luego se tendria que colocar la siguiente formula a lado de NOTA.
=BUSCARV(D5,CALIFICACIONES,2,FALSO)
Finalmente se debe copiar dicha formula al resto de celdas quedando el cuadro
de la siguiente manera:

Marco Aurelio Porro Chulli

 53

2. Caso Práctico
La empresa de venta de computadoras DELTRIN necesita consultar sus articulos
rapidámente para eso le requiere a usted crear una hoja de calculo que automatize
dicho proceso.

Lo primero que se tiene que hacer es seleccionar la Tabla de Articulos con los
encabezados (Codigo, Producto, Precio, Marca, Almacen y Sotck) luego
asignarle un Nombre al Rango de Celdas seleccionadas, por ejemplo
ARTICULOS.
Luego se debe crear en la HOJA Consulta el siguiente cuadro:

Marco Aurelio Porro Chulli

 54

Se deben ingresar las siguientes formulas:

Articulo Celda B7
=SI(ESBLANCO(B6),"Ingrese Código", BUSCARV (B6,ARTICULOS,2,0))
Precio Celda B8
=SI(ESBLANCO(B6),"-", BUSCARV (B6,ARTICULOS,3,0))
Marca Celda B9
=SI(ESBLANCO(B6),"-", BUSCARV (B6,ARTICULOS,4,0))
Almacén Celda E9
=SI(ESBLANCO(B6),"-", BUSCARV (B6,ARTICULOS,5,0))
Fecha Celda B11
=AHORA()
Hora Celda B12
=HORA(B11)

Marco Aurelio Porro Chulli

 55

Diplomado de Excel
Objetivo:
 Creación y aplicación Gráficos
Gráficos
1. Definición
Es una representación de los
datos de una hoja de
electrónica a través de
figuras o líneas que
permiten un análisis e
interpretación más claros de
los mismos
2. Partes de un Gráfico
Un eje vertical (denominado también eje de valores o eje y) y un eje
horizontal (conocido también como eje de categorías o eje x).
Los gráficos 3D tienen un tercer eje, el eje de Los gráficos tienen normalmente
dos ejes que se utilizan para medir y clasificar los datos: profundidad
(denominado también eje de series o eje z), que permite representar los datos
a lo largo de la profundidad de un gráfico.

Marco Aurelio Porro Chulli

 56

Los gráficos radiales no tienen ejes horizontales (categorías), y los gráficos

circulares y de anillos no tienen ningún eje.

3. Tipos de Gráficos
A. Columnas: Se usan para mostrar
cambios entre distintos datos dentro de
un mismo período de tiempo, o los
cambios que sufren diferentes datos a lo
largo de varios períodos de tiempo.

B. Barras: Son como las de columnas,

salvo que los ejes están invertidos. El
eje X se localiza donde va
comúnmente el Eje Y, y viceversa.
Tienen el mismo uso que los gráficos
de columnas.

C. Líneas: En estos gráficos lo que se busca es resaltar la manera en que

cambian los datos. Cada línea representa una serie de datos.

Marco Aurelio Porro Chulli

 57

D. Circulares: En un gráfico circular se

pueden representar datos
contenidos en una columna o una
fila de una hoja de cálculo. Los
gráficos circulares muestran el
tamaño de los elementos de una
serie de datos, en proporción a la
suma de los elementos.
Los puntos de datos de un gráfico circular se muestran como porcentajes
del total del gráfico circular. Son excelentes para mostrar las relaciones
entre el todo y sus partes. Por ejemplo el presupuesto del gasto familiar.
E. Área: Este gráfico se utiliza para señalar la cantidad de cambios en los
datos. Los gráficos de área destacan la magnitud del cambio en el tiempo
y se pueden utilizar para llamar la atención hacia el valor total en una
tendencia. Por ejemplo, se pueden trazar los datos que representan el
beneficio en el tiempo en un gráfico de área para destacar el beneficio
total.

F. Anillo: Este gráfico se utiliza para señalar cambio en un los datos a

intervalos regulares. En un gráfico de anillos se pueden representar datos
organizados. Únicamente en columnas o en filas de una hoja de cálculo.

Marco Aurelio Porro Chulli

 58

G. Radiales: En un gráfico
radial se pueden
representar datos
organizados en
columnas o en filas de
una hoja de cálculo.
Los gráficos radiales
comparan los valores
agregados de un
número de series de
datos.
H. Dispersión (xy) : Este gráfico se usa para mostrar la relación entre dos o
más series de datos medidas a intervalos regulares.

I. Superficie: Muestra
tendencias de los
valores en dos
dimensiones a lo largo
de una curva continua.
Se pueden trazar datos
que se organizan en
columnas o filas de una
hoja de cálculo en un
gráfico de superficie.
Un gráfico de superficie es útil cuando busca combinaciones óptimas
entre dos conjuntos de datos.
Como en un mapa topográfico, los colores y las tramas indican áreas que
están en el mismo rango de valores. Puedes utilizar un gráfico de
superficie cuando ambas categorías y series de datos sean valores
numéricos.

Marco Aurelio Porro Chulli

 59

J. Burbujas: Compara conjuntos de tres valores similar al de dispersión pero

con el valor tres como tamaño del marcador. En un gráfico de burbujas,
se pueden trazar los datos que se organizan en columnas en una hoja de
cálculo de manera que los valores x se muestran en la primera columna y
los valores y correspondientes y los valores de tamaño de burbuja se
muestran en columnas adyacentes.

K. Cotizaciones : Como su nombre implica, un gráfico de cotizaciones se

utiliza con mayor frecuencia para mostrar la fluctuación de los precios de
las acciones. Sin embargo, este gráfico también se puede utilizar para
datos científicos. Por ejemplo, podría utilizar un gráfico de cotizaciones
para indicar la fluctuación de las temperaturas diarias o anuales.

Marco Aurelio Porro Chulli

 60

4. Caso Práctico
El Consorcio TERAMAS S.A. dese realizar un analisis completos de sus ventas
requiriendo las siguientes graficos:
A. Columna en 3D de todas las ventas realizados en los 3 ultimos meses
B. Linea 2D apiladas con marcadores de los distritos de JLO y Chiclayo
C. Burbuja con Efecto 3D de todos los distritos en el mes de Noviembre
Solución
A. Columna en 3D de todas las ventas realizados en los 3 ultimos meses
1. Seleccionar el rango de datos

2. Luego ir a la cinta Insertar, luego clic en el icono Columnas y

finalmente clic en Columnas 3D

Marco Aurelio Porro Chulli

 61

B. Linea 2D apiladas con marcadores de los distritos de JLO y Chiclayo

1. Seleccionar el rango de datos

2. Luego ir a la cinta Insertar, luego clic en el icono Línea y finalmente

clic en Apiladas con Marcadores

Marco Aurelio Porro Chulli

 62

C. Burbuja con Efecto 3D de todos los distritos en el mes de Noviembre

1. Seleccionar el rango de datos

2. Luego ir a la cinta Insertar, luego clic en el icono Burbuja y finalmente

clic en Burbuja con Efecto 3D

Marco Aurelio Porro Chulli

 63

Diplomado de Excel
Objetivo:
 Administrar Tablas en Excel
 Realizar filtros en las tablas
Tablas
1. Definición
Es un conjunto de datos organizados en filas
o registros, en la que la primera fila contiene
las cabeceras de las columnas (los nombres
de los campos), y las demás filas contienen
los datos almacenados.
Es como una tabla de base de datos, de hecho también se denominan listas
de base de datos. Cada fila es un registro de entrada, por tanto podremos
componer como máximo una lista con 16,384 campos y 1’048,576 registros.
Las tablas son muy útiles porque además de almacenar información,
incluyen una serie de operaciones que permiten analizar y administrar esos
datos de forma muy cómoda.
Entre las operaciones más interesantes que podemos realizar con las listas
tenemos:
 Ordenar la los registros.
 Filtrar el contenido de la tabla por algún criterio.
 Utilizar fórmulas para la lista añadiendo algún tipo de filtrado.
 Crear un resumen de los datos.
 Aplicar formatos a todos los datos.
2. Entendiendo las tablas de Excel
Las tablas de Excel son un rango de celdas que ha sido estructurado
adecuadamente con nuestros datos. Cada fila de una tabla corresponde a una
sola entidad, por ejemplo, una tabla puede contener información de los
empleados de una empresa y por lo tanto cada fila hará referencia a un
empleado diferente.

Marco Aurelio Porro Chulli

 64

Las tablas tienen encabezados en la parte superior y cada uno de ellos

describe la información contenida en cada columna. Así por ejemplo, nuestra
tabla de empleados tiene los encabezados Número,Empleado ( Apellido y
Nombre, Edad, Situación y Salario)
3. Ordenar una Tabla de Datos
A. Ubicar el cursor en cualquier delda de la tabla de datos o seleccionar la
tabla de datos.
B. Ir a la Cinta Datos
C. Clic en icono Ordenar

D. Se muestra la siguiente ventana:

Activar la casilla de verificación

E. Seleccione en COLUMNA Cargo, dejemos el ORDENAR SEGÚN Valores

y en CRITERIO DE ORDENACION cambiemos a Z a A. Luego hacemos
un clic en el Botón de Comando Agregar Nivel y seleccionamos
COLUMNA Empleado, dejemos el ORDENAR SEGÚN Valores y en
CRITERIO DE ORDENACION cambiemos de Z a A

Marco Aurelio Porro Chulli

 65

La ventana queda de la siguiente manera:

F. Finalmente clic en el Botón Aceptar . Los datos quedan de la siguiente

manera:

Marco Aurelio Porro Chulli

 66

4. Aplicar filtro al contenido de la Tabla

A. Ubicar el cursor en cualquier delda de la tabla de datos o seleccionar la
tabla de datos.
B. Ir a la Cinta Datos
C. Clic en icono Filtro

D. Se agregar automaticamente a cada encabezado de la tabla una FLECHA

DESPLEGABLE

E. Para poder realizar el FILTRO se debe usar esta fecha desplegable. Por
ejemplo : Si deseamos mostrar los empleados de SITUACION Contratado
se debe realizar:
 Clic en el filtro SITUACION, se muestra la siguiente ventana:

 Desactivar las casilla de verificación (Seleccionar todo) y luego

activar la casilla de verificación Contratado
 Finalmente clic en el Botón de Comando Aceptar

Marco Aurelio Porro Chulli

 67

 La tabla de datos queda de la siguiente manera:

Debemos observar que en el encabezado SITUACION esta activo el filtro.

5. Quitar filtro al contenido de la Tabla
A. Clic en el icono del filtro activo en el encabezado SITUACION

B. Se muestra la siguiente ventana:

C. Activar las casilla de verificación (Seleccionar todo) y luego finalmente

clic en el Botón de Comando Aceptar

Marco Aurelio Porro Chulli

 68

D. La tabla de datos queda de la siguiente manera:

Debemos observar que en el encabezado SITUACION ya no esta activo el filtro y

aparecen todos los datos.

6. Eliminar filtro
A. Ir a la Cinta Datos
B. Clic en icono Filtro

C. Las FLECHA DESPLEGABLE de cada ENCABEZADO se desaparecen

quedando los datos completos.

Marco Aurelio Porro Chulli

 69

7. Aplicar filtro avanzado al contenido de la Tabla

A. Los criterios por los cuales se realizará el filtrado deben especificarse
dentro de celdas de la misma hoja. Supongamos que deseo filtrar los
empleados cuyor Cargo son Almacenero. La hoja quedaria como en la
grafica:

B. Ubicar el cursor en cualquier delda de la tabla de datos o seleccionar la

tabla de datos.
C. Ir a la Cinta Datos
D. Clic en icono Avanzadas
E. Se muestra la siguiente ventana:

Marco Aurelio Porro Chulli

 70

F. Activar el botón de opción COPIAR A OTRO LUGAR , luego seleccionar

las celdas donde esta el criterio en RANGO DE CRITERIOS $H$4:$H$5 y
finalmente seleccionar en COPIAR A la celda $A$30
La ventana queda como en la figura:

Clic en el botón de Comando ACEPTAR

G. Los registros que cumplan la condición se copiaran a partir de la celda
A30

Marco Aurelio Porro Chulli