Les formules de tableau sont un élément crucial de la boîte à outils qui rend Excel polyvalent. Cependant, ces expressions peuvent être décourageantes pour les débutants.
Bien qu’elles puissent paraître compliquées au premier abord, leurs bases sont faciles à saisir. Apprendre les tableaux est pratique car ces formules simplifient la gestion de données Excel complexes.
Qu’est-ce qu’une formule de tableau dans Excel ?
Un tableau est une collection de cellules d’une colonne, d’une ligne ou d’une combinaison rassemblées en un groupe.
Dans Microsoft Excel, le terme formule de tableau fait référence à une famille de formules qui effectuent une ou plusieurs opérations sur une telle plage de cellules, en une seule fois, plutôt qu’une cellule à la fois.
Les versions antérieures d’Excel demandaient aux utilisateurs d’appuyer sur Ctrl + Shift + Enter pour créer une fonction tableau, d’où le nom de fonctions CSE (Ctrl, Shift, Escape), bien que ce ne soit plus le cas pour Excel 365.
Un exemple pourrait être une fonction permettant d’obtenir la somme de toutes les ventes supérieures à 100 $ pour un jour donné. Ou pour déterminer la longueur (en chiffres) de cinq nombres différents stockés dans cinq cellules.
Les formules de tableau peuvent également être le moyen idéal d’extraire des échantillons de données de grands ensembles de données. Elles peuvent utiliser des arguments qui incluent une plage de cellules d’une seule colonne, une plage de cellules d’une seule ligne ou des cellules couvrant plusieurs lignes et colonnes.
Il est également possible d’appliquer des déclarations conditionnelles lors de l’extraction de données dans une formule de tableau, par exemple en extrayant uniquement les nombres supérieurs ou inférieurs à une valeur spécifique ou en extrayant chaque nième valeur.
Ce contrôle granulaire vous permet de vous assurer que vous extrayez un sous-ensemble exact de vos données, que vous filtrez les valeurs de cellules indésirables ou que vous prenez un échantillon aléatoire d’éléments à des fins de test.
Comment définir et créer un tableau dans Excel ?
Les formules de tableau sont, à leur niveau le plus basique, faciles à créer. Pour un exemple simple, nous pouvons considérer une facture pour une commande de pièces détachées d’un client avec les informations suivantes : le SKU (Stock Keeping Unit) du produit, la quantité achetée, le poids cumulé des produits achetés et le prix par unité achetée.
Pour compléter la facture, trois colonnes doivent être ajoutées à la fin du tableau inférieur, une pour le sous-total des pièces, une pour le coût de l’expédition, et la troisième pour le prix total de chaque ligne.
Vous pouvez utiliser une formule simple pour calculer chaque ligne indépendamment. Mais si la facture porte sur de nombreux articles, cela pourrait rapidement devenir beaucoup plus compliqué. Ainsi, le « Sous-total » peut être calculé à l’aide d’une seule formule placée dans la cellule E4 :
=B4:B8 * D4:D8
Les deux nombres que nous multiplions sont des plages de cellules plutôt que des cellules individuelles. Dans chaque ligne, la formule extrait les valeurs de cellule appropriées des tableaux et place le résultat dans la bonne cellule sans autre intervention de l’utilisateur.
Si nous supposons un taux d’expédition fixe de 1,50 $ par livre, nous pouvons calculer notre colonne d’expédition avec une formule similaire placée dans la cellule F4 :
=C4:C8 * B11
Cette fois, nous n’avons utilisé qu’un tableau du côté gauche de l’opérateur de multiplication. Les résultats sont toujours saisis automatiquement, mais chacun est multiplié par une valeur statique.
Enfin, nous pouvons utiliser le même type de formule que pour le sous-total pour obtenir un total en additionnant les tableaux de sous-totaux et de frais d’expédition dans la cellule G4 :
=E4:E8 + F4:F8
Une fois de plus, une simple formule de tableau a permis de transformer en une seule formule ce qui aurait dû en être cinq.
À l’avenir, l’ajout de calculs de taxes à cette feuille de calcul ne nécessiterait qu’une seule formule à modifier, au lieu de modifier chaque ligne.
Gestion des conditionnels dans les tableaux d’Excel
L’exemple précédent utilise des formules arithmétiques de base à travers des tableaux pour produire les résultats, mais les mathématiques simples ne fonctionneront plus pour des situations plus complexes.
Par exemple, supposons que la société qui crée la facture dans l’exemple précédent ait changé de transporteur. Dans ce cas, le prix de l’expédition standard pourrait baisser, mais des frais pourraient être appliqués pour les objets dépassant un certain poids.
Pour les nouveaux tarifs d’expédition, l’expédition standard sera de 1,00 $ par livreet les envois de poids supérieur seront 1,75 $ par livre. L’expédition de colis lourds s’applique à tout ce qui dépasse sept livres.
Plutôt que de revenir au calcul de chaque frais d’expédition ligne par ligne, une formule de tableau peut inclure une instruction conditionnelle IF pour déterminer les frais d’expédition appropriés pour chaque article :
=IF(C4:C8 < 7, C4:C8*B11, C4:C8*B12)
Avec une modification rapide d’une fonction dans une cellule et l’ajout d’un nouveau taux d’expédition dans notre tableau de taux, nous pouvons maintenant voir que non seulement la colonne d’expédition entière est calculée sur la base du poids, mais que les totaux reprennent aussi automatiquement les nouveaux prix.
La facture est donc à l’épreuve du temps, ce qui garantit que toute modification future du calcul des coûts ne nécessitera que la modification d’une seule fonction.
La combinaison de conditionnels multiples ou différents peut nous permettre d’effectuer diverses actions. Cette logique peut être utilisée de plusieurs façons sur un même tableau de données. Par exemple,
- Une fonction IF supplémentaire peut être ajoutée pour ignorer l’expédition si le sous-total est supérieur à un certain montant.
- Un tableau de taxes peut être ajouté pour taxer différentes catégories de produits à des taux différents.
Quelle que soit la complexité de la facture, il suffit de modifier une seule cellule pour recalculer n’importe quelle partie de la facture.
Exemple : Calculer la moyenne des nièmes nombres
En plus de simplifier le calcul et la mise à jour de grandes quantités de données, les formules de tableau permettent d’extraire une tranche d’un ensemble de données à des fins de test.
Comme le montre l’exemple suivant, il est facile d’extraire une tranche de données d’un grand ensemble de données à des fins de validation grâce aux formules de tableau :
Dans l’exemple ci-dessus, la cellule D20 utilise une fonction simple pour calculer la moyenne de chaque nième résultat du capteur 1. Dans ce cas, nous pouvons définir nth en utilisant la valeur de la cellule D19, ce qui nous permet de contrôler la taille de l’échantillon qui détermine la moyenne :
=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16,""))
Cela vous permet de segmenter rapidement et facilement un grand ensemble de données en sous-ensembles.
Maîtriser les formules de tableau dans Excel est essentiel
Ces exemples simples sont un bon point de départ pour comprendre la logique des formules de tableau. Réfléchissez-y afin de pouvoir effectuer rapidement plusieurs opérations aujourd’hui et à l’avenir en modifiant simplement quelques références de cellules et fonctions. Les formules de tableau sont un moyen léger et efficace de découper des données. Maîtrisez-les dès le début de votre apprentissage d’Excel afin de pouvoir effectuer des calculs statistiques avancés en toute confiance.