lunes, 16 de marzo de 2015

Desplegables Dependientes con Nombres con Espacios en Blanco

"Necesito crear listas desplegables dependientes con validación de datos. El problema es que algunos nombres tienen espacios en blanco y excel no me los reconoce".

Como siempre no hay problema. Pero antes de nada hoy toca felicitar a mi amigo del alma Don José: Muchas felicidades hermano!! Tendremos que celebrarlo, a ser posible en esa vuestra marisquería a la que tantos premios os dan...

Partimos del siguiente ejemplo:
Queremos generar una lista desplegable en B3 con los 4 modelos existentes, a saber, GT 100; ASIUS; PROMETEO; y TT 400. Para ello nos situamos en B3, vamos a Validación de datos y en Permitir elegimos Lista. En Origen marcamos el rango $F$3:$I$3  y aceptamos. Para que al seleccionar uno de los modelos aparezca el tipo de su grupo, debemos ahora crear nombres de dichos rangos. El problema es que en la lista original de modelos algún nombre tiene espacios (GT 100 y TT 400). Para evitar este problema, insertamos una fila entre la 3 y la 4 y copiamos los nombres originales pero sustituyendo ahora los espacios por guión bajo:
Seleccionamos ahora el rango F4:I7 y vamos a la ficha Fórmulas, grupo Nombres definidos y seleccionamos Crear desde la selección. Marcamos Fila superior y aceptamos:
Si nos fijamos en el cuadro de nombres veremos que ya disponemos de éstos:
Nos situamos ahora en D3 y vamos a Validación de datos. En Permitir seleccionamos Lista y en Origen escribimos la siguiente fórmula:
=INDIRECTO(BUSCARH($B$3;$F$3:$I$4;2;FALSO))  y aceptamos.
La parte de BUSCARH lo que hace es buscar el nombre en nuestra lista original y transformarlo en el nombre con guión bajo. Una vez hecho esto, INDIRECTO reconoce el nombre de la "sublista" y nos presenta los tipos relativos a cada modelo:

viernes, 6 de marzo de 2015

Ordenar Texto con Fórmulas

"Tengo grandes listados donde me aparecen, en distintas columnas, Nombre; Apellido 1; Apellido 2. Quisiera que primero me presente la información con el formato Apellido1 Apellido 2, Nombre y, finalmente, que me los ordene de la A a la Z automáticamente".

Como siempre, partimos de un ejemplo:
Lo primero que vamos a hacer es CONCATENAR los apellidos y nombre como nos interesa. Nos situamos en la celda H3 y escribimos:
 y copiamos hasta la celda H16. De esta manera estamos concatenando primero el primer apellido; después un espacio en blanco; inmediatamente el segundo apellido; ahora una coma y un espacio en blanco y, finalmente, el nombre. De esta manera, en la celda H3, por ejemplo, aparecerá Arnaiz Guerra, Begoña:
Como no existe una función en excel que ordene directamente texto, vamos a utilizar una fórmula matricial para asignarle un número de orden a cada registro. Primero le doy nombre al rango H3:H16. Selecciono dicho rango y en el cuadro de nombres escribo ApNombre, y pulsamos enter para acabar. Vamos a G3 y escribimos:
 Pulsamos Ctrl+Shift+Enter y se mostrará como fórmula matricial:
Terminamos copiando esta fórmula hasta G16.
Excel es capaz de comparar textos por orden alfabético, de tal manera que si comprobamos si un texto es mayor, menor o igual que otro texto, excel nos devolverá un VERDADERO o FALSO. El menor valor es la A y el mayor la Z. Ejemplos:
El resultado de aplicar la fórmula matricial será: 
Ahora ya disponemos de un número de orden para cada registro. Procedemos ahora a preparar el cuadro para la salida final de información:
Seleccionamos el rango G3:H16 y le damos el nombre OrdenNombres. Vamos a K3 y escribimos la fórmula (que luego copiaremos hasta K16):
 

lunes, 2 de marzo de 2015

Listas Desplegables "Excluyentes"

"En mi entrada de datos, tengo una serie de celdas con lista desplegable generada con validación de datos. Me gustaría que al ir seleccionando referencias de la lista, dichas referencias desaparecieran del listado disponible en el resto de celdas".

Para solucionar este problema, tendremos que utilizar varias funciones y herramientas así que, como dijo Jack el destripador, vamos por partes...

Partimos del siguiente ejemplo:
En el rango B3:B16 queremos tener listas desplegables en las que aparezcan los colores de la lista1 pero con la peculiaridad de que a medida que vayamos seleccionado colores de la lista éstos desaparezcan de la siguiente lista desplegable (como se muestra en la imagen a continuación):

A medida que vamos eligiendo colores en la entrada de datos, en las siguientes listas sólo quedan disponibles los colores no seleccionados todavía.
Empezamos dándole el nombre Datos al rangoB3:B16. Hecho esto, generamos la tabla que se puede ver en el rango G2:H11. La primera columna (columna G) es un número de orden de los elementos existentes (los colores). En la columna H vamos a formular. Nos situamos en H3 y escribimos:
=SI(O(Datos=E3);"";E3)   y pulsamos Ctrl + Shift + Enter ya que se trata de una fórmula matricial. Aparecerá así: {=SI(O(Datos=E3);"";E3)}
Esta fórmula comprobará los valores seleccionados en el rango B3:B16 (Datos) y hará que dichos valores desaparezcan (los convierte en "") de la lista existente en H3:H11. A continuación preparamos la siguiente zona de procesos:
Nos situamos en J3 y escribimos la fórmula:
=SI(H3="";0;G3)   y copiamos hasta J11.
En K3 escribimos:
=K.ESIMO.MAYOR($J$3:$J$11;G3)   y copiamos hasta K11. Con esta fórmula ordenamos de mayor a menor los valores obtenidos en la columna J.
Seleccionamos G3:H11 y le creamos el nombre Lista2. Vamos a L3 y escribimos:
=SI.ERROR(BUSCARV(K3;lista2;2;FALSO);"")    y copiamos hasta L11. Con esta fórmula estamos buscando en la tabla llamada Lista 2 los colores que se corresponden con los números de la columna K:
Si nos fijamos en la imagen, lo que hemos conseguido es generar una lista ordenada (Lista3) de los colores que todavía no hemos elegido. A partir de aquí podemos hacer dos cosas, a saber: generar directamente la lista desplegable en las celdas B3:B16 a partir del rango Lista3 (en este caso aparecerán sólo los colores no elegidos pero con espacios en blanco en la lista); o generar un rango dinámico para ajustar la lista desplegable a sólo los colores que aparecen en la Lista3. Vamos a ver las dos opciones. En cualquier caso empezamos por crear el nombre Lista3 para el rango L3:L11. 

Opción 1: Seleccionamos B3:B16 vamos a Validación de datos/Permitir: Lista y en Origen escribimos  =Lista3  y aceptamos:
Como se puede comprobar, a medida que vamos eligiendo colores desaparecen de la lista pero queda el espacio en blanco dentro el desplegable.

Opción 2: Creamos un rango dinámico para evitar filas en blanco dentro del desplegable. Para ello seleccionamos el rango B3:B16 vamos a Validación de datos/Permitir: Lista y en Origen escribimos:
=DESREF($L$2;1;;CONTAR.SI($K$3:$K$11;"<>0"))  y aceptamos (para más información sobre esta fórmula véase el post Lista Desplegable con Rango Dinámico):