J'adore utiliser des tables Excel, mais je souhaite que Microsoft soit résolu un problème majeur
J'ai écrit à plusieurs reprises sur les nombreux avantages de la mise en forme de vos données en tant que table structurée dans Microsoft Excel. Cependant, malgré cela, il y a un problème majeur qui continue de jeter une clé en préparation – les tables EXCEL sont incompatibles avec des formules de tableau dynamique.
Sommaire
Le problème: les formules de tableau dynamique ne fonctionnent pas dans les tables Excel
Une formule de tableau dynamique est une formule dont le résultat se déverse d'une cellule à d'autres cellules. De plus, si le résultat de la formule augmente ou diminue en taille, le tableau fait de même.
En rapport
Tout ce que vous devez savoir sur le déversement dans Excel
Cela ne vaut pas la peine de pleurer pour des références renversées.
Par exemple, taper:
=UNIQUE(A2:A21)
dans la cellule B2 et en appuyant sur Entrée renvoie toutes les valeurs uniques des cellules A2 à A21 dans les cellules séparées de la colonne B. Remarquez comment, lorsque vous sélectionnez l'une des cellules contenant l'une de ces valeurs uniques, une ligne bleue vous rappelle qu'il s'agit d'un tableau renversé.
Cependant, les tables Excel formatées sont conçues pour contenir des lignes et des colonnes de données indépendantes, plutôt que des données qui se déversent à partir d'une seule cellule. Donc, si vous essayez de saisir la même formule de tableau dynamique dans une cellule dans une table, vous verrez le #spill! erreur.
En rapport
Tout ce que vous devez savoir sur les tables Excel (et pourquoi vous devriez toujours les utiliser)
Cela pourrait totalement changer la façon dont vous travaillez dans Excel.
Les tables Excel et les formules de tableau dynamique sont des conteneurs pour l'expansion automatique des données. Donc, si vous essayez de mettre une chose qui se développe automatiquement (comme une formule de tableau dynamique) dans quelque chose d'autre qui se développe également automatiquement (comme une table Excel), Excel ne peut pas déterminer lequel des deux devrait dicter la taille de l'ensemble de données.
Dans un monde idéal, Microsoft trouverait un moyen de vous permettre d'utiliser des formules de tableau dynamique dans les tables, la table se développant et rétrécissant automatiquement pour accueillir le réseau renversé. Jusqu'à ce qu'il le fasse, cependant, je continuerai à utiliser les approches alternatives suivantes, qui, bien que utiles, ne sont pas des correctifs parfaits.
Solution 1: Convertissez le tableau en une plage non formatée
Une façon de contourner ce problème est de convertir la table Excel en une plage en sélectionnant n'importe quelle cellule dans la table et en cliquant sur « Convertir à la plage » dans l'onglet Conception de la table.
Maintenant, parce que les données ne sont plus dans une table Excel structurée, vous pouvez utiliser des formules de tableau dynamique dans le contenu de votre cœur.
Lorsque vous convertissez une table en une plage, le formatage des cellules est conservé car la conversion se concentre sur le nettoyage des propriétés structurelles du tableau, plutôt que sur la mise en forme des cellules sous-jacentes. Donc, même si la gamme regard Comme une table formatée, il va se comporter comme une gamme régulière.
Cela dit, même si vous pouvez réactiver les boutons de filtre en appuyant sur Ctrl + Shift + L, les gammes non formatées n'ont pas les mêmes capacités que les tables Excel. Par exemple, vous ne pouvez pas référencer les en-têtes de colonne en formules, et les gammes régulières n'ont pas la même capacité automatique que les tables Excel.
De plus, les graphiques et les pharmacles liés à la plage de données ordinaire ne capturent pas automatiquement les lignes ajoutées, et si vous voulez des lignes à bandes, vous devrez appliquer des règles de mise en forme conditionnelle complexes.
En rapport
Comment formater un tableau renversé dans Excel
N'appliquez pas la mise en forme directe.
Au lieu de cela, pour conserver vos données dans un format tabulaire structuré, vous pouvez utiliser des fonctions alternatives qui peuvent être appliquées aux lignes individuelles dans une colonne de table sans renverser les résultats.
Solution 2: Utilisez des fonctions alternatives
Microsoft est toujours à la recherche de moyens de simplifier les tâches d'écriture de formule dans Excel, c'est pourquoi il a introduit de nombreuses fonctions chics qui renvoient plus d'un résultat ces dernières années. Cependant, comme ceux-ci ne fonctionnent pas dans les tableaux, vous pouvez revenir à certaines des plus anciennes fonctions qui n'ont pas cette capacité.
Cela dit, comme les fonctions plus anciennes nécessitent souvent des formules plus complexes que les fonctions dynamiques nouvelles, elles nécessitent généralement plus d'arguments et peuvent être moins efficaces, en particulier dans les ensembles de données plus importants.
Lorsque vous tapez les fonctions suivantes dans la première ligne d'une colonne dans un tableau et appuyez sur Entrée, la formule sera appliquée automatiquement aux cellules restantes de cette colonne, ainsi que dans toutes les lignes supplémentaires que vous ajoutez par la suite au bas du tableau.
Générer une liste dynamique numérotée
Fonction de tableau dynamique |
Séquence (avec Counta) |
---|---|
Ce que fait cette fonction |
Renvoie une séquence de nombres |
Fonction alternative |
RANGÉE |
Dans cet exemple, taper:
=SEQUENCE(COUNTA(B:B)-1,1,1)
Dans la cellule A2, le nombre de cellules non verbales de la colonne B, informe une pour tenir compte de la ligne d'en-tête et crée une seule colonne de nombres séquentiels, à partir de 1. De plus, si des données sont ajoutées ou supprimées de la colonne B, le nombre de la colonne A sera mise à jour automatiquement.
En rapport
Ne créez pas de listes numérotées manuellement dans Excel: utilisez la séquence et le couda à la place
Cette formule simple vous fera gagner beaucoup de temps.
Pour obtenir le même résultat dans une table Excel sans utiliser de formule de tableau dynamique, dans la cellule A2, type:
=ROW()-ROW(TableX((#Headers),(Name)))
Remplacer Table avec le nom de table correct et Nom avec le nom de colonne correct.
Création d'un tableau de nombres aléatoires
Fonction de tableau dynamique |
Randarray |
---|---|
Ce que fait cette fonction |
Renvoie un tableau de nombres aléatoires |
Fonction alternative |
Entre les deux |
Dans cette plage non formatée, tapant:
=RANDARRAY(20,1,50,100,TRUE)
dans la cellule A2 renvoie une liste aléatoire de nombres entiers entre 50 et 100 dans les cellules A2 à A21.
En rapport
Comment générer des nombres aléatoires dans Microsoft Excel
Utilisez une fonction simple ou obtenez un contrôle plus avancé avec un outil de générateur.
Pour faire de même dans une table formatée, dans la cellule A2, type:
=RANDBETWEEN(50,100)
dans la cellule A2, et cliquez et faites glisser la poignée du tableau pour étendre le tableau vers le bas jusqu'à ce qu'il y ait 20 rangées de données.
Randarray et Randbetween sont des fonctions volatiles, ce qui signifie qu'elles recalculent chaque fois qu'un changement est apporté à la feuille de calcul. Pour fixer les nombres aléatoires une fois qu'ils ont été générés, sélectionnez toutes les données (Ctrl + A), copiez-le (Ctrl + C) et appuyez sur CRL + Shift + V pour coller les nombres sous forme de valeurs.
Diviser une cellule en colonnes distinctes
Fonction de tableau dynamique |
TextSplit |
---|---|
Ce que fait cette fonction |
Divise le texte en lignes ou colonnes à l'aide de délimiteurs |
Fonctions alternatives |
Textbefore et textafter |
Dans cet exemple, Textsplit prend le nom dans la cellule A2 et déborde la version divisée de chaque nom en cellules B2 et C2, avec la chaîne d'espace de virgule agissant comme délimiteur. Ensuite, la sélection de la cellule B2 et le double-clicage de la poignée de remplissage applique la formule dynamique du tableau aux cellules restantes dans la plage.
=TEXTSPLIT(A2,", ")
Lorsque vous travaillez avec des tables Excel, au lieu d'utiliser cette formule de tableau dynamique, vous pouvez utiliser TextFefore et Textafter.
Dans la cellule B2, type:
=TEXTBEFORE((@Name),",")
Pour extraire le texte devant la virgule de la colonne du nom.
Ensuite, dans la cellule C2, type:
=TEXTAFTER((@Name)," ")
pour extraire le texte après l'espace.
En rapport
4 façons de diviser les données en plusieurs colonnes dans Microsoft Excel
Vous avez l'embarras du choix!
Fonction de tableau dynamique |
UNIQUE |
---|---|
Ce que fait cette fonction |
Renvoie des valeurs uniques d'une plage |
Fonctions alternatives |
Index, unique et rangée |
Dans cette gamme régulière, cette formule unique répertorie toutes les valeurs uniques dans les cellules A2 à A50.
=UNIQUE(A2:.A50)
Remarquez la période après le côlon. Ce personnage, connu dans ce contexte en tant qu'opérateur de référence de garniture, dit à Excel de couper les lignes vierges à la fin du résultat, ce qui empêche un zéro d'apparaître dans la liste.
En rapport
Comment énumérer et trier les valeurs et le texte uniques dans Microsoft Excel
Créez une liste de noms, dates ou autres données uniques dans votre feuille de calcul avec une fonction simple.
Pour obtenir un résultat similaire dans une table Excel, dans la cellule B2, type:
=INDEX(UNIQUE($A$1:$A$50),ROW(A2))
où
- INDICE() Renvoie une valeur,
- Unique ($ a 1 $: $ a 50 $)qui est le premier argument de la formule d'index, trouve des valeurs uniques dans les cellules A1 à A50, et
- Row (A2)qui est le deuxième argument de la formule d'index, renvoie le ncette valeur unique, où n est le numéro de ligne de la cellule active.
Cependant, bien que cela trouve avec succès toutes les valeurs uniques des données source, le tableau ne se développe pas et ne se contracte pas automatiquement. Pour tenir compte de cela, dans le ruban de conception de la table, vérifiez la «ligne totale» et développez la liste déroulante résultante en bas du tableau pour modifier le type d'agrégation en «compter». Cela compte les valeurs uniques dans la colonne de table.
Ensuite, dans une cellule séparée, type:
=COUNTA(UNIQUE($A$2:.$A$50))
Pour compter les valeurs uniques dans la plage de source.
Maintenant, si le décompte dans la rangée totale du tableau ne correspond pas au nombre dans la cellule CountA-Unique, vous savez que vous devez développer ou réduire la taille de votre tableau en conséquence.
Oui, il existe des moyens de naviguer dans l'incompatibilité entre les tables Excel et les formules de réseaux dynamiques, mais il est certes lourd et prend du temps. C'est pourquoi nous avons tous besoin de Microsoft pour nous faire une faveur et trouver un moyen d'intégrer ces deux outils utiles soner plutôt que plus tard.