Assembler les Données

Pour les utilisateurs d’Excel, travailler avec des Données est la meilleure partie de la création d’un tableau de bord. Comme vous le savez sans doute, Excel fournit d’excellents outils pour l’agrégation et la manipulation de Données, y compris des centaines de formules et de tableaux de Données et des outils pour trier les Données, diviser les Données et supprimer les doublons. Dans les sections suivantes, je discute de certains des outils d’Excel pour manipuler les Données.

Tableaux croisés dynamiques

Le tableau croisé dynamique est l’un des outils les plus puissants d’Excel. Un tableau croisé dynamique est un rapport qui filtre, trie et récapitule vos Données en fonction des conditions que vous définissez. Les tableaux croisés dynamiques sont interactifs en ce sens que vous pouvez faire glisser et déposer des champs dans les zones de champs de tableau croisé dynamique appropriées pour modifier rapidement la façon dont vos Données sont récapitulées.

Tout ce que vous pouvez faire avec un tableau croisé dynamique, vous pouvez le faire avec des formules de feuille de calcul. Un tableau croisé dynamique résumera vos Données en beaucoup moins de temps qu’il n’en faut pour écrire les formules pour faire la même chose. Si vous souhaitez apporter des modifications à un tableau croisé dynamique en ajoutant, supprimant ou réorganisant des champs, il se met à jour en une fraction de seconde. La réécriture des formules pour les changements prend beaucoup plus de temps. La figure 1 montre une partie de certaines Données brutes et la figure 2 montre un tableau croisé dynamique possible que vous pouvez créer à partir des Données.

Figure 1: Données brutes pour un tableau croisé dynamique

Figure 2: un tableau croisé dynamique

Le tableau croisé dynamique de la figure 2 a été créé en faisant glisser le champ Qtr vers la zone Colonnes, les champs Région et État vers la zone Lignes et le champ Ventes vers la zone Valeurs. Par défaut, Excel additionne le champ Ventes car ses Données sont numériques. Pour les Données non numériques, Excel compte par défaut les Données. La figure 3 montre le volet Office Champs de tableau croisé dynamique avec ces conditions.

Figure 3: le volet Office Champs de tableau croisé dynamique

Les zones du volet Office Champs de tableau croisé dynamique sont les suivantes :

  • Outils (représentés par l’icône d’engrenage): cette section vous permet de modifier la disposition du volet Office et de trier les noms des champs. Si vous avez beaucoup de champs, vous préférerez peut-être une mise en page qui affiche plus de champs.
  • Rechercher : en tapant dans cette zone de texte, vous limitez la liste de champs aux seuls champs qui correspondent ou correspondent partiellement au texte que vous tapez.
  • Filtres : si vous faites glisser un champ dans cette zone, Excel place une zone de liste déroulante au-dessus de votre tableau croisé dynamique. Vous pouvez sélectionner une ou plusieurs valeurs dans la liste déroulante pour limiter les Données affichées par le tableau croisé dynamique.
  • Colonnes : les champs de cette zone sont affichés en haut du tableau croisé dynamique. Vous pouvez placer plusieurs champs dans la zone Colonnes pour les imbriquer. Autrement dit, les champs situés plus haut dans la zone Colonnes couvriront plusieurs colonnes et les cellules situées en dessous afficheront uniquement les valeurs liées aux champs supérieurs. La figure 4 montre le champ Mois imbriqué sous le champ Qtr. Les valeurs Jan, Fev et Mar sont les seules valeurs liées à Qtr-1, ce sont donc les seules valeurs affichées imbriquées en dessous.

Figure 4 : Champs imbriqués dans la zone Colonnes

  • Lignes : les champs de cette zone sont affichés sur le côté gauche du tableau croisé dynamique. Comme pour la zone Colonnes, vous pouvez imbriquer des champs pour les afficher dans une hiérarchie. Le tableau croisé dynamique illustré à la figure 3 montre le champ État imbriqué sous le champ Région.
  • Valeurs : les champs de cette zone sont agrégés dans le corps du tableau croisé dynamique. Somme et NB sont les agrégateurs les plus couramment utilisés.

Mais il existe plusieurs autres façons d’agréger, notamment Moyenne, Min et Max. L’agrégation se produit à l’intersection des champs Colonne et Ligne. Par exemple, là où Qtr-1 et Missouri se croisent, le tableau croisé dynamique de la figure 3 additionne les ventes uniquement pour les lignes contenant un État du Missouri et un Qtr de Qtr-1.

