Excel Dynamic Arrays, Fonctions and Formules

La mise à jour du moteur informatique révolutionnaire d'Excel 365 facilite les formules Array Formules, plus une compétence dédiée pour les utilisateurs avancés. Ce tutoriel explique le concept de tableaux dynamiques Excel et montre comment les utiliser pour améliorer l'efficacité des feuilles de travail et simplifier les paramètres.

Les formules des tableaux Excel ont toujours été considérées comme le brevet d'experts et d'experts en formule. La phrase "peut être résolue avec des formules de tableau" amène souvent de nombreux utilisateurs à réagir immédiatement: "Y a-t-il un autre moyen?"

L'introduction de tableaux dynamiques est un changement tant attendu et très bienvenu. Parce qu'ils sont capables de gérer plusieurs valeurs de manière simple, sans aucune astuce et astuces, chaque utilisateur Excel peut comprendre et créer facilement des formules de tableau dynamique.

- Tableau dynamique Excel

• Disponibilité dynamique du tableau
• Fonctions de tableau dynamique
• Exemple de formules de tableau dynamique
• Zone de débordement - une formule, plusieurs cellules
• Référence de la zone de débordement (symbole #)
• Intersection implicite et caractères @
• Avantages des tableaux dynamiques
• Limitations des tableaux dynamiques
• Tableaux dynamiques et formules traditionnelles de tableau CSE
• Compatibilité en arrière
• La formule de tableau dynamique Excel n'est pas valide

Excel Dynamic Array

Les tableaux dynamiques sont des tableaux redimensionnés qui calculent automatiquement et renvoient les valeurs à plusieurs cellules, toutes basées sur des formules entrées dans une seule cellule.

Microsoft Excel a subi de nombreux changements au cours des trente dernières années, mais une chose est cohérente: une formule, une cellule. Même avec des formules de tableau traditionnelles, vous devez saisir la formule dans chaque cellule où vous souhaitez afficher le résultat. Avec des tableaux dynamiques, cette règle ne s'applique plus. Désormais, toute formule qui renvoie la valeur du tableau débordera automatiquement dans les cellules adjacentes sans appuyer sur Ctrl Shift Entrer ou faire autre chose. En d'autres termes, la manipulation d'un tableau dynamique est aussi simple que de manipuler une seule cellule.

Utilisons un exemple très basique pour illustrer ce concept. Supposons que vous deviez multiplier deux ensembles de nombres, par exemple, calculer différents pourcentages.

Dans la version non dynamique d'Excel, la formule suivante ne s'applique qu'à la première cellule, sauf si vous l'entrez dans plusieurs cellules et appuyez sur Ctrl Shift Entrée pour le définir explicitement comme une formule de tableau:

=A3:A5*B2:D2

Maintenant, voyez ce qui se passe lorsque vous utilisez la même formule dans Excel 365. Vous le saisissez simplement dans une cellule (B3 dans ce cas), appuyez sur Entrée ... Toute la zone remplira immédiatement le résultat:

Le remplissage de plusieurs cellules avec une seule formule est appelé débordement , et la zone remplie des cellules est appelée débordement.

Il convient de noter que les mises à jour récentes sont plus qu'une nouvelle façon de gérer les tableaux Excel. En fait, il s'agit d'une modification percée de l'ensemble du moteur informatique. Avec des tableaux dynamiques, une série de nouvelles fonctions ont été ajoutées à la bibliothèque de fonctions Excel, et les fonctions existantes ont également commencé à s'exécuter plus rapidement et plus efficacement. En fin de compte, le nouveau tableau dynamique doit remplacer complètement les formules de tableau à l'ancienne entrées à l'aide du raccourci Entrée Ctrl Shift.

Excel Dynamic Array Disponibilité

Des tableaux dynamiques ont été lancés lors de la conférence Microsoft Ignite en 2018 et ont été publiés aux abonnés Office 365 en janvier 2020. Actuellement, ils sont disponibles dans les abonnements Microsoft 365 et Excel 2021.

Les versions suivantes prennent en charge les tableaux dynamiques:

• Excel 365 pour Windows
• Excel 365 pour Mac
• Excel 2021
• Excel 2021 pour Mac
• Excel pour iPad
• Excel pour iPhone
• Version de tablette Android Excel
• Version mobile Android d'Excel
• Excel sur le Web

Excel Dynamic Array Fonction

Dans le cadre de la nouvelle fonctionnalité, 6 nouvelles fonctions sont introduites dans Excel 365 qui peuvent traiter nativement les tableaux et les données de sortie dans les plages de cellules. La sortie est toujours dynamique - les résultats sont automatiquement mis à jour lorsque des modifications se produisent dans les données source. Par conséquent, le nom du groupe est - une fonction de tableau dynamique .

Ces nouvelles fonctions peuvent facilement faire face à de nombreuses tâches traditionnellement considérées comme difficiles à résoudre. Par exemple, ils peuvent supprimer des doublons, extraire et compter des valeurs uniques, filtrer les valeurs nulles, générer des entiers et des décimales aléatoires, trier dans l'ordre croissant ou descendant, et plus encore.

Ce qui suit est une brève introduction aux fonctions de chaque fonction et un lien vers un tutoriel approfondi:

• Unique - Extrait des éléments uniques de la gamme de cellules.
• Filtre - Filtre Données en fonction des critères que vous définissez.
• Trie - Trie la plage de cellules par la colonne spécifiée.
• SORTBY - Trie les gammes cellulaires par une autre zone ou tableau.
• Randarray - génère un tableau de nombres aléatoires.
• Séquence - Générez une liste de numéros de série.
• TextSplit - divisez la chaîne en colonnes ou / et lignes par le délimiteur spécifié.
• TOCOL - Convertissez un tableau ou une région en une seule colonne.
• Torow - convertit une région ou un tableau en une seule ligne.
• WrapCols - convertit les lignes ou les colonnes en un tableau bidimensionnel en fonction du nombre de valeurs spécifiées pour chaque ligne.
• Wraprows - Reshape la ligne ou la colonne dans un tableau bidimensionnel en fonction du nombre de valeurs spécifiées pour chaque colonne.
• Take - extrait un nombre spécifié de lignes ou de colonnes consécutives à partir du début ou de la fin du tableau.
• Drop - Supprime un certain nombre de lignes ou de colonnes d'un tableau.
• Extension - étend un tableau à un nombre spécifié de lignes et de colonnes.
• ChooseCols - Renvoie la colonne spécifiée du tableau.
• Chooserows - Extraire la ligne spécifiée du tableau.
• GroupBy - Groupes et agrégats les données par ligne en fonction des valeurs dans une ou plusieurs colonnes.
• PIVOTBY - Groupe les données par lignes et colonnes et agrégats valeurs associées.

De plus, il existe deux alternatives modernes aux fonctions Excel populaires qui n'appartiennent pas officiellement au groupe, mais peuvent profiter de tous les avantages des tableaux dynamiques:

• XLookup - est le successeur le plus puissant de Vlookup, Hlookup et Lookup, qui peut trouver et renvoyer plusieurs valeurs dans les colonnes et les lignes.
• XMatch - est le successeur le plus général de la fonction de fonction de correspondance, qui effectue des recherches verticales et horizontales et renvoie la position relative de l'élément spécifié.

Formule de tableau dynamique Excel

Dans les versions modernes d'Excel, le comportement dynamique du tableau a été profondément intégré et devient originaire de toutes les fonctions , même si ces fonctions n'ont pas été conçues à l'origine pour fonctionner avec des tableaux. En bref, pour toute formule qui renvoie plusieurs valeurs, Excel crée automatiquement une zone redimensionnée dans laquelle les résultats sont sortis. En raison de cette capacité, les fonctions existantes peuvent désormais jouer des rôles magiques!

L'exemple suivant montre l'application pratique des formules de tableau dynamique et l'impact des tableaux dynamiques sur les fonctions existantes.

Exemple 1. Nouvelle fonction de tableau dynamique

Cet exemple montre à quelle vitesse et facile une solution peut être obtenue en utilisant des fonctions de tableau dynamique Excel.

Pour extraire une liste de valeurs uniques d'une colonne, vous utilisez généralement une formule CSE complexe comme indiqué ci-dessous. Dans Dynamic Excel, vous n'avez besoin que d'une forme de base de formule unique:

=UNIQUE(B2:B10)

Vous entrez la formule dans n'importe quelle cellule vide et appuyez sur Entrée. Excel extrait immédiatement toutes les différentes valeurs de la liste et les diffuse à la plage de cellules à partir de la cellule où vous avez entré la formule (dans ce cas D2). Lorsque les données source changent, les résultats sont automatiquement recalculés et mis à jour.

Exemple 2. Combinant plusieurs fonctions de tableau dynamique dans une seule formule

S'il n'y a aucun moyen de terminer une tâche avec une fonction, vous pouvez relier plusieurs fonctions ensemble! Par exemple, pour filtrer les données en fonction des conditions et trier les résultats par ordre alphabétique, vous pouvez envelopper la fonction de tri autour du filtre comme suit:

=SORT(FILTER(A2:C13, B2:B13=F1, "No results"))

Lorsque A2: C13 est les données source, B2: B13 est la valeur à vérifier et F1 est la condition.

Exemple 3. Utilisation de nouvelles fonctions de tableau dynamique avec des fonctions existantes

Étant donné que le nouveau moteur informatique implémenté dans Excel 365 peut facilement convertir les formules traditionnelles en tableaux, rien ne vous empêche de regrouper de nouvelles et anciennes fonctions.

Par exemple, pour calculer le nombre de valeurs uniques dans une région, vous pouvez nicher la fonction unique de tableau dynamique dans l'ancienne fonction Counta:

=COUNTA(UNIQUE(B2:B10))

Exemple 4. Les fonctions existantes prennent en charge les tableaux dynamiques

Si vous fournissez une plage de cellules à la fonction de finition dans une ancienne version d'Excel (comme Excel 2016 ou Excel 2019), il renverra un seul résultat pour la première cellule:

=TRIM(A2:A6)

Dans Dynamic Excel, la même formule traite toutes les cellules et renvoie plusieurs résultats que suit:

Exemple 5. Formule Vlookup qui renvoie plusieurs valeurs

Comme nous le savons tous, la fonction VLookup est conçue pour renvoyer une seule valeur en fonction de l'indice de colonne que vous spécifiez. Cependant, dans Excel 365, vous pouvez fournir un ensemble de numéros de colonne pour retourner les correspondances de plusieurs colonnes:

=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)

