Excel : Les fonctions de référence REF COLONNE ET REF LIGNE

Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit

Bienvenue dans ce cours Excel qui vous montre les fonctions de recherche et de référence Excel : REF COLONNE ET REF LIGNE

La fonction COLUMN REF et la fonction ROW REF

La fonction REF COLUMN d’Excel renvoie le numéro de colonne de référence d’une cellule spécifiée.
Par exemple, la formule = REF COLONNE (C10) renvoie le nombre 3, car la colonne C est la troisième colonne de la feuille de calcul.
La fonction ROW, quant à elle, renvoie le numéro de ligne d’une référence.
Ces deux fonctions trouvent une application intéressante, surtout si elles sont imbriquées dans d’autres fonctions telles que des fonctions de recherche telles que VLOOKUP ; COMPARER; INDEX etc…

Syntaxe de la fonction REF COLONNE

Fonction très simple dans son paramétrage, dont l’intérêt est lié à l’imbrication dans d’autres fonctions ou à la création de compteurs.
Dans la cellule B3 est insérée la fonction = REF COLONNE (D1), dont le seul argument est la référence à la cellule D1 de la feuille de calcul. le résultat est quatre puisque la cellule D1 se trouve dans la quatrième colonne de la feuille de calcul Excel.

Si le seul argument (facultatif) de la Fonction n’est pas indiqué, si la Fonction = REF COLONNE () est saisie dans la cellule B3, le résultat renvoyé serait 2, c’est-à-dire le numéro de la colonne de la cellule B3, dans laquelle la Fonction a été inséré.

Dans la fonction COLUMN REF, il est possible de se référer à une matrice de données.
Si la fonction = REF COLONNE (A1 : F1) est entrée dans la cellule B3, le résultat mis en surbrillance dans la barre de fonction est une matrice (voir l’image ci-dessous).

Les fonctions de matrice dynamique sont disponibles à partir des versions 2019 et 365 d’Excel. Dans ces versions, si la Fonction = REF COLONNE (A1 : F1) est saisie dans toutes les cellules adjacentes les valeurs 1, 2, 3, 4, 5, 6 sont proposées, respectivement les références de la matrice des colonnes 1 à 6 (A1 : F1).
Dans la barre de formule, sélectionner la fonction et appuyer sur la touche F11 met en surbrillance la matrice {1 \ 2 \ 3 \ 4 \ 5 \ 6}.
Dans les versions antérieures à 2019 ou 365, cette expansion ne se produit pas dans les cellules adjacentes et, par conséquent, le numéro de la colonne de la première cellule de la plage de données est inséré, dans le cas de notre exemple = COLUMN REF (A1 : F1) le numéro 1.

Imbriquer la fonction COLONNE dans la fonction VLOOKUP

Applicable depuis Excel 2019 ou 365.
Dans l’image ci-dessous, vous souhaitez rechercher dans la matrice F3 : I8, la catégorie de produit, le modèle et le prix unitaire référé au code produit a2.
Normalement pour obtenir cette information, dans les cellules B3, C3 et D3, il faut entrer 3 fonctions VLOOKUP distinctes qui vont extraire les index 2, 3 et 4 de la matrice F3 : I8 respectivement.

En exploitant l’expansion des données offerte par les matrices dynamiques, il est possible d’imputer une seule fonction dans la cellule B3 et d’indiquer les références de la matrice via la fonction COLUMN REF
= VLOOKUP (A3, F3 : I8, COLONNE REF (B1 : D1), 0)
En effet, le premier argument est la valeur à rechercher (c’est-à-dire la référence de la cellule contenant le code a2), le second argument identifie la matrice de données dans la première colonne de laquelle le code doit être recherché. le troisième argument représente la référence d’index (c’est-à-dire la colonne) que nous voulons renvoyer. le quatrième argument 0 (zéro) ou faux indique que la recherche doit être exacte et non par approximation.
En tant que troisième argument, c’est-à-dire l’index, au lieu de définir une colonne spécifique, la fonction COLUMN REF a été insérée et une plage de cellules (B1 : D1) a été indiquée comme argument qui renvoie les valeurs 2, 3 et 4 (c’est-à-dire les indices des colonnes qui renvoient la catégorie de produit, le modèle et le prix unitaire rapporté)
Évidemment, il aurait été pareil de choisir une référence différente telle que B12 : D12, l’important est que cela renvoie toujours les valeurs 2, 3 et 4. car les colonnes B, C et D sont dans ces positions spécifiques.
Une fois la fonction insérée dans la cellule B3, en appuyant sur Entrée, la fonction s’étend automatiquement également aux cellules suivantes C3 et D3, renvoyant correctement les valeurs présentes dans la matrice de données.

Syntaxe de la fonction ROW

= RIF.RIGA ([réf])
dans lequel:
ref : (argument facultatif) représente le numéro de la ligne de la feuille de calcul en référence à une cellule ou à une plage de cellules (matrice)

Exemple d’application de la fonction LINE REF

Comme indiqué précédemment pour COLUMN REF, ces fonctions ne sembleraient pas très utiles si elles étaient appliquées seules, mais elles peuvent être très utiles imbriquées dans les fonctions de recherche.
Quant à l’imbrication dans la fonction VLOOKUP dans la définition de plusieurs index, vous pourriez facilement mettre RIF.RIGA au lieu de REF.COLUMN, en fait ce qui vous intéresse c’est le retour d’une matrice qui va identifier les index.
Dans le cas spécifique de RIF.RIF (comme mis en évidence dans l’image ci-dessous) dans le processus d’expansion de la matrice dynamique, les résultats ne sont pas répertoriés horizontalement mais verticalement sous la cellule dans laquelle la fonction = VLOOKUP (A3 ; F3 a été saisi) : I8 ; RIF.RIGA (K2 : K4 ); 0)

Une autre application intéressante de la RIF.RIF.Function pourrait être de créer un compteur à l’intérieur d’une formule qui devrait ensuite être glissé.
Pour illustrer le résultat, la fonction ROW dans ce cas est insérée seule et non imbriquée.
Exemple : supposons que vous deviez créer un nombre progressif commençant par le chiffre 1 à partir de la cellule B3.
La Fonction suivante peut être insérée dans la cellule B3 : = ROW REF() – 2 (le -2 sert à corriger la référence réelle de la cellule B3 qui serait 3)

Merci à www.valterborsato.it 🙂