Cómo organizar la verificación de condición en Excel. Comprobación de datos de Excel. E. Usar referencias a otras hojas en las reglas.

programa excel le permite controlar los datos ingresados ​​en las celdas de la tabla. Es decir, especifique el tipo y rango de datos que se pueden ingresar en la celda; todos los demás datos se consideran incorrectos y no se pueden ingresar en la celda. También puede establecer un rango de números válidos, por ejemplo, del 1 al 100, fechas, por ejemplo, no anteriores fecha actual, máximo máximo o valor mínimo, número de caracteres introducidos, etc. Si los datos ingresados ​​en una celda no cumplen con las reglas especificadas, en la pantalla, después de presionar la tecla , aparece una ventana de mensaje de error.

Para controlar los datos de entrada, es necesario definir la condición para verificar estos datos:
• 1. Seleccione una celda o grupo de celdas para las cuales necesita definir un procedimiento de control.
• 2. En la pestaña Datos -> Trabajar con datos, haga clic en el botón Validación de datos.
• 3. En la pestaña Opciones de la ventana Validación de valor de entrada, establezca la condición de validación de datos.

La condición de verificación se establece seleccionando las listas desplegables Tipo de datos y Valor, respectivamente, el tipo de datos (entero, Número Real, fecha, etc.) que se consideran correctas, y reglas de comparación (entre, mayor que, menor, etc.), así como límites del rango de valores aceptables (campos Mínimo y Máximo). Por ejemplo, para establecer una condición que signifique que solo se puede ingresar un número entero del 1 al 99 en una celda, debe seleccionar Entero en la lista Tipo de datos, seleccionar la opción entre en la lista Valor e ingresar 1 y 99 en el Mínimo. y campos Máximo, respectivamente.

Si una celda tiene una regla de validación definida y el valor que el usuario ingresó en la celda es incorrecto, el resultado de presionar la tecla o intentar pasar a otra celda de alguna otra manera (presionando una tecla o haciendo clic en el botón del ratón) aparece en la pantalla mensaje estándar sobre el error. Al hacer clic en el botón Repetir en esta ventana se activa el proceso de edición del contenido de la celda en la que se encuentran los datos incorrectos, utilizando el botón Cancelar para eliminar los datos incorrectos de la celda.

El desarrollador puede especificar un mensaje que aparecerá en la pantalla en respuesta a una entrada de datos errónea. Para hacer esto, abra la pestaña Mensaje de error, seleccione el tipo de mensaje en la lista Ver e ingrese el título y el texto del mensaje en los campos Encabezado y Mensaje, respectivamente.

El tipo de mensaje determina el tipo de icono que se muestra en la ventana del mensaje y las acciones que se pueden tomar si los datos son incorrectos.

Tipo de mensaje	Icono	Botones de comando
Detener		Rehacer, cancelar
Advertencia		Sí, No, Cancelar
Mensaje		Aceptar, cancelar

Independientemente del tipo de mensaje, si ocurre un error y se hace clic en el botón Cancelar, la operación de ingresar datos en una celda se cancela y el estado de la celda permanece como estaba antes del intento de ingresar datos.

El mensaje de advertencia termina con la pregunta "¿Continuar?". Como resultado de hacer clic en el botón Sí, se escriben datos incorrectos en la celda. Al hacer clic en el botón No se activa el modo de edición.

Al hacer clic en el botón Aceptar en la ventana mensaje informativo deja datos incorrectos en una celda de la tabla.

Al entrar gran cantidad La información en las celdas de la tabla es fácil de cometer errores. EXCEL tiene una herramienta para verificar los datos ingresados ​​inmediatamente después de hacer clic teclas ENTRAR- Comprobación de datos.

Herramienta Comprobación de datos() no sería tan popular si su funcionalidad se limitara únicamente a la verificación misma. Las técnicas básicas para trabajar con esta herramienta se enumeran brevemente a continuación.

