Dans Microsoft 365 pour Mac ou Excel 2024 pour Mac, combinez CHOOSECOLS, TOCOL, HSTACK et FILTER pour transformer des libellés d’éléments graphiques et leurs quantités répétés en lignes d’inventaire. La formule présentée ci-dessous préserve l’alignement des paires, les quantités nulles et les entrées incomplètes.

Ce guide s’adresse aux utilisateurs de la version de bureau pour Mac dont la feuille de calcul répète une colonne d’attribut suivie de sa colonne de quantité. Ici, chaque attribut est le libellé d’un élément graphique, comme Carreau rond. Le but est de produire un inventaire à deux colonnes, puis de rechercher les quantités sans les dissocier de leurs libellés.

Quelles versions d’Excel pour Mac prennent en charge cette formule ?

Microsoft indique explicitement que les trois fonctions de réorganisation sont disponibles dans Excel pour Microsoft 365 pour Mac et Excel 2024 pour Mac : CHOOSECOLS, TOCOL et HSTACK. Utilisez l’une de ces éditions pour la formule principale.

Excel 2021 pour Mac inclut les tableaux dynamiques, FILTER, LET et XLOOKUP, mais cela ne confirme pas la prise en charge des nouvelles fonctions de réorganisation. La documentation des fonctionnalités d’Excel 2021 pour Mac de Microsoft répertorie les fonctionnalités de tableau et de recherche disponibles ; les pages consacrées aux trois fonctions ci-dessus ne mentionnent pas Excel 2021.

Vérifiez votre édition et votre version dans la fenêtre À propos. Le guide des versions d’Office de Microsoft explique que la fenêtre sur Mac indique à la fois la version et le type de licence. Si votre édition ne dispose pas des fonctions de réorganisation, utilisez Microsoft 365 pour Mac ou Excel 2024 pour Mac pour la méthode automatique, ou suivez la méthode manuelle décrite ci-dessous.

Préparer un exemple synthétique complet

Dans une feuille vierge, saisissez ces données dans A1:F4. Laissez réellement vides les deux cellules du milieu de la ligne 3. Tous les libellés et toutes les quantités sont donnés à titre d’exemple.

Attribut 1 Quantité 1 Attribut 2 Quantité 2 Attribut 3 Quantité 3
Carreau rond 4 Texture à lignes 2 Motif à pois 0
Motif en arc 3 Texture quadrillée 5
Carreau rond 7 Motif ondulé 6 Carreau carré 1

Chaque paire de cellules adjacentes représente une entrée de l’inventaire. Le résultat attendu reprend d’abord les données de chaque ligne source de gauche à droite, puis passe à la ligne suivante. Carreau rond apparaît deux fois, car il s’agit d’entrées distinctes ; regrouper les libellés répétés serait une tâche supplémentaire.

Cette méthode suppose que chaque libellé est immédiatement suivi de sa quantité et que toutes les colonnes sélectionnées respectent ce motif répétitif. Si votre feuille contient aussi un nom de collection ou un autre champ qui doit accompagner chaque entrée, ce résultat à deux colonnes ne le conservera pas. Déterminez les champs nécessaires à l’inventaire avant d’appliquer la formule.

Transformer les paires répétées en lignes d’inventaire

Saisissez Élément dans H1 et Quantité dans I1. Laissez vide la zone de résultat sous ces en-têtes. Collez cette formule complète dans H2, puis appuyez sur Retour. Les formules de tableau dynamique sont saisies une seule fois et se déploient dans les cellules voisines, comme l’explique le guide des formules matricielles de Microsoft.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

Le résultat attendu occupe H2:I9 :

Élément Quantité
Carreau rond 4
Texture à lignes 2
Motif à pois 0
Motif en arc 3
Texture quadrillée 5
Carreau rond 7
Motif ondulé 6
Carreau carré 1

L’ordre des paires de l’exemple, les huit entrées obtenues, la conservation de la valeur zéro et la quantité totale de 28 ont été vérifiés à l’aide d’un petit calcul Python déterministe. La formule Excel a été comparée à la syntaxe des fonctions documentée par Microsoft ; elle n’a pas été exécutée dans Excel.

Comment la formule préserve l’alignement des paires

LET attribue des noms aux résultats intermédiaires afin que la formule puisse les réutiliser. Les noms source, clean, labels, quantities et keep sont définis dans cette formule ; vous n’avez pas besoin de créer de plages nommées. Microsoft décrit ce comportement dans sa documentation sur LET.

CHOOSECOLS sélectionne les colonnes de libellés 1, 3 et 5, ainsi que les colonnes de quantités 2, 4 et 6. Ces nombres correspondent aux positions dans la plage source sélectionnée. La documentation de CHOOSECOLS de Microsoft confirme que cette fonction renvoie les colonnes indiquées d’un tableau.

Les deux appels à TOCOL utilisent 0 pour conserver toutes les positions et FALSE pour parcourir les lignes de gauche à droite. Comme ils parcourent les positions correspondantes dans le même ordre, chaque libellé reste associé à sa quantité. Microsoft documente ces deux arguments dans la référence de TOCOL.

HSTACK place les deux colonnes obtenues côte à côte. Elles contiennent chacune neuf positions avant le filtrage ; leurs longueurs sont donc identiques. Microsoft explique que HSTACK ajoute des tableaux horizontalement et complète les tableaux plus courts avec des erreurs si leurs nombres de lignes diffèrent.

Enfin, keep sélectionne les positions contenant un libellé, une quantité ou les deux. FILTER supprime uniquement les paires complètement vides. Son dernier argument fournit un message lorsqu’il ne reste aucune entrée, conformément à la syntaxe documentée de FILTER par Microsoft.

Préserver les cellules vides et les zéros sans masquer les entrées incomplètes

Une quantité vide et une valeur numérique égale à zéro ne transmettent pas la même information. Zéro est un décompte saisi ; une cellule vide doit encore être interprétée. Les exemples de Microsoft pour TOCOL montrent que les cellules vides non traitées peuvent renvoyer zéro. L’étape clean utilise donc IF pour renvoyer un texte vide lorsque les cellules source sont vides, avant la réorganisation. Microsoft documente les valeurs conditionnelles renvoyées dans sa référence de IF.

La formule conserve volontairement les paires incomplètes pour qu’elles restent visibles. Effectuez les vérifications suivantes avant de vous fier au résultat :

Paire source Comportement du résultat Action
Libellé et quantité numérique Conserve les deux Vérifiez que la quantité est correcte
Libellé et zéro Conserve zéro Gardez cette valeur si zéro est un décompte intentionnel
Les deux cellules sont vides Supprime la paire Aucune correction nécessaire
Libellé et quantité vide Conserve le libellé et la quantité vide Complétez la quantité
Libellé vide et quantité Conserve le libellé vide et la quantité Complétez le libellé

Évitez de supprimer indépendamment les cellules vides des tableaux de libellés et de quantités. Si un libellé manque à une position et une quantité à une autre, supprimer les cellules vides séparément peut décaler les tableaux et créer des associations erronées. L’élément clé de conception de cette formule consiste à conserver les positions jusqu’à leur assemblage.

Une cellule qui semble vide mais contient des espaces n’est pas considérée comme vide par cette formule. Corrigez ces entrées dans la source. De même, la formule conserve les quantités saisies sous forme de texte au lieu de les convertir automatiquement. Vérifiez ces valeurs avant de calculer les totaux.

Rechercher une entrée ou renvoyer toutes les entrées correspondantes

Saisissez Carreau rond dans K2. Pour récupérer sa première quantité, saisissez cette formule dans L2 :

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2# désigne l’ensemble du résultat déployé qui commence à H2 ; la recherche s’adapte donc à ses changements de taille. Microsoft explique cette notation dans la documentation sur l’opérateur de plage déversée.

