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

COUNTIF pour une plage non contiguë

Nous aimons tous COUNTIF. Et c’est très facile à faire – dites simplement =COUNTIF(“A1:A100″,”>5”) et il trouve toutes les valeurs dans la plage A1 à A100 qui sont supérieures à 5. Mais que se passe-t-il si je voulais le résultat pour seulement A3, A8 et il devrait omettre les autres cellules. Essayez de mettre en suivant
formule –
=COUNTIF((A3, A8),”>5″) et cela vous donnera une erreur #VALUE.
Une solution possible est
=(A3>5)+(A8>5)
Que se passe-t-il si vous devez faire pour A3, A4, A5, A8, A24, A40, A45, A89. Maintenant, vous aurez utiliser une formule comme –
=(A3>5)+(A4>5)+(A5>5)+(A8>5)+(A24>5)+(A40>5)+(A45>5)+(A89>5)
La formule devient lourde à mesure que le nombre de cellules augmente. Dans ce cas, vous pouvez utiliser formule ci-dessous. Cette formule unique peut prendre en charge des plages contiguës (comme A3: A5) et non contiguës à la fois –
=SOMME(NB.SI(INDIRECT({“A3:A5″,”A8″,”A24″,”A40″,”A45″,”A89″}),”>5”))

Compter le nombre de mots dans une cellule/plage

Supposons qu’on vous ait donné ce qui suit et que vous deviez compter le nombre de mots dans une cellule ou dans une plage.
Formule pour calculer le nombre de mots dans une cellule –
=NBARBRE(TRIM(A1))-NBRC(SUBSTITUER(TRIM(A1),” “,””))+(TRIM(A1)<>””)
Formule pour calculer le nombre de mots dans une plage –
=SOMMEPROD(NBCAR(TRIM(A1:A100))-NBRC(SUBSTITUT(TRIM(A1:A100)),”
“,””))+(COUPER(A1:A100)<>””))

Numérologie Somme des chiffres alias Somme des chiffres jusqu’à le résultat est un seul chiffre

En numérologie, il est souvent difficile d’ajouter les chiffres jusqu’à ce que le résultat soit un chiffre unique. Par exemple,
74 = 7 + 4 = 11 = 1 + 1 = 2
23 = 2 + 3 = 5
78 = 7 + 8 = 15 = 1 + 5 = 6
1234567 = 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 = 2+ 8 = 10 = 1+ 0 = 1
La formule pour obtenir le même résultat est
=MOD(A1-1,9)+1

Générer des numéros séquentiels et les répéter

Supposons que vous ayez été chargé de générer une séquence de nombres et de les répéter.
Par exemple –
1,2,3,4,1,2,3,4,1,2,3,4
Vous pouvez utiliser la formule ci-dessous et faire glisser vers le bas –
=MOD(LIGNES($1:1)-1,4)+1
Remplacez 4 par n’importe quel autre nombre pour générer n’importe quelle autre séquence. Par conséquent, si vous voulez pour générer 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4, 5,6,7,8,9,10 puis formule
devient –
=MOD(LIGNES($1:1)-1,10)+1
La structure de la formule est
=MOD(LIGNES($1:1)-1,X)+Y
X – Nombre de numéros
Y – Numéro de départ
En utilisant la formule ci-dessus, vous souhaitez générer la séquence
5,6,7,8,9,10,5,6,7,8,9,10,5,6,7,8,9,10, puis utilisez la formule ci-dessous (vous avez besoin de 6 chiffres et indiquant que le nombre est 5)
=MOD(LIGNES($1:1)-1,6)+5

Répétez un nombre et incrémentez et répétez….

Supposons que vous ayez été chargé de répéter un nombre et d’incrémenter ce nombre et répétez-le. Par exemple –

1,1,1,1,2,2,2,2,3,3,3,3 ….. (Ici, nous le répétons 4 fois et incrémentons et répétons
4 fois encore et ainsi de suite)
Ensuite, vous pouvez utiliser la formule suivante

=ROUNDUP(ROWS($1:1)/4,0)

Supposons que vous souhaitiez commencer le nombre par 5 et non 1, vous pouvez alors utiliser la formule ci-dessous –

=ROUNDUP(ROWS($1:1)/4,0)+4

Par conséquent, la structure générale de la formule est

=ROUNDUP(ROWS($1:1)/X,0)+Y-1

X – Nombre de fois qu’un nombre particulier est répété
Y – Numéros de départ
Par conséquent, si vous voulez commencer par le numéro 7 et que vous voulez le répéter 5 fois, suivez
la formule doit être utilisée

=ROUNDUP(ROWS($1:1)/5,0)+6

Générer des nombres aléatoires non répétitifs via Formule

Supposons que vous souhaitiez générer des nombres aléatoires non répétitifs entre 1 et 30, vous pouvez utiliser la formule suivante dans A2 et faites glisser vers le bas

=IFERROR(AGGREGATE(14,6,ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),
RANDBETWEEN(1,30-ROWS($1:1)+1)),””)

Remarque : $A$1:$A1 fait référence à A2 lorsque vous mettez la formule dans A2 et que vous la faites glisser vers le bas.
Supposons que vous ayez mis la formule dans G4, cela devrait être remplacé par $G$3:$G3.

Si vos numéros de début et de fin sont en B1 et C1, utilisez la formule ci-dessous

=IFERROR(AGGREGATE(14,6,ROW(INDIRECT($B$1&”:”&$C$1))*
NOT(COUNTIF($A$1:$A1,ROW(INDIRECT($B$1&”:”&$C$1)))),
RANDBETWEEN($B$1,$C$1-ROWS($1:1)+1)),””)

Pour les versions antérieures à 2010, la construction de base suivante peut être utilisée (Build error handling selon les versions. Par exemple, Excel 2007 prendra en charge SIERREUR alors que 2003
prend en charge ISERROR) –

=LARGE(INDEX(ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),,),
RANDBETWEEN(1,30-ROW(A1)+1))