Ces fonctions cachées d’Excel ont révolutionné ma façon de travailler !

Dans ce tutoriel, je vais vous montrer comment utiliser les nouvelles fonctions de manipulation de tableaux qui sont désormais disponibles dans Excel 365.

Ces fonctions vont vous permettre de réorganiser vos données de manière flexible et efficace, sans avoir besoin de recourir à des manipulations manuelles fastidieuses.

Nous allons découvrir ensemble ces fonctions différentes qui, une fois combinées, vous permettront de transformer n'importe quel tableau désorganisé en un format exploitable et professionnel.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous gérons l'inventaire d'une librairie.

Ce tableau contient plusieurs rayons de la librairie, et pour chaque rayon, nous avons une liste des livres disponibles répartis sur plusieurs colonnes.

Ce format de données, avec les informations réparties horizontalement, est assez courant dans les fichiers qu'on reçoit.

Cependant, il n'est pas idéal pour l'analyse ou pour être exploité dans une base de données.

Nous allons donc apprendre à le transformer de différentes manières selon nos besoins.

Avant de commencer, il est important de comprendre que ces nouvelles fonctions génèrent ce qu'on appelle des « tableaux dynamiques ».

Cela signifie que le résultat de la formule va automatiquement s'étendre sur plusieurs cellules, un comportement qu'Excel appelle « déversement ».

Nous devons donc toujours nous assurer que les cellules adjacentes à notre formule sont vides, sinon Excel affichera une erreur #DÉVERS!.

2. Transformer des données en une seule colonne ou ligne

Commençons par découvrir la fonction DANSCOL(), qui est probablement l'une des plus utiles de cette nouvelle série.

Cette fonction nous permet de prendre n'importe quelle plage de données, qu'elle soit sur une ligne, plusieurs lignes ou plusieurs colonnes, et de la transformer en une seule colonne verticale.

Nous nous rendons dans une cellule vide, par exemple G7, où nous allons saisir notre première formule.

La fonction DANSCOL() est simple à utiliser, étant donné qu’elle n’attend qu’un seul argument obligatoire correspondant à notre plage de données que nous souhaitons passer en colonne.

Commençons simplement en sélectionnant uniquement les livres :

 =DANSCOL(B7:E11) 

an>

Nous saisissons cette formule et nous appuyons sur [Entrée].

Excel va alors déverser le résultat sur plusieurs cellules en dessous de notre formule.

Nous observons immédiatement que tous nos livres sont désormais listés verticalement, ligne par ligne.

Cependant, nous remarquons un problème : là où nous avions des cellules vides dans notre tableau d'origine (comme pour le rayon Science qui n'a que trois livres), Excel affiche maintenant le chiffre « 0 », ce qui n'est pas idéal car cela pollue notre liste de livres.

Pour résoudre ce problème, nous allons utiliser le deuxième argument de la fonction DANSCOL(), auquel nous donnons la valeur de « 1 » :

 =DANSCOL(B7:E11;1) 

an>

Ce deuxième argument permet de spécifier ce que nous voulons ignorer :

• La valeur 1 signifie que nous voulons ignorer les cellules vides.
• Si nous mettons 2, Excel ignorera les erreurs.
• Si nous mettons 3, Excel ignorera à la fois les cellules vides et les erreurs.

Dans la plupart des cas, la valeur 1 est suffisante.

Nous appuyons sur [Entrée] et maintenant notre liste est propre, sans les zéros parasites :

Maintenant, parlons du troisième argument de DANSCOL().

Par défaut, Excel lit nos données ligne par ligne, de gauche à droite, puis passe à la ligne suivante.

Mais nous pouvons changer ce comportement en ajoutant un troisième argument.

 =DANSCOL(B7:E11;1;VRAI) 

Avec cette modification, Excel va maintenant lire nos données colonne par colonne plutôt que ligne par ligne.

Cette option peut être utile dans certains cas spécifiques où la logique de vos données est organisée verticalement plutôt qu'horizontalement.

Cependant, dans la plupart des situations, nous laisserons ce troisième argument à sa valeur par défaut (FAUX), ou nous ne le spécifierons tout simplement pas.