Le résultat est 4. Le mode de correspondance explicite 0 demande une correspondance exacte, et la recherche par défaut renvoie la première entrée correspondante. La référence de XLOOKUP de Microsoft documente ces deux comportements. Elle confirme également que XLOOKUP n’est pas disponible dans Excel 2016 et Excel 2019.

Si les libellés répétés ont un sens, une recherche de la première correspondance ne suffit pas. Pour renvoyer toutes les entrées Carreau rond, placez cette formule complète de remplacement dans N2 et laissez de la place dans les colonnes N:O :

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

Elle renvoie deux lignes : Carreau rond avec 4 et Carreau rond avec 7. FILTER renvoie les lignes qui répondent à la condition indiquée, comme le décrit la référence de FILTER de Microsoft. Choisissez la formule de première correspondance lorsqu’une seule entrée suffit ; utilisez le résultat filtré lorsque vous devez examiner toutes les occurrences.

Vérifier le résultat et adapter la plage source

Pour l’exemple, vérifiez que le résultat compte huit lignes et que Motif à pois a toujours une quantité de zéro. Comparez ensuite le total des quantités source à celui des quantités réorganisées. Saisissez ces formules dans deux cellules libres :

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Les deux doivent renvoyer 28. La documentation de SUM de Microsoft permet d’additionner plusieurs plages et explique que les valeurs textuelles sont ignorées. Des totaux identiques ne prouvent donc pas que chaque quantité est numérique et ne permettent pas de détecter des quantités associées aux mauvais libellés. Vérifiez également les paires libellé-quantité.

Pour inclure davantage de lignes source, remplacez A2:F4 dans la formule principale par la plage réelle, par exemple A2:F100. Les lignes situées en dehors de cette référence ne sont pas incluses. Pour quatre paires répétées, utilisez une source de huit colonnes, par exemple A2:H100, et remplacez les positions des libellés par 1,3,5,7 et celles des quantités par 2,4,6,8.

Lorsque vous étendez la source à huit colonnes, déplacez le résultat vers une zone libre, par exemple J2, et mettez à jour les références aux résultats déversés en J2#. Gardez la source et le résultat séparés. Une position de colonne non valide provoque une erreur, comme l’explique la documentation sur les erreurs de CHOOSECOLS.

Résoudre un résultat bloqué ou utiliser la méthode manuelle d’empilement

Si la formule principale renvoie #SPILL!, sélectionnez la cellule de formule et inspectez la zone de résultat prévue. Déplacez les entrées existantes hors de cette zone et vérifiez qu’il n’y a pas de cellules fusionnées. Placez la formule en dehors de tout tableau Excel : Microsoft indique dans son guide de résolution des erreurs #SPILL! que les formules de tableau déversées ne peuvent pas se déployer dans les tableaux ni dans les cellules fusionnées.

Si la source contient une erreur, corrigez cette entrée avant d’utiliser l’inventaire. La formule ne masque pas les erreurs ; Microsoft précise qu’une erreur dans le tableau d’inclusion de FILTER entraîne une erreur de la fonction. Envelopper toute la transformation dans une fonction de suppression des erreurs pourrait masquer une entrée à corriger.

Dans une petite feuille de calcul sous Excel 2021 pour Mac, vous pouvez obtenir manuellement la même structure à deux colonnes : copiez A2:B4 vers H2:I4, C2:D4 vers H5:I7, puis E2:F4 vers H8:I10. Supprimez uniquement les lignes dont les deux cellules sont vides. Cette méthode empile les entrées par bloc de paires, et non dans l’ordre des lignes source utilisé par la formule principale. Elle doit aussi être répétée lorsque la source change.

Une fois cette préparation manuelle terminée, Excel 2021 pour Mac peut utiliser sa fonctionnalité documentée XLOOKUP avec des plages fixes :

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

Un inventaire bien préparé conserve toutes les paires significatives, met en évidence les entrées incomplètes et utilise une méthode de recherche adaptée aux libellés répétés. Vérifiez ces propriétés avec l’exemple, puis élargissez la plage source pour qu’elle corresponde à votre feuille de calcul.

Sources