ARRAYFORMULA dans Excel : Guide Complet

Introduction

La fonction ARRAYFORMULA (ou son équivalent dans Excel, l'entrée matricielle) est un outil puissant pour effectuer des calculs sur des plages de cellules entières sans avoir à recopier la formule individuellement dans chaque cellule. Elle permet d'automatiser les opérations et de simplifier les feuilles de calcul complexes. Dans Excel, on utilise la validation matricielle (Ctrl+Maj+Entrée) au lieu d'une fonction explicite ARRAYFORMULA.

Syntaxe

Dans Excel, la syntaxe n'est pas une fonction ARRAYFORMULA mais une validation matricielle :

1. Sélectionnez la plage de cellules où vous souhaitez afficher les résultats.
2. Tapez votre formule.
3. Au lieu d'appuyer sur Entrée, appuyez sur Ctrl + Maj + Entrée.

Excel encadrera automatiquement la formule avec des accolades {}. Vous ne devez pas taper ces accolades manuellement.

Fonctionnement

Lorsque vous validez une formule matricielle (avec Ctrl+Maj+Entrée), Excel applique la formule à chaque cellule correspondante dans la plage d'entrée et affiche les résultats dans la plage de sortie sélectionnée. Si la formule renvoie un tableau de résultats, chaque élément du tableau est affiché dans une cellule de la plage de sortie.

Cas d'utilisation

• Calcul de commissions : Calculer la commission de chaque vendeur en fonction de son chiffre d'affaires. La formule peut multiplier une plage de chiffres d'affaires par un taux de commission.
• Analyse de données financières : Calculer des ratios financiers pour plusieurs entreprises simultanément à partir de leurs bilans.
• Gestion des stocks : Calculer la valeur totale de chaque produit en stock en multipliant une plage de quantités par une plage de prix unitaires.
• Traitement de données RH : Calculer l'ancienneté de chaque employé à partir d'une plage de dates d'embauche.

Bonnes pratiques

• Dimensionnement correct : Assurez-vous que la plage de sortie est suffisamment grande pour contenir tous les résultats de la formule matricielle. Sinon, Excel affichera une erreur #SPILL!.
• Modification des formules : Pour modifier une formule matricielle, vous devez sélectionner toute la plage de sortie et appuyer sur F2, modifier la formule, puis valider avec Ctrl+Maj+Entrée. Vous ne pouvez pas modifier une seule cellule de la plage de sortie.
• Évitez les références circulaires : Les formules matricielles peuvent entraîner des références circulaires si elles sont mal conçues.

Combinaisons

Les formules matricielles se combinent très bien avec d'autres fonctions Excel pour créer des calculs complexes. Par exemple:

• INDEX et MATCH : Pour rechercher des valeurs dans des tableaux en fonction de critères complexes.
• SUM et IF : Pour effectuer des sommes conditionnelles sur des plages de données.
• TRANSPOSE : Pour transposer des tableaux de données.
• FREQUENCY : Pour calculer la fréquence de valeurs dans un ensemble de données.
• AVERAGEIFS : Pour calculer la moyenne d'une plage en fonction de plusieurs critères.