OFFSET Function
Returns a reference to a range that is offset from a starting cell by a given number of rows and columns. Creates dynamic ranges for SUM, AVERAGE, and other functions.
ExcelGoogle Sheets
Syntaxe
OFFSET(reference, rows, cols, [height], [width])Paramètres
| Paramètre | Description |
|---|---|
| reference | The starting cell or range. |
| rows | Number of rows to move from the reference (positive = down, negative = up). |
| cols | Number of columns to move (positive = right, negative = left). |
| height(optionnel) | The height in rows of the returned reference. |
| width(optionnel) | The width in columns of the returned reference. |
Exemples
Sum the last 5 values in a column
=SUM(OFFSET(A1, COUNTA(A:A)-5, 0, 5, 1))Résultat: 250
Get a value 3 rows below and 2 columns right
=OFFSET(A1, 3, 2)Résultat: Value from C4
Create a dynamic range for a chart
=AVERAGE(OFFSET(B1, 0, 0, COUNTA(B:B), 1))Résultat: 85.5
Conseils et bonnes pratiques
- •OFFSET is volatile like INDIRECT — use sparingly in large workbooks.
- •Prefer INDEX for non-volatile alternatives when possible.
- •Combine with COUNTA to create ranges that grow as data is added.
Formules associées
Besoin d'une autre formule ?
Décrivez ce dont vous avez besoin en texte simple et notre IA générera la formule pour vous.
Essayer le Générateur de formules