When working with large amount of data, people usually struggle to discover patterns or trends or to reorder the data to make more informed decisions. PivotTable views—a powerful tool for reorienting large amounts of data to answer different questions—were difficult to create, putting them out of reach for all but the most advanced Excel users.
The enhancements in Office Excel 2007 address these challenges. Office Excel 2007 offers powerful analytical tools to help you discern trends from complex data. By analyzing information effectively, you can gain business insight and make more informed decisions.
Rich Data Visualization with Conditional Formatting
You can now discover and illustrate important trends and highlight exceptions in your data by using conditional formatting with rich visualization schemes. You can apply conditional formatting more easily and identify trends by using colored gradients, data bars, and icons.
• Greater Than…and Less Than…: Enables you to create value-based rules like Greater Than, Less Than, Between, Equal To, or Text That Contains.
• Top/bottom rules: Enable you to format differently the top/bottom x (or x%) items.
• Data Bars: By default, when you apply data bars with one click via the Ribbon, Excel uses the highest and lowest values in the range to draw the shortest and longest bar. You can further tweak the color of the data bar or the way Excel computes the values for the shortest and longest bar by selecting More Options.
• Color Scales: Enables you to create a two-color or three-color scale rule using default colors. When you apply color scales with one click, Excel uses the lowest, highest, and midpoint values in the range to determine the color scales. You can change the colors or the way Excel determines the values for the highest, lowest, or midpoint values by selecting More Options.
• Icon Sets: Enables you to create a conditional formatting rule with three-icon, four-icon, or five-icon icon sets of your choice. The most commonly used icons are exposed here; by default, Excel uses percentiles to calculate the break between icon sets. You can change the icons or the way Excel determines the values for the icon sets, as well as pick from further icon sets, by selecting More Options.
Figure 26: Conditional formatting icon sets
Rules Manager
Use the Rules Manager to work with conditional formatting when you need to:
• View existing rules. You can view the rule condition, the range it is applied to, and the format set on the rule. The Show Formatting Rules for drop-down menu at the top of the dialog box enables you to change the scope of the rules you are looking at. You can view rules for the selected cells, the current worksheet, the current table, other worksheets in the book, other tables in the workbook, or the entire workbook.
• Add new rules via the New Rule button.
• Change the order in which rules are evaluated. This is done using the UP ARROW and DOWN ARROW keys.
• Edit existing rules via the Edit Rule button. The Edit Rule UI is the same as the New Rule UI.
• Delete rules via the Delete Rule button.
• Control whether more than one rule can evaluate to true.
Improved Sorting and Filtering
Sorting and filtering are two of the most important types of basic analysis that you can do with data. New options for sorting and filtering make Office Excel 2007 the ideal tool for working with large amounts of complex data. You can:
• Select any number of items for filter conditions with multi-select in AutoFilters.
• Sort or filter by the background color of cells.
• Filter by applying “quick filters” for specific data types (for example, filter by quarter, month, or year for a date column).
• Reapply a set of filters with one button click.
• Manage up to 64 sorting levels from a central location.
Figure 27: Enhanced filtering and sorting
Office Excel 2007 in Action
After organizing her data into a table, Susan is still having difficulty perceiving how the numbers are trending. By using conditional formatting, she can illustrate these conditions in a more intuitive way. For example, by applying a three-color gradient to Fabrikam Inc.’s 2005 results, Susan creates a financial “heat map” that tells her the areas that demand her attention, as the higher values are now depicted in green. She quickly pinpoints that product sales were particularly strong in the Northeast Region. With the ability of Office Excel 2007 to sort by color, Susan can better identify the states in which the highest sales volume occurred, and then share this insight with Sylvie during their next meeting.
Easy-to-Use PivotTable and PivotChart Views
Office Excel 2007 offers new tools for creating and working with PivotTable views and PivotChart views that make it easy to ask multiple questions about a given data set. With enhanced context menus, contextual tool tips, and new layout and design options, these features can expose this advanced tool to even more people.
Accessible and Visually Enhanced Tools
Information workers of varying skill levels can now create a PivotTable or PivotChart in a few simple steps. Whether you are working with local data or accessing back-end data (such as a SQL Server 2005 Analysis Services cube), all you need to decide is which fields to display as rows, which fields to display as columns, and which fields to display as values and in what form (values can be displayed as sum, averages, or other calculations).
Figure 28: Creating a PivotTable
By using the PivotTable Field List, you can select the fields you want to include by using a clear, side-by-side view. If you need to pose another question against your data, you can drag fields in different orders to alter the hierarchy of your information. Similar to charts, you can quickly modify the look of a PivotTable by using the chart gallery.
Local and external filters
It is now easier to filter data in PivotTable views. Extensive improvements to filtering include:
• Visible filter entry points and dialog boxes.
• New value filters.
• Date and label filters.
• Multi-item selection.
You can sort by various fields, such as color or compact axis, and then view your results. Use inline “drill indicators” to drill up and down on any part of the data.
Most important, you can take advantage of these features while working within a browser. Office Excel 2007 works with Excel Services so that you can interact with a PivotTable smoothly—whether you are working with Office Excel 2007 on a desktop or in a Web browser.
Improved design and layout
Enhancements to the layout of PivotTable views make them more readable and presentable. Some of the improvements to the design include:
• Compact row axes that display multiple fields in one column.
• A flexible, in-grid layout that is suitable for all types of reporting.
• New style options available from the Table Gallery so you can choose a style that is consistent with the look of your table or other aspects of your spreadsheet.
Quick Access to Current Business Data
You can now query the latest business information quickly. Office Excel 2007 offers full support for SQL Server 2005 Analysis Services, which delivers rich data consolidation, warehousing, and structure.
SQL Server 2005 Analysis Services
Smooth integration between Office Excel 2007 and SQL Server 2005 Analysis Services helps you make better use of your IT department’s existing data warehousing investments. You can now “slice” data so that you can create custom reports from relational databases and expose information such as:
• Calendar year versus fiscal year, as a way to capture different views of business data over time.
• “Top Sellers” as an always up-to-date, single-click selection in a spreadsheet that draws from SQL Server 2005 data.
Role of PivotTable and PivotChart Views in SQL Server 2005 Analysis Services
PivotTable and PivotChart views play a central role in the Office Excel 2007 support of SQL Server 2005 Analysis Services. SQL Server 2005 Analysis Services provides a business semantic model, called a Unified Dimensional Model (UDM), which defines business logic, calculations, and metrics. The UDM serves as the single data source for all reports, spreadsheets, online analytical processing (OLAP) browsers, and KPIs.
Benefits of the Field List
PivotTable and PivotChart views that are connected to the UDM in SQL Server 2005 Analysis Services—instead of data in a spreadsheet—offer additional benefits that extend beyond core pivot functionality. Specifically, you gain all of the benefits of the “metadata” that is built into the UDM so that you can quickly find what you need by using the Field List.
SQL Server 2005 Analysis Services features that are exposed by the Field List include:
• Separate measures, dimensions, and KPIs that have different icons.
• The measure group picker at the top so that you can focus on one measure group by filtering out all dimensions that are not useful with that group.
• Display folders that prevent the top-level list of fields from becoming too cluttered.
• Attributes, which are the single fields that make up dimensions. The attributes are exposed individually for greater flexibility.
SQL Server 2005 Analysis Services features that are not exposed but are supported by the Field List include named sets that the UDM designer can use to define subsets of items in a dimension. For example, specific customers within the Customer dimension can be added with a single click in the Field List. This is effective in cases where it would be tedious to select the individual items manually. It is also useful when the set is defined by a business rule. For instance, Top Sellers can change on a daily basis.
Figure 29: Integration with SQL Server 2005 Analysis Services
0 komentar:
Post a Comment