La fonction DANSLIGNE() est le pendant horizontal de DANSCOL().

Elle prend une plage de données et la transforme en une seule ligne horizontale.

Cette fonction est particulièrement utile quand nous avons des données verticales que nous voulons afficher horizontalement pour des raisons de présentation ou de mise en page.

La syntaxe est identique à DANSCOL(), mais avec un résultat qui s'étend horizontalement.

 =DANSLIGNE(B7:E11;1;VRAI) 

Une utilisation courante de DANSLIGNE() est lorsque nous voulons créer des listes déroulantes dynamiques dans Excel.

En effet, la fonctionnalité de validation des données d'Excel fonctionne mieux avec des plages horizontales pour certains types de listes.

3. Réorganiser des données en grille

Passons maintenant à une fonction plus avancée : ORGA.COLS().

Cette fonction prend une liste verticale et la « découpe » en plusieurs colonnes en fonction d'un nombre de lignes que nous spécifions.

C'est comme si nous prenions une longue liste et que nous la plions en accordéon.

Pour bien comprendre ORGA.COLS(), créons d'abord une liste verticale de tous nos livres en remettant la fonction DANSCOL() dans la cellule G7

Maintenant, dans la cellule I7, nous allons utiliser ORGA.COLS() pour réorganiser cette liste.

 =ORGA.COLS(G7#;10) 

Ici, « G7# » correspond à la plage dynamique retournée par la formule de la cellule G7.

Le symbole « # » est ce qu'on appelle l'« opérateur de plage déversée ».

Il s'agit d'une notation spéciale introduite avec les tableaux dynamiques d'Excel 365.

Cela signifie que notre formule ne fait pas simplement référence à la cellule G7, mais à l'ensemble des cellules dans lesquelles la formule de G7 a déversé son résultat.

C'est beaucoup plus pratique que d'avoir à écrire manuellement « G7:G56 », surtout si le nombre de lignes change régulièrement.

En effet, l’avantage majeur de cette notation est qu'elle est complètement dynamique. Si demain notre formule en G7 retourne 60 livres au lieu de 50, la référence « G7# » s'ajustera automatiquement pour inclure les 60 lignes, sans que nous ayons à modifier quoi que ce soit.

C'est ce qui rend ces nouvelles fonctions si puissantes pour créer des tableaux de bord et des rapports qui se mettent à jour automatiquement.

Nous avons deux options pour saisir cette référence lorsque nous construisons notre formule.

• La première méthode consiste à taper manuellement la référence : nous cliquons sur la cellule G7 dans notre formule, puis nous insérons le symbole « # ».
• La deuxième méthode consiste à sélectionner toute la plage générée par la formule dynamique pour qu’Excel convertisse la plage avec la référence « G7# »

Le deuxième argument utilisé dans la fonction est le nombre de lignes que nous voulons avant que la liste ne « retourne » vers la colonne suivante.

Dans notre cas, nous avons choisi 10, ce qui signifie qu'après 5 livres, Excel commencera une nouvelle colonne.

Nous appuyons sur [Entrée] et nous voyons notre liste se réorganiser en plusieurs colonnes de 10 lignes chacune.

C'est très pratique pour créer des présentations compactes de listes longues, notamment pour l'impression où nous voulons optimiser l'espace sur la page.

Cependant, nous remarquons un problème : si notre liste ne contient pas un multiple exact de 10 éléments, les dernières cellules affichent #N/A, qui est une erreur indiquant qu'il n'y a pas de valeur disponible, ce qui n’est pas très esthétique.

Heureusement, pour résoudre cela, nous pouvons utiliser le troisième argument de ORGA.COLS(), qui nous permet de spécifier une valeur de remplissage pour les cellules vides.

 =ORGA.COLS(G7#;10;"") 

Ainsi, en mettant deux guillemets vides, nous indiquons à Excel de laisser ces cellules vides plutôt que d'afficher une erreur.

ORGA.LIGNES() fonctionne exactement comme ORGA.COLS(), mais dans le sens horizontal.

Au lieu de découper une liste verticale en colonnes, elle découpe une liste horizontale en lignes. C'est particulièrement utile quand nous avons des données qui s'étendent sur une très longue ligne et que nous voulons les présenter sur plusieurs lignes pour une meilleure lisibilité.

 =ORGA.LIGNES(G7#;10;"") 

4. Extraire des portions précises avec PRENDRE() et EXCLURE()

Les fonctions PRENDRE() et EXCLURE() sont deux fonctions complémentaires qui nous permettent d'extraire ou d'exclure des portions spécifiques de nos tableaux.

Ces fonctions sont particulièrement puissantes car elles peuvent travailler depuis le début ou la fin de nos données.

Commençons avec PRENDRE().

Cette fonction nous permet d'extraire un nombre précis de lignes et/ou de colonnes depuis le début ou la fin d'un tableau.

Positionnons-nous dans la cellule I10 et saisissons la formule :

 =PRENDRE(A6:E11;2) 

Cette formule va extraire uniquement les deux premières lignes de notre tableau, c'est-à-dire la ligne d'en-têtes et la première liste de livres.

Maintenant, si nous voulons extraire les trois premières colonnes de tout notre tableau, nous modifions la formule :

 =PRENDRE(A6:E11;;3) 

Nous laissons le deuxième argument vide (les deux points-virgules), ce qui signifie que nous prenons toutes les lignes, et nous avons mis 3 comme troisième argument pour prendre seulement les trois premières colonnes.

La vraie puissance de PRENDRE() vient de sa capacité à travailler depuis la fin du tableau en utilisant des nombres négatifs. Si nous tapons «=PRENDRE(A6:E11;-1) », nous obtenons uniquement la dernière ligne de notre tableau, c'est-à-dire le rayon Histoire avec tous ses livres.

Passons maintenant à EXCLURE(), qui fait exactement l'inverse.

Au lieu d'extraire des lignes ou colonnes, elle les retire.

Ainsi « =EXCLURE(A6:E11;2) » va supprimer les deux premières lignes et nous retourner tout le reste, c'est-à-dire notre tableau sans les en-têtes et la première série de livres :

Si nous voulons supprimer les deux dernières colonnes, nous utilisons « =EXCLURE(A6:E11;;-2) ». Cela nous laisse avec les colonnes Rayon, Livre 1 et Livre 2 uniquement.

5. Sélectionner des lignes et colonnes précises avec CHOISIRLIGNE() et CHOISIRCOL()

Les fonctions CHOISIRLIGNE() et CHOISIRCOL() nous donnent un contrôle très précis sur quelles lignes et colonnes nous voulons extraire de notre tableau.

Contrairement à PRENDRE() qui prend un nombre consécutif de lignes ou colonnes, ces fonctions nous permettent de choisir exactement lesquelles nous voulons, dans l'ordre que nous voulons, et même de les répéter.

Commençons par utiliser la formule :

 =CHOISIRLIGNES(A6:E11;2) 

Cette formule va extraire uniquement la deuxième ligne de notre tableau, c'est-à-dire le rayon Fiction avec tous ses livres.

Maintenant, faisons quelque chose de plus intéressant. Nous voulons afficher nos rayons dans un ordre différent.

Nous modifions notre formule : « =CHOISIRLIGNE(A6:E11;1;4;2;5;3) ».

Cette formule va nous donner d'abord la ligne d'en-têtes (ligne 1), puis le rayon BD (ligne 4), puis Fiction (ligne 2), puis Histoire (ligne 5), et enfin Science (ligne 3).

Nous avons complètement réorganisé notre tableau selon nos besoins.

Une fonctionnalité puissante de CHOISIRLIGNE() est la possibilité de répéter des lignes.

Par exemple, « =CHOISIRLIGNE(A6:E11;1;1;1;1) » va répéter ligne d’entête quatre fois.

Cela peut sembler bizarre à première vue, mais nous pouvons imaginer certains cas où cela pourra être utile.

Nous pouvons également utiliser des nombres négatifs pour compter depuis la fin.

 =CHOISIRLIGNE(A6:E11;1;-1) 

Cette formule nous donne d'abord la ligne d’entête ; la dernière ligne du tableau.

La fonction CHOISIRCOL() fonctionne exactement de la même manière mais pour les colonnes.