- ホーム
- > 洋書
- > 英文書
- > Computer / Spreadsheets
Full Description
CRUNCH ANY DATA, FROM ANY SOURCE, QUICKLY AND EASILY, WITH EXCEL 2013 PIVOT TABLES! Use Excel 2013 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours... understand exactly what's going on in your business... take control, and stay in control!Even if you've never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power. In just the first seven chapters, you learn how to generate complex pivot reports complete with drill-down capabilities and accompanying charts. Then, you go even further, discovering how to build a comprehensive, dynamic pivot table reporting system for any business task or function.Learning advanced pivot table and pivot chart techniques for Excel 2013 or the newest Office 365 has never been easier. You'll find simple, step-by-step instructions, real-world case studies, even complete, easy recipes for solving your most common business analysis problems.* Create, customize, and change your pivot tables and pivot charts* Transform gigantic data sets into crystal-clear summary reports* Summarize and analyze data even faster with new Excel 2013 recommended pivot tables* Instantly highlight your most (and least) profitable customers, products, or regions* Quickly filter pivot tables using slicers* Use dynamic dashboards using Power View to see exactly where your business stands right now* Revamp analyses on the fly by simply dragging and dropping fields* Build dynamic self-service reporting systems your entire team can use* Use PowerPivot or the Data Model to create pivot tables from multiple data sources and worksheets* Work with and analyze OLAP data, and much moreAbout MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will* Dramatically increase your productivity-saving you 50 hours a year or more* Present proven, creative strategies for solving real-world problems * Show you how to get great results, no matter how much data you have* Help you avoid critical mistakes that even experienced users makeCATEGORY: SpreadsheetsCOVERS: Microsoft Office Excel 2013
Contents
IntroductionChapter 1 Pivot Table FundamentalsWhat Is a Pivot Table?Why Should You Use a Pivot Table?When Should You Use a Pivot Table?The Anatomy of a Pivot TableValues AreaRows AreaColumns AreaFilters AreaPivot Tables Behind the ScenesLimitations of Pivot Table ReportsA Word About CompatibilityNext StepsChapter 2 Creating a Basic Pivot TableEnsure Your Data Is in a Tabular LayoutAvoid Storing Data in Section HeadingsAvoid Repeating Groups as ColumnsEliminate Gaps and Blank Cells in Your Data SourceApply Appropriate Type Formatting to Your FieldsSummary of Good Data Source DesignCreating a Basic Pivot TableAdding Fields to the ReportAdding Layers to Your Pivot TableRearranging Your Pivot TableCreating a Report FilterUnderstanding the Recommended PivotTables FeatureUsing SlicersCreating a Standard SlicerCreating a Timeline SlicerKeeping Up with Changes in Your Data SourceChanges Have Been Made to Your Existing Data SourceYour Data Source's Range Has Been Expanded with the Addition of Rows or ColumnsSharing the Pivot CacheSaving Time with New Pivot Table ToolsDeferring Layout UpdatesStarting Over with One ClickRelocating Your Pivot TableNext StepsChapter 3 Customizing a Pivot TableMaking Common Cosmetic ChangesApplying a Table Style to Restore GridlinesChanging the Number Format to Add Thousands SeparatorsReplacing Blanks with ZerosChanging a Field NameMaking Report Layout ChangesUsing the New Compact LayoutUsing the Outline Form LayoutUsing the Traditional Tabular LayoutControlling Blank Lines, Grand Totals, and Other SettingsCustomizing the Pivot Table Appearance with Styles and ThemesCustomizing a StyleModifying Styles with Document ThemesChanging Summary CalculationsUnderstanding Why One Blank Cell Causes a CountUsing Functions Other Than Count or SumAdding and Removing SubtotalsSuppress Subtotals When You Have Many Row FieldsAdding Multiple Subtotals for One FieldChanging the Calculation in a Value FieldShowing Percentage of TotalUsing % Of to Compare One Line to Another LineShowing RankTracking Running Total and Percent of Running TotalDisplay Change from a Previous FieldTracking Percent of Parent ItemTrack Relative Importance with the Index OptionNext StepsChapter 4 Grouping, Sorting, and Filtering Pivot DataGrouping Pivot FieldsGrouping Date FieldsIncluding Years When Grouping by MonthsGrouping Date Fields by WeekUngroupingGrouping Numeric FieldsUsing the PivotTable Fields ListDocking and Undocking the PivotTable Fields ListRearranging the PivotTable Fields ListUsing the Areas Section Drop-DownsSorting in a Pivot TableSorting Customers into High-to-Low Sequence Based on RevenueUsing a Manual Sort SequenceUsing a Custom List for SortingFiltering the Pivot Table: An OverviewUsing Filters for Row and Column FieldsFiltering Using the Check BoxesFiltering Using the Search BoxFiltering Using the Label FiltersFiltering a Label Column Using Information in a Values ColumnCreating a Top-Five Report Using the Top 10 FilterFiltering Using the Date Filters in the Label Drop-DownFiltering Using the Filters AreaAdding Fields to the Filters AreaChoosing One Item from a FilterChoosing Multiple Items from a Report FilterReplicating a Pivot Table Report for Each Item in a FilterFiltering Using Slicers and TimelinesUsing Timelines to Filter by DateDriving Multiple Pivot Tables from One Set of SlicersNext StepsChapter 5 Performing Calculations Within Your Pivot TablesIntroducing Calculated Fields and Calculated ItemsMethod 1: Manually Add the Calculated Field to Your Data SourceMethod 2: Use a Formula Outside Your Pivot Table to Create the Calculated Field5Method 3: Insert a Calculated Field Directly into Your Pivot TableCreating Your First Calculated FieldCreating Your First Calculated ItemUnderstanding the Rules and Shortcomings of Pivot Table CalculationsRemembering the Order of Operator PrecedenceUsing Cell References and Named RangesUsing Worksheet FunctionsUsing ConstantsReferencing TotalsRules Specific to Calculated FieldsRules Specific to Calculated ItemsManaging and Maintaining Your Pivot Table CalculationsEditing and Deleting Your Pivot Table CalculationsChanging the Solve Order of Your Calculated ItemsDocumenting Your FormulasWhat's NextChapter 6 Using Pivot Charts and Other VisualizationsWhat Is a Pivot Chart...Really?Creating Your First Pivot ChartKeeping Pivot Chart Rules in MindChanges in the Underlying Pivot Table Affect Your Pivot ChartThe Placement of Data Fields in Your Pivot Table Might Not Be Best Suited for Your Pivot ChartA Few Formatting Limitations Still Exist in Excel 2013Examining Alternatives to Using Pivot ChartsMethod 1: Turn Your Pivot Table into Hard ValuesMethod 2: Delete the Underlying Pivot TableMethod 3: Distribute a Picture of the Pivot ChartMethod 4: Use Cells Linked Back to the Pivot Table as the Source Data for Your ChartUsing Conditional Formatting with Pivot TablesCreating Custom Conditional Formatting RulesWhat's NextChapter 7 Analyzing Disparate Data Sources with Pivot TablesUsing Multiple Consolidation RangesCreating a Multiple Consolidation Pivot TableAnalyzing the Anatomy of a Multiple Consolidation Ranges Pivot TableThe Row FieldThe Column FieldThe Value FieldThe Page FieldsUsing the Internal Data ModelBuilding Out Your First Data ModelManaging Relationships in the Data ModelAdding a New Table to the Data ModelRemoving a Table from the Data ModelCreate a New Pivot Table Using the Data ModelLimitations of the Internal Data ModelBuilding a Pivot Table Using External Data SourcesBuilding a Pivot Table with Microsoft Access DataBuilding a Pivot Table with SQL Server DataWhat's NextChapter 8 Sharing Pivot Tables with OthersDesigning a Workbook as an Interactive Web PageSharing a Link to Your Web WorkbookEmbedding Your Workbook in a Blog Post or Your Web PageSharing Pivot Tables with Other Versions of OfficeChapter 9 Working with and Analyzing OLAP DataWhat Is OLAP?Connecting to an OLAP CubeUnderstanding the Structure of an OLAP CubeUnderstanding the Limitations of OLAP Pivot TablesCreating Offline CubesBreaking Out of the Pivot Table Mold with Cube FunctionsAdding Calculations to Your OLAP Pivot TablesCreating Calculated MeasuresCreating Calculated MembersManaging Your OLAP CalculationsPerforming What-If Analysis with OLAP DataNext StepsChapter 10 Mashing Up Data with PowerPivotUnderstanding the Benefits and Drawbacks of PowerPivot and the Data ModelMerge Data from Multiple Tables Without Using VLOOKUPImport 100 Million Rows into Your WorkbookCreate Better Calculations Using the DAX Formula LanguageOther Benefits of the PowerPivot Data Model in All Editions of ExcelBenefits of the Full PowerPivot Add-In with Excel Pro PlusUnderstanding the Limitations of the Data ModelJoining Multiple Tables Using the Data Model in Regular Excel 2013Preparing Data for Use in the Data ModelAdding the First Table to the Data ModelAdding the Second Table and Defining a RelationshipTell Me Again-Why Is This Better Than Doing a VLOOKUP?Using QuickExploreCreating a New Pivot Table from an Existing Data ModelGetting a Distinct CountUsing the PowerPivot Add-In from Excel 2013 Pro PlusEnabling PowerPivotImport a Text FileAdd Excel Data by Copying and PastingAdd Excel Data by LinkingDefine RelationshipsAdd Calculated Columns Using DAXBuild a Pivot TableUnderstanding Differences Between PowerPivot and Regular Pivot TablesTwo Kinds of DAX CalculationsDAX Calculations for Calculated ColumnsUsing RELATED() to Base a Column Calculation on Another TableUsing DAX to Create a Calculated Field in the Pivot TableDAX Calculated Fields Implicitly Respect the FiltersDefine a DAX Calculated FieldIs Unfilter Even a Word?CALCULATE Is a Super-Enhanced Version of SUMIFSAdding Fields to the Values Area Generates DAX Calculated FieldsUsing a Calendar Table to Enable Time Intelligence FunctionsAdding the Data to PowerPivot and Formatting ItPowerPivot Doesn't Automatically Sort by Custom ListsCreate a PivotTable and Marvel at the ResultsThis Is a Discussion About Time IntelligenceUsing Key Performance IndicatorsSetting up a KPI Compared to an Absolute ValueSetting Up a KPI Compared to a Calculated Target ValueOther Notes About PowerPivotCombination LayoutsGetting Your Data into PowerPivot with SQL ServerOther IssuesNext StepsChapter 11 Dashboarding with Power ViewPreparing Your Data for Power ViewCreating a Power View DashboardEvery New Dashboard Element Starts as a TableSubtlety Should Be Power View's Middle NameConvert the Table to a ChartAdd Drill-Down to a ChartTo Begin a New Element, Drag a Field to a Blank Spot on the CanvasEvery Chart Point Is a Filter for Every Other ElementAdding a Real SlicerThe Filter Pane Can Be ConfusingUse Tile Boxes to Filter One or a Group of ChartsReplicating Charts Using MultiplesShowing Data on a MapUsing Table or Card View with ImagesChanging the CalculationAnimating a Scatter Chart Over TimeSome Closing Tips on Power ViewAnimating Pivot Table Data on a MapNext StepsChapter 12 Enhancing Your Pivot Table Reports with MacrosWhy Use Macros with Your Pivot Table Reports?Recording Your First MacroCreating a User Interface with Form ControlsAltering a Recorded Macro to Add FunctionalityWhat's NextChapter 13 Using VBA to Create Pivot TablesEnabling VBA in Your Copy of ExcelUsing a File Format That Enables MacrosVisual Basic EditorVisual Basic ToolsThe Macro RecorderUnderstanding Object-Oriented CodeLearning Tricks of the TradeWriting Code to Handle Any Size Data RangeUsing Super-Variables: Object VariablesUsing With and End With to Shorten CodeUnderstanding VersionsCode for New Features Won't Work in Previous VersionsBuilding a Pivot Table in Excel VBAAdding Fields to the Data AreaFormatting the Pivot TableDealing with Limitations of Pivot TablesFilling Blank Cells in the Data AreaFilling Blank Cells in the Row AreaLearning Why You Cannot Affect a Pivot Table by Inserting or Deleting CellsControlling TotalsDetermining the Size of a Finished Pivot Table to Convert It to ValuesPivot Table 201: Creating a Report Showing Revenue by CategoryEnsuring Table Layout Is UtilizedRolling Daily Dates Up to YearsEliminating Blank CellsControlling the Sort Order with AutoSortChanging the Default Number FormatSuppressing Subtotals for Multiple Row FieldsCopying a Finished Pivot Table as Values to a New WorkbookHandling Final FormattingAdding Subtotals to Get Page BreaksPutting It All TogetherCalculating with a Pivot TableAddressing Issues with Two or More Data FieldsUsing Calculations Other Than SumCalculated Data FieldsCalculated ItemsCalculating GroupsUsing Show Values As to Perform Other CalculationsUsing Advanced Pivot Table TechniquesUsing AutoShow to Produce Executive OverviewsUsing ShowDetail to Filter a RecordsetCreating Reports for Each Region or ModelManually Filtering Two or More Items in a PivotFieldUsing the Conceptual FiltersUsing the Search FilterSetting up Slicers to Filter a Pivot TableUsing the Data Model in Excel 2013Add Both Tables to the Data ModelCreate a Relationship Between the Two TablesDefine the Pivot Cache and Build the Pivot TableAdd Model Fields to the Pivot TableAdd Numeric Fields to the Values AreaPutting It All TogetherNext StepsChapter 14 Advanced Pivot Table Tips and TechniquesTip 1: Force Pivot Tables to Refresh AutomaticallyTip 2: Refresh All Pivot Tables in a Workbook at the Same TimeTip 3: Sort Data Items in a Unique Order Not Ascending or DescendingTip 4: Turn Pivot Tables into Hard DataTip 5: Fill the Empty Cells Left by Row FieldsOption 1: Implement the Repeat All Data Items FeatureOption 2: Use Excel's Go To Special FunctionalityTip 6: Add a Rank Number Field to Your Pivot TableTip 7: Reduce the Size of Your Pivot Table ReportsDelete Your Source Data TabTip 8: Create an Automatically Expanding Data RangeTip 9: Compare Tables Using a Pivot TableTip 10: AutoFilter a Pivot TableTip 11: Transpose a Data Set with a Pivot TableStep 1: Combine All Non-Column-Oriented Fields into One Dimension FieldStep 2: Create a Multiple Consolidation Ranges Pivot TableStep 3: Double-Click the Grand Total Intersection of Row and ColumnStep 4: Parse Your Dimension Column into Separate FieldsTip 12: Force Two Number Formats in a Pivot TableTip 13: Create a Frequency Distribution with a Pivot TableTip 14: Use a Pivot Table to Explode a Data Set to Different TabsTip 15: Use a Pivot Table to Explode a Data Set to Different WorkbooksWhat's NextChapter 15 Dr. Jekyll and Mr. GetPivotDataTurning Off the Evil GetPivotData ProblemPreventing GetPivotData by Typing the FormulaGetPivotData Is Surely Evil-Turn It OffWhy Did Microsoft Force GetPivotData on Us?Using GetPivotData to Solve Pivot Table AnnoyancesBuild an Ugly Pivot TableBuild the Shell ReportUsing GetPivotData to Populate the Shell ReportUpdating the Report in Future Months9780789748751 TOC 12/18/2013
