excel intermedio v3

63
Antes de comenzar… Dado el tiempo de duración del taller, y para poder ver todo el contenido, se solicita que se apague el celular o se coloque en “silencio” y en no vibración. Hay un lista que va puesto por puesto, por favor, registren sus datos ahí. Al final de la actividad hay una encuesta que deben de responder, ya que ella nos permite analizar y dimensionar el impacto de la actividad y así realizar mejoras en el tiempo.

Upload: gorki

Post on 10-Feb-2017

356 views

Category:

Education


0 download

TRANSCRIPT

Antes de comenzar… • Dado el tiempo de duración del taller, y para

poder ver todo el contenido, se solicita que se apague el celular o se coloque en “silencio” y en no vibración.

• Hay un lista que va puesto por puesto, por favor, registren sus datos ahí.

• Al final de la actividad hay una encuesta que deben de responder, ya que ella nos permite analizar y dimensionar el impacto de la actividad y así realizar mejoras en el tiempo.

Temas a ver… • Fusionar dos gráficos.

• Tablas dinámicas.

• Gráficos dinámicos.

• Función “Si”

• Funciones anidadas.

• Fijar filas, columnas y celdas.

• Importar a Word lo hecho en Excel.

Fusionar dos gráficos. “Si no lo puedes explicar con simplicidad, es que no lo

entiendes bien”

-Albert Einstein-

• También conocido como combinar dos gráficos.

• Consiste en unir dos gráficos que tengan

información similar y que juntos permiten tomar

mejores decisiones en vez de que ambos gráficos

se analizaran por separado.

• La fusión más común es la de un gráfico de barra

con uno de línea.

Fusionar dos gráficos.

• También conocido como combinar dos gráficos.

• Consiste en unir dos gráficos que tengan

información similar y que juntos permiten tomar

mejores decisiones en vez de que ambos gráficos

se analizaran por separado.

• La fusión más común es la de un gráfico de barra

con uno de línea.

Fusionar dos gráficos.

¿Y cómo se hace la “fusión de gráficos”?

• Primero haremos una tabla con datos a mostrar en un gráfico.

• Es muy importante que los datos a ingresar sean en las mismas unidades de medida. En este caso la unidad de medida es el $

• Es importante que se defina el tipo de gráfico a usar en cada columna.

VENTAS ($) GANANCIAS ($)

45 10

20 13

34 20

40 27

43 32

Ahora… • Hacer un gráfico con las dos columnas que se

desean que aparezcan en un solo gráfico.

0

5

10

15

20

25

30

35

40

45

50

1 2 3 4 5

Ventas ($)

Ganancias ($)

Ahora… • Ahora debes hacer clic derecho sobre la serie de

datos que convertiremos a línea, que en este caso

es la serie Ganancia.

Ahora… • Selecciona la opción Cambiar tipo de gráfico de

series y se mostrará el cuadro de diálogo Cambiar

tipo de gráfico.

Ahora… • Selecciona el tipo de gráfico Línea con

marcadores y haz clic en Aceptar. El gráfico

combinado aparecerá en pantalla.

0

5

10

15

20

25

30

35

40

45

50

1 2 3 4 5

Ventas ($)

Ganancias ($)

Finalmente… • Puedes agregar un título al gráfico y para ello

debes selecciona el comando Título del gráfico

que se encuentra en el grupo Etiquetas de la ficha

Presentación:

Tablas dinámicas (TD). “Si no lo puedes explicar con simplicidad, es que no lo

entiendes bien”

-Albert Einstein-

¿Qué es una tabla dinámica?

• Una tabla dinámica permite de forma automática

clasificar, contar, totalizar o dar la media de los

datos almacenados en una tabla u hoja de

cálculo.

• Se muestran los resultados en una segunda tabla

(llamada "tabla dinámica" o "tabla pivote") que

muestra los datos resumidos.

Funcionamiento de las tablas dinámicas.

• Supongamos que tenemos una tabla de datos que contiene mucha información sobre las ventas de la compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.

Funcionamiento de las tablas dinámicas.

• Nos piden un reporte con el total de ventas por

ciudad y el total de ventas por producto. Así que lo

que se necesita hacer es sumar las ventas para

cada ciudad y sumar las ventas de cada producto

para obtener el reporte.

• En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado.

• Una TD nos permite hacer una comparación entre

diferentes columnas de una tabla.

• Para ellos, imaginemos una tabla dinámica de la

siguiente manera…

Funcionamiento de las tablas dinámicas.

• Lo primero que debemos hacer es especificar los

campos de nuestra tabla de datos que vamos a

comparar. Elegimos las ciudades como las filas de

