sábado, 13 de marzo de 2010

Validación de datos personalizada.

Aprovecharé la solicitud de ayuda un lector sobre un problema, para desarrollar un ejemplo de validación de datos personalizada que se le planteaba, por otro lado muy típico en España:
...Tengo una hoja Formulario.xls, en la que los usuarios me han de rellenar unos datos personales.
Por ejmplo:
-Celda B2 es el DNI,
-Celda C2 es la letra de DNI.
De forma oculta en otra celda L7 yo calculo la letra con la fórmula, ya de todos conocida de "=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKEF";1+RESIDUO(B2;23);1) "
Como hago para validar C2, para que si meten una letra que no es la correcta les salga un mensaje de error...

Usaremos la opción de 'Validación de datos personalizada' para dar solución a este cuestión. Desde el menú Datos > Validación seleccionamos Permitir 'Personalizada', con lo que nos desplegará la opción de 'Fórmula', en la cual escribiremos la prueba lógica que se ha de cumplir para que nos permita introducir un valor en la celda C2, es decir, escribiremos
=C2=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKEF";1+RESIDUO(B2;23);1)

Validación de datos personalizada.


Con lo que tendríamos nuestra celda adaptado a nuestro propósito, que es impedir la incongruencia entre número de DNI y su letra.

Ya sólo nos quedaría configurar las pestañas de 'Mensaje de entrada' y 'Mensaje de error' con algún texto los suficientemente descriptivo.

