N O D E

Tecnología I Z A J E

Informática

GUÍA PARA EL ESTUDIANTE

Excel Avanzado
 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”.

Informática II

© Prohibida la reproducción parcial o total

bajo cualquier forma
(Art. 125 Ley 23 de 1982)

Bogotá – Colombia
Versión 04 - Enero 2010

2
 Contenido
PRESENTACIÓN 5
GUÍA METODOLÓGICA% 6

1. UNIDAD DE APRENDIZAJE 1
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

2. UNIDAD DE APRENDIZAJE 2
2.1. Filtrar información, establecer Subtotales, Tablas dinámicas
y Gráficos dinámicos 23
2.2. Bases de Datos 23
2.2.1. Operaciones Básicas sobre una base de datos 24
2.2.2. Subtotales: 30
2.2.3. Tablas Dinámicas: 34

3. UNIDAD DE APRENDIZAJE 3
3.1. Macros automáticas y programadas en Visual Basic
For Application – Excel 54
3.1.1. Crear una macro automáticamente 54
3.1.2. Cómo asignar Macros a botones de control 56
3.1.3. Visual Basic For Application (VBA)- Excel 59
3.1.4. Formularios Visual Basic Application - Excel 78

4. BIBLIOGRAFIA 93

Excel Avanzado 3
 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.

Excel Avanzado 5
DIAGNÓSTICO

INFORMACIÓN GENERAL

Regional Programa _Módulo_

Estudiante Formador

EVALUACIÓN DIAGNÓSTICA

Estilo de
aprendizaje

Excel Avanzado 7
 Excel Avanzado

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 Evidencias

• Aplica y crea funciones Producto
condicionadas anidadas

• Establece y crea Desempeño

funciones condicionales
combinadas con otras

• Establece técnicas para

utilizar funciones de Desempeño
búsqueda para localizar
información entre hojas de
cálculo

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.

Informática II 8
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”;”VERDE”)) , ahora cópiela hacia abajo

(celdas)

Excel Avanzado 9
FDEH (El Formador Dice Y El Estudiante
Hace)

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 40% y el examen
final del 30%.

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:

Informática II 10
 1. Para resolver el numeral a), ubiquémonos en la celda E3 y en ella editaremos la
siguiente fórmula:
=(B3*30%)+(C3*40%)+(D3*30%)

Esta fórmula se debe copiar en las siguientes celdas de la columna hasta

completar los resultados para cada estudiante.

Explicación:
El Formador dará la explicación pertinente de la fórmula
anterior.

2. Ahora, Pasemos al numeral b) y nos ubicaremos la celda F3, para construir la

