Transcript ppt
Computer Science 1000
Spreadsheets I
Permission to redistribute these slides is strictly prohibited without permission
Computers and Data
suppose we ask a computer to remember a retail
transaction
Socks
$ 4.99
a list of 5 items
the cost of each item
the subtotal
the tax associated
the final total
Pants
Shirt
Tweed Suit Jacket
Driving Gloves
Subtotal
Tax
Total
how does the computer do this?
$ 65.99
$ 47.99
$119.99
$ 28.99
$267.95
$ 13.40
$281.35
Computers and Data
computer memory (RAM) – a simplified version
your computer memory is like a massive set of storage
“bins”
think of a post office with 8 billion mailboxes
each bin stores a byte of information
each bin has a label
called its address
each running program is given a subset of those bins, called its
address space
Computers and Data
Computer
Memory
when your program wants to store data
it chooses a memory location that’s:
a) large enough
e.g. if it’s storing a piece of text that is 20
characters, it must find a 20 byte piece of
memory
b) currently unused
we don’t want to overwrite existing
program information
the data is copied to that location in
memory
the address of that memory is
remembered
this is accomplished using a variable, in
programming language speak
name
K
E
V
Computers and Data
when your program wants to
access data that it has stored
it locates the appropriate spot in
memory using its remembered
address
the data can be copied from that
spot
perhaps to be used in a computation,
or sent to an I/O device
Computer
Memory
name
K
E
V
Computers and Data
some points to consider:
the layout of the data in memory is usually not important to
the program
as long as it can access the data it needs (by its memory address),
that’s fine
from our previous example, perhaps the transaction items get
stored at various locations in a program’s memory
the format of the data in memory is usually not important
to the program
suppose the price of an item was $4,356.90
the computer will simply store this (in binary form) as the floatingpoint number 4356.9
Computers and Data
while layout and format might not be important to the
computer, those details are important to the user
layout issues:
proximity creates cohesion – like items can be placed together, while unlike
items can be distanced from each other – facilitating categorization (and
hence, faster searching)
certain alignment of text facilitates easier reading
aligning decimal places in numbers facilitates easy number comparison
format issues:
certain formatting (currency: commas, dollar signs, two decimal places) are
familiar to us
text formatting (bold, italic) allow us to highlight critical information (headers,
keywords, etc)
Computers and Data
to summarize:
when dealing with information, layout and formatting of data can
facilitate some key advantages for users, even if that information
is not necessary for the data to be stored
there are a number of application programs
designed to present information in a meaningful
way to a person
browsers (markup languages – next week)
spreadsheets (today!)
Spreadsheet
a two-dimensional grid of data, that allows
relationships between items of data
each grid location is called a cell
each horizontal line of cells is called a row
each vertical line of cells is called a column
hence, each cell has a unique row/column identification
that differentiates it from the other cells
Example (Excel™)
Cell
Example (Excel™)
Row
Example (Excel™)
Column
Spreadsheet – Software
Microsoft Excel
OpenOffice Calc
a free spreadsheet alternative
part of the OpenOffice suite
LibreOffice Calc
the most widely used office software
part of the Office software suite
another free alternative
part of the LibreOffice suite
many similarities (cell addressing, numbered rows, lettered columns)
some differences (e.g. ; vs , )
for this course, we will use Microsoft Excel, as it is available in your
computer lab
Spreadsheet – Row Labeling
each row in a spreadsheet has a label
for most spreadsheet programs, the label is a number
first row labeled with 1
last row depends on the application and its version
e.g. my Excel 2010 goes up to row 1048576
rows are often stated as “Row” plus their label
e.g. one would refer to the highlighted row below as “Row 4”
Spreadsheet – Column Labeling
each column in a spreadsheet has a label
for most spreadsheet programs, the label is a letter
first column labeled with A
columns are stated as “Column” plus their label
e.g. one would refer to the highlighted row below as “Column E”
Spreadsheet – Column Labeling
what happens when we run out of letters?
e.g. what is the 27th column called?
the letters roll over, much like digits do
first 26 columns: A – Z
next 26 columns: AA-AZ
next 26 columns: BA-BZ
Spreadsheet – Cell Labeling
a cell typically belongs to one row and one column
this ignores merged cells, but we’ll ignore that for now
a cell’s label is the combination of its column and its
row
the cell highlighted in black is A1
the cell highlighted in red is C3
Cell Data
at it’s simplest level, a spreadsheet simply stores data
each cell holds a value
to insert data into a spreadsheet
click on a cell (this will highlight it)
type the data you want to store
press Enter, or select another cell
Cell Data
what types of data can a
spreadsheet hold?
numbers
text
dates/times
each of these can be formatted in a
particular way
e.g. numbers can be displayed as
currency ($, two decimal places) or a
percentage
dates can be displayed in different
formats
Spreadsheet – Example
insert the data from our
retail transaction into the
spreadsheet
items in Column A
prices in Column B (as a
simple number)
Socks
Pants
Shirt
Tweed Suit Jacket
Driving Gloves
Subtotal
Tax
Total
solution:
highlight Cell A1, type Socks, press Enter
highlight Cell B1, type 4.99, press Enter
repeat for next seven lines
$ 4.99
$ 65.99
$ 47.99
$119.99
$ 28.99
$267.95
$ 13.40
$281.35
Spreadsheets – Cell Dimensions
in our last example, Tweed Suit Jacket and Driving
Gloves do not fit in their cells
hence, part of the item name is hidden behind its price
we need to make the column wider
to do this:
manually: place your cursor at right side of column label,
and drag
automatically: select your column (click on column label),
and choose Autofit Column Width from the Format
dropdown
Manual
Autofit
Spreadsheets – Cell Dimensions
note that row dimensions can also be controlled, in
the same way as column dimensions
for example, suppose we wanted to provide a bit of
separation between the SubTotal line and the items of
purchase
manual: place mouse between row labels and drag
autofit: same menu as before, but select Autofit
Row Height
note that the automatic solution won’t work for this
example, as it won’t increase the height of the cell
Cell Formatting
as mentioned, a formatting can be applied to a cell
note that this does not affect the value being
stored, just the value that is displayed
there are many possibilities for formatting
we will consider a few here
text
numbers
some others will be considered in your lab
the best method for learning: try them out
Cell Formatting - Text
formatting text in cells is much like formatting in a
word processor
can control font, colour, background, alignment,
word wrap, etc
process:
select cells you wish to format
choose the appropriate control from the Ribbon
example: italicize the Subtotal label and amount,
and bold the Total and amount
Cell Ranges
the previous works, but is a bit inconvenient
select A6, italicize
select B6, italicize
what if Row 6 contained 25 cells that we wished to apply
this to?
Solution: Cell Range
a collection of cells, highlighted at once
any formatting or editing (e.g. delete) will be applied
across all cells in a range
Cell Ranges
to select a range:
drag your cursor from the top-left cell of your
desired range to the bottom-right
e.g. to select all cells from D2 to F7
Cell Ranges
if a range is rectangular, it is denoted as topLeft :
bottomRight
in previous example, selected range was D2:F7
note that multiple rectangular ranges can be
selected at once
denoted as range1, range 2
e.g. D2:E6, G2:H5
to select multiple ranges, hold
down Ctrl button
Cell Ranges
from previous example
we need to boldface the bottom two cells
let’s use a Range to do this
select the range using drag method
click the bold (B) button in the Font group
Cell Formatting – Numeric
spreadsheets offer different formatting options for numbers
number of decimal places
types (currency, %)
and others
many of these options found in the Number group of the
Home ribbon
if no formatting is specifically applied to the cell, then a
general formatting is assumed
no trailing zeroes
no commas or symbols
Cell Formatting – Numeric
from previous example, numbers should be formatted as a
currency
select the numbers (as a range)
from the drop-down menu in the Number group of the Home ribbon,
select Currency
Cell Formatting – Numeric
many other options for numeric formatting not shown in
Ribbon
to see these, click the
symbol beside the Number label in
the ribbon
this dialog box gives detailed control over how a number is
displayed
Cells – Stored Value vs. Displayed Value
recall previous comment that formatting and layout doesn’t
affect the value being stored
we can see the value being stored by examining the formula
bar when a cell is highlighted
the formula bar (labeled fx) is positioned directly above the sheet
as the following slide shows:
text values are stored as is, with no bold/italic etc …
numeric values are stored without any formatting
Formula
Bar
Cells – Stored Value vs. Displayed Value
why is this important?
the power of a spreadsheet lies in its ability to compute values based on
data in other cells (we’ll see this soon)
the value used for computation is the stored value, not the displayed
value
in example below, cell A1 has been formatted to show only one digit after
the decimal point
if we were to multiply the value of cell A1 by 2, we would get 1.5, not 1.6
Inserting Data
we’ve seen how to append rows of data to an
existing spreadsheet
suppose we wish to add data somewhere other
than the end
e.g. suppose we want to add another purchased item to
our list, or a set of headers
a couple of options
select the entire table as a range, and drag the border
using your mouse
insert a new row, by right clicking on the row to insert
above, and select Insert
Inserting Rows - Drag
Not a good option in this
case – notice the taller
rows
Inserting Rows – Right Click
Much better – the
correct rows are still tall.
Inserting Rows
in either case, we’re now free to add headers
Font Size
Boldface
Cell Shade
we’ll apply cell shading, boldface font, and a larger font
size, to make the headings really stand out
Spreadsheets - Borders
every cell has a light border surrounding it
called gridlines
extra borders can be added via the borders drop-down
to add extra borders:
select the range that you would like to border
use the drop-down to select border:
placement (all, top, bottom, left, right)
style (thick, thin, etc)
from our previous example:
add a single border above subtotal
add a single border above total
add a double border below total
Adding Border above Subtotal
other borders are added in similar manner
Spreadsheets - Borders
if you wish to disable gridlines (to really see your borders),
uncheck Gridlines in View ribbon
Sorting Data
spreadsheets typically allow us to sort data
this has many useful applications
class grades (scholarships)
sports – points (awards, fantasy pools)
method:
select range that you wish to sort
click the sort button
select the type of sort (ascending/descending)
Sorting Data
what happened?
Excel’s sort assumes that your data has a
header that is not to be sorted
solution (choose one):
include the header in your range
choose Custom Sort, and deselect the My Data
has headers option
Sorting Data
caution must be used when sorting
consider our transaction example
suppose I want to sort from cheapest to most
expensive
Sorting Data
previous is not correct
when we move the price, we must move the
corresponding item
fortunately, Excel warns us when we are about
to do this
we can avoid the warning by selecting the entire
range that we wish to sort, and the column to
sort on
choose Custom Sort, and choose your column to sort
on