02Funciones

7/21/2019 02Funciones

1/26

1Funciones

FuncionesIns Escario Jover

Facultad de Ciencias Sociales y del Trabajo. Zaragoza

Mara Jess Lapea Marcos

Facultad de Economa y Empresa. Zaragoza

M Antonia Zapata Abad

Facultad de Ciencias. Zaragoza

Departamento de Informtica e Ingeniera de Sistemas


2/26

2 Funciones

Zaragoza, enero 2013


3/26

3Funciones

Contenido

Funciones ........................................................................................................................................... 4

Funciones estadsticas bsicas ............................................................................................................................... 4

Funcin SI con condiciones simples........................................................................................................................ 6

Acerca de la funcin SI ............................ ................................. ................................ ................................. ...... 10

Revisin de la funcin SI con condiciones simples ........................................... ................................ ................ 12

Funcin SI con condiciones complejas ..................................... ................................ ................................. .......... 14

Revisin de la funcin SI con condiciones complejas .................................................... ................................. . 14

Funcin BUSCARV .................................................................................................................................................. 16

Acerca de la funcin BUSCARV ..................................................................................................................... 19

Prcticas globales con funciones ......................................................... ................................. .............................. 20

Ejercicios adicionales ............................ ................................. ................................ ................................. ............... 23

Tabla resumen de funciones ............................................................................................................ 25

Funciones matemticas.......................................................................................................................................... 25

Funciones estadsticas .......................... ................................. ................................. ................................ ................ 25

Funciones de bsqueda y referencia ......................... ................................. ................................. ...................... 25

Funciones de base de datos ............................. ................................ ................................. ................................. . 26

Funciones de texto ............................ ................................. ................................. ................................ ................... 26

Funciones lgicas ......................... ................................. ................................. ................................ ......................... 26

Funciones de fechas .............................. ................................ ................................. .................................... ............ 26


4/26

4 Funciones

Funciones

Excelproporciona una gran cantidad de funciones predefinidas para la realizacin de distintos tipos de clculos

matemticos, estadsticos, financieros... En esta prctica principalmente vamos a hacer ejercicios con algunas

funciones estadsticas y dos funciones de Excel que resultan muy tiles: la funcin lgica SI y la funcin de

bsqueda BUSCARV. Al final de los ejercicios aparecen listadas algunas de las principales funciones de Excel.

Funciones estadsticas bsicas

1.

Abre el archivo auxiliar Hospitales2007 y completa su tabla resumen utilizando las funciones

correspondientes.

Cuando no conoces el nombre de la funcin que necesitas, utiliza Frmulas\Insertar funcin. Observa que puedes

ver las funciones clasificadas por categoras y una pequea ayuda sobre su uso.

En este caso, las funciones son MAX, MINy PROMEDIO. Y en todas ellas debes indicar sus argumentos, esto es,

sobre qu rango quieres hacer el clculo. Puedes completar los argumentos de la funcin en el correspondiente cuadro

de dilogo. Tambin puedes empezar a teclearla, seleccionarla entre las funciones que propone Excely completar los

argumentos directamente en la barra de frmulas.


5/26

5Funciones

2. Comprueba que si aades un cero al dato de D4se actualiza la tabla resumen automticamente. Deshaz el

cambio.

3.

Guarda y cierra el libroHospitales2007.

4.

Abre el archivo auxiliar Alumnos.xlsx. Observa que tiene los datos de los alumnos del grupo 1 en la hoja

Grupo 1y los del grupo 2 en Grupo 2. Aade una tercera hoja denominada Curso 1y escribe en ella la

siguiente tabla.

5. Completa la tabla anterior con las frmulas adecuadas.

Completa los argumentos de las funciones arrastrando sobre los rangos correspondientes y con un formato adecuado.

Observa que cuando en una hoja haces referencia a una celda o rango de otra hoja, el nombre comienza por el nombre

de la hoja donde se encuentra seguido del signo de cierre de admiracin ( !). Observa que para cambiar de hoja debes

fijar lo que tienes escrito escribiendo lo que corresponda.


6/26

6 Funciones

Funcin SI con condiciones simples

6.

Abre el archivo auxiliar ExplicaSi.xlsx.

7.

Colcate en la primera hoja de ExplicaSi, se llama Presentacin. Lee la explicacin que aparece de la

funcin Si. El resto de hojas contienen distintos ejemplos de uso de la funcin Si que realizaremos en los

siguientes ejercicios.

8. Colcate en la siguiente hoja llamada 1-SI con nmeros(en las expresiones slo aparecen nmeros). Utiliza

