excel Microsoft 2007 - cucea.udg.mx · PDF fileFiltros avanzados 17 Tablas dinámicas 22...
Transcript of excel Microsoft 2007 - cucea.udg.mx · PDF fileFiltros avanzados 17 Tablas dinámicas 22...
Coordinación de Tecnologías para el AprendizajeEducación Continua
Microsoft
avanzadoexcel 20
07
Microsoft
avanzadoexcel 20
07
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na1
Manual de Excel Avanzado
Educación Continua
Coordinación de Tecnologías para el Aprendizaje
Centro Universitario de Ciencias Económico Administrativas
Beatriz Sofía Romo Valadez Blanca Cristina Sánchez Meza Elena Castro Rivas José Rubén Zúñiga Romero Laura Calderón Partida Mara Beatriz Coral Sandoval
Compiladoras
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na2
Directorio:
Dr. Marco Antonio Cortés Guardado
Rector General
Dr. Migue Ángel Navarro Navarro
Director Ejecutivo
Lic. José Alfredo Peña Ramos
Secretario General
Mtro. Itzcóatl Tonatiuh Bravo Padilla
Rector del Centro Universitario de Ciencias Económico Administrativas
Dr. Adrián de León Arias
Secretario Académico
Dr. Everardo Partida Granados
Secretario Administrativo
Lic. Andrés López Díaz
Secretario de Vinculación y Desarrollo Empresarial
Mtro. Jorge Lozoya Arandia
Coordinador de Tecnologías para el Aprendizaje
Lic. Edna Minerva Barba Moreno
Administradora
Beatriz Sofía Romo Valadez Blanca Cristina Sánchez Meza Elena Castro Rivas José Rubén Zúñiga Romero Laura Calderón Partida Mara Beatriz Coral Sandoval
Compiladoras
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na3
La Universidad de Guadalajara se reserva todos los derechos de autor. Se permite la reproducción parcial o total para uso personal y privado, su almacenamiento de manera impresa o en soporte electrónico, siempre y cuando sea con fines educativos y de investigación, no se altere el contenido del mismo y se cite la fuente.
No se permite la reproducción total o parcial, el almacenamiento, el alquiler, la transmisión o la transformación de este recurso informativo, en cualquier forma o por cualquier medio, sea electrónico o mecánico, mediante fotocopias, digitalización u otros métodos para su comercialización directa e indirecta, sin el permiso previo por escrito del titular.
Citar la fuente de la siguiente manera:
Educación Continua. (Diciembre, 2012). Manual Excel avanzado. Jalisco: Universidad de Guadalajara.
Coordinación de Tecnologías para el Aprendizaje, Educación Continua Periférico Norte N° 799, Núcleo Universitario Los Belenes, C.P. 45100, Zapopan, Jalisco, México.
Tel: (33) 3770 3300 ext. 25460/25042 http://www.cucea.udg.mx/?q=acerca/conoce/cta/administracion/educacion-continua
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na4
Contenido Temático Introducir una formula 1
Fórmula 1
Estructura de una función simple 1
Estructura de una función lógica simple 2
Pestaña fórmulas 2
Método que utilizan las fórmulas para calcular valores: operadores 3
Operadores de cálculo de las fórmulas 3
Operadores aritméticos 4
Operadores de comparación 4
Operador de concatenación de texto 4
Operadores de referencia 5
Orden de precedencia de los operadores en las fórmulas 5
Funciones lógicas 6
SI 6
Ejemplo de anidación 7
Función O 7
Función Y 8
Funciones de búsqueda y referencia 8
BUSCARV 8
Validación 9
Formato condicional 11
Filtros automáticos 13
Filtros con formato de tabla 16
Filtros avanzados 17
Tablas dinámicas 22
Gráficos dinámicos. 26
Formas de protección 29
Protección de libro 30
Macros 31
Crear una macro 31
Ejecutar una macro 34
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na5
Usar referencias relativas 38
Ver las instrucciones de la macro 39
Activar la pestaña Programador 42
Teclas de acceso rápido en Excel 43
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na1
Introducir una formula Fórmula
Una fórmula es una expresión que contiene nombres de funciones, referencias a
celdas y/o valores, así como operadores, que genera un valor nuevo. Una formula
comienza siempre por un signo igual (=).
Para introducir una formula se tendrá en cuenta los siguientes pasos:
1. Haga clic en la celda en la que desea introducir la formula.
2. Escriba un signo igual (=).
3. Introduzca el nombre de la fórmula así como los datos o nombres de las
celdas que se involucrarán en la función. A medida que se escribe la fórmula
en la celda, también aparece en la parte derecha de la barra de formulas.
4. Presione Enter cuando termine de introducir la información de su fórmula.
Podrá observar que en dicha celda aparece ya un resultado.
Estructura de una función simple
Nombre de la función Paréntesis
Signo Igual = Buscarv (A2, D5:H20, 2, 0) Valor buscado Ordenador Matriz de Búsqueda Indicador de Columnas
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na2
Estructura de una función lógica simple
Nombre de la función Paréntesis
Signo Igual = Si (A2>B2,A2,B2) Prueba lógica Valor Verdadero Valor Falso
Sugerencias:
Para introducir la misma fórmula en un rango de celdas, seleccione en primer lugar,
el rango, introduzca la formula y, a continuación presione CTRL + Enter.
También puede introducirse una formula en un rango de celdas, copiando una
fórmula de otra celda1.
Pestaña fórmulas
Esta pestaña está situada en la 4ta posición de los menús. Contiene elementos
para insertar las fórmulas (agrupadas por categoría: financieras, lógicas, texto, etc), para
nombrar rangos, y para insertar y quitar flechas de precedencia y dependencia de
fórmulas.
1 Cuando se copia una fórmula a una celda, de forma horizontal (estando en la misma fila pero en diferente columna) cambiará la letra de la columna en la fórmula. Si se copia una fórmula a una celda, de forma vertical (estando en la misma columna pero en diferente fila) cambiará el número de la fila en la fórmula.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na3
Al dar clic en Insertar función, aparece un
cuadro de dialogo, el cual nos permite
elegir e insertar cualquier tipo de fórmula,
seleccionando algunas de las que nos
ofrece en su listado
Los botones que le siguen al botón de Insertar fórmula, tienen las mismas
características que el cuadro de diálogo que se abre en esta función. Y sólo se
muestran de manera gráfica en la pestaña Fórmulas.
Asignar nombre a un rango permite identificar a un grupo de celdas bajo un
mismo nombre. Y el registro de tales asignaciones se encuentra en el botón
Administrador de nombres.
Rastrear la precedencia y dependencia de las fórmulas es útil para localizar de
manera rápida las celdas que intervienen en cada fórmula.
Método que utilizan las fórmulas para calcular valores: operadores
Como ya se mencionó, una fórmula es una ecuación que realiza operaciones
con los datos de una hoja de cálculo. Las funciones pueden realizar operaciones
matemáticas, como suma o multiplicación; comparar los valores de una hoja de cálculo
o bien, combinar texto. Las formulas pueden hacer referencia a otras celdas en la
misma hoja de cálculo del mismo libro o a celdas en hojas de otros libros.
Operadores de cálculo de las fórmulas
Los operadores especifican el tipo de calculo que se desea realizar con los
elementos de una formula. Microsoft Excel incluye cuatro tipos diferentes de
operadores de cálculo: aritmético, comparación, texto y referencia.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na4
Operadores aritméticos Para ejecutar las operaciones matemáticas básicas como suma, resta o
multiplicación, combinar números y generar resultados numéricos, utilice los siguientes
operadores aritméticos:
Operador Aritmético Significado Ejemplo.
+ (signo más) Suma 3+3
- (signo menos) Resta / Negación 3-1 ó –1
* (asterisco) Multiplicación 3*3
/ (barra oblicua) División 3/3
% (signo de porcentaje) Porcentaje 20%
^ (acento circunflejo) Exponente. 3^2 (el mismo que 3*3)
Operadores de comparación Se pueden comprar dos valores con los siguientes operadores. Al comparar dos
valores con estos operadores, el resultado es un valor lógico: VERDADERO o FALSO
Operador de Comparación. Significado Ejemplo.
= (igual) Igual a A1=B1
> (mayor que) Mayor que A1>B1
< (menor que) Menor que A1<B1
>= (mayor o igual que) Mayor o igual que A1>=B1
<= (menor o igual que) Menor o igual que A1<=B1
<> (distinto) Distinto de A1<>B1
Operador de concatenación de texto Utilice el signo”&” para unir o conectar una o varias celdas de texto con el fin de
generar un solo elemento de texto.
Operador de Texto. Significado. Ejemplo.
& (“y “ comercial) Conecta o concatena dos
valores para generar un
“Viento” & “norte “ genera
“Vientonorte”
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na5
valor de texto continuo
Operadores de referencia Combinan rangos de celdas para los cálculos con los siguientes operadores:
Operador de Referencia. Significado. Ejemplo.
: (dos puntos) Operadores de rango que
generan una referencia a
todas las celdas entre dos
referencias, éstas incluidas.
B5:B15
, (coma) Operador de unión que
combina varias referencias
en una sola.
SUMA (B5:B15,D5:D15)
Orden de precedencia de los operadores en las fórmulas
Si se combinan varios operadores en una única fórmula, Microsoft Excel
ejecutará las operaciones en el orden que se indica en la tabla que se muestra a
continuación. Si una formula contiene operadores con el mismo orden de precedencia
(por ejemplo, si una formula contiene un operador de multiplicación y otro de división),
Excel evaluará los operadores de izquierda a derecha. Para cambiar el orden de
evaluación, escriba entre paréntesis la parte de la fórmula que se calculará en primer
lugar.
Operador Descripción
: (dos puntos) (un espacio) , (coma)= Operadores de referencia.
- Negación (como en –1)
% Porcentaje
^ Exponente
* y / Multiplicación y división
+ y - Suma y resta
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na6
& Conecta dos cadenas de texto (concatenación)
=<> <= >= <> Comparación
Funciones lógicas
SI Devuelve un valor si una prueba lógica, al evaluarla es “verdadera” y otra
diferente si es “falsa”.
Se usa para efectuar pruebas condicionales sobre valores y fórmulas y para
causar bifurcaciones basándose en el resultado de la condición. El resultado de
la prueba determina el valor que devolverá la función SI.
Su Sintaxis es:
SI(prue_logica,valor_si_verdad,valor_si_falso)
Donde “prue_logica” es cualquier valor o expresión que pueda evaluarse como
verdadero o falso.
“valor_si_verdad” es el valor que se devolverá si “prue_logica” es VERDADERA
“valor_si_falso “ es el valor que se devolverá si prue_logica es FALSO
Se pueden anidar hasta 7 funciones SI
Ejemplos:
A B C 1 Descripción Precio
anterior Precio Nuevo
2 Licuadora SX500 $ 125.50 $ 198.30 3 Plancha Vapor GX $ 89.90 $ 101.10 4 Batidora SX $ 158.00 $ 145.00 5 Freidora Taurus $ 215.00 $ 205.20
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na7
Tomando los datos de la tabla anterior, las fórmulas:
=SI(B2>C2,”Aumento Precio”, “Reducción de Precio”) da como resultado el
letrero de “Aumento de Precio”
=SI(PROMEDIO(B2:B5)>PROMEDIO(C2:C5),”En promedio, disminuyo precio”, ”En
promedio, aumentó de precio”) da como resultado, ”En promedio, aumentó de
precio”
Ejemplo de anidación
Escriba en la celda A3 una fórmula para saber si en la celda A1 está un valor
POSITIVO, NEGATIVO O CERO (que se escriba el texto). En Excel se representaría
el diagrama anterior con la siguiente fórmula escrita en la celda A3
=SI(A1=0,”CERO”,SI(A1<0,”NEGATIVO”,”POSITIVO”))
\___\_____/\_____________________________/
Condic1 Val. si Verdad1 Valor si Condic1 falsa
Algunas ocasiones es necesario hacer consultas o comparaciones que
involucren 2 o más condiciones para las cuales se cumplen ciertos eventos, si el
evento se cumple tendríamos como resultado VERDADERO y si no se cumple se
tendría un FALSO.
NOTA: La prueba_lógica y el valor_si_verdadero, pueden anidarse hasta 64 veces
o niveles.
Función O
Esta función regresa el valor de VERDADERO, si alguna condición se cumple,
pero si ninguna condición se cumple, tendrá el valor de FALSO.
Su sintaxis es O(condición1,condición2,...)
Ejemplos: =O(2=3,3>5)
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na8
El resultado es FALSO
También si A5=10 y B20=50 =O(9<A5,100=B20)
Da como resultado VERDADERO
Función Y
Esta función regresa el valor de VERDAD (TRUE) si todas las condiciones se
cumplen, pero si alguna no se cumple, regresara el valor de FALSO (FALSE) Su
Sintaxis es la siguiente: Y(condición1,condición2,....)
Ejemplos: =Y(4+3=10,5>1)
Da el resultado de FALSO
También si C4=15 y B34=34 =Y(C4+50>B34,100-25>b34,10<C4)
Da como resultado VERDADERO También si D1=3,D2=-1,D3=10
=Y(D1>0,D2>0,D3>0) Da como resultado FALSO.
Funciones de búsqueda y referencia BUSCARV Busca un valor en la primera columna, es decir, verticalmente de una selección
de tablas y devuelve un valor en la misma fila de otra columna de la selección
de tablas. Existe una variante de esta fórmula, la cual busca horizontalmente un
valor, de ahí su nombre BUSCARH.
Sintaxis:
=BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)
NOTA:
Valor_buscado: Valor que se va a buscar en la primera columna de la matriz de
tabla.
Matriz_buscar_: en Dos o más columnas de datos. Use una referencia a un
rango o un nombre de rango.
Indicador_columnas: Número de columna de matriz_buscar_en desde la cual
debe devolverse el valor coincidente.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na9
Ordenado: Valor lógico que especifica si BUSCARV va a buscar una coincidencia
exacta o aproximada, este argumento puede ser omitido.
Ejemplo:
=BUSCARV(B1,Hoja2!A2:D7,2,FALSO)
Validación Validar significa condicionar el ingreso de datos dentro de una celda.
Estas condiciones pueden ser muy variadas, desde un número entre un criterio
mayor y otro menor, hasta la forma de ingresar la fecha, hora o alguna longitud
de texto. Para hacer uso de la validación es necesario seguir los siguientes
pasos:
Nos posicionamos en la
pestaña de Datos y
seleccionamos la opción
Validación de datos.
Al seleccionar la opción de validación de datos se visualiza el siguiente
cuadro de dialogo:
En la opción de “Permite” de la pestaña Configuración se despliegan las
diferentes opciones para ingresar los criterios de validación, al seleccionar
cualquiera de las opciones aparecen un par de espacios para ingresar datos
(según sea el tipo que se haya elegido)
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na10
Una vez seleccionado los criterios nos posicionamos en la pestaña de
Mensaje entrante, donde daremos
título y escribiremos indicaciones de
qué es lo que se debe de hacer. Estas
indicaciones las veremos como un
comentario al posicionarnos sobre la
celda que contenga la validación.
Hecho esto nos posicionamos en la pestaña de Mensaje de Error.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na11
Aquí personalizaremos el cuadro de dialogo que mostrará el error. Para
ello contamos con tres estilos:
Grave: marca el error al usuario y no permite ingresar datos que no
estén dentro de las especificaciones.
Advertencia: marca el error y habilita la celda para que se modifique
si se desea.
Información: solo señala al usuario las condiciones para ingresar
datos, pero no lo limita para introducción de datos.
Formato condicional El formato condicional es hacer identificable una celda de una forma
rápida y eficaz. Esta función consiste en aplicar un formato según una condición
especificada.
Para aplicar formato condicional, realice lo siguiente:
1. Abra o cree un archivo de Excel el cual contenga datos que puedan ser
evaluados a través de criterios.
2. Colóquese en la celda o seleccione un rango se celdas al que aplicara el
formato condicional y, vaya a la ficha Inicio, y en el grupo Estilos,
presione el botón Formato condicional.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na12
3. Seleccione el tipo y subtipo de formato condicional que dese aplicar.
● Resaltar reglas de celdas Establece el criterio basándose en el contenido de
las celdas.
● Reglas superiores e inferiores Establece un criterio para buscar los valores
más altos o más bajos en un grupo de celdas.
● Barras de datos Rellena las celdas con colores degradados mismos que en
base a la longitud de la barra representan el valor contenido en la celda.
● Escalas de color Rellena las celdas con colores de diferentes tonos mismos
que representa los valores superiores, medios e inferiores.
● Conjuntos de iconos Aplica un icono el cual representa los valores contenidos
en las celdas.
● Nueva regla Permite establecer una nueva regla (criterio) personalizada.
● Borrar reglas Elimina las reglas (criterios) establecidas en las celdas.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na13
● Administrar reglas Permite ver, editar o eliminar reglas (criterios).
Escriba los criterios y especifique el formato a aplicar en caso que se cumpla la
condición y presione Aceptar.
4. Los datos que cumplan con el criterio especificado en la condición, se
mostraran con el formato seleccionado.
Filtros automáticos
En Excel los filtros automáticos proporcionan un acceso rápido a la gestión de
listas de datos. De manera simple se pueden filtrar o eliminar rápidamente aquellos
datos que se desea o no ver o imprimir; es decir, los datos que no cumplen los criterios
que se han especificado, simplemente no se muestran; los restantes registros
permanecen visibles en la hoja de cálculo.
Para aplicar un filtro debe seleccionarse los encabezados de la tabla en manejo,
seleccionar la pestaña Datos y hacer clic en Filtro. En seguida notará que aparecen
unos triángulos en el lado derecho de cada encabezado.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na14
Al dar clic sobre las flechas que aparecen a la hora de insertar el filtro se abre un
cuadro de dialogo donde nos permite ordenar de mayor a menor, por orden alfabético
ascendente o descendente.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na15
Seleccionando la opción Filtro de texto nos da la opción para hacer una
comparación directa de valores (Es igual a…, No es igual a…, Comienza por…) Así
mismo, los información que se desea filtrar puede personalizarse eligiendo la opción
Filtro personalizado. Al elegir esta opcion se abrirá la siguiente ventana:
Desde ahí se pueden realizar más comparaciones y se pueden elegir
condiciones relacionadas mediante “Y” u “O”. Dependiendo del operador relacional
será el resultado que genere el filtro.
Operador Valor de la premisa 1 Valor de la premisa 2 Valor final
Y
V V V
V F F
F V F
F F F
O
V V V
V F V
F V V
F F F
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na16
Tabla de verdad de los operadores lógicos “Y” y “O”
Dado que se están utilizando condiciones relacionadas, el valor de una premisa afecta a
la otra según el operador que se esté utilizando. Como se puede observar en la tabla
anterior, con el operador Y basta que el valor de alguna de las premisas sea falsa para
que el resultado2 sea falso. Mientras que con el operador O basta que el valor de
alguna de las premisas sea verdadera para que el resultado sea verdadero.
Filtros con formato de tabla Una forma igual de sencilla para insertar un filtro pero de una manera más
vistosa con múltiples formatos de relleno y contorno es la siguiente:
1. Primero se hace una selección completa de los datos que se quieren manejar.
2. Hecha la selección hay que ir a
la pestaña de Insertar y
seleccionar la opción de Tabla
3. Hecho esto, Excel muestra una cuadro de dialogo para dar la ubicación de la
tabla.
4. Dar clic en Aceptar y de inmediato se obtendrá la misma tabla que se había
seleccionado, pero ahora no sólo con filtros sino también con bordes y tramas,
es decir, con un formato de tabla. Por lo tanto, al posicionarse en cualquier
2 Por resultado se entiende el despliegue final de los datos que se mostrarán en el filtro.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na17
celda dentro de la tabla creada se habilitará una nueva pestaña llamada
Herramientas de tabla con una subpespaña llamada Diseño.
Nombre de la tabla Herramientas Estilos de tabla
Datos externos de tabla Opciones de Estilo de Tabla Filtros avanzados La utilización de un filtro avanzado es bastante sencilla, pero requiere un poco
de atención en su elaboración. A diferencia de un filtro automático, los filtros
avanzados permiten mostrar los resultados filtrados en un lugar diferente al de la tabla
original. Además dan la posibilidad de colocar más de dos condiciones “Y” y “O”.
Para realizar un filtro avanzado es necesario especificar el rango de la lista, el
rango de criterios y, de manera opcional la ubicación que tendrá la tabla sobre la cual
se aplicará el filtro.
Para crear filtros avanzados deben seguirse los siguientes pasos:
1. Hacer una copia de los encabezados de la tabla.
2. Pegarlos en algún lugar de la hoja o en otra hoja del libro sobre el cual
se está trabajando3.
3 Si se desea, puede escribir de forma manual los nombres de los encabezados; no importa si escribe con mayúsculas o minúsculas, únicamente debe respetar los acentos.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na18
3. Dar clic en la pestaña Datos y elegir la opción Avanzadas.
4. Establecer el rango de lista.
5. Establecer el rango de criterios.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na19
6. De manera opcional se puede elegir la opción de hacer una copia de la
tabla e indicar la ubicación en la que se encontrará.
Las opciones contenidas en la ventana de filtro avanzado son las siguientes:
• Filtrar la lista sin moverla a otro lugar.- Esto hará que el filtro se aplique
en la tabla original.
• Copiar a otro lugar.- Al tener seleccionada esta opción se activará de
manera automática la caja de texto Copiar a:
• Rango de lista.- Es la tabla donde se encuentran los datos que se desean
filtrar.
• Rango de criterios.- Es el espacio en el cual se escribirán los criterios que
Excel ha de seguir para filtrar los datos. Esta selección abarca DESDE el
nombre de los encabezados que se pegaron (ver paso 2 de la hoja
anterior) HASTA la última línea que contendrá texto con criterios.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na20
• Copiar a:.- Si se seleccionó la opción de Copiar a otro lugar habrá
notado que se activó esta casilla; aquí deberá introducirse o
seleccionarse el espacio en el cual se hará la copia de la tabla, y en esta
copia será donde se aplique el filtro.
• Sólo registros únicos.- Si en la tabla original había registros repetidos, al
tener seleccionada esta opción, no se mostrarán aquellos que se repiten.
Una vez hecho lo anterior, deberán introducirse los criterios (en las líneas que
comprenden el rango de criterios especificado). Los criterios pueden contener
operadores de comparación4 o simplemente texto.
Como ya se había explicado al inicio de este tema, los filtros avanzados
permiten colocar más de dos condiciones “Y” u “O”. Cuando los criterios se colocan en
una misma línea (dentro del rango de criterio) se leen (y actúan) como Y. Cuando los
criterios se colocan en diferente línea (dentro del rango de criterio) se leen (y actúan)
como O.
Nota:
• Si en el rango de criterio se especificaron 3 líneas para los criterios, deben –
forzosamente- utilizarse esas tres líneas, de lo contrario no se hará ningún
filtrado.
• Después de aplicar un filtro avanzado, si se desea volver a visualizar la tabla
“original” se deberán quitar los criterios (del rango de criterios) e ir nuevamente
4 Ver tema de operadores de comparación
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na21
a la pestaña Datos y la opción de avanzadas.
Resultado de aplicar un filtro avanzado cuyo criterio es que el país fuera Cubana. El filtro está aplicado sobre la
tabla original. En este caso, los rangos de criterio abarcaron de A53:D54
Resultado de aplicar un filtro avanzado cuyos criterios son que el autor sea Bierce, Ambrose Y el país fuera
Estadounidense. El filtro está aplicado sobre la tabla original. En este caso, los rangos de criterio abarcaron de
A53:D54
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na22
Resultado de aplicar un filtro avanzado cuyos criterios son que el autor sea Anónimo O, que el tipo fuera Teatro Y que el país fuera Española. El filtro está aplicado sobre una copia de la tabla. En este caso, los rangos de criterio abarcaron de
A53:D55
Tablas dinámicas
A partir de una base de datos, Excel es capaz de generar resúmenes de tipo
estadístico que reciben el nombre de tablas dinámicas. De esta forma, se elaboran
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na23
resúmenes de un campo de la base de datos, en función de los distintos valores que
tomen otros dos, los cuales se representan en las celdas de la primera fila y primera
columna de la tabla respectivamente.
Para ilustrar esto, suponga que dispone de una base de datos como la
siguiente:
A partir de estos datos, se va a generar una tabla dinámica que presente un
resumen estadístico del campo Precio, en función de los distintos valores que tomen
los campos Sección y País origen.
Excel pone a su disposición un asistente que le guiará durante todo el proceso
de creación de la tabla. Y el procedimiento será el siguiente:
1. Seleccione todo el rango de la base de datos (en el ejemplo, A2:E17) o bien,
sitúe el cursor sobre cualquiera de las celdas que forman parte de la base de
datos.
2. Despliegue el menú de la pestaña Insertar y seleccione la opción tabla
dinámica.
a. Se visualizará el siguiente cuadro de diálogo:
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na24
3. En este cuadro de dialogo aparece el rango que ya tenemos seleccionado
en la opción tabla o rango, además de que nos da la opción de que el
resultado de estos datos se pueda mostrar en esta misma hoja, en una hoja
nueva de cálculo o en una hoja de cálculo existente, con tan solo seleccionar
su ubicación.
4. Para terminar la creación de nuestra primera tabla dinámica damos clic en
Aceptar.
Como se puede observar en la imagen anterior, al insertar una tabla dinámica
aparece en la hoja de Excel tres nuevos elementos:
a
b
c)
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na25
a) Pestaña de Herramientas de tabla dinámica.- Contiene dos “sub
pestañas” Opciones y Diseño; las cuales contienen herramientas para
insertar otros elementos a la tabla y darle formato, respectivamente.
b) Vista preliminar de la tabla dinámica.- Como su nombre lo indica nos
muestra una vista de la creación de la tabla, dependiendo de los
campos que se le hayan agregado y el orden donde se posicione cada
campo en la lista de campos de la tabla dinámica.
c) Lista de campos de la tabla dinámica.- Está formada por cuatro
elementos principales, los cuales son:
• Filtro de informe: Proporciona el campo seleccionado pero
añadiendo un Autofiltro, el cual contendrá toda la información
que contenía ese encabezado.
• Rótulos de Columna: Muestra la información contenida en el
campo de forma horizontal, además también añade un
Autofiltro. Es recomendable para los campos que tienen poca
información.
• Rótulos de Fila: Funciona de la misma manera que el rótulo de
columna, a excepción de que la información es presentada de
forma vertical. Es recomendable para los campos que tienen
mucha información.
• Valores: Presenta por default la sumatoria del campo que se
encuentre dentro de esta área. Si son valores numéricos
mostrará el total de su sumatoria5; si son caracteres de texto
mostrará el conteo.
5. A continuación arrastramos los campos que deseamos a lugar en el cual
queremos que aparezcan (filtro de informe, rótulos de columna, rótulos de
fila, valores) O bien, los palomeamos directamente en la lista de campos de
la tabla dinámica.
Esta es la posible vista de la tabla ya manipulada:
5 Además de mostrar sumatorias, se puede mostrar: Promedio, Max, Min, etc., dando clic en la flecha que aparece en el nombre del campo/configuración de campo de valor siempre y cuando sean valores de tipo numérico (no carácter).
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na26
Nota:
• Para deshabilitar un campo solo se deshabilita la casilla de selección o lo
arrastramos de nuevo al área de campos de selección de campos.
Gráficos dinámicos.
Los gráficos dinámicos tienen la ventaja de combinar el resumen de datos de la
tabla dinámica y el atractivo visual del gráfico. Puede servir para mostrar distintas
formas de ver los mismos datos, para hacer comparaciones y tendencias. Un gráfico
dinámico siempre está vinculado a un informe de tabla dinámica y estará basado en
una base de datos. Se denominan gráficos dinámicos porque al momento de estarse
creando pueden manipularse los datos que se desea mostrar y/o comparar.
Para crear un gráfico dinámico se puede utilizar cualquier tipo de gráfico,
excepto los de dispersión, de burbujas y de cotizaciones.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na27
Suponga el ejemplo de la base de datos utilizado para la realización de tablas
dinámicas. A partir de estos datos se va a generar un gráfico dinámico que presente el
Precio en función de los distintos valores de los campos Sección y País origen. Para ello,
realice los siguientes pasos:
1. Seleccione el rango de datos de la base de datos (A2:E17) o sitúe la celda
activa en cualquiera de las celdas que forman parte de las bases de datos.
2. En la pestaña Insertar elija la opción Tabla dinámica, despliegue la lista y
seleccione la opción Gráfico dinámico. 3. Aparecerá el siguiente cuadro de Dialogo:
4. Damos clic en Aceptar, y el gráfico estará listo ser manipulado.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na28
Esta es la vista de un gráfico dinámico ya modificado:
De la misma forma que ocurría en las tablas dinámicas, al insertar un gráfico
dinámico se habilita una pestaña más (Herramientas del gráfico dinámico) con sus
respectivas “sub pestañas” (Diseño, Presentación, Formato, Analizar) dentro de las
cuales puede aplicársele formato al gráfico.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na29
Formas de protección
En Excel la información puede ser protegida a nivel de hoja, es decir, impide que
se realicen cambios no deseados en los datos contenidos en las celdas
bloqueadas de una hoja y permite también especificar que celdas serán las que
si permitan modificaciones a su contenido, o a nivel de todo el libro, es decir,
restringe la creación u modificación de hojas, así como de su estructura. Para
proteger una hoja o un libro, realice lo siguiente:
Protección de hoja 1. Abra el libro que contiene la(s) hoja(s) que desee proteger y ubíquese en una
de ellas.
2. Vaya a la ficha Revisar y, en el grupo Cambios, haga clic en Proteger hoja.
3. En la ventana mostrada, deje marcada la casilla Proteger hoja y contenido de
celdas bloqueadas para que la protección solo se realice a las celdas
bloqueadas, si lo desea puede establecer una contraseña para que solo quien la
conozca pueda desproteger la hoja y marque solo las casillas necesarias según
lo que desee permitir que sea modificable en las celdas bloqueadas y, presione
Aceptar.
NOTA: Cuando intente realizar alguna modificación de las no permitidas en la
hoja, Excel mandará el mensaje siguiente:
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na30
Protección de libro
1. Abra el libro que desee proteger.
2. Vaya a la ficha Revisar y, en el grupo Cambios, haga clic en Proteger libro y,
en Restringir edición, seleccione la opción: Proteger estructura y ventanas
3. En la ventana mostrada, marque las opciones necesarias según la protección
deseada y, presione Aceptar.
NOTA: Cuando intente realizar alguna modificación y está, no esté permitida,
Excel mostrará el mensaje siguiente:
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na31
Macros
Excel, al igual que el resto de los programas de la paquetería de Office, cuenta
con un lenguaje de programación llamado Visual Basic for Applications, a partir del cual
es posible resolver problemas de manera rápida y sencilla. Y es en este lenguaje con el
que se graban las macros.
Una macro es un conjunto de acciones que después de ser grabadas podrán ser
introducidas de manera automática. Se utilizan principalmente para llevar a cabo tareas
repetitivas que llevaría mucho tiempo estarlas haciendo una por una para diferentes
datos.
Crear una macro
Antes de grabar una macro, es necesario planificar muy bien los pasos que han
de seguirse para dar solución al problema que se quiere resolver mediante su uso. Es
útil guiarse mediante la respuesta a las siguientes preguntas ¿Qué quiero hacer?, ¿Qué
pasos voy a seguir?, ¿Dónde se encuentran las herramientas que voy a necesitar?
Por ejemplo, suponga que desea cambiar el formato a un conjunto de celdas no
contiguas. El formato que quiere aplicar es: letra tipo Calibri, tamaño 16, color rojo en
negritas, texto centrado y relleno color amarillo. Hasta esta parte, usted ya sabe lo que
quiere hacer. El siguiente punto es identificar los pasos que ha de seguir para hacerlo.
Puede establecer cambiar primero el tipo de letra, luego el tamaño, luego el color de la
letra y el estilo, después la alineación centrada y finalmente el color de relleno. Como
ya sabe los pasos que ha de seguir, finalmente hay que identificar el lugar en el que se
encuentran los elementos que se necesitan para llevar a cabo el cambio de formato.
Para el ejemplo, todos se encuentran ubicados en la pestaña Inicio en el apartado
Fuente.
De esta manera, ya está listo para crear una macro:
a) En la pestaña Vista6, seleccionar el ícono con el nombre de Macros, y
elegir la opción Grabar macro…
6 Otra manera de insertar una macro es desde la pestaña Programador (ver anexos)
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na32
b) Aparecerá el siguiente cuadro de dialogo:
En el espacio de Nombre de la macro: asignamos un nombre
específico (no debe comenzar con número ni llevar espacios ni
caracteres especiales).
Método abreviado: Asignamos una literal para la ejecución de
la macro (este espacio si diferencia mayúsculas de minúsculas).
Grabar libro en: Indicamos dónde se quiere guardar la macro.
Nos establece tres posibles opciones:
o Libro macros personal: La macro se grabara en un libro actual
(en una hoja especial)
o Este libro: La macro se almacena en el libro actual.
o Libro nuevo: La macro se almacenara en un libro aparte y
estas se podrán utilizar en otros libros.
• Descripción: Aquí podemos añadir una pequeña reseña de lo
que hará la macro
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na33
c) Hacemos clic sobre el botón Aceptar para comenzar la grabación de la
macro. A partir de este momento todos los cambios que se realizan
dentro de la hoja de Excel se tomarán como parte de la macro, por lo
que hay que tener especial cuidado de lo que comience a hacerse para
evitar posibles errores, ya que todo se grabará en un módulo de Visual
Basic.
Continuando con el ejemplo que se dio para dar la introducción a las macros, a
continuación se muestran las imágenes de los pasos a seguir de acuerdo a lo
establecido:
Menú Vista/Macros/Grabar macro… Ingresar nombre de la macro y Descripción
Comenzar a grabar la macro y seguir los pasos establecidos. Nótese que en la imagen
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na34
el dato de la columna A ya tiene el formato deseado.
Para detener la macro hay que dar clic en el cuadro azul que aparece en la parte
inferior izquierda.
Ejecutar una macro Existen varias formas de ejecutar una macro:
a) Método abreviado.- Tecleando la letra establecida al grabar la macro.
b) Botón de formulario.- Éste se inserta del menú Programador7 en el
apartado Controles, la opción Insertar.
7 Si no está activado ir al botó n de Office
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na35
Al dar clic sobre la opción del botón, la forma del cursor cambia y nos da la
posibilidad de crear (como si se tratara de una autoforma) el botón. Dándole el tamaño
deseado en la posición deseada. Una vez hecho esto, se abrirá una ventana en la cual
se muestran las macros que se tienen grabadas en el libro actual. Ahí hay que
seleccionar la macro deseada.
c) Autoforma.- Insertamos una autoforma del menú Insertar
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na36
Una vez elegida la autoforma deseada, hay que arrastrarla dentro del área de
trabajo hasta que quede del tamaño deseado. Dar clic derecho sobre ella, elegir la
opción Asignar macro…
En la ventana que se abre, elegir la macro deseada y dar clic en Aceptar.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na37
d) Ver macros/Ejecutar.- Desde el menú Programador en el apartado de
Código en la opción Macros elegir la macro que se desea aplicar y dar
clic en Ejecutar.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na38
Nota:
• El formato se aplicará a la celda sobre la que se esté posicionado al momento de
utilizar cualquiera de los métodos descritos en este apartado.
Usar referencias relativas
Un aspecto a tener en cuanta cuando se graba una macro, es la forma en que se
diferenciarán las direcciones de celdas que se incluyan en la macro.
En principio, cualquier referencia a una celda se almacena dentro de la macro
como una dirección absoluta en la forma L1C1, es decir la celda B5 se almacenará como
L5C2(fila 5 y columna 2 de la hoja). Pero también existe la posibilidad de utilizar
referencias relativas. Por ejemplo L(6)C(-3) es una referencia relativa que indica la celda
situada 6 filas hacia abajo y 3 hacia la izquierda respecto a la celda actual.
Lo más común, es utilizar referencias relativas ya que de esta forma la macro
podrá ser ejecutada sobre celdas distintas a las que sirvieron de modelo para su
grabación o durante la misma.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na39
Para utilizarlas debe seguir los mismos pasos que para crear una macro
“normal”, únicamente hay que dar clic en Usar referencias relativas antes de
comenzar a grabarla.
Cuando termine de grabar la macro, recuerde volver a la celda “A1” y después
detener macro.
Ver las instrucciones de la macro
Si se desea visualizar las instrucciones incluidas en una macro, se procede como
se indica a continuación.
1. Despliegue el menú Programador y seleccione la opción Macros.
2. En la lista Nombre de la macro, seleccione la macro que desea
visualizar o modificar.
3. Hacer clic sobre el botón Modificar.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na40
De esta manera se accederá al editor de Visual Basic y se mostrará el nombre de
la macro (antecedida por la palabra Sub), la descripción puesta a la macro al momento
de grabarla y el código correspondiente a los clics que se realizaron al grabarla.
4. Cuando se quiere abandonar el editor y regresar a la hoja, se despliega
el Menú Archivo y se selecciona la opción Cerrar y volver a Microsoft
Excel.
Crear una macro desde la pestaña Programador
1. Ir a la pestaña Programador. Ahí encontrará varios botones, entre ellos el de Grabar macro dentro del bloque llamado Código.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na41
2. Al dar clic sobre esa opción se desplegará el cuadro de diálogo:
o En el espacio de Nombre de la macro: asignamos un nombre
específico (no debe comenzar con número ni llevar espacios ni
caracteres especiales).
o Método abreviado: Asignamos una literal para la ejecución de la
macro (este espacio si diferencia mayúsculas de minúsculas).
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na42
o Grabar libro en: Indicamos dónde se quiere guardar la macro. Nos
establece tres posibles opciones:
o Libro macros personal: La macro se grabara en un libro actual
(en una hoja especial)
o Este libro: La macro se almacena en el libro actual.
o Libro nuevo: La macro se almacenara en un libro aparte y
estas se podrán utilizar en otros libros.
• Descripción: Aquí podemos añadir una pequeña reseña de lo
que hará la macro
Hacemos clic sobre el botón Aceptar para comenzar la grabación de la macro. Activar la pestaña Programador
1. Dar click sobre el botón de Office, el cual se encuentra en la
parte superior izquierda de la ventana del programa.
2. Se desplegará el siguiente menú. Ahí deberá elegir Opciones de Excel.
3. A continuación se abrirá una ventana. En ella hay que dar click en la opción Más
frecuentes y palomear el check box que pertenece a Mostrar ficha de
programador en cinta
de opciones.
Universidad de Guadalajara Centro Universitario de Ciencias Económico Administrativas
Elaboró: Educación Continua Fecha de elaboración: 18/09/2010 Fecha de actualización: 17/12/2012
Pági
na43
Anexos Teclas de acceso rápido en Excel
Teclas rápidas Control (+) Inicio Ir hasta la primera columna Control (+) Fin Ir hasta la última columna Shift (+) F11 Insertar una nueva hoja Control (+) Av. Pág. Ir a la siguiente hoja Control (+) Re Pág. Ir a la hoja anterior Control (+) E Seleccionar toda la hoja Control (+) Z Deshacer la última acción Control (+) C Copiar las celdas seleccionadas. Control (+) X Cortar las celdas seleccionadas Control (+) V Pegar el contenido del portapapeles Control (+) Barra espaciadora
Seleccionar una columna
Control (+) Barra espaciadora y enseguida Control (+) Tecla +
Insertar una columna
Control (+) Barra espaciadora y enseguida Control (+) Tecla -
Eliminar una columna
Control (+) 0 Ocultar columnas Control (+) ) Mostrar columnas Shift (+) Barra espaciadora Seleccionar una fila Shift (+) Barra espaciadora y enseguida Control (+) Tecla +
Insertar una fila
Shift (+) Barra espaciadora y enseguida Control (+) Tecla -
Eliminar una fila
Control (+) 9 Ocultar filas Control (+) ( Mostrar filas