[ Pobierz całość w formacie PDF ]
. 4800-x Ch01.F 8/27/01 11:54 AM Page 2222 Part I: Basic InformationDatabase AccessOver the years, most spreadsheets have enabled users to work with simple flat data-base tables (even the original version of 1-2-3 contained this feature).Excel s data-base features fall into two main categories:Worksheet databases.The entire database stores in a worksheet, limitingthe size of the database.In Excel, a worksheet database can have no morethan 65,535 records (because there are 65,536 rows; the top row holds thefield names) and 256 fields (because there are 256 columns).External databases.The data stores in one or more disk files and you canaccess it as needed.Generally, when the cell pointer resides within a worksheet database, Excel rec-ognizes it and displays the field names whenever possible.For example, if youmove the cell pointer within a worksheet database and choose the Data Sort com-mand, Excel enables you to select the sort keys by choosing field names from adrop-down list.A particularly useful feature, Excel s AutoFilter, enables you to display only therecords that you want to see.When AutoFilter mode is on, you can filter the databy selecting values from pull-down lists (which appear in place of the field nameswhen you choose the Data Filter AutoFilter command).Rows that don t qualifyare temporarily hidden.See Figure 1-7 for an example.Figure 1-7: Excel s AutoFilter feature makes it easy toview only the database records that meet your criteria.If you prefer, you can use the traditional spreadsheet database techniques thatinvolve criteria ranges.To do so, choose the Data Filter Advanced Filtercommand. 4800-x Ch01.F 8/27/01 11:54 AM Page 23Chapter 1: Excel in a Nutshell 23Chapter 9 provides additional details regarding worksheet lists anddatabases.Excel can automatically insert (or remove) subtotal formulas in a table that is setup as a database.It also creates an outline from the data so that you can view onlythe subtotals, or any level of detail that you desire.OutlinesA worksheet outline often serves as an excellent way to work with hierarchicaldata, such as budgets.Excel can create an outline automatically by examining theformulas in your worksheet.After you ve created an outline, you can collapse orexpand the outline to display various levels of details.Figure 1-8 shows an exam-ple of a worksheet outline.Figure 1-8: Excel can automatically insert subtotal formulas and create outlines.Scenario ManagementScenario management is the process of storing input values that drive a model.Forexample, if you have a sales forecast, you may create scenarios such as best case,worst case, and most likely case.If you seek the ultimate in scenario-management features, 1-2-3 s VersionManager is probably your best bet.Unlike Version Manager, Excel s ScenarioManager can only handle simple scenario-management tasks.However, it is defi-nitely easier than trying to keep track of different scenarios manually. 4800-x Ch01.F 8/27/01 11:54 AM Page 2424 Part I: Basic InformationAnalysis ToolPakThe Analysis ToolPak add-in provides 19 special-purpose analysis tools (primarilystatistical in nature) and many specialized worksheet functions.These tools makeExcel suitable for small- to medium-scale statistical analysis.Pivot TablesOne of Excel s most powerful tools is its pivot tables.A pivot table enables you todisplay summarized data in just about any possible way.Data for a pivot tablecomes from a worksheet database or an external database and stores in a specialcache, which enables Excel to recalculate data rapidly after a pivot table is altered.Chapter 18 contains additional information about pivot tables.Excel 2000 and later versions also support the pivot chart feature.Pivot chartsenable you to link a chart to a pivot table.Auditing CapabilitiesExcel also offers useful auditing capabilities that help you identify errors or trackthe logic in an unfamiliar spreadsheet.To access this feature, select ToolsFormula Auditing (or Tools Auditing, in versions prior to Excel 2002).Excel 2002 includes background formula auditing [ Pobierz całość w formacie PDF ]

  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • funlifepok.htw.pl

    •