Astuce n ° 1 du livre XL - des moyens rapides de copier une formule dans une colonne.
Vous avez des milliers de lignes de données. Vous avez ajouté une nouvelle formule dans la ligne supérieure de votre ensemble de données, quelque chose comme ceci =PROPER(A2&" "&B2), comme indiqué ci-dessous. Vous devez maintenant copier la formule sur toutes les lignes de votre ensemble de données.
De nombreuses personnes saisiront la poignée de remplissage et commenceront à faire glisser vers le bas. Mais à mesure que vous faites glisser vers le bas, Excel commence à aller de plus en plus vite. À partir d'Excel 2010, il y a une pause de 200 microsecondes à la dernière ligne de données. 200 microsecondes suffisent pour que vous remarquiez la pause, mais pas assez pour que vous réagissiez et relâchiez le bouton de la souris. Avant de vous en rendre compte, vous avez trop poussé la poignée de remplissage.
La solution consiste à double-cliquer sur la poignée de remplissage! Allez exactement au même endroit où vous commencez à faire glisser la poignée de remplissage. Le pointeur de la souris se transforme en signe plus noir. Double-cliquez.
Excel examine les données environnantes, trouve la dernière ligne contenant les données d'aujourd'hui et copie la formule jusqu'à la dernière ligne de l'ensemble de données.
Dans le passé, les cellules vides dans la colonne de gauche provoquaient l'arrêt du «double-clic sur la poignée de remplissage» juste avant la cellule vide. Mais comme vous pouvez le voir ci-dessous, des noms comme Madonna, Cher ou Pele ne poseront aucun problème. À condition qu'il y ait au moins un chemin diagonal (par exemple, via B76-A77-B78), Excel trouvera le vrai bas de l'ensemble de données.
Dans mes séminaires Power Excel en direct, cette astuce suscite toujours un hoquet de la moitié des personnes présentes dans la salle. C'est mon tour numéro un pour gagner du temps.
Alternatives au double-clic sur la poignée de remplissage
Cette astuce est une astuce géniale si tout ce que vous avez fait à ce stade est de faire glisser la poignée de remplissage vers le bas de l'ensemble de données. Mais il existe des moyens encore plus rapides de résoudre ce problème:
- Utilisez des tableaux. Si vous sélectionnez une cellule dans A1: B112 et appuyez sur Ctrl + T, Excel formatera la plage sous forme de tableau. Une fois que vous avez un tableau, entrez simplement la formule dans C2. Lorsque vous appuyez sur Entrée, il sera copié vers le bas.
- Utilisez un raccourci clavier complexe mais efficace. Ce raccourci nécessite que la colonne adjacente ne contienne aucune cellule vide. Bien que cela semble compliqué à expliquer, les personnes qui me parlent de ce raccourci peuvent tout faire en un clin d'œil.
Voici les étapes:
- À partir de votre formule nouvellement entrée dans C2, appuyez sur la touche Flèche gauche pour passer à la cellule B2.
- Appuyez sur Ctrl + Flèche bas pour passer à la dernière ligne contenant des données. Dans ce cas, B112.
-
Appuyez sur la flèche vers la droite pour revenir au bas de la colonne C presque vide.
Utilisation des raccourcis clavier - À partir de la cellule C112, appuyez sur Ctrl + Maj + Flèche vers le haut. Cela sélectionne toutes les cellules vides à côté de vos données, plus la formule en C2.
-
Appuyez sur Ctrl + D pour remplir la formule de C2 dans tous les espaces de la sélection. (Notez que Ctrl + R remplit à droite, ce qui peut être utile dans d'autres situations.)
Ctrl + D pour remplir vers le bas
Comme alternative, vous pouvez obtenir les mêmes résultats en appuyant sur Ctrl + C avant l'étape 1 et en remplaçant l'étape 5 par Ctrl + V.
Merci aux personnes suivantes qui ont suggéré cette astuce: D. Carmichael, Shelley Fishel, Dawn Gilbert, @Knutsford_admi, Francis Logan, Michael Ortenberg, Jon Paterson, Mike Sullivan et Greg Lambert Lane ont suggéré Ctrl + D. Bill Hazlett, auteur d'Excel pour la classe de mathématiques, a souligné Ctrl + R. C'est le dessin animé d'Em DeMarco publié sur Twitter qui a inspiré le recrutement d'illustrateurs pour ce livre.
Regarder la vidéo
- Astuce n ° 1 du livre XL - des moyens rapides de copier une formule dans une colonne. Aussi dans cette vidéo:
- Ctrl + Flèche bas pour aller à la fin d'un bloc de données
- Ctrl + Maj + Flèche vers le haut pour sélectionner en haut d'un bloc de données
- Ctrl + D pour remplir la formule à partir de la première ligne d'une sélection vers le bas
- Double-cliquez sur la poignée de recopie
- Fonction appropriée
- & caractère pour la concaténation
- Fonction ROMAINE
- Fonction ARABE
Transcription vidéo
Le podcast est sponsorisé par "MrExcel XL", un livre avec plus de 40 astuces Excel, ainsi que des dessins animés Excel, des tweets de cocktails et des blagues.
Apprenez Excel à partir d'un podcast, épisode 1974 - Fast Formula Copy!
D'accord, eh bien, tout ce livre contient beaucoup d'illustrations, et celle qui m'a rendu accro est cette illustration d'Emily DeMarco. Emily était dans une sorte de séminaire Excel, où ils montraient comment combiner des données en utilisant le caractère de concaténation, et elle gribouillait à travers cela, et quelqu'un dans le public a dit: "Mais n'est-ce pas la même chose que = CONCATENER?" À quiconque enseigne des cours, oui. Mais qui peut épeler conCATenate? J'ai vu ça, j'ai trouvé ça hilarant.
Donc, la question aujourd'hui est de savoir comment prendre cette formule, je ne vais pas parler de cette formule, mais comment prendre cette formule, ou n'importe quelle formule, et la copier sur des centaines ou des milliers de lignes de données? Et avant d'arriver à mon chemin, nous devons d'abord reconnaître la méthode Excel Olympics! Il y a des gens qui peuvent faire ces cinq étapes comme une seule frappe, cela prend moins d'une seconde, et si c'est vous, félicitations, bravo à vous, s'il y a jamais des Olympiques Excel, je vous nomme pour faire partie de l'équipe. Donc, si vous êtes un raccourci clavier, la flèche gauche se déplace vers B2, CTRL + flèche bas, flèche droite, CTRL + Maj, la flèche haut sélectionne vers le haut, et dans ma préférée, celle que je n'ai jamais vraiment utiliser avant, était CTRL + D. CTRL + D prend la formule dans la ligne supérieure de la sélection et la copie vers le bas.Donc, si vous aimez les raccourcis clavier, c'est la voie à suivre.
Mais la plupart des gens résolvent ce problème, ils créent cette première formule là-haut dans la rangée 2, et ils saisissent la poignée de remplissage et ils commencent à faire glisser, à droite, et cela commence à aller de plus en plus vite et de plus en plus vite. Avant que vous le sachiez, il y a des centaines de lignes sous le bas des données. Et si vous remarquez, ils ralentissent réellement là. Ils ne sont pas assez longs pour que vous réagissiez réellement, juste assez longtemps pour que vous puissiez voir le ralentissement. Donc, si vous vous retrouvez à descendre trop loin et à remonter, j'appelle cela la danse de la poignée de remplissage, et vous allez adorer ce prochain tour.
Donc, voici le moyen le plus rapide, commencez exactement de la même manière, vous avez la croix blanche là-bas, il y a le curseur de la souris, quand vous vous approchez, il se transforme en noir +, juste là quand vous avez ce noir +, double -Cliquez sur! Excel regarde vers la gauche, calcule le nombre de lignes de données dont nous disposons aujourd'hui et copie la formule vers le bas! Qu'il s'agisse de 112 lignes dans ce cas, ou de 1,1 million de lignes, dans les deux cas, cela va au fond.
Très bien maintenant, pour ceux d'entre vous qui savaient déjà double-cliquer sur la poignée de recopie, vous le savez depuis longtemps: quand le double-clic sur la poignée de recopie ne fonctionne-t-il pas? C'est vrai, c'est quand quelqu'un n'a pas de nom de famille! La colonne de gauche comporte un espace vide. Au lieu de Cher, mettons Prince. Donc normalement, double-cliquez sur la poignée de recopie, elle regarde vers la gauche, et elle ne va copier que vers la ligne 8. À droite, à cause de ce vide dans la ligne 9. Et bien sûr, ce n'est jamais sur le premier écran, c'est toujours des centaines de lignes dans les données. Eh bien, c'était le problème, mais, silencieusement et sans préavis dans Excel 2010, ils regardent maintenant dans toutes les colonnes de gauche pour déterminer jusqu'où copier la formule vers le bas. Donc, il ne se laisse pas berner par la cellule vide dans la colonne de gauche!C'est de loin mon conseil préféré dans tous les Excel, quand je fais les séminaires Power Excel, ces séminaires en direct, et j'y arrive environ 45 minutes dans la journée, j'ai presque toujours un hoquet de quelques personnes. la pièce, lorsque je double-clique sur la poignée de recopie.
Maintenant, juste un fait amusant ici, le livre "MrExcel XL", vous savez que j'ai grandi à l'époque, dans les années 70 quand il y avait toutes sortes de films, qui sortaient avec des chiffres romains, donc le Parrain II était le premier po. Dans l'histoire moderne, Retour vers le futur III, Star Wars IV, Rocky V, Star Trek VI, voire Final Fantasy VII pour 7. Vous savez, et c'est drôle, Excel a en fait une fonction ROMAINE! Très bien, vous pouvez donc demander le ROMAIN de ROW, et cela vous donnera les chiffres romains. Ce qui est vraiment étrange, car dans Excel 2013, si vous entrez un chiffre romain XL et que vous demandez l'ARABIQUE, il convertira ce chiffre ROMAIN en chiffre normal. Alors, quand j'en ai terminé avec mon 39e livre, et que je savais que le 40e livre allait arriver, juste comme un bout du chapeau pour tous ces films qui utilisent des chiffres romains, je le savais,ça allait être "MrExcel XL", parce que c'est mon 40ème livre, même si j'y ai mis le slogan, = ROMAN (40), donc les gens comprendraient la blague!
Très bien, juste un bref récapitulatif de toutes les choses dont nous avons parlé dans cet épisode: Et CECI n'est que le conseil n ° 1 du livre. Eh bien merci à tous ceux qui ont écrit en suggérant cette astuce. Ce sont toutes des personnes qui m'ont tweeté. Une que je n'ai même pas couverte ici, Bill Hazlett, souligne que CTRL + R est comme CTRL + D, si vous sélectionnez une cellule et faites glisser vers la droite, ou sélectionnez vers la droite, CTRL + R copiera vers la droite. Allez-y et cliquez sur ce bouton d'information dans le coin supérieur droit en ce moment, pour savoir comment vous pouvez acheter ce livre.
Merci d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!
