Pour calculer chaque ressource de base contenue dans des kits créatifs imbriqués, distinguez les ressources directes du contenu des kits, multipliez à chaque niveau d’imbrication, puis additionnez les résultats. Ce guide pour Excel pour Mac permet de créer une matrice complète des besoins et une recherche exacte par ressource et par kit.

La tâche consiste à déterminer combien d’exemplaires de chaque élément graphique sont nécessaires pour un kit, ou pour un nombre choisi de kits, lorsque les kits en contiennent d’autres. Tous les noms et toutes les quantités ci-dessous sont fictifs. Le calcul compte les exemplaires graphiques ; il ne déduplique pas les fichiers source réutilisables.

Quelles versions d’Excel pour Mac prennent en charge cette méthode ?

Utilisez Excel pour Microsoft 365 pour Mac, Excel 2024 pour Mac ou Excel 2021 pour Mac. Microsoft répertorie ces éditions pour les fonctions matricielles et de recherche utilisées ici. Microsoft documente aussi explicitement les matrices dynamiques dans Excel 2021 pour Mac. Le guide utilise des plages de feuille de calcul ordinaires et des formules validées avec Entrée. Fonction MMULT, Fonction INDEX, Nouveautés d’Excel 2021 pour Mac.

Une formule de matrice dynamique remplit automatiquement les cellules voisines ; ce comportement est appelé propagation. Saisissez chaque formule matricielle uniquement dans la cellule de départ indiquée et laissez sa plage de sortie vide. Gardez ces formules en dehors des tableaux Excel : Microsoft indique que les formules matricielles propagées ne sont pas prises en charge dans les tableaux. Formules de matrice dynamique et comportement des matrices propagées.

Les calculs de l’exemple ont été vérifiés avec un petit calcul Python déterministe. Les formules n’ont pas été exécutées dans l’application de bureau Excel.

Comment consigner les ressources directes et les kits imbriqués ?

Commencez par une feuille de calcul vierge. Vous allez créer deux matrices d’entrée : l’une pour les éléments graphiques de base placés directement dans chaque kit, l’autre pour les kits placés directement dans d’autres kits. La séparation de ces données évite de compter à nouveau des quantités déjà développées.

Saisir la matrice des ressources de base directes

Collez ce bloc séparé par des tabulations dans A1. Sa zone numérique, B2:D5, comporte quatre lignes de ressources et trois colonnes de kits.

Ressource\tKitPoint\tKitRayures\tKitStudio
Point\t2\t1\t0
Arc\t1\t0\t2
Grille\t0\t3\t1
Tuile\t0\t1\t0

Lisez chaque colonne comme le contenu graphique direct d’un kit. Un KitPoint contient directement deux exemplaires de Point et un Arc. Un KitRayures contient directement un Point, trois Grilles et une Tuile. Ses kits imbriqués seront consignés séparément.

Utilisez zéro comme valeur numérique pour chaque composant absent. Microsoft précise que les entrées de MMULT doivent contenir des nombres ; des cellules vides ou du texte peuvent produire #VALUE!. Gardez les libellés en dehors des plages numériques transmises à la fonction. Fonction MMULT.

Saisir la matrice des kits imbriqués

Collez le bloc suivant dans A7. Ici, une ligne identifie le kit composant et une colonne identifie le kit parent. La matrice numérique d’imbrication occupe B8:D10.

Kit composant\tKitPoint\tKitRayures\tKitStudio
KitPoint\t0\t2\t1
KitRayures\t0\t0\t3
KitStudio\t0\t0\t0

La matrice décrit les recettes suivantes :

  • KitPoint ne contient aucun autre kit.
  • KitRayures contient deux KitPoints.
  • KitStudio contient un KitPoint et trois KitRayures.

Ne consignez que le contenu immédiat. L’unique KitPoint direct de KitStudio figure dans D8 ; les six KitPoints supplémentaires contenus dans ses trois KitRayures doivent être calculés. Saisir sept dans D8 compterait ces six KitPoints deux fois.

Utilisez exactement le même ordre de kits dans B1:D1, B7:D7 et A8:A10 : KitPoint, KitRayures, KitStudio. La multiplication matricielle fait correspondre les positions ; des libellés identiques ne corrigeront pas un ordre d’entrée différent.

Cet exemple suppose des quantités entières non négatives et des recettes qui aboutissent à des ressources de base. Aucun kit ne peut contenir sa propre définition, même par l’intermédiaire d’une chaîne d’autres kits.

Comment calculer la matrice complète des besoins ?

