funciones de búsqueda y referencia -...

© Reservados todos los derechos. El contenido de esta obra está inscrito en el registro territorial de la propiedad intelectual de la Comunidad de Madrid con nº M-006418/2006. Este material es para uso personal por parte del alumno que esté realizando este curso. Queda prohibido por el autor reproducir, plagiar o distribuir esta obra sin la preceptiva autorización.

Funciones de Búsqueda y Referencia

Funciones de búsqueda y referencia - II

Vas a preparar una hoja de cálculo para elaborar una dieta equilibrada.

Abre el archivo Dieta.xlsx que encontrarás en tu carpeta de ejercicios.

En la hoja Dieta introducirás las funciones necesarias para saber si la dieta incluida es o no equilibrada.

En la hoja Tabla aparece una tabla con los aportes nutricionales de una serie de alimentos.

Selecciona la celda D4 y pulsa sobre el botón Insertar función de la barra de fórmulas.

En el cuadro de diálogo Insertar función selecciona la función BUSCARV perteneciente a la categoría Búsqueda y referencia.

Fíjate en la descripción de la función que aparece en la parte inferior del cuadro de diálogo.

Como puedes observar, esta función sirve para buscar un valor dentro de la primera columna de un rango de celdas, y una vez encontrado, devuelve el valor contenido en la celda de la misma fila y de la columna que indiquemos.

Pulsa sobre el botón Aceptar.



Aparece el cuadro de diálogo Argumentos de función. Observa que esta función puede recibir 3 ó 4 argumentos. Los tres primeros son obligatorios, por eso aparecen resaltados en negrita, y el último es opcional.

Haz clic sobre el cuadro del argumento Valor_buscado. Lee en la parte inferior del cuadro la descripción de dicho argumento.

Como has podido leer, en este argumento debes introducir el valor o la referencia a la celda que contiene el valor que quieres buscar dentro del rango de celdas. Pliega el cuadro de diálogo y selecciona la celda C4.

Vuelve a desplegar el cuadro de diálogo pulsando el botón y haz clic sobre el argumento Matriz_buscar_en para poder leer su descripción.



Como has podido observar se trata del rango de celdas que contienen los valores que hay que buscar y recuperar. En este caso, la tabla de calorías alojada en la hoja Tabla.

Pliega el cuadro de diálogo pulsando sobre el botón del extremo derecho del cuadro Matriz_buscar_en y selecciona el rango de celdas A2:E33 de la hoja Tabla.

Vuelve a desplegar el cuadro de diálogo pulsando el botón y haz clic dentro del cuadro del argumento Indicador_columnas para poder leer su descripción en la parte inferior del cuadro de diálogo.

En este argumento debes introducir la distancia, en número de columnas, que existe entre el comienzo de la tabla y la columna de donde deseas recuperar el valor. En este caso, deberás introducir el número 3, ya que la columna de las proteínas es la tercera columna del rango de la Matriz_buscar_en.



Haz clic dentro del cuadro correspondiente al argumento Ordenado para poder leer su descripción.

Como puedes observar, en este cuadro debes indicar si deseas que muestre el valor correspondiente a una aproximación del valor buscado, o si por el contrario quieres que busque la coincidencia exacta.

Introduce el valor FALSO para buscar el valor exacto en el rango de celdas.

Pulsa sobre el botón Aceptar.

Sitúate en la hoja Tabla y comprueba que el valor mostrado es el correspondiente a las proteínas que contiene el yogur desnatado.

Vuelve a la hoja Dieta y edita la celda D4, para ello selecciónala y haz clic dentro del cuadro de edición de la barra de fórmulas.

Sitúa el punto de inserción al final de la fórmula y teclea *B4/100 ya que tenemos que saber el valor nutricional de la cantidad exacta de alimento, y el valor de la tabla se refiere a 100 gramos de alimento.



Pulsa INTRO.

Selecciona la celda D4 y cópiala.

Selecciona el rango de celdas discontinuo D5:D8;D10:D13;D15:D17, despliega la lista de opciones de pegado, pulsando sobre la flecha que aparece debajo del botón Pegar del grupo Portapapeles y selecciona la opción Fórmulas.

Observa que los valores obtenidos son incorrectos. Esto es debido a que hemos utilizado referencias relativas al rango de celdas del argumento Matriz_buscar_en.

Edita el contenido de la celda D4, para ello selecciónala y haz clic en la barra de fórmula.

Introduce Referencias absolutas a las referencias a las celdas que intervienen en el argumento Matriz_buscar_en.



Repite la operación de copiado y pegado de fórmulas.

Ahora el resultado sí es el correcto.

Vuelve a copiar la celda D4 y pega su fórmula en el rango de celdas restante.

Observa que nuevamente el resultado no es el correcto.

El motivo vuelve a ser el tipo de referencias utilizado en la función.

Selecciona la celda E4, y observa la función en la barra de fórmulas.

Observa que la función BUSCARV está buscando en el rango correcto, pero no está buscando el valor correcto, ya que el argumento Valor_buscado contiene una referencia a la celda D4 en lugar de una referencia a la celda C4, esto es debido a que hemos copiado una fórmula situada en la celda D4 con una referencia relativa a la celda C4 y al pegarla en la celda E4, esta referencia relativa ha variado.



Edita el contenido de la celda D4 y cambia la referencia relativa a la celda C4 por una referencia mixta que fije la posición de la columna. Haz lo mismo con la referencia a la celda B4.

Selecciona la celda D4 y cópiala. Selecciona el rango de celdas E4:G4 y pega en ellas la fórmula que acabas de copiar.

Sólo se copia en el rango E4:G4, porque como puedes observar, el valor contenido en el rango de celdas D4:G4 es el mismo, esto es debido a que aún queda un detalle más, debemos cambiar el argumento Indicador para que la función BUSCARV nos devuelva el valor de los lípidos, calorías e hidratos de carbono, en lugar del valor de las proteínas.

Selecciona la celda E4, haz clic en cualquier zona situada entre los paréntesis de la función BUSCARV en la barra de fórmulas, y pulsa sobre el botón Insertar función.

Aparecerá el cuadro de diálogo Argumentos de función donde deberás cambiar el argumento Indicador.

Introduce el número 4 para el argumento Indicador ya que la columna correspondiente a los lípidos en el rango de celdas de la Matriz_buscar_en es la columna número 4.

Copia la celda E4 y pega su fórmula en el rango de celdas discontinuo E5:E8;E10:E13;E15:E17.



Repite la operación cambiando el argumento indicador de la celda F4 por el número 2 y en la celda G4 por el número 5. Observa el resultado.

Suma ahora los totales de los valores nutricionales.

Para ello, selecciona la celda D19 y pulsa sobre el botón Autosuma del grupo Modificar de la ficha Inicio, selecciona el rango de celdas D4:D17 y pulsa la tecla INTRO.

Pincha sobre el cuadro de relleno situado en la esquina inferior derecha de la celda D19 y arrastra hasta la celda G19.

Ya sólo queda averiguar, si esta dieta se adecua a las necesidades de nuestro cuerpo.

Las necesidades de proteínas de una persona sana que lleve un ritmo de vida normal son 2,5 gramos por Kg, luego una persona que pese 60 Kg necesitará 2.5 x 60 gramos de proteínas diarios.



Las necesidades de lípidos son 1 gramo por Kg. diario.

Las necesidades calóricas diarias son de 45 calorías por Kg.

De estas calorías debemos calcular, qué parte corresponde a grasas y proteínas para poder calcular la cantidad recomendada de hidratos de carbono.

Cada gramo de lípidos aporta 9 calorías y cada gramo de proteínas 4.

Luego las calorías necesarias medidas en hidratos de carbono, resultarán de restar a la necesidad calórica las calorías aportadas por lípidos y proteínas.

Introduce tu peso en la celda C1.

Selecciona la celda D22 e introduce la fórmula = C1*2,5.

Selecciona la celda D23 e introduce la fórmula =C1.

Selecciona la celda D24 e introduce la fórmula =C1*45.

Selecciona la celda D25 e introduce la fórmula =(D24-(4*D22+9*D23))/4

Determina ahora si la dieta propuesta es o no equilibrada.

Utiliza la función SI para determinar si la dieta es equilibrada o no, y la cantidad de proteínas, lípidos e hidratos de carbono que faltan o sobran en esta dieta para una persona de tu peso.

Selecciona la celda F22 y pulsa sobre el botón Insertar función de la barra de fórmulas.

Selecciona la función SI.

Como Prueba_lógica establece la siguiente fórmula D22=D19.

En el argumento Valor_si_verdadero introduce el valor “EQUILIBRADO”.

Sitúate en el cuadro correspondiente al argumento Valor_si_falso, despliega la lista que aparece en el extremo izquierdo de la barra de fórmulas y selecciona la función SI.



Como Prueba_lógica de la función anidada, introduce la fórmula D22>D19.

En el cuadro correspondiente al argumento Valor_si_verdadero introduce el siguiente texto:

"Necesita " & D22-D19 & " gramos más de proteínas"

Observa que es una fórmula que utiliza un operador que no habías utilizado hasta ahora, el operador & concatena cadenas de caracteres con el resultado de fórmulas o funciones.

Recuerda que las cadenas de caracteres se representan entre comillas.

D22-D19 no aparece entrecomillado porque no es una cadena de caracteres, sino una fórmula, de modo que aparecerán las cadenas de caracteres concatenadas con el resultado de esta fórmula.

En el cuadro correspondiente al argumento Valor_si_falso introduce el siguiente texto:

"Necesita " & D19-D22 & " gramos menos de proteínas"

Selecciona la celda F23 y haz lo mismo para los lípidos.

Repite la misma operación para las calorías e hidratos de carbono.

El resultado para un peso de 60 Kg debe ser el siguiente:



Equilibra la dieta utilizando los alimentos de la tabla y aumentando o disminuyendo la cantidad de alimento.

Por ejemplo cambia la merluza por salmón, aumenta la cantidad de yogur a 150 gramos, cambia los 150 gramos de pollo por 300 gramos de ternera, cambia la pera por nueces, etc.

funciones de búsqueda y referencia -...

Documents