Exemple 6. Formule de transposition simplifiée

Dans les versions antérieures d'Excel, la syntaxe de la fonction de transposition ne peut pas être sans erreur. Pour faire pivoter les données dans la feuille de calcul, vous devez calculer les colonnes et les lignes d'origine, sélectionnez le même nombre de cellules vides mais modifiez l'orientation (c'est une opération déroutante dans une grande feuille de calcul!), Tapez la formule de transposition dans la zone sélectionnée et appuyez sur Ctrl Shift Entrez pour terminer correctement. appel!

Dans Dynamic Excel, vous entrez simplement la formule dans la cellule la plus à gauche de la zone de sortie et appuyez sur Entrée:

=TRANSPOSE(A1:B6)

Faites-le!

Zone de débordement - une formule, plusieurs cellules

Une zone de débordement est une région de cellules contenant les valeurs renvoyées par une formule de tableau dynamique.

Lors de la sélection d'une cellule dans la zone de débordement, une bordure bleue semble montrer que tout ce qui lui est calculé est calculé par la formule dans la cellule supérieure gauche. Si vous supprimez la formule dans la première cellule, tous les résultats disparaîtront.

La zone de débordement est une excellente fonctionnalité qui facilite les utilisateurs d'Excel plus facilement. Auparavant, lors de l'utilisation de formules de tableau CSE, nous avons dû deviner combien de cellules nous voulions les copier. Maintenant, vous entrez simplement la formule dans la première cellule et laissez Excel gérer le reste.

