■■ J’ai entré le nombre d’unités vendues et le revenu total pour chaque vendeur dans une feuille de travail, et je peux facilement calculer le prix moyen pour chaque vendeur. Comment puis-je créer un joli format qui est automatiquement copié si j’entre de nouvelles données? De plus, existe-t-il un moyen simple de copier automatiquement mes formules lorsque de nouvelles données sont ajoutées?

■■ J’ai inscrit plusieurs années de prix du gaz naturel dans ma feuille de calcul et j’ai créé un joli graphique linéaire montrant la variation mensuelle des prix. Puis-je configurer les choses pour que lorsque j’ajoute de nouvelles données sur le prix du gaz, mon graphique soit automatiquement mis à jour?

■■ Pour chaque transaction de vente, j’ai le vendeur, la date, le produit, le lieu et la taille de la transaction. Puis-je facilement résumer, par exemple, les ventes totales de rouge à lèvres par Jen ou Colleen dans l’Est?

■■ Comment les découpeurs de table (nouveau dans Excel ) nous aident-ils à découper et à découper les données dans un tableau Excel?

■■ Comment puis-je facilement faire référence à des parties d’un tableau dans d’autres parties de mon classeur?

■■ Les formats conditionnels s’appliquent-ils automatiquement aux nouvelles données ajoutées à une table?

Lorsque la plupart d’entre nous utilisent Microsoft Excel, nous entrons souvent de nouvelles données. Ensuite, nous mettons à jour manuellement nos formules, formats et graphiques. Quelle traînée! Maintenant, les capacités du tableau Excel 2013 font de cette corvée une chose du passé.

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début de cet article.

J’ai entré le nombre d’unités vendues et le revenu total pour chaque vendeur dans une feuille de travail, et je peux facilement calculer le prix moyen pour chaque vendeur. Comment puis-je créer un joli format qui est automatiquement copié si j’entre de nouvelles données? De plus, existe-t-il un moyen simple de copier automatiquement mes formules lorsque de nouvelles données sont ajoutées?

Le fichier  dans le dossier Templates de ce chapitre (voir Figure 1) contient des unités

données sur les ventes et les revenus pour chacun des six vendeurs. Vous savez que de nouvelles données seront ajoutées à partir de la ligne 12 et, dans la colonne H, vous souhaitez calculer le prix moyen (Unités / Revenu) généré par chaque vendeur. Vous souhaitez créer un format attrayant pour les données et faire copier automatiquement la formule du prix moyen à mesure que de nouvelles données sont ajoutées.

FIGURE 1:Cette figure montre les données pour créer une table.

En créant un tableau, vos formules et votre mise en forme peuvent être automatiquement mises à jour lorsque vous ajoutez des données. Commencez par sélectionner la plage actuelle de données et d’en-têtes (E5: G11). Cliquez sur Tableau dans l’onglet Insertion ou appuyez sur Ctrl + T. Après avoir vérifié que la case Ma table a des en-têtes est sélectionnée, vous verrez que la plage de table (E5: G11) est magnifiquement formatée. Ce formatage se poursuivra automatiquement chaque fois que de nouvelles données seront entrées dans le tableau. Lorsque vous travaillez dans un tableau, de nombreux styles et options sont disponibles dans l’onglet Conception des outils de tableau. (Voir figure .) Cet onglet n’est visible que lorsque la cellule active se trouve dans un tableau. Vous pouvez sélectionner un style de mise en forme qui sera appliqué aux données de la table actuelle et à toutes les nouvelles données ajoutées à la table.

Notez que les en-têtes de colonne ont des flèches, comme vous pouvez le voir sur la figure . Ces flèches servent de fonctionnalité que vous pouvez utiliser pour trier ou filtrer le tableau (plus d’informations sur le filtrage pour savoir comment résumer facilement, par exemple, le total des ventes de rouge à lèvres par Jen ou Colleen en Orient est abordé plus loin dans ce chapitre).

FIGURE 2: Cette figure montre les options de conception de table.

FIGURE 3: Il s’agit d’un tableau formaté avec des filtres.

Les cellules du tableau sélectionné (à l’exclusion des en-têtes) reçoivent par défaut le nom Table1. Le nom a été changé en Ventes dans le groupe Propriétés de l’onglet Conception. Si vous cliquez sur l’onglet Formules, puis choisissez Gestionnaire de noms, vous pouvez voir que la plage E6: G12 est nommée Ventes. La beauté de ce nom de plage (et du tableau) est que la plage se développe dynamiquement pour inclure de nouvelles lignes ajoutées au bas du tableau et de nouvelles colonnes ajoutées à droite du tableau. A l’article 21, «La fonction DECALER», vous avez utilisé la fonction DECALER pour créer une plage dynamique, mais les nouvelles capacités de table facilitent la création d’une plage dynamique.

Supposons qu’en D15 vous souhaitiez calculer le revenu total. Commencez par taper = SOMME (S;Microsoft Excel vous offre la possibilité de compléter automatiquement la saisie avec la plage du tableau des ventes. Implémentez la saisie semi-automatique en double-cliquant sur le nom de la plage. Vous pouvez également implémenter la saisie semi-automatique en déplaçant le curseur sur Ventes et en appuyant sur la touche Tab. Ensuite, lorsque vous voyez = SOMME (Ventes et tapez un crochet ouvrant ([), la saisie semi-automatique de formule offre la possibilité de compléter la formule avec les en-têtes de colonne du tableau Ventes. (Voir Figure .) Vous pouvez compléter votre formule comme = SUM (Sales [Revenue]) et calculez le revenu total à 155 480 $ (voir la figure .) (Vous verrez un exemple de sélection des entrées dans la zone de saisie semi-automatique qui commencent par des signes numériques [#] lorsque la structure du tableau est discutée plus tard. dans cet article.)

FIGURE 4:Voici les options de saisie semi-automatique pour une table.

Si nouveau  des lignes de données sont ajoutées, les données de ces lignes sont automatiquement prises en compte dans la formule. Pour illustrer cette idée, vous pouvez ajouter de nouvelles données à la ligne 12: Amanda a vendu 400 unités pour 5 000 $. Comme le montre la figure 25-6, les revenus totaux ont augmenté de 5 000 $ pour atteindre 160 480 $.

La mise en forme a également été étendue à la ligne 12 et la formule de revenu total a été mise à jour pour inclure les données d’Amanda. Même si des données sont ajoutées dans le tableau (au lieu d’en bas), tout sera mis à jour de manière cohérente.

FIGURE 5 :Cette figure montre le revenu total pour les données originales.

FIGURE 6: De nouvelles données sont ajoutées au tableau de la ligne 12.

Supposons maintenant que vous souhaitez calculer le prix par unité gagné par chaque vendeur dans la colonne H. Tapez Prix unitaire dans H5 comme en-tête de colonne et, dans la cellule H6, pointez sur la cellule G6. La formule est maintenant [@Revenue]. Tapez une barre oblique (/) et pointez sur la cellule F6 et appuyez sur Entrée. Une chose incroyable se produit. Excel copie automatiquement la formule jusqu’au bas du tableau dans la cellule H12, comme indiqué dans une Figure précédente . Si vous ouvrez une cellule de la colonne H, la formule apparaît sous la forme [@Revenue] / [@ Units]. Bien sûr, = [@ Revenue] / [@ Units] est beaucoup plus facile à comprendre que = G6 / F6. Cette formule peut être interprétée comme divisant ce qui se trouve dans la ligne actuelle de la colonne Revenu par ce qui se trouve dans la ligne actuelle de la cColonne Unités. Si vous ajoutez maintenant de nouvelles données aux trois premières colonnes du tableau, votre formule de prix copier automatiquement vers le bas.

FIGURE 7 : La fonction Table entraîne la copie automatique de la formule de prix unitaire.

Si vous cliquez n’importe où dans un tableau, l’onglet contextuel Outils de tableau apparaît sur le ruban et propose des choix, notamment les suivants:

■■ Changer le nom de la table Peut être utilisé pour renommer une table, comme changer le nom de

Table1 (par défaut) à Sales.

■■ Convertir en plage Convertit la plage du tableau en cellules normales et supprime la structure du tableau.

■■ Redimensionner le tableau Ajoute ou soustrait des lignes et / ou des colonnes à la plage de tableaux définie.

■■ Supprimer les doublons Supprime les lignes contenant des doublons. Par exemple, sélectionner uniquement la colonne Nom dans la boîte de dialogue Supprimer les doublons garantit qu’un nom ne se produira pas plus d’une fois. La sélection des colonnes Nom et Unités garantit qu’aucune ligne du tableau ne correspondra à la fois au Nom et aux Unités et / ou aux autres noms de colonne.

■■ Ligne d’en-tête Si sélectionné, affiche la ligne d’en-tête. Si elle est effacée, la ligne d’en-tête n’est pas affichée.

■■ Ligne totale La ligne totale est discutée lorsque la structure du tableau est expliquée plus loin dans ce chapitre.

■■ Première colonne Si sélectionné, applique un format spécial à la première colonne du tableau.

■■ Dernière colonne Si sélectionné, attribue un format spécial à la dernière colonne du tableau.

■■ Lignes groupées Si sélectionné, donne aux lignes paires du tableau un format différent de celui des lignes impaires.

■■ Colonnes groupées Si cette option est sélectionnée, les colonnes impaires du tableau ont un format différent de celui des colonnes paires.

■■ Styles de tableau Peut être sélectionné à partir de n’importe quel format de tableau indiqué dans ce groupe. Si le tableau se développe ou se contracte, le format sera appliqué de manière cohérente.

J’ai entré plusieurs années de prix du gaz naturel dans ma feuille de calcul et j’ai créé un joli graphique linéaire montrant la variation mensuelle des prix. Puis-je configurer les choses pour que lorsque j’ajoute de nouvelles données sur le prix du gaz, mon graphique soit automatiquement mis à jour?

Dans le fichier , la feuille de calcul d’origine contient les prix du gaz naturel par millier de pieds de juillet 2002 à décembre 2004. (Voir la figure 8.) Comme décrit précédemment, vous pouvez sélectionner B4: C34 (contenant les mois et les prix) et appuyer sur Ctrl + T pour créer un tableau à partir de cette plage, puis vous pouvez créer un graphique linéaire pour afficher ces données en choisissant Ligne dans le groupe Graphiques sous l’onglet Insertion et en sélectionnant le quatrième type de graphique linéaire. Le graphique linéaire déjà créé est illustré à la figure 9.

FIGURE 8 : Voici les données sur les prix de l’essence pour 2002-2004.

Ensuite, vous pouvez copier cette feuille de calcul (en cliquant avec le bouton droit sur le nom de la feuille de calcul, en choisissant Déplacer ou Copier la feuille, puis en sélectionnant Créer une copie) et ajouter les prix de l’essence jusqu’en juillet 2006. (Les données s’étendent maintenant à la ligne 53.) La nouvelle feuille de calcul est nommée Nouvelles données. Notez que le graphique linéaire de cette feuille de calcul est automatiquement mis à jour pour inclure les nouvelles données. (Voir la figure .)

FIGURE 9 : Graphique linéaire des prix du gaz: données 2002-2004.

FIGURE 10 : Voici un graphique linéaire des prix du gaz montrant les données de 2002 à 2006.

Cet exemple montre que si vous basez votre graphique sur une table, de nouvelles données seront automatiquement incluses dans le graphique.

Pour chaque transaction de vente, j’ai le vendeur, la date, le produit, l’emplacement et la taille de la transaction. Puis-je facilement résumer, par exemple, les ventes totales de rouge à lèvres par Jen ou Colleen dans l’Est?

Le fichier du dossier modèle contient les transactions de vente. (Voir figure ci-dessous.) Pour chaque transaction, vous disposez des informations suivantes: numéro de transaction, nom, date, produit, emplacement, dollars et unités vendues. Si vous formatez ces données sous forme de tableau, vous pouvez    n ajoutez une ligne totale pour les colonnes Unités et Dollars, puis utilisez les flèches de filtre pour que la ligne totale inclue le sous-ensemble de transactions souhaité. Pour commencer, placez le curseur n’importe où dans les données et créez un tableau en appuyant sur Ctrl + T. Notez que si vous faites défiler le tableau, la ligne d’en-tête reste visible. Avec le curseur dans le tableau, cochez la case Total Row dans le groupe Options de style de tableau sur l’onglet Conception. Par défaut, Excel saisit le nombre total de lignes du tableau dans la cellule K1895, que vous pouvez supprimer. Après avoir d’abord sélectionné la cellule dans laquelle les flèches apparaissent, cliquez sur les flèches à droite des cellules I1895 et J1895, puis cliquez sur Somme. Cela totalise toutes les entrées dans les colonnes Unités et Dollars du tableau. Ainsi, le revenu total est actuellement de 239 912,67 $ et 78 707 unités ont été vendues. (Voir la figure 12 et le fichier .)

FIGURE  11: Ce tableau présente les données sur les ventes de maquillage.

FIGURE 12: Cette figure montre le total des revenus et des unités vendues.

Pour que les totaux reflètent uniquement les ventes de rouge à lèvres dans l’Est par Ashley ou Hallagan, cliquez sur la flèche dans F3 (à droite de l’en-tête Nom). Désactivez la case à cocher Sélectionner tout pour qu’aucun nom ne soit sélectionné, cochez les cases pour Ashley et Hallagan (voir figure), puis cliquez sur OK. Ensuite, cliquez sur le Flèche du produit, cochez la case Rouge à lèvres, puis cliquez sur la flèche Emplacement et cochez la case Est. Vous voyez maintenant toutes les données correspondant aux critères de filtrage de la figure 14 et dans la feuille de calcul Trancheurs du fichier Tablemakeuptotals.xlsx. Vous verrez qu’Ashley et Hallagan ont vendu 564 unités de rouge à lèvres dans l’Est pour un total de 1 716,56 $. Cette fonction de filtrage des tableaux facilite le calcul des totaux pour tout sous-ensemble de lignes dans une feuille de calcul Excel. Si vous le souhaitez, vous pouvez également copier les lignes impliquant Colleen ou Jen vendant du rouge à lèvres en Orient et les coller ailleurs.

FIGURE 13: Filtrez les noms du tableau.

FIGURE 14: Les sous-totaux sont filtrés pour les unités et les revenus.

Comment les segments de table (nouveau dans Excel 2013) nous aident-ils à découper et à découper les données dans un tableau Excel?

Dans Excel 2010, des segments ont été introduits pour simplifier le filtrage des tableaux croisés dynamiques. (Voir l’article 43, «Utilisation des tableaux croisés dynamiques et des segments pour décrire les données», pour plus de détails.) Dans Excel , les segments peuvent désormais être utilisés pour filtrer les tableaux. L’avantage des trancheuses est que vous pouvez facilement voir les options et la sélection du filtre. Pour créer des filtres pour votre exemple de maquillage, placez votre curseur à l’intérieur du tableau et, dans l’onglet Insertion, sélectionnez Trancheurs dans le groupe Filtres. Pour créer des tranches pour le nom, le produit et l’emplacement, sélectionnez-les comme indiqué dans la figure .

Les trancheuses sont illustrées à la figure . Dans une tranche, vous pouvez filtrer sur plusieurs éléments à l’aide de la touche Maj pour sélectionner les éléments adjacents ou la touche Ctrl pour sélectionner les éléments non adjacents.

Vos trancheuses de la figure 16 filtrent toutes les ventes impliquant Ashley et Hallagan vendant du rouge à lèvres dans l’Est. Notez que la ligne Totaux et les lignes visibles sont identiques à la figure 14. Pour supprimer tous les filtres, cliquez sur l’icône de filtre située dans le coin droit de la tranche.

Vous pouvez redimensionner une tranche en la sélectionnant et en faisant glisser les bords ou les coins. Lorsque vous sélectionnez une tranche, vous pouvez la configurer à l’aide de l’onglet Options des outils de tranche du ruban. Dans cet onglet, vous pouvez modifier de nombreuses fonctionnalités de la tranche, notamment le style de tranche, le nom de la tranche, le nombre de colonnes et la taille de la tranche.

FIGURE 15 : Créez des tranches pour le nom, le produit et l’emplacement.

FIGURE 16 : Utilisez des trancheuses pour filtrer une table.

Comment puis-je facilement faire référence à des parties d’un tableau dans d’autres parties de mon classeur?

Le fichier  montre de nombreux exemples de la façon dont vous pouvez faire référence à des parties d’une table lorsque vous travaillez en dehors de la plage de la table. Ces références sont souvent appelées références structurées. (Voir figure 17.) Lorsque vous entrez un nom de table dans une formule suivie d’un crochet ouvrant ([), la saisie semi-automatique rend les noms de colonne et les spécificités de table suivantes disponibles pour la sélection:

■■ Nom du tableau Toutes les cellules du tableau, à l’exception de l’en-tête et du nombre total de lignes.

■■ #Toutes toutes les cellules du tableau, y compris la ligne totale (le cas échéant).

■■ #Data Toutes les cellules du tableau sauf la première ligne et la ligne totale.

■■ #Headers Juste la ligne d’en-tête.

■■ #Totals Juste la ligne totale. S’il n’y a pas de ligne totale, cela renvoie une plage de cellules vide.

■■ #Cette ligne Toutes les entrées du tableau de la ligne actuelle.

Une référence de colonne inclut toutes les cellules d’une colonne de tableau, à l’exception de l’en-tête et du total des entrées de ligne (si seulement).

Voici quelques exemples d’utilisation des spécificateurs de table dans les formules:

■■ Dans la cellule B8, la formule = SOMME(Table1 [@]) additionne les entrées de la ligne 8 (41 + 28 + 49 + 40).

■■ Dans la cellule C15, la formule = NBVAL (Table1 [#All]) donne 55 car le tableau contient 55 entrées.

■■ Dans la cellule C16, la formule = NBVAL ([Tableau1]) donne 45 car l’en-tête et le total des lignes ne sont pas comptés. Dans la cellule C17, la formule = NBVAL (tableau [#data]) donne 45 car la plage de cellules D5: H13 est référencée.

■■ In   ll C18, la formule = NBVAL (Table1 [#Headers]) donne 5 car seule la ligne d’en-tête

(D4: H4) est référencé.

■■ Dans la cellule C19, la formule = SOMME (Table1 [Q1]) donne 367 car la formule additionne les entrées dans

E5: E13.

■■ Dans la cellule C20, la formule = SOMME (Table1 [#Totals]) résume toutes les entrées de la ligne totale et donne 1 340, qui est la somme totale de toutes les entrées de table.

■■ Dans la cellule C21, la formule = SOMME (Table1 [[# Data], [Q1]: [Q3]]) résume toutes les entrées de données qui se trouvent dans les colonnes Q1: Q3, inclusivement (cellules E5: G13). Ainsi, les noms de colonne séparés par deux points incluent toutes les entrées de données entre et incluant le nom de colonne avant les deux points et le nom de colonne après les deux points.

Bien sûr, toutes ces formules sont automatiquement mises à jour si de nouvelles données sont ajoutées au tableau.

FIGURE 17 : Ce tableau présente des références structurées.

Les formats conditionnels s’appliquent-ils automatiquement aux nouvelles données ajoutées à une table? Oui, les formats conditionnels incluent automatiquement les nouvelles données de table. (Voir figure ci-dessous.) Pour illustrer, un format conditionnel a été placé dans la feuille de calcul Original du fichier pour mettre en évidence les trois ventes les plus importantes du T1 dans la colonne E. Les entrées des lignes 7, 12 et 13 ont été mises en évidence . Dans la feuille de calcul Ajouter Biggersale, l’entrée 90 a été ajoutée dans la cellule E14. Cela devient la plus grande entrée de la colonne et est immédiatement mis en surbrillance. La cellule E7 n’est plus mise en surbrillance car elle n’est plus l’un des trois plus grands nombres de la colonne E du tableau.

FIGURE 18: Le formatage conditionnel s’étend automatiquement aux nouvelles données de table.