Ce tutoriel illumine la fonction de décalage Excel souvent comprise.

La fonction de décalage génère une référence à un décalage de plage à partir d'une cellule de départ par un nombre spécifié de lignes et de colonnes. Explorons sa fonctionnalité à travers une explication concise suivie d'applications pratiques.

• Applications pratiques de décalage
  • Compensation avec la somme
  • Décalé avec moyen / max / min
  • Création de gammes dynamiques avec décalage
  • Offset et Vlookup
• Limitations et alternatives au décalage

Fonction de décalage Excel: syntaxe et utilisation de base

Le décalage renvoie une cellule ou une plage décalée à partir d'une cellule ou d'une plage donnée.

Sa syntaxe est:

OFFSET(reference, rows, cols, [height], [width])

Les trois premiers arguments sont obligatoires; Les deux derniers sont facultatifs. Tous les arguments peuvent être des références cellulaires ou des résultats de formule.

• Arguments requis:

  • reference : la cellule de départ ou la plage.
  • rows : Nombre de lignes à compenser (positif: en bas, négatif: up).
  • cols : Nombre de colonnes à compenser (positif: droite, négatif: gauche).

• Arguments facultatifs:

  • height : Nombre de lignes dans la plage retournée (doit être positive).
  • width : Nombre de colonnes dans la plage retournée (doit être positive).

Si height ou width sont omises, elles par défaut à la hauteur ou à la largeur de reference . Remarque: le décalage est une fonction volatile, ce qui a un impact potentiellement sur les performances de la feuille de travail.

Un exemple de décalage simple:

=OFFSET(A1,3,1)

Cela se déplace de A1 à trois lignes vers le bas et une colonne à droite, renvoyant la valeur en B4.

Considérations clés:

• Le décalage ne déplace pas les cellules; il renvoie une référence.
• Pour les retours de plage, rows et cols se réfèrent à la cellule supérieure gauche.
• Une reference non valide (cellules non adjacentes) se traduit par un #Value! erreur.
• Le décalage au-delà du bord de la feuille de calcul donne un #ref! erreur.
• Offset fonctionne dans les fonctions acceptant des références cellulaires / plages (par exemple, SUM(OFFSET(...)) ).

Par exemple, SUM(OFFSET(A1,3,1,1,3)) Somme B4: D4.

Pour compenser à partir de la cellule actuelle, combinez INDIRECT , ADDRESS , ROW() et COLUMN() :

=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),3,1,1,3))

Pourquoi utiliser le décalage?

Offset excelle dans la création de plages dynamiques, l'adaptation à la modification des données et la référence des plages basée sur une cellule de départ sans conna?tre l'adresse exacte.

Applications pratiques de décalage

Décalage avec la somme:

• Somme dynamique: pour résumer une plage qui comprend automatiquement de nouvelles lignes ajoutées au-dessus de la formule:

=SUM(B2:OFFSET(B9,-1,0))

• Additionner les n lignes des dernières n:

=SUM(OFFSET(B1,COUNT(B:B)-E1 1,0,E1,1)) (ou en utilisant COUNTA pour les cellules non vides)

Compense avec la moyenne, le max, le min: remplacer SUM par AVERAGE , MAX ou MIN dans les exemples ci-dessus pour des calculs dynamiques similaires.

Création de gammes dynamiques avec décalage:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Cela crée une plage dynamique basée sur le nombre de cellules non vergées dans la colonne A de Sheet1. Ajustez les en-têtes au besoin.

Offset et VLOOKUP: Offset permet des recherches gauche ou ascendante, pas possible avec VLookup standard ou HLookup. (Formules complexes omises pour la concision, voir Original pour plus de détails.)

Limitations et alternatives au décalage

La volatilité d'Offset peut avoir un impact sur les performances et les formules complexes sont difficiles à déboguer. Considérez ces alternatives:

1. Tables Excel: les tableaux créent des plages dynamiques qui s'adaptent automatiquement aux modifications de données.
2. Fonction d'index: une alternative non volatile pour les références de plage dynamique.
3. Fonction indirecte: crée des références dynamiques à partir de diverses sources.

Le classeur de pratique téléchargeable (Link in Original) offre une expérience pratique.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!