Kategoria: Excel

Tabela w Excelu

Tabela w Excelu to nie tylko ładnie opisane, sformatowane i obramowane komórki – takie coś nazywam zawsze tylko tabelką. Prawdziwa tabela to obiekt, mający swoje właściwości  i metody.

Załóżmy, że mamy taką tabelkę:

Chcąc przekształcić te komórki w prawdziwą tabelę, zaznaczamy je, a następnie na karcie Wstawianie wciskamy przycisk Tabela. Można też skorzystać ze skrótu klawiaturowego  Ctrl+T.

Pojawi się formularz, w którym można zmienić/zatwierdzić zakres danych jakie mają być tabeli.

Po wstawieniu tabeli do arkusza, aktywacja dowolnej jej komórki powoduje wyświetlenie nowej karty Projektowanie.

kliknij, aby powiększyć

Domyślnie nadawana nazwa dla obiektu tabeli to Tabela1 (i dalej Tabela2, Tabela3…). Dostępne są też wzory formatowania, możliwość wstawienia wiersza podsumowania i.in.

Mają c już tabelę – na co warto zwrócić uwagę?  Jest kilka istotnych właściwości:

nazwy strukturalne

    • każda tabela musi mieć nagłówek. Jeżeli przy tworzeniu tabeli nie ma wiersza spełniającego takich kryteriów, zostanie automatycznie nadany z nazwami odnoszącymi się do numerów kolumn (Kolumna1, Kolumna2… )
    • nagłówki tabelki są nazwami strukturalnymi dla wartości w poszczególnych kolumnach. Widać to chociażby  w formułach, w których argumentami są wartości tabeli – nie ma tu odniesień to adresów komórek tylko właśnie do nazw strukturalnych
      właściwości ogólne
    • w wierszu nagłówka automatycznie wbudowane są znaczki autofiltra pozwalające na filtrowanie rekordów tabeli,
    • wstawienie formuły w pierwszym wierszu tabeli powoduje, że cała kolumna automatycznie zostaje wypełniona tą formułą, nie trzeba nic przeciągać czy kopiować,
    • chcąc dodać kolejny wiersz tabeli -ustawiamy się w ostatniej kolumnie ostatniego wiersza i naciskamy  Tab. Nowy wiersz zostanie automatycznie dodany na końcu tabeli, jeszcze przed wierszem podsumowania. W kolumnach z formułami, pojawią się one także w tym nowym wierszu.
    • w wierszu podsumowania można modyfikować funkcje agregacji. Nie musi to być suma, może być średnia, liczba rekordów itp.
    • po zastosowaniu filtra tabeli – automatycznie zmieniają się wartości wiersza podsumowania, uwzględniając tylko widoczne wartości.

Definiowanie własnych funkcji

W Menedżerze nazw Excela w prosty sposób można przypisać własną nazwę nie tylko zakresom komórek, ale również formułom. Warto to stosować w sytuacji, gdy w większej ilości komórek wykorzystywana jest bardziej złożona formuła i opłacalne jest skrócenie takiej formuły.

Chcąc na przykład wykorzystać w jakiejś tabeli do dalszych obliczeń ilość dni do końca miesiąca, można do menedżera zadań dodać definicję takiej funkcji:

Pod nazwą DKM zapisana jest funkcja:
=NR.SER.OST.DN.MIES(DZIŚ();0)-DZIŚ()
Można jej używać jak każdej normalnej funkcji wbudowanej Excela, wpisując do komórki:
=DKM

Może też oczywiście wykorzystana jako argument w innych formułach.

Komórki scalone

Komórki scalone to zakres komórek  scalony do jednej komórki. Zakres ten musi mieć kształt prostokąta (kwadratu).
Narzędzie scalania komórek jest dostępne na karcie Narzędzia główne, grupa opcji Wyrównanie.

Po zaznaczeniu zakresu komórek i naciśnięciu przycisku Scal i wyśrodkuj następuje scalenie komórek do jednej. Jeżeli scalane komórki nie są puste, wyświetli się komunikat ostrzegawczy przed opróżnieniem ich zawartości. Nowa, scalona komórka będzie miała adres i zawartość pierwszej komórki u góry z  lewej strony scalanego zakresu.
Przycisk ten działa jak przełącznik – ustawiając się na którejś z komórek zakresu i naciskając go ponownie – nastąpi zdjęcie scalenia komórek.

