rozwiń filtry »

Wyszukiwanie publikacji

Porady

czer
04

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

04.06.2015 18:20, dodał: Krzysztof Cyło
Ocena:

Ruszamy z ostatnią, piątą częścią z naszego cyklu poświęconego programowi MS Excel. Przed Wami kolejne 20 porad.

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

Symbol błędu

Przyczyna

Rozwiązanie

####

Wartość wykracza poza szerokość komórki

Rozszerz kolumnę lub zmień format danych

#DZIEL/0!

Oj, dzieliło się przez zero (lub pustą komórkę)

Nie dziel przez zero! J Zmień źródło odwołania formuły

#N/D!

Wprowadzono niewłaściwy argument formuły

Sprawdź formułę i poszczególne jej argumenty. Upewnij się, że wszystkie dane znajdują się w dostępnym arkuszu

#ZERO!

Nieprawidłowe odwołanie do komórki lub zakresu

Najczęściej wystarczy upewnić się, że argumenty są w formule oddzielone średnikiem

#LICZBA!

Użycie argumenty, który nie jest liczbą lub wprowadzenie formuły, której wynik jest zbyt duży lub zbyt mały, by Excel mógł go zaprezentować

Sprawdź formułę – być może zapomniałeś użyć odwołania bezwzględnego (symbol „$”), przez co zaciągane do formuły dane są inne, niż jest to wymagane. Upewnij się też, czy wynik zmieści się w przedziale rozpoznawalnym przez program Excel, tj. od -1*10^307 i +1*10^307

#NAZWA?

Brak lub użycie nie istniejącej nazwy formuły. Ewentualnie błędny zapis w adresie zakresu; tj. bez użycia dwukropka

Upewnij się, że nazwa formuły jest prawidłowa – sprawdź też, czy odwołujesz się w niej do zakresu oddzielonego symbolem „:”

#ADR!

Brak komórek, do których odwołują się formuły (zostały np. przeniesione lub zastąpione). Ew. błąd w zapisie adresu komórki

Sprawdź poprawność adresów i zakresów komórek

#ARG!

Użycie tekstowego argumentu do formuły wymagającej liczb

Sprawdź, czy formuła arytmetyczna odwołuje się do zakresu z liczbami

Ź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).

 

Strona:






Polecane artykuły

Testy

Poradniki

Tagi

 android   apple   ciekawostki   gaming   google   gry   internet   komputery   kosmos   microsoft   mobilne   nauka   polska   samsung   smartfon   smartfony   tablet   technologia   telefony   windows 

Korzystamy z plików cookies i umożliwiamy zamieszczanie ich osobom trzecim. Pliki cookie pozwalają na poznanie twoich preferencji na podstawie zachowań w serwisie.
Uznajemy, że jeżeli kontynuujesz korzystanie z serwisu, wyrażasz na to zgodę. Poznaj szczegóły i możliwości zmiany ustawień w Polityce Cookies.