A. Comprobación de los valores introducidos

Como puede ver en la figura siguiente, las condiciones para verificar el valor ingresado se pueden configurar de manera muy flexible.

Solo puedes permitir que se ingresen valores en una celda cierto tipo seleccionando condición necesaria de la lista desplegable:

• Entero. Sólo se permite ingresar en una celda números enteros que pertenecen a un determinado rango;
• Válido. Solo se pueden ingresar números en una celda, incluidos los números parte decimal(no puede ingresar texto, puede ingresar una fecha);
• Fecha de. Se supone que se ingresarán en la celda fechas que van desde el 01/01/1900 al 31/12/9999. Lea más sobre el formato de fecha en el artículo.
• Tiempo. Se supone que en la celda con este cheque Este tipo ingresará la hora. Por ejemplo, la siguiente figura muestra una condición en la que se permite ingresar la hora en una celda que solo pertenece a la segunda mitad del día, es decir de 12:00:00 a 23:59:59. En lugar de ingresar tediosamente el valor 12:00:00, puede usar su equivalente numérico de 0,5. La posibilidad de ingresar números en lugar de la hora se deriva del hecho de que cualquier fecha en EXCEL está asociada con un número entero positivo y, por lo tanto, la hora (ya que es parte del día) corresponde fracción números (por ejemplo, 0,5 es mediodía). El equivalente numérico de 23:59:59 sería 0,99999.

• Longitud del texto. A la celda sólo se le permite entrar una cierta cantidad caracteres. Con esta restricción, puede ingresar tanto números como fechas, lo principal es que la cantidad de caracteres ingresados ​​​​no contradice el límite de longitud del texto. Por ejemplo, si el número de caracteres está limitado a menos de 5, no puede ingresar una fecha posterior al 13/10/2173, porque corresponde al número 99999, y el 14/10/2173 ya es 100000, es decir 6 personajes. Es interesante que si hay un límite de, por ejemplo, menos de 5 caracteres, no podrás ingresar la fórmula =SQRT(2) en la celda, porque resultado =1.4142135623731 (dependiendo de la precisión especificada en EXCEL), pero =ROOT(4) - puedes, porque el resultado =2, y esto es solo 1 carácter.
• Lista. Probablemente el más chico interesante datos. En este caso, la entrada de valores en una celda se puede limitar a una lista previamente definida. Por ejemplo, si especifica la unidad de medida de un producto como fuente, separada por punto y coma piezas;kg;m2;m3, entonces no podrás seleccionar nada más que estos 4 valores de la lista. En la fuente, puede especificar un rango de celdas que contengan una lista generada previamente o un enlace a . Se da un ejemplo en el artículo.
• Otro. La celda puede ingresar valores que satisfagan criterios más complejos. Para establecer los criterios, debe utilizar una fórmula. Consideremos esta condición con más detalle.

Con el tipo seleccionado Otro, en el campo Fórmula debe ingresar una fórmula para calcular el valor lógico. Si se evalúa como VERDADERO, entonces se permite ingresar dicho valor en la celda; si es FALSO, entonces no se puede ingresar su valor. Como argumento de fórmula, debe utilizar una referencia a la celda a la que se aplica. Comprobación de datos o una referencia a una celda que depende de ella. Por ejemplo,

Al seleccionar varias celdas, cuando sea necesario, no olvide indicar enlace absoluto en las células (por ejemplo, 1$A$:20$A$ ).

Al usar la herramienta Comprobación de datos, se supone que se ingresarán constantes en la celda ( 123, producto1, 01/05/2010 etc.), aunque nadie prohíbe introducir fórmulas. En este caso, se seguirá comprobando el resultado del cálculo de la fórmula. En general, no recomiendo ingresar fórmulas en celdas con verificación de datos; es fácil confundirse. En este caso te aconsejo que utilices .

EN.Mostrar un comentario si la celda es la actual.

Usa la pestaña Mensaje de salida para mostrar el comentario.

