Jusqu'à présent, parfois avec le sourire je me souviens d'une de mes premières formations terrain en entreprise il y a 10 ans.
Imaginez : le bureau open space du bureau de représentation d'une entreprise internationale de produits de grande consommation, immense comme un terrain de football. Design chic, matériel de bureau coûteux, code vestimentaire, expatriés roucoulant dans les coins, c'est tout 🙂 Dans l'une des salles de réunion, je commence une formation avancée de deux jours sur la version alors actuelle d'Excel 2003 pour 15 employés clés du service économique. , avec leur chef. Nous faisons connaissance, je les interroge sur les tâches professionnelles, les problèmes, je leur demande de montrer plusieurs dossiers de travail types. Ils montrent la longueur des kilomètres de déchargement depuis SAP, les feuilles de rapports qu'ils font à ce sujet, etc. Eh bien, c'est une chose familière – je détermine mentalement les sujets et le calendrier, je m'adapte au public. Du coin de l'œil, je remarque comment l'un des participants, démontrant un morceau de son rapport, tire patiemment la cellule avec la formule par la croix noire dans le coin inférieur droit pendant plusieurs milliers de lignes, puis saute la fin de la table à la volée, la tire en arrière, etc. Incapable de la supporter, je l'interromps en enroulant la souris autour de l'écran et montre un double-clic sur la croix noire, expliquant l'auto-complétion jusqu'à la butée.
Soudain, je me rends compte que le public est étrangement silencieux et que tout le monde me regarde étrangement. Je regarde imperceptiblement autour de moi partout où je peux – tout va bien, mes bras et mes jambes sont en place, ma braguette est boutonnée. Je rembobine mentalement mes derniers mots à la recherche d'une clause terrible - il n'y avait rien de criminel, semble-t-il. Après cela, le chef du groupe se lève silencieusement, me serre la main et dit avec un visage de pierre : « Merci, Nikolai. Cette formation peut être complétée.
Eh bien, en bref, il s'est avéré qu'aucun d'entre eux n'avait la moindre idée du double-clic sur une croix noire et de la saisie semi-automatique. Il est en quelque sorte arrivé historiquement qu'il n'y avait personne pour leur montrer une chose aussi simple mais nécessaire. Tout le département a tiré des formules manuellement pour des milliers de lignes, pauvres gars. Et me voici. Scène d'huile. Le chef de service a alors beaucoup demandé de ne révéler le nom de leur entreprise à personne 🙂
Plusieurs fois plus tard, il y a eu des situations similaires, mais seulement avec des auditeurs individuels - la plupart maintenant, bien sûr, connaissent cette fonction.
La question est différente. Après la première joie de maîtriser une fonctionnalité aussi merveilleuse, la plupart des utilisateurs comprennent que la copie automatique de formules en double-cliquant sur la croix noire (marqueur de saisie semi-automatique) a tous les aspects positifs et négatifs :
- La copie n'arrive pas toujours à la fin du tableau. Si le tableau n'est pas monolithique, c'est-à-dire qu'il y a des cellules vides dans des colonnes adjacentes, alors ce n'est pas un fait que la saisie semi-automatique fonctionnera jusqu'à la fin du tableau. Très probablement, le processus s'arrêtera à la cellule vide la plus proche avant d'atteindre la fin. S'il y a des cellules occupées par quelque chose en dessous de la colonne, la saisie semi-automatique s'arrêtera exactement dessus.
- Lors de la copie butin de conception de cellule, car Par défaut, non seulement la formule est copiée, mais également le format. Pour corriger, cliquez sur le bouton des options de copie et sélectionnez Seules les valeurs (Remplir sans format).
- Il n'y a pas de moyen rapide d'étirer également la formule de manière pratique pas en bas mais à droitesauf à tirer à la main. Le double-clic sur la croix noire est juste en bas.
Essayons de corriger ces lacunes avec une simple macro.
Appuyez sur le raccourci clavier gauche Alt + F11 ou bouton Visual Basic languette promoteur (Développeur). Insérer un nouveau module vide via le menu Insertion – Module et copiez-y le texte de ces macros :
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion Si rng.Cells.Count > 1 Alors n = rng.Cells(1).Row + rng.Rows. Nombre - ActiveCell.Row ActiveCell.AutoFill Destination :=ActiveCell.Resize(n, 1), Type :=xlFillValues End If End Sub Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion Si rng.Cells.Count > 1 Alors n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination :=ActiveCell.Resize(1, n), Type : =xlFillValues End If End Sub
Ces macros :
- peut remplir non seulement vers le bas (SmartFillDown), mais aussi vers la droite (SmartFillRight)
- ne gâchez pas le format des cellules en dessous ou à droite - seule la formule (valeur) est copiée
- les cellules adjacentes vides sont ignorées et la copie se produit exactement à la fin du tableau, et non à l'écart le plus proche dans les données ou à la première cellule occupée.
Pour plus de commodité, vous pouvez attribuer des raccourcis clavier à ces macros à l'aide du bouton Macros – Options (Macros — Options) juste là sur l'onglet. promoteur (Développeur). Maintenant, il suffira d'entrer la formule ou la valeur souhaitée dans la première cellule de la colonne et d'appuyer sur la combinaison de touches spécifiée pour que la macro remplisse automatiquement toute la colonne (ou ligne) :
Beauté.
PS Une partie du problème de copie des formules à la fin du tableau a été résolu dans Excel 2007 avec l'avènement des « tableaux intelligents ». Certes, ils ne sont pas toujours et pas partout appropriés. Et à droite, Excel n'a jamais appris à copier tout seul.
- Que sont les macros, comment les utiliser, où obtenir le code Visual Basic et où le coller.
- Tableaux intelligents dans Excel 2007-2013
- Copier des formules sans changement de lien