MATERIA:TECNOLOGIAS DE LA INFORMACIÓN Y LA COMUNICACIÓN.
TEMAS: TABLAS DINÁMICAS Y MACROS.
DOCENTE: RANULFO FLORES ARIZMENDI.
ALUMNA: NINFA ALHELY NAVA JIMÉNEZ.
GRUPO: 301 SEMESTRE-3
TABLAS DINÁMICAS Y MACROS
TABLAS DINAMICAS
Una tabla dinámica es una herramienta de visión que se encuentra en diversos programas y paquetes de datos que sirve para agrupar información con un sencillo análisis por ventanas. Dichas ventanas pueden ser hojas de cálculo (por ejemplo, en Microsoft Excel, y LibreOffice Calc) o programas de inteligencia empresarial. Entre otras funciones, las tablas dinámicas pueden de forma automática, clasificar, contar, totalizar o dar la media de los datos almacenados en una tabla o una hoja de cálculo. Los resultados se muestran en una segunda tabla (llamada "tabla dinámica" o "tabla pivote") que muestra los datos resumidos. Las tablas de cotejo pivote son también útiles para la creación rápida de tabulaciones cruzadas sin ponderar. El usuario crea y cambia la estructura del resumen mediante arrastrar y soltar campos de forma gráfica. Esta "rotación" o giro de la tabla de resumen da su nombre al concepto. El término tabla pivote es una frase genérica utilizada por múltiples proveedores. Sin embargo, Microsoft Corporation ha registrado la marca específica de "PivotTable".
Las tablas pivote pueden ser vistas como una simplificación de los conceptos más completos y complejos llamados OLAP.
Con las tablas dinámicas nos será más práctico procesar datos e información en nuestras hojas de cálculo de Excel. Veamos cómo.
Junto a las funciones, las tablas dinámicas son una de las características de Excel que más juego dan para trabajar con hojas de cálculo.
A diferencia de las tablas por defecto, las tablas dinámicas sirven para filtrar y destacar datos de una hoja de cálculo de manera versátil.
Para entendernos, las tablas dinámicasnos dan una mejor perspectiva de una tabla repleta de datos monolíticos.
Convertir una tabla normal y corriente en tabla dinámica es tan fácil como seleccionar la tabla o las columnas y filas que queremos aprovechar y desdeInsertar pulsar en Tabla dinámica.
A partir de aquí, la cosa se complica.
Crear una tabla dinámica
El propósito de la tabla dinámica es ordenar mejor los datos que hemos añadido a una hoja de cálculo, además de poder filtrar grandes cantidades de información ocultando temporalmente aquello que no necesitamos ver en ese momento.
De ahí que, para crear una tabla dinámica, partimos de datos previos a partir de los que se crearán campos de tabla dinámicaque se pueden activar o desactivar.
Como decía más arriba, una vez hemos seleccionado las celdas o la tabla a usar, creamos la tabla desde Insertar > Tabla dinámica. Más adelante podremos elegirTablas dinámicas recomendadas para ver una selección de tablas que encajen con los datos que facilitamos.
En el menú que veremos a continuación, el campo Seleccione una tabla o rango se rellenará automáticamente con las celdas o columnas que hemos seleccionado previamente, si bien podemos hacer una nueva selección.
Para mantener la tabla previa, elegimos la opción Nueva hoja de cálculo.
Obtendremos una tabla en blanco y, a mano derecha, un panel para configurar la tabla dinámica añadiendo los campos que nos interesa manejar.
En función de los campos que activemos,los datos se relacionarán entre síobteniendo combinaciones más prácticas para procesar mejor esa información.
Además, la tabla va cambiando automáticamente, algo que con una tabla corriente deberíamos hacer manualmente.
Por otro lado, la tabla dinámica está asociada a la original, de manera que, si cambiamos o añadimos datos, podemosactualizar nuestra tabla dinámica para que incorpore esos cambios.
Para ello, seleccionando cualquier celda, vamos a Analizar > Actualizar y se incorporarán los datos nuevos de la tabla original.
Personalizar la tabla dinámica
Otras cosas que podemos hacer son, además de activar y desactivar campos,configurarlos como fila o columna manualmente. Y en el caso de datos numéricos, arrastrándolos a Valoresobtendremos operaciones habituales como sumar todos los datos de una lista.
Jugando con estos elementos, de una única tabla obtendremos distintas combinaciones según qué respuesta queremos satisfacer con la información que vemos en pantalla.
Al terminar, podemos ocultar el panel derecho pulsando la X correspondiente y recuperarlo cuando lo necesitemos desdeMostrar > Lista de campos.
Por lo demás, es posible cambiar el formato o diseño de las celdas y de los datos como en una tabla cualquiera.
Por ejemplo, desde el menú contextual (clic con el botón derecho del ratón) encontraremos Configuración de campo de valor… para adaptar los números a un formato adecuado u Opciones de tabla dinámica… para alterar el aspecto de toda la tabla.
Agrupar datos
Es posible que tengamos demasiados campos y que, algunos de ellos, sean más útiles combinados. De ahí que las tablas dinámicas nos permitan agrupar datos.
Haciendo clic derecho en una celda de la columna a agrupar, veremos las dos opciones: Agrupar… y Desagrupar…. Obviamente, no podemos agrupar según qué datos, ya que deben ser equivalentes, como, por ejemplo, los relacionados conperiodos de tiempo.
En caso de no quedar satisfechos, siempre podemos acudir a Desagrupar… y obtener el resultado opuesto según la configuración que le demos.
Segmentar datos
Si manejamos una gran cantidad de datosen nuestra tabla dinámica, nos será más útil filtrar esa información mediante segmentaciones.
Desde Analizar > Insertar una segmentación de datos seleccionamos los campos donde necesitamos esassegmentaciones o filtros para manejar mejor muchos datos.
Las segmentaciones se mostrarán en forma de cuadros flotantes mostrando los elementos asociados a los filtros y podemos colocarnos donde queramos.
Con las segmentaciones podremos ocultar toda la información de que disponemos salvo aquella relacionada con el elemento que marquemos, pudiendo realizar dos o más selecciones al pulsar el botónSelección múltiple. También es posible desactivar el filtro pulsando en el botónBorrar filtro del cuadro flotante.
Si queremos prescindir de esos filtros, haciendo clic derecho veremos la opciónQuitar y el nombre del filtro.
Gráficos dinámicos
Aunque las tablas dinámicas ayudan mucho a comprender y analizar datos, no puede faltar la opción de crear gráficos para mostrar visualmente esos datos.
En el caso de las tablas dinámicas es posible crear gráficos dinámicos desdeAnalizar > Gráfico dinámico. Se nos mostrarán todos los tipos de gráficos disponibles organizados por categoría, como columna, radial, circular, barra, etc.
Los gráficos dinámicos se configuran igual que las tablas, desde el panel lateral derecho que, si está oculto, podemos recuperar desde Mostrar > Lista de campos.
Cambiando valores, filas y columnas alteraremos los elementos que representa el gráfico.
MACROS
Macros en Excel: optimiza tu trabajo con las hojas de cálculo
Cuando se trabaja habitualmente con programas de edición de textos, con bases de datos o con hojas de cálculo es fácil darse cuenta de lo que significa tener que introducir constantemente los mismos comandos. En especial cuando se trata de procesos complejos o en varios pasos, este tipo de programas puede poner a prueba los nervios más resistentes. Las denominadas macros, que se traducirían del inglés como “instrucciones”,registran una secuencia de comandos para que puedan ser ejecutadas automáticamente por el usuario en un determinado momento, convirtiéndose así en un elemento de apoyo más que bien recibido. Escritas en lenguaje de programación, su uso no requiere, sin embargo, disponer de conocimientos informáticos, pues muchos programas, entre ellos Microsoft Excel, son capaces de grabar macros simples, al mismo tiempo que proporcionan ayuda a la hora de escribirlas. Para el software de procesamiento de hojas de cálculo de Microsoft, las macros son una de las funciones más importantes. Ahora bien, ¿qué son las macros de Excel y por qué son tan útiles? La respuesta a esta pregunta fundamental a continuación.
Qué es una macro de Excel
Microsoft Excel se cuenta entre las soluciones más demandadas a la hora de editar, analizar y presentar datos. Los usuarios de Windows pueden utilizar las populares hojas de cálculo, incluidas en la suite ofimática de Microsoft, para, entre otras cosas, planificar un presupuesto o crear un calendario personal. En el entorno laboral Excel es también una herramienta muy utilizada, pues no solo permite elaborar planes de proyectos, registros de horas de trabajo o planes presupuestarios, sino también e igual de fácilmente, representaciones gráficas de cifras de ventas, de beneficios o de pérdidas. Una vez se ha aprendido a utilizar el programa, se aprecian pronto sus numerosas funciones, aunque también es fácil desarrollar una cierta aversión hacia tareas rutinarias y repetitivas o ante acciones que no se puedan ejecutar fácilmente con ayuda de la interfaz estándar.
No es extraño, por esto, que la posibilidad de crear macros se cuente entre las características cruciales del programa de cálculo. La herramienta integrada en Excel para grabar macros utiliza el lenguaje de scripts Visual Basic for Applications (VBA), que también se implementa en Word, Powerpoint, Access y Outlook. Gracias a este lenguaje es posible crear macros en Excel capaces de ejecutar de forma automática comandos rutinarios o incluso añadir nuevas funciones (algoritmos para el análisis de datos) a la hora de cálculo.
Así funcionan las macros en Excel
Excel dispone los diversos elementos que componen la interfaz de uso como objetos-programa organizados jerárquicamente, cada uno de los cuales posee propiedades y métodos específicos. En este sistema jerárquico, todos los objetos disponibles se encuentran conectados entre sí y se ven reflejados de forma aproximada en la interfaz de usuario, que proporciona los mandos necesarios para poder interactuar con la aplicación. Estos objetos pueden editarse determinando sus propiedades y asignándoles métodos. Por ejemplo, para el objeto “Workbook” existen los métodos “Close”, con cuya ayuda se cierra el libro seleccionado, así como la propiedad “ActiveSheet”, que muestra la hoja activa en el libro de trabajo.
Mediante las listas (objetos señalados con el sufijo plural -s) las macros también pueden ejecutar acciones sobre un grupo de objetos. El objeto para listas “Worksheets” en una macro tiene como resultado que las instrucciones se aplican a todas las hojas de trabajo. Para ejecutar una macro se tienen estas tres opciones:
Seleccionar la opción correspondiente en elmenú de macros
Seleccionar una tecla creada por el usuario
Un atajo de teclado personal
Las ventajas de utilizar macros con Excel
Si, con anterioridad, consciente o inconscientemente, no se ha aprovechado la utilidad de las macros de Excel, no significa esto que haya sido en detrimento del trabajo con archivos de Excel, pero sí que se ha elegido el camino más difícil. Y es que saltan a la vista lasventajas decisivas de la tecnología de macros que las convierten en una tarea obligatoria para todo aquel que quiera sacar el máximo partido al software de hojas de cálculo. Esto es lo que aporta crear macros con Excel:
Reducen la tasa de errores: con cada comando que se introduce manualmente en la hoja de cálculo aumenta la probabilidad de cometer un error y es que, especialmente cuando se trata de secuencias complejas de comandos y de acciones repetitivas, es muy fácil dar un paso en falso que bloquee incluso al programa al completo. Grabando macros, en cambio, solo esconde un potencial de error el propio proceso de su creación, pero si se hizo todo correctamente, la macro funciona siempre a la perfección.
Reducen el tiempo de trabajo: como las macros se crean una sola vez y se utilizan siempre que se necesite, ahorran un tiempo valioso que es posible dedicar a otras tareas.
Aumentan la utilidad de Excel: Con Visual Basic no solo se pueden programar macros, sino también desarrollar nuevas funciones. De este modo el usuario tiene la posibilidad de ampliar el abanico de funciones con aquellas adecuadas a sus necesidades y de simplificar de forma considerable sus fórmulas. Algo muy práctico es que Excel presenta estas funciones definidas por el usuario como si fueran nativas del programa y, más aún, el usuario puede fijar botones de acceso rápido a sus propias macros en la barra de símbolos.
Crear macros en Excel: manual paso a paso
Para crear macros propias hay que activar el editor Visual Basic, que forma parte de las herramientas para desarrolladores y no está, por defecto, disponible en la cinta de opciones (la antigua barra de herramientas). En un primer paso, entonces, se debe añadir la pestaña o ficha denominada Developer (Programador).
Nota: por motivos editoriales se ha seguido la versión en inglés de Microsoft Excel. Entre paréntesis se mostrará siempre la función en castellano.
Primer paso: añadir la ficha Developer a la cinta de opciones
Dirígete a la pestaña File (Archivo) y selecciona Options (Opciones). En el punto Customize the Ribbon (Personalizar la cinta de opciones) se encuentra un listado de todas las pestañas principales o Main Tabs, entre las que se encuentra Developer (Programador):
Para poder grabar macros hay que añadir la pestaña Developer (Programador) a la cinta de opciones
Marca la casilla de Developer y confirma haciendo clic en On.
Segundo paso: crear un libro nuevo para macros
A continuación se crea un libro que sirva de base a las macros registradas. Para ello, haz clic en Macros en la pestaña Developer y cuando aparezca la ventana emergente introduce el nombre de la primera macro –en este ejemplo “Hello”– en Macro Name. Confirma haciendo clic en Create (Crear), a lo que se abSubequivale aquí a subrutina y hace referencia a una macro, que en sí es una especie de programa secundario de Excel. Cuando se inicia una macro, se ejecuta el código que figura entreSubyEnd Sub. Podemos realizar una pequeña prueba ampliando el código de la macro Hello con el mensaje “Hello World!”:
Sub Hello()
MsgBox ("Hello world!")
End Sub
Guardamos el código en formato .xslm (Macro-Enabled Excel Format), cerramos y volvemos a la hoja de Excel. Aquí hacemos clic en Macros y seleccionamos la macro llamada “Hello” de la lista. Para ejecutarla accionamos el botón Run (Ejecutar) y aparece un pequeño cuadro de información con el texto que acabamos de definir:
La primera macro Hello con el mensaje “Hello world!” ha sido creada con éxito
Haciendo clic en OK se cierra la ventana y se finaliza la ejecución de la macro.
Por último, se guarda el libro en el editor Visual Basic.
Tercer paso: crear un botón de acceso rápido para las macros
Las macros de Excel son accesibles para su ejecución tanto desde la pestaña Developer como desde View, pero si se va a usar una macro a menudo, quizá convenga generar un acceso rápido en la lista de símbolos de acceso rápido en la parte superior izquierda. Eso se hace de la siguiente forma:
Abre la pestaña File (Archivo).
Abre las opciones y haz clic en Quick Access Toolbar (Lista de símbolos de acceso rápido)
Selecciona en Choose comands from (Seleccionar comando desde) la opción Macros y busca allí la macro “Hello” que has creado.
Añádela a la lista de símbolos de acceso rápido marcándola y confirmando con Add (Añadir).
Antes de confirmar definitivamente con OK aún puedes determinar el símbolo con el que se mostrará. Para ello selecciona la macro y a continuación haz clic en Modify (Cambiar). Para este ejemplo se ha elegido un smile.
Una vez finalizado, la lista de símbolos, que incluye “Guardar”, “Deshacer” y “Rehacer”, se ha completado con la macro “Hello”:
Se ha creado un símbolo de acceso rápido para la macro Hello en la lista de símbolos de Excel
Nota: también es posible definir un atajo de teclado para la ejecución de macros. Sigue para ello la ruta “Programador>Macros>Opciones” o “Developer>Macros>Options”.
Cuarto paso: grabar una macro
El editor Visual Basic, incluido entre las aplicaciones ofimáticas de Microsoft, brinda la ventaja de que permite crear macros simples sin tener que dominar este lenguaje de programación. Como ejemplo, vamos a crear una macro que cambia el nombre de una hoja automáticamente. Sigue estos pasos:
Selecciona la opción Record Macro (Grabar macro) en la ficha Developer que ya conoces.
En el cuadro de diálogo que aparece a continuación titula la macro como “RenameWorksheets” (renombra hojas de trabajo) e inicia la grabación haciendo clic en OK.
Cambia el nombre de la “Sheet 1” (Hoja 1) por el de “New Name” (Nuevo nombre) y da por finalizada la grabación de la macro haciendo clic en Stop Recording (Finalizar grabación).
Si ahora volvemos al editor Visual Basic(“Programador”>”Macros”>”Editar” o “Developer>Macros>Edit”) debería mostrarse un código similar a este:
En el Editor Visual Basic se muestra el código de las macros para editarlo
Las cuatro primeras líneas de código bajo la línea que comienza con Sub, que empiezan con un apóstrofe, constituyen comentarios que no tienen ninguna influencia en la funcionalidad general de la macro y que solo sirven para una mejor comprensión del código. Estos comentarios también permiten desactivar temporalmente algunas líneas de código. En este ejemplo, concretamente, estas cuatro líneas generadas automáticamente no son necesarias, por lo que podrías borrarlas sin ningún tipo de problema, aunque, si no eres experto, te recomendamos no hacer modificaciones innecesarias en el código.
La siguiente línea contiene el método Select para seleccionar la “Sheet 1” (Hoja 1), paso necesario durante la ejecución manual del cambio de nombreantes del cambio en sí. Sin embargo, los scripts de Visual Basic no necesitan seleccionar objetos para poder editarlos, por lo que esta línea de código también es prescindible. Así que, una vez finalizada su edición, el código definitivo tendría este aspecto:
Sub RenameWorksheets ()
Sheets("Sheet1").Name = "New Name"
End Sub
Ahora, vuelve a Excel y, de nuevo, cambia el nombre de la hoja de trabajo por el de “Sheet 1” (u Hoja1) para poder ejecutar a modo de prueba la macro “RenameWorksheets” recién creada. El nombre debería, así, cambiar automáticamente. Ten en cuenta que para poder ejecutar otra vez la macro hay que ajustar la macro al nombre nuevo.
Con una macro también se puede crear un comando para cambiar el nombre de una hoja automáticamente
Cómo crear diagramas y cuadros de diálogo con macros de Excel
Hemos visto como Visual Basic facilita la mecanización de las más diversas tareas en Excel. Con la ayuda de la grabación de comandos, el usuario no solo dispone de una herramienta muy sencilla con la que simplificar los procesos, sino que también adquiere, de paso, conocimientos sobre este lenguaje de programación. Así que no dudes en usar el editor de código si te parece que creando una macro en Excel podrías agilizar un determinado proceso.
Una vez presentada la creación de macros en general, los siguientes ejemplos ilustran de forma concreta la diversidad de usos posibles de las macros de Excel.
Creación de un diagrama en un área de celdas
Una función muy utilizada de las tablas de Excel es la visualización de los datos como esquemas o gráficos, una tarea algo laboriosa para la cual las macros pueden servir de gran ayuda. Para comenzar, crea una macro con el nombre “AssortedTasks“ y declara la variable para tu objeto:
Dim mygraphic As Chartobject
En la siguiente línea crea un objeto, al cual asignas la variable mygraphic:
Set mygraphic = ActiveSheet.ChartObjects.Add(100, 50,00)
Los valores entre paréntesis corresponden a la posición y al tamaño del diagrama: 100 y 50 son las coordinadas de la esquina superior izquierda, mientras que 200 y 200 corresponden a la anchura y a la altura. Si ejecutamos la macro, Excel genera el objeto automáticamente en función de esta posición y este tamaño, aunque, al no haber introducido aún ningún dato, se muestra aún vacío, así:
La función para crear macros también facilita la creación de gráficos. En esta imagen vemos un primer objeto vacío
En un siguiente paso, la tarea consiste precisamente en nutrir el gráfico con los datos que han de mostrarse visualmente en él, para lo cual es necesario, por un lado, introducir los datos y, por el otro, ajustar nuevamente la macro, al carecer aún de la información sobre el lugar de donde ha de tomar los datos. Siguiendo el esquema With … End With se añade el método SetSourceData y lo especificamos con la variable Selection, que sirve para que la macro tenga en consideración, a la hora de ejecutarse, todas las casillas que han sido marcadas. El código completo resulta así:
Sub AssortedTasks()
Dim mygraphic As ChartObject
Set mygraphic = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)
With mygraphic
.Chart.SetSourceData Source := Selection
End With
End Sub
Si, a continuación, a modo de prueba, introduces los valores del 1 al 5 en las celdas A1-A5 y ejecutas la macro, Excel presenta los datos introducidos como un gráfico de barras por defecto, al no haber proporcionado ninguna información al respecto, que se haría mediante la variable ChartType.
Ejemplo de un gráfico de barras sencillo creado con una macro de Excel
Excel interactivo: crear cuadros de diálogo
Otra posibilidad que se deriva del uso de macros en Excel consiste en la creación de cuadros de diálogo con los cuales los usuarios pueden interactuar con el programa. Si, por ejemplo, se trata de crear una ventana en la cual el valor que se introduzca se escriba automáticamente en una celda definida, comienza creando y nombrando la macro como hasta ahora. En nuestro ejemplo, damos a esta macro el nombre de “DialogBox”. En la subrutina indica a continuación el lugar a donde se destina el valor que introducirán luego los usuarios:
Sub DialogBox()
ActiveSheet.Range("A1").Value = Sub
Con este código, la macro se encarga de que la información que ha introducido el usuario en el cuadro de diálogo se guarde en la celda A1 de la hoja activa (Hoja 1).
La asignación de la variable de este campo o Valuecorresponde a la segunda parte del comando. En el ejemplo nos hemos decantado por el comandoInputBox, muy similar al comando MsgBox que se utilizó en aquella macro “Hello” del principio. Combinado con los tres argumentos siguientes, escritos en Visual Basic entre comillas, resulta el cuadro de diálogo que mostramos a continuación:
Prompt: con ayuda del primer argumento se define el texto que aparece en la ventana y que ha de servir de apoyo al usuario.
Title: con este argumento se determina la cabecera del cuadro de diálogo
Default: define un valor estándar
Este sería un código ejemplar de una macro completa para un cuadro de diálogo con caja y texto:
Sub DialogBox()
Sheet1.Range("A1").Value = InputBox("Please, enter a value for the field A1", "Title of the dialog box", "Value for the field A1")
End Sub
Si ejecutas la macro, aparece la ventana correspondiente:
Ejemplo de cuadro de diálogo creado con una macro de Excel
Importar o exportar macros de Excel
Una vez se han grabado macros en un documento de Excel es muy fácil utilizarlas en otro documento o compartirlas con otros usuarios. Esta es la finalidad de una función para importar y exportarcontenida en el editor Visual Basic y que permite guardar la macro en el formato .bas e integrarla en un documento Excel. La única condición es que las macros también estén activadas en este documento, aunque, debido al riesgo para la seguridad que albergan estos scripts, por otro lado tan prácticos, Excel ejerce ciertas limitaciones estándar para proteger a sus usuarios, de tal forma que puede bloquear aquellas macros con o sin notificación o todas las que carezcan de firma digital. La solución más sencilla pasa por autorizar todas las macros automáticamente, aunque antes de la importación hay que asegurarse de que los códigos son fiables.
Estas opciones para activar o desactivar macros se encuentran en el Trust Center (Centro de seguridad). Para ello se abre la pestaña File, se selecciona Options, aquí Trust Center y, finalmente, Trust Center Settings (Ajustes del Trust Center). Aquí se encuentra el punto Macros Settings (Personalizar macros), que incluye las posibilidades de configuración mencionadas arriba.
En el Trust Center se pueden ajustar las preferencias para las macros
Para exportar una macro, se abre el editor Visual Basic, se hace clic en la ficha File (Archivo) y seguidamente en Export File (Exportar archivo). Ponle un nombre a tu colección de macros, selecciona el directorio y finaliza la exportación con Guardar. Este archivo solo pesa unos pocos bytes, de forma que puede enviarse cómodamente por correo electrónico o guardarse en un dispositivo de almacenamiento portátil. Para importar una macro, comienza de la misma forma para después seleccionar Import File (Importar archivo) e indicar finalmente el directorio de destino.
Comentarios
Publicar un comentario