Utilisez Ctrl + Maj + Entrée (formules CSE) pour suralimenter vos formules dans Excel! Oui, c'est vrai, il existe une classe secrète de formules dans Excel. Si vous connaissez les trois clés magiques, vous pouvez obtenir une seule formule matricielle Excel pour remplacer des milliers d'autres formules.
95% des utilisateurs d'Excel ne connaissent pas les formules CSE. Lorsque la plupart des gens entendent leur vrai nom, ils pensent que «cela ne semble pas du tout utile» et ne prennent jamais la peine d'en savoir plus sur eux. Si vous pensez que SumIf et CountIf sont cool, vous découvrirez bientôt que les formules Ctrl + Maj + Entrée (CSE) exécuteront des cercles autour de SumIf et CountIf. Les formules CSE vous permettent de remplacer littéralement des milliers de cellules de formules par une seule formule. Oui, mes collègues Excel Guru, il y a quelque chose d'aussi puissant assis juste sous notre nez et nous ne l'utilisons jamais.
Avant SumIf, vous pouviez utiliser une formule CSE pour faire la même chose que SumIf. Microsoft nous a donné SumIf et CountIf, mais les formules CSE ne sont pas obsolètes. Oh non, et ils peuvent faire beaucoup plus! Et si vous voulez faire AverageIf? Qu'en est-il de GrowthIf? AveDevIf? Même MultiplyByPiAndTakeTheSquareRootIf. À peu près tout ce que vous pouvez imaginer peut être fait avec l'une de ces formules CSE.
Voici pourquoi je pense que les formules du CSE n'ont jamais fait leur chemin. Premièrement, leur vrai nom effraie tout le monde. Deuxièmement, ils nécessitent une manipulation étrangère et contre-intuitive pour les faire fonctionner. Après avoir saisi une formule CSE, vous ne pouvez pas simplement appuyer sur Entrée. Vous ne pouvez pas simplement quitter la cellule en cliquant sur une touche fléchée. Même si vous obtenez la bonne formule et appuyez sur la touche Entrée, Excel vous donne le "VALUE!" Totalement non convivial. Erreur. Il ne dit pas: "Wow - c'est beau. Vous êtes à 99,1% du chemin", ce que vous étiez probablement.
Voici le secret: après avoir tapé une formule CSE, vous devez maintenir les touches Ctrl et Maj enfoncées, puis appuyer sur Entrée. Prenez un pense-bête et notez-le: Ctrl Maj Entrée. Les utilisateurs de Power Excel auront la possibilité d'utiliser ces formules environ une fois par mois. Si vous n'écrivez pas Ctrl Maj Entrée vers le bas maintenant, vous l'oublierez au moment où quelque chose se produira à nouveau.
Examinez cet exemple: Supposons que vous vouliez calculer la moyenne uniquement des valeurs de la colonne C où la colonne A était dans la région Est.
La formule de la cellule A13 est un exemple de formule CSE.
=AVERAGE(IF(A2:A10="East",C2:C10))
Branchez-le et vous obtiendrez 7452,667, la moyenne des valeurs de l'Est uniquement. Cool? Vous venez de créer votre propre version de la fonction Excel inexistante AverageIf. N'oubliez pas: appuyez sur Ctrl + Maj + Entrée pour entrer la formule.
Consultez l'exemple suivant ci-dessous.
Dans cet exemple, nous rendons la formule CSE plus générale. Plutôt que de spécifier que nous recherchons "Est", indiquez que vous voulez la valeur de A13. La formule est maintenant =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).
Curieusement, Excel vous permettra de copier et coller des formules CSE sans aucune frappe spéciale. J'ai copié C13 à C14: C15 et j'ai maintenant des moyennes pour toutes les régions.
Notre système mainframe stocke la quantité et le prix unitaire, mais pas le prix calculé. Bien sûr, il est assez facile d'ajouter une colonne C et de la remplir avec =A2*B2la colonne C totale, mais ce n'est pas nécessaire!
Ici, notre formule CSE est =SUM(A2:A10*B2:B10). Il prend chaque cellule dans A2: A10, multiplie par la cellule correspondante dans B2: B10 et totalise le résultat. Tapez la formule, maintenez les touches Ctrl et Maj enfoncées pendant que vous appuyez sur Entrée, et vous répondez dans une formule au lieu de 10.
Voyez-vous à quel point c'est puissant? Même si j'avais 10 000 lignes de données, Excel prendra cette formule unique, fera les 10 000 multiplications et me donnera le résultat.
OK, voici les règles: Vous devez appuyer sur Ctrl + Maj + Entrée à chaque fois que vous entrez ou modifiez ces formules. Si vous ne le faites pas, la #VALUE! Est totalement ambiguë! Erreur. Si vous avez plusieurs plages, elles doivent toutes avoir la même forme générale. Si vous avez une plage mélangée avec des cellules uniques, les cellules individuelles seront «répliquées» en mémoire pour correspondre à la forme de votre plage.
Une fois que vous avez réussi à entrer l'un de ces éléments et à le regarder dans la barre de formule, vous devriez avoir des accolades autour de la formule. Vous n'entrez jamais vous-même dans les accolades. Appuyer sur Ctrl + Maj + Entrée les met là.
Ces formules sont difficiles à maîtriser. a mal à la tête rien qu'en y pensant. Si j'en ai un difficile à saisir, je vais dans Outils> Assistants> Assistant Somme conditionnelle et parcours les boîtes de dialogue. La sortie de l'assistant de somme conditionnelle est une formule CSE, donc cela peut généralement me donner suffisamment d'indices sur la façon d'entrer ce dont j'ai vraiment besoin.
Avez-vous dû passer le BASIC en 11e année avec M. Irwin? Ou, pire encore, avez-vous obtenu un «D» en algèbre linéaire avec Mme Duchesse à l'université? Si tel est le cas, vous êtes en bonne compagnie et vous frissonnerez chaque fois que vous entendrez le mot «tableau». - Je cours en hurlant dans l'autre sens chaque fois que quelqu'un dit tableau. Je les comprends, mais c'est Excel, je n'ai pas besoin de tableaux ici !. Le mauvais secret est qu'Excel et Microsoft appellent ces formules des «formules matricielles». Arrêtez - ne vous enfuyez pas. C'est OK, vraiment. les a renommées formules CSE parce que cela semble moins effrayant, et parce que le nom vous aide à vous rappeler comment les entrer - Ctrl Maj Entrée. Je mentionne uniquement le vrai nom ici au cas où vous rencontriez quelqu'un de Microsoft qui n'a jamais eu Mme Duchesse pour l'algèbre linéaire, ou, au cas où vous décideriez de parcourir les fichiers d'aide. Si vous allez aider,recherche sous le mauvais alias de «formule matricielle», mais entre nous amis, appelons-les CSE - OK?
Lisez Utiliser une formule de tableau Excel spéciale pour simuler SUMIF avec deux conditions.
