Agregar pestañas personalizadas a la cinta de opciones (Excel 2007 - 2013)

Supongamos que hemos desarrollado un modelo en Excel (2007 en adelante) que contiene macros. Para facilitar el uso queremos agregar una pestaña (ficha) en la cinta de opciones para activar estas macros.

En este ejemplo, la ficha "Mis Macros" contiene tres iconos. Cada uno de ellos activa una macro ("Saludo" saluda al usuario con un "buen día" o "buenas tardes" de acuerdo a la hora del día; "Fecha" muestra la fecha corriente y "Hora" la hora).
Desde la versión 2007 de Excel en adelante no tenemos la posibilidad de programar la cinta de opciones con Vba, tal como podíamos hacer en las versiones anteriores donde podíamos crear barras de herramientas o agregar un nuevo elemento en la barra del menú de Excel programáticamente.

Si bien podemos agregar pestañas (fichas) en la cinta de opciones usando el menú Opciones-Personalizar Cinta de Opciones, agregar iconos a la ficha y ligar macros a los iconos, esta ficha es permanente, es decir, aparecerá en todo cuaderno de Excel que abramos. Lo mismo con los iconos que agreguemos en la barra de heramientas de accedso rápido (QAT).

Sin embargo podemos modificar la cinta de opciones dinámicamente, es decir, ligar la cinta modificada a un cuaderno determinado de manera que cuando cerramos el cuaderno las modificaciones desaparecen. Gracias a los excelente tutoriales de MVP Ron de Bruin la tarea es relativamente sencilla y no requiere dominio de detalles técnicos.

En este post mostraré los pasos para crear la pestaña para activar macros del ejemplo.

La forma más conveniente de editar la cinta de opciones es utilizando la herramienta Custom UI Editor que es gratuita y puede descargarse aquí.

Empezamos por crear el cuaderno que contiene las macros (el cuaderno con los códigos puede descargarse aquí). Una vez creado, guardamos y cerramos el cuaderno.

El próximo paso es activar el Custom UI Editor, abrir el archivo Excel que hemos creado (File - Open, en el editor) y elegir una de las dos opciones Office 2010 u Office 2007 en el menú Insert

Si el cuaderno será usado solamente con Excel 2010-2013 elegimos la opción Office 2010 Custom UI Part; para archivos que serán usados en Excel 2007-2013 usamos la opción Office 2007 Custom UI Part.
En nuestro ejemplo usaremos la segunda opción. En el editor pegamos este código

<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
  <ribbon >
    <tabs >
      <tab id="DbMacros" insertBeforeMso="TabInsert" label="Mis Macros">

        <group id="DbGroup01" imageMso="ReviewEditComment" label="Macros">
          <button id="DbG01B01" label="Saludo" imageMso="HappyFace" size="large"  onAction="Saludos" />
          <button id="DbG01B02" label="Fecha" imageMso="HeaderFooterCurrentDate" size="large"  onAction="la_fecha" />
          <button id="DbG01B03" label="Hora" imageMso="SlideShowRehearseTimings" size="large"  onAction="la_hora" />
        </group >


      </tab >
    </tabs >
  </ribbon >
</customUI >

Un archivo texto con el código puede descargarse aquí.

En la ventana del Custom UI Editor, este código se vera así

Analicemos las distintas partes del código XML que define la pestaña que agregamos:

<tab id="DbMacros" insertBeforeMso="TabInsert" label="Mis Macros">

En esta orden definimos un identificador, definimos dónde debe aparecer la pestaña en la cinta (a la izquierda de la pestaña Insertar) y la descripción (label).

<group id="DbGroup01" imageMso="ReviewEditComment" label="Macros">
          <button id="DbG01B01" label="Saludo" imageMso="HappyFace" size="large"  onAction="Saludos" />
          <button id="DbG01B02" label="Fecha" imageMso="HeaderFooterCurrentDate" size="large"  onAction="la_fecha" />
          <button id="DbG01B03" label="Hora" imageMso="SlideShowRehearseTimings" size="large"  onAction="la_hora" />
        </group >

Definimos el grupo y los botones que aparecerán dentro del grupo. Para cada botón definimos el identificador, la etiqueta, la imagen, el tamaño y con onAction la macro que será activada al pulsar el icono.

Las imágenes de los iconos están definidas por el valor de imageMso. Una lista de los iconos con sus nombre puede verse en esta página.

Después de introducir el código xml en el Custom UI Editor lo guardamos con el menú Save As

A partir de este momento cada vez que abramos el cuaderno la pestaña con el grupo de iconos "Mis Macros" aparecerá en la cinta de opciones.

