Listas desplegables en Excel con ajuste automático.

En Excel es my fácil crear listas desplegables. El método más práctico es usando Validación de Datos – Listas. En este blog hemos tratado el tema de diversas oportunidades y hemos mostrado también como crear listas desplegables dependientes.

En relación a este tema recibo a menudo consultas sobre cómo ir eliminando de la lista los valores que ya han sido seleccionados.

Supongamos que estamos organizando en que mesa se sentarán los invitados a una cena o recepción. Para el caso supongamos una lista de 16 invitados que habrá que sentar en cuatro mesas, cuatro en cada una.

Empezamos por poner la lista de invitados en una hoja y una tabla que representa las mesas y sillas en otra








El objetivo es crear una lista desplegable que pondremos en cada celda del rango C3:F6 (rango que hemos puesto en el nombre “mesas”) de manera que cada vez que ubiquemos un comensal en la tabla éste desaparezca de la lista.

Mostraremos dos posibilidades: con columnas auxiliares y con fórmulas matriciales.

Con columnas auxiliares.

En la hoja que contiene la lista de invitados creamos una columna auxiliar con el rótulo “Por ubicar”




El objetivo de esta columna es controlar que invitado ya ha sido ubicado en alguna mesa. Para eso usamos la fórmula

=SI(CONTAR.SI(mesas,C2)=1,"",C2)

Por ejemplo, si ubicamos a Carla y a Federico en la matriz de las mesas, obtenemos







Ahora agregamos una nueva columna auxiliar (Aux1)



La fórmula

=CONTAR.SI($D$2:$D$17,"<="&D2)

da un número de orden a los valores del rango C2:C17 (como la función JERARQUIA a una serie de números).
En caso que el valor de celda sea vacío (la celda no está vacía ya que contiene una fórmula) el resultado es 0 (cero).
Nuestro próximo paso es crear una celda para controlar cuantas invitados nos quedan por ubicar, es decir, cuantos valores distintos de cero hay en el rango Aux1. Con este objetivo ponemos esta fórmula en la celda J1
=CONTAR.SI(E2:E17,">0")




Ahora creamos la columna auxiliar Aux2 en el rango F2:F17 con esta fórmula

=K.ESIMO.MAYOR($E$2:$E$17,$J$1-FILA()+2)




Esta columna nos sirve como argumento para la columna final, en el rango G2:G17, donde obtenemos los valores de la lista desplegable



Para evitar que en la lista desplegable aparezcan los valores #NUM de las últimas dos celda, usamos el valor de la celda “control” para crear un rango dinámico que las excluya con la fórmula

=DESREF(invitados!$G$2,0,0,invitados!$J$1,1)



Aplicamos esta validación de datos al rango “mesas”.



A medida que vamos ubicando a los invitados, la lista se va ajustando y mostrando sólo aquellos que quedan por ubicar



Con fórmulas matriciales.

Al igual que en el modelo anterior creamos la lista de los invitados a ubicar en el rango D2:D17.

La lista de invitados a ubicar la creamos en el rango E2:E17 con esta fórmula matricial

={INDICE($D$2:$D$17,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI($D$2:$D$17,"<="&D2:D17),FILA()-1),CONTAR.SI($D$2:$D$17,"<="&D2:D17),0))}





Como puede verse, no obtenemos errores #NUM, pero las celdas al final de la lista no están vacías. De manera que necesitamos una celda de control, como en la solución con columnas auxiliares, para que la lista desplegable muestre sólo los nombres disponibles.
En la celda H1 ponemos esta fórmula matricial

={SUMA((CONTAR.SI($D$2:$D$17,"<="&D2:D17)<>0)*1)}





La lista desplegable la creamos con el fórmula

=DESREF('invitados matricial'!$E$2,0,0,'invitados matricial'!$H$1,1)

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Technorati Tags: MS Excel

Encontrar la dirección de una celda en una matriz a partir del valor

El tema no es nuevo en el blog. En el pasado mostré como determinar la dirección de una celda a partir de su valor usando funciones definidas por el usuario (UDF, macros).

En los últimos tiempos el tema, en distintas variantes, se ha repetido en varias consultas. Por ejemplo, en la nota sobre encontrar el encabezamiento en una matriz a partir del valor de una de las celdas.

Tanto si queremos encontrar el rótulo de columna o de fila correspondientes a un valor en una matriz tenemos que ser capaces de calcular su posición en la hoja.
El tema de esta nota es cómo hacerlo usando funciones (usando macros ya lo hemos mostrado).

