Tot nou toe onthou ek soms met 'n glimlag een van my eerste veldkorporatiewe opleidings 10 jaar gelede.
Imagine: the open space office of the representative office of an international FMCG company, huge as a football field. Chic design, expensive office equipment, dress code, expats cooing in the corners – that’s all 🙂 In one of the meeting rooms, I start a two-day advanced training on the then current version of Excel 2003 for 15 key employees of the economic department, along with their leader. We get acquainted, I ask them about business tasks, problems, I ask them to show several typical work files. They show the kilometer length of unloading from SAP, the sheets of reports that they make on this, etc. Well, it’s a familiar thing – I mentally figure out topics and timing, adjust to the audience. Out of the corner of my eye, I notice how one of the participants, demonstrating a piece of his report, patiently pulls the cell with the formula down by the black cross in the lower right corner for several thousand lines, then skips the end of the table on the fly, pulls it back, etc. Unable to stand it, I interrupt him curling the mouse around the screen and show a double-click on the black cross, explaining about auto-completion down to the stop.
Skielik besef ek die gehoor is agterdogtig stil en almal kyk vreemd na my. Ek kyk onmerkbaar om myself waar ek kan – alles is reg, my arms en bene is in plek, my vlieg is toegeknoop. Ek spoel my laaste woorde geestelik terug op soek na een of ander verskriklike klousule – daar was niks krimineel nie, blyk dit. Daarna staan die hoof van die groep stilweg op, skud my hand en sê met 'n klipgesig: “Dankie, Nikolai. Hierdie opleiding kan voltooi word.
Wel, in kort, dit het geblyk dat nie een van hulle 'n benul gehad het oor dubbelklik op 'n swart kruis en outovoltooiing nie. Dit het op een of ander manier histories gebeur dat daar niemand was om vir hulle so 'n eenvoudige maar noodsaaklike ding te wys nie. Die hele departement het formules met die hand getrek vir duisende lyne, arme kêrels. En hier is ek. Olie toneel. Die hoof van die departement het toe baie gevra om nie die naam van hul maatskappy aan iemand bekend te maak nie 🙂
Verskeie kere later was daar soortgelyke situasies, maar net met individuele luisteraars – die meeste ken nou natuurlik hierdie funksie.
Die vraag is anders. Na die eerste vreugde om so 'n wonderlike kenmerk te bemeester, kom die meeste gebruikers agter dat outomatiese kopiëring van formules deur dubbelklik op die swart kruis (outovoltooimerker) al die positiewe en negatiewe aspekte het:
- Kopiëring gebeur nie altyd aan die einde van die tabel nie. As die tabel nie monolities is nie, dit wil sê daar is leë selle in aangrensende kolomme, dan is dit nie 'n feit dat outovoltooiing tot aan die einde van die tabel sal werk nie. Heel waarskynlik sal die proses by die naaste leë sel stop voordat dit die einde bereik. As daar selle is wat deur iets onder die kolom beset word, sal outovoltooi presies op hulle stop.
- Wanneer kopieer selontwerp bederf, want By verstek word nie net die formule gekopieer nie, maar ook die formaat. Om reg te stel, klik op die kopie-opsies-knoppie en kies Slegs waardes (Vul sonder formaat).
- Daar is geen vinnige manier om ook die formule gerieflik uit te rek nie nie af nie, maar na regsbehalwe om met die hand te trek. Dubbelklik op die swart kruis is net af.
Kom ons probeer om hierdie tekortkominge met 'n eenvoudige makro reg te stel.
Druk sleutelbordkortpad links Alt + F11 of knoppie Visual Basic tab ontwikkelaar (Ontwikkelaar). Voeg nuwe leë module in via kieslys Voeg in – Module en kopieer die teks van hierdie makros daarheen:
Sub SmartFillDown() Dim rng As Range, n So Long Stel rng = ActiveCell.Offset(0, -1). HuidigeRegion As rng.Cells.Count > 1 Dan n = rng.Cells(1).Row + rng.Rows. Count - 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 As rng.Cells.Count > 1 Dan n = rng.Cells(1).Kolom + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type: =xlFillValues End If End Sub
Sulke makros:
- kan nie net afvul nie (SmartFillDown), maar ook na regs (SmartFillRight)
- moenie die formaat van die selle hieronder of regs bederf nie – slegs die formule (waarde) word gekopieer
- leë aangrensende selle word geïgnoreer en kopiëring vind presies tot aan die einde van die tabel plaas, en nie tot by die naaste gaping in die data of die eerste besette sel nie.
Vir groter gerief kan jy sleutelbordkortpaaie aan hierdie makro's toewys deur die knoppie te gebruik Makro's - Opsies (Makro's - Opsies) net daar op die blad. ontwikkelaar (Ontwikkelaar). Nou sal dit genoeg wees om die gewenste formule of waarde in die eerste sel van die kolom in te voer en druk die gespesifiseerde sleutelkombinasie vir die makro om outomaties die hele kolom (of ry) te vul:
Skoonheid.
PS 'n Deel van die probleem met die kopiëring van formules na die einde van die tabel is opgelos in Excel 2007 met die koms van "slim tabelle". Dit is waar, hulle is nie altyd en nie oral gepas nie. En regs het Excel nooit geleer om op sy eie te kopieer nie.
- Wat is makro's, hoe om dit te gebruik, waar om Visual Basic-kode te kry en waar om dit te plak.
- Slim tabelle in Excel 2007-2013
- Kopieer formules sonder skakelverskuiwing