1 / 9

File Processing with Excel’s List

File Processing with Excel’s List. File Concepts. File consists of a group of records. Each record contains a group of fields. Example: Student file SID Sname Major Sex GPA S1 Peter CIS M 3.0 S3 Paul ACCT M 2.7 S5 Mary CIS F 3.2. Excel’s List.

xandy
Download Presentation

File Processing with Excel’s List

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. File Processing with Excel’s List

  2. File Concepts • File consists of a group of records. Each record contains a group of fields. • Example: Student file • SID Sname Major Sex GPA • S1 Peter CIS M 3.0 • S3 Paul ACCT M 2.7 • S5 Mary CIS F 3.2

  3. Excel’s List • An Excel list consists of columns and rows of data structured in a specific way: • Each column contains the same category of data in every row in the column(similar to a field in a file) • Each row in the list contains all of the fields of data for one entity (a person,organization, object, etc.) similar to a record in a file. • The first row of the list must contain a unique name at the top of each column. • There can be no blank rows in the list (there can be blank cells in a column, but the entire row cannot be empty)

  4. List Example

  5. Key Field & Grouping Field • Key field: A value of the field uniquely determines a record in a file. • Example: CID field • Grouping field: A filed that can be used to group records: • Example: City field, Rating field.

  6. Basic File/List Processing • Sorting: • Data/Sort • Two level sorting: Data/Sort/Add level • Selecting records meeting criteria • Data/Filter • Text Filter: • Begin with, End with, Contains, • Number Filter: • Top n, Above Average, Below Average • Date Filter: • Before, After, Next week, Last week, This month, Next quarter, This year, All dates in the period, etc. • Compute subtotals • Must sort list by grouping fields first • Data/Subtotal • Compute subtotals of two grouping fields

  7. Example • Student file • SID Sname Major Sex GPA • S1 Peter CIS M 3.0 • S3 Paul ACCT M 2.7 • S5 Mary CIS F 3.2 • What information can be retrieved from this file?

  8. Calculated Field • A field whose value is derived from existing fields, for example, Age can be calculated from DateOfBirth. • Age=Year(Today())-Year(DOB) • Or • Age=(Today()-DOB)/365

  9. Date Functions • Each date is assign a serial number starting from 1/1/1900, which has a serial number 1. • Functions: • Today(): current date • Now() : current date and time • Year(date), Month(date), etc. • Weekday(date) • Date calculation • Days between two dates • Compute age from birthday

More Related