Praca z Excelem – 100 porad i tricków (5/5)

4 czerwca 2015 Redakcja Conowego.pl 548 Views Brak komentarzy

Cykl poświęcony poradom i trickom ostatecznie przerodził się w swego rodzaju kurs, poradnik dotyczący pracy z Excelem w ogóle. Przeszliśmy przez sporą część funkcji, jaki oferuje arkusz kalkulacyjny, ale – mam nadzieję – po drodze nie zabrakło również obiecanych tricków, nieznanych wcześniej osobom stosunkowo zaawansowanym w pracy z arkuszem kalkulacyjnym. W ostatnim artykule znajdziecie porady, które nie znalazły się w poprzednich poradnikach – są one więc bardziej zróżnicowane. Będzie trochę o formatowaniu, o formułach, o wykresach, sortowaniu i… sami zobaczycie.
Postanowiłem jednak ostatecznie zrezygnować z wprowadzania elementów tabel przestawnych i makr, co zakładałem z początku. Zdecydowałem, że tym – nazwijmy to – nieco bardziej zaawansowanym elementom Excela poświęcimy osobne poradniki.
Zanim zaproszę Was do zapoznania się z treścią porad, zachęcam do odwiedzenia również poprzednich artykułów z cyklu:
Praca z Excelem – 100 porad i tricków (1/5)
Praca z Excelem – 100 porad i tricków (2/5)Praca z Excelem – 100 porad i tricków (3/5) Praca z Excelem – 100 porad i tricków (4/5)
W ostatnim artykule porady będą miały nieco bardziej zróżnicowany charakter, niekoniecznie będą się ze sobą łączyły. Pamiętajcie jednak, że to właśnie łącznie różnych funkcji i formuł stanowi klucz do efektywnej pracy z programem Excel.

#81 Pobieranie danych z internetu
Podobnie jak w poprzednich poradach, wyjdźmy i w tej od problemu, a następnie przedstawmy sobie rozwiązanie. Chcemy tym razem zautomatyzować proces aktualizowania danych w skoroszycie. Załóżmy że pracujemy na danych odnośnie wyceny jednostek funduszy inwestycyjnych, która na ogół dokonywana jest w każdym dniu, w którym odbywają się sesje giełdowe.
Jeżeli danych, które potrzebujemy, nie ma zbyt wiele, to można codziennie wchodzić na odpowiednie strony i zwyczajnie je przepisywać. Gdy jednak pracujemy na dużej ilości danych, lub gdy po prostu chcemy zautomatyzować każdą możliwą czynność (do czego powinniśmy dążyć pracując w Excelu),powinniśmy skorzystać z jednej z funkcji Excela.
Przechodzimy do formatki Dane, stamtąd, z sekcji Dane zewnętrzne, wybieramy opcję Z sieci Web. Otworzy się dodatkowe okienko, przypominające przeglądarkę internetową. Korzystając z niej możemy wybrać adres strony, z której chcemy pobierać aktualizujące się dane. Będą one rozpoznane przez przeglądarkę tylko wtedy, gdy pojawi się przy nich symbol czarnej strzałki na żółtym tle. Należy wybierać takie źródła, w których nasze dane są przedstawione w możliwie najprostsze tabeli, zwłaszcza bez odstępów i przedziałów w środku. Link lepiej jest też skopiować ze zwykłej przeglądarki.

Po wybraniu tabeli wciskamy Importuj. Następnie należy wskazać miejsce, w którym rozpocznie się wklejania pobranych danych. Warto jednak wejść też do zakładki Właściwości, skąd możemy wybrać częstotliwość aktualizacji, jak i to, czy stare dane mają być nadpisywane, czy też nowe mają się pojawiać obok nich.

#82 Zwiększ funkcjonalność górnego paska
Piszemy o trickach bardziej lub mniej zaawansowanych – ten zdecydowanie należy do pierwszej grupy. Być może jednak nie wszyscy zauważyli małego symbolu trójkąta w lewym rogu górnego paska. Po kliknięciu w niego rozwija się lista dodatkowych opcji, które możemy umieścić w formie skrótu na górnym pasku. Ja z listy (którą można jeszcze rozszerzyć wchodząc we opcję Więcej poleceń) wybrałem przede wszystkim ikonkę, która otwiera mi nowy arkusz.

