Statistiques et synthèses sur recherches partielles

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Statistiques sur recherches partielles

Grâce aux calculs matriciels, il est aisé d'engager des opérations de synthèse sur la base de termes de recherche incomplets. Par recoupements, les formules matricielles isolent les enregistrements concordants. Dès lors, nous simplifions le repérage et la synthèse des données.



Sur l'exemple illustré par la capture ci-dessus, l'utilisateur enclenche une recherche sur un groupe de codes catégories, reconnu par les trois premiers chiffres : 306. Il en résulte un dénombrement ainsi qu'un bilan comptable réalisé sur la base de données. De plus, ces résultats tiennent compte d'une potentielle contrainte supplémentaire émise sur le stock minimum.

Source et présentation de la problématique
Pour exercer nos travaux, nous proposons tout d'abord de récupérer une base de données.

• Télécharger le classeur matrices-fragments-recherches.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Des articles sont référencés dans une base de données. Les sept premiers caractères de la référence inscrite en colonne B désignent une catégorie. C'est pourquoi, il est intéressant d'offrir la possibilité à l'utilisateur d'exercer des recherches partielles. En choisissant ou en inscrivant un code catégorie dans le tableau de bord en cellule K5, l'application pourra alors rendre des statistiques intéressantes sur les produits de la même gamme. Mais il doit aussi être possible de saisir une référence complète.

Les données de synthèses attendues concernent le nombre d'éléments recensés selon les contraintes émises. En effet, il est possible d'indiquer un stock minimum à observer en cellule K6. Il s'agit donc d'un critère à recouper. De plus, la valeur marchande totale est demandée. Pour chaque article concerné, la quantité en stock doit être multipliée par le prix unitaire. Puis, tous ces montants doivent être additionnés. C'est l'essence même de la fonction matricielle SommeProd.

• En haut à gauche de la fenêtre Excel, déployer la liste déroulante de la zone Nom,

Comme vous pouvez le voir, chaque colonne de la base de données a été automatiquement nommée en fonction de son titre de champ. Si vous cliquez sur l'un de ces noms, l'intégralité des données correspondantes est sélectionnée. C'est ainsi que nous pourrons désigner les matrices dans les calculs pour simplifier la syntaxe des formules matricielles.

Recherche et dénombrement
Puisque l'information sur la référence est susceptible d'être partielle, notre calcul matriciel, destiné à recouper les critères, doit être en mesure de trouver un fragment de texte dans une chaîne. Nous parlons donc du code catégorie désigné à trouver dans les références de la base de données. De fait, la fonction Cherche est nécessaire. Elle retourne la position de l'occurrence trouvée. Cette position ne nous intéresse pas. Nous souhaitons seulement savoir si la recherche est fructueuse. Dans ce cas, nous comptabilisons l'enregistrement. De plus, si l'occurrence n'est pas trouvée, la fonction Cherche retourne une erreur. Et ces erreurs empêchent la formule matricielle d'aboutir. Pour les neutraliser, nous encapsulerons la recherche dans la fonction logique EstNum. En cas de succès, elle retourne Vrai, soit 1. En cas d'échec, elle retourne Faux, soit 0. Et ainsi le tour est joué pour que la fonction SommeProd ajoute les unités restituées à l'issue.

Pour bien comprendre le mécanisme du calcul matriciel que nous allons entreprendre, nous proposons d'exploiter l'assistant fonction. Grâce à lui, comme nous l'avons appris lors des formations précédentes, nous comprendrons l'interprétation temps réel des critères posés sur les matrices.

• En cellule K5, grâce à la liste déroulante, choisir la catégorie 3062PCN par exemple,
• Veiller pour l'instant à ce que le stock minimum soit réglé à 0 en K6,
• Sélectionner la cellule K9 et taper le symbole égal (=) pour initier le calcul matriciel,
• Saisir la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,

L'assistant pour la fonction Excel SommeProd se déclenche. Notre raisonnement n'est pas classique. Il consiste à recouper des critères sur des matrices. C'est pourquoi, nous allons établir l'intégralité de sa syntaxe dans son premier paramètre : Matrice1.

• Dans la zone Matrice1, taper la fonction logique suivie d'une parenthèse, soit : EstNum(,
• Saisir alors la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Désigner le code à trouver en cliquant sur sa cellule K5,
• Puis, taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Désigner alors la matrice des références par son nom, soit : Ref,
• Fermer la parenthèse de la fonction Cherche,
• Dans l'enchaînement, fermer la parenthèse de la fonction EstNum,

Aussitôt, vous voyez apparaître des indicateurs booléens sur la droite de la zone Matrice1. Ils renseignent sur l'emplacement des enregistrements ayant répondu positivement à la recherche. Et grâce à la fonction EstNum, les erreurs sont neutralisées et converties en valeurs booléennes (Faux). Ces booléens doivent à leur tour être convertis en chiffres pour organiser le décompte. Cette conversion va se produire naturellement par le recoupement de la seconde condition à suivre sur les stocks.

• A la suite de la syntaxe, taper le symbole étoile (*) pour annoncer la condition à recouper,
• Ouvrir une parenthèse pour accueillir la nouvelle matrice conditionnelle,

