 |
The power of spreadsheets lies in the ability to address other cells in
functions, formulas, and equations. Understanding the different addressing
methods is an important part of learning the basics of any spreadsheet.
While it is possible to use spreadsheets on a daily basis using only one
addressing method, other techniques could be more efficient, and in some
cases the only way.
The most commonly used form of addressing is relative addressing.
Typing in the only the cell column/row location, like B3, uses relative
addressing. Instead of storing the physical location of the cell, the reference is
remembered as the location relative to the current cell. If in the cell
D2, a function "=B2+C2" is entered, it actually evaluates as
adding the value from the cell two cells to the left to the value found
one cell to the left. This method of addressing allows functions to be
copied across cells with ease. Any copying or cut and paste operations
done on the function will automatically adjust to the new cell locations.
The cell references change, but the relative references do not. Therefore,
if we copy the function from the previous example to the cell in D3, it
will change to "=B3+C3". The physical cells addressed in this
function changed, but it still evaluates to adding the value of the cell
two cells to the left to the value to the cell on the left. In our example
above, if there were 400 rows that needed the equivalent function, it would
only have to be entered for the first row and copied to the other cells.
If relative addressing is not desirable, it is possible to use
absolute addressing. This form of addressing will point to the actual
physical
location of the cell, and will not adjust when copied to another location.
In both Excel and Quattro Pro, placing a dollar sign before a cell reference
will make it absolute. The dollar sign can either be placed before a row
reference, a column reference, or both. The reference of $A$1 would be
absolute for the row and column, $A1 would be absolute for the column but
relative for the row, and A$1 would be relative for the column but absolute
for the row. For example, if in cell B2 we have a formula =$A$1+$B1+A$2,
when it is copied to cell C3 it would adjust to =$A$1+$B2+B$2.
At certain times (such as function arguments) it is necessary to specify
an entire block of cells. Excel uses to colon to specify a block
(A1:B2), and Quattro Pro uses two periods (A1..B2). The first cell reference
specifies the top left hand corner of the block, and the second one points
to the bottom right had corner. This example would include the cells A1,
A2, B1, and B2. Instead of actually typing in the block reference, Excel
and Quattro Pro allows the region to be selected with the mouse. While
entering in a function, the mouse can be moved to highlight blocks in the
spreadsheet and the corresponding reference will be inserted at the cursor
location. Some features of Quattro Pro, such as the formula composer, requires
hitting the point button to select a
block of cells. After the selection has been made, press the return key
to insert the block reference.
The blocking method above is only for blocks that are contingent. To
represent non-contiguous blocks, a union is represented in Excel
and Quattro Pro by a comma (A2:A4,C3:C5 or A2..A4,C3..C5). It is best to
think of the method as including two or more blocks in the same reference.
Excel also supports an intersection between two blocks by placing
a space between two block references "(A1:C4 B2:D5)". This would
represent only the cells that are common to regions specified by A1:C4
and B2:D5. When using the mouse to make a non-contiguous block, select
a block, hold down the control key, and then make another block. Excel
4.0 on the Mac uses the command key instead.
A three dimensional reference is a cell reference that points
to a cell in another sheet or page. For a 3D reference in Excel, the sheet
name and the exclamation point precedes the cell name (Sheet1!A1). Quattro
Pro uses a colon after the page name (A:A1). If the name of the page or
sheet has a space in it, it is necessary to enclose the page or sheet name
with single quotes ('Total Sales'!A1). Multiple pages or sheets can be
represented by using the same techniques from above. For example to reference
the cell block between A2 and B4 in every page from B to G in Quattro Pro,
the reference would be B..G:A2..B4.
|
