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):

Evitar Códigos Duplicados en Varias Columnas

"Tengo una tabla con códigos en las columnas A y C, y quiero introducir nuevos códigos en la columna B, pero evitando que se puedan repetir los que ya se encuentran en dichas dos columnas y que, por otro lado, tampoco se puedan repetir los nuevos que voy introduciendo en la columna B".

Partimos del siguiente ejemplo:

Lo primero que hacemos es crear los nombres de las tres columnas de esta tabla. Vamos a la ficha Fórmulas y en el grupo Nombres definidos seleccionamos Crear desde la selección y marcamos Fila superior: 

Ya hemos generado los tres nombres:

Seleccionamos ahora el rango B3:B16 y vamos a la ficha Datos/Validación de datos. Como criterio de validación permitimos Personalizar, y en fórmula escribimos la siguiente:

=Y(CONTAR.SI(CodigoA;B4)=0;CONTAR.SI(CodigoC;B4)=0;CONTAR.SI($B$4:B4;B4)=1)

Una vez aceptemos, excel verificará, para cada una de estas celdas (B4:B16), que los valores que vayamos introduciendo no se encuentran repetidos en las columnas A y C 
=Y(CONTAR.SI(CodigoA;B4)=0;CONTAR.SI(CodigoC;B4)=0     y, además, verifica que no se duplican los nuevos valores que vayamos introduciendo en la columna B    CONTAR.SI($B$4:B4;B4)=1)

En caso de que se repita alguno de los códigos introducidos aparecerá un mensaje de error que nos obligará a realizar una nueva entrada correcta:

Selección Aleatoria de un Valor de un Rango (2)

"Necesito seleccionar aleatoriamente un número de un rango determinado. He visto una solución en tu blog en el post Seleccionar Aleatoriamente un Valor de un Conjunto. El problema que me encuentro es que si el rango en cuestión contiene celdas en blanco, entonces, la solución que propones devuelve valores cero. Me gustaría saber si existe la forma de que evite dichas celdas en blanco y elija un valor entre aquellas que contienen número".

Para solucionar esta variación sobre el caso visto en el post Seleccionar Aleatoriamente un Valor de un Conjunto, vamos a generar una tabla auxiliar que nos permita "apartar" las celdas que se encuentran vacías para que no se puedan generar valores cero en el resultado. Partimos del siguiente ejemplo:

Queremos obtener aleatoriamente un valor de esta lista pero sin considerar las celdas en blanco. En la solución proporcionada en la primera versión de este problema, generamos un número aleatorio de posición para que excel me devuelva el valor existente en dicha celda. Es decir, si mi lista tiene 20 valores, genero un número aleatorio entre el 1 y el 20 y le pido a excel que vaya a la celda del número obtenido, por ejemplo la 4, y me devuelva el valor de dicha celda. Aplicando dicha solución a esta lista, nos podríamos encontrar con que en la cuarta celda de la lista la celda esté vacía (obtendríamos valor cero).

Lo primero que hacemos es seleccionar el rango B5:B18 y le damos el nombre de Valores. A continuación generamos una nueva lista auxiliar de dos columnas, cuya primera columna es una serie de número de orden de menor a mayor (en este caso del 1 al 14, ya que tenemos 14 datos):

Nos situamos en la celda G5 y escribimos la fórmula:

=K.ESIMO.MAYOR(valores;F5)  y copiamos hasta G18

De esta manera hemos conseguido generar una nueva lista ordenada de mayor a menor con los valores de nuestra lista original pero ahora ya no tenemos celdas en blanco por el medio del rango, ya que hemos conseguido que se "vayan al final" de nuestra nueva lista y que se muestren como error del tipo #¡NUM!

