Autor: Oscar Cronquist Artículo actualizado en Abril 21, 2020

Este artículo muestra cómo configurar Validación de datos para controlar lo que el usuario de Excel puede ingresar. La condición es que no puede haber dos registros idénticos en la tabla de Excel.

La imagen de arriba muestra una advertencia de que el usuario de Excel intentó ingresar un registro duplicado que no es válido, el cuadro de diálogo le dice que "Este valor no coincide con las restricciones de validación de datos definidas para esta celda.

Hay tres botones disponibles, "Reintentar", "Cancelar" y "Ayuda" en el cuadro de diálogo. El botón "Reintentar" deja el valor tal como está pero seleccionado, esto le permite editar el valor que acaba de ingresar. El botón "Cancelar" elimina el valor que acaba de ingresar. El botón "Ayuda" abre una página web en el Soporte de Microsoft que explica cómo funciona la Validación de datos.

Tenga en cuenta que todavía es posible copiar y pegar valores en la tabla de Excel sin que aparezca la advertencia del cuadro de diálogo. Ahora se ve una flecha verde en cada esquina de la celda del registro que le indica que no es válido.

Crear una tabla de Excel

An Tabla de Excel le permite aplicar dinámicamente la validación de datos a los nuevos datos, lo que significa que si ingresa un nuevo registro debajo del conjunto de datos, también tendrá las mismas reglas de validación de datos automáticamente que el resto de los datos.

Con esta configuración no hay necesidad de ajustar los rangos de celdas cuando se agregan o eliminan nuevos datos, las Tablas de Excel lo hacen por usted al instante.

  1. Seleccione cualquier celda en el conjunto de datos.
  2. Presione las teclas de método abreviado CTRL + T para abrir el cuadro de diálogo "Crear tabla", ver imagen de arriba
  3. Activar / desactivar la casilla de verificación "Mi tabla tiene encabezados" en consecuencia.
  4. Haga clic en el botón "Aceptar" para aplicar la configuración y crear una tabla de Excel.

El conjunto de datos ahora es una tabla de Excel que puede ver por el formato de celda y las flechas al lado de los encabezados de columna. Si lo desea, puede cambiar el estilo de la tabla de Excel y eliminar las flechas de "Filtro" junto a los encabezados de columna.

Aparece una nueva pestaña en la cinta denominada "Diseño de tabla" si selecciona en cualquier celda de la tabla de Excel, le permite cambiar las opciones y estilos de la tabla.

Aplicar validación de datos

Validación de datos le permite controlar lo que el usuario de Excel puede y no puede ingresar utilizando diferentes métodos. Vamos a utilizar una fórmula de "Validación de datos" que activará una advertencia de cuadro de diálogo si no se cumplen las condiciones.

  1. Seleccione los datos en su tabla de Excel, seleccioné el rango de celdas B3: D9.
  2. Ir a la pestaña "Datos" en la cinta.
  3. Haga clic en el botón "Validación de datos".
  4. Elija Personalizado, vea la imagen de arriba.
  5. Escriba en el campo "Fórmula:":
    =COUNTIFS(INDIRECT("Table1[First name]"), $B3, INDIRECT("Table1[Last name]"), $C3, INDIRECT("Table1[Date]"), $D3)<=1
  6. Haga clic en el botón Aceptar para aplicar la configuración y crear "Validación de datos" al rango de celdas B3: D8

Si ingresa un registro duplicado, aparece el siguiente mensaje de error.

Explicando la fórmula de validación de datos en la fila 9

Para aprender cómo funcionan las fórmulas con mayor detalle, recomiendo la herramienta "Evaluar fórmula" que está integrada en Excel, también puede usar esta herramienta para fórmulas de "Validación de datos". Copie la fórmula "Validación de datos" y péguela en una celda.

Pegué la fórmula en la celda F3 y luego presioné Enter. Seleccione la celda F3, haga clic en la pestaña "Fórmulas" en la cinta. Haga clic en el botón "Evaluar fórmulas" para mostrar el cuadro de diálogo "Evaluar fórmula", vea la imagen de arriba.

La Función INDIRECTA es un programa que se imparte en función volátil lo que significa que recalcula cada vez que Excel recalcula, esto puede hacer que sea más intensivo en CPU si se usa ampliamente. Esta función es necesaria para hacer referencia a valores a una tabla de Excel en una fórmula de validación de datos.

Por eso aparece el texto de advertencia "Una función en esta fórmula hace que el resultado cambie cada vez que se calcula la hoja de cálculo. El paso de evaluación final coincidirá con el resultado en la celda, pero los pasos intermedios pueden no " en el cuadro de diálogo "Evaluar fórmula", vea la imagen de arriba.

Las expresiones subrayadas son lo que se va a evaluar en el siguiente paso y el resultado son valores en cursiva. Haga clic en el botón "Evaluar" para pasar al siguiente paso de cálculo en la fórmula. Haga clic en el botón "Cerrar" para cerrar el cuadro de diálogo.

Paso 1 - Cómo hacer referencia a valores en fórmulas de validación de datos de Excel

No puede hacer referencia a tablas de Excel en las fórmulas de validación de datos, sin embargo, hay una solución alternativa. La función INDIRECTA le permite hacer referencia a tablas de Excel.

Las referencias a tablas de Excel se denominan "referencias estructuradas" y no cambian cuando se agregan o eliminan valores o registros en la tabla de Excel.

Tabla1 [Nombre]

se convierte en

INDIRECTO ("Tabla1 [Nombre]")

La desventaja es que debe cambiar las fórmulas si cambia el nombre de la tabla o los nombres de encabezado de la tabla en consecuencia, no cambian automáticamente en este caso.

Paso 2: cuenta cuántas veces existe un registro en una tabla

La Función COUNTIFS le permite contar celdas en función de múltiples condiciones, vamos a contar filas en función de los valores que ingrese el usuario de Excel en la Tabla de Excel.

CONDADOS (criteria_range1, criteria1, [criteria_range2, criteria2]…)

Utilizaremos tantos pares de criterios (rangos y criterios) como columnas en la tabla de Excel. Usaré seis argumentos ya que hay tres columnas en mi tabla de Excel. Debe ajustar eso a su tabla de Excel específica.

COUNTIFS (INDIRECTO ("Table1 [Nombre]"), $ B3, INDIRECT ("Table1 [Apellido]"), $ C3, INDIRECT ("Table1 [Fecha]"), $ D3)

se convierte en

CONDADOS ($ B $ 3: $ B $ 9, $ B3, $ C $ 3: $ C $ 9, $ C3, $ C $ 3: $ D $ 9, $ D3)

se convierte en

CONDADOS ({"Stieg"; "Jonas"; "Camilla"; "Lars"; "Henning"; "Jan"}, $ B3, {"Larsson"; "Jonasson"; "Läckberg"; "Kepler"; "Mankell" ";" Guillou "}, $ C3, {40807; 41324; 41215; 40777; 41081; 40970}, $ D3)

se convierte en

CONDADOS ({"Stieg"; "Jonas"; "Camilla"; "Lars"; "Henning"; "Jan"; "Stieg"}, "Stieg", {"Larsson"; "Jonasson"; "Läckberg"; " Kepler ";" Mankell ";" Guillou ";" Larsson "}," Larsson ", {40807; 41324; 41215; 40777; 41081; 40970; 40807}, 40807)

y devuelve 1 en la celda F3.

Paso 3: compruebe si el número es menor o igual que 1

El signo menor que y el signo igual juntos significan que el número debe ser igual o menor que 1 para que sea verdadero.

COUNTIFS (INDIRECTO ("Tabla1 [Nombre]"), $ B3, INDIRECTO ("Tabla1 [Apellido]"), $ C3, INDIRECTO ("Tabla1 [Fecha]"), $ D3) <= 1

se convierte en

1 <= 1

y devuelve TRU. El mensaje de error "Validación de datos" no aparece.

Artículos recomendados