Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Bible des formules EXCEL Excel 2013 / 2016 Table des matières 1. SOMME des chif

Bible des formules EXCEL Excel 2013 / 2016 Table des matières 1. SOMME des chiffres lorsque la cellule contient tous les nombres 1 2. SOMME des chiffres lorsque la cellule contient des nombres et des nombres non 1 3. Une liste est unique ou non (si elle contient des doublons) 1 4. N° de chef d’accusation de valeurs uniques 1 5. N° de chef d’accusation de valeurs uniques conditionnellement 1 6. Ajouter un mois à une date donnée ou le soustraire d’une date donnée 2 7. Ajouter une année ou soustraire une année d’une date donnée 2 8. Convertir un nombre en nom de mois 3 9. Conversion de la date en trimestre civil 3 10. Date de conversion en trimestre de l’exercice financier indien 3 11. Calculez l’âge à partir de l’anniversaire donné 4 12. Conversion du format du nombre à la date 4 13. Conversion du format du nombre en heure 4 14. Compter les cellules commençant (ou se finissant) par une chaîne particulière 4 15. N° de chef d’accusation Nombre de cellules n’ayant des nombres que 5 16. Chef d’accusation No. Nombre de cellules ne contenant que des caractères 5 17. Nombre de caractères dans une chaîne sans tenir compte des blancs 5 18. Nombre de fois qu’un caractère apparaît dans une chaîne 5 19. Compter les non-nombres dans une chaîne 5 20. Compter les nombres dans une chaîne 6 21. Ne compter que les alphabets d’une chaîne 6 22. Valeur la plus fréquente dans une plage 6 23. COUNTIF sur la liste filtrée 6 24. SUMIF sur la liste filtrée 7 25. Extraire le prénom du nom complet 7 26. Extraire le nom de famille du nom complet 7 27. Extraire l’initiale du deuxième prénom 7 28. Extraire le deuxième prénom du nom complet 7 29. Supprimer le deuxième prénom dans le nom complet 7 30. Extraire la partie entière et décimale d’un nombre 8 31. Premier jour du mois pour une date donnée 8 32. Combien de lundis ou tout autre jour de la semaine entre 2 dates 8 33. Nombre maximal de fois qu’une entrée particulière apparaît consécutivement 9 34. Trouvez la prochaine semaine du jour 9 35. Trouvez la semaine précédente du jour 10 36. Obtenir le nom du fichier via la formule 10 37. Obtenir le nom du classeur via la formule 11 38. Obtenir le nom de la feuille via la Formule 11 39. Obtenir le répertoire du classeur à partir de la formule 11 40. Dernier jour du mois pour une date donnée 11 41. Effectuer une recherche VLOOKUP multicolonne 12 42. RECHERCHEV de droite à gauche 13 43. Observation de contenu sensible à la casse 13 44. Rang au sein des groupes 14 45. Supprimer les alphabets d’une chaîne 14 46. Supprimer les nombres de la chaîne 15 47. Représentation romaine des nombres 15 48. Additionner les valeurs n inférieures dans une plage 15 49. Additionner chaque nième ligne 16 50. Nous avons AVERAGEIF. Qu’en est-il de MEDIANIF et MODEIF ?16 51. Nombre de jours dans un mois 17 52. Comment savoir si une année est une année bissextile 17 53. Dernier jour ouvrable du mois si une date est indiquée 17 54. Premier jour ouvrable du mois si une date est indiquée 18 55. Date du nième jour de l’année 18 56. Calculer la moyenne géométrique en ignorant les valeurs 0 et négatives 19 57. Fonction financière - Calculer l’IME 19 58. Fonction financière - Calculer la partie intérêts d’un IME 20 59. Fonction financière - Calculer la partie principale d’un IME 22 60. Fonction financière - Calculer le nombre d’EME pour rembourser un prêt 23 61. Fonction financière - Calculer le taux d’intérêt 24 62. Fonction financière – Calculer les intérêts composés 25 63. Fonction financière – Calculer les intérêts effectifs 26 64. Abréger les prénoms 27 65. Obtenir le nom de colonne pour un numéro de colonne 28 66. Obtenir la plage de colonnes pour un numéro de colonne 29 67. Trouvez le nième plus grand nombre lorsqu’il y a des doublons 29 68. Extraire la date et l’heure de l’horodatage 30 69. Convertir un nombre en années et mois 30 70. COUNTIF pour l’intervalle non contigu 31 71. 72. Numérologie Somme des chiffres aka Somme des chiffres jusqu’à ce que le résultat soit un seul chiffre 32 73. Générez des nombres séquentiels et répétez-les 32 74. Répéter un nombre et incrémenter et répéter 32 75. Générer des nombres aléatoires non répétitifs à l’aide de la formule 33 76. Formule de calcul de l’exercice financier (p. ex. 2015-2016 ou EF16) 34 Compter le nombre de mots dans une cellule / plage 31 77. Premier jour ouvrable de l’année 34 78. Dernier jour ouvrable de l’année 34 79. Convertir de Excel Date (date grégorienne) à Julian Date 35 80. Convertir des dates juliennes en dates Excel (grégoriennes) 35 81. Extraire le nom d’utilisateur d’un identifiant de courrier électronique 36 82. Extraire le nom de domaine d’un e-mail ID 36 83. Emplacement du premier numéro dans une chaîne 36 84. Emplacement du dernier numéro dans une chaîne 36 85. Recherchez la valeur de la première cellule non vide dans une plage 36 86. Recherchez la première valeur numérique dans une plage 36 87. Recherchez la dernière valeur numérique dans une plage 36 88. Trouver la première valeur non numérique dans une plage 37 89. Rechercher la dernière valeur non numérique dans une plage 37 90. Trouver la dernière valeur utilisée dans une plage 37 91. MAXIF 37 92. MINIF 37 93. Générer une liste unique à partir d’entrées en double 38 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 =SOMMEPRODUIT (--MID(A1,LIGNE(INDIRECT(« 1:"&LEN(A1))),1)) 2. SOMME des chiffres lorsque la cellule contient des nombres et des nombres non numériques Si votre cellule contient des nombres autres que des nombres comme A1: = 76$5a4b045%d, alors la formule suivante peut être utilisée pour trouver la somme des chiffres =SOMMEPRODUIT((LEN(A1)-LEN(SUBSTITUER(A1,LIGNE(1:9)," »)))*LIGNE(1:9 )) La formule ci-dessus peut être utilisée même si elle contient également tous les nombres. 3. Une liste est unique ou non (si elle contient des doublons) En supposant que votre liste soit comprise entre A1 et A1000. Utilisez la formule suivante pour savoir si la liste est unique. =MAX (FRÉQUENCE (A1 : A1000, A1 : A1000)) =MAX (INDEX (COUNTIF (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. N° de chef d’accusation de valeurs uniques Utilisez la formule suivante pour compter non. de valeurs uniques - =SOMMEPRODUIT ((A1 : A100<>" ») /COUNTIF (A1 : A100, A1:A100&" »)) 5. N° de chef d’accusation de valeurs uniques conditionnellement Si vous avez des données comme ci-dessous et que vous souhaitez trouver le nombre unique pour la région = « A », vous pouvez utiliser la formule de tableau ci-dessous - =SOMME (SI (FRÉQUENCE (IF(A2: A20<>" »,SI(A2:A20="A »,MATCH(B2:B20,B2:B20,0))),LIGNE(A 2:A20)- LIGNE(A2)+1),1)) Si vous avez plus de conditions, la même chose peut être construite après A2:A20 = « A ». « , alors vous pouvez utiliser ci-dessous la formule Array =SOMME(SI(FRÉQUENCE(IF(A2:A20<>" »,SI(A2:A20="A »,MATCH(B2:B20,B2:B2 0,0))),LIGNE(A 2:A20)-LIGNE(A2)+1),1)) Si vous avez plus de conditions, la même chose peut être construite après A2:A20 = « A ». Remarque - La formule matricielle n’est pas entrée en appuyant sur ENTRÉE après avoir entré votre formule, mais en appuyant sur CTRL+MAJ+ENTRÉE. Si vous copiez et collez cette formule, prenez F2 après le collage et CTRL + MAJ + ENTRÉE. Cela mettra { } crochets autour de la formule que vous pouvez voir dans la barre de formule. Si vous modifiez à nouveau, vous devrez refaire CTRL + MAJ + ENTRÉE. Ne mettez pas { } manuellement. 6. Ajouter un mois à une date donnée ou le soustraire Très souvent, vous aurez des problèmes d’affaires où vous devrez ajouter ou soustraire un mois à partir d’une date donnée. Un scénario est le calcul de la date EMI. Disons que vous avez une date du 22/10/14 (MM/JJ/AA) dans A1 et que vous souhaitez ajouter le nombre de mois contenu dans la cellule B1. La formule dans ce cas serait la suivante : =DATE (A1, B1) [Formule secondaire =DATE(ANNÉE(A1), MOIS(A1) +B1, JOUR(A1)) ] Maintenant, vous voulez soustraire le mois qui est contenu dans la cellule B1. =DATE(A1,-B1) [Formule secondaire =DATE(ANNÉE(A1), MOIS(A1)-B1,JOUR(A1)) ] 7. Ajouter ou soustraire une année à une date donnée Dans de nombreux problèmes commerciaux, vous pouvez rencontrer des situations où vous devrez ajouter ou soustraire des années à partir d’une date donnée. Disons que A1 contient la date et B1 contient le nombre d’années. Si vous voulez 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 voulez soustraire des années d’une date donnée, les formules seraient... =EDATE(A1,-12*B1) =DATE(ANNÉE(A1)-B1,MOIS(A1),JOUR(A1)) 8. Convertir un nombre en nom de mois Utilisez la formule ci-dessous pour générer un mois uploads/Finance/ bible-des-formules-excel.pdf