4 ejemplos de utilización de validación de datos con fórmulas

Sergio 13/09/2024
65 Comentarios
validación de datos excel

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:

  1. Selecciona el rango de celdas que quieres validar. En este caso, selecciona la columna A completamente.
  2. Selecciona el botón Validación de datos que se encuentra en el grupo Herramientas de datos de la ficha Datos.
  3. 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ú?

Categorías: Fórmulas y funciones
Tags:

Respuestas

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

    1. agusti n.l 29/08/2017

      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

  4. Pedro Velez Paz 08/02/2014
    Hola Sergio nuevamente, en base a lo que tu nos enseñaste, estoy tratando de aplicar la formula =MAYUSC(A1)=0, para que en la celda solo se pueda ingresara en mayúsculas, es correcto lo que hice?
  5. Sergio 08/02/2014

    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

  9. Héctor 27/02/2014
    Ciertamente la validación con fórmulas es una herramienta potente. Sólo quería añadir que también es posible escribir la fórmula en una celda de la hoja y hacer referencia a ella desde el cuadro de validación de datos. Esto puede ser útil cuando la fórmula deba verificar muchas condiciones y la haga demasiado larga en cuyo caso simplemente no cabe en el espacio disponible. Esto lo tuve que hacer para validar la introducción de una codificación de cierta complejidad que incluía texto, guiones y números bajo formatos específicos. Hice uso de varias celdas para validar cada condición y resumí en la celda a referenciar el resultado de la validación. No soy un experto en el tema pero me di cuenta de que podía hacerse y lo quise compartir.
  10. Sergio 27/02/2014
    Héctor, gracias por tu comentario. Es cierto que cuando se evalúa más de una condición, las fórmulas se vuelven kilométricas.

    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.

  11. Xavi 16/03/2014
    Cuando en el caso del ejemplo 4 en el que tu pones toyota, si yo quisiera poner una palabra con espacio en el titulo como lo tendra que hacer para que funcionara la validacion de datos, ya que al poner el espacio, ni con las barras bajas funciona. Gracias
  13. DIego 04/06/2014
    Saludos desde México; tengo una duda. Deseo realizar el despliegue de una lista de Municipios y al seleccionar una de ellas, una de las Localidades correspondientes a ese Municipio. Pero he detectado que al seleccionar un Municipio cuyo Nombre sea compuesto. Por ejemplo: “Las Margaritas” o “Ángel Albino Corzo”, en la celda anexa de las Localidades no despliega las opciones. ¿Existe algún límite de Datos Dependientes o alguna otra limitación del programa?. También me he percatado que algunos nombres con tilde (no todos) me topo con el mismo problema. Saludos
    1. Eduardo 30/10/2018

      -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?

  15. Gerardo Arreola 09/08/2014
    Que tal Sergio, estoy trabajando con una extensa relación de nombres de personas la cual tengo separada por hojas,
    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

  17. Javier Camacho 03/03/2015

    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!!

    1. Sergio 03/03/2015

      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.

    1. Sergio 21/03/2015

      ¿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.

  21. ernesto bautista 23/03/2015

    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

  22. Fernando 23/03/2015

    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

  23. Alfredo 27/05/2015

    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ú

    1. Sergio Propergol 28/05/2015

      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.

  24. Daniel López 09/07/2015

    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.

  27. sara 26/10/2015

    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

  28. viperionDavid 09/12/2015

    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.

    1. Sergio Propergol 09/12/2015

      ¿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.

  29. Francisco 17/02/2016

    quiero insertar una formula un poco extensa en el validador de datos como hacerle para que me deje teclear mas caracteres?

  30. Juliana 18/02/2016

    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

  31. Hugo Javier Caridad Arana 31/03/2016

    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!!!!

  33. Gregorio 01/08/2017

    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.

    1. Sergio 01/08/2017

      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.

  34. John 21/09/2017

    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.

    1. Sergio 22/09/2017

      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.

  35. Luis Pertuz 13/02/2018

    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?

  36. José Carlos 25/02/2018

    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.

    1. Sergio 26/02/2018

      José Carlos, plantea la consulta en el foro de la web procurando incluir un archivo con el ejemplo. Enseguida lo veremos y te respondemos.

  38. MAIKER VASQUEZ 14/10/2018

    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.

    1. Sergio 14/10/2018

      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”.

  39. Randy Cortés 22/08/2019

    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 “@”

    1. Sergio 26/08/2019

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

  40. alejandrp 05/09/2019

    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

  41. daniel ramirez 03/12/2019

    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

    1. Sergio 07/12/2019

      Hola Daniel!
      Solo tienes que seleccionar las celdas previamente. Cuando apliques la validación, se extenderá a todas ellas.

  42. David 11/02/2023

    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

  43. Esmeralda 25/05/2024

    Buen día, como puedo validar datos de lo siguiente; en una columna tengo códigos S,A,E y NA y quiero que en otra celda NO permita ingresar datos en donde exista NA solo en las demás ?