TABLAS DINÁMICAS

¿Que son?

Las tablas dinámicas en Excel son un tipo de tabla que nos permiten decidir con facilidad los campos que aparecerán como columnas, como filas y como valores de la tabla y podemos hacer modificaciones a dicha definición en el momento que lo deseemos.

Las conocemos como tablas dinámicas porque tú decides “dinámicamente” la estructura de la tabla, es decir sus columnas, filas y valores.

Las tablas dinámicas en Excel también son conocidas como tablas pivote debido a su nombre en inglés: Pivot tables. Son una gran herramienta que nos ayuda a realizar un análisis profundo de nuestros datos ya que podemos filtrar, ordenar y agrupar la información de la tabla dinámica de acuerdo a nuestras necesidades.

Para qué sirven las tablas dinámicas en Excel

Por la descripción que he dado hasta el momento, pareciera que las tablas dinámicas en Excel son una maravilla. El problema es que muchos usuarios de Excel no las utilizan porque parecieran ser complejas, sin embargo una vez que conoces y entiendes su funcionamiento te darás cuenta de todos sus beneficios.

Las tablas dinámicas en Excel nos ayudan a comparar grandes cantidades de datos e intercambiar fácilmente columnas por filas dentro de la misma tabla y realizar filtros que resulten en reportes que de otra manera necesitaríamos un tiempo considerable para construirlos. Considera el siguiente ejemplo.

En una hoja de Excel tengo la información de ventas de diferentes establecimientos de la empresa así como el nombre del vendedor que realizó la venta.

En unos cuantos clics puedo crear una tabla dinámica que muestre las ventas mensuales de cada uno de los establecimientos:

Esta tabla dinámica nos permite saber fácilmente que el mes de marzo tiene el monto de ventas mayor de todo el período. De la misma manera podemos saber que el establecimiento Este es el que tiene al monto de ventas mayor de todo el período.

Ahora bien, con un par de clics adicionales puedo modificar la misma tabla dinámica para conocer las ventas de cada uno de los vendedores:

Fácilmente puedo conocer que el vendedor con mayor monto de ventas en el período es Caro.

Este tipo de información no se puede obtener a simple vista de la tabla original. Fue a través de la tabla dinámica que hemos podido analizar de una mejor manera nuestros datos.

Cuando utilizar tablas dinámicas

Cuando tienes una tabla con datos que deseas analizar desde diferentes puntos de vista, será un indicador para saber que debes utilizar una tabla dinámica.

De igual manera, si tus datos tienen registros que deseas agrupar y totalizar para realizar una comparación entre ellos, será otro indicador de que una tabla dinámica será la mejor opción. Esta agrupación también aplica cuando deseas agrupar por fechas una tabla dinámica ya que podrás hacerlo con facilidad.

Procedimiento

Tabla dinámica recomendada

Crear manualmente una tabla dinámica

Haga clic en una celda del rango de datos o de la tabla de origen. Vaya a Insertar > Tablas > Tabla dinámica recomendada. Excel analiza los datos y le presenta varias opciones, como en este ejemplo con los datos de gastos domésticos. Seleccione la tabla dinámica que mejor le parezca y presione Aceptar. Excel creará una tabla dinámica en otra hoja y mostrará la lista Campos de tabla dinámica.

Haga clic en una celda del rango de datos o de la tabla de origen. Vaya a Insertar > Tablas > Tabla dinámica. Si usa Excel para Mac 2011 y versiones anteriores, el botón Tabla dinámica se encuentra en el grupo Análisis de la pestaña Datos. Excel mostrará el cuadro de diálogo Crear tabla dinámicacon el nombre de tabla o de rango seleccionado. En este caso, utilizamos una tabla denominada "tbl_HouseholdExpenses". En la sección Elija dónde desea colocar el informe de tabla dinámica, seleccione Nueva hoja de cálculo u Hoja de cálculo existente. En el caso de Nueva hoja de cálculo, tendrá que seleccionar la hoja de cálculo y la celda donde quiera colocar la tabla dinámica. Si quiere incluir varios orígenes de datos o de tabla en la tabla dinámica, active la casilla Agregar estos datos al Modelo de datos. Haga clic en Aceptar. Excel creará una tabla dinámica en blanco y mostrará la lista Campos de tabla dinámica.

