Uso de NOMBRES (NAMES) en Excel – Validación de Datos

viernes, febrero 24, 2006

En una nota anterior he hablado de las ventajas de usar NOMBRES en Excel. Hemos mostrado que los NOMBRES no sólo pueden servir para identificar rangos (lo que he llamado "nominar" rangos) sino que también pueden contener fórmulas.
Una demostración sobre este tipo de uso de NOMBRES puede verse en esta nota sobre Formato Condicional, y también en esta nota sobre valores #N/A.
Hoy veremos otro uso de como esta funcionalidad nos permite sobreponernos a algunas limitaciones de Excel.
En muchos de los modelos que desarrollo para mis clientes uso Validación de Datos. Esta funcionalidad permite controlar el tipo de datos permitidos.





Por ejemplo podemos establecer que sólo valores enteros entre 10 y 100 sean válidos para un rango determinado.



La variante que yo utilizo en muchos de mis modelos es "Lista"



Cuando usamos esta variante, en cada celda del rango se abre una lista de valores de la cual. Cualquier valor que no figure en la lista no será aceptado por Excel.

El problema con esta variante reside en que Excel aceptará solamente listas que sen encuentren en la misma hoja de la celda que queremos formar. Por ejemplo, no podemos usar una lista que se encuentre en Hoja2 para formar las celdas en Hoja1.
La solución es definir el rango de la lista como NOMBRE. Veamos esto con un ejemplo.
Supongamos que en Hoja2 tenemos una lista de precios:


Y en Hoja1 un formulario para calcular ofertas:



La idea es utilizar Validación de Datos para generar una lista de la cuál se elegirán los productos. El problema, como ya dijimos, es que Excel no permite utilizar rangos que se encuentren en otras hojas. Y nuestra lista de precios se encuentra en la Hoja2.

Para solucionar este problema hacemos lo siguiente:
1 – entramos en la hoja dos y seleccionamos el rango con los números de catálogo de los productos. En el cuadro nombres introducimos el nombre "productos".


2 – En la Hoja1 seleccionamos el rango A7:A12 y entramos en el menú de Validación de Datos. Elegimos la opción "lista".



3 – Pulsamos la ventanilla "origen" e inmediatamente el botón F3, lo que nos permitirá elegir uno de entre los nombre que hemos creado. Elegimos "producto" y pulsamos "aceptar".



4 – A partir de este momento cada vez que seleccionemos una celda en el rango A7:A12, veremos un pequeño triángulo. Si pulsamos este triángulo se abriera una lista que refleja los productos que se encuentran en la Hoja2.



En las celdas del rango B7:B12 utilizamos la fórmula


=SI(ESBLANCO(A7),"",BUSCARV(A7,lista_de_precios,2,0))

de esta manera, cuando elegimos una valor de la lista, automáticamente aparecerá el precio correspondiente.


Como ven, también hemos creado un NOMBRE que contiene el rango A2:B12 en la Hoja2, es decir el catálogo de productos con sus precios.
La función ESBLANCO nos sirve para evitar valores #N/A cuando no hay números de catálogo en la columna A de la Hoja1.



Categorías: Funciones&Formulas_, Manejo de Datos_, Varios_


Technorati Tags:
,

52 comments:

OSCAR JAIME,  19 febrero, 2007 03:58  

SENCILLO PERO BIEN EXPLICADO.
GRACIAS

Anónimo,  03 abril, 2007 18:56  

Por favor necesito ayuda!!!
No puedo hacer que aparesca el precio con la formula que esta explicada me marca #¿NOMBRE? que puedo hacer?
gracias por tu tiempo

Jorge L. Dunkelman 04 abril, 2007 19:50  

Hola, el error #¿NOMBRE? se debe por lo general a que Excel no reconoce el nombre de la función o el nombre que contiene el rango.
Debes revisar cómo has escrito las fórmulas.
También puedes enviarme el archivo para ver cuál es el problema.

Alberto 10 julio, 2007 05:53  

