Fonctions vectorielles ou Array Functions seront des fonctions qui, une fois maîtrisées vous feront non seulement gagner un temps précieux mais vous permettront des réaliser des analyses impossibles avec une utilisation d’Excel classique.

La meilleure manière de vous montrer l’utilité des fonctions vectorielles est probablement de prendre un exemple.

Si vous deviez compter combien de Produits ont le même nom ligne à ligne dans ce tableau, que feriez vous ?

excel11-1

Vous devriez normalement être tenté de

  • faire un test logique ligne à ligne, si le test est positif renvoyer 1, sinon 0. En d’autres termes, il s’agit de faire un =IF(E7=F7;1;0)
  • Sommer le résultat de votre test

Comme cela.

excel11-2

 

Ce que vous permet une fonction vectorielle, c’est de factoriser ces deux opérations en une seule ligne. Jusqu’à présent nous avons considéré un fonctionnement d’Excel cellule par cellule. Désormais les fonctions vectorielles nous permettent de considérer des plages de cellules que nous appellerons Vecteurs.

Dans cet exemple nous allons prendre deux vecteurs, chacun représentant une colonne de notre tableau. Nous allons ensuite tester les valeurs de chaque vecteur, une à une, afin de reconstruire un nouveau vecteur “résultat” dont le contenu sera sommé.

En d’autres termes, nous allons donner une plage de valeur à Excel (Vecteur 1) :

  • {Alpha, Alpha, Alpha, Delta, Beta, Beta, Alpha}

Que nous allons comparer avec une deuxième plage de valeur (Vecteur 2) :

  • {Alpha, Beta, Delta, Delta, Delta, Beta, Alpha}

Dans le cas où la comparaison se trouve être positive, Excel devra nous retourner une valeur 1, sinon 0. Nous obtiendrons alors ce vecteur Résultat :

  • {1, 0, 0, 1, 0, 1, 1}

Il nous suffira de sommer les éléments de ce vecteur pour arriver à notre résultat, 4 !

Pour faire faire à Excel le raisonnement que nous venons de décrire, il suffit d’écrire la formule suivante :

=SUM(IF(E7:E13=F7:F13;1;0))

  • E7:E13 est le vecteur Produit 1
  • F7:F13 est le vecteur produit 2
  • La fonction IF, renverra le vecteur {1, 0, 0, 1, 0, 1, 1}
  • … qui sera ensuite sommé

Si vous entrez directement cette formule, vous aurez à coup sûr une erreur. En effet, nous devons faire comprendre à Excel que nous ne manipulons plus des cellules mais des vecteurs. Il faut donc valider votre formule avec CTRL+MAJ+ENTREE. Valider votre formule de cette manière aura pour effet de rajouter automatiquement des crochets  “{“, “}” de part et d’autre de votre formule.

excel11-3

Ne pas valider sa formule vectorielle avec CTRL+MAJ+ENTREE sera la cause de 90% de vos erreurs. N’oubliez pas que vous pouvez toujours auditer votre formule en utilisant la touche F9.

Vous pouvez ajouter des tests logiques dans votre IF vectoriel en utilisant des opérateurs logiques à la place des AND ou des OR traditionnels:

  • OR(Argument1, Argument2) devient (Argument1)+(Argument 2)
  • AND(Argument1, Argument2) devient (Argument1)*(Argument2)

Par exemple, si nous reprenons le tableau précédent et que nous cherchions le nombre de cas où les produits sont égaux uns à uns mais uniquement si Alpha est le produit 1, nous pourrions écrire :

=SUM(IF((E7:E13=F7:F13)*(E7:E13=”Alpha”);1;0))

  • E7:E13=F7:F13 : est-ce que les produits sont bien égaux uns à uns ?
  • E7:E13=”Alpha” : est-ce que le premier produit est bien égal à Alpha ?
  • Si ces deux conditions sont vérifiées (d’où la présence de *), alors le vecteur résultat prend une valeur 1

excel11-4

N’oubliez pas de valider la formule avec CTRL+MAJ+ENTREE !