Empecemos por plantear esta matriz que muestra las unidades vendidas de cada línea de productos en cada zona (norte, sur, este, oeste)





Nuestro objetivo es que dado un valor de la matriz podamos ubicar la celda en que se encuentra. Esto nos permitirá luego determinar a qué zona y a qué producto corresponde el monto.
Seleccionamos el rango de valores (C3:F6) y lo incluimos en el nombre “ventas”



En el rango B8:B11 ponemos los rótulos: Monto, Producto, Zona y Celda



En C8 ponemos el valor, en C9 obtenemos el número de fila, en C10 el de columna y en C11 calculamos la dirección de la celda.
Las fórmulas son

Producto (C9): ={MAX((ventas=C8)*COLUMNA(ventas))}

Zona (C10): ={MAX((ventas=C8)*FILA(ventas))}

Celda (C11): =DIRECCION(C10,C9)

Las dos primeras son fórmulas matriciales que introducimos apretando simultáneamente Ctrl+Mayúsculas+Enter.

Como puede verse, obtenemos la dirección de la celda. ¿Cómo funcionan las fórmulas de las celdas C9 y C10?

La expresión “ventas=C8” crea una matriz de valores FALSO/VERDADERO


La expresión FILA(ventas) crea un vector {3;4;5;6}.
Al multiplicar ambos vectores entre sí obtenemos una matriz cuyos valores son 0 excepto el que representa la fila del valor buscado (4 en nuestro caso)



Finalmente, la función MAX extrae el máximo valor, obviamente el de la fila del valor buscado.
Aplicamos la misma técnica para calcular el número de columna


El paso final es usar la función DIRECCION con las celdas C9 y C10 como argumentos.

Ahora extenderemos nuestro ejercicio para que parezca útil en algo, por lo menos.
Queremos crear una lista de los tres productos más vendidos y en qué zona


Para encontrar los tres productos más vendidos usamos la función K.ESIMO.MAYOR (¿a quién se le ocurrió semejante nombre?). En C15 ponemos
=K.ESIMO.MAYOR(ventas,B15)

y la copiamos a C16:C17


En la celda D15 ponemos esta fórmula matricial

={INDICE($B$1:$B$6,MAX((ventas=$C15)*FILA(ventas)))}
y la copiamos al rango D16:D17

En la celda E15 ponemos la fórmula matricial
={INDICE($A$2:$F$2,,MAX((ventas=$C15)*COLUMNA(ventas)))}

y la copiamos al rango E16:E17


Con la primer función INDICE encontramos el producto buscando en el rango B1:B6. Importante: nótese que el rango comienza en la primer fila de la hoja. Esto se debe a que el vector creado por la función FILA empieza con el número de la primer fila de la matriz en la hoja. Lo mismo sucede con el vector creado por la función COLUMNA.


Technorati Tags: MS Excel

Icono de pegar fórmulas en Excel 2003

Si usamos a menudo la opción Pegar Fórmulas es muy práctico tener un icono para hacer el pegado con un solo clic. En Excel 2007 podemos agregarlo con facilidad a la barra de herramientas de acceso rápido

con la técnica para agregar iconos en la barra de acceso rápido.

Curiosamente en Excel 2003 esta opción no aparece en la opción Personalizar de las barras de herramientas



Una alternativa es grabar o escribir una macro y asociarla a un icono. Pero como de hecho el icono existe en la barra de herramientas Estándar



podemos sencillamente arrastrar el icono (en forma de texto) y posicionarlo en la barra de herramientas de manera que siempre esté visible.

Para hacer esto empezamos por crear una fórmula cualquiera en una celda y copiarla (hacemo9s esto para que la opción pegar fórmulas aparezca en la lista).

Luego usamos el menú Herramientas-Personalizar (o clic con el botón derecho en la zona de la barras de herramientas)

Ahora arrastramos el texto Fórmulas y lo ponemos, por ejemplo, al lado del icono de Pegado



A partir de ahora podemos pegar fórmulas con un solo clic.

Technorati Tags: MS Excel

Búsquedas complejas en matriz de Excel

En los últimos meses varios lectores me han consultado sobre cómo resolver este ejercicio (lo que me lleva a pensar que se trata de un típico ejercicio de los que aparecen en los cursos de Excel)

