Bài giảng Business Driven Technology - Technology plug-in T3 - Problem Solving Using Excel

TECHNOLOGY PLUG-IN T3Problem Solving Using ExcelLEARNING OUTCOMESDescribe how to create and sort a list using ExcelExplain why you would use conditional formatting using ExcelDescribe the use of AutoFilter using ExcelExplain how to use the Subtotal command using ExcelDescribe the use of a PivotTable using ExcelINTRODUCTIONIf you routinely track large amounts of information, such as customer mailing lists, phone lists, product inventories, sales transactionsThere are five areas in this plug-in:ListsConditional FormattingAutoFilterSubtotalsPivotTablesLISTSA list is a collection of rows and columns of consistently formatted data adhering to somewhat stricter rules than an ordinary worksheetLISTSWhen you create a list, keep the following in mind:Maintain a fixed number of columns (or categories) of informationUse each column to hold the same type of informationDon’t leave blank rows or columns in the list areaMake your list the only information in the worksheet so that Excel can more easily recognize the data as a listMaintain your data’s integrity by entering identical information consistentlyLISTSTo create a list in Excel, follow these steps:Open a new workbook or a new sheet in an existing workbookCreate a column heading for each field in the list, format the headings in bold type, and adjust their alignmentFormat the cells below the column headings for the data that you plan to useAdd new records (your data) below the column headings, taking care to be consistent in your use of words and titles so that you can organize related records into groups laterLISTSSorting Rows and ColumnsOnce your records are organized into a list, you can sort the data for further analysisTo sort a list based on one column, follow these steps:Select the SortData worksheet from the T3_ProblemSolving_Data.xls Click any cell in the Sales Rep column; you want to use this column as the basis for sorting the listClick the Data tabClick the Ascending radio button to specify the order to sort by (A to Z, lowest to highest, earliest date to latest)Sorting Rows and ColumnsSorting Rows and ColumnsTo sort a list based on two or three columns follow these steps:Click any cell in the Sales Rep columnClick the Data tab, and then click the Sort buttonClick the Column list arrow, and then select the Sales Rep in the Sort by drop-down listClick the Order list arrow and specify A to Z order for that columnClick the Add Level button, then click Magazine in the Then by drop-down listSpecify A to Z order for the second sortClick the Add Level button, then click Sale for the sortSpecify Smallest to Largest order for the third sortClick OK to run the sortSorting Rows and ColumnsSorting Rows and ColumnsTo create a custom sort order, follow these steps:Click the Office button, click the Excel Options button, click the Popular category, and then under Top options for working with Excel, click the Edit Custom Lists buttonClick the line NEW LIST under Custom Lists section and the text pointer appears in the List Entries list boxType West, North, South, East, and then click AddClick OKSorting Rows and ColumnsTo use a custom sort order, follow these steps:Click any cell in your listClick the Home tab, then under the Editing group click Sort & Filter, and then click Custom SortUnder Column, in the Sort by drop-down box, select the Region fieldUnder Order, select Custom List and select West, North, South, EastClick OKSorting Rows and ColumnsCREATING CONDITIONAL FORMATTINGExcel gives you the ability to add conditional formatting - formatting that automatically adjusts depending on the contents of cells - to your worksheetThis means you can highlight important trends in your dataCREATING CONDITIONAL FORMATTINGTo create a conditional format, complete the following steps:Select the worksheet ConditionalFormattingSelect the column SaleClick the Home tabIn the Styles section, click the Conditional Formatting button, and then point to Highlight Cell Rules and click Between...In the first text box, type the number 1000In the second text box, type the number 1200CREATING CONDITIONAL FORMATTINGIn the third text box, use the drop-down arrow to select Green Fill with Dark Green TextClick OKClick the Conditional Formatting button, and then point to Highlight Cell Rules and click Greater Than...Type 1250 in the first box and select Red Fill with Dark Red Text using the dropdown arrow from the second boxClick OKCREATING CONDITIONAL FORMATTINGUSING AUTOFILTER TO FIND RECORDSWhen you want to hide all the records (rows) in your list except those that meet certain criteria, you can use the AutoFilter command on the Filter submenu of the Data menuThe AutoFilter command places a drop-down list at the top of each column in your list (in the heading row)USING AUTOFILTER TO FIND RECORDSTo use the AutoFilter command to find records, follow these steps:Select the worksheet AutoFilterClick any cell in the listClick the Data tab and then click the Filter button in the Sort & Filter sectionEach column head now displays a list arrow.Click the list arrow next to the Region headingClick East to use for this filterUSING AUTOFILTER TO FIND RECORDSCreating A Custom AutoFilterTo create a custom AutoFilter, follow these steps:Click any cell in the listClick the Data tab and then click the Filter buttonClick the list arrow next to the heading Sale and select Number Filters, then click on Custom FilterCreating A Custom AutoFilterClick the first relational operator list box and select is greater than or equal to and then click the value list box and select $500Click the And radio button, then specify is less than or equal to in the second relational operator list box and select $800 in the second value list boxClick OKCreating A Custom AutoFilterANALYZING A LIST WITH THESUBTOTALS COMMANDThe Subtotals command helps organize and analyze a list by displaying records in groups and inserting summary information, such as subtotals, averages, maximum values, or minimum valuesThe Subtotals command can also display a grand total at the top or bottom of your list, letting you quickly add up columns of numbersANALYZING A LIST WITH THESUBTOTALS COMMANDTo add subtotals to a list, follow these steps:Select the worksheet SubtotalsSort the list by RegionClick the Data tab, then click the Subtotal button in the Outline sectionIn the At Each Change In list box, choose Sales RepIn the Use Function list box, choose SUMIn the Add Subtotal To list box, choose Sale, which is the column to use in the subtotal calculationClick OKANALYZING A LIST WITH THESUBTOTALS COMMANDPIVOTTABLESA powerful built-in data-analysis feature in Excel is the PivotTableA PivotTable analyzes, summarizes, and manipulates data in large lists, databases, worksheets, or other collectionsIt is called a PivotTable because fields can be moved within the table to create different types of summary lists, providing a “pivot”PivotTable TerminologySome notable PivotTable terms are:Row field - Row fields have a row orientation in a PivotTable report and are displayed as row labelsColumn field - Column fields have a column orientation in a PivotTable report and are displayed as column labelsData field - Data fields from a list or table contain summary data in a PivotTable, such as numeric data (e.g., statistics, sales amounts)Page field - Page fields filter out the data for other items and display one page at a time in a PivotTable reportPivotTable TerminologyUsing the PivotTable FeatureSelect the worksheet PivotTableDataClick any cell in the listClick the Insert tab, then click the PivotTable button in the Tables group, and click on PivotTableIn the Select a table or range box, make sure you see $A$1:$E$97Click OKUsing the PivotTable FeatureDrag the Month button to the PAGE areaDrag the Region button to the COLUMN areaDrag the Magazine button to the ROW areaDrag the Sale button to the DATA areaUsing the PivotTable FeatureModifying A PivotTable ViewDrag the buttons off the diagram and arrange the fields like this:Magazine in the PAGE areaMonth in the COLUMN areaSales Rep in the ROW areaSale in the DATA areaModifying A PivotTable ViewBuilding A PivotChartClick the PivotChart button in the Tools section of the PivotTable ribbonSelect the Stacked Column chart and click OKClick the Move Chart button on the PivotTable ribbon (on the Design tab, in the Location group) and select New sheet from the Move Chart dialog boxBuilding A PivotChart