Hemos visto que el ingreso de información a la planilla de Excel es una etapa importante para el trabajo con formularios e incluso listas. Separar los textos, los números y las fórmulas / funciones, será necesario para obtener dinamismo y profesionalidad en los trabajos con planilla.
El segundo paso es validar la información que se esta ingresando para dar una garantía al resultado final. Entonces habrá que ingresar la información en otra planilla (planilla de Datos) y usar al formulario (Factura 2) como recepción y validación.
Figura 1
Para validar datos podemos utilizar varias funciones y herramientas de Excel que son muy oportunas.
- · L Función SI
- · Funciones Lógicas Y / O
- · Otras Funciones Combinadas y/o Anidadas
- · L Herramienta Validación de Datos : Datos à Validación de Daos
- · Estrategia y Organización de la Información.
La Función SI
Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas. Su sintaxis es:
SI(prueba_lógica;valor_si_verdadero;valor_si_falso)
Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100, la expresión se evalúa como VERDADERO. De lo contrario, se evaluará como FALSO. Este argumento puede utilizar cualquier operador de comparación.
Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lógica es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de presupuesto" y el argumento prueba_lógica se evalúa como VERDADERO, la función SI muestra el texto "Dentro de presupuesto". Si el argumento prueba_lógica es VERDADERO y el argumento valor_si_verdadero está en blanco, este argumento devuelve 0 (cero). Para mostrar la palabra VERDADERO, utilice el valor lógico VERDADERO para este argumento. Valor_si_verdadero puede ser otra fórmula.
Valor_si_falso es el valor que se devuelve si el argumento prueba_lógica es FALSO. Por ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento prueba_lógica se evalúa como FALSO, la función SI muestra el texto "Presupuesto excedido". Si el argumento prueba_lógica es FALSO y se omite valor_si_falso, (es decir, después de valor_si_verdadero no hay ninguna coma), se devuelve el valor lógico FALSO. Si prueba_lógica es FALSO y valor_si_falso está en blanco (es decir, después de valor_si_verdadero hay una coma seguida por el paréntesis de cierre), se devuelve el valor 0 (cero). Valor_si_falso puede ser otra fórmula.
Observaciones
· Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para crear pruebas más complicadas. (Vea el ejemplo 3 para ver una muestra de funciones SI anidadas).
· Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones.
· Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI.
· Excel proporciona funciones adicionales que se pueden utilizar para analizar los datos en función de una condición.
Por ejemplo, para contar el número de veces que una cadena de texto o un número aparecen dentro de un rango de celdas, utilice las funciones de hoja de cálculo CONTAR.SI y CONTAR.SI.CONJUNTO. Para calcular una suma basándose en una cadena de texto o un número de un rango, utilice las funciones SUMAR.SI y SUMAR.SI.CONJUNTO.
h
|
A
|
B
|
1
|
Datos
|
|
2
|
50
|
|
|
|
|
|
Fórmula
|
Descripción (resultado)
|
|
=SI(A2<=100;
"Dentro de presupuesto";
"Presupuesto excedido")
|
Si el número anterior es igual o
menor que 100, la fórmula muestra "Dentro de presupuesto". De lo
contrario, la función mostrará "Presupuesto excedido" (Dentro de
presupuesto)
|
|
=SI(A2=100;
SUMA(B5:B15);"")
|
Si el número anterior es 100, se
calcula el rango B5:B15. En caso contrario, se devuelve texto vacío
("") ()
|
Y (función Y)
Devuelve VERDADERO si todos los argumentos se evalúan como VERDADERO; devuelve FALSO si uno o más argumentos se evalúan como FALSO.
Un uso común de la función Y es expandir la utilidad de otras funciones que realizan pruebas lógicas. Por ejemplo, la función SI realiza una prueba lógica y, luego, devuelve un valor si la prueba se evalúa como VERDADERO y otro valor si la prueba se evalúa como FALSO. Con la función Y como argumento prueba_lógica de la función SI, puede probar varias condiciones diferentes en lugar de sólo una.
Y(valor_lógico1; [valor_lógico2]; ...)
La sintaxis de la función Y tiene los siguientes argumentos (argumento: valor que proporciona información a una acción, un evento, un método, una propiedad, una función o un procedimiento.):
valor_lógico1 Obligatorio. La primera condición que desea probar se puede evaluar como VERDADERO o FALSO.
valor_lógico2; ... Opcional. Las condiciones adicionales que desea probar se pueden evaluar como VERDADERO o FALSO, hasta un máximo de 255 condiciones.
Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o bien deben ser matrices (matriz: utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango de matriz comparte una fórmula común; una constante de matriz es un grupo de constantes utilizadas como un argumento.) o referencias que contengan valores lógicos.
Si un argumento de matriz o de referencia contiene texto o celdas vacías, esos valores se pasarán por alto.
Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR!.
Ejemplo 1
|
|
A
|
B
|
C
|
Fórmula
|
Descripción
|
Resultado
|
=Y(VERDADERO; VERDADERO)
|
Todos los argumentos son VERDADERO
|
VERDADERO
|
=Y(VERDADERO; FALSO)
|
Un argumento es FALSO
|
FALSO
|
=Y(2+2=4; 2+3=5)
|
Todos los argumentos se evalúan como VERDADERO
|
VERDADERO
|
|
Ejemplo 2
|
|
A
|
B
|
C
|
Datos
| | |
50
| | |
104
| | |
Fórmula
|
Descripción
|
Resultado
|
|
Muestra VERDADERO si el número de la celda A2 es un número entre 1 y 100. De lo contrario, muestra FALSO.
|
VERDADERO
|
|
|
Muestra el número en la celda A3, si es un número entre 1 y 100. De lo contrario, muestra el mensaje "El valor está fuera del rango".
|
El valor está fuera del rango.
|
|
|
Muestra el número en la celda A2, si es un número entre 1 y 100. De lo contrario, se muestra un mensaje.
|
50
|
|
xl
Muestra como se usa La Funcion SI y la función Y anidada
En el ejemplo de la Factura
(Pueden bajar el archivo desde
à aquí )
Figura2
Debajo de lo que se ve tenemos estas Funciones
|
Q
|
R
|
S
|
T
|
U
|
V
|
W
|
X
|
Y
|
Z
|
AA
|
AB
|
AC
|
AD
|
AE
|
AF
|
AG
|
AH
|
17
|
RESUMEN DE CONTENIDOS
|
18
|
19
| | | | | | | | | | | | | | | | | | |
20
| | |
=SI(Datos!C14>0;Datos!B14;"")
|
$
|
=SI(Datos!C14>0;Datos!C14;"")
|
21
| | |
22
| | |
=SI(Y(Datos!C14>0;
Datos!C15>0);Datos!B15;"")
|
$
|
=SI(Y(Datos!C14>0; Datos!C15>0);
(-1)*ENTERO(AE21*Datos!C15)/100;"")
|
23
| | | | | | | | | | | | | | | | | | |
24
| | |
=SI(Datos!C16>0;Datos!B16;"")
|
$
|
=SI(Datos!C16>0;Datos!C16;"")
|
25
| | |
26
| | |
=SI(Y(Datos!C16>0;
Datos!C17>0);Datos!B17;"")
|
$
|
=SI(Y(Datos!C16>0; Datos!C17>0);
(-1)*ENTERO(AE25*Datos!C17)/100;"")
|
27
| | | | | | | | | | | | | | | | | | |
28
| | |
SUBTOTAL SERVICIOS
|
$
|
=SUMA(AE21:AI27)
|
29
| | |
Estrategias de Trabajo
Como podrán ver en la planilla de la Factura hay dos hojas, una que sirve como hoja de Datos y Otra que es la Factura.
Cuando en la Función aparece Datos! Es porque hace referencia a la hoja Datos. De igual manera cuando hace referencia a la hoja Factura puede ser referida como Factura2!
Ejemplo: =SI(Y(Datos!C16>0; Datos!C17>0);Datos!B17;"")
En este caso vemos como la función anidada hace referencia a la celda C16 de la hoja Datos.
Aplicar validación de datos a celdas
La validación de datos se usa para controlar el tipo de datos o los valores que los usuarios pueden escribir en una celda. Por ejemplo, es posible que desee restringir la entrada de datos a un intervalo determinado de fechas, limitar las opciones con una lista o asegurarse de que sólo se escriben números enteros positivos.
La validación de datos es una función de Excel que permite establecer restricciones respecto a los datos que se pueden o se deben escribir en una celda. La validación de datos puede configurarse para impedir que los usuarios escriban datos no válidos. Si lo prefiere, puede permitir que los usuarios escriban datos no válidos en una celda y advertirles cuando intenten hacerlo. También puede proporcionar mensajes para indicar qué tipo de entradas se esperan en una celda, así como instrucciones para ayudar a los usuarios a corregir los errores.
Por ejemplo, en un libro de marketing, puede configurar una celda para permitir únicamente números de cuenta de tres caracteres. Cuando los usuarios seleccionan la celda, puede mostrarles un mensaje como el siguiente:
Figura 3
Si los usuarios no tienen en cuenta este mensaje y escriben datos no válidos en la celda, como un número de dos o de cinco dígitos, puede mostrarles un mensaje de error específico.
En un escenario un poco más avanzado, podría usar la validación de datos para calcular el valor máximo permitido en una celda según un valor que se encuentra en otra parte del libro. En el siguiente ejemplo, el usuario ha escrito 4.000 dólares en la celda E7, lo cual supera el límite máximo especificado para comisiones y bonificaciones.
Figura4
Si se aumentara o redujera el presupuesto de nómina, el máximo permitido en E7 también aumentaría o se reduciría automáticamente.
Las opciones de validación de datos se encuentran en la ficha Datos, en el grupo Herramientas de datos.
Figura5
La validación de datos se configura en el cuadro de diálogo Validación de datos.
Figura6
¿Cuándo es útil la validación de datos?
La validación de datos es sumamente útil cuando desea compartir un libro con otros miembros de la organización y desea que los datos que se escriban en él sean exactos y coherentes.
Puede usar la validación de datos para lo siguiente, entre otras aplicaciones:
Restringir los datos a elementos predefinidos de una lista Por ejemplo, puede limitar los tipos de departamentos a Ventas, Finanzas, Investigación y desarrollo y TI. De forma similar, puede crear una lista de valores a partir de un rango de celdas que se encuentren en otra parte del libro.
Figura7
Restringir los números que se encuentren fuera de un intervalo específico Por ejemplo, puede especificar un límite mínimo de deducciones de dos veces el número de hijos en una celda específica.
Restringir las fechas que se encuentren fuera de un período de tiempo específico Por ejemplo, puede especificar un período de tiempo entre la fecha actual y los 3 días siguientes.
Restringir las horas que se encuentren fuera de un período de tiempo específico Por ejemplo, puede especificar un período de tiempo para servir el desayuno entre la hora en que abre el restaurante y cinco horas después.
Limitar la cantidad de caracteres de texto Por ejemplo, puede limitar el texto permitido en una celda a 10 caracteres o menos. De forma similar, puede establecer la longitud específica de un campo de nombre completo (C1) en la longitud actual de un campo de nombre (A1) y un campo de apellidos (B1), más 10 caracteres.
Validar datos según fórmulas o valores de otras celdas Por ejemplo, puede usar la validación de datos para establecer un límite máximo para comisiones y bonificaciones de 3.600 dólares, según el valor de nómina proyectado general. Si los usuarios escriben un valor de más de 3.600 dólares en la celda, aparecerá un mensaje de validación.
valor_buscado Obligatorio. Valor que se va a buscar en la primera columna de la tabla o rango. El argumento valor_buscado puede ser un valor o una referencia. Si el valor que proporcione para el argumento valor_buscado es inferior al menor valor de la primera columna del argumento matriz_buscar_en, BUSCARV devuelve al valor de error #N/A. 
matriz_buscar_en Obligatorio. El rango de celdas que contiene los datos. Puede usar una referencia a un rango (por ejemplo, A2:D8) o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado. Estos valores pueden ser texto, números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes. 
indicador_columnas Obligatorio. Un número de columna del argumento matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente. 
Inferior a 1, BUSCARV devuelve al valor de error #¡VALOR!. 
Superior al número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #¡REF!.
ordenado Opcional. Un valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada: 
Si ordenado se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado. 
Si el argumento ordenado es FALSO, BUSCARV sólo buscará una coincidencia exacta. Si hay dos o más valores en la primera columna de matriz_buscar_en que coinciden con el argumento valor_búsqueda, se usará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.
Al buscar valores de texto en la primera columna de matriz_buscar_en, asegúrese de que los datos de la primera columna de matriz_buscar_en no tienen espacios al principio ni al final, de que no hay un uso incoherente de las comillas rectas ( ' o " ) ni tipográficas ( ' o ") y de que no hay caracteres no imprimibles. En estos casos, BUSCARV puede devolver un valor inesperado o incorrecto. 
Al buscar valores de fechas o números, asegúrese de que los datos de la primera columna de matriz_buscar_en no se almacenen como valores de texto, ya que, en ese caso, BUSCARV puede devolver un valor incorrecto o inesperado. 
Si ordenado es FALSO y valor_buscado es un valor de texto, se pueden usar los caracteres comodín de signo de interrogación (?) y asterisco (*) en el argumento valor_buscado. El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres. Si lo que desea buscar es un signo de interrogación o un asterisco, escriba una tilde (~) antes del carácter.
