Hittills minns jag ibland med ett leende en av mina första företagsutbildningar för 10 år sedan.
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.
Plötsligt inser jag att publiken är misstänkt tyst och alla tittar konstigt på mig. Jag ser mig omärkligt omkring mig varhelst jag kan – allt är OK, mina armar och ben är på plats, min gylf är knäppt. Jag spola tillbaka mina sista ord mentalt i jakten på någon fruktansvärd klausul – det verkar inte finnas något brottsligt. Efter det reser sig gruppens chef tyst upp, skakar min hand och säger med ett stenansikte: ”Tack Nikolai. Denna utbildning kan genomföras.
Jo, kort och gott, det visade sig att ingen av dem hade en aning om att dubbelklicka på ett svart kryss och autokomplettera. Det hände på något sätt historiskt att det inte fanns någon som visade dem en så enkel men nödvändig sak. Hela avdelningen drog formler manuellt för tusentals rader, stackare. Och här är jag. Olje scen. Avdelningschefen bad då väldigt mycket att inte avslöja namnet på deras företag för någon 🙂
Flera gånger senare uppstod liknande situationer, men bara med enskilda lyssnare – de flesta känner nu förstås till denna funktion.
Frågan är en annan. Efter den första glädjen att bemästra en sådan underbar funktion, kommer de flesta användare att förstå att automatisk kopiering av formler genom att dubbelklicka på det svarta korset (markör för autoslutförande) har alla positiva och negativa aspekter:
- Kopiering sker inte alltid till slutet av tabellen. Om tabellen inte är monolitisk, dvs det finns tomma celler i intilliggande kolumner, så är det inte ett faktum att autokomplettering kommer att fungera till slutet av tabellen. Troligtvis kommer processen att stanna vid närmaste tomma cell innan den når slutet. Om det finns celler som är upptagna av något under kolumnen kommer autoslutförandet att stoppa exakt på dem.
- Vid kopiering celldesign förstör, eftersom Som standard kopieras inte bara formeln utan även formatet. För att korrigera, klicka på knappen för kopieringsalternativ och välj Endast värden (Fyll utan format).
- Det finns inget snabbt sätt att också bekvämt sträcka ut formeln inte ner utan till högerförutom att dra för hand. Dubbelklicka på det svarta krysset är bara nere.
Låt oss försöka åtgärda dessa brister med ett enkelt makro.
Tryck på kortkommandot åt vänster Alt + F11 eller knapp Visual Basic fliken utvecklare (Utvecklaren). Infoga ny tom modul via menyn Infoga – Modul och kopiera texten till dessa makron dit:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion Om rng.Cells.Count > 1 Då n = rng.Cells(1).Rad + rng.Rows. Antal - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Typ:=xlFillValues End If End Sub Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion Om rng.Cells.Count > 1 Då n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Skriv: =xlFillValues End If End Sub
Sådana makron:
- kan fylla inte bara ner (SmartFillDown), utan även till höger (SmartFillRight)
- förstör inte formatet på cellerna nedanför eller till höger – bara formeln (värdet) kopieras
- tomma intilliggande celler ignoreras och kopiering sker exakt till slutet av tabellen, och inte till närmaste lucka i data eller den första ockuperade cellen.
För större bekvämlighet kan du tilldela kortkommandon till dessa makron med knappen Makron – Alternativ (Makron — Alternativ) precis där på fliken. utvecklare (Utvecklaren). Nu räcker det med att ange önskad formel eller värde i den första cellen i kolumnen och trycka på den angivna tangentkombinationen för att makrot automatiskt ska fylla hela kolumnen (eller raden):
Skönhet.
PS En del av problemet med att kopiera formler till slutet av tabellen löstes i Excel 2007 med tillkomsten av "smarta tabeller". Det är sant att de inte alltid och inte överallt är lämpliga. Och till höger lärde sig Excel aldrig att kopiera på egen hand.
- Vad är makron, hur man använder dem, var man får Visual Basic-kod och var man klistrar in den.
- Smarta tabeller i Excel 2007-2013
- Kopiera formler utan länkförskjutning