El archivo con las macros puede descargarse aquí.

Como contar registros únicos en tablas dinámicas

Eduardo, colega de trabajo, es inteligente, aplicado y no le gusta depender del departamento de informática. Por eso, maneja una serie de pequeñas bases de datos en Excel (para el horror del departamento de IT ya mencionado). Para generar sus reportes usa principalmente tablas dinámicas de las cuales, para ponerlo de alguna manera, está perdídamente enamorado. A tal punto que todos mis intentos de mostrarle las bondades de otras herramientas como, por poner un ejemplo, Filtro Avanzado, siempre fracasan en forma rotunda.
Por eso cuando Eduardo entró ayer en mi oficina, sólo por la mirada, me di cuenta que algo le estaba pasando. No era una visita de cortesía.

- ¿Cómo hago para saber cuántos clientes tengo en mi base de datos?
 - Dado que todo lo hacés con tablas dinámicas, te sugiero que arrastres el campo de clientes al área de los datos usando la función Contar para totalizar.
- Si, es lo que hice; pero cada cliente aparece en más de una fila en la base de datos y entonces me cuenta la cantidad de veces que cada cliente aparece, no la cantidad de clientes.
- Por supuesto, así funcionan las tablas dinámicas.
- Pero, ¿cómo, no hay una función para contar registros únicos?
- Si y no...
- Uf, ya empezaste. ¿Si o no?
- En Excel 2013 hay una función para contar registros únicos en un reporte dinámico; en Excel 2010 y anteriores, no.
- ¡Ah! Yo uso Excel 2010, ¿cómo hago?
- Creando un campo auxiliar en la base de datos.

Supongamos que nuestra base de datos es la tabla de facturas de la base de datos Northwind

Para contar cuántos clientes hay en la base de datos creamos una tabla dinámica arrastrando los campos País y Cliente al área de las filas y nuevamente el campo Cliente al área de datos usando la función Contar (dado que el campo Cliente no contiene valores numéricos, Excel usará esta función en forma automática)

Inmediatamente podemos ver que en Argentina hay 3 clientes, pero la tabla dinámica muestra 11.
En las versiones de Excel anteriores a Excel 2013, tenemos que usar una columna auxiliar.
Insertamos la columna Auxiliar entre los campos Cliente y Dirección; en esta columan ponemos la fórmula =CONTAR.SI($B$3:B3,B3) que copiamos a todas las filas

El campo Auxiliar muestra el número de orden de aparación de cada cliente. Ahora podemos usar este campo como filtro de la tabla dinámica para que muestre sólo los registro donde el valor de Auxiliar es 1

Ahora podemos ver que la cuenta es correcta

Con Excel 2013, las cosas son más sencillas. No necesitamos crear ningún campo auxiliar. Sencillamente creamos la tabla dinámicas a partir de la base de datos. En el asistente de tablas dinámicas nos aseguramos de marcar la opción "Agregar estos datos al Modelo de datos" (esta opción sólo existe en Excel 2013)

Una vez creada la tabla, arrastramos el campo País al área de filas y el campo Cliente al área de los datos; seleccionamos el área de datos y abrimos el menú de configuración del campo. En la casilla de elección del tipo de cálculo tenemos una nueva función: "Recuento distinto"

Elegimos esta función y apretamos aceptar

Podemos ver que el encabezamiento del campo ha cambiado de "Recuento de cliente" a "Recuento distinto de Cliente" y que efectivamente tenemos 84 clientes en la base de datos.

Personalmente pienso que la traducción tendría que haber sido "Recuento único".

Reportes dinámicos con segmentación de datos en Tablas Excel

¿Se acuerdan de la funcionalidad Segmentación de Datos? Microsoft introdujo en Excel 2010 esta funcionalidad para las tablas dinámicas. La herramienta permite filtrar reportes de tablas dinámicas con ciertas ventajas sobre el filtro tradicional.

En Excel 2013 también las Tablas cuentan con esta herramienta. Esto abre la posibilidad de crear reportes dinámicos que pueden ser muy útiles en presentaciones de datos. Veamos este reporte hecho con esta herramienta

Como pueden ver en la animación, la tabla oculta todas las filas no relevantes a la elección que efectuamos en los cuadros de segmentación. La ventaja de este método sobre el tradicional Autofiltro, es que vemos las elecciones que hemos efectuado. Además cuando elegimos un valor los cuadros de segmentación oscurecen todos los valores que dejan de ser relevantes.

La mejor parte de todo esto es que es muy fácil crear estos reportes.

