Tot nu toe herinner ik me soms met een glimlach een van mijn eerste field corporate trainingen 10 jaar geleden.
Stel je voor: het open kantoor van het representatieve kantoor van een internationaal FMCG-bedrijf, zo groot als een voetbalveld. Chique design, dure kantoorapparatuur, dresscode, expats die in de hoeken koeren – dat is alles 🙂 In een van de vergaderruimtes start ik een tweedaagse vervolgtraining over de dan geldende versie van Excel 2003 voor 15 sleutelmedewerkers van de economische afdeling , samen met hun leider. We maken kennis, ik vraag ze naar zakelijke taken, problemen, ik vraag ze om verschillende typische werkbestanden te laten zien. Ze tonen de kilometerlengte van het lossen uit SAP, de bladen met rapporten die ze hierover maken, enz. Nou, het is een bekend iets – ik bedenk mentaal de onderwerpen en de timing, pas me aan het publiek aan. Vanuit mijn ooghoek zie ik hoe een van de deelnemers, die een stukje van zijn rapport demonstreert, geduldig de cel met de formule enkele duizenden regels naar beneden trekt bij het zwarte kruis in de rechter benedenhoek, en vervolgens het einde van het rapport overslaat. Ik kan hem niet uithouden, onderbreek hem terwijl ik de muis over het scherm krul en dubbelklik op het zwarte kruis, waarbij ik tot aan de aanslag uitleg geef over automatisch aanvullen.
Opeens realiseer ik me dat het publiek verdacht stil is en iedereen me vreemd aankijkt. Ik kijk onmerkbaar om me heen waar ik kan - alles is in orde, mijn armen en benen zitten op hun plaats, mijn gulp is dichtgeknoopt. Ik spoel mentaal mijn laatste woorden terug op zoek naar een vreselijke clausule - er was niets misdadigs, zo lijkt het. Daarna staat het hoofd van de groep zwijgend op, schudt mijn hand en zegt met een stenen gezicht: “Dank je, Nikolai. Deze opleiding kan worden afgerond.
Nou, kortom, het bleek dat geen van hen enig idee had van dubbelklikken op een zwart kruis en automatisch aanvullen. Op de een of andere manier gebeurde het historisch dat er niemand was om hen zoiets eenvoudigs maar noodzakelijks te laten zien. De hele afdeling heeft handmatig formules opgesteld voor duizenden regels, arme kerels. En hier ben ik. Olie scène. Het hoofd van de afdeling vroeg toen heel erg om de naam van hun bedrijf aan niemand te onthullen 🙂
Meerdere malen later waren er soortgelijke situaties, maar alleen met individuele luisteraars - de meesten kennen deze functie nu natuurlijk.
De vraag is anders. Na de eerste vreugde om zo'n geweldige functie onder de knie te krijgen, beginnen de meeste gebruikers te begrijpen dat het automatisch kopiëren van formules door te dubbelklikken op het zwarte kruis (markering voor automatisch aanvullen) alle positieve en negatieve aspecten heeft:
- Kopiëren gebeurt niet altijd aan het einde van de tabel. Als de tabel niet monolithisch is, dwz er zijn lege cellen in aangrenzende kolommen, dan is het niet zo dat automatisch aanvullen tot het einde van de tabel werkt. Hoogstwaarschijnlijk stopt het proces bij de dichtstbijzijnde lege cel voordat het het einde bereikt. Als er cellen zijn die bezet zijn door iets onder de kolom, dan stopt automatisch aanvullen precies.
- Bij het kopiëren celontwerp bederft, want Standaard wordt niet alleen de formule gekopieerd, maar ook het formaat. Om te corrigeren, klikt u op de knop Kopieeropties en selecteert u Alleen waarden (Vul zonder formaat).
- Er is geen snelle manier om de formule ook gemakkelijk uit te rekken niet naar beneden maar naar rechtsbehalve om met de hand te trekken. Dubbelklikken op het zwarte kruis is net naar beneden.
Laten we proberen deze tekortkomingen op te lossen met een eenvoudige macro.
Druk op sneltoets links Alt + F11 of knop Visual Basic tab ontwikkelaar (Ontwikkelaar). Nieuwe lege module invoegen via menu Invoegen – Module en kopieer de tekst van deze macro's daar:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion Als rng.Cells.Count > 1 Dan n = rng.Cells(1).Rij + rng.Rijen. Aantal - 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 Als rng.Cells.Count > 1 Dan n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Typ: =xlFillValues End If End Sub
Dergelijke macro's:
- kan niet alleen naar beneden (SmartFillDown), maar ook naar rechts (SmartFillRight) worden gevuld
- verpest het formaat van de cellen hieronder of aan de rechterkant niet - alleen de formule (waarde) wordt gekopieerd
- lege aangrenzende cellen worden genegeerd en het kopiëren gebeurt precies naar het einde van de tabel, en niet naar de dichtstbijzijnde opening in de gegevens of de eerste bezette cel.
Voor meer gemak kunt u sneltoetsen aan deze macro's toewijzen met de knop Macro's – Opties (Macro's — Opties) daar op het tabblad. ontwikkelaar (Ontwikkelaar). Nu is het voldoende om de gewenste formule of waarde in de eerste cel van de kolom in te voeren en op de opgegeven toetsencombinatie te drukken om de macro automatisch de hele kolom (of rij) te laten vullen:
Schoonheid.
PS Een deel van het probleem met het kopiëren van formules naar het einde van de tabel werd in Excel 2007 opgelost met de komst van "slimme tabellen". Toegegeven, ze zijn niet altijd en niet overal geschikt. En aan de rechterkant heeft Excel nooit geleerd om alleen te kopiëren.
- Wat zijn macro's, hoe u ze gebruikt, waar u Visual Basic-code kunt krijgen en waar u deze kunt plakken.
- Slimme tabellen in Excel 2007-2013
- Formules kopiëren zonder link shift