Analyse FR

ProcédureAnalyse de la Feuille de Route - AFR⚓

Prérequis

Votre feuille de route (FR) est remplie et les formules sont opérationnelles (cf  Jeu Maintenance : Feuille de Route -FR )

  1. B1 ; N° Équipage

    Le n° d'équipage se trouve dans la feuille de calcul nommée "feuille de route" dans la cellule B1.

    Double cliquez sur la cellule B1, tapez = à l'intérieur puis sélectionnez la feuille nommée "feuille de route", cellule B1, puis validez.

    On obtient donc dans la feuille "Synthèse 1" cellule B1 la formule ='Feuille de route'.B1

    Le nom de la feuille est encadrée par des cotes : 'nom de la feuille'

    Le . sépare le nom de la feuille de sa référence de cellule : ='nom de la feuille'.refcellule

  2. La suite

    Dans le tableau ci-dessous la colonne référence concerne la feuille "Synthèse 1".

    Référence

    Formule

    Remarques

    B6

    ='Feuille de route'.D46

    B7

    ='Feuille de route'.E46

    B8

    =B6-B7

    B9

    =B8/B6

    E7

    ='Feuille de route'.M46

    E8

    ='Feuille de route'.F46 +E12

    E9

    =E4+E7-E8

    B12

    =NB.SI('Feuille de route'.L6:L45;B11)

    Voir explications ci-dessous

    E12

    =SOMME(B11:D11)*3000

    C12 et D12

    recopie de B12 (voir explications ci-dessous

    B15 :E15

    ? ? ? ? ? ? ? ?

    A vous de jouer

  3. La formule en B12

    La fonction NB.SI[1] (cf concepts sur les fonctions ou tutoriel) sera revue par ailleurs : l'objectif est de rechercher dans la plage L6 :L45 la valeur de la cellule B11 et d'en comptabiliser le nombre.

    Exemple

    Autrement dit, vous avez dans votre feuille de route dans la plage L6 :L45 placé le code PV si vous avez acquis la protection "préparation du véhicule".

    La cellule B11 contient la valeur de type texte "PV"

    La formule NB.SI va donc retourner en B11 le nombre d'occurrence de la valeur PV  : c'est à dire le nombre de fois où on a une égalité entre la valeur PV dans la plage L6 :L45.

  4. La formule en C12

    Cas de B10 dans la formule B12⚓

    Il suffit à priori de recopier vers la droite la cellule B12. Faites le et observez le résultat.

    Dans la cellule B11, la référence relative à B11 deviendra donc C11 qui correspond à ce que l'on attend : en effet en C12 on veut compter le nombre de "PO" dont la valeur est bien C11.

    On copie la formule B11 en C11, B10 devient logiquement C10.

    Cas de H6 :H45 dans la formule B12⚓

    Selon le même raisonnement que ci-dessus, les L vont devenir M. Cela ne convient pas, car si notre critère de recherche change, la plage doit rester la même.

    Modifiez la plage L6 :L45 par $L6 :$L45 : on fige ainsi la colonne L en cas de copie vers la droite, et recopiez votre formule B12 jusqu'en D12. Vérifiez que les arguments obtenus sont conformes.

  5. Plage B15 :E15

    Sur quelle plage dans la feuille de route avez vous renseigné le passage aux stands ?

    Quelle cellule servira de critère ?

    NB.SI (plage ;critère) donnera le nombre d'occurrence du critère dans la plage

    Compléments

    Vous avez dans votre feuille de route dans la plage M6 :M45 placé le code MS1 ou MS2 ou MS3 ou MS4 si vous êtes passé par les différents stands.

    La cellule B14 contient la valeur de type texte "MS1", la cellule C14 contient la valeur de type texte "MS2" ... etc

    La formule NB.SI va donc retourner en B15 le nombre d'occurrence de la valeur MS1  : c'est à dire le nombre d'occurrence (nombre de fois) de la valeur MS1 dans la plage H4 :H41.

    Résultat en B15 : =NB.SI('Feuille de route'.$M6:$M45;B14)

    On fait référence à B14 en relatif et à la colonne M en absolu. Si on avait aussi fait référence aux n° de ligne en absolu, cela fonctionnerait aussi ($M$6 :$M$45)

  6. Synthèse 2 : C4

    Passez sur la feuille Synthèse 2.

    Nous souhaitons connaître dans la feuille de route le nombre d'événements subis pour chacun des codes normalisés.

    Essayez de chercher la formule correspondante en C4 et recopiez la vers le bas jusqu'en C12 : réfléchir la nature des références que vous allez utiliser pour la plage et le critère à fixer dans la fonction NB.SI[1]

    Corrigé

    =NB.SI('Feuille de route'.G$6:G$45;B4)

    Remarque

    La plage qui contient les données de recherche est bien G4 à G41. Mais comme nous allons recopier la formule vers le bas, il faut figer les n° de ligne, le $ est donc placé devant les n° de ligne pour obtenir G$4 à G$41 : le référencement aux lignes est donc absolu

    Le critère est dans la cellule B4 en relatif.

  7. Synthèse 2 : C15 et C26

    Il faut pour chaque code cumuler les kms perdus : c'est la fonction SOMME.SI[2] qui sera utilisée.

    Comme pour la fonction NB.SI on cherche dans une plage l'égalité avec un critère : on effectue alors la somme des valeurs de plage_somme pour les lignes qui vérifient l'égalité avec le critère.

    =SOMME.SI('Feuille de route'.G$4:G$41;B15;'Feuille de route'.E$4:E$41)

    En C26, déterminez la formule qui cumule les dépenses pour chaque code. La solution se trouve dans le bloc complément ci-dessous

    Compléments

    =SOMME.SI('Feuille de route'.G$4:G$41;B26;'Feuille de route'.F$4:F$41)