Inntil nå husker jeg noen ganger med et smil en av mine første bedriftsopplæringer for 10 år siden.
Tenk deg: det åpne romkontoret til representasjonskontoret til et internasjonalt FMCG-selskap, stort som en fotballbane. Elegant design, dyrt kontorutstyr, kleskode, expats som kurrer i hjørnene – det er alt 🙂 I et av møterommene starter jeg en to-dagers videreutdanning i den da gjeldende versjonen av Excel 2003 for 15 nøkkelansatte i økonomiavdelingen , sammen med deres leder. Vi blir kjent, jeg spør dem om forretningsoppgaver, problemer, jeg ber dem vise flere typiske arbeidsfiler. De viser kilometerlengden på lossing fra SAP, rapportene de lager om dette osv. Vel, det er en kjent ting – jeg finner mentalt ut temaer og timing, tilpasser meg publikum. Ut av øyekroken legger jeg merke til hvordan en av deltakerne, som demonstrerer en del av rapporten hans, tålmodig trekker cellen med formelen ned ved det svarte krysset i nedre høyre hjørne i flere tusen linjer, for så å hoppe over slutten av bordet i farten, trekker det tilbake osv. Jeg kan ikke stå det, jeg avbryter ham med å krølle musen rundt skjermen og viser et dobbeltklikk på det svarte krysset, og forklarer om autofullføring ned til stopp.
Plutselig skjønner jeg at publikum er mistenkelig stille og alle ser rart på meg. Jeg ser umerkelig rundt meg hvor jeg kan – alt er i orden, armene og bena er på plass, gylfen er knepet opp. Jeg spoler mentalt tilbake mine siste ord på leting etter en forferdelig klausul – det var ikke noe kriminelt, ser det ut til. Etter det reiser lederen av gruppen seg stille opp, håndhilser på meg og sier med steinansikt: «Takk, Nikolai. Denne opplæringen kan gjennomføres.
Vel, kort fortalt viste det seg at ingen av dem hadde peiling på å dobbeltklikke på et svart kryss og autofullføring. Det skjedde på en eller annen måte historisk at det ikke var noen som viste dem en så enkel, men nødvendig ting. Hele avdelingen trakk formler manuelt for tusenvis av linjer, stakkars karer. Og her er jeg. Oljescene. Avdelingslederen ba da veldig om å ikke røpe navnet på selskapet deres til noen 🙂
Flere ganger senere var det lignende situasjoner, men bare med individuelle lyttere – de fleste kjenner nå selvfølgelig denne funksjonen.
Spørsmålet er annerledes. Etter den første gleden ved å mestre en slik fantastisk funksjon, forstår de fleste brukere at automatisk kopiering av formler ved å dobbeltklikke på det svarte krysset (autofullføringsmarkøren) har alle de positive og negative aspektene:
- Kopiering skjer ikke alltid til slutten av tabellen. Hvis tabellen ikke er monolitisk, dvs. det er tomme celler i tilstøtende kolonner, så er det ikke et faktum at autofullføring vil fungere til slutten av tabellen. Mest sannsynlig vil prosessen stoppe ved nærmeste tomme celle før den når slutten. Hvis det er celler okkupert av noe under kolonnen, vil autofullføring stoppe nøyaktig på dem.
- Ved kopiering celledesign ødelegger, fordi Som standard blir ikke bare formelen kopiert, men også formatet. For å korrigere, klikk på kopieringsalternativer-knappen og velg Bare verdier (Fyll uten format).
- Det er ingen rask måte å strekke formelen på ikke ned, men til høyrebortsett fra å trekke for hånd. Dobbeltklikk på det svarte krysset er bare nede.
La oss prøve å fikse disse manglene med en enkel makro.
Trykk hurtigtast til venstre Alt + F11 eller knappen Visual Basic tab utvikler (Utvikler). Sett inn ny tom modul via menyen Sett inn – modul og kopier teksten til disse makroene dit:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion Hvis rng.Cells.Count > 1 Da er 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 Hvis rng.Cells.Count > 1 Da er n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type: =xlFillValues End If End Sub
Slike makroer:
- kan fylle ikke bare ned (SmartFillDown), men også til høyre (SmartFillRight)
- ikke ødelegg formatet til cellene under eller til høyre – bare formelen (verdien) kopieres
- tomme tilstøtende celler ignoreres og kopiering skjer nøyaktig til slutten av tabellen, og ikke til nærmeste gap i dataene eller den første okkuperte cellen.
For større bekvemmelighet kan du tilordne tastatursnarveier til disse makroene ved å bruke knappen Makroer – Alternativer (Makroer – Alternativer) der på fanen. utvikler (Utvikler). Nå vil det være nok å skrive inn ønsket formel eller verdi i den første cellen i kolonnen og trykke på den angitte tastekombinasjonen for at makroen automatisk skal fylle hele kolonnen (eller raden):
Skjønnhet.
PS En del av problemet med å kopiere formler til slutten av tabellen ble løst i Excel 2007 med bruken av "smarte tabeller". Riktignok er de ikke alltid og ikke overalt passende. Og til høyre lærte Excel aldri å kopiere på egen hånd.
- Hva er makroer, hvordan du bruker dem, hvor du får Visual Basic-kode og hvor du limer den inn.
- Smarte tabeller i Excel 2007-2013
- Kopier formler uten koblingsskift