Rango dinámico de la lista de validación de datos de Excel
El tutorial muestra cómo hacer reglas de validación de datos personalizadas en Excel. Encontrará algunos ejemplos de fórmulas de validación de datos de Excel para permitir sólo números o valores de texto en celdas específicas, o sólo texto que comience con caracteres específicos, permitir datos únicos evitando duplicados, y más.
En el tutorial de ayer empezamos a ver la Validación de Datos de Excel – cuál es su propósito, cómo funciona y cómo utilizar las reglas incorporadas para validar los datos en sus hojas de trabajo. Hoy, vamos a dar un paso más allá y hablar de los aspectos esenciales de la validación de datos personalizada en Excel, así como experimentar con un puñado de fórmulas de validación diferentes.
Microsoft Excel tiene varias reglas de validación de datos incorporadas para números, fechas y texto, pero sólo cubren los escenarios más básicos. Si quieres validar celdas con tus propios criterios, crea una regla de validación personalizada basada en una fórmula. A continuación te explicamos cómo hacerlo:
Nota. Todas las reglas de validación de datos de Excel, tanto las integradas como las personalizadas, sólo verifican los datos nuevos que se escriben en una celda después de crear la regla. Los datos copiados no se validan, ni los datos introducidos en la celda antes de crear la regla. Para identificar las entradas existentes que no cumplen con los criterios de validación de datos, utilice la función Círculo de datos no válidos como se muestra en Cómo encontrar datos no válidos en Excel.
¿Cómo puedo crear una lista desplegable rellenable en Excel?
En la pestaña DATOS, seleccione Validación de datos. Haga clic en Validación de datos en la lista desplegable. En el cuadro de diálogo, seleccione Lista en el menú desplegable Permitir. En el campo de origen, escriba las opciones que desea que incluya el menú desplegable, separadas por comas.
¿Cómo funciona la validación de datos en Excel?
La validación de datos de Excel es una función que restringe (valida) la entrada del usuario en una hoja de cálculo. Técnicamente, se crea una regla de validación que controla qué tipo de datos se pueden introducir en una determinada celda.
Excel desplegable
El tutorial explica cómo hacer la validación de datos en Excel: crear una regla de validación para números, fechas o valores de texto, hacer listas de validación de datos, copiar la validación de datos a otras celdas, encontrar entradas no válidas, arreglar y eliminar la validación de datos.
Al configurar un libro de trabajo para sus usuarios, es posible que a menudo desee controlar la entrada de información en celdas específicas para asegurarse de que todas las entradas de datos sean precisas y coherentes. Entre otras cosas, es posible que quieras permitir sólo un tipo de datos concreto, como números o fechas, en una celda, o limitar los números a un determinado rango y el texto a una longitud determinada. Incluso puede querer proporcionar una lista predefinida de entradas aceptables para eliminar posibles errores. La validación de datos de Excel le permite hacer todas estas cosas en todas las versiones de Microsoft Excel 365, 2021, 2019, 2016, 20013, 2010 e inferiores.
Al agregar una regla de validación de datos en Excel, puede elegir una de las configuraciones predefinidas o especificar criterios personalizados basados en su propia fórmula de validación. A continuación, hablaremos de cada una de las opciones incorporadas, y la semana que viene veremos más de cerca la validación de datos de Excel con fórmulas personalizadas en un tutorial separado.
Validación de datos en Excel
Los clientes de QI Macros utilizan Excel para crear formularios para todo tipo de recopilación de datos: hojas de tiempo, cuadros de mando, incluso minibases de datos. Por desgracia, cuando intentan analizarlos con PivotTables, pronto descubren que los humanos son muy creativos a la hora de escribir. Un sistema hospitalario escribía “Medicare” con varias siglas: MDCR, Medcr, Medicr, etc. Esto dificulta cualquier análisis o extracción de datos sin una gran limpieza. La función de validación de datos de Excel puede eliminar la confusión.
El departamento de viajes de una gran empresa pidió a sus agentes de viajes que rastrearan los vuelos utilizando las fechas, los códigos de ruta (por ejemplo, DEN-LAX-DEN) y la ciudad de destino. Los agentes de viajes encontraron formas creativas de dificultar el análisis: fechas incorrectas, intercambiar rutas con destinos, escribir mal los destinos, dejar el guión fuera de la ruta. Y así sucesivamente:
Un cliente intentaba obtener hojas de tiempo válidas en Excel. Las horas se redondeaban a la media hora más cercana. Pero los empleados seguían poniendo valores como 4:30 en lugar de 4,5. Esto es lo fácil que puede ser resolver ese problema con la validación de datos.
Validación de datos en Excel basada en una condición
Muchas hojas de cálculo con las que me he encontrado tienen un problema común: la introducción de datos incoherentes. Si alguien introduce “Estados Unidos de América” en una celda, “Estados Unidos de América ” (espacio extra después de “Unidos” y “América”) en otra celda, y luego desea utilizar “Estados Unidos de América” como criterio de fórmula, funciones como SUMIFS y VLOOKUP no funcionarán correctamente ya que “Estados Unidos de América” no se introdujo de forma consistente en cada área del libro de trabajo.
Una buena forma de evitar este problema es restringir los valores que se pueden introducir en una celda mediante la Validación de Datos. Por ejemplo, si su empresa vende actualmente a Australia, Tailandia, Nueva Zelanda y China, puede introducir estos valores como una lista en una sección de su libro de trabajo y, a continuación, utilizar la Validación de datos para evitar que se introduzcan en una celda nombres de países mal escritos y otras variantes.
La validación de datos es una gran herramienta, pero ¿qué ocurre el mes que viene si su empresa empieza a vender a Indonesia y Rusia? ¿Cómo puede extender automáticamente la validación de datos a las filas siguientes de su tabla de entrada de datos para evitar errores? La mejor manera de resolver este problema es con rangos y tablas dinámicas.