Estimado, he leido el blog y me parece muy bueno, pero tengo un pequeño inconveniente con una validación que no me doy cuenta como implementar y tal vez me puedas ayudar.
Quiero hacer un libro de banco con varias columnas por supuesto, pero lo que importa es que quisiera poder hacer validación de los nros de cheques que se ingresan, siempre y cuando el comprobante emitido sea un cheque, o sea, si por una casualidad coincide un nro. de cheque con un comprobante de otro tipo no me interesa si está repetido, pero sí si el comprobante es un cheque. Espero haber sido claro, si podes ayudarme un poquito, gracias.

Jorge L. Dunkelman 10 julio, 2007 20:54  

Hola Alberto,
Excel no puede "saber" si un número de comprobante corresponde a un cheque o no, excepto que uses un rango único de números para los cheques o que en una columna auxiliar se encuentre la información. Por ejemplo en la columna C están los números de comprobantes y en la columna D el tipo de comprobante. En este último caso crearías un formato condicional con la función usando como criterio el valor de la fila en la columna D.

Fernando,  25 agosto, 2007 22:08  

Hola Jorge, tengo un problema. A ver si se explicarlo y si es posible hacerlo.
Tengo una lista desplegable en una celda con datos de una lista de otra hoja, esto lo hecho con validación de datos y con la función INDIRECTO. En otra celda he puesto una fórmula de manera que aparece un valor asociado al dato introducido en la lista desplegable. A su vez ese valor lo utilizo para calcular otro valor en otra celda. Este último valor calculado sólo puede estar entre un cierto rango de valores. Lo que quiero es que al seleccionar un dato de la lista desplegable me aparezca un mensaje de error (como con validación de datos) cuando el valor de la otra celda no esté dentro del rango.
El problema es que no puedo hacerlo por validación de datos porque ya utilizo esto para hacer la lista desplegable. Si lo hago con validación de datos en la celda donde calculo el valor, no salta el mensaje porque el valor está calculado mediante una fórmula, no introducido.
No se si me he explicado con claridad. Gracias de antemano y felicidades por el blog.

Jorge L. Dunkelman 27 agosto, 2007 20:12  

Así es, la limitación de validación de datos es que no responde cuando el valor es copiado o producto de una fórmula.
Una solución posible es imitar el proceso de validación de datos con un evento en la hoja. Eventos son uns especie de macro que se dispara cuando algo específico ocurre en la hoja a la cual el evento esta relacionado.

Anónimo,  13 septiembre, 2007 22:23  

Hola.. mi duda es la siguiente..

Como hacer.. cuando para un valor de una lista hay a su ves varios valores posibles..

Es decir necesito una lista q cuando seleccione un valor, aparesca en la celda siguente otra lista relacionada a ese valor.

Tengo una lista con productos.. pero para cada producto hay distintos tipos..

Jorge L. Dunkelman 04 octubre, 2007 18:17  

Me parece que la técnica que propongo en esta nota te podría ayudar.

Anónimo,  27 noviembre, 2007 01:53  

Muchas GRACIAS!!!! hace 4 dias que estoy renegando con un menu desplegable donde los datos estan en otra hoja!!!

Marko 24 marzo, 2008 18:38  

ESTIMADO AMIGO REALICE TODOS LOS PASOS INDICADOS Y DE MARAVILLA HASTA ESCRIBIR LA PRUEBA LóGICA =SI(ESBLANCO(A7),"",BUSCARV(A7,lista_de_precios,2,0)) ,SENCILLAMENTE NO ME FUNCIONA USO EXCEL 2002, DE IGUAL MANERA USO LA FUNSION BUSCARV PERO NO LOGRA DAR CON

Jorge L. Dunkelman 24 marzo, 2008 23:47  

Marko
mándame el archivo para que vea dónde está el problema.

irving_fernando 15 abril, 2008 11:32  

Q TAL SIGUIENDO TUS EJEMPLOS LOGRE CREAR UNA LISTA, PERO SOLO ME DA HASTA 50 PRODUCTOS, PODRE CREARLA DE MAYOR CAPACIDAD???

Jorge L. Dunkelman 15 abril, 2008 17:52  

La cantidad de valores en la lista desplegable depende sólo del tamaño del rango que los contiene.
Fíjate si tienes algún error en la definición.

Carlos,  28 julio, 2008 06:57  

