Manual Microsoft Excel 2010 Avanzado
Manual Microsoft Excel 2010 Avanzado
Manual Microsoft Excel 2010 Avanzado
Elaborado por:
Junio 2011
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 1
NDICE
Introduccin
Descripcin General del curso
84
REFERENCIAS
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 2
INTRODUCCIN
Excel es, hoy por hoy, la hoja de clculo ms utilizada en el mercado. Excel
permite guardar, manipular, calcular y analizar datos numricos, textos y frmulas,
adems de poder representar todos estos datos a travs de grficos de distinto
tipo, que facilitan su interpretacin. Puede resultar de enorme utilidad para realizar
desde las ms simples operaciones, como pueden ser llevar la contabilidad del
hogar, registrar la asistencia a cursos, hasta aquellas ms complejas, (como
establecer previsiones sobre posibles inversiones, realizar anlisis estadsticos,
entre otros.
Microsoft Office Excel 2010 es una potente herramienta que se puede usar para
crear y aplicar formato a hojas de clculo, y para analizar y compartir informacin
para tomar decisiones mejor fundadas. La nueva interfaz orientada a obtener
resultados, la visualizacin de datos enriquecida y las vistas de tabla dinmica
permiten crear, de un modo ms sencillo, grficos de aspecto profesional y fcil
uso. A estas capacidades se suma la posibilidad de utilizarla como base de datos.
Excel trabaja con hojas de clculo que estn integradas en libros de trabajo. Un
libro de trabajo es un conjunto de hojas de clculo y otros elementos como
grficos, hojas de macros, etc. El libro de trabajo contiene hojas de clculo que se
pueden eliminar, insertar, mover, copiar, cambiar de nombre.
Por otra parte permite hacer uso de hojas de clculo para mostrar y analizar datos.
Pueden introducirse y modificarse los datos simultneamente en varias hojas de
clculo y pueden ejecutarse los clculos basndose en los datos de varias hojas
de clculo. Si se crea un grfico, ste puede colocarse en la hoja de clculo con
sus datos correspondientes o en una hoja de grfico.
El presente manual ha sido creado para ensear a los usuarios, de manera clara y
amena, el uso de aplicaciones avanzadas en Excel. Aqu se desarrolla en forma
concisa y prctica la utilizacin de las herramientas avanzadas de una de las
hojas de clculo ms poderosas y populares bajo el ambiente de Windows.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 3
En el primer captulo, permite hacer un resumen referencial de elementos bsicos
para el manejo del programa que permiten nivelar los conocimientos en el manejo
de las herramientas y la interfaz de usuario la cual se presenta algunos cambios
con respecto a versiones anteriores.
Finalmente en el ltimo captulo, se tratan todos los aspectos relacionados con las
diversas herramientas de seguridad de los archivos, para la restriccin a la hora
de hacer uso de un libro de trabajo. Y posteriormente se tocan algunas
herramientas actualizadas que trae la nueva versin 2010.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 4
DESCRIPCIN GENERAL
Objetivo General:
Desarrollar habilidades en el uso de herramientas avanzadas de Microsoft Excel
2010, las cuales apoyen procesos de anlisis y toma de decisiones en la
organizacin.
Objetivos Especficos:
Describir los elementos fundamentales para el manejo de las principales
herramientas y funciones de la aplicacin.
Describir las herramientas avanzadas orientadas hacia el manejo de bases
de datos y su aplicacin en casos prcticos para la obtencin de reportes
ajustados a las necesidades del entorno.
Aplicar herramientas de optimizacin de tareas en Excel a travs del uso de
procesos automatizados que permitan desarrollar actividades
eficientemente.
Conocer los diversos procedimientos para aplicar reglas de seguridad a los
libros de trabajo, para asegurar la integridad de los datos y la informacin.
Duracin: 16 Horas.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 5
Unidad 1. Introduccin a Excel Avanzado
Caractersticas generales.
Microsoft Excel 2010 es una Hoja de Clculo con mltiples funciones que facilitan
la elaboracin de clculos. Su interfaz y las barras de herramientas permiten
resolver modelos matemticos con alto grado de calidad y precisin. Se destaca
por ser una de las aplicaciones ms difundidas en el mundo; ya que despus del
procesador de textos, es la que cuenta con mayor nmero de usuarios. Su
potencialidad permite resolver problemas en todas las reas desde los niveles
ms bsicos, hasta los avanzados de ingeniera, administracin, contadura,
finanzas, entre otros.
Provee herramientas comprensivas para ayudar a crear, analizar y compartir
planillas, dndole al usuario la posibilidad de crear planillas de clculo, a travs de
su diversidad de herramientas para el manejo de bases de datos extensas,
permitiendo generar reportes rpidamente, basado en las necesidades del
usuario. Tambin podr analizar su informacin con grficos y vistas dinmicas
con Pivot Table; tambin otorga la posibilidad de subir esta informacin a la Web
para la visualizacin y colaboracin universal.
Excel, puede guardar casi 4.300 millones de letras, nmeros, signos, etc. Por otra
parte, hay que tener en cuenta que, en modo Standard, cada Archivo que se crea
cuando se inicia la aplicacin conforma lo que se denomina Libro, el cual est
conformado bsicamente por tres Hojas, cada una de las cuales, puede almacenar
toda esa informacin. Y el sistema, permite agregar, si hiciera falta, varias hojas
de clculo ms.
Al entrar al programa se encuentra una ventana con un ambiente de trabajo propio
de la aplicacin como se observa a continuacin, aunque en ciertas ocasiones el
ambiente esta adecuado al usuario que maneja continuamente la aplicacin en el
ordenador que se est utilizando.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 6
Se visualiza en la pantalla una cuadrcula, en el borde superior una serie de
rtulos con letras que indican las Columnas y a la izquierda otros rtulos con
nmeros que indican las Filas. La interseccin de una columna con una fila
determina lo que se denomina Celda y esta celda es llamada por la interseccin
de los rtulos, como por ejemplo: A1, B3, F15, etc. En cada celda se pueden
almacenar: un nmero, un texto o una frmula. Para permitirle explorar grandes
cantidades de datos en hojas de clculo, Office Excel 2010 admite hasta 1 milln
de filas y 16.000 columnas por hoja de clculo. Especficamente, la cuadrcula de
Office Excel 2010 tiene 1.048.576 filas por 16.384 columnas, lo que ofrece un
1.500% ms de filas y un 6.300% ms de columnas comparadas con las que
haba en Microsoft Office Excel 2003. Para su informacin, ahora las columnas
acaban en XFD en lugar de acabar en IV.
En lugar de 4000 tipos de formato, ahora puede utilizar un nmero ilimitado en el
mismo libro y el nmero de referencias de celda por celda se aumenta de 8000 al
que limite la memoria disponible.
Para mejorar el rendimiento de Excel se ha aumentado la administracin de
memoria de 1 GB de memoria en Microsoft Office a 2 GB en Office Excel 2010.
Tambin experimentar una aceleracin de los clculos en hojas de clculo de
gran tamao con muchas frmulas gracias a la compatibilidad de Office Excel
2010 con varios procesadores y conjuntos de chips multiproceso. Office Excel
2010 admite asimismo hasta 16 millones de colores.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 7
Estas planillas en EXCEL son denominadas Hojas y estas hojas forman un Libro,
este libro es almacenado en un archivo. De esta forma, se denominan a cada
celda por el nombre de la hoja y la direccin de la celda, separadas por el smbolo:
!, por ejemplo: Hoja2!A5. La celda que esta recuadrada con un borde grueso (A1),
esta celda se denomina Celda Activa y es aquella en la que se est posicionado.
Interfaz de usuario en Office 2010
La nueva interfaz de usuario orientada a los resultados facilita el trabajo en
Microsoft Office Excel. Los comandos y funciones que solan estar ocultos tras
complejos mens y barras de herramientas ahora son ms fciles de encontrar en
las fichas orientadas a tareas que contienen grupos lgicos de comandos y
caractersticas. Muchos cuadros de dilogo han sido reemplazados por galeras
desplegables que muestran las opciones disponibles. Adems, se ofrece
informacin descriptiva sobre herramientas y vistas previas de ejemplo para
ayudarle a elegir la opcin adecuada.
Independientemente de la actividad que est llevando a cabo en la nueva interfaz
de usuario, ya sea aplicar formato a datos o analizarlos, Excel presenta las
herramientas que resultan ms tiles para completar dichas tareas correctamente.
Al entrar al programa se encuentra una ventana con un ambiente de trabajo propio
de la aplicacin como se observa a continuacin, aunque en ciertas ocasiones el
ambiente esta adecuado al usuario que maneja continuamente la aplicacin en el
ordenador que se est utilizando.
Elementos bsicos de la nueva interfaz de usuario de algunos de los productos de
la suite Microsoft Office. Los principales programas de Office tienen una nueva
interfaz de usuario (IU) que reemplaza los mens, barras de herramientas y la
mayora de los paneles de tareas de las anteriores versiones, con un solo
mecanismo que es sencillo e intuitivo.
Esta nueva interfaz ha sido diseada de forma que le ayude a ser ms productivo
y que sea ms fcil buscar las caractersticas adecuadas para varias tareas,
descubrir la nueva funcionalidad y ser ms eficaz.
Esta nueva interfaz ha sido diseada de forma que le ayude a ser ms productivo
y que sea ms fcil buscar las caractersticas adecuadas para varias tareas,
descubrir la nueva funcionalidad y ser ms eficaz.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 8
La Cinta de Opciones contiene todas las opciones del programa agrupadas en
pestaas. Al hacer clic en Insertar, por ejemplo, se vern las operaciones
relacionadas con la insercin de los diferentes elementos que se pueden crear en
Excel. Todas las operaciones se pueden hacer a partir de estos mens. Pero las
ms habituales se podran aadir a la barra de acceso rpido como se ver
posteriormente. En algunos momentos algunas opciones no estarn disponibles,
las reconocers porque tienen un color atenuado.
Por otra parte cuenta con las Fichas Contextuales que no son ms que fichas
adicionales que aparecen con determinados conjuntos de comandos que solo son
relevantes cuando se editan objetos de un determinado tipo, es por ello que las
fichas contextuales aparecen cuando son necesarias, facilitando mucho la
bsqueda y el uso de los comandos justos para la operacin que se va a realizar.
Por ejemplo, los comandos para editar grficos no son relevantes hasta que
aparece un grfico en la hoja de clculo y el usuario est intentando modificarlo.
As mismo cuenta con una Barra de Acceso Rpido, a travs de la cual se tiene
acceso a comandos de uso continuo, en esta barra estos comandos estn siempre
visibles y a la mano permitiendo al usuario personalizarla de acuerdo a sus
necesidades.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 9
La ficha Archivo es la ficha de color, ubicada en la esquina superior izquierda de
los programas de Microsoft Office 2010.
Al hacer clic en la pestaa Archivo, se ven muchos de los comandos bsicos que
se vean al hacer clic en el botn de Microsoft Office o en el men Archivo en
versiones anteriores de Microsoft Office, como Abrir, Guardar e Imprimir.
Haga clic en Opciones de Excel en la parte inferior de este men y, a
continuacin, haga clic en cualquiera de las categoras de la lista a la izquierda.
En versiones anteriores de Excel, poda definir opciones en el cuadro de dilogo
Opciones, que se abra en el men Herramientas. En esta versin, muchas de
esas opciones estn disponibles a travs del botn de Microsoft Office, donde
estn ms visibles y ms cerca para trabajar con archivos viejos o nuevos.
Manejo de funciones Avanzadas
De todas las ventajas que presenta la herramienta, una de las principales es el
uso de las funciones para generar resultados a la hora de procesar datos, dentro
de una base de datos.
Para ello se hacen uso de las referencia de celdas que no son ms que nombres
que identifican una celda o un rango de celdas en una hoja de clculo e indica a
Microsoft Excel en qu celdas debe buscar los valores o los datos que se desea
utilizar en una frmula. En las referencias se pueden utilizar datos de distintas
partes de una hoja de clculo en una frmula o bien, utilizar el valor de una celda
en varias frmulas. Tambin puede hacerse referencia a las celdas de otras hojas
en el mismo libro, a otros libros y a los datos de otros programas. Las referencias
a celdas de otros libros se denominan referencias externas. Las referencias a
datos de otros programas se denominan referencias remotas.
Dependiendo de cul sea su uso existen tres tipos de referencias:
Referencia Relativa: Las referencias de filas y columnas cambian si se copia la
formula en otra celda, es decir se adapta a su entorno porque las referencias las
hace con respecto a la distancia entre la frmula y las celdas que forman parte de
la formula. Esta es la opcin que ofrece Excel por defecto.
Ejemplo: A B
1 15 20
2 =A1 + 2 30
Si ahora se copia la celda A2 en B3, como se copia una columna hacia la derecha
y en una fila hacia abajo, la frmula cambiar por: =B2+2 . Lo que variar es la
referencia a la celda A1, al copiarla una columna hacia la derecha se incrementar
el nombre de la columna en uno, es decir, en vez de A pondr B y al copiarla una
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 10
fila hacia abajo en vez de fila 1 pondr 2 , resultado =B2+2. Para mantener en la
frmula sumar 2 al contenido de la celda superior.
Referencia Absoluta: Las referencias de filas y columnas no cambian si se copia la
formula a otra celda, las referencias a las celdas de la formula son fijas.
Suponiendo el ejemplo: A B
1 15 20
2 =$A$1 + 2 30
Las funciones en Excel estn organizadas por categoras orientadas hacia su rea
de aplicacin, pero existen ciertas funciones de uso comn que pueden ser
aplicadas en cualquier tipo de rea, con la finalidad de obtener resultados basados
en las necesidades de los usuarios. De ellas se hablar a continuacin
Funcin condicional SI
Devuelve un valor si la condicin especificada es VERDADERO y otro valor si
dicho argumento es FALSO.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 11
Se debe utilizar SI para realizar pruebas condicionales en valores y frmulas.
Sintaxis
SI(prueba_lgica;valor_si_verdadero;valor_si_falso)
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 12
2.- Seleccionar el icono de insertar funcin, o si se conoce la categora a la que
pertenece la formula, se ubica dentro de la biblioteca y se selecciona de all
directamente la funcin solicitada. Tambin se puede acceder a esta
misma pantalla haciendo clic sobre el icono que se encuentra
ubicado en la barra de frmulas.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 13
Funcin Y
Sintaxis
Y(valor_lgico1;valor_lgico2; ...)
Observaciones
Funcin O
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 14
Sintaxis
O(valor_lgico1;valor_lgico2; ...)
Observaciones
Una funcin lgica puede ser parte del argumento de otra, para entender el
concepto se presenta un ejemplo de asignacin de cdigos a ciertos valores, con
las siguientes condiciones:
Dato Menor a 50 ---> Bajo, Mayor a 50 y menor que 100 ---> Medio, Mayor que
100---> Alto
Para construir una funcin lgica anidada se deben seguir los siguientes pasos,
para explicar los mismos se har a travs del siguiente ejemplo.
Dada la siguiente tabla, calcular el nuevo precio basado en lo siguiente:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 15
1.- Llamar el asistente para funciones por cualquiera de los mtodos ya conocidos.
2.- Seleccionar la categora de funciones lgicas y all dar clic en la funcin SI
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 16
4.- Para introducir la segunda condicin se hace clic en el cuadro de nombres
donde se llama nuevamente la funcin condicional seguros de estar posicionados
en el valor falso para que la nueva condicin quede anidada
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 17
Es de hacer notar que cada vez que se necesite anidar una nueva condicin se
procede a realizar el paso nmero 4 hasta un mximo de 7 condicionales
anidadas.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 18
Para colocar los valores en orden ascendente, elija el comando Ordenar del
men Datos y seleccione la opcin Ascendente.
Los valores de la primera columna de matriz_buscar_en pueden ser texto,
nmeros o valores lgicos.
El texto en maysculas y en minsculas es equivalente.
Indicador_columnas: Es el nmero de columna de matriz_buscar_en desde la cual
debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual
a 1, la funcin devuelve el valor de la primera columna del argumento
matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el
valor de la segunda columna de matriz_buscar_en y as sucesivamente. Si
indicador_columnas es menor que 1, BUSCARV devuelve el valor de error
#VALOR!; si indicador_columnas es mayor que el nmero de columnas de
matriz_buscar_en, BUSCARV devuelve el valor de error #REF!.
Ordenado: Es un valor lgico que especifica si BUSCARV debe localizar una
coincidencia exacta o aproximada. Si se omite o es VERDADERO (1), devolver
una coincidencia aproximada. En otras palabras, si no localiza ninguna
coincidencia exacta, devolver el siguiente valor ms alto inferior a valor_buscado.
Si es FALSO(0) , BUSCARV encontrar una coincidencia exacta. Si no encuentra
ninguna, devolver el valor de error # N/A.
Bsqueda Horizontal (Buscarh())
Busca un valor en la fila superior de una tabla o una matriz de valores y, a
continuacin, devuelve un valor en la misma columna de una fila especificada en
la tabla o matriz. Use BUSCARH cuando los valores de comparacin se
encuentren en una fila en la parte superior de una tabla de datos y desee
encontrar informacin que se encuentre dentro de un nmero especificado de filas.
Use BUSCARV cuando los valores de comparacin se encuentren en una
columna a la izquierda o de los datos que desee encontrar.
La H de BUSCARH significa "Horizontal".
Sintaxis:
BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado).
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 19
Si ordenado es VERDADERO, los valores de la primera fila de
matriz_buscar_en deben colocarse en orden ascendente: ...-2, -1, 0, 1, 2,...
, A-Z, FALSO, VERDADERO; de lo contrario, BUSCARH puede devolver un
valor incorrecto. Si ordenado es FALSO, no es necesario ordenar
matriz_buscar_en.
El texto en maysculas y en minsculas es equivalente.
Se pueden poner los datos en orden ascendente de izquierda a derecha
seleccionando los valores y eligiendo el comando Ordenar del men Datos.
A continuacin haga clic en Opciones y despus en Ordenar de izquierda a
derecha y Aceptar. Bajo Ordenar por haga clic en la fila deseada y despus
en Ascendente.
Indicador_filas: Es el nmero de fila en matriz_buscar_en desde el cual debe
devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la
primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la
segunda fila en matriz_buscar_en y as sucesivamente. Si indicador_filas es
menor que 1, BUSCARH devuelve el valor de error #VALOR!; si indicador_filas es
mayor que el nmero de filas en matriz_buscar_en, BUSCARH devuelve el valor
de error #REF!.
Ordenado: es un valor lgico que especifica si BUSCARH debe localizar una
coincidencia exacta o aproximada. Si es VERDADERO o se omite, devolver una
coincidencia aproximada. Es decir, si no encuentra ninguna coincidencia exacta,
devolver el siguiente valor mayor inferior a valor_buscado. Si es FALSO,
BUSCARH encontrar una coincidencia exacta. Si no encuentra ninguna,
devolver el valor de error #N/A.
Uso de la funcin en Falso.
En cualquiera de las dos funciones BuscarV o BuscarH, se utiliza FALSO en su
ltimo argumento cuando el valor buscado es un dato preciso, especifico. Debido s
esto si la funcin no encuentra un dato igual al buscado, la misma arroja el valor
de #N/A.
Para visualizar mejor este caso se explicara mejor con el siguiente ejemplo:
Se tiene una base de datos estructurada relacionada con el inventario de
productos de una empresa. (Esa base de datos que servir como matriz de
bsqueda, podr encontrarse en la misma hoja, en una hoja diferente o en otro
libro ajeno al que se est usando).
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 20
Se necesita hacer una consulta de precios de un producto en particular y para ello
se utilizara la funcin ConsultaV (debido a que es una Tabla Vertical), bajo los
siguientes pasos.
1.- Seleccionar del asistente para funciones la categora de Funciones de
bsqueda y referencia, como muestra la figura:
2.- Aparecer un cuadro de control con los argumentos de la funcin los cuales
sern llenados segn los requerimientos como se muestra en la figura
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 21
Uso de la funcin en Verdadero.
En cualquiera de las dos funciones ConsultaV o ConsultaH, se utiliza
VERDADERO en su ltimo argumento cuando el valor buscado es un dato de
coincidencia exacta o aproximada. Debido si esto si la funcin no encuentra un
dato igual al buscado, selecciona el valor aproximado ms cercano por debajo del
mismo. En caso de que no encuentre alguno, arroja el valor de #N/A.
Se necesita conocer el nivel de inventario segn la cantidad de unidades que hay
en existencia y para ello se utilizara la funcin ConsultaV (debido a que es una
Tabla Vertical), bajo los siguientes pasos.
1.- Seleccionar del asistente para funciones la categora de Funciones de
bsqueda y referencia, como en el ejemplo anterior.
2.- Aparecer un cuadro de control con los argumentos de la funcin los cuales
sern llenados segn los requerimientos como se muestra en la figura.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 22
Nota: El rango numrico es creado tomando el lmite inferior de cada uno de los
rangos que muestra la tabla. Es importante resaltar que en este tipo de casos la
tabla debe estar ordenada de menor a mayor para que funcione correctamente.
Funcin Elegir
Utiliza el argumento nm_ndice para devolver un valor de una lista de argumentos
de valores. Utilice ELEGIR para seleccionar uno de los 29 valores posibles a partir
del rango del argumento ndice. Por ejemplo, si valor1 a valor 7 son los das de la
semana, ELEGIR devuelve uno de los das cuando se utiliza un nmero entre 1 y
7 como argumento nm_ndice .
Sintaxis
ELEGIR(nm_ndice;valor1;valor2;...)
Nm_ndice especifica el argumento de valor que se selecciona. El argumento
nm_ndice debe ser un nmero entre 1 y 29, o bien una frmula o referencia a
una celda que contenga un nmero entre 1 y 29.
Si nm_ndice es 1, ELEGIR devuelve valor1; si es 2, ELEGIR devuelve
valor2 y as sucesivamente.
Si nm_ndice es menor que 1 o mayor que el nmero del ltimo valor de la
lista, ELEGIR devuelve el valor de error #VALOR!
Si nm_ndice es una fraccin, se trunca al entero inferior antes de ser
utilizada.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 23
Valor1, valor2,... son de 1 a 29 argumentos de valores entre los cuales
ELEGIR selecciona un valor o una accin que se ejecuta basndose en el
argumento nm_ndice. Los argumentos pueden ser nmeros, referencias a
celdas, nombres definidos, frmulas, funciones o texto.
Observaciones
Si nm_ndice es una matriz, cada valor se evaluar cuando se ejecute
ELEGIR.
Los argumentos de valor para ELEGIR pueden ser referencias de rango,
as como valores individuales.
Por ejemplo se tiene la siguiente tabla de ventas de un periodo donde se
visualizan los datos de cada venta y se necesita saber en una columna adicional el
trimestre al cual pertenece cada venta.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 24
2.- Aparecer un cuadro de control con los argumentos de la funcin los cuales
sern llenados segn los requerimientos como se muestra en la figura.
Funcin Contar.si
Cuenta las celdas, dentro del rango, que no estn en blanco y que cumplen con el
criterio especificado.
Sintaxis
CONTAR.SI(rango;criterio)
Rango es el rango dentro del cual desea contar las celdas.
Criterio es el criterio en forma de nmero, expresin, referencia a celda o texto,
que determina las celdas que se van a contar. Por ejemplo, los criterios pueden
expresarse como 32, "32", ">32", "manzanas" o B4.
Funcin Sumar.si
Suma las celdas en el rango que coinciden con el argumento criterio.
Sintaxis
SUMAR.SI(rango;criterio;rango_suma)
Rango es el rango de celdas que desea evaluar.
Criterio es el criterio en forma de nmero, expresin o texto, que determina las
celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32,
"32" ">32", "manzanas".
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 25
Rango_suma son las celdas que se van a sumar. Este tercero es opcional, esto
quiere decir que si la condicin est en el mismo rango donde se efecta la suma,
no hace falta el tercer parmetro, pero si el criterio est en un rango y donde se
hace la suma en otro (u otros) rangos, entonces tiene que colocarse el tercer
parmetro.
FUNCION SUMAR.SI.CONJUNTO
Desde la versin de Excel 2007 se incorpora la funcin nueva,
SUMAR.SI.CONJUNTO, que es parecida a SUMAR.SI, la cual suma celdas
teniendo en cuenta un solo criterio, en tanto que SUMAR.SI.CONJUNTO, suma
celdas teniendo en cuenta mltiples criterios.
Sintaxis:
SUMAR.SI.CONJUNTO(rango de sumas; criterio_rango1; criterio1; criterio rango2;
criterio2..)
donde:
Rango de sumas: es un argumento obligatorio, en el que se suman una o mas
celdas.
criterio_rango1: es un argumento requerido que es el primer rango en el que se
evala el criterio asociado (criterio1).
criterio1: argumento obligatorio que es un criterio asociado a criterio_rango1 en la
forma de nmero, expresin, referencia de celda o texto, que define en cual celda,
en el rango de sumas, se adicionar debido al criterio_rango1.
Los otros argumentos son opcionales.
Se ver un ejemplo donde se consideran 3 criterios:
Un negocio de venta de vinos cuenta con la siguiente tabla
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 26
Se requiere saber la cantidad de unidades Mayores a 5 Vendidas por vendedor y
por producto como lo muestra la siguiente tabla:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 27
Obsrvese que el primer criterio es el rango de unidades que se van a sumar si
cumplen con las tres condiciones propuestas. Para dar como resultado un total de
16 unidades que corresponden a Buchanan Steven, del producto Champagne y
que son superiores a 5 unidades.
FUNCION CONTAR.SI.CONJUNTO
Excel 2007 incorpora una funcin nueva, CONTAR.SI.CONJUNTO, que es
parecida a CONTAR.SI, la cual cuenta celdas teniendo en cuenta un solo criterio,
en tanto que CONTAR.SI.CONJUNTO, cuenta celdas teniendo en cuenta
mltiples criterios.
Sintaxis:
CONTAR.SI.CONJUNTO(rango1; criterio1; rango2; criterio2)
donde :
rango1: es el rango donde se cuentan las celdas, debido al criterio1, criterio2..
Los primeros dos argumentos son obligatorios.
Se ver un ejemplo donde se consideran 3 criterios:
Un negocio de venta de vinos cuenta con la siguiente tabla, la cual refleja en cada
fila las ventas realizadas durante un periodo de tiempo
Se requiere saber la cantidad de ventas por vendedor y por producto, que sean
superiores a 5 unidades como lo muestra la siguiente tabla:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 28
En respuesta a esta problemtica, se hace uso de la funcin Contar.Si.Conjunto,
la cual permite aplicar las tres condiciones para contar lo requerido. Es de hacer
notar que se pueden aplicar hasta un mximo de 127 criterios.
La funcin quedara de la siguiente manera:
Obsrvese que estn especificados cada uno de los criterios. Para dar como
resultado un total de 2 ventas que corresponden a Buchanan Steven, del producto
Champagne y que son superiores a 5 unidades.
FUNCION PROMEDIO.SI
De forma similar a la anterior funcin, permite determinar el promedio en un
conjunto de datos, dadas ciertas condiciones.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 29
Sintaxis:
PROMEDIO.SI(rango, criterio, rango_para_promediar)
donde :
rango: es el rango donde se buscan las coincidencias
Criterio: Argumento que sirve como valor de comparacin para el rango.
Rango_para_promediar: Elementos que se van a tomar en cuenta para calcular el
promedio.
Por ejemplo si se quisiera calcular el stock promedio del producto A que hay en los
tres almacenes, se usara la siguiente frmula:
=PROMEDIO.SI(C3:C8,G3,D3:D8)
Donde: el rango C3:C8 representa el rango donde se buscan las coincidencias con
G3 y se suma segn las coincidencia del rango D3:D8, el resultado obtenido sera
4.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 30
Unidad 2. Designar entradas de celdas vlidas
Validacin de datos
La validacin de datos se usa para controlar el tipo de datos o los valores que los
usuarios pueden escribir en una celda. Esta herramienta se utiliza cuando de
desea restringir la entrada de datos a un intervalo determinado por ejemplo de
edades, fechas, nmeros, textos, entre otros. En esta unidad se describe cmo
funciona la validacin de datos en Excel y se explica brevemente las diferentes
tcnicas de validacin de datos disponibles.
La validacin de datos es una funcin de Excel que permite establecer
restricciones respecto a los datos que se pueden o se deben escribir en una celda.
La validacin de datos puede configurarse para impedir que los usuarios escriban
datos no vlidos. Si se prefiere, puede permitir que los usuarios escriban datos no
vlidos en una celda y advertirles cuando intenten hacerlo. Tambin es posible
proporcionar mensajes para indicar qu tipo de entradas se esperan en una celda,
as como instrucciones para ayudar a los usuarios a corregir posibles errores de
transcripcin de datos.
La validacin de datos es sumamente til cuando desea compartir un libro con
otros miembros de la organizacin y se desea que los datos que se escriban en l
sean exactos y coherentes, basados en ciertos lineamientos.
Para aplicar algn tipo de validacin en una tabla se deben seguir los siguientes
pasos:
1. Seleccionar el rango de la hoja
de Excel que se desea validar.
2. Acceder a las opciones de
validacin haciendo clic la
pestaa Datos / Datos /
Validacin de Datos. All
aparecer el siguiente cuadro de
opciones:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 31
En este cuadro existen tres pestaas que van a permitir configurar los elementos
de validacin para ese rango de datos seleccionado.
3. En la primera pestaa Configuracin se seleccionan las opciones para el
tipo de validacin a aplicar y las caractersticas de la misma. Primeramente
en Permitir se da clic en el listado y se selecciona una de las opciones que
aparecen all predeterminadas como se muestra en la figura:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 32
4. Posteriormente en el rea de Datos se selecciona el tipo de operador
relacional que se va a utilizar para restringir el rea seleccionada y
opciones para complementar la validacin.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 33
6. Finalmente se encuentra la pestaa Mensaje de Error, la cual permite
modificar y personalizar el mensaje que aparece cuando el usuario
introduce un dato que no es vlido. En esta seccin se encuentra tambin
una lista desplegable en la cual se podr seleccionar cualquiera de los tres
tipos de error determinados dependiendo el uso que se le vaya a dar.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 34
Entre las principales aplicaciones que se le pueden dar a esta herramienta de
Validacin se encuentran las siguientes:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 35
2.- Cuando se desea validar por un listado de tal manera que al
posicionarse en la celda aparezca un listado de las posibles opciones
a seleccionar para incluirlas en la celda, como se muestra en la figura.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 36
Celdas validadas tipo lista.
Es importante resaltar que a partir de esta versin se puede usar una opcin
adicional de Validacin de datos que permite segn una validacin dada
Redondear aquellas celdas que no
cumplen con una condicin dada,
denominada Redondear con un crculo
datos no vlidos. Esa opcin se utiliza
cuando ya la base de datos est llena y
se quieren visualizar aquellos datos no
vlidos, como se observa en el ejemplo.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 37
Unidad 3. Formularios en el ambiente de Excel
Manejo de Formularios
Microsoft Excel proporciona varios controles para las hojas de dilogo. Se pueden
utilizar estos controles en hojas de clculo para ayudar a seleccionar los datos.
Los botones, las casillas de verificacin y otros controles disponibles en la barra
de herramientas Formularios proporcionan opciones cuando se utiliza un
formulario. Por ejemplo, si se crea un formulario personal, pueden agregarse dos
botones de opcin para especificar si un empleado trabaja a tiempo parcial o a
tiempo completo. Como solamente puede seleccionarse un botn de opcin a la
vez, el empleado podr trabajar a tiempo parcial o a tiempo completo, pero nunca
ambas opciones a la vez.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 38
Principales Controles
Icono Descripcin Icono Descripcin
Barra de
Cuadro de Grupo
desplazamiento
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 39
Al hacer clic en alguna celda para que el control de nmero no se seleccione. Al
hacer clic en el control hacia arriba o hacia abajo en el control de nmero, la celda
G18 se actualiza con un nmero que indica el valor actual del control de nmero
ms o menos el cambio incremental del mismo. El valor del control de nmero no
cambiar si el valor actual es 1 y hace clic en el control hacia abajo o si el valor
actual es 20 y hace clic en el control hacia arriba.
Botn
Este botn es usado para asignar una accin o
macro que se encuentra almacenado en el archivo.
Y para poder insertarlo se selecciona de Ficha del
Programador, el Subgrupo Controles, se hace clic
en el icono insertar y el los controles de formulario
se selecciona el Icono Botn y se dibuja un
cuadrado en la hoja de Excel del tamao del botn
que se necesita.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 40
Al hacer clic con el botn derecho del mouse aparecen las opciones que
permitirn modificar el formato del mismo y asignarle la macro que se necesite.
Botn de Opcin
Este control permite activar o desactivar una opcin. Se deben usar para permitir
solamente una de varias posibilidades.
Para utilizar este control, la Ficha del Programador, el Subgrupo Controles, se
hace clic en el icono insertar y de los controles de formulario se selecciona el
botn de opcin, se dibuja en la hoja de Excel y con el clic derecho del mouse se
procede a acceder a las propiedades de la casilla en la pestaa Control.
En esta ficha se modifican las siguientes opciones:
Valor: Determina el valor inicial del botn de opcin, si est Activado o
Desactivado.
Vincular con la celda: Devuelve el nmero del botn de opcin seleccionado en
el grupo de opciones (El primer botn de opcin es el nmero 1). Se utiliza la
misma celda Vincular con la Celda para todas las opciones de un grupo. Despus
se usar el nmero devuelto en una frmula o macro para responder a la opcin
seleccionada.
Por ejemplo si se crea un formulario personal con un botn de opcin denominado
Tiempo Completo, si el botn de opcin est activado o Tiempo Parcial si se ha
activado el segundo botn:
=si(ci=1; Tiempo Completo;Tiempo Parcial)
Sombreado 3D: Muestra la casilla de verificacin con un efecto de sombreado
tridimensional.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 41
Casillas de verificacin
Este control se activa o desactiva y permite activar ms de una casilla a la vez en
una hoja o en un grupo.
Para utilizar este control, se selecciona el control de la barra de herramientas, se
dibuja en la hoja de Excel y con el clic derecho del mouse se procede a acceder a
las propiedades de la casilla en la pestaa Control.
En esta ficha se modifican las siguientes opciones:
Valor: permite determinar el estado de la casilla de verificacin, es decir si esta
activada o desactivada.
Vincular con la celda: Es la celda que devuelve el estado de la casilla de
verificacin. Si la casilla de verificacin esta activada, la celda del cuadro vincular
con la celda, contiene VERDADERO. Si la casilla de verificacin esta desactivada,
la celda contiene FALSO. Si la casilla de verificacin es Mixta, la celda contiene
#N/A. Si la celda esta vacia, Excel interpreta que el estado de la casilla de
verificacin es FALSO.
Sombreado 3D: Muestra la casilla de verificacin con un efecto de sombreado
tridimensional.
Cuadro combinado
Es un control que le permite a Excel distinguir la posicin de un elemento en un
listado seleccionado. Para utilizar este control, se selecciona la Ficha del
Programador, el Subgrupo Controles, se hace clic en el icono insertar y el los
controles de formulario se selecciona el cuadro combinado, se dibuja en la hoja de
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 42
Excel y con el clic derecho del mouse se procede a acceder a las propiedades de
la casilla en la pestaa Control.
En esta ficha se modifican las siguientes opciones:
Nota
La casilla Sombreado 3D es opcional; aporta una apariencia tridimensional al
cuadro desplegable o combinado.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 43
Nota: Si en las fichas de trabajo de la cinta de opciones no
se encuentra la Ficha del Programador, la misma se activa
en la Ficha Archivo, dando clic en el botn opciones como
muestra la figura y aparecer el cuadro Opciones de Excel,
se selecciona la opcin Personalizar Cinta de Opciones, y en
el cuadro que muestra del lado derecho se visualiza una
opcin para activar o desactivar la mencionada Ficha.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 44
Unidad 4. Manejo de herramientas de base de datos
Filtros avanzados
El comando Filtro avanzado permite filtrar una lista en su lugar, como el comando
Autofiltro, pero no muestra listas desplegables para las columnas. En lugar de ello,
tiene que escribir los criterios segn los cuales desea filtrar los datos en un rango
de criterios independiente situado sobre la lista. Un rango de criterios permite filtrar
criterios ms complejos.
Una condicin en una columna u otra: Para buscar datos que cumplan una
condicin de una columna o una
condicin de otra, introduzca los
criterios en filas diferentes del
rango. Por ejemplo, el siguiente
rango de criterios muestra todas las
filas que contienen "Producto" en la
columna Tipo, "Davolio" en la
columna Vendedor o valores de
ventas superiores a 1.000 $.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 45
una columna, introduzca los criterios en filas independientes. Por ejemplo, el
siguiente rango de criterios muestra las filas que contienen "Davolio" en la
columna Vendedor y valores de ventas superiores a 3.000 $ y tambin muestra las
filas del vendedor Buchanan con valores de ventas superiores a 1.500 $.
Notas
La frmula que utilice con el fin de generar una condicin debe utilizar una
referencia relativa para hacer referencia al rtulo de columna (por ejemplo,
Ventas) o al campo correspondiente del primer registro. Todas las dems
referencias de la frmula deben ser referencias absolutas y la frmula debe
evaluarse contra VERDADERO o FALSO. En el ejemplo, "C7" hace
referencia al campo (columna C) del primer registro (fila 7) de la lista.
En la frmula puede utilizar un rtulo de columna en lugar de una referencia
relativa a celda o un nombre de rango. Si Microsoft Excel presenta el error
#NOMBRE? en la celda que contiene el criterio, puede no tenerlo en
cuenta, ya que no afecta a la forma en que se filtra la lista.
Cuando evala datos, Microsoft Excel no distingue entre caracteres en
mayscula y minscula.
Subtotales
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 46
automticamente. As, los datos resultantes son fciles de formatear, colocar en
un grfico e imprimir. En resumen, aade lneas de subtotal a columnas de la lista.
Para insertar subtotales, primero se ordena la lista para agrupar las filas cuyos
subtotales se desea calcular. Despus pueden calcularse los subtotales de
cualquier columna que contenga nmeros.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 47
2.- Para activar los Subtotales se selecciona la Ficha Datos el Subgrupo Esquema,
el icono Subtotales y aparecer el siguiente cuadro:
3.- Del conjunto de opciones que se muestra en la figura, se selecciona para cada
cambio en Vendedor que fue la columna por la cual se orden la tabla, se elige la
funcin a utilizar y se tildan aquellos campos donde se desea agregar el subtotal.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 48
Obsrvese como se crearon grupos los cuales presentan al final
un total del conjunto de datos que pertenecen a el y un pequeo
men de nmeros en la parte superior que permitir desplazarse
por las diversas opciones del subtotal.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 49
Excel automticamente se posiciona sobre las celdas que se desea seleccionar,
se procede a copiar y se pegan los datos en el sitio donde se desea conservar el
reporte, el cual quedara de la siguiente manera:
Subtotales Anidados
1.- Ordenar la tabla por las columnas que se desea generar el reporte, en la Ficha
Inicio, en el Subgrupo Modificar, el icono Ordenar y Filtrar, la opcin Orden
Personalizado, y se seleccionan los niveles de ordenamiento que se desean.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 50
Obsrvese que primero se va ordenar por Vendedor y luego por Producto
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 51
Para generar el otro subtotal se sigue el mismo
procedimiento anterior pero ahora por Pas destinatario,
con la diferencia que se destilda le opcin Remplazar
Subtotales Actuales, como se observa en la figura.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 52
Tablas dinmicas
Comprende una serie de Datos agrupados en forma de resumen que agrupan
aspectos concretos de una informacin global. Es decir con una Tabla Dinmica
se pueden hacer resmenes de una Base de Datos, utilizndose para, promediar,
o totalizar datos.
Debe ser muy importante la cantidad de informacin a manejar para que el uso de
la tabla dinmica se justifique. Para su utilizacin, se debe recurrir a la pestaa
Insertar, el Subgrupo Tablas, la opcin Tablas Dinmicas.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 53
En este caso como se posicin con antelacin en los datos, Excel
automticamente selecciona los mismos para el diseo de la Tabla Dinmica.
Aparecer automticamente un conjunto de Fichas contextuales en la parte
derecha de la cinta de opciones, las cuales van a permitir posteriormente definir el
formato de la tabla que se est diseando.
Se observa como se genera una nueva hoja con el siguiente diseo: La hoja
muestra un nuevo cuadro de dilogo de tabla dinmica denominado Lista de
campos de tabla dinmica
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 54
En la parte superior del cuadro de dilogo aparecen los campos de la tabla de
datos original. En la parte inferior se muestran cuatro reas a donde arrastrar los
datos y disear la tabla.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 55
Los campos que se siten en el rea Rtulos de columna aparecern en
horizontal a lo largo de la tabla y los que se siten en el rea Rtulos de fila en
vertical. Los Valores se establecen para situar los clculos totales de datos. Se
procede a marcar los tres campos que se van a incluir en la tabla dinmica. Se
puede observar como dependiendo del tipo de datos que contenga el campo, ste
se coloca automticamente en el rea correspondiente.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 56
Elementos de la Tabla Dinmica
Campo Pgina
Un campo de pgina es un campo de una lista o una tabla de origen al que se ha
asignado una orientacin de pgina en una tabla dinmica. En el ejemplo, Regin
es un campo de pgina que puede utilizarse para filtrar los datos resumidos por
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 57
regiones. Si hace clic en otro elemento de un campo de pgina, la tabla dinmica
cambiar para mostrar los datos resumidos asociados a ese elemento.
Elemento del campo Pgina
Cada entrada o valor exclusivos del campo o columna de la lista de origen o de la
tabla ser un elemento en la lista de un campo de pgina. En el ejemplo, el
elemento Este se presenta actualmente en el campo de pgina Regin y la tabla
dinmica muestra nicamente los datos resumidos para la regin Este.
Campo Datos
Un campo de datos es un campo de una lista o de una tabla de origen que
contiene datos. En el ejemplo, Suma de importes de pedidos es un campo de
datos que resume las entradas del campo Importe de pedidos o de la columna en
los datos de origen. Normalmente, un campo de datos resume datos numricos,
como estadsticas o importes de ventas, pero tambin puede contener texto.
Como valor predeterminado, los datos de texto se resumen en una tabla dinmica
mediante la funcin Contar y los datos numricos mediante la funcin Suma.
Campo Fila
Los campos de fila son campos de una lista o de una tabla de origen a los que se
ha asignado una orientacin de fila en la tabla dinmica. En el ejemplo, Producto y
Vendido son campos de fila. Los campos de fila interiores como Vendido son los
ms prximos al rea de datos; los campos de fila exteriores, como Producto,
estn situados a la izquierda de los campos de la fila interior.
Campo Columna
Un campo de columna es un campo de una lista o una tabla de origen al que se ha
asignado una orientacin de columna en una tabla dinmica. En el ejemplo,
Trimestres es un campo de columnas con dos elementos; Trim.2 y Trim.3. Los
campos de columna interiores son los que tienen sus elementos ms prximos al
rea de datos; los campos de columna exteriores estn encima de los campos de
columna interiores (en el ejemplo se muestra nicamente un campo de columna).
rea de datos
El rea de datos es la parte de la tabla dinmica que contiene los datos
resumidos. Las celdas del rea de datos muestran los datos resumidos de los
elementos de los campos de fila y de columna. Los valores de cada celda del rea
de datos representan un resumen de los datos procedentes de filas o registros de
origen.
Una tabla dinmica utiliza una funcin de resumen para resumir los valores del
rea de datos. Microsoft Excel utiliza la funcin Suma para calcular los campos de
datos que contienen datos numricos y utiliza la funcin Contar para calcular las
celdas que contienen texto. Puede elegir una funcin de resumen distinta (como
Promedio, Mx, Mn o DesvEst) para analizar y personalizar los datos.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 58
Puede utilizarse tambin un clculo personalizado que calcule los valores
basndose en otros elementos o celdas del rea de datos; por ejemplo, pueden
presentarse los valores como un porcentaje de una fila especfica en la tabla
dinmica.
Si desea crear sus propias frmulas que utilicen elementos de la tabla dinmica o
bien datos de otras hojas de clculo, puede crear un campo calculado o un
elemento calculado dentro de un campo.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 59
Obtencin de Subtablas
Esta herramienta tambin permite generar Subtablas que engloben los tem de a
base de datos de donde proviene el resultado final de una celda de la Tabla
Dinmica. Por ejemplo haciendo doble clic sobre una celda, en este caso en la del
monto total 74,83, Excel automticamente produce un detalle de los datos de
donde proviene ese monto total generado, de la siguiente forma:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 60
Tablas Dinmicas de Tres Dimensiones
En este caso se presentara una tabla ms compleja que la anterior. Permite
analizar no solo las ventas por Vendedor y por Producto, sino tambin por Pas
Destinatario. Es una tabla de tres dimensiones. Se construye de la misma manera,
cambiando solamente el diseo. En este caso se agregar el Campo Pas
Destinatario al rea de Coloque Campos de Pgina Aqu. Al hacer clic en su
autofiltro se podr seleccionar uno de los pases por ejemplo Argentina. Esta
tabla queda exclusivamente relacionada con las ventas en Argentina.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 61
1.- Se acciona, clic sobre cualquier celda con el
botn derecho del mouse, y aparece la pantalla
segn la imagen
2.- Se acciona clic sobre Actualizar Datos o se
hace clic en el icono Actualizar Datos que se
encuentra en la ficha opciones
Por otra parte es importante resaltar que al insertar una tabla dinmica en la hoja
de Excel automticamente aparece un conjunto de fichas denominado
Herramientas de Tabla Dinmica, a travs de las cuales se pueden modificar
aspectos generales a la tabla y agregar diversas caractersticas adicionales. Estas
fichas se visualizan de la siguiente manera.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 62
Y se observar una grfica como esta:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 63
Unidad 5. Macros: Automatizacin de tareas de uso frecuente
Conceptos bsicos relacionados con Macros
Que es una Macro
Desde el punto de vista general, se puede decir que las macros ayudan a
automatizar tareas. Son partes de cdigo de programacin que es interpretado por
Excel y lleva a cabo distintas acciones o una sucesin de tareas. De esta forma,
con una macro se pueden realizar tareas repetitivas muy fcilmente.
Sin embargo, ste no es el nico uso que se le puede dar a una macro ya que, si
se utiliza el lenguaje de programacin en el que hablan las macros, Visual Basic
para Aplicaciones (VBA), se podrn desarrollar desde tareas y funciones simples
para usos especficos hasta aplicaciones complejas.
En algunos programas de hojas de clculo, las macros son colecciones de
pulsaciones de teclas. Al pulsar una tecla, introducimos dicha pulsacin en la
macro. Las macros de pulsaciones de teclas se pueden interpretar con facilidad
puesto que existe una correspondencia unvoca entre lo que hace en el teclado y
lo que ve en la macro. No obstante, es conveniente que no sean muy largas
porque se pueden transformar en ilegibles y poco flexibles.
El lenguaje de macros de Excel es un verdadero lenguaje de programacin.
Probablemente nunca encontrar lmites para lo que se puede hacer con Visual
Basic. La contrapartida a esta potencia es que el lenguaje puede resultar un poco
difcil para entenderlo bien al principio.
El grabador de macros le puede ayudar a iniciarse en la creacin de macros y
aprendizaje de Visual Basic. No precisa conocimiento alguno del lenguaje para
activar el grabador, trabaje un poco, desactive el grabador y despus recupere la
macro que se ha producido. No obstante, si se extiende demasiado, difcilmente
una macro va a producir los resultados que usted quiere.
La creacin de la macro adecuada en Microsoft Excel equivale a poner aceite en
la bisagra de una puerta que chirra. Si, debera crear una macro para aadir ese
subrayado. En Excel, el aceite no est en el garaje. Se llama grabador de macros
y forma parte de Excel, encontrndose dispuesto cuando se necesite. Lo nico
que hay que hacer es utilizarlo.
Si realiza frecuentemente una tarea en Microsoft Excel, puede automatizarla
mediante una macro. Una macro consiste en una serie de comandos y funciones
que se almacenan en un mdulo de Microsoft Visual Basic y que puede ejecutarse
siempre que sea necesario realizar la tarea. Por ejemplo, si suele escribir cadenas
largas de texto en las celdas, puede crear una macro para dar formato a esas
celdas y que se ajuste el texto.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 64
Grabar macros: Al grabar una macro, Excel almacena informacin sobre cada
paso dado cuando se ejecuta una serie de comandos. A continuacin, se ejecuta
la macro para que repita los comandos. Si se comete algn error mientras se
graba la macro, tambin se graban las correcciones que se realicen. Visual Basic
almacena cada macro en un nuevo mdulo adjunto a un libro.
Administrar macros: Tras grabar una macro, se puede ver el cdigo de macro
con el Editor de Visual Basic para corregir errores o modificar lo que hace la
macro. Por ejemplo, si la macro de ajuste de texto tambin tiene que aplicar el
formato de negrita al texto, se puede grabar otra macro para aplicar el formato de
negrita a una celda y, a continuacin, copiar las instrucciones de esa macro a la
macro de ajuste de texto. El Editor de Visual Basic es un programa diseado para
que los usuarios principiantes puedan escribir y editar fcilmente cdigo de macro,
y proporciona mucha Ayuda en pantalla. No es preciso saber cmo se programa o
se utiliza el lenguaje de Visual Basic para realizar cambios sencillos en las
macros. El Editor de Visual Basic permite modificar macros, copiarlas de un
mdulo a otro, copiarlas entre diferentes libros, cambiar el nombre de los mdulos
que almacenan las macros o cambiar el nombre de las macros.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 65
los conocimientos sobre el manejo de estas herramientas permitirn construir
aplicaciones de alta complejidad en poco tiempo.
El formato de los archivos
Excel 2010 presenta una forma diferente de guardar nuestras planillas ya que en
esta versin se crea una separacin entre los archivos normales y aquellos que
incluyen macros de Visual Basic para Aplicaciones o controles ActiveX. De esta
forma, con slo ver la extensin del archivo se podr determinar si contiene cdigo
VBA o controles ActiveX. Este cambio se genera, principalmente, por una cuestin
de seguridad, para que podamos identificar los archivos antes de abrirlos.
Pasos para Crear una Nueva Macro
Grabar una macro
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 66
Al seleccionar la opcin Grabar macro..., lo primero que se ve es el cuadro de
dilogo Grabar macro donde se puede dar un nombre a la macro.
El primer carcter del nombre de la macro debe ser una letra. Los dems
caracteres pueden ser letras, nmeros o caracteres de subrayado. No se permiten
espacios en un nombre de macro; puede utilizarse un carcter de subrayado como
separador de palabras.
No utilice un nombre de macro que tambin sea una referencia de celda; de lo
contrario puede aparecer un mensaje indicando que el nombre de la macro no es
vlido. Si desea ejecutar la macro presionando un mtodo abreviado, escriba una
letra en el cuadro Tecla de mtodo abreviado. Puede utilizarse CTRL+ letra (para
letras minsculas) o CTRL+ MAYS + letra (para letras maysculas), donde letra
es cualquier tecla del teclado. La tecla de mtodo abreviado que se utilice no
puede ser ni un nmero ni un carcter especial como @ o #.
En el cuadro Guardar macro en, haga clic en la ubicacin en que desea almacenar
la macro. Si desea que la macro est disponible siempre que utilice Excel,
seleccione Libro de macros personal.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 67
Para comenzar la grabacin de la macro se pulsa el botn Aceptar y a
continuacin, se visualiza la barra de estado, all se encontrara este botn en la
barra de estado donde se tendr la opcin de detener la grabacin.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 68
2. Seleccione Macro de la ficha Vista el icono Macros, la opcin Ver Macros
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 69
En cuanto al resto de botones:
Cancelar. Cierra el cuadro de dilogo sin
realizar ninguna accin.
Paso a paso. Ejecuta la macro instruccin
por instruccin abriendo el editor de
programacin de Visual Basic.
Modificar. Abre el editor de programacin de
Visual Basic para modificar el cdigo de la
macro. Estos dos ltimos botones son para
los que sapan programar.
Eliminar. Borra la macro.
Opciones. Abre otro cuadro de dilogo
donde podemos modificar la tecla de mtodo
abreviado (combinacin de teclas que provoca la ejecucin de la macro sin
necesidad de utilizar el men) y la descripcin de la macro.
Antes de modificar una macro, deber familiarizarse con el Editor de Visual Basic.
Puede utilizarse el Editor de Visual Basic para escribir y modificar las macros
adjuntas a los libros de Microsoft Excel.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 70
1. Seleccione Macro de la ficha Vista el icono Macros, la opcin Ver Macros
2. En el cuadro Nombre de la macro, escriba un nombre para la macro.
3. Haga clic en Modificar.
Para obtener Ayuda para el Editor de Visual Basic, haga clic en Ayuda de
Microsoft Visual Basic en el men Ayuda.
1. Abra el libro que contiene el mdulo que desea copiar y el libro en que
desea copiar el mdulo.
2. Seleccione de la ficha Vista el icono Macros, la opcin Ver Macros
3. En el men Ver, haga clic en Explorador de proyectos.
4. Arrastre el mdulo que desee copiar al libro de destino.
Nota: Si asigna una macro a un botn o a otro objeto que ya est en uso como,
por ejemplo, un hipervnculo, se eliminar la informacin del hipervnculo. A partir
de entonces, al hacer clic en el botn o en el objeto, se ejecutar la macro.
Iniciar una macro desde un rea, una zona activa o un objeto grfico
1. Dibuje otro objeto para crear una zona activa para un objeto existente.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 71
2. Con el segundo objeto grfico seleccionado, haga clic con el botn
secundario del mouse (ratn) en un controlador de tamao del botn o del
objeto grfico para mostrar el men contextual.
3. En el men contextual, haga clic en Asignar macro.
4. Siga uno de estos procedimientos:
Para asignar una macro al botn u objeto grfico, escriba el nombre
de la macro en el cuadro Nombre de la macro y, a continuacin,
haga clic en Aceptar.
Para grabar una nueva macro y asignarla al objeto grfico
seleccionado, haga clic en Grabar. Cuando finalice de grabar la
macro, haga clic en Detener grabacin en la barra de herramientas
Grabar macro.
Para escribir una macro en el Editor de Visual Basic, haga clic en
Nueva. Para modificar una macro existente, haga clic en el nombre
de la macro, en el cuadro Nombre de la macro y, a continuacin,
haga clic en Modificar.
5. En el men Formato, haga clic en Autoforma y, a continuacin, haga clic en
la ficha Colores y lneas.
6. Bajo Rellenar, haga clic en Sin relleno en el cuadro Color. Bajo Lnea, haga
clic Sin lnea en el cuadro Color.
7. Repita estos pasos para cada zona activa que desee crear. No es
necesario crear un nuevo objeto grfico para cada zona activa.
Cuando se guarda un archivo y se quiere que las Macros que se han creado se
almacenen con el resto de las hojas de clculo d utilizar un tipo de archivo
diferente.
Esto ocurre porque Office no conoce la procedencia de las Macros. Como estn
compuestas por cdigo podran realizar acciones que fuesen perjudiciales para
nuestro equipo. Si se confa en las posibles Macros que contuviese el archivo o se
han creado con antelacin, pulse el botn Opciones para activarlas.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 72
La ficha del programador
Dentro de la cinta de opciones se encontra diferentes fichas con herramientas
para explotar Excel 2010 a fondo. Entre ellas, existe una ficha de opciones
especfica para interactuar con VBA: la ficha del programador. Esta ficha es
especial y no se encuentra disponible dentro de la instalacin bsica, por lo que
debemos modificar algunas opciones para poder utilizarla.
Para activar la ficha Programador se debe dirigir a las Opciones de Excel incluidas
dentro del Botn de Office. Una vez all, dentro del grupo de opciones Ms
frecuentes y bajo el ttulo Opciones principales para trabajar con Excel, debe
incluir una tilde en el casillero Mostrar ficha Programador en la cinta de opciones.
Grupo: Cdigo
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 73
grabadora de macros. Esta herramienta guarda todas las acciones que realizamos
y las transforma en cdigo VBA.
Esta ventana mostrar las macros incluidas en los distintos libros abiertos o
particularmente en alguno de los que se encuentren abiertos. Aqu tambin se
encontraran las opciones ms sencillas para administrar las macros:
Se puede decir que las referencias fijas son inamovibles e indican una
determinada coordenada (celda) o un grupo de ellas dentro de la hoja de clculo.
Por el contrario, las referencias relativas realizan, a partir de la ubicacin de la
celda actual, los movimientos indicados en la macro.
Grupo: Controles
Los controles pueden ser algo tan simple como un cuadro de texto o un botn, o
algo ms complejo como una aplicacin. Estos controles podrn ser incrustados
dentro de planillas o directamente en formularios especficos. De esta forma, se
relacionaran las celdas de la planilla, el cdigo VBA y los controles ActiveX.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 74
Grupo Controles: Insertar
Con el botn Insertar se podrn incluir los diferentes controles que nos
proporciona Microsoft Excel 2010 por defecto, como muestra la figura.
Los controles de formulario son los botones, los cuadros de texto, las etiquetas,los
botones de opcin, los cuadros combinados, entre otros. Los controles ActiveX se
utilizan en sitios web y en las aplicaciones del equipo, pero no son soluciones
autnomas sino que slo pueden ejecutarse desde el interior de los programas
que los hospedan, como Windows Internet Explorer y los programas de Microsoft
Office.
El aspecto de los
botones de formulario y
ActiveX es muy similar,
pero la forma de
asignarles instrucciones y
modificar sus
propiedades es bastante
diferente
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 75
Desde aqu se podrn modificar las diferentes opciones referidas a los controles
ActiveX incrustados. Este grupo de propiedades ser diferente, dependiendo del
tipo de control que tengamos seleccionado. Son muy variadas las opciones que se
pueden modificar y stas pueden estar ordenadas alfabticamente u organizadas
por categoras.
Por ltimo, se puede decir que la opcin Ejecutar cuadro de dilogo, del grupo
Controles, permite ejecutar un cuadro de dilogo personalizado de Excel 5.0.
Estos Grupo Controles: Ejecutar cuadro de dilogo.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 76
Editores y ubicaciones de confianza
Un editor es un programador que ha creado una macro, un control ActiveX, un
complemento u otra extensin de aplicacin para uso de todos los usuarios. Estos
editores son programadores que deben acreditarse con credenciales vlidas. Para
esto, el proyecto de cdigo debe estar firmado digitalmente (sello electrnico
seguro con cifrado que certifica la autenticidad de una macro o un documento).
Esta firma debe ser vlida y estar actualizada (no caducada).
De esta forma, si se ejecuta un cdigo que no cumpla con estos criterios, el Centro
de confianza deshabilitar el cdigo en forma automtica y aparecer un mensaje
de aviso. Cuando aparezca un cuadro de dilogo de seguridad, se tendr la
opcin de dejar la macro deshabilitada, habilitarla o confiar en el editor. Para esto
se hace clic en la opcin Confiar en todos los documentos de este editor.
Por su parte, las Ubicaciones de confianza son sitios seguros en los que
confiamos. Los cdigos ejecutados desde estas ubicaciones no emitirn
advertencias de seguridad.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 77
Es posible deshabilitar todas las ubicaciones de confianza si se selecciona la
opcin correcta dentro de este grupo.
Configuracin de macros
Para las macros que no se encuentren dentro de una ubicacin de confianza,
existen algunas opciones que nos permitirn deshabilitar las macros con o sin
notificacin, deshabilitar todas las macros con excepcin de aquellas que se
encuentren firmadas digitalmente o habilitar todas las macros. sta es la opcin
menos recomendada desde el punto de vista de la seguridad.
Opciones de privacidad
Desde el Centro de confianza tambin podr modificar opciones relacionadas con
conexiones externas (Contenido externo) y las Opciones de privacidad.
Guardar el archivo habilitado para Macros
Es de hacer notar que en Excel 2010 en el momento de guardar un archivo que
contenga Macros, se debe dar clic en la Ficha Archivo y all seleccionar guardar
como, asignarle un nombre y en el recuadro tipo seleccionar Libro de Excel
habilitado para Macros como muestra la figura.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 78
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 79
Unidad 6. Herramientas de seguridad de un libro de Excel
Contrasea para abrir: para que slo puedan acceder al libro aquellas
personas que conocen la contrasea.
Contrasea de escritura: para que cualquiera pueda acceder al libro de
trabajo, pero solamente lo puedan modificar aquellas personas que
conozcan la contrasea.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 80
nadie pueda verla y Hacer clic sobre el botn Aceptar.
Si se conoce la contrasea, la
escribimos en el recuadro y
Aceptar.
Si se quiere borrar una contrasea, se tiene que abrir el libro con la contrasea
para tener la posibilidad de modificarlo, y a continuacin realizar los mismos pasos
que a la hora de ponerla pero borrando lo que hay en el recuadro de contrasea.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 81
Proteger hojas celdas
Cuando una celda est bloqueada no podr sufrir variaciones. Realmente por
defecto todas las celdas estn protegidas o bloqueadas para que no sufran
cambios, pero no se aplican ya que la hoja no est protegida, para que realmente
se bloqueen las celdas antes hay que proteger la hoja de clculo.
Para desbloquear las celdas que se van a A8 variar en algn momento sigue los
siguientes pasos:
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 82
Dejar activada la casilla Proteger hoja y contenido de celdas bloqueadas para
proteger el contenido de las celdas de la hoja activa.
5.- Activar las opciones deseadas de la casilla Permitir a los usuarios de esta
hoja de clculo para que no tenga efecto la proteccin para la modificacin
seleccionada y desactivarla para tener en cuenta la proteccin.
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 83
REFERENCIAS
Bsqueda y referencia
http://www.jorgesanchez.net/ofimatica/manuales/funcExcel.pdf
Crear series en Excel
http://excelforo.blogspot.com/2009/09/insertar-una-serie-en-excel.html
http://www.mailxmail.com/curso-basico-excel/creacion-series-excel
http://www.adrformacion.com/cursos/eaxp/leccion1/tutorial5.html
Gonzalo Hctor Fernndez Pcia Bs. As Argentina Microsoft Excel 2003
www.lawebdelprogramador.com
Manual de Microsoft Excel 2003.
www.manualespdf.es/manual-excel-2003
Manual de Microsoft Excel 2003.
http://www.duiops.net/manuales/excel/excel.htm
Patric Conrad, Administracin de Datos con Excel, EDITORIAL MICROSOFT
PRESS, AO 1996.
REED, Jacobson. (1999). Microsoft Excel / VisualBasic Paso a Paso. Madrid:
Editorial Mc Graw Hill
Subtotales http://sabanet.unisabana.edu.co/cursos/excel/subtotales.html
Soporte Excel Microsoft http://office.microsoft.com/es-ar/excel-help/
Prohibida la reproduccin total o parcial de este material sin la autorizacin expresa de FUNDAMETAL 84