Offset formule dans Microsoft Excel
VLOOKUP, HLOOKUP et INDEX-MATCH sont des moyens célèbres et courants de rechercher des valeurs dans un tableau Excel. Mais ce ne sont pas les seuls moyens de rechercher des valeurs dans Excel. Dans cet article, nous allons apprendre à utiliser la fonction OFFSET avec la fonction MATCH dans Excel. Oui, vous avez bien lu, nous utiliserons la fameuse fonction OFFSET d’Excel pour rechercher une certaine valeur dans un tableau Excel.
Formule générique
|
= |
Lisez ceci attentivement:
StartCell: il s’agit de la cellule de départ de la table de recherche. Disons que si vous souhaitez rechercher dans la plage A2: A10, la StartCell sera A1.
RowLookupValue: il s’agit de la valeur de recherche que vous souhaitez rechercher dans les lignes sous StartCell. RowLookupRange: il s’agit de la plage dans laquelle vous souhaitez rechercher le RowLookupValue. C’est la plage ci-dessous StartCell (A2: A10). ColLookupValue: il s’agit de la valeur de recherche que vous souhaitez trouver dans les colonnes (en-têtes).
ColLookupRange: Il s’agit de la plage dans laquelle vous souhaitez rechercher le ColLookupValue. C’est la plage sur le côté droit de StartCell (comme B1: D1).
Donc, assez de théorie. Commençons par un exemple.
Exemple: Recherche de ventes à l’aide de la fonction OFFSET et MATCH à partir d’un tableau Excel Nous avons ici un exemple de tableau des ventes effectuées par différents employés au cours de différents mois.
Maintenant, notre patron nous demande de donner les ventes effectuées par Id 1004 au cours du mois de juin. Il existe de nombreuses façons de le faire, mais puisque nous apprenons la formule OFFSET-MATCH, nous les utiliserons pour rechercher la valeur souhaitée.
Nous avons déjà la formule générique ci-dessus. Il suffit d’identifier ces variables dans ce tableau.
StartCell est B1 ici. RowLookupValue est M1. RowLookupRange est B2: B1o (plage sous la cellule de départ).
ColLookupValue est dans la cellule M2. ColLookupRange est C1: I1 (plage d’en-tête à droite de StartCell).
Nous avons identifié toutes les variables. Mettons-les dans une formule Excel.
Écrivez cette formule dans la cellule M3 et appuyez sur le bouton Entrée.
Lorsque vous appuyez sur le bouton Entrée, vous obtenez le résultat rapidement. La vente effectuée ID 1004 au mois de juin est 177.
Comment ça marche?
Le travail de link: / counting-the-offset-function-in-excel [OFFSET function] `est de s’éloigner de la cellule de départ donnée vers la ligne et les colonnes données, puis de renvoyer la valeur de cette cellule. Par exemple, si j’écris OFFSET (B1,1,1), la fonction OFFSET ira à la cellule C2 (1 cellule vers le bas, 1 cellule vers la droite) et renvoie sa valeur.
Ici, nous avons la formule
|
OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0)) |
Le premier link: / lookup-formulas-excel-match-function [fonction MATCH] retourne l’indice de M1 (1004) dans la plage B2: B10, qui est 4.
Maintenant, la formule est
|
OFFSET(B1,4,MATCH(M2,C1:I1,0)) |
La fonction MATCH suivante renvoie l’indice de M2 (‘Jun’) dans la plage C1: I1, qui I7. Maintenant, la formule est OFFSET (B1,4,7). Maintenant, la fonction OFFSET déplace simplement 4 cellules vers la cellule B1 qui l’amène à B5. Ensuite, la fonction OFFSET se déplace de 7 à gauche vers B5, ce qui l’amène à I5. C’est ça. La fonction OFFSET renvoie la valeur de la cellule I5 qui 177.
Avantages de cette technique de recherche?
C’est rapide. Le seul avantage de cette méthode est qu’elle est plus rapide que les autres méthodes. La même chose peut être faite en utilisant la fonction INDEX-MATCH ou la fonction RECHERCHEV. Mais il est bon de connaître certaines alternatives.
Cela peut être utile un jour.
Voici quelques remarques concernant l’utilisation de la formule de décalage dans Excel.
Remarques:
-
OFFSET ne renvoie qu’une référence, aucune cellule n’est déplacée.
-
Les lignes et les colonnes peuvent être fournies sous forme de nombres négatifs pour inverser leur direction de décalage normale – les colonnes négatives sont décalées vers la gauche et les lignes négatives sont décalées au-dessus.
-
OFFSET est une « fonction volatile » – elle recalculera à chaque changement de feuille de calcul. Les fonctions volatiles peuvent ralentir l’exécution des classeurs plus volumineux et plus complexes.
-
OFFSET affichera #REF! valeur d’erreur si le décalage est en dehors du bord de la feuille de calcul.
-
Lorsque la hauteur ou la largeur est omise, la hauteur et la largeur de la référence sont utilisées.
-
OFFSET peut être utilisé avec toute autre fonction qui s’attend à recevoir une référence.
-
La documentation Excel indique que la hauteur et la largeur ne peuvent pas être négatives, mais que les valeurs négatives fonctionnent.
J’espère que cet article sur la façon d’utiliser la formule de décalage dans Microsoft Excel est explicatif. Trouvez plus d’articles sur les valeurs de recherche et les formules Excel associées 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:
lien: / lookup-formulas-use-index-and-match-to-lookup-value [Utiliser INDEX et MATCH pour rechercher une valeur]: La formule INDEX-MATCH est utilisée pour rechercher dynamiquement et précisément une valeur dans une table donnée. Il s’agit d’une alternative à la fonction RECHERCHEV et elle surmonte les lacunes de la fonction RECHERCHEV.
link: / tips-sum-by-offset-groups-in-rows-and-columns [Sum by OFFSET groups in Rows and Columns]: La fonction OFFSET peut être utilisée pour additionner dynamiquement des groupes de cellules. Ces groupes peuvent être n’importe où dans la feuille.
link: / lookup-formulas-retrieving-every-nth-value-in-a-range [Comment récupérer chaque nième valeur dans une plage dans Excel]: En utilisant la fonction OFFSET d’Excel, nous pouvons récupérer des valeurs à partir de lignes alternées ou Colonnes. Cette formule utilise la fonction ROW pour alterner entre les lignes et la fonction COLUMN pour alterner dans les colonnes.
link: / counting-the-offset-function-in-excel [Comment utiliser la fonction OFFSET dans Excel]: La fonction OFFSET est une fonction Excel puissante qui est sous-estimée par de nombreux utilisateurs. Mais les experts connaissent la puissance de la fonction OFFSET et comment pouvons-nous utiliser cette fonction pour effectuer des tâches magiques dans la formule Excel. Voici les bases de la fonction OFFSET.
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité]: Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
link: / formulas-and-functions-introduction-of-vlookup-function [Comment utiliser la fonction Excel VLOOKUP]: 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 le]
lien: / formules-et-fonctions-introduction-de-vlookup-function [Excel]
lien: / tips-countif-in-microsoft-excel [Fonction COUNTIF]: Compter 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.