Utilisez IFNA autour d'une recherche déversée pour combler ses résultats #N/A à partir d'une seconde table de référence. Cet exemple Excel pour Mac fait correspondre les étiquettes d'actifs artistiques, conserve les quantités déjà trouvées dans la table primaire et marque clairement les étiquettes manquantes des deux tables.

Quelles versions d'Excel pour Mac prennent en charge cette approche ?

Utilisez Excel pour Microsoft 365 pour Mac, Excel 2024 pour Mac ou Excel 2021 pour Mac. Microsoft répertorie ces éditions pour XLOOKUP et IFNA. Sa documentation XLOOKUP exclut explicitement Excel 2016 et Excel 2019, bien que ces éditions apparaissent dans la liste d'applicabilité plus large de la page. Les formules ci-dessous ciblent les éditions Mac prises en charge. Fonction XLOOKUP, Fonction SI.NA

La tâche est spécifique : vous avez une colonne d'étiquettes d'actifs demandées et vous voulez une quantité par étiquette. La référence primaire a la priorité ; la seconde référence fournit des quantités uniquement là où la recherche primaire produit #N/A. Toutes les étiquettes et quantités ci-dessous sont synthétiques.

Configurer deux tables de référence et une liste d'actifs demandés

Commencez sur une feuille de calcul vierge dans un classeur. Utilisez les emplacements de cellules ci-dessous pour que les formules fonctionnent sans changer leurs références. Ces petites tables de référence sont des plages de feuille de calcul ordinaires ; vous n'avez pas besoin de les convertir en objets Tableau Excel.

Entrez la référence primaire dans D1:E4 :

Cellule dans la colonne D Étiquette d'actif Cellule dans la colonne E Quantité
D1 Actif E1 Quantité
D2 Contour de feuille E2 8
D3 Texture de points E3 0
D4 Motif de carreau E4 12

Entrez la seconde référence dans G1:H5 :

Cellule dans la colonne G Étiquette d'actif Cellule dans la colonne H Quantité
G1 Actif H1 Quantité
G2 Texture de points H2 9
G3 Esquisse de courbe H3 5
G4 Texture de grain H4 3
G5 Contour de feuille H5 10

Entrez Actif demandé dans J1, puis placez ces cinq étiquettes dans J2:J6, une par cellule et dans cet ordre :

  1. Contour de feuille
  2. Texture de points
  3. Esquisse de courbe
  4. Texture de grain
  5. Motif d'arc

Gardez les quantités numériques et remplissez chaque cellule de quantité dans les deux références. Pour cet exemple, zéro signifie une quantité enregistrée de zéro. Cela ne signifie pas que l'actif est manquant.

Créer la liste déversée des étiquettes

Entrez les en-têtes Actif, Quantité primaire et Quantité complétée dans A1, B1 et C1. Laissez A2:C20 vide, puis entrez cette formule dans A2 :

=FILTER(J2:J20,J2:J20<>"","")

Cela filtre les cellules vides dans la liste demandée. Avec les données fournies, il déverse les cinq étiquettes d'actifs dans A2:A6. Microsoft documente FILTER pour les trois éditions Mac répertoriées ci-dessus et explique que son argument final fournit un résultat lorsque aucune ligne ne remplit la condition. Ici, ce résultat est une chaîne vide. Fonction FILTRE

Les formules ultérieures utilisent A2# pour référencer l'ensemble de ce déversement. L'opérateur # suit la taille du déversement, évitant ainsi de coder en dur le point de terminaison actuel comme A6. Microsoft décrit ce comportement dans sa documentation sur l'opérateur de plage déversée.

Combler les quantités manquantes à partir de la seconde référence

Tout d'abord, créez la recherche primaire dans B2 :

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

Cela recherche la colonne d'actifs primaire et renvoie les quantités correspondantes. XLOOKUP utilise la correspondance exacte par défaut et renvoie #N/A lorsqu'aucune correspondance n'existe et qu'aucun résultat alternatif n'est fourni. Fonction RECHERCHEX

Pour l'exemple, B2:B6 doit contenir 8, 0, #N/A, #N/A et #N/A. Conservez ces erreurs de non-correspondance pour l'étape suivante.

Entrez maintenant la formule de complétion dans C2 :

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA examine le déversement primaire. Aux positions contenant #N/A, la formule fournit le résultat correspondant de la seconde recherche. Les autres résultats primaires passent inchangés. Microsoft documente spécifiquement que IFNA renvoie un tableau de résultats lorsque son premier argument est une formule matricielle. Fonction SI.NA

Le résultat attendu dans la feuille de calcul est :

Actif dans la colonne A Quantité primaire dans la colonne B Quantité complétée dans la colonne C
Contour de feuille 8 8
Texture de points 0 0
Esquisse de courbe #N/A 5
Texture de grain #N/A 3
Motif d'arc #N/A Non répertorié

Lisez ces lignes comme un test de priorité. Contour de feuille reste 8 même si la seconde référence contient 10. Texture de points reste 0 même si la seconde référence contient 9. Esquisse de courbe et Texture de grain reçoivent leurs quantités secondaires. Motif d'arc reste visiblement non résolu car aucune des deux références ne le contient.

Les deux tableaux doivent décrire les mêmes actifs demandés dans le même ordre. Dans cette configuration, les deux proviennent de A2#. Si vous adaptez un classeur existant, ne combinez pas un résultat primaire trié d'une certaine manière avec une recherche de secours basée sur une liste d'étiquettes ordonnée différemment.

Utiliser une seule formule lorsque vous n'avez pas besoin de la colonne intermédiaire

