Utilisez FILTER pour renvoyer toutes les quantités associées à un libellé d’actif graphique dans Excel pour Mac. Le tableau obtenu peut se déverser dans les cellules de la feuille de calcul, alimenter un autre calcul ou être additionné sans perdre les entrées correspondantes répétées.

Ce guide utilise des libellés et des quantités synthétiques d’actifs graphiques. Il s’agit de sélectionner un libellé, de récupérer toutes ses quantités et de réutiliser ces nombres individuellement dans un calcul. Microsoft indique que FILTER renvoie un tableau selon les critères spécifiés. Fonction FILTER.

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

La documentation actuelle de Microsoft indique explicitement qu’Excel pour Microsoft 365 pour Mac, Excel 2024 pour Mac et Excel 2021 pour Mac prennent en charge FILTER. Utilisez l’une de ces éditions pour les formules ci-dessous. Fonction FILTER.

Pour identifier votre installation, ouvrez Excel et choisissez Excel → À propos d’Excel. Microsoft documente ce chemin de menu sur Mac, et son guide d’identification des versions explique que la boîte de dialogue À propos de l’application Mac affiche le numéro de version et le type de licence. Vérifiez aussi l’édition, en plus du numéro de build. Partager des documents avec d’autres versions d’Office pour Mac ; À propos d’Office : quelle version d’Office est-ce que j’utilise ?.

Excel 2019 pour Mac et Excel 2016 pour Mac ne figurent pas parmi les versions pour lesquelles la disponibilité de FILTER est documentée. Si vous utilisez l’une de ces éditions, la méthode prise en charge pour ce flux de travail particulier avec des tableaux dynamiques consiste à ouvrir le classeur dans l’une des éditions Mac répertoriées. Les instructions ci-dessous dépendent de ce prérequis ; elles ne constituent pas une solution de remplacement reposant sur les anciennes formules matricielles.

Configurer un petit exemple de quantités d’actifs

Dans une feuille de calcul vierge, saisissez les données suivantes dans A1:B7. La première ligne contient les en-têtes. Saisissez les quantités sous forme de nombres, y compris le zéro dans B6.

Ligne de la feuille de calcul Colonne A : Actif Colonne B : Quantité
1 Actif Quantité
2 Tile A 4
3 Arc B 7
4 Tile A 2
5 Dot C 5
6 Tile A 0
7 Tile A 4

Saisissez Actif sélectionné dans D1 et Tile A dans D2. Saisissez Quantités correspondantes dans F1. Laissez vides les cellules situées sous F2 pour que le résultat ait la place de s’étendre.

Cet exemple contient volontairement deux quantités de quatre et une quantité de zéro pour le même libellé. Un résultat correct doit conserver les quatre lignes correspondantes : 4, 2, 0 et 4. Supprimer les valeurs répétées ou exclure le zéro modifierait le résultat demandé.

Renvoyer toutes les quantités correspondantes avec FILTER

Sélectionnez F2, saisissez la formule suivante et appuyez sur Entrée :

=FILTER(B2:B7,A2:A7=D2)

La formule utilise B2:B7 comme tableau des quantités et A2:A7=D2 comme test d’inclusion. Ces plages couvrent les mêmes six lignes source, ce qui satisfait à l’exigence de Microsoft selon laquelle le tableau d’inclusion doit avoir une hauteur ou une largeur compatible. Fonction FILTER.

Avec les données de l’exemple, le résultat occupe F2:F5 :

Cellule Résultat
F2 4
F3 2
F4 0
F5 4

Saisissez la formule uniquement dans F2 ; Excel place automatiquement les résultats supplémentaires dans les cellules voisines. Microsoft appelle ce comportement le déversement. Pour modifier la formule ultérieurement, modifiez la cellule supérieure gauche du résultat, qui est F2 ici. Formules de tableau dynamique et comportement de déversement des tableaux.

Les deux occurrences de quatre correspondent à des lignes source distinctes. Conservez-les toutes les deux si le calcul suivant doit tenir compte de chaque occurrence de l’actif sélectionné.

