Exercice 1 : Calcul des Écarts sur Volume et Prix Contexte...
Bienvenue dans le tutoriel Excel numéro 10 – Formules Excel.
Un bon nombre de pays ne suivent pas l’année civile comme année financière. Par exemple,
L’année financière de l’Inde commence le 1er avril et se termine le 31 mars. Ainsi, actuellement (20-fév-16),
l’exercice financier est 2015-16 (il est également écrit comme FY16). Le 1er avril 2016, il deviendra 2016-
17 (Il est également écrit comme FY17).
Maintenant, si une date est donnée, la formule suivante peut être utilisée pour dériver le type de résultat 2015-16.
=YEAR(A1)-(MONTH(A1)<=3)&”-“&YEAR(A1)+(MONTH(A1)>3)
Pour générer le type de résultat FY16, la formule suivante peut être utilisée
=”FY”&RIGHT(YEAR(A1)+(MONTH(A1)>3),2)
Si une année est donnée en A1, disons 2016, la formule ci-dessous peut être utilisée pour connaître le premier jour ouvrable de l’année (formater le résultat en date)
=WORKDAY(EOMONTH(“1JAN”&A1,-1),1)
La formule ci-dessus suppose que vos week-ends sont le samedi et le dimanche.
Mais, si vos week-ends sont différents (par exemple dans les pays du golfe), vous pouvez utiliser la formule suivante
=WORKDAY.INTL(EOMONTH(“1JAN”&A1,-1),1,”0000110”)
Où 0000110 est une chaîne de 7 caractères, 1 représente un week-end et 0 est un jour ouvrable.
le chiffre est le lundi et le dernier chiffre est le dimanche. L’exemple ci-dessus concerne les pays du Golfe où le vendredi et le samedi sont les week-ends.
Vous avez également la possibilité de donner une plage qui a des jours fériés. Dans ce cas, votre formule serait :
=WORKDAY(EOMONTH(“1JAN”&A1,-1),1,D1:D10)
=WORKDAY.INTL(EOMONTH(“1JAN”&A1,-1),1,”0000110”,D1:D10)
Où la plage D1:D10 contient la liste des jours fériés.
Si une année est donnée en A1, disons 2016, la formule ci-dessous peut être utilisée pour connaître le dernier jour ouvrable de l’année (formater le résultat en date)
=WORKDAY(“1JAN”&A1+1,-1)
La formule ci-dessus suppose que vos week-ends sont le samedi et le dimanche.
Mais, si vos week-ends sont différents (par exemple dans les pays du Golfe), vous pouvez utiliser la formule suivante
=WORKDAY.INTL(“1JAN”&A1+1,-1,”0000110”)
Où 0000110 est une chaîne de 7 caractères, 1 représente un week-end et 0 est un jour ouvrable. Première
le chiffre est le lundi et le dernier chiffre est le dimanche. L’exemple ci-dessus concerne les pays du Golfe où
Le vendredi et le samedi sont les week-ends.
Vous avez également la possibilité de donner une plage qui a des jours fériés. Dans ce cas, votre formule devient :
=WORKDAY(“1JAN”&A1+1,-1,D1:D10)
=WORKDAY.INTL(“1JAN”&A1+1,-1,”0000110”,D1:D10)
Où la plage D1:D10 contient la liste des jours fériés.
Q. D’abord, qu’est-ce qu’une date julienne ?
A. Une date julienne a une date à 7 chiffres ou à 5 chiffres et ceux-ci sont généralement utilisés dans l’ancienne informatique
systèmes hérités.
7 chiffres – AAAAJJJ – 2016092 (il s’agit du 1er avril 2016. 92 signifie qu’il s’agit du 92e jour à partir du 1-
janvier de cette année-là)
5 chiffres – AAJJJ – 16092
Q. Quelles formules utiliser pour convertir les dates Excel en dates juliennes ?
A. Pour 7 chiffres, utilisez la formule suivante
=TEXT(A1,”yyyy”)&TEXT(A1-(“1JAN”&YEAR(A1))+1,”000”)
Pour 5 chiffres, utilisez la formule suivante
=TEXT(A1,”yy”)&TEXT(A1-(“1JAN”&YEAR(A1))+1,”000”)
Pour les dates juliennes à 7 chiffres, la formule suivante doit être utilisée
=DATE(LEFT(A1,4),1,RIGHT(A1,3))
Pour les dates juliennes à 5 chiffres, la formule suivante doit être utilisée en fonction du siècle (Remarque – les dates juliennes sont plus susceptibles de tomber au 20e siècle)
21e siècle
=DATE(20&LEFT(A1,2),1,RIGHT(A1,3))
20ième siècle
=DATE(19&LEFT(A1,2),1,RIGHT(A1,3))
Remarque – 19 ou 20 peuvent être remplacés par une condition IF pour mettre à droite 19 ou 20 selon sur l’année. Par exemple, l’année 82 est plus susceptible d’être au 20ème siècle où l’année 15 est plus susceptibles d’être au 21e siècle.
En supposant que A1 a un identifiant de messagerie, dites A1:=v.a.verma@gmail.com et vous devez récupérer
v.a.verma qui est le nom d’utilisateur dans l’ID de messagerie. Utilisez la formule suivante –
=IFERROR(LEFT(A1,SEARCH(“@”,A1)-1),””)
Si vous souhaitez récupérer le nom de domaine qui, dans l’exemple ci-dessus, est gmail.com, utilisez ce qui suit
formule –
=REPLACE(A1,1,SEARCH(“@”,A1)+1,””)
Supposons que vous ayez A1 : = “abfg8ty#%473hj” et que vous vouliez trouver quelle est la position du premier
nombre là-dedans. Dans cette chaîne, le premier nombre est 8 et sa position est 5. Vous pouvez utiliser ce qui suit
formule –
=IFERROR(AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT(“1:”&LEN(A1)
))),1),””)
Dans l’exemple ci-dessus, le dernier numéro est 3 et sa position est 12. Vous pouvez utiliser la formule suivante
pour trouver ça –
=IFERROR(AGGREGATE(14,6,FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT(“1:”&LEN(A1)
))),1),””)
=IFERROR(INDEX(A1:A10,MATCH(TRUE,INDEX(NOT(ISBLANK(A1:A10)),,),0)),””).
=IFERROR(INDEX(A1:A100,MATCH(1,INDEX(–ISNUMBER(A1:A100),,),0)),””)
=IFERROR(1/LOOKUP(2,1/A1:A100),””)
=IFERROR(INDEX(A1:A100,MATCH(1,INDEX(–ISTEXT(A1:A100),,),0)),””)
=IFERROR(LOOKUP(REPT(“z”,255),A1:A100),””)
= IFERROR(LOOKUP(2,1/(A1:A100<>””),A1:A100),””)
Remarque – Excel 2016 a introduit la fonction MAXIFS
Supposons que vous souhaitiez trouver les ventes maximales pour la région Est, c’est-à-dire MAXIF
=SOMMEPROD(MAX((A2:A100=”Est”)(B2:B100))) =AGREGATE(14,6,($A$2:$A$100=”Est”)($B$2:$B$100),1)
La formule SOMMEPROD est plus rapide que la deuxième formule
Remarque – Excel 2016 a introduit la fonction MINIFS
Supposons que vous souhaitiez trouver les ventes minimales pour la région ouest, c’est-à-dire MINIF
=AGGREGATE(15,6,1/($A$2:$A$10=”West”)*($B$2:$B$10),1)
Mais la formule ci-dessus n’ignorera pas les blancs ou les valeurs 0 dans votre plage. Si vous voulez ignorer
0 valeurs/blancs, dans votre plage, puis utilisez la formule suivante.
=AGGREGATE(15,6,1/(($A$2:$A$10=”West”)*($B$2:$B$10<>0))*($B$2:$B$10),1)
Supposons que vous ayez des entrées dans A2:A100 et que vous souhaitiez générer une liste contenant uniquement
entrées uniques dans la colonne C à partir de C2. Vous pouvez utiliser la formule suivante dans C2 et faire glisser vers le bas
la formule –
Cas 1 – A2 : A100 ne contient aucun espace
=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($C$1:$C1,$A$2:$A$100),0,0),
)),””)
Cas 2 – A2 : A100 contient des blancs. Dans ce cas, vous devrez utiliser la formule Array.
=IFERROR(INDEX($A$2:$A$100, MATCH(0, IF($A$2:$A$100<>””,COUNTIF(C1:$C$1, $A$2:$A$100)), 0)),””)
Ou
=IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($C$1:$C1,$A$2:$A$100&””),0)),””)
Remarque – La formule matricielle n’est pas saisie en appuyant sur ENTREE après avoir saisi votre formule, mais en appuyant sur CTRL+MAJ+ENTRÉE. Si vous copiez et collez cette formule, prenez F2 après coller et CTRL+MAJ+ENTRÉE. Cela mettra { } crochets autour de la formule que vous pouvez voir dans la barre de formule. Si vous modifiez à nouveau, vous devrez à nouveau faire CTRL + MAJ + ENTRÉE.
Ne mettez pas { } manuellement.
Voici une série d’exercices conçus pour perfectionner vos compétences Excel. Les corrigés sont inclus pour…
Excel offre plusieurs méthodes pour calculer une moyenne tout en tenant compte des filtres ou…
Excel propose plusieurs fonctions pour insérer ou manipuler la date actuelle. Voici les principales méthodes…
Lorsque des nombres sont stockés sous forme de texte dans Excel, ils ne peuvent pas…
Extraire uniquement les chiffres d'une cellule contenant du texte et des nombres mélangés est une…
Pour supprimer plusieurs caractères spécifiques (par exemple, des symboles, chiffres ou lettres indésirables) dans des…
This website uses cookies.