Modulo Informatica Excel Ub2012

I N S T I T U T O C O L O M B I A N O D E

A P R E N D I Z A J E

Excel Avanzado

GUÍA PARA EL ESTUDIANTE

Elaborado por:

HELADIO POLO CASTRO

INSTITUTO COLOMBIANO DE APRENDIZAJE INCAP

Programa Técnico Laboral En Sistemas

Tecnología Informática II



Informática II 2

Informática II Instituto Colombiano de Aprendizaje Elaborado por: Heladio Polo Castro Editado por: Instituto Colombiano de Aprendizaje INCAP Avenida Caracas No. 63-66 © Prohibida la reproducción parcial o total bajo cualquier forma (Art. 125 Ley 23 de 1982) Bogotá – Colombia Versión 04 - Enero 2010

EL SIGUIENTE MATERIAL SE PREPARÓ CON FINES ESTRICTAMENTE ACADÉMICOS, DE ACUERDO CON EL ARTÍCULO 32 DE LA LEY 23 DE 1982, CUYO TEXTO ES EL SIGUIENTE:

ARTÍCULO 32: “Es permitido utilizar obras literarias, artísticas o parte de ellas, a título de ilustración en obras destinadas a la enseñanza, por medio de publicaciones, emisiones o radiodifusiones, o grabaciones sonoras o visuales, dentro de los límites justificados por el fin propuesto, o comunicar con propósito de enseñanza la obra radiodifundida para fines escolares, educativos, universitarios y de formación personal sin fines de lucro, con la obligación de mencionar el nombre del autor y el título de las obras utilizadas”.



Excel Avanzado 3

Contenido

PRESENTACIÓN ______________________________________________ 5 GUÍA METODOLÓGICA _______________________________________ 6

1. UNIDAD DE APRENDIZAJE 1 ____________________________________ 8

1.1. Funciones Condicionales anidadas y Funciones de Búsqueda

avanzadas:________________________________________________ 8

1.2. Funciones: __________________________________________ 8 1.2.1. Función Si Condicional SI() y Anidadas: _______________ 9

1.2.2. Funciones de Búsqueda en Excel: ___________________ 13 1.2.2.1. CONSULTAH y CONSULTAV:________________ 13

1.2.2.2. Buscar: _____________________________________ 14 2. UNIDAD DE APRENDIZAJE 2 ___________________________________ 22

2.1. Filtrar información, establecer Subtotales, Tablas dinámicas y

Gráficos dinámicos ________________________________________ 23 2.2. Tabla de Datos ______________________________________ 23

2.2.1. Operaciones Básicas sobre una Tabla de datos _________ 24 2.2.1.1. Ordenación de la Tabla de datos _________________ 25

2.2.1.2. Filtrado de la Tabla de datos ____________________ 27 2.2.1.3. Autofiltro: ___________________________________ 27

2.2.1.4. Filtro avanzado: ______________________________ 29 2.2.2. Subtotales: _____________________________________ 30

2.2.2.1. Subtotales Anidados: __________________________ 33 2.2.3. Tablas Dinámicas: _______________________________ 34

2.2.3.1. Partes de una Tabla Dinámica ___________________ 35 2.2.3.2. Utilizar un campo de Filtro en Tabla Dinámica _____ 40

2.2.3.3. Mostrar Valores Como: ________________________ 42 2.2.3.4. Formas de trabajar con un informe de tabla dinámica: 44

2.2.3.5. Informes de Gráfico Dinámico ___________________ 46 3. UNIDAD DE APRENDIZAJE 3 ___________________________________ 52

3.1. Macros automáticas ___________________________________ 52 3.1.1. Crear una macro automáticamente ___________________ 53

3.1.2. Cómo asignar Macros a botones de control ____________ 55 3.1.3. Aplicar botones de tipo formulario __________________ 57

4. BIBLIOGRAFIA___________________________________________ 62



Informática II 4

Apreciado Estudiante: Usted escogió al INCAP para que lo oriente en el camino de la formación profesional. La entidad le proporcionará un formador, quien le ayudará a descubrir sus propios conocimientos y habilidades. El INCAP, le ofrece además, recursos para que usted alcance sus metas, es decir, lo que se haya propuesto y para ello dispondrá de módulos guías, audiovisuales de apoyo, sistemas de evaluación, aula y espacios adecuados para trabajos individuales y de grupo. Éste módulo guía que constituye además un portafolio de evidencias de aprendizaje, está distribuido de la siguiente manera: PRESENTACION: Es la información general sobre los contenidos, la metodología, los alcances la importancia y el propósito del módulo. GUIA METODOLOGICA: Orienta la practica pedagógica en el desarrollo del proceso de formación evaluación y se complementa con el documento de la didáctica para la formación por competencias de manejo del formador. DIAGNÓSTICO DE ESTILO DE APRENDIZAJE: Que le permitirá utilizar la estrategia más adecuada para construir sus propios aprendizajes. AUTOPRUEBA DE AVANCE: Es un cuestionario que tiene como finalidad que usted mismo descubra, qué tanto conoce los contenidos de cada unidad, y le sirve de insumo para la concertación de su formación y el reconocimiento de los aprendizajes previos por parte de su formador (talleres que se encuentran al final de cada unidad). EVALUACION DIAGNÓSTICA: Permite registrar tanto el estilo de aprendizaje como la auto prueba de avance. CONTENIDOS: Son el cuerpo de la unidad y están presentados así:

Unidad

Logro de competencia laboral

Indicadores de logro: Evidencias de Desarrollo

Didáctica del método inductivo Activo para el desarrollo de las competencias: FDH: Formador Dice y hace, FDEH: Formador dice y Estudiante hace, EDH: Estudiante dice y hace.

VALORACIÓN DE EVIDENCIAS BIBLIOGRAFÍA



Excel Avanzado 5

PRESENTACIÓN

En la actualidad, las empresas en sus diferentes modalidades, invierten gran cantidad de dinero en recursos (mano de obra capacitada, herramientas informáticas, apoyo logístico, etc.), que le permitan administrar la información derivada de los diferentes procesos generados al interior de las mismas. Uno de los recursos más valiosos es el humano y por ende la importancia de su preparación técnica para el adecuado desempeño laboral y la óptima utilización de los recursos informáticos adquiridos. Es por todo lo anterior que se hace necesario preparar al estudiante del programa técnico en el manejo competente, óptimo y eficaz de las herramientas ofimáticas más utilizadas a nivel empresarial. El presente módulo, pretende de manera práctica y coherente, enseñar al estudiante el uso adecuado de Excel, PowerPoint y Access; Herramientas tecnológicas fundamentales en nuestro quehacer empresarial, y así ubicarlo competitivamente en el entorno laboral actual, permitiéndole alcanzar cargos medios dentro de una organización y brindarle la oportunidad de mejorar su nivel económico, técnico, social, y cultural. Es importante resaltar que el módulo es solo una guía básica que debe acompañarse de investigación, trabajo en equipo, ejercicios prácticos extracurriculares, lecturas técnicas y auto motivación, en aras de alcanzar las competencias requeridas.



Informática II 6

GUÍA METODOLÓGICA

La estrategia metodológica del INCAP, para la formación técnica del aprendiz mediante competencias laborales, comprende dos caminos: 1. Las clases presénciales dictadas por el formador haciendo uso del método inductivo

– activo 2. El trabajo práctico de los estudiantes dirigido y evaluado por el Instructor, a través de

talleres, desarrollo de casos, lecturas y consultas de los temas de clase etc. Con esto, se busca fomentar en el estudiante el análisis, el uso de herramientas tecnológicas y la responsabilidad.

Los módulos guías utilizados por el INCAP, para desarrollar cada uno de los cursos, se elaboran teniendo en cuenta ésta metodología. Sus características y recomendaciones de uso son: A cada unidad de aprendizaje le corresponde un logro de competencia laboral el cual

viene definido antes de desarrollar su contenido. Seguidamente se definen los indicadores de logro o sea las evidencias de aprendizaje requeridas que evaluará el Instructor

Glosario: Definición de términos o palabras utilizadas en la unidad que son propias

del tema a tratar. Desarrollo de la unidad dividida en contenidos breves seguidos por ejercicios,

referenciados así:

- FDH (El Formador dice y hace): Corresponde a la explicación del contenido y el desarrollo de los ejercicios por parte del Formador.

- FDEH (El Formador dice y el estudiante hace): El Estudiante desarrolla los

ejercicios propuestos y el Formador supervisa.

- EDH (El estudiante dice y hace): Es el trabajo práctico que desarrollan los estudiantes fuera de la clase, a través de talleres, desarrollo de casos, lecturas y consultas de los temas, los cuales deben ser evaluados por el formador.

