位址和參照, 絕對和相對

Cell references

An individual cell is fully identified by the sheet it belongs, the column identifier (letter) located along the top of the columns and a row identifier (number) found along the left-hand side of the spreadsheet. On spreadsheets read from left to right, the complete reference for the upper left cell of the sheet is Sheet.A1.

Cell ranges

You can reference a set of cells by referencing them in ranges. Ranges can be a block of cells, entire set of columns and entire set of rows. The range A1:B2 is the first four cells in the upper left corner of the sheet. Range A:E contains all the cells of column A, B, C, D and E. Range 2:5 contains all the cells of row 2, 3, 4 and 5.

The reference to a cell of another document contains the fully qualified name of the other document between single quotes ('), then a hash #, then the name of the sheet of the other document, followed by a period and the name or reference of the cell.

For example, .

The path and document name URI must always be enclosed in single quotes. If the name contains single quotes (') they must be escaped using two single quotes (''). Only a sheet name may not be quoted if it does not contain a space or a character that would be an operator or the name would be pure numeric (for example, '123' must be quoted).

參照運算子

這些運算子會傳回零個、一個或多個儲存格的儲存格範圍。

範圍具有最高等級的優先順序，接著是交集，最後才是併集。

運算子	名稱	Example
:	區域	A1:C108, A:D or 3:13
!	平均數集合	SUM(A1:B6!B5:C12) 在此，儲存格 B5 和 B6 會位於平均數集合中，並計算出它們的總和。
~	連結或併集	Takes two references and returns a reference list, which is a concatenation of the left reference followed by the right reference. Double entries are referenced twice. =COUNT(A1:B2~B2:C3) counts values of A1:B2 and B2:C3. Note that the cell B2 is counted twice. =INDEX(A1:B2~C1:D2;2;1;2) selects cell C2, that is, the first cell of the second row, first column, of the second range (C1:D2) of the range list.

A reference list is not allowed inside an array expression.

相對定址

欄 A 以及列 1 的儲存格定位為 A1。您要指出和儲存格相連的區塊，可以指出該區域左上角的位址，然後輸入一個冒號，接著再輸入該區塊右下角儲存格的位址。這個左上角最前面四個儲存格的矩形區塊稱為 A1:B2。

這種一個區塊定址的類型對於 A1:B2 是一個相對的參照。這裡的相對指的是，如果您複製這個公式，這個參照符合這個填入。

絕對定址

Absolute referencing is the opposite of relative addressing. A dollar sign is placed before each letter and number in an absolute reference, for example, $A$1:$B$2.

LibreOffice can convert the current reference, in which the cursor is positioned in the input line, from relative to absolute and vice versa by pressing F4. If you start with a relative address such as A1, the first time you press this key combination, both row and column are set to absolute references ($A$1). The second time, only the row (A$1), and the third time, only the column ($A1). If you press the key combination once more, both column and row references are switched back to relative (A1)

LibreOffice Calc shows the references to a formula. If, for example, you click the formula =SUM(A1:C5;D15:D24) in a cell, the two referenced areas in the sheet will be highlighted in color. For example, the formula component "A1:C5" may be in blue and the cell range in question bordered in the same shade of blue. The next formula component "D15:D24" can be marked in red in the same way.

什麼時候使用相對參照，什麼時候使用絕對參照？

要以什麼區別相對參照？假設您要在儲存格 E1 計算區塊 A1:B2 中的儲存格小計。輸入 E1 的公式為：=SUM(A1:B2)。如果您稍後決定要在欄 A 之前插入新的欄，您要增加的元素則會在 B1:C2 且公式會在 F1 而不是 E1。插入新的欄之後，您因此必須檢查並校正目前試算表中或其他試算表中的的所有公式。

幸運的是，LibreOffice 會替您完成此工作。插入新欄 A 後，公式 =SUM(A1:B2) 將自動更新為 =SUM(B1:C2)。插入新列 1 時，列號也會自動調整。在 LibreOffice Calc 中，每當移動參照區塊時，絕對參照和相對參照就會自動進行調整。但請註意，複製公式時只調整相對參照，而不調整絕對參照。

要參照試算表中特定的儲存格進行計算時，絕對參照會被使用。如果您沒有將儲存格設定為絕對參照，那麼，當將參照了此儲存格的公式複製到原來儲存格下方的儲存格中時，此公式所參照的儲存格也將跟著下移。

除了插入新的列或欄，當參照特定儲存格的現有公式被複製到此試算表的另一區塊中時，參照也會變更。假定您在第 10 列中輸入了公式 =SUM(A1:A9)。如果要計算右側相鄰欄的總和，只需將此公式複製到右側的儲存格中。複製到欄 B 中的公式將自動調整為 =SUM(B1:B9)。