Dadas estas tablas se pide mostrar la lista de deportes que practica un socio ingresando el número de socio. Se permite usar cualquier número de fórmulas.

Como es obvio, la solución es bastante complicada y no creo que sea de interés general para el lector promedio de este blog. De todas maneras, hay mucho que aprender de este ejercicio:

1 – cómo construir fórmulas complejas

2 – cómo usar fórmulas matriciales

3 – y lo más importante: cómo no organizar datos en Excel.

Empecemos por mostrar una captura de pantalla que muestra cómo funcionará nuestra solución:

La fórmula matricial que crea la lista de de los deportes de cada socio (la fórmula en el rango B26:F26) es

=INDICE(deportes,,K.ESIMO.MENOR(SI(ESERROR(HALLAR("x",INDIRECTO(DIRECCION((COINCIDIR(B25,num_socio,0)+13),2)&":"&DIRECCION((COINCIDIR(B25,num_socio,0)+13),6)),1)*COLUMNA(1:5)),"",HALLAR("x",INDIRECTO(DIRECCION((COINCIDIR(B25,num_socio,0)+13),2)&":"&DIRECCION((COINCIDIR(B25,num_socio,0)+13),6)),1)*COLUMNA(1:5)),COLUMNA(1:5)))

Esta fórmula la crearemos en varios pasos. El primero es crear una fórmula que nos dé el número de socio dado su nombre. Esto lo hacemos con la función BUSCARV.

Primero definimos tres rangos nominados (rangos que ponemos dentro de nombres)

nombre_Socios =Hoja1!$A$3:$A$10

num_socio=Hoja1!$A$14:$A$21

socios=Hoja1!$A$3:$B$10

El nombre “nombre_Socios” nos sirve para crear una lista desplegable en la celda B24

En la celda B25 ponemos esta fórmula con BUSCARV para obtener el número de socio a partir del nombre



El próximos paso es crear una fórmula transitoria en la celda B26 (más adelante prescindiremos de esta celda)



Esta fórmula nos da el número de fila en la Tabla de Actividades que corresponde al socio buscado.

Ahora crearemos una serie de fórmulas en pasos intermedios para luego sintetizarlas un una única fórmula (la que expusimos más arriba).

En el rango B27:F27 ponemos esta fórmula matricial (y nuevamente recordamos que las fórmulas matriciales son introducidas pulsando simultáneamente Ctrl+Mayúsculas+Enter)

La función HALLAR nos permite determinar qué columnas de la fila correspondiente contienen una “x”. Para determinar la fila usamos

DIRECCION(B25,2)&":"&DIRECCION(B25,6)

donde B25 nos da el número de fila del socio. Usamos INDIRECTO para que el resultado de la concatenación de ambas funciones DIRECCION sea interpretada como rango y no como simple texto.

En el rango B28:F28 ponemos esta fórmula matricial

Este artilugio nos permite generar un vector con los primeros cinco número enteros (también podríamos haber ingresado los números directamente, pero para nuestra única fórmula necesitaremos un vector).

Ahora vamos a combinar las fórmulas de las filas 27 y 28 en una única fórmula matricial en la fila 29

=SI(ESERROR(HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5)),"",HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5))

Esta fórmula nos permite prescindir de las fila 27 y 28, que pasamos a borrar (además he cambiado el socio para que no resulte una serie de números sucesivos)



Como puede apreciarse, esta fórmula nos da los números de columna en la tabla donde aparece una “x” en la fila del socio.

Ahora tenemos que resolver el problema de las celdas en blanco. Es decir, queremos que no haya celdas en blanco entre los números que surgen de la fórmula.

Para lograrlo tendremos que echar mano a la función K.ESIMO.MENOR, combinándola con la fórmula anterior:

=K.ESIMO.MENOR(SI(ESERROR(HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5)),"",HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA($B$13:$F$13)-1),COLUMNA(1:5))

Ahora tenemos los valores relevantes en forma sucesiva y donde no hay valores el resultado es #NUM, lo que como veremos no representa ningún problema.

A esta altura del partido (para aquellos bravos lectores que han logrado llegar a esta parte de la nota) es obvio que podemos obtener los nombres de los deportes con la función INDICE (también ésta en forma matricial)

=INDICE(B13:F13,,K.ESIMO.MENOR(SI(ESERROR(HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5)),"",HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5)),COLUMNA(1:5)))

