excel avanzado - centro tecnológico minero · excel avanzado introducción este manual está...
TRANSCRIPT
EXCEL AVANZADO
EXCEL AVANZADO
Índice Introducción ................................................................................................................. 5
1 Capítulo I Aplicación de Funciones ...................................................................... 7
1.1 Objetivo específico ............................................................................................... 7
1.2 Funciones Lógicas ............................................................................................... 7
1.3 Funciones de texto .............................................................................................. 9
1.4 Funciones de Búsqueda y Referencia ............................................................... 13
1.5 Funciones Estadísticas...................................................................................... 17
2 Capítulo II Tablas Dinámicas ............................................................................. 20
2.1 Objetivo específico ............................................................................................. 20
2.2 Introducción ....................................................................................................... 20
2.3 ¿Qué es una Tabla Dinámica? .......................................................................... 20
2.4 Funcionamiento de las Tablas Dinámicas ........................................................ 20
2.5 Cómo crear una Tabla Dinámica ....................................................................... 23
2.6 Partes de una Tabla Dinámica .......................................................................... 27
2.7 Dar formato a una Tabla Dinámica ................................................................... 28
2.8 Formato de valores en una Tabla Dinámica ...................................................... 30
2.9 Filtrar una Tabla Dinámica ................................................................................ 33
2.10 Ejemplo ............................................................................................................ 34
2.11 Modificar campos de una Tabla Dinámica....................................................... 41
3 CAPÍTULO III Macros ....................................................................................... 44
3.1 Objetivo específico ............................................................................................. 44
3.2 El Editor de Visual Basic ................................................................................... 44
3.3 Los Módulos ....................................................................................................... 46
3.4 Los Objetos de Excel ........................................................................................ 47
3.5 Procedimientos y Funciones ............................................................................ 48
3.5.2 Funciones ....................................................................................................... 49
3.6 Creación de una Macro .................................................................................... 49
3.6.1 Crear una macro automáticamente .............................................................. 49
3.6.2 Ejecutar una macro ...................................................................................... 51
3.6.3 Crear una macro manualmente ................................................................... 52
EXCEL AVANZADO
3.6.4 Guardar archivos con Macros ...................................................................... 54
EXCEL AVANZADO
Introducción
Este MANUAL está dirigido a los planificadores de corto plazo y su objetivo es ayudar a recordar, actualizar y reforzar los conocimientos que han adquirido. Con los principios de seguridad y control de pérdidas y en su responsabilidad en el ámbito laboral para lograr una operación eficiente. Sin lugar a dudas el buen desempeño laboral solo se logra en la medida que se consigue compatibilizar eficientemente a los trabajadores con el cargo que tienen que desempeñar, de modo que exista una completa identificación entre ambos. Es así que después de varios años de planificación, se ha considerado necesario efectuar una reinstrucción participativa para ayudar a que los trabajadores actualicen los conocimientos requeridos por el puesto y se adapten a él; y de esta forma puedan lograr el objetivo fundamental de la EMPRESA, ser un productor de cobre eficiente, con costos competitivos, sin accidentes y con respeto por el medio ambiente. De esta manera las compañías mineras podrán asegurarse que sus planificadores logren ser sintomáticos a través de la capacitación, certificación y aptitud para planificar estos equipos y detectar, evaluar y manejar los riesgos antes y durante la realización de su trabajo.
EXCEL AVANZADO
7
1 Capítulo I Aplicación de Funciones 1.1. Objetivo específico Utilizar las funciones en fórmulas para satisfacer la recuperación de la información requerida de una planilla. 1.2. Funciones Lógicas 1.2.1 Función SI
La función SI devuelve un valor entre dos posibles valores, dependiendo de una condición indicada.
Sintaxis =SI(prueba lógica; [valor si verdadero]; [valor si falso])
prueba lógica: es una comparación entre dos celdas usando operadores lógicos.
Recuerda que los operadores lógicos son: =(igual), <(menor), >(mayor), <>(distinto), >=(mayor o igual), <=(menor o igual).
[valor si verdadero]: es el valor, celda o texto especificado a devolver si prueba lógica es verdadera.
[valor si falso]: es el valor, celda o texto especificado a devolver si prueba lógica es falsa.
Los argumentos valor si verdadero y valor si falso son opcionales. Si se omiten la función SI devuelve directamente VERDADERO o FALSO.
Veamos el siguiente ejemplo para entenderla mejor.
Ejemplo
Un profesor está analizando las calificaciones de sus alumnos en el rango D17:E25. En el rango F18:F25 desea que según la calificación mínima necesaria de la celda H18, aparezca un mensaje indicando si el alumno aprobó o no. Dichos mensajes están en las celdas J18 y J21. A continuación se presenta la solución.
EXCEL AVANZADO
8
1.2.2. Función Y
La función Y evalúa una serie de condiciones. Si todas las condiciones son verdaderas devuelve VERDADERO. Si al menos una de ellas es falsa devuelve FALSO.
Sintaxis =Y(valor lógico1; [valor lógico2];...) valor lógico1; [valor lógico2];… : pueden ser hasta 30 condiciones que se desean probar en las cuales se obtenga como resultado un valor lógico tipo VERDADERO o FALSO.
Veamos el siguiente ejemplo para entenderla mejor: Ejemplo
En el rango D18:E25 se tiene un listado del personal de una empresa, con su respectiva edad. El gerente desea identificar aquellas personas con edad mayor a la indicada en la celda H18 y menor o igual a la indicada en la celda K18. El resultado deber aparecer en el rango F18:F25. Veamos la solución:
EXCEL AVANZADO
9
2.3.3. Función O
La función O evalúa una serie de comparaciones. Si al menos una de ellas es verdadera, la función devuelve VERDADERO. Solo si todas ellas son falsas, devuelve FALSO.
Sintaxis =O(valor lógico1; [valor lógico2];...) valor lógico1; [valor lógico2];… : pueden ser hasta 30 condiciones que se desean probar en las cuales se obtenga como resultado un valor lógico tipo VERDADERO o FALSO.
Veamos el siguiente ejemplo para entenderla mejor.
Ejemplo
En este ejemplo se tiene un listado del personal de una empresa, con su respectivo día de trabajo. El gerente desea identificar aquellas personas que trabajaron alguno de los días indicados en la celdas H18 y K18. El resultado deber aparecer en el rango F18:F25. Veamos la solución:
1.3 Funciones de texto
1.3.1 Función MAYUSC La función MAYUSC convierte una cadena de texto indicada a mayúsculas. Sintaxis =MAYUSC(texto)
EXCEL AVANZADO
10
texto: es una cadena de letras a convertir en mayúsculas. Puede ser un texto entre comillas o una referencia a una celda.
Veamos el siguiente ejemplo para entenderla mejor. Ejemplo En el rango D18:D25 se tiene un listado de obras literarias con diferentes formas de escritura. En el rango E18:E25 se desea convertir cada letra de los títulos a mayúsculas. A continuación se presenta la solución.
1.3.2. Función MINUSC
La función MINUSC convierte una cadena de texto indicada a minúsculas. Sintaxis =MINUSC(texto) texto: es una cadena de letras a convertir en minúsculas. Puede ser un texto entre comillas o una referencia a una celda.
Veamos el siguiente ejemplo para entenderla mejor. Ejemplo En el rango D18:D25 se tiene un listado de obras literarias con diferentes formas de escritura. En el rango E18:E25 se desea convertir cada letra de los títulos en minúsculas. A continuación se presenta la solución.
EXCEL AVANZADO
11
1.3.3. Función CONCATENAR
La función CONCATENAR sirve para unir los valores o celdas especificadas. Sintaxis =CONCATENAR(texto1; texto2; ...) texto1, texto2, ...: son hasta 30 valores o referencias a celdas que se desean unir.
Veamos el siguiente ejemplo para entenderla mejor. Ejemplo En el rango D18:D20 se tienen nombres de programas de software. En el rango E18:E20 se desea agregar y unir a los mismos el valor de la celda G18. A continuación se presenta la solución.
1.3.4. Función NOMPROPIO La función NOMPROPIO convierte a mayúsculas la primera letra de cada palabra y a minúsculas el resto.
EXCEL AVANZADO
12
Sintaxis =NOMPROPIO(texto)
texto: es una cadena de letras a convertir al modelo mayúsculas / minúsculas para nombres propios. Puede ser un texto entre comillas o una referencia a una celda.
Veamos el siguiente ejemplo para entenderla mejor. Ejemplo En el rango D18:D25 se tiene un listado de obras literarias con diferentes formas de escritura. En el rango E18:E25 se desea convertir cada palabra al modelo de mayúsculas / minúsculas para nombres propios. A continuación se presenta la solución. Dichos resultados deben aparecer en el rango E18:E25. Veamos la solución:
1.3.5. Función EXTRAE La función EXTRAE devuelve una cantidad de caracteres específicos de una cadena de texto.
Sintaxis =EXTRAE(texto; posición inicial; núm de caracteres)
texto: es una referencia a una celda, una función que devuelva una cadena de caracteres o un texto entre comillas al cual se le extrae una cantidad de caracteres indicados por núm de caracteres.
EXCEL AVANZADO
13
posición inicial: es un número que indica la posición donde comienza la subcadena a extraer.
núm de caracteres: es un valor que indica la cantidad de caracteres que se extraen a partir de posición inicial.
Veamos el siguiente ejemplo para entenderla mejor.
Ejemplo En el rango D18:D23 se tiene una serie de datos de algunos forjadores de la humanidad. En el rango E18:E21 se desean extraer la cantidad de caracteres indicada en la celda G21, comenzando desde la posición indicada en la celda G18. A continuación se presenta la solución.
1.4. Funciones de Búsqueda y Referencia 1.4.1. Función BUSCARV La función BUSCARV devuelve un valor determinado de una columna indicada perteneciente a una tabla, según un índice.
Sintaxis =BUSCARV(valor buscado; matriz de comparación; indicador columnas; [ordenado])
valor buscado: es el valor buscado en la primera columna de la tabla.
matriz de comparación: es la tabla donde se efectúa la búsqueda.
indicador columnas: es un número que especifica la columna de la tabla de donde se devuelve un valor.
EXCEL AVANZADO
14
ordenado: es un valor lógico (VERDADERO o FALSO) que indica que la primera columna de la tabla donde se buscan los datos esta ordenada o no. Si omite este argumento se considera VERDADERO, es decir, se considera que la columna uno de la tabla esta ordenada. Si no se está seguro poner siempre FALSO.
Veamos el siguiente ejemplo para entenderla mejor Ejemplo Un doctor analiza los meses de edad de bebés en el rango D17:E25. En el rango F18:F25 desea saber la cantidad de vacunas que debe tener cada bebé según la edad. En el rango H17:I21 está la tabla que indica cantidad de vacunas según edad. A continuación se presenta la solución.
1.4.2. Función BUSCARH La función BUSCARH devuelve un valor determinado de una fila indicada perteneciente a una tabla, según un índice.
Sintaxis =BUSCARH(valor buscado; matriz buscar en; indicador filas; [ordenado])
valor buscado: es el valor buscado en la primera fila de la tabla.
matriz buscar en: es la tabla donde se efectúa la búsqueda.
indicador filas: es un número que especifica la fila de la tabla donde se buscará valor.
ordenado: es un valor lógico (VERDADERO o FALSO) que indica que la primera fila de la tabla donde se buscan los datos esta ordenada o no. Si se omite este argumento se considera que la fila uno de la tabla esta ordenada (VERDADERO).
EXCEL AVANZADO
15
Veamos el siguiente ejemplo para entenderla mejor Ejemplo Un doctor analiza los meses de edad de bebés en el rango D17:E25. En el rango F18:F25 se desea saber la cantidad de vacunas que debe tener cada bebé según la edad. En el rango H17:L18 se encuentra la cantidad de vacunas para cada edad. A continuación se presenta la solución.
1.4.3. Función ÍNDICE La función INDICE devuelve el valor referido a una fila y columna indicadas dentro de una tabla especificada.
Sintaxis =INDICE(matriz; núm fila; núm columna)
matriz: es la tabla donde se efectúa la búsqueda.
núm fila: es un número que especifica la fila de la tabla donde se buscará el valor. Si se omite debe estar presente núm columna.
núm columna: es un número que especifica la columna de la tabla donde se buscará el valor. Si se omite debe estar presente núm fila.
Veamos el siguiente ejemplo para entenderla mejor
Ejemplo Se analiza una tabla de datos de personas en el rango D17:F25. En la celda H18 se desea saber cuál es el dato que corresponde a la fila y columna indicadas en las celdas J18 y K18 respectivamente. A continuación se presenta la solución. Veamos la solución:
EXCEL AVANZADO
16
1.4.4. Función COINCIDIR La función COINCIDIR devuelve el número de posición de un dato que coincida con un valor especificado dentro de una tabla.
Sintaxis =COINCIDIR(valor buscado; matriz buscada; tipo de coincidencia)
valor buscado: es el valor buscado dentro del rango de matriz buscada.
matriz buscada: es un rango donde efectúa la búsqueda.
tipo de coincidencia: si es 0 se busca un valor que sea igual al dato indicado. Si es 1 se busca el mayor valor en un rango que sea menor o igual al dato buscado (el rango debe estar ordenado en forma creciente). Si es -1 busca el valor mayor o igual al dato considerando el rango ordenado decrecientemente.
Veamos el siguiente ejemplo para entenderla mejor Ejemplo En el rango D17:E25 se analiza la tabla de posiciones de un torneo de fútbol. En la celda G18 se desea saber qué posición ocupa en esa tabla el equipo indicado en la celda I18. A continuación se presenta la solución.
EXCEL AVANZADO
17
1.5. Funciones Estadísticas 1.5.1. SUMAR.SI La función SUMAR.SI realiza la suma de un rango de acuerdo a un criterio determinado.
Sintaxis
=SUMAR.SI(rango; criterio; [rango suma])
rango: es el rango que será evaluado.
criterio: es la condición que identificará las celdas en el rango evaluado.
[rango suma]: es el rango que contiene los valores a sumar de acuerdo a lo especificado en el rango de criterio. Es opcional.
Veamos el siguiente ejemplo para entenderla mejor. Ejemplo En el rango D18:D25 se tiene un listado de comisionistas. En la celda H21 se desea calcular la cantidad de kilómetros que realizó el comisionista indicado en la celda H18. A continuación se presenta la solución.
1.5.2. CONTAR.SI La función CONTAR.SI cuenta valores en un rango, de acuerdo a un criterio determinado.
Sintaxis = CONTAR.SI(rango; criterio)
EXCEL AVANZADO
18
rango: es el rango que será evaluado.
criterio: es la condición que identificará las celdas a contar en el rango evaluado. Veamos el siguiente ejemplo para entenderla mejor. Ejemplo Un gerente examina ingresos, salidas y faltantes que se van produciendo en un inventario día a día, en el rango D18:E25. En la celda H18 desea saber las cantidades de ingresos, salidas o faltantes, según lo indicado en la celda K18. A continuación se presenta la solución.
1.5.3. CONTARA La función CONTARA cuenta la cantidad de celdas con valores, tanto numéricos como texto, en el rango especificado.
Sintaxis =CONTARA(valor1; valor2; ...) valor1, valor2: son hasta 30 referencias a celdas, rangos y/o funciones. Veamos el siguiente ejemplo para entenderla mejor
Ejemplo Un gerente analiza las cantidades de ventas de sus empleados en el rango D18:E25. En la celda H18 desea obtener la cantidad de vendedores que concretaron ventas. A continuación se presenta la solución.
EXCEL AVANZADO
19
EXCEL AVANZADO
20
Capítulo II Tablas Dinámicas 2.1. Objetivo específico Aplicar funciones para realizar operaciones más complejas en la obtención de la información.
2.2. Introducción
Las Tablas Dinámicas son una forma alternativa de presentar o resumir los datos de una lista, es decir, una forma de ver los datos desde puntos de vista diferentes.
El nombre Tabla Dinámica se debe a que los encabezados de fila y columna de la lista pueden cambiar de posición y también pueden ser filtrados.
Con las Tablas Dinámicas también podremos preparar los datos para ser utilizados en la confección de gráficos.
La comprensión cabal de este tema se obtiene con la práctica y es así como se verá que es uno de los tópicos más potentes de Excel, principalmente en las versiones más recientes.
2.3. ¿Qué es una Tabla Dinámica?
Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te estás perdiendo de utilizar una gran herramienta de Excel.
Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.
2.4. Funcionamiento de las Tablas Dinámicas
Las tablas dinámicas permiten agrupar datos en una gran cantidad de maneras diferentes para poder obtener la información que necesitamos. En esta ocasión explicaré el funcionamiento básico de una tabla dinámica.
EXCEL AVANZADO
21
Supongamos que tienes una tabla de datos que contiene mucha información sobre las ventas de la compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.
Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas por producto. Así que lo que necesitas hacer es sumar las ventas para cada ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:
Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:
EXCEL AVANZADO
22
Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.
Finalmente elegimos una columna de valores numéricos que serán calculados y resumidos en la tabla dinámica:
Así tendrás un reporte que compara las ciudades y los productos y para cada combinación obtendrás el total de ventas.
EXCEL AVANZADO
23
Lo más importante que quiero transmitir con este artículo es que las tablas dinámicas te permiten elegir entre todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un buen análisis de la información.
2.5. Cómo crear una Tabla Dinámica
Las tablas dinámicas reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.
Usaremos como ejemplo la tabla que se encuentra más abajo.
Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.
EXCEL AVANZADO
24
Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.
Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.
EXCEL AVANZADO
25
En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.
Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro.
EXCEL AVANZADO
26
Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Moveremos al área de columnas el campo Producto y como filas al campo Ciudad. Finalmente como valores colocaré el campo Ventas.
De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:
Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas.
EXCEL AVANZADO
27
2.6. Partes de una Tabla Dinámica
Ahora analizaremos con un poco más de detalle cada una de las partes que las conforman.
Justo cuando se ha creado una tabla dinámica se muestra en la parte derecha de la hoja la lista de campos disponibles y por debajo las áreas donde podemos arrastrar dichos campos. Estas áreas denotan cada una de las partes de una tabla dinámica.
Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que vez en pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas.
Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica.
Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica.
Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.
Una vez especificados los campos para cada una de las áreas la tabla dinámica cobra vida. Puedes tener una tabla dinámica funcional con tan solo especificar las columnas, filas y valores. Los filtros son una herramienta para mejorar el análisis sobre los datos de la tabla dinámica.
EXCEL AVANZADO
28
2.7. Dar formato a una Tabla Dinámica
Una vez que has creado una tabla dinámica, Excel permite aplicarle formato fácilmente como si fuera una tabla de datos. La ficha Diseño incluye comandos especiales para aplicar formato a una tabla dinámica.
La dicha Diseño es una ficha contextual, por lo que deberás seleccionar la tabla dinámica para que se muestre.
Esta ficha está dividida en tres grupos.
Diseño. Este grupo permite agregar subtotales y totales generales a la tabla dinámica así como modificar aspectos básicos de diseño.
Opciones de estilo de tabla dinámica. Las opciones de este grupo permiten restringir la selección de estilos que se muestran en el grupo que se encuentra justo a su derecha. Es decir, si seleccionas la opción “Filas con bandas”, entonces se mostrarán a la derecha los estilos que tienen filas con bandas.
EXCEL AVANZADO
29
Estilos de tabla dinámica. Muestra la galería de estilos que se pueden aplicar a la tabla dinámica. Con tan sólo hacer clic sobre el estilo deseado se aplicará sobre la tabla.
Puedes hacer clic en el botón Más del grupo Estilos de tabla dinámica para ver todos los estilos disponibles. Con tan sólo elegir alguno de los estilos se aplicará inmediatamente a la tabla dinámica.
EXCEL AVANZADO
30
2.8. Formato de valores en una Tabla Dinámica
En esta ocasión te mostraré cómo dar formato rápidamente a los valores agrupados de una tabla dinámica de manera de puedan tener el formato de número adecuado. Solamente sigue los siguientes pasos:
Supongamos la siguiente tabla dinámica:
EXCEL AVANZADO
31
Para dar formato a los valores numéricos debes hacer clic sobre el campo correspondiente dentro del área Valores.
Del menú mostrado debes seleccionar la opción Configuración de campo de valor.
Se mostrará el cuadro de diálogo Configuración de campo de valor.
EXCEL AVANZADO
32
Debes hacer clic en el botón Formato de número y se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato deseado:
Debes hacer clic en el botón Formato de número y se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato deseado:
EXCEL AVANZADO
33
Después de hacer la selección adecuada acepta los cambios y de inmediato se aplicará el nuevo formato a todos los valores de la tabla dinámica:
2.9. Filtrar una Tabla Dinámica
Puedes filtrar y ordenar la información que se encuentra dentro de una tabla dinámica utilizando los filtros que Excel coloca de manera predeterminada en el reporte como Etiquetas de columna y Etiquetas de fila.
Al seleccionar cualquier de las opciones del filtro, la información será resumida y solamente mostrará un subconjunto de los datos de la tabla dinámica.
Una vez que se ha aplicado un filtro Excel reemplaza el icono predeterminado para indicar que ese campo está siendo actualmente filtrado.
EXCEL AVANZADO
34
Para mostrar de nuevo todos los valores de los campos filtrados debes hacer clic en el botón de filtrado y seleccionar la opción Borrar filtro de.
De igual manera puedes ordenar instantáneamente los valores de la tabla dinámica. Solamente haz clic sobre el botón de Etiquetas de fila o Etiquetas de columna y elige la opción Ordenar de A a Z o la opción Ordenar de Z a A.
2.10. Ejemplo
Una empresa de exportación de máquinas agrícolas tiene la siguiente tabla en una Hoja de Excel 2007.donde figuran los datos del 1º trimestre del año.
EXCEL AVANZADO
35
A partir de ella se quiere crear una nueva tabla en la que se informe la cantidad de maquinarias exportadas y el detalle de cuantas se vendieron de cada una.
Para crear la tabla que nos responda a estas preguntas, nos ubicamos en cualquier celda de la tabla, luego vamos a la pestaña Insertar panel Tablas.
En el que pulsamos en Tabla dinámica, al hacer esto aparece un menú en el que tenemos las opciones de Gráfico dinámico y Tabla dinámica, pulsaremos en este último como se puede en la figura del lado derecho.
A continuación aparece el panel Crear tabla dinámica
EXCEL AVANZADO
36
En este caso el rango de la tabla base (tabla de partida) queda automáticamente seleccionado, incluyendo los rótulos, también seleccionamos "Nueva hoja de cálculo" y pulsamos en aceptar y automáticamente se crea la Hoja4 en la que se destacan:
1. Una nueva cinta de opciones denominada "Herramienta de tabla dinámica"
Un panel llamado "Lista de campos de tabla dinámica" que es una novedad de Excel 2007 y que tiene un rectángulo en la parte superior, donde se ubican los campos o rótulos de la tabla de origen, también hay cuatro rectángulos, en la parte inferior, denominados " Filtro de informe", "Rótulos de columna", "Rótulos de fila" y "Valores" donde irán apareciendo los rótulos de la tabla a medida que los seleccionemos en la parte suprior en forma de botones como el que se muestra.
Los botones se pueden arrastrar de un rectángulo a otro aunque los rótulos que tienen valores numéricos, siempre aparecen en rectángulo "Valores".
Como se ve, hasta este momento, tiene las casillas de verificación de rótulos sin marcar, pues bien, es justamente seleccionar las casillas "MAQUINA" y "CANTIDAD" lo debemos hacer en el próximo paso.
EXCEL AVANZADO
37
Observar que aparecen automáticamente 2 botones.
Listo ya tenemos la primera tabla con las respuestas pedidas recuadradas en rojo.
Si nos interesara saber solamente el dato de cuantas fertilizadora y sembradora se exportaron, junto con su total, tenemos un comando de filtrado en la parte superior y así obtenemos la siguiente tabla.
Luego de aplicar el filtro.
Este es un ejemplo didáctico para hacer una introducción, pero se le puede sacar mucho más el jugo a esta herramienta.
EXCEL AVANZADO
38
Podemos querer saber el detalle de las máquinas que fueron exportadas y por cual vendedor. En este caso tendremos que seleccionar la casilla del rótulo VENDEDOR y en la nueva Hoja aparece una tabla y el panel "Lista de campos de tabla dinámica"
Se ve que en los rectángulos, más precisamente en el llamado Rótulo de fila, aparece un nuevo botón, el botón VENDEDOR en forma simultánea a la selección de la casilla de verificación VENDEDOR.
La tabla responde a lo que queremos saber, pero le podemos dar otro aspecto arrastrando el botón VENDEDOR al rectángulo "Rótulo de columna".
EXCEL AVANZADO
39
Y la tabla queda como la que está abajo, luego de haberle dado algo de formato
En esta tabla se puede ver, por ejemplo, que Peña vendió 16 fertilizadoras y un tractor.
Sería interesante saber el número de maquinarias exportadas a que país y por cual vendedor.
Para hacerlo, verificamos la casilla "País" y filtramos MAQUINA
y se genera la tabla
Donde se puede ver que Chuan le vendió 30 máquinas a China.
Hasta ahora nuestra tabla dinámica efectúa sumas, pero puede hacer otras operaciones tales como porcentajes, máximos, mínimo y otras más que iremos viendo.
EXCEL AVANZADO
40
Podemos preguntarnos cuál fue la máxima cantidad de maquinarias que vendió Peña. Para hacer esto nos ubicamos en una celda cualquiera de la tabla de arriba y apretando el botón derecho del mouse aparece el siguiente menú emergente.
En el que pulsamos en "Configuración de campo de valor", como indica la flecha, con lo que aparece el panel también llamado "Configuración de campo de valor"
En el que si vamos a la pestaña "Resumen por" están las opciones de resumen en las que elegiremos Máx y luego de aceptar, la tabla se transforma en:
EXCEL AVANZADO
41
Que nos dice que la cantidad Máxima de maquinarias que vendió Peña es 9, como se ve en el recuadro rojo, en forma adicional podemos ver que esta cantidad fue vendida a Brasil (verificar con la tabla de partida o tabla base).
Este resultado se puede ver con una simple inspección de los datos, que en este caso son tres, pero cuando estos aumentan es donde vemos la utilidad del cálculo de un máximo.
2.11. Modificar campos de una Tabla Dinámica
Las tablas dinámicas son muy fáciles de manipular y de modificar. Excel permite cambiar los campos de la tabla de una manera muy sencilla y reestructurar la información con tan solo arrastrar los nuevos campos.
Las modificaciones las haremos en la Lista de campos de la tabla dinámica que se encuentra en la parte derecha de la pantalla. Esta lista se mostrará con tan solo hacer clic en cualquier celda de la tabla dinámica.
EXCEL AVANZADO
42
Si por alguna razón esta lista de campos no aparece en tu libro de Excel, debes activarla con el comando Lista de campo que se encuentra dentro del grupo Mostrar de la ficha Opciones.
Para remover un campo arrástralo fuera del área donde se encuentra y cuando el puntero del ratón se convierta en una “X” suelta el botón del ratón para eliminar el campo. Otra manera de eliminar el campo es haciendo clic sobre él y seleccionar la opción Quitar campo.
Para mover un campo ya existente hacia una nueva ubicación solamente arrástralo de un área a otra o de igual manera selecciona cualquiera de las opciones de menú que permiten mover el campo a cualquiera de las áreas disponibles: Mover al filtro de informe, Mover a rótulos de fila, Mover a rótulos de columna o Mover a valores.
EXCEL AVANZADO
43
Con este método puedes cambiar fácilmente las columnas por filas y viceversa.
Finalmente para agregar un nuevo campo puedes arrastrarlo desde la lista de campos hacia el área deseada. También puedes marcar la caja de selección del campo lo cual hará que sea agregado a alguna de las áreas predeterminadas.
Las tablas dinámicas son un elemento de análisis de información de Excel muy poderosa y esa fortaleza proviene de la facilidad que provee para manipular la información de acuerdo a tus necesidades.
EXCEL AVANZADO
44
Capítulo III: Macros 3.1. Objetivo específico Utilizar Visual Basic Aplicaciones para la construcción de macros automatizando procesos que son repetitivos. 3.2. El Editor de Visual Basic
El lenguaje de programación que utiliza Excel para trabajar con macros es el VBA (Visual Basic Application) que como podemos deducir está basado en Visual Basic.
En la imagen de arriba podemos ver la pantalla general del editor de Visual Basic.
El menú del editor de Visual Basic
Esta es la barra de menú del editor de Visual Basic donde podemos acceder a las diversas opciones y comandos del propio editor.
EXCEL AVANZADO
45
La barra de botones
Esta es la barra de botones de Visual Basic, donde podemos acceder de una manera más rápida a las opciones más comúnmente utilizadas, como son la ejecución, la parada, guardar, etc... En la parte izquierda de la pantalla podemos ver dos recuadros, el recuadro superior es el del proyecto. Aquí se encuentran los módulos, las hojas utilizadas en los procedimientos/funciones, etc.
En la parte inferior tenemos otro recuadro, con las propiedades del objeto seleccionado. En esta imagen podemos ver las propiedades del objeto Hoja1 y que es una hoja de cálculo (Worksheet).
En la parte derecha tenemos el espacio dedicado a redactar el código de los procedimientos/funciones, y en la parte superior existen dos cuadros combinados donde podemos seleccionar los objetos y los métodos de ese objeto respectivamente.
EXCEL AVANZADO
46
3.3. Los Módulos
Un módulo es el área donde se crean las subrutinas (macros) que automatizan algún proceso en Excel.
Añadir un módulo
Para añadir un nuevo módulo de VBA a un proyecto, hay que seleccionar el nombre del proyecto en la ventana Explorador de proyectos y seleccionar INSERTAR MODULO.
También puede ir a la opción Insertar de la barra de menú y seleccionar Módulo.
Cuando se graba una macro, Excel inserta automáticamente un módulo VBA para contener el código grabado.
EXCEL AVANZADO
47
Quitar un módulo
Hay que seleccionar el nombre del módulo en la ventana del
Explorador de proyectos y elegir ARCHIVO QUITAR.
3.4. Los Objetos de Excel Visual Basic Aplicaciones controla todos los objetos de Excel. Veremos los objetos más importantes que tienen relación con Libros, Hojas y Celdas. A continuación se listan algunos de ellos:
Workbooks Worksheets Activecell Cells Range Row Columns
Abrir un libro Workbooks.Open FileName:=Nombre, updateLinks:=False Cerrar un libro ActiveWorkbook.Close Cerrar todos los libros
Application.DisplayAlerts = False
Application.Workbooks.Close
Guardar un libro ActiveWorkbook.SaveAs NombreArchivo Borrar un libro Workbooks("C:\TuLibro.xls").Delete Crear un libro Workbooks("C:\TuLibro.xls").Delete
EXCEL AVANZADO
48
Imprimir un libro ActiveWorkbook.Printout
Agregar hoja ActiveWorkbook.Worksheets.Add Seleccionar la primera hoja del libro ActiveWorkbook.Sheets(1).Select Selección de un rango Range("B6:B10").Select Borrar el contenido de un rango Range("A1").ClearContents 3.5. Procedimientos y Funciones 3.5.1. Procedimientos
Un procedimiento es un bloque de instrucciones de código que sirven para llevar a cabo alguna tarea específica. Un procedimiento empieza siempre con la instrucción:
Sub Nombre_procedimiento Instrucciones … End sub
A continuación crearemos un procedimiento para poner el texto "Hola" en la casilla A1. Ejemplo 1
Sub Primero Range(“A1”).Value=”Hola” End Sub
Observe el código: Range("A1").Value="Hola"
EXCEL AVANZADO
49
En esta línea estamos indicando que trabajamos con un objeto Range. Para indicarle que nos referimos a la casilla A1, encerramos entre paréntesis esta referencia (más adelante verá otra forma de referirnos a las casillas). De este objeto, indicamos que queremos establecer un nuevo valor para la propiedad Value, observe que para separar el objeto de su propiedad utilizamos la notación punto.
Recuerde que el conjunto Range es un objeto que pende del objeto WorkSheets, así por ejemplo el siguiente código haría lo mismo que el anterior:
WorkSheets(1).Range("A1").Value = "Hola" Ejecutar un procedimiento
1) Ubicar el cursor dentro del procedimiento. 2) Pulse la tecla F5.
3.5.2. Funciones Una función es un segmento de código (instrucciones) igual que un procedimiento, con la única diferencia que la función devuelve un valor. Ejemplo Supongamos que queremos crear una función que nos calcule y devuelva un sueldo base reajustado en un 5%. La función sería: Function ReajustarSB (SueldoBase) as Long ReajustarSB = SueldoBase * 1,05 End Function 3.6. Creación de una Macro
3.6.1. Crear una macro automáticamente
La forma más fácil e intuitiva de crear macros es crearlas mediante el grabador de macros del que dispone Excel.
EXCEL AVANZADO
50
Este grabador de macros te permite grabar las acciones deseadas que posteriormente las traduce a instrucciones en VBA, las cuales podemos modificar posteriormente si tenemos conocimientos de programación.
Para grabar una macro debemos acceder a la pestaña Vista y despliega el submenú Macros y dentro de este submenú seleccionar la opción Grabar macro...
Además de esta opción en el menú podemos encontrar las siguientes opciones:
Ver Macros... - Donde accedemos a un listado de las macros creadas en ese libro.
Usar referencias relativas - Con esta opción utilizaremos referencias relativas para que las macros se graben con acciones relativas a la celda inicial seleccionada.
Al seleccionar la opción Grabar macro..., lo primero que vemos es el cuadro de diálogo Grabar macro donde podemos dar un nombre a la macro (no está permitido insertar espacios en blanco en el nombre de la macro).
EXCEL AVANZADO
51
Podemos asignarle un Método abreviado: mediante la combinación de las tecla CTRL + "una tecla del teclado". El problema está en encontrar una combinación que no utilice ya Excel.
En Guardar macro en: podemos seleccionar guardar la macro en el libro activo, en el libro de macros personal o en otro libro.
En Descripción: podemos describir cuál es el cometido de la macro o cualquier otro dato que creamos conveniente.
Para comenzar la grabación de la macro pulsamos el botón Aceptar y a continuación, si nos fijamos en la barra de estado, encontraremos este botón en la barra de estado donde tenemos la opción de detener la grabación.
A partir de entonces debemos realizar las acciones que queramos grabar, es conveniente no seleccionar ninguna celda a partir de la grabación, ya que si seleccionamos alguna celda posteriormente, cuando ejecutemos la macro, la selección nos puede ocasionar problemas de celdas fuera de rango.
Una vez concluidas las acciones que queremos grabar, presionamos sobre el botón
Detener de la barra de estado, o accediendo al menú de Macros y haciendo clic en
.
3.6.2. Ejecutar una macro
Una vez creada una macro, la podremos ejecutar las veces que queramos.
Antes de dar la orden de ejecución de la macro, dependiendo del tipo de macro que sea, será necesario seleccionar o no las celdas que queramos queden afectadas por las acciones de la macro.
Por ejemplo si hemos creado una macro que automáticamente da formato a las celdas seleccionadas, tendremos que seleccionar las celdas previamente antes de ejecutar la macro.
Para ejecutar la macro debemos acceder al menú Ver Macros..., que se encuentra en el menú Macros de la pestaña Vista, y nos aparece el cuadro de diálogo Macro como el que vemos en la imagen donde tenemos una lista con las macros creadas.
EXCEL AVANZADO
52
Debemos seleccionar la macro deseada y pulsar sobre el botón Ejecutar. Se cerrará el cuadro y se ejecutará la macro.
En cuanto al resto de botones:
Cancelar - Cierra el cuadro de diálogo sin realizar ninguna acción.
Paso a paso - Ejecuta la macro instrucción por instrucción abriendo el editor de programación de Visual Basic.
Modificar - Abre el editor de programación de Visual Basic para modificar el código de la macro. Estos dos últimos botones son para los que sapan programar.
Eliminar - Borra la macro.
Opciones - Abre otro cuadro de diálogo donde podemos modificar la tecla de método abreviado (combinación de teclas que provoca la ejecución de la macro sin necesidad de utilizar el menú) y la descripción de la macro
3.6.3. Crear una macro manualmente
Para crear una macro de forma manual es necesario tener conocimientos de programación en general y de Visual Basic en particular, ya que es el lenguaje de programación en el que se basa el VBA de Excel. Si no tienes esta base puedes saltarte este punto del tema.
EXCEL AVANZADO
53
Primero debemos abrir el editor Visual Basic presionando la combinación de teclas Alt + F11.
Una vez abierto el editor de Visual Basic debemos insertar un módulo de trabajo que es donde se almacena el código de las funciones o procedimientos de las macros. Para insertar un módulo accedemos al menú Insertar → Módulo.
A continuación debemos plantearnos si lo que vamos a crear es una función (en el caso que devuelva algún valor), o si por el contrario es un procedimiento (si no devuelve ningún valor).
Una vez concretado que es lo que vamos a crear, accedemos al menú Insertar → Procedimiento...
Nos aparece un cuadro de diálogo como vemos en la imagen donde le damos el Nombre: al procedimiento/función sin insertar espacios en su nombre.
También escogemos de qué Tipo es, si es un Procedimiento, Función o es una Propiedad.
Además podemos seleccionar el Ámbito de ejecución. Si lo ponemos como Público podremos utilizar el procedimiento/función desde cualquier otro módulo, pero si lo creamos como Privado solo podremos utilizarlo dentro de ese módulo.
Una vez seleccionado el tipo de procedimiento y el ámbito presionamos sobre Aceptar y se abre el editor de Visual Basic donde escribimos las instrucciones necesarias para definir la macro.
EXCEL AVANZADO
54
3.6.4. Guardar archivos con Macros
Cuando guardamos un archivo y queremos que las Macros que hemos creado se almacenen con el resto de las hojas de cálculo deberemos utilizar un tipo de archivo diferente.
Para ello deberemos ir al Botón Office y seleccionar la opción Guardar como.
Se abrirá el cuadro de diálogo Guardar como. En el desplegable Guardar como tipo seleccionar Libro de Excel habilitado para macros (*.xlsm).
EXCEL AVANZADO
55
.
Dale un nombre y el archivo se almacenará.
Cuando abrimos un archivo que tiene Macros almacenadas se nos mostrará este anuncio bajo la banda de opciones:
Esto ocurre porque Office no conoce la procedencia de las Macros. Como están compuestas por código podrían realizar acciones que fuesen perjudiciales para nuestro equipo.
Si confías en las posibles Macros que contuviese el archivo o las has creado tú pulsa el botón Opciones para activarlas.
Aparecerá este cuadro de diálogo:
EXCEL AVANZADO
56
Selecciona la opción Habilitar este contenido y pulsa Aceptar.