Al final de cada unidad se puede presentar un resumen de los contenidos más relevantes y ejercicios generales.



Excel Avanzado 7

DIAGNÓSTICO

INFORMACIÓN GENERAL Regional_____________Programa__________________Módulo____________ Estudiante_________________________Formador_______________________ EVALUACIÓN DIAGNÓSTICA Estilo de aprendizaje_______________________________________________



Informática II 8

1. UNIDAD DE APRENDIZAJE 1

Logros de Competencia Laboral

1. Conoce y aplica el uso de condicionales anidados y combinados con otras funciones, además conoce y aplica el uso de funciones de búsqueda avanzadas de Excel utilizadas en situaciones claves.

Indicadores de Logros

Aplica y crea funciones condicionadas anidadas

Establece y crea funciones condicionales combinadas con otras

Establece técnicas para utilizar funciones de búsqueda para localizar información entre hojas de cálculo

Evidencias Producto

Desempeño

Desempeño

FDH (El Formador Dice Y Hace)

1.1. Funciones Condicionales anidadas y Funciones de

Búsqueda avanzadas:

1.2. Funciones: En una hoja de cálculo, las funciones son herramientas especiales que permiten al usuario realizar cálculos complejos de manera fácil y sencilla. El resultado de la función se reflejará en la CELDA en la que esta se edita.

Excel Avanzado



Excel Avanzado 9

1.2.1. Función Si Condicional SI() y Anidadas: La función SI me permite evaluar una condición y dependiendo del resultado del análisis (que puede ser falso o verdadero), se retornará un valor por verdadero u otro valor por falso. Su sintaxis es:

=SI(Prueba_logica;Valor_por_verdadero;Valor_por_falso)

Prueba_lógica: es la condición que analiza el sistema y cuyo resultado puede ser falso o verdadero. Valor_por_verdadero: Es el valor que el sistema retorna si el resultado de la condición es verdadero. Valor_por_falso: Es el valor que el sistema retorna si el resultado de la condición es falso. Nota: El valor de retorno puede ser un valor numérico, un valor alfabético, un valor alfanumérico, el resultado de una operación matemática, también puede incluir otro SI condicional (si anidado) o incluir otra función (estadísticas, matemáticas, búsqueda, información o lógica)

FDEH (El Formador Dice Y El Estudiante Hace)

Ejemplo 1: Abra un libro nuevo y en la hoja 1 del libro introduzca los siguientes

datos:

De acuerdo a esta información

asignar a los valores las siguientes

letras: en la columna B

Si es 1 colocar “Azul”

Si es 2 colocar “Rojo”

Si es 3 colocar “Verde”

DESARROLLO:

=SI(A2=1;”AZUL”;SI(A2=2;”ROJO”;SI(A2=3;”VERDE”;”ERROR”)) , verifica que los valores digitados sean de 1 a 3, ahora cópiela hacia abajo (celdas)



Informática II 10


Ejemplo 2: En una hoja de cálculo nueva introduce la siguiente información.

a) En la columna E calcule la nota definitiva para cada estudiante, teniendo en cuenta que el primer parcial tiene un valor del 30%, los talleres 20% y el examen final del 50%.

b) En la columna F indique si el estudiante Aprobó o Reprobó la asignatura de

acuerdo con la siguiente condición: Nota definitiva superior o igual a 3,00 debe colocarse Aprobado, en caso contrario Reprobado.

c) En la Columna G se debe colocar la evaluación en letras de acuerdo con las

siguientes condiciones: (utilice SI anidados)

a. Nota Definitiva es mayor o igual a 4,5 = Excelente b. Nota Definitiva entre 4,0 y 4,4 = Sobresaliente c. Nota Definitiva entre 3,0 y 3,9 = Aceptable d. Nota Definitiva inferior a 3,0 = Insuficiente

d) Introduce las fórmulas correspondientes para calcular los datos de promedio,

nota máxima y nota mínima para la nota definitiva.

Desarrollo:



Excel Avanzado 11

1. Para resolver el numeral a), ubiquémonos en la celda E3 y en ella editaremos la siguiente fórmula:

=(B2*30%)+(C2*20%)+(D2*50%)

Esta fórmula se debe copiar en las siguientes celdas de la columna hasta completar los resultados para cada estudiante.

Explicación:

Esta es otra forma de generar Notas académicas por porcentaje de participación, al final la suma de los

productos de las notas dará la Definitiva.

2. Ahora, Pasemos al numeral b) y nos ubicaremos la celda F3, para construir la siguiente función:

=SI(E2>=3,0;”Aprobado”;”Reprobado”)

Al igual que en el punto anterior la función se debe copiar en las celdas subsiguientes.

Explicación: El argumento E3>=3,0 corresponde a la Prueba_lógica y en ella se analiza si la nota definitiva es superior o igual a 3,0; el argumento “Aprobado” corresponde al Valor_por_verdadero que será el valor que devolverá el sistema si el resultado de la prueba lógica es verdadero; el argumento “Reprobado” corresponde al Valor_por_falso que será el valor que retornará el sistema si el resultado del análisis de la prueba lógica es falso.

3. Para resolver el numeral c) en la celda G3 se debe construir una función más

compleja que la anterior (utilizando la Función SI() anidada), teniendo en cuenta que son cuatro las condiciones que debe analizar el sistema para emitir el resultado deseado; miremos entonces como quedaría la función.

