Doteď občas s úsměvem vzpomínám na jedno ze svých prvních terénních firemních školení před 10 lety.
Představte si: open space kancelář zastoupení mezinárodní společnosti FMCG, obrovská jako fotbalové hřiště. Elegantní design, drahé kancelářské vybavení, dress code, expati vrčící v rozích – toť vše 🙂 V jedné ze zasedacích místností zahajuji dvoudenní pokročilé školení na tehdy aktuální verzi Excelu 2003 pro 15 klíčových zaměstnanců ekonomického oddělení spolu se svým vůdcem. Seznamujeme se, ptám se jich na obchodní úkoly, problémy, žádám je, aby ukázali několik typických pracovních souborů. Ukazují kilometrovou délku vykládky ze SAP, archy reportů, které o tom dělají atd. No, je to známá věc – v duchu vymýšlím témata a načasování, přizpůsobuji se publiku. Koutkem oka si všimnu, jak jeden z účastníků, předvádějící kus své zprávy, trpělivě stahuje buňku se vzorcem dolů za černý křížek v pravém dolním rohu na několik tisíc řádků, pak přeskočí konec stůl za běhu, stahuje ho dozadu atd. Když jsem to nevydržel, přerušil jsem ho kroucením myší po obrazovce a ukázal dvojité kliknutí na černý kříž a vysvětloval mu automatické dokončování až na doraz.
Najednou si uvědomuji, že publikum je podezřele tiché a všichni se na mě divně dívají. Nenápadně se rozhlížím kolem sebe, kde se dá – vše je v pořádku, ruce i nohy mám na svém místě, mušku mám zapnutou. V duchu přetáčím svá poslední slova a hledám nějakou hroznou klauzuli – zdá se, že v tom nebylo nic zločinného. Poté vedoucí skupiny tiše vstává, podává mi ruku a s kamennou tváří říká: „Děkuji, Nikolai. Toto školení lze absolvovat.
No zkrátka se ukázalo, že nikdo z nich neměl ani páru o dvojkliku na černý křížek a automatickém doplňování. Nějak historicky se stalo, že se nenašel nikdo, kdo by jim tak jednoduchou, ale potřebnou věc ukázal. Celé oddělení ručně vytahovalo vzorce pro tisíce řádků, chudáci. A jsem tady. Ropná scéna. Vedoucí oddělení pak velmi prosil, aby nikomu neprozradil jméno jejich firmy 🙂
Několikrát později došlo k podobným situacím, ale pouze u jednotlivých posluchačů – většina dnes tuto funkci samozřejmě zná.
Otázka je jiná. Po první radosti ze zvládnutí tak úžasné funkce většina uživatelů pochopí, že automatické kopírování vzorců dvojitým kliknutím na černý křížek (značka automatického doplňování) má všechny kladné i záporné stránky:
- Ne vždy se kopíruje až na konec tabulky. Pokud tabulka není monolitická, tj. v sousedních sloupcích jsou prázdné buňky, pak není pravda, že automatické doplňování bude fungovat až do konce tabulky. S největší pravděpodobností se proces před dosažením konce zastaví v nejbližší prázdné buňce. Pokud jsou pod sloupcem něčím obsazené buňky, automatické doplňování se na nich zastaví přesně.
- Při kopírování design buňky kazí, protože Ve výchozím nastavení se nekopíruje pouze vzorec, ale také formát. Pro opravu klikněte na tlačítko možností kopírování a vyberte Pouze hodnoty (Vyplňte bez formátu).
- Neexistuje žádný rychlý způsob, jak formuli také pohodlně natáhnout ne dolů, ale dopravakromě tahání rukou. Dvojité kliknutí na černý kříž je právě dole.
Pokusme se tyto nedostatky napravit jednoduchým makrem.
Stiskněte klávesovou zkratku vlevo Alt + F11 nebo tlačítko Visual Basic Karta vývojka (Vývojář). Vložit nový prázdný modul přes menu Vložit – Modul a zkopírujte tam text těchto maker:
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 SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion If rng.Cells.Count > 1 Then n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Typ: =xlFillValues End If End Sub
Taková makra:
- umí vyplnit nejen dolů (SmartFillDown), ale i doprava (SmartFillRight)
- nekazit formát buněk pod nebo vpravo – zkopíruje se pouze vzorec (hodnota).
- prázdné sousední buňky jsou ignorovány a kopírování probíhá přesně na konec tabulky a ne do nejbližší mezery v datech nebo do první obsazené buňky.
Pro větší pohodlí můžete těmto makrům přiřadit klávesové zkratky pomocí tlačítka Makra – Možnosti (Makra — Možnosti) přímo tam na kartě. vývojka (Vývojář). Nyní bude stačit zadat požadovaný vzorec nebo hodnotu do první buňky sloupce a stisknout zadanou kombinaci kláves, aby makro automaticky vyplnilo celý sloupec (nebo řádek):
Krása.
PS Část problému s kopírováním vzorců na konec tabulky byla v Excelu 2007 vyřešena s příchodem „chytrých tabulek“. Pravda, nejsou vždy a ne všude vhodné. A napravo se Excel nikdy nenaučil kopírovat sám od sebe.
- Co jsou makra, jak je používat, kde získat kód Visual Basic a kam jej vložit.
- Inteligentní tabulky v Excelu 2007-2013
- Kopírovat vzorce bez posunu odkazu