Naszym celem, w tym odcinku, nie jest dane otworzyć, zimportować, wkleić czy pobrać. Naszym celem dzisiaj jest
dane efektywnie wpisać.
Dzisiejszy odcinek poświęćmy informatyce. Pewien bardzo podstawowy zakres wiedzy informatycznej przyda się każdemu z nas – w dzisiejszym, bardzo już zinformatyzowanym świecie, trudno już być w tym temacie laikiem i ignorantem.
Co to jest tabela, wiemy wszyscy – to strukturalizowana postać prezentacji lub przechowywania danych.
Rys.1.Lista płac jako przykład tabeli danych
|
Nazwisko
i imię |
Płaca
zasadnicza |
Nadgodziny |
Premia |
Płaca
brutto |
PDoOF |
Płaca
netto |
|
Kowalski
Jan |
1 200,00 |
200,00 |
|
1 400,00 |
266,00 |
1 134,00 |
|
Nowak
Kazimierz |
1 400,00 |
150,00 |
200,00 |
1 750,00 |
332,50 |
1 417,50 |
Chcąc wyszukać interesującą nas informację w tabeli, spoglądamy na nagłówek, wyszukujemy po tekście w jej nagłówku właściwą kolumnę, a następnie wyszukujemy właściwy wiersz lub wiersze, przeszukując listę nagłówków wierszy, tzw. etykiet, zwykle w pierwszej kolumnie tej tabeli. Tak postępuje człowiek: W powyższej liście płac, chcemy płacę netto – w nagłówku mamy: płaca netto (czyli znamy kolumnę), chcemy znać tę płacę dla Kowalskiego – w pierwszej (to akurat niekonieczne, tam zwykle jest numerator) kolumnie szukamy Kowalskiego – jest! Gdzie jest płaca netto Kowalskiego? Oczywiście na skrzyżowaniu kolumny płacy netto i wiersza Kowalskiego. I tak też postępuje każdy program, który cokolwiek wyszukuje.
Ogólnie wyróżniamy dwa rodzaje tabel – typu lista (rejestr, lista o zmiennej długości) oraz typu zestawienie (lista o stałej długości).
Rys.2. Rodzaje tabel danych (rejestr i zestawienie)
Arkusz kalkulacyjny jest, jak wiemy, doskonałym kreatorem tabel. Wszyscy dane już wprowadzaliśmy – robi się to łatwo. A co robić, i jak, żeby te nasze dane były przydatne w sensie informatycznym, tj. żeby ktoś mógł je np. przeszukać w celu znalezienia czegoś?
Przede wszystkim tabela powinna mieć czytelny i mało skomplikowany nagłówek i, to naprawdę priotytet, musi być on w jednym wierszu. Opisy komentarze, parametry dla funkcji powinny być w innym wierszu równoległym. Rzeczywisty wiersz nagłówka dla celów wyszukiwania danych może być zresztą… ukryty. Ważne, by „widziały” go funkcje.
Rys.3. Przykłady nagłówków: efektywnego i nieefektywnego informatycznie
Następnie powinna mieć przemyślany klucz wyszukiwawczy – informatycy mówią klucz, a programy bazodanowe to pole „indeksują”. My nie będziemy nic indeksować, ani kluczyć – my mamy dane i chcemy je przeszukiwać. Oprócz nagłówka (w wierszu) musimy stworzyć taką wydzieloną kolumnę, która będzie nam identyfikować wiersze przeszukiwanej tabeli.
Przykładem tabeli, efektywnej do przeszukiwania, jest lista płac na Rys.1, tabela rejestru na Rys.3. oraz gotowa tabela naszego rejestru wydatków, którą razem wykonamy za chwilę (Rys.5.)
Wykonamy zestawienie
wydatków z tego tygodnia.
Zaczynamy od stworzenia odpowiedniego nagłówka, teraz jest on dla nas, później będzie używany do wyszukiwania – pamiętajmy o tym i nie twórzmy zcalonych, przeformatowanych, zawierających długie opisy komórek. Krótko, zwięźle i – przede wszystkim – czytelnie. Jeżeli pole zawiera spację – to jedną, niech nie zawiera „?” i „*” – to znaki specjalne w procedurach wyszukiwawczych (oznaczają: dowolny jeden znak i dowolne znaki). Wpisujemy: do A1: lp, do B1: data, do C1: kto wydał, do D1: opis wydatku, do E1: koszt, do F1: uwagi
Drugim krokiem jest wpisanie pierwszego wiersza
Wpisujemy: do A2: 1, do B2:
Nie wydarzyło się nic szczególnego, w arkusz Excela dane wpisuje się łatwo i przyjemnie.
Wpisujemy dalej: do A3: 2, do B3:
- Pierwszy, ogólnie znany to Kopiuj-Wklej (z menu Edycja lub z menu pod prawym klawiszem myszy)
- „Pociągnięcie” komórki w dół za jej prawy dolny róg
- Ustawienie się w komórce w 2. wierszu danych i naciśnięcie (uwaga) skrótu [Ctrl+’ (apostrof]. Skopiowana zostaje formuła (tu: po prostu wartość) z komórki powyżej – jest to bardzo efektywny sposób wpisywania, powtarzających się danych, do list.
Jeżeli przewidujemy, że kolejny wiersz również zawierać będzie tę samą datę, skopiujmy ja od razu również tam, Excel ma tę przewagę nad oknami-formularzami programów ewidencyjnych, że umożliwia wpisywanie/edycję wielu rekordów naraz. To ważna cecha. {autonumeracja} Numeratora wiersza w kolumnie A, nie musimy już od tej pory wpisywać ręcznie. Od razu zaznaczmy obszar z liczbami 1 i 2 (A2:A3), chwyćmy myszą za kropkę w prawym-dolnym rogu zaznaczenia i pociągnijmy w dół, np. do komórki A11. Excel ponumeruje kolejne rekordy automatycznie kolejnymi liczbami naturalnymi od 3 do 10.
{uzupełnianie formuł} Idziemy dalej: Do C3 wpisujemy „M”... a Excel sam uzupełnia nam „Mama”. Jeżeli naszą intencją było wpisanie słowa „Mama”, wystarczy nacisnąć [Enter] (zakończyć wpisywanie), jeżeli nie, piszmy dalej, np. „Mirek”, czyli, gdy podpowiedź zignorujemy, Excel to zaakceptuje. Jeżeli natomiast chcemy wpisać samo „M”, a Excel uparcie nam podpowiada, wystarczy po tym „M” nacisnąć [Delete], aby podpowiedź po prostu zlikwidować. Wpiszmy, czy też zaakceptujmy, podpowiedź – w komórce C3 ma być „Mama”. Do D3 wpisujemy np. rachunek telefoniczny, do E3: 100,00 . Do F3 wpisujemy „+ 3,50 opłaty”, a Excel nie chce nam tego przyjąć! Traktuje to jako formułę (bo znak + na początku oznacza formułę). {wymuszanie formatu tekstowego} Jak więc wpisać w komórkę coś, co chcemy, żeby było tekstem, a nie np. formułą, liczbą, datą, czy hiperłączem (odnośnikiem, jak np. w Internecie)? Bardzo prosto, wystarczy poprzedzić cały nasz wpis ‘ (apostrofem). Wpisujemy więc ‘+ 3,50 opłaty i to Excel już akceptuje. Apostrof, proszę zauważyć, nie jest wyświetlany w komórce.
Wprowadźmy jeszcze jeden wiersz razem. Numerator mamy (do wiersza 10-tego) obok chcemy wpisać datę dzisiejszą, bo nasz wydatek przypadł na dzisiaj. {skrót daty bieżącej} Daty dzisiejszej również nie musimy wpisywać, wystarczy nacisnąć skrót [Ctrl+; (średnik)]. Ależ ten Excel ma ułatwień! Ale najciekawsze dopiero przed nami! W komórce C4 chcemy wpisać „Tata”. Ale zaraz, nasza rodzina to Mama, Tata, Mirek i Agnieszka, 6-miesięczny Sławek za nic jeszcze nikomu nie płaci. Czy możemy stworzyć sobie listę dopuszczalnych wartości, które możemy wpisać, żeby wybierać je np. z jakieś listy podręcznej? Tak, i to na dwa sposoby. Pierwszy nie wymaga żadnych zabiegów. {wybór z listy} Naciskamy prawy klawisz myszy, wybieramy Wybierz z listy po czym wybieramy z naszej listy Mirka, bo on właśnie wydał nasze ciężko zarobione pieniądze. Skąd on się na tej liście wziął? – z wartości powyżej. Excel umożliwia taki wybór, gdy powyższa lista jest ciągła, tzn. nie zawiera pustych komórek – raczej nie polecam tej metody, trzeba się naklikać, a lista nam czasami ginie, gdy np. nie ma komu wydatku przypisać i pole pozostaje puste (np. w kasie brakuje nam ciągle 50 zł, świeżo ufryzowana mama twierdzi, że widocznie ich tu nigdy nie było – trzeba po prostu stworzyć pozycję pozostałe). Ale jest sposób lepszy! {lista dopuszczalnych wartości komórki} W wolnych komórkach, obok tabeli (czyli np. w H1:H4), wypiszmy kolejno członków naszej rodziny (bez Sławka). Zaznaczmy całą kolumnę C i z menu Dane wybierzmy Sprawdzanie poprawności. W oknie Sprawdzanie poprawności danych, z listy Dozwolone wybieramy pozycję Lista, a jako Źródło wskazujemy obszar H1:H4.
Rys.4. Określanie listy dopuszczalnych wartości danych ze wskazanej listy danych.
Gdy nasze opcje potwierdzimy, w kolejne komórki kolumny C nie będziemy mogli wpisać już nic innego, jak tylko osoby z naszej listy. Dostęp do tej listy jest dwojaki: albo rozwijamy listę myszą, albo stosujemy skrót [Alt+Enter] (Jest to ogólny skrót rozwijania list w Excelu).
Istnieje mnóstwo opcji tegoż okna – nie sposób wszystkich wymienić – proszę sprawdzić samodzielnie. Dobrym testem będzie tu uniemożliwienie wpisu do kolumny E wartości innej, niż numeryczna, a w kolumnie B wartości innej, niż data. Na liście wyboru Dozwolone w powyższym oknie figurują pozycje Liczba i Data.
Wypełnijmy naszą tabelę wartościami, jak pokazano poniżej. Spróbujmy jak najmniej „klepać”, jak najwięcej pomagać, sobie wymienionymi wyżej sposobami.
{kosmetyka} Aby w naszej tabeli dane numeryczne miały jednolity format, nadajmy kolumnie E format liczbowy z dwoma miejscami dziesiętnymi [Ctrl+Shift+1], a kolumnie B format daty. Zaznaczmy cały arkusz [Ctrl+Shift+Spacja] i kliknijmy linię, oddzielającą nazwy kolumn A i B, czyli dostosujmy szerokości kolumn do wpisanych wartości. Ustawmy kursor na komórce A2 i z menu Okno wybierzmy Zablokuj okienka. Spowodujemy tym zablokowanie wiersza 1 (jest tam nagłówek naszej tabeli) i odtąd nie będzie on nam „uciekał” w górę, poza ekran, gdy rozrost naszej listy spowoduje przewinięcie arkusza w górę (Wpisaliśmy tylko tygodniowe wydatki, a zamierzamy przecież wprowadzić na stałe szczegółową analizę wydatków z domowego budżetu)
Rys.5.Docelowy wygląd naszej tabeli
Jeszcze kilka słów o nagłówkach – wszak głównym tematem jest informatyczna poprawność stworzonej tabeli danych. Nagłówek w tabeli powyżej, zapisany w pliku Excela jest poprawny informatycznie, gdybyśmy jednak chcieli zapisać go w którymś ze starszych formatów danych, np. w pliku DBF (ma to niekiedy swoje zalety) powyższy nagłówek powinniśmy dodatkowo uprościć. Pliki DBF dopuszczają nagłówki max. 10-cio znakowe, bez polskich znaków i spacji. Pole „kto wydał” zastąpilibyśmy polem „osoba”, pole „opis wydatku” po prostu polem „opis”. Przy zapisie do „tych innych” formatów danych, należy też zwrócić uwagę na szerokość kolumn oraz ich format. Powyższe dane zapisałyby się poprawnie, bo celowo nadaliśmy format liczbowy kolumnie E, a kolumnie B format daty, dlatego też Excel przy zapisie w tych formatach by to zachował.
{sortowanie danych} Dane w naszej tabeli są rejestrem wydatków – codziennym zapisem poniesionych kosztów. Nie zawsze jednak kolejność chronologiczna nam odpowiada, czasami chcielibyśmy wiedzieć, kto np. poniósł wydatek największy i na co – zakładamy oczywiście, że nasza tabela nie ma 10 wierszy (przepraszam: rekordów) tylko więcej (1.000?), tak, że trudno ją „ogarnąć” bez pomocy metod informatycznych. Najprostszą metodą, która pomoże nam w uporządkowaniu wierszy (rekordów!) tabeli, jest sortowanie. Temat jest raczej przez użytkowników Excela znany, bo jest to zwykle jedna z pierwszych w ogóle poznawanych funkcjonalności Excela. Proszę jednak pamiętać, że jest to metoda użyteczna tylko dla nas, ludzi, nasze metody poznawcze wymagają uporządkowania danych w tabeli. Systemom bazodanowym jest to raczej obojętne – one dane i tak indeksują na swój sposób (to nie do końca prawda, ale upraszczam celowo) Zobaczmy więc, kto przoduje w wielkości wydatków, posortujmy tabelę po wielkości kwoty wydatku, od wartości największej do najmniejszej.
Zaznaczamy całą tabelę (koniecznie z nagłówkami) lub po prostu całe kolumny A:E. Z menu Dane wybieramy Sortuj i w oknie, jak przedstawiono poniżej wybieramy koszt jako klucz sortowania, z opcją Malejąco, zostawiając opcję Ma wiersz nagłówka. Potwierdzamy i wiemy: Mirek zakupem DVD najbardziej nadwerężył nasz budżet. Ale nie gniewamy się -może pozwoli nam też jakiś film oglądnąć?
Rys.6.Sortowanie tabeli danych
Sortować można jednak w bardziej wyrafinowany sposób. Spróbujmy te same dane posortować tak: klucz 1. (kolejne pola od góry w oknie sortowania): kto wydał (Rosnąco), koszt (Malejąco) – mamy pogrupowane wydatki według osób, a w ramach tych grup wydatki, posortowane od największego do najmniejszego. Najmniejszego. {sumy pośrednie} Gdy jeszcze w tym momencie z menu Dane wybierzemy Sumy pośrednie, to po wyborze opcji, jak na rysunku poniżej, Excel sam doda odpowiednie sumy pośrednie oraz, co ciekawe, odpowiednio zorganizowaną strukturę arkusza (minusy i wąsy po lewej stronie arkusza), proszę poklikać na tych minusach, „zwiną” wiersze, objętę danym wąsem, plusy oczywiście czynią odwrotnie – rozwijają podstrukturę danej pozycji.
Rys.7.Sumy pośrednie
I już wiadomo, kto w ogóle najwięcej wydał – znowu Mirek. Suma całkowita też się oczywiście liczy, choć tu nie pokazuje się z braku miejsca, ale nie rozszerzyłem kolumny celowo – Tata ma problemy z sercem.
Podsumowanie:
Excel to bardzo efektywne narzędzie wpisywania danych – oczywiście w sensownym zakresie ilości rekordów, jego elastyczność jednak nieco tu przeszkadza (bo wszyscy mogą z danymi zrobić wszystko), ale ogólnie zachowuje się poprawnie – i dlatego większość Excelistów używa go właśnie do wpisywania danych. Ale danych nie powinni przepisywać! Ale o tym w następnych odcinkach naszego kursu.
Test umiejętności:
- Wykonaj, według podanych wyżej reguł i stosując metody, poprawną tabelę – listę swoich kaset video, książek, własnych wydatków.
- Wykonaj sprawdzanie poprawności danych dla pola Numerator. {rola numeratora} Nie powiedzieliśmy tego, ale spełnia on ważną rolę w naszej tabeli. Gdy coś posortujemy źle, gdy coś nam się rozjedzie, sortowanie po numeratorze odtwarza nam pierwotny wygląd tabeli.
- Posortuj tabelę z naszego przykłady chronologicznie i podaj sumy dzienne wydatków.
Skróty klawiszowe, dotyczące wpisywania danych:
[Ctrl+’] – skopiuj formułę z
komórki powyżej
[Ctrl+D] – skopiuj formuły w dół
[Ctrl+R] – skopiuj formuły w prawo
[Ctrl+Enter] – wpisz formułę w
obszar danych (w edycji)
[Ctrl+Shift+Enter] – wpisz formułę
tablicową (w edycji)
[Ctrl+F3] – Definiuj nazwę
[Ctrl+Shift+F3] – Nadaj nazwy z etykiet
[Alt+=] – wpisz sumę
(autosuma)
[Alt+↓] – rozwiń listę (dotyczy również autofiltru)