Validación de datos personalizada en Excel

Transcripción del video

En esta hoja voy a crear un criterio de validación para las celdas de la columna A que nos ayude a validar que los números ingresados sean pares.

Seleccionaré las celdas de la columna y en la pestaña Datos haré clic en el botón Validación de datos y elegiré la opción Personalizada.

Esta opción habilita un cuadro de texto llamado Fórmula y es aquí donde podemos ingresar la fórmula que se encargará de revisar los valores de cada una de las celdas.

La única condición que debemos cumplir con nuestra fórmula es que el resultado devuelto deberá ser un valor lógico, es decir, debemos obtener un valor falso o verdadero.

Si la fórmula devuelve el valor verdadero, entonces los datos serán considerados como válidos y serán ingresados en la celda.

Por el contrario, si la fórmula devuelve el valor Falso, se mostrará el mensaje de error.

Ingresaré el símbolo igual para iniciar la fórmula, y ya que en este ejemplo queremos aceptar los números pares, ingresaré la función ES.PAR y como argumento ingresaré la referencia a la celda A2 que es la primera celda del rango y cerraré el paréntesis de la función.

Si el número ingresado en la celda es par, la función devolverá el valor Verdadero y por lo tanto será un número válido para la celda, pero si el valor ingresado es impar, la función devolverá el valor falso y se mostrará el mensaje de error.

Pulsaré el botón Aceptar y vamos a probar nuestro criterio de validación de datos.

En la celda A2 ingresaré el número 2 y no habrá problema con la validación de datos.

En la celda A3 ingresaré el número 3 y al pulsar Entrar obtendré el mensaje de validación de datos porque este número ocasiona que la función ES.PAR devuelva un valor falso.

Pulsaré el botón Cancelar e ingresaré el número 4 y no habrá problema alguno con la validación de datos.

Como has visto, la única condición que debemos cumplir con nuestra fórmula es que debe regresar un valor lógico como resultado.

Probemos ahora con un nuevo criterio de validación de datos que solo permita la captura de números impares.

Seleccionaré las celdas de la columna Impares y pulsaré el botón Validación de datos para elegir la opción Personalizada.

Ingresaré el símbolo igual y la función ES.IMPAR y como su único argumento ingresaré la referencia a la celda C2 y pulsaré el botón Aceptar para terminar con la creación de este criterio.

En la celda C2 ingresaré el número 1 y en la celda C3 el número 3, pero al intentar ingresar el número 4, obtendremos el mensaje de error.

Pulsaré el botón Cancelar para terminar con el ingreso a esta celda.

Es probable que ya puedes suponer el tipo de fórmula que debemos utilizar para permitir solamente el ingreso de cadenas de texto.

Seleccionaré las celdas de la columna Texto y pulsaré el botón Validación de datos y elegiré la opción Personalizada.

La función que nos ayuda a validar si un valor es una cadena de texto es la función ESTEXTO, así que ingresaré dicha fórmula en el cuadro de texto correspondiente y como argumento colocaré la referencia a la celda E2 y cerraré el paréntesis de la función.

Pulsaré el botón Aceptar y activaré la celda E2 para ingresar la palabra Excel y no habrá problema con este valor, pero al intentar ingresar el número 5 obtendremos el mensaje de error.

Las fórmulas que hemos utilizado en estos ejemplos han sido muy sencillas, pero en los próximos ejemplos aumentaremos poco a poco la complejidad de las fórmulas.

Activaré la Hoja2 y vamos a validar que las cadenas de texto ingresadas en la primera columna comiencen con la letra A.

Seleccionaré todas las celdas de la columna y pulsaré el botón Validación de datos para crear un criterio personalizado.

La fórmula que nos ayudará a obtener el primer carácter de cualquier cadena de texto utilizará la función IZQUIERDA y en este caso usaremos esta función para evaluar el valor de la celda A2 y cerraré el paréntesis de la función.

Si recordamos la manera en que trabaja la función IZQUIERDA, nos devuelve la primera letra de una cadena de texto por lo que necesitamos compararla con la letra “A” para saber si son iguales.

Ingresaré el símbolo igual y a continuación ingresaré la letra “A” encerrada en comillas dobles.

Tenemos lista nuestra fórmula que devolverá el valor verdadero en caso de que la palabra inicie con la letra A, de lo contrario devolverá el valor falso.

Pulsaré el botón Aceptar y en la celda A2 ingresaré el nombre Arturo.

En la celda A3 ingresaré el nombre Beto y al pulsar Entrar veremos el mensaje de error ya que el nombre no inicia con la letra A.

Pasemos al siguiente ejemplo donde vamos a crear una validación de datos que solo acepte fechas que sean de un día lunes.

Seleccionaré las celdas y pulsaré el botón Validación de datos.

Elijo la opción Personalizada e ingresaré el símbolo igual para comenzar con la fórmula.

La función que devuelve el día de la semana de una fecha es la función DIASEM que de manera predeterminada nos devuelve el número 1 para el domingo y por lo tanto el día lunes tendrá asignado el número 2.

Ingresaré esta función y como argumento colocaré la referencia a la celda C2 y el resultado de esta función lo voy a comprar con el número 2 que representa el día lunes.

Pulsaré el botón Aceptar y en la celda C2 vamos a ingresar la fecha 7 de enero del 2019 y no habrá problema porque ese fue un día lunes.

En la celda inferior ingresaré la fecha 8 de enero del 2019 y en esta ocasión recibiremos un mensaje de error porque esta fecha es de un martes.

Pulsaré el botón Cancelar e ingresaré la fecha 14 de enero del 2019 y será ingresada correctamente en la celda porque es una fecha de un día lunes.

Hagamos otro ejemplo.

Activaré la Hoja3 y vamos a crear un criterio de validación para que la suma de las celdas de la columna Total nunca sobrepase el presupuesto que tenemos indicado en la celda B1.

La manera en que funcionará este criterio será que, en todo momento estaremos sumando el rango E2:E6 y preguntaremos si es menor o igual al valor de la celda B1.

Seleccionaré las celdas de la columna Total y pulsaré el botón Validación de datos, elijo la opción Personalizada e ingreso el símbolo igual de nuestra fórmula.

Comenzaré con la función SUMA e ingresaré el primer paréntesis y directamente en la hoja seleccionaré el rango E2:E6 para incluirlo en la fórmula y en seguida pulsaré la tecla F4 para convertir la referencia en absoluta.

En esta fórmula necesitamos una referencia absoluta de manera que cada celda sume siempre el mismo rango de celdas.

Cierro el paréntesis de la función y voy a introducir el operador “menor o igual que” seguido de la referencia a la celda B1 que contiene el total del presupuesto.

De nuevo pulsaré la tecla F4 porque necesito que la referencia a la celda B1 permanezca estática y termino este criterio de validación de datos pulsando el botón Aceptar.

En seguida ingresaré el número 100 en cada una de las celdas y con esto estaremos en el límite del presupuesto que es 500.

Si intento ingresar el número 101 en alguna de las celdas recibiremos el mensaje de error porque habremos sobrepasado el total de 500 indicado en la celda B1.

Con este ejemplo ya comienzas a darte cuenta que las fórmulas pueden ayudarnos a crear criterios de validación de datos de mayor complejidad que nos ayudarán a cumplir con determinadas reglas en nuestros datos y eso será de mucha ayuda para tener información consistente.

Activaré la Hoja4 para hacer un último ejemplo donde vamos a crear la validación de datos para mostrar un mensaje de error al momento de intentar ingresar un valor duplicado.

Seleccionaré las celdas de la columna y abriré el cuadro de diálogo del comando Validación de datos para crear un criterio personalizado.

Ingresaré el símbolo igual y en esta ocasión utilizaremos la función CONTAR.SI y para su primer argumento seleccionaré con el ratón el mismo rango de celdas que estamos evaluando y pulsaré la tecla F4 para convertir la referencia en absoluta.

El segundo argumento de la función será la referencia a la celda A2 que es la primera celda del rango y cerramos el paréntesis de la función.

Esta fórmula contará las veces que aparece el valor de la celda A2 dentro del rango A2:A15 y ya que el segundo argumento es una referencia relativa, Excel cambiará automáticamente dicha referencia para evaluar la celda A3, luego la celda A4 y así sucesivamente para cada una de las celdas de la columna.

La condición que debe cumplirse en este criterio es que el resultado de la función CONTAR.SI debe ser igual a 1, ya que, si obtuviéramos un valor mayor a 1, querría decir que tenemos un valor que aparece más de una vez y por lo tanto es un valor repetido.

Pulsaré el botón Aceptar y probaremos nuestro criterio de validación de datos.

En la celda A2 ingresaré la palabra uno y en la celda inferior ingresaré la palabra dos.

A continuación, intentaré ingresar de nuevo la palabra uno, pero al pulsar la tecla Entrar recibiremos el mensaje de Error porque la palabra uno ya existe dentro del rango de celdas.

Pulsaré el botón Reintentar e ingresaré la palabra tres y el valor será aceptado en la celda.

Espero que con estos ejemplos te hayas dado cuenta del gran potencial que existe al usar una fórmula en los criterios de validación de datos.

Lo último que puedo decirte es que las fórmulas que podemos utilizar en la validación de datos no tienen restricción alguna y la única condición que debemos cumplir es que deben devolver un valor lógico, es decir, un valor falso o un valor verdadero.