Pourquoi XLOOKUP ne récupère pas ces quatre lignes

Microsoft décrit XLOOKUP comme renvoyant l’élément correspondant à la première correspondance trouvée. Cette fonction peut renvoyer plusieurs colonnes d’une ligne correspondante, mais cela ne permet pas d’énumérer toutes les lignes qui contiennent le même libellé. Fonction XLOOKUP.

Par exemple, la formule suivante ne renvoie que 4 dans l’exemple :

=XLOOKUP(D2,A2:A7,B2:B7)

Ce résultat convient lorsqu’une seule ligne correspondante fournit la réponse. Pour cette tâche, quatre lignes correspondantes distinctes fournissent des quantités ; utilisez donc le résultat FILTER.

Réutiliser le tableau dans d’autres calculs

Référencez le résultat complet avec F2#. Microsoft décrit le signe dièse comme l’opérateur de plage déversée : il désigne l’ensemble du tableau produit à partir de la cellule de départ. Cela évite de limiter une formule en aval au résultat actuel de quatre cellules. Consignes et exemples de formules matricielles.

Placez les formules en aval à côté du résultat plutôt que juste en dessous, afin de laisser de la place à d’autres quantités correspondantes. Les exemples suivants utilisent les colonnes H à J.

Additionner toutes les quantités correspondantes

Saisissez Quantité totale dans H1, puis saisissez cette formule dans H2 :

=SUM(F2#)

Le résultat illustratif est 10 : quatre plus deux plus zéro plus quatre. SUM accepte des plages et additionne leurs valeurs numériques. Fonction SUM.

Si vous n’avez besoin que du total, vous pouvez aussi le calculer directement dans une autre cellule vide :

=SUM(FILTER(B2:B7,A2:A7=D2))

Choisissez le tableau visible si vous souhaitez examiner ou réutiliser les quantités individuelles. Choisissez la formule imbriquée si la feuille de calcul ne nécessite que le total final.

Multiplier chaque quantité par un facteur commun

Supposons que l’exemple nécessite trois exemplaires de chaque quantité correspondante. Saisissez Exemplaires dans I1 et le nombre 3 dans I2. Saisissez Quantités ajustées dans J1, puis saisissez cette formule dans J2 :

=F2#*$I$2

Les résultats attendus dans J2:J5 sont 12, 6, 0 et 12. Microsoft documente les calculs matriciels qui multiplient des valeurs et renvoient plusieurs résultats, et son guide de calculatrice pour Mac indique que l’astérisque est l’opérateur de multiplication. Consignes et exemples de formules matricielles ; Utiliser Excel comme calculatrice dans Excel pour Mac.

Pour obtenir un seul total ajusté, saisissez ce qui suit dans H4 :

=SUM(F2#*$I$2)

Le total attendu est 30. Il s’agit de calculs illustratifs utilisant les données synthétiques fournies. Les opérations arithmétiques ont été vérifiées indépendamment ; les formules du classeur ont été comparées à la documentation Microsoft, sans être exécutées dans Excel pour Mac.

Déterminer le résultat lorsqu’aucune correspondance n’est trouvée

Remplacez D2 par Wave D, un libellé absent de l’exemple. La formule originale à deux arguments FILTER renvoie #CALC!, car Excel ne peut pas renvoyer un tableau vide. Microsoft indique que le troisième argument facultatif permet de fournir une valeur en l’absence de résultat. Comment corriger une erreur #CALC!.

Choisissez cette valeur en fonction des besoins du calcul suivant. Le tableau de décision ci-dessous s’applique à cette feuille de calcul :

Besoin Formule ou méthode Conséquence
Les correspondances manquantes doivent rester visibles pendant les calculs Conserver =FILTER(B2:B7,A2:A7=D2) L’absence de correspondance produit #CALC!, ce qui invite à vérifier la situation.
Le résultat est avant tout une liste lisible Utiliser =FILTER(B2:B7,A2:A7=D2,"No matches") En l’absence de correspondance, un message texte est renvoyé.
Un libellé absent doit contribuer à hauteur de zéro au total Utiliser =SUM(FILTER(B2:B7,A2:A7=D2,0)) En l’absence de correspondance, le total renvoyé est zéro.

Utiliser zéro comme valeur de remplacement constitue une règle de calcul délibérée. Cela ne signifie pas qu’une ligne source correspondante existe avec une quantité égale à zéro. Dans cet exemple, Tile A a une quantité réelle de zéro ; Wave D n’a aucune ligne source. Préservez cette distinction lorsque la présence d’un actif est importante.

Un message texte demande également de la prudence : Microsoft indique que SUM ignore le texte dans les cellules référencées. Par conséquent, additionner un résultat qui ne contient que Aucune correspondance peut produire zéro et masquer l’absence de lignes correspondantes. Utilisez l’approche qui affiche une erreur si les calculs en aval doivent s’interrompre lorsqu’un libellé est absent. Fonction SUM.

Rétablissez D2 à Tile A avant de poursuivre avec les exemples numériques d’origine.

Ajouter une deuxième condition uniquement si la tâche l’exige

Si une tâche ultérieure demande spécifiquement des quantités positives, utilisez à la fois le test du libellé et un test de quantité positive :

=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))

