sábado, 21 de enero de 2012

Personalizando Fórmulas con Validación de Datos

"He leído el anterior artículo y me gustaría saber si hay forma de limitar la entrada de datos, por medio de la validación de datos, para conseguir que no permita introducir un código en la celda si no cumple la siguientes reglas: a)que el número total de caracteres sea de siete; b) que los dos primeros caracteres sean dos letras y estén escritos en mayúscula; c) que los siguientes caracteres sean 5 números."

Lo que queremos conseguir requiere del uso de la herramienta Validación de Datos por un lado y del uso de unas cuantas funciones para garantizar que se cumplen todas las restricciones indicadas por nuestro lector. En concreto, las restricciones que debe contemplar la fórmula que realicemos son:

1. Que el número de caracteres totales introducidos ha de ser 7
2. Que los dos primeros caracteres han de ser texto
3. Que los dos primeros caracteres han de ser mayúsculas
4. Que los caracteres del 3 al 7 han de ser números

Supongamos que tenemos una zona de introducción de datos como la mostrada en la siguiente imagen:


Lo que debemos hacer es seleccionar el rango B4:B12 que es nuestra zona de entrada de datos. Vamos a la ficha Datos/Validación/Validación de Datos y seleccionamos Permitir Personalizada. En Fórmula escribimos la siguiente (es un poco larga):

=Y(LARGO(B4)=7;IGUAL(B4;MAYUSC(B4));ESERROR(VALOR(EXTRAE(B4;1;1)));
ESERROR(VALOR(EXTRAE(B4;2;1)));
ESERROR(VALOR(EXTRAE(B4;3;5)))=FALSO)=VERDADERO

Voy a explicar las distintas funciones y partes de la fórmula. Empezamos con la función Y. Esta función nos sirve para comprobar si se cumplen una serie de pruebas que vamos a realizar. En caso de que se cumplan todas las pruebas que realicemos el valor que nos devolverá esta función será VERDADERO y en caso contrario FALSO (precisamente por este motivo podemos utilizarla dentro de validación de datos como fórmula personalizada).

La primera prueba que realizamos es la de el número total de dígitos. Para ello hacemos uso de la función LARGO, que nos devuelve el número de caracteres existentes en una celda, y comprobamos si es igual a 7.

La segunda comprobación que realizamos es si los dos primeros caracteres son mayúsculas. Para ello utilizamos la función IGUAL y la función MAYUSC. La función IGUAL comprueba si dos cadenas de texto son idénticas o no diferenciando entre mayúsculas y minúsculas.

Ahora comprobamos si los dos primeros caracteres son letras. Para ello debemos extraer dichos 2 caracteres para analizarlos. Hacemos uso de la función EXTRAE. Al utilizar esta función excel considera como texto los caracteres extraídos. En caso de que se trate de números será sencillo convertirlos nuevamente haciendo uso de la función VALOR. Finalmente utilizo la función ESERROR para comprobar que si tras convertir los dos primeros caracteres a número me devuelve un mensaje de error sólo puede ser debido a que se trate de texto. La misma operación pero al revés, es decir, comprobando que el valor devuelto es FALSO, resolverá la última parte de la fórmula donde verifico que los últimos 5 dígitos son números.

Una vez introducida esta fórmula en la validación de datos ya puede comprobar que el funcionamiento es el deseado y que sólo nos permitirá introducir valores correctos. Cada vez que cometamos un error nos aparecerá el mensaje que definamos dentro de la validación de datos, tal y como se muestra en los siguientes ejemplos:



38 comentarios:

  1. Buenas tardes Kiko, he visto tu fórmula sobre la valoración de datos y me ha ido de fábula pero me he quedado encallada en un punto: Yo tengo este criterio, 1234L12345, 4 números, una letra mayúscula y 5 números más. Tengo una columna donde los números que entren deben llevar siempre una L. No he tenido problema para hacer la fórmula pero donde no puedo seguir es en como le digo a la fórmula que la letra mayúscula siempre sera L. Así cuando entren el registro con la letra equivocada saltará un mensaje de alerta. Muchas gracias. Recibe un cordial saludo.

    ResponderEliminar
  2. El "trozo de fórmula" que debes introducir es:
    =IGUAL("L";EXTRAE(C7;5;1))
    Esta fórmula devuelve VERDADERO sólo cuando el 5º caracter es una L mayúscula. En cualquier otro caso devuelve FALSO.

    ResponderEliminar
    Respuestas
    1. Buenas Kiko,

      tienes idea de como resaltar texto duplicado entre varias hojas?

      Por ejemplo, tengo una lista de 80 nombres en la hoja 1 y en la hoja 2 otra lista de 80 nombres y quiero resaltar los nombres duplicados entre las dos hojas.
      Lo he intentado nombrando las listas con un nombre y luego aplicar formato condicional pero no he conseguido nada...

      A ver si tengo suerte de que me leas.

      Un saludo y gracias !!

      Eliminar
  3. Te contesto en el siguiente artículo

    ResponderEliminar
  4. Saludos. Me salen un error que dice: "No se puede encontrar uno de los rangos especificados". Me pueden ayudar por favor

    ResponderEliminar
    Respuestas
    1. Hola. Mándame tu problema a kiko.rial@gmail.com lo veo e intento solucionarlo. Un saludo

      Eliminar
  5. BUENAS TARDES PROFESOR..
    DISCULPE LO QUE PASA ESQUE NECESITO VALIDAR ALGUNAS CELDAS CON FORMATO DEL RFC, ES DECIR, QUE LOS PRIMEROS 4 CARACTERES SEAN ALFABÉTICOS, DEL 5º AL 10º CARACTER SEAN NUMERICOS Y DEL 11º AL 13 SEAN ALFANUMÉRICOS.
    OJALA PUEDA AYUDARME.. UN SALUDO Y GRACIAS

    ResponderEliminar
  6. Buenas tardes José. Ya tengo tu solución pero como la fórmula resulta un poco "tediosa" prefiero enviarte por mail dicha solución. Puedes darme una dirección de envío? Gracias

    ResponderEliminar
  7. Hola Profesor, buenos días, la verdad que me encanta su página y estoy
    aprendiendo mucho de ella, tiene cosas interesantísimas que no pensaba
    ni que se podían hacer, me gustaría pedirle un favor, me enviara la
    fórmula del codigo RFC, se lo agradecería muchísimo
    mi dirección de correo es manu280569@yahoo.es
    Saludos y siga asi

    ResponderEliminar
    Respuestas
    1. Muchas gracias por tus comentarios. Ya te he enviado lo que me pides. Un saludo

      Eliminar
  8. BUENAS TARDES PROFESOR, EXCELENTES ARTICULOS, SOLO QUE ESTOY ATORADO CON UNA FORMULA PARA VALIDAR LOS CENTROS DE TRABAJO ESCOLARES SERIA 28KJN1234A, DOS NUMEROS,3LETRAS, 4 NUMEROS, UNA LETRA, TODAS MAYUSCULAS, LE AGRADECERIA MUCHO

    ResponderEliminar
    Respuestas
    1. Seleccionas el rango donde vas a aplicar la Validación de datos y dentro de la herramienta de validación marcas personalizada y escribes esta fórmula:
      =Y(LARGO(B3)=10;IGUAL(B3;MAYUSC(B3));ESNUMERO(VALOR(IZQUIERDA(B3;2)));ESERROR((VALOR(EXTRAE(B3;3;1))));ESERROR((VALOR(EXTRAE(B3;4;1))));ESERROR((VALOR(EXTRAE(B3;5;1))));ESNUMERO(VALOR(EXTRAE(B3;6;4)));ESERROR((VALOR(DERECHA(B3;1)))))=VERDADERO

      Eliminar
  9. Hola profesor, aprendo mucho de esta página, le agradeceria mucho si me pudiera ayudar aplicando la formula anterior =Y(LARGO(B3)=10;IGUAL(B3;MAYUSC(B3));ESNUMERO(VALOR(IZQUIERDA(B3;2)));ESERROR((VALOR(EXTRAE(B3;3;1))));ESERROR((VALOR(EXTRAE(B3;4;1))));ESERROR((VALOR(EXTRAE(B3;5;1))));ESNUMERO(VALOR(EXTRAE(B3;6;4)));ESERROR((VALOR(DERECHA(B3;1)))))=VERDADERO pero que se refiera a una lista que se llama escuelas. gracias de antemano mi correo es quintaponchito@hotmail.com

    ResponderEliminar
    Respuestas
    1. Perdón pero no entiendo lo que necesitas. La fórmula de referencia se puede aplicar a una celda o rango de celdas, tengan el nombre que tengan. Me puedes especificar lo que buscas? Gracias

      Eliminar
    2. CON GUSTO PROFESOR. TENGO UNA CELDA VALIDADA CON UNA LISTA DE CLAVES DE ESCUELAS; 07DPR4565T, 07DPR4878Y, ETC.. COMO SON MILES, TENGO DESACTIVADA LA LISTA DESPLEGABLE PARA QUE LA ESCRIBAN. EL PROBLEMA ES QUE ACEPTA LAS LETRAS MINÚSCULAS Y PARA LOS DATOS QUE SE REQUIEREN ESO ES UN ERROR. NECESITO OBLIGAR QUE LO ESCRIBAN CON MAYUSCULAS. gracias de antemano mi correo es quintaponchito@hotmail.com

      Eliminar
  10. Para obligar a que escriban cualquier letra en mayúscula debes seleccionar el rango en cuestión donde se vayan a escribir las claves, por ejemplo A1:A10, y en Validación de datos seleccionar la opción de Personalizada y escribir la fórmula =IGUAL(A1;MAYUSC(A1))
    de esta forma, si alguien introduce una o varias letras en minúscula se abrirá una ventana advirtiendo del error.

    ResponderEliminar
  11. Necesito crear un codigo por medio validacion de datos, dicho codigo debe empezar con la serie de letra "HU", y unos digitos seguidos, no hay limite para numeros, la unica condicion es la la serie de letras.
    Gracias de Antemano

    ResponderEliminar
    Respuestas
    1. Suponiendo que introducimos el primer código en A1, si simplemente debe empezar por HU, independientemente de que lo escriban en mayúscula o minúscula, la fórmula a utilizar dentro de la Validación de Datos/Personalizar sería:
      =Y(IZQUIERDA(A1;2)="HU")
      Si tiene que empezar por HU y además que esté en mayúscula, entonces:
      =Y(IZQUIERDA(A1;2)="HU";IGUAL(MAYUSC(IZQUIERDA(A1;2));IZQUIERDA(A1;2)))

      Eliminar
  12. Estimado, Profesor estoy con una duda, quiero saber si se puede hacer y como se hace, poner formula y una lista desplegable en una misma celda. es decir q si no se culpme la funcion de la formula puedan elegir en la lista..

    ResponderEliminar
  13.  En la columna Factura el código debe contener 7 dígitos esto incluye la letra de la Serie “H” (solo debe aceptar esta serie), no se puede repetir el código de la factura "sin duplicados".

    ResponderEliminar
    Respuestas
    1. Buenas noches francisco, quisiera saber si lograste resolver duda ya que tengo una similar y quisiera saber si pudieras ser tan amable de ayudarme.

      Eliminar
    2. Hola Elizabeth. Me puedes concretar más tu pregunta? Gracias

      Eliminar
  14. Buenos días Sr Rial, mi pregunta es que debo crear una restricción mediante validación de datos, es decir que dentro del rango A3:A20 se deben ingresar solo 7 dígitos, de los cuales el primero solo debe ser "H" y los siguientes 6 numeros, y no se deben duplicar.

    ResponderEliminar
    Respuestas
    1. Debes seleccionar el rango A3:A20 y en Validación de Datos personalizada escribir la siguiente fórmula:
      =Y(CONTAR.SI($A$3:A3;A3)=1;LARGO(A3)=7;IZQUIERDA(A3;1)="h";ESNUMERO(VALOR(EXTRAE(A3;2;6))))

      Eliminar
    2. Buenas tardes, utilicé su formula y tengo una duda con respeto al uso e interpretación de la siguiente función anidada: ESNUMERO(VALOR(EXTRAE(B6,2,6)))), estuve analizando las funciones de forma invididual, comprendo que la función EXTRAE, escoge los caracteres de una celda dada, y la función ESNUMERO me determina si la celda seleccionada es un numero o no, indicando con verdadero y falso. La función VALOR no logré que me diera un resultado, siempre me da un error en valor, para que se utiliza en forma individual y en forma anidada? muchas gracias.

      Eliminar
    3. Disculpe el excel que estoy utilizando es el 2013, esas funciones están destinadas para esta versión o las anteriores.

      Eliminar
    4. La función VALOR convierte en número un argumento de texto que representa un número. Un código del tipo H123456 es, para excel, un texto. Si extraemos desde el segundo y hasta el sexto dígito el resultado es 123456 pero sigue siendo un texto para excel. Para "reconvertirlo" en número le aplicamos la función VALOR. En el caso de que todos los caracteres sean números entonces no dará error. Pero si hay algún caracter no numérico (alguna letra) entonces no podrá convertirlo en número y dará error. He usado este sistema porque en tu pregunta pedías que después de la H debían venir 6 números.Un saludo

      Eliminar
    5. Estas funciones están disponibles tanto en excel 2013 como en versiones anteriores.

      Eliminar
  15. Hola Kiko gracias por la información. Sabes de alguna forma para que en la celda validada sólo me muestre los primeros 4 dígitos del texto seleccionado? es que tengo una lista de validación con código nombre unido pero sólo quiero que en la celda se vea el código.

    ResponderEliminar
    Respuestas
    1. Necesito que me concretes más lo que quieres (algún ejemplo, por favor). Gracias

      Eliminar
  16. Profesor su ayuda necesito validar que los 3 primeros datos sean letras pero me sale un error que indica que el rango ingresado es incorrecto Y(LARGO(C4)=15,ESERROR(VALOR(EXTRAE(C4,1,1))),ESERROR(VALOR(EXTRAE(C4,2,1))),ESERROR(VALOR(EXTRAE(C4,3,1))),ESERROR(VALOR(EXTRAE(C4,4,12)))=FALSO)=VERDADERO

    ResponderEliminar
    Respuestas
    1. La fórmula correcta, si quieres comprobar que los 3 primeros caracteres sean letras, es:
      =Y(LARGO(C4)=15,ESERROR(VALOR((EXTRAE(C4,1,1)))),ESERROR(VALOR((EXTRAE(C4,2,1)))),ESERROR(VALOR((EXTRAE(C4,3,1)))))=VERDADERO
      Si se cumple esta fórmula el resultado será VERDADERO y significará que la cadena introducida en C4 tiene 15 caracteres y los 3 primeros son letras (texto).

      Eliminar
  17. buenas mire si quiero saber como se valida una celda que queremos que las 3 primeras posiciones sean letras y las siguientes 4 sean numeros no se como seria la formula

    ResponderEliminar
    Respuestas
    1. Suponiendo que la celda a validar sea A2, dentro de la Validación de Datos debes escribor la siguiente fórmula:
      =Y(ESERR(VALOR(EXTRAE(A2;1;1)));ESERR(VALOR(EXTRAE(A2;2;1)));ESERR(VALOR(EXTRAE(A2;3;1)));ESNUMERO(VALOR(EXTRAE(A2;4;4))))=VERDADERO

      Eliminar
  18. Maestro me puede ayudar, igualmente necesito validar datos asi como el Sr. Juan "Jose Valdez16 abril, 2013 23:44
    BUENAS TARDES PROFESOR..
    DISCULPE LO QUE PASA ESQUE NECESITO VALIDAR ALGUNAS CELDAS CON FORMATO DEL RFC, ES DECIR, QUE LOS PRIMEROS 4 CARACTERES SEAN ALFABÉTICOS, DEL 5º AL 10º CARACTER SEAN NUMERICOS Y DEL 11º AL 13 SEAN ALFANUMÉRICOs."

    Espero pueda ayudarme con las formulas

    este es mi correo floressanchezt@outlook.es

    ResponderEliminar
    Respuestas
    1. Ye le he mandado solución a su mail. Un saludo

      Eliminar
  19. Kiko tengo duda cuantos caracteres puedo ingresar en la validación de celda personalizada mi formula tiene 495 caracteres saludos

    ResponderEliminar
  20. Si te refieres al número de caracteres que se pueden escribir dentro de la herramienta Validación de datos/Personalizada en la fórmula, el número máximo es de 255.
    En una celda, y según la página oficial de microsoft, la longitud máxima del contenido de una fórmula es de 8.192 caracteres (esto no lo he comprobado personalmente).

    ResponderEliminar