Czy warto stosować komórki scalone? Z pewnością tak, choć trzeba pamiętać, że pełnią one rolę głównie ozdobną. Pozwalają na ładne przedstawienie danych w formie tabelki, ale mogą się pojawić problemy przy analizie danych czy odwołaniach funkcji. Jeżeli argumentem jakieś funkcji, w zupełnie innym miejscu arkusza czy skoroszytu jest wartość którejś z dalszych komórek zakresu, wstawiona będzie wartość pusta.
Na screenie widoczny jest przykład, gdy w kolumnie A wstawiona jest data, a w kolumnie B – formuła wyliczająca ilość dni do końca miesiąca w stosunku do daty z kolumny A w tym samym wierszu:

Wartości są prawidłowe tylko w wierszu 2 i 12 czyli tam, gdzie rozpoczyna się scalony zakres (A2 i A12). Wstawiając więc taką formułę czy też zmieniając zakres , trzeba uważać.

 

 

Kurs Excel - zaawansowane techniki

Makra – odblokowywanie i uruchamianie

Jedną z domyślnych właściwości Excela jest ochrona przed otwarciem skoroszytów z makrami. Oczywiście nie chodzi tu o automatyczną blokadę wszystkich plików Excela, a tylko tych nieznanego pochodzenia, mogących zawierać szkodliwe oprogramowanie. Problematyczne mogą być tu przede wszystkim pliki pobrane z internetu czy otrzymane mailem od nieznanej nam osoby, lepiej na nie uważać.

W Excelu jest kilka opcji postępowania z plikami zawierającymi makra. Można je ustawić w Centrum Zaufania.
Karta Plik –> Opcje

Kolejny krok do przejście do Centrum Zaufania i kliknięcie przycisku Ustawienia Centrum Zaufania…

W nowym formularzu wybieramy Ustawienia makr

Domyślnie jest tu zaznaczona opcja druga: wyłącz wszystkie makra i wyświetl powiadomienia. I jest to moim zdaniem opcja optymalna – te ustawienia dotyczą  wszystkich plików otwieranych w Excelu, więc lepiej się zabezpieczyć.

Od tej pory pliki z makra otwierać się będą z komunikatem:

Jeżeli jesteśmy pewni pliku, wystarczy wcisnąć przycisk Włącz zawartość i makra zostaną włączone. Natomiast te, których nie jesteśmy pewni i nie naciśniemy przyciski – będzie można obejrzeć, edytować, jednak makra nie będą działać. Jednak uwaga – w przypadku bardziej złożonych projektów z oprogramowanymi zdarzeniami arkusza czy skoroszytu – bez makr mogą pojawić się problemy i całość nie będzie działać tak, jak powinna. Dotyczy to także funkcji użytkownika czyli funkcji zdefiniowanych kodem VBA.

Ponieważ jednak takie każdorazowe uruchamiane pliku z tym przyciskiem może być męczące. W takim przypadku warto odbezpieczyć cała grupę plików Excela, uznanych jako bezpieczne.
Robimy to także w ustawieniach Centrum Zaufania, ale tym razem wybieramy po lewej stronie Zaufane lokalizacje, a następnie naciskamy przycisk Dodaj nową lokalizację….

W nowym formularzu wystarczy wybrać folder (i ewentualnie znajdujące się w nim podfoldery), w którym chcemy przechowywać pliki Excela z makrami. Po zatwierdzeniu – pliki z tego folderu/folderów będą otwierać się automatycznie z włączonymi makrami.

 

 

Kurs Excel - zaawansowane techniki

 

Połączenie z innym plikiem Excela

Jak połączyć się z plikiem Excela, gdy np.na komputerze firmowym są ograniczone możliwości zainstalowania np.Power Query i nie można skorzystać z tego rozwiązania? Można to zrobić przez łącza w komórkach.

Załóżmy, że mamy taki plik udostępniony np. na serwerze firmowym:

Plik jest udostępniony, różni użytkownicy dodają/edytują dane, nie można w nim wstawić np. tabeli przestawnej, nie wszystkie makra działają. Na naszym komputerze lokalnym tworzymy własny plik do analizy danych (jeśli chcemy zastosować makra – to z rozszerzeniem .xlsm). Najprościej skopiować nagłówek – tym bardziej, że do analizy pewnie warto będzie dołożyć dodatkowe kolumny z obliczeniami.

Następnie wchodzimy do komórki A2, wpisujemy znak równości

a następnie, po przejściu do komórki A2 w pliku źródłowym, naciskamy Enter.

W komórce A2 pliku lokalnego pojawia się formuła:
=TabelaKsiazki.xlsx!$A$2
Teraz trzeba ją przerobić zmieniając adres bezwzględny na względny czyli
=TabelaKsiazki.xlsx!A2
Dodatkowo – warto pamiętać, że jeżeli dana  komórka w pliku źródłowym jest pusta – w komórce z nią połączonej pokaże się zero. A ponieważ zero jest też wartością – lepiej tak zmodyfikować formułę, aby pobierała tylko wartości z niepustych komórek.
=JEŻELI(TabelaKsiazki.xlsx!A2=””;””;TabelaKsiazki.xlsx!A2)

No i na końcu przeciągamy formułę w prawo

i cały wiersz – w dół

I już – łącza do komórek są wstawione. Od tej pory wszystkie zmiany wprowadzone w pliku źródłowym będą odzwierciedlone w pliku lokalnym. Plik źródłowy może pozostać zamknięty, istotne jest odświeżanie łączy – karta Dane grupa opcji Połączenia.

Warto też zauważyć,  że przy zamkniętym pliku źródłowym automatycznie zmieniają się formuły w komórkach – pojawia się pełna  nazwa pliku :

 

 

 

Power Query do pliku Excela

Poprzednia notka PQ do Arkuszy Google opisuje jak ściągnąć dane z pliku Excela w internecie. W codziennej pracy często mamy jednak do czynienia z plikami udostępnionymi gdzieś na serwerze firmowym. Dane tam zapisane są nam potrzebne do analizy i raportowania. Oczywiście w prosty sposób można to zrobić również za pomocą Power Query.

Na karcie Power Query przechodzimy do grupy Pobierz dane zewnętrzne, naciskamy przycisk Z pliku i wybieramy Z programu Excel.

Otworzy się okno dialogowe, w którym wskazujemy plik, z którego chcemy pobrać dane.
Po wybraniu pliku w formularzu Nawigatora zaznaczamy arkusz z danymi.

Następnie naciskamy załaduj i wszystkie dane z pliku źródłowego znajdą się w pliku docelowym. Możemy w dowolny sposób je filtrować, obliczać czy analizować w tabeli przestawnej, jednocześnie nie wpływając na pracę innych użytkowników źródłowego pliku udostępnionego. W dodatku wszystkie zmiany wprowadzone przez innych użytkowników – będą się aktualizować po każdym odświeżeniu połączenia.
Można to zrobić w dowolnym momencie po wejściu w tabelę i naciśnięciu przycisku Odśwież na karcie Dane lub naciśnięciu przycisków Alt+F5.

Warto też zajrzeć do Właściwości połączenia… – w tym miejscu można ustawić zasady automatycznej aktualizacji danych:

W identyczny sposób można też pobierać dane nie tylko z plików Excela, ale również plików tekstowych csv.

 

 

 

Power Query do Arkuszy Google

Power Query to  świetne narządzie pozwalające na wstawianie do pliku Excela łączy z danymi z różnych źródeł m.in. stron internetowych, innych plików Excela, baz danych itp.
Opis jak go pobrać i zainstalować jest tu:
Power Query

Świetnie nadaje się także do pobierania danych z plików udostępnionych czy Arkuszy Google.  Ma to szczególne znaczenie dla analizy danych. Takie współużytkowane pliki mają znaczne ograniczenia w funkcjonowaniu makr (niektóre w ogóle nie działają), wstawiania tabel itp. Arkusze Google to w ogóle pliki Excela bez makr, więc jedyna funkcjonalność to formuły w komórkach. Poprzez PQ można jednak pobrać dane z takich plików do własnego, lokalnego pliku i tu już zastosować bez problemu wszystkie możliwości Excela. Każda zmiana pliku źródłowego jest automatycznie widoczna po aktualizacji łączy.

Jak to zrobić? Dla potrzeb tej notki utworzyłam i udostępniam taki Arkusz Google, więc pokażę to na konkretnym przykładzie. Plik o nazwie TabelaKsiazki.xlsx jest tu:
TabelaKsiazki na dysku Google
– można do niego wejść, a następnie otworzyć i edytować jako Arkusz Google

Po wejściu kopiujemy link do arkusza:

Jeśli ktoś chce przetestować – to link do mojego pliku to:

https://docs.google.com/spreadsheets/d/1Hsq7m2y2O-xgPyV2JeUP4pA7nrzVltUr/edit#gid=737587641

Chcąc wstawić link jako źródło danych do PQ trzeba go jednak zmodyfikować.  Usuwamy to, co za ostatnim ukośnikiem i wstawiamy tam frazę: export?format=xlsx

https://docs.google.com/spreadsheets/d/1Hsq7m2y2O-xgPyV2JeUP4pA7nrzVltUr/export?format=xlsx

I taki link możemy już wstawiać do pliku Excela, gdzie za pomocą PQ chcemy pobrać dane.

Kolejny krok to przejście na kartę Power Query

Po naciśnięciu OK pokaże się okienko z obiektami widocznymi w tym pliku:

Widać tu:

    1. Arkusz o nazwie TabeleKsiazki.
    2. Tabelę w tym arkuszu, również o nazwie TabelaKsiazki

Po prawej stronie podgląd tych obiektów. Jeżeli wybierzemy tabelę – to wprawdzie wszystkie zmiany w tabeli będą się przenosiły do pliku lokalnego, ale już dodanie nowych wierszy nie będzie widoczne. Bezpieczniej jest więc wybrać jako źródło danych cały arkusz.

A tu krótki filmik z mojego kanału YT ilustrujący opisane pobieranie Arkuszy Google do Power Query.

 

 

Praca z plikiem udostępnionym

Plik udostępniony, znajdujący się gdzieś na serwerze firmowym, jest bardzo dobrym rozwiązaniem w bieżącej pracy. Jednocześnie może nam nim pracować i edytować dane wiele osób. Niestety, trochę gorzej z analizą i przetwarzaniem danych. Przede wszystkim w pliku udostępnionym ograniczone jest działanie kodu VBA, niektóre makra i procedury po prostu nie mogą działać, nie można wstawiać tabel itp. Ma to swoje uzasadnienie – jednocześnie pracujący użytkownicy nie mogą sobie przeszkadzać i niweczyć nawzajem swojej pracy. Poza tym – im więcej osób jednocześnie edytuje dane, tym bardziej plik jest obciążony. Trudno też zastosować zaawansowane metody.
Oczywiście, w takich przypadkach zawsze można zrobić kopię lokalną i na niej pracować, ale przy kolejnej analizie – trzeba operację powtórzyć. Zdecydowanie prościej jest stworzyć własny plik na komputerze i połączyć go z danymi w źródłowym pliku danych. W ten sposób można analizować dane w dowolny sposób, tworzyć np. tabele przestawne oparte na prawdziwych danych, przygotowywać zestawienia itp. A każde odświeżenie łączy – powoduje, że zawsze pracujemy na aktualnych danych źródłowych.

Są 2 główne sposoby na takie połączenie pliku:

W kolejnych notkach opiszę szczegółowo obydwa sposoby.

Kompresja obrazów w Excelu

Każdy, kto kiedyś wstawiał obrazy do Excela wie dobrze, że przy większej ich ilości  skoroszyt “puchnie” znacznie zwiększając swój rozmiar. Można sobie z tym poradzić kompresując osadzone obrazy i znacznie zmniejszając plik.

Jak to zrobić? Pokazuję to tu:

Zapraszam na mój kanał Excela na YT.

Kurs Excel - zaawansowane techniki

Wymuszenie włączenia makr w skoroszycie Excela

Wielu metod ochrony pliku Excela przed wprowadzaniem nieuprawnionych zmian opiera się na makrach i/lub kodzie VBA. Wszystko działa dobrze i sprawnie pod warunkiem, że otwierający plik użytkownik ma włączone makra. Nie da się tego zrobić automatycznie (i dobrze, szczególnie w przypadku plików nieznanego pochodzenia), ale jak spowodować, żeby użytkownik sam to zrobił? Trzeba spowodować, aby plik był bezużyteczny i tylko włączenie makr pozwoli na pracę.

Szczegóły mogą być tu różne, ale generalnie podstawowe zasady powinny wyglądać tak:

    • wszystkie arkusze ukryte kodem VBA
    • jeden dodatkowy arkusz startowy, z przyciskiem makra włączającego dostęp do skoroszytu czyli odkrywające arkusze robocze i zakrywające arkusz startowy. Jeżeli makra będą wyłączone – makro nie zadziała.
    • makro przy zdarzeniu przed zamknięciem skoroszytu (Workbook_BeforeClose) ukrywające wszystkie arkusze robocze i odkrywające arkusz startowy.

To powinno wystarczyć. Wszystkie inne sposoby ochrony pliku można dostosować do własnych potrzeb.

 

 

Kurs Excel dla księgowych