Hola esto lo andaba buscando seras que puedes subir el archivo y poner el link si puedes grax

Jorge L. Dunkelman 20 agosto, 2008 22:31  

Hola Carlos

sucede que no he guardado el archivo y estoy con poco tiempo disponible para rehacerlo. Tal vez dentro de unos días.

Anónimo,  22 septiembre, 2008 19:24  

Hola Jorge. ¿Es posible que en la entrada de validacion de datos no tengas que escribir completo todos los dígitos (p.e. la referencia completa) sino solo una parte como en cuadro combinado, que conforme vas escribiendo aparecen las referencias que van comenzando por los dígitos escritos?. Gracias.

Jorge L. Dunkelman 23 septiembre, 2008 21:12  

El tema de los valores que se van "autocompletando" en listas desplegables es una de las consultas que más recibo. Prometo publicar una nota sobre el tema.

Anónimo,  29 septiembre, 2008 18:03  

Deseo crear una lista desplegable en un formulario desplegable pero en el cuadro propiedades no me aparece ListFillRange...cómo debo usar correctamente este comando?

Jorge L. Dunkelman 01 octubre, 2008 00:11  

Qué control estás usando? La propiedad ListFillRange pertenece a algunos controles ActiveX (cuadro de lista, cuadro combinado), como puedes ver en esta nota.

Gastón 22 octubre, 2008 00:54  

Me acabas de salvar la vida. Muchas gracias!

Anónimo,  12 diciembre, 2008 15:20  

Me parecio excelente la nota y la pagina! FElicitaciones! TEngo para hacerte una pregunta respecto de este tema. Como hago que los rangos sean dinamicos? Seguindo tu ejemplo, quiero que al agregar un producto este me aparezca en el menu desplegable automaticamente. Como realizo esto? DEsde ya muchas GRacias
Diego

Anónimo,  31 diciembre, 2008 02:33  

Hola Jorge! realicé el ejercicio que propones para validar datos y quedo excelente el problema que tengo es que realice el ejercicio en un mismo libro (como es el ejemplo que pones) lo que yo quiero es en un libro tener los datos de origen y en otro diferente poner la lista desplegable correspondiente a esos datos es posible?

Jorge L. Dunkelman 04 enero, 2009 22:28  

No, no es posible usar nombres definidos en cuadernos remotos.

Anónimo,  05 enero, 2009 19:39  

ok gracias entonces cual sería la opción

Jorge L. Dunkelman 05 enero, 2009 21:01  

Habría que analizar la situación, pero me parece que lompas racional es poner los datos de referencia en una hoja del cuaderno donde quieres usar la referencia. No siempre es razonable poner datos en cuadernos separados. Por ejemplo, puedes tener un cuaderno por año para llevar estadísticas de ventas. Pero lo m´´as eficiente es tener los datos en un solo cuaderno (y tal vez también en una sola hoja).

Marcelo Alvarez,  05 febrero, 2009 19:43  

Se me ocurre que vinculando datos de otro libro en una hoja del libro actual tendríamos la base de datos y con ella atender la lista desplegable?

Yosh,  20 febrero, 2009 07:01  

Hola bueno me parece muy interesante y de gran utilidad este materia pero cuando llego a la formula me arroja un error y no he podido hacer q el precio salga el nombre del producto q coloque Q puedo hacer?

Jorge L. Dunkelman 22 febrero, 2009 19:34  

Mandarme el archivo para que pueda ver dónde está el problema.

Anónimo,  18 septiembre, 2009 16:04  

Estimado Jorge tengo problemas no puedo en la validación de datos poder aplicar la lista me dice que no puedo hacerlo desde otra hoja, que hay que hacer?

Jorge L. Dunkelman 19 septiembre, 2009 09:54  

De eso se trata justamente la nota. Tienes que definir el rango de la lista (que se encuentra en otra hoja) dentro de un nombre y usar el nombre en la opción Lista de Validación de Datos.

Daniel,  06 octubre, 2009 16:55  

Jorge, genial tu blog!! es de grandísima ayuda!
Tengo una duda/problema al tener rellenadas 2 celdas con la validación de datos, como hago para que al modificar la primera de ellas, la segunda pierda el valor que tiene, es decir que me limpie el dato que hay, puesto que este no esta en su lista.

