AccueilVersion Calc 1.x Version Calc 2.x Présentation Calc 3.xBien débuter Calc Fonctions Calc Exercices Calc FormulairesAstuces CalcPrécisions calculsLa bonne formuleProgrammation BasicLiens diversDéfinitionsRecherche sur siteHumourInfos, news

EXERCICES CALC. Poids Idéal

 

Accès direct : Poids Idéal 1 Poids Idéal 2

 

 

Commençons par ouvrir un nouveau classeur.

Pour cet exemple, je me suis basé sur les infos trouvées sur les sites distrimed.com et doctissimo.fr.

Sur http://www.distrimed.com, dans la page consacrée au poids on trouve plusieurs formules pour calculer le poids idéal. Nous allons utiliser les formules de Broca, Lorentz, Creff, puis l'IMC (Indice de masse corporelle).

Nous allons donc entrer les données sexe, taille, poids, âge et morphologie dans les cellules B1 à B5, et les résultats des formules apparaîtront dans les cellules B7 à B12.

Ajustez les largeurs de colonnes (voir hauteur lignes, largeur colonnes) et les nommons les cellules (voir cellules nommées) comme suit :

Cellule
nom
A1
sexe
A2
taille
A3
poids
A4
age
A5
morpho
A10
IMC

 

Ensuite, entrons nos formules. Commençons par la formule de Broca est simple:

poids idéal (en Kg) = taille (en cm) - 100

Nous pouvons l'écrire comme suit, dans la cellule B7 :

=B2-100, puisque la taille se trouve en B2. Mais il est bien plus sage d'écrire :

=taille-100, puisque nous avons nommé cette cellule et surtout la formule est nettement plus lisible.

Evidement, si la cellule B2 (taille) est vide, le résultat sera négatif !

Ensuite, la formule de Lorentz fait apparaître la notion de sexe. Comment résoudre simplement ce petit souci ? Si nous devons systématiquement entrer les variables homme, femme dans la cellule B2, ce n'est pas simple et de plus il y des risques d'erreur. Plusieurs solutions sont possibles:

  • Taper 1 ou 2 suivant le sexe est une solution simpliste qui marchera.
  • Tapez H ou F suivant le sexe est une alternative

Nous allons choisir la méthode "propre", où il suffit d'entrer H ou F pour le sexe. Une petite formule se chargera de convertir les H ou F en 1 ou 2, comme par exemple :

=SI(sexe="H";1;2)

Formule simpliste. Un peu trop ! En effet le résultat sera 1 s'il s'agit de H, mais dans n'importe quel autre cas, ce sera 2, donc une femme. Il faut donc faire un SI imbriqué comme suit :

=SI(sexe="H";1;SI(sexe="F";2;"ERREUR"))

Dans ce cas, si sexe est H, la valeur 1 est attribuée, sinon, si sexe est F, on attribue la valeur 2, sinon, dans tous les autres cas, on affiche "ERREUR".

Dernier cas d'erreur possible si on tape la lettre en minuscule, il faut la convertir en majuscule. Pour ceci il existe la fonction MAJUSCULE

La fonction =MAJUSCULE("mon test") donnera pour résultat MON TEST. C'est bien le but recherché.

Voici donc la formule finale que nous inscrivons en C1 et que nous nommerons sexnum (sexe numéro 1 ou 2)

=SI(MAJUSCULE(sexe)="H";1;SI(MAJUSCULE( sexe)="F";2;"Erreur"))

 

La suite: La formule de Lorentz fait intervenir des coefficients suivant qu'on soit homme ou femme. Le problème sexe est traité. Reste ces coefficients. La formule dit :

Homme: taille(en cm) - 100 - ((taille (en cm) - 150) /4 )
Femme: taille (en cm) - 100 - ((taille (en cm) - 150) /2,5)

Il est donc possible d'utiliser la fonction SI() comme par exemple :

=SI(sexnum=1;4;2,5) Si sexnum=1, on prend la valeur 4, sinon la valeur 2,5

Vous pouvez aussi utiliser la fonction CHOISIR comme suit :

=CHOISIR(sexnum;4;2,5)

Plus simple à lire. on lit le numéro d'ordre de la variable sexnum (1 ou 2),

Notre formule finale, en B8 sera donc :

=taille-100-((taille-150)/CHOISIR(sexnum;4;2,5))

 

 

