Quelle est la fonction Excel Décalage et comment les utiliser
Qu’est-ce que la fonction OFFSET?
La fonction de décalage est une fonction de recherche d’informations. Si vous avez une table volumineuse contenant certaines données et que vous souhaitez en récupérer des données, la meilleure façon de le faire est d’insérer une formule de décalage pour la table dans cette feuille de calcul ou une autre feuille dans le même classeur. Ainsi, une fois que vous avez créé la table de récupération des compensations, il vous suffit de saisir les données par exemple « mois » et vous obtiendrez les « ventes » ou « revenus » de ce mois à partir de cette table.
Parcourir le tableau est une autre option, mais envisageriez-vous l’option si vous avez des feuilles et des feuilles de données contenant toutes un tableau de données de 1000 colonnes? C’est là que le décalage est combiné avec d’autres fonctions.
Le concept de tableau croisé dynamique vient de l’offset. Un tableau croisé dynamique est un tableau général et à partir de cette combinaison spécifique de données et de graphiques est obtenu au fur et à mesure des besoins. Des tables dynamiques peuvent également être créées de la même manière. Pour ces tableaux, vous devrez créer la formule de décalage excel
Syntaxe de OFFSET
|
=OFFSET(reference,rows, columns, [height], [width]) |
Lignes – vous demandez à Excel de déplacer le nombre de lignes pour obtenir les informations. Dans ce cas, il doit se déplacer tout autour de la table et donc simplement laisser un espace vide indiqué par une virgule (,). Sinon, pour faire avancer une colonne, donnez la valeur 1 et pour déplacer une colonne avant, donnez la valeur -1.
Colonnes – il s’agit de conduire la fonction au nombre de colonnes. Le système de valeurs est le même que celui de la ligne.
Hauteur – la hauteur de la table, dans ce cas A11.
Largeur – largeur de la table, dans ce cas D11.
Une fois que vous avez configuré la fonction de décalage, il est important d’exécuter un essai pour éliminer toute erreur.
Prenons un tableau qui contient le nom, l’identifiant de messagerie, la date de naissance et l’âge.
Le tableau commence à la cellule A1, contenant l’en-tête «Nom». Les données sont valables, disons A11. Et les lignes courent jusqu’à D1. Le tableau entier exécute A1: D11. Vous voulez un système de récupération qui vous obtienne le nom lorsque vous entrez l’ID de messagerie, ou l’ID de messagerie lorsque vous entrez le nom avec d’autres détails. Vous créez une formule en satisfaisant les 5 arguments.
Le point de référence est la cellule à partir de laquelle la recherche doit commencer. Dans ce cas, cela devrait être A2. Telle est la norme générale.
Exemple:
Par exemple, vous donnez la référence de B4 et la ligne de décalage 0, et la colonne de décalage 1, la valeur de C4 sera renvoyée. Déroutant? Permet d’avoir quelques autres exemples. Le dernier exemple de fonction OFFSET indique comment SOMMEZ dynamiquement.
Fonction OFFSET pour obtenir la valeur de droite et de gauche d’une cellule Nous avons ce tableau.
Maintenant, si je veux obtenir la valeur de 2 cellules à droite de B2 (ce qui signifie D2). J’écrirai cette formule OFFSET:
|
=OFFSET(B2,0,2) |
La fonction OFFSET déplacera 2 cellules à droite de la cellule B4 et renverra sa valeur. Voir l’image ci-dessous si je veux obtenir la valeur de 1 cellule gauche à B2 (ce qui signifie A2). J’écrirai cette formule OFFSET:
|
=OFFSET(B2,0,-1) |
Le signe moins (-) indique le décalage pour inverser le mouvement.
Fonction OFFSET pour obtenir la valeur du dessous et du dessus d’une cellule Donc, encore une fois dans le tableau ci-dessus, si je veux obtenir la valeur de 2 cellules ci-dessus à B3 (ce qui signifie B5). J’écrirai cette formule OFFSET:
|
=OFFSET(B2,2,0) |
Si je veux obtenir la valeur de 1 cellule gauche à B2 (ce qui signifie A2). J’écrirai cette formule OFFSET:
|
=OFFSET(B2,-2,0) |
Astuce de pro: * Traitez un OFFSET comme un pointeur graphique où Référence est l’origine et la ligne et la colonne sont les axes x et y.
Fonction OFFSET pour additionner dynamiquement la plage Permet de voir cet exemple.
Dans le tableau ci-dessus Ligne du total à la fin du tableau. Au fil du temps, vous ajouterez des lignes à ce tableau. Ensuite, vous devrez modifier la plage de somme dans la formule.
Ici, nous pouvons prendre l’aide de la fonction OFFSET pour additionner dynamiquement. Actuellement, dans la cellule C11, nous avons = SOMME (C2: C10). Remplacez-le par la formule ci-dessous.
|
=SUM(C2:OFFSET(C11,-1,0)) |
Cette formule prend simplement la première ligne de données, puis additionne la plage au-dessus de la ligne totale.
Offset pour obtenir un tableau La fonction OFFSET a deux arguments optionnels, [height] et [width]. Ne les prenez pas pour acquis. Lorsque la fonction OFFSET est fournie avec les arguments [hauteur] et [largeur], elle renvoie un tableau à deux dimensions. Si un seul d’entre eux est passé comme argument, il retourne sur un tableau dimensionnel de valeurs.
Si vous tapez cette formule dans n’importe quelle cellule:
|
=SUM(OFFSET(C1,1,0,9,3)) |
Il additionnera les valeurs comprises entre C2 et 9 lignes en dessous et 3 colonnes à droite.
OFFSET (C1,1,0,9,3): Cela se traduira par \ {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8 , 3,9, 8,9,2, 3,5,4, 6,6,5}.
Donc en conclusion, offset est une fonction très utile d’Excel qui peut vous aider à résoudre de nombreux threads étranglés. Dites-moi comment vous utilisez la fonction OFFSET dans votre formule et où elle vous a le plus aidé.
La fonction de recherche est identique à offset mais utilise des fonctions telles que Match, Counta et IF pour travailler une table dynamique.
L’utilisation de l’offset dans Excel nécessite des compétences et une bonne connaissance des fonctions d’Excels et de la manière dont elles fonctionnent ensemble. Le retirer sans aucune erreur est le plus gros obstacle.
Voici toutes les notes d’observation concernant l’utilisation de la formule.
Remarques:
-
Cette formule fonctionne avec le texte et les nombres.
-
Il y a cinq arguments du décalage qui doivent être satisfaits pour obtenir une information sans erreur. Si vous écrivez une formule incorrecte, une erreur Ref se produit.
J’espère que vous avez compris Qu’est-ce que la fonction de décalage Excel et comment l’utiliser dans Excel. Explorez plus d’articles sur la recherche Excel et faites correspondre les valeurs à l’aide de formules ici. Si vous avez aimé nos blogs, partagez-les avec vos amis sur Facebook. Et vous pouvez également nous suivre sur Twitter et Facebook. Nous serions ravis de vous entendre, faites-nous savoir comment nous pouvons améliorer, compléter ou innover notre travail et le rendre meilleur pour vous. Écrivez-nous à [email protected].
Articles liés
link: / lookup-formulas-vlookup-function-to-Calculate-grade-in-excel [fonction VLOOKUP pour calculer la note dans Excel]: Pour calculer les notes IF et IFS ne sont pas les seules fonctions que vous pouvez utiliser. La RECHERCHEV est plus efficace et dynamique pour ces calculs conditionnels. Pour calculer les notes à l’aide de RECHERCHEV, nous pouvons utiliser cette formule.
link: / lookup-formulas-17-things-about-excel-vlookup [17 choses à propos d’Excel VLOOKUP]: VLOOKUP est le plus couramment utilisé pour récupérer les valeurs correspondantes, mais VLOOKUP peut faire beaucoup plus que cela. Voici 17 choses à propos de RECHERCHEV que vous devez savoir pour utiliser efficacement.
lien: / lookup-formulas-lookup-the-first-text-from-a-list [RECHERCHE le premier texte d’une liste dans Excel]: La fonction RECHERCHEV fonctionne très bien avec les caractères génériques. Nous pouvons l’utiliser pour extraire la première valeur de texte d’une liste donnée dans Excel. Voici la formule générique.
lien: / lookup-formulas-lookup-date-with-last-value-in-list [LOOKUP date with last value in list]: Pour récupérer la date qui contient la dernière valeur, nous utilisons la fonction LOOKUP. Cette fonction recherche la cellule qui contient la dernière valeur d’un vecteur, puis utilise cette référence pour renvoyer la date.
link: / lookup-formulas-how-to-retrieve-latest-price-in-excel [Comment récupérer le dernier prix dans Excel]: Il est courant de mettre à jour les prix dans toute entreprise et d’utiliser les derniers prix pour tout achat ou les ventes sont incontournables. Pour récupérer le dernier prix d’une liste dans Excel, nous utilisons la fonction LOOKUP. La fonction LOOKUP récupère le dernier prix.
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourci Excel pour augmenter votre productivité]: Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
link: / tips-if-condition-in-excel [Comment utiliser la fonction IF dans Excel]: L’instruction IF dans Excel vérifie la condition et renvoie une valeur spécifique si la condition est TRUE ou renvoie une autre valeur spécifique si FALSE .
link: / formulas-and-functions-introduction-of-vlookup-function [Comment utiliser la fonction RECHERCHEV dans Excel]: C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs dans différentes plages et feuilles.
link: / tips-countif-in-microsoft-excel [Comment utiliser la fonction COUNTIF dans Excel]: Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous n’avez pas besoin de filtrer vos données pour compter des valeurs spécifiques. La fonction Countif est indispensable pour préparer votre tableau de bord.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel]: Ceci est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.