Esto nos permite ahora, aplicando la función CONTAR, calcular cuántas celdas de mi nueva lista contienen un valor númerico. Si calculamos  =CONTAR(G5:G18) nos devolverá el resultado 10, porque es el número de valores existentes en dicho rango de 14 celdas (las otras 4 contienen el error #¡NUM!). De esta manera ya sé que debo generar un número aleatorio entre 1 y 10. Por seguir trabajando con nombres de rango, seleccionamos G5:G18 y le creamos el nombre NewLista.

Nos situamos en la celda D10 y escribimos la fórmula definitiva:

=DESREF(G4;ALEATORIO.ENTRE(1;CONTAR(NewLista));)

Cada vez que pulsemos la tecla F9, excel recalculará un valor aleatorio y lo mostrará en la celda D10. Una fórmula alternativa en D10 sería utilizar la función INDICE:

=INDICE(NewLista;ALEATORIO.ENTRE(1;CONTAR(NewLista))) 

Intercalar 1 Fila en Blanco cada n Filas (sin macros)

"Necesito dejar una fila en blanco cada 4 filas, es decir, que aparezcan 4 registros y en el quinto que incorpore una fila en blanco, y así sucesivamente hasta un total de 5.000 registros ¿Hay alguna manera de hacerlo sin hacer uso de macros?".

Para evitar solucionarlo de manera manual, lo que nos llevaría bastante tiempo, existe una manera razonablemente sencilla y razonablemente automática y que no requiere de programación. Partimos de una tabla con distintos datos. En nuestro ejemplo utilizaremos 50 registros:

Vamos a manejar dos hojas dentro del archivo para mayor seguridad. La tabla de la imagen la tenemos en la hoja 2. Lo primero que debemos hacer es crear un número de orden para los registros. Para ello escribimos un 1 en A3, un 2 en A4 y seleccionamos ambos (A3:A4). Hacemos doble clic en la parte inferior derecha del rango seleccionado y de esta manera rellenamos la serie hasta el último dato. 

Nos vamos ahora a la HOJA1. Preparamos la siguiente entrada de datos:

En D1 introducimos el número de fila que debe quedar en blanco. Si queremos dejar en blanco la quinta fila de nuestra tabla escribiremos un 5. En D2 introducimos el valor del último dato de nuestra tabla de la HOJA2. En nuestro ejemplo hay 50 registros y, por lo tanto, el valor será 50.

En E4 vamos a calcular cuántas filas se van a insertar en total. La fórmula es:

=SI(RESIDUO(D2;D1-1)=0;(D2/(D1-1))-1;TRUNCAR(D2/(D1-1)))

En E5 calculamos cuál será el último número de nuestra nueva lista. La fórmula es:

=E4+D2  es decir, el número de filas inicial más las que se van a insertar.

Ahora a partir de la celda C8 generamos nuevamente la serie comenzando con el número 1 y acabando con el 50 (igual que hicimos en la hoja 2).

En D8 escribimos un 1. En D9 la siguiente fórmula:

=SI(RESIDUO(D8+1;$D$1)=0;D8+2;D8+1)  y hacemos doble clic para completar la Nueva Lista.

En E8 la siguiente fórmula:

=SI($D$1*C8<=$E$5;$D$1*C8;"- - -")   y hacemos doble clic para completar la Lista de Múltiplos. El resultado será el siguiente:

Copiamos la Nueva Lista y la pegamos COMO VALORES en la HOJA2 encima del rango A3:A52  y obtenemos lo siguiente:

Ya estamos terminando... Ahora volvemos a la hoja 1 y seleccionamos el rango que contiene números en nuestra Lista de Múltiplos. En nuestro caso el rango E8:E19 de la HOJA1. Pulsamos Copiar y vamos a la HOJA2 y lo pegamos COMO VALORES a continuación del último dato, es decir, a partir de la fila 53.

Ya sólo nos queda ir a Ordenar y filtrar y seleccionar Orden Personalizado. En la ventana que se abre seleccionamos Ordenar por la Columna A de menor a mayor:

Aceptamos y... problema resuelto! (podríamos ahora borrar los número de orden generados en la hoja2 en la columna A):

Aunque pueda parecer un poco tedioso, una vez realizada la plantilla no lleva más de 30 segundos resolver cada caso que se nos plantee.

Copiar Registros Intercalados

"Tengo cientos de registros relativos a números de unidades vendidas y debajo de cada registro el porcentaje que representa cada uno respecto a la suma total. Necesito copiar un listado pero sólo de las unidades vendidas sin dejar filas intercalas en el medio". 

Tal y como les prometí, este post va dedicado a mis alumnos del Master in Management del IE Business School, que con tanta paciencia me soportan los lunes y martes...

Partimos del siguiente ejemplo:

Lo que queremos conseguir es hacer una sola fórmula que podamos copiar hacia abajo para obtener un listado como el que aparece en la siguiente imagen con las cantidades de cada dato:

Para ello vamos a utilizar varias funciones, a saber: INDIRECTO, DIRECCION, FILA y COLUMNA.

Nos situamos en la celda G4 y escribimos la siguiente fórmula que copiaremos hasta G11:

=INDIRECTO(DIRECCION(FILA(C4)*2-4;COLUMNA(C4)))

La función FILA nos devuelve el número de fila de la celda en cuestión. En nuestro ejemplo FILA(C4) nos devolverá el valor 4. multiplicamos el resultado por 2 para generar una serie de números pares. FILA(C4)*2=8; FILA(C5)*2=10; FILA(C6)*2=12; etc. Como nuestro primer dato se encuentra en la fila número 4 (y no en la 8) tendremos que restarle 4 para que la serie comience en dicho número, en 4: FILA(C4)*2-4=4; FILA(C5)*2-4=6; FILA(C6)*2-4=8; etc. Nuestro datos precisamente se encuentran en las filas 4, 6, 8, etcétera. La columna es siempre la misma COLUMNA(C4). Si colocamos estas funciones dentro de la función DIRECCION, lo que obtendremos es la dirección de las celdas en cuestión, a saber:

=DIRECCION(FILA(C4)*2-4;COLUMNA(C4)) resulta C4

=DIRECCION(FILA(C5)*2-4;COLUMNA(C5)) resulta C6

=DIRECCION(FILA(C6)*2-4;COLUMNA(C6)) resulta C8

=DIRECCION(FILA(C7)*2-4;COLUMNA(C7)) resulta C10, etcétera.

De esta manera ya tenemos las direcciones de las celdas que queremos obtener. Sólo nos falta utilizar una función que transforme el nombre de la celda en el valor que contiene la misma. Esta función es INDIRECTO. 

Aunque el problema ya está resuelto, nos podríamos encontrar un pequeño problema y es que si ahora añadimos filas por encima de la fila 4 entonces dejará de funcionar. Para evitar esto podemos hacer uso del siguiente "truco". Creamos un nombre para el primer dato. Nos situamos en la celda C4, vamos a la lista de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre, por ejemplo, Dato1 y pulsamos Enter. A partir de ahora la celda C4 se llama Dato1. Nos situamos en G2 y escribimos la fórmula:

=FILA(Dato1)

De esta manera obtendremos el número de fila en el que se encuentra el primer dato  de forma variable. Podemos ahora incorporarlo a nuestra fórmula original y problema resuelto:

=INDIRECTO(DIRECCION(FILA(C4)*2-$G$2;COLUMNA(C4)))