Jorge L. Dunkelman 06 octubre, 2009 19:46  

Daniel
en la nota sobre listas desplegables dependientes puse en uno de los comentarios esto>


1 - en la pestaña Hoja1 haces clic con el botón derecho del mouse y abres Ver Código
2 - en el módulo de la hoja pones este código

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A5"), Target).Address = Range("A5").Address Then
Range("B5").ClearContents
End If
End Sub

Lo que hace este evento es que cada vez que hay un cambio en el valor de A5 (el país) el contenido de la celda B5 es borrado.

Anónimo,  09 abril, 2010 19:30  

Buenos días Jorge, desde que descubri tu blog tengo una visión muy distinta de Excel, de verdad muchisimas gracias por toda la ayuda que nos brindas a los novatos en el tema.
Tengo unas dudas en las cuales espero poder recibir tu ayuda:
1. ¿Como puedo bloquear una celda para que el usuario no la modifique y que sólo se actualice al seleccionar un valor en otra celda con lista desplegable?
2. ¿Como puedo bloquear la modificación de una celda al llegar a un número en particular? Es decir si la celda tiene el valor de 100% que ya no se pueda modificar
3. ¿Es posible que 2 listas desplegables se actualicen al seleccionar un valor de otra lista desplegable? Es decir, tengo una lista desplegable que al seleccionar un valor se muestran las opciones en otra lista desplegable, lo que requiere es que se muestren 2 listas desplegables con diferentes valores pero asociados al valor inicial.

Espero que no esten confusas mis dudas y de verdad espero contar con tu ayuda.

De antemano muchisimas gracias por la ayuda brindada, y nuevamente muy agradecida por tu gran blog.

Saludos,
Mari.

Jorge L. Dunkelman 09 abril, 2010 23:11  

Hola Mari,
1 - bloqueando la celda con Herramientas-Proteger Hoja y ligando la celda a la que contiene la lista desplegable (supuestamente tiene que haber una fórmulas que usa la celda de la lista desplegable como referencia)
2 - Con Validación de Datos
3- Si, fijate en mi blog en las notas sobre listas desplegables dependientes.

Gracias por los conceptos y me alegro que el blog te resulte útil.

Anónimo,  10 abril, 2010 05:47  

Buenas noches Jorge, gracias por tus respuestas y en tan corto tiempo, pero creo que no quede clara y que no fui del todo clara describiendo mis dudas, te comento:

1. En este punto no entiendo a que te refieres con ligar la celda con la lista desplegable; y sí la celda tiene una formula que depende del valor seleccionado en la lista desplegable

2. Intente hacerlo con validación de datos pero no di con la formula

3. Ya he consultado la nota que me indicas,incluso con ella entendi como crear las listas que necesitaba, solo que lo que necesito es mostrar 2 listas al mismo tiempo dependiendo de un valor seleccionado, me crea conflicto porque el nombre de las las listas dependientes debe ser igual del valor que las referencia, pero 2 listas no pueden tener el mismo nombre.

Disculpa mi ignorancia en la materia pero como te indique soy nueva y ando aprendiendo un poco.

Nuevamente gracias por toda tu ayuda y por tu blob.

Mari.

Jorge L. Dunkelman 10 abril, 2010 09:21  

Mari,
seguiremos la charla por mail privado. Fijate en el enlace Ayuda (en la parte superior del blog)

Anónimo,  21 diciembre, 2010 17:15  

Buenas tardes,

he hecho una función de visual basic con la que cálculo la letra del nif.
Ahora me gustaría que en lugar de tener que pasar-le a la función los datos a revisar, esta se ejecutara directamente con la validación de datos.

¿es posible hacer que la validación de datos utilice mi funcion? En caso afirmativo ¿como debería hacerlo?


Muchas gracias

Jorge L. Dunkelman 21 diciembre, 2010 18:33  

Excel no permite usar UDF (funciones definidas por el usuario) en validación de datos. La alternativa es simular la validación usando el evento Worksheet_Change, por ejemplo.

Anónimo,  16 enero, 2011 18:14  