Empezamos por un rango que contiene datos de ventas, que hemos convertido en Tabla.

Nuestra tabla tiene fechas por día, pero en nuestro informe queremos seleccionar por años y meses. Las Tablas no tienen la funcionalidad Agrupar de las tablas dinámicas, así que tendremos que dar un rodeo. A la derecha de la columna "Enviado" agregamos las columnas "Año" y "Mes". Los valores los generamos con las funciones AÑO y MES, obviamente

Ahora tenemos que hacer un poco de espacio por encima y a la izquierda de la tabla para poder ubicar los cuadros de segmentación de datos. Al seleccionar alguna celda de la tabla, se hace visible el menú Herramientas de Tabla donde activamos la segmentación de datos

Elegimos los campos con los que queremos filtrar nuestro informe y apretamos Aceptar. Excel crea los cuadros

Nuestra próxima tarea es cambiar el diseño de los cuadros de segmentación. Para hacer esto seleccionamos un cuadro de segmentación para hacer visible el menú de Herramientas de Segmentación

Elegimos un estilo de la galería de estilos y lo aplicamos a los cuadros. Si ninguno de los estilos nos satisface, podemos duplicar y modificar alguno de los estilos existentes

Otra herramienta importante en el menú es el grupo de comandos Botones. Aquí podemos definir el número de columna que tendrá el cuadro (por defecto tiene una sola), el ancho y el alto

Finalmente ocultamos las columnas Mes y Año, ubicamos los cuadros en las posiciones que nos parezcan convenientes y quitamos las líneas de cuadrícula de la hoja (Vista-Mostrar)

El resultado final

El archivo se puede descargar aquí.

Otra técnica para "aplanar" tablas de datos

Hace un poco más de dos años atrás publiqué una nota sobre cómo crear una tabla sumario en Excel. La idea es "aplanar" una matriz de datos de manera que pueda ser usada como base de datos para tablas dinámicas.

¿Qué significa aplanar en este caso? Veamos este ejemplo de un cuadro de ventas de productos por meses

Para poder aprovechar eficientemente las posibilidades de las tablas dinámicas, tendríamos que organizar estos mismos datos de esta manera:

Si usamos Excel 2010 o 2013 podemos usar, además de la técnica que mostré en la nota anterior, la nueva herramienta Power Query (anteriormente, Data Explorer).

El primer paso consiste en descargar e instalar el complemento.

Luego convertimos nuestra matriz de datos en Tabla

Seleccionamos la tabla y en el menú de Power Query usamos la opción "From Table"

para introducirla en la ventana de Power Query

Ahora viene la parte menos evidente del proceso (esperemos que Microsoft lo mejore en el futuro): en la ventana del Query seleccionamos las columnas (clic al encabezamiento de la columna manteniendo el botón Ctrl apretado) y abrimos el menú contextual con un clic del botón derecho; en el menú hacemos un clic en la opción "Unpivot Columns"

con este resultado

Ahora apretamos el botón "Done" en el editor del Query. Excel crea una nueva hoja con el resultado de Query

Todo los que nos queda por hacer es reemplazar los encabezamientos de las columnas que el Query creó ("Mes" en lugar de "Attribute" y "Ventas" en lugar de "Value", en nuestro ejemplo).

La gran ventaja de este método sobre el anterior, es que podemos actualizar el Query en caso de hacer cambios en la tabla de origen. Por ejemplo, si agregamos una línea para el nuevo Producto 11 a la tabla de datos, todo lo que tenemos que hacer es usar la opción "Refresh" en la opción Query de la cinta de opciones

El único inconveniente es que debemos volver a reemplazar los encabezamientos ya que Excel vuelve a poner los valores por defecto (Attribute y Value).

Si agregamos una nueva columna a la tabla, digamos el mes de Mayo, tendremos que rehacer el Query, ya que en este caso la nueva columna aparecerá como tal también en el Query (no ha sido incluida en el proceso Unpivot Column).

Como dejé (casi) de usar BUSCARV, también con Excel 2010

En la nota Como casi dejé usar VLOOKUP mostré como crear reportes dinámicos a partir de dos o más matrices de datos usando la nueva funcionalidad de Excel 2013 Relaciones (Datos-Herramientas de Datos-Relaciones).

Esta funcionalidad no existe en forma nativa en Excel 2010, pero podemos agregarla instalando el complemento PowerPivot (en la nota del enlace se señalan todos los requisitos para la instalación).
No me voy a extender aquí sobre las bondades del PowerPivot (lo que muestro a continuación es la puntita de la punta del iceberg), pero a todos mis lectores que usen Excel 2010 o Excel 2013 les recomiendo ver este tutorial (incluye la descarga del archivo de datos para las prácticas).

