10 Reglas y Técnicas de Validación de Datos – Excel y Google Sheets

La Validación de Datos es una herramienta de Excel que te permite restringir qué entradas son válidas en una celda. Aquí tienes 10 reglas y técnicas que te ayudarán a sacar el máximo partido de la Validación de Datos y sus múltiples funciones.

1. Utilizar Listas Desplegables
2. Validación de Datos Basada en Otra Celda
3. Consejos y Mensajes de Error
4. Opciones y Ajustes
5. Formatos de Fecha y Hora
6. Establecer un Límite de Caracteres
7. Validar Números de Teléfono y Correos Electrónicos
8. Fórmulas Personalizadas
9. Cambiar o Eliminar Reglas de Validación de Datos
10. Problemas y Correcciones

 

 

Utilizar Listas Desplegables

El tipo de validación de datos más útil es la función de lista desplegable. Para obtener instrucciones sobre cómo crear una lista desplegable, consulte Crear o Agregar una Lista Desplegable en Excel y Google Sheets.

 

Hay muchos usos para las listas desplegables y muchas formas de crearlas. Por ejemplo, una simple lista desplegable Lista Desplegable Si/No como la que se muestra a continuación puede añadirse como indicador rápido y fiable en una hoja de cálculo.

 

También puede utilizar VBA para crear una lista desplegable en una celda. Introduzca este código en el editor VBA:

