Pour qu’une recherche d’inventaire dans Excel sélectionne la règle correspondante la plus précise, identifiez d’abord les lignes admissibles, puis comptez les champs renseignés. Renvoyez la quantité de la ligne unique ayant le score le plus élevé et signalez les égalités. Ce guide explique comment obtenir ce résultat pour des ressources artistiques fictives dans Excel pour Mac.

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

La formule ci-dessous utilise LET, XLOOKUP et des calculs de tableaux dynamiques. Microsoft documente ces fonctionnalités pour Excel pour Microsoft 365 pour Mac, Excel 2024 pour Mac et Excel 2021 pour Mac. Le guide des fonctionnalités d’Excel 2021 pour Mac mentionne explicitement LET, XLOOKUP et les tableaux dynamiques. Nouveautés d’Excel 2021 pour Mac, Fonction LET.

Excel 2016 et Excel 2019 ne prennent pas en charge XLOOKUP, selon la note de compatibilité explicite de Microsoft. Utilisez l’une des éditions Mac prises en charge pour cette formule exacte ; changer la façon dont vous saisissez la formule ne rend pas disponible une fonction manquante. La formule d’inspection facultative présentée plus loin dans cet article utilise FILTER, également documentée pour les trois éditions Mac prises en charge ci-dessus. Fonction XLOOKUP, Fonction FILTER.

Que signifie « la plus précise » pour votre inventaire ?

Dans cet exemple, vous recherchez la quantité associée à une ressource artistique à partir de trois attributs : le nom de la ressource, la finition et la taille. Un champ vide dans une règle signifie que celle-ci accepte n’importe quelle valeur pour cet attribut. Un champ renseigné doit correspondre à la valeur saisie correspondante. Parmi les règles admissibles, celle qui comporte le plus de champs renseignés l’emporte.

Il s’agit d’une règle explicite du classeur, définie pour cet exemple. Excel ne déduit pas cette règle de vos descriptions. Ici, tous les attributs ont le même poids : une règle qui précise la ressource et la finition a la même précision qu’une règle qui précise la ressource et la taille. Si les deux correspondent et ont le score le plus élevé, la formule signale une égalité.

Un simple XLOOKUP ne peut pas déterminer cette priorité à lui seul. Microsoft précise qu’il renvoie par défaut le premier élément correspondant. Inverser l’ordre de recherche change le point de départ ; cela ne classe pas les règles selon leur précision. La formule calcule donc d’abord la ligne gagnante, puis demande à XLOOKUP sa quantité. Fonction XLOOKUP.

Configurer les règles pour les ressources artistiques fictives

Dans une feuille de calcul vide, saisissez ces en-têtes et ces valeurs dans A1:D6. Les cellules vides du tableau ci-dessous doivent rester vides dans Excel. Tous les noms et toutes les quantités sont donnés à titre d’exemple.

Ligne de la feuille A : Ressource B : Finition C : Taille D : Quantité
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

La règle de la ligne 2 est une valeur par défaut intentionnelle : elle ne précise aucun attribut et sa précision est de zéro. La ligne 3 précise un attribut, la ligne 4 en précise deux et la ligne 5 en précise trois. Ces quantités sont des valeurs attribuées alternatives ; la recherche renvoie une seule valeur. Elle n’additionne pas la quantité par défaut et celles des exceptions.

Saisissez les en-têtes Asset, Finish, Size et Result dans H1:K1. Saisissez ensuite Tile dans H2, Matte dans I2 et Small dans J2. Gardez les trois attributs de saisie renseignés. Dans cette configuration, les cellules vides servent de conditions sans restriction uniquement dans le tableau des règles ; une saisie incomplète renvoie un message.

Utilisez des quantités numériques, y compris zéro sous forme numérique le cas échéant. Limitez les plages aux lignes contenant réellement des règles. Les étendre aux lignes vides inutilisées ajouterait des règles supplémentaires entièrement vides selon cette méthode de correspondance.

Saisir la formule qui sélectionne la règle gagnante

Sélectionnez K2, saisissez cette formule complète, puis appuyez sur Return. Elle utilise le tableau des règles et les cellules de saisie définis ci-dessus. Les calculs de tableaux dynamiques sont effectués dans la formule ; le résultat final occupe une seule cellule. La documentation de Microsoft indique que les formules de tableaux dynamiques se saisissent normalement, sans les raccourcis clavier utilisés pour les anciennes formules matricielles. Formules de tableaux dynamiques et comportement de propagation des tableaux.

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

Avec les valeurs saisies dans l’exemple, K2 doit renvoyer 7. Quatre règles sont admissibles, mais seule celle de la ligne 5 précise les trois attributs.

Fonctionnement de l’admissibilité et de la précision

LET attribue des noms locaux aux plages et aux résultats intermédiaires, ce qui facilite l’examen du processus de sélection. Microsoft précise que ces noms sont limités à la portée de la formule. Fonction LET.

Pour chaque attribut, l’expression d’admissibilité vérifie si la cellule de la règle est vide ou correspond à la valeur saisie. L’addition combine ces deux possibilités, et la comparaison convertit le résultat en test logique. La multiplication des trois tests d’attribut exige que toutes les conditions soient satisfaites. Dans ses exemples de FILTER, Microsoft utilise le même modèle de multiplication pour combiner des critères de tableau selon la logique ET. Fonction FILTER.

