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

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!

Formato personalizado numérico condicional en Excel

En los albores de este blog ya había tocado el del formato personalizado de números. Vuelvo sobre el tema debido a la consulta que puso uno de mis lectores en un comentario de la entrada:

tengo unos registros en donde guardo la hora del día, pero quiero que me ponga madrugada si la hora es de 0 a las 5, mañana si es de 6 a 12, tarde de 13 a 19, y noche de 20 a 24, ¿se podrá con el formato personalizado?

En formato personalizado de celda de Excel existe la posibilidad de usar formatos condicionales. El método es muy elemental y admite sólo tres condiciones, de las cuales dos son explícitas y la tercera implícita. Las condiciones, o criterios, son comparaciones numéricas sencillas.

Si la consulta de mi estimado lector se limitara a tres condiciones, digamos mañana, tarde y noche, se podría hacer. Para el caso que queramos que aparezca "mañana" si la hora es anterior o igual a las 12:00, "tarde" si cae entre las 12:00 y las 18:00 y noche para el resto, la sintaxis es la siguiente

[<=0,5]"Mañana";[>0,75]"Noche";"Tarde"

Las dos condiciones explícitas las ponemos entre [] y la implícita es "todo el resto".

El valor 0,5 resulta de dividir 12 por 24; el valor 0,75 de dividir 18 por 24. Como ya explicamos en el pasado, en Excel las horas son números que resultan de dividir la hora por el total de horas del día (24).

En esta imagen podemos ver las horas 08:00 y 16:00 con tres formatos distintos: hora, general y personalizado condicional

Dado que el formato numérico condicional está limitado a tres criterios, no podemos resolver la consulta de mi lector con esta herramienta. La alternativa es usar formato condicional, que a partir de Excel 2007 no está limitado a tres condiciones. En el caso de la consulta los criterios son:

Usaremos formato condicional con la opción Fórmula, definiendo una fórmula para cada uno de los criterios. Para evitar problemas de precisión, usaremos la función HORANUMERO para calcular los números de series de las horas

El cuaderno con el ejemplo se puede descargar aquí.

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

Formato condicional personalizado con iconos en Excel 2007/10

Entre las funcionalidades cuyas sus posibilidades más se han extendido en Excel 2007/10 se cuenta sin dudas el formato condicional. Ya he tratado el tema en varias notas, en general y el uso de iconos en particular.

Un lector me consulta:

Quiero utilizar conjuntos de iconos de la siguiente manera: 1.Valores entre 0 y 4: flecha verde. 2.Valores entre 4 y 10: flecha naranja 3. Resto de valores, bien por debajo de 0 o por encima de 10: flecha roja ¿¿Se puede hacer?? Es que no encuentro ninguna fórmula

En realidad no necesitamos ninguna fórmula (recordemos que estamos usando Excel 2007 o Excel 2010). Podemos usar el conjunto de iconos, pero tendremos que modificar las opciones por defecto que Excel nos ofrece.

En el caso de mi lector, queremos usar tres iconos/flechas diferentes pero para cuatro posibilidades. Vamos a mostrar el proceso de construir la regla.

Supongamos que esta es la lista de valores a la que queremos aplicar el formato condicional (que por comodidad he ordenado de menor a mayor sin que esto sea necesario para aplicar las reglas)

Empezamos por seleccionar el rango de la lista y elegir la opción “conjunto de iconos-4 flechas de color”

Ahora debemos cambiar el formato por defecto que aplica Excel, para lo cual usamos el menú Formato Condicional-Administrar reglas-Editar reglas

Cambiamos la opción Tipo de “porcentual” a “número”

En la primer regla ponemos “>10” y cambiamos el icono usando el botón de iconos (Aclaración: esta opción existe sólo en Excel 2010. Gracias a Carola por llamar mi atención sobre este punto)

Al hacer esto los valores en la segunda regla cambian automáticamente, determinando el límite inferior de la regla

Ponemos como límite superior “4” para esta regla y cambiamos el icono. Esto determina el límite inferior de la tercera regla

La cuarta regla queda definida por defecto. Apretamos “Aceptar” y..voila!

Diseño clásico de tablas dinámicas en Excel 2007

En relación a la nota anterior sobre el tema, un lector me consultaba si era posible cambiar el diseño por defecto de las tablas dinámicas en Excel 2007 de manera que se abrieran con el diseño "clásico" (Excel 2003) y que también se pudieran arrastrar los campos, como era posible en el Excel Clásico.

En la nota anterior pasé por alto un detalle importante que da respuesta a las dos inquietudes de mi lector.

Al crear una tabla dinámica en Excel 2007, el diseño por defecto es el siguiente

Para cambiar el diseño al del Excel Clásico, en lugar de usar los estilos de tablas dinámicas como mostramos en la nota anterior, podemos usar el formulario Opciones de Tablas Dinámicas

En la pestaña "Mostrar" marcamos la opción "Diseño de tabla dinámica clásica". Esta opción no sólo cambia el diseño de la tabla sino que también permite arrastrar los campos, incluidos los campos de página, tal como lo hacíamos en las versiones anteriores de Excel. Una vez activada esta opción la tabla se verá así

Nótese que ahora los rótulos de los campos de columnas son visibles y es posible arrastrarlos. Lo mismo con los campos de página.

Este video muestra el proceso



Otro "tip" para los nostálgicos del Excel Clásico. En Excel 2007 se puede activar el "viejo" asistente de tablas dinámicas apretando el atajo de teclado Alt+T+B.

Tablas dinámicas – cambiar el diseño de Excel 2007 a Excel Clásico

Quienes emigran al nuevo Excel (2007/10) del Excel Clásico (97-2003) deben adaptarse a muchos cambios. Uno de ellos es en el diseño por defecto de las tablas dinámicas.

Partiendo de la misma base de datos obtenemos estos resultados

en Excel Clásico

en Excel 2007

Además de algunas diferencias estéticas, saltan a la vista dos detalles:

• los botones a la izquierda de los rótulos de fila en la versión 2007 que no están presentes en las versiones anteriores
• en Excel 2007 los totales de de cada rótulo de fila están en la fila inmediata superior al detalle del los valores, mientras que en Excel Clásico están al final.

A muchos usuarios les cuesta, o no quieren, acostumbrarse a este nuevo diseño. Si queremos obtener las tablas dinámicas en el nuevo Excel con el diseño del Excel Clásico podemos hacer lo siguiente

1 – cambiar el diseño de la tabla al formato tabular



2 – Cambiar el estilo

3 – Quitar los botones +/-

El resultado final