Volviendo a nuestro tema, veamos cómo usamos el PowerPivot para crear un reporte dinámico con más de una fuente de datos.

Una vez instalado el complemento, veremos una nueva pestaña en la cinta

Como en el ejemplo de la nota anterior, nuestro cuaderno incluye dos hojas:

Ventas: las ventas de los productos (fecha, cliente, producto y suma)

• Producto: la categoría de cada producto (producto, categoría).
• Nuestro objetivo es crear un reporte de ventas por categoría.

Seleccionamos una celda de una de las tablas del cuaderno y en la pestaña del PowePivot apretamos "Datos de Excel-Crear Tabla Vinculada"

Excel abre la ventana del PowerPivot donde podemos ver que ha agregado la tabla seleccionada.

Hacemos lo mismo con la segunda tabla. Ahora ambas tablas aparecen en la ventana del PowerPivot

Ahora tenemos que crear una relación entre ambas tablas. El campo en común aquí es Producto. Una de las formas de hacerlo tenemos que cambiar la vista de la ventana a "Vista de diagrama"

Con el mouse conectamos el campo Producto de la tabla Categorías (donde los productos son valores únicos) con el campo Producto de la tabla de ventas

Otra forma de hacerlo es usando "Crear relaciones" en la pestaña "Diseñar"

Una vez definidas las relaciones, abrimos la pestaña Inicio del PowerPivot y usamos Tabla dinámica para general el informe

Excel genera una tabla dinámica a partir de ambas tablas como podemos ver en la ventana de la lista de campos

Como (casi) deje de usar VLOOKUP (usando Datos-Relaciones)

Excel 2013 introdujo muchas innovaciones que, más aún que en las versiones anteriores, lo convierten en la herramienta ideal para los analistas de datos. Algunas, como PowerPivot, ya se han ganado su fama. Otras han pasado desapercibidas, como la nueva funcionalidad Datos-Relaciones

Una de las situaciones más corrientes cuando analizamos datos es la necesidad de unificar en una única tabla datos que se encuentran en dos o más tablas. Esto es necesario, en particular, cuando queremos analizar los datos con tablas dinámicas.

Veamos este ejemplo: en una hoja tenemos una tabla con los datos de ventas

y en otra una tabla con las categorías de los productos (madera, electricidad, etc.)

Para crear reportes con tablas dinámicas lo que haríamos con las versiones anteriores de Excel es usar VLOOKUP para combinar las categorías en la tabla de ventas. De esta manera podemos crear un informa de ventas por categorías y períodos, categorías y clientes, etc.

Con la nueva funcionalidad podemos combinar los datos de ambas tablas como en una consulta (query) en Access, sin necesidad de cargar nuestro modelo con miles de fórmulas VLOOKUP.

El primer paso es convertir las listas de datos en Tablas (Insertar-Tabla). Para facilitar el trabajo posterior cambiamos el nombre por defecto (Tabla1) por algo más significativo (tblVentas)

Hacemos lo mismo con la tabla de las categorías (tblCategorias)

El segundo paso es crear las relaciones entre las tablas. En la cinta activamos Datos-Herramientas de Datos-Relaciones y apretamos la opción Nuevo. En el formulario "Crear relación" definimos el campo (columna) común a ambas tabla (en nuestro ejemplo Producto)

Apretamos "Aceptar" y "Cerrar".

Para crear la tabla dinámica con los datos combinados seleccionamos alguna de las tablas y creamos la tabla dinámica (Insertar-Tabla dinámica)

En el formulario que se abre marcamos la opción "Agregar estos datos al modelo de datos"

Excel abre una nueva hoja con la plantilla de la tabla dinámica

En el área de definiciones "Campo de tabla…" activamos la opción "Todos". Esto nos permite ver todos los campos de ambas tablas y usarlas en nuestro informe dinámico.
Ahora podemos crear el informe Ventas por categorías

Nueva versión de Excel - Excel 2013

Supongo que para muchos de mis lectores éstas son noticias de ayer: Microsoft ha puesto a disposición de los usuarios la nueva versión del paquete Office, el Office 365.

Como es de esperar el paquete incluye la nueva versión de Excel – Excel 2013. Microsoft ofrece también la posibilidad de descargar el paquete en sus distintas configuraciones (tomar en cuenta que se requiere Windows 7 o superior).

Para descargar el paquete hay que abrir una cuenta en Office 365.

No he probado aun la nueva versión, lo que iré haciendo en los momentos libres en las próximas semanas.