Pour l’exemple, cela renvoie 4, 2 et 4. Microsoft indique que la multiplication entre conditions constitue un test ET dans FILTER. Cette variante exclut volontairement le zéro correspondant ; conservez donc la formule d’origine si vous avez besoin de toutes les quantités correspondantes. Fonction FILTER.

Résoudre les problèmes de résultat et inclure les nouvelles lignes

Si la formule ne renvoie pas les quatre quantités attendues, vérifiez la feuille de calcul dans l’ordre suivant :

  1. Vérifiez l’espace de sortie. Une plage de résultat bloquée provoque #SPILL!. Déplacez les éléments nécessaires hors de la zone de déversement prévue avant de réessayer.
  2. Vérifiez l’emplacement de la formule. Les formules de tableau déversé doivent être placées dans la grille de la feuille de calcul, hors d’un tableau Excel.
  3. Vérifiez les dépendances du classeur. Microsoft ne prend en charge les tableaux dynamiques liés entre classeurs que lorsque les deux classeurs sont ouverts ; une actualisation après la fermeture du fichier source peut produire #REF!.

Ces limites sont décrites dans Formules de tableau dynamique et comportement de déversement des tableaux.

Ensuite, vérifiez les libellés et les quantités sources. Comparez D2 aux cellules d’actif prévues et vérifiez que les quantités ont été saisies sous forme numérique. Un total plausible ne suffit pas à vérifier le résultat, car SUM peut ignorer les valeurs textuelles. Fonction SUM.

Les formules de l’exemple font référence aux lignes source 2 à 7. Si vous ajoutez des données à la ligne 8, modifiez les deux plages source pour l’inclure. Par exemple, après avoir saisi Tile A dans A8 et la quantité numérique 3 dans B8, remplacez la formule dans F2 par :

=FILTER(B2:B8,A2:A8=D2)

Le tableau attendu devient 4, 2, 0, 4, 3, et le total devient 13. La référence F2# suit le résultat étendu, mais elle ne peut pas inclure une ligne omise dans les plages source d’origine.

Pour les listes qui s’allongent fréquemment, Microsoft recommande d’utiliser un tableau Excel avec des références structurées, qui s’ajustent lorsque des lignes sont ajoutées ou supprimées. Placez le résultat déversé en dehors de ce tableau. Formules de tableau dynamique et comportement de déversement des tableaux.

Pour finir, vérifiez les résultats individuels avant de vous fier au total : pour les six lignes source d’origine, sélectionner Tile A doit renvoyer quatre quantités, dont les deux quatre et le zéro. Une fois ce résultat vérifié, réutilisez F2# pour vos calculs et choisissez une règle explicite pour les cas sans correspondance, adaptée à l’objectif de la feuille de calcul.

Sources