nuestra tabla dinámica.

Funcionamiento de las tablas dinámicas.

• Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.

Funcionamiento de las tablas dinámicas.

• Finalmente elegimos una columna de valores

numéricos que serán calculados y resumidos en la

tabla dinámica:

Funcionamiento de las tablas dinámicas.

• Así se obtiene un reporte que compara las ciudades y los productos y para cada combinación se obtendrá el total de ventas. Lo más importante que se quiere transmitir es que las tablas dinámicas permiten elegir entre todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un buen análisis de la información.

Funcionamiento de las tablas dinámicas.

Análisis de una base de datos.

• Para generar una tabla dinámica, se debe de contar con una planilla con datos coherentes entre sí. Los datos coherentes son aquellos que en su conjunto tienen relación con

algo específico, es decir, que tengan sentido.

• Lo ideal es que siempre exista una columna de referencia (generalmente en la Columna A) y el resto de columnas con información variada.

• Cuando se trabaja con datos, lo ideal es que cada columna tenga su título para que así sea más fácil el trabajar con los datos en la TD.

• Para entender mejor lo anterior, se analizará una base de datos que más adelante se descargará.

• NOTA: analizar si las filas que tengan datos incompletos nos son útiles o no. Si no son útiles, lo mejor es borrarlos o colocar un guion o el valor cero.

Bajemos una base de datos.

• Abrir el navegador y escribimos la siguiente

dirección web…

http://superalumnos.net/files/BD-Inmuebles.xls

• Con ello descargaremos un archivo Excel con

varios valores.

• Este archivo es importante para que podamos

continuar.

• Una vez descargado, abrir dicho archivo.

Construcción de una tabla dinámica (TD).

• Para generar una TD, debemos hacer los

siguiente… A. Seleccionar una celda vacía en la misma hoja o en una hoja nueva

(independiente si se selecciona celda u hoja nueva, la celda a usar

siempre será la del vértice izquierdo superior de la tabla dinámica).

B. Ahora, vamos a la opción “INSERTAR” y hacer clic en “TABLA”

C. Con ello haremos que se abra el siguiente cuadro de dialogo

Construcción de una tabla dinámica (TD).

Aquí

seleccionamos

el rango de

celdas a usar

en la TD Aquí se indica

en dónde se

ubicará la TD

Ya

configurado,

sólo falta

hacer clic en

aceptar.

Construcción de una tabla dinámica (TD).

Aquí se

muestra el

lugar donde irá

la TD

generada.

Aquí se

muestran las

distintas

opciones de

configuración

de la TD.

Partes de una tabla dinámica en Excel.

• Justo cuando se ha creado una tabla

dinámica se muestra en la parte

derecha de la hoja la lista de campos

disponibles y por debajo las áreas

donde podemos arrastrar dichos

campos. Estas áreas denotan cada

una de las partes de una tabla

dinámica.

Partes de una tabla dinámica en Excel.

• Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que ves en pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas.

• Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica.

• Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica.

• Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.

Partes de una tabla dinámica en Excel.

• Una vez especificados los campos para cada una

de las áreas, la tabla dinámica cobra vida. Puedes

tener una tabla dinámica funcional con tan solo

especificar las columnas, filas y valores. Los filtros

son solamente una herramienta para mejorar el

análisis sobre los datos de la tabla dinámica.

Construcción de una tabla dinámica (TD).

• Ahora, generemos unas TD.

Seleccionemos del listado (dejar un ticket) a

“Provincia”, “Precio Venta” y “Vendedor”.

Seleccionemos del listado (dejar un ticket) a

“Tipo”, “Provincia” y “Superficie”.

Construcción de una tabla dinámica (TD).

• Ahora, generemos unas TD.

Ahora, desde el listado al dibujo en blanco de la

TD arrastremos a la parte superior “Vendedor”,

luego arrastremos a la izquierda “Referencia”, y

al medio arrastremos “Precio Venta”.

Dar formato a una tabla dinámica.

Formato a una TD. • Una vez que has creado una tabla dinámica, Excel

permite aplicarle formato fácilmente como si fuera

una tabla de datos. La ficha “Diseño” incluye

comandos especiales para aplicar formato a una

tabla dinámica.

• La ficha “Diseño” es una ficha contextual, por lo

que deberás seleccionar la tabla dinámica para

que se muestre.

Formato a una TD. • Esta ficha está dividida en tres grupos.

Grupo 1

Diseño Grupo 2

Opciones de

estilo de TD

Grupo 3

Estilos de TD

Grupo 1: Diseño. • Este grupo permite agregar subtotales y totales