Sub ListaDesplegable_en_VBA()
    Range("A2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="Naranja,Manzana,Mango,Pera,Melocotón"
End Sub

Listas Desplegables Dinámicas

• Cuando se crea una lista desplegable basada en un rango de celdas utilizando una regla de validación de datos, si los datos de ese rango específico cambian, los valores de la lista desplegable también cambian.

 

• Vaya un paso más allá con una lista desplegable en cascada basada en el valor seleccionado en otra lista. En el ejemplo siguiente, el usuario ha seleccionado LG como marca de celular. La lista de Modelos de celular sólo muestra los modelos de LG. Si el usuario seleccionara Apple o Samsung, la lista cambiaría para mostrar los modelos correspondientes a esas marcas de celular.

 

• Un método alternativo es utilizar una Sentencia SI dentro de la función de validación de datos para mostrar una lista específica que cambie según lo que seleccione el usuario. En el ejemplo siguiente, si el usuario selecciona Mostrar fruta, se mostrará la lista desplegable de frutas, mientras que si selecciona Mostrar carnes se mostrará la lista desplegable de carnes.

 

Filtrar Datos con una Lista Desplegable

Puede utilizar la herramienta de filtro de Excel junto con una lista desplegable para ampliar la funcionalidad.

• Los filtros y las listas desplegables son similares e incluso utilizan la misma combinación de teclas (CTRL + MAYÚS + L). En un contexto de validación de datos, la combinación de teclas activa un menú desplegable.

 

• Una Lista Desplegable también puede utilizarse como filtro en Excel. Permite extraer filas de datos que coincidan con la entrada de la lista desplegable y devolver estas filas a un área separada de la hoja de cálculo.

 

Herramientas de Formato y Visualización de Listas Desplegables

• Si desea crear una lista desplegable en la que el color de fondo color de fondo dependa del texto seleccionado puede utilizar el Formato condicional para modificar el color de fondo cuando se selecciona un elemento de la lista.

 

• Cuando se crea una lista desplegable, el valor por defecto de la celda donde se ha colocado la lista suele estar en blanco. Puede ser útil rellenar previamente esta celda con un valor por defecto o con un mensaje para el usuario, como Seleccionar…

 

• Una vez que haya creado una lista desplegable, puede utilizar las funciones ORDENAR y UNICOS para ordenar la lista desplegable en orden alfabético.

 

• Para que una lista desplegable sea más legible, puede agrupar una lista de elementos por categorías (modelos de teléfono en grupos de marcas, por ejemplo).

 

Autocompletar Desplegable

Autocompletar es una función útil de Excel que puede reducir el número de caracteres que hay que escribir en cada celda. La funcionalidad estándar de validación de datos desplegable no soporta Autocompletar. Sin embargo, puedes aprender aquí cómo aproximar Autocompletar con una lista desplegable. Así, cuando empiece a escribir las primeras letras de una entrada en la lista desplegable, aparecerá automáticamente el resto de la entrada.

 

Lista Desplegable Rellena Otra Celda

• Al seleccionar un elemento de una lista desplegable, se puede rellenar una celda diferente en Excel y Google Sheets. Por ejemplo, en el gráfico siguiente, se selecciona Patel de la lista, y la celda inferior se rellena con el valor 34. Puedes aprender a hacer esto haciendo clic aquí.

 

• Mediante una lista desplegable, también puede insertar una imagen en otra celda automáticamente.

 

Validación de Datos Basada en Otra Celda

Al introducir valores en Excel, puede restringir los datos que se introducen en cualquier rango de celdas basándose en el texto o en un valor de otra celda.

 

Sugerencias y Mensajes de Error

• Al crear una lista desplegable, puede escribir un mensaje de entrada personalizado que aparece en pantalla cuando se selecciona una celda. Este mensaje de entrada también puede denominarse Sugerencia.

 

• También puede personalizar el Mensaje de error que se devuelve al usuario si se introducen datos no válidos en una celda.

 

Opciones y Ajustes

• Hay siete tipos de datos diferentes que puede seleccionar en la casilla Permitir de los Criterios de Validación: Número entero, Decimal, Lista, Fecha, Hora, Longitud de texto y Personalizado.

• La opción Omitir blancos sólo está disponible si está editando una celda que tiene esa opción desactivada.

 

• También puede elegir, al editar una regla de validación de datos, si desea o no aplicar estos cambios a otras celdas con la misma configuración.

Formatos de Fecha y Hora

Puede utilizar la Validación de datos para restringir el formato introducido en una celda para asegurarse de que se trata de una fecha, y también para restringir la entrada que debe establecerse entre una fecha de inicio y una fecha final.

 

 

Establecer un Límite de Caracteres

En Excel, el número de caracteres permitidos en una celda es 32767. Sin embargo, puedes establecer tu propio límite de caracteres para una celda de texto.

 

Validar Números de Teléfono y Correos Electrónicos

• Puede utilizar la Validación de datos para asegurarse de que un número de teléfono se introduce correctamente en Excel.

 

• También puede utilizar la Validación de datos para asegurarse de que una dirección de correo electrónico contenga un signo @ y no contenga espacios ni comas.

 

Fórmulas Personalizadas

Puede escribir una fórmula personalizada para crear sus propias reglas de validación de datos. En el ejemplo siguiente, la fórmula garantiza que los datos de una celda empiecen por un texto determinado.

 

Ejemplos de Fórmulas Personalizadas

• Puede utilizar la función Función INDIRECTO en Excel junto con una lista desplegable de validación de datos para crear una referencia de celda a partir de texto.

 

• Utilice la función Función ESNUMERO para comprobar que los datos introducidos son, de hecho, un número. La validación de datos tiene incorporada la opción Número entero, pero puede generalizarla aún más con una fórmula personalizada.

 

• Utilice una función Función ESTEXTO para comprobar si hay texto. El ejemplo siguiente sólo permite valores de texto en las celdas con validación de datos aplicada.

 

• Puede utilizar un desplegable de validación de datos con la función Función CONTAR.SI para contar las celdas que cumplen ciertos criterios y devolver un total.

 

• CONTAR.SI también es útil para evitar que los usuarios introduzcan valores duplicados.

 

• Una matriz de puntuación de riesgo es una matriz que se utiliza durante la evaluación de riesgos para calcular un valor de riesgo introduciendo la probabilidad y la consecuencia de un suceso. Puede utilizar una Lista Desplegable de validación de datos junto con las funciones COINCIDIR y BUSCARV para crear una matriz de puntuación de riesgo .

Cambiar o Eliminar Reglas de Validación de Datos

• Si tiene una regla de validación de datos establecida en Excel, puede encontrar cualquier valor restringido seleccionando Validación de datos en el menú Ir a Especial.

 

• Puede copiar reglas de validación de datos de un conjunto de celdas a otro conjunto de celdas utilizando la función Pegado especial (CTRL + ALT + V).

 

• Si ya tiene reglas establecidas en una hoja de cálculo, puede modificar las reglas de validación en caso de que cambien sus requisitos.

 

• Puede modificar el contenido de una lista desplegable siguiendo las instrucciones de este tutorial.

 

• Puede actualizar fácilmente (cambiar, ampliar o tener menos elementos) una Lista Desplegable añadiendo o eliminando elementos del rango de origen de la lista.

 

• Si la validación de datos en un rango ya no es necesaria, puede eliminarla.

 

• Si ya no necesita una lista desplegable, puede eliminar fácilmente la configuración de validación de datos siguiendo las instrucciones de este tutorial.

 

Problemas y Soluciones

• Si tienes un valor en Google Sheets que no coincide con los elementos de tu lista desplegable (un elemento no válido), aparecerá un triángulo rojo en la esquina superior izquierda de la celda. Para deshacerte de este triángulo, puedes seguir los pasos de este tutorial.

 

• Existen varias razones por las que su regla de validación de datos o lista desplegable no funciona correctamente. Puede solucionar estos problemas consultando este tutorial.

 

• Un error de incompatibilidad puede producirse al ejecutar código VBA. El error detiene por completo la ejecución del código e indica el error mediante un cuadro de mensaje. Una de las formas de evitar que esto ocurra es utilizar la Validación de Datos en la hoja de cálculo para evitar que el usuario cause errores en la hoja de cálculo en primer lugar. Sólo permítales introducir valores que no causen errores en la hoja de cálculo.