CODIGO QR

EJEMPLO DE MACRO

EN ESTE LINK ENCONTRARÀS N ARCHIVO DONDE HAY UN EJEMPLO SENCILLO DE MACROS.
www.transfernow.net/79bav671zw25

EXTENSIONES DE ARCHIVOS EXCEL

Tipo de archivo de Excel 2013	Extensión	Descripción
Libro de Excel	.xlsx	Formato de archivo predeterminado de Excel. No puede almacenar código de macro de VBA ni hojas de macro de Microsoft Excel 4.0 (archivos .xlm en Excel 4.0).
Hoja de cálculo Open XML estricta	.xlsx	Versión ISO estricta del formato de archivo Libro de Excel (.xlsx).
Libro habilitado para macros de Excel	.xlsm	Usa el mismo formato XML básico que el formato de libro de Excel, pero puede almacenar código de macro de VBA. Se pedirá a los usuarios que guardan un libro de Excel que tiene código de VBA u hojas de macro de Excel 4.0 (archivos .xlm en Excel 4.0) que usen este formato de archivo.
Plantilla de Excel	.xltx	Formato de archivo predeterminado de una plantilla de Excel. No puede almacenar código de macro de VBA ni hojas de macro de Excel 4.0 (archivos .xlm en Excel 4.0).
Plantilla habilitada para macros de Excel	.xltm	Puede contener una parte VBAProject u hojas de macro de Excel 4.0 (archivos .xlm en Excel 4.0). Los libros creados a partir de esta plantilla heredan la parte VBAProject o las hojas de macro de Excel 4.0 que haya en la plantilla.
Complemento de Excel	.xlam	Programa complementario que ejecuta código adicional. Los complementos de Excel usan el formato de archivo Open XML para almacenar los datos, y permiten usar proyectos de VBA y hojas de macro de Excel 4.0.

PASOS PARA UTILIZAR LA GRABADORA DE MACROS

La grabadora de macros

Puedes crear una macro utilizando el lenguaje de programación VBA, pero el método más sencillo es utilizar la grabadora de macros que guardará todos los pasos realizados para ejecutarlos posteriormente.

La grabadora de macros almacena cada acción que se realiza en Excel, por eso es conveniente planear con antelación los pasos a seguir de manera que no se realicen acciones innecesarias mientras se realiza la grabación. Para utilizar la grabadora de macros debes ir a la ficha Programador y seleccionar el comando Grabar macro.

Al pulsar el botón se mostrará el cuadro de diálogo Grabar macro.

En el cuadro de texto Nombre de la macro deberás colocar el nombre que identificará de manera única a la macro que estamos por crear. De manera opcional puedes asignar un método abreviado de teclado el cual permitirá ejecutar la macro con la combinación de teclas especificadas.

La lista de opciones Guardar macro en permite seleccionar la ubicación donde se almacenará la macro.

• Este libro. Guarda la macro en el libro actual.

• Libro nuevo. La macro se guarda en un libro nuevo y que pueden ser ejecutadas en cualquier libro creado durante la sesión actual de Excel.

• Libro de macros personal. Esta opción permite utilizar la macro en cualquier momento sin importar el libro de Excel que se esté utilizando.

También puedes colocar una Descripción para la macro que vas a crear. Finalmente debes pulsar el botón Aceptar para iniciar con la grabación de la macro. Al terminar de ejecutar las acciones planeadas deberás pulsar el botón Detener grabación para completar la macro.

EJEMPLO

La maravillosa aplicación que utilizan los comerciales, vendedores, etc. nos extrae esta tabla, que como veréis, sólo nos muestra los datos, sin ningún tipo de formato, y esto, por así decirlo: no es presentable a nadie.

Y lo que hacemos mes a mes, es enviarle algo así a nuestro superior:

Más bonito, visual, corrigiendo los formatos de fechas, y añadiendo un promedio de ventas/día en cada vendedor, con un formato condicional para que muestre los semáforos en relación a las ventas entre ellos (Verde: más ventas; Rojo: menos ventas; Ámbar: la media). Para que este promedio salga correctamente, hemos tenido que eliminar los “0” en los fines de semana (días en los que no hay servicio de ventas).

Todas estas acciones, que hacemos mes a mes, realmente no nos comen mucho tiempo, no más de 5 minutos… salvo que nos lo pidan diario o semanal, tengamos varios grupos de vendedores… Estoy seguro de que este pequeño tutorial te será de mucha ayuda para tus tareas.

Vamos con algo de acción.

Localicemos la pestaña de Programador en Excel 2007. Si no la tienes activa, haz clic en este otro enlace para enseñarte a hacerlo en Office 2007 y 2010.

Y ahora, a la izquierda, veremos varias opciones… la que nos interesa: Grabar Macro.

Es cierto que hay mucha gente que programa sus macros para tareas más complejas, pero este tutorial no es para esa gente… es para todo el mundo que empieza con excel o que no tiene muchas tablas en el asunto. La mejor manera de empezar es grabando una Macro.

Antes de grabar la Macro, vamos a preparar un poco nuestra tabla… simplemente vamos a bajar los datos, salvo los títulos, dejando 3 filas vacías encima de los datos de los vendedores. De la siguiente forma:

¿Motivo? Simple… las semanas comienzan en lunes, y el primer día del que tenemos datos es jueves, así que dejamos espacio para esos días. En este mes no nos servirá para nada, pero sí para futuros meses, ya le dejamos espacio. En el futuro, sólo tenemos que pegar los datos empezando en el día que sea necesario.

Ahora sí, vamos con la Macro.

Cuando pulsamos el botón Grabar Macro, nos aparecerá esta ventana:

Vamos a ponerle el nombre. Para esto, Office es un poco especialito, dado que:

• El primer carácter del nombre de la macro debe ser una letra.
• Los demás caracteres pueden ser letras, números o caracteres de subrayado.
• No se permiten espacios en un nombre de macro; puede utilizarse un “_” como separador de palabras.
• No puedes utilizar un nombre de macro que también sea una referencia de celda; de lo contrario puede aparecer un mensaje indicando que el nombre de la macro no es válido.

Llamemos a nuestra macro: Estilo_informe, y le indicaremos que nos lo guarde en “Libro de macros personal”, así lo tendremos disponible en cualquier hoja de Excel que abramos.

Desde el momento en que pulsemos el botón Aceptar, Excel comenzará a grabar todas nuestras acciones, que serán las que programen nuestra macro, así que vayamos con cuidado para no tener que repetir acciones, o borrar la macro.

Sigamos estos pasos:

1. Le damos formato a la fecha, para que se muestre como más te guste (si indicas que te muestre el nombre, podrás identificar rápidamente los fines de semana).
2. Coloreamos la tabla a nuestro gusto. Yo diferencio días de la semana y fin de semana, para ver grupos de días más cómodamente. También le pongo las celdas que no uso en color blanco para darle un aspecto más limpio… pijadas personales.
3. Quitamos los “0” de los fines de semana para que el promedio nos salga correctamente.
4. Añadimos una nueva tablita, debajo de la grande, que sea resumen del número de ventas y el promedio de ventas/día de cada vendedor. A la derecha de la tablita añadimos los totales. Como el mes que hemos escogido es de 31 días, en el futuro no tendremos que mover la tabla, dado que no habrá meses de más de 31 días.
5. Coloreamos esta tablita en el estilo de la tabla superior.

Hemos terminado, ahora le damos al botón “Detener Macro”, y listo.

Ahora, cada vez que queramos dar forma a nuestra tabla extraída del programa de los vendedores, lo único que tendremos que hacer será “dejar X filas vacías encima de los datos” en relación al día de la semana con que empiece el mes, y “llamar” a nuestra macro para que haga su trabajo. Nos vamos a la Pestaña Programador y le damos al botón de Macros, y escojemos la nuestra de la lista. Puede que tengas que desplegar el menú de “Macros en:”.

Si nos damos cuenta, lo único que hemos hecho a mayores de la preparación de nuestro informe ha sido grabar una Macro que ha guardado todas las acciones que hemos hecho.