#83 Średnia ważona – szybki sposób liczenia
W Excelu nie ma funkcji bezpośrednio wyliczającej średnią ważoną. Jest jednak sposób na prawidłowe i szybkie wyliczenie np. średniej z toku studiów. Mamy arkusz jak poniżej, przedstawiający w kolumnie po lewej uzyskane oceny, a po prawej – punkty ECTS, odpowiadające wadze danego przedmiotu w ogólnym rozliczeniu.
Korzystamy z funkcji SUMA.ILOCZYNÓW, by policzyć sumę mnożenia – ocena * punkty ECTS z każdego wiersza z osobna. Następnie całość dzielimy korzystając z formuły SUMA, zliczającej ilość wag, czyli w naszym przypadku punkty ECTS.

Funkcja wygląda więc w uproszeniu tak:
=SUMA.ILOCZYNÓW/SUMA(wag)
#84 Podglądnij wynik formuły
Często używanym przeze mnie „trickiem” jest podglądanie wyników liczenia danej formuły. Dla przykładu – na danych, ocenach, użyłem formuły LICZ.JEŻELI. Zakres i kryterium będzie dla mnie zbieżne – chce uzyskać informację, ile różnych wielkości (ocen) pojawia się w zestawieniu. Wynik, jak poniżej, to trzy.

Teraz chciałbym jednak sprawdzić, ile razy poszczególne pozycje powtarzają się w zestawieniu – formuła LICZ.JEŻELI zlicza w końcu wszystkie, a następnie wyrzuca ilość powtarzających się wartości. W tym celu wystarczy wejść do edycji formuły (dwa razy na nią kliknąć lub wcisnąć F2) i wcisnąć klawisz F9, przedstawiający wyniki dla poszczególnych pozycji.

#85 Szybki wykres kolumnowy, do wydruku na całą stronę
Jeżeli chcecie w szybki sposób stworzyć wykres kolumnowy, celem chociażby wyciągnięcia poglądowych wniosków na temat analizowanych danych, wystarczy zaznaczyć zakres, który ma być przedstawiony na wykresie, a następnie wcisnąć klawisz F11. Oczywiście tego typu wykres jest dość surowy, ale można go edytować jak każdy inny. Dodatkowo pojawi się on w osobnej zakładce, przystosowany wymiarem do wydruku na całą stronę, co również może być przydatne.
Szybkie tworzenie wykresu wewnątrz arkusz, czyli takiego, jakie wykorzystuje się najczęściej, można przeprowadzić za pomocą skrótu ALT + F1.
#86 Używaj hiperłączy
Czasem arkusz przybiera pokaźne wymiary; nie mieści się w widoku bez przewijanie lub nawet przewijając ciężko jest wyłapać wszystkie obliczenia i przydatne treści. Sposobem na radzenie sobie z tym są proste hiperłącza, dzięki którym odwoływać się będziemy do poszczególnych komórek.
Na poniższym przykładzie zamieściłem wyniki sprzedaży dwóch sprzedawców. Są one ułożone po miesiącach, w okresie pięciu lat. W sumie daje nam to 61 kolumn tabeli. Proponuję, by w pierwszej kolumnie utworzyć swego rodzaju „spis treści”, z którego będzie mogli szybko przechodzić do początku poszczególnych lat.

Hiperłącze wstawiamy klikając prawym klawiszem myszki (lub CTRL + K)na komórce, która ma stanowić nasz spis treści. W nowym oknie ważne jest, by zaznaczyć zakładkę Miejsce w tym dokumencie, a następnie w polu Wpisz odwołanie do komórek wpisać współrzędne komórki, do której będzie to odwołanie. W naszym przypadku będą to komórki rozpoczynające poszczególne lata.
#87 Tworzymy pole wyboru (checkbox)
Naszym zadaniem jest stworzenie pola wyboru, na wzór tych, które stosowane są np. w ankietach. Ankieta jest zresztą całkiem dobrym przykładem. Na bazie zaznaczonych odpowiedzi. Excel będzie nam bowiem podpowiadał od razu logiczne wnioski, wynikające z ciągu odpowiedzi. Oczywiście sam z siebie Excel co najwyżej może się zawiesić – wszystko poza tym trzeba już samodzielnie mu przedłożyć.

Zacznijmy od tego, że większość aktywnych obszarów tworzy się w Excelu przy użyciu karty Deweloper, która standardowo nie jest wyświetlana na wstążce. W bardzo prosty sposób można ją jednak przywołać – odsyłam Was po instrukcję do ekspresowego poradnika na stronie Microsoftu –support.office.com.

