Użyteczne Triki

Przykład opisany w tej lekcji dostępny jest w arkuszu Excela: Triki.xlsx, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

W tej lekcji przedstawiam różne pomocne i zupełnie nie związane ze sobą tricki, które nie pasowały do innych lekcji, bądź też były zbyt zaawansowane aby dodać je do lekcji dla początkujących.

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Przykład 1.

(Arkusz: ‘Wyrównanie’)

Problem: Tracimy dużo czasu na wyrównywanie obiektów, tak aby były w jednej linii i równych odstępach.

Rozwiązanie:

Wybieramy wszystkie wykresy poprzez klikanie na nich kolejno lewym klawiszem myszy z wciśniętym klawiszem Ctrl.

Po czym na karcie ‘UKŁAD STRONY’ klikamy ikonę ‘Wyrównaj’ i wybieramy polecenie ‘Wyrównaj do lewej’.

Wykresy zostaną równo ułożone, aby ustalić właściwą odległość od tabeli możemy je przesuwać posługując się strzałkami znajdującymi się na klawiaturze.

Teraz wyrównamy odstępy pomiędzy nimi korzystając z polecenia ‘Rozłóż w pionie’.

Polecenia wyświetlane po kliknięciu ikony wyrównaj udostępniają wiele innych opcji wyrównywania, ich działanie dobrze opisują nazwy poleceń.

W praktyce biznesowej Excel służy z reguły do wykonywania obliczeń i przygotowywania wykresów, wyniki analiz są przedstawiane w postaci prezentacji PowerPoint.

Powyżej opisana funkcjonalność Excela jest dostępna i działa dokładnie tak samo w programie PowerPoint, gdzie jest znacznie bardziej użyteczna.

Przykład 2.

(Arkusz: ‘Formuła’)

Problem: W formule nie można wprowadzić adresu komórki, poprzez kliknięcie jej myszką, ponieważ formuła zasłania komórkę, którą chcemy kliknąć (E8).

Rozwiązanie: Adresy komórek w formułach szybciej jest wprowadzać wybierając komórki korzystając ze strzałek na klawiaturze.

Alternatywne Rozwiązanie: Należy zmienić formatowanie komórki na wyrównaj do lewej, wtedy długa formuła będzie ‘wystawać’ w prawo nie utrudniając wybierania innych komórek.

Przykład 3.

Problem: Po wpisaniu w komórkę liczby np. 1000, w komórce pojawia się inna liczba np. 10.

Powód: W ‘Opcjach programu Excel’ na Karcie ‘Zaawansowane’ zostało zaznaczone: ‘Automatycznie wstaw przecinek dziesiętny’.

Rozwiązanie: Odznaczyć

Przykład 4.

Problemem: Excel źle oblicza / nie dokonuje zmian w formułach po wprowadzeniu / zmianie danych.

W poniższym przykładzie suma została skopiowana z komórki C16 do D16 i wciąż pokazuje tą samą wartość, już na pierwszy rzut oka widać że suma danych z kolumny D nie może wynosić ponad 2 miliony.

Powód: W Opcjach na Karcie ‘Formuły’ została zaznaczona opcja ‘Ręczne’, opcja ta jest użyteczna w przypadku dużych plików, które wolno się przeliczają, plik jest przeliczany dopiero po wciśnięciu F9, przed zapisem lub gdy zmienimy ‘Opcje obliczania’ na ‘Automatyczne’.

Rozwiązanie: Zmienić na Obliczanie: ‘Automatyczne’.

Przykład 5.

Problemem: Plik z którego korzystamy posiada dużą ilość linków do innego pliku utworzonego i aktualizowanego przez inną osobę.

Dane zostały zmienione i nowy plik ma ten sam układ danych, ale znajduje się w innym katalogu na serwerze i ma inną nazwę.

Rozwiązanie: w Menu ‘DANE’ znajduje się polecenie ‘Edycja łącza’.

W oknie ‘Edytowanie łączy’ klikamy przycisk ‘Zmień źródło’.

Wybieramy nową wersję pliku, z której mają być pobierane dane i klikamy OK.

Wszystkie linki w całym pliku (a nie tylko arkuszu, który był aktywny), jakie odnosiły się do poprzedniej wersji budżetu zostały zmienione i teraz pobierają dane z pliku który wskazaliśmy.

