Master Excel Tables with AutoFilter and Subtotal Master avec les tableaux Excel AutoFilter et sous-total

Posted on April 9, 2008 at 5:23 am Publié le 9 avril 2008 à 5:23 am

How much of Microsoft Excel are you using? Combien de Microsoft Excel utilisez-vous? I thought so. Je pensais. Get ready because we’re stepping it up another notch. Excel is a powerful but often underutilized tool. Préparez-vous parce que nous sommes renforcer la place un cran. Excel est un puissant, mais souvent sous-outil. Many of us use it every day to plan and track budgets, analyze marketing data, and occasionally to do more important things such as manage the office March Madness pool. Beaucoup d'entre nous utilisent tous les jours de planifier et de suivre les budgets, d'analyser les données de mise sur le marché et, à l'occasion de faire des choses plus importantes telles que gérer le bureau Mars Madness piscine. Several Online Tech Tips posts have already covered clever but unfamiliar functions like Plusieurs Conseils techniques en ligne postes ont déjà couverts intelligent mais peu familier des fonctions comme VLOOKUP VLOOKUP and et SUMIF SUMIF . Now it’s time for another round of Outrageous Excel Tips! Maintenant il est temps pour une nouvelle série de Conseils Outrageous Excel!

I’m going to illustrate two Excel features which, used in concert, make tabular analysis a snap. Je vais illustrer Excel deux caractéristiques qui, utilisé de concert, faire des tableaux d'analyse, une rupture. Tabular data is what you get from a database extract or CSV file. Sous forme de tableaux de données est ce que vous obtenez une base de données de l'extrait ou un fichier CSV. The AutoFilter and oft-overlooked Subtotal functions, like peanut butter and chocolate, are meant to be used together. They provide an easy way to “code” a spreadsheet to do subsets, sums, averages, counts, and more. Le AutoFilter et souvent négligée Total partiel fonctions, comme le beurre d'arachide et de chocolat, sont destinées à être utilisées en même temps. Ils offrent un moyen facile de "code" une feuille de calcul à faire des sous, des sommes, des moyennes, des chefs, et plus encore.

The Excel table below is an extract of our company’s purchases by department. Le tableau Excel ci-dessous est un extrait de notre entreprise achats par département. We have a typical “header” row, followed by 11 data rows. Nous avons un type "tête" ligne, suivi par 11 lignes de données. You can see the functions I included for this table: Sum, Count, Min and Average. Vous pouvez voir les fonctions J'ai inclus pour ce tableau: Somme, Count, Min et Moyenne. More about them later. Plus d'informations à leur sujet plus tard.

Now, the boss comes in and needs to see just how much advertising airtime the Marketing people are purchasing, and how much we are paying. Maintenant, est le patron et a besoin de voir à quel point la publicité du temps d'antenne de mise sur le marché sont des personnes d'achat, et combien nous payons. AutoFilter gives us the power to make entire rows visible or invisible based on cell contents. AutoFilter nous donne le pouvoir de faire tout lignes visibles ou invisibles basé sur le contenu des cellules. The Subtotal function updates the Sum, Count, Min and Average accordingly. Le Sous-total fonction met à jour la Somme, Count, Min et Moyenne en conséquence. Let’s quickly add an AutoFilter. Nous allons ajouter rapidement un AutoFilter.

Excel AutoFilter Excel AutoFilter

To add AutoFilter, select the entire range of cells, including the header row containing the column labels (A1:F12). Pour ajouter AutoFilter, sélectionnez toute la gamme de cellules, y compris la ligne d'en-tête contenant la colonne des étiquettes (A1: F12). Then use Data -> Filter -> AutoFilter. Ensuite, l'utilisation de données -> Filtre -> AutoFilter. Now you can see the filter indicator above each column in the table: Maintenant vous pouvez voir le filtre indicateur au-dessus de chaque colonne dans le tableau:

The neat thing is this: click on any column heading, and AutoFilter gives you a drop-down list of values in the column for you to select from, and filters the entire table for the result: La chose est soigné: cliquez sur n'importe quel titre de colonne, et AutoFilter vous donne une liste déroulante des valeurs dans la colonne, vous pouvez sélectionner un, et les filtres tout le tableau pour le résultat:

The lists are dynamic based on table contents, so adding a row or changing an existing value means the drop-down list is updated also. Les listes sont dynamiques d'après le tableau contenu, afin d'ajouter une ligne ou de modifier une valeur existante, la liste déroulante est également mis à jour. You can show all values (default), select just one value, or create a custom filter based on boolean logic. Vous pouvez afficher toutes les valeurs (par défaut), sélectionnez une juste valeur, ou créer un filtre basé sur la logique booléenne. In this example we need to select the “Airtime” value. Dans cet exemple, nous avons besoin pour sélectionner le "temps d'antenne".

The result is: Le résultat est:

AutoFilter acts by making rows visible and invisible. AutoFilter actes en lignes visibles et invisibles. The original eleven data rows are still present, although some are invisible based on the selected filter. À l'origine, onze lignes de données sont toujours présents, bien que certaines sont invisibles basé sur le filtre sélectionné. A2:F12 is still data range. A2: F12 est encore éventail de données.

We quickly see the two “Airtime” buys in this dataset, and the average price paid is $5000. Nous avons rapidement voir les deux "temps d'antenne" achète dans cet ensemble de données, et le prix moyen payé est de $ 5000. Use the drop down filters to select all values again, or select a different value, and do it on any number of columns. Utilisez le menu déroulant des filtres pour sélectionner toutes les valeurs de nouveau, ou choisissez une autre valeur, et de le faire sur n'importe quel nombre de colonnes. Depending on your table, some columns will make sense for filtering and others may not. En fonction de votre table, quelques colonnes de sens pour le filtrage mai et d'autres pas.

Excel Subtotals Excel sous-totaux

In this example, the values for Sum, Count, Min, and Average update based on the filter automatically because of the powerful Subtotalfunction Subtotalfunction. Dans cet exemple, les valeurs de Somme, Count, Min, Moyenne et mise à jour basée sur le filtre automatiquement en raison de la puissante Subtotalfunction Subtotalfunction. Subtotal operates only on visible rows of data in a range, so it’s great in combination with AutoFilters. Total partiel fonctionne seulement visibles sur les lignes de données dans une gamme, il est grand en combinaison avec AutoFilters. Subtotal is also simple to set up. The syntax for Subtotal is: Total partiel est également simple à mettre en place. La syntaxe de sous-total est:

=Subtotal(function#, range) = Sous-total (# fonction, étendue)

Okay, you do have to know the “function #” which is a pain. Are you listening MicroSoft? Bon, vous devez connaître la fonction # "qui est une douleur. Êtes-vous en écoutant Microsoft? Here is the list of commonly used functions with Subtotal. Voici la liste des fonctions couramment utilisées avec Sous-total. Others can be accessed by searching Excel Help: D'autres peuvent être accessibles par la recherche Aide Excel:

Function # Fonction #	Function Name Nom Fonction
101	AVERAGE MOYENNE
102	COUNT
104	MAX
105	MIN
109	SUM SOMME

In the example the Sum, Count, Min and Averages are obtained by these respective formulae: Dans cet exemple, la Somme, Count, Min et moyennes sont obtenues par ces différentes formules:

SUM =Subtotal(109,F2:F12) SUM = Sous-total (109, F2: F12)

COUNT =Subtotal(102,F2:F12) COUNT = Sous-total (102, F2: F12)

MIN =Subtotal(105,F2:F12) MIN = Sous-total (105, F2: F12)

AVERAGE =Subtotal(101,F2:F12) MOYENNE = Sous-total (101, F2: F12)

Now that you have seen the power of AutoFilter and Subtotal , go give it a shot. Maintenant que vous avez vu la puissance de AutoFilter et Total partiel, rendez-vous de lui donner un coup de feu. After all, Fantasy-League baseball is just starting. Après tout, Fantasy-Ligue de baseball ne fait que commencer.

Pete Kushmeider, Writer and blogger Pete Kushmeider, écrivain et blogger

Technorati Tags: Technorati Tags: Excel , Data Analysis Analyse de données