Astuces Formules avec Excel Sommaire A) – Fonctions DATES 1) – Déterminer le mois d’une date 3 2) – Fonction ANNEE() 3) – Convertissez vos dates en jours de la semaine 4)- Calculez le nombr 5) – Utilisez des dates 6) – Deux fonctions p deux dates orao Snivxto w nev:ÇEge 4 en chiffres 5 7) – Formule pour extraire le mois et l’année à partir d’une date 8) – Trouver la différence entre deux dates (DATEDIF) 6 9) – IJne formule pour créer une liste des jours de la semaine 7 10) – Formule pour calculer les dates antérieures à 1900 8 1 1) – Formule pour calculer vos horaires de travail 9
B) – Fonctions INFORMATIONS 1) – Fonction Sl() 12 2) – un moyen simple de vérifier vos conditions 3) – Personnalisez vos messages d’erreurs avec une formule 13 4) – Affichez automatiquement la date de saisie de vos données 5) – Faites ressortir une valeur selon plusieurs critères 14 D) – Fonctions MATHEMATIQUES 16 1) – Additionner rapidement des données 2) – Fonction SOMME() 3) – Effectuer des sommes globales dans un tableau 4) – Somme automatique à chaque ligne et colonne d’une plage 17 5) – Effectuer des sommes à partir de listes filtrées 18 6) – Ignorez certaines valeurs dans une addition ) – Fonction SOMME.
SI : ignorez certaines valeurs dans vos additions 19 8) – Insérez automatiquement les sous-totaux 20 9) – Arrondissez vos résultats de manière personnalisée 10) – Sommes sélectives grâce à une formule matricielle 21 1) – Supprimez une ligne PAGF OF trois 1 2) – Calculez des moyennes plus significatives 22 1 3) – Découvrez et utilisez la fonction MOD 14) – Fonction MOD() 23 Astuces Formules l. doc – bitsch. gerard@orange. r 44120 – VERTOU page : 1 / 36 1 5) – Affichez des fractions irréductibles avec la formule PGCD 1 6) – Testez vos tableaux avec des valeurs aléatoires 4 E) – Astuces Diverses 1) – Additionner plus de 24 heures sans erreur 2) – Afficher le résultat d’une formule en permanence 25 3) – Affichez les formules de vos feuilles de calcul 4) – Affichez les paramètres d’une fonction 5) – Bonne méthode pour construire une feuille 6)- Calculez la durée d’un trajet 26 7) – Camouflez vos formul au résultat recherché 29 1 1) – Entrez une formule en français dans une macro 1 2) – Faites ressortlr les données uniques grâce à la mise en forme conditionnelle 13) – Fonction nommage de cellules 30 14) – Formule pour appliquer une couleur à vos dates 31 5) – Incrémentation d’une valeur – Insérer automatiquement la date de saisie d’une formule 16) 33 – Masquez les messages d’erreurs de vos formules 17) Personnalisez les calculs de la barre d’état 18) – 34 19) – Raccourci Excel pour effectuer vos calculs intermédiaires Repérez les antécédents d’un calcul 20) – 35 – Résoudre vos erreurs, comprenez d’abord ce que vous dlt 21) Excel 22) – Saisir des formules toujours plus vite Astuces_FormuIes_1 . doc – bitsch. gerard@orange. fr 32 page : 2/ 36 1) – Déterminer le mais d’une date Lorsque vous appliquez le PAGFd OF prié à une date, vous
La date du 9 septembre 2009 est enregistrée par Excel comme étant le chiffre 40065, celle du IO septembre 2009 le 40066, etc. Mais comment faire en sorte que le nom du mois s’affiche en tant que texte dans une autre cellule ? Il est nécessaire pour cela de passer par la formule INDEX. L’une de ses variantes est une formule liée à la fonction MOIS qui vous permettra de lire le mois écrit en entier. Comme dans le tableau ci -dessous, la colonne B représente la date dont il est question, la C, le mois isolé avec la fonction INDEX. Voici la formule. « septembre »; » octobre »; « novembre’ Grâce à la fonction TEXTE, vous pouvez cependant agir plus vite et plus simplement.
Vous donnez ainsi à la date un format personnalisé, dans lequel seul le nom du mois est affiché. Voici à quoi ressemble la formule appropriée. 2) – Fonction ANNEEO Comment faire pour savoir si une année est bissextile ou pas. Imaginons que votre date soit en Al. BI, saisissez la formule bissextile ») et validez par Entrée. Le principe est assez simple : nous demandons à Excel de générer une DATE en lui indiquant l’année contenue en Al et en « forçant » le mois (2) et le jour (29), puis nous calculons le MOIS. Si le résultat est bien égal à 2 (Février) alors nous avons une année bissextile, sinon, eh bien, c’est une année normale ! ) – Convertissez vos dates en « ours de la semaine Dans bien des cas, il peut icher le iour de la semaine PAGF s OF semaine correspondant à une date, plutôt que la date elle-même. Le 22/04/2010 sera alors affiché comme étant jeudi, le 23/04/2010 comme étant vendredi, etc. Vous obtiendrez ce résultat en utilisant un format de cellule personnalisé. Voici comment procéder. Sélectionnez la ou les cellules dont vous souhaitez modifier le ormat. Faites un clic droit, puis choisissez Format de cellule ou combinez les touches Ctrl + 1 du clavier. Dans la boîte de dialogue qui apparait, cliquez sur l’onglet Nombre et choisissez Personnalisée. Dans le champ Type, tapez jljj et valldez en cliquant sur OK. .doc – bitsch. gerard@orange. r page : 3/ 36 Dans la ou les cellules concernées, le résultat apparaît désormais en toutes lettres, comme vous pouvez le voir avec l’illustration ci-contre 4) – Calculez le nombre de jours ouvrés entre deux dates Manipuler les dates sous Excel, quel casse-tête ! Pourtant, le ogiciel offre des outils très puissants pour les travailler. z-vous, par exemple, qu’avec la fonction le tableur calcule pour vous le nombre de jours ouvrés qui sépare deux dates ? Pratique, si l’on connaît le nombre de jours qu’il faut pour réaliser une restation et que l’on doit fournir à un client la date de remi précis de jours. Généralement, lorsque vous entrez une date dans Excel, celle-ci apparaît immédiatement au format déslré.
Mais pour Excel, une date est un nombre comme un autre, qui est simplement présenté différemment. par exemple, la date 1 5/06/2010 correspond au hiffre 40344. Si vous entrez la valeur 40344 dans une cellule et que vous lui appliquez ensuite le format Date (en combinant les touches Ctrl + 1 du clavier), vous obtiendrez 15/06/2010. Si vous ajoutez 1 au chiffre 40344, c’est le jour suivant qui apparaît. Page : 4/ 36 Par exemple, 40345 correspond au 16/06/2010, etc. L’illustration suivante vous donne une idée du résultat La plus petite date qui puisse être représentée sous Excel correspond au chiffre 1: il s’agit du 01101/1900 (ou 1904, selon les réglages choisis).
Cest pour cette raison que les dates antérieures u 01101/1 900 ne peuvent être prises en compte dans les calculs sans étape intermédiaire. Les données horalres sont enregistrées selon une méthode similaire. Elles apparaissent après la virgule, à droite de la valeur correspondant à la date. Dans la mesure où un intervalle d’un jour correspond 1, ce chiffre représente ég Ainsi, la valeur 403445 PAGF 7 OF horaire obtenus par calcul dans un tableur Excel, vous n’obtenez généralement pas le résultat souhaité. En effet, lorsque vous effectuez un calcul d’horaire, Excel considère automatiquement le résultat comme étant également un oraire. Pour contourner ce problème, il suffit de changer l’horaire en chiffre, que vous pourrez ensuite utiliser dans un calcul.
Fini les ennuis de compatibilité de format! Pour ce faire, il vous faudra utiliser les fonctions HEURE() et MINUTE(). Si vous avez le nombre d’heures au format horaire en cellule 86 et le montant du salaire horaire en cellule 37, entrez la formule suivante en cellule B9 pour obtenir le montant total du salaye: Voici le résultat sur l’image ci-contre 7) – Formule pour extraire le mois et l’année à partir d’une date Astuces_Formules_1 . doc – bitsch. erard@orange. fr page : 5/ 36 Vous avez une cellule contenant une date (comme 16/07/201 0) et vous voulez extraire le mois et l’année de la cellule en question, pour obtenir l’affichage juillet 2010?
Utilisez tout simplement la fonction CHOISIR proposée par janvier 2010 en BI, février 201 0 en B2 et ainsi de suite. Dans Excel, vous souhaitez afficher le nombre de jours, de mois et d’années entre deux dates. Vous pouvez utiliser pour cela la fonction DATEDIF. Cette fonction cachée d’Excel peut être très pratique. 1). Placez-vous dans la cellule où vous souhaitez afficher le ombre de jours, de mois ou d’années entre deux dates et utilisez la formule suivante : =DateDif( date_debut; date_fin; « intervalle » ) en remplaçant date_debut par les coordonnées de la cellule qui contient la date de début et date_fin pour les coordonnées de la cellule de la deuxième date. 2).
Remplacez enfin intervalle par l’intervalle de temps dans lequel vous souhaitez le résultat : Intervalle D MD Définition Nombre d’années complètes Nombre de mos complets Nombre de jours Nombre de jours (les mois et les années sont ignorés) Nombre de mois (les jours et les années sont enorés) d’années, de mois et de jours entre deux dates. Utilisez pour cela la formule suivante : -DateDif(A1 ans, mois et jours’i;’i jour ») g) – une formule pour créer une liste des jours de la semaine Il peut s’avérer utile de créer des listes de jours de la semaine, notamment lorsqu’on utilise des calendriers dynamiques. Vous pouvez bien entendu le faire en inscrivant manuellement les jours un par un dans les cellules. Mais vous pouvez choisir de le faire automatiquement grâce à une formule.
Liune d’elles est simplement d’entrer le texte Lundi, puis de aisser Excel remplir automatiquement les cases suivantes en utilisant la « poignée de recopie ». Vous avez également la possibilité de créer une liste des jours de la semaine grâce à une formule. Cela peut être une application utile, notamment dans des calendriers dynamques. Voici comment procéder: Activez une feuille de tableur vide. Si vous avez besoin d’une liste de jours de la semaine allant de droite à gauche dans les cellules AI:GI , entrez la formule suivante dans la cellule Al: page : 7 36 Vous obtenez comme résultat le texte Lundi. Tirez ensuite cette formule iusqu’à la cellule
