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".

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

Análisis de encuestas con Formato Condicional

Las mejoras e innovaciones introducidas  han convertido a Formato Condicional en el nuevo Excel (2007/2010) en una aun más excelente herramienta para analizar datos. En esta nota mostraré como usar Formato Condicional para resumir en forma eficiente y sencilla los resultados de una encuesta.

Hace unas semanas recibí una consulta sobre como resumir concisamente una encuesta sobre el nivel de servicio de ciertos departamentos de una empresa. La encuesta fue montada usando la herramienta Formularios de la aplicación Documentos de Google.

Google va acumulando los resultados en una hoja de cálculo que puede descargarse a Excel. Los encuestados debían calificar en una escala de 0 (pésimo) a 5 (excelente) el funcionamiento de ciertos departamentos de servicios de la empresa.

Veremos como podemos crear una “termo-mapa” para mostrar en forma concisa y práctica los resultados de la encuesta.

Empezamos por convertir la matriz de los resultados en una tabla (Insertar-Tabla)

El próximo paso es crear una tabla dinámica, que ubicamos en una nueva hoja. Personalmente no me agrada el diseño por defecto de las tablas dinámicas en el nuevo Excel, por lo que uso Opciones de tablas-Mostrar para definir el diseño clásico de tabla dinámica que también permite arrastrar los campos a la cuadrícula

Arrastramos le campo de departamentos al área de las filas y cada uno de los campos de las preguntas al área de los valores. Resumimos los valores con la función Promedio y ajustamos el formato de los números.

Quitamos “Promedio de” en los encabezados de la columna para hacerlos más legibles.

Seleccionamos el área de valores y aplicamos Formato Condicional –Escala de Color

El resultado habla por si mismo.

Cuanto más verde es el fondo, mejor la calificación y, por lo contrario, cuando más rojo peor la calificación. Claramente podemos ver que el mejor departamento en la calificación general es Recursos Humanos. El nivel de servicio del Taller es claramente deplorable!

Planificar proyectos – pedido de ayuda a mis lectores

Necesito la ayuda de mis lectores para verificar y corregir, donde haga falta, mi nuevo modelo para planificar actividades. Los primeros cinco lectores que estén dispuestos a ayudarme recibirán un enlace para descargar el modelo.

La ayuda consiste en usar el modelo, para encontrar "bugs" y sugerir mejoras de funcionamiento o diseño.

A cambio de la ayuda, una vez terminadas las correcciones, recibirán una copia del modelo con la contraseña que les permitirá ver todas las fórmulas y códigos utilizados en su desarrollo.

El mail aceptando ayudar debe enviarse a la dirección que figura en el enlace "Ayuda" (de ninguna manera poner la dirección del mail en un comentario!!). Desde ya, muchas gracias.

Y ahora, ¿qué es este modelo de planificación de actividades? Hemos tocado el tema tangencialmente en las notas sobre cómo crear un diagrama de Gantt en Excel. Este modelo va más lejos. La idea es poder definir actividades de un proyecto, crear dependencias entre las actividades (una actividad "precedente" determina la fecha de inicio de la actividad "dependiente"), mostrar la relación de la actividades en un diagrama de Gantt y crear una cuadro de control de la actividades.

Parte de los datos son calculados automáticamente: al determinar la fecha de comienzo y la duración, la fecha de finalización es calculada automáticamente. Lo mismo si se ingresa la fecha de finalización y la duración, etc.

Este modelo tiene dos objetivos:

• Dar una alternativa sencilla a la planificación de proyectos cuando el uso de aplicaciones como MSProject son un "overkill"
• Servir como material para estudiar las distintas técnicas empleadas (controles, nombres dinámicos, eventos, macros, gráficos dinámicos, etc.)

El modelo ha sido desarrollado en tres versiones: Excel 2003, Excel 2007 y Excel 2010.
Está compuesto de cuatro hojas visibles, dos ocultas y una que puede ser creada "al vuelo".

Inicio: definición del nombre del proyecto e instrucciones para el uso del modelo.

Actividades: definición de las actividades del proyecto (nombre, descripción, responsable, fecha inicio, final fin, duración, precedente, estatus de cálculo, estatus de datos). El modelo permite determinar "precedentes", actividades que deben ser completadas antes que otra actividad (la dependiente) pueda comenzar.