Avis. Si d'autres données bloquent la zone de débordement, une erreur #spill se produit. Une fois les données de blocage supprimées, l'erreur disparaîtra.

Pour plus d'informations, voir la zone de débordement Excel.

Référence de la zone de débordement (symbole #)

Pour référencer toute la zone de débordement renvoyée par une formule de tableau dynamique, ajoutez un signe de livre ou un signe de livre (#) après l'adresse de la cellule dans le coin supérieur gauche de la zone.

Par exemple, pour savoir combien de nombres aléatoires sont générés par la formule Randarray dans A2, fournissez la référence de la zone de débordement à la fonction Counta:

=COUNTA(A2#)

Pour ajouter des valeurs dans la zone de débordement, utilisez:

=SUM(A2#)

indice:

• Pour référencer rapidement une zone de débordement, sélectionnez simplement toutes les cellules de la boîte bleue avec votre souris et Excel créera une référence de débordement pour vous.
• Contrairement aux références de zone régulière, les références de surface de débordement sont dynamiques et répondront automatiquement au redimensionnement de la zone. Pour plus de détails, voir les opérateurs de région de débordement.

Intersection implicite et caractères @

Dans Dynamic Array Excel, le langage de formule a un autre changement important - le caractère @ est introduit, appelé l'opérateur d'intersection implicite .

Dans Microsoft Excel, l'intersection implicite est un comportement de formule qui réduit plusieurs valeurs à une seule valeur. Dans Excel plus ancien, les cellules ne peuvent contenir que des valeurs uniques, c'est donc le comportement par défaut et aucun opérateur spécial n'est requis.

Dans la nouvelle version d'Excel, toutes les formules sont traitées par défaut comme des formules de tableau. Si vous ne souhaitez pas que le comportement du tableau soit utilisé dans une formule spécifique, vous pouvez utiliser l'opérateur d'intersection implicite. En d'autres termes, si vous voulez que la formule ne renvoie qu'une seule valeur, préfixez le nom de la fonction avec @ et il s'exécutera comme une formule sans réseau dans Excel traditionnel.

Pour voir comment cela fonctionne dans la pratique, consultez la capture d'écran ci-dessous.

En C2, il existe une formule de tableau dynamique qui déborde le résultat dans de nombreuses cellules:

=UNIQUE(A2:A9)

Dans E2, la fonction est préfixée avec le caractère @, qui appelle l'intersection implicite. En conséquence, seule la première valeur unique est renvoyée:

=@UNIQUE(A2:A9)

Pour plus d'informations, voir les intersections implicites dans Excel.

Avantages des tableaux dynamiques Excel

Il ne fait aucun doute que les tableaux dynamiques sont l'une des meilleures améliorations d'Excel au fil des ans. Comme toute nouvelle fonctionnalité, ils ont des avantages et des inconvénients. Heureusement, les avantages de la nouvelle formule Excel Dynamic Array sont écrasants!

Simple et puissant

Les tableaux dynamiques facilitent la création de formules plus puissantes. Voici quelques exemples:

• Extrait des valeurs uniques: formules traditionnelles | Fonctions de tableau dynamique
• Comptez les valeurs uniques et différentes valeurs: Formules traditionnelles | Fonctions de tableau dynamique
• Trier les colonnes alphabétiquement: formules traditionnelles | Fonctions de tableau dynamique

Support natif pour toutes les formules

Dans Dynamic Excel, vous n'avez pas à vous soucier des fonctions des tableaux de prise en charge et quelles fonctions ne le font pas. Si la formule peut renvoyer plusieurs valeurs, elle le fera par défaut. Cela s'applique également aux opérations arithmétiques et aux fonctions traditionnelles, comme le montre cet exemple.

Fonctions de tableau dynamique imbriquées

Pour résoudre une solution de tâche plus complexe, vous pouvez combiner librement de nouvelles fonctions de tableau dynamique Excel, ou les utiliser avec des fonctions anciennes, comme indiqué ici et ici.

Les citations relatives et absolues ne sont pas très importantes

En raison de l'approche "une formule, plusieurs valeurs", il n'est pas nécessaire de verrouiller la zone avec le symbole $ car techniquement la formule n'est que dans une seule cellule. Donc, dans la plupart des cas, il n'a pas vraiment d'importance d'utiliser des références cellulaires absolues, relatives ou mixtes (qui est toujours la source de confusion pour les débutants) - les formules de tableau dynamique produiront de toute façon le résultat correct!

Limitations des tableaux dynamiques

Le nouveau tableau dynamique est génial, mais comme toute nouvelle fonctionnalité, il y a quelques notes.

Les résultats ne peuvent pas être triés de la manière habituelle

La zone de débordement renvoyée par les formules de tableau dynamique ne peut pas être triée à l'aide de la fonction de tri d'Excel. Une telle tentative se traduira par une erreur " Impossible de modifier la partie du tableau ". Pour organiser les résultats de petits à grands ou de grands à petits, enveloppez la formule actuelle dans la fonction de tri. Par exemple, c'est ainsi que vous pouvez filtrer et trier en même temps.

Ne peut supprimer aucune valeur dans la zone de débordement

Pour la même raison, il est impossible de supprimer toute valeur dans la zone de débordement: une partie du tableau ne peut pas être modifiée. Ce comportement est attendu et logique. Il en va de même pour les formules traditionnelles du tableau CSE.

Non pris en charge par des formulaires Excel

Cette fonctionnalité (ou erreur?) Est assez inattendue. Les formules dynamiques du tableau ne fonctionnent pas dans des tables Excel, uniquement pour les zones régulières. Si vous essayez de convertir une zone de débordement en une table, Excel le fait. Cependant, vous ne verrez que #Spill! Erreur, pas de résultat.

Non disponible pour Excel Power Query

Les résultats des formules de tableau dynamique ne peuvent pas être chargés dans la requête de puissance. Par exemple, si vous essayez de fusionner deux ou plusieurs zones de débordement en utilisant la requête de puissance, cette opération ne fonctionne pas.

Tableaux dynamiques et formules traditionnelles de tableau CSE

Avec l'introduction de tableaux dynamiques, nous pouvons discuter de deux types d'Excel:

1. Dynamic Excel prend entièrement des tableaux, des fonctions et des formules dynamiques. Actuellement, Excel 365 et Excel 2021.
2. Excel traditionnel , également connu sous le nom de Excel non dynamique, ne prend en charge que Ctrl Shift Entrer les formules du tableau. Il est Excel 2019, Excel 2016, Excel 2013 et plus tôt.

Inutile de dire que les tableaux dynamiques surpassent les formules du tableau CSE dans tous les sens. Bien que les formules de tableau traditionnelles soient conservées pour des raisons de compatibilité, à partir de maintenant, il est recommandé d'utiliser de nouvelles formules de tableau.

Voici les différences les plus importantes:

• Les formules de tableau dynamique sont entrées dans une cellule et effectuées en utilisant la touche Entrée ordinaire. Pour compléter la formule du tableau à l'ancienne, vous devez appuyer sur Ctrl Shift Entrée.
• De nouvelles formules de tableau débordent automatiquement de plusieurs cellules. La formule CSE doit être copiée dans la plage de cellules pour retourner plusieurs résultats.
• La sortie d'une formule de tableau dynamique est automatiquement redimensionnée à mesure que les données de la zone source changent. Si la zone de retour est trop petite, la formule CSE tronque la sortie; Si la zone de retour est trop grande, une erreur sera retournée dans les cellules redondantes.
• Les formules de réseau dynamique peuvent être facilement modifiées dans une seule cellule. Pour modifier la formule CSE, vous devez sélectionner et modifier toute la zone.
• Il n'est pas possible de supprimer et d'insérer des lignes dans la zone de formule CSE - vous devez d'abord supprimer toutes les formules existantes. L'utilisation de tableaux dynamiques, l'insertion ou la suppression des lignes n'est pas un problème.

Compatibilité arrière: tableaux dynamiques dans Excel traditionnel

Lorsque vous ouvrez un classeur contenant des formules de tableau dynamique dans Excel plus ancien, il est automatiquement converti en une formule de tableau régulière enfermée en accolades {}. Lorsque vous ouvrez à nouveau la feuille de calcul dans la nouvelle version d'Excel, les accolades seront supprimées.

Dans Excel traditionnel, les nouvelles fonctions de tableau dynamique et les références de zone de débordement sont précédées de _xlfn pour indiquer que cette fonctionnalité n'est pas prise en charge. Le symbole de référence de la zone de débordement (#) sera remplacé par la fonction AnchorArray.

Par exemple, ce qui suit est de savoir comment la formule unique est affichée dans Excel 2013 :

La plupart des formules de tableau dynamiques (mais pas toutes!) Continueront d'afficher leurs résultats dans Excel traditionnel jusqu'à ce que vous apportiez à eux. La modification de la formule la cassera-t-elle immédiatement et affichera-t-elle un ou plusieurs # noms? Valeur d'erreur.

La formule de tableau dynamique Excel n'est pas valide

Selon la fonction, différentes erreurs peuvent se produire si une syntaxe incorrecte ou des paramètres non valides sont utilisés. Voici trois des erreurs les plus courantes que vous pouvez rencontrer lors de l'utilisation d'une formule de tableau dynamique.

#RÉPANDRE! Erreur

#Spill se produit lorsqu'un tableau dynamique renvoie plusieurs résultats, mais quelque chose bloque la zone de débordement! erreur.

Pour corriger cette erreur, vous effacez ou supprimez simplement toutes les cellules de la zone de débordement qui ne sont pas complètement vides. Pour trouver rapidement toutes les cellules qui sont sur le chemin, cliquez sur l'indicateur d'erreur et cliquez sur Sélectionner la cellule de blocage .

En plus des zones de débordement non vides, cette erreur peut être causée par d'autres raisons. Pour plus d'informations, voir:

• Erreur Excel #spill - Causes et solutions
• Comment corriger #spill dans vLookup, index Match, Sumif! erreur

#Ref! Erreur

En raison de la prise en charge limitée des références externes entre les classeurs, les tableaux dynamiques nécessitent que deux fichiers soient ouverts en même temps. Si le classeur source est fermé, #Ref sera affiché! erreur.

#NOM? Erreur

Si vous essayez d'utiliser des fonctions de tableau dynamique dans les anciennes versions d'Excel, #Name se produit? erreur. N'oubliez pas que les nouvelles fonctions ne sont disponibles que dans Excel 365 et Excel 2021.

Si cette erreur se produit dans une version prise en charge d'Excel, vérifiez le nom de la fonction dans la cellule en question. Peut-être qu'il a été incorrectement entré :)

C'est comment utiliser des tableaux dynamiques dans Excel. J'espère que vous apprécierez cette super nouvelle fonctionnalité! Quoi qu'il en soit, merci d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!