PAGINA 9

CLASE 3 - MS EXCEL 2010

FORMULAS Y FUNCIONES

3. Las funciones (I)

Vamos a profundizar en el manejo de funciones ya definidas por Excel 2010 para agilizar la creación de hojas de cálculo, estudiando la sintaxis de éstas así como el uso del asistente para funciones, herramienta muy útil cuando no conocemos muy bien las funciones existentes o la sintaxis de éstas. 

3.1. Introducir funciones 

Una función es una fórmula predefinida por Excel (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene.

La sintaxis de cualquier función es:

nombre_función(argumento1;argumento2;...;argumentoN)

Siguen las siguientes reglas:

- Si la función va al comienzo de una fórmula debe empezar por el signo =.
- Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis.
- Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones.
- Los argumentos deben de separarse por un punto y coma ;.

Ejemplo: =SUMA(A1:C8)

Tenemos la función SUMA() que devuelve como resultado la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8, así la función anterior sería equivalente a:

=A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8

En este ejemplo se puede apreciar la ventaja de utilizar la función.

Las fórmulas pueden contener más de una función, y pueden aparecer funciones anidadas dentro de la fórmula.

Ejemplo: =SUMA(A1:B4)/SUMA(C1:D4)

Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información.

Para introducir una fórmula debe escribirse en una celda cualquiera tal cual introducimos cualquier texto, precedida siempre del signo =.

3.2. Autosuma y funciones más frecuentes

Una función como cualquier dato se puede escribir directamente en la celda si conocemos su sintaxis, pero Excel dispone de herramientas que facilitan esta tarea.

En la pestaña Inicio o en la de Fórmulas encontrarás el botón de Autosuma que nos permite realizar la función SUMA de forma más rápida.

3.3. Insertar función

Para insertar cualquier otra función, también podemos utilizar el asistente. Si queremos introducir una función en una celda:

1.  Situarse en la celda donde queremos introducir la función.
2. Hacer clic en la pestaña Fórmulas 
3. Elegir la opción Insertar función.

 

 O bien, hacer clic sobre el botón de la barra de fórmulas. 

Aparecerá el siguiente cuadro de diálogo Insertar función:

Observa como conforme seleccionamos una función, en la parte inferior nos aparecen los distintos argumentos y una breve descripción de ésta. También disponemos de un enlace Ayuda sobre esta función para obtener una descripción más completa de dicha función. A final, hacer clic sobre el botón Aceptar.

La ventana cambiará al cuadro de diálogo Argumentos de función, donde nos pide introducir los argumentos de la función: Este cuadro variará según la función que hayamos elegido, en nuestro caso se eligió la función SUMA (). 

• Cómo insertar una formula o funcion?

 

CLASE 4 - MS EXCEL 2010

Función SI

La función SI en Excel evalúa una prueba lógica y devuelve un valor de acuerdo al resultado obtenido de dicha prueba. Para poder utilizar correctamente la función SI es necesario saber utilizar correctamente los operadores de comparación de Excel.

Sintaxis de la función SI

La función SI tiene tres argumentos y solo el primero de ellos es obligatorio:

SI(prueba_lógica, [valor_si_verdadero], [valor_si_falso])

• prueba_lógica: La comparación a realizar.
• valor_si_verdadero (opcional): Valor a regresar si la prueba lógica es verdadera.
• valor_si_falso (opcional): Valor a regresar si la prueba lógica es falsa.

Aunque el segundo y tercer argumento son opcionales, sin ellos la función no sería de mucha utilidad. El grande beneficio de la función SI radica en poder indicar a Excel el valor que deseamos obtener en caso de que la prueba lógica sea verdadera o sea falsa.

Ejemplos de la función SI

Por ejemplo, en la siguiente imagen puedes observar que la función SI hace una comparación para saber si B1 es mayor que B2. Si el resultado es verdadero, la función devolverá la cadena de texto “mayor que” en la celda B4:

Con solo cambiar los valores de las celdas B1 y B2 obtendré un resultado diferente de la misma fórmula:

En el primer caso, la prueba lógica es verdadera y por lo tanto la función SI devuelve el segundo argumento. Sin embargo, en el segundo ejemplo la prueba lógica es falsa, porque B1 no es mayor que B2, y por lo tanto se devuelve el tercer argumento de la función.

Es importante mencionar que tanto el segundo y tercer argumento de la función SI no tienen que ser cadenas de texto sino que pueden ser otras funciones que nos devuelvan el resultado que necesitamos. Hagamos otro ejemplo. Deseo evaluar el valor de las celdas B1 y B2 y en caso de que B1 sea mayor que B2 quiero sumar ambos valores pero si B1 es menor que B2 entonces quiero obtener el producto de ambos. Eso lo logramos con la siguiente fórmula:

SI(B1>B2, B1+B2, B1*B2)

Al utilizar esta fórmula obtenemos el siguiente resultado:

En este ejemplo B1 es mayor que B2, entonces obtenemos la suma de ambos como resultado de la función. Pero ahora observa lo que sucede al cambiar los valores:

Ya que ahora B1 es menor que B2, la función SI devuelve la multiplicación de ambos valores tal como lo hemos indicado en el tercer argumento de la función SI. Esta misma fórmula la podríamos escribir de la siguiente manera:

=SI(B1>B2, SUMA(B1,B2), PRODUCTO(B1,B2))

Esta fórmula se comportará exactamente igual que la anterior pero quiero resaltar el hecho de que podemos especificar funciones como el segundo y tercer argumento de la función SI. De esta manera podemos ejecutar funciones diferentes de acuerdo al resultado de la prueba lógica que estemos realizando.

VIDEOTUTORIAL 

 Función Excel Y

La función Y se encuentra en Excel al lado de la barra de fórmulas en insertar función, que se indica con las letras fx, o también se puede encontrar en la pestaña formulas/bibliotecas de funciones/lógicas.


La función de Excel Y devolverá VERDADERO si todos los argumentos son VERDADERO o FALSO si uno o más argumentos son FALSO.
La sintaxis es la siguiente:
=Y(valor_lógico1;[valor_lógico2];…)
Son las condiciones que se desea comprobar, pudiendo tener desde una, que es obligatorio, hasta 255 condiciones.
Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o bien deben ser matrices o referencias que contengan valores lógicos.
Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos 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!.

Pasemos a un ejemplo:


También la formula se puede escribir en la barra de fórmulas directamente, como se indica en la imagen, En este ejemplo al ser todos los argumentos verdadero, dará como resultado verdadero,

Veamos otro ejemplo:


Ahora al contener el ejemplo falso, dará como resultado falso aunque sea un solo valor falso.

Esta función es mucho más potente combinada con la función “si” aunque también puede combinarse con muchas otras…


En el ejemplo se quiere saber qué alumnos aprueban todas las asignaturas con 7 o más de 7, y quienes van a diciembre con tener aunque sea una asignatura por debajo de 7.
Se combinan la función “SI” con la función “Y”, al introducir un texto en una formula se debe agregar entre comillas, una vez hecha la formula, se debe arrastrar hacia abajo, (es una referencia relativa)

Función Excel O

La función O la podemos hallar igualmente dentro de la Biblioteca de funciones en la categoría Lógicas, como la función SI y la Y…

La función O devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO.
Los argumentos son los mismos que la función Y, pudiendo también tener hasta 255 condiciones.
Esta función tiene las mismas características que la función Y.

Pasemos al ejemplo:

Si uno de los argumentos es verdadero dará como respuesta verdadero.


Si los dos son falsos dará falso.


Suponiendo que la directora de la escuela organiza un excursión para todos los alumnos que en alguna de sus notas alcance a 7.
Se usa la función SI con la función O anidada:


Veamos un ejemplo combinando las tres funciones:
Dada una lista de vendedores, queremos asignarle la comisión de ventas que le corresponde según dos criterios:
• El nivel de ventas supera el objetivo.
• El precio promedio de las ventas supera cierto valor.
Si solo cumple uno de los objetivos, el vendedor tendrá un 5% de comisión, mientras que si supera ambos, se llevará un 10%.
Por el contrario, si no alcanza al menos uno, no se lleva comisión.


En la celda D7 se ingresa la formula =SI(Y(B7>$D$1;C7>$D$2);$D$4;SI(O(B7>$D$1;C7>$D$2);$D$3;0%)) asegurándose de dejar la celda de las consignas fija para no tener problemas a la hora de arrastrarla  ; la primera parte muestra una condición, que si se cumplen ambos criterios, le corresponde a la cantidad de D4, de no cumplir con dicho criterio, tenemos el segundo argumento de la función “SI”, que es un SI anidado con la función “O” , esta función nos dirá si al menos cumple con una condición, de no ser así, el último argumento de la función si indicara que corresponde 0% .   VIDEOTUTORIAL 

   CLASE 5 - MS EXCEL 2010  VALIDACION DE DATOS

¿Qué es la validación de datos?

La validación de datos de Microsoft Excel permite definir el tipo de datos que se desea introducir en una celda. Por ejemplo, se puede permitir la entrada de una puntuación por letras con sólo las letras comprendidas entre la A y la F. Se puede configurar la validación de datos para evitar que los usuarios introduzcan datos no válidos o permitir su entrada y comprobarlos tras finalizar el usuario. También se pueden proporcionar mensajes que definan la entrada que se espera en la celda así como instrucciones para ayudar a los usuarios a corregir errores.

Cuando se introducen datos que no cumplen los requisitos, Excel muestra un mensaje con instrucciones proporcionadas por el usuario.

 

La validación de datos resulta especialmente útil al diseñar formularios u hojas de cálculo que otras personas utilizarán para introducir datos como formularios presupuestarios o informes de gastos.
En este artículo se describe cómo configurar la validación de datos, incluidos los tipos de datos que se pueden validar y los mensajes que se pueden mostrar, además de proporcionar un libro que se puede descargar para obtener ejemplos de validación que se pueden modificar y utilizar en hojas de cálculo propias.

Tipos de datos que se pueden validar

Excel permite designar los siguientes tipos de datos válidos para una celda:
Números     Especifique que la entrada en una celda debe ser un número entero o un número decimal. Puede establecer un mínimo o máximo, excluir un número o intervalo determinado, o utilizar una fórmula para calcular la validez de un número.
Fechas y horas     Establezca un mínimo o máximo, excluya determinadas fechas u horas, o utilice una fórmula para calcular la validez de una fecha u hora.
Longitud     Limite el número de caracteres que se puede escribir en una celda o exija un número mínimo de caracteres.
Lista de valores     Elabore una lista de las opciones para una celda como, por ejemplo, pequeño, mediano, grande, y admita sólo esos valores en la celda. Se puede mostrar una flecha de lista desplegable cuando un usuario hace clic en la celda para facilitar la selección de una opción de la lista.

Tipos de mensajes que se puede mostrar

Para cada celda que se valida, se pueden mostrar dos mensajes diferentes: uno que aparece antes de que el usuario introduzca los datos y otro que aparece después de que el usuario intente introducir los datos que no cumplen los requisitos. Si los usuarios tienen activado el Ayudante de Office, éste mostrará los mensajes.
Mensaje de entrada     Mensaje entrante Este tipo de mensaje aparece cuando un usuario hace clic en la celda validada. Se puede utilizar este tipo de mensaje para facilitar instrucciones sobre el tipo de datos que se han de introducir en la celda.
Mensaje de error     Este tipo de mensaje aparece sólo cuando el usuario escribe datos no válidos y presiona la tecla ENTRAR. Se puede elegir entre tres tipos de mensajes de error:

• Mensaje de información     Este mensaje no impide la entrada de datos no válidos. Además del texto que proporciona el usuario, incluye un icono de información, un botón Aceptar, que introduce los datos no válidos en la celda, y un botón Cancelar, que restaura el valor anterior de la celda.
• Mensaje de advertencia     Este mensaje no impide la entrada de datos no válidos. Incluye el texto que proporciona el usuario, un icono de advertencia y tres botones: Sí introduce los datos no válidos en la celda, No regresa a la celda para más modificaciones y Cancelar restaura el valor anterior de la celda.
• Mensaje de detención     Este mensaje no permite la entrada de datos no válidos. Incluye el texto que proporciona el usuario, un icono de detención y dos botones: Reintentar regresa a la celda para más modificaciones y Cancelar restaura el valor anterior de la celda. Observe que este mensaje no está concebido como medida de seguridad: si bien los usuarios no pueden introducir datos no válidos escribiendo y presionando ENTRAR, pueden eludir la validación copiando y pegando o rellenando datos en la celda.

Si no se especifica ningún mensaje, Excel marca si los datos que introduce un usuario son válidos de modo que se pueden comprobar más adelante, pero no notifica al usuario cuando una entrada no es válida.

Ejemplos de validación

La mejor manera de comprender lo que es la validación de datos es verla en acción. Si descarga el libro de ejemplo que acompaña a este artículo, obtendrá ejemplos de cada tipo de validación y todos los tipos de mensaje. El libro incluye instrucciones para ver la configuración utilizada para cada celda validada y copiar la configuración de la validación en los propios libros, donde se puede modificarla según las propias necesidades.

Configurar la validación de datos

Cuando sepa qué tipo de validación desea utilizar en una hoja de cálculo, podrá usar el comando Validación en el menú Datos para configurarla. Las instrucciones se encuentran en el libro de ejemplo que podrá descargar en este artículo así como en la Ayuda de Excel 2002. A continuación figura información general del proceso:

1. Comfigurar la hoja de cálculo     Introduzca primero los datos y fórmulas en la hoja de cálculo. Si utiliza una lista de opciones válidas, introduzca la lista y asígnele un nombre.
2. Definir la configuración de una celda     Empezando por la primera celda que desee validar, utilice el cuadro de diálogo Validación de datos para designar el tipo de validación que desee, un mensaje entrante y un mensaje de error si lo desea.
3. Configurar la validación de otras celdas     A menudo podrá ahorrar tiempo copiando la configuración de la validación de la primera celda a otras celdas y, a continuación, modificando dicha configuración.
4. Probar las reglas de validación     Introduzca datos válidos y no válidos en las celdas para asegurarse de que funcione debidamente la configuración y los mensajes aparezcan en el momento previsto. Utilice el comando Validación para cambiar la configuración. Si realiza cambios en la validación de una celda, podrá aplicar automáticamente los cambios a todas las demás celdas con la misma configuración.
5. Configurar las listas de opciones válidas     Si utilizó una lista de opciones válidas y desea que los usuarios no puedan encontrar ni cambiar la lista, puede poner la lista en otra hoja de cálculo, configurar la validación, ocultar la hoja de cálculo que contiene la lista y, a continuación, proteger el libro mediante una contraseña. La contraseña del libro contribuirá a evitar que usuarios que no conozcan la contraseña puedan mostrar la hoja de cálculo que contiene la lista.
6. Aplicar protección si se desea     Si piensa proteger la hoja de cálculo o el libro, hágalo después de configurar la validación. Asegúrese de desbloquear las celdas validadas antes de proteger la hoja de cálculo; en caso contrario, los usuarios no podrán escribir en las celdas.
7. Compartir el libro si se desea     Si piensa compartir el libro, hágalo después de configurar la validación y la protección. Tras compartir un libro, no podrá cambiar la configuración de la validación a menos que deje de compartir el libro. Excel, sin embargo, seguirá validando las celdas designadas mientras se comparta el libro.
8. Comprobar si los resultados incluyen datos no válidos     Después de que los usuarios introduzcan datos en la hoja de cálculo, podrá comprobar si ésta incluye datos no válidos de acuerdo lo que se describe más adelante en este artículo.

Introducir datos en celdas validadas

A continuación se describe cómo es el proceso de introducción de datos para los usuarios. Se pueden utilizar mensajes entrantes y de error para facilitar las instrucciones que los usuarios necesitan para comprender cómo se ha configurado la hoja de cálculo con el propósito de asegurar que los datos son correctos. Para verlo, descargue el libro de ejemplo que acompaña a este artículo y vea los ejemplos en la hoja Mensajes.
Ver el mensaje entrante     Cuando un usuario hace clic en una celda validada o utiliza las teclas de dirección para desplazarse a la celda, el mensaje entrante aparece en el globo del Ayudante o en un cuadro de mensaje independiente. Si proporcionó una lista desplegable para la celda, la flecha de lista desplegable aparecerá a la derecha de la celda.
Escribir datos     Cuando el usuario escribe datos o hace clic en la flecha de lista desplegable para seleccionar un valor de la lista, el mensaje entrante permanece en la pantalla (la lista desplegable puede tapar parte del mensaje).
Escribir datos válidos     Si el usuario escribe datos válidos y presiona la tecla ENTRAR, los datos quedan introducidos en la celda y no sucede nada especial.
Escribir datos no válidos     Si el usuario escribe datos que no cumplen los criterios y se especificó un mensaje de error para los datos no válidos, dicho mensaje aparecerá en un globo del Ayudante o en una ventana independiente de mensajes. A continuación, el usuario podrá leer el mensaje y decidir qué va a hacer.

• En caso de un mensaje de información, el usuario puede hacer clic en Aceptar para introducir los datos no válidos o hacer clic en Cancelar para empezar de nuevo.
• En caso de un mensaje de advertencia, el usuario puede hacer clic en Sí para introducir los datos no válidos, en No para seguir modificando la celda o en Cancelar para empezar de nuevo.
• En caso de un mensaje de detención, el usuario no puede introducir los datos no válidos y puede hacer clic en Reintentar para modificar la celda o en Cancelar para empezar de nuevo.

Si no se proporcionan mensajes, la introducción de datos en celdas validadas es igual a la introducción normal de datos en Excel. Sin embargo, Excel marca todas las celdas con entradas no válidas de modo que sean fáciles de encontrar.

Comprobar si hay entradas no válidas en una hoja de cálculo

Al recibir hojas de cálculo de usuarios que pueden haber introducido datos no válidos, puede configurar Excel de modo que muestre círculos rojos alrededor de los datos que no cumplan los criterios, facilitando así la búsqueda de errores en las hojas de cálculo. Utilice los botones Rodear con un círculo datos no válidos y Borrar círculos de validación en la barra de herramientas Auditoría.

El valor de esta celda está rodeado con un círculo porque no cumple una regla de validación.
El círculo desaparecerá cuando corrija los datos de la celda. Para ver en qué consiste esta característica, descargue el libro de ejemplo que acompaña a este artículo y vea la hoja Datos no válidos.