la funcinSipara calcular la bonificacin de cada alumno teniendo en cuenta que el que ha entregado los

ejercicios tiene una bonificacin de 0,5 puntos y el resto 0.

La bonificacin de un alumno tiene dos posibles valores. Siempre se elige uno u otro segn se cumpla o no unacondicin (ha entregado algn ejercicio?). Podemos comprobar si la condicin se cumple o no con los datos que

tenemos en nuestro libro (columna H, fila correspondiente al alumno). Por tanto, es adecuado utilizar la funcin Si.


7/26

7Funciones

En primer lugar, planteamos de forma general el caso del primer alumno. Piensa cules deben ser los argumentos de la

funcin Sien la celda E6:

1. Condicin: La condicin que debemos comprobar si se cumple o no es ..... D6="S"

2. Caso Verdadero: La bonificacin si la condicin se cumple es ................... 0,5

3.

Caso Falso: La bonificacin si la condicin no se cumple es ...................... 0

Tenemos que escribir en la celda E6la correspondiente frmula separando, como siempre, un argumento del siguiente

con un punto y coma, y teniendo en cuenta que la estructura de la funcin Sies:

=SI(condicin; expresin_si_verdadero; expresin_si_falso)

Por tanto debes introducir la frmula:

Despus de confirmar la frmula, arrastra la celda E6sobre las celdas correspondientes del resto de alumnos.

9. Colcate en la siguiente hoja llamada 2-SI con texto y frmula(una expresin es una frmula y la otra un

texto). En este caso, calcula la bonificacin de cada alumno teniendo en cuenta que el que ha entregado

algn ejercicio tiene una bonificacin de 0,2 puntos por cada ejercicio entregado y en el resto deseamos

que aparezca el textoSin entregar.

Procede como en el caso anterior, teniendo en cuenta:

una funcin Sipuede incluir frmulas como expresiones

si la expresin a calcular es una constante de tipo texto, se escribe entre comillas.El resultado ser:


8/26

8 Funciones

10.

Colcate en la siguiente hoja llamada 3-Ms de dos posibilidades (cuando hay ms de dos posibles

expresiones a calcular hay que utilizar una funcin Si anidada dentro de otra funcin Si). En este caso,

calcula la bonificacin de cada alumno teniendo en cuenta que los alumnos que no entregan ejercicios, no

tienen bonificacin. Para el resto:

- Si entregan hasta 3 (inclusive), 0,1 puntos por ejercicio entregado.

-

Si entregan hasta 6 (inclusive), 0,2 puntos por ejercicio entregado.

-

Si entregan ms de 6, una bonificacin total de 1,5 puntos.

En primer lugar, piensa cules deben ser los argumentos de la funcin Sien la celda E6:

1. La condicin que debemos comprobar si se cumple o no es ..........D6=0

2. La bonificacin si la condicin se cumple es ................................0

3. La bonificacin si la condicin no se cumple depende del nmero de ejercicios entregados; deberemos calcularla

utilizando otra funcin Si.

Por tanto, la frmula de la celda E6empezar as:

=SI(D6=0; 0; SI()).

Ahora debes pensar cules deben ser los argumentos de la nueva funcin Sicuando no se cumple D6=0:

3.1.La condicin que debemos comprobar ahora si se cumple o no es .............. D6


9/26

9Funciones

caso, si entregan ejercicios tienen una bonificacin de 0,5 puntos y si no entregan ejercicios tienen una

bonificacin de 0 puntos.

En primer lugar, calcula la bonificacin en el caso en que tenemos el dato de cuntos ejercicios han entregado, esto es,

suponiendo que en la columna Dno tenemos filas vacas. Esta ser nuestra primera funcin Si. Comprueba que el

resultado es correcto en esos casos.

Despus completa la funcin anidndola dentro de otra funcin Sique compruebe la condicin que nos hemos saltadoantes, esto es, que compruebe si tenemos algn dato en la celda correspondiente de Ejercicios entregados?

Observa que los argumentos de esta segunda funcin Si en la celda E6sern:

1. La condicin que debemos comprobar si se cumple o no es .........D6=""

2. La bonificacin si la condicin se cumple es ................................ ""

3. La bonificacin si la condicin no se cumple se calcula con ........ la frmula cuando tenemos el dato.

La frmula de la celda E6tendr la siguiente estructura:

=SI(D6=""; ""; frmula_primera_funcin_Si)

Observa que las dobles comillas estn seguidas en los dos casos, sin ningn espacio entre ellas; esto es as porque loque se pretende es que cuando la celda D6est vaca (sin ni siquiera un espacio en blanco) la celda con la bonificacin

(E6) quede tambin vaca.

