50 fonctions Excel à connaître absolument Microsoft Excel fait partie des logiciels les plus complets en matière de bureautique. Il permet de créer des tableurs complexes et supporte de très nombreuses fonctions. Pour effectuer certaines actions, vous devez passer par des formules assez compliquées. Les fonctions Excel présentées ici sont toutes disponibles au sein de Microsoft Excel 2010 en français. Assez peu de différences existent avec les autres version du logiciel, mais vous devrez traduire ces fonctions si vous utilisez Excel en anglais ou dans une autre langue. Si vous utilisez d’autres fonctions au quotidien, n’hésitez pas à les présenter en commentaire ! Pour aller plus loin, vous pouvez également vous former pour devenir un expert d’Excel. Mathématiques et trigonométrie ALEA.ENTRE.BORNES – Sur Excel, pour obtenir un nombre entier aléatoire entre deux bornes spécifiées. Logique ET – Permet de tester plusieurs conditions et vérifier qu’elles sont vraies. Recherche et matrices Date et heure Texte Statistiques Ingénierie
la fonction Recherchev présentation La fonction RECHERCHEV d'Excel permet de faire exécuter au tableur une recherche dans un tableau de valeurs et de renvoyer la valeur trouvée dans une cellule déterminée à l'avance. On pourra, par exemple, faire trouver par Excel la désignation et le prix unitaire d'un produit en saisissant simplement sa référence. Comme le fait l'ordinateur central d'un hypermarché quand il renvoie la référence et le prix d'un produit dont le code-barres a été lu en caisse ou saisi par la caissière. syntaxe La syntaxe générale est la suivante : =RECHERCHEV(cellule_lue ; tableau ; numéro_de_colonne) celulle_lue : il s'agit de la cellule où Excel lit la valeur qui entraîne la recherche (exemple : la référence d'un produit conduit à la recherche de son prix et de sa désignation). exemple L'exemple ci-dessous montre l'utilisation de cette fonction pour un travail simple de facturation. Voici ce que fait Excel quand il exécute la formule située en B2 : précisions
Les 10 formules magiques! Microsoft Excel trop compliqué? Voici 10 formules utiles ou intéressantes sur Excel, à retenir facilement et à appliquer dans tous vos tableaux. Le langage “officiel” étant assez hermétique, le mieux est encore de suivre chaque exemple et de l’appliquer sur Excel en même temps que vous lisez cet article. Microsoft Excel est le logiciel de tableur par excellence. La somme simple (SOMME) Elle permet d’additionner plusieurs données chiffrées, comme à l’école. La première permet d’additionner des cases qui se suivent (une colonne ou une rangée)=SOMME(première case:dernière case) dans la case où vous souhaitez voir apparaître le résultat. Variante: cette formule permet aussi d’additionner tous les chiffres contenus dans un cadre particulier (sur plusieurs colonnes et plusieurs rangées), en prenant pour première case celle la plus “en haut à gauche”, et comme dernière case celle la plus “en bas à droite”. Démonstration par l’exemple: La somme en fonction d’un critère déterminé (SOMME.SI) En bref
Fonction RECHERCHEV - Support Office La fonction de recherche et matrice RECHERCHEV permet de rechercher des éléments dans une table ou une plage par ligne. Par exemple, vous pouvez rechercher le prix d’une pièce automobile à l’aide de son numéro de référence. Dans sa forme la plus simple, la fonction RECHERCHEV a la signification suivante : = RECHERCHEV(valeur à rechercher, plage dans laquelle rechercher la valeur, numéro de colonne dans la plage de cellules contenant la valeur de retour, correspondance exacte ou approximative, indiquée par 0/FAUX ou 1/VRAI). Cette vidéo fait partie d’un cours de formation intitulé RECHERCHEV : quand et comment l’utiliser. Conseil : Pour tirer le meilleur parti de la fonction RECHERCHEV, il convient d’organiser vos données afin que la valeur que vous recherchez (numéro de référence) se trouve à gauche de la valeur de retour à rechercher (prix de la pièce). Détails techniques Utilisez la fonction RECHERCHEV pour rechercher une valeur dans une table. Syntaxe Par exemple : Prise en main Exemples
MAÎTRISER LES FONCTIONS DE BASES DE DONNÉES Vous êtes plusieurs à me poser des questions sur les fonctions de bases de données dans Excel. Personnellement, je n’utilise plus jamais ces fonctions puisque de nouvelles fonctions, apparues dans les dernières versions d’Excel, peuvent à mon avis, procéder aux mêmes types de calculs plus efficacement (comme la fonction Somme.si.ens. ou sumifs par exemple). Cela dit, cet article vous présente néanmoins comment utiliser les fonctions de bases de données. Comment reconnaître les fonctions de bases de données? Les fonctions de bases de données débutent toutes par =BD (en français) ou =D (en anglais) et elles sont utilisées pour performer des calculs sur les bases de données selon divers critères. Exemples de fonctions de bases de données Dans l’exemple suivant, je vous présente un bref aperçu des fonctions BDMAX, BDMIN, BDMOYENNE et BDSOMME. La structure des formules est toujours la même : 1 – Plage de données de référence 3 – Critères à respecter Consulter la liste des formations offertes
DATEDIF() : Calculer la différence entre deux dates - Tutoriels & Astuces Excel > Fonctions & Formules La fonction DATEDIF() permet de calculer la différence entre deux dates en années, mois et jours. Cette fonction n'est pas documentée dans l'aide Excel, mais elle s'avère bien pratique pour effectuer des calculs sur des âges ou des dates d'échéance. Syntaxe : DATEDIF (Date1; Date2;Intervalle) Date 1 est la date de DépartDate 2 est la date de FinIntervalle peut prendre différentes valeurs: "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 à noter : Date2 doit toujours être supérieure ou égale à Date1 Exemple Pour illustrer les possibilités de la fonction DATEDIF, prenons l’exemple de 3 chantiers dont nous connaissons les dates de démarrage et de fin, et pour lesquels nous souhaitons connaître la durée : Idem pour le chantier 1, qui a duré 5 ans ou 61 mois ou 1886 jours. Formule :
=TEXTE(valeur à mettre en forme; "Code de format à appliquer") La fonction TEXTE vous permet de modifier la manière dont un nombre est affiché en lui appliquant une mise en forme qui utilise des codes de format. Cela peut vous être utile lorsque vous souhaitez afficher des nombres dans un format plus lisible, ou quand vous souhaitez combiner des nombres à du texte ou des symboles. Remarque : Étant donné que la fonction TEXTE convertit les nombres en texte, vous risquez de rencontrer des difficultés pour y faire référence dans des calculs futurs. Aussi, nous vous conseillons de conserver la valeur d’origine dans une cellule, puis d’utiliser la fonction TEXTE dans une autre cellule. Ensuite, si vous avez besoin de créer d’autres formules, faites référence à la valeur d’origine et non au résultat de la fonction TEXTE. Détails techniques Syntaxe TEXTE(valeur; format_texte) La syntaxe de la fonction TEXTE utilise les arguments suivants : Vue d’ensemble Dans sa forme la plus simple, la fonction TEXTE a la signification suivante : Télécharger nos exemples Oui.
Fonction RECHERCHEV (valeur exacte) Vous pouvez utiliser la fonction RECHERCHEV pour récupérer les données d'un tableau en vous appuyant sur le contenu de la première colonne, exactement comme pour une base de données. Principe de la fonction RECHERCHEV Il est courant d'avoir des classeurs avec des informations sur ses clients, ses prospects ou ses produits et de souhaiter récupérer ces données sans avoir à les recopier. Pour cela, il faut utiliser la fonction RECHERCHEV. Pour construire une fonction RECHERCHEV, il faut 4 paramètres Le premier paramètre de la fonction contient une valeur contenue dans la première colonne du tableau de données.Le deuxième paramètre représente la plage de données du tableau de références.Le troisième paramètre désigne la colonne qui va être lue.Recherche exacte (0 ou FAUX) ou approchante (1 ou VRAI). Remarque Avant de détailler chaque composant, il est indispensable de s'attarder sur le dernier paramètre qui est normalement optionnel mais qu'il faut toujours renseigner.
Vérifier qu'une cellule contient du texte (sans respect de la casse) Important : Cet article a été traduit automatiquement, voir l’avertissement. Vous pouvez consulter la version en anglais de cet article ici. Supposons que vous voulez vous assurer qu'une colonne contient du texte et non des nombres ou que vous voulez rechercher toutes les commandes associées à un vendeur donné. Il existe plusieurs façons de vérifier qu'une cellule contient du texte, qu'il soit en majuscules ou en minuscules. Vous pouvez également utiliser un filtre pour rechercher du texte. Rechercher les cellules contenant du texte Sélectionnez la plage de cellules qui doit faire l’objet de la recherche. Remarque : Pour annuler une recherche en cours, appuyez sur ÉCHAP. Pour vérifier si du texte est présent dans une cellule Pour ce faire, utilisez la fonction ESTTEXTE. Vérifier que le contenu de la cellule correspond à du texte spécifique Pour ce faire, utilisez la fonction SI. Vérifier qu'une partie du contenu de la cellule correspond à du texte spécifique
RECHERCHEV : présentation et contexte d’utilisation - Excel - Vidéo Découvrir d’autres formations Excel sur LinkedIn Learning Apprenez à utiliser RECHERCHEV pour rechercher une valeur dans une liste ou un tableau Excel. Utiliser RECHERCHEV équivaut à rechercher le nom d’une personne dans un annuaire téléphonique en vue d’obtenir un numéro de téléphone. RECHERCHEV examine une valeur dans une colonne et trouve sa valeur correspondante sur la même ligne dans une autre colonne. Objectifs Au terme de ce cours, vous pourrez : entrer des arguments RECHERCHEV dans une formule. Avant de commencer Vous devriez déjà avoir une bonne connaissance des formules Excel. Télécharger ce cours Version hors connexion (31 Mo) Exercices pratiques Désolé. Aide-mémoire Consultez l’aide-mémoire pour un bref rappel imprimable des principaux points de ce cours. D’autres cours sont disponibles sur Formations Microsoft Office.
Comment fusionner ou séparer les données de plusieurs cellules sur Excel Lorsque l’on manipule des champs de texte sur Excel, il arrive souvent que le texte ne soit pas bien réparti entre les cellules. On a donc souvent besoin soit de fusionner 2 cellules de texte en une seule, soit de séparer une cellule de texte en 2 cellules distinctes. Je vous montre tout de suite comment faire : Comment fusionner le texte de 2 cellules en une seule cellule Pour cela nous allons utiliser la formule très pratique CONCATENER. Cela signifie tout simplement mettre a bout 2 chaines de caractères. La fonction concatener prend autant d’arguments que vous voulez, vous pouvez donc mettre à la suite 2, 3 ou 10 cellules si vous le souhaitezDans notre exemple, on considère en fait 3 chaines de caractères, en insérant la chaine » » pour plus de lisibilité du résultat Comment séparer le texte d’une seule cellule en plusieurs cellules Plus difficile maintenant, comment séparer le texte de part et d’autre d’un espace? On utilise pour cela la fonction GAUCHE Isoler le texte entre 2 espaces
La fonction RECHERCHEV en masquant les #N/A La fonction RECHERCHEV La fonction RECHERCHEV (Recherche Verticale) va nous permettre de faire apparaître des données provenant d’une autre feuille ou d’un autre fichier d’Excel. Nous pourrons ainsi sur une autre feuille, ‘afficher par exemple les coordonnées de nos fournisseurs, clients, employés, ou comme dans l’exemple ci-dessous d’établir rapidement le tableau des ventes du mois en récupérant les catégories de produits, leur désignation, leur prix, ainsi qu’une Fiche récapitulative produits. Vous trouverez maintes applications car c’est une fonction très utilisée et très intéressante. Il nous faudra pour cela avoir une colonne commune comme ici la « Référence » du produit, celle-ci devant se trouver en première colonne dans nôtre tableau des ventes. Après la recopie des formules, les références non encore saisies ou inexistantes provoqueront un affichage dans la cellule d’un message #N/A (Not Available). Un exemple de RechercheV avec les #N/A masqués. – La catégorie (en colonne B)
protéger des données présentation Dans de très nombreuses circonstances, il est utile de pouvoir protéger ses données, contre la curiosité, la malveillance et, le plus souvent, les erreurs de manipulation. Cette page présente les différentes méthode de protection de données et à quoi elles s'appliquent. Que désirez-vous protéger ? protéger un fichier Excel Je vous propose de télécharger ce fichier Excel et de constater les différences entre possibilité de lecture et possibilité d'écriture. protéger un classeur protéger une feuille et des cellules Pour protéger une cellules, plusieurs cellules ou une plage de cellules, il faut verrouiller ces cellules, puis protéger la feuille. Chaque feuille possède ses paramètres de protection indépendant de ceux des autres feuilles. En fait, par défaut, toutes les cellules d'une feuille Excel sont verrouillées. Il s'agit donc de définir quelles sont les cellules déverrouillées dans une feuille puis de protéger la feuille. Définir les cellules déverrouillées (non protégées)
Comment utiliser la fonction SI La fonction SI dans le logiciel Excel, permet de réaliser des calculs qui contiennent deux ou plusieurs cas en fonction du résultat d’un calcul. Il n’est souvent pas aisé de l’utiliser. Notre objectif est de faciliter son usage par les utilisateurs d’Excel. La fonction SI peu être utiliser seul ou s’associer très souvent avec le OU exclusif ou avec le ET inclusif et même avec les deux à la fois selon les conditions du calcul. La fonction « SI () » Si le résultat d’un calcul nécessite pour analyser seulement deux conditions exclusives, la fonction SI est nécessaire. La fonction « SI(OU(…)) Ou (ET(…)). » La fonction SI est suivi de OU, si les conditions posées son nombreuses et que la vérification d’une seule de ces conditions suffit, Tandis que SI est suivi de ET, si toute les conditions doivent être satisfaites. Exemple1 : si l’élève n’as pas la moyenne en maths, il est quand même refusé. SI (ET (Moyenne>= 10 ; Maths>=10) ; « Admis » ; « refusé »)