Przejdźmy już do owej zakładki, wybierając następującą ścieżkę:
Deweloper – > Wstaw -> Formanty formularza – Pole wyboru
Odtąd będziemy mogli narysować sobie przycisk, który można zaznaczać i odznaczać. Zasadniczo nic poza tym na razie on nie robi. Podstawową rzeczą, która teraz należy zrobić, to sformatować ten formant. Klikamy więc prawym klawiszem myszki na polu, a następnie wybieramy Formatuj formant, przechodząc do zakładki Formant możemy wybrać komórkę. w której Excel zaznaczać będzie, czy dane pole jest zaznaczone, czy nie, wpisując w niej stosownie: PRAWDA lub FAŁSZ. Tworzymy więc automatyczną funkcję, współpracującą z przyciskiem wyboru. Nadal wydaje się to być mało użyteczne (mamy przycisk, który nic nie robi, i komórkę wskazującą, czy jest on aktywny, czy nie). Niemniej słowo „PRAWDA” lub „FAŁSZ” jest tak naprawdę całkiem przydatne – na jego podstawie można już bowiem budować formuły warunkowe i logiczne, które same opracują wyniki naszej ankiety. Łączenie formuły JEŻELI, uwzględniającą zapis PRAWDA lub FAŁSZ pomoże np. w stworzeniu profilu respondenta, czy innych wniosków, zgodnych z hipotezami.

#88 Liczebniki porządkowe z angielskimi końcówkami
Jest w Excelu format, który cyfrom przypisze walutę, ale nie ma takiego, który przypisywałby im końcówki wykorzystywane w języku angielskim do określania liczebników; mam tu na myśli np. 1st, 2nd, 3rd, czy 4th. Zasadniczo tego typu końcówek jest cztery, wszystkie wymieniłem w poprzednim zdaniu. Jak sprawić, by Excel samodzielnie dopisywał liczbom tę końcówkę?

Najpierw trzeba poznać zasadę, w jakich przypadkach stosowana jest która końcówka. Zasada jest dość prosta – większość liczb kończy się końcówką „th”. Wyjątkiem są tylko te, które kończą się na 1, 2 lub 3, ale nie te, których drugą cyfrową od końca jest 1, 2 lub 3. Mamy więc 11th, ale 21st. Jak przełożyć to na język formuł? Wykorzystamy funkcję JEŻELI, warunkującą wykonanie danej czynności, symbol &, „scalający” tekst, a także trzy inne formuły: LUB, WARTOŚĆ, PRAWO i WYBIERZ.
Formuła będzie dość skomplikowana, bo wygląda następująco:
=B4&JEŻELI(LUB(WARTOŚĆ(PRAWY(B4;2))={11;12;13});”th”;JEŻELI(LUB(WARTOŚĆ(PRAWY(B4))={1;2;3});WYBIERZ(PRAWY(B4);”st”;”nd”;”rd”);”th”))
#89 Najpopularniejsze błędy w Excelu – co oznaczają i jak sobie z nimi radzić
Niewiele trzeba, by dla Excela nasza formuła okazała się niezrozumiała. Warto wiedzieć, co oznaczają poszczególne komunikaty, bo w ten sposób można łatwo sobie z nimi poradzić.

Źródło: M.Dąbkowska-Kowalik, ECDL Moduł 4
#90 Szybkie pokazywanie formuł
W trzecim artykule pisałem o funkcji, jaką w Excelu jest pokazywanie formuł – jest to o tyle przydatne, że umożliwia np. szybkie sprawdzanie czyjejś (lub własnej) pracy. Można w końcu sprawdzić, czy jakaś wartość nie jest wklejona na twardo, w miejsce gdzie powinna zaciągać się formuła. Skrótem aby to zrobić jest CTRL + ~ (tylda).
 