specificity compte les champs renseignés dans la règle. Les doubles opérateurs unaire convertissent chaque test de cellule non vide en nombre, produisant des scores de zéro à trois. IF attribue aux lignes non admissibles un score de moins un pour le classement ; MAX trouve ensuite le score admissible le plus élevé. La valeur moins un maintient les lignes non admissibles en dessous même de la valeur par défaut sans restriction, dont le score est zéro. Fonction IF, Fonction MAX.

winner marque uniquement les lignes admissibles ayant ce score maximal. La formule vérifie qu’il existe une correspondance et qu’il n’y a pas plusieurs gagnants avant de renvoyer une quantité. La vérification numérique finale rejette volontairement une quantité vide ou un nombre stocké sous forme de texte. Microsoft précise que ISNUMBER ne convertit pas les nombres textuels en valeurs numériques. Le zéro numérique reste un résultat valide. Fonctions IS.

Vérifier les résultats avant d’étendre la feuille de calcul

Remplacez H2:J2 par chacune des combinaisons ci-dessous. Il s’agit de résultats attendus calculés à partir des règles de l’exemple, et non d’observations mesurées de l’inventaire.

Ressource Finition Taille Résultat attendu Critère gagnant
Tile Matte Small 7 Trois champs précisés
Tile Matte Large 12 Ressource et finition
Tile Gloss Small 18 Ressource uniquement
Disc Gloss Large 10 Ressource et taille
Arc Matte Small 30 Valeur par défaut sans restriction

Ensuite, remplacez temporairement les valeurs de la ligne 6 par la ressource Tile, la finition Matte, la taille Small et la quantité 9. Une fois les valeurs d’origine rétablies, le résultat doit devenir Égalité : vérifier les règles : deux règles précisent désormais les trois attributs correspondants. Même si les quantités étaient identiques, il y aurait toujours égalité, car la sélection de la règle est ambiguë. Rétablissez ensuite les valeurs d’origine de la ligne 6.

Pour tester le cas sans correspondance, modifiez temporairement les quatre plages de règles de la formule afin qu’elles commencent à la ligne 3, en excluant la valeur par défaut. La saisie Arc, Matte, Small doit alors renvoyer Aucune règle correspondante. Une fois le test terminé, rétablissez les plages à partir de la ligne 2. Pour tester la vérification de la quantité, effacez D5 tout en conservant les valeurs d’origine ; le résultat attendu est Vérifier la quantité de la règle. Rétablissez ensuite 7.

Examiner les règles qui se chevauchent et résoudre les égalités

Pour afficher toutes les règles admissibles pour la saisie actuelle, entrez cette formule distincte dans M2. Laissez d’abord M2:P6 vide. Le résultat affiche les lignes de règles correspondantes dans leur ordre d’origine ; il ne les classe pas.

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER renvoie les lignes sélectionnées par son tableau booléen d’inclusion, et son dernier argument fournit un message lorsqu’aucune ne remplit les critères. Les cellules sources vides peuvent s’afficher sous forme de zéros dans ce résultat d’inspection ; continuez à considérer les cellules vides du tableau de règles d’origine comme des conditions sans restriction. Fonction FILTER.

La formule d’inspection se propage dans les cellules voisines. Placez-la en dehors d’un tableau Excel et gardez la zone de sortie libre. Microsoft précise que les formules de tableaux propagés ne sont pas prises en charge dans les tableaux et que des cellules occupées dans la zone de sortie peuvent provoquer l’erreur #SPILL!. Formules de tableaux dynamiques et comportement de propagation des tableaux.

Pour résoudre une égalité, corrigez un doublon, restreignez une règle qui se chevauche ou définissez une politique de priorité supplémentaire. Par exemple, les règles ressource plus finition et ressource plus taille obtiennent toutes deux un score de deux ici. Le classeur ne peut pas décider quel attribut est le plus important tant que vous n’avez pas encodé cette préférence. Le déplacement des lignes ne doit pas déterminer le résultat avec la politique utilisée dans cet article.

Étendre la recherche sans en changer le sens

Pour une autre ressource, saisissez ses trois attributs dans H3:J3 et copiez K2 dans K3. Les références aux cellules de saisie se décalent avec la formule, tandis que les signes dollar maintiennent les plages de règles fixes. Lorsque vous ajoutez des règles après la ligne 6, étendez de manière cohérente les quatre plages de la formule principale et les deux dimensions de la formule d’inspection.

Gardez chaque attribut dans sa propre colonne et utilisez des noms cohérents, sans espaces superflus. Cette formule compare les valeurs complètes des champs ; elle ne recherche pas des fragments dans une description de ressource combinée. La vérification de la quantité confirme que la valeur choisie est numérique ; appliquez donc aux données sources toute autre exigence relative à l’inventaire, comme des quantités entières non négatives.

La recherche obtenue suit une séquence de décision claire : exiger une saisie complète, identifier les règles admissibles, trouver la précision la plus élevée, rejeter les égalités et renvoyer la quantité numérique sélectionnée. Cette séquence permet d’utiliser ensemble des valeurs par défaut générales et des exceptions détaillées, tout en laissant visibles les choix non résolus.

Sources