Comment récupérer la première cellule non nulle d'une ligne ou d'une colonne sur Excel
Dans ce tutoriel, je vais vous montrer comment récupérer la première cellule non vide contenue dans une ligne ou dans une colonne en utilisant une formule INDEX(EQUIV(INDEX())) !
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Présentation du cas
Pour illustrer ce tutoriel, nous allons utiliser la base de données que nous retrouvons ici à l’écran, et qui reprend le listing du personnel d’une société avec le salaire mensuel versée pour chacun de ces salariés au cours de l’année 2018 :
Une cellule vide signifie qu’aucun salaire n’a été versé durant le mois, et donc que la personne ne faisait pas encore partie du personnel de l’entreprise.
L’objectif va donc être de déterminer le premier mois au cours duquel un salaire a effectivement été versé, ce mois correspondant de fait au mois d’embauche de la personne dans l’entreprise.
Pour cela, nous allons utiliser une formule assez complexe : INDEX(EQUIV(INDEX()))
2. Trouver la position du premier salaire : EQUIV(INDEX())
Et oui, vous avez lu il ne s’agit pas d’une erreur nous allons bien ici utiliser la formule INDEX() imbriquée dans la formule EQUIV(), et non l’inverse !
Voici la formule que nous allons utiliser pour retrouver la position du premier salaire versée pour la première ligne (Aceline Bonami), nous en étudierons la composition juste après
=EQUIV(VRAI;INDEX(C6:N6>0;0);0)
/p>
Pour synthétiser ces deux formules, nous cherchons tout d’abord (grâce à la formule INDEX()) à construire une matrice d’une taille équivalente au nombre de cellules contenues dans la plage d’analyse (plage C6 à N6 dans laquelle nous retrouvons les salaires versés à Aceline Bonami).
Cette matrice est constituée uniquement de VRAI et de FAUX.
Pour bien comprendre la composition de cette matrice, nous allons saisir cette formule INDEX() dans une autre cellule (P6) :
Le résultat retourné par cette formule est FAUX, ce qui signifie que la première cellule (C6) n’a pas pour valeur un nombre supérieur à zéro.
Vous devez sans doute vous demander quel est l’intérêt de cette information ?
En réalité, le résultat de la formule est bien plus complet, et il suffit de se rendre dans l’outil d’évaluation de formule pour s’en rendre compte (sélectionner la cellule P6, puis dans le menu Formules du ruban, cliquer sur Évaluer la formule) :
Dans la fenêtre qui s’affiche, cliquer sur le bouton Évaluer afin que la formule se calcul dans la zone d’évaluation :
À ce moment, là nous pouvons alors nous rendre compte qu’Excel génère la matrice : {FAUX,FAUX,FAUX,VRAI,VRAI,VRAI,VRAI,VRAI,VRAI,VRAI,VRAI,VRAI}.
Évidemment, il n’est pas possible d’afficher cette matrice dans la cellule, Excel insert alors le premier élément de celle-ci : FAUX.
En revanche, il est tout à fait possible d’exploiter cette matrice pour la traiter en tant que paramètre d’une fonction qui nécessiterai une matrice.
Et c’est justement ce que demande la formule EQUIV(), qui nous permet alors de récupérer la position du premier VRAI (attention au passage de ne pas omettre le dernier paramètre zéro :
Le premier salaire est donc versé au cours du quatrième mois !
3. Puis trouver le mois correspondant avec la formule EQUIV(INDEX(EQUIV()))
Enfin, il ne reste plus qu’à imbriquer cette formule en tant que paramètre de la formule EQUIV() pour utiliser la formule INDEX(EQUIV()) de manière classique pour récupérer le mois correspondant (cliquez ici pour en savoir plus sur la formule INDEX(EQUIV())) :
Attention de bien passer la référence des cellules de l’en-tête en référence absolue (en appuyant sur la touche [F4] du clavier), afin d’ajouter les symboles dollars et de fixer l’appel à cette référence lorsque nous allons étendre la formule aux cellules du dessous.
Enfin, nous pouvons valider la formule en appuyant sur la touche [Entrée] du clavier :
Il ne reste plus qu’à changer le format de la cellule afin d’afficher la date de manière claire dans la cellule (choisir Date courte dans le menu de formatage rapide du menu Accueil du ruban, ou grâce au raccourci clavier [Ctrl]+[j])
Et pour finir, nous pouvons étendre la formule en double-cliquant sur le petit carré noir situé en bas à droite de la cellule, lorsque le curseur prend la forme d’un symbole plus :