Aprovechando el tema tratado incluiré otra forma de calcular la letra de los D.N.I.
=ELEGIR(RESIDUO(B2;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J";"Z";"S";"Q";"V";"H";"L";"C";"K";"N")

45 comentarios:

  1. buen aporte compañero se lo agradesco

    ResponderEliminar
  2. y funciones mas faciles

    ResponderEliminar
  3. Hola de nuevo.. estoy intentando hacer una validación...
    en la que en ciertas columnas me acepte solo 5 números que no son consecutivos... como lo son 1,2,3,98,99 como podría hacer una validación personalizada con esos números??
    Gracias

    Y felicidades por tu foro!

    ResponderEliminar
    Respuestas
    1. Hola Lune,
      bueno, no hace falta una validación personalizada, sería suficiente con una Validación tipo Lista, y esas lista componerla con los valores que indicas (separados por ';' punto y coma ).
      Slds

      Eliminar
  4. Tengo una duda con respecto a la asignacion de un valor numerico a una letra
    A=10, B=12,C=13

    Si se ingresa A en A4 toma el valor de 10
    =SI(A4="A";10)
    la idea es seguir agregando funciones en la misma celda
    =SI(A4="A";10),=SI(A4="B";12),=SI(A4="C";13), ETC.
    NO se si es posible.

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      No sé si te he entendido bien; pero posible es, pero con limitaciones.. ya que sólo es posible anidar hasta 64 funciones, a parte del trabajo que conlleva.
      La mejor opción sería contruir una tabla auxiliar con las correspondencias, por ejemplo, columna C:D, en C las letras y en D los valores; así entonces en A3 escribes la letra y en B3 al función
      =BUSCARV(A3;C:D;2;0)

      Si lo que pretendes es que en una celda concreta escribes una letra, y quieres visualizar un valor concreto, hablaríamos de un tema de formato personalizado, pero el valor real de la celda (independientemente de qué estemos viendo) sería el introducido.

      Slds

      Eliminar
    2. Gracias por la ayuda, probare la solucion que mencionas.

      Eliminar
  5. Hola buenas tardes, ante todo felicitarte por esta pagina.Esto de la informática no es mi fuerte y no sé si es aqui donde debo plantear ni duda pero allá va:
    Tengo en una hoja una base de datos donde la coluna A son fechas y B (nombres de persona)y C incidencias de esa persona.
    Debo encontrar la manera de pasar los datos de B y C a un calendario mensual hecho en otra hoja con excel 2003. La formula BuscarV (para comparar las fechas de A y las del calendario)no me sirve porque en un mismo dia tengo más de una inciedencia y sólo me devuelve un unico valor. ¿ccomo puedo hacelo?

    ResponderEliminar
    Respuestas
    1. Hola, muchas gracias por el apoyo.
      Habría que conocer la distribución de datos en el destino (calendario), pero en principio la combinación de la función INDICE con COINCIDIR te serviría para identificar únicamente una fecha y una incidencia.
      Busca en el blog por que hay varias entradas explicando ejemplos de estas funciones.
      De todas formas si no das con la solución envíame el fichero a
      excelforo@gmail.com
      y le echo un vistazo
      Slds cordiales

      Eliminar
  6. como puedo hacer para validar una celda que solo me permita ingresar numero comprendidos entre 0 y 9, y no letras, con rango de 8 dígitos. como por ejemplo en una celda que contenga valores del DNI puede ser 00230530

    ResponderEliminar
    Respuestas
    1. Hola Eder, que tal?
      Espero te encuentres bien.

      Suponiendo que sea la celda B2 la que quieras validar, Desde validación de datos personalizada incluye la siguiente fórmula:
      =Y(ESNUMERO($B$2);LARGO($B$2)=8)
      sólo permitirá números, no letras, y de ocho dígitos.

      Espero te sriva.
      Un cordial saludo

      Eliminar
    2. esta bien funciona, pero para el caso que el numero empezara con "0" como haria tal es el caso de un numero de dni que empieza con 00

      Eliminar
    3. Hola!!
      Espero sigas bien...
      Prueba con
      =Y(ESNUMERO($B$2);LARGO($B$2)>=6;LARGO($B$2)<=8)

      Un cordial saludo

      Eliminar
    4. Hola ismael,a la verdad ya me deja registrar el campo cuando escribo al principio "0" pero al momento que se muestra en la celda desaparecen los ceros,¿cómo puedo hacer para que muestre elregistro con los ceros al principio? saludos y perdona la molestia

      Eliminar
    5. Hola,
      puedes darle a la celda un formato personalizado tipo 00000000

      con eso será suficiente.
      Slds

      Eliminar
  7. Buenos días hrno gracias por los datos son excelentes.

    ResponderEliminar
  8. Hola te escribo desde Chile, he aprendido mucho con tus datos, por eso Gracias. Mi consulta es que tengo que validar una celda con un rango que permita ingresar números >= 1 y <=7 o una letra P. Se refiere a un rango de calificaciones que puede obtener un alumno y si le faltaesa calificación debe ir una P de pendiente. Te agradeceré una ayuda.

    ResponderEliminar
    Respuestas
    1. Hola Sergio,
      muchas gracias!!
      Tendrás que aplicar una Validación de datos personalizada, con la fórmula:
      =O(Y(celda>=1;celda<=7);celda="P")

      Slds

      Eliminar
  9. Hola, quisiera ver si me pudieses ayudar con un problema, veras, quisiera hacer que en una celda solo deje entrar ciertos valores, dichos valores estan en un rango y ademas dependen del resultado de una lista desplegable, suponiendo que la lista desplegable esta en la celda A2, necesito que si A2= "x", entonces solo acepte valores entre 10,000 y 20,000, si A2= "y", entonces solo acepte valores entre 20000 y 15000, y si A2= "z" entonces solo acepte valores entre 20,000 y 35,000, al hacer una validación personalizada introduje la siguiente formula pero no me entendió, me podrías ayudar?

    O(Y(A2="x";celda>10000;celda<20000);Y(A2="y";celda>20000;celda<35000);Y(A2="z";celda>35000;celda<50000))

    Gracias :)

    ResponderEliminar
  10. Buenas Tardes Amigo que tal, quisiera saber como hago para implementar una validación personalizada donde me muestre solamente tres letras (J,G,V) con un guion y luego 8 numeros aleatorios, ejemplo (J-12345678). No permita otras letras y una longitud de 8 números.

    Muchas Gracias

    ResponderEliminar
    Respuestas
    1. Hola Juan Carlos,
      podrías incluir una Validación de datos personalizada con esta fórmula:
      =Y(O(IZQUIERDA(D6;2)="J-";IZQUIERDA(D6;2)="G-";IZQUIERDA(D6;2)="V-");ESNUMERO(VALOR(DERECHA(D6;8)));LARGO(D6)=10)

      suponiendo D6 como la celda con la validación
      Saludos

      Eliminar
    2. Hola Ismael Romero que tal, excel me muestra un error por pantalla que dice error en la fórmula, con tres descripciones, puedo resumir tiene que ver con los simbolos =, - o comillas simples " ".

      Eliminar
    3. Hola,
      es una fórmula verificada... prueba a escribirla en lugar de copiar y pegarla, o quizá tu configuración tenga como separador de argumentos la coma en vez de punto y coma...
      ??
      Slds

      Eliminar
    4. Este comentario ha sido eliminado por el autor.

      Eliminar
  11. Buenas Tardes Ismael, la configuración regional, tenia lo argumentos invertidos, funciona de maravilla.

    Ismael por casualidad tiene un correo, ya que tengo varias dudas por solucionar.

    Muchas Gracias por tus excelentes aportes, y respuesta inmediata.

    ResponderEliminar
    Respuestas
    1. Hola Juan Carlos,
      mi correo:
      excelforo@gmail.com

      lee, por favor, antes de nada las Normas de Uso del blog.
      Un saludo

      Eliminar
  12. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  13. Bunos Dìas Ismael que tal, necesito saber como hago para colocar en una celda dos valores distinto y que cada uno este condicionado con distinta formulas, para trabajar con la misma celda dentro de cada formula. Tengo la función “=SI(J=10;(A2+B2)*10/100+E2;SI(J=29(C2>D2);D2/2+C2;C2/2+D2)+E2)”, me esta mostrando error de funciòn.

    Explicación: Son dos datos distintos 10% y 15% validado dentro la misma celda, la formula de 10% es (A2+B2)*10/100+E2; y la formula de 15% cada celda tiene un porcentaje distinto, es decir; C2=5% y D=7%, luego de sacar cada porcentaje, se procede a la formula (C2>D2);D2/2+C2;C2/2+D2)+E2).

    Muchas Gracias por tu colaboración

    ResponderEliminar
    Respuestas
    1. Hola,
      no está muy claro qué necesitas... pero en tu fórmula hay varios errores de sintáxis:
      =SI(J2=10;(A2+B2)*10/100+E2;SI(J2=29;(C2>D2);D2/2+C2;C2/2+D2)+E2)
      te lo he marcado en negrita...:
      SI(J=10;...
      debería ser algo así (adivino)
      SI(J2=10;...
      y en
      SI(J=29(C2>D2)
      algo así:
      SI(J2=29;(C2>D2)

      El resto del planteamiento no lo comprendo.

      En todo caso, en otro comentario ya te decía que no comprendía y que si podías exponer el caso de otra forma ;-)
      Slds

      Eliminar
    2. Hola Ismael que tal; aquí te envió la explicación màs detallada.

      * Tenemos dos valores J2=15% y J2=20%; se debe validar en la celda J2 exclusivamente los dos porcentaje con distinta formula, si existe un monto distinto a los porcentaje mostrar un mensaje de error donde muestre que solamente son 15% y 12%;
      • Si en la celda J2 aparece (15%):
      (A2+B2)*10/100+E2
      • Si en la celda J2 aparece (20%):
      (A2>B2);B2/2+A2;A2/2+B2)+E2)
      Nota: Tomando en cuenta que A2 y B2 puede ser cualquier monto definido por el usuario.

      Eliminar
    3. Hola,
      no son compatibles las fórmulas en celdas validadas.. es decir, éstas no se ven afectadas por la regla de validación.
      Una celda validada solo afecta cuando vamos a introducir algún dato....

      Eliminar
  14. Buenas Tardes Ismael Romero que tal, que gusto volver saber de ti, la presente es para resolver un problema que tiene que ver con el formato de fecha corta Excel 2010: Tengo dos hojas, la primera se encarga de ingresar en una celda una fecha dd/mm/aaaa, al guardar con una macro en la otra hoja en una celda la fecha aparece mm/dd/aaaa, es decir; me intercambia el día por el mes. Verifique la fecha en configuración regional, el formato de fecha en ambas celdas (Hoja1, Hoja2), y todo está bien.

    Muy amable por tu colaboración y respaldo.

    ResponderEliminar
    Respuestas
    1. Hola Juan Carlos,
      trabajar con Fechas es siempre complicado, y más cuando las traemos desde VBA...
      Una solución fácil suele consistir en anidar tu variable en VBA con la función CDate(variable), habitualmente funciona bien y te devuelve la Fecha con el formato estándar dd/mm/aaaa

      Si no funcionara, además, aplicaríamos la función
      Format(Cdate(variable),"dd/mm/yyyy")
      en tu código de VB

      Saludos

      Eliminar
  15. Buenos Días Ismael que tal, muchas gracias por tu respuesta, el código VBA que tengo en la macro guardar es este, donde C3 es la celda de fecha.

    Aqui dejo como coloque el codigo el macro guardar, coloque la fecha dentro el parentesis, ya que no permite colocar la de manera de formato (dd/mm/aaaa)

    Texto = CDate("01/01/1900")
    Range("C3") = Texto
    Range("C3") = CDate("01/01/1900")

    ...pero todavia me sigue dando problema

    Muchas Gracias por tu valiosa acesoria.
    Saludo!!!

    ResponderEliminar
    Respuestas
    1. ??
      es suficiente la forma:
      Range("C3").value = CDate("01/01/1900")

      En caso contrario aplica la función format que te indiqué
      Range("C3").value=Format(Cdate("01/01/1900"),"dd/mm/yyyy")

      Slds

      Eliminar
  16. Ismale coloque este codigo en un nuevo modulo

    Sub Cambiarfecha()
    'para col B
    Range("B3").Select
    While ActiveCell.Value <> ""
    'ajustar el formato deseado
    Selection.NumberFormat = "dd/mm/yyyy;@"
    ActiveCell.Offset(1, 0).Select
    Wend
    End Sub

    ... y todavia me presenta el problema.

    ResponderEliminar
    Respuestas
    1. Estás seguro que la configuración regional que tienes de fecha dd/mm/aaaa?

      Eliminar
  17. Hola Ismanel que tal, si la configuración regional esta muy bien configurada, ya lo resolvi, era que tenia una macro para convertir de minuscula a mayuscula, y estaba tomando el rango de fecha, se lo quite y listo.

    Muchas Gracias por tu apoyo y amabilidad

    Saludo!!!!

    ResponderEliminar
  18. Buenos Dìas Ismanel que tal, como esta todo, tengo un combo donde aperecen dos datos distintos, al seleccionar cada datos, tiene que colocar en monto en una celda, que contiene una formula.

    Ejemplo: Si en la Celda A1 se coloca el nro 40, la formula =(A1="40");2+3*0.04), si en la celda A1 se coloca el nro 50, la formula =((A1="50");2+3*0.05), me presenta error de sintasis.

    Necesito tu valiosa colaboración.

    Muchas Gracias

    ResponderEliminar
    Respuestas
    1. Hola Juan Carlos,
      entiendo que al validar el valor seleccionado del Combo en otra celda se añade la fórmula indicada...
      La idea sería incluir como variable el valor del combobox

      celda.formula="=(A1=" & combobox1.value & ");2+3*" & (combobox1.value/1000) & ")"

      Espero te de la idea.

      Saludos

      Eliminar
  19. hola, tengo un problema con una validación, quiero que si una celda me dice "entrada", me deje ingresar en otra celda solo números positivos y si dice "salida"; solo me deje ingresar números negativos, he probado con la funcion si usando >0 o <0 pero a la hora de ingresar el valr siempre me salta como error.. gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      supongamos en A1 pone 'Entrada' o 'Salida' y en la celda B1 debemos añadir un número positivos negativo según tus indicaciones.
      Entonces seleccionas B1 y añades una validación de datos personalizada con la fórmula:
      =O(Y(A1="Entrada";B1>=0);Y(A1="Salida";B1<0))

      Saludos

      Eliminar