Fórmulas con condiciones múltiples en Excel

Supongamos un formulario en Excel donde controlamos un proceso de aprobación de créditos compuesto de tres partes: presentación, proceso y aprobación

Al introducir una fecha en la celda A4, la celda Estatus (D4) muestra la etapa en que se encuentra el proceso; al introducir una fecha en B4, el valor de Estatus cambia a “En proceso” y finalmente al introducir una fecha en C4, Estatus muestra “Aprobado”



La solución más inmediata es crear una fórmula con la función SI

=SI(A4<>"",SI(B4<>"",SI(C4<>"","Aprobado","En proceso"),"Presentado"),"")

Esta fórmula tiene varios problemas. El primero es que el uso de SI anidado nos pone serias limitaciones a la cantidad de condiciones que podemos procesar. Si bien en Excel 2007 o 2010 podemos anidar hasta 64 niveles de SI (en comparación a los 7 niveles en Excel Clásico), por encima de las 3 o 4 condiciones la fórmula se vuelve compleja y difícil de manejar.

Podemos superar este problema creando una fórmula con la función ELEGIR

=ELEGIR(CONTARA(A4:C4)+1,"","Presentado","En proceso","Aprobado")

Otro problema con estas fórmulas es que no toman en cuenta errores. Por ejemplo, que introduzcamos una fecha en “Proceso” sin que hayamos puesta una fecha anterior en “Presentación”.

Para superar este inconveniente podemos usar esta fórmula que combina ELEGIR con MMULT y una matriz de constantes

=ELEGIR(MMULT((A4:C4<>0)*1,{4;2;1})+1,"","Error 3","Error 2","Error 2","Presentado","Error 1","En proceso","Aprobado")



MMULT multiplica los miembros de la matriz (A4:C4<>0) por los valores de la matriz {4;2;1} determinando así el valor de la variable de ELEGIR. Este video muestra el proceso de cálculo de la fórmula cuando sólo la celda A4 contiene una fecha



También esta fórmula tiene un inconveniente. Es difícil de mantener, por ejemplo, si queremos agregar condiciones o cambiar valores.

La mejor solución, y que está inspirada en una respuesta de Ron Coderre en uno de los foros en los que participo, es usar una tabla auxiliar para determinar los valores y SUMAPRODUCTO con BUSCARV (CONSULTAV en Excel 2010)

La fórmula es (o parece!) sencilla

=CONSULTAV(SUMAPRODUCTO(((A4:C4)<>"")*{1,10,100}),$I$4:$J$11,2,0)

Como en la fórmula anterior, multiplicamos dos matrices para obtener un valor que usamos en CONSULTAV (o BUSCARV en versiones anteriores a Excel 2010) para obtener el texto correspondiente en Estatus



Para determinar los valores de la columna Indicador, usamos un sistema “pseudo-binario”. Esta imagen explica el método

Cada fila en la tabla es una de las posibilidades; en cada posibilidad indicamos con un 1 si la celda contiene una fecha; luego sumamos las celdas de la fila donde la columna Fecha 1 le asignamos el valor 1 (unidades), la columna Fecha 2 el valor 10 (decenas) y a la columna Fecha 3 el valor 100 (centenas).

El archivo con los ejemplos se puede descargar aquí.

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: