Let’s say we have a built pivot table with the results of analyzing sales by months for different cities (if necessary, read this article to understand how to create them in general or refresh your memory):
We want to slightly change its appearance so that it displays the data you need more clearly, and not just dumping a bunch of numbers on the screen. What can be done for this?
Other calculation functions instead of the banal amount
If you right-click on the calculated field in the data area and select the command from the context menu פעלד אָפּציעס (Field Settings) or in Excel 2007 version – ווערט פעלד אָפּציעס (Value Field Settings), then a very useful window will open, using which you can set a bunch of interesting settings:
In particular, you can easily change the field calculation function to mean, minimum, maximum, etc. For example, if we change the sum to the quantity in our pivot table, then we will see not the total revenue, but the number of transactions for each product:
By default, Excel always automatically selects summation for numeric data. (Sum), and for non-numeric ones (even if out of a thousand cells with numbers there is at least one empty one or with text or with a number in text format) – a function for counting the number of values (ציילן).
If you want to see in one pivot table at once the average, the amount, and the quantity, i.e. several calculation functions for the same field, then feel free to throw the mouse into the data area of the field you need several times in a row to get something similar:
…and then set different functions for each of the fields by clicking on them in turn with the mouse and choosing the command פעלד אָפּציעס (Field settings)to end up with what you want:
If in the same window פעלד אָפּציעס דריקט דעם קנעפּל אַדישנאַלי (אָפּציעס) or go to the tab Additional Calculations (in Excel 2007-2010), then the drop-down list will become available Additional Calculations (Show data as):
In this list, for example, you can select options Percentage of line amount (% of row), Percentage of total by column (% of column) or Share of the total (% of total)to automatically calculate percentages for each product or city. This is how, for example, our pivot table will look like with the function enabled Percentage of total by column:
Sales dynamics
If in the drop down list Additional Calculations (Show data as) choose option דיסטינגקשאַן (די חילוק), and in the lower windows פעלד (Base field) и עלעמענט (Base item) קלייַבן מאָנאַט и צוריק (in the native English version, instead of this strange word, there was a more understandable פריער, those. previous):
…then we get a pivot table that shows the differences in sales of each next month from the previous one, i.e. – sales dynamics:
And if you replace דיסטינגקשאַן (די חילוק) on Given difference (% of difference) און לייגן קאַנדישאַנאַל פאָרמאַטטינג to highlight negative values in red, we get the same thing, but not in rubles, but as a percentage:
PS
In Microsoft Excel 2010, all of the above calculation settings can be done even easier – by right-clicking on any field and selecting commands from the context menu גאַנץ פֿאַר (סאַמערייז וואַלועס דורך):
… און Additional Calculations (Show Data as):
Also in the version of Excel 2010, several new functions were added to this set:
- % of total by parent row (column) – allows you to calculate the share relative to the subtotal for a row or column:
In previous versions, you could only calculate the proportion relative to the grand total.
- % of the amount cumulative – works similarly to the cumulative sum function, but displays the result as a fraction, i.e. in percents. It is convenient to calculate, for example, the percentage of the plan or budget execution:
- Sorting from smallest to largest and vice versa – a slightly strange name for the ranking function (RANK), which calculates the ordinal number (position) of an element in the general list of values. For example, with its help it is convenient to rank managers by their total revenue, determining who is in what place in the overall standings:
- וואָס זענען דרייפּונקט טישן און ווי צו בויען זיי
- גרופּינג נומערן און דאַטעס מיט די געבעטן שריט אין דרייפּונקט טישן
- בויען אַ פּיוואָטטאַבלע באריכט אויף קייפל ריינדזשאַז פון מקור דאַטן