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!

Las nuevas funciones DIA.LAB y DIAS.LAB en Excel 2010

Esta última semana se ha caracterizado por una intensa actividad, tratando de terminar a tiempo algunos proyectos. La semana entrante no pinta mejor, por lo que no me queda más que pedir paciencia a todos los que me han consultado por mail en los últimos días. Poco a poco iré poniéndome al día.

Mientras tanto, una entrada “al paso”. Hace poco menos de dos años atrás publiqué dos entradas mostrando alternativas a las funciones DIA.LAB y DIAS.LAB de Excel. El problema con estas funciones consistía en que tenían pre-programado el fin de semana con los días sábado y domingo.

Quien haya instalado Excel 2010 puede olvidarse de las soluciones que proponía en esas notas y usar las nuevas funciones DIA.LAB.INTL y DIAS.LAB.INTL

Estas funciones cuentan con un argumento opcional “fin de semana”

DIA.LAB.INTL(fecha_inicial; días_lab; [fin_de_semana]; [festivos])

DIAS.LAB.INTL(fecha_inicial; fecha_final; [fin_de_semana]; [festivos])

Este argumento nos permite establecer cuáles son el o los días a tomar en cuenta como fin de semana. Lo interesante es que este argumento tiene dos sintaxis alternativas:

1 – Podemos usar un valor de esta tabla de valores (ver la ayuda en línea de Excel)

2 – usar una combinación de siete ceros y unos. La posición de cada cifra en la cadena representa el día de la semana, empezando por el lunes. Usamos el 1 para representar los días no laborables y 0 para los laborales. Así, por ejemplo, si el fin de semana es viernes-sábado, la cadena será “0000110”; si el fin de semana es domingo-lunes la cadena será “1000001”; y si alguien conoce el país donde el fin de semana es “1111111”, por favor me avisa donde queda el consulado o la oficina de inmigraciones….

Usar el control Calendario en Excel 2010

En el pasado, en aquellos lejanos días de Excel Clásico (97-2003), mostramos en este blog las bondades de usar el control Calendario (Mscal.ocx) y también como validar fechas con el control.

Las malas noticias para los que empiezan a trabajar con Excel 2010 es que el control ha sido removido del paquete de Office.

Microsoft comenta el tema en esta nota diciendo:

El control Calendar (mscal.ocx) […] fue removido de Access 2010 y no se puede utilizar en Excel 2010. En cambio, los usuarios pueden utilizar el Date Picker o sus propios controles de calendario personalizado.

El Date Picker se encuentra en la colección de controles ActiveX

Una segunda posibilidad es usar controles o complementos de terceras partes. Personalmente puedo recomendar el complemento desarrollado por Sam Radakovitz. Todo la que hay que hacer es descargar el complemento en la nota que Sam publicó el blog de Microsoft Office y copiarlo en la carpeta XLSTART (por ejemplo: C:\Program Files\Microsoft Office\OFFICE14\XLSTART).

Una vez instalado el complemento aparecerá en la cinta en la pestaña Complementos

El complemento hace que el calendario aparezca en toda celda que contenga una fecha o en la celda inmediata inferior.
También aparece en la celda que se encuentre debajo de una que contenga la palabra “Date” (fecha, en inglés. No, no funciona con Fecha en castellano) o desde el menú contextual que se abre con un clic del botón derecho del mouse



Existe una tercera posibilidad: instalar el control Calendario de Excel Clásico (Mscal.ocx) para utilizarlo en Excel 2010. Esta posibilidad es válida para la versión 32 Bit de Excel 2010.

El control puede descargarse en fonstuff o mejor aún en la página de descargas de Graham Mayor (buscar el MSCAL.ZIP).

Para poder usar el control hay que copiar el archivo MSCAL.OCX en C:\Windows\System32 y registrarlo. Para registrarlo hacemos un clic a Start (Inicio) y en Run copiamos “regsvr32 mscal.ocx” en

Apretamos OK y veremos esta mensaje

Ahora el control aparecerá en la colección de controles ActiveX

Tablas Dinámicas en Excel 2010 – Segmentación de Datos

En la nota anterior empecé a exponer las mejoras introducidas en Excel 2010 en comparación a las versiones anteriores. Vamos a tratar otra mejora substancial al funcionamiento de las tablas dinámicas: la segmentación de datos (Slicers, en la interfaz en inglés).

Como en la nota anterior usamos la base de datos NorthWind para crear nuestro ejemplo

La posibilidad de filtrar los datos de la tabla existe en todas las versiones de Excel

Si bien el filtrado ha sido mejorado considerablemente en Excel 2007, todavía existían muchas limitaciones:
Si queremos elegir más de un ítem tenemos que marcar y desmarcar uno por uno (en Excel 2007 podemos usar filtros con criterios)

• Los filtros no reflejan la organización jerárquica de los datos Si los datos están organizados en forma jerárquica (en nuestro ejemplo Tipo de Producto-->Producto). Por ejemplo, si agregamos los productos al capo de la filas y filtramos por Beverages (bebidas), seguiremos viendo todos los productos de todas las categorías.

• No podemos configurar la apariencia de los filtros, ni su ubicación. Esto limita en gran medida la posibilidad de usarlo directamente en nuestros dashboards, en particular si el usuario no tiene suficiente experiencia en el uso de Excel.

• Y, tal vez lo más importante, si creamos más de una tabla para la misma base de datos, tenemos que filtrar cada tabla por separado.

Todas estas limitaciones han sido superadas en Excel 2010 con la introducción de la segmentación de datos.
Para activar la segmentación de datos tenemos que seleccionar alguna de las celdas de la tabla dinámica

Vamos a empezar por insertar dos segmentos



Como puede verse, podemos mover y cambiar el diseño de los filtros. Cuando activamos uno de los filtros, aparece en la cinta el menú Herramientas de Segmentación de Datos. No me voy a extender aquí en todas la posibilidades pero si llamar la atención al menú Configuracion

Ahora crearemos una segunda tabla basada en los mismos datos para mostrarlos en forma porcentual. Para hacer el uso más evidente, vamos a exhibir en las tablas los productos.

Naturalmente queremos aplicar un filtro simultáneamente a ambas tablas. Esto la hacemos con el menú Conexiones de tabla dinámica (en la cinta de Herramientas de Segmentación o con el menú contextual del cuadro de segmentación)

Este video muestra dos efectos del filtro: 1) el filtro ProductName se sincroniza con la elección en el filtro CategoryName; 2) ambas tablas muestran los cambios en forma sincronizada



Creo que todo esto, junto con las posibilidades de configuración personalizada (y todavía no hablamos de Vba), muestran que en Excel 2010 tenemos un herramienta aún más poderosa para el desarrollo de nuestros informes o dashboards.

Mejoras en las tablas dinámicas de Excel 2010

Las tablas son dinámicas son, en mi opinión, la mejor herramienta en el “arsenal” de Excel. En todo los que se refiere a analizar datos, en particular si se trata de grandes cantidades de datos, son irreemplazables. Pero siempre hay lugar a mejoras y en esta nota voy a reseñar una de esas mejoras.

Empecemos por crear una tabla dinámica basada, qué novedad!, en la base de datos Northwind que viene con el paquete de Office. Una imagen vale más que cien palabras pero un video vale más que mil, así que mostraremos el proceso en esta captura de pantalla



Una vez creado el informe dinámica, podemos cambiar la forma en que los datos son presentados. En las versiones anteriores de Excel hacíamos esto mediante el menú de configuración de campo de valor. En Excel 2010 la opción Mostrar Como ha sido agregada al menú contextual (el que se abre presionando el botón derecho del mouse)

No sólo tenemos un acceso más cómodo sino que también han sido agregadas seis nuevas opciones:

Vamos a demostrar algunos de los usos, para lo cual agregaremos algunas columnas a la tabla:

Mostrar valores como porcentaje de uno de los campos (de fila como Beverages, por ejemplo o de columna como Qtr1)



Como porcentaje del total de filas principales




Crear totales acumulados




Como ven estamos sacando provecho a la posibilidad de arrastrar más de una vez el mismo campo al área de datos.

Otra mejora importante es la posibilidad de agregar etiquetas de elementos:

Activar Macros y controles ActiveX en Excel 2010

Quien haya descargado e instalado Excel 2010 descubrirá, como en Excel 2007, que por defecto la pestaña de Programador no aparece en la cinta. Esto se debe a que por defecto las macros no están habilitadas en Excel 2010. Tampoco tenemos acceso a los controles (formulario y ActiveX).

Si están buscando una solución al problema "No se puede insertar el objeto", debido a la actualización de Microsoft del 9 de diciembre, lean esta nota.

Para poder usar las macros y los controles en Excel 2010 tenemos que seguir los siguientes pasos:
Activamos la pestaña Archivo y apretamos el botón Opciones

Activamos el Centro de Confianza y apretamos el botón Configuración del centro de confianza

En el centro e confianza activamos la opción Configuración de macros y habilitamos la opción Habilitar todas las macros

Apretamos Aceptar con lo que habremos habilitado las macros en nuestra copia de Excel 2010.
Sin embargo la pestaña de Programador no aparecerá. Para hacerla aparecer usamos nuevamente el menú Opciones de Archivo y en Personalizar la cinta de opciones marcamos Programador

A partir de ese momento podemos grabar, editar y crear macros y también usar controles en nuestros cuadernos.

Microsoft anuncia el nuevo Excel 2010

Posiblemente JLD Excel sea el último blog de la galaxia en anunciar a sus lectores el lanzamiento de la versión beta de Excel 2010.

Los interesados en experimentar con ésta nueva versión de Excel pueden descargarla el sitio Office 2010 Beta.

Al descargarla les recomiendo marcar la opción “custom” y “guardar versiones anteriores”. La validez de la versión expira en Octubre del 2010.



Entre las novedades que trae la versión:

# tablas dinámicas mejoradas y la inclusión del complemento PowerPivot

# la introducción de las micro-gráficas (sparklines, gráficas insertables en una única celda)

# nuevas mejores en el Formato Condicional

# la interfaz parece más “liviana”; se ha mejorado la cinta que ahora permite al usuario agregar botones

# vista previa de las opciones al momento de pegar datos que hemos copiado

# soporte para 64 bits

Y más.

Technorati Tags: MS Excel