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 ?

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.

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.

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

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:

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.