1 / 27

PowerPoint Presentation to Accompany GO! with Microsoft ® Office 2007 Intermediate Chapter 4

PowerPoint Presentation to Accompany GO! with Microsoft ® Office 2007 Intermediate Chapter 4 Importing Data and Using Excel As a Database. Objectives. Import Data into Excel Reorganize Data from External Sources Analyze Data with Excel Database Tools Use Database Functions.

arlais
Télécharger la présentation

PowerPoint Presentation to Accompany GO! with Microsoft ® Office 2007 Intermediate Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PowerPoint Presentation to Accompany GO! with Microsoft®Office 2007 Intermediate Chapter 4 Importing Data and Using Excel As a Database

  2. Objectives • Import Data into Excel • Reorganize Data from External Sources • Analyze Data with Excel Database Tools • Use Database Functions

  3. Import Data into Excel • Data • Facts about people, events, things, or ideas • Database • Collection of related data • Information • Data organized in a useful manner

  4. Import Data into Excel • Record - row • Related data about one person, event thing, or idea • Field - column • Category of data such as phone number • Database table • Each field has a unique name • No empty columns or rows

  5. Import Data into Excel • Data can be imported from: • Word table • TXT file • Access • Comma delimited file

  6. Import Data into Excel • Importing from Word • Data from Word tables can be imported • Step 1: Copy table from Word • Step 2: Paste Special in Excel • Step 3: Choose text as type

  7. Import Data into Excel • Import data from text file – Step 1 choose file type that best describes your data • Delimited • Fixed Width

  8. Import Data into Excel • Import data from text file – Step 2 Set your delimiters

  9. Import Data into Excel • Import data from text file - Step 3 Format data • General • Text • Date • Do not import

  10. Reorganize Data from External Sources • Convert Text into Columns • For example, breaking a full name into first and last name for sorting

  11. Reorganize Data from External Sources • Remove spaces using the TRIM function

  12. Reorganize Data from External Sources • Consolidating Data • Displaying worksheets Side by Side allows for easier scrolling and consolidation

  13. Reorganize Data from External Sources • Highlighting duplicate values using conditional formatting

  14. Reorganize Data from External Sources • Example of conditional formatting

  15. Reorganize Data from External Sources • Removing Duplicate Records • In the Remove Duplicates dialog box • Define the fields that must match in order for a record to be considered • Consider matching on more than one field to be cautious • The first record is the one that is preserved

  16. Reorganize Data from External Sources • Creating a Table • Insert the table • Set the table style using the Table Styles gallery

  17. Analyze Data with Excel Database Tools • Use Custom AutoFilters

  18. Analyze Data with Excel Database Tools • Advanced Filter Criteria

  19. Analyze Data with Excel Database Tools • Using Wildcards in Criteria Ranges • Asterisk (*) used to replace a number of unspecified characters • B* will give you any List Agent whose name begins with B • *d will include any List Agent whose name ends with a d

  20. Analyze Data with Excel Database Tools • Extract Data to a New Area on the Worksheet

  21. Analyze Data with Excel Database Tools • Subtotals

  22. Analyze Data with Excel Database Tools • Group and Outline Data

  23. Use Database Functions • Database Functions – perform calculations on a database table • Syntax DFunction Name (database, field, criteria) • Examples • DSUM • DAVERAGE • DCOUNTA • DGET

  24. Use Database Functions • DSUM – sums a column of values in a database that is limited by criteria

  25. Use Database Functions • DCOUNTA function • Counts the number of occurrences of a specified condition • Excludes blank cells • DGET function • Retrieves a single value from your data table that matches the conditions you specify

  26. Use Database Functions • Printing options • Set Print Area • Select Multiple sheets for printing • Add Page numbers and total number of pages in Header/Footer

  27. Covered Objectives • Import Data into Excel • Reorganize Data from External Sources • Analyze Data with Excel Database Tools • Use Database Functions

More Related