Dans l’explication ci-dessous, appelons B la matrice des ressources directes et N la matrice d’imbrication. Ce sont des notations mathématiques ; vous n’avez pas besoin de les définir comme des noms dans le classeur. Multiplier B par N donne les ressources apportées par un lien de contenance. Multiplier ce résultat par N ajoute un autre lien.

Pour ces trois kits, la chaîne la plus profonde est KitStudio contenant KitRayures, qui contient KitPoint. Par conséquent, la matrice complète est la somme des ressources directes, des ressources à un niveau d’imbrication et de celles à deux niveaux d’imbrication :

Besoins complets = B + BN + BN²

L’exposant indique une multiplication matricielle répétée. Dans Excel, calculez-la avec MMULT ; l’opérateur ordinaire ^2 appliqué à une plage élèverait au carré chacune de ses valeurs.

Calculer les contributions à chaque niveau d’imbrication

Saisissez cette formule dans G2, puis appuyez sur Entrée. Elle remplit G2:I5 avec les ressources atteintes après un lien d’imbrication.

=MMULT($B$2:$D$5,$B$8:$D$10)

Saisissez la formule suivante dans L2. Elle remplit L2:N5 avec les ressources atteintes après deux liens d’imbrication.

=MMULT(G2:I5,$B$8:$D$10)

Les dimensions expliquent l’ordre de multiplication : une matrice de ressources de quatre lignes sur trois colonnes multipliée par une matrice d’imbrication de trois lignes sur trois colonnes produit une autre matrice de ressources de quatre lignes sur trois colonnes. Microsoft précise que le nombre de colonnes du premier tableau doit être égal au nombre de lignes du second. Fonction MMULT.

Pour une vérification intermédiaire, la colonne KitStudio de G2:I5 doit contenir 5, 1, 9 et 3. Sa colonne dans L2:N5 doit contenir 12, 6, 0 et 0, dans l’ordre des ressources Point, Arc, Grille, Tuile.

Additionner les contributions directes et imbriquées

Saisissez KitPoint, KitRayures et KitStudio dans Q1:S1. Saisissez Point, Arc, Grille et Tuile dans P2:P5. Placez ensuite cette formule dans Q2 :

=$B$2:$D$5+G2:I5+L2:N5

La matrice complète dans Q2:S5 doit être la suivante :

Ressource de base KitPoint KitRayures KitStudio
Point 2 5 17
Arc 1 2 9
Grille 0 3 10
Tuile 0 1 3

Vérifiez manuellement le nombre de Points dans KitStudio. Il ne contient aucun Point direct, en reçoit deux de son KitPoint direct et quinze de ses trois KitRayures, qui contiennent chacune cinq Points. Le total est de dix-sept. Cette vérification suit chaque chemin menant à la même ressource, y compris ceux qui passent par un sous-kit partagé.

Comment vérifier qu’aucun niveau d’imbrication ne manque ?

Un total plausible ne prouve pas que le développement est complet. Utilisez la matrice d’imbrication elle-même pour vérifier s’il existe un autre niveau de contenance.

Saisissez cette formule dans G8 pour calculer la matrice d’imbrication à deux liens dans G8:I10 :

=MMULT($B$8:$D$10,$B$8:$D$10)

Saisissez ensuite cette formule dans L8 pour calculer la matrice d’imbrication à trois liens dans L8:N10 :

=MMULT(G8:I10,$B$8:$D$10)

Pour les données fournies, G8:I10 contient une seule valeur non nulle : six KitPoints atteints via un KitRayures à l’intérieur de KitStudio. Toutes les valeurs de L8:N10 sont nulles. Il n’existe aucun chemin à trois liens ; aucune contribution ne peut donc dépasser les deux niveaux d’imbrication déjà inclus.

Cette règle d’arrêt découle du modèle matriciel. Avec des quantités non négatives, une matrice d’imbrication suivante entièrement nulle signifie que tous les chemins d’imbrication plus longs sont également absents. Cette vérification est plus fiable que celle consistant à vérifier si une ressource particulière reçoit encore une contribution.

Dans un classeur plus grand comportant k kits distincts et aucun cycle de contenance, la chaîne la plus longue compte au maximum k−1 liens. Continuez à multiplier chaque matrice de contributions aux ressources par la matrice d’imbrication et à additionner les résultats jusqu’à ce que la puissance d’imbrication suivante soit entièrement nulle. La formule à trois termes ci-dessus est complète pour cet exemple ; elle doit être étendue si des kits supplémentaires créent des chaînes plus profondes.

