本书提供所有数据集以实践书中概念，教你如何创建和使用数据透视表。了解数据透视表的数据源规则，掌握创建方法。通过快捷键启动数据透视表，并学习如何操作字段列表。探讨如何根据需求选择和放置字段，理解Excel根据数据类型自动分配字段区域的原理。此外，学习刷新数据、处理混合数据类型以及共享数据源的影响。还涵盖了调整默认设置、处理空白单元格、创建计算字段和项、更改布局以及处理多个透视表的技巧。
About the companion content
The download files for this book include all of the data sets used to produce the book, so you can practice the concepts in the book. You can download this book’s companion content from the following page:
MicrosoftPressStore.com/Excel2019PivotTable/downloads
Pivot tables have only one hard rule pertaining to the data source: The data source must have column headings, which are labels in the first row of the data that describe the information in each column. Without column headings, you cannot create a pivot table report.
Originally called Power Query was later rebranded as Get & Transform Data.
You can also press the shortcut to start a pivot table: Press and release Alt, press and release N, and then press and release V.
Note the presence of another option in the Create PivotTable dialog box shown in Figure 2-13: the Add This Data To The Data Model option. You would select this option if you were trying to consolidate multiple data sources into a single pivot table.
If clicking on the pivot table does not activate the PivotTable Fields list, you can manually activate it by right-clicking anywhere inside the pivot table and selecting Show Fields list. You can also click anywhere inside the pivot table and then choose the large Fields List icon on the Analyze tab under PivotTable Tools in the ribbon.
Before you start dropping fields into the various areas, answer two questions:
• “What am I measuring?”
• “How do I want to see it?”
The answer to the first question tells you which fields in your data source you need to work with, and the answer to the second question tells you where to place the fields.
How Does Excel Know Where Your Fields Go?
As you’ve just experienced, the PivotTable Fields list interface enables you to add fields to your pivot table by simply selecting the check box next to each field name. Excel automatically adds the selected fields to the pivot table. But how does Excel know which area to use for a field you select? The answer is that Excel doesn’t really know which area to use, but it makes a decision based on data type. Here’s how it works: When you select a check box next to a field, Excel evaluates the data type for that field. If the data type is numeric, Excel places the field into the Values area; otherwise, Excel places the field into the Rows area. This placement obviously underlines the importance of correctly assigning the data types for your fields.
If you have someone who clears out cells by mashing down the spacebar several times instead of using the Delete key, Excel will treat those spaces as text. A mix of numbers and text in a column will cause Excel to default to counting the column instead of summing.
The action of updating your pivot cache by taking another snapshot of your data source is called refreshing your data. There are two reasons you might have to refresh your pivot table report:
• Changes have been made to your existing data source.
• Your data source’s range has been expanded with the addition of rows or columns.
Note that if you format your pivot table source data as a table using Home, Format As Table or Ctrl+T, the Pivot Table Source range will automatically expand as the data grows. You will still have to click Refresh to pick up the new rows.
In legacy versions of Excel, when you created a pivot table using a data set that was already being used in another pivot table, Excel actually gave you the option of using the same pivot cache. However, Excel 2019 does not give you such an option.
Instead, each time you create a new pivot table in Excel 2019, Excel automatically shares the pivot cache. Most of the time, this is beneficial: You can link as many pivot tables as you want to the same pivot cache with a negligible increase in memory and file size.
On the flip side, when you group one pivot table by month and year, all the pivot tables are grouped in a similar fashion. If you want one pivot table by month and another pivot table by week, you have to force a separate pivot cache. You can force Excel to create a separate pivot cache by taking the following steps:
1. Press Alt+D, release, and then press P to launch the PivotTable Wizard.
2. Click the Next button to get past the first screen of the wizard.
3. On the second screen, select the range for your pivot table and click the Next button.
4. Excel displays a wordy message saying that you can use less memory if you click Yes. Instead, click No.
5. On the next screen, click the Finish button.
At this point, you have a blank pivot table that pulls from its own pivot cache.
It’s important to note that there are a few side effects to sharing a pivot cache. For example, suppose you have two pivot tables using the same pivot cache. Certain actions affect both pivot tables:
• Refreshing your data—You cannot refresh one pivot table and not the other. Refreshing affects both tables.
• Adding a calculated field—If you create a calculated field in one pivot table, your newly created calculated field shows up in the PivotTable Fields list of the other pivot table.
• Adding a calculated item—If you create a calculated item in one pivot table, it shows in the other as well.
• Grouping or ungrouping fields—Any grouping or ungrouping you perform affects both pivot tables. For instance, suppose you group a date field in one pivot table to show months. The same date field in the other pivot table is also grouped to show months.
Although none of these side effects are critical flaws in the concept of sharing a pivot cache, it is important to keep them in mind when determining whether using a pivot table as your data source is the best option for your situation.
If you find yourself always making the same changes to a pivot table, consider making that change in the pivot table defaults.
Follow these steps to change this setting for the current pivot table:
1. Right-click any cell in the pivot table and choose PivotTable Options.
2. On the Layout & Format tab in the Format section, type 0 next to the field labeled For Empty Cells Show (see Figure 3-5).
FIGURE 3-5 Enter a zero in the For Empty Cells Show box to replace the blank cells with zero.
1. Click OK to accept the change.
The result is that the pivot table is filled with zeros instead of blanks.
When you attempt to expand the innermost field, Excel offers to add a new innermost field.
Caution
When you arrange several pivot tables vertically, as in Figure 3-19, you’ll notice that changes in one pivot table change the column widths for the entire column, often causing #### to appear in the other pivot tables. By default, Excel changes the column width to AutoFit the pivot table but ignores anything else in the column. To turn off this default behavior, right-click each pivot table and choose PivotTable Options. In the first tab of the Options dialog box, the second-to-last check box is AutoFit Column Widths On Update. Clear this check box.
As you’ve seen in these pages, I rarely use the Compact form for a pivot table. I use Pivot Table Defaults to make sure my pivot tables start in Tabular layout instead of Compact layout.
Although slicers are now the darlings of the pivot table report, the good old-fashioned report filter can still do one trick that slicers cannot do. Say you have created a report that you would like to share with the industry managers. You have a report showing customers with revenue and profit. You would like each industry manager to see only the customers in their area of responsibility.
Follow these steps to quickly replicate the pivot table:
1. Make sure the formatting in the pivot table looks good before you start. You are about to make several copies of the pivot table, and you don’t want to format each worksheet in the workbook, so double-check the number formatting and headings now.
2. Add the Sector field to the Filters area. Leave the Sector filter set to (All).
3. Select one cell in the pivot table so that you can see the Analyze tab in the ribbon.
4. Find the Options button in the left side of the Analyze tab. Next to the Options tab is a drop-down menu. Don’t click the big Options button. Instead, open the drop-down menu (see Figure 4-31).
FIGURE 4-31 Click the tiny drop-down arrow next to the Options button.
5. Choose Show Report Filter Pages. In the Show Report Filter Pages dialog box, you see a list of all the fields in the report area. Because this pivot table has only the Sector field, this is the only choice (see Figure 4-32).
FIGURE 4-32 Select the field by which to replicate the report.
6. Click OK and stand back.
Excel inserts a new worksheet for every item in the Sector field. On the first new worksheet, Excel chooses the first sector as the filter value for that sheet. Excel renames the worksheet to match the sector. Figure 4-33 shows the new Consulting worksheet, with neighboring tabs that contain Museums, Retail, Training, and Utilities.
FIGURE 4-33 Excel quickly adds one page per sector.
If you have daily dates that include an entire year or that fall in two or more years, Excel 2019 groups the daily dates to include years, quarters, and months. If you need to report by daily dates, you will have to select any date cell, choose Group Field, and add Days.
A calculated field is a data field you create by executing a calculation against existing fields in the pivot table. Think of a calculated field as a virtual column added to your data set. This column takes up no space in your source data, contains the data you define with a formula, and interacts with your pivot data as a field—just like all the other fields in your pivot table.
A measure is a calculated field created in a Data Model pivot table using the Data Analysis Expressions (DAX) formula language.
A calculated item is a data item you create by executing a calculation against existing items within a data field. Think of a calculated item as a virtual row of data added to your data set. This virtual row takes up no space in your source data and contains summarized values based on calculations performed on other rows in the same field. Calculated items interact with your pivot data as data items—just like all the other items in your pivot table.
The order of operations for Excel is as follows:
• Evaluate items in parentheses.
• Evaluate ranges (😃.
• Evaluate intersections (spaces).
• Evaluate unions (,).
• Perform negation (–).
• Convert percentages (%).
• Perform exponentiation (^).
• Perform multiplication () and division (/), which are of equal precedence.
• Perform addition (+) and subtraction (–), which are of equal precedence.
• Evaluate text operators (&).
• Perform comparisons (=, <>, <=, >=).
Here is another widely demonstrated example. If you enter 10^2, which represents the exponent 10 to the second power as a formula, Excel returns 100 as the answer. If you enter –10^2, you expect –100 to be the result, but instead Excel returns 100 yet again. The reason is that Excel performs negation before exponentiation, which means Excel converts 10 to –10 before doing the exponentiation, effectively calculating –10–10, which indeed equals 100. When you use parentheses in the formula, –(10^2), Excel calculates the exponent before negating the answer, giving you –100.
When you build calculated fields or calculated items, Excel enables you to use any worksheet function that accepts numeric values as arguments and returns numeric values as the result. Some of the many functions that fall into this category are COUNT, AVERAGE, IF, AND, NOT, and OR.
Some examples of functions you cannot use are VLOOKUP, INDEX, SUMIF, COUNTIF, LEFT, and RIGHT. Again, these are all impossible to use because they either require cell array references or return textual values as the result.
If you long for the days when pivot charts were located on their own chart sheets, you are in luck. All you have to do is place your cursor inside a pivot table and then press F11 to create a pivot chart on its own sheet.
When creating a pivot chart, Excel ignores subtotals and grand totals.
In a pivot chart, both the x-axis and y-axis correspond to specific areas in your pivot table:
• y-axis—Corresponds to the column area in a pivot table and makes up the y-axis of a pivot chart.
• x-axis—Corresponds to the row area in a pivot table and makes up the x-axis of a pivot chart.
A primary key is a field that contains only unique non-null values (no duplicates or blanks). Primary key fields are necessary in the Data Model to prevent aggregation errors and duplications. Every relationship you create must have a field designated as the primary key.
This process of extracting, manipulating, and integrating data is called ETL. ETL refers to the three separate functions typically required to integrate disparate data sources: extraction, transformation, and loading. The extraction function involves reading data from a specified source and extracting a desired subset of data. The transformation function involves cleaning, shaping, and aggregating data to convert it to the desired structure. The loading function involves actually importing or using the resulting data.
• Merge—Merge the selected query with another query in the workbook by matching specified columns.
• Append—Append the results of another query in the workbook to the selected query.
Why are cube formulas more powerful? The GETPIVOTDATA function refers to an existing pivot table and extracts cells that already exist in the pivot table to another location. In contrast, cube formulas can completely replace a pivot table. Cube formulas reach into the data store and pull summary data into Excel even if you don’t have that data in a pivot table yet.
=CUBESET("ThisWorkbookDataModel",
"[CustData].[Customer Name].children",
"All Customers",
2,
"[Measures].[Sum of Revenue]")
Even if you know and love VLOOKUP, when you get to a data set with 100,000 records and lookup table with 12 columns, you don’t want to build 1,200,000 VLOOKUP formulas to join those data sets together. Power Pivot and the Data Model scales perfectly.
Power Pivot and Power Query are powerful tools that are completely new for most Excellers. These are my favorite books to learn more:
• For Power Query, read M Is For Data Monkey by Ken Puls and Miguel Escobar (ISBN 978-1-61547-034-1).
• For DAX formulas in Power Pivot, read Supercharge Excel by Matt Allington (ISBN 978-1-61547-053-2).
• For Advanced Power Pivot, read Power Pivot and Power BI by Rob Collie (ISBN 978-1-61547-039-6).
You can build a macro in an .xlsx workbook, but it won’t be saved with the workbook.
You have several options for saving workbooks that enable macros:
• Excel Macro-Enabled Workbook (.xlsm)—This uses the XML-based method for storing workbooks and enables macros. I prefer this file type because it is compact and less prone to becoming corrupt.
• Excel Binary Workbook (.xlsb)—This is a binary format and always enables macros.
• Excel 97-2003 Workbook (.xls)—Although this legacy file type supports macros, it doesn’t support a lot of handy newer features. You lose access to slicers, new filters, rows 65537 through 1048576, columns IW through XFD, and other pivot table improvements.
From Excel, press Alt+F11 to open the Visual Basic Editor
Excel Help—For assistance on any keyword, put the cursor in the keyword and press F1. Excel Help displays a help topic regarding the keyword.
Understanding object-oriented code
VBA is an object-oriented language. Most lines of VBA code follow the Noun.Verb syntax. However, in VBA, it is called Object.Method. Examples of objects are workbooks, worksheets, cells, and ranges of cells. Methods can be typical Excel actions such as .Copy, .Paste, and .PasteSpecial.
Many methods allow adverbs—parameters you use to specify how to perform the method. If you see a construct with a colon and an equal sign (:=), you know that the macro recorder is describing how the method should work.
You also might see code in which you assign a value to the adjectives of an object. In VBA, adjectives are called properties. If you set ActiveCell.Value = 3, you are setting the value of the active cell to 3. Note that when you are dealing with properties, there is only an = (equal sign), not a := (colon and equal sign).
The macro recorder uses syntax such as Range(“H12”) to refer to a cell. However, it is more flexible to use Cells(12, 8) to refer to the cell in row 12, column 8. Similarly, the macro recorder refers to a rectangular range as Range(“A1:L87601”). However, it is more flexible to use the Cells syntax to refer to the upper-left corner of the range and then use the Resize() syntax to refer to the number of rows and columns in the range. The equivalent way to describe the preceding range is Cells(1, 1).Resize(87601,12). This approach is more flexible because you can replace any of the numbers with a variable.
In the Excel user interface, you can use Ctrl+any arrow on the keyboard to jump to the edge of a range of data. If you move the cell pointer to the final row on the worksheet and press the Ctrl+up arrow key, the cell pointer jumps to the last row with data. The equivalent of doing this in VBA is to use the following code:
Range("A1048576").End(xlUp).Select
You do not need to select this cell; you just need to find the row number that contains the last row. The following code locates this row and saves the row number to a variable named FinalRow:
FinalRow = Range("A1048576").End(xlUp).Row
There is nothing magical about the variable name FinalRow. You could call this variable x, y, or even your dog’s name. However, because VBA enables you to use meaningful variable names, you should use something such as FinalRow to describe the final row:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
You can also find the final column in a data set. If you are relatively sure that the data set begins in row 1, you can use the End key in combination with the left-arrow key to jump from cell XFD1 to the last column with data. To generalize for the possibility that the code is running in legacy versions of Excel, you can use the following code:
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
If you have ever had to use the arcane formula =A2+1-Day(A2) to change daily dates into monthly dates, you will appreciate the ease with which you can group transactional data into months or quarters.
So far in this chapter, you have been using PT.TableRange2 when copying the data from the pivot table. The TableRange2 property includes all rows of the pivot table, including the page fields.
There is also a TableRange1 property, which excludes the page fields.
You can rearrange data items in your pivot table manually by simply typing the exact name of the data item where you would like to see its data. You can also drag the data item where you want it.
You can delete your source data, and your pivot table will function just fine. After you delete the source data, when you save the pivot table, the file shrinks. Your clients can use the pivot table as normal, and your workbook is half as big. The only functionality you lose is the ability to refresh the pivot data because the source data is not there.
What happens if your clients need to see the source data? Well, they can simply double-click the intersection of the row and column grand totals. This tells Excel to output the contents of the pivot table’s cache into a new worksheet. So, with one double-click, your clients can re-create the source data that makes up the pivot table!
Tip 11: Compare tables using a pivot table
If you’ve been an analyst for more than a week, you’ve been asked to compare two separate tables to come up with some brilliant analysis about the differences between them. This is a common scenario where leveraging a pivot table can save you some time.
Tip 20: Use percentage change from previous for year-over-year
扫一扫
5054
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
