Documentation/Calc Functions/ADDRESS/es

This page is a translated version of the page Documentation/Calc Functions/ADDRESS and the translation is 15% complete.

TDF LibreOffice Proyecto Document Liberation Blogs comunitarios Weblate Nextcloud Redmine Ask LibreOffice Donar

Inicio Funciones Calc | Lista de funciones | Abreviaturas

Nombre de la función:

DIRECCION

Categoría:

Spreadsheet

Resumen:

Constructs a cell reference based on the specified row and column numbers (and, optionally, worksheet and file names), returning the reference as a text string. Optional arguments control:

Whether the cell reference should use relative or absolute addressing (see the Addresses and References, Absolute and Relative help page for details).
Whether the cell reference should use either Calc A1, or Excel R1C1 formula syntax (see the Formula help page for details).
Whether the name of a sheet (with or without a file name) should be included in the cell reference.

The DIRECCION function is used to dynamically determine the reference of a cell of interest. The resulting string can then be passed to the INDIRECTO function to retrieve the cell's content.

Sintaxis:

DIRECCION(Row; Column[; Abs[; A1[; Sheet]]])

Devuelve:

Returns a text string representing a cell reference, constructed using the given arguments.

Argumentos:

Row is an integer value, or a reference to a cell containing such value, which is the row number to be used. A Calc spreadsheet may contain up to 1 048 576 rows (see below for additional limitations).

Column is an integer value, or a reference to a cell containing such value, which is the column number to be used. Although by default Calc usually refers to columns using letters (starting by "A" being column "1"), DIRECCION only accepts numbers for this argument. A Calc spreadsheet may contain up to 16384 columns (see below for additional limitations).

Limitaciones adicionales:

Limitaciones de XLS(X)
File Format	max ROWS			max COLUMNS				Comentario
XLSX	`1048576`	2^20	2²⁰	`16384`	2^14	2¹⁴	XFD	Excel 2007 y más reciente
XLS	`65536`	2^16	2¹⁶	`256`	2^8	2⁸	IV	Desde Excel 97 hasta 2003
XLS	`16384`	2^14	2¹⁴	`256`	2^8	2⁸	IV	Para Excel 5 y 95

Limitaciones de ODS
File Format	max ROWS			max COLUMNS				Comentario
ODS	`1048576`	2^20	2²⁰	`16384`	2^14	2¹⁴	XFD	LibreOffice desde la versión 7.4, inclusive
ODS	`1048576`	2^20	2²⁰	`1024`	2^10	2¹⁰	AMJ	LibreOffice antes de la versión 7.4
ODS	`1048576`	2^20	2²⁰	`1024`	2^10	2¹⁰	AMJ	OOo desde la versión 3.3, inclusive
ODS	`65536`	2^16	2¹⁶	`1024`	2^10	2¹⁰	AMJ	OOo desde la versión 3.0, inclusive; antes de la 3.3
ODS	`65536`	2^16	2¹⁶	`256`	2^8	2⁸	IV	OOo antes de la versión 3.0

Abs is an integer value in the range 1 to 8, or a reference to a cell containing an integer value in that range, which determines how absolute or relative addressing is used. If Abs is omitted, the function uses the value 1. Although there are eight acceptable values for this argument, there are only four possible meanings, as shown in the table below.

A1 is a logical value, or a reference to a cell containing a logical value, which determines the formula syntax that should be used in the returned string. If A1 is set to 0 or FALSO, the Excel R1C1 formula syntax is used. If A1 is omitted or set to any other numeric value, the Calc A1 formula syntax is used.

The following table shows the meaning of the various options for the Abs argument and shows typical outputs for different combinations of the Abs and A1 arguments, assuming that the Row and Column arguments are each set to 1.

Cell reference output syntax according to **Abs** and A1 arguments
Abs	Meaning	A1 = VERDADERO	A1 = FALSO
1, 5, or omitted	Fully absolute	$A$1	R1C1
2 or 6	Row absolute, column relative	A$1	R1C[1]
3 or 7	Row relative, column absolute	$A1	R[1]C1
4 or 8	Fully relative	A1	R[1]C[1]

Sheet is a text string (in double quotation marks), or a reference to a cell containing a text string, which is the name of the sheet to be appended at the beginning of the returned cell reference. The sheet name can include the name and location of a separate document.

If the Sheet argument is omitted, no sheet name (nor sheet separator) is included in the returned string, and the resulting cell reference will be taken to be relative to the current sheet.

Calc does not check that a sheet of the specified name currently exists. Note that if the Excel R1C1 formula syntax is selected (A1 set to either FALSO or 0) and Sheet is neither omitted nor an empty string, then DIRECCION returns a text string using an exclamation mark (!) as sheet separator, instead of the dot (.) separator used with Calc A1 formula syntax.

The following conditions may be encountered:

If Row is non-numeric, then DIRECCION reports a #¡VALOR! error.
If Row is a non-integer real number, then DIRECCION truncates the value to its integer part.
For the majority of cases, after truncation, if Row resides outside the range 1 ≤ Row ≤ max (see the tables above), then DIRECCION reports an invalid argument error (Err:502).
However, the allowed range of values for Row changes if the Excel R1C1 formula syntax is selected (A1 set to either FALSO or 0) and row relative addressing is selected (Abs set to 3, 4, 7, or 8). In these cases, negative row offsets can be specified (for example R[-1]C[1]).

Now, after truncation, if Row resides outside the range -(max-1) ≤ Row ≤ (max-1), DIRECCION reports an invalid argument error (Err:502).
In addition, the same error may be reported if a value in this range appears to reference a row that does not exist in the sheet (for example, the formula =DIRECCION(-1; 1; 4; 0) placed in cell A1 causes Err:502 but causes no error when placed in cell A2).
In other words, the resulting absolute address should still be valid, within the normal constraints for Row of 1 ≤ Row ≤ max; otherwise an error is reported.

If Column is non-numeric, then DIRECCION reports a #¡VALOR! error.
If Column is a non-integer real number, then DIRECCION truncates the value to its integer part.
For the majority of cases, after truncation, if Column resides outside the range 1 ≤ Column ≤ max (see the tables above), then DIRECCION reports an invalid argument error (Err:502).
However, the allowed range of values for Column changes if the Excel R1C1 formula syntax is selected (A1 set to either FALSO or 0) and column relative addressing is selected (Abs set to 2, 4, 6, or 8). In these cases, negative column offsets can be specified (for example R[1]C[-1]).

Now, after truncation, if Column resides outside the range -(max-1) ≤ Column ≤ (max-1), DIRECCION reports an invalid argument error (Err:502).
In addition, the same error may be reported if a value in this range appears to reference a column that does not exist in the sheet (for example, the formula =DIRECCION(1; -1; 4; 0) placed in cell A1 causes Err:502 but causes no error when placed in cell B1).
In other words, the resulting absolute address should still be valid, within the normal constraints for Column of 1 ≤ Column ≤ max; otherwise an error is reported.

If Abs is non-numeric, then DIRECCION reports a #¡VALOR! error.
If Abs is a non-integer real number, then DIRECCION truncates the value to its integer part.
After truncation, if Abs resides outside the range 1 ≤ Abs ≤ 8, then DIRECCION reports a #¡VALOR! error.

If A1 is non-numeric (and non-logical), then DIRECCION reports a #¡VALOR! error.

Detalles adicionales:

The DIRECCION function returns a text string, and not a real reference. If the "referenced" cell is actually moved (thus modifying its reference), the DIRECCION function has no automatic method to keep track of such move. The DIRECCION function builds the resulting text string depending only on its own arguments.
Calc's DIRECCION and INDIRECTO functions are closely related. DIRECCION returns a cell reference as text, which can be passed as an argument to INDIRECTO to retrieve the content of the referenced cell.
If you pass a string to INDIRECTO that was generated by DIRECCION, make sure that the two functions use the same setting for the A1 argument. If the two functions assume a different formula syntax, errors may result.
In versions of the ODF prior to 1.2, the specification for the DIRECCION function did not include the A1 argument to differentiate between Calc A1 and Excel R1C1 formula syntax.
- When opening a document that was saved in ODF 1.0/1.1 format, DIRECCION function calls that have a sheet name as their fourth argument will have that sheet name shifted to become the fifth argument. A new fourth argument with the value 1 will be inserted to select the default Calc A1 formula syntax.
- When saving a document in ODF 1.0/1.1 format, DIRECCION function calls will have their fourth argument removed. In this way, any function calls that previously used the Excel R1C1 formula syntax will revert back to default Calc A1 formula syntax.
Microsoft Excel represents some cell references differently to Calc, so this function is not always portable. For example, the formula DIRECCION(1; 1; 4; ; "Sheet2") returns "Sheet2.A1" in Calc, whereas the equivalent in Excel returns "Sheet2!A1".

Ejemplos:

Data for examples
	A	B	C	D
1
2				4
3				3
4				4
5				0
6				Sheet2
7
8				`'file:///C:/my-spreadsheets/my-test.ods'#$Sheet1`

Fórmula	Descripción	Devuelve
=DIRECCION(4; 3) =DIRECCION(4; 3; 1; VERDADERO(); "")	Apply the default settings for the Abs (1: fully absolute) and A1 (VERDADERO: `Calc A1` formula syntax) arguments. No value is supplied for the Sheet argument, so no sheet information is included in the returned cell reference.	$C$4
=DIRECCION(4; 3; ; ; "Sheet2")	The Abs and A1 arguments are omitted, so their default settings are again applied. A string is supplied for the Sheet argument, so that sheet information is included in the returned cell reference.	Sheet2.$C$4
=DIRECCION(4; 3; 2; ; "Sheet2") =DIRECCION(4; 3; 6; ; "Sheet2")	The A1 argument is omitted, so its default setting is again applied. With the Abs argument set to 2 or to 6, a cell reference is returned that uses row absolute / column relative addressing.	Sheet2.C$4
=DIRECCION(D2; D3; D4; D5; D6)	All arguments are present and utilize values stored in cells. Since the A1 argument is set to 0, the function returns a cell reference based on the `Excel R1C1` formula syntax.	Sheet2!R[4]C[3]
=DIRECCION(10; 5; 1; ; D8)	Returns a cell reference that includes a Windows-based file name and sheet name contained within the string defined for the Sheet argument (held in cell D8). Since Abs is `1`, then the arguments (R10C5) result in the string "`$E$10`". If cell $E$10 of the specified sheet and file contains the number 123, then the formula `=INDIRECTO(DIRECCION(10; 5; 1; ; D8))` returns the value 123.	`'file:///C:/my-spreadsheets/my-test.ods'#$Sheet1.$E$10`

Funciones de LibreOffice relacionadas:

Norma ODF:

Section 6.14.2, part 2

Funciones equivalentes (o similares) en Excel:

ADDRESS