lunes, 17 de marzo de 2014

Validación de datos sobre un rango con celdas vacías.

Atacaremos hoy un tema bastante recurrente, conseguir una Validación de datos sobre un rango de celdas con algunas de ellas sin valores o vacías.
Esta explicación da respuesta a la cuestión planteada por un lector:

...Yo selecciono el rango para la lista A1:A5, pero solo existen datos en las celdas A1, A3 y A5, hay alguna forma de que al momento de desplegar la lista solo me muestre las celdas con valores y excluya las celdas vacías (A2 y A4)?...


La respuesta que daremos, probablemente no sea la más directa, pero al menos conseguiremos nuestro objetivo, que es conseguir una validación de datos sin esas celdas vacías.
Partimos de la situación propuesta por el lector:

Validación de datos sobre un rango con celdas vacías.



La idea es clara, mostrar una Validación de datos únicamente con los valores de las celdas con datos.
Para ello generaremos un primer Nombre definido con fórmula:
lista =SI(Hoja1!$A$1:$A$5="";Hoja1!$A$1;Hoja1!$A$1:$A$5)
con el que conseguimos un 'rango virtual' donde las celdas vacías han sido reemplazadas por el valor de la primera celda del rango (celda A1).
No es necesario, pero si quisiéramos visualizar ese rango podríamos ejecutar sobre nuestra hoja de cálculo, en C1:C5 lo vemos:

Validación de datos sobre un rango con celdas vacías.


Creamos un segundo Nombre definido que empleará el anterior. Con este Nombre definido, conseguimos un listado de registros únicos:
listado =SI.ERROR(INDICE(lista;K.ESIMO.MENOR(SI(COINCIDIR(lista;lista;0)=FILA(INDIRECTO("1:"&FILAS(lista)));COINCIDIR(lista;lista;0);"");FILA(INDIRECTO("1:"&FILAS(lista)))));"")
En este caso SÍ es necesario mostrarlo, ejecutándola matricialmente (al igual que la anterior) en el rango E1:E5 veríamos:

Validación de datos sobre un rango con celdas vacías.



El último paso, nuevamente generando un Nombre definido con la siguiente fórmula:
ListFinal =DESREF(Hoja1!$E$1;;;CONTARA(Hoja1!$E$1:$E$5)-CONTAR.BLANCO(Hoja1!$E$1:$E$5);1)
Con éste conseguimos tener un rango dinámico que se adapta exactamente al número de elementos únicos a mostrar.


En la celda E8 configuramos una Validación de datos tipo Lista con referencia a nuestro último Nombre definido: '=ListFinal':

Validación de datos sobre un rango con celdas vacías.



Nuestra misión se ha conseguido, al desplegar sobre la celda validada E8 veremos exclusivamente los valores de las celdas con datos del rango A1:A5:

Validación de datos sobre un rango con celdas vacías.



El único inconveniente es que necesariamente debemos reflejar en la hoja de cálculo el rango referido a listado.. pero no hay nada perfecto, verdad?
;-)

8 comentarios:

  1. Quiero felicitarte por el Excelente manejo de las funciones, principalmente por las matriciales. En mi opinión con darle un ordenar para eliminar los espacios vacíos es otra opción rápida, ya vez que el trabajo siempre es para ayer.
    Saludos.

    ResponderEliminar
    Respuestas
    1. Muchas gracias!!
      ;-)
      es cierto que bastaría con un ordenar.. pero, por desgracia no siempre es posible, por ejemplo, por que sea parte de un Informe estructurado, etc...

      Un cordial saludo!!!

      Eliminar
  2. hola, necesito algo muy similar a lo explicado, osea, tengo una lista que aumenta dia a dia con la siguiente informacion: codigo producto, descripcion, situacion, cliente, nº guia, esto en hoja 1.
    necesito que en hoja 2 me liste todos los productos que cumplan la condicion de "adquirir" por su estado de stock 0.

    ResponderEliminar
    Respuestas
    1. Hola,
      lo más sencillo es que construyas una Tabla dinámica sobre el origen de datos (asegúrate que previamente lo has convertido en Tabla), aplicando al campo en cuestión que te indique esa condición (no tengo claro cuál es) el filtro correspondiente.

      También podrías aplicar un filtro avanzado con el mismo criterio de la Tabla dinámica, sobre el campo en cuestión (sea cual sea)

      Slds cordiales

      Eliminar
  3. hola sr ismael romero, que buen aporte, estoy agradecido por su trabajo, espero me ayude con el problema al definir como matriz la ecuacion para lista el resultado este o no en blanco la celda es igual al primer valor del rango encambio sin usar matriz cumple con el cambio desde celda en blanco a valor de la primera celda, despues para el listado no eh conseguido funcione su formula, gracias por su atencion

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      simplemente revisa los pasos y asegúrate de ejecutar matricialmente (presionando Ctrl+Mayus+Enter en lugar de Enter) las fórmulas en que así se indica...
      Si aún así, tras tu comprobación, sigues sin verlo, envíamelo a:
      excelforo@gmail.com

      Un saludo

      Eliminar
    2. sr ismael, creo el problema esta en la creacion del nombre existen diferentes formas de crearlo y no conosco la correcta, gracias

      Eliminar
    3. Hola Diego,
      revisa las entradas de la categoría:
      http://excelforo.blogspot.com.es/search/label/Asignar%20nombres%20a%20rangos

      Bastará que vayas a la ficha Fórmulas>grupo Nombres definidos> botón Asignar Nombre

      Saludos

      Eliminar