A diferencia de la nota habitual ( Revisar/ Comentar/ Crear un comentario), que desaparece después de que el cursor del mouse sale de la celda (si la opción no está activa Mostrar todas las notas), este comentario se muestra cada vez que se selecciona una celda.

CON.Muestra un mensaje de error detallado.

Después de ingresar un valor incorrecto Comprobación de datos puede mostrar mensaje detallado sobre lo que se hizo mal. Esta es una especie de analogía. cuadro de mensajes() de Vba.

D.Crear rangos vinculados (listas)

E. Usar referencias a otras hojas en las reglas.

En EXCEL 2007 Verificación de datos, como en, no puede especificar directamente un enlace a los rangos de otra hoja, por ejemplo, así =Hoja2!$A$1 . Le permite evitar esta limitación usando .

si en Verificación de datos necesitas hacer, por ejemplo, una referencia a una celda A1 otra hoja, primero debe definir esa celda y luego hacer referencia a esa Nombre en la regla Verificación de datos.

En Excel 2010, por otro lado, puede utilizar reglas de validación de datos que hagan referencia a valores en otras hojas. En Excel 2007 y Excel 97-2003, este tipo de validación de datos no se admite y no aparece en la hoja de cálculo. Sin embargo, todas las reglas de verificación de datos permanecen disponibles en el libro y se aplican cuando reapertura libros de trabajo en Excel 2010 a menos que se modifiquen en Excel 2007 o Excel 97-2003.

F. Cómo funciona la verificación de antecedentes

Comprobación de datos Se activa explícitamente cuando se ingresan valores en una celda desde el teclado y luego se presiona una tecla. INGRESAR. En este caso, aparece una ventana que describe el error.

Si los valores se insertan mediante Portapapeles (Pegar valores) o usando un atajo de teclado CONTROL+ D(copiando el valor de la celda de arriba) o copiado de arriba a abajo, luego regístrelo explícitamente no está implementado. Además, al copiar valores, puedes eliminar reglas por completo accidentalmente. Verificaciones de datos, por ejemplo, si la celda de origen no está definida Comprobación de datos, y los datos del mismo se insertan a través de Portapapeles y usando una combinación de teclas CTRL+V.

Expliquemos con un ejemplo. Supongamos que la celda A1 aplicado Comprobación de datos sujeto a verificación Otro, donde =ROW(A1)=1 se ingresa en el campo de fórmula, es decir para todas las celdas de la primera fila la condición Verificaciones de datos tomará el valor VERDADERO, para otras filas - FALSO, independientemente del contenido de la celda.

Ahora seleccionemos la celda. A2 y presione CTRL+D. Valor de A1 se copiará a A2 junto con la condición Verificaciones de datos. A pesar de que ahora la condición Verificaciones de datos se evaluará como FALSO y no se mostrará ningún mensaje de advertencia. Para garantizar que los datos de las celdas cumplan las condiciones definidas en Verificación de datos, necesitas llamar al comando de menú. Circule la información incorrecta (). Las celdas con datos incorrectos aparecerán delineadas con óvalos rojos. Ahora seleccionemos la celda nuevamente. A2 y presione la tecla F2(ingrese al modo de edición), luego haga clic en INGRESAR- Aparecerá una ventana con un mensaje que indica que el valor ingresado es incorrecto.

Hay otra forma de eludir las verificaciones de antecedentes. Digamos que la entrada en una celda está limitada a valores del 1 al 3. Ahora en cualquier otra celda sin Verificaciones de datos ingrese el valor 4. Seleccione esta celda, en Barra de formulas seleccione el valor 4 y cópielo a Portapapeles. Ahora seleccione la celda con Verificación de datos y presione CTRL+V. ¡El valor ha sido insertado en la celda! Además, Comprobación de datos permaneció intacto, a diferencia del caso cuando, a través de Portapapeles, por ejemplo, se inserta un valor de WORD. Para asegurarse de que los datos de una celda no cumplan las condiciones definidas en Verificación de datos, necesitas llamar al comando de menú. Circule la información incorrecta (Datos/Trabajar con datos/Validar datos/Circular datos incorrectos).

