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

Bienvenue dans le tutoriel 5 sur les formules Excel.

VLOOKUP de droite à gauche

VLOOKUP recherche toujours de gauche à droite. Par conséquent, dans le tableau ci-dessous, je peux trouver la date de
Naissance de Naomi en donnant la formule suivante –
=VLOOKUP(« Naomi »,B:D,3,0)

Mais, si je dois trouver Emp ID correspondant à Naomi, je ne peux pas le faire via VLOOKUP
formule. Pour effectuer VLOOKUP de droite à gauche, vous devrez utiliser INDEX / MATCH
combinaison. Par conséquent, vous devrez utiliser la formule suivante –
=INDEX(A:A,EQUIV(« Naomi »,B:B,0))

VLOOKUP sensible à la casse

Supposons que vous ayez des données comme le tableau ci-dessous et que vous souhaitiez effectuer une RECHERCHEV sensible à la casse.

Si vous effectuez une VLOOKUP régulière sur SARA, j’obtiendrais la réponse 4300. Mais dans une casse sensible
VLOOKUP, la réponse doit être 3200. Vous pouvez utiliser la formule ci-dessous pour VLOOKUP sensible à la casse.

=INDEX(B2:B10,EQUIV(VRAI,INDEX(EXACT(« SARA »,A2:A10),,),FAUX))

Rang au sein des Groupes

Supposons que vous disposiez de données comme le tableau ci-dessous et que vous souhaitiez connaître le classement des étudiants.

Vous mettrez simplement la formule suivante dans D2
=RANG(C2,C2:C100)
Mais que se passe-t-il si on vous demande de produire le rang des élèves dans chaque école. Dès lors, chaque
le classement de l’école commencera par 1…
Mettez la formule suivante dans D2 pour ce cas pour le classement par ordre décroissant. (Pour monter
commande, remplacer « > » par « < » sans guillemets) =SOMMEPROD((B$2:B$100=B2)*(C$2:C$100>C2))+1
OU
=COUNTIFS(B$2:B$100,B2,C$2:C$100, »> »&C2)+1

Supprimer les alphabets d’une chaîne

Si votre chaîne se trouve dans la cellule A1, utilisez la formule suivante pour supprimer tous les alphabets d’une chaîne.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(LOWER(A1), »a », » »), »b », » »), »c », » »), »d », » »), »e », » »), »f », » »),
« g », » »), »h », » »), »i », » »), »j », » »), »k », » »), »l », » »), »m », » »), »n », » »), »o », » »),
« p », » »), »q », » »), »r », » »), »s », » »), »t », » »), »u », » »), »v », » »), »w », » »), »x », » »), »y », » »), »z », » »)

Supprimer les nombres de la chaîne

Pour supprimer des nombres d’une chaîne (par exemple Vij1aY A. V4er7ma8 contient des nombres qui ne sont pas nécessaires), nous pouvons utiliser la fonction SUBSTITUE imbriquée pour supprimer des nombres. Utiliser
ci-dessous la formule en supposant que la chaîne est dans la cellule A1.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A1,1, » »),2, » »),3, » »),4, » »),5, » »),6, » »),7, » »),8, » »),9, » »),0, » »)

Remarque – Étant donné que cette formule est sur plusieurs lignes, vous devrez donc la copier dans Formule
Bar. Si vous copiez cette formule dans une cellule, elle la copiera sur trois lignes.

Représentation romaine des nombres

Utilisez la fonction ROMAIN.
Ainsi ROMAIN(56) donnera LVI.
ROMAN ne fonctionne que pour les numéros 1 à 3999.

Somme des valeurs N inférieures dans une plage

Supposons que vous ayez des nombres dans la plage A1: A100 et que vous souhaitiez résumer les valeurs N inférieures
=SOMMEPROD(PETIT($A$1:$A$100,LIGNE(1:10))))
Au cas où, vous voudriez ignorer les valeurs 0 (et les blancs)
=SOMMEPROD(PETIT(SI($A$1:$A$100<>0,$A$1:$A$100),LIGNE(1:10)))
Les deux formules ci-dessus ne fonctionneront que s’il y a au moins N valeurs selon ROW(1:N).
Par conséquent, pour les formules ci-dessus, cela ne fonctionnerait que s’il y a au moins 10 nombres dans A1 à
A100.
Pour surmonter cette limitation –
Entrez les formules ci-dessous comme formule matricielle
=SOMME(SIERREUR(PETIT($A$1:$A$100,LIGNE(1:10))),0)

=SOMME(SIERREUR(PETIT(SI($A$1:$A$100<>0,$A$1:$A$100),LIGNE(1:10)),0))
Versions non matricielles des formules ci-dessus (pour Excel 2010 et supérieur)
=SOMMEPROD(AGREGAT(15,6,$A$1:$A$100,LIGNE(1:10))))
=SOMMEPROD(AGREGAT(15,6,$A$1 :$A$100/($A$1 :$A$100<>0),LIGNE(1:10))))

Somme chaque nième ligne

Si vos numéros sont dans la plage A1: A100, utilisez la formule ci-dessous
=SOMMEPROD((A1:A100)(MOD(LIGNE(A1:A100)-LIGNE(A1)+1,2)=0)) La formule ci-dessus est pour chaque 2e rangée. Remplacez 2 par N. Par conséquent, pour chaque 5e ligne – =SOMMEPROD((A1:A100)(MOD(LIGNE(A1:A100)-LIGNE(A1)+1,5)=0))
Ceci est une formule générique et fonctionnera pour n’importe quelle plage. Si votre plage est B7:B50, votre formule
deviendrait
=SOMMEPROD((B7:B50)*(MOD(LIGNE(B7:B50)-LIGNE(B7)+1,2)=0)

Nous avons AVERAGEIF. Qu’en est-il de MEDIANIF et MODEIF ?

Excel ne fournit pas MEDANIF et MODEIF. Vous devrez utiliser des formules Array pour atteindre ces fonctionnalités. Supposons que nos données sont comme ci-dessous.

Pour calculer MEDANIF et MODEIF, entrez les formules ci-dessous, c’est-à-dire pas en appuyant sur ENTER après
Votre formule mais en appuyant sur 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 faire
CTRL + MAJ + ENTRÉE à nouveau. Ne mettez pas { } manuellement.

=MÉDIANE(SI(A2:A13= »M »,B2:B13))
=MODE(SI(A2:A13= »M »,B2:B13))
Alternatives non-Array
Pour MEDIANIF
=AGREGAT(16,6,(B1:B13)/(A1:A13= »m »),50%)
Pour MODEIF
=INDEX(B1:B20,EQUIV(MAX(INDEX((NB.SI(B1:B20,B1:B20))(A1:A20= »m »))),,)),IND EX((NB.SI(B1:B20,B1:B20)(A1:A20= »m »)),,),0))