12. Colcate en la ltima hoja llamada 5-Operar con el resultado de SI (utilizar la funcin SI junto con otras

operaciones). En este caso, calcula la bonificacin de cada alumno teniendo en cuenta que los alumnos que

no entregan ejercicios, no tienen bonificacin. Para el resto:



-

Si entregan ms de 6, 0,3 puntos por ejercicio entregado.

La bonificacin del alumno es siempre el nmero de ejercicios que ha entregado multiplicado por un valor. El valor por

el que se multiplica vara segn no haya entregado ejercicios, haya entregado 3 ejercicios o menos, 6 ejercicios o menos,

o ms de 6 ejercicios. En primer lugar, calcula ese valor variable utilizando una funcin Si.

Despus completa la frmula multiplicando el nmero de ejercicios por el resultado de la funcin Si. En la celda E6

obtendrs algo as:

=D6 * frmula_funcin_Si


10/26

10 Funciones

Acerca de la funcin SI

La funcin SIse utiliza cuando las expresiones que deben calcularse en un rango de celdas dependen de sialguna condicin es verdadera o no (siempre que se pueda comprobar a partir de los datos que se tienen).

Tiene tres argumentos que se escriben necesariamente en este orden:

=SI(condicin; expresin_si_verdadero; expresin_si_falso)

El resultado de esta funcin es el resultado de evaluar expresin_si_verdadero cuando la condicin esverdadera y es el resultado de expresin_si_falsocuando la condicin es falsa.Si tecleamos la frmula, para revisarla mejor, podemos dejar espacios entre el punto y coma (;) y la expresinsiguiente pero nunca entre el nombre de la funcin (SI) y el parntesis abierto.Si se prefiere, en los casos sencillos se pueden utilizar el correspondiente cuadro de dilogo de la funcin:

Las condicionespueden ser simples o complejas. Ejemplos de condicionessimplesson:

Condicin Verdadera Falsa ...

A1>=5 si el contenido de la celda A1 es mayor o igual que 5 en otro caso

A1="" si la celda A1 est vaca en otro caso

A1="da" si el contenido de la celda A1 es el texto da en otro caso

A17 si el contenido de la celda A1 es distinto de 7 en otro caso

B2+B3>100 si la suma de los contenidos de las celdas B2 y B3 es superior a 100 en otro caso

Las expresionesa evaluar pueden ser una constante o una frmula (con constantes, celdas, funciones).Cuando aparece un dato de tipo texto en la condicin o en una expresin de la frmula, el texto debe


11/26

11Funciones

escribirse entre dobles comillas.

Cuando queremos comprobar si varias condiciones son verdaderas simultneamente, usamos una condicincomplejade tipo Y. Por ejemplo, si queremos expresar una condicin que sea verdadera si A1es mayor oigual que 5 y adems B2 contiene el texto lunes, escribimos la prueba lgica compleja:

Y(A1>=5; B2="lunes")

Una condicin compleja de tipo Y es verdadera slo cuando todas las condiciones que aparecen en susargumentos son verdaderas. En otro caso, es falsa.

Por otro lado, cuando queremos comprobar si se alguna de varias condiciones es verdadera, usamos unacondicincomplejade tipo O. Por ejemplo, si queremos expresar una condicin que sea verdadera si A1esdistinto de 7 o si A1 est vaca (o si se cumplen ambas), escribimos la prueba lgica compleja:

O(A17; A1="")

Una condicin compleja de tipo O es verdadera cuando al menos una de las condiciones que aparecen en susargumentos es verdadera. En otro caso, es falsa.En el siguiente ejemplo podemos hacer uso de la funcin SI. Suponemos que tenemos una Hoja Excelcon losnombres y las notas numricas de unos alumnos.

En primer lugar, calculamos en C2 la calificacin del alumno Pepe. Esa calificacin es Suspenso si la nota esmenor que cinco yAprobadoen otro caso. Los textos los debemos introducir entre comillas. Por tanto, la funcinpara C2es:

=SI(B2


12/26

12 Funciones

13.

Guarda y cierra el libro ExplicaSI.

Revisin de la funcin SI con condiciones simples

14. Crea un nuevo libro llamado Funciones.

15. Llama Notasa la primera hoja de ese libro, y escribe en ella lo siguiente:

16.

Calcula en C2la calificacin del alumno Pepe teniendo en cuenta que es: Suspensosi la nota es menor que

cinco;Aprobadosi la nota est entre cinco y siete (menor que 7); Notablesi est entre siete y nueve (menor

que 9); ySobresalienteen otro caso.

Utiliza varias funciones Sianidadas y recuerda que los datos textuales se escriben entre comillas.Comprueba si la frmula que has escrito es correcta. Para ello, introduce distintos valores para la nota en la celda B2y

observa cmo vara la calificacin.

17.

Qu sucede si dejas B2en blanco (la celda vaca)?

18.

Modifica la frmula para que cuando B2est en blanco, la celda C2se muestre vaca pero, al introducir un

nota C2muestre la calificacin correspondiente.

Recuerda que si no queremos hacer ningn clculo cuando nos falta el dato de una celda, basta anidar la frmula que

tenamos (para el caso en que s tenemos el dato) dentro de una funcin Si que compruebe si hay dato. En este

ejercicio:

=SI(B2=""; ""; frmula_caso_con_nota)

19. Comprueba cul es la respuesta de esta funcin. Para ello, introduce distintos valores para la nota y prueba

tambin a borrar la nota. Asegrate de que siempre el resultado es el correcto.

20.

Aade al menos 10 nuevos alumnos a la hoja junto con sus notas y duplica la frmula de C2para calcular

sus calificaciones.

21. Guarda y cierra el libro Funciones.

22. Crea un nuevo libro llamado Comisiones y escribe en l la tabla que aparece a continuacin donde se

recogen los nombres de una serie de vendedores de una empresa y el importe de sus ventas:


13/26

13Funciones

23.

Calcula la comisin que debe recibir cada vendedor teniendo en cuenta que:

a) Si el importe de las ventas es inferior o igual a 5.000 euros entonces la comisin es un 10% de las

ventas.

b)

Si el importe de las ventas es superior a 5.000 euros e inferior o igual a 10.000 euros entonces la

comisin es de un 10% de los primeros 5.000 euros y un 12% del resto.

c) Si el importe de las ventas es superior a 10.000 euros entonces la comisin es de un 10% de los

primeros 5.000 euros, un 12% de los siguientes 5.000 euros y un 15% del resto.

Utiliza funciones Siadecuadas para obtener el siguiente resultado:

24. Guarda y cierra el libro Comisiones.


14/26

14 Funciones

Funcin SI con condiciones complejas

25. Abre tu libro Funcionesy adele una nueva hoja llamada NotasComplejas. Copia all, a partir de la celda

A1, la tabla de notas de la hoja Notas.

26. Modifica las frmulas para que cuando en una fila aparezca el nombre del alumno y la celda de la nota

est en blanco, aparezca como calificacin No presentado. En otro caso, las calificaciones se mantienen como

antes.

Observa que para que aparezca como calificacin No presentadodeben cumplirse simultneamente dos condiciones:

que la celda del nombre no est en blanco y que la de la nota s lo est.

=SI(Y(A2""; B2=""); "No presentado"; frmula_anterior)

27.

Completa la frmula de las calificaciones para que contemple todos los casos siguientes:

a)

Si tanto el nombre como la nota estn en blanco, la calificacin debe quedar en blanco.

b) Si el nombre est en blanco y no la nota, la calificacin debe mostrar el mensajeERROR!.

c)

Si el nombre no est en blanco y la nota s, la calificacin es No presentado.

d)

En otro caso (el nombre y la nota no estn en blanco) la calificacin es la que corresponda con los

convenios habituales (menor que 5 esSuspenso, mayor o igual que 5 y menor que 7 esAprobado...).

28. Completa la columna de calificaciones con un formato condicional que resalte con fondo rojo claro y texto

rojo oscuro todas las celdas en las que aparezca el mensaje de error.

Utiliza Inicio\Formato condicional\Resaltar reglas de celdas\Es igual a

29.

Guarda y cierra el libro Funciones.

Revisin de la funcin SI con condiciones complejas

30.

Abre el archivo auxiliar Calificacionesy observa que contiene una tabla donde se recogen los nombres de

una serie de alumnos y sus notas en los tres trimestres de un curso.

31. En la hoja Caso1calcula con un decimal la nota media de cada alumno interpretando como 0 las notas que

estn en blanco.

Observa que la funcin Promediono te sirve.


15/26

15Funciones

32.

Calcula las calificaciones teniendo en cuenta que a una nota media igual o superior a 5 le corresponde una

calificacin deAPTO y en otro caso la calificacin es NO APTO.

33. En la hoja Caso2copia la tabla tal como la tienes ahora. Modifica las frmulas para el clculo de la nota y

la calificacin de modo que no aparezca nada en ninguna de las dos si un alumno no tiene nota en algn

trimestre.

34. En la hoja Caso3copia la tabla otra vez. Vuelve a modificar las frmulas para el clculo de la nota y la

calificacin de modo que si falta el dato de Nombre y apellidos, aparezca un mensaje de error. En otro

caso, si estn todas las notas, haga el clculo correspondiente. Si falta alguna nota, aparece como Nopresentado. Las celdas con mensaje de error, deben estar con fondo y texto en tonos de rojo.

Para el formato de las celdas con error, utiliza un formato condicional.

35.

Aade algunas filas con datos adecuados para comprobar todos los casos. Cuando este todo correcto,

guarda y cierra el libro Calificaciones.


16/26

16 Funciones

Funcin BUSCARV

36. En el siguiente bloque de ejercicios vamos a desarrollar un ejemplo donde se utiliza la funcin BuscarV.

Supongamos que una papelera ha asignado un cdigo a cada uno de los tipos de artculos que vende.

Adems, tiene preparada una Hoja Excel para archivar los datos bsicos de cada venta: cdigo y nombre

del artculo vendido, n de unidades vendidas, precio de venta cada unidad y total de la venta. Colcate

en una hoja vaca de tu libro Funciones, llmala Papelera, y escribe en ella la siguiente tabla donde serecogen los cdigos de los artculos que han sido vendidos en el almacn y las cantidades vendidas.

Escribe los datos en las mismas celdas que indica la figura.

37. Nuestra intencin es aprender a completar el nombre de artculo automticamente sin tener que teclearlo en

cada ocasin. Para ello, aadimos la siguiente tabla donde se recogen una lista de cada uno de los

artculos que se venden en la papelera y sus cdigos. Utilizaremos esta tabla para completar

automticamente los nombres de artculos en la tabla de ventas de la papelera.

Escribe los datos en las mismas celdas que indica la figura.

38. Da el nombre tablaDeArticulosal rango $C$19:$D$22de la hoja.

Selecciona el rango C19:D22, en el cuadro de nombresescribe tablaDeArticulosy confirma.

Observa que en el rango seleccionado no se incluyen las celdas que dan ttulo a las columnas, slo se incluyen los datos

(son las nicas celdas donde debe buscar Excel). Recuerda que el nombre de un rango no puede contener espacios en

blanco.

39. Utiliza la funcin BuscarV para completar los nombres de artculos en la tabla Ventas de la papelera

segn los datos de la tabla Artculos de la papelera.

Para completar el nombre del artculo correspondiente a una venta, debemos fijarnos en cul es el cdigo que aparece

en la venta, localizarlo en la primera columna de la tabla de artculos y quedarnos con el dato adecuado de la fila donde

aparece ese cdigo.


17/26

17Funciones

En primer lugar, plantearemos de forma general el caso de la primera venta. Piensa cules son las instrucciones que le

debemos dar a Excelmediante la funcin BuscarVen la celda B4.

1. Dato a buscar: El cdigo de la venta ............................................................................. A4

2. Rango donde buscar: El rango donde estn los datos de los artculos ........................... tablaDeArticulos

3.

N de columna: La columna del rango anterior donde est el nombre del artculo ....... 2

4. Ordenacin: Si la columna de cdigos est ordenada en el rango donde buscar ...........Falso

Tenemos que escribir en la celda B4la correspondiente frmula separando, como siempre, un argumento del siguiente

con un punto y coma, y teniendo en cuenta que la estructura de la funcin BuscarVes:

=BUSCARV(dato_a_buscar; rango_donde_buscar; num_columna; rango_ordenado?)

Por tanto debes introducir la frmula:

Despus de confirmar la frmula, arrastra la celda B4sobre las celdas correspondientes del resto de ventas.

Nota: No es obligatorio utilizar un nombre de rango como segundo argumento pero, para poder reutilizar la funcin

duplicndola en las dems filas, s es imprescindible referirte al rango con una referencia absoluta.

40. En la lista de datos de los artculos del almacn vamos a aadir su precio. Ampla la tabla de artculos del

almacn de forma que quede como se muestra a continuacin:

41. Amplia el rango de la tabla de artculos para que incluya tambin la columna de precios.


18/26

18 Funciones

Elige Frmulas\Nombres definidos\Administrador de nombres, selecciona tablaDeArticulos, selecciona el

contenido del cuadro de texto Se refiere a, selecciona el rango$C$19:$E$22de la hoja Papelera, confirma y cierra.

Comprueba que has realizado bien la actualizacin. Comprueba que los clculos que tenas en la tabla de ventas siguen

siendo correctos.

42.

En la tabla de ventas del almacn aade el precio de las unidades vendidas y el total de la venta como se

muestra en la siguiente tabla.


19/26

19Funciones

Para el precio debes usar la funcin BUSCARVy para el total la frmula correspondiente.

