Les formules de tableau Excel sont super puissantes. Vous pouvez remplacer des milliers de formules par une seule formule une fois que vous avez appris l'astuce Ctrl + Maj + Entrée. Aujourd'hui, une seule formule matricielle effectue 86 000 calculs.
La triskaidekaphobie est la peur du vendredi 13. Ce sujet ne guérira rien, mais il vous montrera une formule absolument incroyable qui remplace 110 268 formules. Dans la vraie vie, je n'ai jamais besoin de compter combien de vendredi 13 se sont produits dans ma vie, mais la puissance et la beauté de cette formule illustre la puissance d'Excel.
Dites que vous avez un ami qui est superstitieux à propos du vendredi 13. Vous voulez illustrer combien de vendredi 13 votre ami a vécu.
Configurez la feuille de calcul simple ci-dessous, avec la date de naissance en B1 et =TODAY()en B2. Ensuite, une formule sauvage dans B6 évalue chaque jour que votre ami a été en vie pour déterminer combien de ces jours étaient le vendredi et tombaient le 13 du mois. Pour moi, le nombre est 86. Rien à craindre.
Au fait, le 17/02/1965 est vraiment mon anniversaire. Mais je ne veux pas que tu m'envoies une carte d'anniversaire. Au lieu de cela, pour mon anniversaire, je veux que vous me laissiez expliquer comment fonctionne cette formule étonnante, une petite étape à la fois.
Avez-vous déjà utilisé la fonction INDIRECT? Lorsque vous le demandez =INDIRECT("C3"), Excel accède à C3 et renvoie tout ce qui se trouve dans cette cellule. Mais INDIRECT est plus puissant lorsque vous calculez la référence de cellule à la volée. Vous pouvez configurer une roue de prix où quelqu'un choisit une lettre entre A et C, puis choisit un nombre entre 1 et 3. Lorsque vous concaténez les deux réponses, vous aurez une adresse de cellule, et tout ce qui se trouve à cette adresse de cellule est le prix . On dirait que j'ai gagné un livre photo au lieu du séjour au complexe.
Savez-vous comment Excel stocke les dates? Quand Excel vous montre le 17/02/1965, il stocke 23790 dans la cellule, car le 17/02/1965 était le 23790e jour du 20e siècle. Au cœur de la formule se trouve une concaténation qui joint la date de début et un signe deux-points et la date de fin. Excel n'utilise pas la date formatée. Au lieu de cela, il utilise le numéro de série dans les coulisses. Ainsi B3&":"&B4devient 23790: 42167. Croyez-le ou non, c'est une référence de cellule valide. Si vous souhaitez tout additionner dans les lignes 3 à 5, vous pouvez utiliser =SUM(3:5). Ainsi, lorsque vous passez 23790: 42167 à la fonction INDIRECT, il pointe sur toutes les lignes.
La prochaine chose que fait la formule tueuse est de demander le ROW(23790:42167). Normalement, vous passez une seule cellule: =ROW(D17)soit 17. Mais dans ce cas, vous passez des milliers de cellules. Lorsque vous demandez ROW(23790:42167)et terminez la formule avec Ctrl + Maj + Entrée, Excel renvoie en fait tous les nombres de 23790, 23791, 23792, et ainsi de suite jusqu'à 42167.
Cette étape est une étape incroyable. Dans cette étape, nous partons de deux nombres et «sortons» un tableau de 18378 nombres. Maintenant, nous devons faire quelque chose avec cette gamme de réponses. La cellule B9 de la figure précédente ne compte que le nombre de réponses que nous obtenons, ce qui est ennuyeux, mais cela prouve que cela ROW(23790:42167)renvoie 18378 réponses.
Simplifions considérablement la question initiale afin que vous puissiez voir ce qui se passe. Dans ce cas, nous trouverons le nombre de vendredis en juillet 2015. La formule ci-dessous en B7 fournit la bonne réponse en B6.
Au cœur de la formule se trouve ROW(INDIRECT(B3&":"&B4)). Cela va renvoyer les 31 dates en juillet 2015. Mais la formule passe ensuite ces 31 dates à la WEEKDAY(,2)fonction. Cette fonction renverra un 1 pour lundi, 5 pour vendredi, et ainsi de suite. La grande question est donc de savoir combien de ces 31 dates renvoient un 5 lorsqu'elles sont passées à la WEEKDAY(,2)fonction.
Vous pouvez regarder la formule calculer au ralenti à l'aide de la commande Evaluer la formule de l'onglet Formule du ruban.
C'est après que INDIRECT a converti les dates en référence de ligne.
À l'étape suivante, Excel est sur le point de transmettre 31 numéros à la fonction WEEKDAY. Maintenant, dans la formule du tueur, il passerait 18 378 numéros au lieu de 31.
Voici les résultats des 31 fonctions WEEKDAY. N'oubliez pas que nous voulons compter combien sont 5.
Vérifier si le tableau précédent est 5 renvoie tout un tas de valeurs Vrai / Faux. Il existe 5 valeurs vraies, une pour chaque vendredi.
Je ne peux pas vous montrer ce qui se passe ensuite, mais je peux l'expliquer. Excel ne peut pas additionner un tas de valeurs True et False. C'est contraire aux règles. Mais si vous multipliez ces valeurs True et False par 1 ou si vous utilisez la fonction double négatif ou N (), vous convertissez les valeurs True en 1 et les valeurs False en 0. Envoyez-les à SUM ou SUMPRODUCT, et vous allez obtenir le nombre de valeurs True.
Voici un exemple similaire pour compter combien de mois ont un jour 13 en eux. C'est simple à penser: chaque mois a un 13, donc la réponse pour une année entière vaut mieux être 12. Excel fait le calcul, génère 365 dates, les envoie toutes à la fonction DAY () et détermine combien de fins le 13 du mois. La réponse, comme prévu, est 12.
La figure suivante est une feuille de calcul qui fait toute la logique de la formule unique montrée au début de cette rubrique. J'ai créé une ligne pour chaque jour que j'ai vécu. Dans la colonne B, j'obtiens le JOUR () de cette date. Dans la colonne C, j'obtiens le WEEKDAY () de la date. Dans la colonne D, B est-il égal à 13? Dans la colonne E, C = 5? Je multiplie ensuite D * E pour convertir le Vrai / Faux en 1/0.
J'ai caché beaucoup de lignes, mais je vous montre trois jours aléatoires au milieu qui se trouvent être à la fois un vendredi et le 13.
Le total dans F18381 est le même 86 que ma formule originale a renvoyé. Un grand signe. Mais cette feuille de calcul contient 110 268 formules. Ma formule originale de tueur fait toute la logique de ces 110 268 formules en une seule formule.
Attendez. Je veux clarifier. Il n'y a rien de magique dans la formule originale qui devienne intelligent et raccourcisse la logique. Cette formule originale fait vraiment 110 268 étapes, probablement même plus, car la formule d'origine doit calculer le tableau ROW () deux fois.
Trouvez un moyen de l'utiliser ROW(INDIRECT(Date:Date))dans la vraie vie et envoyez-le moi par e-mail (pub at dot com). J'enverrai un prix aux 100 premières personnes à répondre. Probablement pas un séjour en station. Plus probablement un Big Mac. Mais c'est comme ça que ça se passe avec les prix. Beaucoup de Big Mac et peu de séjours en station.
J'ai vu pour la première fois cette formule affichée sur le babillard électronique en 2003 par Ekim. Le crédit a été donné à Harlan Grove. La formule est également apparue dans le livre de Bob Umlas This Is not Excel, It's Magic. Mike Delaney, Meni Porat et Tim Sheets ont tous suggéré l'astuce moins / moins. SUMPRODUCT a été suggéré par Audrey Lynn et Steven White. Merci à tous.
Regarder la vidéo
- Il existe une classe secrète de formules appelées formules matricielles.
- Une formule matricielle peut effectuer des milliers de calculs intermédiaires.
- Ils vous obligent souvent à appuyer sur Ctrl + Maj + Entrée, mais pas toujours.
- Le meilleur livre sur les formules matricielles est Ctrl + Maj + Entrée de Mike Girvin.
- INDIRECT vous permet d'utiliser la concaténation pour créer quelque chose qui ressemble à une référence de cellule.
- Les dates sont bien formatées mais sont stockées sous forme de nombre de jours depuis le 1er janvier 1900.
- La concaténation de deux dates pointera vers une plage de lignes dans Excel.
- Demander le
ROW(INDIRECT(Date1:Date2))"pop out" un tableau de nombreux nombres consécutifs - Utilisation de la fonction WEEKDAY pour déterminer si une date est vendredi.
- Combien de vendredis ont lieu en juillet?
- Pour regarder une formule calculer au ralenti, utilisez l'outil Évaluer la formule
- Combien de 13 se produisent cette année?
- Combien de vendredis 13 se sont produits entre deux dates?
- Vérifiez chaque date pour voir si le WEEKDAY est le vendredi
- Vérifiez chaque date pour voir si le JOUR est 13
- Multipliez ces résultats en utilisant SUMPRODUCT
- Utilisez - pour convertir Vrai / Faux en 1/0
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2026 - Ma formule préférée dans tout Excel!
Podcasting ce livre entier, cliquez sur le «i» dans le coin supérieur droit pour accéder à la playlist!
D'accord, c'était le 30ème sujet du livre, nous étions en quelque sorte à la fin de la section des formules, ou au milieu de la section des formules, et j'ai dit que je devais inclure ma formule préférée de tous les temps. C'est juste une formule incroyable, que vous deviez compter le nombre vendredi 13 ou non, cela ouvre un monde dans toute une zone secrète d'Excel appelée Formules Array! Mettez une date de début, une date de fin, et cette formule calcule le nombre de vendredi 13 qui se sont produits entre ces deux dates. Il fait en fait cinq calculs chaque jour entre ces deux dates, 91895 calculs + SUM, 91896 calculs se déroulant à l'intérieur de cette petite formule, d'accord. Maintenant, à la fin de cet épisode, vous allez être tellement intrigué par les formules matricielles. Je veux souligner,mon ami Mike Girvin a le meilleur livre sur les formules matricielles appelé "Ctrl + Shift" Enter ", c'est une impression récente avec la couverture bleue, autrefois une couverture jaune et verte. Maintenant, quel que soit celui que vous obtenez, c'est un excellent livre, même contenu dans le jaune et le vert.
Très bien, commençons par l'intérieur de cela, avec une formule que vous n'avez peut-être pas entendue appelée INDIRECT. INDIRECT nous permet de concaténer, ou d'une certaine manière de construire un morceau de texte qui ressemble à une référence de cellule. D'accord, disons que nous avons une roue de prix ici, et je vous ai simplement demandé de choisir entre A, B et C. D'accord, alors choisissez ceci et choisissez C, puis choisissez ceci et choisissez 3, d'accord, et votre prix est un séjour en station, car c'est ce qui est stocké dans C3. Et la formule ici est concaténée ensemble depuis C5 et tout ce qui est dans C6 en utilisant le & puis en la passant à l'INDIRECT. Donc = INDIRECT (C5 & C6), dans ce cas, est C3, qui doit être une référence équilibrée. INDIRECT dit: "Hé, nous allons aller à C3 et vous renvoyer la réponse, d'accord?" De retour dans Lotus 1-2-3, cela s'appelait la fonction @@,dans Excel, ils l'ont renommé INDIRECT. D'accord, donc vous avez dans l'INDIRECT, voici maintenant la chose incroyable qui se passe à l'intérieur de là.
Nous avons deux dates, comment Excel stocke les dates, 17/02/1965, ce n'est vraiment que du formatage. Si nous avons regardé le nombre réel derrière cela, c'est 23790, ce qui signifie qu'il y a 23790 jours depuis le 1/1/1900 et 42167 jours depuis le 1/1/1980. Sur un Mac, ce sera depuis le 1/1/1904, donc les dates seront à environ 3000. D'accord, c'est comme ça qu'Excel le stocke, il nous le montre cependant, grâce à ce format de nombre comme date, mais si nous concaténions ensemble B3 et a: et B4, cela nous donnerait en fait les nombres stockés dans les coulisses. Donc = B3 & ”:” & B4, et si nous passons cela à INDIRECT, cela va en fait pointer vers toutes les lignes de 23790 à 42167.
Donc, il y a l'INDIRECT de B6, j'ai demandé le ROW de cela, cela va me donner tout un tas de réponses, et pour savoir combien de réponses j'ai utilisé compte, d'accord. Et pour que cela fonctionne, si j'appuie simplement sur Entrée, cela ne fonctionne pas, je dois maintenir les touches Ctrl et Maj enfoncées et appuyer sur Entrée, et voir, cela ajoute le () autour de la formule ici. Il dit à Excel de passer en mode super formule, en mode formule matricielle et de faire tout le calcul pour tout ce qui ressort de ce tableau, 18378, d'accord. Donc, c'est une astuce incroyable, indirecte de date1: date2, transmettez cela à la fonction ROW, et voici un petit exemple.
Donc, nous voulons juste savoir combien de vendredis ont eu lieu en juillet, voici une date de début, voici une date de fin, et pour chacune de ces lignes, je vais demander le WEEKDAY. WEEKDAY nous dit quel jour de la semaine il s'agit, et ici dans l'argument, 2 vendredis auront une valeur de 5. Donc, je cherche la réponse, et nous allons choisir cette formule, aller dans Formules, et Evaluate Formula, et Evaluate Formula est un excellent moyen de voir une formule se calculer au ralenti. Donc, il y a le B3, le 1er juillet, et vous voyez que cela change en un nombre, puis nous joignons les deux points, à droite, il y a le B4, qui va changer en un nombre, et maintenant nous obtenons le texte, 42186: 42216. À ce stade, nous passons cela à la ROW, et cette simple petite expression va se transformer en 31 valeurs ici.
Maintenant, dans l'exemple où j'avais tout de 1965 à 2015, cela ferait apparaître 86000 valeurs, n'est-ce pas, et vous ne voulez pas faire cela et évaluer la formule, car ce serait un peu fou, d'accord? Mais vous pouvez voir ce qui se passe ici avec le 31, et maintenant je passe ces 31 jours à la fonction WEEKDAY, et nous obtenons 3-4-5. Donc 3 signifie que c'était un mercredi, et ensuite 4 signifie que c'était un jeudi, et ensuite 5 signifie que c'était un vendredi. Prenez toutes ces 31 valeurs et voyez si elles sont = 5 qui est un vendredi, et nous allons obtenir un tas de FAUX et VRAI, donc mercredi, jeudi, vendredi, puis 7 cellules plus tard serait le prochain VRAI, impressionnant!
Très bien, donc dans ce cas, nous avons 5 VRAIS et 26 FAUX, Afin de les additionner, je dois convertir le FAUX en 0 et les VRAIS en 1, et une façon très courante de le faire est d'utiliser le - . D'accord, malheureusement, il n'a pas montré la réponse là-bas, où nous avons vu tout un tas de 1 et de 0, mais c'est en fait ce qui se passe, puis SUMPRODUCT l'ajoute et nous amène au 5. Ici, si nous voulons déterminer combien de 13 du mois il y a eu cette année, de cette date de début à cette date de fin, processus très similaire. Bien que nous ayons 365, transmettez-le à la fonction DAY et vérifiez combien sont 13, d'accord. Pour l'exemple de 92000 lignes, vous savez, nous obtenons le jour, nous obtenons le jour de la semaine, vérifiant si le, DAY = 13, vérifiant si le WEEKDAY = FALSE, multipliant ceci * ceci,et ce n'est que dans les cas où il y a un vendredi 13 que cela finit par être VRAI. Le SUMPRODUCT dit alors "Additionnez tout cela", et c'est ainsi que nous obtenons les 86, littéralement 91895 calculs + SUM, 91896 qui se produisent à l'intérieur de cette formule unique, c'est fou puissant! Allez acheter le livre de Mike, c'est un livre incroyable, il vous ouvrira tout un monde de formules Excel, et en fait, vous devriez simplement acheter les deux livres. Achetez mon livre, achetez le livre de Mike et vous aurez une collection impressionnante qui vous permettra de passer le reste de l'année.cela vous ouvrira tout un monde de formules Excel, et en fait, vous devriez simplement acheter les deux livres. Achetez mon livre, achetez le livre de Mike et vous aurez une collection impressionnante qui vous permettra de passer le reste de l'année.cela vous ouvrira tout un monde de formules Excel, et en fait, vous devriez simplement acheter les deux livres. Achetez mon livre, achetez le livre de Mike et vous aurez une collection impressionnante qui vous permettra de passer le reste de l'année.
Alright, so recap: there's a secret class of formulas called array formulas, and array formula can do thousands of intermediate calculations. They usually require you to press Ctrl+Shift+Enter, but not always, and the best book on array formulas is Mike Girvin’s “Ctrl+Shift+Enter” book. Alright, so INDIRECT lets you use concatenation to build something that looks like a cell reference, and then INDIRECT goes to that cell reference. Concatenating two dates with a colon will point to a range of rows in Excel, and then asking for the ROW of the INDIRECT of date1:date2 will pop out an array of many consecutive numbers, maybe 31, maybe 365, or maybe 85000. Check each date to see if the WEEKDAY=Friday, check each day to see if the DAY=13, multiply those two arrays of TRUEs and FALSEs using the SUMPRODUCT. In many cases we'll use -- to convert the TRUE/FALSE to 1’s and 0’s to allow the SUMPRODUCT to work. It's an awesome formula, I didn't create it, I found it on the message board, as I worked through it, I'm like “Wow, this is really cool!”
Très bien, je tiens à vous remercier d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2026.xlsx
