Rappels sur les règles de priorité en mathématique

Priorités dans les calculs et rôle des parenthèses

=1+2*1+2

Le résultat sera :  1 + 2 + 2 = 5 (priorité de calcul sur la multiplication puis l’addition)

=(1+2)*(1+2)

Le résultat sera :  3 * 3 = 9 (priorité de calcul sur la multiplication puis l’addition)

=2^10

2 à la puissance 10 (résultat = 1024)

Calculs basiques dans Excel

Parcequ'il faut bien commencer par le début

=SOMME(F7:F11)

Revient à faire =F7+F8+F9+F10+F11. Le « : » veut dire en langage Excel : jusqu’à

=SOMME(F7:F11 ; G5:G10 ; P9)

Permet de faire la somme de plages non contigües (utilisez la touche CTRL pour votre sélection)

=MOYENNE(B5:B12)

Renvoie la moyenne arithmétique de B5:B12

=MAX(B5:B12)

Renvoie la plus grande valeur de la plage B5:B12

=MIN(B5:B12)

Renvoie la plus petite valeur de la plage B5:B12

Quelques formules utiles au quotidien

Calculs sur TVA ou évolutions dans le temps

TTC = HT * (1 + TVA%)

Vous avez le HT et le taux de TVA%, vous cherchez à calculer le montant TTC.

HT = TTC / (1 + TVA%)

Vous avez le TTC et le taux de TVA%, vous cherchez à calculer le montant HT.

TVA% = (TTC / HT) -1

Vous avez le TTC et le HT, vous cherchez à calculer le taux de TVA%.

Δ% = (B – A) / A

Évolution(Δ) en % de A vers B (fonctionne en positif aussi bien qu’en négatif)

Commpter et dénombrer dans Excel

Répond à la question « combien ? »

=NB(C2:C15)

Compte le nombre de cellules qui contiennent un chiffre, dans la plage C2:C15

=NBVAL(C2:C15 ; F5 :F30)

Compte le nombre de cellules non vides dans les plages C2:C15 et F5:F30

=NB.VIDE(D2:D15)

Compte le nombre de cellules vides dans la plage D2:D15

=NB.SI(D20:D50 ; ">5")

Compte le nombre de cellules dans la plage D20:D50 qui sont supérieures à 5 strictement

=NB.SI(B4:D12 ; "Oui")

Compte le nombre de cellules dans la plage B4:D12 qui contiennent exactement « Oui » (insensible à la casse)

=NB.SI(C3:C18 ; "<>Bébé")

Compte le nombre de cellules dans la plage C3:C18 qui sont différentes de « Bébé »

=NB.SI(B2:B21 ; "*5")

Compte le nombre de cellules dans la plage B2:B21 qui finissent par 5 (l’étoile remplace une chaîne de caractères de longueur variable)

=NB.SI(B2:B8 ; "?E*5")

Compte le nombre de cellules dans la plage B2:B8 qui ont un E en deuxième position et qui finissent par 5 (le « ? » remplace un caractère unique)

Sommes conditionnées

Permet de sommer avec conditions

=SOMME.SI(C2:C20 ; "Fruit" ; D2:D20)

Fait la somme des chiffres de la colonne D, mais seulement lorsque « Fruit » est trouvé en colonne C

=SOMME.SI(C2:C20 ; "*pom*" ; D2:D20)

Fait la somme des chiffres de la colonne D, mais seulement lorsque les cellules de la colonne C contiennent le terme « pom »

=SOMME.SI.ENS(E8:E17 ; C8:C17  ;  "MIELE" ; E8:E17 ; "Oui")

Fait la somme des chiffres de la colonne E, mais seulement lorsque les cellules de la colonne C contiennent « MIELE » et que les cellules de la colonne E contiennent « Oui »

Fonctions "Date" et "Heure"

Permet de manipuler facilement des dates

18/12/2020

C’est le format officiel pour taper une date

12:30

C’est le format officiel pour taper une heure + minutes

=AUJOURDHUI()

Affiche la date du jour (mise à jour automatique)

=JOUR(B8)

Renvoie le numéro du jour de la date se trouvant en B8

=MOIS(B8)

Renvoie le numéro du mois de la date se trouvant en B8

=ANNEE(B8)

Renvoie le numéro de l’année de la date se trouvant en B8

=DATE(E8 ; D8 ; C8)

Fabrique une date avec l’année, le mois et le jour
(Attention à l’ordre des arguments)

=DATEDIF(AncienneDate ;
NouvelleDate ; "y")

Calcule la différence entre 2 dates en années (voir les autres arguments possibles ci-dessous)

"y" : Différence en années
"m" : Différence en mois
"d" : Différence en jours
"ym" : Différence en mois, une fois les années soustraites
"yd" : Différence en jours, une fois les années soustraites
"md" : Différence en jours, une fois les années et les mois soustraits

=HEURE(B5)+(MINUTE(B5)/60)

Convertit une heure (et ses minutes) en valeur numérique

=FIN.MOIS(C9 ; 2)

Regarde la date se trouvant en C9, va à la fin du mois, puis avance de 2 mois (vous pouvez utiliser des chiffres entiers négatifs pour « remonter dans le temps »)

=FIN.MOIS(B2 ; 3) + 12

Regarde la date se trouvant en B2, va à la fin du mois, puis avance de 3 mois, puis avance de 12 jours

Fonctions conditionnelles

SI, SI-SI, SI-ET, SI-OU, etc.

Une formule conditionnelle s’écrit toujours avec 3 arguments : Question ; que se passe-t-il si c’est vrai ; que se passe-t-il si c’est faux

=SI(E27>1000 ; "Bon client" ; "Bof")

Si E27 contient un chiffre supérieur à 1000, alors, la fonction écrit Bon client, sinon Bof

=SI(D2>=2 ; "Bravo" ; "")

Si D2 contient un chiffre supérieur ou égal à 2, alors, la fonction écrit Bravo, sinon la fonction laisse la cellule vide

=SI(D12>100 ; D12*5% ; D12*4%)

Si D12 contient un chiffre supérieur à 100, alors, la fonction renvoie le résultat de D12*5%, sinon la fonction renvoie le résultat de D12*4% 

=SI(D7>=10 ; "Reçu" ; SI(D7>=8 ; "Rattrapage" ; "Recalé"))

Cette fonction imbrique 2 SI. La note du bas est en D7. SI la note est supérieure ou égale à 10, alors, Reçu. Sinon, SI la note est supérieure ou égale à 8, alors, Rattrapage, sinon, recalé 

=SI(C5>C$12 ; D$12 ; 0)

Si C5 est supérieure à la valeur contenue dans C12, alors, afficher le contenu de D12, sinon, afficher zéro. Cette fonction est re copiable vers le bas grâce au $ 

=SI(OU(C7<8 ; D7<8 ; E7<8)  ; "Recalé" ; "Reçu")

SI ou bien, C7 est inférieure à 8, ou bien D7 est inférieure à 8, ou bien E7 est inférieure à 8, alors, la fonction renvoie Recalé, sinon, la fonction renvoie Reçu.
Note : il suffit qu’un des éléments dans le OU soit vrai pour que le résultat soit vrai.

=SI(ET(C30="Grand" ; D30="Fort") ; "Videur" ; "Vestiaire")

SI, ET à la fois, C30 contient Grand, et que aussi, D30 contient Fort, alors, la fonction renvoie Videur. Sinon, la fonction renvoie Vestiaire.
Note : il faut que les 2 éléments dans le ET soient vrais pour que le résultat soit vrai.

Fonctions pour Bases de Données

Calculs dans des Bases de Données

BDSOMME, BDNB, BDMOYENNE, etc.

Avant d’écrire une fonction commençant par BD, écrivez d’abord les critères dans des cellules

=BDSOMME(B2:K12 ; K2 ; M17:M18)

Effectue dans la colonne K la somme des chiffres, lorsque les critères situés dans la plage M17:M18 sont respectés

=BDMOYENNE(B2:K12 ; K2 ; M17:M18)

Effectue dans la colonne K la moyenne des chiffres, lorsque les critères situés dans la plage M17:M18 sont respectés

=BDNB(B2:K12 ; K2 ; M17:M18)

Compte dans la colonne K les cellules, lorsque les critères situés dans la plage M17:M18 sont respectés

=BDNBVAL(B2:K12 ; K2 ; M17:M18)

Compte dans la colonne K les cellules non vides, lorsque les critères situés dans la plage M17:M18 sont respectés

=RECHERCHEV(B15 ; $C$3:$R$22 ; 3 ; FAUX)
Si la recherche est infructueuse, la fonction renvoie #N/A

Recherche exactement le contenu de B15 dans la première colonne de la matrice située en C3:R22, puis, si la fonction trouve ce contenu, alors, se décale à la 3ème colonne de la matrice et renvoie le contenu de la cellule

=RECHERCHEV(B15 ; $C$3:$R$22 ; 2 ; VRAI)
La première colonne de la matrice doit être triée par ordre croissant si vous utilisez l’argument vrai

Recherche par intervalle le contenu de B15 dans la première colonne de la matrice située en C3:R22, puis, si la fonction trouve ce contenu, alors, se décale à la 2ème colonne de la matrice et renvoie le contenu de la cellule

=EQUIV(H4 ; B4:F4 ; 0)

Recherche le contenu de H4 dans la matrice en ligne B4:F4, de manière exacte, et renvoie le numéro de la colonne de la matrice si le contenu est trouvé (même fonctionnement pour une matrice en colonne, ou la fonction renvoie le numéro de la ligne)

=INDEX($B$3:$F$3 ; 3)

Considère la matrice B3:F3, se place à la colonne 3 de cette matrice et renvoie le contenu de la matrice depuis cette cellule, quel qu’il soit

Fonctions « texte »

Pour le traitement de Bases de Données

=CONCATENER("ART" ; "HUR" ; 4)
="ART" & "HUR" & 4 (alternative)

Assemble les chaines de caractères contenues dans chaque argument. Dans cet exemple, la fonction affichera ARTHUR4

=GAUCHE(B30 ; 2)

Renvoie les 2 premiers caractères situés à gauche de la chaîne de caractères (ou de chiffres) se trouvant dans la cellule B30

=DROITE(B30 ; 4)

Renvoie les 4 derniers caractères situés à droite de la chaîne de caractères (ou de chiffres) se trouvant dans la cellule B30

=STXT(B30 ; 2 ; 5)

Renvoie, à partir du 2ème caractère depuis la gauche, les 5 caractères suivants, de la chaîne de caractères (ou de chiffres) se trouvant dans la cellule B30

=NBCAR(B51)

Compte le nombre de caractères contenus dans la cellule B51, espaces compris

=MAJUSCULE(B4)

Met en majuscule la totalité du texte contenu dans la cellule B4 (notez qu’un é devient un É)

=MINUSCULE(D4)

Met en minuscule la totalité du texte contenu dans la cellule D4 (notez qu’un É devient un é)

=NOMPROPRE(B2)

Met la première lettre de chaque mot en majuscule et le reste en minuscule. Applique la règle à la totalité du texte contenu dans la cellule B2

=SUPPRESPACE(B7)

Nettoie le contenu du texte contenu dans B7 : enlève les espaces en début et en fin de cellule, et ne laisse qu’un espace maximum en milieu de cellule.

=CHERCHE("@"; B67 ; 1)
Fonctionne aussi avec une chaîne de caractères

Cherche le caractère @ dans la cellule B67, commence à chercher à partir du 1er caractère, et renvoie sa position si trouvé

=SUBSTITUE(B16 ; "," ; ".")

Visite le contenu de la cellule B16, et remplace chaque virgule trouvée (,) par un point (.)

=CNUM(B182)
Exemple :

Convertit une chaîne textuelle représentant un nombre, en vrai nombre (exploitable en termes de calculs) ; Résultat de notre exemple : 5050,10

Fonctions logiques, statistiques, et mathématiques

Pour la vie de tous les jours :)

=SIERREUR(A2/B2, "Erreur de calcul")
La fonction SIERREUR est sensible à tous ces types d’erreurs : #N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, #NUL!

Si le calcul A2/B2 renvoie une erreur (par exemple une division par zéro), alors afficher Erreur de calcul.

=ENT(18,798)

Renvoie la partie entière de 18,798 ou du chiffre contenu dans la cellule. Ici, le résultat est 18

=ARRONDI(B20 ; 0) à l’unité
=ARRONDI(B20 ; 1) à la dizaine
=ARRONDI(B20 ; 2) à la centaine

Renvoie l’arrondi au degré spécifié (0 pour l’unité, 1 pour la dizaine, 2 pour la centaine, etc.)
L’arrondi à la centaine de 85,429 est 85,43.
Les degrés négatifs sont admis

=ABS(B32)

Renvoie la valeur absolue du nombre contenu dans la cellule B32. La valeur absolue de 5 est 5, la valeur absolue de -2,3 est 2,3

=ALEA()

Permet de générer au hasard un chiffre entre 0 et 1. Utile pour générer des populations à des fins expérimentales.

=ALEA()*1000

Permet de générer au hasard un chiffre entre 0 et 1000

=ALEA.ENTRE.BORNES(500 ; 700)

Permet de générer au hasard un chiffre entre 500 et 700

=PREVISION(B16 ; A4:A13 ; B4:B13)

Calcule ou prévoit une valeur y à partir d’une valeur X donnée, sachant que l’on possède déjà une liste de valeurs x et y connues (calcul par régression linéaire)

=COMBIN(49 ; 6)

Calcule le nombre de combinaisons possibles de 6 éléments dans un total de 49 éléments (au loto, cela fait 13 983 816 de combinaisons)

{=DROITEREG(C2:C16 ; B2:B16) }

Cette fonction permet de calculer les coefficients a et b d’une droite de régression affine (donc de type y=ax+b), en fournissant à Excel un tableau de y connus et de x connus (sélectionnez 2 cellules puis tapez =, car la réponse utilise 2 cellules différentes, afin d’afficher a et b)

Fonctions financières

Explications et contexte

Nom de la variable
(= nom fonction)

Fonctionnement en mode prêt bancaire ou Assurance Vie
Attention : si vous mettez un nombre de périodes en mois, le remboursement sera donné mensuellement. De même, si le nombre de période est en mois, divisez par 12 votre taux d’emprunt annuel.
Type est en général laissé vide (par défaut, les échéances sont en fin de mois)

NPM

Nombre de périodes (années ou mois) pour un prêt bancaire.
Durée d’un contrat d’Assurance Vie.

VA

Montant du capital emprunté dans un prêt bancaire.
Premier versement investi en Assurance Vie.

VC

Montant restant à régler en fin de prêt (0).
Capital en fin de contrat d'assurance Vie.

VPM

Montant à rembourser à chaque période d’un prêt bancaire (négatif).
Versement volontaire programmée d'un contrat d’Assurance Vie.

TAUX

Taux d’un prêt bancaire en % par période.
Rentabilité d’un contrat d’Assurance Vie ou d’un placement.

Exemples d’utilisations des fonctions financières

Rédaction de la fonction

=VPM(C8/12 ; C4 ; C5 ; C6)

=VC(C8/12 ; C4 ; C7 ; C5)

=VA(C8/12 ; C4 ; C7 ; C6)

=NPM(C8/12 ; C7 ; C5 ; C6)

=TAUX(C4/12;C7;C5;C6)

L'argument [estimation] est optionnel et doit être laissé vide. Par défaut, Excel utilise 10% comme résultat "probable", puis effectue des calculs, jusqu'à trouver un résultat acceptable (méthode de calcul itérative).
Cette fonction peut ne pas "converger", auquel cas elle affichera #NOMBRE