Cada uno le encontrará una utilidad diferente, y programará una macro diferente dependiendo de sus necesidades, pero desde luego todos coincidirán en que es una herramienta imprescindible para el ahorro de tiempo en las tareas más rutinarias.

• Podemos programar macros para eliminar contenido en un clic.
  • Por ejemplo, si el programa de vendedores extrae las tablas perfectas, podemos simplemente eliminar los “0” y añadir nuestra tabla resumen.
• Podemos programar macros para dar un formato determinado.
• Podemos programar macros para que nos creen gráficos obteniendo datos de un rango determinado.
• Podemos asignar una macro a un botón o una imagen para no tener que buscarla en la Pestaña Programador.
• Podemos…

Podemos hacer lo que todos queremos hacer… hacer más en menos tiempo, e invertir el restante en cosas más placenteras o productivas… al fin y al cabo, la procrastinación está ahí para hacer uso de ella de vez en cuando, no va a ser todo trabajar o estudiar, no?

LENGUAJE VBA

Visual Basic for Applications

Microsoft VBA (Visual Basic para aplicaciones) es el lenguaje de macros de Microsoft Visual Basic que se utiliza para programar aplicaciones Windows y que se incluye en varias aplicaciones Microsoft. VBA permite a usuarios y programadores ampliar la funcionalidad de programas de la suite Microsoft Office. Visual Basic para Aplicaciones es un subconjunto casi completo de Visual Basic 5.0 y 6.0.

Microsoft VBA viene integrado en aplicaciones de Microsoft Office, como Outlook, Word, Excel, Access y Powerpoint. Prácticamente cualquier cosa que se pueda programar en Visual Basic 5.0 o 6.0 se puede hacer también dentro de un documento de Office, con la sola limitación que el producto final no se puede compilar separadamente del documento, hoja o base de datos en que fue creado; es decir, se convierte en una macro (o más bien súper macro). Esta macro puede instalarse o distribuirse con sólo copiar el documento, presentación o base de datos.

Su utilidad principal es automatizar tareas cotidianas, así como crear aplicaciones y servicios de bases de datos para el escritorio. Permite acceder a las funcionalidades de un lenguaje orientado a eventos con acceso a la API de Windows.

Al provenir de un lenguaje basado en Basic tiene similitudes con lenguajes incluidos en otros productos de ofimática como StarBasic y Openoffice.

Sub LoopTableExample

    Dim db As DAO.Database
    Dim rcs As DAO.Recordset

    Set db = CurrentDb
    Set rcs = db.OpenRecordset("SELECT * FROM tblMain")

    Do Until rcs.EOF
        MsgBox rcs!FieldName
        rcs.MoveNext
    Loop

    rcs.Close
    db.Close
    Set rcs = Nothing
    Set db = Nothing
End Sub

VBA puede ser usado para crear una función definida por el usuario para usar en una hoja de Microsoft Excel:

Public Function BUSINESSDAYPRIOR(dt As Date) As Date

    Select Case Weekday(dt, vbMonday)
    Case 1
        BUSINESSDAYPRIOR = dt -3
    Case 7
        BUSINESSDAYPRIOR = dt -2
    Case Else
        BUSINESSDAYPRIOR = dt -1
    End Select
End Function

VBA también tiene acceso a funciones internas de Windows en diversos grados, y puede acceder recursos desde horarios hasta archivos y control:

Sub ObtenerFecha()

    MsgBox "La fecha es " & Format(Now(), "dd-mm-yyyy")


End Sub

Se puede acceder al lenguaje al ingresar al menú herramientas. Y una vez allí MACRO y EDITOR DE VISUAL BASIC.

FUTURO

El siguiente paso natural en la evolución de VBA es dejar de ser un subconjunto de Visual Basic y serlo de la plataforma .NET. Microsoft no planea hacer mejoras significativas a VBA en el futuro. Aunque continuará dando soporte a las licencias de VBA que se han ido ofreciendo, VBA está siendo sustituido por las Herramientas para Aplicaciones de Microsoft Visual Studio (VSTA: Visual Studio Tools for Applications) y las Herramientas para Office de Microsoft Visual Studio (VSTO: Visual Studio Tools for Office). Estas herramientas funcionan bajo la plataforma .NET. Desde el 1 de julio de 2007, Microsoft ya no ofrece nuevas licencias de VBA a nuevos clientes. Los que poseían una licencia de VBA podrán conseguir una licencia de las nuevas soluciones por parte de Microsoft.