A ce stade, vous notez que les indicateurs booléens ont disparu. En effet, notre syntaxe n'étant pas achevée, aucune conclusion ne peut être tirée en l'état.

• Désigner la matrice des quantités en stock par son nom, soit :Stock,
• Taper le symbole supérieur suivi du symbole égal, soit : >=, pour l'inégalité à honorer,
• Cliquer sur la cellule de la contrainte sur les stocks, soit : K6,
• Fermer la parenthèse de cette seconde matricielle,

Sur la droite de la boîte de dialogue, vous notez la réapparition des indicateurs. Mais cette fois, ils sont transcrits en chiffres : 1 lorsque l'enregistrement concorde et 0 le cas échéant. La vocation de la fonction SommeProd est d'additionner toutes ces valeurs déduites. Il va donc en résulter le dénombrement des enregistrements appartenant à la catégorie spécifiée et dépassant le stock seuil indiqué.



Et à ce titre, vous notez l'affichage du résultat du dénombrement livré en avant-première en bas de la boîte de dialogue.

• Valider la syntaxe en cliquant sur le bouton Ok de l'assistant fonction,

De fait, nous obtenons un dénombrement qui s'ajuste aux contraintes dynamiques. Vous pouvez le tester en modifiant la catégorie de code et en réhaussant par exemple le seuil du stock.



La syntaxe complète de la formule matricielle que nous avons construite est la suivante :

=SOMMEPROD(ESTNUM(CHERCHE(K5; Ref))*(Stock>=K6))

Le calcul suivant se déduit naturellement du précédent. A chaque fois qu'un enregistrement concorde, il est repéré par le chiffre 1. Nous devons multiplier ce chiffre par la valeur marchande de l'article en stock. Cette valeur correspond à la multiplication du prix unitaire par la quantité en stock. A l'issue, la fonction SommeProd réalisera l'addition de toutes ces données. Nous obtiendrons donc la valeur marchande totale de tous les enregistrements concordants.

• Sélectionner la cellule K9,
• Dans sa barre de formule, sélectionner l'intégralité de la syntaxe,
• La copier à l'aide du raccourci clavier CTRL + C,
• Sortir de la barre de formule avec la touche Entrée du clavier,
• Dans la barre de formule de la cellule K10, coller (CTRL + V) la syntaxe copiée,
• Puis, adapter le calcul comme suit :

=SOMMEPROD(ESTNUM(CHERCHE(K5; Ref))*(Stock>=K6)*(Prix*Stock))

Comme nous l'annoncions, nous multiplions les précédents résultats du dénombrement par la valeur marchande de chaque enregistrement. Dans le cas où il ne concorde pas, cette valeur est multipliée par 0, donc ignorée.

Il convient d'attribuer un format monétaire à ce résultat. Vous pouvez utiliser le raccourci clavier CTRL + M sur la cellule K10 sélectionnée.

Mise en valeur dynamique des recherches
Pour appuyer les résultats de synthèse, il est toujours intéressant de faire ressortir les enregistrements correspondants de façon explicite. Pour cela, il suffit de bâtir une règle de mise en forme conditionnelle repérant chacun d'entre eux. Et cette règle peut exploiter de façon dérivée la syntaxe du calcul matriciel pour le dénombrement. En effet, une mise en forme conditionnelle raisonne de façon chronologique. Elle n'analyse pas une matrice mais les cellules d'une rangée tour à tour.

• Prélever la syntaxe de la cellule K9 depuis sa barre de formule par CTRL + C,
• Sortir de la barre de formule par la touche Entrée du clavier,
• Sélectionner toutes les informations de la base de données, soit la plage de cellules B4:H247,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie qui se propose juste en dessous pour l'activer,
• Dès lors, coller (CTRL + V) la syntaxe précédemment copiée,
• Ensuite, adapter la syntaxe comme suit :

=SOMMEPROD(ESTNUM(CHERCHE($K$5; $B4))*($G4>=$K$6))

Nous figeons le code cherché ($K$5) puisque l'analyse va se déplacer sur toutes les lignes de la base de données. Nous remplaçons la matrice de recherche par la première référence à analyser, afin de respecter la chronologie. C'est pourquoi, nous la conservons figée en colonne et nous la libérons en ligne ($B4). Ainsi, elles seront toutes passées en revue pour réaliser les comparaisons respectives.

De la même façon, pour respecter la contrainte sur les stocks, nous figeons totalement la cellule issue du panneau de contrôle ($K$6). Et, nous ne faisons plus référence à la matrice mais au premier stock que nous figeons en colonne et libérons en ligne, pour les mêmes raisons.

Lorsque ces conditions croisées sont vérifiées, les enregistrements correspondants doivent surgir.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu pâle,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir le rouge foncé pour la couleur du texte,
• Puis, valider ces attributs de format en cliquant sur le bouton Ok,
• Dans l'enchaînement, valider la création de la règle de nouveau avec le bouton Ok,

De retour sur la feuille, vous notez que des enregistrements sont mis en valeur. En y regardant de plus près, vous constatez qu'ils correspondent au code catégorie et au stock demandés.



Si vous tapez un code catégorie plus large, sur quatre chiffres par exemple, comme 3062, le repérage dynamique s'étend pour s'ajuste dynamiquement à la demande et en conformité avec les contraintes du tableau de bord.