The bulk of the book covers how to use pivot tables in the Excel user interface. The final chapter describes how to create pivot tables in Excel's powerful VBA macro language. This means that any user who has a firm grasp of the basics preparing data, copying, pasting, entering simple formulas should have no problem understanding the concepts in this book.
Life Before Pivot Tables Imagine that it is You are using Lotus or Excel 4. You have thousands of rows of transactional data, as shown in Figure I. Your manager asks you to prepare a summary report showing revenue by region and product.
In , preparing this report was a daunting task. It required superhuman spreadsheet skills that few could master. Here are the steps you needed to take: 1. You need to get a list of the unique regions in the dataset. You need to get a list of the unique products in the dataset. Use the Advanced Filter command with Unique Records Only a second time to extract a list of the unique products.
You need to turn the list of products sideways so that it runs across the columns. Copy the list of unique models. Then choose Edit, Paste Special, Transpose to arrange the products as headings going across the report.
You now have a skeleton of the report, as shown in Figure I. You could use the DSUM function to total a column based on one criterion, but not based on two criteria. Therefore, you need to abandon typical functions and instead rely on an array formula.
Before entering the array formula, set up two fields above the report to hold a sample region and sample model. In the corner cell of the report, build an array formula to test whether the region column is North and the Model column is T, and if so, add the corresponding row from the Revenue column.
The formula is shown in the formula bar in Figure I. Figure I. Finally, after using two advanced filters and a Paste Special command, writing the hardest formula in the world, and then using the Data Table command, you have the result your manager is looking for, as shown in Figure I. If you could pull off this analysis in 10 minutes, you were doing an amazing job.
Spreadsheet's Bookshelf. Here the challenge would be to know when this is done. It's a shame that this happens with a book that we had been waiting for so long, specially for most of us who regularly travel and it is not practical to carry such a heavy package everywhere. I'm also quite disappointed with the results obtained after contacting the author who said the he didn't have the pdf file himself! Do you believe this? And Excel Dashboards and Reports for Dummies is the fastest you for you to catch dashboard fever!
Properly created dashboards are graphical representations that put data in a context for your audience, and they look really cool. You'll find out when you see the examples in this book, and soon your dashboards and reports will be getting rave reviews. Michael is one of 96 Microsoft Excel MVPs worldwide who has been recognized for his contributions to the Excel community. He is also the principal player behind DataPigTechnologies.
He currently lives in Frisco, Texas where he works as a Senior Program Manager for a top technology firm. Michael can be contacted at mike datapigtechnologies.
0コメント