J’aime donner des trucs et des conseils sur la façon d’utiliser divers programmes informatiques.
Effectuez vos calculs en une seule étape et perdez les colonnes supplémentaires
Si vous faites beaucoup de calculs de nombres dans Excel qui impliquent des tableaux et détestez l’idée d’avoir à créer des colonnes de nombres supplémentaires pour afficher les résultats intermédiaires, alors vous êtes au bon endroit. Il s’avère qu’il existe un moyen plus simple et plus élégant d’effectuer des calculs en plusieurs étapes impliquant des tableaux. Excel permet depuis longtemps l’utilisation de formules conçues par l’utilisateur qui peuvent accepter des tableaux comme arguments, bien que ces fonctionnalités restent encore relativement inconnues, sauf pour les utilisateurs intermédiaires et avancés. Connues sous le nom de formules matricielles, elles sont également parfois appelées formules CSE, car pour qu’elles soient appelées, l’utilisateur doit appuyer sur CTRL SHIFT ENTER (plutôt que sur l’habituel ENTER) après avoir entré la formule. Selon les types de calculs que vous devez effectuer, une formule matricielle peut résider dans une cellule ou dans plusieurs si les résultats consistent en plusieurs valeurs.
Comme indiqué ci-dessous, les formules matricielles vous permettent d’effectuer, en moins d’étapes, de nombreux types de calculs différents non couverts par la liste des fonctions natives d’Excel. Si c’est la première fois que vous utilisez des formules matricielles et que vous souhaitez vous familiariser avec leur utilisation, ce didacticiel d’introduction vous guidera à travers les étapes de base pour effectuer des calculs simples en une étape à l’aide de formules matricielles à cellule unique. En outre, cet article illustre des façons de construire vos propres formules matricielles à l’aide d’exemples simples.
Première étape : vérifier la liste des fonctions natives d’Excel
Les formules matricielles vous permettent de contourner le processus de création de colonnes supplémentaires de résultats intermédiaires dans votre classeur afin que vous puissiez obtenir plus directement le résultat final de vos calculs. Ils facilitent non seulement l’organisation de votre classeur, mais réduisent également sa taille. Il y a cependant un compromis, car les informations contenues dans (ce qui était) ces colonnes supplémentaires sont stockées temporairement dans la mémoire d’Excel. Pour les ordinateurs moins puissants, les formules matricielles peuvent parfois entraîner une baisse substantielle des performances. Cela est plus problématique si vous travaillez avec de très grands ensembles de données ou si vous utilisez de nombreuses formules matricielles et que votre classeur est défini pour des mises à jour automatiques.
Par conséquent, la première étape consiste à vérifier la liste des fonctions natives d’Excel pour voir s’il en existe déjà une qui peut effectuer les calculs que vous avez en tête. Une façon de faire est de vérifier les listes de fonctions dans la fenêtre de dialogue « Insérer une fonction », comme illustré ci-dessus. Une fonction native vous épargnera non seulement le travail supplémentaire de saisie d’une formule matricielle personnalisée, mais elle s’exécutera probablement plus rapidement. Par exemple, supposons que vous souhaitiez calculer la somme des carrés d’une colonne de nombres. Autrefois (vers les années 1990), vous auriez dû créer une colonne supplémentaire de nombres composée des carrés de chaque nombre dans votre ensemble de données d’origine, puis utiliser ces nouvelles valeurs comme argument de tableau dans la fonction SOMME d’Excel. Dans les versions ultérieures d’Excel, la fonction SUMSQ est finalement devenue disponible afin que vous n’ayez plus à effectuer cette étape supplémentaire.
Bien que les versions ultérieures d’Excel intègrent de plus en plus de fonctions natives d’une plus grande complexité qui acceptent des tableaux comme arguments, elles ne peuvent pas anticiper tous les besoins des utilisateurs potentiels. Par exemple, si vous souhaitez effectuer la somme de cubes ou la somme d’un polynôme d’ordre supérieur avec plus d’un terme, les fonctions natives d’Excel n’ont pas encore ces capacités. Pour de telles situations, les formules matricielles sont à peu près la seule alternative viable.
Pour les étapes 2 à 4, j’utiliserai comme exemple la somme des cubes dans un tableau puisque pour Excel 2007, la version que j’utiliserai tout au long de ce didacticiel, une fonction native n’est pas encore disponible.
Deuxième étape : Choisissez la plage de données sur laquelle vous souhaitez effectuer vos calculs
Dans une formule matricielle, vous pouvez soit saisir votre tableau de données sous forme de plage (par exemple, A2:A7), soit lui donner un nom et l’utiliser comme argument.
Lorsque j’utilise des formules matricielles, ma préférence personnelle est de donner un nom à mon tableau de données, qui est la méthode que j’utiliserai tout au long de ce didacticiel. Lorsque vous le faites de cette façon, si jamais vous deviez changer le nombre de lignes ou de colonnes, tout ce que vous auriez à faire est de faire ces changements une seule fois dans « Name Manager », plutôt que d’avoir à retaper et à ressaisir le tableau formule. (Cela minimise également la possibilité d’introduire des erreurs lors de l’édition, en particulier pour les formules matricielles plus compliquées dans lesquelles la plage de données peut apparaître plus d’une fois comme argument.)
Pour nommer votre tableau de données,
- Sélectionnez le tableau de données sur lequel vous souhaitez effectuer vos calculs.
- Cliquez sur l’onglet Formules en haut.
- Dans la zone « Noms définis », cliquez sur « Définir le nom ».
- Dans la fenêtre de dialogue, saisissez le nom de votre baie (par exemple, MyArray).
Remarque : Les instructions ci-dessus s’appliquent à Excel 2007, la version utilisée pour ce didacticiel, et peuvent différer légèrement d’une version d’Excel à l’autre.
Faites défiler pour continuer
Troisième étape – Entrez votre formule matricielle
Ici, nous allons calculer la somme des cubes des nombres dans le tableau de données, A2: A7, que j’ai nommé MyArray (voir la deuxième étape ci-dessus). Les calculs de formule matricielle présentés ici utilisent les fonctions natives d’Excel, SOMME et PUISSANCE.
- Sélectionnez la cellule dans laquelle vous souhaitez que votre formule matricielle réside. (C’est ici que le résultat final sera affiché.)
- Entrez le signe égal (=).
- Juste après le signe =, entrez votre formule dans la barre de formule. Puisque nous calculons la somme des cubes pour chaque élément dans MyArray, entrez ce qui suit : SUM(POWER(MyArray,3))
- Appuyez simultanément sur les boutons CTRL + MAJ + ENTRÉE. Vous devriez voir les résultats dans la cellule dans laquelle vous avez entré votre formule matricielle (indiquée ci-dessus en rouge). Vous devriez également voir des accolades autour de la formule que vous venez de saisir dans la barre de formule, comme indiqué dans la capture d’écran ci-dessus.
Étape 4 : Testez votre formule matricielle
- Cliquez sur la cellule dans laquelle vous avez entré votre formule matricielle et vérifiez qu’elle est entourée d’accolades dans la barre de formule.
- Apportez quelques modifications dans une ou plusieurs cellules de votre plage et vérifiez que la réponse finale correcte s’affiche dans la cellule dans laquelle vous avez saisi votre formule matricielle.
Comme indiqué dans la capture d’écran ci-dessus, en augmentant la valeur de A7 de 6 à 60, la somme des cubes, comme indiqué dans la cellule B2, se met à jour correctement de 441 (la valeur d’origine indiquée ci-dessus à l’étape 3) à 216225.
Un avertissement
Si vous oubliez d’appuyer sur CTRL + MAJ + ENTRÉE et appuyez accidentellement sur ENTRÉE à la place, vous obtiendrez un message d’erreur ou, pire, des résultats erronés, selon votre version d’Excel. Puisqu’il est facile d’oublier, vérifiez toujours la barre de formule pour ces accolades !
Comment calculer le nombre total de caractères dans une collection de cellules ?
Pour cet exemple, les données résident dans les cellules A1-A6 (voir capture d’écran), donc MyArray = A1:A6. La formule matricielle pour calculer le nombre total de caractères dans ces cellules (et le résultat final) réside dans la cellule B1 (encadrée en rouge).
Le nombre de caractères pour toute chaîne résidant dans la cellule A1 est facilement trouvé à partir de la fonction LEN, c’est-à-dire,
LEN(A1)
Pour trouver le nombre total de caractères pour chaque membre d’un tableau, remplacez le nom ou la plage du tableau par « A1 » dans la formule ci-dessus et utilisez-le comme argument pour la fonction SOMME, c’est-à-dire,
SOMME(NBCF(MonTableau))
En saisissant ce qui précède sous forme de formule matricielle, vous devriez voir ce qui suit dans la barre de formule :
{=SUM(LEN(MonTableau))}
Comment puis-je déterminer le nombre total de mots dans une collection de cellules avec des chaînes ?
Pour cet exemple, les données résident dans les cellules C1-C3, donc MyArray = C1:C3. La formule matricielle pour calculer le nombre total de mots dans ces trois cellules (et le résultat final) réside dans la cellule D1 (encadrée en rouge).
Pour une seule cellule, C1, le nombre de mots peut être trouvé en comptant le nombre de fois que le caractère d’espace apparaît et en ajoutant 1, c’est-à-dire,
LEN(C1)-LEN(SUBSTITUE(C1, » « , » »))+1
Pour trouver le nombre total de mots pour chaque cellule d’un tableau, remplacez le nom du tableau ou la plage par « C1 » dans la formule ci-dessus et utilisez-le comme argument pour la fonction SOMME, c’est-à-dire,
SOMME(NBCAR(MonTableau)-NBCAR(SUBSTITUTION(MonTableau, » « , » »))+1)
En saisissant ce qui précède sous forme de formule matricielle à l’aide de CTRL + MAJ + ENTRÉE, vous devriez voir ce qui suit dans la barre de formule (comme indiqué dans la capture d’écran ci-dessus):
{=SUM(LEN(MonTableau)-LEN(SUBSTITUE(MonTableau, » « , » »))+1)}
Je ne peux rien saisir dans la barre de formule car la boîte de dialogue « Insérer une fonction » d’Excel continue de me gêner !
Ne cliquez pas sur l’icône « Insérer une fonction » à gauche de la barre de formule (représentée ici barrée en rouge). Au lieu de cela, sélectionnez la cellule dans laquelle vous souhaitez que votre formule réside et tapez un signe égal (=).
Le signe égal apparaîtra alors dans la barre de formule, afin que vous puissiez saisir la formule de votre choix.
Cet article est exact et fidèle au meilleur de la connaissance de l’auteur. Le contenu est uniquement à des fins d’information ou de divertissement et ne remplace pas un conseil personnel ou un conseil professionnel en matière commerciale, financière, juridique ou technique.
Pensez-vous que les formules matricielles Excel pourraient être utiles dans votre travail ?
othellos le 06 juillet 2013 :
Très bon objectif sur les formules Excel. Votre méthode suggérée m’est très utile car j’utilise beaucoup de formules dans mon travail. Je n’ai jamais pensé à les faire de cette façon. Bons conseils. Merci beaucoup :=)
Susan R. Davis de Vancouver le 31 octobre 2012 :
Oui, et j’apprécie l’excellent tutoriel.
JaneEyre9999 le 10 mai 2012 :
Oui! Merci, peu importe depuis combien de temps nous utilisons un logiciel de tableur, nous restons tous coincés dans nos vieux « trucs ».
pas de bonnes affaires le 13 mars 2012 :
Les plus difficiles sont décalés et indirects. Bill Jelen avait les meilleurs livres Excel
anonyme le 09 mars 2012 :
Tous les utilisateurs d’Excel doivent aimer cet objectif et vous… chère Mary Stuart 🙂 Un autre travail magnifique et utile de votre part. 5 étoiles. Passez de merveilleux moments .. toujours .. chère Mary 😀
Beverly Rodriguez d’Albany New York le 06 mars 2012 :
Article très bien fait et complet !
SophiaStar LM le 06 mars 2012 :
Il va falloir que je garde ça sous la main, Excel n’est pas mon point fort ! Merci pour ces supers conseils !
Sonia Carew le 06 mars 2012 :
Merci! C’est de loin mon objectif préféré car je n’aime pas Excel mais je dois l’utiliser au quotidien. Cet objectif va tellement m’aider… Merci, merci !
anonyme le 06 mars 2012 :
Ça tombe bien j’avais un ami qui me parlait hier d’Excel
Itaya Lightbourne de Topeka, KS le 05 mars 2012 :
Excellent article et sera certainement utile! 🙂
Susan Deppner de l’Arkansas USA le 05 mars 2012 :
Apparemment des informations très complètes !
Traceur1 LM le 05 mars 2012 :
Oui, je travaille souvent avec Excel et cet objectif est très instructif et utile. Merci et excellent travail !