siguiente función:
=SI(E3>=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(E3>=4,5;"Excelente";SI(E3>=4;"Sobresaliente";SI(E3>=3;"Aceptable";"Insuficie
nte")))

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

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.

Excel Avanzado 11
Al final tendremos una tabla como la siguiente:

Ejemplo 3:
En una hoja de cálculo nueva introduce la siguiente información y complete la tabla con
datos al azar:

Desarrollo:
1. Colócate en la celda B8 y digita lo siguiente:
=SI(ESERROR(MODA(B2:B7));"";MODA(B2:B7))
La anterior función hace lo siguiente: cuando en la columna de ventas de
un mes determinado no haya información coloca un espacio en blanco,

Informática II 12
 en vez de imprimir error; de lo contrario genera el valor de ventas más
repetido del mes indicado.

2. Colócate en la celda B9 y digita lo siguiente:

=SI(ESERROR(PROMEDIO(B2:B7));"";PROMEDIO(B2:B7))

Esta función lo que hace es cuando en la columna de ventas de un mes

determinado no haya información coloca un espacio en blanco, en vez de
imprimir error; de lo contrario genera el promedio de ventas de todos los
productos.

3. Ahora copia la fórmula en las columnas siguientes a la derecha.

FDH (El Formador Dice Y

Hace)

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.
[Link]. BuscarH y BuscarV:

Permiten buscar información en tablas. BUSCARH busca en tablas horizontales mientras

BUSCARV 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,
BUSCARV, 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.

Los formatos o sintaxis de estas dos funciones son:

=BUSCARH(Valor_buscado;Matriz_buscar_en;Indicador_filas;ordenado)
=BUSCARV(Valor_buscado;Matriz_buscar_en;Indicador_columnas;ordenado)

Excel Avanzado 13
[Link]. 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.

FDEH (El Formador Dice Y El Estudiante

Hace)

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 A9:B13 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 A1:C7 de la hoja
Buscar. En la 3ª columna deberá aparecer el premio que corresponda a cada
ganador:

Informática II 14
 Ganador Nº de Premio
puntos
Antonio 600
Fernández
Catalina Lago 1200
Herrera
Roberto Suárez 900
Vega
Luis Ferrer 2100
Manotas
Ana Sánchez 500
Torres
José Alonso 4050
Parra Oliver

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 distinta. 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 C2. 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 B2 (que contiene el nº de

puntos obtenido por el ganador).

En el argumento "matriz", seleccionar el rango de celdas A10:B13 (donde se establecen

las correspondencias de nº de puntos con premios).

Oprimir Enter y en la celda C2 aparecerá el premio correspondiente. Cuando la función

no encuentra en la matriz seleccionada ningún valor coincidente con el que hemos

Excel Avanzado 15
 introducido, selecciona el que más se le aproxima por abajo (por ejemplo, considerará
que lo más aproximado a 900 es 500).

Para poder copiar esta fórmula a las celdas C3 a C7 es necesario convertir la referencia
a la matriz en una referencia absoluta; por tanto, se deberá modificar la fórmula para que
quede de la siguiente manera: =BUSCAR(B2;$A$10:$B$13). También funcionaría con
referencias mixtas: =BUSCAR(B2;A$10:B$13).

Una vez modificada la fórmula, cópiala a las celdas C3 a C7. Observa cómo aparecen
automáticamente los premios correspondientes.

Aplicación de las Funciones BUSCARV y

BUSCARH

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 (BUSCARV) o fila (BUSCARH) se debe buscar la
correspondencia que queremos.

Función BUSCARV

Supongamos que en el ejercicio anterior, en la tabla de Premios se incluyen los datos

relativos a tres promociones diferentes:

Nº de Premios prom1 Premios prom2 Premios prom3

puntos
Una camiseta y un Maletín Una suscripción a la revista
500 Una entrada para el cine
deportivo "Enter"
Un walkman con Una entrada para el El libro "Programación
1000 teatro Avanzada en Java"
auriculares
Un Equipo de Sonido Una entrada para el Una vajilla completa
2000
fútbol
Un Computador de Una entrada para la Un viaje a París para dos
4000
Escritorio ópera personas

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.

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

BUSCARV.

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 BUSCARV.

Informática II 16
 Luego, añadir las columnas Premios prom2 y Premios prom3 en el
rango C9:D13 de la hoja BUSCARV. 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 BUSCARV

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 BUSCARH

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

EJEMPLO
:

Dar el nombre de BUSCARH a la Hoja 3. Copiar la tabla de Premios situada en el

rango A9:D13 de la hoja BUSCARV a la hoja BUSCARH, de forma que los datos se
dispongan en horizontal y no en vertical. Para ello, sigue los siguientes pasos:

• Seleccionar el rango A9:D13 de la hoja BUSCARV y pulsar

el botón Copiar

• Cambiar a la hoja BUSCARH haciendo clic en su pestaña.

Situarse en la celda A9.

Excel Avanzado 17
 • 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 BUSCARH, copiar la tabla situada en estas

mismas celdas de la hoja BUSCARV. Borrar el contenido del rango C2:C7 (columna
Premio)

Situarse en la celda C2 y activar el asistente para funciones; seleccionar la función

BUSCARH. A continuación, proceder de la misma forma que con BUSCARV cambiando
la matriz a seleccionar, que será B9:E12 (en realidad, $B$9:$E$12).
El argumento “Ordenado” en la función BUSCARV

Como ya se ha visto, el cuarto argumento de la función BUSCARV 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 (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):

Informática II 18
Excel Avanzado 19
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 BUSCARV 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 12
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
sumatoria de la columna valores a

Informática II 20
los demás dejar la celda en blanco.

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.

Entra a Internet y copia la siguiente dirección y realice todos los

ejercicios propuestos correspondientes a esta unidad:

[Link] o entra al blog:

[Link]

Valoración de Evidencias:

Excel Avanzado 21
 Filtrar información
2. UNIDAD DE APRENDIZAJE 2

Logros de Competencia Laboral

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

Indicadores de Logros Evidencias

 Crear filtros avanzados y Producto
autofiltros de manera eficiente
y efectiva
 Utilizar de manera eficaz y Desempeño
eficiente la potencialidad de la
función Subtotal para resumir y
agrupar datos contenidos en
una tabla
 Aplicar técnicas avanzadas
para combinar diferentes Desempeño
funciones de agrupación en un
mismo subtotal en una base
de datos
 Diseñar adecuadamente Producto
tablas y gráficos dinámicos
para resumir y organizar
información contenida en
una tabla

Informática II 22
FDH (El Formador Dice Y
Hace)

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

y Gráficos dinámicos
2.2. Bases 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 bases de datos. Las bases 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 bases 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 bases de datos. Sin embargo, si las necesidades de
gestión de la base 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 bases de datos.

Las tablas normalmente se interpretan como listas. Una lista es una serie etiquetada de
filas de hoja de cálculo que contienen datos relacionados, como una base 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.

Campos

Registro

Excel Avanzado
FDEH (El Formador Dice Y El Estudiante
Hace)

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 bases de datos muy simples. Para muchas ocasiones
será suficiente, pero para bases de datos más completas, complejas y profesionales no
resultará adecuado. La tabla de la base 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 base de datos

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

 Organizar (ordenar) los registros con un orden determinado.

 Separar (filtrar) los registros con el fin de localizar cierta información
fácilmente.

Informática II 24
 Ordenar

Filtrar

[Link]. Ordenación de la base de datos

Para ordenar la base 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 base 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.

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

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.

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

Informática II 26
[Link]. Filtrado de la base 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

[Link]. 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.

Excel Avanzado 27
 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 listas 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.

Informática II 28
[Link]. 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.

Excel Avanzado 29
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

Luego Aparece el siguiente cuadro de texto en donde el usuario selecciona las opciones
deseadas, según sus necesidades.

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

Rango de lista: determina por defecto el rango

de la tabla donde se encuentran los datos a
filtrar.

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.

2.2.2. Subtotales:
Se pueden crear subtotales (sumas, cuentas, promedios, etc.) para los campos de la
base 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.

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

Excel Avanzado 31
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.

Al final tendremos un resultado similar al que observamos en la gráfica siguiente,

dependiendo de las opciones editadas por el usuario.

Informática II 32
FDEH (El Formador Dice Y El Estudiante
Hace)

[Link]. 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 QUITAR el chulo 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

Excel Avanzado 33
utilizando las funciones: suma y promedio anidadas en una misma tabla
subtotalizada.

FDH (El Formador Dice Y

Hace)

2.2.3. Tablas Dinámicas:

Se utiliza para analizar los datos de la base 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 base de
datos de Excel, de una base 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.

Informática II 34
  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

[Link]. Partes de una Tabla Dinámica

Campo de Filtro
Campo de Fila Campo de Columna

Campo de Valores

Área de Valores

FDEH (El Formador Dice Y El Estudiante

Hace)

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:

Excel Avanzado 35
 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 1 Nubia Miranda 5.0
Taller 3 Pedro González 4.8
Taller 1 Martha Osorio 3.8
Taller 2 Hernán Casas 4.0
Taller 3 Martha Osorio 4.4
Taller 3 Hernán Casas 4.5
Taller 2 Nubia Miranda 3.9
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.

Informática II 36
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.

Excel Avanzado 37
 CAMPOS
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 Área Columnas
NOTAS irá en el área VALORES.

Área Filas Á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.

Luego se debe seleccionar la

opción Configuración de campo
de valor la cual está ubicada en
el menú que aparece en
pantalla

Informática II 38
 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.

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:

Excel Avanzado 39
 FDEH (El Formador Dice Y El Estudiante
Hace)

[Link]. Utilizar un campo de Filtro de Informe 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:

FECHA
NOMBRE DEPARTAMENTO CIUDAD EDAD OCUPACION SALARIO
INGRESO

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

Informática II 40
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:

Excel Avanzado 41
11. Luego dele clic en el área de valores y clic
derecho y seleccione Configuración de campo de valor

11

12. Luego da clic en la pestaña Mostrar valores como

y seleccione el ítem deseado.

[Link]. 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)

Informática II 42
% 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)

Excel Avanzado 43
[Link]. 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.

Informática II 44
 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 Para insertar o quitar líneas en blanco

mostrar subtotales, después de cada elemento
mostrarlos en la
parte inferior o en la Mostrar la tabla en forma
parte superior compacta, esquema o tabular

Me permite activar o desactivar Me permite modificar es estilo de la

los totales generales tanto para tabla dinámica en todos sus aspectos
filas como para columnas

Excel Avanzado 45
 Opciones de Tabla Dinámica

Opciones que me Opciones que permiten Actualizar

permiten modificar el la tabla dinámica, cuando la tabla
Opciones que me permiten: agrupar,
formato del campo origen ha sido modificada y
activo y las opciones desagrupar y ordenar los elementos de la
además me permite cambiar el
de la tabla dinámica. tabla dinámica
origen de datos o la tabla origen si
es requerido.

Esta opción se utiliza para crear

campos o elementos calculados

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.

[Link]. 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

Informática II 46
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.

Excel Avanzado 47
EDH (El Estudiante Dice Y
Hace)

 Ejercicio Uno

Crea una base 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).

Introduce estos datos en la base de datos:

Informática II 48
Una vez creada la base 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 base 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 base de datos de la tienda de electrodomésticos:

Excel Avanzado 49
 Compras de Andrés Saldaña superiores a $ [Link] y compras de más de una
unidad con un total mayor de $ [Link].
 Ventas de electrodomésticos de la marca LG con un valor total superior a $
[Link] y ventas de Microondas.
 Ventas con artículos que contengan la letra M, de menos de $ [Link], una sola
unidad y de alguna Ana o de alguna Nubia.

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

Valoración de Evidencias:

 Ejercicio Tres

Para la base 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.
 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 $ [Link].
 Los subtotales sólo para el total de la venta.

Para la base 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.

Valoración de Evidencias:

Informática II 50
 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 1 Nubia Miranda 40.0 5.0
Taller 3 Pedro González 32.5 4.8
Taller 1 Martha Osorio 25.0 3.8
Taller 2 Hernán Casas 20.5 4.0
Taller 3 Martha Osorio 24.0 4.4
Taller 3 Hernán Casas 43.0 4.5
Taller 2 Nubia Miranda 38.0 3.9

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 base 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 base 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.

Valoración de Evidencias:

Excel Avanzado 51
Ahora ingrese a Internet y copia la siguiente dirección y realice los ejercicios propuestos
correspondientes a esta unidad
[Link] o entra al blog:
[Link]

Nota: También puedes bajar y realizar un taller de Archivos planos en las misma
dirección anterior.

Informática II 52
 Macros
automáticas
3. UNIDAD DE APRENDIZAJE 3
Logros de Competencia Laboral

1. Aprende conceptos básicos y crea Macros automáticas, diseña

formularios con controles y programa macros apoyadas con el programa
Visual Basic Application-VBA

Indicadores de Logros Evidencias

 Utilizar de manera eficaz y Conocimiento
eficiente la potencialidad de la
herramienta Macros para
automatizar series de
instrucciones recurrentes
 Crear y asociar diferentes tipos
de macros a botones de acción Producto
aplicados a formularios
 Aplicar técnicas avanzadas para Desempeño
configurar macros apoyados por
el programa Visual Basic
Application
 Define y crea funciones y Producto
procedimientos aplicados en
macros con el objetivo de
solucionar un requerimiento
planteado por un usuario

Excel Avanzado 53
FDH (El Formador Dice Y
Hace)

3.1. Macros automáticas y programadas en Visual Basic For Application

– Excel

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,
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.

Informática II 54
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 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 .

Ejecutar una macro

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.

Excel Avanzado 55
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.

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

Informática II 56
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á

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
FDEH (El Formador Dice Y El Estudiante
Hace)

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.

Informática II 58
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

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

FDH (El Formador

Dice Y Hace)

3.1.3. Visual Basic For Application (VBA)- Excel

VBA significa Visual Basic for Application. Es un lenguaje de
programación que surge de Visual Basic (VB). VBA de Excel está adaptado a
Excel, para trabajar con celdas, hojas, autofiltro etc. (es decir, los objetos de
la aplicación Excel).

¿Qué puedo hacer con VBA?

Con este lenguaje de programación puedes crear tus propios programas en

Excel. Estos programas pueden ser todo desde una macro (una pequeña
programa VBA, por ejemplo un atajo personalizado) hasta una aplicación
entera con listas desplegables, menús etcétera.

Excel Avanzado 59
 ¿Por qué VBA?
VBA sirve para muchas cosas. Imagínate todas las tareas repetitivas que
tienes que ejecutar todos los días. Un ejemplo - siempre vas aplicando el
mismo formato a un grupo de celdas de un libro que te mandan todos los
días. En vez de hacer un montón de clics para obtener este formato, puedes
automatizar el proceso, a un botón, o un atajo de teclado.

[Link]. El Editor de Visual Basic (VBE)

El lenguaje de programación que utiliza Excel para trabajar con macros es el VBA (Visual
Basic Application) que como podemos deducir está basado en Visual Basic.

Explorador de Área de Módulos

Proyectos de Código

Ventana de
Propiedades

La Barra de menús del editor de Visual Basic (VBE)

Informática II 60
 Esta es la barra de menú del editor de Visual Basic donde podemos acceder a las
diversas opciones y comandos del propio editor.

Ejecutar Interrumpir Restablecer

La barra de Herramientas

Esta es la barra de botones de Visual Basic, donde podemos acceder de una manera más
rápida a las opciones más comúnmente utilizadas, como son la ejecución, la parada, guardar,
etc.

En la parte izquierda de la pantalla podemos ver

dos cuadros, el cuadro superior es el
Explorador de Proyectos. Aquí permite
ver todos los proyectos y todos los módulos de
cada proyecto. Los módulos se agrupan en
cuatro categorías:

- Módulos asociados a objetos Excel(Libros y

Hojas)
- Módulos asociados a Formularios
- Módulos con procedimientos
- Módulos de clase

Cada módulo puede contener muchos

procedimientos

En la parte inferior izquierda tenemos otro

recuadro, La Ventana Propiedades que
nos muestra las propiedades del objeto
seleccionado. En la imagen podemos ver las
propiedades del objeto Hoja1 y que es una
hoja de cálculo (Worksheet).

Excel Avanzado 61
 En la parte derecha tenemos el espacio dedicado a redactar el código de los procedimientos,
funciones, y en la parte superior existen dos cuadros combinados donde podemos
seleccionar los objetos y los métodos de ese objeto respectivamente.
Ventana de
Ventana de Eventos
Objetos

Nota: Los módulos y formularios pueden exportarse a un archivo para luego

importarlo a otro proyecto Excel.

La extensión de los archivos creados depende del tipo de archivo

exportado:

- Los módulos de clase(módulos asociados al libro y a las hojas y

módulos independientes) tienen la extensión .cls
- Los formularios tienen la extensión .frm
- Los módulos estándares tienen la extensión .bas

Guardar archivos de Excel con macros VBA

Para guardar el archivo y queremos que las Macros que hemos creado se almacenen con el
resto de las hojas de cálculo deberemos utilizar un tipo de archivo diferente.

Para ello deberemos ir al Botón Office y seleccionar la opción Guardar como.

Se abrirá el cuadro de diálogo Guardar como. En la opción Guardar como tipo seleccionar
Libro de Excel habilitado para macros (*.xlsm).

Informática II 62
 Cuando vayas a abrir la próxima vez el archivo con las macros grabadas, aparecerá este
cuadro de diálogo

Si confías en las posibles Macros que tuviese el archivo o las has creado tú, selecciona el
botón Opciones para activarlas.

Y luego aparecerá un cuadro de diálogo y selecciona la opción Habilitar este contenido y

pulsa Aceptar

Excel Avanzado 63
 Y ahora ya podremos ejecutar las macros creadas en el libro abierto
[Link]. Definición de Procedimientos, funciones y Variables

Procedimientos

Los procedimientos son subprogramas que permiten descomponer una tarea

de programación compleja en un conjunto de tareas más breves y simples.
Permiten organizar códigos dentro de módulos. Además no devuelven
valores, comienzan siempre por la instrucción SUB y finalizan con la
instrucción END SUB

Hay dos tipos de procedimientos SUB:

- Los procedimientos Sub generales

- Los procedimientos Sub asociados a eventos (_Open, _Click)

Funciones

Informática II 64
Los procedimientos Function, llamados comúnmente funciones, devuelven un
valor que resulta de un cálculo. El valor se devuelve a través del nombre de la
función.

Además Ud. Puede crear sus propias funciones personalizadas.

LLAMAR A UN PROCEDIMIENTO

Ejemplo de Procedimiento General:

El siguiente procedimiento pide al usuario que confirme su deseo de abandonar

la aplicación, y sale de Excel si el usuario responde que Sí.

Private Sub Terminar()

If MsgBox(“ desea salir del programa?”),_
VbQuestion+VbyesNo) = vbYes then
[Link]
End If
End Sub

Ejemplo de Procedimiento asociado a evento:

El siguiente procedimiento asociado a evento abre automáticamente el libro

[Link] cuando se abre el libro [Link]. Este procedimiento está
asociado al evento Open del objeto Workbook y se encuentra en el módulo
ThisWorkbook del libro [Link]

Excel Avanzado 65
Private Sub Work_Open()
„ Abre el libro [Link]
[Link] Filename:=”C:\VENTAS\[Link]”
„ Activa el libro Resumen
Windows(“[Link]”).Activate
End Sub
[Link]. Tipos de Datos y Variables VBA
Las variables permiten almacenar valores intermedios durante la ejecución del
código VBA para usarlos luego en cálculos, comparaciones, pruebas …
Las variables se identifican por un nombre que permite hacer referencia al valor
que contienen y un tipo que determina la naturaleza de los datos que pueden
almacenar.

NUMÉRICAS

CADENAS DE CARACTERES
El tipo es String. Existen dos tipos de cadenas:
1. Cadenas de longitud variable
2. Cadenas de longitud fija

Informática II 66
BOOLEAN O LOGICA
El tipo es Boolean. La variable puede tomar los valores True (verdadero) o False
(Falso), que es su valor por defecto. Ocupa dos bytes.

FECHA
El tipo es Date. La variable puede tomar los valores de fecha y de hora del
primero de enero del año 100 al 31 de diciembre de [Link] ocho bytes.