Pour garder la recherche primaire et la complétion ensemble, remplacez la formule dans C2 par cette alternative complète :

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

Cela utilise les mêmes données d'exemple et devrait produire les mêmes cinq quantités complétées. La colonne B n'est plus requise par le calcul. Conserver la version en deux étapes lors de la configuration du classeur facilite la visualisation de la référence qui fournit chaque résultat.

Décider si un résultat est réellement manquant

Avant d'adapter la formule, examinez ce que renvoie la première recherche. Une étiquette manquante, un résultat apparemment vide et une quantité de zéro nécessitent des décisions différentes.

Résultat primaire Ce que fait cette formule de complétion Ce qu'il faut vérifier
Quantité numérique, y compris 0 Le conserve Confirmez qu'il s'agit de la quantité primaire prévue.
#N/A Utilise la recherche secondaire Confirmez que l'erreur représente une étiquette absente.
Chaîne vide, "" Le conserve Vérifiez si la formule primaire masque les non-correspondances.
Une autre erreur Le laisse visible Réparez la formule ou la référence sous-jacente.

Par exemple, si votre recherche primaire existante utilise "" comme argument if_not_found, IFNA ne peut pas reconnaître cette chaîne vide comme #N/A. Supprimez cet argument de la recherche primaire, comme indiqué dans B2, ou utilisez la formule combinée ci-dessus. La logique de complétion dépend de la conservation d'un résultat de non-correspondance reconnaissable. Fonction RECHERCHEX, Fonction SI.NA

Inspectez également les quantités de référence elles-mêmes. IFNA répond à une valeur d'erreur ; il ne teste pas si une cellule de quantité a été remplie. Si un enregistrement primaire correspondant a une quantité inachevée, complétez cet enregistrement ou définissez une règle distincte pour les quantités inachevées avant d'appliquer ce flux de travail.

Il y a une distinction supplémentaire : une erreur #N/A existante dans les données de quantité primaire déclenchera également ce secours. Pour cet exemple, le prérequis est des colonnes de quantités numériques propres, avec #N/A n'apparaissant que suite à une recherche infructueuse. Vérifiez cette condition avant de considérer chaque remplacement comme une correspondance d'actif manquante.

Vérifier la priorité et maintenir le déversement fonctionnel

Utilisez les cinq lignes de l'exemple comme un petit test d'acceptation avant de remplacer par vos propres étiquettes d'actifs. Ensemble, elles testent une correspondance primaire, un zéro valide, deux correspondances secondaires et une étiquette absente des deux références.

Pour un contrôle supplémentaire, modifiez temporairement la quantité secondaire pour Contour de feuille de 10 à 14. La quantité complétée doit rester 8. Ensuite, modifiez la quantité secondaire d'Esquisse de courbe de 5 à 6 ; sa quantité complétée devrait devenir 6. Rétablissez les valeurs d'origine par la suite. Ce sont des contrôles illustratifs pour votre classeur, pas des résultats de test Excel rapportés.

Gardez un enregistrement prévu par étiquette d'actif dans chaque référence. XLOOKUP recherche normalement à partir du premier élément et renvoie la première correspondance, donc les étiquettes répétées ne devraient pas être censées combiner les quantités. Résolvez la signification des étiquettes en double avant de vous fier à cette conception de recherche. Fonction RECHERCHEX

Corriger une plage de sortie bloquée

Entrez chaque formule déversée uniquement dans sa cellule de départ. Excel remplit automatiquement les cellules voisines, et seule la cellule en haut à gauche d'un déversement est modifiable. Placez les formules en dehors des objets Tableau Excel ; Microsoft prend en charge les tableaux comme données sources mais ne prend pas en charge les formules déversées à l'intérieur. Formules de tableau dynamique et comportement des tableaux déversés

Si vous voyez #SPILL!, sélectionnez la cellule de formule pour révéler la limite de sortie prévue. Utilisez l'option Sélectionner les cellules obstructives de l'alerte d'erreur, si disponible, et déplacez les entrées existantes qui bloquent le résultat. Les cellules fusionnées peuvent également empêcher le déversement ; déplacez la sortie ou défusionnez les cellules affectées. Comment corriger une erreur #SPILL!

Étendre les entrées délibérément

La formule de liste demandée examine J2:J20. Les étiquettes ajoutées dans cette plage sont incluses automatiquement ; les étiquettes entrées en dessous nécessitent l'extension de la plage de la formule. De même, les recherches de référence utilisent des plages fixes se terminant aux lignes 4 et 5. Étendez chaque plage d'actifs et sa plage de quantité correspondante ensemble lors de l'ajout d'enregistrements.

Pour une feuille de calcul plus grande, les Tableaux Excel avec références structurées peuvent faire en sorte que les plages sources s'ajustent lorsque des lignes sont ajoutées ou supprimées. Gardez les formules de sortie dans la grille de la feuille de calcul en dehors de ces tableaux. Formules de tableau dynamique et comportement des tableaux déversés

Gardez les entrées de cet exemple dans le même classeur. Microsoft documente une prise en charge limitée des tableaux dynamiques entre classeurs : les tableaux dynamiques liés nécessitent que les deux classeurs restent ouverts, et l'actualisation après la fermeture de la source peut produire #REF!. Formules de tableau dynamique et comportement des tableaux déversés

Une fois que les lignes attendues correspondent, utilisez la colonne complétée comme résultat quantité-actif. Conservez la règle de priorité primaire, préservez les zéros significatifs et laissez les étiquettes absentes des deux références marquées Non répertorié afin de pouvoir compléter les données de référence délibérément.

Sources