Traduit de Anglais vers Français - [Link].

com

Bible des formules EXCEL

Excel 365/Excel 2021
Version 2 - Mise à jour (22 août) pour inclure les tableaux dynamiques et
bien d'autres nouvelles formules
 DOCUMENT LIBRE DE DROIT D'AUTEUR

Ce travail est complètementlibre de droit. N'hésitez pas à le distribuer à vos amis et collègues et au sein de votre
organisation. Il n'est pas nécessaire de demander une autorisation pour la distribution et l'utilisation. De plus,
vous pouvez considérer ce travail comme le vôtre et m'accuser de vous avoir volé.

En cas de doute, vous pouvez contacter l'auteur Vijay A. Verma à e@[Link] .

Il n'est absolument pas nécessaire de demander l'autorisation pour utiliser cette œuvre à des fins commerciales.
et non commerciale.

- - - DIFFUSER L'APPRENTISSAGE ---

Table des matières
1. SOMME des chiffres lorsque la cellule contient tous les nombres .......................................... ...................................... 2

2. SOMME des chiffres lorsque la cellule contient à la fois des nombres et des non-nombres ............................... ..2

3. Une liste est unique ou non (qu'elle comporte des doublons) ............................... ...................................... 2

4. Compter le nombre de valeurs uniques ............................................ .................................................................. ...................... 2

5. Compter le nombre de valeurs uniques de manière conditionnelle .......................................... ...................................................... 2

6. Générez des noms de jours de la semaine séquentiels comme Sun, Mon, Tue, ....., Sat ............................... ................ 3

7. Générez des noms de mois séquentiels comme janvier, février, mars... décembre................................. ...................... 4

8. Rechercher le dernier jour du mois.................................................. .................................................................. ...................... 4

9. Nombre de jours dans un mois .................................................. .................................................................. ...................... 4

10. Trouver le premier jour du mois.................................................. .................................................................. ...................... 4

11. Ajouter un mois ou soustraire un mois à une date donnée.............................................. ........................ 5

12. Ajouter ou soustraire une année à une date donnée................................................. ...................................... 5

13. Convertir un nombre en nom de mois ............................................ .................................................................. ..... 5

14. Convertir un nom de mois en nombre ............................................ .................................................................. ....... 6

15. Convertir un numéro en nom de jour de la semaine.................................. .................................................................. .. 6

16. Convertir un nom de jour de la semaine en numéro.................................................. .................................................................. .. 6

17. Formule de l'exercice financier (par exemple 2015-16 ou FY16) .................................... ...................................... 7

18. Conversion d'une date en trimestre civil ............................................ ................................................. 7

19. Conversion de la date en trimestre de l'exercice financier indien............................................ ........................... 7

20. Déterminer le trimestre de l'exercice financier .............................................. .................................................................. ....... 8

21. Calculer l'âge à partir de l'anniversaire donné .............................................. .................................................................. ...... 8

22. Convertir de jj/mm/aa en mm/jj/aa (DMY en MDY) ................................. ...................... 8

23. Convertir de mm/jj/aa en jj/mm/aa (MDY en DMY) ................................. ...................... 9
24. Conversion du format nombre en date.................................................. .................................................................. .... 9

25. Conversion du format de nombre en format d'heure........................................... .................................................................. dix

26. Convertir le temps en heures, minutes et secondes décimales ....................................... .................. dix

27. Convertir les heures, minutes et secondes décimales en heure ....................................... .................. dix

28. Générer une séquence de dates .............................................. .................................................................. .............. 11

29. Générer une séquence de temps .......................................... .................................................................. ............ 11

30. Comment savoir si une année est bissextile ...................................... .................................................................. .... 11

31. Dernier jour ouvrable du mois si une date est indiquée ............................................... .................................. 12

32. Premier jour ouvrable du mois si une date est donnée ............................................... ................................ 12

33. Déterminer le nombre de jours ouvrables dans une année ............................................ ...................................... 13

34. Déterminer le nombre de jours ouvrables dans un mois ............................................ ...................... 13

35. Combien de lundis ou tout autre jour de la semaine entre 2 dates ............................... 14
36. Trouver le numéro du vendredi 13 entre deux dates données ....................................... .......... 14
37. Calculer le jour ouvrable suivant si la date tombe un week-end/un jour férié............................... .14

38. Calculer le jour ouvrable précédent si la date tombe un week-end/un jour férié ............................ 15

39. Date du Nième jour de l'année ............................................ .................................................................. .................. 16

40. Extraire la date et l'heure de l'horodatage ............................................ .................................. 16

41. Premier jour ouvrable de l'année ............................................ .................................................................. .............. 16

42. Dernier jour ouvrable de l'année ............................................ .................................................................. ............ 17

43. Convertir de la date Excel (date grégorienne) en date julienne .................................. ................ 17

44. Convertir des dates juliennes en dates Excel (grégoriennes) ....................................... ...................... 18

45. Convertir un nombre en années et en mois ............................................ ...................................... 18

46. Trouver la semaine suivante du jour.................................................. .................................................................. .............. 18

47. Rechercher la semaine précédente du jour ............................................ .................................................................. ..... 19

48. Compter les cellules commençant (ou se terminant) par une chaîne particulière ............................... ................ 20

49. Compter le nombre de cellules comportant uniquement des chiffres............................................ ...................................................... 20

50. Inverser une chaîne .................................................. .................................................................. ...................................... 20

51. Chaîne de nombres inversés.................................................. .................................................................. ...................... 20

52. Inverser une chaîne de mots.................................................. .................................................................. ...................... 21

53. Inverser une plage de cellules .............................................. .................................................................. ...................... 21

54. Obtenir le numéro de la première ligne d'une plage.................................. .................................................................. ........ 21

55. Obtenir le numéro de la dernière ligne d'une plage.................................. .................................................................. ......... 22

56. Obtenir le numéro de la première colonne d'une plage ............................................ .................................................................. .22

57. Obtenir le numéro de la dernière colonne d'une plage ............................................ .................................................................. .. 22

58. Extraire des caractères uniques.................................................. .................................................................. .................. 22

59. Tester si une plage contient uniquement des chiffres.................................. ...................................... 22

60. Tester si une plage contient uniquement des alphabets anglais ............................................ .................. 22

61. Compter le nombre de cellules contenant uniquement des caractères ....................................... ................ 23

62. Nombre de caractères dans une chaîne sans tenir compte des espaces ....................................... .... 23

63. Nombre de fois qu'un caractère apparaît dans une chaîne.................................................. .................................. 23

64. Compter les non-nombres dans une chaîne ............................................ .................................................................. ........... 23

65. Compter les nombres dans une chaîne.................................................. .................................................................. ...................... 24

66. Compter uniquement les alphabets dans une chaîne ............................................ .................................................................. ......... 24

67. Générer des alphabets anglais ............................................ .................................................................. ............ 24

68. Convertir des alphabets anglais en chiffres ............................................ ............................................ 25

69. Convertir des nombres en alphabets anglais .............................................. ............................................ 25

70. Extraire le nième mot du recto.................................................. .................................................................. ............... 25

71. Extraire le nième mot de l'arrière .............................................. .................................................................. ................ 25

72. Extraire les deux premiers mots OU les n premiers mots ...................................... ...................................................... 26

73. Extraire les deux derniers mots OU les n derniers mots ....................................... .................................................. 26
74. Valeur la plus fréquente dans une plage............................................ ...................................... 27
75. COUNTIF avec condition OR............................................ .................................................................. ............ 27

76. SUMIF avec condition OU ............................................ .................................................................. ...................... 28

77. COUNTIF sur la liste filtrée ............................................ .................................................................. ...................... 28

78. SUMIF sur la liste filtrée.................................................. .................................................................. ...................... 28

79. Extraire le prénom du nom complet ............................................ .................................................................. ... 28

80. Extraire le nom de famille du nom complet ............................................ .................................................................. .... 28

81. Extraire l'initiale du deuxième prénom.................................................. .................................................................. ...... 28

82. Extraire le deuxième prénom du nom complet.................................................. ...................................................... 29

83. Supprimer le deuxième prénom du nom complet ............................................ .................................................................. ... 29

84. Extraire la partie entière et décimale d'un nombre ............................................ ...................... 29

85. Durée maximale d'apparition consécutive d'une entrée particulière................................................. ....... 30

86. Obtenir le nom du fichier via une formule .............................................. .................................................................. ......... 30

87. Obtenir le nom du classeur via la formule .............................................. ...................................................... 30

88. Obtenir le nom de la feuille via la formule .............................................. .................................................................. ..... 30

89. Récupérer le répertoire du classeur à partir de la formule .............................................. ....................................... 31

90. Effectuer une RECHERCHEV multi-colonnes ............................................ .................................................................. ..... 31

91. RECHERCHEV de droite à gauche.................................................. .................................................................. ............ 32

92. RECHERCHEV sensible à la casse.................................................. .................................................................. ...................... 33

93. Rang au sein des groupes .................................................. .................................................................. ...................... 33

94. Extraire les alphabets d'une chaîne .............................................. .................................................................. ......... 34

95. Supprimer les alphabets d'une chaîne.................................................. .................................................................. ........ 34

96. Extraire les nombres d'une chaîne ............................................ .................................................................. .............. 34

97. Supprimer les nombres de la chaîne............................................ .................................................................. ...... 34

98. Représentation romaine des nombres.................................................. .................................................................. .35

99. Somme des N valeurs inférieures dans une plage ............................................ .................................................................. ......... 35

100. Additionner chaque Nième ligne ............................................ .................................................................. ...................... 36

101. Additionner chaque ligne paire.................................................. .................................................................. ...................... 36

102. Additionner chaque ligne impaire ............................................ .................................................................. ...................... 36

103. Somme des N premières valeurs dans une plage ............................................ .................................................................. ............ 37

104. Nous avons MOYENNEIF. Qu’en est-il de MEDIANIF et MODEIF ? ....................................... 37

105. Calculer la moyenne géométrique en ignorant les valeurs 0 et négatives............................................... 38

106. Générer des codes GL.................................................. .................................................................. ...................... 39

107. Abréger les prénoms.................................................. .................................................................. ............... 39

108. Obtenir le nom de colonne pour un numéro de colonne ............................................ ...................................... 40

109. Obtenir une plage de colonnes pour un numéro de colonne........................................... ...................................... 40

110. Trouver le nième plus grand nombre lorsqu'il y a des doublons ....................................... .......... 41
111. COUNTIF pour une plage non contiguë .......................................... ...................................................... 41
112. Compter le nombre de mots dans une cellule/plage ....................................... ...................................... 42

113. Numérologie Somme des chiffres, également appelée Somme des chiffres jusqu'à ce que le résultat soit un seul chiffre.... 42

114. Générer des nombres séquentiels et les répéter............................................ ...................... 43

115. Répéter un nombre, incrémenter et répéter.... ....................................... .................................. 43
116. Générer des nombres aléatoires non répétitifs via une formule............................................ 44
117. Extraire le nom d'utilisateur d'un identifiant de messagerie ............................................ .................................................. 44

118. Extraire le nom de domaine d'un identifiant de messagerie.................................. ...................................... 45

119. Emplacement du premier nombre dans une chaîne ............................................ ...................................................... 45

120. Emplacement du dernier numéro dans une chaîne ............................................ ...................................................... 45

121. Trouver la valeur de la première cellule non vide d'une plage .................................. ...................... 45

122. Rechercher la première valeur numérique dans une plage ............................................ ...................................................... 45

123. Rechercher la dernière valeur numérique dans une plage ............................................ ...................................................... 45

124. Rechercher la première valeur non numérique dans une plage ............................................ ...................................... 45

125. Rechercher la dernière valeur non numérique dans une plage ............................................ ...................................... 46

126. Rechercher la dernière valeur utilisée dans une plage ............................................ .................................................................. ...... 46

127. J'ai des données sur de nombreuses années mais je veux la somme pour les 12 derniers mois seulement ............... 46

128. Générer une liste unique à partir d'entrées en double............................................ ...................... 47

129. Fonction financière - Calculer l'EMI.................................................. .................................................................. 47

130. Fonction financière - Calculer la partie intérêt d'un IME................................................. ................ 48

131. Fonction financière - Calculer la partie principale d'un IME................................................. .............. 50

132. Fonction financière - Calculer le nombre d'IME pour rembourser un prêt................................. 51

133. Fonction financière - Calculer le taux d'intérêt.................................................. ...................................... 52

134. Fonction financière – Calculer les intérêts composés.......................................... .............. 54

135. Fonction financière – Calculer l’intérêt effectif ............................................ ...................... 55
136. Fonction financière – Calculer le TCAC et le TCAC .......................................... ...................... 56
137. Facturation des dalles – Calculer l'impôt sur le revenu et les factures d'électricité (services publics) en fonction des dalles ........ 57

138. LTRIM et RTRIM via des formules Excel.......................................... ...................................... 58

Bible des formules Excel

Une note sur les tableaux dynamiques (pas pour Excel 365 / Excel 2021)

Pour les versions Excel qui ne disposent pas de la fonctionnalité Dynamic Arrays, les équivalents non Dynamic
Arrays sont également indiqués immédiatement après les formules Dynamic Arrays.

Une note sur les formules matricielles (pas pour Excel 365 / Excel 2021)

Parfois, vous devrez saisir une formule sous forme de formule matricielle. Dans Excel 365/Excel 2021, toutes les formules
sont traitées comme des formules matricielles, vous n'avez donc pas besoin de saisir de formule comme formule
matricielle. Uniquement pour les anciennes versions d'Excel, vous devrez peut-être saisir une formule sous forme de
formule matricielle.

La formule matricielle n'est pas saisie en appuyant sur ENTRÉE après avoir saisi votre formule mais en
appuyant sur CTRL+SHIFT+ENTER. Si vous copiez et collez cette formule, prenez F2 après le collage et
CTRL+SHIFT+ENTER. Cela mettra des crochets { } autour de la formule que vous pouvez voir dans la
barre de formule. Si vous modifiez à nouveau, vous devrez refaire CTRL+SHIFT+ENTER. Ne mettez pas { }
manuellement.

Auteur - Vijay A Verma @ [Link] Page 1 sur 60

Bible des formules Excel

1. SOMME des chiffres lorsque la cellule contient tous les nombres

Si votre cellule ne contient que des nombres comme A1 := 7654045, la formule suivante peut être utilisée pour trouver la
somme des chiffres.

=SOMME(--MID(A1,SEQUENCE(LEN(A1)),1))

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

=SOMME(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

Si A1 est vide, alors pour gérer l'erreur, vous pouvez inclure les formules ci-dessus dans un bloc SIERREUR.

2. SOMME des chiffres lorsque la cellule contient à la fois des nombres et des
non-numéros

Si votre cellule ne contient pas de nombres autres que des nombres comme A1 := 76$5a4b045%d, les formules
suivantes peuvent être utilisées pour trouver la somme des chiffres.

=SOMME(SIERREUR(--MID(A1,SEQUENCE(LEN(A1)),1),0))

=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$9),"")))*ROW($1:$9))

=SOMME(SIERREUR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))

3. Une liste est unique ou non (qu'elle comporte des doublons)

En supposant que votre liste soit de A1 à A1000. Utilisez la formule suivante pour savoir si la liste est unique.

=MAX([Link](A1:A1000,A1:A1000))

Si la réponse est 1, alors elle est Unique. Si la réponse est supérieure à 1, elle n’est pas unique.

4. Compter le nombre de valeurs uniques

Utilisez la formule suivante pour compter non. de valeurs uniques -

=SI(COMPTE(A1:A100)=0,0,COMPTE(UNIQUE(FILTRE(A1:A100&"",A1:A100<>""))))

=SOMMEPRODUIT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

=SOMME((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

5. Compter le nombre de valeurs uniques de manière conditionnelle

Si vous disposez de données comme ci-dessous et que vous souhaitez trouver le nombre unique pour la région = « A », vous pouvez
utiliser la formule ci-dessous :

Auteur - Vijay A Verma @ [Link] Page 2 sur 60

Bible des formules Excel

=IF(COUNTIF(A2:A20,"A")=0,0,COUNTA(UNIQUE(FILTER(B2:B20,A2:A20="A"))))

=SOMME(SI(FRÉQUENCE(IF(A2:A20<>"",IF(A2:A20="A",MATCH(B2:B20,B2:B20,0))),LIGNE(A
2:A20)- RANGÉE(A2)+1),1))

Si vous avez plus de conditions, la même chose peut être construite après A2:A20 = « A ».

6. Générez des noms de jours de semaine séquentiels comme Sun, Mon, Tue,
. . . . . , Assis

Pour 3 caractères Nom du jour de la semaine

=TEXTE(DATE(2017,1,SÉQUENCE(7)),"jjj")

=TEXTE(DATE(2017,1,LIGNE($1:$7)),"jjj")

(Remarque : l'année 2017 a été utilisée car le 1er janvier 2017 était un dimanche. Vous pouvez utiliser n'importe quelle année à la place de

2017 où le 1er janvier était un dimanche, comme l'année 2023.)

Pour le nom complet du jour de la semaine, remplacez ddd par dddd

=TEXTE(DATE(2017,1,SÉQUENCE(7)),"jjjj")

=TEXTE(DATE(2017,1,ROW($1:$7)),"dddd")

Vous pouvez ajouter un numéro si vous souhaitez afficher un autre nom de jour de la semaine. Dites, si vous voulez
afficher 1 = lundi, 2 = mardi…….7 = dimanche, ajoutez simplement 1 . Les formules deviendraient donc

=TEXTE(1+DATE(2017,1,SÉQUENCE(7)),"jjj")

=TEXTE(1+DATE(2017,1,LIGNE($1:$7)),"jjj")

Auteur - Vijay A Verma @ [Link] Page 3 sur 60

Bible des formules Excel

Les formules ci-dessus généreront la séquence dans une colonne. Si vous souhaitez générer à la suite, utilisez
les formules suivantes

=TEXTE(DATE(2017,1,SÉQUENCE(,7)),"jjj")

=TRANSPOSE(TEXTE(DATE(2017,1,ROW($1:$7)),"jjj"))

7. Générez des noms de mois séquentiels comme janvier, février, mars...

décembre

Générer des noms de mois à 3 caractères

=TEXTE(DATE(1,SÉQUENCE(12),1),"mmm")

=TEXTE(DATE(1,LIGNE($1:$12),1),"mmm")

Pour le nom complet du mois, remplacez mmm par mmmm

=TEXTE(DATE(1,SÉQUENCE(12),1),"mmmm")

=TEXTE(DATE(1,LIGNE($1:$12),1),"mmmm")

Les formules ci-dessus généreront les noms des mois dans une colonne. Pour générer à la suite –

=TEXTE(DATE(1,SÉQUENCE(,12),1),"mmm")

=TRANSPOSE(TEXTE(DATE(1,ROW($1:$12),1),"mmm"))

8. Trouver le dernier jour du mois

Supposons que vous ayez une date dans la cellule A1≔ 14 août 22, alors la formule pour trouver le dernier jour du
mois est

=EOMOIS(A1,0)

9. Nombre de jours dans un mois

Supposons que l'on vous ait donné une date, par exemple le 15 novembre 21, et que vous deviez déterminer
combien de jours contient ce mois particulier.

La formule que vous devez utiliser dans le cas ci-dessus serait

=JOUR(EOMOIS(A1,0))

10. Trouver le premier jour du mois

Supposons que vous ayez une date dans la cellule A1≔ 14 août 21, alors la formule pour trouver le premier jour du
mois est

Auteur - Vijay A Verma @ [Link] Page 4 sur 60

Bible des formules Excel

=A1-JOUR(A1)+1

=EOMOIS(A1,-1)+1

=DATE(ANNÉE(A1),MOIS(A1),1)

11. Ajouter un mois ou soustraire un mois à une date donnée

Très souvent, vous rencontrerez des problèmes commerciaux où vous devrez ajouter ou soustraire un
mois à une date donnée. Un scénario est le calcul pour la date EMI.

Supposons que vous ayez une date du 22/10/21 (MM/JJ/AA) dans A1 et que vous souhaitiez ajouter le
nombre de mois contenu dans la cellule B1.

La formule dans ce cas serait

=DATE(A1,B1)

[Formule secondaire =DATE(ANNÉE(A1),MOIS(A1)+B1,JOUR(A1)) ]

Maintenant, vous souhaitez soustraire le mois contenu dans la cellule B1.

=EDATE(A1,-B1)

[Formule secondaire =DATE(ANNÉE(A1),MOIS(A1)-B1,JOUR(A1)) ]

12. Ajouter une année ou soustraire une année à une date donnée

Dans de nombreux problèmes commerciaux, vous pourriez rencontrer des situations dans lesquelles vous devrez ajouter ou
soustraire des années à une date donnée.

Disons que A1 contient la date et que B1 contient le nombre d'années.

Si vous souhaitez ajouter des années à une date donnée, les formules seraient :

=DATE(A1,12*B1)

=DATE(ANNÉE(A1)+B1,MOIS(A1),JOUR(A1))

Si vous souhaitez soustraire des années à une date donnée, les formules seraient :

=EDATE(A1,-12*B1)

=DATE(ANNÉE(A1)-B1,MOIS(A1),JOUR(A1))

13. Convertir un nombre en nom de mois

Utilisez la formule ci-dessous pour générer un mois nommé à 3 lettres comme janvier, février... décembre.

Auteur - Vijay A Verma @ [Link] Page 5 sur 60

Bible des formules Excel

=TEXTE(A1*30,"mmm")

Remplacez "mmm" par "mmmm" pour générer le nom complet du mois comme janvier,
février... décembre dans l'une des formules de cet article.

14. Convertir un nom de mois en numéro

Supposons que la cellule A1 contienne la chaîne janvier, février… décembre (ou janvier février… .. décembre) et que vous
souhaitez afficher 1, 2…… 12.

=MOIS("1"&A1)

La formule fonctionnerait tant que les noms de mois contiennent >=3 caractères. Par conséquent, cela fonctionnerait pour,
disons, janvier, décembre, avril ou octobre.

15. Convertir un numéro en nom de jour de la semaine

Supposons que vous souhaitiez renvoyer 1 = dimanche, 2 = lundi…..7 = samedi

=TEXTE(DATE(2017,1,A1),"jjjj")

Remarque – 2017 a été utilisé dans la formule ci-dessus car le 1er janvier 2017 était un dimanche. Vous pouvez utiliser n’importe quelle

année où le 1er janvier était un dimanche, comme l’année 2023.

Pour afficher seulement 3 caractères du nom du jour de la semaine, remplacez dddd par ddd

=TEXTE(DATE(2017,1,A1),"jjj")

Vous pouvez ajouter un numéro à A1 si vous souhaitez afficher un autre nom de jour de la semaine

Dites, si vous voulez afficher 1 = lundi, 2 = mardi…….7 = dimanche, ajoutez simplement 1 à A1

=TEXTE(1+DATE(2017,1,A1),"jjjd")

Dites, si vous voulez afficher 1 = vendredi, 2 = samedi…….7 = jeudi, ajoutez simplement 5 à A1

=TEXTE(5+DATE(2017,1,A1),"jjjj")

16. Convertir un nom de jour de la semaine en numéro

Supposons que la cellule A1 contienne la chaîne Dimanche, Lundi….Samedi (ou Dimanche, Mon…..Sat) et que vous
souhaitez afficher 1, 2…..7, la formule suivante peut être utilisée pour renvoyer les nombres. Dimanche sera 1 heure
et samedi sera 7 heures.

=ROND(RECHERCHE(GAUCHE(A1,2),"SuMoTuWeThFrSa")/2,0)

=MATCH(GAUCHE(A1,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)

Si nous voulons renvoyer un autre nombre aux jours de la semaine, la formule peut être modifiée en
conséquence. Par exemple, pour que Mon = 1 et Sun = 7

Auteur - Vijay A Verma @ [Link] Page 6 sur 60

Bible des formules Excel

=ROND(RECHERCHE(GAUCHE(A1,2),"MoTuWeThFrSaSu")/2,0)

=MATCH(GAUCHE(A1,2),{"Lun","Tu","Nous","Je","Fr","Sa","Di"},0)

17. Formule de l'exercice financier (par exemple 2015-16 ou FY16)

Un bon nombre de pays ne suivent pas l'année civile comme exercice financier. Par exemple, l’exercice
financier de l’Inde commence le 1er avril et se termine le 31 mars. Par conséquent, actuellement (20 février
2016), l’exercice financier est 2015-16 (il s’écrit également FY16). Le 1er avril 2016, cela deviendra 2016-17 (il
est également écrit FY17).

Maintenant, si une date est donnée, la formule suivante peut être utilisée pour obtenir un résultat de type 2015-2016.

=ANNÉE(A1)-(MOIS(A1)<=3)&"-"&ANNÉE(A1)+(MOIS(A1)>3)

Pour générer un résultat de type FY16, la formule suivante peut être utilisée

="FY"&DROITE(ANNÉE(A1)+(MOIS(A1)>3),2)

18. Conversion d'une date en trimestre civil

En supposant que la date se trouve dans la cellule A1. Vous souhaitez le convertir en quart (1, 2, 3 et 4). De janvier à mars, du 1er avril au 2

juin, du 3 juillet au 3 septembre et du 4 octobre au 4 décembre.

=PLAFOND(MOIS(A1)/3,1)

OU

= ARRONDISSEMENT(MOIS(A1)/3,0)

OU

=CHOISIR(MOIS(A1),1,1,1,2,2,2,3,3,3, 4,4,4)

19. Conversion de la date en un trimestre de l'exercice financier indien

En supposant que la date se trouve dans la cellule A1. Vous souhaitez le convertir en trimestre d’exercice financier indien. De janvier à mars,

du 4 avril au 1er juin, du 2 juillet au 2 septembre et du 3 octobre au 3 décembre.

=PLAFOND(MOIS(EDATE(A1,-3))/3,1)

OU

= ARRONDISSEMENT(MOIS(EDATE(A1,-3))/3,0)

OU

=CHOISIR(MOIS(A1),4,4,4,1,1,1,2,2,2,3,3,3)

Auteur - Vijay A Verma @ [Link] Page 7 sur 60

Bible des formules Excel

20. Déterminer le trimestre de l'exercice

Peu de pays suivent un trimestre différent autre que le premier trimestre de janvier à mars et le deuxième trimestre d'avril à juin. Dans le cas

de janvier à mars comme Q1, la formule est simple (si la cellule A2 est la date)

=ARRONDISSEMENT(MOIS(A2)/3,0)

Cela donnera le résultat 1, 2, 3 et 4 pour les trimestres. Si vous le souhaitez, vous pouvez concaténer "Q" dans la formule
pour afficher Q1, Q2, etc. comme ci-dessous

="Q"&ROUNDUP(MOIS(A2)/3,0)

Si votre exercice financier commence en avril, alors pour janvier-mars, le trimestre est le 4 tandis que pour avril à juin, le
trimestre est le 1 et ainsi de suite. Dans ce cas, vous pouvez utiliser la formule suivante

=PLAFOND(MOIS(EDATE(A1,-3))/3,1)

= ARRONDISSEMENT(MOIS(EDATE(A1,-3))/3,0)

Si votre exercice financier commence en juillet, alors pour janvier-mars, le trimestre est le 3 tandis que pour juillet à septembre, le
trimestre est le 1 et ainsi de suite. Dans ce cas, vous pouvez utiliser la formule suivante

=PLAFOND(MOIS(EDATE(A1,-6))/3,1)

= ARRONDISSEMENT(MOIS(EDATE(A1,-6))/3,0)

Si votre exercice financier commence en octobre, alors pour janvier-mars, le trimestre est le 2 tandis que pour octobre à décembre,
le trimestre est le 1 et ainsi de suite. Dans ce cas, vous pouvez utiliser la formule suivante

=PLAFOND(MOIS(EDATE(A1,-9))/3,1)

= ARRONDISSEMENT(MOIS(EDATE(A1,-9))/3,0)

21. Calculer l'âge à partir de l'anniversaire donné

=DATEDIF(A1,TODAY(),"y")&" Années "&DATEDIF(A1,TODAY(),"ym")&" Mois

"&DATEDIF(A1,TODAY(),"md")&" Jours"

22. Convertir de jj/mm/aa en mm/jj/aa (DMY en MDY)

Supposons que vous ayez les dates suivantes au format DMY

24/8/22
24/8/2022
08/04/92
08/04/1992

Et vous devez les convertir au format MDY, puis utiliser la formule suivante

Cas 1–si votre format de date par défaut est MDY

Auteur - Vijay A Verma @ [Link] Page 8 sur 60

Bible des formules Excel

=FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"mm/jj/aaaa"),"/","</s><s>")&"</s></t
> ","//s[2]")&"/"&FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"mm/jj/aaaa"),"/","</s
> <s>")&"</s></t>","//s[1]")&"/"&FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"mm/ jj/
aaaa"),"/","</s><s>")&"</s></t>","//s[3]")

Cas 2–si votre format de date par défaut est DMY

=FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"jj/mm/aaaa"),"/","</s><s>")&"</s></t
> ","//s[2]")&"/"&FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"jj/mm/aaaa"),"/","</s
> <s>")&"</s></t>","//s[1]")&"/"&FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"dd/
mm/aaaa"),"/","</s><s>")&"</s></t>","//s[3]")

23. Convertir de mm/jj/aa en jj/mm/aa (MDY en DMY)

Supposons que vous ayez les dates suivantes au format MDY :
24/08/22
24/08/2022
8/04/92
08/04/1992

Et vous devez les convertir au format DMY, puis utiliser la formule suivante

Cas 1–si votre format de date par défaut est MDY

=(FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"mm/jj/aaaa"),"/","</s><s>")&"</s></ t
>","//s[2]")&"/"&FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"mm/jj/aaaa"),"/","</s
> <s>")&"</s></t>","//s[1]")&"/"&FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"mm/ jj/
aaaa"),"/","</s><s>")&"</s></t>","//s[3]"))

Cas 2–si votre format de date par défaut est DMY

=FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"jj/mm/aaaa"),"/","</s><s>")&"</s></t
> ","//s[2]")&"/"&FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"jj/mm/aaaa"),"/","</s
> <s>")&"</s></t>","//s[1]")&"/"&FILTERXML("<t><s>"&SUBSTITUTE(TEXT(A1,"dd/
mm/aaaa"),"/","</s><s>")&"</s></t>","//s[3]")

Remarque – Fondamentalement, MDY en DMY et DMY en MDY sont les mêmes formules car nous
échangeons simplement la place de MM avec DD et ne faisons rien d'autre.

24. Conversion du format nombre en date

Si vous avez des nombres comme 010216 et que vous souhaitez les convertir au format de date, la
formule suivante peut être utilisée

=--TEXT(A1,"00\/00\/00") pour une année à 2 chiffres

Remarque – Un minimum de 5 chiffres sont nécessaires pour que la formule ci-dessus fonctionne

Auteur - Vijay A Verma @ [Link] Page 9 sur 60

Bible des formules Excel

Si vous avez des nombres comme 01022016 et que vous souhaitez les convertir au format de date, la
formule suivante peut être utilisée

=--TEXT(A1,"00\/00\/0000") pour une année à 4 chiffres

Remarque – Un minimum de 7 chiffres sont nécessaires pour que la formule ci-dessus fonctionne

25. Conversion du format nombre en format heure

Si vous avez des nombres comme 1215 et que vous souhaitez les convertir au format hh:mm, la formule
suivante peut être utilisée

=--TEXTE(A1,"00\:00")

Remarque – Au moins 3 chiffres sont nécessaires pour que la formule ci-dessus fonctionne

Pour convertir au format hh:mm:ss

=--TEXTE(A1,"00\:00\:00")

Remarque – Un minimum de 5 chiffres sont nécessaires pour que la formule ci-dessus fonctionne

26. Convertir le temps en heures, minutes et secondes décimales

Supposons que A1 ait l'heure 23h35, vous pouvez alors utiliser les formules suivantes

Pour convertir en heures décimales

=A1*24

Pour convertir en minutes décimales

=A1*1440

(1440 n'est rien d'autre que 24*60)

Pour convertir en secondes décimales

=A1*86400

(86400 n'est rien d'autre que 24*60*60)

(Remarque – Vous devrez formater votre cellule de résultat au format décimal)

27. Convertir les heures, minutes et secondes décimales en heure

Supposons que A1 ait des heures décimales 23,58, vous pouvez alors utiliser la formule suivante pour la reconvertir en
heure

=A1/24

Auteur - Vijay A Verma @ [Link] Page 10 sur 60

Bible des formules Excel

S'il comporte des minutes décimales, disons 1415, vous pouvez alors utiliser la formule suivante pour le reconvertir en
temps.

=A1/1440

S'il comporte des secondes décimales, par exemple 84900, vous pouvez utiliser la formule suivante pour le reconvertir en
temps.

=A1/86400

(Remarque – Vous devrez formater votre cellule de résultat au format Heure)

28. Générer une séquence de dates

Générez 90 dates séquentielles à partir du 1er avril 2021. Disons que la date est dans la cellule A1. Vous pouvez
utiliser l'une des formules suivantes

=SÉQUENCE(90,,A1)

=LIGNE(INDIRECT(A1&":"&A1+89))

Maintenant, générons toutes les dates d'un mois donné. Disons que nous sommes en février 2021. Vous pouvez
utiliser la formule suivante où A1 a la date du 1er février 2021

=SÉQUENCE(JOUR(EOMOIS(A1,0)),,A1)

=LIGNE(INDIRECT(A1&":"&EOMOIS(A1,0)))

Les formules ci-dessus généreront des dates dans une colonne. Pour générer à la suite

=SÉQUENCE(,90,A1)

=TRANSPOSE(LIGNE(INDIRECT(A1&":"&A1+89)))

=SÉQUENCE(,JOUR(EOMOIS(A1,0)),A1)

=TRANSPOSE(LIGNE(INDIRECT(A1&":"&EOMONTH(A1,0))))

29. Générer une séquence de temps

Générez 40 heures séquentielles commençant à 11h00 avec un incrément de 15 minutes où
A1 : = 11h00

=A1+SÉQUENCE(40,,,15/(24*60))

=A1+(RANGÉE(1:40)-1)*15/(24*60)

30. Comment savoir si une année est une année bissextile

Auteur - Vijay A Verma @ [Link] Page 11 sur 60

Bible des formules Excel

Disons que A1 contient l'année. Pour savoir s'il s'agit d'une année bissextile ou non, utilisez la formule
suivante -

=MOIS(DATE(A1,2,29))=2

=JOUR(EOMOIS(DATE(A1,2,1),0))=29

VRAI signifie qu'il s'agit d'une année bissextile et FAUX signifie qu'il ne s'agit pas d'une année bissextile.

31. Dernier jour ouvrable du mois si une date est indiquée

Si A1 contient une date, la formule de calcul du dernier jour ouvrable du mois serait

=JOUR OUVRABLE(EOMOIS(A1,0)+1,-1)

La formule ci-dessus suppose que vos week-ends sont le samedi et le dimanche.

Mais si vos week-ends sont différents (par exemple dans les pays du Golfe), vous pouvez utiliser la formule suivante :

=[Link](EOMONTH(A1,0)+1,-1,"0000110")

Où 0000110 est une chaîne de 7 caractères, 1 représente un week-end et 0 un jour ouvrable. Le premier
chiffre est le lundi et le dernier chiffre est le dimanche. L'exemple ci-dessus concerne les pays du Golfe où le
vendredi et le samedi sont les week-ends.

Vous avez également la possibilité de proposer une plage comportant des jours fériés. Dans ce cas, votre formule
deviendrait

=JOUR OUVRABLE(EOMOIS(A1,0)+1,-1,D1:D10)

=[Link](EOMONTH(A1,0)+1,-1,"0000110",D1:D10)

Où la plage D1:D10 contient la liste des jours fériés.

32. Premier jour ouvrable du mois si une date est donnée

Si A1 contient une date, alors la formule pour le premier jour ouvrable du mois serait

=JOUR OUVRABLE(EOMOIS(A1,-1),1)

La formule ci-dessus suppose que vos week-ends sont le samedi et le dimanche.

Mais si vos week-ends sont différents (par exemple dans les pays du Golfe), vous pouvez utiliser la formule suivante :

=[Link](EOMONTH(A1,-1),1,"0000110")

Où 0000110 est une chaîne de 7 caractères, 1 représente un week-end et 0 un jour ouvrable. Le premier
chiffre est le lundi et le dernier chiffre est le dimanche. L'exemple ci-dessus concerne les pays du Golfe où le
vendredi et le samedi sont les week-ends.

Auteur - Vijay A Verma @ [Link] Page 12 sur 60

Bible des formules Excel

Vous avez également la possibilité de proposer une plage comportant des jours fériés. Dans ce cas, votre formule
deviendrait

=JOUR OUVRABLE(EOMOIS(A1,-1),1,D1:D10)

=[Link](EOMONTH(A1,-1),1,"0000110",D1:D10)

Où la plage D1:D10 contient la liste des jours fériés.

33. Déterminer le nombre de jours ouvrables dans une année

Supposons que vous ayez reçu une année en A1 (disons A1 = 2021) et que vous deviez déterminer le nombre
de jours ouvrables dans une année, alors votre formule pour déterminer le nombre de jours ouvrables serait :

=JOURS RÉSEAU(DATE(A1,1,1),DATE(A1,12,31))

La formule ci-dessus est basée sur le fait que les samedis et dimanches sont des week-ends. À
partir d'Excel 2010, vous pouvez contrôler les week-ends dans la formule et la fonction est
[Link]

=[Link](DATE(A1,1,1),DATE(A1,12,31),"0000110")

Dans la chaîne "000110" – Le premier chiffre est le lundi et le dernier chiffre est le dimanche. 1 définit ce jour
particulier comme le week-end.

Si vous avez votre liste de jours fériés dans une plage, par exemple B1:B20 (B1:B20 doit contenir des dates au
format date), vous pouvez avoir les formules suivantes

=JOURS RÉSEAU(DATE(A1,1,1),DATE(A1,12,31),B1:B20)

=[Link](DATE(A1,1,1),DATE(A1,12,31),"0000110",B1:B20)

34. Déterminer le nombre de jours ouvrables dans un mois

Disons que vous devez trouver le nombre de jours ouvrables dans l'année 2022 pour le mois de juillet où l'année est
en A1 et le mois au format numérique est en A2 (par conséquent, A2 devrait être 7 et non juillet), vous pouvez alors
utiliser la formule suivante

=JOURS RÉSEAU(DATE(A1,A2,1),EOMOIS(DATE(A1,A2,1),0))

La formule ci-dessus est basée sur le fait que les samedis et dimanches sont des week-ends. À
partir d'Excel 2010, vous pouvez contrôler les week-ends dans la formule et la fonction est
[Link]

=[Link](DATE(A1,A2,1), EOMONTH(DATE(A1,A2,1),0),"0000110")

Dans la chaîne "000110" – Le premier chiffre est le lundi et le dernier chiffre est le dimanche. 1 définit ce jour
particulier comme le week-end.

Auteur - Vijay A Verma @ [Link] Page 13 sur 60

Bible des formules Excel

Si vous avez votre liste de jours fériés dans une plage, par exemple B1:B20 (B1:B20 doit contenir des dates au
format date), vous pouvez avoir les formules suivantes

= JOURS RÉSEAU (DATE (A1, A2,1), EOMOIS (DATE (A1, A2,1), 0), B1: B20)

=[Link](DATE(A1,A2,1), EOMONTH(DATE(A1,A2,1),0),"0000110",B1:B20)

35. Combien de lundis ou tout autre jour de la semaine

entre 2 dates
Supposons que A1 = 23 janvier 2016 et A2 = 10 novembre 2016. Pour connaître le nombre de lundis entre ces deux
dates

=SOMME(--(JOUR SEMAINE(ROW(INDIRECT(A1&":"&A2)))=2))

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Mon"))

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=2))

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Mon"))

« Lun » peut être remplacé par n'importe quel autre jour de la semaine selon les besoins.

36. Trouver le numéro du vendredi 13 entre deux dates

données
Supposons que vous ayez reçu deux
dates A1 : = 1-jan-2014
A2 : = 25 novembre 2016

Vous pouvez calculer le nombre de vendredi 13èmeentre ces deux dates en suivant la formule

=SOMMEPRODUIT((JOUR SEMAINE(SÉQUENCE(A2-A1+1,,A1))=6)*(JOUR(SÉQUENCE(A2-
A1+1,,A1))=13))

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=6)*(DAY(ROW(INDIRECT(A
1&":"&A2)))=13))

37. Calculez le jour ouvrable suivant si la date tombe un week-end/un

jour férié

Supposons qu'on vous donne une date et qu'on vous demande de calculer le jour ouvrable suivant si la date est le
week-end. Si la date est un jour ouvrable normal, vous devez afficher la même date. Par exemple, le 8 mars 2019 est
un jour ouvrable. Par conséquent, vous devez afficher la même date. Mais s'il s'agit du 9 mars 2019 ou du 10 mars
2019, qui correspondent au samedi et au dimanche, vous devez alors indiquer le 11 mars 2019 comme jour
ouvrable suivant. Dans ce cas, la formule à utiliser serait

=JOUR DE TRAVAIL(A2-1,1)

Auteur - Vijay A Verma @ [Link] Page 14 sur 60

Bible des formules Excel

En supposant que vos vacances soient en E2:E3, alors la formule serait

=JOUR DE TRAVAIL(A2-1,1,$E$2:$E$3)

Remarque – Si vous utilisez des week-ends autres que le samedi et le dimanche, utilisez [Link] avec
les paramètres appropriés.

38. Calculez le jour ouvrable précédent si la date tombe un

week-end/un jour férié
Supposons qu'on vous donne une date et qu'on vous demande de calculer le jour ouvrable précédent si la date est
celle du week-end. Si la date est un jour ouvrable normal, vous devez afficher la même date.

Par exemple, le 8 mars 2019 est un jour ouvrable. Par conséquent, vous devez afficher la même date. Mais s'il s'agit du 9
mars 2019 ou du 10 mars 2019, qui correspondent au samedi et au dimanche, vous devez alors indiquer le 8 mars 2019
comme jour ouvrable précédent.

Dans ce cas, la formule à utiliser serait

=JOUR OUVRABLE(A2+1,-1)

En supposant que vos vacances soient en E2:E3, alors

la formule serait

=JOUR DE TRAVAIL(A2+1,-1,$E$2:$E$3)

Auteur - Vijay A Verma @ [Link] Page 15 sur 60

Bible des formules Excel

Remarque – Si vous utilisez des week-ends autres que le samedi et le dimanche, utilisez [Link] avec
les paramètres appropriés.

39. Date du Nième jour de l'année

Supposons que A1 contienne l’année et qu’il vous soit demandé de trouver le 69e jour de l’année
contenu dans A2. Alors la formule pour trouver le Nième jour de l’année serait

=DATE(A1,1,1)+A2-1

40. Extraire la date et l'heure de l'horodatage

Supposons que vous ayez une valeur d'horodatage de date dans la
cellule A1 A1 = 14/06/15 22h15

Et vous souhaitez en extraire la date et l’heure.

Pour extraire la date, utilisez la formule suivante et formatez la cellule de résultat en tant que date

= INT(A1)

Pour extraire l'heure, utilisez la formule suivante et formatez la cellule de résultat en heure

= MOD(A1,1)

41. Premier jour ouvrable de l'année

Si une année est donnée en A1, par exemple 2016, la formule ci-dessous peut être utilisée pour connaître le premier jour
ouvrable de l'année (formater le résultat en date)

=JOUR OUVRABLE(DATE(A1-1,12,31),1)

=JOUR OUVRABLE(DATE(A1,1,1)-1,1)

La formule ci-dessus suppose que vos week-ends sont le samedi et le dimanche.

Mais si vos week-ends sont différents (par exemple dans les pays du Golfe), vous pouvez utiliser la formule suivante :

=[Link](DATE(A1-1,12,31),1,"0000110")

Où 0000110 est une chaîne de 7 caractères, 1 représente un week-end et 0 un jour ouvrable. Le premier
chiffre est le lundi et le dernier chiffre est le dimanche. L'exemple ci-dessus concerne les pays du Golfe où le
vendredi et le samedi sont les week-ends.

Vous avez également la possibilité de proposer une plage comportant des jours fériés. Dans ce cas, votre formule
deviendrait

=JOUR OUVRABLE(DATE(A1-1,12,31),1,D1:D10)

=[Link](DATE(A1-1,12,31),1,"0000110",D1:D10)

Auteur - Vijay A Verma @ [Link] Page 16 sur 60

Bible des formules Excel

Où la plage D1:D10 contient la liste des jours fériés.

42. Dernier jour ouvrable de l'année

Si une année est donnée en A1, par exemple 2016, la formule ci-dessous peut être utilisée pour connaître le dernier jour
ouvrable de l'année (formater le résultat en date)

=JOUR OUVRABLE(DATE(A1+1,1,1),-1)

=JOUR OUVRABLE(DATE(A1,12,31)+1,-1)

La formule ci-dessus suppose que vos week-ends sont le samedi et le dimanche.

Mais si vos week-ends sont différents (par exemple dans les pays du Golfe), vous pouvez utiliser la formule suivante :

=[Link](DATE(A1+1,1,1),-1,"0000110")

Où 0000110 est une chaîne de 7 caractères, 1 représente un week-end et 0 un jour ouvrable. Le premier
chiffre est le lundi et le dernier chiffre est le dimanche. L'exemple ci-dessus concerne les pays du Golfe où le
vendredi et le samedi sont les week-ends.

Vous avez également la possibilité de proposer une plage comportant des jours fériés. Dans ce cas, votre formule
deviendrait

=JOUR OUVRABLE(DATE(A1+1,1,1),-1,D1:D10)

=[Link](DATE(A1+1,1,1),-1,"0000110",D1:D10)

Où la plage D1:D10 contient la liste des jours fériés.

43. Convertir de la date Excel (date grégorienne) en date

julienne
[Link] d’abord, qu’est-ce qu’une date julienne ?

[Link] date julienne comporte soit une date à 7 chiffres, soit une date à 5 chiffres et celles-ci sont généralement utilisées dans les anciens systèmes

informatiques existants.

7 chiffres - AAAAJJ - 2016092 (il s'agit du 1er avril 2016. 92 signifie que c'est le 92e jour du 1er janvier de
cette année)
5 chiffres - AAJJ - 16092

[Link] formules utiliser pour convertir des dates Excel en dates juliennes où A1 a la date du 14
février 2022 ?
[Link] 7 chiffres, utilisez la formule suivante

=--TEXTE(A1,"aaaa")&TEXTE(A1-DATE(ANNÉE(A1),1,1)+1,"000")

Pour 5 chiffres, utilisez la formule suivante

=--TEXTE(A1,"aa")&TEXTE(A1-DATE(ANNÉE(A1),1,1)+1,"000")

Auteur - Vijay A Verma @ [Link] Page 17 sur 60

Bible des formules Excel

44. Convertir des dates juliennes en dates Excel (grégoriennes)

Pour les dates juliennes à 7 chiffres, la formule suivante doit être utilisée

=DATE(GAUCHE(A1,4),1,DROITE(A1,3))

Pour les dates juliennes à 5 chiffres, la formule suivante doit être utilisée

=DATE(ANNÉE("1-1-"&GAUCHE(A1,2)),1,DROITE(A1,3))

45. Convertir un nombre en années et en mois

Supposons que vous ayez reçu un nombre dans la cellule A1, par exemple 26, et que vous souhaitiez l'afficher sous la
forme 2 ans et 4 mois, vous pouvez utiliser la formule suivante :

=INT(A1/12)&" Années et "&MOD(A1,12)&" Mois"

Désormais, un utilisateur peut devenir plus exigeant et dire que si le mois est inférieur à 12, alors les
années ne doivent pas être affichées. Par exemple, il pourrait dire que 8 devrait être converti en 8 mois
et qu'il ne devrait pas être affiché comme 0 ans et 8 mois.

Dans ce cas, la formule serait -

=IF(INT(A1/12)>0,INT(A1/12)&" Années et ","")&MOD(A1,12)&" Mois"

Désormais, 8 sera affiché comme 8 mois seulement et non comme 0 ans et 8 mois.

Désormais, l'utilisateur peut en demander plus. Il peut dire que lorsque je donne 12, cela s'affiche comme 1
an et 0 mois et il veut simplement voir 1 an seulement. Et pour 36 ans, il veut voir seulement 3 ans et non 3
ans 0 mois. Dans ce cas, la formule devra être modifiée davantage. Maintenant, la formule devient -

=IF(INT(A1/12)>0,INT(A1/12)&" Années ","")&IF(MOD(A1,12)=0,"",MOD(A1,12)&"

Mois" )

Désormais, un utilisateur peut venir et demander une dernière chose. Il peut dire que s'il s'agit de 1 an ou 1 mois, il
ne doit pas être affiché sous forme d'années ou de mois car 1 n'est pas au pluriel. Par conséquent, 25 doit être
affiché sous la forme 2 ans et 1 mois et non sous la forme 2 ans et 1 mois. Par conséquent, 18 ne doit pas être
affiché sous la forme 1 an et 6 mois mais sous la forme 1 an et 6 mois. De même, 13 doit être affiché sous la forme 1
an et 1 mois et non sous la forme 1 an et 1 mois.

=IF(INT(A1/12)>0,INT(A1/12)&" Année"&IF(INT(A1/12)>1,"s","")&" et
","")&MOD( A1,12)&" Mois"&IF(MOD(A1,12)>1,"s","")

46. Trouvez la semaine suivante du jour

Il y a 2 scénarios dans ce cas. Par exemple, si la date d'aujourd'hui est le 2 janvier 2017 (lundi) et que j'essaie de
trouver le lundi suivant, je peux obtenir le 2 janvier 2017 ou le 9 janvier 2017 selon mes besoins. Du mardi au
dimanche, ce n'est pas un problème car ils n'arrivent qu'après le 2 janvier 2017.

Auteur - Vijay A Verma @ [Link] Page 18 sur 60

Bible des formules Excel

Cas 1 - Si le jour tombe à la même date, alors cette même date(Par conséquent, dans le cas du 2 janvier 2017, le lundi
prochain serait le 2 janvier 2017 uniquement.)

Lun prochain =PLAFOND($A$1-2,7)+2

Mardi prochain =PLAFOND($A$1-3,7)+3
Mercredi prochain =PLAFOND($A$1-4,7)+4
jeu suivant =PLAFOND($A$1-5,7)+5
Vendredi prochain =PLAFOND($A$1-6,7)+6
Samedi prochain =PLAFOND($A$1-7,7)+7
Soleil prochain =PLAFOND($A$1-8,7)+8

Cas 2 - Si le jour tombe à la même date, alors la date suivante(Par conséquent, dans le cas du 2 janvier 2017, le lundi prochain
serait le 9 janvier 2017 uniquement.)

Lun prochain =PLAFOND($A$1-1,7)+2

Mardi prochain =PLAFOND($A$1-2,7)+3
Mercredi prochain =PLAFOND($A$1-3,7)+4
jeu suivant =PLAFOND($A$1-4,7)+5
Vendredi prochain =PLAFOND($A$1-5,7)+6
Samedi prochain =PLAFOND($A$1-6,7)+7
Soleil prochain =PLAFOND($A$1-7,7)+8

47. 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 janvier 2017 (lundi) et que j'essaie de
trouver le lundi précédent, je peux obtenir le 2 janvier 2017 ou le 26 décembre 2016 selon mes 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 2017, le
lundi précédent serait le 2 janvier 2017 uniquement.)

Lundi précédent =PLAFOND($A$1-8,7)+2

Mardi précédent =PLAFOND($A$1-9,7)+3
Mercredi précédent =PLAFOND($A$1-10,7)+4
jeu précédent =PLAFOND($A$1-11,7)+5
Vendredi précédent =PLAFOND($A$1-12,7)+6
Samedi précédent =PLAFOND($A$1-13,7)+7
Soleil précédent =PLAFOND($A$1-14,7)+8

Cas 2 - Si le jour tombe à la même date, alors la date précédente(Par conséquent, dans le cas du 2 janvier 2017, le lundi
précédent serait le 26 décembre 2016 uniquement.)

Lundi précédent =PLAFOND($A$1-9,7)+2

Mardi précédent =PLAFOND($A$1-10,7)+3
Mercredi précédent =PLAFOND($A$1-11,7)+4
jeu précédent =PLAFOND($A$1-12,7)+5
Vendredi précédent =PLAFOND($A$1-13,7)+6
Samedi précédent =PLAFOND($A$1-14,7)+7
Soleil précédent =PLAFOND($A$1-15,7)+8

Auteur - Vijay A Verma @ [Link] Page 19 sur 60

Bible des formules Excel

48. Compter les cellules commençant (ou se terminant) par une chaîne
particulière

1. Supposons que vous souhaitiez compter toutes les cellules commençant par C

=[Link](A1:A10,"c*")

c* n'est pas sensible à la casse. Par conséquent, il comptera les cellules commençant par c ou C.

Supposons que vous souhaitiez rechercher toutes les cellules commençant par Excel.

=[Link](A1:A10,"excel*")

2. Pour terminer

=[Link](A1:A10,"*c")

c* n'est pas sensible à la casse. Par conséquent, il comptera les cellules commençant par c ou C.

Supposons que vous souhaitiez rechercher toutes les cellules commençant par Excel.

=[Link](A1:A10,"*excel")

49. Compter le nombre de cellules comportant uniquement des chiffres

La fonction COUNT compte uniquement les cellules qui contiennent des nombres.

En supposant que votre plage est A1:A10, utilisez la formule suivante

=COMPTE(A1:A10)

50. Inverser une chaîne

Supposons que la cellule A1 :="qwerty" et que vous souhaitiez l'inverser

=TEXTJOIN(,,MID(A1,LEN(A1)-SEQUENCE(LEN(A1))+1,1))

=TEXTJOIN(,,MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))

51. Chaîne de nombres inversés

Supposons que la cellule A1 porte le numéro 16709 et que vous deviez l'inverser, c'est-à-dire que vous voulez 90761, la
formule suivante peut être utilisée

=SOMMEPRODUIT(MID(A1,SÉQUENCE(LEN(A1)),1)*10^SÉQUENCE(LEN(A1))/10)

=--TEXTJOIN(,,MID(A1,LEN(A1)-SEQUENCE(LEN(A1))+1,1))

Auteur - Vijay A Verma @ [Link] Page 20 sur 60

Bible des formules Excel

=--TEXTJOIN(,,MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))

52. Inverser une chaîne de mots

Supposons que vous ayez la chaîne de mots suivante dans la cellule A2 : « Moscou, Londres, Paris, Delhi,
Washington, Miami, Détroit, Berlin ».
Vous souhaitez inverser cette chaîne de mots et souhaitez le résultat suivant.
"Berlin, Détroit, Miami, Washington, Delhi, Paris, Londres, Moscou"

FILTERXML viendra à votre secours. Vous pouvez utiliser la formule suivante pour cela

=TEXTJOIN(", ",,SORTBY(FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>") &"</

s></t>", "//s"),SÉQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2,", ", " "))+1),-1))

=TEXTJOIN(", ",,INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")& "</

s></t>", "//s"),LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+2-
LIGNE(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,", "," "))+1))))

53. Inverser une plage de cellules

Supposons que vous ayez les mots suivants dans la plage A2:A11 et que vous souhaitiez inverser la séquence

La formule suivante peut être utilisée dans D2 et se répandra dans les cellules en dessous

=INDEX(A2:A11,COUNTA(A2:A11)-SEQUENCE(COUNTA(A2:A11))+1)

=INDEX(A2:A11,COUNTA(A2:A11)-ROW(INDIRECT("1:"&COUNTA(A2:A11)))+1)

= TRI PAR (FILTRE (A2:A11,A2:A11<>""),SÉQUENCE(COUNTA(A2:A11)),-1)

54. Obtenir le numéro de la première ligne d'une plage

Supposons que votre plage donnée soit D15: Q99 et que vous souhaitiez extraire le numéro de la première ligne qui
est 15 ici, puis utilisez la formule suivante

=@LIGNE(D15:Q99)

Auteur - Vijay A Verma @ [Link] Page 21 sur 60

Bible des formules Excel

=MIN(LIGNE(D15:Q99))

55. Obtenir le numéro de la dernière ligne dans une plage

Supposons que votre plage donnée soit D15: Q99 et que vous souhaitiez extraire le dernier numéro de ligne qui est
99 ici, puis utilisez la formule suivante

=@Trier(LIGNE(D15:Q99),,-1)

=MAX(LIGNE(D15:Q99))

56. Obtenir le numéro de la première colonne d'une plage

Supposons que votre plage donnée soit D15: Q99 et que vous souhaitiez extraire ici le numéro de la première
colonne qui est 4 (4 signifie D), puis utilisez la formule suivante

=@COLONNE(D15:Q99)

=MIN(COLONNE(D15:Q99))

57. Obtenir le numéro de la dernière colonne dans une plage

Supposons que votre plage donnée soit D15: Q99 et que vous souhaitiez extraire ici le dernier numéro de
colonne qui est 17 (17 signifie Q), puis utilisez la formule suivante

=MAX(COLONNE(D15:Q99))

58. Extraire des caractères uniques

Supposons que la cellule A1 ait la valeur "abracadabra" et que vous souhaitiez en extraire des caractères uniques, c'est-à-
dire que la réponse devrait être "abracadabra". Ensuite, la formule suivante peut être utilisée (à condition que vous
disposiez de la fonctionnalité Dynamic Array)

=TEXTJOIN(,,UNIQUE(MID(A1,SEQUENCE(LEN(A1)),1)))

=TEXTJOIN(,,UNIQUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

59. Testez si une plage contient uniquement des chiffres

Utilisez la formule ci-dessous pour tester si une plage donnée, par exemple A1: A10, ne contient que des nombres.

=ESTNUMBER(--TEXTJOIN("",1,A1:A10))

60. Testez si une plage contient uniquement des alphabets anglais

Utilisez la formule ci-dessous pour tester si une plage donnée, par exemple A1: A10, contient uniquement des alphabets
anglais.

Auteur - Vijay A Verma @ [Link] Page 22 sur 60

Bible des formules Excel

=NON(ISERROR(SUM(MATCH(UPPER(A1:A10),CHAR(ROW(65:90)),0))))

Si vous voulez ignorer les espaces

=NON(ISERROR(SUM(MATCH(UPPER(FILTER(A1:A10,A1:A10<>"")),CHAR(ROW(65:90)), 0))))

61. Compter le nombre de cellules contenant uniquement des

caractères
Par conséquent, si votre cellule porte le nombre 2,23, elle ne sera pas comptée car il s’agit d’un nombre.

Utilisez la formule ci-dessous en considérant que votre plage est A1:A10

=[Link](A1:A10,"*")

62. Nombre de caractères dans une chaîne sans tenir compte des
espaces

Disons que vous avez une chaîne comme Vijay A. Verma et j'ai besoin de savoir combien de caractères elle contient. Dans
ce cas, il en a 12, y compris les décimales et en laissant les blancs de côté.

Utilisez la formule ci-dessous pour le même -

=LEN(SUBSTITUT(A1," ",""))

63. Nombre de fois qu'un caractère apparaît dans une chaîne

Supposons que vous vouliez compter le nombre de fois où le caractère « a » apparaît dans une chaîne

=LEN(A1)-LEN(SUBSTITUT(INFÉRIEUR(A1),"a",""))

64. Compter les non-nombres dans une chaîne

Supposons que vous ayez une chaîne "abc123def45cd" et que vous souhaitiez y compter les non-nombres.

Si votre chaîne est en A1, utilisez la formule suivante en A1

=COUNT(IF(ISNUMBER(--MID(A1,SEQUENCE(LEN(A1)),1))," ",0))-(A1="")

=COUNT(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))," ",0))-(A1="")

= SUMPRODUCT(--NOT(ISNUMBER((--MID(A1,SEQUENCE(LEN(A1)),1))))) -(A1="")

= SUMPRODUCT(--NOT(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))) -
(A1="")

Auteur - Vijay A Verma @ [Link] Page 23 sur 60

Bible des formules Excel

65. Compter les nombres dans une chaîne

Supposons que vous ayez une chaîne "abc123def43cd" et que vous souhaitiez y compter des nombres.

Si votre chaîne est en A1, utilisez la formule suivante –

=COUNT(--MID(A1,SEQUENCE(LEN(A1)),1))

=COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

= SOMMEPRODUIT(--ISNUMBER((--MID(A1,SEQUENCE(LEN(A1)),1))))

= SUMPRODUCT(--ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

=SOMMEPRODUIT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))

66. Comptez uniquement les alphabets dans une chaîne

Supposons que vous ayez une chaîne "Ab?gh123def%h*" et que vous souhaitiez compter uniquement les alphabets.

Supposons que votre chaîne soit en A1, mettez la formule suivante pour cela.

=SOMMEPRODUIT(LEN(A1)-
LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))

=SUMPRODUCT(--(ABS(77.5-
CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))

67. Générer des alphabets anglais

Pour générer des alphabets anglais A…Z

=CHAR(SÉQUENCE(26,,65))

=CHAR(LIGNE(65:90))

Pour générer des alphabets anglais a…z

=CHAR(SÉQUENCE(26,,97))

=CHAR(LIGNE(97:122))

Les formules ci-dessus généreront des alphabets anglais dans une colonne. Pour générer à la suite

=CHAR(SÉQUENCE(,26,65))

=CHAR(SÉQUENCE(,26,97))

=TRANSPOSE(CHAR(ROW(65:90)))

Auteur - Vijay A Verma @ [Link] Page 24 sur 60

Bible des formules Excel

=TRANSPOSE(CHAR(ROW(97:122)))

68. Convertir les alphabets anglais en chiffres

Il peut y avoir des scénarios dans lesquels vous devez convertir les alphabets a, b en y, z en 1, 2 en 25, 26 (ou A, B en
Y, Z en 1, 2 en 25, 26). Vous pouvez utiliser l'un des éléments suivants formules pour le faire.

=CODE(INFÉRIEUR(A1))-96

=CODE(SUPÉRIEUR(A1))-64

69. Convertir des nombres en alphabets anglais

Il peut y avoir des scénarios dans lesquels vous devez convertir les nombres 1, 2 en 25, 26 en a, b en y, z (ou en A, B
en Y, Z). Vous pouvez utiliser l'une des formules suivantes pour le faire

(la première formule est pour les minuscules et la seconde pour les majuscules)

=CHAR(A1+96)

=CHAR(A1+64)

70. Extraire le nième mot du devant

Supposons que vous ayez la chaîne suivante : "Moscou, Londres, Paris, Delhi, Washington, Miami,
Détroit, Berlin".
Et vous souhaitez récupérer le nième mot du début.

Vous pouvez utiliser la formule FILTERXML suivante pour cela

=FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[N]")

Où N doit être remplacé par le numéro de mot souhaité.

Par conséquent, si vous vouliez un deuxième mot, remplacez-le par 2.

=FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[2]")

Remarque – Dans ma chaîne, une virgule suivie d'un espace est un séparateur. Si votre séparateur est différent, par
exemple un espace, remplacez la partie rouge dans SUBSTITUTE(A2,",","</s><s>"). Par conséquent, si votre séparateur est
simplement un espace, alors

=FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[N]")

71. Extraire le nième mot du dos

Supposons que vous ayez la chaîne suivante
Moscou, Londres, Paris, Delhi, Washington, Miami, Détroit, Berlin Et vous
souhaitez récupérer le nième mot de l'arrière-plan.

Auteur - Vijay A Verma @ [Link] Page 25 sur 60

Bible des formules Excel

Vous pouvez utiliser la formule FILTERXML suivante pour cela

Vous souhaitez maintenant récupérer le nième mot de l'arrière-plan. Vous pouvez utiliser la formule FILTERXML suivante pour cela

=FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s["&LEN(A2 )-
LEN(SUBSTITUT(A2,", "," "))+2-N&"]")

Par conséquent, si vous voulez le dernier mot, remplacez N par 1. Si vous avez besoin de l'avant-dernier mot, remplacez N par 2.
Par conséquent, pour le 3ème dernier mot, la formule serait (après avoir remplacé N par 3)

=FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s["&LEN(A2 )-
LEN(SUBSTITUT(A2,", "," "))+2-3&"]")

Remarque – Ici encore, le séparateur dans SUBSTITUTE doit être remplacé de manière appropriée si vous n'utilisez
pas mon séparateur dans votre chaîne.

72. Extraire les deux premiers mots OU les n premiers mots

Vous pouvez utiliser la formule suivante pour extraire les 2 premiers mots d'une chaîne de texte

=SIERREUR(GAUCHE(A2,TROUVER(" ",A2&" ",TROUVER(" ",A2)+1)-1),"")

Une formule générique pour extraire les 2 premiers mots qui peut être étendue à n mots

=SIERREUR(GAUCHE(A2,TROUVER(REPT(" ",LEN(A2)),SUBSTITUTE(A2&""," ",REPT("

",LEN(A2)),2))-1),"")

=SIERREUR(REPLACE(A2,FIND(REPT(" ",LEN(A2)),SUBSTITUTE(A2&" "," ",REPT("

",LEN(A2)),2)),LEN(A2),""),"")

Pour extraire les n premiers mots, remplacez le 2 ci-dessus qui est surligné en gras. Par conséquent, si vous souhaitez
extraire les 3 premiers mots

=SIERREUR(GAUCHE(A2,TROUVER(REPT("
",LEN(A2)),SUBSTITUTE( A2&" "," ",REPT(" ",LEN(A2)),3))-1),"")

=SIERREUR(REPLACE(A2,FIND(REPT(" ",LEN(A2)),SUBSTITUTE( A2&"

"," ",REPT(" ",LEN(A2)),3)),LEN(A2)," "), "")

73. Extraire les deux derniers mots OU les n derniers mots

Pour extraire les 2 derniers mots, utilisez la formule ci-dessous

=SI(ISNUMBER(FIND(" ",A2)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT("

",LEN(A2))),2*LEN(A2))),"")

Faire une formule générique, extraire les 2 derniers mots

Auteur - Vijay A Verma @ [Link] Page 26 sur 60

Bible des formules Excel

=SI([Link](A2,"* *")>0,TRIM(DROITE(SUBSTITUTE(A2," ",REPT("

",LEN(A2))),2*LEN(A2))),"")

Maintenant, vous devrez changer uniquement * * et 2 qui sont marqués en rouge. Si vous devez
remplacer les 3 derniers mots, alors * * sera remplacé par * * * et 2 par 3. Par conséquent, la formule
deviendra

=IF(COUNTIF(A2,"* * *")>0,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT("

",LEN(A2))),3*LEN(A2))),"" )

Par conséquent, si vous souhaitez extraire uniquement le dernier mot

=IF(COUNTIF(A2,"*")>0,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT("
",LEN(A2))),1*LEN(A2))),"")

Attention, en fait pour le dernier mot, utilisez simplement le deuxième composant de la formule (juste pour faire
plus simple)

=TRIM(DROITE(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),1*LEN(A2)))

Si vous n'êtes pas fan de la technique * * et souhaitez remplacer uniquement les nombres, utilisez la formule ci-dessous où
2 peut être remplacé par n'importe quel nombre (2 sert à extraire les 2 derniers mots)

=IF((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))>=2-1,TRIM(RIGHT( SUBSTITUTE(A2,"

",REPT(" ",LEN(A2)) ),2*LEN(A2))),"")

74. Valeur la plus courante dans une plage

En supposant que votre plage soit A1:A10, entrez la formule ci-dessous

=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))

Remarque – Si la plage A1 : A10 ne contient que des nombres, la formule suivante suffit

=MODE(A1:A10)

75. COUNTIF avec condition OU

Pour la plage donnée, disons que vous souhaitez compter les sous-zones A et B pour
la zone Nord, vous pouvez alors utiliser la formule suivante

=SUM(COUNTIFS(A2:A10,"Nord",B2:B10,{"A","B"}))

Auteur - Vijay A Verma @ [Link] Page 27 sur 60

Bible des formules Excel

76. SUMIF avec condition OU

Pour la plage donnée, disons que vous souhaitez additionner les
notes pour la classe 1 et les matières = anglais et français. Ensuite,
vous pouvez utiliser la formule suivante

= SOMME(SUMIFS(C2:C10,A2:A10,1,B2:B10,
{"Anglais","Français"}))

77. COUNTIF sur la liste filtrée

Vous pouvez utiliser SUBTOTAL pour effectuer COUNT sur une liste filtrée, mais COUNTIF ne peut pas être effectué sur une
liste filtrée. La formule ci-dessous peut être utilisée pour effectuer COUNTIF sur une liste filtrée

=SOMMEPRODUIT(SOUS-TOTAL(3,OFFSET(B2,LIGNE(B2:B20)-LIGNE(B2),))*(B2:B20>14))

Ici, B2:B20>14 est comme un critère dans COUNTIF (=COUNTIF(B2:B20,">14"))

78. SUMIF sur la liste filtrée

Vous pouvez utiliser SUBTOTAL pour effectuer SUM sur une liste filtrée, mais SUMIF ne peut pas être effectué sur une liste
filtrée. La formule ci-dessous peut être utilisée pour effectuer SUMIF sur une liste filtrée

=SOMMEPRODUIT(SOUS-TOTAL(9,OFFSET(B2,LIGNE(B2:B20)-LIGNE(B2),))*(B2:B20>14))

Ici, B2:B20>14 est comme un critère dans SUMIF.

79. Extraire le prénom du nom complet

=GAUCHE(A1,TROUVER(" ",A1&" ")-1)

80. Extraire le nom de famille du nom complet

=TRIM(DROITE(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

81. Extraire l'initiale du deuxième prénom

Supposons que vous ayez un nom John Doe Smith et que vous souhaitiez afficher D comme initiale. En supposant
que vos données soient en A1, vous pouvez utiliser la formule suivante

=SI(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,1),"")

Auteur - Vijay A Verma @ [Link] Page 28 sur 60

Bible des formules Excel

Si le nom est composé de 2 ou 1 mots, le résultat sera vide. Cela fonctionne uniquement sur un nom de 3 mots, car le
milieu ne peut être décidé que pour un nom de 3 mots.

82. Extraire le deuxième prénom du nom complet

=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND("

",A1)+1)),"")

=IF(COUNTIF(A1,"* * *"),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1)),2),FIND("

",A1)+1,LEN (A1))),"")

=IF(COUNTIF(A1,"* * *"),GAUCHE(REPLACE(A1,1,FIND(" ",A1),""),FIND("

",REPLACE(A1,1,FIND(" ", A1),""))-1))

83. Supprimer le deuxième prénom du nom complet

=IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1,"

",REPT(" ",LEN(A1))) ,LEN(A1))),"")

=IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND("

" ,A1),""),"")

84. Extraire la partie entière et décimale d'un nombre

Pour extraire une partie entière, l'un des éléments ci-dessous peut être utilisé -

=INT(A1)

=TRONC(A1)

Valeur positive dans A1 - Si A1 contient 84,65, alors la réponse serait 84 dans les deux cas

Valeur négative dans A1 - Si A1 contient -84,65, alors la réponse serait -85 en cas de INT
et -84 en cas de TRUNC.

Si vous souhaitez uniquement une valeur +ve, que la valeur dans A1 soit -ve ou +ve, la formule peut avoir de nombreuses
variantes. Notez les réponses pour les valeurs négatives dans le cas INT.

84,65 - 84,65
INT(ABS(A1)) 84 84
COFFRE(ABS(A1)) 84 84
ABS(INT(A1)) 84 85
ABS (COFFRE (A1)) 84 84

Pour extraire la partie décimale -

=MOD(ABS(A1),1)

Auteur - Vijay A Verma @ [Link] Page 29 sur 60

Bible des formules Excel

=ABS(A1)-INT(ABS(A1))

Valeur positive dans A1 - Si A1 contient 84,65, alors la réponse serait 0,65.

Valeur négative dans A1 - Si A1 contient -24,39, alors la réponse serait 0,39.

85. Durée maximale d'apparition consécutive d'une

entrée particulière

Supposons que nous voulions compter le nombre maximum de fois où « A » apparaît

consécutivement,

=MAX(FRÉQUENCE(SI(A2:A20="A",LIGNE(A2:A20)),
SI(A2:A20<>"A",LIGNE(A2:A20))))

86. Obtenir le nom du fichier via la formule

Avant d'obtenir cela, assurez-vous que votre fichier a été enregistré au moins une fois car cette formule
dépend du nom du chemin du fichier qui ne peut être extrait par la fonction CELL que si le fichier a été
enregistré au moins une fois.

=CELL("nom de fichier",$A$1)

87. Obtenir le nom du classeur via la formule

Avant d'obtenir cela, assurez-vous que votre fichier a été enregistré au moins une fois car cette formule
dépend du nom du chemin du fichier qui ne peut être extrait par la fonction CELL que si le fichier a été
enregistré au moins une fois.

=REPLACE(LEFT(CELL("nom de fichier",$A$1),FIND("]",CELL("nom de fichier",$A$1))-

1),1,FIND("[",CELL("nom de fichier" ,$A$1)),"")

88. Obtenir le nom de la feuille via la formule

Avant d'obtenir cela, assurez-vous que votre fichier a été enregistré au moins une fois car cette formule
dépend du nom du chemin du fichier qui ne peut être extrait par la fonction CELL que si le fichier a été
enregistré au moins une fois.

Utilisez la formule suivante -

Auteur - Vijay A Verma @ [Link] Page 30 sur 60

Bible des formules Excel

=REPLACE(CELL("nom de fichier",A1),1,FIND("]",CELL("nom de fichier",A1)),"")

Assurez-vous que A1 est utilisé dans la formule. S'il n'est pas utilisé, il extraira le nom de la dernière feuille
active qui n'est peut-être pas celle que nous souhaitons.

Si vous souhaitez uniquement le nom de la dernière feuille active, la formule deviendra

=REPLACE(CELL("nom de fichier"),1,FIND("]",CELL("nom de fichier")),"")

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

Avant d'obtenir cela, assurez-vous que votre fichier a été enregistré au moins une fois car cette formule
dépend du nom du chemin du fichier qui ne peut être extrait par la fonction CELL que si le fichier a été
enregistré au moins une fois.

Si votre classeur se trouve, par exemple, dans C:\Excel\MyDocs, la formule pour récupérer le répertoire
correspondant serait

=GAUCHE(CELL("nom de fichier",A1),FIND("[",CELL("nom de fichier",A1))-2)

90. Effectuer une RECHERCHEV multi-colonnes

Vous connaissez RECHERCHEV, l'une des fonctions les plus appréciées d'Excel. La syntaxe est
RECHERCHEV(lookup_value,table_array,col_index_num,range_lookup)

Ici, lookup_value peut être une valeur unique et non plusieurs valeurs.

Maintenant, vous rencontrez une situation où vous souhaitez effectuer une recherche virtuelle 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.

=RECHERCHEXL(1,(A2:A12=F2)*(B2:B12=G2),C2:C12)

=INDEX(FILTRE(A2:C12,(A2:A12=F2)*(B2:B12=G2)),3)

=INDEX(C2:C12,MATCH(1,--((A2:A12=F2)*(B2:B12=G2)*(LIGNE(A2:A12)-
LIGNE(A2)+1)<>0), 0))

Auteur - Vijay A Verma @ [Link] Page 31 sur 60

Bible des formules Excel

Approche de concaténation

=RECHERCHEXL(F2&"@@@"&G2,A2:A12&"@@@"&B2:B12,C2:C12,"")

=INDEX(C2:C12,MATCH(F2&"@@@"&G2,A2:A12&"@@@"&B2:B12,0))

@@@ peut être remplacé par n'importe quel caractère qui ne doit pas faire partie de ces colonnes.

Par concaténation, vous pouvez avoir autant de colonnes que possible.

PRUDENCE -Le résultat de la concaténation complète ne doit pas avoir une longueur supérieure à 255.
Par conséquent, F2&"@@@"&G2 ne doit pas avoir plus de 255 caractères.

Une autre alternative consiste à utiliser la formule ci-dessous -

=INDEX(C2:C12,MATCH(1,--NOT(ISLOGIQUE(IF(A2:A12=F2,IF(B2:B12=G2,C2:C12)))),0))

Remarque – Pour gérer la condition Introuvable, vous pouvez placer toutes les formules ci-dessus dans un
bloc SIERREUR.

91. RECHERCHEV de droite à gauche

(Rendu redondant par XLOOKUP mais utile pour les anciennes versions)

RECHERCHEV recherche toujours de gauche à droite. Par conséquent, dans le tableau ci-dessous, je peux trouver la date de
naissance de Naomi en donnant la formule suivante :

=RECHERCHEV("Naomi",B:D,3,0)

Auteur - Vijay A Verma @ [Link] Page 32 sur 60

Bible des formules Excel

Mais, si je dois trouver l'Emp ID correspondant à Naomi, je ne peux pas le faire via la formule
RECHERCHEV. Pour effectuer RECHERCHEV de droite à gauche, vous devrez utiliser la combinaison
INDEX / MATCH. Par conséquent, vous devrez utiliser la formule suivante –

=INDEX(A:A,MATCH("Naomi",B:B,0))

Si vous utilisez XLOOKUP, la formule serait simplement

=RECHERCHEXL("Naomi",B:B,A:A)

92. RECHERCHEV sensible à la casse

Supposons que vous ayez des données comme dans le tableau ci-dessous et que vous souhaitiez effectuer une RECHERCHEV sensible à la casse.

Si vous effectuez une RECHERCHEV régulière sur SARA, j'obtiendrais la réponse 4300. Mais dans une RECHERCHEV sensible à la
casse, la réponse devrait être 3200. Vous pouvez utiliser la formule ci-dessous pour la RECHERCHEV sensible à la casse.
-

=RECHERCHEXL(1,--EXACT("SARA",A2:A10),B2:B10)

=INDEX(FILTRE(A2:B6,EXACT(A2:A6,"SARA")),2)

=INDEX(B2:B10,MATCH(VRAI,EXACT("SARA",A2:A10),0))

93. Rang au sein des groupes

Supposons que vous disposiez de données comme dans le tableau ci-dessous et que vous souhaitiez connaître le classement des étudiants.

Vous mettrez simplement la formule suivante dans D2

=GRANG(C2,C2:C100)

Mais que se passe-t-il si on vous demande de produire le classement des élèves au sein de chaque

école. Par conséquent, le classement de chaque école commencera par 1.

Mettez la formule suivante dans D2 pour ce cas pour le classement par

ordre décroissant. (Pour l'ordre croissant, remplacez ">" par "<" sans
guillemets)

=SOMMEPRODUIT((B$2:B$100=B2)*(C$2:C$100>C2))+1

Auteur - Vijay A Verma @ [Link] Page 33 sur 60

Bible des formules Excel

OU

=COUNTIFS(B$2:B$100,B2,C$2:C$100,">"&C2)+1

94. Extraire les alphabets d'une chaîne

Si votre chaîne est dans la cellule A1, dites "Vij1aY A. V4er7ma8", utilisez la formule suivante pour en
extraire les alphabets

=TEXTJOIN("",,IF(--(ABS(77.5-
CODE(MID(UPPER(A1),SEQUENCE(LEN(A1)),1)))<13),MID(A1,SEQUENCE(LEN(A1 )),1),"" ) )

=TEXTJOIN("",,IF(--(ABS(77.5-
CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13),MID (A1,ROW(INDIR
ECT("A1:A"&LEN(A1))),1),""))

95. Supprimer les alphabets d'une chaîne

Si votre chaîne est dans la cellule A1, dites "Vij1aY A. V4er7ma8", utilisez la formule suivante pour supprimer tous les
alphabets d'une chaîne

=TEXTJOIN("",,IF(--(ABS(77.5-
CODE(MID(UPPER(A1),SEQUENCE(LEN(A1)),1)))<13),"",MID(A1,SEQUENCE( LEN(A1)),1) ) )

=TEXTJOIN("",,IF(--(ABS(77.5-
CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13)," ",MID(A1,ROW(IND
IRECT("A1:A"&LEN(A1))),1)))

=SUBSTITUT(SUBSTITUT(SUBSTITUT(SUBSTITUT(SUBSTITUT( SUBSTITUT(SUBSTITUTE(SUBSTITUT(SUB
(SUBSTITUT (SUBSTITUT (SUBSTITUT)
( SUBSTITUT(INFÉRIEUR(A1),"a",""),"b",""),"c",""),"d",""),"e",""),"f ",""),
"g",""),"h",""),"i",""),"j",""),"k",""),"l", ""),"m",""),"n",""),"o",""),
"p",""),"q",""),"r","" ),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""), "y",""),"z","")

96. Extraire les nombres d'une chaîne

=--TEXTJOIN("",,IF(ISERROR(--
MID(A1,SEQUENCE(LEN(A1)),1)),"",MID(A1,SEQUENCE(LEN(A1)),1)))

=--TEXTJOIN("",,IF(ISERROR(--
MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),"",MID(A1,ROW(INDIRECT( "A1:A"&LEN( A1))),1)))

97. Supprimer les nombres de la chaîne

Auteur - Vijay A Verma @ [Link] Page 34 sur 60

Bible des formules Excel

Pour supprimer des nombres d'une chaîne (par exemple Vij1aY A. V4er7ma8 contient des nombres qui ne sont pas
obligatoires), utilisez la formule ci-dessous en supposant que la chaîne se trouve dans la cellule A1 :

=TEXTJOIN("",,IF(ISERROR(--
MID(A1,SEQUENCE(LEN(A1)),1)),MID(A1,SEQUENCE(LEN(A1)),1),""))

=TEXTJOIN("",,IF(ISERROR(--
MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("A1:A" &LEN(A1 ) ) ) ,
1),""))

=SUBSTITUT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITU
"),6,""),7,""),8,""),9,""),0,"")

Remarque - Étant donné que cette formule comporte plusieurs lignes, vous devrez donc la copier dans la barre de formule.
Si vous copiez cette formule dans une cellule, elle la copiera sur trois lignes.

98. Représentation romaine des nombres

Utilisez la fonction ROMAINE.

Donc ROMAN(56) donnera LVI.

ROMAN ne fonctionne que pour les numéros 1 à 3999.

99. Somme des N valeurs inférieures dans une plage

Supposons que vous ayez des nombres dans la plage A1 : A100 et que vous souhaitiez résumer les 10 dernières valeurs.

=SOMME(PETIT($A$1:$A$100,ROW(1:10)))

=SOMMEPRODUIT(PETIT($A$1:$A$100,ROW(1:10)))

=SOMME(AGGREGATE(15,6,$A$1:$A$100,ROW(1:10)))

=SOMMEPRODUIT(AGGREGATE(15,6,$A$1:$A$100,ROW(1:10)))

Au cas où vous souhaiteriez ignorer les valeurs 0 (et les blancs)

=SOMME(PETIT(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))

=SOMMEPRODUIT(PETIT(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))

Les deux formules ci-dessus ne fonctionneront que s'il y a au moins N valeurs selon ROW(1:N). Par
conséquent, pour les formules ci-dessus, cela ne fonctionnerait que s’il y avait au moins 10 nombres dans A1
à A100.

Pour surmonter cette limitation -

Auteur - Vijay A Verma @ [Link] Page 35 sur 60

Bible des formules Excel

Entrez les formules ci-dessous

=SOMME(SIERREUR(PETIT($A$1:$A$100,ROW(1:10)),0))

=SOMME(SIERREUR(PETIT(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)),0))

=SOMMEPRODUIT(AGGREGATE(15,6,$A$1 :$A$100/($A$1 :$A$100<>0),ROW(1:10)))

=SOMME(AGGREGATE(15,6,$A$1 :$A$100/($A$1 :$A$100<>0),ROW(1:10)))

100. Somme chaque Nième ligne

Si vos nombres sont dans la plage A1:A100, utilisez la formule ci-dessous

=SOMME((A1:A100)*(MOD(LIGNE(A1:A100)-LIGNE(A1)+1,2)=0))

=SOMMEPRODUIT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0))

La formule ci-dessus est pour chaque 2ème ligne. Remplacez 2 par N. Par conséquent, pour chaque 5ème ligne –

=SOMME((A1:A100)*(MOD(LIGNE(A1:A100)-LIGNE(A1)+1,5)=0))

=SOMMEPRODUIT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0))

Il s’agit d’une formule générique qui fonctionnera pour n’importe quelle gamme. Si votre plage est B7:B50, votre formule
deviendrait

=SOMME((B7:B50)*(MOD(LIGNE(B7:B50)-LIGNE(B7)+1,2)=0))

=SOMMEPRODUIT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))

101. Additionnez chaque ligne paire

Si votre plage est A1:A100, utilisez la formule suivante.

=SOMME((A1:A100)*(MOD(LIGNE(A1:A100)-LIGNE(A1)+1,2)=0))

=SOMMEPRODUIT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0))

Il s'agit d'une formule générique, donc si votre plage est B7:B50, votre formule deviendra

=SOMME((B7:B50)*(MOD(LIGNE(B7:B50)-LIGNE(B7)+1,2)=0))

=SOMMEPRODUIT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))

102. Additionnez chaque ligne impaire

Si votre plage est A1:A100, utilisez la formule suivante.

=SOMME((A1:A100)*(MOD(LIGNE(A1:A100)-LIGNE(A1)+1,2)<>0))

Auteur - Vijay A Verma @ [Link] Page 36 sur 60

Bible des formules Excel

=SOMMEPRODUIT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)<>0))

Il s'agit d'une formule générique, donc si votre plage est B7:B50, votre formule deviendra

=SOMME((B7:B50)*(MOD(LIGNE(B7:B50)-LIGNE(B7)+1,2)<>0))

=SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)<>0))

103. Somme des N premières valeurs dans une plage

Supposons que vous ayez des nombres dans la plage A1:A100 et que vous souhaitiez résumer les N premières valeurs

=SOMME(LARGE($A$1:$A$100,ROW(1:10)))

=SOMMEPRODUIT(LARGE($A$1:$A$100,ROW(1:10)))

=SOMME(AGGREGATE(14,6,$A$1:$A$100,ROW(1:10)))

=SOMMEPRODUIT(AGGREGATE(14,6,$A$1:$A$100,ROW(1:10)))

Au cas où vous souhaiteriez ignorer les valeurs 0 (et les blancs)

=SOMME(LARGE(IF($A$1 :$A$100<>0,$A$1 :$A$100),ROW(1:10)))

=SOMMEPRODUIT(LARGE(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))

Les deux formules ci-dessus ne fonctionneront que s'il y a au moins N valeurs selon ROW(1:N). Par
conséquent, pour les formules ci-dessus, cela ne fonctionnerait que s’il y avait au moins 10 nombres dans A1
à A100. Pour surmonter cette limitation –

=SOMME(SIERREUR(LARGE($A$1:$A$100,ROW(1:10)),0))

=SOMME(SIERREUR(LARGE(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)),0))

=SOMME(AGGREGATE(14,6,$A$1 :$A$100/($A$1 :$A$100<>0),ROW(1:10)))

=SOMMEPRODUIT(AGGREGATE(14,6,$A$1 :$A$100/($A$1 :$A$100<>0),ROW(1:10)))

104. Nous avons MOYENNEIF. Qu’en est-il de MEDIANIF et

MODEIF ?
Excel ne fournit pas MEDIANIF et MODEIF. Vous devrez utiliser des formules Array pour obtenir ces
fonctionnalités. Supposons que nos données soient comme ci-dessous –

Auteur - Vijay A Verma @ [Link] Page 37 sur 60

Bible des formules Excel

Pour calculer MEDIANIF et MODEIF, entrez les formules ci-dessous

=MÉDIANE(SI(A2:A13="M",B2:B13))

=MODE(SI(A2:A13="M",B2:B13))

Pour MEDIANIF

=AGRÉGAT(16,6,(B1:B13)/(A1:A13="m"),50%)

Pour MODEIF

=INDEX(B1:B20,MATCH(MAX(INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,)),IND
EX((COUNTIF(B1:B20, B1:B20)*(A1:A20="m")),,),0))

105. Calculer la moyenne géométrique en ignorant les valeurs 0

et négatives
La moyenne géométrique est une moyenne utile et s'applique uniquement aux valeurs +ve. Par conséquent, vous devrez ignorer
les valeurs <=0 lors du calcul de la moyenne géométrique. Il est généralement utilisé lorsque des pourcentages sont impliqués. Par
exemple, la croissance démographique pour la première année est de 30 %, pour la deuxième année de 25 % et pour la troisième
année, elle est de 15 %. Ensuite, la moyenne géométrique est utilisée pour calculer la moyenne non arithmétique.

Généralement, la moyenne géométrique est calculée par la formule =GEOMEAN(A1:A10)

Cela donnerait une erreur si la plage contient des valeurs <=0. Il existe différentes manières de résoudre ce
problème, la plus couramment utilisée étant d'ignorer les valeurs <=0 lors du calcul de la moyenne géométrique.
Pour ignorer les valeurs <=0, vous devez utiliser une formule Array, c'est-à-dire qui doit être saisie en appuyant sur
CTRL+SHIFT+ENTER.

=GÉOMÉENNE(SI(A1:A10>0,A1:A10))

La formule ci-dessus ne prend en compte que les valeurs positives.

Auteur - Vijay A Verma @ [Link] Page 38 sur 60

Bible des formules Excel

Astuce bonus-Lorsque le pourcentage de croissance de l'âge est impliqué, vous devrez utiliser la formule ARRAY suivante
pour calculer la moyenne géométrique -

=GÉOMÉENNE(SI(A1:A10>0,(1+A1:A10)))-1

N'oubliez pas de formater votre résultat en %age.

106. Générer des codes GL

Pour générer 20 nombres de codes GL comme 1000, 1100, 1200 et ainsi de suite, la formule ci-dessous peut être
utilisée

=1000+(LIGNE(1:20)-1)*100

=SÉQUENCE(20,,1000,100)

La formule ci-dessus générera des codes GL dans une colonne. Pour générer à la suite

=SÉQUENCE(,20,1000,100)

=TRANSPOSER(1000+(LIGNE(1:20)-1)*100)

107. Abréger les prénoms

Si vous avez des noms donnés comme
- Smith Johnson
Liz Lotte
Diadème Christy Lewisk
John

Et vous devez produire des abréviations ou des acronymes pour eux, comme ci-dessous, en majuscules.

Smith Johnson - SJ
Liz lotte - LT
Diadème Christy Lewisk - CTL
john - J

Ensuite, vous pouvez utiliser la formule suivante pour jusqu'à 3 mots dans le nom -

=UPPER(TRIM(LEFT(A1,1)&MID(A1,FIND(" ",A1&" ")

+1,1)&MID(A1,FIND("*",SUBSTITUTE(A1&" "," ","* ",2))+1,1)))

Explication de la formule

1. GAUCHE(A1,1) - Extrait la première lettre du prénom

2. MILIEU(A1,TROUVER(" ",A1&" ")+1,1) -
FIND(" ",A1&" ") - Find recherche le premier espace du nom donné pour localiser le début du deuxième
prénom. " " a été concaténé à la fin de A1 de sorte que s'il n'y a qu'un prénom, FIND ne donnera pas
d'erreur car il trouvera toujours les espaces. +1 a été ajouté pour commencer la position MID à partir de
laquelle commence le deuxième prénom.

Auteur - Vijay A Verma @ [Link] Page 39 sur 60

Bible des formules Excel

3. MID(A1,FIND("*",SUBSTITUTE(A1&" "," ","*",2))+1,1))

SUBSTITUTE(A1&" "," ","*",2) remplacera le deuxième espace par un *, nous pouvons donc trouver la position de *
pour localiser le début du nom de famille. Comme en 2 ci-dessus, un double espace " " a été ajouté dans A1 pour
que FIND trouve toujours le deuxième espace. +1 a été ajouté pour commencer la position MID à partir de laquelle
commence le nom de famille.
4. TRIM supprimera tous les blancs insérés à cause de 2 ou 3.
5. UPPER convertira la chaîne en majuscules.
Remarque - Si vous n'utilisez pas la concaténation d'un espace simple et d'un espace double comme dans 2 et
3, alors le bloc SIERREUR peut être utilisé. Dans ce cas, la formule deviendrait -

=UPPER(TRIM(GAUCHE(A1,1)&IFERREUR(MID(A1,FIND("
",A1)+1,1),"")&IFERREUR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"")))

Note-Cette technique peut être utilisée pour étendre jusqu'à plusieurs mots. Seul le changement
aura lieu dans le dernier bloc où vous pourrez remplacer 2 par 3, 4,5 et ainsi de suite dans
IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1 ,1),"") pour les 4ème, 5ème, 6ème mots et
les concaténer.... Par conséquent, pour jusqu'à 6 mots, la formule deviendrait

=UPPER(TRIM(GAUCHE(A1,1)&IFERREUR(MID(A1,FIND("
",A1)+1,1),"")&IFERREUR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"")
&SIERREUR(MID(A1,FIND("*",SUBSTITUTE(A1,"
","*",3))+1,1),"")&SIERREUR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",4))+1,1), "")
&SIERREUR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",5))+1,1),"")))

108. Obtenir le nom de colonne pour un numéro de colonne

Supposons que vous ayez un nombre dans A1 et que vous souhaitiez obtenir le nom de la colonne pour cela.

Donc, si A1=1, vous voulez "A". Donc, si A1 =26,

vous voulez "Z". Donc, si A1=27, vous voulez "AA"
et ainsi de suite.

La formule pour dériver le nom de la colonne serait -

=SUBSTITUT(ADRESSE(1,A1,4),1,"")

109. Obtenir la plage de colonnes pour un numéro de colonne

Supposons que vous ayez un nombre dans A1 et que vous souhaitiez obtenir la plage de colonnes correspondante.

Donc, si A1=1, vous voulez "A:A".

Donc, si A1 =26, vous voulez "Z:Z".
Par conséquent, si A1=27, vous voulez "AA:AA" et ainsi de suite.

La formule pour dériver la plage de colonnes serait -

=SUBSTITUT(ADRESSE(1,A1,4)&":"&ADRESSE(1,A1,4),1,"")

Auteur - Vijay A Verma @ [Link] Page 40 sur 60

Bible des formules Excel

110. Trouvez le nième plus grand nombre lorsqu'il y a des

doublons
Vous connaissez la fonction LARGE qui permet de trouver la nième plus grande valeur. Par conséquent, si vous avez une série
comme ci-dessous –

Et vous donnez =LARGE(A1:A10,3), vous obtenez la réponse 18

Maintenant, si nous avons une série comme ci-dessous

Maintenant, vous donnez =LARGE(A1:A10,3) et maintenant le résultat est 24. La raison en est que la grande fonction donne
la nième plus grande valeur dans un tableau trié. Par conséquent, la fonction LARGE triera le tableau ci-dessus comme
suit : {24,24,24,22,22,18,18,9} et le troisième plus grand est 24.

Mais en réalité, vous voulez le 3ème plus grand, qui est 18, comme réponse.

La formule pour un tel cas serait

=GRAND(UNIQUE(A1:A10),3)

=LARGE(IF(FREQUENCE($A$1:$A$10,$A$1:$A$10)<>0,$A$1:$A$10),3)

111. COUNTIF pour une plage non contiguë

Nous aimons tous COUNTIF. Et c'est très simple à faire - dites simplement =COUNTIF("A1:A100",">5") et il
trouve toutes les valeurs comprises dans la plage A1 à A100 qui sont supérieures à 5. Mais que se passe-t-il si
je veux le résultat pour seulement A3, A8 et il doit omettre les autres cellules. Essayez de mettre la formule
suivante -

=COUNTIF((A3, A8),">5") et cela vous donnera l'erreur #VALUE.

Auteur - Vijay A Verma @ [Link] Page 41 sur 60

Bible des formules Excel

Une solution possible est

=(A3>5)+(A8>5)

Que se passe-t-il si vous devez le faire pour A3, A4, A5, A8, A24, A40, A45, A89. Maintenant, vous devrez
utiliser une formule comme -

=(A3>5)+(A4>5)+(A5>5)+(A8>5)+(A24>5)+(A40>5)+(A45>5)+(A89>5)

La formule devient lourde à mesure que le nombre de cellules augmente. Dans ce cas, vous pouvez utiliser la
formule ci-dessous. Cette formule unique peut prendre en charge les plages contiguës (comme A3:A5) et non
contiguës :

=SOMME(COUNTIF(INDIRECT({"A3:A5","A8","A24","A40","A45","A89"}),">5"))

112. Compter le nombre de mots dans une cellule/plage

Supposons que l'on vous ait donné ce qui suit et que vous deviez compter le nombre de mots dans une cellule ou
dans une plage.

Formule pour calculer le nombre de mots dans une cellule -

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")

Formule pour calculer le nombre de mots dans une plage -

=SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100)," ",""))
+(TRIM(A1:A100)<>""))

113. Numérologie Somme des chiffres, également appelée Somme des chiffres jusqu'à
ce que le résultat soit un seul chiffre

En numérologie, il s'agit souvent d'additionner les chiffres jusqu'à ce que le résultat soit un seul chiffre. Par exemple,

74 = 7 + 4 = 11 = 1 + 1 = 2
23 = 2 + 3 = 5

Auteur - Vijay A Verma @ [Link] Page 42 sur 60

Bible des formules Excel

78 = 7 + 8 = 15 = 1 + 5 = 6
1234567 = 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 = 2+ 8 = 10 = 1+ 0 = 1

La formule pour obtenir le même résultat est

=MOD(A1-1,9)+1

114. Générez des numéros séquentiels et répétez-les

Supposons que vous ayez pour tâche de générer une séquence de nombres et de les répéter.

Par exemple -
1,2,3,4,1,2,3,4,1,2,3,4

Vous pouvez utiliser la formule ci-dessous et faire glisser vers le bas -

=MOD(LIGNES($1:1)-1,4)+1

Remplacez 4 par n’importe quel autre nombre pour générer toute autre séquence. Par conséquent, si
vous souhaitez générer 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2 ,3,4,5,6,7,8,9,10 alors la formule
devient -

=MOD(LIGNES($1:1)-1,10)+1

La structure de la formule est

=MOD(LIGNES($1:1)-1,X)+Y

X - Nombre de numéros Y -
Numéro de départ

Utiliser au-dessus de formule, toi vouloir à générer le séquence

5,6,7,8,9,10,5,6,7,8,9,10,5,6,7,8,9,10, puis utilisez la formule ci-dessous (vous avez besoin de 6 nombres et indiquez
que le nombre est 5 )

=MOD(LIGNES($1:1)-1,6)+5

115. Répétez un nombre, incrémentez et répétez....

Supposons que l’on vous ait confié la tâche de répéter un nombre, d’incrémenter ce nombre et de
le répéter. Par exemple -
1,1,1,1,2,2,2,2,3,3,3,3.....(Ici, nous le répétons 4 fois et incrémentons et répétons 4 fois
encore et ainsi de suite)

Ensuite, vous pouvez utiliser la formule suivante

=ARRONDISSEMENT(LIGNES($1:1)/4,0)

Supposons que vous souhaitiez commencer le nombre par 5 et non par 1, vous pouvez alors utiliser la formule ci-dessous :

Auteur - Vijay A Verma @ [Link] Page 43 sur 60

Bible des formules Excel

=ARRONDISSEMENT(LIGNES($1:1)/4,0)+4

Par conséquent, la structure générale de la formule est

=ARRONDISSEMENT(LIGNES($1:1)/X,0)+Y-1

X - Nombre de fois qu'un nombre particulier est répété Y -

Numéros de départ

Par conséquent, si vous voulez commencer par le numéro 7 et que vous souhaitez le répéter 5 fois, alors la formule
suivante doit être utilisée

=ARRONDISSEMENT(LIGNES($1:1)/5,0)+6

116. Générer des nombres aléatoires non répétitifs via

une formule
Supposons que vous souhaitiez générer des nombres aléatoires non répétitifs compris entre 1 et 30, vous pouvez utiliser la
formule suivante dans A2 et la faire glisser vers le bas

=INDEX(UNIQUE(RANDARRAY(30-1+1,,1, 30, TRUE)), SÉQUENCE(10))

Dans la formule ci-dessus, SEQUENCE(10) signifie que nous voulons générer 10 nombres aléatoires.
30-1+1 = Max – Min + 1 => Ces nombreux nombres seront générés
1- Min et 30 – Max VRAI
– Entier uniquement

=SIERREUR(AGGREGATE(14,6,ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),
RANDBETWEEN(1,30-ROWS($1:1)+1 )),"")

Remarque : $A$1 : $A1 fait référence à A2 lorsque vous mettez la formule dans A2 et que vous la faites glisser vers le bas.
Supposons que vous ayez mis la formule dans G4, elle devrait être remplacée par $G$3:$G3.

Si vos numéros de début et de fin sont en B1 et C1, utilisez la formule ci-dessous

=IFERREUR(AGGREGATE(14,6,ROW(INDIRECT($B$1&":"&$C$1))*
NOT(COUNTIF($A$1:$A1,ROW(INDIRECT($B$1&":"& $C$1)))),
RANDBETWEEN($B$1,$C$1-ROWS($1:1)+1)),"")

Pour les versions antérieures à 2010, la construction de base suivante peut être utilisée (gestion des erreurs de
construction en fonction de la version. Par exemple, Excel 2007 prendra en charge IFERROR alors que 2003 prend en
charge ISERROR) -

=LARGE(INDEX(ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),,),
RANDBETWEEN(1,30-ROW(A1)+1))

117. Extraire le nom d'utilisateur d'un identifiant de messagerie

En supposant que A1 ait un identifiant de messagerie, dites A1 : =vaverma@[Link] et vous devez récupérer vaverma
qui est le nom d'utilisateur dans l'identifiant de messagerie. Utilisez la formule suivante –

Auteur - Vijay A Verma @ [Link] Page 44 sur 60

Bible des formules Excel

=SIERREUR(GAUCHE(A1,RECHERCHE("@",A1)-1),"")

118. Extraire le nom de domaine d'un identifiant de messagerie

Si vous souhaitez récupérer un nom de domaine qui dans l'exemple ci-dessus est [Link], utilisez la formule
suivante :

=REMPLACER(A1,1,RECHERCHE("@",A1)+1,"")

119. Emplacement du premier nombre dans une chaîne

Supposons que vous ayez A1 : = « abfg8ty#%473hj » et que vous souhaitiez trouver quelle est la position du premier
nombre. Dans cette chaîne, le premier nombre est 8 et sa position est 5. Vous pouvez utiliser la formule suivante –

=SIERREUR(AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,SEQUENCE(LEN(A1))),1),"")

=SIERREUR(AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,ROW(INDIRECT("1:"&LEN(A1) ) ) ) ,
1),"")

120. Emplacement du dernier numéro dans une chaîne

Dans l'exemple ci-dessus, le dernier nombre est 3 et sa position est 12. Vous pouvez utiliser la formule suivante pour
trouver ceci :

=SIERREUR(AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,SEQUENCE(LEN(A1))),1),"")

=SIERREUR(AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,ROW(INDIRECT("1:"&LEN(A1) ) ) ) ,
1),"")

121. Trouver la valeur de la première cellule non vide dans une plage

=INDEX(FILTRE(A1:A10,A1:A10<>0,""),1)

=SIERREUR(INDEX(A1:A10,MATCH(TRUE,NOT(ISBLANK(A1:A10)),0)),"")

122. Rechercher la première valeur numérique dans une plage

=INDEX(FILTRE(A1:A100,ISNUMBER(A1:A100),""),1)

=SIERREUR(INDEX(A1:A100,MATCH(1,--ISNUMBER(A1:A100),0)),"")

123. Rechercher la dernière valeur numérique dans une plage

=SIERREUR(1/RECHERCHE(2,1/A1:A100),"")

124. Rechercher la première valeur non numérique dans une plage

Auteur - Vijay A Verma @ [Link] Page 45 sur 60

Bible des formules Excel

=INDEX(FILTRE(A1:A100,ISTEXT(A1:A100),""),1)

=SIERREUR(INDEX(A1:A100,MATCH(1,--ISTEXT(A1:A100),0)),"")

125. Rechercher la dernière valeur non numérique dans une plage

=SIERREUR(RECHERCHE(REPT("z",255),A1:A100),"")

126. Rechercher la dernière valeur utilisée dans une plage

= SIERREUR(RECHERCHE(2,1/(A1:A100<>""),A1:A100),"")

127. J'ai des données sur de nombreuses années mais je veux la somme pour les 12
derniers mois seulement

Supposons que vos données soient alignées comme ceci et que vous souhaitiez avoir la somme uniquement pour les 12 derniers mois.

Cela devrait être pris en compte si de futures entrées sont effectuées. Par conséquent, si les données de juin sont saisies, le résumé doit

commencer à partir du mois de juin. –

Utilisez cette formule –

=SOMME(INDEX(TRI(B2:B20,A2:A20,-1),SÉQUENCE(12)))

=SIERREUR(SUM(OFFSET(INDIRECT("$B"&COUNTA($A:$A)),0,0,IF(COUNTA($A:$A)-
12>0,-12,-COUNTA($A :$A)+1),1)),"")

Auteur - Vijay A Verma @ [Link] Page 46 sur 60

Bible des formules Excel

Remarque – Si vous n'avez qu'une seule colonne, disons B, remplacez $A par $B. (Si seulement A, remplacez $B par $A)

128. Générer une liste unique à partir des entrées en double

Supposons que vous ayez des entrées dans A2:A100 et que vous souhaitiez générer une liste contenant uniquement des entrées
uniques dans la colonne C commençant par C2. Vous pouvez utiliser la formule suivante en C2 et faire glisser la formule vers le bas
–

Cas 1 – A2 : A100 ne contient aucun espace

=UNIQUE(A2:A100)

Manière plus ancienne -

=SIERREUR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($C$1:$C1,$A$2:$A$100),0,0), ) ) , "")

Cas 2 – A2 : A100 contient des blancs

=UNIQUE(FILTRE(A2:A100,A2:A100<>""))

Méthode plus ancienne - Dans ce cas, vous devrez utiliser la formule Array.

=SIERREUR(INDEX($A$2:$A$100, MATCH(0, IF($A$2:$A$100<>"",COUNTIF(C1:$C$1,

$A$2:$A$100)), 0 )),"")

OU

=SIERREUR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($C$1:$C1,$A$2:$A$100&""),0)),"")

129. Fonction financière - Calculer l'EMI

Vous souhaitez contracter un emprunt et vous souhaitez calculer EMI OU vous souhaitez construire un calculateur
EMI dans Excel. C'est un travail assez facile à faire -

Vous devrez utiliser la fonction PMT pour cela. Il a la structure suivante -

PMT(taux, nper, pv, [fv], [type])

taux: Votre taux d'intérêt

nper: Nombre de paiements. Votre nper et votre taux doivent être sur la même échelle. c'est-à-dire que si
vous envisagez de payer mensuellement, le taux indiqué dans votre formule ne devrait être que mensuel.
Généralement, le taux d'intérêt est spécifié chaque année, soit 10,5 % par an. Vous devez diviser ce montant
par 12 pour obtenir le taux mensuel. Par conséquent, si vous vouliez un prêt sur 3 ans, cela signifie que nper
serait 3x12 = 36 mois. Si c'est trimestriel, taux = 10,5%/4 = 2,625% et nper serait 3x4 = 12 Si c'est annuel, taux
= 10,5% et nper = 3

Auteur - Vijay A Verma @ [Link] Page 47 sur 60

Bible des formules Excel

PV: Montant de votre prêt. Vous devrez en mettre une valeur négative dans votre formule. Si vous ne
mettez pas de valeur négative, votre EMI serait négatif mais la réponse serait la même mais avec un
signe négatif.

+ ve / -ve PMT nécessite quelques explications, mais vous pouvez choisir de les ignorer. Cela dépend de votre
trésorerie. Si vous contractez un emprunt, donc encaissez, donc pv est +ve. Mais chaque mois, vous devrez
payer quelque chose, donc encaisser. Par conséquent, PMT est -ve. Si vous investissez, encaissez donc. Donc
pv vaut -ve. Mais chaque mois, vous recevrez quelque chose, donc encaisserez. Par conséquent, PMT est +ve.

Maintenant, ce qui signifie +ve ou -ve est simplement votre préférence. Je vous recommande de ne pas vous inquiéter à ce sujet.

fv: Votre valeur restante après avoir terminé votre versement. Généralement, il est de 0 car tout prêteur souhaite
récupérer son argent. (La valeur par défaut est 0)
taper: 0 - À la fin de la période, 1 - Au début de la période (la valeur par défaut est 0) Notez
également que fv et type sont facultatifs et peuvent ne pas être requis dans votre formule.

La formule utilisée dans l'image ci-dessous est =PMT(B1/12,B2,-B3,B4,B5)

Astuce bonus=Si vous utilisez la fonction ABS, il ne sera pas nécessaire de mettre une valeur négative de PV.
Par conséquent, la formule dans ce cas serait =ABS(PMT(B1/12,B2,B3,B4,B5))

Le calculateur EMI ci-dessous peut être téléchargé à partir d'ici[Link]

wpcontent/uploads/2014/12/[Link]

130. Fonction financière - Calculer la partie intérêt d'un IME

Maintenant, l'EMI pour un mois = Intérêts pour ce mois et Principal pour ce mois. IPMT est utilisé
pour calculer la partie intérêts de votre EMI.

Excel définit l'IPMT comme « renvoie le paiement des intérêts pour une période donnée pour un investissement sur
la base de paiements périodiques et constants et d'un taux d'intérêt constant ».

La syntaxe d'IPMT est IPMT (rate, per, nper, pv, [fv], [type]). '

taux: Votre taux d'intérêt

par: Période pour laquelle vous souhaitez calculer les intérêts

Auteur - Vijay A Verma @ [Link] Page 48 sur 60

Bible des formules Excel

nper: Nombre de paiements. Votre nper et votre taux doivent être sur la même échelle. c'est-à-dire que si vous
envisagez de payer mensuellement, le taux indiqué dans votre formule ne devrait être que mensuel. Généralement,
le taux d'intérêt est spécifié chaque année, soit 10,5 % par an. Vous devez diviser ce montant par 12 pour obtenir le
taux mensuel. Par conséquent, si vous vouliez un prêt sur 3 ans, cela signifie que nper serait 3x12 = 36 mois. Si c'est
trimestriel, taux = 10,5%/4 = 2,625% et nper serait 3x4 = 12
Si c'est annuel, taux = 10,5% et nper = 3

PV: Montant de votre prêt. Vous devrez en mettre une valeur négative dans votre formule. Si vous ne
mettez pas de valeur négative, votre EMI serait négatif mais la réponse serait la même mais avec un
signe négatif.

+ ve / -ve IPMT nécessite quelques explications, même si vous pouvez choisir de les ignorer. Cela dépend de
votre trésorerie. Si vous contractez un emprunt, donc encaissez, donc pv est +ve. Mais chaque mois, vous
devrez payer quelque chose, donc encaisser. Par conséquent, IPMT est -ve. Si vous investissez, encaissez
donc. Donc pv vaut -ve. Mais chaque mois, vous recevrez quelque chose, donc encaisserez. Par conséquent,
IPMT est +ve.

Maintenant, ce qui signifie +ve ou -ve est simplement votre préférence. Je vous recommande de ne pas vous inquiéter à ce sujet.

fv: Votre valeur restante après avoir terminé votre versement. Généralement, il est de 0 car tout prêteur souhaite
récupérer son argent. (La valeur par défaut est 0)

taper: 0 - À la fin de la période, 1 - Au début de la période (la valeur par défaut est 0)

Notez également que fv et type sont facultatifs et peuvent ne pas être obligatoires dans votre formule.

La formule utilisée dans l'image ci-dessous est =IPMT(B1/12,B2,B3,-B4,B5,B6)

De plus, étant donné que les intérêts varient chaque mois, il est donc logique de les calculer pour chaque mois. Les
colonnes H et I portent des intérêts pour chaque mois.

Astuce bonus=Si vous utilisez la fonction ABS, il ne sera pas nécessaire de mettre une valeur négative de PV.
Par conséquent, la formule dans ce cas serait =ABS(IPMT(B1/12,B2,B3,B4,B5,B6))

Le calculateur EMI ci-dessous peut être téléchargé à partir d'ici[Link]

wpcontent/uploads/2014/12/[Link]

Auteur - Vijay A Verma @ [Link] Page 49 sur 60

Bible des formules Excel

131. Fonction financière - Calculer la partie principale d'un

IME
Maintenant, l'EMI pour un mois = Intérêts pour ce mois et Principal pour ce mois. IPMT est utilisé
pour calculer la partie intérêts de votre EMI. Pour calculer la partie principale d'un EMI, vous
devrez utiliser PPMT.

Excel définit PPMT comme « renvoie le paiement du principal pour une période donnée pour un
investissement sur la base de paiements périodiques et constants et d'un taux d'intérêt constant ».

La syntaxe de PPMT est PPMT(rate, per, nper, pv, [fv], [type])

taux: Votre taux d'intérêt

par: Période pour laquelle vous souhaitez calculer le capital

nper: Nombre de paiements. Votre nper et votre taux doivent être sur la même échelle. c'est-à-dire que si vous
envisagez de payer mensuellement, le taux indiqué dans votre formule ne devrait être que mensuel. Généralement,
le taux d'intérêt est spécifié chaque année, soit 10,5 % par an. Vous devez diviser ce montant par 12 pour obtenir le
taux mensuel. Par conséquent, si vous vouliez un prêt sur 3 ans, cela signifie que nper serait 3x12 = 36 mois. Si c'est
trimestriel, taux = 10,5%/4 = 2,625% et nper serait 3x4 = 12
Si c'est annuel, taux = 10,5% et nper = 3

PV: Montant de votre prêt. Vous devrez en mettre une valeur négative dans votre formule. Si vous ne
mettez pas de valeur négative, votre EMI serait négatif mais la réponse serait la même mais avec un
signe négatif.

+ ve / -ve PPMT nécessite quelques explications, même si vous pouvez choisir de les ignorer. Cela dépend de
votre trésorerie. Si vous contractez un emprunt, donc encaissez, donc pv est +ve. Mais chaque mois, vous
devrez payer quelque chose, donc encaisser. Par conséquent, PPMT est -ve. Si vous investissez, encaissez
donc. Donc pv vaut -ve. Mais chaque mois, vous recevrez quelque chose, donc encaisserez. Par conséquent,
PPMT est +ve.

Auteur - Vijay A Verma @ [Link] Page 50 sur 60

Bible des formules Excel

Maintenant, ce qui signifie +ve ou -ve est simplement votre préférence. Je vous recommande de ne pas vous inquiéter à ce sujet.

fv: Votre valeur restante après avoir terminé votre versement. Généralement, il est de 0 car tout prêteur souhaite
récupérer son argent. (La valeur par défaut est 0)

taper: 0 - À la fin de la période, 1 - Au début de la période (la valeur par défaut est 0)

Notez également que fv et type sont facultatifs et peuvent ne pas être obligatoires dans votre formule.

La formule utilisée dans l'image ci-dessous est =PPMT(B1/12,B2,B3,-B4,B5,B6)

De plus, étant donné que le capital varie chaque mois, il est donc logique de le calculer pour chaque mois. Les
colonnes H et I portent le principal pour chaque mois.

Astuce bonus=Si vous utilisez la fonction ABS, il ne sera pas nécessaire de mettre une valeur négative de PV.
Par conséquent, la formule dans ce cas serait =ABS(PPMT(B1/12,B2,B3,B4,B5,B6))

Le calculateur EMI ci-dessous peut être téléchargé à partir d'ici[Link]

wpcontent/uploads/2014/12/[Link]

132. Fonction financière - Calculer le nombre d'IME pour

rembourser un prêt
Vous avez contracté un prêt et vous connaissez vos capacités EMI. Vous voulez donc savoir combien de mois il
faudra pour rembourser complètement un prêt.

C'est un travail assez simple à faire dans Excel. Vous devrez utiliser la fonction NPER pour cela.

Excel définit NPER comme « renvoie le nombre de périodes pour un investissement basé sur des paiements
périodiques et constants et un taux d'intérêt constant ».

Auteur - Vijay A Verma @ [Link] Page 51 sur 60

Bible des formules Excel

La syntaxe de NPER est NPER(rate,pmt,pv,[fv],[type]).

taux: Votre taux d'intérêt

pmt: EMI (Paiement par période). Vous devrez en mettre la valeur -ve dans votre formule. Votre pmt et
votre tarif doivent être sur la même échelle. c'est-à-dire que si vous envisagez de payer mensuellement,
le taux indiqué dans votre formule ne devrait être que mensuel. Généralement, le taux d'intérêt est
spécifié chaque année, soit 10,5 % par an. Vous devez diviser ce montant par 12 pour obtenir le taux
mensuel. Par conséquent, si vous vouliez un prêt sur 3 ans, cela signifie que nper serait 3x12 = 36 mois.
Si c'est trimestriel, taux = 10,5%/4 = 2,625% et nper serait 3x4 = 12
Si c'est annuel, taux = 10,5% et nper = 3

PV: Montant de votre prêt. Vous devrez mettre la valeur +ve de ceci dans votre formule.
Remarque : PMT ou PV doivent être -ve. Les deux ne peuvent pas être +ve et -ve en même temps.

+ ve / -ve nécessite quelques explications et cela ne peut être ignoré. Cela dépend de votre
trésorerie. Si vous contractez un emprunt, donc encaissez, donc pv est +ve. Mais chaque mois, vous
devrez payer quelque chose, donc encaisser. Par conséquent, PMT est -ve. Si vous investissez, encaissez
donc. Donc pv vaut -ve. Mais chaque mois, vous recevrez quelque chose, donc encaisserez. Par
conséquent, PMT est +ve.

fv: Votre valeur restante après avoir terminé votre versement. Généralement, il est de 0 car tout prêteur souhaite
récupérer son argent. (La valeur par défaut est 0)

taper: 0 - À la fin de la période, 1 - Au début de la période (la valeur par défaut est 0)

Notez également que fv et type sont facultatifs et peuvent ne pas être obligatoires dans votre formule.

La formule utilisée dans l'image ci-dessous est =NPER(B1/12,-B2,B3)

Le calculateur EMI ci-dessous peut être téléchargé à partir d'ici[Link]

wpcontent/uploads/2014/12/[Link]

133. Fonction financière - Calculer le taux d'intérêt

Vous souhaitez contracter un emprunt. Vous savez quel montant de prêt contracter (pmt), vous savez
combien de mois vous souhaitez rembourser (nper) et vous voulez connaître le taux d’intérêt effectif.
Excel facilite la tâche. La fonction RATE est la réponse à cela.

Auteur - Vijay A Verma @ [Link] Page 52 sur 60

Bible des formules Excel

Excel définit RATE comme « Renvoie le taux d'intérêt par période d'une rente. RATE est calculé par
itération et peut avoir zéro ou plusieurs solutions. Si les résultats successifs de RATE ne convergent
pas vers 0,0000001 après 20 itérations, RATE renvoie le #NUM ! valeur d'erreur." La syntaxe de
RATE est RATE (nper, pmt, pv, [fv], [type], [guess]).

nper: Délais de paiement. Généralement en mois.

pmt: EMI (Paiement par période). Vous devrez en mettre la valeur -ve dans votre formule. Votre pmt et votre
tarif doivent être sur la même échelle. c'est-à-dire que si vous envisagez de payer mensuellement, le PMT
dans votre formule ne devrait être que mensuel.

PV: Montant de votre prêt. Vous devrez mettre la valeur +ve de ceci dans votre formule.
Remarque : PMT ou PV doivent être -ve. Les deux ne peuvent pas être +ve et -ve en même temps.

+ ve / -ve nécessite quelques explications et cela ne peut être ignoré. Cela dépend de votre
trésorerie. Si vous contractez un emprunt, donc encaissez, donc pv est +ve. Mais chaque mois, vous
devrez payer quelque chose, donc encaisser. Par conséquent, PMT est -ve. Si vous investissez, encaissez
donc. Donc pv vaut -ve. Mais chaque mois, vous recevrez quelque chose, donc encaisserez. Par
conséquent, PMT est +ve.

fv: Votre valeur restante après avoir terminé votre versement. Généralement, il est de 0 car tout prêteur souhaite
récupérer son argent. (La valeur par défaut est 0)

taper: 0 - À la fin de la période, 1 - Au début de la période (la valeur par défaut est 0)

deviner: Si vous omettez de deviner, on suppose qu'il est de 10 pour cent. Si RATE ne converge pas, essayez différentes
valeurs pour deviner. RATE converge généralement si la valeur estimée est comprise entre 0 et 1. Encore une fois, notez
que si le PMT est mensuel, alors la valeur Guess devrait également être mensuelle. Par conséquent, si vous donnez un taux
d’intérêt annuel de 12 %, la estimation devrait être 12 %/12 = 1 %.

Notez également que fv, type et deviner sont facultatifs et peuvent ne pas être obligatoires dans votre formule.

La formule utilisée dans l'image ci-dessous est =RATE(B1,-B2,B3,B4,B5,B6/12)

Le calculateur EMI ci-dessous peut être téléchargé à partir d'ici[Link]

wpcontent/uploads/2014/12/[Link]

Auteur - Vijay A Verma @ [Link] Page 53 sur 60

Bible des formules Excel

134. Fonction financière - Calculer les intérêts composés

Dans le cadre de nos cours de mathématiques dans notre enfance, nous avions appris l'intérêt
composé. La fameuse formule dont on retient est
Solde composé = Principal x (1+taux) N̂

C'est un travail assez simple à faire dans Excel. La formule à utiliser est FV.

L'aide d'Excel décrit FV comme « renvoie la valeur future d'un investissement sur la base de paiements
périodiques et constants et d'un taux d'intérêt constant ».

La syntaxe de FV est FV(rate,nper,pmt,[pv],[type])

Vous n’avez besoin que de 3 informations pour le solde composé.

taux: Taux d'intérêt sur lequel la composition doit être effectuée

nper: Nombre total de périodes pour lesquelles la composition doit être effectuée. Désormais, le taux et le nper
devraient être sur la même échelle. Si le taux d’intérêt est mensuel, alors nper devrait être en mois. Si le taux
d’intérêt est trimestriel, alors nper devrait être trimestriel. Si le taux d’intérêt est annuel, alors le nper devrait être
exprimé en années.

PV: C'est le principal initial et il doit être spécifié en -ve. (Remarque, j'ai déjà discuté de l'importance
de +ve et -ve dans de nombreux conseils précédents sur les fonctions financières.)

La formule utilisée dans l'image ci-dessous pour le mois

=FV(B1/12,B3*12,0,-B2)

La formule utilisée dans l'image ci-dessous pour Trimestriel

=FV(F1/4,F3*4,0,-F2)

La formule utilisée dans l'image ci-dessous pour annuel

=FV(J1,J3,0,-J2)

Le Composé Équilibre Calculatrice peut être téléchargé depuis

[Link]
[Link]

Auteur - Vijay A Verma @ [Link] Page 54 sur 60

Bible des formules Excel

135. Fonction financière - Calculer l'intérêt effectif

Vous demandez un prêt et un taux d’intérêt vous a été proposé. Le taux d'intérêt indiqué est appelé
« taux d'intérêt nominal ». Ils indiqueront le taux d’intérêt nominal en termes annuels. Par conséquent,
s’ils citent un intérêt de 12 % pour un prêt, il s’agit d’un chiffre annuel. Désormais, vous payez
généralement les EMI chaque mois. Ils disent simplement que vous devez payer un intérêt mensuel de
1 % qui a été dérivé du taux d'intérêt annuel / 12, soit 12 %/12 = 1 % dans ce cas.

Mais en réalité, un taux d’intérêt de 1 % (taux d’intérêt nominal mensuel) est composé chaque mois, ce qui fait
que votre taux d’intérêt effectif annuel devient plus élevé. Mais les institutions financières prêteuses ne
proposent pas ce taux plus élevé, car cela ferait paraître le coût de votre prêt plus élevé.

CalculerTaux d'intérêt effectif, Excel a fourni une fonction appelée EFFECT.

Excel décrit EFFET - Renvoie le taux d'intérêt annuel effectif, compte tenu du taux d'intérêt
annuel nominal et du nombre de périodes de composition par an.

La syntaxe de EFFECT est EFFECT(nominal_rate, npery).

Taux nominal-Taux d'intérêt annuel

npery-Périodes composées dans une année. Pour les mensualités, il est de 12. Pour les
mensualités, il est de 4.

Dans l'image ci-dessous, le taux d'intérêt effectif est de 12,68 % pour un paiement mensuel. Cela peut représenter
une petite différence pendant un an ou deux, mais si vous contractez une hypothèque sur un logement, disons pour
20 ans, cela fait une sacrée différence.

La formule utilisée est =EFFET(B1,B2)

Désormais, si vous effectuez un investissement et effectuez des paiements mensuels, vous obtiendrez un
rendement annuel de 12,68 % contre 12 % si vous effectuez un paiement annuel.

Le efficace intérêt taux calculatrice peut être téléchargé depuis

[Link]
[Link]

Auteur - Vijay A Verma @ [Link] Page 55 sur 60

Bible des formules Excel

136. Fonction financière – Calculer le TCAC et le TCAC

On nous pose souvent la question du calcul du TCAC (taux de croissance annuel composé) et du
TCAM (taux de croissance annuel moyen). Pour les profanes qui ne savent pas de quoi il s’agit,
voici de bonnes ressources pour présenter aux gens le CAGR et l’AAGR.

TCAC –[Link]
TCAM –[Link]

La formule pour calculer le TCAC est très simple. La fonction RATE peut être utilisée pour calculer le CAGR.
Nous avons besoin de connaître uniquement PV, FV et NPER. Supposons que vous ayez investi 10 000 $
(donc PV ) et que cet investissement soit devenu 16 448 $ (donc FV) au bout de 5 ans. Ainsi, pour calculer le
TCAC, nous utiliserons la formule suivante –

=TAUX(5,,-10000,16448)

=TAUX(5,,10000,-16448)

Par conséquent, dans les formules ci-dessus, vous devez mettre soit PV comme négatif, soit FV comme négatif. Les
deux ne peuvent pas être positifs ou négatifs en même temps.

Si vous n'avez pas de FV mais une série de paiements fixes (disons un investissement de 3 000 chaque année), vous
pouvez utiliser –

=TAUX(5,-3000,10000)

=TAUX(5,1000,-10000)

Maintenant, revenons à AAGR. Il n’existe pas de formule unique pour le TCAM. Si votre ensemble de données est présenté comme
ci-dessous, vous pouvez utiliser la formule suivante pour calculer l'AAGR :

=SOMMEPRODUIT((B3:B11-B2:B10)/(B2:B10))/(LIGNES(B2:B10))

Auteur - Vijay A Verma @ [Link] Page 56 sur 60

Bible des formules Excel

137. Facturation des dalles - Calculer l'impôt sur le revenu et les factures d'électricité
(services publics) en fonction des dalles

Vous rencontrerez des factures de dalle dans deux documents très courants : l’un est l’électricité/service public
et l’autre est l’impôt sur le revenu. Si vous voyez vos factures d'électricité, vous remarquerez les types d'entrées
suivants (les valeurs sont uniquement à des fins d'illustration, veuillez ne leur attacher aucune signification)

0 à 50 unités – 1,5 $ par unité 51 à 100

unités – 2,0 $ par unité 101 à 500
unités – 3,5 $ par unité 501 – 2 000
unités – 6 $ par unité À partir de 2000
– 9 $ par unité

Cela signifie que vos 50 premières unités seront facturées à 1,5 $ par unité, les 50 suivantes seront facturées
à 2,0 $ par unité et les 400 unités suivantes seront facturées à 3,5 $ par unité, les 1 500 unités suivantes
seront facturées à 6 $ par unité et toute unité après 2000. sera facturé à 9$ l'unité.

Par conséquent, si votre facture est de 37 unités, vos frais seront = 37*1,5 = 55,5 $. Par conséquent, si votre
facture est de 87 unités, vos frais seront = 50*1,5+(87-50)*2 = 149 $. Par conséquent, si votre la facture est
de 415 unités, vos frais seront = 50*1,5+50*2+(415-100)*3,5 = 1277,5 $

Ainsi, si votre facture est de 780 unités, vos frais seront = 50*1,5+50*2+400*3,5+(780-500)*6 =
3255$
Ainsi, si votre facture est de 2670 unités, vos frais seront =
50*1,5+50*2+400*3,5+1500*6+(2670-2000)*9 = 16605$

Même type de distribution, vous le remarquerez également dans l’impôt sur le revenu. Considérez les dalles ci-dessous.

0 – 10 000 $ – néant 10 001 $ –

50 000 $ – 10 % 50 001 $ – 100
000 $ – 20 % 100 001 $ – 500
000 $ – 30 % 500 000 $ et plus –
35 %

Auteur - Vijay A Verma @ [Link] Page 57 sur 60

Bible des formules Excel

Ainsi, si votre revenu est de 8 000 $, votre impôt sur le revenu sera de 0.
Par conséquent, si votre revenu est de 40 000 $, votre impôt sur le revenu sera de =(40 000-10 000)*10 % = 3 000
$. Ainsi, si votre revenu est de 90 000 $, votre impôt sur le revenu sera de =40 000*10 %+(90 000- 50 000)*20. % =
12 000 $
Ainsi, si votre revenu est de 210 000 $, votre impôt sur le revenu sera de =40 000*10 %+50
000*20 %+(210 000-100001)*30 % = 47 000 $. Ainsi, si votre revenu est de 850 000 $, votre
impôt sur le revenu sera de =40 000*. 10 %+50 000*20 %+400 000*30 %+(850 000-500 000)*35
% = 256 500 $

Téléchargez le classeur correspondant à partir de[Link]

wpcontent/uploads/2015/12/[Link]

Maintenant, comment calculer cela dans Excel via une seule formule. Ici, SUMPRODUCT vient à
votre secours. La seule condition préalable est que vous devez configurer vos données de manière
appropriée à cette fin, comme indiqué ci-dessous.

Ensuite, la formule de facturation des dalles suit simplement

=SOMMEPRODUIT((F2>A2:A6)*(F2-A2:A6)*(C2:C6-SIERREUR(–C1:C5,0)))

Ci-dessous l'exemple pour l'impôt sur le revenu

Ensuite, la formule de l’impôt sur le revenu suit simplement

=SOMMEPRODUIT((O2>J2:J6)*(O2-J2:J6)*(L2:L6-SIERREUR(–L1:L5,0)))

138. LTRIM et RTRIM via des formules Excel

Nous avons donc la fonction TRIM dans Excel. Presque tous les langages de programmation fournissent
également les fonctions LTRIM et RTRIM, mais Excel ne fournit pas LTRIM et RTRIM. La même chose est fournie
dans VBA, mais la plupart des utilisateurs d'Excel n'utilisent pas VBA. Ce sont des gens simples qui souhaitent
accomplir leur travail quotidien uniquement grâce aux fonctions Excel.

Auteur - Vijay A Verma @ [Link] Page 58 sur 60

Bible des formules Excel

Avant de me plonger dans LTRIM et RTRIM, récapitulons TRIM.

TRIM, en gros, supprime tous les espaces de votre chaîne et s'il y a plus d'un espace entre
vos mots, il les convertira en un seul espace. Ainsi, il supprime tous les espaces de début,
tous les espaces de fin et tous les espaces entre les mots sauf un. S’il n’y a qu’un seul espace
entre les mots, cet espace restera intact.

Vous trouverez ci-dessous les exemples de fonctionnement avec la fonction TRIM –

"Vijay A Verma" = "Vijay A Verma" (deux espaces entre Vijay et A ont été réduits à un.
Trois espaces entre A et Verma ont été réduits à un)

"Vijay Verma" = "Vijay Verma"(Deux espaces avant Vijay ont été réduits à aucun espace
et six espaces entre Vijay et Verma ont été réduits à un)

"Vijay Verma " = "Vijay Verma"(Trois espaces après Vijay ont été réduits à zéro
l'espace et six espaces entre Vijay et Verma ont été réduits à un)

"Vijay Verma " = "Vijay Verma" (Quatre espaces avant Vijay ont été réduits à zéro
espace, 3 espaces entre Vijay et Verma ont été réduits à un espace et huit espaces
après Verma ont été réduits à aucun espace)

Donc, je suis sûr que vous savez maintenant ce que fait TRIM.

Maintenant, j'ai parlé du fait que la plupart des langages de programmation fournissent notamment les
fonctions LTRIM et RTRIM. Alors, que font LTRIM et RTRIM –

1. LTRIM supprime tous les espaces de début et laisse tous les autres espaces intacts. Par conséquent,
si j'utilise LTRIM en suivant -

" Little Mary " = " Little Mary " (Les quatre espaces de début ont été coupés afin qu'il n'y ait pas
d'espace avant le premier mot. Il a laissé les espaces entre deux mots et les espaces après le dernier
mot intacts)

2. RTRIM supprime tous les espaces de fin et laisse tous les autres espaces intacts. Par
conséquent, si j'utilise LTRIM en suivant -

" Little Mary " = " Little Mary " (Les trois espaces de fin ont été coupés afin qu'il n'y ait pas
d'espace après le dernier mot. Il a laissé les espaces entre deux mots et les espaces avant le
premier mot intacts)

Maintenant, revenons à la création de LTRIM et RTRIM via les fonctions Excel.

La formule du LTRIM

=REMPLACER(A1,1,TROUVER(GAUCHE(TRIM(A1),1),A1)-1,"")

Ainsi, cette formule Excel particulière agirait comme LTRIM, c'est-à-dire qu'elle supprimerait tous les
espaces de début et laisserait tous les autres espaces intacts.

La formule du RTRIM

Auteur - Vijay A Verma @ [Link] Page 59 sur 60

Bible des formules Excel

=SIERREUR(REPLACE(A1,MAX(INDEX((MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)

<>" ")*ROW(A1:INDEX(A:A,LEN(A1))),,))+1,LEN(A1),""),"")

La fonction ci-dessus agirait comme RTRIM, c'est-à-dire qu'elle supprimerait tous les espaces de fin et
laisserait tous les autres espaces intacts.

- - - - Fin du document ----

Auteur - Vijay A Verma @ [Link] Page 60 sur 60