manual de microsft excel avanzado
TRANSCRIPT
3cer SEMESTRE DE ENFERMERIA
“A”2015-2016
2015
MANUAL DE MICROSFT EXCEL AVANZADO
INDICE DE CONTENIDO
CALCULAR EL TOTAL DE VENTAS POR MESES........................................................................2
GRÁFICO DE BARRAS CORRESPONDIENTE AL TOTAL DE VENTAS..........................................3
GRÁFICO DE BARRAS APILADAS DE LOS MESES ENERO, FEBRERO Y MARZO........................4
GRÁFICO DE SECTORES PARA LAS VENTAS MENSUALES.......................................................5
GRÁFICO DE LÍNEAS...............................................................................................................6
GRAFICO TIPO: COLUMNAS...................................................................................................7
GRAFICO TIPO: CIRCULAR......................................................................................................9
GRAFICO TIPO: COLUMNA; ESTILO: CIRCULAR..................................................................11
GRAFICO: TIPO CIRCULAR - ESTILO CIRCULAR 3D................................................................12
COMPRAS DE CINCHOS AL MERCADO CENTRAL.................................................................14
PASOS PARA REALIZAR UNA COLUMNA AGRUPADA CON LINEA.......................................16
PASOS PARA REALIZAR UN GRAFICO RADIAL CON ESTILO DE RADIAL CON MARCADORES............................................................................................................................................. 17
PASOS PARA REALIZAR UN GRAFICO DE COLUMNAS APILADAS.........................................23
TABLA DINÁMICA................................................................................................................29
Crear una tabla dinámica en Excel 2010..........................................................................29
EJERCICIO DE FORMATO CONDICIONAL..............................................................................32
EJERCICIO MEDIANTE LA UTILIZACION DE FILTROS.............................................................35
2
CALCULAR EL TOTAL DE VENTAS POR MESESA B C D
1 MES PRODUCTO1 PRODUCTO2 TOTAL VENTAS
2 Enero 100 40 =SUMA(B2:C2)
3 Febrero 150 25 =SUMA(B3:C3)
4 Marzo 240 41 =SUMA(B4:C4)
5 Abril 95 52
6 Mayo 75 167
7 Junio 175 286
8
9
10
Para Calcular el TOTAL DE VENTAS:
1) Aplicamos la fórmula de SUMA =SUMA(B2:C2)
2) Realizamos la misma fórmula hasta encontrar el TOTAL DE VENTAS de todos los
meses.
3
GRÁFICO DE BARRAS CORRESPONDIENTE AL TOTAL DE VENTAS1) Seleccionamos los datos que deseamos que aparezcan en nuestro gráfico de
barras.
2) En la cinta de opciones hacemos clic en INSERTAR, luego en la opción de BARRA seleccionamos el tipo de formato que deseamos para nuestra gráfica.
3) Aparecerá nuestro GRAFICO DE BARRAS.
4) Donde le podremos dar el DISEÑO, PRESENTACIÓN, FORMATO, a nuestro estilo.
4
GRÁFICO DE BARRAS APILADAS DE LOS MESES ENERO, FEBRERO Y MARZO1) Seleccionamos los datos que deseamos que aparezcan en nuestro gráfico de
barras.
2) En la cinta de opciones hacemos clic en INSERTAR, luego en la opción de BARRA seleccionamos el tipo de formato que deseamos en este caso BARRA APLICADA.
3) Aparecerá nuestro GRAFICO DE BARRA APLICADA
5
GRÁFICO DE SECTORES PARA LAS VENTAS MENSUALES1) Seleccionamos los datos que deseamos que aparezcan en nuestro gráfico de
barras.
2) En la cinta de opciones hacemos clic en INSERTAR, luego en la opción de ÁREA seleccionamos el tipo de formato que deseamos en este caso ÁREA APLICADA.
3) Aparecerá nuestro GRAFICO DE SECTORES PARA LAS VENTAS MENSUALES
6
GRÁFICO DE LÍNEAS1) Seleccionamos los datos que deseamos que aparezcan en nuestro gráfico de
barras.
2) En la cinta de opciones hacemos clic en INSERTAR, luego en la opción de LÍNEA seleccionamos el tipo de formato que deseamos.
3) Aparecerá nuestro GRAFICO DE LÍNEA
7
GRAFICO TIPO: COLUMNAS
ESTILO: COLUMNA AGRUPADA EN 3D
1. Seleccionamos los datos que deseamos que aparezcan en el gráfico.
2. Clic en insertar, luego clic en insertar grafico de columnas
3. Seleccionamos la opcion columna agrupada en 3D
4. Al obtener el grafico, clic en el signo mas y procedemos a colocar su respectivo nombre.
8
5. Los meses colocarlos en la derecha.
GRAFICO TIPO: CIRCULAR ESTILO: CIRCULAR 3D
1. Seleccionamos los datos que deseamos que aparezcan en el gráfico.
9
2. Clic en insertar, seleccionamos grafico 3D
3. Al obtener el grafico, clic en el signo mas y procedemos a colocar su respectivo nombre.
4. Las sucursales seran colocardos en la parte inferior
10
GRAFICO TIPO: COLUMNA; ESTILO: CIRCULAR1. Seleccionamos los datos que deseamos que aparezcan en nuestro gráfico
2. En la BARRA de opciones hacemos clic en INSERTAR, luego en la opción de CIRCULAR seleccionamos el formato que utilizaremos para nuestra gráfica.
3. Aparecerá nuestro grafico circular.
4. Donde le podremos dar el DISEÑO, PRESENTACIÓN, FORMATO, a nuestro estilo.
cc
CARTERASCENTROPLAZA MUNDOSAN LUISAPOPALOURDES
11
GRAFICO: TIPO CIRCULAR - ESTILO CIRCULAR 3D
COMPRAS AL PROVEDOR DE ZAPATOS
1. Con el cuadro de datos que tenemos, precedemos a realizar el grafico, seleccionando los datos que deseamos que aparezcan.
2. En la BARRA de opciones hacemos clic en el icono INSERTAR y en el icono CIRCULAR y seleccionamos el grafico circular en 3D.
3. Aparecerá nuestro grafico
c
c
12
4. Una vez que tenemos el grafico, en la barra de opciones podremos darle el DISEÑO, PRESENTACION Y FORMATO a nuestro estilo, y podremos agregarle
el título al gráfico.
c
13
COMPRAS DE CINCHOS AL MERCADO CENTRAL
Tipo: Circular.
Estilo: Anillo.
1. Realizar la tabla compras2. Seleccionar toda la columna de sucursal y la columna chincos3. Dar clip en insertar
4. Ir hasta gráfico y dar clip en otros escoger la opción anillos y dar clip
14
5. Grafico obtenido
6. Vamos a la barra de herramientas y escogemos la opción diseños de gráfico y damos clip en La flecha de abajo
7. Escogemos el diseño 6 y damos clip
15
PASOS PARA REALIZAR UNA COLUMNA AGRUPADA CON LINEA.
MATERIA: EMPREMATICA PRIMER PERIODO
1. Abrir una hoja d Excel.2. Escribir el nombre de los alumnos de emprematica del primer periodo, las 3
notas.3. De las 3 notas sacar el promedio (=promedio(#1,#2)).4. Señalar 5 alumnos de la tabla y dar clic en insertar.
5. Buscar columna y dar clic.
6. Buscar columna agrupada-línea o cuadro combinado y dar clic.
16
PASOS PARA REALIZAR UN GRAFICO RADIAL CON ESTILO DE RADIAL CON MARCADORES.
1. Dar clic en la pestaña “Insertar”.
Sin tener los datos seleccionados, pincha en la pestaña “Insertar”, que aparece después de la pestaña “Inicio”.
2. Pincha en el tipo de gráfico “OTROS”.
En el apartado “Gráficos”, selecciona la categoría “otros”.
17
3. Selecciona el sub-tipo “radial con marcadores”.
En el menú que aparece, selecciona el sub-tipo llamado “Columna Apiladas”. Este es el tipo de gráfico de columnas más utilizado
4. Aparecerá un rectángulo en blanco.
Si el resultado de todo esto es un rectángulo en blanco, vacío, no te preocupes; es precisamente lo que pretendíamos.
Pincha con el botón derecho sobre el rectángulo.
Pincha con el botón derecho sobre el rectángulo blanco y elige la opción “Seleccionar Datos…”
18
5. Arma tu gráfico, a gusto.
Un campo llamado “Rango de datos del gráfico:“Este campo recibe el rango que conforma la tabla de datos que pretendemos graficar.
Un pequeño panel a la izquierda, en el que solo encontrarás habilitado el botón “Agregar“.Si tu gráfico, por alguna razón, no te resulta usando la técnica anterior o seleccionando directamente el Rango de Datos del gráfico en el campo anterior, puedes hacerlo manualmente agregando cada serie una por una, al pinchar en este botón.
Al pinchar sobre este botón, verás aparecer un nuevo cuadro de diálogo en el que aparecen solo dos campos:
“Nombre de la Serie“, en el que tú le vas a escribir un nombre cualquiera, que identificará a la serie en el gráfico. Ejemplo: NOTA 1
19
“Valores de la serie“, donde vas a insertar el rango en el que se encuentran los datos de la serie que vas a graficar.
Una vez hecho esto, solo debes pinchar sobre el botón “Aceptar” para cerrar este cuadro de diálogo.
Volverás al cuadro de diálogo anterior, donde podrás repetir el proceso cuantas veces necesites.
A la derecha, encontrarás un segundo panel llamado “Etiquetas del eje horizontal (categorías)“. Aquí encontrarás un botón llamado “Editar“.
Pinchando en este botón podrás elegir el rango donde se encuentran las categorías, es decir, aquellos valores que aparecerán como etiquetas.
Una vez hecho esto, solo debes pinchar en el botón “Aceptar” y aparecerán los nombres de las categorías.
20
Pincha nuevamente en el botón “Aceptar” y tu gráfico quedará listo.
PEPITO
JUANITO
LUISITO
ANA MARIA
KARINASUSANITA
PEDRITO
JOSESITO
VERONIKITA
0.00
5.00
10.00
PROMEDIO
6. Ponle tu estilo.
Finalmente, vamos a darle un poco de color a nuestro gráfico. Para hacerlo, solo debes seleccionar el gráfico y, en la ficha contextual “Herramientas de gráficos“, elije la ficha “Diseño“.
En esta ficha, en el apartado “Estilos de diseño“, verás varios estilos predefinidos que podrás utilizar para hacer tu gráfico más colorido en solo un par de clics.
21
PEPITO
JUANITO
LUISITO
ANA MARIA
KARINASUSANITA
PEDRITO
JOSESITO
VERONIKITA
0
5
10
PROMEDIO
22
PASOS PARA REALIZAR UN GRAFICO DE COLUMNAS APILADAS.
1. Dar clic en la pestaña “Insertar”.
Sin tener los datos seleccionados, pincha en la pestaña “Insertar”, que aparece después de la pestaña “Inicio”.
2. Pincha en el tipo de gráfico “Columna”.
En el apartado “Gráficos”, selecciona la categoría “Columna”.
23
3. Selecciona el sub-tipo “Columna Apiladas”.
En el menú que aparece, selecciona el sub-tipo llamado “Columna Apiladas”. Este es el tipo de gráfico de columnas más utilizado
4. Aparecerá un rectángulo en blanco.
Si el resultado de todo esto es un rectángulo en blanco, vacío, no te preocupes; es precisamente lo que pretendíamos.
Pincha con el botón derecho sobre el rectángulo.
Pincha con el botón derecho sobre el rectángulo blanco y elige la opción “Seleccionar Datos…”
24
5. Arma tu gráfico, a gusto.
Un campo llamado “Rango de datos del gráfico:“Este campo recibe el rango que conforma la tabla de datos que pretendemos graficar.
Un pequeño panel a la izquierda, en el que solo encontrarás habilitado el botón “Agregar“.Si tu gráfico, por alguna razón, no te resulta usando la técnica anterior o seleccionando directamente el Rango de Datos del gráfico en el campo anterior, puedes hacerlo manualmente agregando cada serie una por una, al pinchar en este botón.
Al pinchar sobre este botón, verás aparecer un nuevo cuadro de diálogo en el que aparecen solo dos campos:
25
“Nombre de la Serie“, en el que tú le vas a escribir un nombre cualquiera, que identificará a la serie en el gráfico. Ejemplo: NOTA 1
“Valores de la serie“, donde vas a insertar el rango en el que se encuentran los datos de la serie que vas a graficar.
Una vez hecho esto, solo debes pinchar sobre el botón “Aceptar” para cerrar este cuadro de diálogo.
Volverás al cuadro de diálogo anterior, donde podrás repetir el proceso cuantas veces necesites.
A la derecha, encontrarás un segundo panel llamado “Etiquetas del eje horizontal (categorías)“. Aquí encontrarás un botón llamado “Editar“.
Pinchando en este botón podrás elegir el rango donde se encuentran las categorías, es decir, aquellos valores que aparecerán como etiquetas.
Una vez hecho esto, solo debes pinchar en el botón “Aceptar” y aparecerán los nombres de las categorías.
26
Pincha nuevamente en el botón “Aceptar” y tu gráfico quedará listo.
PEPITO JUANITO LUISITO ANA MARIA0
2
4
6
8
10
12
14
NOTA3NOTA1
6. Ponle tu estilo.
Finalmente, vamos a darle un poco de color a nuestro gráfico. Para hacerlo, solo debes seleccionar el gráfico y, en la ficha contextual “Herramientas de gráficos“, elije la ficha “Diseño“.
En esta ficha, en el apartado “Estilos de diseño“, verás varios estilos predefinidos que podrás utilizar para hacer tu gráfico más colorido en solo un par de clics.
27
TABLA DINÁMICA
Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.
Crear una tabla dinámica en Excel 2010
PEPITO JUANITO LUISITO ANA MARIA0
2
4
6
8
10
12
14
5
8
4
6
31.5 5
6.5
Los mejores estudiante de emprema-tica
NOTA3NOTA1
28
1. Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.
2. Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.
29
3. Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.
4. En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.
5. Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo
30
una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro.
6. Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Siguiendo el ejemplo propuesto del artículo anterior, colocaré como columna el campo Producto y como fila al campo Ciudad. Finalmente como valores colocaré el campo Ventas.
De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:
31
EJERCICIO DE FORMATO CONDICIONAL
Ejercicio 1: GASTOS SERVICIOS Ingrese la siguiente tabla de datos y proceda a aplicar un formato condicional de barras, y que:
* Resalte todos aquellos gastos superiores a 3.500.000 (un color).
* Resalte valores entre 2.000.000 y 2.900.000 (un color).
* Resaltar valores menores a 1.800.000 (un color).
GASTO ENERO FEBRERO MARZO ABRIL MAYO JUNIOLUZ 1550700 1724600 1398200 2347300 1500500 1942000AGUA 3830500 3942500 3567200 3500900 4329800 4051200GAS 865500 800300 750200 690200 842300 955400TELEFONIA 2540000 2670500 1875000 2983500 1724600 253000INTERNET 2680000 2347300 2980500 3050400 2900300 2566300MENSAJERA 3483000 3980450 4051200 4329800 3950500 4200100CELADURIA 4695000 4789000 4200100 3765200 3980450 4200100NOMINA 13590400 12842900 13592200 10982400 12500200 12988900
Para colocar formato condicional de barras
1. Primero seleccionamos toda la tabla2. Damos clic en formato condicional3. Luego en barra de datos y escogemos el degrado que deseamos para nuestra
tabla damos clic y quedara así:
32
Para resaltar gastos superiores a 3.500.000 (un color).
1. Seleccionamos solo las cifras de la tabla2. Damos clic en formato condicional3. Luego en resaltar reglas de celdas, escogemos es mayo que4. aparecerá el siguiente cuadro donde colocaremos la cifra solicitada
escogeremos el color que deseamos que se resalte y damos clic en aceptar
5. Quedará así:
Para resaltar valores entre 2.000.000 y 2.900.000 (un color).
1. Seleccionamos solo las cifras de la tabla2. Damos clic en formato condicional3. Luego en resaltar reglas de celdas, escogemos la opción entre4. aparecerá el siguiente cuadro donde colocaremos las dos cifras solicitadas
escogeremos el color que deseamos que se resalte y damos clic en aceptar
5. quedará así
33
Para resaltar valores menores a 1.800.000 (un color).
1. Seleccionamos solo las cifras de la tabla2. Damos clic en formato condicional3. Luego en resaltar reglas de celdas, escogemos la opción es menos que4. aparecerá el siguiente cuadro donde colocaremos la cifra solicitada
escogeremos el color que deseamos que se resalte y damos clic en aceptar
5. finalmente quedara así:
34
EJERCICIO MEDIANTE LA UTILIZACION DE FILTROS
En el presente ejercicio se empleara los filtros de Excel tanto los sencillos como avanzados para responder cada una de las interrogantes que se plantean a continuación:
1. ¿Cuántos vehículos vendió Andrea?
La respuesta será 9 vehículos
COMPROBACION:
1.1. Vamos a filtrar en este caso la columna que dice vendedor.
1.2. Colocaremos filtros a toda la tabla para empezar a trabajar, señalando toda la tabla, una vez hecho eso buscaremos una opción que dice ordenar y
35
filtrar se desplegara un menú donde escogeremos la opción FILTRO y automáticamente se colocaran una direcciones en sentido para abajo en los títulos de las columnas de la tabla, lo cual nos indicara que se ha colocado correctamente el filtro.
1.3. Una vez colocado el FILTRO a toda la tabla, escogeremos la columna vendedor para comprobar si la respuesta que tenemos es verdadera o falsa. Daremos click en la fecha del título VENDEDOR
donde se desplegara una menú que contiene: ordenar de A a Z o de Z a A, ordenar por color filtro de texto, en la parte de abajo aparecerá seleccionar todo y aparecerán los nombres de los vendedores, en este caso vamos a seleccionar solo donde dice el nombre Andrea y ponemos aceptar, para filtrar la columna vendedor así comprobaremos que la respuesta de 9 vehículos que vendió es la correcta.
36
2. ¿Cuántos vehículos se vendieron al contado?
La respuesta es 9 vehículos se vendieron de contado.
2.1Vamos a filtrar en este caso la columna que dice forma de pago.
37
2.2 Daremos click en la fecha del título forma de pago y seleccionaremos la opción que dice contado para filtrar la tabla según la forma de pago del vehículo dando como resultado la respuesta obtenida anteriormente.
3. ¿Cuántos vehículos son de lujo del año 1998?
La respuesta es 2 vehículos de lujo del año 1998
COMPROBACION:
3.1Vamos a filtrar en este caso la columna que dice TIPO y MODELO.
38
3.2Podemos empezar a filtrar cualquiera de las dos columnas pero vamos a empezar por la columna TIPO donde aparecerán Ens. Nacional y Lujo, escogeremos la opción donde dice lujo para filtrar los vehículos de Lujo.
39
3.3Ahora procederemos a filtrar la columna MODELO escogiendo el año 1998 para comprobar que la respuesta en la correcta.
4. ¿Cuántos vehículos superan los $ 20.000,00?
La respuesta es que 17 vehículos superan los $20.000,00
COMPROBACION:
4.1. Vamos a filtrar en este caso la columna que dice VALOR NETO.
40
4.2Para filtrar valores emplearemos la opción filtros de números donde escogeremos la opción Mayor que, donde se abrirá una ventana en la cual ingresaremos el valor a filtrar en este caso es 20000000 pero si comas ni puntos porque Excel no reconoce estos signos y el filtro saldrán incorrecto, luego de colocar la cantidad a filtrar damos en click en aceptar y automáticamente se filtrara numero mayores a 20000000.
5. ¿Cuántos vehículos tienen descuento?
La respuesta aquí es que 9 vehículos tienen descuento.
COMPROBACION:
5.1Vamos a filtrar en este caso la columna que dice
DESCUENTO.
5.2 Daremos click en la fecha y quitaremos el tick en los valores en
41
0.00 que se encuentran en la parte de debajo de ventana, para filtrar los vehículos que no tienen descuento y podemos aceptar y automáticamente nos dará una lista solo de los vehículos que si tienen descuentos que en este caso serán 9.
42
6. ¿Cuántos vehículos son marca chevrolet y su valor neto es de $ 18.000,00 a $ 28.000,00?
La respuesta es que hay 5 vehículos marca Chevrolet que su valor neto es de $18000000 a $28000000
COMPROBACION:
6.1 Vamos a filtrar en este caso la columna que VALOR NETO.
6.2 Vamos a dar click en la fecha del título valor neto y utilizaremos la opción filtrado de números nuevamente pero esta vez escogeremos la opción de ENTRE, se desplegara una ventana nueva donde tendremos que colocar los dos valores que queremos filtrar, el valor menor en la parte superior que en este caso es 18000000 y el valor mayor en la parte inferior que en este caso es 28000000 pondremos aceptar y automáticamente se filtraran valores entre estas dos cantidades.
6.3Una vez filtrados los valores procederemos a filtrar lo que es la marca del vehículos en este caso filtraremos vehículos
43
con la marca Chevrolet para que nos dé como resultado los 5 vehículos que son marca Chevrolet y están dentro de los valores $18000000 y $28000000.
44
7. Hacer una lista de los 8 vehículos con más bajo precio.- Aqui aplicaremos otra vez la opción filtrado de números seleccionando la
opción menor que, se desplegara la ventana donde colocaremos un valor cualquiera para encontrar los 8 vehículos con más bajo precio en este caso yo coloque 25000000, ponemos aceptar y se filtraran los vehículos con más bajo precio.
8. Hacer una lista de las 10 comisiones más altas.
- Emplearemos otra vez el filtrado de números
en la columna COMISION seleccionaremos esta vez la opción mayor o igual que, así mismo iremos probando con varios valores hasta que nos dé como resultado las 10 comisiones más altas, en este caso yo puse 400000, podemos aceptar y se filtraran las comisiones que necesitamos.
45
9. ¿Cuánto suman los valores netos entre 20.000,00 y 30.000,00?- Utilizaremos en mismo filtro de números pero esta vez con la opción ENTRE para filtrar
valores entre 20000000 y 30000000 en la columna VALOR NETO, para luego proceder a sumarlos usando una función básica suma =Suma (L2:L14).
46
47
10. Hacer una lista de los vehículos entre el año 1997 y 2000 que cuya forma de pago sea a crédito.
- Para resolver este problema utilizaremos nuevamente filtros de números con la opción ENTRE para filtrar vehículos cuyo MODELO sea entre los años 1997 y 2000, pero también utilizaremos un filtro en la columna FORMA DE PAGO señalando la forma de pago sea a crédito dando como resultado la lista de los vehículos entre esos años y su forma de pago sea a crédito.
48
Filtro en forma de pago (Crédito)
49
11. Hacer una lista de los vehículos Toyota del año 1998 y 2000.- Para resolver este problema utilizaremos nuevamente filtros de números con la
opción ENTRE para filtrar vehículos cuyo MODELO sea entre los años 1998 y 2000, pero también utilizaremos un filtro en la columna MARCA señalando la marca del vehículo sea a Toyota dando como resultado la lista de los vehículos entre esos años y su marca sea Toyota.
FILTRO DE MARCA DEL VEHÍCULO