Problemy mogą się pojawić jeśli istnieją różnice w nazwach arkuszy, bądź właściwe dane w obu plikach nie są dokładnie w tych samych komórkach. O tym jak radzić sobie ze zmianą układu wierszy i kolumn w nowej wersji pliku źródłowego piszę w kolejnym przykładzie.

Przykład 6.

Problemem: Utworzyliśmy dużą ilość linków, po czym format pliku źródłowego zmienił się i tabele przesunęły się, teraz dane znajdują się w innych wierszach i kolumnach.

Rozwiązanie: Jeśli układ tabel nie zmienił się, najprościej będzie:

1. Otworzyć stary plik źródłowy i plik docelowy (z linkami).

2. Przekształcić stary plik źródłowy tak aby miał format dokładnie taki jak nowy plik źródłowy. Linki w pliku docelowym automatycznie zmieniają się podczas dodawania/usuwania kolumn i wierszy w pliku źródłowym.

3. Zamknąć plik docelowy zapisując zmiany.

4. Zamknąć stary plik źródłowy nie zapisując zmian.

5. Otworzyć plik docelowy i zmienić źródło ze starego pliku źródłowego na nowy.

6. Linki będą odnosić się do właściwych komórek w nowym pliku źródłowym

Sposób ten może być również używany do kopiowania linków w tym samym układzie odnoszących się do innej tabeli tego samego pliku. Wystarczy tylko zrozumieć, że kiedy oba pliki są otwarte linki są automatycznie zmieniane, a kiedy plik docelowy zostanie zamknięty można wrócić do poprzedniej wersji pliku używając polecenia cofnij lub zamknąć go bez zapisywania zmian.

Jeżeli układ tabel jest zupełnie inny trzeba będzie po zamienia pliku źródłowego, przy użyciu ‘Zmień źródło’, zmieniać każdą grupę z linków osobno korzystając z polecenia zamień (Ctrl+H) i np. zamieniać dla wybranego obszaru pliku fragment linku: 'Budżet 2011'!$D$ na 'Budżet 2011'!$L$.

Zawsze zamieniamy dłuższy tekst, bo zamiana samego D na L spowodowała by także zamianę litery d na l w słowie Budżet i link przestałby działać.

Przykład 7.

(Dane do samodzielnego wykonania arkusz: ‘Tło Arkusza 1’, Rozwiązanie arkusz: ‘Tło Arkusza 2’)

Obrazek wykorzystany w tym przykładzie znajduje się w zbiorze plików przeznaczonych do ćwiczeń.

Problemem: Chcemy wprowadzić w tle naszej tabeli logo firmy lub oficjalne korporacyjne tło prezentacji, aby nasze zestawienie wyglądało bardziej profesjonalnie.

Rozwiązanie: Z karty: ‘Układ strony’ wybieramy polecenie ‘Tło’,

Po czym wybieramy odpowiedni obraz, w naszym przykładzie logo.jpg.

.

Na poniższym rysunku widać, jak wygląda obrazek w tle arkusza w 2 przypadkach gdy komórki tabeli mają tło i gdy go nie posiadają.

Tak przygotowany raport możemy dalej modyfikować, np. poprzez zmianę koloru wypełnienia komórek wokół tabeli pozostawiając miejsce ‘ramkę’ z logo firmy, oraz wyłączenie linii siatki i nagłówków.

Po tych zmianach ostateczna wersja raportu może wyglądać tak jak na poniższym rysunku.

Sugeruję dwa razy przemyśleć projekt przed wprowadzeniem tzw. ‘bajerów’ często proste rozwiązania wyglądają najbardziej profesjonalnie.

Więcej na ten temat w lekcji ‘Profesjonalne Raporty dla Zaawansowanych’.

Po dodaniu tła do arkusza ikona ‘Tło’ zamienia się w ‘Usuń tło’.

Przykład 8.

(Arkusz: ‘&’)

Problemem: Wpisywanie długiej nazwy funkcji ZŁĄCZ.TEKSTY() wydaje się nam zbyt czasochłonne, a formuły używające wielu tych funkcji są bardzo długie.

Rozwiązanie: Taką samą funkcjonalność ma znak &, co przedstawia poniższy rysunek.

Przykład 9.

(Arkusz: ‘Łączenie Wykresów’)

Autor poniższego arkusza przygotował 2 wykresy udziałów rynkowych dla Firm A i B, po czym uznał, że dane będą wyglądały czytelniej jeśli umieści się je na 1 wykresie.

Zamiast zmieniać jeden z wykresów szybciej będzie:

1. Zaznaczyć jeden z wykresów i skopiować go (Ctrl+C),

2. Zaznaczyć drugi wykres

3. Wkleić skopiowany wykres (Ctrl+V) co spowoduje połączenie obu wykresów, wcześniej wybrane formatowanie zostanie zachowane.

4. Legenda niestety nie zostanie automatycznie zaktualizowana, należy ją ponownie dodać.

Rzeczywiście dane wyglądają teraz lepiej, od razu rzuca się w oczy ogromna współzależność udziału rynkowego obu firm.

Przykład 10.

(Arkusz: ‘Formatowanie Warunkowe’)

Formatowanie warunkowe może mieć wiele zastosowań, te standardowe zostały opisane w lekcji poświęconej Formatowaniu Warunkowemu, poniżej przedstawiam jeszcze dwa użyteczne zastosowania.

W poniższej tabeli przedstawione są średnie odchylenia prognoz od zrealizowanej sprzedaży. Chcielibyśmy szybko ustalić, która z prognoz w największej ilości przypadków była najbliżej prawdy (miała najniższe średnie odchylenia).

Zaznaczamy obszar C3:E14 i wybieramy Formatowanie warunkowe a następnie polecenie ‘Nowa reguła formatowania’.

W górnej części okna pokazanego na poniższym rysunku wybieramy opcję ‘Formatuj tylko komórki zawierające’ i wprowadzamy formuły zgodnie z rysunkiem.

Jeżeli wartość w komórce jest równa minimum z jej wiersza, tło komórki zmieni się na zielone.

Dzięki wstawieniu symboli $ tylko przy kolumnach, ta sama formuła będzie prawidłowo działać dla całej tabeli.

Po kliknięciu dwóch przycisków OK od razu widać, że najniższe odchylenia występowały najczęściej w przypadku pierwszej metody.

Przykład 11.

(Arkusz: ‘Formatowanie Warunkowe’)

Innym nietypowym zastosowaniem formatowania warunkowego jest wyróżnienie miejsc, w których dane zmieniają się. Na poniższej liście pewien współczynnik może przyjmować kilka wartości, ponieważ rozpoznanie na pierwszy rzut oka miejsc, w których następują zmiany wartości tego współczynnika jest trudne, posłużymy się formatowaniem warunkowym.

Jeżeli wartość w komórce nie jest równa wartości komórki powyżej (z adresu należy usunąć symbole $) komórka z zakresu i powyższa komórka powinny zostać oddzielone linią.

Aby wybrać oddzielenie linią należy po kliknięciu ‘Formatuj...’ w oknie ‘Nowa reguła formatowania’, przejść na kartę ‘Obramowanie’ okna i zaznaczyć górną krawędź, nie zmieniamy ustawień dla pozostałych krawędzi.

Wynikiem będzie pojawienie się oddzielających linii w miejscach gdzie wartość współczynnika zmienia się.

Przykład 12.

(Arkusz: ‘Uzupełnienie’)

Dane importowane z korporacyjnych systemów informatycznych często nie mają nagłówków dla każdego wiersza a jedynie dla pierwszego z danej grupy, dodatkowo komórki mogą być połączone. Z danych w tym formacie nie można utworzyć tabeli przestawnej, nie można ich filtrować, utrudnionych jest także wiele innych operacji. Najlepiej byłoby uzupełnić brakujące opisy wierszy.

Jeżeli komórki są połączone zaznaczamy je i klikamy ikonę 'Scal i wyśrodkuj' co spowoduje ich rozdzielenie, opisy będą znajdowały się zawsze w pierwszej komórce z danej grupy.

Następnie wciskamy klawisz F5 i w oknie 'Przechodzenie do' wybieramy 'Specjalnie...',

Po czym zaznaczamy opcję 'Puste'.

i klikamy OK.