#break#
#91 Ułamki zwykle w Excelu
Czy frustrowało Was, że w Excelu „nie da się” wpisywać ułamków zwykłych? Dla przykładu – wpisując w komórce 1/2, Excel automatycznie konwertuje zapis na datę – drugiego stycznia. Aby Excel rozpoznał treść jako ułamek zwykły, wystarczy poprzedzić zapis np. zerem lub inną liczbą/cyfrą oraz spacją. Dla przykładu: zapis „1 1/2” zostanie zrozumiany jako 1,5, a „0 1/2” jako 0,5. Ewentualnie Excel oferuje też specjalny format, przeznaczony dla ułamków zwykłych.
#92 Wyciąganie wartości z ostatniego zapisanego wiersza
Mamy taki problem – systematycznie dodajemy do naszej tabeli dane, nowe wiersze, ale jednocześnie do pozostałych obliczeń chcemy wykorzystać jedynie ostatnią wartość z tabeli. Formuła, w której pracujemy, nie będzie rozpoznawać aktualizujących się danych, ciągle będzie zaciągała wartości ze wskazanej komórki. Jak zrobić, by nowe wartości, były przepisywane do wybranej przez nas komórki, tak, by stamtąd mogły być wykorzystywane na potrzeby innych obliczeń?  Mamy sytuację jak poniżej:

Ciąg czynności będzie następujący: najpierw musimy określić, w którym wierszu znajduje się ostatnia zapisana komórka. Musi ona zatem spełniać warunek <>„” – ma być bowiem różna od pustej komórki. Następnie mamy określić numer wiersza, w którym się znajduje taka komórka, a więc dotychczasową formułę przemnożymy przez numer jej wiersza, korzystając z funkcji WIERSZ. Może się zdarzyć, że w zakresie będzie kilka komórek spełniających kryterium, a zatem celem wyciągnięcia numeru ostatniego wypełnionego wiersza, wykorzystamy formułę MAX. W ten sposób uzyskujemy formułę tablicową, którą zatwierdzamy przy użyciu kombinacji: CTRL + SHIFT + ENTER.

Mając już numer wiersza, teraz skorzystamy z formuły INDEKS, która temu numerowi przypisze wartość z danej komórki.

Zakresem formuły będzie dla nas ta sama kolumna, numerem wiersza będzie wcześniej skonstruowana formuła, a numerem kolumny – 1. Również tę formułę zatwierdzamy przy użyciu kombinacji: CTRL + SHIFT + ENTER.
#93 Usuwamy dane osobowe z właściwości arkusza
Standardowo, stworzone przez nas pliki posiadają we właściwościach zapisane, kto go stworzył, kto ostatnio zmodyfikował i kiedy. Jeżeli nie chcemy, by te dane prowadziły do naszej osoby, można w łatwy sposób się ich pozbyć z właściwości pliku.
W Excelu 2010 ścieżka kliknięć jest następująca:
Plik -> informacje -> Wyszukaj problemy -> Przeprowadź inspekcję dokumentu -> Inspekcja -> Usuń wszystko
#94 Ochrona arkusza
Czasem może nam zależeć, by nasz plik nie był dostępny dla każdej, tj. by nikt poza osobami upoważnionymi nie mógł dokonywać w nim zmian. Aby ochronić cały arkusz, wystarczy wejść w kartę Recenzja, a następnie kliknąć – Chroń arkusz. Standardowe ustawienia umożliwiają jedynie poruszanie się po arkuszu, bez możliwości dodawania jakichkolwiek zmian. Wystarczy zatem jedynie wprowadzić hasło, którym ma być chroniony arkusz, a następnie je powtórzyć.
Po nadaniu hasła, komórki arkusza będą jednak niedostępne również dla osoby, które to hasło nadała. Celem aktywowania arkusza, wystarczy zdjąć ochronę, poprzez wpisanie tego samego hasła, co wcześniej. Zrobimy to przez kartę Recenzja i opcję Nie chroń arkusza.
#95 Ochrona arkusza – udostępnienie wybranych komórek

Może się też zdarzyć, że poszczególnym użytkownikom będziemy chcieli udostępnić jedynie określone zakresy danych – np. tabelę. Celem określenia tego zakresu, należy najpierw zaznaczyć pożądany obszar, a następnie prawym klawiszem myszki: Formatuj komórki -> Ochrona i odznaczyć opcję Zablokuj. To nie spowoduje jeszcze, że arkusz, czy choćby jego część, będzie zablokowana lub nie. Daje to jednak Excelowi informację, które komórki nie mają zostać objęte ochroną. Teraz wystarczy już tylko powtórzyć procedurę z porady #94. Blokujemy arkusz, ale tym razem, zakres z odznaczoną opcją Zablokuj, będzie dostępny do edycji, a na jego zmianę będą reagowały formuły chronione hasłem.
Jeżeli chcecie natomiast zablokować tylko wybrane komórki, a cały arkusz pozostawić do edycji, należy zaznaczyć cały arkusz, odznaczyć opcję Zablokuj, a następnie przywrócić ją dla wybranych komórek. Tak będzie chyba najszybciej.
#96 Ochrona arkusza – sposób na funkcjonalny formularz
Powiedzmy, że naszym narzędziem pracy jest formularz osobowy, taki jak poniżej. Dane – dajmy na to – klientów, uzupełniamy w odpowiednich rubrykach. Problem jednak jest w taki, że aby przejść do odpowiedniej sekcji, tj. do następnej komórki zaznaczonej na fioletowo, trzeba się trochę przeklikać. Korzystając z kombinacji strzałek, tabu i entera można manewrować po naszym zakresie – zabiera to jednak trochę czasu. Jeżeli planujecie korzystać z takiego arkusza stosunkowo często, wówczas najlepiej będzie w tych komórkach (fioletowych) odznaczyć opcję Zablokuj w zakładce Ochrona w Formatowaniu komórek. W ten sposób, po wybraniu opcji ochrony arkusza, tylko te pola pozostaną aktywne i będzie można dużo szybciej się między nimi poruszać – najlepiej tabem.

