Fórmulas Personalizadas de Validación de Datos

Este tutorial le mostrará cómo crear fórmulas personalizadas en la Validación de Datos en Excel y Google Sheets

Validación de Datos – Debe Comenzar Con – Excel

Podemos escribir una fórmula personalizada para asegurarnos de que los datos de una celda empiezan por un texto determinado.

 

1. Resalte el rango requerido, por ejemplo: B3:B8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.

 

3. Seleccione Personalizado en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=IGUAL(IZQUIERDA(B3;4);»FRU-«)

La fórmula utiliza 2 funciones IGUAL e IZQUIERDA para determinar si los 4 primeros caracteres introducidos en la celda son idénticos a «FRU-»

4. Si desea añadir un mensaje de entrada para su usuario, haga clic en Mensaje de entrada.

 

5. Asegúrese de que la casilla«Mostrar mensaje de entrada cuando se selecciona la celda» está marcada y, a continuación, escriba el título y el mensaje que desee.
6. Si desea añadir una alerta de error, haga clic en Mensaje de error.

 

7. Puede cambiar el Estilo de Parada a Advertencia o Información si lo desea, y luego escriba el título y el mensaje de error.
8. Haga clic en Aceptar.
9. Si ha utilizado la opción Mensaje de entrada, aparecerá un comentario en la pantalla informando al usuario de la regla.

 

10. Escriba «FRI-124» en la celda B3. Si ha seleccionado Alto, aparecerá el siguiente cuadro de mensaje.

 

 

11. Si ha seleccionado Advertencia, aparecerá este cuadro de mensaje. Esto le permite continuar si determina que los datos son correctos.

12. Si ha seleccionado Información, aparecerá este cuadro de mensaje.

 

13. Si hace clic en Aceptar, se le permitirá continuar con los datos incorrectos introducidos en la celda.
14. Haga clic en Cancelar para salir del mensaje o en Aceptar para introducir el texto en la celda.

Validación de Datos – Permitir Sólo Mayúsculas en Excel

Podemos escribir una fórmula personalizada para asegurar que los datos de una celda sólo permitan mayúsculas si se introduce texto en la celda.

1. Resalte el rango requerido, por ejemplo: B3:B8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Personalizado en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=IGUAL(B3;MAYUSC(B3))

La fórmula utiliza dos funciones IGUAL y MAYUSC para determinar si el texto introducido en la celda está en mayúsculas. Las celdas con una mezcla de número y texto se consideran texto y los números se ignoran en la regla.

4. Haz clic en Aceptar.
5. Escriba «fru-124» en la celda B3.
6. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

 

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.
8. Escriba «123456» en la celda B3.
9. Esto será permitido ya que es un número y no un texto.

En nuestro siguiente ejemplo, nos aseguraremos de que sólo se pueda introducir texto en mayúsculas en la celda.

Validación de Datos – Permitir Sólo Texto en Mayúsculas en Excel

Podemos escribir una fórmula personalizada en Validación de datos puede ser utilizada para asegurar que los datos en una celda sólo permiten texto en mayúsculas

NOTA: si introduce información en una celda que comienza con texto pero contiene números, Excel considerará la información como texto.

 

1. Resalte el rango requerido, por ejemplo: B3:B8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Personalizado en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=Y(IGUAL(B3;MAYUSC(B3));ESTEXTO(B3))

 

La fórmula utiliza 4 funciones Y, IGUAL, MAYUSC y ESTEXTO para determinar si el texto introducido en las celdas está en mayúsculas Y para determinar si la información introducida es realmente texto y no un número puro.

4. Haga clic en Aceptar.
5. Escribe «fru-124» en la celda B3.
6. Si ha utilizado la opción de Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

 

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.
8. Escriba «123456» en la celda B3.
9. Volverá a aparecer el mensaje de error.
10. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.

Validación de Datos – Evitar Espacios en Blanco en Excel

Podemos escribir una fórmula personalizada para asegurar que no se introducen espacios en los datos introducidos en un rango de celdas.

 

1. Resalte el rango requerido, por ejemplo: B3:B8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Personalizado en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=B3=SUSTITUIR(B3;» «;»»)

 

La fórmula utiliza la función SUSTITUIR para comprobar que no existen espacios.

4. Haga clic en Aceptar.
5. Escriba «FRU – 124» en la celda B4.
6. Si ha utilizado la opción de Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

 

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.

Validación de Datos – Evitar Duplicados en Excel

Podemos escribir una fórmula personalizada para evitar que introduzcamos información duplicada en un rango de celdas.

1. Resalte el rango requerido, por ejemplo: B3:B8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Cliente en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=CONTAR.SI($B$3:$B$8;B3)<2

 

La fórmula utiliza la función CONTAR.SI y utiliza VALORES ABSOLUTOS en el rango B3:B8 para asegurar que esta es la lista que la función CONTAR.SI mira cuando comprueba si hay valores duplicados.

4. Haga clic en Aceptar.
5. Escriba «FRU-123» en la celda D4.
6. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

 

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.

Validación de Datos – Existe en la lista en Excel

Podemos escribir una fórmula personalizada que garantice que sólo se introduzca un texto específico en una celda.

 

1. Resalte el rango requerido, por ejemplo D3:D8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Personalizado en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=CONTAR.SI($F$6:$F$8;D3)>0

 

La fórmula utiliza la función CONTAR.SI y utiliza VALORES ABSOLUTOS en el rango F6:F8 para asegurar que esta es la lista que la función CONTAR.SI mira cuando comprueba que se está introduciendo el texto correcto.

4. Haz clic en Aceptar.
5. Escriba «Unidad» en la celda D4.
6. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

 

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.

Validación de Datos – No Existe en la Lista en Excel

Podemos utilizar una fórmula personalizada para asegurar que un texto específico no se introduzca en una celda.

 

1. Resalte el rango requerido, por ejemplo C3:C8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Cliente en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=CONTAR.SI($F$6:$F$8;C3)=0

 

La fórmula utiliza la función CONTAR.SI y utiliza VALORES ABSOLUTOS en el rango F6:F8 para asegurar que esta es la lista que la función CONTAR.SI mira cuando comprueba que se está introduciendo el texto correcto.

4. Haga clic en Aceptar.
5. Escriba «Bistec» en la celda C4.
6. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

 

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.

Validación de Datos – Permitir Sólo Números en Excel

Podemos utilizar una fórmula personalizada para asegurar que sólo se introduce un número en una celda.

 

1. Resalte el rango requerido, por ejemplo: F3:F8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Cliente en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=ESNUMERO(F3:F8)

 

La fórmula utiliza la función ESNUMERO para garantizar que se introduzca un número en las celdas del rango.

4. Haz clic en Aceptar.
5. Escriba «nueve» en la celda F4.
6. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.

Validación de Datos – No Excede el Valor en Excel

Podemos utilizar una fórmula personalizada para asegurarnos de que los valores introducidos en una celda no superan un valor especificado.

1. Resalte el rango requerido, por ejemplo: E3:E8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Cliente en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=E3<=$G$6

 

La fórmula un ABSOLUTO en el rango G6 para asegurar que éste es el valor que la regla comprueba cuando se introducen los datos en E3.

4. Haga clic en Aceptar.
5. Escriba «9» en la celda E4.
6. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.

Validación de Datos – No Excede el Total en Excel

Podemos utilizar una fórmula personalizada para asegurarnos de que los valores introducidos en un rango de celdas no superan un valor total especificado para el rango

 

1. Resalte el rango requerido, por ejemplo F3:F8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Cliente en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=SUMA($F$3:$F$8)<=$H$6

 

La fórmula utiliza la función SUMA y utiliza VALORES ABSOLUTOS en el rango F3:F8 para asegurar que esta es la lista que la función SUMA mira cuando comprueba que el total del rango no es mayor que el valor introducido en H6.

4. Haga clic en Aceptar.
5. Escriba «40» en la celda F4.
6. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

7. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a introducir el texto correcto en la celda.

Validación de Datos – Sólo Fechas de Días de la Semana en Excel.

Podemos utilizar una fórmula personalizada para asegurar que sólo se introducen los días de la semana cuando se utilizan las fechas en Excel.

 

1. Resalte el rango requerido, por ejemplo G3:G8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Cliente en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=DIASEM(F3;2)<6

La función de día de la semana comprueba que el día contenido en la fecha no es un sábado o un domingo.

4. Modifique la fecha en G5 para que aparezca un sábado (por ejemplo,el 9 de mayo de 2020).
5. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

7. Haga clic en Cancelar o Reintentar para introducir una fecha alternativa.

Validación de Datos – Sólo Fecha Futura en Excel

Podemos crear una fórmula personalizada para que sólo permita al usuario introducir una fecha que esté en el futuro.

1. Resalte el rango requerido, por ejemplo G3:G8.
2. En la cinta de opciones, seleccione Datos > Herramientas de datos > Validación de datos.
3. Seleccione Cliente en el cuadro desplegable Permitir y, a continuación, escriba la siguiente fórmula

=G3>HOY()

La fórmula utiliza la función HOY para comprobar si la fecha introducida en la celda es mayor que la fecha de hoy.

4. Modifique la fecha en G5 por la de ayer.
5. Si ha utilizado la opción Alerta de error, aparecerá su mensaje de advertencia y error personalizado. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

6. Haga clic en Cancelar o Reintentar para introducir una fecha alternativa.

Validación de Datos – Debe Comenzar en Google Sheets

 

1. Resalte el rango requerido, por ejemplo: B3:B8.
2. En el menú, seleccione Datos > Validación de datos.

 

3. El rango de celdas ya estará rellenado.

 

4. Seleccione Fórmula personalizada en la lista desplegable Criterios.

 

5. Escriba la fórmula.

=IGUAL(IZQUIERDA(B3;4);»FRU-«)

6. Seleccione Mostrar advertencia o Rechazar entrada si los datos no son válidos.

 

7. Puede escribir algún texto de ayuda de validación si lo requiere.

 

8. Haga clic en Guardar.

 

9. Escriba FRI-123
10. Si ha seleccionado Mostrar advertencia, aparecerá el siguiente mensaje.

 

11. 11. Si ha seleccionado Rechazar Entrada en Datos Inválidos, se le impedirá introducir los datos y aparecerá el siguiente mensaje en la pantalla.

El resto de los ejemplos de fórmulas personalizadas en Google Sheets funcionan exactamente igual.