Extraire aux intersections des lignes et colonnes

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Extraire aux intersections

Grâce à une formule matricielle brillante, cette nouvelle astuce Excel va démontrer comment extraire de multiples informations croisées par des lignes et des colonnes.



Sur l'exemple illustré par la capture, à partir d'un tableau placé sur la gauche de la feuille, nous produisons l'extraction des informations situées aux intersections des colonnes 3 et 4 avec quelques lignes. Ces résultats sont extraits dans un petit tableau placé sur la droite de la feuille et ce, avec une unique formule matricielle, au demeurant fort simple.

Classeur Excel à télécharger
Pour la mise en place de la technique, nous suggérons d'appuyer l'étude sur un classeur offrant ces données à croiser.

• Télécharger le classeur extractions-intersections.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons effectivement un premier tableau entre les colonnes B et E et les lignes 5 et 16. Pour mieux interpréter le rendu de l'extraction que nous produirons, chacune de ses cellules est sanctionnée par son indice relatif de ligne et par son indice relatif de colonne. Le tableau d'extraction est quant à lui proposé entre les colonnes G et H.

• En haut à gauche de la feuille Excel, déployer la zone Nom,
• Puis, cliquer sur le nom Tab qui se suggère dans la liste,

Comme vous pouvez le voir, il désigne toutes les cellules du premier tableau. Nous exploiterons donc ce nom dans la formule d'extraction.

Initier l'extraction
L'extraction doit donc être produite à partir du tableau reconnu sous le nom Tab. La fonction Excel qui permet d'extraire des données à partir d'un tableau est la fonction Index.

• Sélectionner toutes les cellules d'extraction, soit la plage G5:H16,

En effet, dans cette technique matricielle, la formule ne pourra être répliquée que pas présélection et non en tirant la poignée.

• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner le tableau d'extraction par son nom, soit : Tab,
• Puis taper un point-virgule (;) pour passer dans l'indice de ligne de la fonction Index,

Matrice des lignes à recouper
Dans ce raisonnement matriciel, ce n'est pas l'indice d'une ligne que nous allons lui passer. Nous allons lui transmettre une matrice des numéros de lignes à recouper avec une matrice des numéros de colonnes que nous construirons dans un second temps.

• Construire la matrice suivante : {2;4;8;9;10},
• Puis, taper un point-virgule (;) pour passer dans l'indice de colonne de la fonction Index,

Dans une matrice représentant des lignes, les numéros doivent nécessairement être séparés par des points-virgules. Ici donc, nous demandons de recouper les informations situées sur les lignes 2, 4, 8, 9 et 10 avec les informations situées aux croisements des colonnes qui restent à définir.

Matrice des colonnes à recouper
Désormais donc et là encore, en guise d'indice de colonne, c'est une matrice des numéros de colonnes que nous allons passer en troisième paramètre de la fonction Index. Comme notre tableau d'extraction n'est composé que de deux colonnes, nous allons construire une matrice de deux rangées. Et cette fois, c'est le point qui doit séparer les numéros pour représenter une matrice en colonnes.

• Construire la matrice suivante : {3.4},
• Fermer la parenthèse de la fonction Index,
• Puis, valider la formule par le raccourci clavier CTRL + MAJ + Entrée,

Tous les résultats tombent. Il existe néanmoins une pléiade d'erreurs fort logiques sur lesquelles nous reviendrons. Lorsqu'il n'y a plus rien à extraire, une fonction de recherche répond par un échec. Le message #N/A signifie Not Available, soit non disponible. Néanmoins, vous remarquez que ce sont bien les informations situées aux intersections des lignes et des colonnes qui sont extraites dans l'ordre. En effet, dans un raisonnement matriciel, chaque ligne d'une matrice est confrontée à chaque ligne ou à chaque colonne d'une autre matrice.

La plage étant toujours sélectionnée, si vous modifiez l'une et l'autre matrice comme ceci par exemple : {2;4;6;9;10};{2.4} et que vous validez par CTRL + MAJ + Entrée, vous constatez avec plaisir que les extractions aux intersections s'ajustent parfaitement.



Corriger les erreurs d'extraction
Pour parfaire la solution, nous souhaitons que la formule stoppe son extraction lorsque la butée des matrices est atteinte. Dans ce contexte particulier, la fonction SiErreur est inopérante. Le même calcul doit tout d'abord être testé avec la fonction logique EstNa à imbriquer dans une fonction conditionnelle Si pour réaliser le test.

• Sélectionner de nouveau la plage d'extraction, soit : G5:H15,
• Dans la barre de formule, cliquer juste après le symbole égal,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Inscrire la fonction de test d'erreur suivie d'une parenthèse, soit : EstNa(,

Ainsi le calcul d'extraction à tester est déjà passé en paramètre de la fonction EstNa.

• Cliquer à la toute fin de la syntaxe pour y placer le point d'insertion,
• Fermer la parenthèse de la fonction EstNA,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Puis, inscrire deux guillemets, soit : "",

Ainsi, lorsque le test est vérifié, attestant que l'extraction produit une erreur, nous choisissons de conserver la cellule vide.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Puis, répliquer le calcul d'extraction, soit : INDEX(tab;{2;4;6;9;10};{2.4}),
• Fermer la parenthèse de la fonction Si,
• Puis, valider de nouveau la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez l'apprécier, les extractions aux intersections sont toujours produites quand dans le même temps, les erreurs ont disparu et tout cela avec une formule unique dont voici la syntaxe complète :

{=SI(ESTNA(INDEX(tab;{2; 4; 6; 9; 10}; {2.4})); ""; INDEX(tab; {2; 4; 6; 9; 10}; {2.4}))}