Abordons maintenant la formule de Creff. Elle fait intervenir la notion de morphologie (normale, gracile ou large). C'est l'occasion de voir comment traiter les formules sans reconvertir les lettres en 1, 2 ou 3. Nous allons donc entrer directement les valeurs N, G, L dans la cellule B5. En C5 un mini commentaire sera indispensable, comme par exemple :N (normale), G (gracile), L (large), pour vous rappeler quoi faire !

Pour utiliser cette formule vous devez aussi entrer l'âge en B4.

Dans la formule de Creff les coefficients sont:

morphologie "normale" = (taille (en cm) - 100 + Age (en années) / 10 ) * 0,9
morphologie "gracile" = (taille (en cm) - 100 + Age (en années) / 10 ) * 0,9 * 0,9
morphologie "large" = (taille (en cm) - 100 + Age (en années) / 10 ) * 0,9 * 1,1

Ce que nous pouvons formuler ainsi :

=(morpho="N")*0,9
=(morpho="G")*0,9*0,9
=(morpho="L")*0,9*1,1

Que nous pouvons réunir ainsi :

((morpho="N")*0,9)+((morpho="G")*0,81)+((morpho="L")*0,99)

Complétons la suite de la formule en B9 : =(taille-100+(age)/10)*(((morpho="N")*0,9)+((morpho="G")*0,81)+((morpho="L")*0,99))


Formule complète ? Non, il manque la fonction MAJUSCULE. A vous de jouer...

IMC et obésité. Selon : http://www.doctissimo.fr/ dans la rubrique obésité nous lisons "L'obésité peut être définie par l'indice de masse corporelle IMC (BMI en anglais), calculé selon la formule : poids (kg) / taille au carré (m2) ".

Donc en B10, on peut écrire :

=poids/(taille*taille)

Pourtant le résultat est quasi nul. Où est l'erreur ? Dans la taille, entrée en cm, alors qu'ici on parle de mètres. Il faut donc écrire:

=poids/((taille/100)*(taille/100))

Nommez cette cellule IMC si ce n'est déja fait.

 

Maintenant ça complique avec des valeurs attribuées:

Corpulence idéale 18,5 à 24,9
Surcharge pondérale ou "embonpoint" 25 à 29,9
Obésité modérée 30 à 34,5
Obésité sévère 35 à 39,9
Obésité très sévère supérieur à 40

Plusieurs solutions sont possibles pour afficher les différents cas. Dans tous les cas, il faut vérifier si nous entrons dans les différents critères.

On commence à 18,5. Pour vérifier ce 1er critère on peut écrire dans une cellule:

=IMC>=18,5 On vérifie si la valeur IMC est supérieure ou égale à 18,5. Il faut noter que si on avait écrit: =IMC=>18,5 une erreur aurait été générée, sans gravité puisque le tableur propose la transformation en =IMC>=18,5. Si vous ne voyez pas la différence, elle se tient au niveau de "=>" refusé et ">=" accepté.

Le résultat sera VRAI ou FAUX inscrit dans la cellule. Il est possible d'avoir un résultat 1 ou 0 pour VRAI et FAUX. Il suffit de modifier la formule en:

=(IMC>=18,5)*1

Pour savoir si on entre dans les critères de "corpulence idéale", il faut savoir si on ne dépasse pas la valeur maxi (24,5). Il faut donc écrire la formule:

=IMC<=24,9

Maintenant on connaît les limites mini et maxi. Il faut encore les combiner avec la fonction ET. On regroupe donc les 2 formules en 1:

=ET(IMC>=18,5; IMC<=24,9)

Et toujours un résultat VRAI ou FAUX, correspondant bien aux critères mini/maxi.

Ensuite il faut appliquer le même système pour les autres critères. Cependant un piège nous attend si nous respectons scrupuleusement les critères tels qu'ils sont définis. Par exemple, la corpulence idéale maxi=24,9 et le début de l'embonpoint mini=25. Si par hasard IMC=24,95 il ne correspondra à rien ! Il faudra donc reprendre la formule précédente et créer les suivantes comme ceci:

=ET(IMC>=18,5;IMC<25)
=ET(IMC>=25;IMC<30)
=ET(IMC>=30;IMC<35)
=ET(IMC>=35;IMC<40)
=IMC>=40

Et maintenant, il faut regrouper tout ça avec la fonction SI. Il va falloir imbrique 5 SI ensembles avec des risques d'erreur.

STOP Essayez de trouvez la bonne formule en imbriquant les SI, sans tricher... Progressez patiemment. Vouloir imbriquer les 5 SI du premier coup est voué à l'échec.

Voici donc le résultat qui fonctionne:

=SI(ET(IMC>=18,5;IMC<25);"Idéal";SI(ET(IMC>=25;IMC<30);"Embonpoint";SI(ET(IMC>=30;IMC<35);"Obésité modérée";SI(ET(IMC>=35;IMC<40);"Obésité sévère";SI((IMC>=40);"Obésité très sévère";"Hors critères")))))

Une autre solution s'impose donc. Un peu plus haut je disais qu'on peut affecter une valeur à VRAI ou FAUX. Pourquoi ne pas profiter de cette solution en affectant un "numéro" à chaque résultat. Reprenons donc nos formules de bases en les modifiant:

=ET(IMC>=18,5;IMC<25)*1
=ET(IMC>=25;IMC<30)*2
=ET(IMC>=30;IMC<35)*3
=ET(IMC>=35;IMC<40)*4
=(IMC>=40)*5

Ce qui donne en regroupant :

=(ET(IMC>=18,5; IMC<25)*1) + (ET(IMC>=25; IMC<30)*2) + (ET(IMC>=30; IMC<35)*3) +(ET(IMC>=35; IMC<40)*4) + ((IMC>=40)*5)

Faites des essais. Vous pouvez voir que l'on obtient des valeurs de 0 à 5, pour zéro, hors critères et 1 à 5 pour les différents critères.

Vous pouvez choisir soit de faire la somme, soit de regrouper ces formules. Pour la somme, si par exemple vous faites vos essais dans les cellules de A15 à A19. Il faut appliquer la fonction SOMME avec :

=SOMME(A15:A19) en A20

sinon, pour revenir à la méthode précédente, vous pouvez inscrire cette formule en C10 par exemple (à côté de IMC)

=(ET(IMC>=18,5; IMC<25)*1) + (ET(IMC>=25; IMC<30)*2) + (ET(IMC>=30; IMC<35)*3)+ (ET(IMC>=35; IMC<40)*4)+ ((IMC>=40)*5)

Ensuite on utilise la fonction CHOISIR et l'on écrit :

=CHOISIR(C10;"Idéal";"Embonpoint";"Obésité modérée";"Obésité sévère";"Obésité très sévère")

C'est quand même plus simple et surtout plus lisible ! Une erreur (ERR:502) survient si on est hors critère (IMC<18,5).

Il suffit d'ajouter la fonction SI comme suit :

=SI(c10=0;"Hors critères";CHOISIR(A20;"Idéal";"Embonpoint";"Obésité modérée";"Obésité sévère";"Obésité très sévère"))

Enfin si vous adorez les formules à rallonge, vous pouvez toujours tester celle-ci :

=SI((ET( IMC>=18,5;IMC<25)*1)+ (ET( IMC>=25;IMC<30)*2)+ (ET( IMC>=30;IMC<35)*3)+ (ET( IMC>=35;IMC<40)*4)+ ((IMC >=40)*5)=0; "Hors critères"; CHOISIR((ET( IMC>=18,5; IMC<25)*1)+ (ET( IMC>=25;IMC<30)*2)+ (ET( IMC>=30;IMC<35)*3)+ (ET( IMC>=35;IMC<40)*4)+ ((IMC >=40)*5);"Idéal"; "Embonpoint"; "Obésité modérée"; "Obésité sévère"; "Obésité très sévère"))

C'est le genre de chose qui marche mais qui est source d'erreur. Pas facile de s'y retrouver.

D'autres formules ? En voici une autre. La Formule de Monnerot-Dumaine. Elle induit la notion de tour de poignet:

Poids idéal(en Kg) = (taille (en cm) - 100 + (4 * Circonférence du poignet (en cm))) / 2

Vous en savez assez pour vous débrouiller seuls. N'oubliez pas quelques règles de base:

  • Sauvegarder souvent.
  • Décomposez vos formules avant de les assembler.
  • Testez, testez...

 

Ca y est ? Vous avez fait des tests et tout fonctionne. Bravo! Je vais donc maintenant vous proposer de faire le même exercice avec des listes déroulantes et des cases à cocher. C'est un peu plus long, mais le résultat est beaucoup plus agréable. La suite est ici.

 

 


Et maintenant, à vos claviers.

 

Ce site n'est plus mis à jour. Merci aux nombreux visiteurs depuis 2003. Consultez le Wiki LibreOffice.
   

Le tableur Calc par l'exemple : christianwtd@free.fr
Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.0 France License.

  .

Site optimisé pour Firefox


Dernière modification : 29/08/2010 à 18h48


Compteur de visite temps réel LERAPIDE

Compteur non certifié