generales a la tabla dinámica así como modificar

aspectos básicos de diseño.

Grupo 1: Diseño. A. En el botón ‘Subtotales’, puedes elegir entre no mostrar

subtotales o mostrarlos en la parte superior o inferior.

B. El botón ‘Totales’, te permite activar o desactivar los

totales en tu tabla dinámica, e inclusive puedes elegir si

los activas solo para las filas o solo para las columnas.

C. Con el botón ‘Diseño de informe’, puedes realizar otros

juegos con la disposición del diseño de tu tabla.

D. El botón ‘Filas en blanco’ te permite elegir si se

muestran filas en blanco, después de cada elemento.

Grupo 2: Opciones de estilo.

• Aquí podemos elegir si la TD contará con

encabezados de fila, filas con columnas, entre

otros. Lo único que hay que hacer, es colocar o

sacar el ticket a la opción deseada.

Grupo 3: Estilo de TD. • Aquí podemos elegir los colores y el estilo que

deseamos que la TD tenga.

Gráficos dinámicos. “Si no lo puedes explicar con simplicidad, es que no lo

entiendes bien”

-Albert Einstein-

Construcción de un gráfico dinámico (GD).

• Para generar un GD, debemos hacer los

siguiente… A. Seleccionar una celda vacía en la misma hoja o en una hoja nueva

(independiente si se selecciona celda u hoja nueva, la celda a usar

siempre será la del vértice izquierdo superior de la tabla dinámica).

B. Ahora, vamos a la opción “INSERTAR” y hacer clic en “GRÁFICO”

C. Con ello haremos que se abra el siguiente cuadro de dialogo

Construcción de un gráfico dinámico (GD).

Aquí

seleccionamos

el rango de

celdas a usar

en el GD Aquí se indica

en dónde se

ubicará elGD

Ya

configurado,

sólo falta

hacer clic en

aceptar.

Construcción de un gráfico dinámico (GD).

Aquí se

muestra el

lugar donde irá

el GD

generado.

Aquí se

muestran las

distintas

opciones de

configuración

del GD.

Construcción de un gráfico dinámico (GD).

• Ahora, generemos unos GD.

Seleccionemos del listado (dejar un ticket) a “Provincia”, “Precio Venta” y “Vendedor”.

Seleccionemos del listado (dejar un ticket) a “Tipo”, “Provincia” y “Superficie”.

Ahora, arrastremos desde el listado a la parte superior “Vendedor”, luego arrastremos a la izquierda “Referencia”, y al medio arrastremos “Precio Venta”.

Función “Si”.

Función “Si”.

Es una de las funciones más usadas en

Excel, ya que permite dar una de dos

respuestas según a una condición dada.

Funciones anidadas. Hay veces que hay que usar más de una función dentro de

otra función.

Concepto de función anidada.

• En ciertos casos, puede que necesite usar una función

determinada dentro de otra, como parte de uno de los

argumentos (recuerde que el argumento es el valor o

conjunto de valores que utiliza una función para llevar a

cabo operaciones o cálculos) de otra función.

= 𝒇𝒖𝒏𝒄𝒊ó𝒏(𝒂𝒓𝒈𝒖𝒎𝒆𝒏𝒕𝒐)

• Función anidada…

= 𝒇𝒖𝒏𝒄𝒊ó𝒏𝟏(𝒇𝒖𝒏𝒄𝒊ó𝒏𝟐(𝒂𝒓𝒈𝒖𝒎𝒆𝒏𝒕𝒐𝟐))

• OBSERVACIÓN: Vale mencionar que se pueden anidar

hasta 64 funciones, pero no es recomendable llegar a

dicho número.

Función anidada simple. • La función “SÍ” es la más utilizada al momento de

crear funciones anidadas.

• Esto de debe a su estructura, ya que la función “SÍ” permite definir dos respuestas según una condición.

• Para comprenderlo mejor, hagamos el siguiente ejercicio.

• Ingrese en la columna A, las letras A, B y C en el orden que usted desee hasta completar 10 ingresos.

• Ahora, colóquese en la columna B e ingrese lo siguiente:

=si(A1=“A”;1;0)

Función anidada simple. • Ahora, arrastre la función SI generada hasta

completar los 10 registros.

• Ello hará que aparezcan ceros y unos según si hay

letra A o no.

• Ahora, ubíquese en la celda B1 y haremos que

cuando haya una letra “B” la función nos entregue

el valor “2”. Para eso, sólo basta ingresar lo

siguiente en el lugar donde está el valor cero…

si(A1="B";2;0)

• Lo anterior hará que se vea así...

=SI(A1="A";1;SI(A1="B";2;0))

Bloquear filas y columnas en las funciones de Excel.

Hay veces que necesitamos fijar una celda o serie de datos.

Fijar fila y columnas. • El signos $ en las fórmulas y funciones de Excel sirven

para crear Referencias Absolutas o Relativas. Esto significa que con un $ podemos fijar, congelar, anclar, inmovilizar, etc. una celda de Excel.

• Cunado el signo $ está delante de la letra significa que en la función sólo se puede cambiar el número de la fila (ej: $A1).

• Cuando el signo $ está delante del número significa que en la función sólo se puede cambiar la letra de la columna (ej: A$1).

• Cuando el signo $ está delante de la letra y del número la celda respectiva queda fija (ej: $A$1).

• La tecla F4 del teclado nos ayuda en este punto, sólo se debe seleccionar, en la función, la celda que se desea fijar.

Llevar lo hecho en Excel a Word

En Excel se generan tablas y gráficos, y llevarlos a Word no es nada complicado.

Antes de… • Siempre considerar que el espacio de escritura en Excel

es mayor que en Word. En otras palabras, mientras que en Excel tenemos una hoja casi infinita, en Word ya se parte con un espacio limitado al tamaño de la hoja, por lo tanto hay que tener presente que quizás no todo quede visible en Word.

• Cuando se trabaja algo de Excel en Word, el PC puede comenzara funcionar más lento.

• Al agrandar o achicar el tamaño de la ventana de Excel en Word, se eliminan o se agregan filas y columnas.

• Al agrandar o achicar el tamaño el resultado de la ventana de Excel en Word, el contenido se achica o agranda.

• Lo anterior se demostrará a continuación.

Insertar en Word una tabla de Excel.

• Es bastante frecuente crear documentos de texto con Word que tienen tablas con operaciones más o menos complejas, por ejemplo un presupuesto. Lo correcto es crear el presupuesto con Excel y después insertarlo en Word con un “vínculo”, de tal forma que si el presupuesto cambia en el origen se actualice de forma automática en el destino, pero esto conlleva manejar dos archivos.

• Creamos en Excel lo deseado y lo guardamos.

• Ahora, seleccionamos solo el rango de celdas con datos a llevar a Word y lo copiamos.

• Vamos a Word y nos colocamos en la parte del documento en el que vamos a insertar los datos de Excel. Inicio, Pegar, Pegado especial.

Insertar en Word una tabla de Excel.

Siempre elegir

esta opción

cuando algo se

lleve de Excel a

Word.

Insertar en Word una tabla de Excel.

Aquí se

muestra el

objeto Excel

en Word.

Aquí se

muestra la

ventana de

Excel abierta

en Word.

Insertar en Word una tabla de Excel.

• En el cuadro de dialogo El objeto Excel en Word

tiene tratamiento de imagen por lo que se puede

ampliar y reducir fácilmente.

• Sí se desea modificar un dato, se debe hacer doble

clic sobre el Objeto Excel para activar la edición.

• El archivo de Excel se puede modificar de forma

independiente, y al abrir el archivo de Word un

cuadro nos advertirá sobre el vínculo y los cambios.

Si se acepta, los cambios hechos en el Excel se

reflejarán en el Word.

Gráficos en Word. • En la pestaña Insertar hacemos clic en el botón

Gráfico. Aparecerá un cuadro de diálogo desde

donde podremos seleccionar el tipo de gráfico

que necesitamos. Seleccionaremos uno de la lista y

pulsamos Aceptar.

Gráficos en Word. • Como se observa, el gráfico que se ha insertado

representa a la tabla de datos de ejemplo de Excel.

• Ahora, sólo bastará cambiar esos valores por los que se necesiten (que ya podremos tener creado en un archivo Excel). Con un simple copiar y pegar o edición uno a uno bastará.

• Al ir cambiando los datos, hará que el gráfico se dibuje automáticamente con los nuevos valores ingresados, como también cambiando el área que la línea azul encierra en el Excel.

• También podemos modificar el tipo de gráfico. Esto se hace mediante el icono Tipo de gráfico que aparece en la pestaña Diseño, cuando hacemos clic en un gráfico.

Gracias

Fuente: • https://exceltotal.com/graficos-combinados-en-excel/

• https://exceltotal.com/tablas-dinamicas-en-excel/

• https://youtu.be/F_k0NeqCdaU

• http://raymundoycaza.com/como-crear-un-grafico-en-excel/

• http://www.aulaclic.es/excel2010/index.htm

• https://cienporcienoffice.wordpress.com/2013/07/10/insertar-en-word-una-tabla-de-excel/