VARIANT
Las variables de tipo Variant pueden contener datos de todo tipo, además de
los valores especiales Empty, Error y Null.

OBJETO
El tipo es Object. Para crear variable que contenga un objeto, comience por
declarar la variable como tipo Objeto y luego asígnele un objeto.
Para asignarle un objeto a una variable Objeto, use la instrucción SET.

Ejemplo:
Dim Rangonum as Range
Set Rangonum = Range (“A10:B20”)

CONSTANTES
Una constante permite asignar un nombre explícito a un valor.
Const Val1=148
Const Val2= “Mega”

FDEH (El Formador Dice Y El

Estudiante Hace)

[Link]. Programación de Objetos en Excel 2007

Podemos abrir el editor Visual Basic presionando la combinación de teclas Alt + F11, o ir
Menú Programador – Visual Basic
Ahora para empezar a trabajar ya en el editor de Visual Basic debemos insertar un módulo
de código que es donde se almacena el código de las
funciones o procedimientos de las macros.
Primero realiza lo siguiente: Ir al menú Insertar → Módulo.

Luego ir al menú Insertar → Procedimiento... 2

Excel Avanzado 67
 Nos aparece un cuadro de diálogo como vemos en la imagen donde le damos el Nombre: al
procedimiento/función sin insertar espacios en su nombre.

Luego debemos seleccionar si lo que vamos a crear es una

función (en el caso que devuelva algún valor), o si por el contrario
es un procedimiento (si no devuelve ningún valor).

Ahora podemos seleccionar el Ámbito de ejecución. Si lo creamos

como Público podremos utilizar el procedimiento/función desde
cualquier otro módulo, pero si lo creamos como Privado solo
podremos utilizarlo dentro de ese módulo.
Por último dar clic en Aceptar y se crea el Procedimiento con el
nombre propuesto, donde escribiremos las instrucciones
necesarias de la macro.

O también puede escribir directamente en la ventana de código:

Sub NombreProced() y Enter y luego VBA asignará su sintaxis
pertinente. (le añade al final End Sub)

[Link]. Objetos de Excel

Introducción
VBA Excel es un lenguaje de programación orientado a objetos. La mayoría de
los elementos que maneja Excel son objetos: los libros, las hojas de cálculo, Los
rangos de celda, las celdas, etc.

Ciertos objetos contienen otros objetos, que pueden también contener otros.
Estos objetos se llaman contenedor u objetos parten. Por ejemplo, el objeto
Application es un contenedor de objetos Workbook (libros abiertos en Excel) y
éste a su vez contiene objetos Worksheet (hojas de cálculo de un libro).
Un conjunto de objetos del mismo tipo se denomina Colección (Workbooks;
Worksheets; Range)
Un objeto posee un conjunto de características llamadas Propiedades
(Version, Cursor) y sirven para describir un objeto y de acciones sobre los
objetos llamados Métodos (Select, Clear , Save, Close) y a éstos le ocurren
Eventos aplicados por el usuario. (Ej: _Open; _Click() )

1. El Objeto Application
Representa la aplicación Microsoft Excel activa. Es el objeto por defecto y por lo
tanto es opcional (Ejemplo: Workbooks equivale a escribir
[Link]).

El objeto contiene:

Informática II 68
 - Las propiedades relativas al entorno de Excel y a la presentación de
la interfaz.
- Propiedades que devuelven objetos y colecciones de primer nivel
- Propiedades específicas que hacen referencia directa a objetos:
ActiveCell, ActiveSheet, ActiveWindow, ActiveWorkBook,
ActiveChart, Selection, etc.

