Do sedaj se včasih z nasmehom spomnim enega svojih prvih terenskih korporativnih izobraževanj pred 10 leti.
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.
Nenadoma ugotovim, da je občinstvo sumljivo tiho in me vsi čudno gledajo. Neopazno se ozrem, kjerkoli se da – vse je v redu, roke in noge so na mestu, muha zapeta. V mislih previjam svoje zadnje besede v iskanju neke strašne klavzule – zdi se, da ni bilo nič kaznivega. Nato vodja skupine tiho vstane, mi stisne roko in reče s kamnitim obrazom: »Hvala, Nikolaj. To usposabljanje je mogoče zaključiti.
No, skratka, izkazalo se je, da nihče od njih nima pojma o dvojnem kliku na črni križec in samodokončanju. Zgodovinsko se je nekako zgodilo, da ni bilo nikogar, ki bi jim pokazal tako preprosto, a nujno stvar. Celoten oddelek je ročno vlekel formule za tisoče vrstic, revčki. In tukaj sem. Oljna scena. Vodja oddelka je nato zelo prosil, da imena njihovega podjetja ne razkrijejo nikomur 🙂
Pozneje je bilo še nekajkrat podobnih situacij, a le pri posameznih poslušalcih – zdaj večina to funkcijo seveda pozna.
Vprašanje je drugačno. Po prvem veselju obvladovanja tako čudovite funkcije večina uporabnikov razume, da ima samodejno kopiranje formul z dvojnim klikom na črni križ (oznaka za samodokončanje) vse pozitivne in negativne strani:
- Kopiranje se ne zgodi vedno do konca tabele. Če tabela ni monolitna, tj. v sosednjih stolpcih so prazne celice, potem ni dejstvo, da bo samodokončanje delovalo do konca tabele. Najverjetneje se bo proces ustavil pri najbližji prazni celici, preden doseže konec. Če so celice pod stolpcem zasedene z nečim, se bo samodejno dokončanje ustavilo natanko pri njih.
- Pri kopiranju oblikovanje celic pokvari, ker privzeto ni kopirana samo formula, ampak tudi oblika. Če želite popraviti, kliknite gumb možnosti kopiranja in izberite Samo vrednote (Izpolni brez formata).
- Ni hitrega načina za priročno raztezanje formule ne navzdol ampak na desnorazen vlečenja z roko. Dvojni klik na črni križ je ravno dol.
Poskusimo te pomanjkljivosti odpraviti s preprostim makrom.
Pritisnite bližnjico na tipkovnici levo Alt + F11 ali gumb Visual Basic tab razvijalec (Razvijalec). Vstavite nov prazen modul preko menija Vstavi – Modul in tja kopirajte besedilo teh makrov:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion If rng.Cells.Count > 1 Then 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 Če je rng.Cells.Count > 1 Potem je n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Vrsta: =xlFillValues End If End Sub
Takšni makri:
- lahko zapolni ne samo navzdol (SmartFillDown), ampak tudi v desno (SmartFillRight)
- ne pokvarite oblike celic spodaj ali desno – kopira se le formula (vrednost).
- prazne sosednje celice so prezrte in kopiranje se izvede točno do konca tabele in ne do najbližje vrzeli v podatkih ali prve zasedene celice.
Za večje udobje lahko tem makrom dodelite bližnjice na tipkovnici z gumbom Makri – možnosti (Makri — Možnosti) tam na zavihku. razvijalec (Razvijalec). Zdaj bo dovolj, da vnesete želeno formulo ali vrednost v prvo celico stolpca in pritisnete določeno kombinacijo tipk, da makro samodejno zapolni celoten stolpec (ali vrstico):
Lepota.
PS Del težave s kopiranjem formul na konec tabele je bil rešen v Excelu 2007 s prihodom »pametnih tabel«. Res je, da niso vedno in ne povsod primerni. In desno, Excel se nikoli ni naučil kopirati sam.
- Kaj so makri, kako jih uporabljati, kje dobiti kodo Visual Basic in kam jo prilepiti.
- Pametne tabele v Excelu 2007-2013
- Kopirajte formule brez premika povezave