LOC – 14/08/2018 – 2
Présentation
Cette fiche présente en détail deux formules : le calcul du stock et la récupération, sur les feuilles Entrées et Sorties, des caractéristiques d’un lot. Mais au préalable, il me faut parler de la préparation des feuilles et des plages nommées.
1. Préparation des feuilles
L’application fonctionne parce que les feuilles sont préparées. Chacune est prévue pour enregistrer un nombre maximum de lignes (100 dans l’exemple). Cela signifie que sur les 100 lignes prévues, des formats sont enregistrés et surtout, les formules sont présentes dans toutes les colonnes calculées.
2. Plages nommées
J’utilise les plages nommées pour définir des plages de données. Plusieurs avantages :
les formules sont plus explicites avec des plages nommées qu’avec des références lignes-colonnes ;
en cas d’extension des zones, il est rapide de mettre à jour les plages nommées sans avoir besoin de corriger les formules ; et même, quand l’extension de zone se fait par insertion de lignes ou de colonnes à l’intérieur de la zone, les plages nommées concernées sont mises à jour automatiquement.
Ces plages nommées tiennent compte de la préparation des feuilles et du nombre maximum de lignes prévues. L’intégration des plages nommées permet d’étendre le nombre maximum de lignes facilement.
Plages nommées de l’application
Je désigne les plages nommées avec un préfixe correspondant à leur feuille (Cave, Entrées, Sorties). Sans autre complément, la plage indique la zone d’informations de la première à la dernière colonne : ces plages sont utiles pour les fonctions de type base de données pour des recherches ou extractions. Avec le suffixe « Ref », la plage indique la colonne Référence. Avec le suffixe « N », la plage indique le nombre de bouteilles mouvementées en Entrées ou Sorties.
À noter enfin : chacune des deux tables de libellés sont des plages nommées.
3. Formule de calcul du stock (feuille MaCAve)
Cette formule, en K2, se présente ainsi :
=SOMME.SI (EntreesRef; A2; EntreesN) -SOMME.SI (SortiesRef; A2; SortiesN).
Elle fait appel deux fois à la fonction Somme.si().
Cette fonction comprend 3 paramètres :
- Liste de recherche
- Information à rechercher
- Liste des éléments à totaliser
La liste de recherche est la colonne Référence de la feuille Entrées (EntreesRef, correspondant à la colonne A) ou de la feuille Sorties (SortiesRef, colonne A).
L’information à rechercher dans cette liste de recherche est la Référence inscrite en début de ligne de la feuille MaCave (sur la ligne 2, en A2).
La liste de éléments à totaliser est la colonne Nombre de bouteilles de la feuille Entrées (EntreesN, colonne H) ou Sorties (SortiesN, colonne C).
Comment fonctionne cette formule ?
Nous sommes sur la feuille MaCave, en ligne 2. La formule en K2 veut calculer le stock du lot dont la référence est en A2. Quand ce stock sera calculé, le système passera à la ligne 3, avec la formule K3, pour calculer le stock de la référence en A3, et ainsi de suite jusqu’à la fin du tableau de MaCave.
Dans un premier temps, la formule recherche la référence lue en A2 dans la feuille Entrées, dans la colonne Référence, zone nommée EntreesRef. Pour cela, elle balaie toute la colonne. Quand elle trouve sur une ligne la même référence que la référence lue, elle cumule le montant (nombre de bouteilles entrées de la colonne H) lu sur cette ligne. Puis elle passe à la ligne suivante, jusqu’à la fin du tableau Entrées. Il peut y avoir aucune, une ou plusieurs occurrences : cela importe peu, le cumul est fait selon les situations.
Dans un deuxième temps, la formule fait une recherche analogue mais dans la feuille Sorties.
Quand les deux cumuls sont terminés, la formule retourne leur différence (Entrées moins Sorties) et la pose comme résultat dans la cellule : c’est le stock.
Ces calculs ne sont effectués que lorsqu’une cellule mentionnée dans la formule (ou ses fonctions) est modifiée, c’est à dire concrètement quand une entrée ou une sortie sont enregistrées. Ils sont faits aussi au moment du chargement du classeur en mémoire.
4. Récupération des caractéristiques
Cette récupération se présente sur plusieurs colonnes des feuilles Entrées et Sorties.
Pour l’exemple, prenons la feuille Entrées, ligne 2, colonne E, Désignation : cette formule ressemble à :
=SI ($A2<>“”; RECHERCHEV ($A2; cave; 5; 0) ; “”)
La fonction Si() comprend trois paramètres :
- une condition : $A2<>“”;
- une fonction à exécuter si la condition donne un résultat VRAI;
- une fonction à exécuter si la condition donne un résultat FAUX.
En clair : si la cellule de début de ligne ($A2) n’est pas vide, la fonction Recherchev() est appliquée pour charger la cellule ; sinon, la cellule est laissée à vide.
La fonction Recherchev() comprend 4 paramètres :
- élément à rechercher (ici, $A2, la référence de début de ligne) ;
- matrice (ici, cave, correspond à tout le tableau de la feuille MaCave) ;
- colonne de l’élément à extraire (numéro dans la matrice en partant de 1 pour la première colonne ; ici, 5, correspond à la colonne E Désignation) ;
- type d’extraction (un code, 0 ou 1).
L’objectif de la fonction Recherchev() est de trouver une information (élément à extraire) présente, éventuellement, dans la ligne d’une matrice ; la recherche se fait par identification sur l’élément à rechercher dans la première colonne de la matrice (recherche verticale).
Deux types d’extraction sont possibles.
- Soit l’information d’identification recherchée dans la première colonne de la matrice doit être strictement identique à l’élément à rechercher. Dans ce cas, la première colonne de la zone de recherche peut présenter des éléments dans le désordre. Si l’élément à rechercher est présent, l’élément à extraire est retourné ; mais si aucune identification stricte n’est possible, une anomalie est retournée à la place de l’élément à extraire (Valeur non disponible, #N/D). Le type d’extraction indiqué est non trié (0), comme dans notre formule. La recherche s’arrête dès qu’une ligne est trouvée.
- L’autre type d’extraction ne nous intéresse pas ici.
Comment fonctionne Recherchev() ?
Le système balaie la première colonne de la matrice en comparant à chaque ligne la valeur lue avec l’élément à rechercher et en tenant compte du type d’extraction. Quand il trouve une ligne répondant aux critères, il retourne l’information présente sur cette ligne dans la colonne de l’élément à extraire.