42

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:

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.

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.

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.

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

Comparte:

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 42 comentarios
Marcelo Guerra - 04/02/2014

Excelente…como siempre!!!

Responder
Pedro Velez - 05/02/2014

Hola Sergio, todas las de elogio que te digan es muy poco para todo lo que nos enseñas, muchas gracias

Responder
febrero59 - 07/02/2014

Gracias Sergio: Te agradezco la guía.
Juan

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

Responder
manolo juarez - 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

Responder
PEDRO VELEZ - 11/02/2014

Sergio muchas gracias si funcionó, muchas gracias

Responder
Miguel Villalobos - 13/02/2014

Muchas gracias por esa ayuda tan valiosa que nos brinda día a día.

Responder
cmm - 14/02/2014

excelente.

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

Responder
manolo juarez - 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.

Responder
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

Responder
manolo juarez - 16/03/2014

Xavi, no es cuestión de escribir en la celda sino cambiar el nombre definido por el usuario.

Responder
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

Responder
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

Responder
manolo juarez - 10/08/2014

Hola, Gerardo. En la referencia a las celdas, incluye también el nombre de las hojas.

Saludos.

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

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

    Responder
Carles Serra - 15/03/2015

Geniales todos los trucos, pero para mi caso, el de los datos con condiciones ha sido todo un descubrimiento! Mil gracias.

Responder
Juan Carlos Piracés - 16/03/2015

Muy buena, muchas gracias

Responder
ernesto bautista - 21/03/2015

sergio estoy haciendo l ejemplo de la agencia de autos y no me permite poner la funcion indirecto me dice que hay error

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

    Responder
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

Responder
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

Responder
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ú

Responder
    Sergio - 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.

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

Responder
Edgar Catalan - 18/09/2015

busco alguna formula que me permita contar los datos repetidos y me su el valor asignado en una celda distinta

Responder
Gonzalo - 08/10/2015

Hola buenos dias, una consulta com puedo unir Lista desplegable con Valores Unicos con validacion de DAtos??

Responder
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

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

Responder
    Sergio - 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.

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

Responder
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

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

Responder

Escribe una respuesta:

Powered by WishList Member - Membership Software