Excel ajoute des totaux généraux et, s’il existe des champs imbriqués dans les zones Colonnes ou Lignes, des sous-totaux. Ces totaux peuvent être affichés ou masqués selon la manière dont vous souhaitez afficher les Données. Pour désactiver les totaux généraux, sélectionnez n’importe quelle partie du tableau croisé dynamique et choisissez Options dans l’onglet Analyse des outils de tableau croisé dynamique du ruban pour afficher la boîte de dialogue Options de tableau croisé dynamique illustrée à la figure 5. Décochez les cases intitulées Afficher les totaux généraux pour les lignes et Afficher le grand Totaux pour les colonnes.

Figure 5 : La boîte de dialogue Options de tableau croisé dynamique

Pour masquer les sous-totaux des champs imbriqués, cliquez avec le bouton droit sur l’une des valeurs du champ et choisissez Paramètres de champ dans le menu contextuel. Dans la boîte de dialogue Paramètres de champ, illustrée à la figure 6, choisissez l’un des boutons d’option suivants.

  • Automatique: affiche les sous-totaux avec la même agrégation (somme, nombre, etc.) que le champ enfant.
  • Aucun: masque les sous-totaux du champ.
  • Personnalisé: affiche les sous-totaux avec une agrégation identique ou différente de celle du champ Enfant.

Figure 6: La boîte de dialogue Paramètres du champ

La figure 7 montre un tableau croisé dynamique avec les totaux généraux et les sous-totaux masqués.

Figure 7: un tableau croisé dynamique sans totaux généraux ni sous-totaux

Les tableaux croisés dynamiques des fonctions de feuille de calcul LIREDONNEESTABCROISDYNAMIQUE sont dynamiques par nature. Cela signifie qu’ils peuvent changer la taille et l’emplacement des Données souhaitées, ce qui rend difficile la référence à leurs cellules dans les formules. Heureusement, Excel fournit la fonction de feuille de calcul LIREDONNEESTABCROISDYNAMIQUE afin que vous puissiez être sûr de toujours pointer vers les Données correctes.

Par exemple, si vous souhaitez utiliser les ventes du premier trimestre de la Californie dans une formule, vous pouvez utiliser cette fonction LIREDONNEESTABCROISDYNAMIQUE:

=LIREDONNEESTABCROISDYNAMIQUE(“Ventes”;$H$4;”Etat”;”Californie”;”Region”;”ouest”;”Qtr”;”Qtr-1″)

Si davantage de Données sont ajoutées aux Données source et que le tableau croisé dynamique est actualisé afin que la cellule qui contient les ventes du premier trimestre de la Californie se déplace, la formule pointe toujours vers le bon endroit. Le premier argument de la fonction fait référence au champ de la zone Valeurs que vous souhaitez renvoyer. L’argument suivant, h$4$, est la cellule supérieure gauche du tableau croisé dynamique qui contient les Données. Les six derniers arguments sont des paires de valeurs qui déterminent quelle intersection de colonnes et de lignes utiliser.

Les paires d’arguments peuvent être dans n’importe quel ordre, mais dans les paires, le nom du champ doit venir en premier suivi de la valeur de ce champ. La paire d’arguments «Etat» et «Californie» indique à la fonction d’utiliser la ligne où la Californie apparaît dans le champ État. Lorsque la fonction évalue les paires d’arguments restantes, elle se réduit à la valeur à renvoyer.

Vous pouvez également utiliser LIREDONNEESTABCROISDYNAMIQUE pour renvoyer les totaux. Par exemple, pour renvoyer le total général de la colonne Qtr-1, utilisez cette fonction :

= LIREDONNEESTABCROISDYNAMIQUE (“Ventes”, $ h $ 4, “Qtr”, “Qtr-1”)

Ou pour renvoyer le total général de l’ensemble du tableau croisé dynamique, utilisez cette fonction :

= LIREDONNEESTABCROISDYNAMIQUE (“Ventes”, $ h $ 4)

La fonction LIREDONNEESTABCROISDYNAMIQUE est fastidieuse à créer manuellement. Heureusement, Excel créera la fonction pour vous lorsque vous cliquez dans un tableau croisé dynamique.

Tapez simplement un signe égal et cliquez sur une cellule à l’intérieur du corps du tableau croisé dynamique pour qu’Excel crée la fonction avec tous les arguments appropriés. Si vous souhaitez faire référence à une cellule dans un tableau croisé dynamique et éviter la fonction LIREDONNEESTABCROISDYNAMIQUE, vous pouvez simplement taper l’adresse de la cellule dans votre formule. Pour désactiver le comportement par défaut de l’utilisation de LIREDONNEESTABCROISDYNAMIQUE, accédez à Fichier ➪ Options ➪ Formules et décochez la case Utiliser les fonctions LIREDONNEESTABCROISDYNAMIQUE pour les références de tableau croisé dynamique.

 

ATTENTION

Contrairement aux formules, un tableau croisé dynamique n’affiche pas automatiquement les modifications apportées aux Données après la création du tableau croisé dynamique. Cliquez sur le bouton Actualiser dans l’onglet Analyse des outils de tableau croisé dynamique du ruban pour mettre à jour le tableau croisé dynamique pour toutes les modifications.

Fonctions de la feuille de calcul

Excel propose des centaines de fonctions de feuille de calcul pour vous aider à manipuler et à organiser les Données d’un tableau de bord. En pratique, vous n’utiliserez pas la plupart d’entre eux. Mais certaines fonctions de feuille de calcul apparaissent le plus souvent dans les projets de tableau de bord. Dans les sections suivantes, je discuterai de certains des plus courants.

 

La fonction RECHERCHEV

La fonction RECHERCHEV recherche une valeur dans la première colonne d’une plage et renvoie une valeur dans une autre colonne sur la même ligne que la valeur trouvée. La syntaxe de RECHERCHEV est la suivante:

RECHERCHEV (valeur_recherche ; tableau_table ; numéro_index_col ; recherche_ligne)

La figure 8 montre un exemple de la fonction RECHERCHEV.

Figure 8 : La fonction RECHERCHEV

Dans cet exemple, il existe un tableau des commissions par vendeur et par mois dans la plage F3: R52. La formule trouve la commission d’avril pour le vendeur dans la cellule B4. Le nom du vendeur doit figurer dans la première colonne de la plage. La formule regarde vers le bas la colonne F jusqu’à ce qu’elle trouve le nom du vendeur, puis renvoie la valeur dans la cinquième colonne.

L’argument valeur_proche est défini sur FAUX dans cet exemple, car vous souhaitez que la formule ne renvoie une valeur que si elle trouve une correspondance exacte. Si vous définissez valeur_proche sur VRAI, Excel s’attend à ce que les Données de la première colonne soient triées et renverra une correspondance étroite s’il n’y en a pas. Il y a des situations où renvoyer une correspondance approximative est utile, mais ils sont rares, et la plupart du temps, vous voudrez que le dernier argument soit FAUX.

La fonction _XLFN.XLOOKUP

La fonction _XLFN.XLOOKUP est un ajout relativement récent à la gamme de fonctions de feuille de calcul Excel. Cela fonctionne comme RECHERCHEV mais ne nécessite pas que la valeur soit trouvée dans la première colonne. Voici la syntaxe de _XLFN.XLOOKUP:

_XLFN.XLOOKUP (valeur_cherchée ;matrice_cherche ; matrice_retournée ; si_pas_trouvé ; mode_EQUIV ; mode_recherche)

Le premier argument est le même pour _XLFN.XLOOKUP et RECHERCHEV. Au lieu de fournir la plage entière et de supposer que la valeur de recherche se trouve dans la première colonne, vous fournissez une matrice_cherche (la colonne à rechercher) et un matrice_retournée(la colonne avec la valeur à renvoyer).

La fonction _XLFN.XLOOKUP inclut également l’argument si_pas_trouvé qui vous permet de spécifier une valeur à renvoyer si la valeur de recherche n’est pas dans la liste. Si vous ne spécifiez pas cet argument et que la valeur n’est pas trouvée, _XLFN.XLOOKUP renvoie la valeur d’erreur # N / A tout comme RECHERCHEV.

L’argument mode_EQUIV est similaire à l’argument recherche_ligne dans RECHERCHEV. Au lieu de VRAI ou FAUX, vous fournissez un nombre pour indiquer à Excel si vous voulez une correspondance exacte ou l’une des options de correspondance approximative. L’option de correspondance exacte, 0, est la valeur par défaut.

Le dernier argument, mode_recherche, vous permet d’indiquer à Excel comment rechercher la valeur. Si vous disposez d’un ensemble de Données volumineux, l’argument mode_recherche peut vous aider à accélérer la recherche. Pour la plupart des ensembles de Données, il peut être ignoré.

La figure 9 montre une formule _XLFN.XLOOKUP pour trouver le nom du vendeur avec la commission la plus élevée en avril.

Figure 9 : La fonction _XLFN.XLOOKUP

Au lieu de rechercher une valeur dans la première colonne, cette formule recherche une valeur dans la cinquième colonne et renvoie la valeur correspondante de la première colonne. La formule de la cellule C4, = MAX (J3: J52), trouve la plus grande valeur dans la colonne J. La fonction _XLFN.XLOOKUP recherche cette valeur dans la colonne J et renvoie la valeur de la colonne F sur la même ligne. Étant donné que vous savez que la valeur existe et que vous voulez une correspondance exacte, vous n’avez pas besoin de fournir les autres arguments à la fonction.

Les fonctions INDEX et EQUIV

Si vous utilisez une version d’Excel qui n’inclut pas _XLFN.XLOOKUP, vous pouvez créer le même résultat en utilisant une combinaison de la fonction INDEX et de la fonction EQUIV. La fonction INDEX renvoie une valeur d’une plage basée sur le numéro de ligne et le numéro de colonne que vous fournissez. Pour une seule plage de colonnes, il vous suffit de fournir le numéro de ligne. La partie fonction EQUIV de la formule calcule quelle ligne INDEX utilise. La figure 10 montre une formule pour renvoyer les mêmes valeurs que la fonction _XLFN.XLOOKUP dans l’exemple précédent.

Figure 10 : Les fonctions INDEX et EQUIV

La syntaxe de la fonction EQUIV est EQUIV (valeur_cherchée ; matrice_recherche ;[type])

La fonction EQUIV fonctionne comme RECHERCHEV, sauf qu’il n’y a pas de colonne à renvoyer. Il renvoie simplement la position de valeur_cherchée dans la liste de valeurs matrice_cherche. Le dernier argument de EQUIV, 0, recherche une correspondance exacte comme lorsque le dernier argument de RECHERCHEV est FAUX. Dans cet exemple, EQUIV renvoie la valeur 8 car la valeur de C4 est la huitième valeur de la plage J3: J52.

Une fois qu’Excel calcule la valeur retournée par EQUIV, la fonction INDEX est évaluée comme

= INDEX (F3: F52;8)

La syntaxe de la fonction INDEX est INDEX (référence, numéro_ligne, numéro_colonne)

Étant donné que l’argument de référence est une seule colonne, F3: F52 dans cet exemple, il vous suffit de fournir l’argument numéro_ligne.

La fonction SOMMEPROD

La fonction SOMMEPROD est l’une des fonctions de feuille de calcul les plus puissantes d’Excel. Il est conçu pour multiplier deux ou plusieurs plages ensemble et additionner les résultats en un seul nombre. Un exemple simple de SOMMEPROD est illustré à la Figure 11.

Figure 11 : La fonction SOMMEPROD

Dans cet exemple, il y a des quantités, et pour chaque quantité, un prix.

SOMMEPROD multiplie chaque quantité par son prix correspondant et additionne tous les résultats. C’est la même chose que de mettre la formule = A2 * B2 et de remplir la colonne C, puis d’utiliser = SOMME (C2: C5), mais il le fait dans une formule.

La fonction SOMMEPROD fonctionne comme une formule matricielle, discutée dans la section suivante, et c’est là que réside sa véritable puissance. Avec SOMMEPROD, vous pouvez filtrer les valeurs en les multipliant par 0, afin qu’elles n’aient aucun effet sur la somme finale. La figure 12 montre un exemple d’utilisation de SOMMEPROD pour additionner uniquement les totaux d’un produit en un mois.

Figure 12 : Filtrer les valeurs avec SOMMEPROD

Cet exemple SOMMEPROD se compose de trois sections : deux sections de comparaison et une section de résultats. La magie de SOMMEPROD est que lorsqu’une comparaison renvoie VRAI, la multiplication traite VRAI comme le nombre 1, et lorsqu’une comparaison renvoie FAUX, la multiplication traite FAUX comme le nombre 0. La première section, ($ C $ 2: $ C $ 3651 = K6), compare chaque valeur de la colonne C à la valeur de la cellule K6 et renvoie VRAI ou FAUX. La deuxième section, (MOIS ($ A $ 2: $ A $ 3651) = 4), compare le mois de chaque date de la colonne A à 4 et renvoie VRAI ou FAUX.

La dernière section, ($ I $ 2: $ I $ 3651), ne fait aucune comparaison. Au lieu de cela, il renvoie simplement la longue liste de nombres de la colonne I. Chacune de ces sections est évaluée en un tableau de 3 650 valeurs. Les valeurs des tableaux des deux premières sections sont VRAI ou FAUX et les valeurs du tableau de la dernière section sont les valeurs de la colonne I.

