Do tej pory czasem z uśmiechem wspominam jedno z moich pierwszych firmowych szkoleń terenowych sprzed 10 lat.
Wyobraź sobie: biuro open space przedstawicielstwa międzynarodowej firmy z branży FMCG, ogromne jak boisko do piłki nożnej. Szykowny design, drogie wyposażenie biura, dress code, gruchanie po kątach emigrantów – to wszystko 🙂 W jednej z sal konferencyjnych rozpoczynam dwudniowe zaawansowane szkolenie z aktualnej wówczas wersji Excela 2003 dla 15 kluczowych pracowników działu ekonomicznego wraz ze swoim przywódcą. Poznajemy się, pytam o zadania biznesowe, problemy, proszę o pokazanie kilku typowych plików roboczych. Pokazują kilometrową długość rozładunku z SAP, arkusze raportów, które robią na ten temat itp. No cóż, to rzecz znana – w myślach obmyślam tematy i harmonogram, dopasowuję się do odbiorców. Kątem oka zauważam, jak jeden z uczestników demonstrując fragment swojego raportu, cierpliwie przeciąga komórkę ze wzorem w dół za czarny krzyżyk w prawym dolnym rogu przez kilka tysięcy linii, po czym pomija koniec stół w locie, odsuwa go itp. Nie mogąc już wytrzymać, przerywam mu kręcenie myszką po ekranie i pokazuję podwójne kliknięcie na czarny krzyżyk, tłumacząc do końca o autouzupełnianiu.
Nagle uświadamiam sobie, że publiczność jest podejrzanie cicha i wszyscy dziwnie na mnie patrzą. Niepostrzeżenie rozglądam się dookoła, gdzie tylko mogę – wszystko OK, ręce i nogi na miejscu, rozporek zapięty. Cofam w myślach ostatnie słowa w poszukiwaniu jakiejś strasznej klauzuli – wygląda na to, że nie było nic przestępczego. Następnie szef grupy cicho wstaje, ściska mi dłoń i mówi z kamienną twarzą: „Dziękuję, Nikołaj. Szkolenie to można ukończyć.
Krótko mówiąc, okazało się, że żaden z nich nie miał pojęcia o dwukrotnym kliknięciu czarnego krzyżyka i autouzupełnianiu. Jakoś historycznie zdarzyło się, że nie było komu pokazać tak prostej, ale koniecznej rzeczy. Cały wydział ręcznie wyciągał formuły dla tysięcy linijek, biedni koledzy. I oto jestem. Scena olejna. Szef departamentu bardzo wtedy prosił, aby nikomu nie ujawniać nazwy swojej firmy 🙂
Kilka razy później zdarzały się podobne sytuacje, ale tylko z indywidualnymi słuchaczami – większość teraz oczywiście zna tę funkcję.
Pytanie jest inne. Po pierwszej radości z opanowania tak wspaniałej funkcji większość użytkowników rozumie, że automatyczne kopiowanie formuł poprzez dwukrotne kliknięcie czarnego krzyżyka (znacznika autouzupełniania) ma wszystkie pozytywne i negatywne aspekty:
- Kopiowanie nie zawsze dzieje się na końcu tabeli. Jeśli tabela nie jest monolityczna, tzn. są puste komórki w sąsiednich kolumnach, to nie jest faktem, że autouzupełnianie będzie działać do końca tabeli. Najprawdopodobniej proces zatrzyma się na najbliższej pustej komórce przed dotarciem do końca. Jeśli pod kolumną znajdują się komórki zajęte przez coś, autouzupełnianie zatrzyma się dokładnie na nich.
- Podczas kopiowania psuje projekt komórki, ponieważ Domyślnie kopiowana jest nie tylko formuła, ale także format. Aby poprawić, kliknij przycisk opcji kopiowania i wybierz Tylko wartości (Wypełnij bez formatu).
- Nie ma szybkiego sposobu na wygodne rozciągnięcie formuły nie w dół, ale w prawoz wyjątkiem ciągnięcia ręcznie. Dwukrotne kliknięcie na czarnym krzyżyku jest właśnie w dół.
Spróbujmy naprawić te niedociągnięcia za pomocą prostego makra.
Naciśnij skrót klawiaturowy w lewo Alt + F11 lub przycisk Visual Basic Zakładka wywoływacz (Deweloper). Wstaw nowy pusty moduł za pomocą menu Wstaw – moduł i skopiuj tam tekst tych makr:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion Jeśli rng.Cells.Count > 1 Then n = rng.Cells(1).Row + rng.Rows. Liczba - ActiveCell.Row ActiveCell.AutoFill Miejsce docelowe:=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 Jeśli rng.Cells.Count > 1 Then n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Wpisz: =xlWypełnijWartości Koniec Jeśli Koniec Sub
Takie makra:
- można wypełnić nie tylko w dół (SmartFillDown), ale także w prawo (SmartFillRight)
- nie psuj formatu komórek poniżej lub po prawej – kopiowana jest tylko formuła (wartość)
- puste sąsiednie komórki są ignorowane i kopiowanie następuje dokładnie do końca tabeli, a nie do najbliższej przerwy w danych lub pierwszej zajętej komórki.
Dla większej wygody możesz przypisać skróty klawiaturowe do tych makr za pomocą przycisku Makra – Opcje (Makra — Opcje) właśnie tam na karcie. wywoływacz (Deweloper). Teraz wystarczy wprowadzić żądaną formułę lub wartość w pierwszej komórce kolumny i nacisnąć określoną kombinację klawiszy, aby makro automatycznie wypełniło całą kolumnę (lub wiersz):
Piękno.
PS Część problemu z kopiowaniem formuł na koniec tabeli została rozwiązana w Excelu 2007 wraz z pojawieniem się „inteligentnych tabel”. To prawda, że nie zawsze i nie wszędzie są one odpowiednie. Po prawej, Excel nigdy nie nauczył się samodzielnie kopiować.
- Czym są makra, jak ich używać, skąd wziąć kod Visual Basic i gdzie go wkleić.
- Inteligentne tabele w Excelu 2007-2013
- Kopiuj formuły bez przesunięcia linku