Classer les kits du plus simple au plus imbriqué facilite l’inspection. Dans la matrice d’imbrication fournie, toutes les valeurs non nulles se trouvent au-dessus de la diagonale. Une valeur diagonale positive indique une auto-contenance directe. Si une chaîne plus longue revient à son kit de départ, corrigez cette recette avant d’utiliser ce développement fini.

Comment rechercher une quantité pour une ressource et un kit ?

Définissez W1 sur Ressource, X1 sur Kit et Y1 sur Quantité. Saisissez Arc dans W2 et KitStudio dans X2. Dans Y2, saisissez :

=INDEX($Q$2:$S$5,MATCH(W2,$P$2:$P$5,0),MATCH(X2,$Q$1:$S$1,0))

Le résultat doit être 9. Le premier MATCH repère la ligne de la ressource ; le second repère la colonne du kit. INDEX renvoie leur intersection. Microsoft documente cette sélection par ligne et colonne et confirme que MATCH avec 0 demande une correspondance exacte sans nécessiter de libellés triés. Fonction INDEX, Fonction MATCH.

Gardez les libellés uniques sur chaque axe, car MATCH en mode exact renvoie la première position correspondante. Un libellé absent produit #N/A. Laissez cette erreur visible pendant que vous vérifiez les noms, plutôt que de la remplacer par zéro : dans la matrice des besoins, zéro a déjà une signification précise. Fonction MATCH.

Comment calculer les besoins d’un lot de plusieurs kits ?

Saisissez KitPoint, KitRayures et KitStudio dans Q7:S7. Saisissez 2, 1 et 3 dans Q8:S8. Il s’agit des quantités demandées séparément : deux KitPoints, un KitRayures et trois KitStudio.

Saisissez Nombre total d’exemplaires dans U1, puis cette formule dans U2 :

=MMULT($Q$2:$S$5,TRANSPOSE($Q$8:$S$8))

TRANSPOSE convertit la ligne horizontale de demande des trois kits en colonne verticale. La multiplication produit ensuite un total par ressource de base et remplit U2:U5. Microsoft indique que TRANSPOSE permute les lignes et les colonnes. Fonction TRANSPOSE.

Ressource de base Calcul Nombre total d’exemplaires
Point 2×2 + 5×1 + 17×3 60
Arc 1×2 + 2×1 + 9×3 31
Grille 0×2 + 3×1 + 10×3 33
Tuile 0×2 + 1×1 + 3×3 10

N’ajoutez pas les KitPoints et KitRayures nécessaires à l’intérieur de ces kits à cette ligne de demande. Leur contenu est déjà inclus dans la matrice complète. Les entrées de demande supplémentaires représentent des kits supplémentaires commandés séparément.

Que vérifier si le résultat semble incorrect ?

Effectuez les vérifications suivantes avant de modifier les formules :

  1. Une matrice affiche #SPILL! : vérifiez si sa plage de sortie prévue contient déjà des valeurs ou des cellules fusionnées. Déplacez ce qui bloque ou déplacez la formule. Microsoft indique que ces deux situations empêchent la propagation. Comment corriger une erreur #SPILL!.
  2. Une multiplication affiche #VALUE! : vérifiez si les entrées contiennent des cellules vides, du texte, des en-têtes inclus ou des dimensions incompatibles. Fonction MMULT.
  3. Les totaux sont trop élevés : vérifiez si les matrices directes contiennent déjà le contenu développé des sous-kits ou si des quantités de kits internes ont été ajoutées à la demande du lot.
  4. Les totaux sont trop faibles : vérifiez la puissance d’imbrication suivante. Un résultat non nul signifie qu’il faudra peut-être ajouter un autre niveau de contribution.
  5. Les nombres semblent cohérents sous de mauvais en-têtes : comparez l’ordre des kits dans les deux matrices d’entrée et dans les en-têtes de sortie.

Lors de la mise à jour du classeur, actualisez toutes les plages d’entrée concernées, les calculs auxiliaires, les en-têtes de sortie, les plages de recherche et les plages de demande. Ces formules utilisent des adresses fixes ; saisir un nouveau kit à côté des entrées existantes ne l’intègre pas automatiquement.

Gardez les recettes directes comme entrées modifiables, conservez la vérification de profondeur d’imbrication à côté du calcul et vérifiez un chemin complet vers une ressource après chaque modification structurelle. Ces vérifications permettent à la matrice finale de servir à la fois aux recherches de kits individuels et aux totaux de lots fondés sur les mêmes recettes enregistrées.

Sources