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

Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit 

Bienvenue dans ce cours Excel qui vous montre les 100 formules et fonctions Excel indispensables.

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 le Compounded
Intérêt. La fameuse formule dont on se souvient est
Solde composé = Principal x (1+taux)^N
C’est un travail assez facile à 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 données périodiques,
des paiements constants et 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 éléments d’information pour le solde composé.
taux : taux d’intérêt sur lequel la capitalisation doit être effectuée

nper : nombre total de périodes pour lesquelles la capitalisation doit être effectuée. Maintenant taux et nper
doit être à la même échelle. Si le taux d’intérêt est mensuel, alors nper doit être exprimé en mois. Si
le taux d’intérêt est trimestriel, alors nper doit être trimestriel. Si le taux d’intérêt est annuel, alors nper
devrait être en années.
pv : il s’agit du principal initial et il doit être spécifié dans -ve. (Attention, j’ai déjà
discuté de la signification 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 Mensuel
=VF(B1/12,B312,0,-B2) La formule utilisée dans l’image ci-dessous pour trimestriel =FV(F1/4,F34,0,-F2)
La formule utilisée dans l’image ci-dessous pour Annuel
=FV(J1,J3,0,-J2)

Le calculateur de solde composé peut être téléchargé à partir de

Fonction financière – Calculer l’intérêt effectif

Vous demandez un prêt et un taux d’intérêt a été proposé. Le taux d’intérêt qui est
coté est appelé “Taux d’intérêt nominal”. Ils citeront le taux d’intérêt nominal chaque année
termes. Par conséquent, s’ils citent 12% d’intérêt pour un prêt, il s’agit d’un chiffre annuel. Maintenant, vous généralement
payer les EMI chaque mois. Ils disent simplement que vous devez payer 1 % d’intérêts mensuels, ce qui
a été dérivé du taux d’intérêt annuel / 12 dont 12%/12=1% dans ce cas.
Mais en fait, le taux d’intérêt de 1 % (taux d’intérêt mensuel nominal) est composé chaque
mois, votre taux d’intérêt effectif annuel devient donc plus élevé. Mais les prêts financiers
Les institutions ne citent pas ce taux plus élevé car cela fera paraître le coût de votre prêt plus élevé.
Pour calculer le taux 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 nominal
taux d’intérêt annuel et le nombre de périodes de capitalisation par an.
La syntaxe de EFFECT est EFFECT(nominal_rate, npery).
Taux nominal – Taux d’intérêt annuel
npery – Périodes de capitalisation dans une année. Pour les paiements mensuels, il est de 12. Pour les paiements trimestriels
paiements, c’est 4.
Dans l’image ci-dessous, le taux d’intérêt effectif est de 12,68 % pour un paiement mensuel. Ceci peut
être une petite différence pendant un an ou deux, mais si vous prenez une hypothèque sur le logement, c’est-à-dire pour
20 ans, ça fait une sacrée différence.
La formule utilisée est =EFFET(B1,B2)
Maintenant, si vous faites un investissement et effectuez des paiements mensuels, vous obtiendrez
rendement annuel de 12,68 % contre 12 % si vous effectuez un paiement annuel.

Le calculateur de taux d’intérêt effectif peut être téléchargé sur

Prénoms abrégés

Si vous avez des noms donnés comme –
Smith Johnson
Liz Lotte
Christy diadème Lewisk
John
Et vous devez produire des abréviations ou des acronymes pour eux comme ci-dessous dans toutes les capitales
Smith Johnson – SJ
Liz lotte – LT
Christy diadème Lewisk – CTL
Jean-J
Ensuite, vous pouvez utiliser la formule suivante pour la même chose 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. LEFT(A1,1) – Extrait la première lettre du prénom
2. MID(A1,CHERCHE(” “,A1&” “)+1,1) –
   TROUVER(” “,A1&” “) – Rechercher trouve le premier espace dans le nom donné pour localiser le début du
   deuxième nom. ” ” a été concaténé à la fin de A1 de sorte que s’il n’y a que le prénom,
   FIND ne donnera pas d’erreur car il trouvera toujours les blancs. +1 a été ajouté pour démarrer le MID
   position à partir de laquelle commence le deuxième prénom.
3. MOYEN(A1,TROUVER(““,SUBSTITUER(A1&” “,” “,”“,2))+1,1))
   SUBSTITUTE(A1&” “,” “,”*”,2) remplacera le deuxième blanc par un *, nous pouvons donc trouver le
   position de * pour localiser le début du nom de famille. Comme au 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 démarrer le MID
   position à partir de laquelle le nom de famille commence.
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 IFERROR peut être utilisé. Dans ce cas, la formule deviendrait –
   =SUPER(COUPER(GAUCHE(A1,1)&SIERREUR(MILIEU(A1,TROUVER(“
   “,A1)+1,1),””)&SIERREUR(MID(A1,TROUVER(““,SUBSTITUTION(A1,” “,”“,2))+1,1),””)))
   Remarque – Cette technique peut être utilisée pour étendre jusqu’à plusieurs mots. Seul le changement sera en dernier
   bloc où vous pouvez 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 un maximum de 6 mots, la formule deviendrait

