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:
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:
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:
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:
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':
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:
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?
;-)
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:
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:
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:
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':
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:
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?
;-)
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.
ResponderEliminarSaludos.
Muchas gracias!!
Eliminar;-)
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!!!
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.
ResponderEliminarnecesito que en hoja 2 me liste todos los productos que cumplan la condicion de "adquirir" por su estado de stock 0.
Hola,
Eliminarlo 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
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
ResponderEliminarHola Diego,
Eliminarsimplemente 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
sr ismael, creo el problema esta en la creacion del nombre existen diferentes formas de crearlo y no conosco la correcta, gracias
EliminarHola Diego,
Eliminarrevisa 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