Użyteczne Triki

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

Przykłady dla wszystkich lekcji szkolenia Excel 2003: ExcelSzkolenie.pl Cwiczenia Excel 2003.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.

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: W oknie ‘Dostosowywanie’ w Kategorii ‘Rysowanie’ znajdziemy 8 niezwykle przydatnych poleceń, przenieśmy je do paska ikon.

(6 poleceń widocznych na poniższym rysunku oraz polecenia: ‘Rozłóż w poziomie’, ‘Rozłóż w pionie’)

(O tym gdzie znaleźć okno Dostosowywanie i jak go używać przeczytasz w lekcji: ‘Zmiany w menu i tworzenie ikon’)

Kolejnym krokiem będzie wybranie wszystkich wykresów poprzez klikanie na nich kolejno lewym klawiszem myszy z wciśniętym klawiszem Ctrl.

Po czym klikamy ikonę ‘Wyrównaj do lewej’.

Wykresy zostaną równo ułożone, 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’.

W praktyce biznesowej Excel służy z reguły do wykonywania obliczeń i przygotowywania wykresów, wyniki analiz i wykresy 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 tą komórkę.

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

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 (menu Narzędzia) na Karcie ‘Edycja’ została zaznaczona: ‘Stała liczba miejsc dziesiętnych’.

Rozwiązanie: Odznaczyć ‘Stała liczba miejsc dziesiętnych’

Przykład 4.

Problemem: Excel nie chce utworzyć linku. Po wpisaniu w komórkę docelową znaku = i przejściu do pliku w którym znajdują się dane, zamiast utworzenia linku, zaznaczana jest komórka z daną.

Powód: Zostały otworzone dwa programy Excel, zamiast otworzenia dwóch plików w tym samym programie.

Rozwiązanie: Zamknąć jeden z plików, przejść do drugiego i otworzyć plik, który właśnie zamknęliśmy korzystając z polecenia Otwórz znajdującego się w menu Plik w Excelu.

Przykład 5.

(Arkusz: ‘Kopiowanie’)

Problemem: Kopiowany wykres lub inny obiekt po wklejeniu jest innego rozmiaru niż oryginał.

Powód: ‘Widok Powiększenie’ jest ustawione na inną wartość niż 100%, tylko przy 100% rozmiar zostanie zachowany.

Rozwiązanie: Ustawić Powiększenie na 100%. Szybko i Wygodnie możemy to zrobić wciskając klawisz Ctrl i kręcąc pokrętłem myszy.

Przykład 6.

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 C13 do D13 i wciąż pokazuje tą samą wartość, już na pierwszy rzut oka widać że suma danych z kolumny D nie może wynosić 411 tysięcy.

Powód: W Opcjach (menu Narzędzia) na Karcie ‘Przeliczanie’ została zaznaczona opcja ‘Ręcznie’, 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.

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

Przykład 7.

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 ‘Edycja’ znajduje się polecenie ‘Łą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.

Przykład 8.

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 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 9.

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

Obrazek wykorzystany w tym przykładzie można pobrać klikając na logo.jpg lub zapisując na swoim komputerze obrazek znajdujący się poniżej.

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 menu: ‘Format’ 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:

Ukrycie kolumn od K do końca arkusza i wierszy od 43 do końca arkusza.

Zmianę koloru wypełnienia wiersz 1 i kolumny A na szare.

Wyłączenie linii siatki i nagłówków wierszy i kolumn.

Po tych zmianach ostateczna wersja raportu 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’.

Przykład 10.

(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 11.

(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.

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

Przykład 12.

(Arkusz: ‘Skalowanie czcionki’)

Podczas zmiany rozmiaru wykresu, czcionka jest także automatycznie zmieniana co często prowadzi do tego że jest nieczytelna lub za duża. Skalowanie czcionki możemy wyłączyć klikając prawym przyciskiem myszy na obszarze wykresu i wybierając polecenie ‘Formatuj obszar wykresu...’.

Na karcie ‘Czcionka’ okna ‘Formatowanie obszaru wykresu’ odznaczamy ‘Autoskalowanie’.

Różnice w wyglądzie wykresów po zmianie ich wielkości w obu przypadkach przedstawiam poniżej.

Przykład 13.

(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.

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

Symbole $ muszą być tylko przy kolumnach tak aby, dzięki temu ta sama formuła będzie prawidłowo działać dla całej tabeli.

Od razu widać, że najniższe odchylenia występowały najczęściej w przypadku pierwszej metody.

Przykład 14.

(Arkusz: ‘Formatowanie Warunkowe’)

Innym 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 miejsc w których następują zmiany wartości tego współczynnika jest trudne na pierwszy rzut oka 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 ‘Formatowanie warunkowe’, przechodzimy na kartę ‘Obramowanie’ okna ‘Formatowanie komórek’ i zaznaczamy 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 15.

(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. 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 16.

(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.

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ę tą funkcjonalność.

Przykład 17.

(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 menu ‘Wstaw’ 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 lub skopiować hiperłącze z komórki A4 i tylko zmieniać numer oddziału oraz kolumnę komórki z odwołania.

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łącz 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.

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

Przykład 18.

(Arkusz: ‘Uwaga na zaokrąglenia’)

W arkuszu ‘Uwaga na zaokrąglenia’ pokazuję jak po dodaniu zera do liczy która po zaokrągleniu jest pokazywana jako -1 uzyskamy nadal -1 (czego należałoby się spodziewać) oraz to że kiedy do tej samej liczby dodamy jeden to wynik zostanie pokazany jako 1.

Czyli pomimo dodania dokładnie 1 wynik różni się o 2.

Problem ten wynika z tego, że -0,5 jest zaokrąglane w dół i pokazywane jako -1, a 0,5 jest zaokrąglane w górę i pokazywane jako 1.

O tym że liczby ujemne są zaokrąglane inaczej niż dodatnie należy pamiętać podczas analizowania zaokrąglonych danych.

Drugim przykładem zaokrągleń stosowanych przez Excela na które należy uważać jest zasada, że procenty pokazywane na wykresie zawsze muszą sumować się do 100%.

Jeżeli ze zwykłych zaokrągleń wychodzi za mało lub za dużo, Excel podwyższ lub obniża pierwsze kategorie na liście, ignorując zupełnie zasady zdrowego rozsądku.

W poniższym przykładzie, jest dużo danych mających po przecinku 0,4%, które jest zaokrąglane w dół. Excelowi zabrakło 3% więc dodał je do 3 pierwszych kategorii. Pomimo tego że firmy A i B mają dokładnie 9% udziału, na wykresie zostanie podane 10%.

Jeszcze większym kuriozum jest pokazanie danej dla firmy C jako 9%, podczas gdy firma D mająca znacznie większy udział jest pokazana jako 8% i po dokładniejszym przyjrzeniu się szerokości wycinków koła dla C i D, to właśnie ten drugi wycinek wydaje się większy.

Problem przestaje być istotny po dodaniu miejsca po przecinku, a ten sam wykres zyskuje zupełnie inne wielkości procentowe udziałów Firm.

Szkolenie Excel 2003 Szkolenie Excel 2007