4 ejemplos de utilización de validación de datos con fórmulas
Ya sabes que la herramienta de Validación de datos es de gran utilidad para controlar y restringir la introducción de datos y así, asegurarte de que el libro de Excel funcione como debería.
El poder seleccionar los datos mediante un cuadro de lista, limitar la cantidad de caracteres o evitar la introducción de valores poniendo topes máximos y mínimos son opciones fácilmente configurables que permiten de forma rápida establecer los criterios que deseas. Pero la opción verdaderamente potente en el uso de la validación de datos es la posibilidad de utilizar fórmulas para restringir los valores introducidos en las celdas.
Te muestro varios ejemplos de utilización de la Validación de datos con fórmulas:
Validación de datos con fórmulas para introducir sólo valores únicos
Un uso muy común pero útil de la validación con fórmulas, se da a la hora de evitar la introducción de datos duplicados, ya que se trata de un error bastante habitual. Por ejemplo, si te encuentras creando un registro de proveedores y cada uno de ellos tiene un código único que le identifica dentro de la empresa, puedes configurar la herramienta de Validación de datos para que al introducir por segunda vez el mismo código, aparezca un mensaje de error. ¿Te imaginas la de errores que puedes evitar?
Para aplicar esta validación de datos en un rango de celdas o en una columna completa, sigue los siguientes pasos:
- Selecciona el rango de celdas que quieres validar. En este caso, selecciona la columna A completamente.
- Selecciona el botón Validación de datos que se encuentra en el grupo Herramientas de datos de la ficha Datos.
- En el cuadro de diálogo Validación de datos que aparece, selecciona la ficha Configuración, y en el cuadro desplegable Permitir, pulsa en la última opción (Personalizada).
En el cuadro Fórmula debes introducir la función CONTAR.SI, que se utiliza para contar el número de veces que aparece un valor especificado como condición en un argumento. Para este ejemplo, escribe la fórmula =CONTAR.SI(A:A;A1)=1, donde A:A es el rango de celdas donde se aplicará la validación, y A1 corresponde a la referencia de la primera celda del rango seleccionado. Después del paréntesis de cierre, se introduce =1, es decir, que el recuento de las celdas del rango A:A no puede tener un resultado diferente a 1, porque significaría que se ha introducido el valor dos veces.
Validación de datos con fórmulas para exigir valores únicos de 7 dígitos
El tipo de datos personalizado (con fórmulas), es el único que te permite establecer dos o más criterios de validación con una sola regla.
Si quieres rizar el rizo con el ejemplo anterior, puedes obligar al usuario a que, además de introducir un valor que no se haya introducido antes, éste tenga una longitud de siete dígitos (ni seis ni ocho).
Siguiendo el ejemplo, en el paso 5 debes introducir la siguiente fórmula:
=Y(ESNUMERO(A2);LARGO(A2)=8;CONTAR.SI($A$2:$A100;A2) <=1)
Como ves en la fórmula anterior, puedes introducir más de una validación: la función CONTAR.SI evita la introducción de duplicados, la función ESNUMERO evita la introducción de texto y LARGO exige que el valor introducido contenga exclusivamente números.
Validación de datos con fórmulas para permitir sólo números pares
Siguiendo con el ejemplo anterior, también es posible establecer que solamente se puedan introducir como código del proveedor, números pares. Para hacerlo sigue los tres primeros pasos del ejemplo anterior, pero en el cuadro Fórmula, debes escribir la fórmula =RESIDUO(A2;2)=0. Esta función calcula el resto de la división del código del proveedor entre dos. Si el resto es cero, significa que el número es par.
Validación de datos con condiciones
Si necesitas controlar la introducción de datos dependiendo de los valores de otras celdas, puedes crear diferentes tipos de listas en los que los elementos de una lista dependan directamente del valor seleccionado de otra lista. Observa el ejemplo:
En una agencia de alquiler de coches, existen tres marcas disponibles: Ford, Renault y Toyota, cada una con sus respectivos modelos diferentes.
En primer lugar, debes crear una tabla como muestra la siguiente imagen:
Después crea un nombre de rango para cada una de las marcas y modelos. Por ejemplo, selecciona los modelos de la marca Ford (solo sus modelos) y escribe la palabra Ford en el Cuadro de nombres. Repite el procedimiento para cada una de las marcas.
A continuación, selecciona la celda F4 donde se ubicará la validación de la marca. Pulsa en el botón Validación de datos y en la sección Permitir, elige la opción Lista. En el cuadro Origen, escribe o selecciona el rango de celdas que contiene solamente las marcas. En este caso será =$A$1:$C$1. Cuando lo hagas, verás que en la parte derecha aparece un pequeño cuadro con un triángulo que al pulsarlo se despliegan las opciones seleccionadas.
Ahora selecciona la celda F5 y abre de nuevo el cuadro Validación de datos en la ficha Configuración. Selecciona Lista del cuadro desplegable Permitir y escribe la fórmula =INDIRECTO($F$4). Utilizando la función INDIRECTO como origen de datos y en referencia a la celda que contiene las marcas de automóviles, solo se desplegará la lista con los modelos de la marca seleccionada previamente en la celda F4.
Existen multitud de usos para la herramienta de Validación de datos. ¿Cómo la utilizas tú?
mi problema es: necesito sacar datos de una hoja, que me los ponga en una de 4 la columnas que me corresponda al valor deseado .
también tengo otro problema al ejecutar el signo
agradezco so atención y orientación gracias. saludos
Agusti, te recomiendo que visites el foro de la web y plantées ahí tu cuestión. La respuesta que te daría por aquí es algo larga y en el foro contamos con más recursos.
Juan
Pedro, no es correcto. La fórmula que sugieres hace una comparación entre el texto en mayúsculas introducido en A1 con el número 0, asà que siempre será falso. Para hacer lo que indicas, debes introducir la siguiente fórmula en la validación de datos: =IGUAL(MAYUSC(A1);A1).
Espero que te sirva
La verdad es que yo siempre trabajo asÃ. Primero creo la fórmula en una celda y cuando veo que da los resultados que deseo, la traslado al cuadro de validación.
Una ventaja de tener la fórmula en la hoja es que está más accesible al usuario y en caso de cambiar las condiciones, puede modificarla más rápidamente.
Echale un vistazo a los siguientes enlaces:
https://ayudaexcel.com/foro/threads/duda-con-listas-desplegables-dependientes.35099/#post-168454
https://ayudaexcel.com/foro/threads/textbox-dependientes.34912/
-Hola Manuel.
¿Se puede hacer un recuento del número de veces que se elige un dato de una lista desplegable de validación de datos?
Eduardo, con fórmulas no es posible hacerlo. Tendrías que programar una macro.
para evitar capturar nombres repetidos entre hojas distintas quisiera utilizar la validación de datos, pero esta formula=CONTAR.SI(A:A;A1)=1, es para un rango de una sola hoja.
De ser posible, me ayudarÃas mucho diciéndome como escribir la formula para evitar duplicidades en todo el libro, o la manera más óptima de realizar esta tarea.
Los nombres están capturados de la siguiente manera: Ej. Perez Gonzalez Maria Encarnacion; sin acentos para facilitar su búsqueda y en una sola celda de la columna C de cada hoja.
Por tu atención, gracias anticipadas
Saludos.
Sergio buen día, estoy realizando la validación de datos para que solo me permita el ingreso de mayúsculas y utilizo la formula =IGUAL(MAYUSC(A1);A1), así me funciona solo si le aplico la condición a una sola celda, pero al aplicarlo seleccionando varias celdas o columnas y poniendo los respectivos rangos en la formula no me funciona… Por que?, de que otra forma lo puedo hacer para que me funcione?
Muchas gracias por su valiosa ayuda!!
Javier, comprueba que al introducir la fórmula en la validación, solo tengas seleccionada una celda. Después, solo tendrás que copiar el formato en el resto de celdas.
También asegúrate de que no aparecen los símbolos de dólar al introducir las referencias de las celdas.
Saludos.
Geniales todos los trucos, pero para mi caso, el de los datos con condiciones ha sido todo un descubrimiento! Mil gracias.
Muy buena, muchas gracias
sergio estoy haciendo l ejemplo de la agencia de autos y no me permite poner la funcion indirecto me dice que hay error
¿Cómo utilizas la función? Me da la impresión de que el error no lo da la función en sí misma sino el argumento que utilizas en ella. Revisa todas las fórmulas que has utilizado a ver si encuentras el fallo.
me arroja el sig mensaje el origen actualmente evalua un error
y en la celda f5 cuando valido los datos en la ficha Configuración. debe decir Lista del cuadro desplegable y solo aparece lista
Te consulto si es posible utilizar un buscador a través de un orden alfabetico desde la lista desplegable, osea digo si al momento de desplegar la lista puedo tipear una letra y que dicha lista se desplace hasta ahí. Muchas gracias
Fernando, hace bastante tiempo se publicó en el foro una herramienta muy muy muy buena para hacer hacer algo similar a lo que buscas.
Al ir introduciendo caracteres en una celda, se van filtrando las celdas que comienzan por los caracteres escritos.
Descárgate la herramienta aquí:
https://ayudaexcel.com/foro/threads/turbo-filtro.14355/
Ya me dirás si te ha sido de utilidad.
Por favor Sergio, valoro mucho tus grandes aportes a los nuevatos 🙂 sabes necesito validar una celda C1 la cual es producto de una resta de A1-B1, será posible que salga la ventanita y me diga error la diferencia es menor a 15 x ejemplo…G R A C I A S…desde Perú
Alfredo, yo no lo haría con validación de datos sino con Formato condicional. Si el resultado es menor a 15, coloreará (por ejemplo) el fondo de la celda.
Buen día, estimado
Interesantes los trucos que utiliza, gracias por compartir su conocimiento, podría ayudarme con una validación? es similar a Exigir valores únicos de 7 dígitos, con la salvedad que incluye una letra, por ejemplo G123456.
Agradezco su atención.
Daniel, entra en el foro de la web (http://www.ayudaexcel.com/foro). Te responderé desde allí cuando reciba el mensaje.
Saludos
busco alguna formula que me permita contar los datos repetidos y me su el valor asignado en una celda distinta
Hola buenos dias, una consulta com puedo unir Lista desplegable con Valores Unicos con validacion de DAtos??
Gonzalo, echa un vistazo a este enlace:
http://jldexcelsp.blogspot.com.es/2006/07/validacin-de-datos-en-excel-agregar.html
ESTIMADO SERGIO, tengo una hoja de diferentes artículos con sus definiciones,y en otra hoja he realizado una validación de datos con la opción lista, pero ahora en dicha validación me aparecen valores repetidos, quisiera poder saber como hacer para que aparezca solo uno
Puedes echar un vistazo a los enlaces:
http://jldexcelsp.blogspot.com.es/2006/08/valores-nicos-en-lista-de-validacin-de.html
http://lareboticadeexcel.blogspot.com.es/2013/02/lista-de-valores-unicos-con-formulas.html
Espero que te sirvan.
Hola, muchas gracias, me ha parecido muy útil tu artículo. Me gustaría preguntarte una cosa ¿como lo harías para en una validación de datos de lista te mostrará un rango (por ejemplo las descripciones de un articulo) y que al seleccionar una opción te devuelva por ejemplo la referencia (pongamos que es el rango de al lado)? He leído muchísimos artículos sobre validación y en ningún caso le he visto solución, muchas gracias.
¿Dónde quieres que aparezca el valor de la derecha? ¿En la misma celda donde tienes la validación de datos?
Para hacer que aparezca en la misma celda, tendrás que utilizar una pequeña macro. En caso de que sea en la celda de la derecha donde quieres que aparezca el valor, puedes utilizar BUSCARV o INDICE+COINCIDIR para que busque el valor de la celda con validación y devuelva el de la derecha.
quiero insertar una formula un poco extensa en el validador de datos como hacerle para que me deje teclear mas caracteres?
¿Cómo es de extensa, Francisco? En principio no debería darte ningún problema…
Buenas Tarde sergio
Lo que pasa es que tengo que elaborar una validación y no tengo i idea que formula utilizar tu me podrías colaborar por favor:
El valor de los libros siempre debe ser el 70% del costo de los activos
te agradeceria mucho q me facilitaras la formula
Por favor, entra en el foro (http://www.ayudaexcel.com/foro) y plantea ahí tu duda. Cuando la vea te echaré una mano.
Hola Sergio, saludos desde Venezuela. Estoy creando un gestor de inventarios en excel para mi microempresa de venta de papelería en general, y en la pestaña de lista de productos he realizado lo siguiente:
1.- Una columna para seleccionar la categoría del producto (A=Alta rotacion;B=Mediana rotacion;C=Baja rotacion), al dar click se muestra una lista desplegable con las 3 opciones
2.- La siguiente columna es la de Código del producto (A-001; A-002;A-008;A-032. hasta A-999) y tambien fue diseñada por listas desplegables dependientes de la columna categoría.
El problema es que al introducir los códigos a traves de lista desplegables dependientes el sistema no me arroja un error de que el código fue introducido anteriormente, y como sabes los codigos deben ser unicos para cada producto.
Pregunta: ¿existe la posibilidad de combinar la función SI con la funcion INDIRECTO y CONTAR.SI?
es decir, quiero ingresar el codigo del producto por medio de listas desplegables pero que cuando ya halla seleccionado un codigo de la lista excel no me permita usar el mismo codigo para otro producto.
Eternamente agradecido, y de antemano muchismas gracias por transmitit conocimiento atraves del presente medio
Hasta luego!!!!
Hola, Hugo.
Por favor, entra en http://www.ayudaexcel.com/foro y plantea ahí tu consulta. Estaré pendiente para responderte.
Gracias
excelente aporte el de validacion de datos…
muchas gracias
Hola Sergio.
Antes que nada darte las gracias por tu ayuda y tus comentarios.
Quiero hacer un desplegable de manera tal que cuando elija una de las opciones me salga un valor numérico en otra celda.
Por ejemplo. Al seleccionar un valor de un artículo del desplegable me sale automáticamente el precio en otra celda.
Espero tus consejos.
Hola, Gregorio. Si has seguido el hilo del tutorial, imagino que habrás seleccionado la opción Lista y habrás conseguido introducir los elementos del desplegable…
Para que te aparezca en una celda el precio del producto que has seleccionado en el desplegable, deberías utilizar alguna función de búsqueda como BUSCARV, que busque el nombre del producto y devuelva el valor que se encuentre a la derecha.
Buenas tardes Sergio, necesito ayuda para obtener una formula que me restrinja el numero de caracteres que un usuario escribe en una celda tipo texto, que le trunque hasta cierto numero, sin que pierda lo que ya ha introducido.
Muchas gracias.
John, es imposible hacerlo con fórmulas a no ser que no quieras hacerlo en la misma celda. Si quisieras que el usuario introduzca el valor en la misma celda que se truncará, necesitarás hacerlo con una macro.
Buenas, necesito hacer una validación de datos por medio de listas desplegables pero necesito que pueda seleccionar más de 2 respuesta de la lista, Ejem. al tomar la Opción Casa, Grande/Mediana o pequeña, pueda colocarle a la respuesta Grande, 4 cuartos, 2 baños, Balcón, ETC, donde cuartos, baños, balcón etc están en otra columna ya que son otras características de Grande.
Como puedo hacer esta lista con múltiples opciones de respuestas?
Luis, busca información sobre cuadros de lista desplegables. Te sugiero que entres en el foro de la web y plantees allí esta cuestión. Te daremos respuesta enseguida.
Buenas tardes, como valido datos si requiero un num de 5 dígitos q inicien en 5, terminen en 4 y que no sean repetidos. Porfavor su ayuda.
José Carlos, plantea la consulta en el foro de la web procurando incluir un archivo con el ejemplo. Enseguida lo veremos y te respondemos.
Hola, en el caso de incluir el número “0” en el valor condicional, este no aparece o se omite.
Saludos a Todos,
Una consulta como puedo hacer si tengo varios ID y cada ID es único pero este se repite en la base de datos (BD) muchas veces, quisiera contar el primer ID que aparezca en la BD y si lo encuentra el mismo ID mas abajo en la BD no lo cuente.
Quedo muy atento.
Mil Gracias de nuevo.
En este caso puedes utilizar la función COINCIDIR anindada dentro de un SI. “Si el ID no da error, significa que lo ha encontrado, y la fórmula debe devolver 1”.
Hola buenas tardes.
Interesante artículo, me ayudó para despejar una duda que tenía con algo similar 🙂
Por otra parte como puedo hacer para que al introducir datos de correo en cierta columna sea forzoso que lleven el “@”
Hola Randy!
En caso de que la celda que va a contener el correo sea A1, puedes utilizar la siguiente fórmula de validación:
=ESNUMERO(COINCIDIR(“*@*.???”;A1;0))
buenas tardes
tengo la formula para determinar la validación de datos en una lista o listas porque son alguno los datos que quiero que se respeten en mi tabla pero solo me permita cierta cantidad de listas como puedo ampliar para que me permita mas listas en la formula
muchas gracias
hola buenas tardes
al momento de realizar la validación de datos con condiciones solo me deja aplicarla en una fila, quisiera saber como podría aplicarla en todo el documento me seria de gran ayuda
muchas gracias
Hola Daniel!
Solo tienes que seleccionar las celdas previamente. Cuando apliques la validación, se extenderá a todas ellas.
gran explicación (como todas), me queda una duda: es posible darle formato a esa lista desplegable (la que aparece en validación de datos)?? Porque si tengo texto grande en las celdas de la hoja, ese desplegable tiene las letras muy pequeñas! GRACIAS