Propiedades del objeto:

Caption: Cadena de caracteres. Nombre mostrado en la barra de títulos de
Excel
Cursor: Constante. Aspecto del puntero del ratón en Excel (xlWait,
xlNorthwestArrow)
StatusBar: Cadena de caracteres. Texto de la barra de estado
Visible: Boolean. Indica si la ventana principal está visible.
Version: Cadena de caracteres. Muestra el número de la versión de Excel
UserName: Cadena de caracteres. Muestra el nombre del usuario matriculado
en Excel
ScreenUpdating: Boolean. Indica si el eco de ejecución de las macros se
visualiza en pantalla.
WorkSheetFunction: Cadena de caracteres. Permite obtener el resultado de una
función aplicada a un rango de celdas. (Suma=
[Link](selection)

Métodos del objeto:

GoTo: Selecciona un rango de una hoja en cualquier libro y activa el libro si no
lo está
Union: Devuelve la unión de al menos dos rangos.
GetOpenFileName o FindFile: Muestra el cuadro de diálogo de Abrir Archivo.
GetSaveasFilename: Muestra el cuadro de diálogo Guardar Como
Inputbox: coloca un mensaje en pantalla y permite la captura de datos.
Msgbox: coloca un mensaje en pantalla de tipo informativo.
Quit: Sale de la aplicación Excel activa.

Ejemplos: Realiza estas macros con la ayuda del formador

Excel Avanzado 69
Calcula una sumatoria y un promedio de un rango. Muestra el nombre del
usuario Actual

2. El Objeto WorkBook
Este objeto representa un libro de Excel. El objeto Workbook es un miembro de
la colección Workbooks
También es devuelto por las propiedades del objeto Application:
- Workbooks
- ActiveWorkbook
- ThisWorkBook
Colecciones:
Charts : Colección de los gráficos de un libro
Names: Colección de los nombres de rangos de celdas
WorkSheets: colección de las hojas de cálculo de un libro
Propiedades
Password : Cadena de caracteres. Devuelve o define la contraseña para abrir el
archivo
HasPasssword: Boolean. Indica si el libro está protegido con contraseña.
HasVBProject: Boolean. Indica si un libro tiene código VBA.

Métodos
Add: Crea un Libro nuevo.
Close: Cierra el libro indicado.
ExportAsFixedFormat: Publica un libro en formato PDF
PrintPreview:Muestra la vista preliminar del libro activo/indicado
PrintOut: imprime en papel el libro activo/indicado
Save: Guarda los cambios en el libro indicado
SaveAs: Guarda el libro indicado con otro nombre de archivo
Protect: Protege el libro especificado con una contraseña

Informática II 70
UnProtect: Quita la contraseña del
libro indicado

Ejemplos:
Realiza esta macro con la ayuda
del formador, en la que se crea un
libro nuevo

Ahora, realiza esta macro se graba

y cierra inmediatamente

3. El Objeto WorkSheet
Este objeto representa una hoja de cálculo Excel. El objeto WorkSheet es un
miembro de la colección WorkSheets del objeto Workbook.

También es devuelto por las propiedades del objeto Application:

- Worksheets
- ActiveSheet
- Sheets

Propiedades:
Name: Cadena de caracteres que contiene el nombre de la hoja de cálculo.
TypeName: Constante. Devuelve o define el tipo de la hoja de cálculo especificada.
Visible: Boolean. Indica si la hoja de cálculo indicada está visible.
Index: Devuelve el numero ordinal de la hoja en el libro activo.

Métodos:
Activate: Activa la hoja de cálculo especificada. Equivale a dar clic sobre la etiqueta
de la hoja.
Copy: Hace una copia de la hoja de cálculo indicada.
Delete: Elimina la hoja de cálculo indicada.

Excel Avanzado 71
 ExportAsFixedFormat: Publica una hoja de cálculo en formato PDF.
Move: Mueve la hoja de cálculo indicada a una posición dada.
Paste: Pega el contenido previa copia en la hoja de cálculo indicada.
PasteSpecial: Pega el contenido previa copia en la hoja de cálculo indicada
dependiendo del formato especificado.
PrintOut: imprime la hoja de cálculo indicada
PrintPreview: Muestra la vista preliminar de la hoja indicada antes de imprimir.
Protect: Protege la hoja de cálculo indicada.
SaveAs: Guarda la hoja de cálculo con un nombre distinto.
Select: Selecciona la hoja de cálculo.
Unprotect: Desactiva la protección de la hoja de cálculo indicada.

Ejemplo:
Realizar estas macros con la ayuda
del formador.
Esta macro muestra la hoja activa
en Vista Previa para imprimir y
luego imprime una copia de ella

La siguiente macro crea una hoja de cálculo nueva en un libro de Excel y luego protege
la hoja 1 con una contraseña

4. El objeto Range
El objeto Range representa un rango de celdas y puede estar constituido por:
- Una celda, una fila, una columna, un rango de celdas, etc.

Informática II 72
Propiedades:
ActiveCell: Representa la primera celda activa de la ventana activa o indicada.
Cells : Representa una celda o una colección de celdas.(fila, columna)
Columns: Representa las columnas de la hoja activa.
Entirecolumn: Representa una o varias columnas del rango indicado.
EntireRow: Representa una o varias filas del rango indicado.
Offset: Sirve para desplazar una o varias filas o columnas
End: Sirve para colocar la celda activa al final de la zona indicada. (= Teclas FIN +
)
Range: Se utiliza para representar un rango de celdas de la hoja activa
Rows: Sirve para representar las filas de la hoja activa.
Unión: Sirve para representar la unión de varios rangos contiguos o discontinuos.
Font: Contiene los atributos de fuente(nombre, tamaño, color, etc.) del rango
indicado.
Interior: Representa el relleno de las celdas del rango indicado. Address:
Representa la dirección absoluta de la celda activa ColumnWidth :
Representa el ancho de las columnas del rango indicado.
HorizontalAlignment: Representa el tipo de alineación Horizontal.
VerticalAlignment: Representa el tipo de alineación Vertical.
RowHeight: Representa el alto de la fila en puntos del rango indicado.
Formular1C1: Representa o define la fórmula el contenido de la celda activa.
Value: Representa o define el valor de la celda actual o indicada.

Métodos:
Copy: Copia el contenido del rango indicado.
ClearContents: Borra el contenido de las celdas del rango indicado
Cut: Mueve el contenido de las celdas del rango indicado.
Paste: Pega la información copiado o cortada con anterioridad
Delete: Elimina las filas o columnas del rango indicado.
Insert: Inserta filas o columnas en el rango indicado
Replace: Busca y reemplaza caracteres en las celdas del rango indicado
AutoFilter: Filtrar una listas con autofiltros.
Find: Busca una información específica en un rango que representa la primera
celda donde se encuentra el dato
Sort: Ordena un rango de valores por un campo determinado
Ejemplos:

Excel Avanzado 73
 Esta macro formatea
un texto escrito en
Excel, dándole color,
Tipo de letra ,
Tamaño y Alineación

Abre un libro nuevo y copia la siguiente tabla de datos en la hoja1.

Luego, realizar esta macro con la ayuda del formador, esta macro copia un rango
con datos en otra hoja dentro del mismo libro

Esta macro Copia un

rango de datos y lo
pega en la misma hoja
en otra celda

[Link]. Estructuras Básicas

If …Then…Else…End If
Permite ejecutar ciertas instrucciones en función del resultado de una condición.

Informática II 74
Ejemplo:
La siguiente macro muestra un mensaje en pantalla donde pide al usuario
confirmación de fin de labores.

Do While…..Loop
Ejecuta un bloque de instrucciones repitiéndolas en función de una cierta
condición, siempre y cuando sea verdadera.

Excel Avanzado 75
Ejemplo: la siguiente macro inserta una o varias filas en el sitio donde está el
cursor (celda activa)

While …Wend
Ejecuta una serie de instrucciones en un ciclo mientras se cumple la condición
específica

Ejemplo:
Realiza esta macro con la
ayuda del formador.
En el mismo libro, localiza
la última fila vacía de una
lista de datos

Informática II 76
For…Next
Repite y ejecuta un bloque de instrucciones según el valor del contador hasta el
número que Ud. Le indique.
Sintaxis

Ejemplo:
Realiza esta macro con
la ayuda del formador.
En el mismo libro,
cambia el nombre a
todas las hojas de
cálculo creadas

For Each….Next
Repite y ejecuta un bloque de instrucciones para cada elemento de una colección
de objetos.
Sintaxis

Ejemplos:

Realiza estos ejercicios con la ayuda

del formador
En el mismo libro, crea una macro que
cambie los valores a la columna B
(Valor)

Excel Avanzado 77
EJEMPLOS:
Realice estas macros
con la ayuda del
formador.
En el mismo libro,
crea una macro
busca un texto y lo
reemplaza

[Link] Visual Basic Application - Excel

Vamos a crear un archivo donde se
lleve el control de pagos de 10
clientes y el registro de sus abonos
correspondientes.

Crea un libro nuevo y en la hoja 1

transcribe la tabla que se muestra
en la imagen de abajo y luego
vamos a crear los botones de
control (Formularios).

Ahora ve al menú Programador y

da clic en Insertar y escoge el
botón de comando (Control
ActiveX) y arrastra el botón
izquierdo del mouse sobre la cuadrícula dibujando un rectángulo. (Ver imagen)
Luego en el menú Programador, de clic en el icono Modo de
diseño.

Informática II 78
Luego de clic derecho sobre el botón y seleccione la opción Objeto botón de
comando - Modificar y escriba lo siguiente: CREAR CLIENTE y al finalizar de un
clic afuera.
Ahora otra vez efectúe los mismos pasos e inserte en la cuadrícula el botón de
comando llamado CONSULTAR CLIENTE.
Ahora ve al menú Programador y da clic en Insertar y escoge el botón de
comando (Formulario) y arrastra el botón izquierdo del mouse sobre la
cuadrícula dibujando un rectángulo. (Ver imagen)
Luego de clic derecho y seleccione la opción Modificar texto y escriba lo siguiente:
INGRESO DE ABONO y al finalizar de un clic afuera.
Ahora dele clic derecho al botón de control y seleccione COPIAR y según la
imagen vaya colocando los tres (3) botones que hacen falta, en el sitio que
corresponda.

Luego trasládese a la hoja 2 y

trascriba la tabla como se muestra en
la imagen de abajo y luego vamos a
crear el botón de cuadro combinado

Ahora ve al menú Programador y da

clic en Insertar y escoge el botón de
cuadro combinado (formulario) y
presiona la tecla ALT y arrastra el
botón izquierdo del mouse sobre la
celda A7 dibujando un rectángulo.
(Ver imagen)

Luego da clic derecho al botón de

cuadro combinado y selecciona FORMATO DE CONTROL y en la pestaña
CONTROL realiza los siguientes pasos: (Ver imagen)
- En RANGO DE ENTRADA: da clic en el botón contraer diálogo y seleccionar en
la hoja 1 el rango desde la celda B2 hasta la B11 y dar Enter (rango donde están
los nombres de clientes)
- En VINCULAR CON LA CELDA: da clic en el botón contraer diálogo y
seleccionar en la hoja 2 la celda F1 y dar Enter (lugar donde se colocará el
número de orden en la lista del cliente seleccionado en el cuadro combinado)

Excel Avanzado 79
- En LÍNEAS DE UNIÓN VERTICALES:
coloca el número 10 (Es el máximo
número de clientes en la lista) 1
- En SOMBREADO 3D: coloca un chulito 2
en la casilla de verificación. 3
4

Ahora en la columna de Factura y Saldo

Inicial vamos a utilizar la función INDICE (se
utiliza para localizar en una matriz o
vector el contenido de una celda
dependiendo del número de orden de
ese elemento en una lista)
Luego, coloca el cursor en la celda B7 y
escribe la siguiente función de INDICE, así:
=INDICE(HOJA1!A2:A11;F1)

Es el rango donde Es la celda donde se aloja el número

están los números de de orden del cliente seleccionado en
las facturas de cada el cuadro combinado
cliente
Luego, coloca el cursor en la celda C7 y escribe la siguiente función de INDICE,
así:
=INDICE(HOJA1!E2:E11;F1)

Ahora ve al menú Programador y da clic en Insertar y escoge el botón de control

(formulario) y arrastra el botón izquierdo del mouse sobre la cuadrícula
dibujando un rectángulo. (Ver imagen)
Luego de clic derecho y seleccione la opción Modificar texto y escriba lo siguiente:
ACTUALIZA SALDO y al finalizar de un clic afuera.

[Link]. Programación de Formularios y

Macros en VBA

Ahora ve al menú Programador y selecciona

el icono de Visual Basic o ALT+F11. Formulario
Luego en el Área de líneas de código,
vamos a escribir las distintas macros que va
a utilizar Ud. en la operación del archivo de
control de pagos de clientes, así:

Informática II 80
Ahora ve al menú INSERTAR y selecciona USER FORM, aparecerá un
formulario sin ningún control (fondo gris), luego ve al menú VER y selecciona
Cuadro de Herramientas
y luego aparecerá un cuadro de diálogo con todos los botones de controles
ActiveX, (Ver imagen).
Luego en la ventana propiedades del formulario 1 en la opción Caption (Se utiliza
para colocar el nombre del botón en el formulario) coloca
CREACION DE CLIENTE

Ahora da clic en el icono de cuadro de texto

y coloca el puntero del mouse sobre la ventana del formulario y arrástralo
dibujando un rectángulo.

Luego da clic en el icono de Etiqueta y coloca el puntero del mouse

sobre la ventana del formulario y arrástralo dibujando unEtiqueta
rectángulo.
Cuadro
de texto
Luego en la ventana propiedades en la
opción Caption (Se utiliza para colocar el
nombre del botón en el formulario) coloca
NOMBRE DEL CLIENTE:
Seguidamente selecciona en la opción Marco
BorderStyle : 1-fmBorderStyleSinge Botón de
Opción
Ahora inserta en el formulario los otros
dos (2) cuadros de textos y etiquetas
correspondientes que hacen falta.
Botón de
Luego ve al cuadro de herramientas de comando

controles y da clic en icono de Marco

y luego dibuja un rectángulo sobre
el formulario (Ver imagen)

Luego en la ventana propiedades en la opción Caption (Se utiliza para colocar el

nombre del marco en el formulario) coloca CONDICION DE PAGO
Seguidamente selecciona en la opción SpecialEffect : 2-fmButtonEffectSunken (
Se utiliza para colocar un efecto de bajo relieve al control especificado)
Luego ve al cuadro de herramientas de controles y da clic en icono de Botón de
opción y luego dibuja un rectángulo dentro del marco o frame.

Excel Avanzado 81
Luego en la ventana propiedades en la opción Caption (Se utiliza para colocar el
nombre del botón de opción dentro del marco) coloca de 0 a 15 DIAS
Seguidamente selecciona en la opción SpecialEffect: 2-
fmButtonEffectSunken (Se utiliza para colocar un efecto de bajo relieve al
control especificado)

Ahora, repite estos pasos para insertar los otros tres botones de opción que
hacen falta.

Luego ve al cuadro de herramientas de controles y da clic en icono de Botón

de comando y luego dibuja un rectángulo dentro del formulario (ver
imagen)

Luego en la ventana propiedades en la opción Caption (Se utiliza para

colocar el nombre del botón de opción dentro del marco) coloca ACEPTAR
Ahora, repite estos pasos para insertar el otro botón de comando que hacen
falta. (Salir)

Después, de haber terminado la inserción y diseño de los botones dentro del

formulario, vamos a programar los eventos y métodos asociados a cada uno
de ellos, así:

En primer lugar le damos clic derecho en el icono de Userform1(ventana

Explorador de Proyectos) o Ctrl +R y seleccionamos al opción Ver código y
en la lista de objetos (a la izquierda) seleccionamos Userform y en la lista de
Eventos (a la Derecha) elegimos Initialize y escribimos lo siguiente:
Método que se utiliza para
Private Sub UserForm_Initialize() inicializar todos los controles
TextBox1 = Empty de un formulario al activarse

TextBox2 = Empty
TextBox3 = Empty Se utiliza para ubicar el
cursor en el primer cuadro
[Link] de texto en el formulario
OptionButton1 = Empty
OptionButton2 = Empty
OptionButton3 = Empty Se utiliza para asignar al
OptionButton4 = Empty control del formulario
valor vacío o en blanco
End Sub

En primer lugar le damos doble clic al primer cuadro de texto y nos lleva al
VBE, donde se escribe de forma automática, lo siguiente:

Informática II 82
 Private Sub TextBox1_Change()

End Sub
Es el Evento por
Significa
defecto asociado a
Cuadro de texto
cuadros de texto
No.1

Ahora escribe lo siguiente dentro de ese procedimiento, así:

Private Sub TextBox1_Change() Se utiliza para asignar el

Dim filalibre As Integer número de la fila actual
filalibre = [Link] Range("B" donde se encuentra el
cursor a una variable
& filalibre).Select
ActiveCell.FormulaR1C1 = TextBox1
End Sub

Luego da doble clic en el icono de Userform1(para que aparezca el

formulario), seguidamente le damos doble clic en el segundo cuadro de
texto y nos lleva al VBE, donde se escribe de forma automática, lo
siguiente:
Private Sub TextBox2_Change()
Es el Evento por
End Sub Significa defecto asociado a
Cuadro de cuadros de texto
texto No.2

Ahora escribe lo siguiente dentro de ese procedimiento, así:

Private Sub TextBox2_Change()
Se utiliza para
Dim filalibre As Integer convertir el texto
filalibre = [Link] en Valor numérico
Range("A" & filalibre).Select
ActiveCell.FormulaR1C1 = Val(TextBox2)
End Sub

Luego da doble clic en el icono de Userform1(para que aparezca el formulario),

seguidamente le damos doble clic en el segundo cuadro de texto y nos lleva al
VBE, donde se escribe de forma automática, lo siguiente:
Private Sub TextBox3_Change()

End Sub

Ahora escribe lo siguiente dentro de ese procedimiento, así:

Excel Avanzado 83
Private Sub TextBox3_Change()
Dim filalibre As Integer
filalibre = [Link] Range("C" &
filalibre).Select ActiveCell.FormulaR1C1 = Se utiliza para aplicar
Val(TextBox3) formato de moneda al
[Link] = "$ #,##0" valor numérico

End Sub

Luego da doble clic en el icono de Userform1(para que aparezca el formulario1),

seguidamente le damos doble clic en el botón de comando – ACEPTAR - y nos
lleva al VBE, donde se escribe de forma automática, lo siguiente:
Private Sub CommandButton1_Click()

End Sub Significa Botón Es el Evento por defecto

de Comando asociado a los botones de
No.1 comando

Ahora escribe lo siguiente dentro de ese procedimiento, así:

Informática II 84
 Luego da doble clic en el icono de Userform1(para que aparezca el
formulario), seguidamente le damos doble clic en el botón de comando –
SALIR - y nos lleva al VBE, donde se escribe de forma automática, lo
siguiente:
Private Sub CommandButton2_Click()

End Sub Significa Botón

de Comando
No.2

Ahora escribe lo siguiente dentro de ese procedimiento, así:

Private Sub CommandButton2_Click()

Unload Me Instrucción que se utiliza para
[Link] borrar la información de los
End Sub controles del formulario

Método que se utiliza para

quitar el formulario de la
pantalla

Luego de haber terminado la programación del formulario, falta finalizar la

programación de los otros procedimientos con que se aplicarán los botones de
comando, así:

Ahora ve a la hoja1 donde está el menú principal y da doble clic sobre el botón
CREAR CLIENTE, te llevará al VBE y escribe lo siguiente, así:
Instrucción que se utiliza
Private Sub CommandButton1_Click() para cargar la información
Load UserForm1 de los controles del
formulario 1 sin mostrarlo
[Link]
End Sub
Método que se utiliza para
mostrar el formulario 1 en la
pantalla

Ahora ve al menú INSERTAR y selecciona USER FORM, aparecerá un

formulario sin ningún control (fondo gris), luego ve al menú VER y selecciona
Cuadro de Herramientas

Excel Avanzado 85
y luego aparecerá un cuadro de diálogo con
todos los botones de controles ActiveX, (Ver Etiqueta
imagen).
Luego en la ventana propiedades del
formulario 2 en la opción Caption (Se utiliza
para colocar el nombre del botón en el Cuadro
Botón de
formulario) coloca CONSULTA DE SALDO Combinado
comando
CLIENTE

Ahora da clic en el icono de cuadro

Combinado y coloca el puntero del mouse sobre la ventana del formulario

y arrástralo dibujando un rectángulo. (Ver imagen)

Luego da clic en el icono de Etiqueta y coloca el puntero del

mouse sobre la ventana del formulario y arrástralo dibujando un rectángulo
(Ver imagen)
Luego en la ventana propiedades en la opción Caption (Se utiliza para
colocar el nombre del botón en el formulario) coloca SELECCIONE EL
CLIENTE.
Seguidamente selecciona en la opción FONT : y en tamaño coloca: 11

Luego ve al cuadro de herramientas de controles y da clic en icono de Botón

de comando y luego dibuja un rectángulo dentro del formulario (ver
imagen)

Luego en la ventana propiedades en la opción Caption (Se utiliza para

colocar el nombre del botón de opción dentro del marco) coloca
CONSULTAR

En primer lugar le damos clic derecho en el icono de Userform2(ventana

Explorador de Proyectos) o Ctrl +R y seleccionamos la opción Ver código y
en la lista de objetos (a la izquierda) seleccionamos Userform y en la lista de
Eventos (a la Derecha) elegimos Initialize y escribimos lo siguiente:

Private Sub UserForm_Initialize()

Range("B1").Select
Do While ActiveCell <> Empty
[Link](1, 0).Select

Informática II 86
 [Link] ActiveCell
Loop
End Sub
Este método se utiliza para asignar los
valores que van a componer la lista del
cuadro combinado

Luego da doble clic en el icono de Userform2(para que aparezca el

formulario2), seguidamente le damos doble clic en el botón de comando –
CONSULTAR - y nos lleva al VBE, donde se escribe de forma automática, lo
siguiente:
Private Sub CommandButton1_Click()

End Sub
Significa Es el Evento por defecto
Botón de asociado a los botones de
Comando No.1 comando

Ahora escribe lo siguiente dentro de ese procedimiento, así:

Ahora ve a la hoja1 donde está el menú principal y da doble clic sobre el botón
CONSULTAR CLIENTE, te llevará al VBE y escribe lo siguiente, así:

Excel Avanzado 87
Private Sub CommandButton2_Click() Instrucción que se utiliza
Load UserForm2 para cargar la información
[Link] de los controles del
formulario 2 sin mostrarlo
End Sub

Método que se utiliza para

mostrar el formulario2 en la
pantalla

Ahora vamos a programar los demás procedimiento que hacen falta.

Primero realiza lo siguiente: Ir al menú Insertar → Módulo.

Luego ir al menú Insertar → Procedimiento... 2

Nos aparece un cuadro de diálogo como vemos en la imagen 1

donde le damos el Nombre: ACTUALIZA_SALDO
Ahora en Ámbito de ejecución, seleccionamos Privado, por último
dar clic en Aceptar y se crea el Procedimiento con el nombre
propuesto, donde escribiremos las instrucciones necesarias de la
macro, así:

Ahora escribe el siguiente procedimiento, así:

Informática II 88
 Sub irhoja_abono() Método que se utiliza para
Sheets("Hoja2").Select trasladar el cursor y mostrar la
End Sub hoja 2 donde está la tabla de
captura de abonos a clientes

Ahora vamos a asignar el

procedimiento a una macro, así:

Ve a la hoja 1 y da clic derecho sobre

el botón INGRESO DE ABONO y
selecciona la opción ASIGNAR
MACRO.. y se selecciona la macro
irhoja_abono y dar clic en Aceptar.

Ahora escribe el siguiente procedimiento, así:

Ahora asígnale la macro a su botón pertinente, según los pasos anteriormente

vistos.

Luego, escribe el siguiente procedimiento, así:

Sub grabar_archivo() Método que se utiliza para
[Link] grabar los cambios realizados
en el archivo actual
End Sub

Excel Avanzado 89
 Ahora asígnale la macro a su botón pertinente, según los pasos anteriormente
vistos.

Luego, escribe el siguiente procedimiento, así:

Sub terminar() Método que se utiliza para
[Link] salir del programa de Excel
End Sub

Ahora asígnale a las macros hechas en VBA su botón de comando pertinente,

según los pasos anteriormente vistos.

Recuerda: Los botones mencionados están ubicados en la hoja 1

EDH (El Estudiante Dice Y Hace)

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

FECHA SECCION
NOMBRES APELLIDOS REGIONAL SALARIO
INGRESO
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

2. Colócale el formato que se muestra en la imagen

3. Colócale el título : TALLER DE MACROS AUTOMÁTICAS EXCEL

Informática II 90
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

EJERCICIO 2 : Formularios
Para realizar este taller Ud. debe de entra a internet y copia la

Excel Avanzado 91
siguiente dirección y bajar el archivo del ejercicio correspondiente
a esta unidad:

[Link] o entra al blog:

[Link]

EJERCICIO 3 : Formularios
En el archivo propuesto y ya creado por Ud. con el nombre de
Control de pagos de clientes favor realizar las siguientes macros
y añádalas a las actuales, así:

1- En la hoja 2 - Macro para Regresar al Menú Principal

2- En la hoja 1 - Macro para Buscar un Cliente
3- En la hoja 1 – Macro para Colocar nombre a la hoja actual
4- En la hoja 1 – Macro para Imprimir la información con los 10
clientes.

Informática II 92
 4. BIBLIOGRAFIA
 Internet
o [Link].c
om
o [Link].
com
o [Link]
[Link]
o [Link]
o [Link]
[Link]

 Excel 4.0 para Windows Miguel A. Rodríguez McGraw-Hill

 Recursos Informáticos Michéle Amelot Ediciones ENI
VBA Excel 2007
 Curso Excel Experto
Aulas Digitales

Excel Avanzado 93