Documentazione/Funzioni di Calc/INDICE

    From The Document Foundation Wiki
    This page is a translated version of the page Documentation/Calc Functions/INDEX and the translation is 15% complete.
    Other languages:


    Nome funzione:

    INDICE

    Categoria:

    Foglio di calcolo

    Riepilogo:

    Nella sua forma più semplice, INDICE può essere utilizzato per recuperare il valore in una cella particolare, specificata dai relativi numeri di riga e colonna all'interno di una tabella di dati. A seconda del contesto, è anche possibile utilizzare INDICE per determinare il riferimento della cella all'intersezione della riga e della colonna specificate.

    INDICE è una potente funzione che può essere utilizzata per cercare valori in più intervalli di celle non contigui e può essere utilizzata in un contesto di matrice per restituire più valori. La flessibilità aumenta quando INDICE viene utilizzato insieme a Funzione CONFRONTA, che consente alle formule di reagire meglio ai cambiamenti nei dati di input.

    Sintassi:

    La sintassi generale della funzione INDICE è:

    • INDICE(Riferimento[; Riga[; Colonna[; Intervallo]]])

    Nel caso speciale in cui il primo argomento ha una sola colonna, è possibile utilizzare la seguente sintassi semplificata:

    • INDICE(Riferimento; Riga)

    Nel caso speciale in cui il primo argomento ha una sola riga, è possibile utilizzare la seguente sintassi alternativa:

    • INDICE(Riferimento; Colonna)

    Restituisce:

    Tecnicamente, INDICE restituisce un riferimento di cella. Tuttavia, a seconda della formula utilizzata, il valore restituito può essere utilizzato come valore di dati (il cui tipo è determinato dal set di dati di input) o come riferimento di cella.

    Argomenti:

    Reference is a reference list specifying the range(s) of cells occupied by the input data table(s). It takes one of the following forms:

    • A single cell range (for example, A1:E10).
    • Multiple cell ranges can be passed as a single argument by providing them in a separated list, surrounded by parentheses – for example, =INDICE((A1:B6; C2:D7; E3:F8); …). In this case Calc will reformat your function call to use the tilde reference concatenation operator – for example =INDICE((A1:B6~C2:D7~E3:F8); …). Alternatively, you can use the tilde reference concatenation operator directly without parentheses – for example =INDICE(A1:B6~C2:D7~E3:F8; …).
    • A single cell range referenced by the name of a database range (defined by selecting Data ▸ Define Range on the Menu bar).
    • One or more cell ranges referenced by the name of a named range. Named ranges defined using Sheet ▸ Named Ranges and Expressions ▸ Define on the Menu bar can be configured to comprise multiple areas by using the tilde reference concatenation operator in the Range or formula expression field of the Define Name dialog.
    • An inline array (for example, {1, 2, 3; 4, 5, 6}).

    Row is a non-negative integer value, or a reference to a cell containing such a value, that is the required row offset from the top left of the data table. In the case of an array formula, the Row argument can be entered as an inline array to retrieve multiple values. In the array context, omitting the Row argument (with two consecutive semicolons) or setting it to the special value 0 (zero) indicates that values for all rows should be returned for the specified column.

    Column is a non-negative integer value, or a reference to a cell containing such a value, that is the required column offset from the top left of the data table. In the case of an array formula, the Column argument can be entered as an inline array to retrieve multiple values. In the array context, omitting the Column argument (with two consecutive semicolons) or setting it to the special value 0 (zero) indicates that values for all columns should be returned for the specified row.

    Range is a positive integer value (defaults to 1), or a reference to a cell containing such a value, that indicates the subrange to be used when the Reference argument comprises multiple areas. For example, if Reference is set to A1:B6~C2:D7~E3:F8, then set Range to 1 for cell range A1:B6, set Range to 2 for cell range C2:D7, and set Range to 3 for cell range E3:F8. In the case of an array formula, the Range argument can be entered as an inline array to retrieve multiple values.

    The following conditions may arise:

    • If any of Row, Column, or Range is non-numeric, then INDICE reports a #VALORE! error.
    • If any of Row, Column, or Range is a non-integer value, then INDICE truncates it to an integer.
    • If, after any truncation, either Row or Column is a negative value, then INDICE reports an invalid argument error (Err:502).
    • If, after any truncation, Row is greater than the number of rows in the cell range indexed by the Range argument, then INDICE reports an invalid argument error (Err:502).
    • If, after any truncation, Column is greater than the number of columns in the cell range indexed by the Range argument, then INDICE reports an invalid argument error (Err:502).
    • If, after any truncation, Range is less than 1 or greater than the number of cell ranges defined in Reference, then INDICE reports a #RIF! error.

    Dettagli aggiuntivi:

    • INDICE can be used in an array formula. For example, enter the array formula { =INDICE($A$1:$D$4; {1;3}; 2) } using Ctrl+Shift+Enter ("CSE") rather than just Enter. Two values are returned – the value from cell B1 is displayed in the formula cell and the value from cell B3 is displayed in the cell below the formula cell.
    • For inline array values, the respective separators to be used in formulas can be configured in Tools ▸ Options ▸ LibreOffice Calc ▸ Formula, in the Separators area.

    Esempi:

    The first batch of examples utilize the following table of data:

    A B C D
    1
    2 4 5 6
    3 red green blue
    4 VERO FALSO VERO
    Formula Descrizione Restituisce
    =INDICE(B2:D4; 1; 1) Returns the data in cell B2. The Row and Column arguments are offsets relative to the supplied cell range, not relative to the spreadsheet. 4

    =INDICE(DataTable; G1; G2) where DataTable is the name of a database range encompassing the cell range B2:D4 and where cells G1 and G2 both contain the number 2.

    Returns the value in cell C3, which is the intersection of the second row with the second column within DataTable. green
    =INDICE(C3:D4; 2; 2) Returns the value in cell D4. VERO
    =INDICE(B2:B4; 3) Returns the value in the third cell, B4. Utilizes the simplified syntax available for a single-column data set. VERO
    =INDICE(B3:D3; 2) Returns the value in the second cell, C3. Utilizes the simplified syntax available for a single-row data set. green
    =INDICE(B2:D4; 2; 0) }

    using CSE.

    Returns a row of the three values from the second row of the input data. The string "red" appears in the formula's cell; "green" appears in the next cell to the right, with "blue" in the cell to the right of that.

    red green blue
    =INDICE(B2:D4; 0; 2) }

    using CSE.

    Returns a column of the three values from the second column of the input data. The number 5 appears in the formula's cell; "green" appears in the cell below, with the number 0 (zero) in the cell below that. The original Boolean FALSO is converted to its numeric value in this scenario.

    5
    green
    0
    =INDICE(B2:D4; {2; 3}; 3) }

    using CSE.

    Returns a column of the second and third values from the third column of the reference range; cells D3 and D4 of the input data. The string "blue" appears in the formula's cell and the value 1 appears in the cell below. The original Boolean VERO is converted to its numeric value in this scenario.

    blue
    1

    The following table gives additional examples that are not related to the table of sample data above.

    Formula Descrizione Restituisce
    =INDICE(A1:C3~A5:C7~A9:C11; 1; 1)

    =INDICE(A1:C3~A5:C7~A9:C11; 1; 1; 1)

    When no value is specified for the Range argument, the default of 1 is used. Returns the data in the cell that resides at the intersection of row 1 and column 1, in the first area defined in the Reference argument.

    Contents of cell A1
    =INDICE(A1:C3~A5:C7~A9:C11; 1; 1; 2) Returns the data in the cell that resides at the intersection of row 1 and column 1, in the second area defined in the Reference argument. Contents of cell A5

    =INDICE(ThreeAreas; 3; 3; 3) where ThreeAreas has been defined as a named range using the reference expression $A$1:$C$3~$A$5:$C$7~$A$9:$C$11.

    Returns the data in the cell that resides at the intersection of row 3 and column 3, in the third area defined in the Reference argument. Contents of cell C11

    =INDICE(ThreeAreas; 2; 2; {1;2;3}) } using CSE. ThreeAreas has been defined as a named range using the reference expression $A$1:$C$3~$A$5:$C$7~$A$9:$C$11. {1;2;3} is a one-column inline array of 3 rows for the Range argument, using semicolon as row-separator.

    Returns the data in the cell that resides at the intersection of row 2 and column 2, in each of the three areas defined in the Reference argument. The three values are returned, arranged in a column.

    Contents of cells B2, B6, and B10
    =INDICE({1,3,5; 7,9,10}; {2;1}; 1; 1) }

    using CSE. {1,3,5; 7,9,10} is a 3-columns by 2-rows inline array, using semicolon as row-separator and comma as column-separator.

    Organized in one column of two rows: from the second line, return the first value; from the first line, return the first value.
    7
    1
    =CELLA("address"; INDICE(A2:C6; 2; 2))

    The INDICE function returns the reference to cell B3. The CELLA function then returns the absolute address of cell B3.

    $B$3

    Funzioni di LibreOffice correlate:

    AREE

    SCEGLI

    CONFRONTA

    Standard ODF:

    Funzioni equivalenti (o simili) di Excel:

    INDEX