G. Buscar celdas con Validación de Datos

Si hay muchas celdas en la hoja con Verificación de datos, entonces puedes usar la herramienta ( Inicio/ Buscar y Seleccionar/ Seleccionar un grupo de celdas).

Opción Comprobación de datos Esta herramienta le permite seleccionar celdas que están sujetas a validación de datos (especificada mediante el comando Datos / Trabajar con datos / Validar datos). Al seleccionar un interruptor Todos todas esas celdas serán seleccionadas. Al seleccionar la opción Estos mismo Solo se resaltan aquellas celdas para las que se establecen las mismas reglas de validación de datos que para la celda activa.

Al validar datos, puede especificar fácilmente las reglas que deben cumplir los datos. Desafortunadamente, Excel insiste en que las listas utilizadas para la validación deben estar en la misma hoja de trabajo que los datos que se validan. Afortunadamente, siempre hay formas de evitar este requisito.

En este truco, le presentaremos dos formas de validar datos basándose en una lista en otra hoja. El primero aprovecha el nombre rangos de excel(que se analizan con más detalle en el Capítulo 3), y el segundo usa una llamada a función.

Método 1: rangos con nombre

Quizás el más simple y de manera rápida Para superar las barreras de Excel para validar datos, puede nombrar el rango que contiene la lista. Para crear un rango con nombre, seleccione las celdas que contienen la lista e ingrese un nombre en el cuadro Nombre de la barra de fórmulas. Para ejecutar este ejemplo, asumiremos que el rango se llama MyRange.

Seleccione la celda en la que desea que aparezca la lista desplegable y luego seleccione Datos → Validación. En el campo Permitir, seleccione Lista y en el campo Fuente, ingrese =MyRange. Haga clic en Aceptar. Como usó un rango con nombre, su lista (aunque esté en una hoja diferente) ahora se puede usar como lista de validación.

Método 2: función INDIRECTA

Función INDIRECTO(INDIRECTO) le permite hacer referencia a una celda que contiene texto que representa la dirección de la celda. Esta celda se puede utilizar como enlace local, incluso si recibe datos de otra hoja. Puede utilizar esta función para vincular a la hoja donde se encuentra la lista.

Digamos que la lista está en Sheetl en el rango $A$1:$A$8 . Haga clic en cualquier celda de otra hoja donde debería aparecer esta lista de verificación (lista de selección). Luego seleccione el comando Datos → Validación y en el campo Permitir seleccione Lista. En el campo Fuente, ingrese el siguiente código: =INDIRECT("Sheetl!$A$1:$A$8"), en ruso Versiones de Excel=INDIRECTO("Hojal!$A$1:$A$8") . Asegúrese de que la casilla de verificación Lista de valores válidos (en la celda) esté seleccionada y haga clic en Aceptar. La lista en Sheetl debería aparecer en el menú desplegable Validación.

Si el nombre de la hoja en la que se encuentra la lista contiene espacios, debe utilizar la siguiente sintaxis de función INDIRECTO(INDIRECTO): =INDIRECT(""Sheetl"!$A$1:$A$8") , en la versión rusa de Excel =INDIRECT(""Sheetl"!$A$1:$A$8") . La diferencia es que aquí hay un apóstrofe después de la primera comilla y el segundo apóstrofe está antes del signo de exclamación.

Es una buena idea utilizar siempre apóstrofes simples, independientemente de si el nombre contiene espacios o no. Con los apóstrofes, aún podrá hacer referencia a hojas con nombres sin espacios y también facilitará la realización de cambios posteriores.

Ventajas y desventajas de ambos métodos.