Zaznaczone zostały puste komórki w wybranym obszarze, aktywną komórką jest C4. Wprowadzamy w nią formułę =C3 i wciskamy kombinację klawiszy Ctrl+Enter, dzięki której formuła ta znajdzie się we wszystkich zaznaczonych komórkach.

Ostatnim etapem będzie zastąpienie formuł wartościami, poprzez skopiowanie kolumn B i C i wklejenie w to samo miejsce wartości (wklej specjalne - wartości).

Przykład 13.

(Arkusz: ‘Przeciąganie’)

Większość użytkowników Excela chcąc wstawić kolumnę D z poniższej tabeli pomiędzy kolumny F i G, najpierw zaznaczyłoby kolumnę G, użyłoby polecenia wstaw kolumnę i w to miejsce przeciągnęłoby kolumnę D, po czym została by jeszcze pusta kolumna D, którą należałoby skasować. O wiele szybszym sposobem będzie:

1. Zaznaczenie kolumny D.

2. Przeciągnięcie jej pomiędzy kolumny F i G, przytrzymując klawisz Shift.

Podczas przeciągania z wciśniętym klawiszem Shift, pojawi się linia symbolizująca miejsce w którym zostanie umieszczona przeciągana kolumna, klawisz Shift zwalniamy po zwolnieniu lewego klawisza myszy.

Na skutek przeciągnięcia kolumny D pomiędzy F i G, kolumny E i F zostały przesunięte w lewo.

Dokładnie tak samo można przeciągać na właściwe miejsce wiersze, zakresy, bądź pojedyncze komórki. Sugeruje wykonanie kilku prób na tabeli z tego ćwiczenia.

Jeżeli podczas przeciągania wciśniemy klawisz Ctrl, dane zostaną skopiowane, kolumna, wiersz, zakres lub komórka pojawi się w nowym miejscu i jednocześnie nadpisze komórki na które została przeciągnięta. Po kilku próbach każdy opanuję również tą funkcjonalność.

Przykład 14.

(Arkusz: ‘Hiperłącze’)

Hiperłącza kojarzą się nam z Internetem i mało kto widzi dla nich zastosowanie w plikach Excela.

Okazują się jednak bardzo przydatne podczas pracy z dużymi dokumentami.

W poniższym przykładzie dane sprzedaży dla 4 oddziałów firmy pokazane są w tabeli, wielokrotne przewijanie tabeli jest czasochłonne posłużymy się więc hiperłączem aby szybko móc znaleźć interesujące nas dane.

Ustawiamy A4 jako aktywną komórkę i z karty ‘WSTAWIANIE’ wybieramy polecenie ‘Hiperłącze...’

W okienku ‘Połącz z:’ wybieramy ‘Miejsce w tym dokumencie’.

Jako tekst do wyświetlenia wpisujemy: Oddział 1, a jako odwołanie podajemy komórkę C2.

Po kliknięciu OK link pojawi się w komórce która była aktywna, a po jego kliknięciu będzie przenosił do komórki C2.

Przygotowując kolejne hiperłącza możemy skorzystać z metody opisanej powyżej ale szybciej będzie skopiować hiperłącze z komórki A4 i tylko zmieniać kolumnę komórki z odwołania, numer oddziału sam zmieni się podczas kopiowania.

Aby edytować hiperłącze klikamy je prawym klawiszem myszy i wybieramy polecenie ‘Edytuj hiperłącze...’.

Okno ‘Edytowanie hiperłącza’ różni się od okna ‘Wstawianie hiperłącza’ wyłącznie nazwą.

Po wstawieniu wszystkich 5 hiperłączy uzyskamy bardzo praktyczne narzędzie ułatwiające nawigację w dużym dokumencie.

Warto zauważyć, że dzięki użyciu polecenia ‘Zablokuj okienka’ hiperłącza są cały czas dostępne.

Po pierwszym użyciu hiperłącza zmieniają kolor, dzięki czemu wiemy, które były już używane, jeżeli nam to przeszkadza można zmienić ich kolor czcionki na niebieski, dzięki czemu nie będą zmieniać koloru.

Użyteczność tego rozwiązania jest dobrze widoczna przy znacznie większych zbiorach danych niż ten użyty w tym przykładzie.

Hiperłącza mogą się odnosić także do komórek w innych arkuszach, dzięki czemu możemy przygotować swego rodzaju ‘Spis Treści’ dla złożonego raportu.