Les 100 formules Excel : les fonctions à utiliser /série 10

Bienvenue dans le tutoriel Excel numéro 10 – Formules Excel.

Formule de l’exercice financier (par exemple, 2015-16 ou FY16)

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)

Premier jour ouvrable de l’année

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.

Dernier jour ouvrable de l’année

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.

Convertir de la date Excel (date grégorienne) en julien Date

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”)

Convertir des dates juliennes en dates Excel (grégoriennes)

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.

Extraire le nom d’utilisateur d’un identifiant de messagerie

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),””)

Extraire le nom de domaine d’un identifiant de messagerie

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,””)

Emplacement du premier nombre dans une chaîne

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),””)

Emplacement du dernier numéro dans une chaîne

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),””)

Trouver la valeur de la première cellule non vide dans une plage

=IFERROR(INDEX(A1:A10,MATCH(TRUE,INDEX(NOT(ISBLANK(A1:A10)),,),0)),””).

Trouver la première valeur numérique dans une plage

=IFERROR(INDEX(A1:A100,MATCH(1,INDEX(–ISNUMBER(A1:A100),,),0)),””)

Trouver la dernière valeur numérique dans une plage

=IFERROR(1/LOOKUP(2,1/A1:A100),””)

Trouver la première valeur non numérique dans une plage

=IFERROR(INDEX(A1:A100,MATCH(1,INDEX(–ISTEXT(A1:A100),,),0)),””)

Trouver la dernière valeur non numérique dans une plage

=IFERROR(LOOKUP(REPT(“z”,255),A1:A100),””)

Rechercher la dernière valeur utilisée dans une plage

= IFERROR(LOOKUP(2,1/(A1:A100<>””),A1:A100),””)

MAXIF

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

MINIF

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)

Générer une liste unique à partir d’entrées en double

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.