Quetzalli VR: 2019

TABLAS DINÁMICAS

son útiles para analizar los datos almacenados en Excel. Las tablas dinámicas son una herramienta de Excel que nos sirve para analizar los datos de una hoja de cálculo en Excel. Esta herramienta nos da la posibilidad de agrupar de diferentes formas la información para obtener una visión general de los datos que necesitamos.

Resultado de imagen para tablas dinamicas y macros excel

FUNCIONAMIENTO DE LAS TABLAS DINÁMICAS

Veremos el funcionamiento de las tablas dinámicas con un ejemplo. Supongamos que necesitamos un reporte que nos muestre el total de ventas por ciudad y por categoría

Primero debemos indicar los datos que queremos que compare nuestra tabla dinámica:

Arrastraremos el valor de Ciudad al campo de filas, Excel nos colocará los diferentes valores de Ciudad en las filas eliminando los datos repetidos.

Arrastraremos el valor Categoría al campo de columnas, Excel nos colocará los diferentes valores de Categoría en las columnas eliminando los datos repetidos.Por último arrastraremos las Ventas al campo de valores.

Excel sumará estos valores para cada elemento de fila y ciudad. También nos creará una fila final en la que hace un sumatorio de todos los valores de las columnas y una columna final en la calcula el sumario para cada una de las filas.

También podemos crear filtros, la función de este campo es restringir la información que vemos en nuestra tabla

FILTROS EN LAS TABLAS DINÁMICA

Podemos utilizar filtros en nuestra tabla dinámica. Por defecto Excel coloca estos filtros en las etiquetas de fila y etiquetas de columnas. Con estos filtros podemos ordenar y seleccionar la información que queremos mostrar en nuestra tabla.Si queremos filtrar los datos según campos de tiempo, por ejemplo las ventas de un periodo de tiempo concreto, podemos utilizar las escalas de tiempo. Puedes ver paso a paso cómo utilizar escalas de tiempo en el post Agrupar fechas con escalas de tiempo.

MODIFICAR EL TIPO DE CÁLCULO QUE MUESTRA

Otra opción disponible es la modificación del tipo de cálculo que se realiza en una tabla dinámica. Para ello debemos colocarnos sobre el campo de valor, hacer clic con el botón izquierdo y seleccionar “Configuración de campo de valor”. Nos aparece una ventana con dos pestañas, en la pestaña “Resumir valores por” Excel por defecto calcula la suma de los campos, pero podríamos elegir otras opciones como Promedio, Máximo, Cuenta…

En la pestaña “Mostrar valores como” podemos modificar la forma en la que Excel nos muestra los datos. Podemos elegir que nos muestre los datos como un porcentaje sobre el total general, % sobre el total de columnas…etc.

Si hacemos clic en el botón “Formato de número” podemos darle a las cantidades mostradas el formato deseado.

CAMBIAR EL DISEÑO DE LA TABLA DINÁMICA

Por último, una vez creada la tabla dinámica podemos darle formato utilizando la ficha Diseño. Podemos elegir cualquiera de los estilos de tabla prediseñados y aplicarlo a nuestra tabla dinámica.

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.

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 OK. Ahora la pestaña ya se encuentra en la cinta de opciones.Vista de la cinta de opciones con la pestaña Developer

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 abre el editor Visual Basic con el siguiente código:Antes de grabar macros se ha de crear un libro para macros donde guardarlas

Sub equivale 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 entre Sub y End Sub. Podemos realizar una pequeña prueba ampliando el código de la macro Hello con el mensaje “Hello World!”:

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 nombre antes 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 RenameWor 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

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: