rozwiń filtry »

Wyszukiwanie publikacji

Porady

maj
07

Praca z Excelem - 100 porad i tricków. Część I

07.05.2015 20:58, dodał: Krzysztof Cyło
Ocena:

#11 Excel nie zna historii sprzed 1 stycznia 1900 roku

Każdej dacie Excel domyślnie przypisuje numer porządkowy (z tego względu być może zdarzyło Wam się wpisać datę, która automatycznie została zamieniona na liczbę - wystarczy w takiej sytuacji zmienić formatowanie komórki na odpowiadające dacie). To właśnie 1 stycznia 1900 roku posiada numer 1. Wiąże się to jednak z dwiema kwestiami.

Pierwsza dotyczy swego rodzaju świadomego (podkreślam, świadomego) błędu twórców pakietu biurowego. Otóż rok ten jest przez Excela uznawany jako przestępny, choć wcale taki nie jest. Według kalendarza gregoriańskiego, z rokiem przestępnym mamy do czynienia co 4 lata, jednakże w przypadku lat podzielnych przez 100 (np. 1800, 2000), rok ten musi być również podzielny przez 400. Użytkownicy mogą o tym nie wiedzieć, a Excel ich z tego błędu nie wyprowadzi - warto mieć na uwadze ten błąd w obliczeniach (Excel datę 29 lutego 1900 uzna za prawidłową). Wynika on z tego, że pierwsza wersja aplikacji Lotus zawierała to niedopatrzenie, a Microsoft, celem zachowania pełnej kompatybilności, był zmuszony przenieść go również do programu Excel.

Druga kwestia to taka, że praca na danych historycznych (tj,. sprzed granicznej daty) jest utrudniona. Wprawdzie arkusz kalkulacyjny przyjmie wszystko i nie ma przeszkód przed wpisaniem daty np. 1700-01-01, to jednak Excel zawsze rozpoznawać będzie te daty jako tekst, a nie daty.

#12 Ile tekstu zmieści komórka?

Strzelajcie - ile tekstu (znaków) pomieści jedna komórka arkusza kalkulacyjnego? Zapewne będzie dla Was zaskoczeniem, że odpowiedź brzmi - 32 000 (czyli mniej więcej tyle, ile wynosi rozdział pracy dyplomowej ;-). Chyba nie ma na świecie osoby, która zdecydowałaby się jednak na wprowadzanie tak długiego tekstu do pojedynczej komórki. Celem wprowadzania tekstu, o wiele bardziej polecam korzystanie z pól tekstowych (Wstawienie -> Pole tekstowe), a także wyżej opisanych komentarzy.

Celem zliczenia ilości znaków w danej komórce wykorzystuje się formułę:

=DŁ

Edytowanie tekstu w komórkach ma to do siebie, że Excel nie podkreśla żadnych błędów. Często zdarza się też zrobić podwójną spację. Rozwiązaniem pierwszego problemu jest wykorzystanie funkcji sprawdzenia pisowni (Recenzja -> Pisownia), a drugiego - formuła usuwającej podwójne spacje:

=USUŃ.ZBĘDNE.ODSTĘPY

#13 Praca z tekstem - łączenie komórek

Powiedzmy, że mamy się zmierzyć z następującym problemem: mamy dwie kolumny: z wypisanymi województwami i odpowiadającymi im miastami. Jak zrobić, by nazwa miasta i województwa znajdowała się w tej samej komórce?

Zasadniczo wykorzystywać będziemy tu - dość nietypową - formułę:

=[argument 1]&[argument 2]

Jak widzicie, wystarczy zatem połączyć dwie komórki (uprzednio wpisując znak "=") symbolem "&". Efekt tego może być jednak niezadowalający, gdyż uzyskamy wyniki jak w kolumnie "D". (DOLNOŚLĄSKIEWrocław)

Formułę należy zatem zmodyfikować do postaci:

=[tekst 1]&" "&[tekst 2]

Jesteśmy już nieco bliżej celu (kolumna "E" na obrazku), jednakże nadal postać "DOLNOŚLĄSKIE Wrocław" mnie nie zadowala. Kolejną rzeczą, jaką możemy zrobić to odwrócić kolejność (najpierw miasto, później województwo), pozostawić spację, a przed nią wstawić przecinek. Formuła wyglądać będzie następująco:

=[tekst 2]&", "&[tekst 1]

Teraz otrzymujemy złączone dane w postaci: Wrocław, DOLNOŚLĄSKIE.

#14 Praca z tekstem - tekst jako kolumny

Teraz spróbujmy zrobić coś odwrotnego - scalony w jednej komórce tekst, rozdzielmy na dwie kolumny. Temu służy opcja "Tekst jako kolumny" (Dane -> Tekst jako kolumny). By skorzystać z tej opcji, musimy zaznaczyć komórkę z danym (lub całą tabelę).

W oknie dialogowym mamy do dyspozycji dwie opcje: Rozdzielany, która służy rozdzielaniu komórek po określonym znaku (z niej też skorzystamy, gdyż miasta i województwa są oddzielone przecinkami), a druga: Stała szerokość. Ta opcja sprawdza się, gdy rozdzielana zawartość komórek nie posiada powtarzających się rozdzielników (np. przeciwników lub spacji), i gdy tekst ma jednakową długość we wszystkich komórkach, a nam zależy na równym jego podziale.

W kroku drugim wybieramy znak graniczny, tj. taki, po którym ma nastąpić rozdzielenie. W naszym przypadku jest to przecinek (spacji nie trzeba już zaznaczać, aczkolwiek ona też występuje). W kroku trzecim wybieramy natomiast format danych w komórkach (u nas będzie to tekst lub ogólny), a także miejsce, gdzie komórki mają być oddzielone. Jeżeli zależy nam na zachowaniu pierwotnych danych, wówczas jako "Miejsce docelowe" wybierzemy inne kolumny, jak na zdjęciu poniżej:

Teraz wystarczy już tylko kliknąć "Zakończ".

#15 Przeszukiwanie arkusza i całego skoroszytu

Jeżeli pracujecie w rozbudowanym pliku, czasem może być ciężko odnaleźć początek danej sekcji lub po prostu pożądane hasło - nazwisko, miejscowość, kod, itp. Najprostszą metodą przeszukiwania arkusza jest zastosowanie skrótu CTRL + F, który uruchamia okno dialogowe poświęcone właśnie wyszukiwarce. Cześć osób może jednak nie wiedzieć, że z tego poziomu można przeszukiwać nie tylko aktualnie wyświetlaną zakładkę, ale i cały skoroszyt. Wystarczy wejść w Opcje -> Wewnątrz -> Skoroszyt. Nic prostszego.

#16 Szybka zamiana znaków

Może się zdarzyć, że zrzut danych, którym dysponujecie, został przygotowany przez inny program, i który nie jest do końca przystosowany do środowiska pracy Excela. Wystarczy, że np. kwoty posiadają separator tysięczny ze znakiem spacji (Excel rozpozna to jako tekst) lub grosze będą oddzielone od pełnych kwot kropką, a nie przecinkiem. Co w takiej sytuacji zrobić? Ponownie możemy skorzystać ze skrótu CTRL + F i otworzyć formatkę "Zamień" lub od razu uruchomić ją korzystając ze skrótu CTRL + H. Później wystarczy już tylko zaznaczyć odpowiednią kolumnę, tabelę, cały arkusz lub skoroszyt - i wpisać znak, który ma być zamieniony. Jeżeli chcecie usunąć jakiś znak, wówczas należy go wpisać w polu "Znajdź", a w pole "Zamień na" pozostawić puste.

Może się zdarzyć, że w zrzucie z innego programu pojawi się znak, który pozornie wygląda jak spacja lub kropka, ale po wpisaniu tego znaku w pole "Znajdź", Excel nie odnajdzie żadnego wyniku. Wówczas sugeruję dokładne przekopiowanie owego znaku.

#17 Grupowanie i rozgrupowanie

Niezwykle użyteczne funkcje znajdziecie w formatce "Dane", po prawej stronie. Grupowanie polega na ukryciu zaznaczonych wierszy lub kolumn. Jest to przydatne w sytuacji, gdy pracujecie na danych, z których np. potrzebna jest Wam wyłącznie suma wybranych wartości. Pokażę to na przykładzie.

Tak wygląda mój skoroszyt. Mam tu przykładowy bilans spółki. Załóżmy, że liczącymi się dla mnie informacjami są wartości przypisane wielkim literom, tj. Aktywa trwałe, Aktywa obrotowe, Kapitał własny i zobowiązania. Pozostałe pozycje (cyfry rzymskie) stanowią analityczne rozbicie tych pozycji. Na tę chwilę nie potrzebuję mieć ich do wglądu - mogę je spokojnie ukryć, czyli zgrupować.

Jak widzicie, w ten sposób udało mi się odsłonić spory kawałek arkusza. Plusami po lewej stronie można następnie rozwijać poszczególne pozycje. Symbole [1] i [2] pozwalają natomiast na odsłanianie lub zakrywanie wszystkich zgrupowań.

To samo można również robić z kolumnami.

#18 Jak sprawić, by stale widoczne były nagłówki?

Czasem bywa tak, że pracujemy w arkuszu z długą listą danych. Opisy poszczególnych kolumn/wierszy znajdują się rzecz jasna wyłącznie na początku zestawienia, w nagłówku, a wraz z przewijaniem paska, opisy znikają. Gdy mamy do czynienia z wieloma parametrami, jak na poniższym skoroszycie, który liczy kilkaset wierszy, można łatwo się pogubić. Dobrze by było, gdyby nagłówki z opisami były (u nas wiersz 3).

W celu uzyskania takiego efektu, powinniśmy się udać do formatki górnego panelu - Widok.

Excel domyślnie umożliwia zablokowanie pierwszego wiersza lub pierwszej kolumny. W naszym przypadku to jednak nie odnosi skutku, gdyż opisy są w trzecim wierszu. W takiej sytuacji można zablokować pierwszy wiersz i dodać kilka wierszy ponad nim, które również będą od tego momentu zablokowane, a następnie przenieść opisy. Nieco szybciej można też zaznaczyć kilka wierszy (plus jeden dodatkowy), a następnie wcisnąć opcję Zablokuj okienka. Niezależnie jednak od metody, arkusz będzie wyglądał następująco:

#19 Rozwijana lista w komórce

Rozwijane listy to bardzo przydatna funkcja, która sprawdza się np. przy przypisywaniu kategorii wydatków domowych, czy statusu danej czynności (np. "Nie zaczęta", "W trakcie", "Skończona"). Pokażę Wam jak zrobić taką listę na przykładzie prostego budżetu domowego.

Oto lista naszych wydatków. Normalnie do każdego z nich musielibyśmy przypisać kategorie wydatku osobno. Ma to jednak kilka wad - po pierwsze jest pracochłonne, po drugie istnieje ryzyko, że daną kategorię wpiszemy inaczej (np. z błędem) w kilku miejscach, przez co dalsze jej wykorzystanie może być obarczone błędem, po trzecie - nie mając z góry określonego katalogu kategorii, możemy nadmiernie się zastanawiać nad przypisaniem konkretnej do danej pozycji. Rozwijane listy mają więc sporo plusów, a ich tworzenie jest banalne proste.

Mając, jak na powyższym zdjęciu, przygotowane zestawienie wydatków, chcemy przypisać im odpowiednią kategorię - jedną z widocznych w tabelce po prawej stronie.

Ścieżka kliknięć jest tu bardzo krótka - wchodzimy w formatkę Dane, a następnie, przy zaznaczonej komórce, w której chcemy wpisać kategorię (w naszym przykładzie kolumna D), wybieramy opcję Poprawność danych -> Ustawienia -> Dozwolone - Lista. Po tym należy już tylko wybrać źródło danych, tj. tabelę, w której są wypisane poszczególne kategorie. Po zatwierdzeniu w danej komórce pojawi się ikonka z rozwinięciem danej listy. "Łapiąc" tę komórkę z prawy dolny róg i przeciągając w dół, zaaplikujecie taką listę do całej tabeli.

#20 Tłumaczenie nazw formuł

Na koniec bardzo krótka, aczkolwiek przydatna rada. Wiele firm instaluje na służbowych laptopach Excela w angielskiej wersji językowej, jak i czasem możecie się natrafić na ciekawy poradnik, jednakże w przypadku którego autor posługuje się angielskimi nazwami formuł. Pod TYM linkiem znajdziecie tłumaczenie wszystkich formuł, wraz z krótkim komentarzem.

To by było na tyle, jeżeli chodzi o ten artykuł. W kolejnym powiemy już sobie nieco więcej na temat filtrów, tabel i wykresów. Zapraszam za tydzień!

Strona:






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.