File Processing with Excel

1
File Processing with Excels 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
Excels 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.
• AgeYear(Today())-Year(DOB)
• Or
• Age(Today()-DOB)/365

9
Calculated Grouping Field

• Examples
• Age group
• Income group
• Academic performance group