=SI(E2>=4,5;"Excelente";SI(E2>=4;"Sobresaliente";SI(E2>=3;"Aceptable";"Insuficie

nte"))) Si Excelente

Si Nota >=4,5 Si Sobresaliente No Nota >=4 Si Aceptable No Nota >=3 No Insuficiente



Informática II 12

4. El numeral d) el estudiante lo realizará con la asesoría del formador, teniendo en cuenta que son funciones ya estudiadas en semestres anteriores.

Al final tendremos una tabla como la siguiente:

Ejemplo 3:

Tomando como base el ejercicio anterior vamos a utilizar las siguientes funciones .SI:

CONTAR.SI : SE UTILIZA PARA MOSTRAR LA CANTIDAD DE ELEMENTOS QUE HAY EN UN RANGO NUMERICO O TEXTO DEPENDIENDO DE UNA CONDICION DADA

PROMEDIO.SI: SE UTILIZA PARA GENERAR EL PROMEDIO DE UN RANGO DE CELDAS NUMERICO DEPENDIENDO DE UNA CONDICION DADA

CANTIDAD DE ESTUDIANTES POR EVALUACION - CONTAR.SI

EVALUACION CANTIDAD

SOBRESALIENTE

EXCELENTE

ACEPTABLE

INSUFICIENTE

Crea las dos

tablas debajo de

la anterior



Excel Avanzado 13

PROMEDIO DE NOTAS POR EVALUACION – PROMEDIO.SI

EVALUACION PROMEDIO

SOBRESALIENTE

EXCELENTE

ACEPTABLE

INSUFICIENTE

SUMAR.SI : SE UTILIZA PARA SUMAR SÓLO LOS VALORES DE UN RANGO DE CELDAS QUE CUMPLEN CON UN DETERMINADO CRITERIO

TAREA – PRACTICA: Realizar los talleres correspondientes a los temas aprendidos en clase, descargarlos de la plataforma de Moodle


1.2.2. Funciones de Búsqueda en Excel: Permite buscar información en una lista o en una tabla; en ellas se utilizan los siguientes argumentos:

Valor Buscado: Valor o cadena a buscar. Puede ser un valor, una referencia a una celda o un texto delimitado por comillas dobles.

Matriz de comparación o búsqueda: Rango que contiene los valores que se van a comparar con el argumento Valor_buscado. Puede ser una referencia a un rango de celdas o a un rango con nombre.

Indicador o número de columna o fila: Indica en qué fila o columna de la tabla se debe extraer el valor. Debe ser mayor o igual que 1, pero siempre debe ser menor o igual que el número de filas o columnas que tenga la tabla.

1.2.2.1. CONSULTAH y CONSULTAV: Permiten buscar información en tablas. CONSULTAH busca en tablas horizontales mientras CONSULTAV lo hace en tablas verticales. Estas tablas deben estar ordenadas. Primero la función busca en la fila superior del rango de la matriz de comparación, BUACARH, o en la columna de la izquierda del rango de la matriz de comparación, CONSULTAV, hasta encontrar un valor igual al buscado. Luego en esa fila o en esa columna Excel extrae el contenido de la celda situada en el argumento indicador.



Informática II 14

Los formatos o sintaxis de estas dos funciones son:

=CONSULTAH(Valor_buscado;Matriz_buscar_en;Indicador_filas;ordenado) =CONSULTAV(Valor_buscado;Matriz_buscar_en;Indicador_columnas;ordenado)

1.2.2.2. Buscar: Tiene dos formatos; ambos opera similar a las funciones anteriormente vistas: miremos el primer formato =BUSCAR(Valor_buscado; Vector_de_comparación; Vector_resultado); El argumento Vector_resultado es el rango que va a contener los posibles resultados. Puede ser o no una matriz paralela al Vector_de_comparación. El Vector_de_comparación se presta para hallar el valor buscado; si no se encuentra dicho valor, el sistema utiliza el mayor valor que sea menor al buscado. Luego, Excel devuelve el contenido de la celda situada en el Vector_resultado. El segundo formato es =BUSCAR(Valor_buscado; Matriz) en donde matriz es el rango en el que se busca: el resultado se extrae de la última fila o columna. Si por alguna razón la matriz o tabla resulta ser más ancha que alta, Excel trata la tabla como una tabla horizontal; en caso contrario, Excel la toma como una tabla vertical.


ACTIVIDAD:

Nuestra empresa, dedicada la distribución y venta de bebidas refrescantes, ha decidido (como método de promoción y vía de investigación de mercado) premiar a aquellos consumidores que envíen las etiquetas de los refrescos de dos litros a un determinado apartado aéreo.

Abrir un nuevo Libro de Excel y llamarlo PromociónPremios. A la Hoja1 se dará el nombre de Buscar.

La tabla de premios, que se copiará en el rango A1:B5 de la hoja Buscar, es la siguiente:

Nº de puntos Premio

500 Una camiseta y un Maletín deportivo

1000 Un walkman con auriculares

2000 Un Equipo de Sonido

4000 Un Computador de Escritorio

Al cabo de un mes se elabora la lista de los primeros ganadores, incluyendo los puntos obtenidos por cada uno. La lista ocupará el rango A7:C13 de la hoja



Excel Avanzado 15

Buscar. En la 3ª columna deberá aparecer el premio que corresponda a cada ganador: (ver imagen)

Ganador Nº de

puntos

Premio

Antonio

Fernández

600

Catalina Lago

Herrera

1200

Roberto Suárez

Vega

900

Luis Ferrer

Manotas

2100

Ana Sánchez

Torres

500

José Alonso

Parra Oliver

4050

Se trata de modificar dicha lista de modo que el premio conseguido por cada ganador aparezca automáticamente en la tercera columna sólo con introducir el nº de puntos obtenido.

DESARROLLO

Para esto será necesario recurrir a la función BUSCAR. Esta función busca la correspondencia con el valor de una tabla en otra tabla matriz. Es útil siempre que en la segunda tabla sólo haya una correspondencia para cada valor; en nuestro caso, a cada nº de puntos corresponde un solo premio.

Una vez copiadas las tablas indicadas más arriba, situarse en la celda C8. Activar el asistente para funciones y seleccionar, en Categorías de funciones, Búsqueda y referencia, y en “Nombre de la función”, la función BUSCAR. En el cuadro de diálogo "Seleccionar argumentos" seleccionar los argumentos

"valor_buscado;matriz".

En el argumento "valor_buscado", seleccionar la celda B8 (que contiene el nº de puntos obtenido por el ganador).



Informática II 16

En el argumento "matriz", seleccionar el rango de celdas A2:B5 (donde se establecen las correspondencias de nº de puntos con premios) y pulsa la tecla F4 (fijar celdas absolutas).

Oprimir Enter y en la celda C8 aparecerá el premio correspondiente. Cuando la función no encuentra en la matriz seleccionada ningún valor coincidente con el que hemos introducido, selecciona el que más se le aproxima por debajo (por ejemplo, considerará que lo más aproximado a 600 es 500).

Para poder copiar hacia abajo de la tabla, en la función el rango de celdas A2 a B5 de la matriz es necesario convertir el rango de celdas relativas en una referencia absoluta; por tanto, se deberá modificar la fórmula para que quede de la siguiente manera:

=BUSCAR(B8;$A$2:$B$5).

Una vez modificada la fórmula, cópiala a las celdas C9 a C13. Observa cómo aparecen automáticamente los premios correspondientes a cada persona.

Aplicación de las Funciones CONSULTAV y

CONSULTAH

Estas funciones son necesarias en aquellos casos en que la matriz o tabla en la que realizamos la búsqueda tiene más de 2 columnas (o filas). En tales casos, se debe indicar en qué columna (CONSULTAV) o fila (CONSULTAH) se debe buscar la correspondencia que queremos.

Función CONSULTAV

Supongamos que en el ejercicio anterior, en la tabla de Premios se incluyen los datos relativos a tres promociones diferentes:

Aprovechando los nombres de antes y el nº de puntos, supondremos que, en lugar de participar en la promoción 1 lo han hecho en la promoción 2.

Nº de

puntos

Premios prom1 Premios prom2 Premios prom3

500 Una camiseta y un Maletín

deportivo Una entrada para el cine

Una suscripción a la revista

"Enter"

1000 Un walkman con

auriculares

Una entrada para el

teatro

El libro "Programación

Avanzada en Java"

2000 Un Equipo de Sonido Una entrada para el

fútbol

Una vajilla completa

4000 Un Computador de

Escritorio

Una entrada para la

ópera

Un viaje a París para dos

personas



Excel Avanzado 17

1. Ubicarnos en la Hoja2 del Libro activo, y colocarle el nombre

CONSULTAV.

2. Los datos se dispondrán del mismo modo que en el ejercicio anterior.:

por tanto, copiar el contenido de la hoja Buscar en la hoja

CONSULTAV. Luego, añadir las columnas Premios prom2 y Premios

prom3 en el rango C9:D13 de la hoja CONSULTAV. A continuación,

borrar el contenido del rango C2:C7 (columna Premio).

3. Situarse en la celda C2 y activar el asistente para funciones.

4. En Categorías de funciones, selecciona Búsqueda y referencia. En

Nombre de la función, selecciona CONSULTAV

5. En el argumento Valor_buscado, seleccionar la celda B2.

6. En el argumento Matriz_buscar_en, seleccionar el rango A10:D13

7. En el argumento Indicador_columnas, escribir 3 (es decir, la tercera

columna de la matriz)

8. En el argumento Ordenado, no es necesario que se introduzca nada

9. Pulsa Enter.

10. Una vez más, para poder copiar la fórmula a las celdas contiguas será

necesario convertir la referencia a la matriz en una referencia absoluta

(o mixta) del modo ya visto antes.

Función CONSULTAH

Funciona del mismo modo y en los mismos casos que CONSULTAV. La diferencia radica en que CONSULTAH se utiliza cuando los datos de la matriz están dispuestos de forma horizontal.

EJEMPLO:

Dar el nombre de CONSULTAH a la Hoja 3. Copiar la tabla de Premios situada en el rango A9:D13 de la hoja CONSULTAV a la hoja CONSULTAH, de forma que los datos se dispongan en horizontal y no en vertical. Para ello, sigue los siguientes pasos:



Informática II 18

Seleccionar el rango A9:D13 de la hoja CONSULTAV y

pulsar el botón Copiar

Cambiar a la hoja CONSULTAH haciendo clic en su

pestaña. Situarse en la celda A9.

Seleccionar la pestaña Inicio, luego se da clic en La

herramienta Pegar ubicada en la parte superior izquierda del

libro de trabajo; se elige a continuación la opción Transponer

la cual aparece en el menú emergente.

Pulsar la tecla Esc para que desaparezca el borde intermitente

alrededor de las celdas copiadas.

En el rango de celdas A1:C7 de la hoja CONSULTAH, copiar la tabla situada en estas mismas celdas de la hoja CONSULTAV. Borrar el contenido del rango C2:C7 (columna Premio)

Situarse en la celda C2 y activar el asistente para funciones; seleccionar la función CONSULTAH. A continuación, proceder de la misma forma que con CONSULTAV cambiando la matriz a seleccionar, que será B9:E12 (en realidad, $B$9:$E$12).

El argumento “Ordenado” en la función CONSULTAV

Como ya se ha visto, el cuarto argumento de la función CONSULTAV es el de Ordenado. En este argumento no es necesario introducir nada siempre que la tabla de correspondencias en la que se realiza la búsqueda esté ordenada en sentido ascendente (en función del valor de la primera columna; por ejemplo, en la tabla de correspondencias de premios en la actividad anterior).

Hay casos, sin embargo, en que la tabla en la que se realizará la búsqueda no está ordenada de esta forma. En tales casos, es necesario introducir como cuarto argumento de la función la palabra FALSO o colocar el número 0 (con lo que se indica al programa que la tabla en cuestión no está ordenada). EJEMPLO 4:

Inserta una nueva hoja (Hoja 4) en el Libro Premios y llamarla Pedido. Crear en ella el siguiente modelo de pedido (rango A1:D15):



Excel Avanzado 19



Informática II 20

En la misma hoja, más abajo, crea la siguiente tabla de correspondencias: A continuación, en las celdas del modelo de pedido correspondientes a los datos de Destinatario, Forma envío, Forma pago, Plazo entrega y Lugar entrega introduce funciones CONSULTAV de forma que al escribir el código del destinatario aparezcan automáticamente los datos correspondientes a dicho código.

En este caso, dado que la tabla de correspondencias no está ordenada, deberás hacer

uso del 4º argumento de la función, tal como se ha explicado más arriba.

EDH (El Estudiante Dice Y Hace)

Abra un libro nuevo y en la hoja 1 crea la siguiente tabla, de la gráfica de

abajo y después realice lo siguiente:

A las siguientes notas asignar el concepto (columna C) teniendo en cuenta

la siguiente

información:

Entre 1 y 4

INSUFICIENTE Entre 5 Y 7

REGULAR

Entre 8 y 10

EXCELENTE

En la hoja 2 del mismo libro

crea la siguiente tabla, de la

gráfica de al lado y después

realice lo siguiente:

En la columna B (SUMA)

Para los valores iguales a 100,

asignar el resultado de la



Excel Avanzado 21

sumatoria de la columna valores a los demás dejar la celda en blanco.

Recuerda anclar el rango de sumatoria

En la hoja 3 del mismo libro crea la siguiente tabla, de la gráfica de abajo

y después realice lo siguiente

Coloque todos los datos del carnet del cliente solamente digitando el

código de la persona respectiva.

Descarga los ejercicios propuestos y realiza los ejercicios anexos al módulo guía de la unidad

Valoración de Evidencias:



Informática II 22



1. Establece y aplica el uso de filtros sencillos y avanzados en tablas, configura y aplica resúmenes por agrupaciones de registros, además diseña y realiza tablas dinámicas y gráficos dinámicos


Crear filtros avanzados y autofiltros de manera eficiente y efectiva

Utilizar de manera eficaz y eficiente la potencialidad de la función Subtotal para resumir y agrupar datos contenidos en una tabla

Aplicar técnicas avanzadas para combinar diferentes funciones de agrupación en un mismo subtotal en una Tabla de datos

Diseñar adecuadamente tablas y gráficos dinámicos para resumir y organizar información contenida en

una tabla

Evidencias Producto Desempeño Desempeño

Producto

Filtrar información



Excel Avanzado


2.1. Filtrar información, establecer Subtotales, Tablas dinámicas y Gráficos dinámicos

2.2. Tabla de Datos

Hasta ahora hemos usado Excel básicamente para realizar cálculos con datos numéricos mantenidos en celdas de las hojas.

En Excel también podemos trabajar con Tabla de datos. Las Tabla de datos contienen tablas; las filas son los registros; las columnas se corresponden con los campos de los registros. Las capacidades de Excel para el manejo de Tabla de datos son mucho más limitadas que las de Microsoft Access, ya que Access es una aplicación diseñada específicamente para gestionar Tabla de datos. Sin embargo, si las necesidades de gestión de la Tabla de datos son sencillas, a menudo Excel puede servir perfectamente. Si las posibilidades de Excel se quedan cortas, entonces habrá que recurrir a Access u otro sistema de gestión de Tabla de datos. Las tablas normalmente se interpretan como Tabla. Una lista es una serie etiquetada de filas de hoja de cálculo que contienen datos relacionados, como una Tabla de datos de facturas o un conjunto de nombres y números de teléfonos de clientes, en que las filas corresponden a los registros y las columnas a los campos. La primera fila de la lista tiene los rótulos de columna

Todos los registros tienen la misma estructura. La estructura viene dada por un conjunto de campos, las columnas de la tabla. Cada campo es un dato del registro. Cada registro está compuesto por los datos de los campos que tiene definidos.

Registro Cu

Campos



Informática II 24


Trabajaremos con una lista de ventas de insumos para computador:

Los nombres de campos ocupando una sola celda. Sin filas en blanco. La primera fila en blanco indica el final de la lista. Se debe dejar una fila en blanco antes de los totales, promedios, etc. Excel sólo permite trabajar con Tabla de datos muy simples. Para muchas ocasiones será suficiente, pero para Tabla de datos más completas, complejas y profesionales no resultará adecuado. La tabla de la Tabla de datos de ejemplo establece seis campos: Fecha, Nombre (del cliente), Apellidos (del cliente), Artículo, Precio y Unidades. Hay 25 registros.

2.2.1. Operaciones Básicas sobre una Tabla de datos

Dos son las operaciones básicas que debemos poder realizar con la Tabla de datos:

Organizar- (ordenar) los registros por medio de un campo determinado. Seleccionar - (filtrar) los registros dependiendo de una condición dada con

el fin de localizar cierta información en una lista o Tabla de datos fácilmente.



Excel Avanzado 25

2.2.1.1. Ordenación de la Tabla de datos

Para ordenar la Tabla de datos primero debemos tener seleccionada la columna de la tabla a aplicar. Luego elegimos la herramienta Ordenar y filtrar que se encuentra seleccionando la pestaña o etiqueta Inicio, Luego seleccionamos la forma de ordenamiento deseada (ascendente es de A a la Z y descendente Z a la A) y en este momento Excel ordena automáticamente todos los registros de la Tabla de datos. Si quisiéramos ordenar de manera personalizada, se debe seleccionar la opción Orden personalizado. Aparece el cuadro de diálogo Ordenar. Excel detecta la fila de encabezamiento (nombres de los campos) y permite entonces seleccionar las columnas por los nombres de los campos. Recuerde que: Para ordenar una tabla por uno o más campos se puede seleccionar el campo sobre el cual se va a realizar la ordenación o seleccionar la tabla completa.

Ordenar

O

rden Filtrar

O

rden



Informática II 26

También podemos ordenar una tabla por columnas en función del contenido de las filas, en esta opción podemos ordenar los rótulos de columna en un orden deseado ya sea ascendente o descendente.

1. Sobre la tabla completa 2. Da clic en la icono Ordenar y Filtrar, luego clic en Orden personalizado 3. Luego da clic en el botón

de Opciones. 4. Luego en la opción

Ordenar de izquierda a derecha y Aceptar

5. Luego escoge el número de la fila que contiene los rótulos de columnas

6. Luego selecciona Asc. o Desc. y Aceptar

En la gráfica, se estaría ordenando por el nombre de la persona, pero se podría ordenar por apellido, artículo, precio, unidades, etc.

En la gráfica superior observamos que podríamos ordenar al mismo tiempo por tres campos diferentes, dependiendo de nuestras necesidades.



Excel Avanzado 27

2.2.1.2. Filtrado de la Tabla de datos

Siempre es más fácil localizar un dato en un pequeño grupo de registros que en una interminable lista. Los filtros permiten seleccionar los registros de una tabla que cumplan con ciertas condiciones. Cuando el filtro se aplica sólo se verán en la tabla esos registros. (Por supuesto, el resto de registros siguen estando ahí, aunque ocultos.) Existen dos tipos de Filtrado de datos en Excel: Auto filtros o filtros automáticos. Filtros Avanzados

2.2.1.3. Autofiltro:

Los filtros automáticos se aplican dando clic en la herramienta filtro de la pestaña o etiqueta datos (botón correspondiente de la barra de herramientas). Al dar clic en dicha herramienta Excel muestra al extremo derecho de cada uno de los encabezados, un botón que permite desplegar una serie de opciones, como se

observa en el gráfico de la parte inferior.



Informática II 28

El usuario, dependiendo de sus necesidades da clic en el botón del encabezado por el que desea filtrar y luego escogerá la opción que supla sus requerimientos según sea el caso. Por ejemplo, de manera interactiva, Excel muestra en el menú, la opción Filtros de texto, la cual despliega un submenú con opciones como: Es igual a, No es igual a, Comienza por, Termina con, etc. permitiendo al usuario seleccionar la alternativa óptima. Las Tabla permiten aplicar rápidamente otros filtros: Los filtros se acumulan, de forma que si estando aplicado el filtro de Apellidos seleccionamos algún Artículo, sólo se verán los registros con esos apellidos y ese artículo.

También podemos personalizar filtros, seleccionando la opción filtros de texto, y en el submenú que aparece en pantalla dar clic en la opción filtro personalizado

Aparecerá una imagen como la que observamos en la parte inferior, en donde el usuario editará las opciones según los requerimientos o necesidades.



Excel Avanzado 29

2.2.1.4. Filtro avanzado: El Filtro avanzado de Excel permite seleccionar registros por medio de condiciones más complejas. Las condiciones se colocan en otras celdas de la hoja de cálculo, de forma que tenemos mucha más flexibilidad. Para crear las condiciones primero copiamos la fila de nombres de campos en otro lugar de la hoja:

En las filas siguientes se escribirán las condiciones. Cuando los criterios se ubican en filas alternativas, se expresa de manera implícita el conector lógico (O). Mientras si se ubican dichos criterios en la fila siguiente a los nombres de los campos se expresa implícitamente el conector lógico (Y).

Por ejemplo con los criterios de selección expresados en la imagen anterior observamos que se mostraran los registros en donde el cliente corresponda a Paola Annear o cuyo artículo vendido sea una Lavadora LG.



Informática II 30

Al final damos clic en el botón aceptar y obtendremos los datos que aparecen en la siguiente imagen:

2.2.2. Subtotales: Se pueden crear subtotales (sumas, cuentas, promedios, etc.) para los campos de la Tabla de datos, pero primero tenemos que ordenar la tabla por el campo sobre el que queramos obtener los totales por ejemplo el campo de fecha, como observamos en la siguiente tabla.

Luego Aparece el siguiente cuadro de texto en donde el usuario selecciona las opciones

deseadas, según sus necesidades.

Para aplicar el filtro avanzado se elige la Pestaña o etiqueta (ficha) Datos, y luego en las herramientas de filtros se

da clic en la opción Avanzadas

Rango de criterios: es el rango donde se encuentran los criterios de selección (condiciones) que determinan los registros a

mostrar.

Copiar a: es el rango donde se mostraran los

datos filtrados.

Rango de lista: determina por defecto el rango de la tabla donde se encuentran los datos a filtrar.



Excel Avanzado 31

Recuerde que: Ordenar es organizar los registros con un orden determinado aplicándolo a un campo específico.



Informática II 32

Al final tendremos un resultado similar al que observamos en la gráfica siguiente, dependiendo de las opciones editadas por el usuario.

Luego seleccionamos la etiqueta o pestaña (ficha) Datos, ubicamos y damos clic en la opción

Subtotal.

Aparece El cuadro de diálogo en donde el usuario selecciona y escoge las opciones

deseadas según sus requerimientos.



Excel Avanzado 33


2.2.2.1. Subtotales Anidados: Ahora si queremos anexar una función de agrupación -Promedio- para

generar la media de unidades vendidas por

fecha, tenemos que realizar lo siguiente:

Dele un solo clic dentro de la tabla con los

subtotales

Vaya a la Barra de Opciones y seleccione Menú Datos- Subtotal En la Opción Usar Función: Seleccionar PROMEDIO Luego DESACTIVAR el check de la casilla: REEMPLAZAR SUBTOTALES ACTUALES Luego dar clic en Aceptar

Ahora ya Ud. podrá navegar por el informe

observando las ventas agrupadas por fecha



Informática II 34

utilizando las funciones: suma y promedio anidadas en una misma tabla

subtotalizada.


2.2.3. Tablas Dinámicas: Se utiliza para analizar los datos de la Tabla de datos por dos campos a la vez. La tabla dinámica se construye con un campo para las filas y otro campo para las columnas. En la intersección irán los valores de los campos numéricos correspondientes. ¿Qué son las TABLAS DINÁMICAS?

Una Tabla Dinámica es una tabla interactiva que resume, o ejecuta una comprobación cruzada, de grandes volúmenes de datos. Se puede crear a partir de una lista o Tabla de datos de Excel, de una Tabla de datos externa, de varias hojas de datos de Excel o de otra tabla dinámica. La tabla dinámica resume los datos utilizando la función de resumen que se especifique, como SUMA, CONTAR O PROMEDIO. Podrá incluir automáticamente subtotales y totales generales o utilizar sus propias formulas agregando campos y elementos calculados. Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utilice un informe de tabla dinámica para analizar datos numéricos en profundidad y para responder preguntas no anticipadas sobre los datos. Un informe de tabla dinámica está especialmente diseñado para: Consultar grandes cantidades de datos de muchas maneras diferentes y

cómodas para el usuario. Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y

subcategorías, y crear cálculos y fórmulas personalizados. Expandir y contraer niveles de datos para destacar los resultados y

desplazarse hacia abajo para ver los detalles de los datos de resumen de las áreas de interés.

Desplazar filas a columnas y columnas a filas para ver resúmenes diferentes de

los datos de origen.



Excel Avanzado 35

Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para poder centrarse en la información que le interesa.

Presentar informes electrónicos o impresos concisos, atractivos y con

comentarios.

Crear informes dinámicos a partir de una consolidación múltiple de rangos

2.2.3.1. Partes de una Tabla Dinámica


Aquí se verá un par de aplicaciones sencillas con pocos datos de una lista Excel, para ilustrar uno de los posibles usos de esta herramienta. 1. Se ilustrará el uso de Tabla Dinámica mediante el siguiente caso: Se dispone de

información de notas de estudiantes en 3 talleres, pero en forma de lista simple, como se muestra a continuación:

Campo de Filtro

Campo de Columna Campo de Fila

Campo de Valores

Área de Valores



Informática II 36

Se desea presentar en una nueva tabla la información de notas pero ordenada por estudiantes, con las notas de cada uno en los 3 talleres sus promedios. También se desea obtener el promedio del grupo de estudiantes en cada tarea. Los promedios individuales y grupales se deben presentar con un decimal. Pues bien, todo ello se puede obtener fácilmente mediante una Tabla Dinámica.

2. Hacer clic en

cualquier celda de la tabla de datos de origen que no esté vacía. Seleccionamos la pestaña o etiqueta (ficha) insertar. Luego damos clic sobre la herramienta tabla dinámica y en el menú desplegado seleccionamos la opción tabla dinámica que presentará el primer paso del Asistente.

TAREAS ESTUDIANTES NOTAS Taller 1 Pedro González 4.5

Taller 2 Martha Osorio 3.3

Taller 1 Hernán Casas 4.6

Taller 3 Nubia Miranda 4.7

Taller 2 Pedro González 3.5


Taller 3 Pedro González 4.8








Excel Avanzado 37

3. En el cuadro de diálogo que se

abrirá en el paso 2, aceptar las sugerencias del programa en Rango, (en este caso, Ejemplo1!$B$3:$D$15, que indica el área completa de los datos originales, usando referencias absolutas para las celdas inicial y final). Luego seleccionamos el rango donde se ubicará la tabla dinámica, en nuestro caso (Ejemplo1!$B$17) y damos clic en el botón aceptar.

4. En el paso siguiente

se abre un nuevo panel a la derecha en el cual nos corresponde indicar a Excel cómo se desea presentar la información a seleccionar para la tabla dinámica. En nuestro caso, podríamos utilizar una columna para cada taller, una fila para cada estudiante, y en valores colocaríamos las notas.



Informática II 38

Para ello se deben arrastrar con el mouse los campos a las áreas respectivas, ubicadas en la parte inferior del panel de tareas. En nuestro caso arrastrar el campo ESTUDIANTES al área FILA; el campo TALLERES debe ir en COLUMNA y el botón NOTAS irá en el área VALORES.

5. La tabla dinámica asume por defecto que la operación a realizar con

los datos numéricos es una suma. Sin embargo, si lo que se necesita es el PROMEDIO de las notas, basta hacer clic en la opción Suma de NOTAS, para acceder a otras operaciones posibles.

CAMPOS

Área Columnas

Área Filas Área Valores

Luego se debe seleccionar la opción Configuración de campo de valor la cual está ubicada en el menú que aparece en

pantalla



Excel Avanzado 39

Para que los promedios se presenten con 1 decimal, hacer clic en el botón Formato de Numero. En el cuadro de diálogo Formato de celdas, elegir la opción Número y en Posiciones decimales bajar el indicador a 1 y Aceptar.

Al final podremos observar en la posición indicada una tabla como se observa a continuación:

Luego seleccionamos en el cuadro de diálogo que aparece en pantalla, la opción Promedio ya que es lo requerido por la

aplicación.



Informática II 40


2.2.3.2. Utilizar un campo de Filtro en Tabla Dinámica A veces por comodidad a la hora de trabajar con datos de una tabla de datos, resulta conveniente utilizar un campo de filtro de informe. Un campo de filtro de informe le permitirá cambiar los grupos de valores que se muestran en la tabla mediante un filtro. 1. Se ilustrará el uso de un campo de filtro en una Tabla Dinámica mediante el siguiente

caso: Se dispone de información de empleados con sus datos personales, pero en forma de lista simple, como se muestra a continuación:

NOMBRE DEPARTAMENTO CIUDAD EDAD OCUPACION FECHA

INGRESO SALARIO

María Antioquia Medellín 47 Administrativo 04/03/2000 875.000

Manuel Atlántico Barranquilla 40 Gerente 05/07/2004 3.500.000

Eduardo Bolívar Cartagena 42 Administrativo 20/05/2000 3.500.000

Esther Caldas Manizales 30 Abogado 08/09/2004 2.500.000

Pilar Cundinamarca Bogotá 42 Abogado 11/05/2009 2.500.000

Alfonso Nariño Cali 47 Vendedor 29/05/2006 2.400.000



Excel Avanzado 41

2. Construya una tabla dinámica que muestre la suma de ingresos por Departamentos, Ciudades y Ocupación , pero el campo Departamento lo utilizaremos como campo de filtro

3. Primero que todo sombrea toda la tabla desde los rótulos de columna hasta la última fila de datos.

4. Ve al menú Insertar y luego selecciona Tabla dinámica y otra vez Tabla dinámica 5. Coloque la tabla en una hoja nueva y luego de clic en Siguiente. 6. Luego aparece una ventana a la izquierda con los nombres de los campos y las

áreas de la tabla dinámica. 7. Luego seleccione el campo Departamento y arrástrelo hasta dentro del área de Filtro

de informe. 8. Luego seleccione el campo Ciudad y arrástrelo hasta dentro del área de Rótulos de

fila. 9. Luego seleccione el campo Ocupación y arrástrelo hasta dentro del área de Rótulos

de columna. 10. Luego seleccione el campo Salario y arrástrelo hasta dentro del área de Valores Luego la tabla dinámica mostrará esta imagen de abajo:



Informática II 42

11. Luego dele clic en el área de valores y clic derecho y seleccione Configuración de campo de valor 12. Luego da clic en la pestaña Mostrar valores como y seleccione el ítem deseado.

2.2.3.3. Mostrar Valores Como:

% DE FILA : A continuación veremos qué porcentaje (%) representa cada uno de las ocupaciones por Departamento y a nivel Total (en cuanto a Salarios)

11

11

1



Excel Avanzado 43

% DE COLUMNA : A continuación veremos qué porcentaje (%) representa cada uno de las ciudades por ocupación y a nivel Total (en cuanto a Salarios)

% DEL TOTAL :

A continuación veremos la unión de porcentajes (%) de participación tanto de filas como de columnas (con respecto a Salarios)



Informática II 44

2.2.3.4. Formas de trabajar con un informe de tabla dinámica: Después de crear el informe de tabla dinámica inicial definiendo el origen de los datos, organizando los campos en la Lista de campos de la tabla dinámica y eligiendo un diseño inicial, a menudo se llevan a cabo las tareas siguientes cuando se trabaja con un informe de tabla dinámica: Explorar los datos haciendo lo siguiente:

Expandir y contraer los datos, y mostrar los detalles subyacentes de valores. Organizar, filtrar y agrupar los campos y los elementos. Cambiar las funciones de resumen y agregar cálculos y fórmulas personalizados.

Cambiar el diseño haciendo lo siguiente:

Cambiar la forma de la tabla dinámica: compacta, de esquema o tabular. Mostrar los subtotales encima o debajo de sus filas. Mover un campo de columna al área de filas, o mover un campo de fila al área de

rótulos de columnas. Cambiar cómo se muestran lo errores y las celdas vacías, y cambiar cómo se muestran los elementos y los rótulos sin datos.

Cambiar el orden de los campos y los elementos y agregar, reorganizar y eliminar campos.

Ajustar el ancho de las columnas al actualizar. Activar o desactivar los encabezados de campo de columnas y filas, o bien mostrar u

ocultar líneas en blanco. Cambiar el formato haciendo lo siguiente:

Dar formato a las celdas y rangos manual y condicionalmente. Cambiar el estilo de formato general de la tabla dinámica. Cambiar el número de formato para campos e incluir el formato de Servidor OLAP.



Excel Avanzado 45

A continuación, mostramos las diferentes barras de herramientas con las cuales se pueden realizar los procedimientos anteriormente mencionados

Diseño de Tabla Dinámica

Me permite no mostrar subtotales, mostrarlos en la parte inferior o en la parte superior

Me permite activar o desactivar los totales generales tanto para filas como para columnas

Mostrar la tabla en forma compacta, esquema o tabular

Para insertar o quitar líneas en blanco después de cada elemento

Me permite modificar es estilo de la tabla dinámica en todos sus aspectos



Informática II 46

Opciones de Tabla Dinámica

2.2.3.5. Informes de Gráfico Dinámico Un informe de gráfico dinámico representa gráficamente los datos de una tabla dinámica. Puede modificar el diseño y los datos que se muestran en un informe de gráfico dinámico tal y como se hace con un informe de tabla dinámica. Un informe de gráfico dinámico

Opciones que me permiten modificar el formato del campo activo y las opciones de la tabla dinámica.

Opciones que me permiten: agrupar, desagrupar y ordenar los elementos de la tabla dinámica

Opciones que permiten Actualizar la tabla dinámica, cuando la tabla origen ha sido modificada y además me permite cambiar el origen de datos o la tabla origen si es requerido.

Estas opciones me permiten borrar y seleccionar elementos de la tabla dinámica; generar un grafico dinámico basado en la tabla dinámica, mover a otra ubicación dicha tabla; ocultar o mostrar encabezados de campo y botones para expandir y contraer elementos de la tabla dinámica.

Esta opción se utiliza para crear

campos o elementos calculados



Excel Avanzado 47

siempre tiene un informe de tabla dinámica asociado (informe de tabla dinámica asociado: informe de tabla dinámica que proporciona los datos de origen a un informe de gráfico dinámico. Se crea automáticamente cuando se crea un nuevo informe de gráfico dinámico. Cuando se cambia el diseño de alguno de los informes, el otro cambia también) que utiliza el diseño correspondiente. Ambos informes tienen campos que se corresponden. Cuando modifica la posición de un campo en uno de los informes, también se modifica el campo correspondiente del otro informe. Los informes de gráfico dinámico funcionan de manera similar a un informe de tabla dinámica; su diferencia radica en la forma de presentar los datos. La tabla dinámica, muestra los datos de manera tabular mientras que el gráfico dinámico muestra la información de manera gráfica. Pasos:

1. Dar clic en el menú de Herramientas de tablas dinámicas

2. Luego dar clic en el icono de crear gráfico dinámico 3. Luego escoger tipo de gráfico a presentar y Aceptar 4. Luego aparece un Panel donde puedes filtrar el campo

especificado en la tabla dinámica para poder graficarlo 5. Por último vemos el gráfico dinámico de forma automática con los datos

seleccionados.

4



Informática II 48


Ejercicio Uno Crea una Tabla de datos de facturas para una tienda de electrodomésticos. Cada registro corresponderá a un electrodoméstico comprado por un cliente un determinado día. Como un cliente puede comprar varios electrodomésticos ese día, a cada factura le pueden corresponder varios registros. Los campos serán los siguientes: Número (de factura): año seguido de un guión y de un número entero. Fecha. Cliente: nombre y apellidos del cliente. Artículo. Precio. Unidades. Subtotal, IVA (16%) y Total (campos calculados).

Realiza los ejercicios anexos al módulo guía de la unidad Introduce estos datos en la Tabla de datos:



Excel Avanzado 49

Una vez creada la Tabla de datos, ordénalas de la siguiente forma: Por cliente y para cada cliente por artículo (en ambos casos de menor a mayor). Por artículo, por cliente y por fecha (de más reciente a más antiguo). Por número (de mayor a menor), por cliente y por artículo. Por total, por artículo y por fecha. Para la Tabla de datos de la tienda de electrodomésticos crea los filtros para obtener la siguiente información: Facturas en las que se vendió una "Nevera 19Pies LG". La factura 2008-4 (lo que se vendió). Las ventas individuales del "Microondas" (ventas de sólo una unidad). Las cinco ventas de mayor precio. Las ventas realizadas entre el 5 y el 10 de Mayo, de artículos de la marca LG. Las ventas realizadas entre el 10 y el 20 de Mayo, de artículos que no sean LG. Las ventas Hechas a Andrés Saldaña o a Nubia Saldaña. Las ventas Televisores de 21" LG entre el 5 y el 10 de Mayo.

Ejercicio Dos Crea estos filtros avanzados en la Tabla de datos de la tienda de electrodomésticos: Compras de Andrés Saldaña superiores a $ 200.000.oo y compras de más de una

unidad con un total mayor de $ 400.000.oo. Ventas de electrodomésticos de la marca LG con un valor total superior a $

1.000.000.oo y ventas de Microondas. Ventas con artículos que contengan la letra M, de menos de $ 300.000.oo, una sola

unidad y de alguna Ana o de alguna Nubia.

¿Cuáles de estos filtros se pueden crear con Autofiltro? ¡Inténtalo!


Ejercicio Tres Para la Tabla de datos de la papelería La Económica crea los siguientes listados o subtotales: Ventas por cliente y por fecha para cada cliente (apellidos), con subtotales por cliente

y por fecha.



Informática II 50

Ventas de cada artículo (orden alfabético) y para cada artículo de cada cliente, con subtotales por artículo y por cliente.

Ventas por cliente y por fecha para cada cliente, con subtotales por cliente y por fecha. Sólo de septiembre.

Ventas de cada artículo (orden alfabético) y para cada artículo de cada cliente, con subtotales por artículo y por cliente. Sólo ventas con un total inferior a $ 600.000.oo.

Los subtotales sólo para el total de la venta. Para la Tabla de datos de la tienda de electrodomésticos crea los siguientes listados o subtotales: Ventas por fecha y por cliente para cada fecha, con subtotales por fecha y por cliente. Ventas por cliente y por fecha para cada cliente, con subtotales por cliente y por

fecha. Ventas de cada artículo (orden alfabético) y para cada artículo de cada fecha, con

subtotales por artículo y por fecha. Ventas de cada factura con subtotales. Ventas de cada artículo (orden alfabético) y para cada artículo de cada cliente, con

subtotales por artículo y por cliente. Los subtotales para los campos Subtotal, IVA y Total.


Ejercicio Cuatro Crear la siguiente tabla, en donde se involucra el tiempo en minutos que duró cada estudiante realizando el respectivo taller.

TAREAS ALUMNOS TIEMPO, min NOTAS Taller 1 Pedro González 25.5 4.5

Taller 2 Martha Osorio 40.0 3.3

Taller 1 Hernán Casas 22.5 4.6

Taller 3 Nubia Miranda 35.0 4.7

Taller 2 Pedro González 45.0 3.5


Taller 3 Pedro González 32.5 4.8








Excel Avanzado 51

Usar la herramienta de Tabla Dinámica para elaborar una tabla que muestre para cada Estudiante sus notas de los 3 talleres, su promedio y el tiempo total (en minutos) que cada uno empleó en hacer los 3 talleres. Los promedios individuales y grupales se deben dar con 1 decimal.

Ejercicio Cinco Crear una tabla dinámica de la Tabla de datos de la papelería La Económica. Juega también con las diferentes opciones para modificar la tabla dinámica.

Ejercicio Seis Crea una tabla dinámica para la Tabla de datos de la tienda de electrodomésticos. Juega con ella cambiando y añadiendo campos en el área de filas, en el área de columnas y en el área de valores. Crear el informe de gráfico dinámico respectivo.


Ahora ingrese a Internet y copia la siguiente dirección y realice los ejercicios propuestos correspondientes a esta unidad

Realiza los ejercicios anexos al módulo guía de la unidad .



Informática II 52



1. Aprende conceptos básicos y crea Macros automáticas, Presenta

proyecto final


Utilizar de manera eficaz y eficiente la potencialidad de la herramienta Macros para automatizar series de instrucciones recurrentes

Crear y asociar diferentes tipos de controles de tipo formularios en tablas Excel 2010

Define y crea funciones y procedimientos aplicados en macros con el objetivo de solucionar un requerimiento planteado por un usuario

Evidencias Conocimiento

Producto

Desempeño

Producto


3.1. Macros automáticas Las macros son elementos que sirven para simplificar una secuencia de instrucciones repetitivas durante una sesión de trabajo. La ejecución de macros puede realizarse al presionar un botón asignado a una barra de herramientas,

Macros automáticas



Excel Avanzado 53

seleccionar la opción correspondiente en un menú o pulsar una determinada combinación de teclas.

3.1.1. Crear una macro automáticamente

La forma más fácil de crear macros es crearlas mediante el grabador de macros del que dispone Excel.

Para grabar una macro debemos ir al menú Vista y seleccionar el submenú Macros y dentro de este submenú seleccionar la opción Grabar macro...

Luego en esta opción podemos encontrar las siguientes opciones:

Ver Macros... - Donde accedemos a un listado de las macros creadas en ese libro.

Usar referencias relativas - Con esta opción utilizaremos referencias relativas para que las macros se graben con acciones relativas a la celda inicial seleccionada.

Al seleccionar la opción Grabar macro..., lo primero que vemos es el cuadro de diálogo Grabar macro donde podemos dar un nombre a la macro (no está permitido insertar espacios en blanco en el nombre de la macro).

Podemos asignarle un Método abreviado: mediante la combinación de las tecla CTRL + "una tecla del teclado". El problema está en encontrar una combinación que no utilice ya Excel.

En Guardar macro en: podemos seleccionar guardar la macro en el libro activo, en el libro de macros personal o en otro libro.

En Descripción: podemos describir cuál es el cometido de la macro o cualquier otro dato que creamos conveniente.

Para comenzar la grabación de la macro pulsamos el botón Aceptar y a continuación, si nos fijamos en la barra de estado, encontraremos este botón en la barra de estado donde tenemos la opción de detener la grabación.

A partir de entonces debemos realizar las acciones que queramos grabar, Una



Informática II 54

vez concluidas las acciones que queremos grabar, presionamos sobre el botón Detener de la barra de estado, o accediendo al menú de Macros y haciendo clic en

.

Una vez creada una macro, la podremos ejecutar las veces que queramos.

Antes de dar la orden de ejecución de la macro, dependiendo del tipo de macro que sea, será necesario seleccionar o no las celdas que queramos queden afectadas por las acciones de la macro.

Por ejemplo, si hemos creado una macro que automáticamente da formato a las celdas seleccionadas, tendremos que seleccionar las celdas previamente antes de ejecutar la macro, otro ejemplo es si ejecutamos una macro de totalice y promedie el contenido de un rango de celdas se necesita colocar el cursor en el sitio exacto a generar las operaciones pertinentes.

Para ejecutar la macro debemos acceder al menú Ver Macros..., que se encuentra en el menú Macros del menú Vista, y nos aparece el cuadro de diálogo Macro como el que vemos en la imagen donde tenemos una lista con las macros creadas.

Debemos seleccionar la macro deseada y pulsar sobre el botón Ejecutar. Se cerrará el cuadro y se ejecutará la macro.

Los demás botones que aparecen en el cuadro son los siguientes:

Cancelar - Cierra el cuadro de diálogo sin realizar ninguna acción.

Paso a paso - Ejecuta la macro instrucción por instrucción abriendo el editor de programación de Visual Basic (VBE).

Modificar - Abre el editor de programación de Visual Basic para modificar el código de la macro.

Opciones - Abre otro cuadro de diálogo donde podemos modificar la tecla de método abreviado (combinación de teclas que provoca la ejecución de la macro sin necesidad de utilizar el menú) y la descripción de la macro.

Ejecutar una macro



Excel Avanzado 55

Eliminar – Borra de la lista la macro seleccionada.

3.1.2. Cómo asignar Macros a botones de control

Se puede asociar una macro a distintos controles(botones de comando, imágenes, etc..) o a la barra de herramientas de acceso rápido para dar al usuario un acceso amigable y rápido para ejecutar macros

1. ASOCIAR UNA MACRO A UN BOTÓN DE COMANDO

Haga clic en el botón del menú Programador (Se activa en las Opciones de Excel – luego opción Más frecuentes del Botón de Office)

Luego escoja botón de comando (controles de formulario) y lo dibújelo en cualquier celda de la hoja activa Luego haga clic con el botón secundario del mouse y seleccione Asignar macro. Luego seleccione el nombre de la macro de la lista y haga clic en aceptar 2. ASOCIAR UNA MACRO A UNA IMAGEN CONTROL ACTIVEX Haga clic en el botón del menú Programador

Luego escoja el botón imagen (controles ActiveX) y dibújela en cualquier celda de la hoja activa

Ahora si quiere cambiar las propiedades de la imagen da clic en (en particular colocar una nueva imagen por medio de la propiedad Picture). Haga clic con el botón derecho del mouse y seleccione la opción Ver código Luego dentro del procedimiento y el evento asociado a la imagen, escriba la instrucción Call seguida del nombre de la macro que desea ejecutar

Desactive el modo de diseño con un clic en el botón Modo Diseño del menú Programador Haga clic en la imagen: la macro Formato se ejecutará



Informática II 56

2. ASOCIAR UNA MACRO A UN ICONO DE LA BARRA DE ACCESO RÁPIDO Haga clic en el botón de Office, luego en el botón Opciones de Excel

Luego seleccione la categoría Personalizar en el menú de la izquierda

Luego en la lista desplegable de categorías (Comandos disponibles en), seleccione Macros, entonces aparecerán las macros disponibles en el recuadro de abajo

Luego seleccione una macro y haga clic en Agregar; la macro pasará al recuadro de la derecha

Luego haga clic en el botón Modificar para cambiar el icono actual de la macro

Luego de clic en el botón Aceptar

Ahora ya estará la macro con su icono asociado en la barra de acceso rápido, para poder ejecutarla tanta veces Ud. desee.



Excel Avanzado 57

3.1.3. Aplicar botones de tipo formulario

Un control no es más que un objeto que muestra datos, realiza acciones o se utiliza como decoración. Por ejemplo, podemos utilizar un botón de control de número para seleccionar un dato numérico, un botón de comando para ejecutar una macro, un cuadro combinado para mostrar datos mediante una lista definida o un botón de Barra de desplazamiento para seleccionar datos numéricos dependiendo de un valor mínimo y valor máximo.

Los siguientes son los tipos de controles más importantes:

Etiqueta sirve para visualizar un texto fijo, texto que escribiremos directamente dentro del control o en su propiedad Título. .

Cuadro combinado . Se utilizan para de un modo más rápido y fácil seleccionar un valor de una lista que recordar un valor para teclearlo. Para configurar su utilización Ud. Tiene que dar clic derecho – Formato de control y completar las opciones pertinentes

Grupo de opciones Se utiliza para presentar un conjunto limitado de alternativas. Un grupo de opciones hace fácil seleccionar un valor, ya que el usuario sólo tiene que hacer clic en el valor que desee. Sólo se puede seleccionar una opción del grupo a la vez.

Botón de opción , se suele utilizar para seleccionar una única opción de un grupo de opciones ya creado. Si el campo contiene el valor 1, el botón tendrá este

aspecto , sino, este otro .

Botón de comando . Un botón de comando permite ejecutar una acción con un simple clic, por ejemplo abrir otro formulario, borrar el registro, ejecutar una macro, etc. También tiene un asistente asociado que permite crear botones para realizar distintas acciones predefinidas.

Botón de casilla de verificación , se utiliza para seleccionar una opción o varias opciones por escoger, su resultado es verdadero (con un check) y negativo (sin la presencia de un check)

Botón de control de número y la Barra de desplazamiento , se utilizan para seleccionar un número dependiendo de un valor mínimo y máximo y un incremento



Informática II 58


Digite la tabla como se muestra en la gráfica de abajo y realice lo siguiente:

Ejercicio: Realizar las siguientes macros así:

1. Para formatear la letra a tipo: Broadway y el tamaño de letra a: 10 2. Para Totalizar los salarios de los vendedores 3. Asigne las macros a botones de Comando Desarrollo de la macro 1 de Formato Vaya al menú Vista – luego Macros Luego seleccionar Grabar macro… Luego en la casilla de nombre colocamos: FORMATO y aceptar Luego ir al menú de INICIO y seleccionar el tipo de letra: BROADWAY y en tamaño de letra: seleccionar: 10 Por último le damos clic en el icono de detener grabación en la barra de estado. Ahora vamos a ejecutar la macro ya creada anteriormente, de la siguiente manera: ¡Recuerde! Primero tiene que sombrear el área donde va a aplicar el formateo de datos Vaya al menú Vista – luego Macros Luego seleccionar Ver macro… Luego seleccione Formato y dele clic en Ejecutar



Excel Avanzado 59

Desarrollo de la macro 2 de Suma Vaya al menú Vista – luego Macros

Luego seleccionar Grabar macro… Luego en la casilla de nombre colocamos: SUMAR y aceptar

Luego colóquese en la celda D8, y dar clic en el icono y de Enter Por último le damos clic en el icono de detener grabación en la barra de estado Ahora vamos a ejecutar la macro ya creada anteriormente, de la siguiente manera: ¡Recuerde! Primero tiene colocarse debajo de la columna donde va a totalizar la columna y borrar el dato de suma ya hecha allí. Vaya al menú Vista – luego Macros Luego seleccionar Ver macro… Luego seleccione SUMAR y dele clic en Ejecutar Ahora, Ud. Puede ejecutar las macros tantas veces Ud. Desee debido a que ya fue creada en el libro de Excel


EJERCICIO 1: 1. Ahora transcribe le siguiente tabla con todas sus filas

NOMBRES APELLIDOS REGIONAL SALARIO FECHA

INGRESO SECCION

LUIS TOVAR BOGOTA 1520000 25/04/2000 CONTABILIDAD

JUAN PEREZ CALI 1800000 15/03/2003 SECRETARIA

PEDRO RODRIGUEZ CARTAGENA 2500000 13/02/2004 FACTURACION

JAIME CASTRO B/QUILLA 3548000 20/10/2001 GERENCIA

CAMILO OJEDA MEDELLIN 1500000 05/08/2007 CAJA

ANDRES ACOSTA BOGOTA 3560000 15/12/2008 GERENCIA

LORNA PAZ B/QUILLA 850000 10/01/2006 CONTABILIDAD

CARLOS TORRES ARMENIA 985000 27/05/2002 SECRETARIA

MARIO PUELLO PASTO 850000 12/09/2008 FACTURACION

LEIDY MENA NEIVA 4500000 20/03/2006 GERENCIA

LUIS PEREZ BOGOTA 982000 08/07/2009 CAJA

CAMILO VARGAS CALI 3580000 13/06/2004 GERENCIA

PABLO RUIZ MEDELLIN 689000 20/10/2001 SECRETARIA

TOTAL SALARIOS



Informática II 60

2. Colócale el formato que se muestra en la imagen 3. Colócale el título : TALLER DE MACROS AUTOMÁTICAS EXCEL 4. luego crea una macro de nombre : ORDENA_APELLIDO (Tabla ordenada por

campo Apellido) 5. Recuerda: Seleccionar el rango de la tabla de datos (incluyendo encabezados)

No seleccionar el total de salarios 6. Ahora comprueba el funcionamiento de la macro dirigiéndose al menú ver -

macro - ver macros …. - Desde donde se podrán administrar las macros del libro activo y de todos los libros abiertos.

7. Ahora ordénala por otro campo. Ejemplo :por Nombre (para que puedas ver su ejecución)

8. Ahora asígnale un botón a la macro creada 9. Enseguida, da clic sobre el botón de comando: ordena_apellido para ver su

ejecución.

Nota: Haz otras macros para manejarlas de forma Independiente, creando más botones de comando por macro propuesta.

EJERCICIO: Ahora crea las siguientes macros con botones de comando, según propuesta, así: 1. > Ordenar por Nombre en forma ascendente 2. > Ordenar por Fecha ingreso descendente 3. > Ordenar por Salario descendente 4. > Ordenar por Regional ascendente 5. > Ordenar por Sección ascendente 6. > Hacer filtro automático 7. > Quitar filtro automático 8. > Colocar imágenes prediseñadas 9. > Subtotales por regional valorizando los salarios



Excel Avanzado 61

EJERCICIO 2: Implementación de controles tipo Formularios

Este taller le permitirá implementar y aplicar los diferentes controles de tipo formulario y su utilización en una tabla de Excel 2010 Para realizar este taller Ud. debe de entra a internet y copia la siguiente dirección y bajar el archivo del ejercicio correspondiente a esta unidad:

EJERCICIO 3: Implementación de controles tipo Formularios II

Para realizar estos talleres Ud. debe de entrar al módulo y aplicar el archivo del ejercicio correspondiente a esta unidad



Informática II 62

4. BIBLIOGRAFIA Internet

o www.aulaclic.c

om o www.abcdatos.

com o www.slideshar

e.net

o www.xltoday.net

o www.programati

um.com

Excel 2003 Macros Recursos Informáticos

VBA Excel 2007 Curso Excel Experto

Aulas Digitales

Internet

Michéle Amelot

Universidad Panamericana

internet

Ediciones ENI

http://www.aulaclic.com/

http://www.aulaclic.com/

http://www.abcdatos.com/

http://www.abcdatos.com/

http://www.slideshare.net/

http://www.slideshare.net/

http://www.xltoday.net/

Documents

Modulo Informatica Excel Ub2012