Aide-mémoire sur les formules Excel ultimes : 50 formules

Microsoft Excel est facile pour tout le monde. Mais de nombreuses fonctionnalités avancées se cachent également sous le capot.

La plupart des gens peuvent exécuter immédiatement les fonctions de base d’Excel. Mais pour pouvoir effectuer un travail plus sophistiqué dans Excel, vous devez d’abord savoir ce qui est disponible.

En lisant cette feuille de triche, vous en apprendrez plus sur ce qu’Excel peut faire pour vous. Et, après cela, vous pouvez rafraîchir vos compétences Excel avec notre article Excel avancé.

Mains tapant sur un ordinateur portable, qui est placé sur un bureau à côté d'un ordinateur portable, de lunettes de soleil noires et d'une boisson au café, d'une feuille de calcul Excel sur l'écran d'un ordinateur portable

Raccourcis et commandes Excel

En plus d’avoir besoin de connaître les fonctions de base d’Excel, vous voudrez peut-être en savoir un peu plus sur les raccourcis. Les raccourcis et les commandes facilitent les opérations de base dans Excel, car vous n’avez pas besoin de rechercher dans les menus. Il est plus facile d’appuyer simplement sur CTRL + D que de copier et coller manuellement.

Voici quelques-uns des raccourcis les plus populaires :

Fonction Raccourci
Insérer la date actuelle ctrl+ ;
Insérer l’heure actuelle maj+ctrl+ ;
Modifier le commentaire de la cellule merde + F2
Afficher la cellule active ctrl + retour arrière
Insérer une colonne alt+LC
Insérer une ligne alt+lR
Remplissez ctrl+D
Remplir à droite ctrl+R
Somme automatique Alt+=
Insérer un graphique Alt+F1
Enregistrer le classeur maj+alt+F2
Passer au dernier ctrl+FIN

Pour plus de raccourcis, consultez notre article complet sur les raccourcis clavier Excel.

Feuille de triche de référence de cellule Excel

La plupart des formules dans Excel nécessitent une référence de cellule. La façon dont vous définissez la référence de cellule affectera la façon dont la formule est appliquée et copiée de l’une à l’autre. Voici la liste des moyens les plus courants de référencer une cellule dans Excel.

Référence de cellule relative =A2+B2
Référence de cellule absolue +1$A$

Référence de cellule relative

Une référence de cellule relative fait référence à l’adresse d’une cellule particulière et cette référence de cellule est automatiquement ajustée lorsque vous copiez une formule d’une cellule à l’autre. Une référence de cellule relative consiste en l’adresse de la cellule.

Si vous utilisez une référence relative, lorsque vous copiez la formule +A1+B1, dans une autre cellule, Excel modifie automatiquement la formule pour refléter les nouveaux numéros de colonne ou de ligne comme +A2+B2.

Références de cellule absolues

Pour empêcher Excel de modifier la référence de ligne ou de colonne lorsque vous copiez une formule, vous pouvez utiliser des références de cellule absolues dans vos formules au lieu de références relatives. Pour créer une référence de cellule absolue, vous devez insérer le symbole « $ » dans la référence de cellule pour indiquer que cette valeur ne doit pas être ajustée.

Fonctions de date et d’heure d’Excel

Quel jour est-il? Il faut regarder le calendrier. Mais votre ordinateur le sait déjà. Excel dispose d’un certain nombre de fonctions de date et d’heure intégrées qui peuvent vous faciliter la vie.

Remarque : en interne, Excel utilise un « numéro de série » pour représenter les dates. Ainsi, les fonctions d’Excel renvoient généralement une date de numéro de série – et il existe d’autres fonctions utilisées pour la formater en une version lisible.

Fonction Syntaxe Description
DATE DATE (année, mois, jour) La fonction de date d’Excel renvoie une date en fonction des paramètres année, mois, date.
DATESI DATEDIF(date_début,date_fin,unité) Cette fonction calcule le temps entre deux dates données.
JOUR JOUR(numéro de série) Cette fonction renvoie le jour réel d’une date, sous la forme d’un entier compris entre 1 et 31.
EDATE EDATE(start_date, mois) Cette fonction ajoute une période de mois à une date de début.
UN MOIS EOMONTH(start_date, mois) Cette fonction fait la même chose que la fonction EDATE, mais elle renvoie la dernière période du mois.
MAINTENANT MAINTENANT() La fonction now renvoie le numéro de série représentant la date à l’heure immédiate (y compris l’heure).
AUJOURD’HUI AUJOURD’HUI() La fonction aujourd’hui renvoie le numéro de série représentant la date (juste la date).
ANNÉE ANNÉE() La fonction année transforme le numéro de série représentant la date en année.

Fonctions logiques Excel

Les fonctions logiques sont là où MS Excel devient vraiment avancé. La plupart des langages de programmation ont des fonctions logiques similaires ; en fait, n’importe quel programme peut être créé à l’aide des commandes « IF ». Comprendre la logique vous aidera à créer des classeurs plus dynamiques.

Si vous rencontrez des difficultés pour comprendre les fonctions logiques d’Excel, le moyen le plus simple consiste généralement à les cartographier sur une fiche.

Fonction Syntaxe Description
SI SI(instruction, vrai, faux) La fonction « SI » démarre avec un paramètre donné. Si ce paramètre est vrai, il fait une chose. Si c’est faux, ça en fait un autre.
OU OU(état 1, état 2) La fonction « OR » est comme la fonction « IF » mais elle peut tester plusieurs conditions logiques en même temps. Il renverra true ou false, il peut donc être utilisé imbriqué dans une fonction IF.
ET ET(état 1, état 2) La fonction « ET » est similaire à la fonction « OU ». Mais alors que la fonction « OU » renvoie vrai si l’une des conditions est remplie, la fonction ET ne renvoie vrai que si toutes les fonctions sont remplies.
PAS NON (état 1) La fonction « NOT » exécutera une instruction et renverra vrai ou faux. Il peut également être utilisé comme fonction imbriquée dans une fonction IF.
SIERREUR SIERREUR(argument, note_erreur) La fonction « IFERREUR » est un moyen de déboguer votre logique. Vous envoyez un argument et ce qui doit être affiché si cet argument produit une erreur.

Fonctions de recherche Excel

Besoin de rechercher des données ? Excel vous propose un large éventail de fonctions RECHERCHE (certes complexes).

Fonction Syntaxe Description
CHERCHER RECHERCHE(valeur_recherche, vecteur_recherche, [result_vector]) La fonction RECHERCHE est l’une des fonctions les plus importantes d’Excel à comprendre, bien que la plupart utilisent les fonctions RECHERCHEV ou RECHERCHEH (car elles sont plus faciles). Vous commencez par un LOOKUP_VALUE (ce que vous essayez de rechercher) puis un LOOKUP_VECTOR (où vous cherchez).
RECHERCHEV VLOOKUP(lookup_value, lookup_location, column_return, environ) La fonction RECHERCHEV est généralement plus facile à utiliser que RECHERCHE. Pour VLOOKUP, vous commencez par la valeur que vous recherchez et l’emplacement que vous recherchez. Vous ajoutez ensuite un column_return ; le match que vous recherchez. Enfin, vous choisissez si vous voulez des valeurs approximatives ou des correspondances exactes.
RECHERCHEH HLOOKUP(lookup_value, lookup_location, column_return, environ) RECHERCHEH fonctionne de la même manière que RECHERCHEV, mais il est moins couramment utilisé car il est utilisé sur des graphiques configurés horizontalement.
INDICE INDEX(tableau, ligne, colonne) La fonction INDEX renverra un élément dans une table ou un tableau, compte tenu de sa position.
CORRESPONDRE MATCH(valeur, tableau, match_type) La fonction MATCH peut être utilisée si RECHERCHEV ou RECHERCHEH ne convient pas. Il recherchera une valeur dans un tableau. Il est important de noter que lorsque vous utilisez MATCH, INDEX et des commandes logiques, vous pouvez parcourir une feuille entière pour trouver une valeur donnée. (En savoir plus sur la fonction MATCH ici.)

Les étudiants Excel apprennent également

Fonctions mathématiques Excel

Les fonctions mathématiques sont parmi les fonctions les plus importantes d’Excel et certaines des fonctions les plus fréquemment utilisées. Si vous commencez à taper un long algorithme, considérez qu’Excel a peut-être déjà la fonction pour le faire.

Vous pouvez effectuer des opérations mathématiques de base comme l’addition (+), la soustraction (-), la multiplication

, et division (/) sans utiliser de fonction réelle. Vous pouvez également produire des choses plus compliquées, telles que des exposants (2^2) de cette manière. Fonction Syntaxe
Description abdos ABS(valeur)
ABS vous donne la valeur absolue d’un nombre. 1 et -1 renverront 1. MOYENNE MOYENNE (gamme)
MOYENNE vous donne la moyenne d’une plage de nombres donnée. MÉDIAN MÉDIANE (gamme)
MEDIAN vous donnera la valeur médiane d’une plage de nombres. PI PI()
Vous n’avez pas besoin de mémoriser PI. La fonction PI fera tout pour vous. SOMME SOMME (plage)
L’une des fonctions les plus couramment utilisées, la fonction SOMME combinera plusieurs cellules d’une plage en une seule somme. SUMIF SUMIF(plage, standard)

Variante de SUM, la fonction SUMIF renvoie les valeurs comprises dans la plage pour un ensemble de normes donné. Il y a aussi la fonction SUMIFS.

Fonctions financières Excel

Quel secteur utilise Microsoft Excel plus que le secteur financier ? Excel est principalement utilisé pour « faire les livres », il est donc logique qu’Excel ait en fait de nombreuses fonctions financières intégrées.

Il convient de noter que bon nombre de ces fonctions financières sont assez complexes. Mais ce n’est pas un problème avec Excel. Habituellement, ces fonctions sont utilisées par des personnes qui savent déjà ce que font les fonctions financières. Si vous n’êtes pas au courant d’une fonction financière compliquée, les résultats peuvent ne pas être précis, même si la syntaxe Microsoft Excel est correcte. Fonction Syntaxe
Description BD [month]DB(cost_basis, salvage_cost, life, period, )
DB calcule l’amortissement d’un actif avec un solde dégressif fixe. DDB [factor]DDB(cost_basis, salvage_cost, life, period, )
La fonction DDB calcule l’amortissement d’un actif avec un solde dégressif double. VF [future_payments]FV(taux_de_retour, nombre_de_paiements, paiements_effectués, [eob], )
La fonction FV est utilisée pour retourner la valeur future d’un investissement donné. C’est un calcul incroyablement complexe, mais il peut être utilisé facilement dans Excel. IPMT [desired_cash_balance]IPMT(interest_rate, payment_period, number_of_payments, present_value, [eob], )
Ceci est utilisé pour calculer la partie des intérêts d’un remboursement de prêt, au cas où vous voudriez savoir combien d’intérêts vous sont facturés. TRI [estimate]TRI(valeurs, )
IRR est une fonction qui renvoie le taux de rendement interne pour une série de flux de trésorerie. INTÉGRÉ [basis]INTRATE(date_règlement, date_échéance, investi, remboursement, )
INTRATE renvoie le taux d’intérêt d’un titre dans lequel un investissement a été effectué ; autrement dit, le taux de rendement. VAN VAN(taux, range_of_cashflows)
La VAN est l’une des fonctions financières les plus couramment utilisées. Il calcule la valeur actualisée nette d’un prêt ou d’un investissement. EMP [future_value]PMT(taux, nombre_de_paiements, valeur_actuelle, [eob], )
Cette fonction renverra le montant du remboursement périodique d’un prêt ; combien doit être remboursé régulièrement pour un prêt donné. PV [future_value]PV(taux, nombre_de_paiements, paiement, [eob], )
Cette fonction renvoie la valeur actuelle d’un investissement, compte tenu des paramètres relatifs à cet investissement. SLN SLN (coût, valeur de récupération, durée de vie)
La fonction SLN renverra l’amortissement d’un actif pour une seule période, calculé sur une ligne droite. XNPV XNPV (taux, plage de valeurs, dates)
Cette fonction est similaire à la fonction NPV, mais elle la calcule en fonction de valeurs irrégulières plutôt que de valeurs régulières. XIRR [guess]XIRR(valeurs, dates, )
Cette fonction est similaire à la fonction IRR, mais elle la calcule en fonction de valeurs irrégulières plutôt que de valeurs régulières. RENDEMENT RENDEMENT(date_règlement, date_échéance, taux, prix, remboursement, fréquence, base)

La fonction RENDEMENT renvoie le montant qui a été produit sur un titre avec intérêt périodique.

En savoir plus sur Excel

Les formules et fonctions Excel ci-dessus devraient vous donner un bon départ. Il existe de nombreuses options si vous souhaitez apprendre Excel. En plus de ce qui précède, il existe des fonctions de graphique, des tableaux croisés dynamiques et SQL dans Excel. Excel peut être une solution assez avancée – si vous êtes prêt à prendre le temps de l’apprendre.