Acerca de la funcin BUSCARV

La funcin BUSCARV se utiliza cuando los valores que deben aparecer en un rango de celdas se encuentran enuna determinada matriz de datos (es decir, un rango de celdas con dos o ms columnas) y se pueden localizara partir de la primera columna de dicha matriz.

Tiene cuatro argumentos que se escriben necesariamente en este orden:

=BUSCARV(dato_a_buscar; rango_donde_buscar; nmero_de_columna; ordenado?)

Y se interpreta as:

Busca el dato_a_buscaren la primera columna de rango_donde_buscar.

En la fila de rango_donde_buscardonde se encuentra ese dato, localiza el valor que aparece en lacolumna nmero_de_columnadel rango (contando la primera como 1).

Muestra el valor localizado como resultado en la celda donde se ha escrito la frmula.

Interpreta que FALSOindica que la primera columna del rango_donde_buscarno est ordenada y que

debe buscar exactamente el valor_buscado. Si no lo encuentra, devuelve el error #N/A. Interpreta que VERDADEROindica que la primera columna del rango_donde_buscars est ordenada

y que si no encuentra el valor_buscado, localiza el valor ms prximo a l.

Por ejemplo, se tiene en una hoja de clculo Exceluna tabla donde en cada fila se recopilan una serie dedatos de las provincias de Aragn. La primera columna de la tabla contiene una abreviatura para identificarla provincia.

Se tiene otra tabla donde en cada fila se recopilan una serie de datos municipios de Aragn. En esta tabla,podramos rellenar como dato de cada municipio cul es la abreviatura de la provincia en el que se encuentray utilizar la funcin BUSCARV para rellenar el nombre completo de la misma. La tabla podra ser:


20/26

20 Funciones

La frmula para completar automticamente el nombre de la provincia a partir de su abreviatura en la celdaI10es:

=BUSCARV(H10; Aragn; 3; FALSO)

donde Aragncorresponde al rango $B$4:$E$6que mostramos a continuacin. La frmula de I10 se puedearrastrar sobre las siguientes filas para encontrar el nombre completo de sus provincias.

Nota: si se prefiere no usar nombres de rango y la frmula se va a duplicar en otras filas, tambin podemosescribir directamente:

=BUSCARV(H10; $B$4:$E$6; 3; FALSO)

Algunas variantes de la funcin explicada que pueden ser de utilidad:

Si se desea buscar un dato igual o aproximado al valor_buscado, se escribe como ltimo argumentoVERDADEROo no se escribe nada (en lugar de FALSO). Entonces es necesario que la primera columnade la matriz donde buscar est en orden ascendente. En este caso, si BUSCARV no encuentra elvalor_buscado, devuelve el dato de la columna solicitada que est en la fila que corresponde almayor valor de la primera columna de la matriz, que sea menor o igual al valor_buscado(salvo quetodos los datos sean mayores, en cuyo caso devolver #N/A).

BUSCARVbusca datos en vertical. Si en el rango a buscar los datos se distribuyen por filas en lugar

de por columnas, se puede buscar en horizontal con la funcin BUSCARH.

Nota: En las primeras versiones en espaol de Excel de Office 2010, cambiaron el nombre de esta funcin porCONSULTAV.

43. Guarda y cierra el libro Funciones.

44. Abre el archivo auxiliar AtletismoOlimpico. Observa que contiene una hoja con los cdigos de tres letras

para pases establecidos por el Comit Olmpico Internacional. Tiene otras hojas con los resultados de

pruebas y el cdigo COI del pas de los competidores. Utiliza la funcin BUSCARV para completar las

columnas con el nombre completo del pas. Comprueba los resultados. Cirralo cuando termines.

Observa que en puedes usar la funcin BUSCARVen una hoja y tener la tabla de bsqueda en otra.

Prcticas globales con funciones

En este bloque tienes una serie de ejercicios en los que practicars sobre todo el uso de las funciones que hemos

visto en ejercicios anteriores. Para ahorrarte tiempo, para cada ejercicio el profesor te habr proporcionado un

archivo auxiliar con los datos constantes necesarios introducidos ya. El ejercicio consiste en que completes los

datos que faltan en las tablas que se proponen con frmulas adecuadas. Cada vez que termines un ejercicio,

comprueba que los resultados son correctos con los datos que te han dado y que tambin lo seguirn siendo si

modificas o aades datos. Cuando todo sea correcto, guarda y cierra el archivo.

45.

Abre el archivo auxiliar MatriculaUniversidad.xlsx. Observa que tiene una hoja Cdigoscon tablas de datos

y otra hoja MatrculaAlumno con varias tablas con columnas vacas. Completa esta hoja de clculo tal y

como est en la figura que aparece a continuacin. Ten en cuenta que:- Todos los datos que faltan debes calcularlos utilizando frmulas.


21/26

21Funciones

-

La titulacin, la asignatura y los crditos deben extraerse de las tablas correspondientes de la hoja

Cdigos.

- El precio de la matrcula depende del nmero de crditos.

- Se aplica un 10% de descuento si es la primera matrcula de la asignatura y un 20% si es una

asignatura premiada con este descuento.

-

El descuento total es la suma de descuentos(por primera matrcula y por asignatura premiada) que se

aplican.- Define un formato condicionalpara la cantidad a pagar de cada asignatura de modo que la cantidad

aparezca en rojo si es mayor de 600 euros y aparezca en verde si es inferior a 200 euros.

-

Las filas vacasdeben contener las frmulas adecuadas para que en el momento que se registren los

datos de otra asignatura se calculen automticamente los datos correspondientes.

- Los nmeros seguirn un formato adecuado.


22/26

22 Funciones

46.

Abre el libro auxiliar Hotel_El_descanso.xlsx. Completa la hoja de clculo tal y como est en la figura que

aparece a continuacin. Ten en cuenta que:

- En las celdas que faltan por completar tienes que introducir frmulas.

- En el detalle de la estancia, el precio total de la habitacin se calcula teniendo en cuenta el tipo de

habitacin y el nmero de noches.

-

Para el clculo del coste del canal de pago se tiene en cuenta la cuota fija.

-

La tabla resumen se calcula respecto a los datos de la columna que contiene los totales a pagar.- Si se modifica cualquiera de los datos de la tabla (tipo de habitacin utilizada por un husped, tarifas

de las habitaciones; cuota fija por uso de canal de pago; etc.) todos los clculos debern seguir siendo

correctos. Comprubalo cuando termines.

- Las filas vacasdeben contener las frmulas adecuadas para que en el momento que se registren los

nuevos huspedes en el hotel se calculen automticamente los datos correspondientes.


23/26

23Funciones

47.

En la carpeta auxiliar RevisinExcelFunciones, encontrars varias parejas de archivos xlsx y pdf: el pdf es un

documento donde se indican las condiciones sobre el caso a resolver y se muestra una imagen de los

resultados correctos; el libro es un archivo Excel con datos donde debes aadir las correspondientes

frmulas. Resuelve, al menos, aquellos casos que te indique tu profesor.

Ejercicios adicionales

48. Crea un libro llamadoAntigedad.xlsx, escribe los datos textuales que se indican y completa con frmulas:

- En B2 utiliza la funcin correspondiente.

-

Debajo, en B3, escribe una fecha concreta

-

En las siguientes celdas, escribe las frmulas correspondientes y presenta los resultados sin decimales.

- Para el clculo de meses, se asumen meses de 30 das

En B2utiliza la funcin que devuelve la fecha actual (tienes las funciones de tipo fecha ms importantes al final del

siguiente apartado). Ten en cuenta que puedes restar fechas y que se piden los resultados redondeados sin decimales.

49.

Mejora la solucin anterior de modo que: cuando B3est en blanco, todos los clculos queden en blanco; y,

al rellenar el dato, se muestren los resultados correspondientes. Guarda y cierra el libroAntigedad.

50.

Abre el libro auxiliar CalculosConFechas.xlsx. Completa la hoja de clculo tal y como est en la figura que

aparece a continuacin. Ten en cuenta que:

- Tienes que introducir frmulasen las tres ltimas columnas.


datos de otro empleado se calculen automticamente los datos correspondientes.

-

Debes hacer uso de las funciones de fecha que proporciona Excely comprobar el resultado teniendo en

cuenta en qu fecha ests realizando el ejercicio.


24/26

24 Funciones

51.

Guarda y cierra el libro CalculosConFechas.

52. Abre el libro auxiliar Saltos.xlsx. Completa la hoja de clculo tal y como est en la figura que aparece a

continuacin. Ten en cuenta que:

- Tienes que introducir frmulasen las tres columnas vacas.


datos de otro atleta se calculen automticamente los datos correspondientes.

-

Debes calcular el nombre de la prueba usando la funcin buscarvy teniendo en cuenta que se calcula en

funcin del cdigo de la prueba.

- Debes calcular la media de los tres saltos.

-

Debes calcular la clasificacin de los saltos utilizando la tabla de Clasificacin de saltos.

53. Comprueba que si en la tabla Clasificacin de saltos cambias la expresin Muy buena por Estupenda, se

actualiza automticamente la clasificacin de los atletas correspondientes. Si no ocurre as, haz los cambios

adecuados en las frmulas para conseguirlo.

54. En la hoja anterior realiza un grfico combinado de columnas y lneas de modo que se representen la

marca del primer salto (mediante columnas) y la media (mediante lneas). Guarda y cierra el libroSaltos.

Haz un grfico de dos columnas. Despus selecciona la serie correspondiente a la media y cambia el tipo de grfico para

dicha serie.


25/26

25Tabla resumen de funciones

Tabla resumen de funciones

Funciones matemticas

Devuelve

abs(num) el valor absoluto de un nmero

cos(num) el coseno de un nmero

exp(num) e elevado a un nmero

ln(num) el logaritmo neperiano de un nmero

mdeterm(matriz) el determinante de una matriz

minversa(matriz) la matriz inversa de una matriz

mmult(matriz1;matriz2) la matriz producto de dos matrices

pi() el nmero

potencia(num;exp) el resultado de elevar un nmero a una potencia

radianes(ngulo) convierte grados en radianesraz(num) la raz cuadrada de un nmero

redondear(num;num_decimales) redondea un nmero al nmero de decimales indicado

seno(num) el seno de un nmero

suma(num1; num2;...) la suma de los argumentos

Funciones estadsticas

Devuelve

coef.de.correl(matriz1;matriz2) el coeficiente de correlacin entre dos conjuntos de datos

contara(valor1;valor2;...) el nmero de valores que hay en la lista de argumentos

covar(matriz1;matriz2) la covarianza de los valores por pares

desvestp(num1;num2;...) la desviacin estndar de la poblacin total

frecuencia(datos;grupos) la distribucin de frecuencia como matriz vertical

max(num1;num2;...) el mximo de una lista de argumentos

mediana(num1:num2;...) la mediana de los nmeros

min(num1;num2;...) el valor mnimo de una lista de argumentos

moda(num1;num2;...) el valor ms comn de un conjunto de datos

promedio(num1;num2;...) el promedio de los argumentos

varp(num1;num2;...) la varianza de la poblacin total

Funciones de bsqueda y referencia

Devuelve

buscarv(dato_a_buscar;rango;num_columna) el valor de la celda que encuentra tras buscar eldato_a_buscar en la primera columna del rango, ydesplazarse a travs de la fila donde lo encuentra hastallegar al nmero de columna indicado

indice(matriz;num_fila;num_col) el elemento de una matrz que ocupa una fila y unacolumna determinada.

transponer(matriz) la transposicin de una matrz.


26/26

26 Funciones

Funciones de base de datos

Devuelve

bdcontara(rango_tabla;nombre_campo;rango_criterios) el nmero de celdas en la tabla del campo indicadoque no estn en blanco y que cumplen los criterios

bdextraer(rango_tabla;nombre_campo;rango_criterios) el valor de la tabla del campo indicado de la nicafila que cumple los criterios (si hay ms de una filaque cumpla los criterios esta funcin da error)

bdmax(rango_tabla;nombre_campo;rango_criterios) el valor mximo de la tabla del campo indicadopara las filas que cumplen los criterios

bdmin(rango_tabla;nombre_campo;rango_criterios) el valor mnimo de la tabla del campo indicado paralas filas que cumplen los criterios

bdpromedio(rango_tabla;nombre_campo;rango_criterios) el promedio de los valores de la tabla del campoindicado para las filas que cumplen los criterios

bdsuma(rango_tabla;nombre_campo;rango_criterios) la suma de los valores de la tabla del campoindicado para las filas que cumplen los criterios

Funciones de texto

Devuelve

concatenar(texto1;texto2;...) los argumentos de texto concatenados

igual(texto1;texto2) si dos valores de texto son iguales o no

Funciones lgicas

Devuelve

no(valor_lgico) falso si el argumento es verdadero yverdadero si el argumento es falso (niega elvalor lgico dado como argumento)

o(valor_lgico1;valor_lgico2;...) verdadero si alguno de los argumentos esverdadero

si(condicin;expresin_si_verdad;expresin_si_falso) la expresin_si_verdad si la condicin esverdadera, y la expresin_si_falso si lacondicin es falsa

y(valor_lgico1;valor_lgico2;...) verdadero si todos los argumentos sonverdaderos

Funciones de fechas

Devuelve

ao(fecha) el ao de una fecha

dia(fecha) el da de una fecha

fecha(ao; mes; da) fecha de un ao, mes y da

hoy() la fecha actual

mes(fecha) el mes de una fecha

02Funciones

Documents

Transcript of 02Funciones