Ensuite, SOMMEPROD multiplie chaque ligne des tableaux ensemble. Pour les premiers éléments des tableaux, qui correspondent à la ligne 2 des Données, SOMMEPROD multiplie FAUX * FAUX * 535,84 car la valeur de C2 n’est pas égale à la valeur de K6 et le mois de A2 n’est pas 4. Puisque FAUX est évalué à 0 et que la multiplication de tout nombre par 0 équivaut à 0, le total de cet élément est 0.

Pour les Données de la ligne 6, l’élément correspond, mais le mois ne correspond toujours pas.

SOMMEPROD évalue cette ligne comme VRAI * FAUX * 575.60. La valeur VRAI est évaluée à 1, mais comme la deuxième section est FAUX, donc 0, la ligne entière est toujours évaluée à 0. Ce n’est que lorsque la formule descend à la ligne 924 que l’élément et le mois correspondent. Lorsqu’il évalue cette ligne, il multiplie VRAI * VRAI * 47.12. Les valeurs VRAI sont évaluées à 1 et cette ligne ajoute 47,12 à la somme finale.

Vous pouvez voir que si une condition est FAUX, Excel insère un 0 dans la multiplication et rend la ligne entière 0. Ce n’est que si chaque condition est VRAI qu’une valeur autre que 0 est ajoutée au total. Peu importe que vous ayez 2 conditions ou 20, si elles sont toutes VRAIES, Excel continue de multiplier par 1, ce qui n’a aucun effet sur la section finale.

De cette façon, SOMMEPROD est comme un tableau croisé dynamique. Dans un tableau croisé dynamique, Excel agrège les valeurs là où tous les champs de ligne croisent tous les champs de colonne. La valeur d’un tableau croisé dynamique sur SOMMEPROD est qu’il est beaucoup plus facile de modifier un tableau croisé dynamique que de réécrire un tas de formules SOMMEPROD. Mais la valeur des formules est qu’elles recalculent instantanément lorsque les Données changent, alors que vous devez actualiser un tableau croisé dynamique.

Une autre similitude entre SOMMEPROD et les tableaux croisés dynamiques est que vous pouvez agréger des valeurs en les comptant au lieu de les additionner. Pour compter les valeurs à l’aide de SOMMEPROD, n’incluez que des sections de comparaison dans les formules. Par exemple, pour compter toutes les lignes avec un produit spécifique en avril, utilisez la formule suivante :

= SOMMEPROD (($ C $ 2: $ C $ 3651 = K7) * (MOIS ($ A $ 2: $ A $ 3651) = 4))

Cette formule est semblable à l’exemple précédent, sauf que la section représentant la colonne I est omise. Désormais, lorsque les deux conditions sont VRAIES, Excel multiplie 1 * 1 et le résultat, 1, est inclus dans la somme. Il y a 18 lignes dans les Données d’exemple qui ont P23981 comme élément et la date est en avril. Pour chacune de ces lignes, 1 est ajouté au total et la formule renvoie 18.

Une autre technique que vous pouvez utiliser avec SOMMEPROD consiste à ajouter des sections de comparaison qui font référence à la même colonne. Par exemple, si vous souhaitez additionner le total de deux produits, P23981 et P73302, qui se produisent en avril, utilisez la formule suivante:

= SOMMEPROD ((($ C $ 2: $ C $ 3651 = “P23981”) + ($ C $ 2: $ C $ 3651 = “P73302”)) * (MOIS ($ A $ 2: $ A $ 3651) = 4) * ($ I $ 2: $ I 3651 $))

Dans cet exemple, deux sections conditionnelles font référence à la colonne C.

Ils ne peuvent jamais être tous les deux VRAI car toute ligne de la colonne C ne peut contenir qu’un seul élément. En les additionnant, Excel ajoutera 0 + 0, 0 + 1 ou 1 + 0 et renverra le total si la colonne C est soit un produit (ou 0 si ce n’est ni l’un ni l’autre). Étant donné qu’Excel se multiplie avant de s’ajouter, vous devez placer ces sections conditionnelles dans leurs propres ensembles de parenthèses pour forcer Excel à effectuer cette évaluation en premier. N’utilisez cette technique que lorsque les sections de comparaison font référence à la même colonne. Si vous additionnez des comparaisons pour différentes colonnes et qu’elles sont toutes les deux VRAIES, cela renverra un 2 et votre résultat sera faux.

Formules de tableau

La fonction SOMMEPROD dans la section précédente est un type spécial de fonction de tableau. Il ne fait que faire la somme des résultats et ne peut les agréger d’aucune autre manière. Les formules matricielles utilisent exactement le même calcul que SOMMEPROD mais vous pouvez choisir comment agréger les valeurs filtrées.

Avec les formules matricielles, vous devez valider la formule après l’avoir entrée à l’aide de Ctrl + Maj + Entrée, pas seulement Entrée comme vous le faites avec SOMMEPROD ou d’autres fonctions de feuille de calcul.

En utilisant le même ensemble de Données des exemples SOMMEPROD, si vous souhaitez renvoyer le plus grand total pour le produit P23981 en avril, vous utiliserez une formule matricielle et la fonction MAX, comme indiqué ci-dessous et dans la figure 14 :

= MAX (($ C $ 2: $ C $ 3651 = K6) * (MOIS ($ A $ 2: $ A $ 3651) = 4) * ($ I $ 2: $ I $ 3651))

La barre de formule de la figure 13 montre des accolades autour de la formule. Vous ne tapez pas ces accolades. Excel les insère automatiquement lorsque vous validez la formule en utilisant Ctrl + Maj + Entrée au lieu de simplement Entrée. Si votre formule matricielle ne renvoie pas le résultat correct, la première chose à vérifier est que les accolades sont présentes. Si ce n’est pas le cas, appuyez sur F2 pour modifier la cellule et réengagez-la avec Ctrl + Maj + Entrée.

Figure 13: Une formule matricielle pour trouver la plus grande valeur

Tout comme SOMMEPROD, cette formule matricielle contient deux sections conditionnelles et une section de valeurs. Une fois qu’Excel a multiplié les trois sections ensemble, en traitant à nouveau VRAI comme 1 et FAUX comme 0, au lieu d’additionner le résultat, il trouve la valeur maximale des résultats. En fait, si vous deviez changer le MAX en SOMME dans cette formule, vous obtiendriez le même résultat que la formule SOMMEPROD.

Pour trouver la plus petite valeur à partir d’une liste de valeurs filtrées, il faut une technique légèrement différente. Étant donné que les valeurs que vous filtrez évaluent à 0 avant d’être agrégées, la plus petite valeur d’une liste de valeurs positives serait toujours égale à 0. Heureusement, la fonction MIN ignore les valeurs de texte et nous pouvons l’utiliser pour n’inclure que des valeurs différentes de zéro. La formule suivante renvoie la plus petite valeur de l’article P23981 en avril :

= MIN (SI (($ C $ 2: $ C $ 3651 = K15) * (MOIS ($ A $ 2: $ A $ 3651) = 4) = 0, “”, $ I $ 2: $ I 3651 $))

Les deux sections conditionnelles sont placées dans l’argument conditionnel d’une fonction IF. Si, multipliés ensemble, ils sont égaux à 0, une chaîne vide (deux guillemets) est insérée dans les valeurs filtrées, sinon la valeur de la colonne I est insérée. Puisque MIN ignore les chaînes, il ignorera toutes les valeurs de chaîne vides et ne retournera que la plus petite valeur où les conditions sont VRAI.

Les Tables

Les tableaux sont des zones spéciales d’une feuille de calcul qui contiennent des Données. Ils sont censés ressembler à des tables de base de Données, mais ils ne partagent que certaines des mêmes propriétés. Les deux principaux avantages de l’utilisation des tableaux sont que les formats de cellule et les formules sont cohérents pour toutes les lignes, et les références au tableau se développent et se réduisent automatiquement à mesure que le tableau grandit et rétrécit. La figure 14 montre un petit tableau et un histogramme crée à partir de ce tableau.

Si vous modifiez le nombre de lignes ou de colonnes dans le tableau, le graphique s’ajustera automatiquement. Le graphique ne fait pas simplement référence à la plage A1: C6, il fait référence à la zone spéciale avec un nom tel que Table1 et sait quand cette zone augmente ou diminue.

Figure 14 : Un histogramme basé sur un tableau

La première étape consiste à convertir une plage Excel normale en un tableau. Pour ce faire, sélectionnez la plage et choisissez Insérer ➪ Tableau dans le ruban pour afficher la boîte de dialogue Créer un tableau dans la figure 15.

Figure 15 : La boîte de dialogue Créer une table