Obtenir le nom de la 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”
Par conséquent, si A1=27, vous voulez “AA” et ainsi de suite.
La formule pour dériver le nom de la colonne serait –
=SUBSTITUER(ADRESSE(1,A1,4),1,””)

Obtenir une 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 pour cela.
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 –
=SUBSTITUER(ADRESSE(1,A1,4)&”:”&ADRESSE(1,A1,4),1,””)

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 un série comme ci-dessous

Et vous donnez =LARGE(A1:A10,3), vous obtenez la réponse comme 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 {24,24,24,22,22,18,18,9} et le 3ème plus grand est 24.
Mais en fait, vous voulez l’unique 3ème plus grand qui est 18 comme réponse.

La formule pour un tel cas serait

=LARGE(IF(FREQUENCY($A$2:$A$10,$A$2:$A$10)<>0,$A$2:$A$10),3)

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 voulez 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 tant qu’heure
= MOD(A1,1)

Convertir un nombre en années et mois

Supposons que vous ayez reçu un nombre dans la cellule A1, disons 26, et que vous souhaitiez l’afficher sous la forme 2
Années et 4 mois, vous pouvez utiliser la formule suivante –
=INT(A1/12)&” Années et “&MOD(A1,12)&” Mois”
Maintenant, un utilisateur peut devenir plus exigeant et il peut 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 doit être converti en 8
Mois et il ne doit 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é sous la forme de 8 mois uniquement et non sous la forme de 0 ans et 8 mois.
Maintenant, l’utilisateur peut demander plus. Il peut dire que lorsque je donne 12, cela s’affiche sous la forme 1 ans et 0 mois et
il veut simplement voir 1 ans seulement. Et pour 36 ans, il veut voir seulement 3 ans pas 3 ans 0
Mois. Dans ce cas, la formule devra être modifiée davantage. Maintenant, la formule devient –
=SI(INT(A1/12)>0,INT(A1/12)&” Années “,””)&SI(MOD(A1,12)=0,””,MOD(A1,12)&”
Mois”)
Maintenant, un utilisateur peut venir et peut demander une dernière chose. Il peut dire que si c’est 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 devrait être
affiché comme 2 ans et 1 mois et non comme 2 ans et 1 mois. Par conséquent, 18 ne devrait pas être
affiché comme 1 an et 6 mois mais comme 1 an et 6 mois. De même, 13 devrait être
affiché comme 1 an et 1 mois et non comme 1 an et 1 mois.
=SI(INT(A1/12)>0,INT(A1/12)&” Année”&SI(INT(A1/12)>1,”s”,””)&” et
“,””)&MOD(A1,12)&” Mois”&SI(MOD(A1,12)>1,”s”,””)

Excel: COUNTIF pour une plage non contiguë

Nous aimons tous COUNTIF. Et c’est très facile à faire – dites simplement =COUNTIF(“A1:A100″,”>5”) et
il trouve toutes les valeurs dans la plage A1 à A100 qui sont supérieures à 5. Mais que se passe-t-il si je
voulait le résultat pour seulement A3, A8 et il devrait omettre les autres cellules. Essayez de mettre en suivant
formule –
=COUNTIF((A3, A8),”>5″) et cela vous donnera une erreur #VALUE.
Une solution possible est
=(A3>5)+(A8>5)
Que se passe-t-il si vous devez faire pour A3, A4, A5, A8, A24, A40, A45, A89. Maintenant, vous aurez
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
formule ci-dessous. Cette formule unique peut prendre en charge des plages contiguës (comme A3: A5) et non contiguës à la fois –
=SOMME(NB.SI(INDIRECT({“A3:A5″,”A8″,”A24″,”A40″,”A45″,”A89″}),”>5”))

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 –
=NBARBRE(TRIM(A1))-NBRC(SUBSTITUER(TRIM(A1),” “,””))+(TRIM(A1)<>””)
Formule pour calculer le nombre de mots dans une plage –
=SOMMEPROD(NBCAR(TRIM(A1:A100))-NBRC(SUBSTITUT(TRIM(A1:A100)),”
“,””))+(COUPER(A1:A100)<>””))