Gantt: diagrama de Gantt que se actualiza automáticamente de acuerdo a los cambios en la hoja Actividades. Se muestran también el número de días requerido para completar el proyecto (total y sólo días laborales).

To Do: permite controlar el estado de las actividades en relación a la fecha corriente.

Detalle de actividad: el hacer un doble clic en el nombre de una actividad (en la hoja Actividades), se crea automáticamente una hoja que permite detallar sub-tareas para la actividad. Si la hoja existe, pasa a ser la hoja activa.

Feriados: oculta; se puede acceder desde la hoja de actividades. Aquí se definen los días feriados para el cálculo del total de días laborales entre el principio y fin del proyecto.

Actualización: el modelo está disponible! Los enlaces para la descarga se encuentran en esta página.

Crear tablas dinámicas independientes.

Tal vez habrán notado un problema que existe en tablas dinámicas que comparten la misma base de datos: aplicar agrupar o desagrupar en una tabla produce el mismo efecto en la otra.

Supongamos que tenemos una tabla de facturas por fecha (como siempre, la bendita base de datos Northwind)

Creamos dos tablas dinámicas: una que muestra las ventas por año y mes y la segunda por año y trimestre, para lo que usaremos Agrupar

Para la primer tabla

con este resultado

Ahora creamos la segunda tabla basándonos en la misma base de datos, para lo cual usamos Insertar-Tabla Dinámica (o Datos-Tabla Dinámica en Excel Clásico). Pero al momento de crear la tabla, antes de que hayamos arrastrado alguno de los campos a alguna de las áreas podemos notar que el campo Años, creado al agrupar la tabla anterior, está presente en la lista de campos

Al arrastrar el campo Fechas al área de filas, los datos estarán agrupados por meses

Veamos que pasa al agrupar los datos en la segunda tabla por trimestres



Ambas tablas sufren el mismo cambio. Para que esto no suceda tenemos que construir la segunda tabla usando el asistente de tablas dinámicas.

En Excel Clásico (97-2003), al crear la segunda tabla usando la misma base de datos, veremos este mensaje

Todo lo que nos queda por hacer es apretar No para crear informes independientes



En Excel 2007/10 no se abre la opción de crear tablas independientes al construir la segunda tabla. Para que esta opción se presente tenemos que usar el asistente de tablas dinámicas. El viejo asistente existe en Excel 2007/10, pero está oculto lejos de los ojos del usuario. Para hacerlo visible usamos el atajo de teclado Alt+T+B

Seguimos los pasos hasta recibir el mensaje

Procedemos como en el caso anterior y creamos tablas dinámicas independientes también en Excel 2007/10

Formato Condicional en Excel 2010 – Cambios y Mejoras

En la nota sobre formato condicional personalizado con iconos pasé por alto las diferencias y mejoras introducidas en la versión 2010 de Excel (gracias a Carola por llamar mi atención sobre el error). Si bien los cambios y mejoras son menos dramáticos de los introducidos en Excel 2007, vale la pena pasar revista a las diferencias entre Excel 2007 y Excel 2010 en lo que a formato condicional se refiere.

En esta nota veremos los cambios en el formato condicional con conjuntos de iconos. Esta funcionalidad fue introducida en Excel 2007

La limitación en Excel 2007 es que no podemos combinar entre iconos que pertenecen a distintos conjuntos. En Excel 2010 podemos cambiar el icono de cada una de las condiciones

Esta funcionalidad incluye la posibilidad de determinar que bajo determinada condición no aparezca ningún icono

Esto es útil cuando queremos que aparezca un icono sólo cuando se cumple una determinada condición, por ejemplo los tres mejores meses de ventas

Otro cambio es la posibilidad de elegir el conjunto de iconos visualmente desde una lista desplegable

Otra herramienta introducida en Excel 2007 y mejorada en Excel 2010 son las barras de datos.

En este ejemplo aplicamos formato condicional con barra de datos en la versión Excel 2007

Podemos ver que las proporciones entre las barras del mejor mes (Agosto) y el peor (Setiembre) no concuerdan con la diferencia entre los números. Las barras de datos en Excel 2007 no sirven para comparar valores. Este problema fue solucionado en Excel 2010. Los mismos datos en Excel 2010 se ven así

Otras mejoras en formato condicional con barras de datos son:

• Posibilidad de elegir entre relleno degradado y relleno sólido (en Excel 2007 sólo degradado)
• Posibilidad de poner borde a la barra

• Posibilidad de representar valores negativos en forma efectiva