Cette boîte de dialogue confirme la plage que vous souhaitez convertir et vous permet de spécifier si la première ligne de votre plage contient des en-têtes. Cliquez sur OK pour terminer la création de la table. Ensuite, sélectionnez le tableau et choisissez Insérer ➪ Graphiques recommandés dans le ruban et choisissez l’option Colonne groupée pour insérer le graphique. Vous pouvez changer le titre du graphique comme je l’ai fait, mais ce n’est pas nécessaire pour cet exemple.

Imaginez maintenant un scénario dans lequel vous souhaitez ajouter l’année suivante à ce graphique et que vous souhaitez séparer le Texas de la région du sud-ouest et en faire sa propre région. Avec une plage Excel normale, vous ajouteriez une nouvelle série pour la nouvelle année et ajusteriez la série existante pour qu’elle pointe vers la plage la plus large. Mais comme vous utilisez un tableau, vous pouvez simplement ajouter les Données et le graphique s’ajustera.

Pour ajouter la nouvelle année, entrez 2023 dans la cellule D1. La figure 16 montre qu’Excel a augmenté les colonnes du tableau pour la nouvelle plage et a déjà ajouté une nouvelle série au graphique.

Figure 16 : Le tableau se développe pour les nouvelles Données.

Ensuite, entrez Texas dans la cellule A7. Excel augmentera les lignes du tableau et vous pourrez entrer des valeurs pour la nouvelle année et diviser la région sud-ouest. La figure 17 montre la nouvelle taille de tableau et le graphique résultant.

Figure 17 : Le graphique s’ajuste aux nouvelles Données.

Excel a automatiquement ajouté une nouvelle colonne groupée au graphique pour la nouvelle région et la série 2023 est remplie avec les Données entrées dans le tableau. Les nouvelles Données saisies pour le Texas pour les années existantes sont automatiquement formatées avec le même format numérique que les autres Données de ces colonnes.

Étant donné que 2023 est une nouvelle colonne, elle n’a pas de mise en forme existante à appliquer et vous devez formater cette colonne séparément. Rien sur le graphique n’a été modifié. Il vous suffit d’ajouter de nouvelles Données au tableau pour obtenir ces résultats.

Référencement de table structurée

Le graphique de l’exemple précédent s’est ajusté à mesure que les Données du tableau changeaient.

Lorsque vous faites référence à des tableaux dans des formules à l’aide du référencement de tableau structuré, vos formules prendront également en compte automatiquement les modifications dans le tableau. Pour ces exemples, le nom de la table a été changé en tblRevenue à partir du nom de table par défaut d’Excel. Pour modifier le nom d’une table, sélectionnez n’importe quelle partie de la table et choisissez Outils de table Conception ➪ Nom de la table dans le ruban et saisissez le nouveau nom de la table.

Pour créer une formule à l’aide du référencement de tableau structuré, sélectionnez une cellule inutilisée dans la feuille de calcul, entrez le début d’une formule comme = SOMME (et pointez sur la plage B2: B7. Si vous entrez des Données dans une cellule adjacente au tableau, Excel suppose que vous souhaitez développer le tableau. Choisissez donc une cellule à l’écart du tableau. Par défaut, Excel convertira votre formule en un tableau structuré référencé comme la formule ci-dessous:

= SOMME (tblRevenue [2021])

Au lieu de faire référence à la plage B2: B7, Excel a converti l’argument en tblRevenue [2021], en se référant à la colonne intitulée 2021 dans la table nommée tblRevenue. Si vous préparez des Données pour un tableau de bord, vous pouvez utiliser le référencement de tableau structuré pour vous assurer que vos formules couvrent toujours toutes les Données.

Vous n’avez pas besoin de pointer vers les Données pour obtenir un référencement de table structuré. Vous pouvez saisir cette même formule manuellement. Commencez à saisir une formule en tapant

= SOMME (tbl dans une cellule. La figure 18 montre la liste déroulante Excel affiche tous vos choix contenant ces lettres.

Figure 18 : liste déroulante d’entrée de formule d’Excel

Si vous avez plusieurs tables dans votre classeur et que vous choisissez de les nommer toutes en commençant par les mêmes lettres, comme tbl, cette méthode vous propose une liste pratique de tables parmi lesquelles choisir. Dans cet exemple, il n’y a qu’une seule table et elle est sélectionnée car c’est la première de la liste. À partir de là, vous pouvez appuyer sur la touche Tab pour compléter le nom de la table sans avoir à tout taper.

Après le nom de la table, saisissez une parenthèse ouverte, [, pour afficher une liste d’options liées à cette table. La figure 19 montre la liste des options pour tblRevenue.

Figure 19 : Options de complétion de formule de tableau

La première option est le symbole @. Lorsque vous utilisez des formules à partir d’une table, le symbole @ indique à Excel de renvoyer la valeur de la même ligne que la formule. La référence [@ 2021] renvoie la valeur de la colonne nommée 2021 et qui se trouve sur la même ligne. Comparez cela à la référence [2021], qui renvoie la colonne entière.

Les quatre options suivantes sont les noms de colonne du tableau. Excel connaît la structure de la table et génère automatiquement des plages nommées pour les colonnes.

Les quatre dernières options sont des références à des zones spéciales du tableau. La référence #All fait référence à l’ensemble du tableau, y compris les en-têtes et les totaux. La référence #Données est la même que #Tout sauf qu’elle exclut les en-têtes et les totaux. Les références #Entête et #Totaux se réfèrent uniquement à leurs domaines respectifs. Vous pouvez sélectionner l’option souhaitée et appuyer sur Tab pour terminer la saisie.

Par exemple, si vous faites une flèche vers le bas jusqu’à la référence #Données, appuyez sur Tab et entrez un crochet fermant et une parenthèse fermante, vous obtiendrez la formule suivante qui additionne toutes les Données du tableau :

= SOMME (tblRevenue [#Données])

Texte en colonnes

Lorsque vous traitez avec des Données provenant de l’extérieur d’Excel, les Données sont parfois toutes regroupées au lieu d’être bien disposées en colonnes. La fonction Texte en colonnes d’Excel peut vous aider à manipuler les Données dans un format utilisable.

La figure 20 montre des Données collées dans la colonne A d’une feuille de calcul. Ce sont des Données en colonnes séparées par des virgules, mais elles sont toutes dans une seule cellule.

Pour séparer les Données, procédez comme suit :

  1. Sélectionnez les Données et choisissez Données ➪ Texte en colonnes dans le ruban.
  2. Dans l’assistant de conversion de texte en colonnes, illustré à la figure 21, sélectionnez l’option Délimité et cliquez sur Suivant.
  3. Désélectionnez le délimiteur de tabulation et sélectionnez le délimiteur virgule. Excel montre un aperçu de la façon dont il a divisé les Données, comme le montre la figure 22.

Cliquez sur Suivant.

  1. À la dernière étape, vous pouvez formater chaque colonne. Excel va généralement convertir les dates et les nombres avec succès. Si vous avez du texte qui contient des nombres avec des zéros non significatifs, Excel supprimera les zéros sauf si vous formatez cette colonne en tant que texte. La figure 24 montre les Données divisées en colonnes séparées.

Figure 20: Données regroupées dans une cellule

Figure 21: L’assistant de conversion de texte en colonnes

Figure 22 : Excel divise les Données sur le délimiteur.

Figure 23 : Données divisées en colonnes séparées

 

Suppression des doublons

Un autre problème courant lors du traitement des Données brutes est la duplication des enregistrements.

L’outil Supprimer les doublons d’Excel est un moyen rapide et facile de supprimer les enregistrements en double sur place sans avoir à utiliser des tableaux croisés dynamiques ou des formules fastidieuses. Les Données illustrées à la figure 24 ont des enregistrements en double.

Pour supprimer les doublons, sélectionnez les Données et cliquez sur Supprimer les doublons dans l’onglet Données du ruban. Cela affichera la boîte de dialogue Supprimer les doublons, illustrée à la figure 25.

Figure 24 : Données avec des enregistrements en double

Figure 25 : La boîte de dialogue Supprimer les doublons

La boîte de dialogue affiche toutes les colonnes des Données et vous pouvez sélectionner laquelle de ces colonnes déterminera ce qui est en double. Avec les factures, comme dans cet exemple, toutes les Données seront dupliquées, alors choisissez toutes les colonnes. Pour les autres types de Données, peut-être qu’une ou deux colonnes seulement détermineront un doublon. Pour éviter de supprimer des Données que vous ne devriez pas, essayez d’utiliser autant de colonnes que possible. Lorsque vous cliquez sur OK, Excel affiche la boîte de message dans la figure 26 montrant combien de lignes seront supprimées.

Figure 26 : Excel affiche le nombre de valeurs supprimées.

Cliquez sur OK pour fermer la boîte de message et il ne vous reste que des enregistrements uniques. Vous pouvez annuler cette action si vous déterminez que vous avez fait une erreur.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x