Fórmulas usadas en validación de datos.
En el curso de Excel 2007 lecciones 066, 067 y 068 y Excel 2010 lecciones 70, 71 y 72 , discutimos varias opciones para validación de datos. En este consejo, hablaremos de algunas más.
El valor de la celda debe ser más alto que el de la celda madre.
Como primer ejemplo tomamos un archivo (1) donde registramos nuestro consumo de gas semanal (2). Para evitar ingresar una lectura incorrecta del medidor. Las lecturas del medidor deberan estar en orden ascendente, así que usamos una fórmula.
Ingresa la lectura inicial del medidor en la celda B3, haz click en la celda B4, presiona la tecla Shift k, haz click en la celda B55.
Ve a “Datos” en la cinta y haz click en “Validación de Datos”.
En la pestaña de “Configuración” (1) de la ventana de “Validación de Datos”, selecciona “Personalizar” en el recuadro “Permitir” (2), en el cuadro “Fórmula”, escribe el signo de igual “=”, haz click en la celda B4, escribe el operador de comparación “>” (mayor que) y haz click en la celda B3 (la fórmula es =B4>B3) e ingresamos los mensajes apropiados en la pestaña “Ingresar Mensaje” (3) y la pestaña “Alerta de Error” y haz click en aceptar.
Validación de datos se ajusta utomáticamente para las celdas seeleccionadas restantes, podemos probar esto seleccionando cualquier celda donde hayamos aplicado validación en la ventana “Validación de Datos” por ejemplo la celda B20 y podemos ver la fórmula modificada para la celda B20, esto es = B20> B19.
La fórmula que ingresamos en “validación de datos” debe estar formulada por la primer celda seleccionada, la primer celda seleccionada en un área está de un color más claro (1).
En los siguientes ejemplos asumimos que de las celdas seleccionadas A1 a A10, la celda A1 es la primera celda seleccionada, y vamos a la pestaña “Datos” en la cinta y hacemos click en “Validación de Datos”, selecciona la pestaña “Configuración” en la ventana “Validación de Datos” y selecciona “Personalizado” en el recuadro “Permitir”.
No se permiten duplicados.
Usamos la función CONTAR.SI.
La función cuenta el número de celdas en un rango (parámetro 1) que cumplen con un criterio (parametro 2) que ya especificaste.
La fórmula es = CONTAR.SI ($ A $ 1: $ A $ 10, A1) = 1 y presiona Aceptar. Convertimos las celdas A1: A10 en el parámetro absoluto de rango para que no se ajuste para las celdas A2 a A10.
Ahora podemos ingresar cualquier valor solo una vez en las celdas A1 a A10.
No se permiten fines de semana.
Si queremos ingresar las fechas de los dias laborales (ni Sabado ni Domingo).
Usamos las funciones Y y DIASEM.
La función Y con frecuencia se usa para expandir y combinar otras funciones que realizan pruebas lógicas.
La función DIASEM regresa el día de la semana para una fecha. La fecha se despliega de modo predeterminado como un integro del 1 (Domingo) al 7 (Sabado).
La fórmula es = Y (DIASEM (A1)<>1; DIASEM (A1)<>7) y presiona Aceptar.
Limitar cantidad total
Si cierta cantidad no debe ser excedida por ejemplo 1000.
La fórmula es = SUMA ($ A $ 1: $ A $ 10)<=1000 y presiona Aceptar. Convertimos las celdas A1: A10 en el rango absoluto del parámetro para que no sea ajustado para las celdas A2 a A10.
Ahora, la cantidad total para las celdas A1 a A10 tiene un límite de 1000.
Permitir solo texto
Si se te permite ingresar solo texto.
Usamos la función: ESTEXTO
ESTEXTO verifica que el contenido de la celda sea texto.
La fórmula es = ESTEXTO (A1) y presiona Aceptar.
Inicial
Si el texto debe comenzar con cierta letra, por ejemplo X (toma en cuenta que esto no es sensible a mayúsculas y minúsculas).
Usamos la función: IZQUIERDA
La función IZQUIERDA da el primer caracter del primer número de caracteres en una secuencia, basado en el número de caracteres que especificamos. La función IZQUIERDA tiene 2 parámetros, el texto, que es la secuencia con los caracteres que quieres obtener y el numero de caracteres, lo cual es opcional. El número de caracteres IZQUIERDA que quieres obtener debe ser mayor o igual a cero.
Si el número de caracteres es mayor que la longitud del texto, entonces IZQUIERDA regresará todo el texto como resultado.
Si el parámetro de número de caracteres es omitido, asume el valor de 1.
Por ejemplo = IZQUIERDA (A1) regresa el primer caracter en la celda A1.
La fórmula es = IZQUIERDA (A1) = “X” y presiona Aceptar.
Letras iniciales + longitud del texto
Si el texto debe comenzar con ciertas letras, por ejemplo XYZ, seguidas por un guión y debe tener exactamente 9 caracteres de longitud.
Usamos la función: CONTAR.SI
La fórmula es = CONTAR.SI (A1, “XYZ-?????”) = 1 y presiona Aceptar. (toma en cuenta que esto no es sensible a mayúsculas y minúsculas, asi que ambas están permitidas, por ejemplo XYZ-ABCDE, xyz-abcde, y en lugar de los signos de interrogación, tanto letras como números son permitidos, por lo tanto XYZ-12ABC también es aceptado).
Rating y combinación de mayúsculas
Si el contenido de la celda debe ser una combinación de números y letras mayúsculas y un guión por ejemplo
XYZ-123-ABC.
Usamos las funciones Y, IGUAL, MAYUSC, IZQUIERDA, EXTRAE, DERECHA, VALOR y LARGO.
La función IGUAL compara dos secuencias y regresa VERDADERO si las secuencias son identicas y FALSO si no es el caso. Una distinción se hace entre las mayúsculas y minúsculas. Las diferencias de formato, sin embargo, son ignoradas. Usa IGUAL para checar el texto ingresado en el documento, esta función tiene 2 parámetros, lo que quiere decir que texto1 es requerido. La primer secuencia de caracter.
texto2 es requerido. La segunsa secuencia.
La función MAYUSC convierte el texto a mayúsculas y tiene 1 parámetro, el text, el cual es requerido. El texto que quieres convertir a mayúsculas. El texto puede ser una secuencia o una referencia.
La función IZQUIERDA: ve el ejemplo anterior
La función EXTRAE regresa un número especifico de caracteres de una secuencia, empezando por la posición especificada y basado en el número de caracteres especificados y consiste de 3 parámetros, lo que quiere decir que el texte es requerido. La secuencia con los caracteres que quieres obtener.
start_num es requerido. La posición del primer caracter que quieres obtener. El primer caracter en el texto tiene un valor de 1.
numero de caracteres es requerido. El número de caracteres que quieres obtener del texto a EXTRAE.
Por ejemplo = EXTRAE (A1, 4, 2) regresa 3 caracteres del 4to caracter de los contenidos de la celda A1, esos son los caracteres 4 y 5 de la celda A1.
La función DERECHA muestra el último caracter del último caracter en una secuencia, basado en el número de caracteres que ingresas y tiene 2 parámetros, esto quiere decir que el texto es requerido. La secuencia con los caracteres que quieres obtener.
num_chars es opcional. El numero de caracteres que quieres que DERECHA obtenga.
Por ejemplo = DERECHA (A1, 3) regresa los últimos 3 caracteres de la celda A1.
La función NUMERO convierte una cadena que representa un número en un número y tiene 1 parametro que es el de text y es requerido. El texto encerrado entre comillas o referencia a la celda que contiene el texto que quieres convertir.
La función LARGO regresa el número de caracteres en una secuencia.
Para acomodar una fórmula larga, es más fácil ponerla en una celda, luego podemos usar la ayuda de fórmulas (1). No tenemos la ayuda de fórmula disponible en la ventana de “Validación de Datos”. Tomamos por ejemplo la celda B1.
Como hay varias condiciones que se deben cumplir, comenzamos con la función Y.
Escribimos =Y (Luego tomamos la función IGUAL que compara 2 valores, parámetro 1 es la celda A1 y parámetro 2 es MAYUSC (A1). Escribimos el cierre de paréntesis de la función IGUAL.
Hasta ahora, tenemos la fórmula = Y (IGUAL (A1, MAYUSC (A1)).
Cualquier condición para la función Y está separada por comas, asi que escribimos la coma ‘,’. La siguiente condición es que los primeros 3 caracteres deben ser mayores o iguales que A. Aquí usamos la función IZQUIERDA (A1, 3)> = ‘A’, escribe una coma “,” otra vez. Para la siguiente condición, los primeros 3 caracteres tienen que ser menores o iguales a Z, usamos IZQUIERDA (A1, 3)<=”Z”
Ahora tenemos la fórmula = Y (IGUAL (A1, MAYUSC (A1)), IZQUIERDA (A1, 3)> = ‘A’, IZQUIERDA (A1, 3)<=”Z”.
Una vez más escribimos una coma para ingresar la siguiente condición. Aquí debemos asegurarnos que el cuarto caracter sea un guion, así que usamos la función EXTRAE, EXTRAE (A1, 4, 1) = “-“
Ahora tenemos la fórmula = Y (IGUAL (A1, MAYUSC (A1)), IZQUIERDA (A1, 3)> = “A”, IZQUIERDA (A1, 3)<=”Z”, EXTRAE (A1, 4, 1) = “-“. Nuevamente escribimos una coma como separador para la siguiente condición.
Ahora queremos asegurarnos que los tres digitos del quinto caracter sean mayores o iguales a 1, usamos las funciones VALOR y EXTRAE, VALOR (EXTRAE (A1, 5, 3))> = 1, escribe una coma nuevamente para la siguiente condición, tres digitos del quinto caracter deberían ser menores o iguales a 999 VALOR (EXTRAE (A1, 5, 3)) <= 999.
Ahora tenemos la fórmula = Y (IGUAL (A1, MAYUSC (A1)), IZQUIERDA (A1, 3)> = “A”, IZQUIERDA (A1, 3)<=”Z”, EXTRAE (A1, 4, 1) = “-“, VALOR (EXTRAE (A1, 5, 3))> = 1, VALOR (EXTRAE (A1, 5, 3))<=999.
Escribimos una coma para la siguiente condición. El octavo caracter debe ser un guión, EXTRAE (A1, 8, 1) = “-“.
Ahora tenemos la fórmula = Y (IGUAL (A1, MAYUSC (A1)), IZQUIERDA (A1, 3)> = “A”, IZQUIERDA (A1, 3)<=”Z”, EXTRAE (A1, 4, 1) = “-“, VALOR (EXTRAE (A1, 5, 3))> = 1, VALOR (EXTRAE (A1, 5, 3)) <= 999, EXTRAE (A1, 8, 1) = “-“.
Escribimos una coma para la siguiente condición.
Los últimos tres caracteres son mayor que o igual a A, usamos la función DERECHA, DERECHA (A1, 3)> = ‘A’, escribe una coma para la siguiente condición, esto es, los últimos 3 caracteres deben ser menor o igual que Z, DERECHA (A1, 3)<=”Z”
La fórmula ahora es = Y (IGUAL (A1, MAYUSC (A1)), IZQUIERDA (A1, 3)> = “A”, IZQUIERDA (A1, 3)<=”Z”, EXTRAE (A1,4,1)=”-“,
VALOR (EXTRAE (A1, 5, 3))> = 1, VALOR (EXTRAE (A1, 5, 3)) <= 999, EXTRAE (A1, 8, 1) = “-“,
DERECHA (A1, 3)> = “A”, DERECHA (A1, 3) <= “Z”
Nuevamente escribimos una coma “,” y para determinar el largo usamos la función LARGO, escribimosLARGO (A1) = 11 y cerramos el parentesis ‘)’ de la función Y e ingresamos Ctrl + Enter para que la celda que contiene la fórmula sea seleccionada.
La fórmula escrita es:
Y (
IGUAL (A1, MAYUSC (A1)),
IZQUIERDA (A1, 3)> = “A”,
IZQUIERDA (A1, 3) <= “Z”,
EXTRAE (A1, 4, 1) = “-“,
VALOR (EXTRAE (A1, 5, 3))> = 1,
VALOR (EXTRAE (A1, 5, 3)) <= 999,
EXTRAE (A1, 8, 1) = “-“,
DERECHA (A1, 3)> = “A”,
DERECHA (A1, 3) <= “Z”,
LARGO (A1) = 11)
Para saber si la fórmula funciona apropiadamente, en la celda A1, escribimos la letra correcta y la combinación de números que debería dar el resultado “VERDADERO”, y una mala combinación para que nos de el resultado “FALSO”.
Primero eliminamos el contenido de la celda A1. Si no hacemos esto y hay una mala combinación en la celda, entonces este error no es reconocido como una combinación erronea después de ingresar la fórmula en la ventana de “Validación de Datos”. Luego seleccionalo y copia la fórmula de la celdaB1 desde la barra de fórmula, selecciona algunas celdas como A1 a A10, ve a la pestaña “Datos” en la cinta y haz click en “Validación de Datos”. En la pestaña de “Configuración” de la ventana “Validación de Datos”, selecciona “Personalizado” en el recuadro “Permitir”, y pega la fórmula en el espacio provisto para “Fórmula” y haz click en Aceptar.
Así que verás, con la opción “Personalizado” para validación de datos, las posibilidades son virtualmente ilimitadas.
