Post on 19-Sep-2018
CURSO AVANZADO DE EXCEL
Dagoberto Salgado HortaE-mail: dasaho2@yahoo.es
Tel (300) 6527920
V 1.0
CAPITULO I
CONDICIONALES, BUSCARV Y BUSCARH
Objetivos
Aprender a utilizar funciones condicionalesAprender a utilizar las funciones BuscarV y BuscarH
Condicionales
Se utilizan para retornar un valorcuándo se cumple una condición lógicay un valor diferente de lo contrarioSe construyen a partir de una condiciónlógica, el valor de retorno cuándo laexpresión es verdadera y el valor deretorno cuándo la expresión es falsa
Ejemplo
Aspectos a tener en cuenta
El primer argumento puede ser cualquier expresión lógica, inclusive utilizando las funciones Y y O
Condicionales anidados
Es posible que el valor verdadero o el falso sea otra expresión condicional. Por ejemplo:
Función Contar.Si
Es útil para contar el número de celdas (datos) cumplen con una condición dada. Por ejemplo:
Función Sumar.Si
Similar a la anterior pero en vez decontar ocurrencias, suma los valores delas celdas. Por Ejemplo:
Base de Datos
Recuerde que los datos deben de residiren una sola celda (nombres compuestosen una sola celda) y la información sedebe disponer en forma de “registros”debajo de los encabezados (nombre delos atributos)
Ejemplo
Función BuscarV
Esta función permite buscar un valordado en la primera columna de unatabla y retornar otro atributo (columna)de la fila dónde se encontró el valor.
Ejemplo
Continuación
Esta función resulta de gran utilidadcuando se requiere “completar” o“cruzar” información entre diferentestablas
Función BuscarH
Similar a la función BuscarV, pero eneste caso se busca el valor en laprimera fila y se retorna el valor de unafila diferente en la misma columna
Ejemplo
CAPITULO II
Importación / Exportación de Datos
Objetivos
Aprender a importar datos desdefuentes externas tales como archivosplanos, bases de datos, otros libros deExcel y página Web
Introducción
Por qué importar datos?Evitar re-escribir información existenteActualizar las hojas de cálculo con datosexternosAnalizar información de una base de datoscon Excel
Requisitos
Para importar datos de alguna fuenteexterna se requiere:
Tener acceso a los datos (conexión a labase de datos, tener disponible el archivode texto, etc)El formato de los datos externos debepoder ser reconocido por Excel (Excelcuenta con una gran variedad de filtrospara el importación de los datos)
Importación de Archivos planos
1. Abra un libro existente dónde secopiaran los datos o cree uno nuevo.
2. Utilice la opción Obtener datosexternos del menú Datos
3. En el cuadro de dialogo Seleccionararchivos de origen de datos,seleccione el archivo de texto a cargar
Continuación4. Seleccione el tipo de archivo. Ancho fijo si todos los campos tienen igual longitud o delimitado si los campos están separados por un caracteres espacial como coma (,), punto y coma (;), etc
Utilice esta opción si no quiere importar algunas filas del comienzo
Previsualización del archivo
Continuación….
5. Seleccione el caracter de separación
Caracter en el que se encuentran encerradas las cadenas (útil si hay celdas que contengan dos o más palabras)
Continuación
6. Seleccione el formato de la columna a importar
Continuación
7. Finalmente seleccione el lugar (celda)donde se realizará la importación
Actualización de datos Importados
Puede actualizar los datos importados pormedio de la opción Actualizar Datos delmenú Datos o con la misma opción en elmenú que se despliega al hacer click
derecho.
O por medio del icono
Importación desde bases de datos1. Seleccione la opciónNueva Consulta deBase de Datos delsubmenú Obtener DatosExternos del menúDatos2. Seleccione el tipo dearchivo de base de datosdesde la cual realizará laimportación (en este caso,una Base de Datos deAccess)
Continuación3. Seleccione el archivode Base de Datos(Access) desde el cualimportará los datos(ADatos1.dbf)4. Seleccione las tablasque importará (en esteejemplo solo se tienenla tabla Personas)
Continuación
5. Puede seleccionar un filtro para laimportación de los datos (solo de importanlos datos que cumplan con la condición delmismo)
Continuación
6. Seleccione (de ser necesario) uncriterio de ordenamiento para los datos
Continuación
7. Finalmente aparecerá el siguiente cuadro de diálogo
Y se selecciona el lugar dónde se importarán los datos
Continuación
También se pueden actualizar los datos importados con las opciones vistas anteriormente.
CAPITULO III
FILTROS Y FILTROS AVANZADOS
Objetivos
Aprender a filtrar información en una base de datos.Delimitar los datos que se muestran en la pantalla
Introducción
Las hojas de calculo de Excel puedencontener tantos datos como sea necesario.Quizás en ocasiones no se desee trabajar contodos ellos al mismo tiempo; se deseebuscar algún nombre, número, código, etc enespecial o rangos con alguna característicadefinida. Para lo anterior, se hace uso deFiltro y Filtros Avanzados
Continuación
Importante: Al ejecutar un filtro, Exceltrata las celdas en la columna de lacelda activa como un rango. Paragarantizar que el filtro funcionecorrectamente, se recomienda agregarsiempre un título a la columna quedesea filtrar.
Continuación.
Para utilizar un auto-filtro se selecciona la opción AutoFiltro del menú Datos.
AutoFiltro
Solo se puede aplicar filtros a una lista de unahoja de cálculo a la vez.Haga clic en la celda de la lista que desea filtrar.En el menú Datos, seleccione Filtro y haga click en Autofiltro.
Continuación
Para presentar sólo las filas que contienen un valor específico, haga clic en la flecha de la columna que contiene los datos que desea presentar.Haga clic en el valor.
Ejemplo
Continuación.
Para filtrar la lista por dos valores de lamisma columna, o para aplicaroperadores de comparación distintos aEs igual a, haga click en la flecha de lacolumna y después en Personalizar.
Continuación.Para buscar según uncriterio, haga clic en eloperador decomparación que deseeutilizar en la primeracasilla y en la casilla deenfrente el valor dereferencia.
Continuación
Puede adicionar un segundo criterio defiltro utilizando las dos casillas inferiores
Utilice Y si ambos criterios deben cumplirse y O si es suficiente con que alguno de los dos se cumpla
Caracteres Comodín
Se puede hacer uso de caracteres comodín para buscar celdas que difieran en uno varios caracteres a partir de la expresión suministradaLos comodines que se pueden utilizar son los siguientes:
ContinuaciónCuando hay datosocultos (filtrados), losnúmeros de filacambian de color aAzul. Si se quierenvolver a mostrar todoslos valores, se haceclick sobre la opciónTodas debajo de laflecha de las columnaque contenga filtros
Filtros Avanzados
Para realizar filtros avanzados, la hojade cálculo debe disponer al menos detres filas vacías después de los rótulosde columna que puedan utilizarse comorango de criterios sobre la lista
ContinuaciónEn las filas situadas bajo los rótulos de criterios,introduzca los criterios que desea buscar.Compruebe que existe al menos una fila vacíaentre los valores de criterios y la lista.
Continuación
Haga clic en una celda de la lista.En el menú Datos, seleccione Filtro y haga click enFiltro avanzado.Para filtrar la lista ocultando las filas que no cumplenlos criterios, haga clic en Filtrar la lista sin moverla.Para filtrarla copiando las filas que cumplen loscriterios a otra área de la hoja de cálculo, haga clic enCopiar a otro lugar y utilice el campo Copiar A paraespecificar dónde se copiar los datos
ContinuaciónEn la opción Rango dela Lista seleccione losrótulos y las filas tantode los criterios como lade los datos.En la casilla Rango decriterios introduzca lasceldas que contienencriterios incluyendo losrótulos de las columnas.
Utilice esta opción si quiere “eliminar” los registros repetidos.
Continuación
Para volver a mostrar los datos ocultos, utilice la opción Mostrar Todo del submenú Filtro del menú Datos
Ejemplo
Muestra las filas cuyo código sea mayor a 2 y que el nombre de la personacontenga las letras “ar
CAPITULO IV
SUBTOTALES Y AGRUPAMIENTOS
OBJETIVOS
Crear listas como bases de datosAgrupar información generando totalesy subtotales de los mismos
Introducción
Por qué agrupar datos:Los datos se agrupan para “ocultar”información que no es de utilidad en unmomento determinadoPara calcular subtotales por “áreas dedatos” (ventas por ciudad, sucursal,vendedor, etc)
Creación de Subtotales
Primero se debe tener una tabla (como basede datos) con filas (registros) y columnas(atributos). La primera fila debe tener losrótulos de la tabla (nombre de los atributos)
Continuación
Se ordena la tabla para poder realizar losagrupamientos. Para ordenar datos se puede:
Seleccionar la tablaUtilizar el iconoÓ utilizar la opción Ordenar del menú Datos
NOTA: Debe seleccionar todas las columnas y filasde la tabla para no “dañar” la información
Continuación
En la opción del menú Ordenar, se puede seleccionar sobre cuáles columnas se hará el ordenamiento. P. Ej
Se debe seleccionar la opción Si en caso de tener en la primera fila el nombre de las columnas (Atributos)
Primer,segundo y tercer criterio de ordenamiento
Ordenamiento ascendente o Descendente
El resultado
Adicionar subtotalesSeleccionar la tabla y utilizar la opción Subtotales del menú Datos
Atributo por el que se realizará el agrupamiento
Función de agrupamiento (promedio, suma, máximo, etc)
Atributo que se sumará, promediará, etc (normalmente cantidades numéricas)
1er: Se debe desmarcar para adicionar nuevas operaciones de subtotales a la tabla.2do: Adicionar saltos de página por cada grupo3er: Desmarcar si se quieren los subtotales al inicio del bloque de datos Eliminar los subtotales
El resultadoTotal del primer grupo
Total (suma) de todos los grupos
Permiten compactar o expandir un grupo (útil para ocultar información no relevante)
Permiten seleccionar hasta que nivel de agrupamiento se desea visualizar. En este caso, 1 para solo el total, 2 para el total general y los subtotales por vendedor y 3 para ver los detalles de cada vendedor
Adición de otras operaciones en los subtotales
Para adicionar otra operación comopromedio en cada uno de los subtotales, sedebe ubicar en alguna de la celdas de latabla, seleccionar la opción subtotales delmenú datos. Cambiar de opción (usarfunción) y desmarcar la opciónReemplazar subtotales actuales
continuación
Subtotales Anidados
Es posible definir “grupos de datos”dentro de los grupos (subtotales) yacreados. Por ejemplo, por cadavendedor totalizar por productovendido.A estos subtotales se les conoce comosubtotales anidados.
Ejemplo
Se debe seleccionar una de las celdasde la tablas. Utilizar la opciónsubtotales del menú datos.Seleccionar el atributo Producto,escoger una función de agrupamiento ydesmarcar la opción Reemplazarsubtotales actuales.
Continuación
Igualmente sobre los subtotalesanidados se pueden utilizar diferentesfunciones siguiendo los pasos vistosanteriormente:
Continuación
CAPITULO V
FORMULARIOS
Objetivos
Utilizar la opción de formularios parainsertar, modificar y eliminar registros(filas) de una tabla de Excel
Introducción
Un formulario es un cuadro de dialogoque permite visualizar y modificar elcontenido de las filas de una tabla enExcel.La tabla para que pueda ser manipuladapor un formulario debe tener rótulos decolumna.
Adición de Registros por medio de Formularios
Para tener acceso a la utilidad de Formularios, se debe localizar en una celda de la tabla de datos y luego seleccionar la opción Formulario en el menú Datos
ContinuaciónUna vez se haya desplegado el formulario, seadicionan registros presionando el botón nuevo,llenando la información de cada uno de los campos ypresionando Enter al final.
Continuación
Cuando cierre el formulario, podrá ver que en la hoja de cálculo se adicionó el nuevo registro.
Algunas Consideraciones
Las columnas que incluyan fórmulas no pueden editarse directamente en el formularioSe pueden deshacer los cambios presionando el botón Restaurar
Modificación de Registros
Es posible modificar registros por medio de un formulario. Ubique el registro a modificar (puede utilizar la barra de desplazamiento) modifique la información y luego presione Enter
Continuación
Si desea deshacer la modificación,puede presionar el botón Restaurar.Una vez presione Enter, el registroquedará modificado.
Búsqueda de Registros
Puede desplazarse entre los registros de la tabla por medio de la barra de desplazamiento. Con las flechas se desplazará de 10 en 10 registros
Continuación
Para buscar un registro que cumpla conalgún criterio, utilice el botón Criterios.Al hacerlo, los campos quedan vacíospara especificar criterios de búsqueda.Una vez escritos se puede utilizar lasbotones Buscar Siguiente y BuscarAnterior
Ejemplo
CAPITULO VI
TABLAS DINÁMICAS.
OBJETIVOS
Crear listas dinámicas que permitanordenar, filtrar y reorganizar datos.
PARA QUE SIRVEN LAS T.D?
Permiten agrupar los datos según como sedesee visualizar la información.Modifican el formato de los datos paraenfatizar el contenido de celdas particulares.Ordenan y filtran sus hojas de cálculobasándose en el contenido de algunascolumnas u oculta filas que contengan datosque no sean de interés.
Tablas Dinámicas
Es importante tener en cuenta queinformación se quieren generar a partirde los datos.Recuerde que antes de manipular losdatos es necesario revisar el formato yla forma como están organizados
Campos y elementos
Un informe de tabla dinámica contienecampos, cada uno de los cualescorresponde a una columna de losdatos de origen y resume varias filas.
Elementos de un Informa de T. Dinámica
Elemento del campo de página.
Campos de fila.
Campo de página.
Campo de fila exterior.
Flecha de lista desplegable de campo.
Campo de columna.
Continuación
CAMPO DE FILA: Campos de los datos de origen que se asignan a una fila de la tabla dinámica.CAMPO DE COLUMNA: Un campo que se asignan a una columna en una tabla dinámica.
Continuación
ELEMENTO: Una subcategoría ointegrante de un campo de la tabladinámica.CAMPO DE PÁGINA: Un campo quese asigna a una página o filtro.
Continuación
ELEMENTO DEL CAMPO DEPÁGINA:Cada entrada o valorexclusivo que será un elemento en lalista de campo de página.CAMPO DE DATOS: Un campo de unalista o tabla de origen que contienedatos que se van a resumir.
Continuación
ÁREA DE DATOS: Parte de un informede tabla dinámica que contiene datosde resumen. Las celdas del área dedatos muestran los datos resumidos delos elementos de los campos de fila yde columna.
Continuación
FLECHA DE LISTA DESPLEGABLE DELCAMPO: Hacer click sobre la parte derechade los campos permite seleccionar loselementos que se desean mostrar.Las celdas situadas en la intersección de unafila y una columna muestran los datosresumidos de los elementos de la partesuperior de la columna y de la izquierda de lafila.
Continuación
CAMPOS DE DATOS Y CELDAS: Un campode datos como suma de Ventas, proporcionalos valores que están resumidos en el informede tabla dinámica.FUNCIONES DE RESUMEN: Para resumirlos valores de los campos de datos, losinformes de tabla dinámica utilizan funcionesde resumen como Suma, contar, promedioetc.
Creación De Tablas DinámicasPara crear una tabla dinámica, se debe seleccionar los datos que harán parte del informe y utilizar el asistente para tablas dinámicas. (Menú Datos opción Informe de tablas y Gráficos Dinámicos)
Creación De Tablas DinámicasLa caja de diálogo para esta opción es la siguiente:
Origen de Datos
Tipo de Informe
Continuación
Datos de origen para un informe de tabla dinámica.
Continuación
Diseño del informe y sus campos
El Resultado
Continuación
VER DETALLES: En un informe detabla dinámica se pueden visualizar lasfilas de detalle de los datos de origenque conforman el valor de resumen.Para ello hace Click Derecho sobre elinforme y seleccione la opción Agrupary luego en Mostrar detalle
Ejemplo
Continuación
Así mismo es posible ocultar los detalles, con la opción Ocultar Detalle
Agrupar Datos
También es posible agrupar diferentes datos en un solo, por ejemplo:
Continuación
Y se desagrupar con la opción Desagrupar
Cambio de Diseño
Es posible cambiar eldiseño (ubicación delos campos) delinforme de tablasdinámicas, arrastrandoun botón de campo aotro lugar del mismo.
Ocultar Filas (Datos)
Es posible ocultar algunas filas o datos con ayuda de las flechas izquierdas de los campos del informe:
Gráficos Dinámicos.
A partir de los informes de TablasDinámicas es posible generar gráficosdinámicos para visualizar la informaciónPara generar el G. Dinámico, seleccionedel menú Tabla dinámica de la barra deherramientas la opción Grafico
Continuación.Siguiendo las instrucciones del asistente para gráficos se obtiene un gráfico con los datos del informe:
Continuación.NOTAS: Un gráfico creado a partir de una tabla dinámicacambiará cuando se oculten elementos, se muestrendetalles o se reorganicen campos en la tabladinámica fuente. Si la tabla dinámica tiene camposde página, el gráfico cambiará cuando se presentendiferentes páginas. Cuando se presente cadaelemento de la lista del campo de página, Excelactualizará el gráfico para que muestre los datosactuales.
Auto formato
Es posible dar formato al informe de Tabla Dinámica de acuerdo a un estilo predefinido. Para ello, utilice el icono
CAPITULO VII
MACROS
Objetivos
Automatizar tareas comunes con ayuda de MacrosEjecutar, crear y modificar Macros
Introducción
Si ejecuta frecuentemente una tarea enExcel, puede automatizarla mediante unamacro. Una macro consiste en una serie decomandos y funciones que se almacenan enun módulo de Visual Basic y que puedeejecutarse siempre que sea necesario.
Macros
La forma mas sencilla de crear macros,es grabar una serie de acciones y luegoExcel las repetirá cuando se le solicite laejecución del mismo
Continuación.
Antes de grabar o escribir una macro,planifique los pasos y los comandos quedesea que ejecute la macro. Si se cometealgún error mientras se graba, también segrabarán las correcciones que se realicen.Cada vez que se grabe una macro, ésta sealmacenará en un nuevo módulo de VisualBasic adjunto al un libro.
Continuación.
Con el Editor de Visual Basic es posiblemodificar y copiar macros entre módulos ylibros, cambiar de nombre a los módulos quealmacenan las macros o cambiar el nombre alas macros.
Grabar una Macro
Seleccione la opción Macro en el menúHerramientas y a continuación, haga clic enGrabar.En el cuadro Nombre de la macro, escriba unnombre para la macro.El primer carácter del nombre de la macrodebe ser una letra. Los demás caracterespueden ser letras o números. No se permitenespacios en un nombre de macro.
Continuación
Utilice esta opción si desea que el Macro se ejecute con un comando del teclado
Continuación
Una vez especificado el nombre delmacro y presionado el botón Aceptar,Excel grabará cada una de lasoperaciones que usted realice.Para finalizar la grabación utilice elbotón
Ejecución del Macro.
Podrá ejecutar el Macro almacenadoutilizando el método abreviado delteclado (si especificó alguno) o con laopción Ejecutar del submenú Macro
Continuación
Desde el cuadro de dialogo anterior,también podrá eliminar o modificarMacros. También es posible ejecutar elMacro paso a paso
Referencias Relativas o Absolutas
Si se seleccionan celdas mientras se está ejecutandouna macro, ésta seleccionará las mismas celdasindependientemente de la celda que se hayaseleccionado en primer lugar ya que grabareferencias absolutas de celda. Si desea tener unamacro para seleccionar celdas no independientes a laposición que tenga la celda activa cuando se ejecute,configure el grabador de macros para que grabereferencias relativas de celda. Esta opción seencuentra disponible en la barra de herramientasDetener Grabación en el icono R. Relativas
Continuación.
Excel continuará grabando macros conreferencias relativas hasta que terminela sesión con Excel o hasta que hagaclic otra vez en Referencias relativas.
CAPITULO VIII
Temas Adicionales
ESCENARIOS
Un escenario es un conjunto de valoresque Excel guarda y puede sustituirautomáticamente en la hoja de cálculo.Se útiles para analizar diferentesalternativas.
Ejemplo
Un alumno está analizando cuanto debesacar en su examen y proyecto finalpara pasar la materia. El cuenta con lossiguiente datos:
ContinuaciónEl plantea algunas alternativas y cada una de ellas las adiciona en un Escenario :Seleccione la opción Escenario del menú Herramientas.En el cuadro de Dialogo presioneel botón Agregar
Continuación
Luego aparece el siguiente cuadro de Diálogo
Nombre del Escenario o Alternativa
Celdas que van a cambiar (valores del Escenario)
Continuación
Luego se especifican los valores de cada una de las celdas cambiantes
Adicionar nuevos Escenarios
Continuación
Finalmente aparece el siguiente cuadro de dialogo con el que se pueden visualizar cada una de las alternativas (utilice el botón Mostrar)
AUDITORIA DE FORMULAS
La auditoria de fórmulas se utiliza pararastrear las precedencias y/o errores enuna fórmula.Es muy útil cuando se tienen fórmulascomplejas que implican varias celdas yse quiere visualizar cuales son losparámetros o referencias de la misma
Continuación
Para utilizar esta utilidad, primero sedebe localizar en la celda que contienela fórmula y luego utilizar la opciónAuditoria de Fórmulas del menúHerramientas
Continuación
Al rastrear precedentes aparecen flechas desde las celdas que sirven como parámetro o referencia a la fórmula actual
Continuación
Con la opción Mostrar Barra deHerramientas de Auditoria deFórmulas aparecerá la siguiente barrade herramientas, en la que seencuentran disponibles todas lasopciones de auditoria
VALIDACION DE DATOS
Excel ofrece la posibilidad de validar losdatos que se ingresan en las celdas.Para realizar esto, primero ubíquese enla celda que desea validar y luegoseleccione la opción Validar del menúDatos
Continuación
Aparecerá la siguiente caja de Dialogo
Valida el ingresoDe notas entre0 y 5
Continuación
En la pestaña mensaje de entrada, sepuede poner un mensaje que aparecerácuando se ubica sobre la celda de lavalidación
ContinuaciónFinalmente, en la pestaña mensaje deerror, se especifica el mensaje queaparecerá si se intenta ingresar un datono válido