MACRO EN EXCEL

MACROS!

¿Què es una macro? 

Una macro es un conjunto de comandos que se almacena en un lugar especial de Excel de manera que están siempre disponibles cuando los necesites ejecutar.
Por ejemplo, si todas las mañanas creas un reporte de ventas y en ese reporte siempre das el mismo formato a los textos, se podría crear una macro para que lo haga automáticamente por ti. Las macros se utilizan principalmente para eliminar la necesidad de repetir los pasos de aquellas tareas que realizas una y otra vez.

Un lenguaje de programación

Las macros se escriben en un lenguaje de computadora especial que es conocido como Visual Basic for Applications (VBA). Este lenguaje permite acceder a prácticamente todas las funcionalidades de Excel y con ello también ampliar la funcionalidad del programa.

Pero no te preocupes si no eres un programador de computadoras, Excel provee de una herramienta especial que permite crear una macro sin necesidad de conocer los detalles del lenguaje de programación.

Caracterìsticas de las macros.

1. Es un comando integrado a Excel que te permite Grabar todas las acciones ejecutadas en Excel.
2. Forman parte del Software integrado a Excel llamado (VBA) Visual Basic para Aplicaciones.
3. Es la forma más fácil de aprender a Programar, ya que solo tienes que grabar las acciones para conocer sus códigos.
4. Solo graban acciones de procesos especiales ejemplo (Editar, Copiar, Pegar, etc.).

Asignar una macro a un botón Formulario o Control

Se pueden utilizar un botón (un control de formulario) o un botón de comando (un control ActiveX) para ejecutar una macro que realiza una acción cuando un usuario hace clic en él.

Se conocen como botón de comando tanto el botón de control de formulario como el botón de comando de control ActiveX. Es posible utilizar un botón o un botón de comando para automatizar la impresión de una hoja de cálculo, el filtrado de filtrar datos o el cálculo de cifras. En general, el botón de control de formulario y el botón de comando de control ActiveX tienen una apariencia y una función similares. Sin embargo, hay algunas diferencias que se explicarán en las secciones siguientes.

Botón (control de formulario)

Botón de comando (control ActiveX)

Encontrará las macros y las herramientas VBA en la pestaña Desarrollador, que está oculta de forma predeterminada, por lo que el primer paso consiste en habilitarla.

Siga estos pasos:

• En Excel 2016, Excel 2013, y Excel 2010

  1. Vaya a Archivo > Opciones >Personalizar cinta de opciones.
  2. En la categoría Personalizar cinta, en la lista Pestañas principales, active la casilla Programador y, a continuación, haga clic en Aceptar.
     
• En Excel 2007
  1. Haga clic en el botón de Microsoft Office  y, a continuación, haga clic en Opciones de Excel.
     
  2. En la categoría Más frecuentes, bajo Opciones principales para trabajar con Excel, active la casilla de verificación Mostrar ficha Programador en la cinta de opciones y, a continuación, haga clic en Aceptar.
     

Agregar un botón (control de formulario)

1. En la ficha Programador, en el grupo Controles, haga clic en Insertar y después, en Controles de formulario, haga clic en Botón  .
   
2. Haga clic en la ubicación de la hoja de cálculo en la que desee que aparezca la esquina superior izquierda del botón.
   Aparecerá el cuadro de diálogo Asignar Macro.
3. Asigne una macro al botón y, a continuación, haga clic en Aceptar.
4. Para especificar las propiedades de control del botón, haga clic con el botón secundario en el botón y, a continuación, haga clic en Formato de control.

Agregar un botón de comando (control ActiveX)

1. En la ficha Programador, en el grupo Controles, haga clic en Insertar y después, en Controles ActiveX, haga clic en Botón de comando  .
   
2. Haga clic en la ubicación de la hoja de cálculo en la que desee que aparezca la esquina superior izquierda del botón de comando.
3. En el grupo Controles, haga clic en Ver código.
   Se iniciará el Editor de Visual Basic. Compruebe que esté seleccionado Clic en la lista desplegable de la derecha. El procedimiento Sub llamado CommandButton1_Click, como se muestra en la imagen siguiente, ejecuta dos macros cuando se hace clic en el botón: SelectC15 y HelloMessage.
   
   
   
4. En el subprocedimiento del botón de comandos, realice una de las operaciones siguientes:
   • Escriba el nombre de una macro existente en la hoja de cálculo. Puede encontrar las macros haciendo clic en Macros en el grupo Código. Puede ejecutar varias macros con un botón escribiendo los nombres de las macros en líneas diferentes dentro del subprocedimiento.
   • Escriba su propio código VBA.
5. Cierre el Editor de Visual Basic y haga clic en Modo de diseño  para asegurarse de que el modo de diseño está desactivado.
6. Para ejecutar el código de VBA que se ha adjuntado al botón, haga clic en el botón de comandos de ActiveX que acaba de crear.
7. Para modificar el control ActiveX, asegúrese de que está en el modo de diseño. En la ficha Programador, en el grupo Controles, active Modo Diseño .
8. Para especificar las propiedades de control del botón de comando, en la pestaña Desarrollador, en el grupo Controles, haga clic en Propiedades  . También puede hacer clic con el botón derecho en el botón de comando y, después, hacer clic en Propiedades.
   Nota: Antes de hacer clic en Propiedades, asegúrese de que el objeto cuyas propiedades desea examinar o modificar está seleccionado.
   Aparece el cuadro de diálogo Propiedades. Para obtener información detallada acerca de cada propiedad, seleccione la propiedad y, a continuación, presione F1 para ver un tema de la Ayuda de Visual Basic. También puede escribir el nombre de la propiedad en el cuadro Buscar de la Ayuda de Visual Basic. La siguiente tabla resume las propiedades disponibles.

Si desea especificar	Use esta propiedad
General:
Si el control se carga al abrir el libro. (excepto para controles ActiveX)	AutoLoad (Excel)
Si el control puede recibir el foco y responder a eventos generados por el usuario.	Enabled (formulario)
Si se puede modificar el control.	Locked (formulario)
El nombre del control.	Name (formulario)
La manera en que el control está unido a las celdas que están debajo de él (libre flotante, mover sin cambiar el tamaño o mover y cambiar el tamaño).	Placement (Excel)
Si se puede imprimir el control.	PrintObject (Excel)
Si el control está visible u oculto.	Visible (formulario)
Texto:
Atributos de fuente (negrita, cursiva, tamaño, tachado, subrayado y grosor).	Bold, Italic, Size, StrikeThrough, Underline, Weight (formulario)
Texto descriptivo sobre el control que lo identifica o lo describe.	Caption (formulario)
Si el contenido del control se ajusta automáticamente al final de una línea.	WordWrap (formulario)
Tamaño y posición:
Si el tamaño del control se ajusta automáticamente para mostrar todo el contenido.	AutoSize (formulario)
El alto o ancho en puntos.	Height, Width (formulario)
La distancia entre el control y el borde izquierdo o el superior de la hoja de cálculo.	Left, Top (formulario)
Formato:
El color de fondo.	BackColor (formulario)
El estilo de fondo (transparente u opaco).	BackStyle (formulario)
El color de primer plano.	ForeColor (formulario)
Si el control tiene una sombra.	Shadow (Excel)
Imagen:
El mapa de bits que se muestra en el control.	Picture (formulario)
La ubicación de la imagen en relación con su título (izquierda, superior, derecha, etc.).	PicturePosition (formulario)
Teclado y mouse:
La tecla de método abreviado para el control.	Accelerator (formulario)
Un icono del mouse personalizado.	MouseIcon (formulario)
El tipo de puntero que se muestra cuando el usuario sitúa el mouse sobre un objeto determinado (por ejemplo: estándar, flecha o en forma de I).	MousePointer (formulario)
Si el control recibe el foco cuando se hace clic en él.	TakeFocusOnClick (formulario)