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

Dans ce tutoriel Excel, nous exposons les meilleures formules Excel pour les pros et les étudiants.

La fonction Count : Compter les nombres dans une chaîne :

Supposons que vous ayez une chaîne « abc123def43cd » et que vous souhaitiez y compter des nombres.
Si votre chaîne est en A1, utilisez la formule suivante –
=SOMMEPROD(NBCAR(A1)-NBCAR(SUBSTITUT(A1,LIGNE(1:10)-1, » »)))
OU
=SOMMEPROD(–ESTNUM((–MID(A1,LIGNE(INDIRECT(« 1: »&NBCF(A1))),1))))

La fonction Count : comptez uniquement les alphabets dans une chaîne

Supposons que vous ayez une chaîne « Ab?gh123def%h* » et que vous souhaitiez ne compter que les Aphabets.
Supposons que votre chaîne soit en A1, mettez la formule suivante pour cela.
=SOMMEPROD(NBRC(A1)-
LEN(SUBSTITUE(UPPER(A1),CAR(ROW(INDIRECT(« 65:90″))), » »)))
OU
=SOMMEPROD(–(ABS(77.5-
CODE(MILIEU(SUPER(A1),LIGNE(INDIRECT(« A1:A »&LEN(A1))),1)))<13))

Valeur la plus fréquente dans une plage

En supposant que votre plage est A1: A10, entrez la formule ci-dessous comme formule matricielle, c’est-à-dire pas par
en appuyant sur ENTER après avoir entré votre formule mais en appuyant sur CTRL+SHIFT+ENTER. Cette volonté
placez des crochets {} autour de la formule que vous pouvez voir dans la barre de formule. Si vous modifiez à nouveau, vous
devra faire CTRL + MAJ + ENTRÉE à nouveau. Ne mettez pas { } manuellement.
=INDEX(A1:A10,EQUIV(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0))
La version non-Array de la formule ci-dessus
=INDEX(A1:A10,EQUIV(MAX(INDEX(COUNTIF(A1:A10,A1:A10),,)),INDEX(COUNTIF(A1:
A10,A1:A10),,),0))

COUNTIF sur la liste filtrée

Vous pouvez utiliser SUBTOTAL pour effectuer COUNT sur une liste filtrée mais COUNTIF ne peut pas être fait
sur une liste filtrée. La formule ci-dessous peut être utilisée pour effectuer COUNTIF sur une liste filtrée
=SOMMEPROD(SOUS-TOTAL(3,DECALAGE(B2,LIGNE(B2:B20)-LIGNE(B2),))*(B2:B20>14))

Ici B2:B20>14 est comme un critère dans COUNTIF (=COUNTIF(B2:B20, »>14″))

SUMIF sur la liste filtrée

Vous pouvez utiliser SOUS-TOTAL pour effectuer SUM sur une liste filtrée mais SUMIF ne peut pas être effectué sur une liste
liste filtrée. La formule ci-dessous peut être utilisée pour effectuer SUMIF sur une liste filtrée
=SOMMEPROD(SOUS-TOTAL(9,DECALAGE(B2,LIGNE(B2:B20)-LIGNE(B2),))*(B2:B20>14))
Ici B2:B20>14 est comme un critère dans SUMIF.

Extraire le prénom du nom complet

=GAUCHE(A1,CHERCHE( » « ,A1& » « )-1)

Extraire le nom de famille du nom complet
=TRIM(RIGHT(SUBSTITUTE(A1, » « ,REPT( » « ,LEN(A1))),LEN(A1)))

Extraire l’initiale du deuxième prénom

Supposons que vous ayez un nom John Doe Smith et que vous souhaitiez afficher D comme initiale du milieu.
En supposant que vos données sont en A1, vous pouvez utiliser la formule suivante
=SI(NB.SI(A1, »* * * »),MID(A1,TROUVER( » « ,A1)+1,1), » »)
Si le nom est de 2 ou 1 mots, le résultat sera vide. Cela fonctionne sur le nom de 3 mots seulement comme
le milieu ne peut être décidé que pour le nom de 3 mots.

Extraire le deuxième prénom du nom complet

=SI(NB.SI(A1, »* * « ),MID(A1,TROUVER( » « ,A1)+1,TROUVER( » « ,A1,TROUVER( » « ,A1)+1)-(TROUVER( » « ,A1)+1)), » ») =IF(COUNTIF(A1, » * « ),TRIM(MID(SUBSTITUTE(A1, » « ,REPT( » « ,LEN(A1)),2),FIND( » « ,A1)+1,NBCAR(A1))), » ») =SI(COUNTIF(A1, » * * »),GAUCHE(REPLACE(A1,1,TROUVER( » « ,A1), » »),TROUVER(« 
« , REMPLACER (A1,1, TROUVER( » « ,A1), » »))-1))

Supprimer le deuxième prénom dans le nom complet

=SI(COUNTIF(A1, »* * « ),GAUCHE(A1,TROUVER( » « ,A1& » « ))&TRIM(DROITE(SUBSTITUER(A1, » « ,REPT( » « ,NBCAR(A1))),NBCAR(A1))), » ») =SI(NB.SI(A1, » * * »),REPLACE(A1,TROUVER( » « ,A1)+1,TROUVER( » « ,A1,TROUVER( » « ,A1)+1)-TROUVER(« 
« ,A1), » »), » »)

Extraire la partie entière et décimale d’un nombre

Pour extraire la partie entière, l’un des éléments ci-dessous peut être utilisé –
=INT(A1)
=TRONC(A1)
Valeur positive dans A1 – Si A1 contient 84,65, alors la réponse serait 84.
Valeur négative dans A1 – Si A1 contient -24,39, alors la réponse serait -24.
Si vous ne voulez que la valeur +ve, que la valeur dans A1 soit -ve ou +ve, la formule peut avoir plusieurs
variantes.
=INT(A1)SIGN(A1) OU =TRUNC(A1)SIGN(A1)
=INT(ABS(A1)) OU =TRUNC(ABS(A1))
=ABS(INT(A1)) OU = ABS(TRUNC(A1))
Pour extraire la partie décimale –
=MOD(ABS(A1),1)
=ABS(A1)-INT(ABS(A1))
Valeur positive dans A1 – Si A1 contient 84,65, alors la réponse serait 0,65.
Valeur négative dans A1 – Si A1 contient -24,39, alors la réponse serait 0,39.

Premier jour du mois pour une date donnée

Supposons qu’on vous ait donné une date, disons le 22/10/14 (MM/JJ/AA) et que vous vouliez calculer
le premier jour du mois en cours. Par conséquent, vous souhaitez obtenir un résultat du 01/10/2014
(MM/JJ/AA).
Les formules à utiliser –
=DATE(ANNEE(A1),MOIS(A1),1)
=A1-JOUR(A1)+1
=EOMOIS(A1,-1)+1