Formule générique
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Sommaire
Pour trouver la position (ligne) de la dernière révision de fichier dans un tableau, vous pouvez utiliser une formule basée sur plusieurs fonctions Excel: MAX, IF, ISERROR, ROW et INDEX.
Dans l'exemple illustré, la formule de la cellule H6 est:
(= MAX (IF (ISERROR (SEARCH (H5 & "*", fichiers)), 0, ROW (fichiers) -ROW (INDEX (fichiers, 1,1)) + 1)))
où "fichiers" est la plage nommée C4: C11.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Le contexte
Dans cet exemple, nous avons un certain nombre de versions de fichiers répertoriées dans un tableau avec une date et un nom d'utilisateur. Notez que les noms de fichiers sont répétés, à l'exception du code ajouté à la fin pour représenter la version ("CA", "CB", "CC", "CD", etc.).
Pour un fichier donné, nous voulons localiser la position (numéro de ligne) de la dernière révision. C'est un problème délicat, car les codes de version à la fin des noms de fichiers rendent plus difficile la correspondance avec le nom de fichier. De plus, par défaut, les formules de correspondance Excel renverront la première correspondance, pas la dernière, nous devons donc contourner ce défi avec des techniques délicates.
Explication
Au cœur de cette formule, nous construisons une liste de numéros de ligne pour un fichier donné. Ensuite, nous utilisons la fonction MAX pour obtenir le plus grand numéro de ligne, qui correspond à la dernière révision (dernière occurrence) de ce fichier.
Pour trouver toutes les occurrences d'un fichier donné, nous utilisons la fonction SEARCH, configurée avec le caractère générique astérisque (*) pour correspondre au nom du fichier, en ignorant les codes de version. SEARCH lancera une erreur VALUE lorsque le texte n'est pas trouvé, nous enveloppons donc la recherche dans ISERROR:
ISERROR(SEARCH(H5&"*",files))
Cela donne un tableau de valeurs TRUE et FALSE comme ceci:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
C'est déroutant, mais TRUE représente une erreur (texte non trouvé) et FALSE représente une correspondance. Ce résultat de tableau est introduit dans la fonction IF comme test logique. Pour value si TRUE, nous utilisons zéro, et pour value si true, nous fournissons ce code, qui génère des numéros de ligne relatifs pour la plage avec laquelle nous travaillons:
ROW(files)-ROW(INDEX(files,1,1))+1)
La fonction IF renvoie ensuite un tableau de valeurs comme celui-ci:
(1; 0; 3; 4; 0; 0; 7; 0)
Tous les nombres sauf zéro représentent des correspondances pour "filename1" - c'est-à-dire le numéro de ligne à l'intérieur de la plage nommée "files" où "filename1" apparaît.
Enfin, nous utilisons la fonction MAX pour obtenir la valeur maximale de ce tableau, qui est 7 dans cet exemple.
Utilisez INDEX avec ce numéro de ligne pour récupérer les informations relatives à la dernière révision (c'est-à-dire le nom complet du fichier, la date, l'utilisateur, etc.).
Sans plage nommée
Les plages nommées permettent de configurer rapidement et facilement une formule plus complexe, car vous n'avez pas à saisir les adresses des cellules à la main. Cependant, dans ce cas, nous utilisons une fonction supplémentaire (INDEX) pour obtenir la première cellule de la plage nommée "fichiers", ce qui complique un peu les choses. Sans la plage nommée, la formule ressemble à ceci:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
