Les 100 formules Excel : les fonctions à utiliser / série 4

Dans ce tutoriel Excel, nous exposons les meilleures formules Excel pour les pros et les étudiants.

Premier jour du mois pour une date donnée

Supposons qu’on vous ait donné une date, disons le 22/10/14 (MM/JJ/AA) et que vous vouliez calculer
le premier jour du mois en cours. Par conséquent, vous souhaitez obtenir un résultat du 01/10/2014
(MM/JJ/AA).
Les formules à utiliser –
=DATE(ANNEE(A1),MOIS(A1),1)
=A1-JOUR(A1)+1
=EOMOIS(A1,-1)+1

Combien de lundis ou tout autre jour de la semaine entre 2 dates

Supposons que A1 = 23-Jan-16 et A2 = 10-Nov-16. Pour trouver le nombre de lundis entre ces
deux rendez-vous
Bible des formules Excel
=SOMMEPROD(–(TEXTE(LIGNE(INDIRECT(A1&”:”&A2)),”ddd”)=”Lun”))
“Lun” peut être remplacé par n’importe quel autre jour de la semaine selon les besoins

Nombre maximal d’apparitions d’une entrée particulière

Consécutivement
Supposons que nous voulions compter le nombre maximum de fois que “A” apparaît consécutivement, vous pouvez utiliser
formule matricielle suivante –
=MAX(FRÉQUENCE(SI(A2:A20=”A”,LIGNE(A2:A20)),SI(A2:A20<>”A”,LIGNE(A2:A20))))
Remarque – La formule matricielle n’est pas saisie en appuyant sur ENTREE après avoir saisi votre formule, mais en appuyant sur CTRL+MAJ+ENTRÉE. Si vous copiez et collez cette formule, prenez F2 après
coller et CTRL+MAJ+ENTRÉE. Cela mettra { } crochets autour de la formule que vous pouvez voir dans la barre de formule. Si vous modifiez à nouveau, vous devrez à nouveau faire CTRL + MAJ + ENTRÉE.
Ne mettez pas { } manuellement.

Trouver la prochaine semaine du jour

Il y a 2 scénarios dans ce cas. Par exemple, si la date d’aujourd’hui est le 2-Jan-17 (lundi)
Essayez de trouver le lundi suivant, je peux obtenir le 2-Jan-17 ou le 9-Jan-17 selon les besoins. Pour mardi
au dimanche, ce n’est pas un problème car ils ne viennent qu’après le 2 janvier 2017.
Cas 1 – Si le Jour tombe à la même date, alors cette même date (Par conséquent, dans le cas du 2-Jan 17, lundi prochain serait le 2-Jan-17 uniquement)

Cas 2 – Si le Jour tombe à la même date, alors la prochaine date (Par conséquent, dans le cas du 2-Jan-17, la prochaine
Le lundi serait le 9 janvier 2017 uniquement)

Trouver la semaine précédente du jour

Il y a 2 scénarios dans ce cas. Par exemple, si la date d’aujourd’hui est le 2-Jan-17 (lundi)

Essayez de trouver le lundi précédent, je peux obtenir le 2 janvier 2017 ou le 26 décembre 2016 selon les besoins.
Du mardi au dimanche, ce n’est pas un problème car ils arrivent avant le 2 janvier 2017 uniquement.
Cas 1 – Si le jour tombe à la même date, alors cette même date (par conséquent, dans le cas du 2 janvier 17, le lundi précédent serait uniquement le 2 janvier 17)

Cas 2 – Si le jour tombe à la même date, alors la date précédente (par conséquent, dans le cas du 2-janv. 17, le lundi précédent serait le 26-déc-16 uniquement)

Obtenir le nom du fichier via la formule
Avant de l’obtenir, assurez-vous que votre fichier a été enregistré au moins une fois car cette formule est dépend du nom du chemin du fichier qui peut être extrait par la fonction CELLULE uniquement si le fichier a été sauvegardé au moins une fois.

=CELLULE(“nomfichier”,$A$1)

Obtenir le nom du classeur via la formule
Avant de l’obtenir, assurez-vous que votre fichier a été enregistré au moins une fois car cette formule est dépend du nom du chemin du fichier qui peut être extrait par la fonction CELLULE uniquement si le fichier a été sauvegardé au moins une fois.
=REPLACE(LEFT(CELL(“filename”,$A$1),FIND(“]”,CELL(“filename”,$A$1))-
1),1,CHERCHE(“[“,CELLULE(“nomfichier”,$A$1)),””)

Obtenir le nom de la feuille via la formule

Avant de l’obtenir, assurez-vous que votre fichier a été enregistré au moins une fois car cette formule est
dépend du nom du chemin du fichier qui peut être extrait par la fonction CELLULE uniquement si le fichier a
été sauvegardé au moins une fois.
Utilisez la formule suivante –
=REPLACE(CELL(“filename”,A1),1,FIND(“]”,CELL(“filename”,A1)),””)
Assurez-vous que A1 est utilisé dans la formule. S’il n’est pas utilisé, il extraira le nom de la feuille pour le
dernière feuille active qui n’est peut-être pas celle que nous voulons.
Si vous voulez le nom de la feuille pour la dernière feuille active uniquement, la formule deviendrait
=REPLACE(CELL(“filename”),1,FIND(“]”,CELL(“filename”)),””)

Obtenir le répertoire du classeur à partir de la formule

Avant de l’obtenir, assurez-vous que votre fichier a été enregistré au moins une fois car cette formule est dépend du nom du chemin du fichier qui peut être extrait par la fonction CELLULE uniquement si le fichier a été sauvegardé au moins une fois.
Si votre classeur se trouve par exemple dans C:\Excel\MyDocs, la formule pour récupérer le répertoire
car ce serait
=GAUCHE(CELLULE(“nomfichier”,A1),CHERCHE(“[“,CELLULE(“nomfichier”,A1))-2

Dernier jour du mois pour une date donnée

Supposons qu’on vous donne une date, disons le 22/10/14 (MM/JJ/AA) et que nous voulons avoir le dernier
date du mois pour la date donnée. Par conséquent, vous avez besoin d’une réponse du 31/10/14. Les formules
à utiliser dans ce cas –
=EOMOIS(A1,0)
=DATE(ANNEE(A1),MOIS(A1)+1,0

=DATE(ANNEE(A1),MOIS(A1)+1,1)-1

Effectuer une RECHERCHEV ( VLOOKUP ) multi-colonnes

Vous connaissez VLOOKUP, l’une des fonctions les plus appréciées d’Excel. La syntaxe est
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Ici, look_value peut être une valeur unique et non plusieurs valeurs.
Maintenant, vous rencontrez une situation où vous voulez faire vlookup avec plus de 1 valeurs.
Dans le but d’illustrer le concept, disons que nous avons 2 valeurs à rechercher.
Vous trouverez ci-dessous votre table de recherche et vous souhaitez rechercher Emp – H et Gender – F pour Age.

=INDEX(C2:C12,EQUIV(1,INDEX(–((A2:A12=F2)(B2:B12=G2)(LIGNE(A2:A12)-
LIGNE(A2)+1)<>0),,),0))
Approche de concaténation
=INDEX(C2:C10,EQUIV(F2&”@@@”&G2,INDICE(A2:A10&”@@@”&B2:B10,,),0))
@@@ peut être remplacé par n’importe quel caractère qui ne devrait pas faire partie de ces colonnes.
Par concaténation, vous pouvez avoir autant de colonnes que possible.
ATTENTION – Le résultat de la concaténation entière ne doit pas avoir une longueur supérieure à 255.
Par conséquent, F2&”@@@”&G2 ne doit pas comporter plus de 255 caractères.
Une autre alternative consiste à utiliser la formule matricielle ci-dessous –
=INDEX(C2:C12,EQUIV(1,–PAS(ISLOGIQUE(SI(A2:A12=F2,SI(B2:B12=G2,C2:C12)))),0))
Remarque – La formule matricielle n’est pas saisie en appuyant sur ENTREE après avoir saisi votre formule, mais en
en appuyant sur CTRL+MAJ+ENTRÉE. Si vous copiez et collez cette formule, prenez F2 après coller et CTRL+MAJ+ENTRÉE. Cela mettra { } crochets autour de la formule que vous pouvez voir dans la barre de formule. Si vous modifiez à nouveau, vous devrez à nouveau faire CTRL + MAJ + ENTRÉE.
Ne mettez pas { } manuellement.