Récemment, je cherchais désespérément une formule matricielle pour convertir plusieurs adresses de cellules en valeurs dans Google Sheets. L’Indirect n’est pas capable de le faire car vous pourriez avoir besoin d’utiliser plusieurs fonctions Indirect à cette fin.
Après quelques essais, j’ai pu écrire une formule qui permet de le faire ! Une alternative à Indirect sans la capacité de traverser la feuille.
Nous ne pouvons pas utiliser ma formule comme alternative à Indirect dans tous les cas. Mais dans notre cas, c’est une formule appropriée.
Un exemple peut vous aider à mieux comprendre le scénario (objectif).
Note: Dans la colonne H2:H10, j’ai utilisé la fonction FORMULATEXT pour vous montrer la formule dans la plage G2:G10.
La formule Indirect suivante ne fonctionnera pas car Indirect ne prend pas en charge la fonction ArrayFormula pour le moment.
=ArrayFormula(indirect(F2:F10))Limitations de l’alternative à Indirect
Nous pouvons utiliser une formule matricielle dans la cellule G2 pour convertir les adresses de cellules dans la plage F2:F10 en valeurs dans Google Sheets.
J’ai une formule matricielle pour l’indirection comme indiqué ci-dessus dans Google Sheets. Cependant, elle présente les limitations suivantes.
-
Les adresses de cellules provenant de différentes feuilles ne sont pas prises en charge.
- La formule n’a pas la capacité de traverser les feuilles.
-
Les adresses des cellules (critères) doivent être saisies dans un ordre particulier.
- Cela signifie que vous devez saisir les adresses des cellules à partir de la première ligne de la plage, puis à partir de la deuxième ligne de la plage, et ainsi de suite. Vous pouvez voir que j’ai respecté cette règle dans ma plage F2:F10.
-
Les adresses des cellules (critères) et la formule doivent être saisies en dehors de la plage.
- Voir les détails ci-dessous (après le point 4).
-
Performance lente / inexistante dans un ensemble de données très volumineux.
Je sais que je dois expliquer en détail le troisième point. Regardez notre exemple ci-dessus et les adresses des cellules dans la colonne F.
Vous pouvez voir que toutes les adresses des cellules données sont de gauche à la colonne F. Cela signifie que si la liste dans la colonne F contient l’adresse de cellule H14, alors la liste doit être déplacée vers la colonne I.
Comment convertir plusieurs adresses de cellules en valeurs dans Google Sheets
La formule peut sembler complexe, même si ce n’est pas le cas. Permettez-moi de vous aider à comprendre la formule en la codant étape par étape.
Logique de la formule alternative à Indirect
Pour convertir les adresses de cellules en valeurs sans utiliser la fonction Indirect dans Google Sheets, nous suivrons la logique suivante :
Nous ferons correspondre les adresses de cellules (critères) à la ‘plage’ correspondante. Selon l’exemple, nous ferons correspondre F2:F10 avec les adresses de cellules dans la plage A1:E.
La correspondance renverra un tableau correspondant à A1:E avec VRAI ou FAUX. Nous extrairons les valeurs des cellules correspondant aux valeurs VRAI.
Voici comment je vais mettre en œuvre cette logique étape par étape.
Étape 1 – Générer les adresses de cellules d’une plage
Dans mon exemple, je vais simplement utiliser la plage A1:E20. Ainsi, nous pouvons tester des formules individuelles sans affecter les performances des feuilles. Mais dans la formule finale, nous pouvons changer A1:E20 en A1:E.
Pour générer les adresses de cellules d’une plage, nous pouvons utiliser la formule Adresse suivante.
Syntaxe : ADRESSE(ligne, colonne, [mode_absolu_relatif], [utilisation_notation_a1], [sheet])
Nous devons utiliser uniquement les 3 premiers arguments.
L’argument ‘ligne’ doit être la première colonne de la plage et l’argument ‘colonne’ doit être la première ‘ligne’ de la plage. Ensuite, utilisez le ‘mode_absolu_relatif’ comme 4.
=ArrayFormula(adresse(ligne(A1:A20),colonne(A1:E1),4))Étape 2 – Faire correspondre les adresses de cellules à l’aide de Regexmatch
Dans les adresses de cellules ci-dessus renvoyées par la formule Adresse, nous pouvons faire correspondre nos critères. Il s’agit d’une étape importante pour convertir plusieurs adresses de cellules en valeurs dans Google Sheets.
Nous ne pouvons pas utiliser la fonction Match ici car elle permet uniquement de faire correspondre un critère ou des critères dans une seule ligne ou colonne. Notre cas concerne une plage multicolumn.
En remplacement de Match, nous pouvons utiliser la formule Regexmatch plus puissante.
Syntaxe: REGEXMATCH(text, expression_régulière)
L’argument ‘texte’ (la plage à faire correspondre) est la formule Adresse ci-dessus.
Nous devons former l’ ‘expression_régulière’ à faire correspondre en combinant les valeurs de la plage F2:F10. Dans la formule finale, nous pouvons utiliser F2:F à la place.
="^"&textjoin("$|^",true,F2:F10)&"$"J’ai utilisé la fonction Textjoin pour combiner les critères. Elle insère trois caractères en tant que délimiteur – accent circonflexe, dollar et barre verticale.
En utilisant des ampersands, j’ai ajouté un signe de circonflexe au début et un signe de dollar à la fin des critères combinés.
Nous avons maintenant le ‘texte’ et l ‘expression_régulière’ à utiliser dans la formule Regexmatch et voici la formule.
=ArrayFormula(regexmatch(adresse(ligne(A1:A20),colonne(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$"))Étape 3 – Formule pour convertir plusieurs adresses de cellules en valeurs dans Google Sheets
Voici la dernière étape.
Maintenant, nous pouvons facilement convertir plusieurs adresses de cellules en valeurs en utilisant une instruction logique SI.
Syntaxe : =ArrayFormula(si(sortie_de_la_formule_de_l_étape_2=vrai,A1:E20,))
Ce sera comme suit :
=ArrayFormula(si(regexmatch(adresse(ligne(A1:A20),colonne(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$")=VRAI,A1:E20,))Mais les valeurs seront dispersées. Nous pouvons les formater en modifiant la formule ci-dessus à l’aide de Textjoin, Split et Transpose.
Utilisez =ArrayFormula(transpose(split(TEXTJOIN("|",1, avant le SI et ,"|"))) à la fin de la formule.
Formule finale dans la cellule G2 :
=ArrayFormula(transpose(split(TEXTJOIN("|",1,si(regexmatch(adresse(ligne(A1:A20),colonne(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$")=vrai,A1:E20,)),"|")))Note: Comme mentionné ci-dessus, si vous le souhaitez, vous pouvez remplacer A1:A20, F2:F10 et A1:E20 par A1:A, F2:F et A1:E respectivement.
Exemple d’utilisation dans la vie réelle
Comme Indirect n’est pas utile pour une utilisation en matrice, nous pouvons suivre la méthode ci-dessus dans certains scénarios de la vie réelle. Voici un exemple.
- Comment trouver la dernière valeur dans chaque ligne dans Google Sheets.
C’est tout. Profitez-en !