Podemos borrar ahora todas las filas intermedias, ya que esta fórmula no las utiliza. Sólo nos queda eliminar los resultados #NUM. Lo que haremos es ocultarnos usando formato condicional



Con esto hemos terminado nuestra tarea, quedando sólo aplicar un poco de cosmética como quitar las líneas de división.

A pesar de toda la pirotecnia que hemos mostrado aquí, desde el punto de vista de diseño y manejo de datos se trata de un mal ejemplo.

Las fórmulas matriciales se caracterizan por ser muy “pesadas”, haciendo que el cálculo de la hoja sea muy lento cada vez que se produce un cambio. En nuestro ejemplo esta sobrecarga no tiene importancia, pero si tuviéramos una lista de de varios miles de socios, el trabajo con este modelo sería un suplicio.

Los mismos datos pueden ser organizados, por ejemplo, en una única tabla (lista, en términos de Excel clásico) y con un simple Autofiltro puede obtenerse todos los cortes neesarios.

Podemos ir más lejos y separar los datos en dos tablas, socios y deportes, y generar el reporte con el MS Query.

Technorati Tags: MS Excel

Gráfico Excel con fecha.

Un lector me consulta cómo hacer este gráfico en Excel

Como pueden ver el eje de las X (categorías en Excel) es una escala de tiempo. Lo particular en este gráfico es que en el eje de las X sólo aparecen las fechas de los puntos de la serie de valores.
Crear este gráfico en Excel no es trivial y es lo que mostraremos en esta nota.

El resultado final será el siguiente

Empezamos por crear los datos básicos de grafico (fechas y valores) y el gráfico

Como puede observarse aparecen en el gráfico de las X valores que no figuran explícitamente en los datos de origen (en la columna A). Esto se debe a que Excel reconoce los datos en la columna A como fechas. Si nos fijamos en las definiciones del eje de la X



vemos que el valor mínimo es la primer fecha en el rango, el valor máximo es el último valor en el rango y la unidad principal es un mes.

Dado que las fechas en los datos no se distribuyen en intervalos regulares, no podemos crear el efecto deseado. No tenemos forma de ocultar las fechas para las cuales no hay datos. Para lograr el efecto deseado tendremos que transformar los datos.

Empezamos por crear datos auxiliares en las columnas D y E

En la columna D ponemos todas fechas, día por día, comprendidas entre la primer y la última fecha. En la columna E ponemos la fórmula

=BUSCARV(D3,$A$2:$B$10,2,0)

Esta forma pone el dato original en la fecha o #N/A si para la fecha no hay datos. Ahora cambiamos los datos en el gráfico por los del rango D1:E333



El segundo paso es cambiar el tipo de eje de las X de Automático a Categoría



Ahora tenemos que realizar una segunda transformación a los datos. En nuestro ejemplo creamos un rango de datos en las columnas G y H. En la columna G ponemos la fórmula

=SI(ESNUMERO(E2),D2,"")

y en la columna H sencillamente creamos una referencia a los datos en la columna E

El resultado es que en los puntos en los que no hay valores, la fecha no aparece.

Ahora podemos usar esta serie de datos en el gráfico, con este resultado

Como puede verse todavía no hemos llegado al resultado final. Nuestros próximos retoques son los siguientes:

# - abrimos el menú de formato del Eje de las X y en la pestaña Escala ponemos el valor 1 en la casilla de “Número de categorías entre rótulos de marcas de graduación

# - En la pestaña Tramas señalamos “Ninguna” en la opción Marca de graduación principal

El resultado “casi” final es

# - No queremos que el primer punto de la serie esté sobre el eje de la Y, para lo cual agregamos líneas en blanco al principio del rango y modificamos los datos de origen del gráfico de acuerdo

# - Para crear el efecto de retícula, cambiamos la trama de las líneas de división y definimos barras de error para los puntos de la serie.

Este paso implica cambiar la definición de la escala del eje de las Y a valores fijos, quitando las marcas de Automático de Mínimo y Máximo



Abrimos el menú formato de serie de datos activamos la pestaña Barras de Error Y, elegimos la opción Ambas y en Valor Fijo ponemos el máximo del eje de las Y (400 en nuestro ejemplo)



Finalmente seleccionamos las barras de error y abrimos el menú de formato. En la pestaña tramas elegimos la trama punteada para la línea y en Marcador la opción a la derecha



Con esto termina nuestra tarea

Technorati Tags: MS Excel