Para rangos y funciones con nombre INDIRECTO(INDIRECTO) tiene ventajas y desventajas. La ventaja de utilizar un rango con nombre es que cambiar el nombre de la hoja no afectará la lista de validación. Esto pone de relieve la falta de función. INDIRECTO(INDIRECTO): cualquier cambio en el nombre de la hoja no se reflejará automáticamente en la misma. Ventaja de la característica INDIRECTO(INDIRECTO): Cuando la primera celda o fila o la última celda o fila se elimina de un rango con nombre, el rango con nombre devolverá un #REF. . Esta es la desventaja de un rango con nombre: si elimina celdas o filas, los cambios no afectarán la lista de validación.

Mientras trabaja con grandes volúmenes información, especialmente cuando es procesada mediante fórmulas o macros, es importante que los datos no tengan errores que puedan afectar el resultado. Las dificultades para obtener fuentes correctas aparecen cuando provienen de fuentes no estructuradas (bases de datos mal diseñadas, llenado manual de tablas y formularios por personas que no están interesadas en los resultados). Particularmente difícil de lograr diseño correcto según una determinada máscara (plantilla). Por ejemplo, una fecha se puede escribir como una cadena de varias maneras:

• 01.01.2001;
• 01/01/2001;
• 1 de enero de 2001, etc.

El artículo describe la funcionalidad que le permite simplificar el proceso de recopilación y verificación de datos en Excel.

¿Dónde es?

Para configurar los parámetros para verificar los valores de entrada, en la pestaña "Datos" en el área "Trabajar con datos", haga clic en el icono "Validación de datos" o seleccione un elemento similar en el menú desplegable:

Aparecerá una ventana con la configuración predeterminada en la pantalla, donde se puede utilizar cualquier valor como tipo de datos:

Configurar una condición de verificación

Inicialmente, debe seleccionar el tipo de datos a verificar, que será la primera condición. Hay 8 opciones en total:

• Entero;
• Número Real;
• Lista;
• Fecha de;
• Tiempo;
• Longitud del texto;
• Otro.

De acuerdo con el elemento seleccionado, aparece una opción. condiciones adicionales. Para números, fechas, horas y longitud del texto es posible especificar restricciones: menor que, mayor que, rango, etc. La fuente debe estar adjunta a la lista y el elemento "otro" le solicita que ingrese una fórmula que devuelva un valor lógico.

lo mas apariencia inusual es la lista desplegable.

Le permite seleccionar los valores especificados como fuente y límite. valores válidos a ellos. También es posible crear listas desplegables dinámicas.

Información sobre herramientas de celda de Excel

La funcionalidad de validación de datos en Excel le permite personalizar la información sobre herramientas para las celdas de la hoja de cálculo. Para hacer esto, vaya a la segunda pestaña de la ventana de verificación del valor de entrada: "Mensaje de entrada".

La imagen muestra un ejemplo. posible mensaje para una celda en la que se ingresan números enteros del 1 al 31 (configuración en la pestaña "Parámetros"). El título y los mensajes se indican a su discreción; no existen reglas para su diseño. No olvide marcar la casilla de verificación "Mostrar información sobre herramientas si la celda es la actual", de lo contrario el mensaje se desactivará.

Un ejemplo de información sobre herramientas en Excel:

Salida de mensaje de error

La última pestaña de la ventana de validación de datos le permite configurar el comportamiento y visualización de mensajes cuando se detecta un valor erróneo.

Hay tres opciones de mensajes que difieren en comportamiento:

• Detener;
• Advertencia;
• Mensaje.

Una parada es un mensaje de error y permite solo 2 acciones: cancelar la entrada y repetir la entrada. Si se cancela, el nuevo valor se cambiará al anterior. La repetición de la entrada permite corregir el nuevo valor.

Una advertencia es más tolerante que una parada, ya que permite dejar un valor que no cumple con las condiciones de verificación después de que el usuario haya confirmado la entrada.

El mensaje muestra el error en información simple y brinda la oportunidad de cancelar la última acción.