#97 Jak przedstawić na wykresie cel?
Mamy prostą tabelę z wielkością sprzedaży. Utworzenie wykresu dla tego zakresu nie będzie szczególnym wyzwaniem – wystarczy go zaznaczyć i wcisnąć ALT + F1.

My jednak chcemy przedstawić na tym wykresie również target miesięcznej sprzedaży – powiedzmy, że będzie to 13 000 zł. Dokładając dodatkową kolumnę do naszej tabeli i rozszerzając zakres wykresu, otrzymujemy jednak mało zadowalający efekt.

Pierwszą rzeczą, jaką możemy zrobić, to zamienić czerwoną kolumnę na inny rodzaj wykresu – najlepiej nada się liniowy. Klikamy więc na czerwoną kolumnę, niezależnie którą, a następnie w narzędziach wykresów zmieniamy typ wykresu na liniowy. Efekt jest już o wiele ciekawszy:

Jest jeszcze jedna rzecz, którą możemy zrobić, celem uatrakcyjnienia prezentacji. Możemy zaznaczyć kolorem zakres poniżej targetu, nieco go uwypuklając. Aby to zrobić, dodajemy do wykresu kolejną serię (Zaznacz dane). Teraz dodajemy nową serię, ponownie będzie to kolumna z celem. Co jednak ważne, przesuwamy tę serię powyżej serii Sprzedaż.
Mając dwie kolumny, ze sprzedażą i celem obok siebie, nakładamy na siebie te kolumny (Formatuj serie danych -> Opcje serii -> Nakładanie 100%). Efekt, po zmianie kolorów wykresów, jest jak poniżej:

#98 Sortowanie w poziomie – jak to zrobić?
No właśnie. Standardowo, nakładając na tabelę filtr, nie ma innej możliwości, jak sortowanie danych w kolumnach. Dlatego też, nakładać filtr na dane jak poniżej, Excel przyjmie, że to lata stanowią nagłówki:

Jeżeli chcemy natomiast sortować tego typu dane po wierszach, tj. po wielkości sprzedaży lub latach, musimy wejść w ustawienia sortowania w zakładce dane. Zaznaczmy zatem nasz zakres, ale – uwaga – wyłącznie te komórki, które zawierają dane, bez nagłówków. Excel w poziomej orientacji sortowania działa już niestety mniej sprytnie. Następnie kliknijmy na Sortowanie w zakładce Dane. W nowym okienku wybieramy opcję, a stamtąd zmieniamy orientację filtrowania – od lewej do prawej.

Po kliknięciu OK, wybieramy wiersz i sposób sortowania.
#99 Zaznaczanie zakresu bez użycia klawiszu SHIFT – tryb rozszerzony
Do tej pory, chcąc zaznaczyć obszar komórek, zapewne wykorzystywaliście do tego strzałki wraz z klawiszem SHIFT. Jest jednak inny, być może szybszy, sposób. Wystarczy wcisnąć klawisz F8. Wówczas Excel wchodzi w tzw. tryb rozszerzony, co widać w lewym dolnym rogu. Od tej pory poruszając się strzałkami, od razu dokonuje się również zaznaczania obszaru, bez użycia SHIFT-a. Działa to również w formułach.

#100 PDF ze skrótami
Jak obiecałem na samym początku, pod koniec cyklu przygotuje dla Was również PDF-a z listą wybranych skrótów klawiszowych.

Oto on.

Udostępnij ten wpis: