Formule Excel OFFSET : Référence Dynamique et Décalage

Maîtrisez la formule Excel OFFSET pour créer des références dynamiques. Apprenez sa syntaxe, ses cas d'utilisation et évitez les erreurs courantes.

Syntaxe

=OFFSET(référence; lignes; colonnes; [hauteur]; [largeur])

référence: La cellule de départ pour le décalage. lignes: Le nombre de lignes à décaler (positif pour le bas, négatif pour le haut). colonnes: Le nombre de colonnes à décaler (positif pour la droite, négatif pour la gauche). hauteur (facultatif): La hauteur de la plage renvoyée. largeur (facultatif): La largeur de la plage renvoyée.

Utilisation dans Google Sheets

Google Sheets prend en charge la fonction OFFSET de manière similaire à Excel. La syntaxe et le fonctionnement sont identiques. Cependant, certaines limitations de performance peuvent apparaître avec des feuilles de calcul très volumineuses ou des formules OFFSET complexes. Il est donc conseillé de tester les performances et d'envisager des alternatives comme INDEX et MATCH si nécessaire. La compatibilité est donc très bonne.

Cas d'utilisation

Création de graphiques dynamiques

Calculs de moyennes mobiles

Extraction de données de tableaux croisés dynamiques

Exemples pratiques

Décalage simple

Données : A1 contient 10, B3 contient 25

=OFFSET(A1; 2; 1)

Déplace de 2 lignes vers le bas et 1 colonne vers la droite à partir de A1.

Résultat : 25

Plage dynamique avec hauteur et largeur

Données : A1:B4 contient une matrice de nombres

=SUM(OFFSET(A1; 1; 0; 3; 2))

Somme des cellules de la plage de 3 lignes et 2 colonnes en commençant une ligne en dessous de A1.

Résultat : Somme de la plage A2:B4

Extraction dynamique d'une colonne

Données : A1:E10 contient des données, C1 contient le numéro de la colonne à extraire.

=AVERAGE(OFFSET(A1; 0; C1; 10; 1))

Calcule la moyenne des valeurs dans la colonne spécifiée par la valeur dans C1, sur 10 lignes.

Résultat : Moyenne des valeurs de la colonne spécifiée.

Conseils et astuces

Utiliser des noms définis pour les cellules de référence pour une meilleure lisibilité.

Tester la formule avec différentes valeurs pour s'assurer qu'elle fonctionne correctement.

Combiner OFFSET avec d'autres fonctions pour des calculs plus complexes.

Privilégier INDEX et MATCH si la performance est critique, surtout avec des grandes feuilles.

Erreurs courantes

#REF!

Le décalage sort des limites de la feuille de calcul.

Vérifier les valeurs des arguments 'lignes' et 'colonnes' pour s'assurer qu'elles ne dépassent pas les limites de la feuille.

#VALUE!

Un des arguments n'est pas un nombre.

S'assurer que les arguments 'lignes' et 'colonnes' sont des valeurs numériques valides.