Vous l’aurez compris, ici nous utilisons le somme if comme un count if vectoriel, mais nous pouvons également l’utiliser comme un véritable sumif. En reprenant l’exemple précédent, il nous suffit de rajouter une colonne fictive “Chiffre d’Affaires cumulé” et de ne plus renvoyer “1” en cas de succès du test mais de sélectionner ce nouveau vecteur G7:G13:

 

excel11-5

La puissance des fonctions vectorielle est assez énorme et il faudrait un livre entier pour vous donner l’étendue des possibilités offertes par ces fonctions. Après un peu de pratique, vous pourrez vous passer de tableaux croisés dynamiques et ne plus utiliser que des fonctions vectorielles (et ne plus avoir à “rafraîchir vos TCDs”).

Pour aller plus loin, vous pouvez par exemple construire des fonctions qui n’existent pas traditionnellement sur Excel :

  • un Max if vectoriel
  • un Average if vectoriel
  • un somme if sous condition (par exemple on somme une plage de donnée en excluant une valeur avec <>
  • …!

Attention cependant, vos modélisations et analyses sur Excel seront potentiellement auditées par votre manager… et inutile de vous dire qu’auditer une fonction vectorielle est un véritable enfer ! Utilisez les avec parcimonie ou votre méchant manager pourrait vous demander de tout refaire parce qu’il n’y comprend rien.

Article precedent

La lettre de motivation

Prochain article

Pourquoi McKinsey, Bain, ou BCG sont-ils considérés comme les meilleurs cabinets mondiaux?

6 Commentaires

  1. Ludovic
    27 juin 2017 à 18 h 28 min — Répondre

    Bonjour,

    Merci de vos conseils, je suis dans une école parisienne après 2 ans de prépa. Je suis donc “ciblé” par les cabinets de consulting. Cependant, n’étant qu’en 2ème année je n’ose pas me lancer dans des recrutements de stage chez BCG, MK ou Bain. En effet, je ne suis pas encore très à l’aise en entretien et je sais qu’en cas d’échec je serai “blacklisté” durant 2 ou 3 ans. Du coup, que pensez vous d’essayer de décrocher un stage dans un bureau dans un pays “de second rang” (je pense par exemple au BCG à Varsovie ou à Budapest). Je suppose qu’il y a moins de candidatures de stagiaires et j’espère avoir plus de chances de décrocher un stage. Est-ce vrai ?
    Est-ce qu’un stage dans un bureau “exotique” est mal vu en suite/ est considéré comme un moins bon stage qu’à Paris ou Londres ?

    Merci d’avance ! Très bonne journée,

    • 15 juillet 2017 à 16 h 01 min — Répondre

      Bonjour,

      Vous faire recaler pour un stage de 2ème année n’est généralement pas pénalisant pour une candidature en dernière année.
      De plus, les process d’interviews sont plus courts et moins exigeants !
      Vous avez une super opportunité de vous entraîner, si vous voulez limiter les risques, visez les “petits” cabinets.

      Pour une candidature dans d’autres pays que la france, on vous demandera d’être bilingue et il arrive que les entretiens se fassent tout de même depuis le bureau Français. In fine, quel que soit votre stage, rien n’est mal vu ! Une fois encore, à votre niveau, seule l’école compte.

  2. Claire
    28 décembre 2017 à 11 h 18 min — Répondre

    Vos cours d’excel sont tops ! Hate de voir les 2 derniers

    • 28 décembre 2017 à 11 h 26 min — Répondre

      Merci pour votre commentaire !
      J’avoue qu’un autre projet qui devrait voir le jour sur ce site me prend pas mal de temps – j’essayerai de sortir les deux prochains…en 2018 ? 🙂

  3. Clarisse
    21 avril 2019 à 21 h 30 min — Répondre

    Vos tutos excel sont top merci, par ailleurs savoir manier vba est-il désormais indispensable ?

    • 22 avril 2019 à 15 h 42 min — Répondre

      Merci!
      C’est toujours utile, et vu le volume des données à manipuler, des langages comme python pourraient également vous sauver la vie / vous faire passer pour une rock star.
      Au fur et à mesure de votre progression d’analyste à Manager, vous n’aurez plus vraiment besoin de faire ses analyses – juste de savoir vérifier les résultats pour guider vos équipes.

Répondre

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *