martes, 9 de diciembre de 2014

CÁLCULO DE CALORÍAS - EXCEL 2007


¿Cuántas calorías se necesitan al día según genero, peso, altura y edad?

La formula pertenece a Harris-Benedict y en ella gracias a unos factores y los datos aportados por el consultante definimos lo que se llama Tasa Metabólica Basal (T.M.B.).

Formulas: 
Mujeres  [655 + (9.6 x Peso kg) ] + [ (1.8 x Altura cm) – (4.7 x Edad)] x Factor actividad 
Hombres  [66 + (13.7 x Peso kg) ] + [ (5 x Altura cm) – (6.8 x Edad)] x Factor actividad
Para comenzar crear una hoja nueva.

A partir de la fila 10 insertar todos los datos necesarios para que más adelante, una vez finalizada la tabla, podamos recoger con unas Listas esos datos e insertarlos para su cálculo.

Estos datos son el género y sus correspondientes coeficientes, unas escalas para el peso, altura y edad, por último el Factor actividad.


Estos valores siempre serán los mismos y solo hay dos opciones, los coeficientes corresponden a cada opción.

Sexo
Coef.
Coef. Peso
Coef. Altura
Coef. Edad
655
9,6
1,8
4,7
Hombre
66
13,7
5
6,8

Comenzamos a construir la tabla.

En este ejercicio la función "BuscarV" va a solucionar la mayoría de operaciones que realizará la calculadora de (T.M.B.).

El primer selector, celda "A3" escoge entre hombre o mujer, al cambiar el selector por uno u otro nos devuelve los índices fijos, estos aparecen en las celdas "B3", "C3", "D3" y "E3".

Para recoger estos índices hay que crear un rango con nombre, vamos a la ficha Formulas, grupo Nombres definidos, seleccionamos el rango de los coeficientes y le asignamos un nombre: “Coeficientes”.



Escribimos en “B3” la siguiente fórmula:

=SI(ESERROR(BUSCARV($A$3;Coeficientes;2;FALSO));"";BUSCARV($A$3;Coeficientes;2;FALSO))

Esta fórmula buscará en la columna 2 el coeficiente asignado a la celda “A3”, siempre que exista un texto en "A3" . 

Copiar esta fórmula a las otras tres celdas de coeficientes. Ahora hay que ir cambiando en las otras 3 formulas el número de columna en la que buscar.

=SI(ESERROR(BUSCARV($A$3;Coeficientes;3;FALSO));"";BUSCARV($A$3;Coeficientes;3;FALSO))

Con Validación de datos, ficha Datos, Herramientas de datos, creamos las 4 listas de peso, altura, edad y actividad.

Para que se muestre el tipo de actividad usaremos una formula casi idéntica a la anterior solo que el rango se le llamará “Actividad”

De esta manera cuando escogemos un coeficiente de actividad muestra una descripción a la cual hace referencia. Esta formula se insertará en "J1":

=SI(ESERROR(BUSCARV(I3;Actividad;2;FALSO));"";BUSCARV(I3;Actividad;2;FALSO))

La celda "G7" devuelve el resultado final de toda la operación.



La celda que muestra el resultado "G7" lo calcula de la siguiente manera:

=SI(A3=0;"";(B3+(C3*F3))+((D3*G3)-(E3*H3)*I3))

Unos detalles, si se quiere una reducción la celda “G6” mostrará unos porcentajes:
 5%, 10%, 15%, 20%.



La celda “G8” calculará esa reducción si hay un porcentaje en “G6”:

=SI(ESERROR(G7*(1-G6));"";G7*(1-G6))



Escribir un poco de código y crear una macro que limpie la tabla cuando se quieren introducir nuevos datos:

Sub BorrarDatos()

Range("A3,F3:I3,G6").Select
Selection.ClearContents
Range("A3").Select   

End Sub


Se pueden ocultar las filas de datos para que no se puedan borrar accidentalmente.


Al final queda una sencilla calculadora que con unos desplegables podemos saber cual es la T.M.B. que corresponde a unos parámetros, tales como el peso, altura, edad y un resultado según la actividad realizada a diario.


Relacionadas: