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.


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

 

Ukrywanie obiektów Excela

Jedną z metod ochrony pliku Excela przed nieuprawnionym działaniem użytkowników jest ukrywanie niektórych obiektów.
Jest tu kilka możliwości:

    • ukrycie kart arkuszy (zakładek)
      karta Plik –> Zaawansowanie –> Pokaż karty arkuszy
      Po odznaczeniu tej opcji nazwy kart arkuszy

będą niewidoczne

 

    • ukrycie arkuszy
      konkretne arkusze można ukryć. Wystarczy zaznaczyć jeden lub kilka arkuszy, a następnie po kliknięciu prawym przyciskiem myszy wybrać Ukryj z menu podręcznego

      Nie da się ukryć  wszystkich arkuszy – co najmniej jeden musi zostać odkryty.
      Odkrywanie arkuszy odbywa się bardzo podobnie – kliknięciem prawy przyciskiem myszy i wybraniem z menu podręcznego Odkryj…
      Pokaże się formularz, w którym widoczne będą wszystkie ukryte arkusze. Zaznaczając arkusz z listy (tu niestety trzeba to zrobić oddzielnie dla każdego arkusza) i naciskając OK – odkrywamy go.

    • ukrycie arkuszy kodem VBA
      arkusze mogą zostać także ukryte “głęboko”, tak, aby nie można było ich w prosty sposób odkryć. Jest to możliwe w kodzie VBA z wykorzystaniem właściwości Visible arkusza. Do wyboru jest tu:

        • xlSheetHidden
          arkusz zostanie ukryty, tak jak po ukryciu w menu podręcznym
        • xlSheetVeryHidden
          arkusz zostanie głęboko ukryty. Nie będzie widoczny na liście arkuszy do odkrycia w formularzu menu podręcznego
        • xlSheetVisible
          odkrycie arkusza

Przykładowa procedura ukrywająca arkusz może wyglądać tak:

Sub UkryjArkusz()
Worksheets(“Arkusz1”).Visible = xlSheetVeryHidden
End Sub

Odkrycie arkusza wymaga również procedury VBA przypisującej Visible=xlSheetVisible

    • ukrycie paska formuły/nagłówków/linii siatki
      domyślnie widoczne, można ukryć w karcie Widok–>Pokazywanie, odhaczając poszczególne opcje:

przy braku zaznaczenia tych opcji widok będzie wyglądać tak:

    • ukrycie formuł komórki
      jeżeli w komórce znajduje się formuła wynik jej działania jest widoczny w komórce, a sama formuła – na  pasku formuł nad nagłówkami kolumn.

Chcąc ukryć zapis tej formuły, przechodzimy do formatowania komórki i w zakładce Ochrona zaznaczamy opcję Ukryj.

Po włączeniu ochrony arkusza zapisana w komórce formuła jest niewidoczna (choć sam wynik jej działania nadal jest widoczny w komórce):

 

 

Poprawność danych w Excelu

Wymuszanie poprawności danych w Excelu jest jedną z metod zabezpieczania pliku Excel przed nieuprawnioną ingerencją i wpisywaniem błędnych danych, generujących dalsze błędy np. w formułach.
Ograniczenia wprowadzanych danych definiujemy na karcie Dane –> Narzędzia danych –> Poprawność danych

Zaznaczamy komórkę lub zakres komórek, w których chcemy wprowadzić ograniczenie, a następnie klikamy przycisk Poprawność danych. Pojawi się nowy formularz:

Zakładka Ustawienia

Kolejny krok to wybranie z listy kryterium poprawności dla zaznaczonych komórek.
Jest tu kilka możliwości:

    • Dowolna wartość
      – ustawienie domyślne, bez ograniczeń. To opcja, którą należy wybrać przy zdejmowaniu ograniczeń.
    • Pełna liczba
      wartości liczbowe będące liczbą całkowitą.
      Wprowadzane wartości danych można dodatkowo zdefiniować do określonego przedziału liczbowego. Możemy być on wyznaczony na sztywno (czyli z konkretnymi wartościami liczbowymi) lub jako odwołanie do wartości innych komórek.
    • Dziesiętne
      zasady identyczne jak w przypadku Pełna liczba, ale bez ograniczenia do liczb całkowitych.
    • Lista
      tu odwołam się do mojej wcześniejszej notki
      Lista rozwijalna
      gdzie szczegółowo opisałam temat, również w aspekcie tworzenia list dynamicznych
    • Data
      w komórkę musimy wpisać datę (również może być ograniczenie wartości do określonego przedziału dat).
      I na wszelki wypadek przypominam, że w Excelu
      Data to liczba
    • Godzina
      chcąc zadeklarować wpisywanie czasu, również musimy pamiętać, że to liczba, choć w odróżnieniu do daty – ułamkowa.
      Warto też zerknąć tu:
      Czas w Excelu
    • Długość tekstu
      definiujemy ilość znaków jaka musi być w komórce. Jeśli wpiszemy np. większa niż 3 – nie da się wpisać “tak”.
      Warto pamiętać, że spacja też jest znakiem.
    • Niestandardowe
      w tej opcji poprawność danych w komórce definiujemy sami za pomocą formuły, mamy więc możliwość wprowadzenia bardziej złożonych kryteriów.
      Np. dla komórki A1:
      =Czy.Tekst(A1) – ogranicza wprowadzanie danych tylko dla tekstu
      =MOD(A1;10) – wprowadzane tylko wartości podzielnych przez 10
      W podobny sposób można tu zdefiniować inne zasady.

Zakładka Komunikat wejściowy

Domyślnie – jest pusty. Po wypełnieniu pól komunikatu pojawi się on po aktywacji komórki.

Zakładka Alert o błędzie

Wypełnienie pól komunikatu powoduje, że przy wpisaniu wartości niezgodnej z ustawieniami wyświetli się komunikat, a sama wartość nie zostanie wprowadzona do komórki.


Kurs Excel 2010 esencja