october 2003bent thomsen - fit 3-21 it – som værktøj bent thomsen institut for datalogi aalborg...
TRANSCRIPT
October 2003 Bent Thomsen - FIT 3-2 1
IT – som værktøj
Bent Thomsen
Institut for Datalogi
Aalborg Universitet
October 2003 Bent Thomsen - FIT 3-2 2
Data List Spreadsheetsor simple databases
- a different use of Spreadsheets
Bent Thomsen
October 2003 Bent Thomsen - FIT 3-2 3
Data List• Data can be arranged in the form of a list
– Enables management and analysis of data
Remember an Excel spreadsheet can hold 65536 rows!
October 2003 Bent Thomsen - FIT 3-2 4
Planning a Data List
• Counter examples of guidelines
Format column labels consistently
Avoid leading and trailing spaces
Avoid blank rows and columns
Only one data list per worksheet
Unhide columns and rows before
editing
Critical data at top or bottom of list
October 2003 Bent Thomsen - FIT 3-2 5
Entering Records Using a Data Form
• Columns label the fields included in the data base– Enter directly in cells or …– Use a data form to create fields
• Data form can be also used to– Locate records– Edit records– Delete records
October 2003 Bent Thomsen - FIT 3-2 6
Entering Records Using a Data Form
• With cells containing column labels and first row of data– Select both rows (labels, data)– Click on Data, then
Form for the data form
• Press [Enter] to moveto new blank form– Enter data– Press [Tab] to go to next
field
Note buttons for
navigating through the list of data
October 2003 Bent Thomsen - FIT 3-2 7
Adding a Data Validation Rule
• Rules can be set for certain fields– Values entered are checked– Invalid entries rejected with polite message
• Validation rules include checks for– Proper type (character, numeric)– Range of values (low to high)– One of a small set of valid values
October 2003 Bent Thomsen - FIT 3-2 8
Adding a Data Validation Rule• Click on the column of the field for which
data validation will be established
• Click on Data, then Validation for dialog box
Select desired input format for selected
cells
October 2003 Bent Thomsen - FIT 3-2 9
Adding a Data Validation Rule• Also possible to specify
– Input Message
– Error message
October 2003 Bent Thomsen - FIT 3-2 10
Searching for a Record
• Difficult to look through a large data base for a particular record
• Solution– Set criteria for search– Specify for single field
• Example– Look for record with last name containing
value "Martindale"
October 2003 Bent Thomsen - FIT 3-2 11
Searching for a Record• With Data Form open, click Criteria
– Form becomes blank– Enter a value or a comparison in a field– Click on Find Next
October 2003 Bent Thomsen - FIT 3-2 12
Searching for a Record• Record that meets search criteria is
displayed
October 2003 Bent Thomsen - FIT 3-2 13
Using Find and Replace to Change Cell Contents
• Use to locate records in large worksheet– Use dialog box to enter target text– Enter replacement text
• Possible to search for text with specified characteristics– Text content– Text color, font size, etc.
• Found text may also be reformatted
October 2003 Bent Thomsen - FIT 3-2 14
Using Find and Replace to Change Cell Contents
• Click on Edit, then Find to bring up dialog box– Enter target text
• Click on Replace tab to specify replacement text and options
October 2003 Bent Thomsen - FIT 3-2 15
Using Find and Replace to Change Cell Contents• Enter replacement text
Where and how to look
options
Click to set format of replacement text
October 2003 Bent Thomsen - FIT 3-2 16
Using Find and Replace to Change Cell Contents
• Replace Format dialog box to specify– Color– Font– Style– Size
October 2003 Bent Thomsen - FIT 3-2 17
Using Find and Replace to Change Cell Contents
• When search and replace has finished, message box displays
• Note results of replaced text
October 2003 Bent Thomsen - FIT 3-2 18
Sorting a Data List
• A database must provide ability to organize data in a specific order (sorting)
• Data is entered as it comes in– Records are arranged in different order at later
time
• Sorting can be done– By any one of the fields– Multi-level, by up to three fields
October 2003 Bent Thomsen - FIT 3-2 19
Sorting a Data List• With a data list open, select cell in column (field)
for basis of sort
• Click on Data, then Sort for dialog box
• Specify up tothree fields for sort
• Specify ascendingor descending
• Note Options button
October 2003 Bent Thomsen - FIT 3-2 20
Sorting a Data List• Sort Options dialog box
– Click for pulldown menu
• Some kinds of fields may require other than alphabetical sort
October 2003 Bent Thomsen - FIT 3-2 21
Filtering a Data List
• Filtering produces a subset of a database– Based on specified criteria– Finds specific information from database
• Useful for working with smaller portion of a large database– Records for a single month– Records for a sales region– Inventory items from a single department
October 2003 Bent Thomsen - FIT 3-2 22
Filtering a Data List• Click on Data, then Filter and AutoFilter
– Down arrow appears on each column heading
• Click on desired column heading arrow– Choose from list– All records with that value are filtered out and displayed
October 2003 Bent Thomsen - FIT 3-2 23
Filtering a Data List• When Custom option is chosen,
Custom AutoFilter dialog boxopens
• Choose comparison
• Specifyvalue
• Multiplecomparisonspossible
October 2003 Bent Thomsen - FIT 3-2 24
Filtering a Data List• When done with AutoFilter
– Click on Data, Filter, – Then click on the AutoFilter to remove the
check mark and toggle the feature off
October 2003 Bent Thomsen - FIT 3-2 25
Adding Record Subtotals• Consider a database with groups of records with
same value in a field– Region, zip code, etc.
• Possible to summarize data based on those groupings
• For any field we can determine– Sum, average, count, min, max, etc.
• Important to sort on the field on which the groupings or subtotals will be based
October 2003 Bent Thomsen - FIT 3-2 26
Adding Record Subtotals• Sort the database on grouping field
• Click on Data, then Subtotals for dialog box– Specify field for grouping– Specify function to use– Click on OK
October 2003 Bent Thomsen - FIT 3-2 27
Adding Record Subtotals• Data list with monthly subtotals
Use level buttons or plus and minus signs to collapse or expand
the outline
October 2003 Bent Thomsen - FIT 3-2 28
Extracting a Subset
• The AutoFilter feature provides a subset of the data– But it is only for viewing
• Occasionally the subset must be extracted to another, separate data list– Useful for creating a chart based on the subset of
data
• The Advanced Filter provides this capability
October 2003 Bent Thomsen - FIT 3-2 29
Extracting a Subset• Copy the data labels, past them to a new location
• Specify values and/or criteria for one or more columns
• Click on Data, Filter, then Advanced Filter, – Dialog box opens
October 2003 Bent Thomsen - FIT 3-2 30
Extracting a Subset• Advanced Filter Dialog box
– Original data list rangealready shows
– Click Collapse Dialogbutton to specify criteria range
$A$30:$H$30
October 2003 Bent Thomsen - FIT 3-2 31
Extracting a Subset• Extracted sheet
Criteria for subset
Subset of original data list
October 2003 Bent Thomsen - FIT 3-2 32
Printing a Data List• Printing a data list involves similar
procedures as printing other Excel worksheets• Use the Print What
section of Print dialogbox to specify
• Use Page Setup optionsto revise layout– Add a header
October 2003 Bent Thomsen - FIT 3-2 33
Printing a Data List• Using Page Setup dialog box
• Buttons in Header dialog box
Printing orientation options
Change font
Page number
•Insert total
•Insert page number
Insert date, time
Insert title of- Path, file- File name- Sheet name
Pictures- Insert- Format
October 2003 Bent Thomsen - FIT 3-2 34
Data List Guidelines
1. Limit of one list per worksheet2. Keep similar items in the same column in all rows3. Place important data at top or bottom of list4. When editing list contents, unhide all columns/rows5. Column label formatting
1. In first row2. Different from data
6. Avoid blank rows, columns7. Avoid leading/trailing spaces in data