Hola Jorge
Por favor su ayuda, tengo la lista creada con la opcion "validacion de dato" y solo necesito saber como hacer que al escribir una o varias letras me ubique automaticamente en la referencia deseada dentro de la lista, esto para ahcer mas facil la busqueda de valores dentro de la lista.
Agradecere mucho su ayuda.

Jorge L. Dunkelman 17 enero, 2011 06:07  
Este comentario ha sido eliminado por el autor.
Federico García del Real Caballero 25 enero, 2012 19:15  

Hola Jorge,

Antes de nada darte la enhorabuena por el blog que lo he descubierto hoy mismo y llevo todo el día cacharreando con Excel gracias a ti.
Tengo una duda que no se muy bien como hacer. Estoy intentando hacer presupuestos dinámicos, que cojan los precios de una matriz.
Esta matriz tiene por un lado el tipo de producto y por otro la cantidad, y el precio son los valores que dependen de ambos datos.

En el presupuesto hay dos columnas, CANTIDAD (sin validación, se podría poner cualquier dato) y PRODUCTO (Elegido de una lista). En base a estos dos valores quiero que aparezca el precio. He conseguido con la función DESREF y COINCIDIR que aparezca el precio en función del producto elegido, pero no se como condicionarlo a la cantidad.

Espero que me puedas ayudar.

Un cordial saludo,

Federico

Jorge L. Dunkelman 25 enero, 2012 20:28  

Siguiendo tu descripción (tipo de producto en la primer columna de la matriz y cantidad en la primer fila, es así?) tendrías que usar INDICE combinada con COINCIDIR. La función COINCIDIR la usarías dos veces, una para encontrar la fila (tipo de producto) y la otra para la columna (cantidad).

Anónimo,  15 mayo, 2012 00:56  

Jorge estoy trabajando con excel 2010 y revisando vi que el nombre de la función BUSCARV no figura en la lista sino que excel cambio el nombre por el de CONSULTAV, con la función en la celda de precio me salía así #¿NOMBRE? ahora que lo cambié a CONSULTAV me sale #N/A. Igual no logro que me salga el precio. cual es el rango del nombre lista_de_precios??? gracias

Jorge L. Dunkelman 15 mayo, 2012 16:07  

En la primera entrega de Excel 2010 Microsoft "rebautizó" varias funciones. Entre ellas BUSCARV que se convirtió en CONSULTAV. Más adelante Microsoft sacó un Service Pack que restauró los viejos nombres.
El nombre se refiere al rango que contiene la lista de precios (en el ejemplo de la nota A2:B14).

seba muñoz 18 agosto, 2013 22:45  

Hola Jorge,

Me gustaría saber si es posible hacer que en una columna solo se escriban ciertas cosas, por ejemplo en la columna c5 hasta c400
solo se puedan ingresar: pala, perro, taza... etc, y si por alguna razón alguien escribe otra cosa no le permita escribirlo.

No sé si hay que tener alguna base de datos donde salgan las palabras que solo se deben escribir en las celdas c5:c400.

Atte,

Sebastian.

Jorge Dunkelman 19 agosto, 2013 07:08  

Sebastián, exactamente para eso existe la validación de datos (el tema de la nota). Tenés que usar Validación de Datos--Lista y apuntar al rango que contiene los valores permitidos (o usar un nombre definido que se refiera a ese rango).

Carlos Salinas M. 21 agosto, 2013 17:50  

hola quisiera saber si puedo ponerle nombre a un rango de celdas pero poner como nombre numeros

Carlos Salinas M. 21 agosto, 2013 17:56  

hola quisiera saber si puedo usar números al momento de asignarle nombre a un rango de celdas,
tengo una lista dependiente de otra pero la primera es un listado de números, gracias..

Jorge Dunkelman 22 agosto, 2013 10:42  

Los nombres definidos tiene que empezar con una letra, un especio, un underscore (_) o una barra invertida (\).
En tu caso podría ser _12345 por ejemplo.
Luego, para poder usarlo en al lista dependiente tendrás que transformarlo nuevamente en número usando la función SUSTITUIR combinándola luego con INDIRECTO.
Puedes ver un ejemplo en esta nota.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP