Fórmulas personalizadas de validación de datos

Este tutorial le mostrará cómo crear fórmulas personalizadas en 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 comiencen con cierto texto.

1. Resalte el rango requerido, por ejemplo: B3: B8.
2. En la cinta, 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:

= EXACTO (IZQUIERDA (B3,4), "FRU-")

La fórmula usa 2 funciones EXACTO y IZQUIERDA para determinar si los primeros 4 caracteres ingresados ​​en la celda son idénticos a "FRU-"

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

5. Asegúrese de que la casilla de verificación "Mostrar mensaje de entrada cuando se selecciona la celda”Está marcado y luego escriba el título y el mensaje que necesita.
6. Si desea agregar una alerta de error, haga clic en Alerta de error.

7. Puede cambiar el estilo de Parada a cualquiera Advertencia o Información si lo necesita, y luego escriba su título y mensaje de error.
8. Hacer clic OK.
9. Si ha utilizado el Aporte Opción de mensaje, aparecerá un comentario en la pantalla informando al usuario de la regla.

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

11. Si seleccionaste 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 OK, se le permitirá continuar con los datos incorrectos ingresados ​​en la celda.
14. Hacer clic Cancelar para salir del mensaje o OK para ingresar el texto en la celda.

La validación de datos permite solo mayúsculas en Excel

Podemos escribir una fórmula personalizada para asegurarnos de que los datos en una celda solo permitan mayúsculas si se ingresa texto en la celda.

1. Resalte el rango requerido, por ejemplo: B3: B8.
2. En la cinta, 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:

= EXACTO (B3, SUPERIOR (B3))

La fórmula usa 2 funciones EXACTO y SUPERIOR para determinar si el texto ingresado en la celda está en mayúsculas. Las celdas con una combinación de número y texto se consideran texto y los números se ignoran en la regla.

4. Hacer clic OK.
5. Escriba "fru-124" en la celda B3.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.
8. Escriba "123456" en la celda B3.
9. Esto se permitirá ya que es un número y no un texto.

En nuestro siguiente ejemplo, nos aseguraremos de que solo se pueda ingresar texto en mayúsculas en la celda.

La validación de datos permite solo texto en mayúsculas en Excel

Podemos escribir una fórmula personalizada en Validación de datos que se puede usar para garantizar que los datos en una celda solo permitan texto en mayúsculas

NOTA: si ingresa información en una celda que comienza con texto pero contiene números, Excel considerará el texto de información.

1. Resalte el rango requerido, por ejemplo: B3: B8.
2. En la cinta, 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 (EXACTO (B3, SUPERIOR (B3)), ESTEXTO (B3))

La fórmula usa 4 funciones Y, EXACTO, SUPERIOR y TEXTO para determinar si el texto ingresado en las celdas está en mayúsculas Y para determinar si la información ingresada es en realidad texto y no un número puro.

4. Hacer clic OK.
5. Escriba "fru-124" en la celda B3.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.
8. Escriba "123456" en la celda B3.
9. Volverá a recibir el mensaje de error.
10. Haga clic en Cancelar para salir del mensaje o en Reintentar para volver a ingresar el texto correcto en la celda.

La validación de datos evita espacios en Excel

Podemos escribir una fórmula personalizada para asegurarnos de que no se ingresen espacios en los datos ingresados ​​en un rango de celdas.

1. Resalte el rango requerido, por ejemplo: B3: B8.
2. En la cinta, 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 existan espacios.

4. Hacer clic OK.
5. Escriba "FRU - 124" en la celda B4.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.

La validación de datos evita duplicados en Excel

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

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

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

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

4. Hacer clic OK.
5. Escriba “FRU-123” en la celda D4.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.

La validación de datos existe en la lista en Excel

Podemos escribir una fórmula personalizada para asegurarnos de que solo se ingrese texto específico en una celda.

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

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

La fórmula usa el CONTAR función y usos ABSOLUTOS en el rango F3: F8 para asegurar que esta es la lista que el CONTAR la función mira cuando verifica que se esté ingresando el texto correcto.

4. Hacer clic OK.
5. Escriba "Único" en la celda D4.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.

La validación de datos no existe en la lista en Excel

Podemos usar una fórmula personalizada para asegurarnos de que no se ingrese texto específico en una celda.

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

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

La fórmula usa el CONTAR función y usos ABSOLUTOS en el rango F3: F8 para asegurar que esta es la lista que el CONTAR la función mira cuando verifica que se esté ingresando el texto correcto.

4. Hacer clic OK.
5. Escriba "Carne de res" en la celda C4.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.

Validación de datos solo se ingresan números en Excel

Podemos usar una fórmula personalizada para asegurarnos de que solo se ingrese un número en una celda.

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

= ESNÚMERO (F3: F8)

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

4. Hacer clic OK.
5. Escriba "nueve" en la celda F4.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.

La validación de datos no excede el valor en Excel

Podemos usar una fórmula personalizada para asegurarnos de que los valores ingresados ​​en una celda no excedan un valor especificado.

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

= E3 <= $ G $ 6

La fórmula un ABSOLUTO en el rango G6 para asegurar que este es el valor que la regla verifica cuando se ingresan datos en E3.

4. Hacer clic OK.
5. Escriba "9" en la celda E4.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.

La validación de datos no supera el total en Excel

Podemos usar una fórmula personalizada para asegurarnos de que los valores ingresados ​​en un rango de celdas no excedan un valor total especificado para el rango

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

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

La fórmula usa el SUMA función y usos ABSOLUTOS en el rango F3: F8 para asegurar que esta es la lista que el SUMA La función mira cuando verifica que el total del rango no sea mayor que el valor ingresado en H6.

4. Hacer clic OK.
5. Escriba "40" en la celda F4.
6. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. 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 ingresar el texto correcto en la celda.

Fechas de los días de la semana de validación de datos solo en Excel.

Podemos usar una fórmula personalizada para asegurarnos de que solo se ingresen los días de la semana cuando las fechas se usan en Excel.

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

= DÍA DE LA SEMANA (F3,2) <6

La función de día de la semana comprueba para asegurarse de que el día incluido en la fecha no sea sábado ni domingo.

4. Modifique la fecha en G5 para mostrar un sábado (por ejemplo, 9^th Mayo de 2022).
5. Si ha utilizado la opción Alerta de error, aparecerán su advertencia personalizada y su mensaje de error. Si no ha utilizado esta opción, aparecerá la advertencia estándar.

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

Validación de datos Fecha futura solo en Excel

Podemos crear una fórmula personalizada para permitir que el usuario solo ingrese una fecha futura.

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

= G3> HOY ()

La fórmula usa el HOY DIA función para verificar si la fecha ingresada en la celda es mayor que la fecha de hoy.

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

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

La validación de datos debe comenzar con en Hojas de cálculo de Google

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á completo.

4. Seleccione Fórmula personalizada es de la lista desplegable Criterios.

5. Escribe la fórmula.

= EXACTO (IZQUIERDA (B3,4), "FRU-")

6. Seleccione cualquiera 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 necesita.

8. Clic en Guardar.

9. Haga clic en B3 para ver el texto de ayuda de Validación

10. Tipo FRI-123
11. Si ha seleccionado Mostrar advertencia, Aparecerá el siguiente mensaje.

12. Alternativamente, si ha seleccionado Rechazar entrada en datos no válidos, no podrá ingresar los datos y aparecerá el siguiente mensaje en la pantalla.

El resto de los ejemplos de fórmulas personalizadas en Google Sheets funcionan exactamente de la misma manera.