Rozdział 16. Importowanie i porządkowanie danych

W tym rozdziale:

Metody importowania danych do Excela Techniki posługiwania się danymi i ich porządkowania Sposoby użycia nowej funkcji Wypełniania błyskawicznego Lista kontrolna czynności do wykonania przy porządkowaniu danych Eksportowanie danych do różnych formatów

Excel jest bardzo często wykorzystywany do porządkowania danych. Surowe dane wprowadza się do programu, a następnie przetwarza się je tak, aby odpowiadały określonym wymaganiom. Dzięki temu dane stają się spójne i można je analizować.

W tym rozdziale znajduje się opis technik pobierania danych do Excela i ich porządkowania.

Kilka słów na temat danych

Dane są wszędzie. Jeśli na przykład prowadzisz stronę internetową, to możesz nawet nie wiedzieć, że ciągle gromadzisz informacje. Każda wizyta w Twoim serwisie pozostawia ślad w plikach serwera. Jeśli masz czas je przeanalizować, to możesz w nich znaleźć sporo ciekawych rzeczy.

To był jeden przykład gromadzenia danych. Ale tak naprawdę dosłownie każdy automatyczny system gromadzi i zapisuje gdzieś informacje. Zazwyczaj system jest wyposażony w odpowiednie mechanizmy pozwalające mu te dane od razu analizować. Jednak nie zawsze tak jest. Poza tym czasami dane są też zbierane ręcznie, np. poprzez telefoniczne ankiety.

Excel jest znakomitym narzędziem do analizy danych i często się go używa do prezentowania informacji w postaci wykresów lub tabel. Często jednak zebrane informacje nie są dokładnie w takiej formie, w jakiej byśmy chcieli, i trzeba je uporządkować, aby nadawały się do analizowania.

Importowanie danych

Aby móc przetwarzać dane, trzeba je najpierw zaimportować do arkusza kalkulacyjnego. Excel obsługuje większość formatów tekstowych, a nawet dane pobierane ze stron internetowych.

Importowanie danych z pliku

W tej części rozdziału znajduje się opis typów plików, które w Excelu można otworzyć za pomocą polecenia Plik/Otwórz. Na rysunku 16.1 widać listę filtrów typów plików dostępną w tym oknie dialogowym.

 

Rysunek 16.1. Filtrowanie według rozszerzeń plików w oknie dialogowym Otwieranie

Formaty plików arkuszy kalkulacyjnych

Oprócz najnowszych formatów plików (XLSX, XLSM, XLSB, XLTX, XLTM oraz XLAM) Excel 2013 otwiera pliki w formatach wszystkich poprzednich wersji:

XLS: pliki binarne tworzone przez Excela 4, Excela 95, Excela 97, Excela 2000, Excela 2002 oraz Excela 2003. XLM: pliki binarne zawierające makra Excela 4 (bez danych). XLT: pliki binarne szablonów Excela. XLA: pliki binarne dodatków Excela.

Można też otworzyć jeden format plików utworzony w innym programie:

ODS: arkusz kalkulacyjny OpenDocument (ang. OpenDocument Spreadsheet).

Pliki tego typu tworzy się przy użyciu programów open source, np. Google Drive, OpenOffice, LibreOffice, StarOffice i in.

Należy podkreślić, że Excel nie obsługuje plików programów Lotus 1-2-3, Quattro Pro ani Microsoft Works.

Formaty plików baz danych

Excel 2013 otwiera następujące formaty plików baz danych:

Pliki programu Access: mogą mieć różne rozszerzenia, np. MDB albo ACCDB. Pliki systemu dBase: Excel otwiera pliki dBase III i IV, nie otwiera natomiast plików dBase II.

Dodatkowo Excel może wykonywać wiele różnych rodzajów połączeń z bazami danych, dzięki czemu informacje można pobierać wybiórczo. Przykładowo z dużej bazy danych można pobrać tylko te rekordy, które są w danej chwili potrzebne, a nie wszystkie dane.

Formaty plików tekstowych

Plik tekstowy zawiera czysty tekst, bez informacji dotyczących formatowania. Excel otwiera większość rodzajów plików tekstowych:

CSV (wartości oddzielane przecinkami): kolumny w takich plikach oddziela się przecinkami, a wiersze znakami powrotu karetki. TXT: kolumny oddziela się tabulatorami, a wiersze - znakami powrotu karetki. PRN: kolumny oddziela się spacjami, a wiersze - znakami powrotu karetki. Excel tego typu pliki importuje do pojedynczej kolumny. DIF: ten format był kiedyś używany w programie VisiCalc. Obecnie jest rzadko używany. SYLK: ten format był kiedyś używany w programie Multiplan. Obecnie jest rzadko używany.

Większość wymienionych typów plików ma różne warianty. Na przykład w plikach tekstowych utworzonych w komputerach Macintosh stosowane są inne niż w Windows oznaczenia końca wiersza. Excel zazwyczaj dobrze sobie z tym wszystkim radzi.

Przy próbie otwarcia pliku tekstowego w Excelu może pojawić się okno Kreatora importu tekstu pomagającego prawidłowo zaimportować dane.

 

Wskazówka

Jeśli nie chcesz korzystać z Kreatora importu tekstu, przytrzymaj wciśnięty klawisz Shift, klikając przycisk Otwórz w oknie dialogowym Otwieranie.

Importowanie plików HTML

Excel otwiera większość plików HTML, które mogą być zapisane zarówno na lokalnym dysku komputera, jak i na serwerze sieciowym. Kliknij opcję Plik/Otwórz i znajdź plik HTML, który chcesz otworzyć. Jeżeli plik znajduje się na serwerze, skopiuj jego adres URL i wklej go w polu Nazwa pliku.

Wygląd plików HTML w Excelu może być bardzo różny. Czasami niczym się nie różni od wyglądu w oknie przeglądarki internetowej, a czasami może go mało przypominać, szczególnie gdy do budowy strony użyto kaskadowych arkuszy stylów (CSS).

 

Odwołanie

Czasami dane z internetu można pobrać za pomocą polecenia Dane/Pobieranie danych zewnętrznych/Z sieci Web.

Importowanie plików XML

XML (ang. eXtensible Markup Language) to tekstowy format plików służący do przechowywania danych strukturalnych. Informacje oznacza się specjalnymi znacznikami, które służą do ich opisu.

Excel otwiera pliki XML i jeśli są proste, to wyświetlenie ich zawartości nie sprawia mu problemów. Natomiast bardziej skomplikowane pliki tego typu mogą wymagać trochę pracy ze strony programu. Nie jest to jednak odpowiedni temat do zgłębiania w tej książce, więc dodatkowych informacji możesz poszukać np. w pomocy Excela.

Importowanie pliku tekstowego do wybranego zakresu

Może Ci się wydawać, że jeśli trzeba pobrać do Excela plik tekstowy, to jedyną możliwością jest zaimportowanie tego tekstu do nowego skoroszytu, a następnie skopiowanie danych do odpowiedniego zakresu komórek. Nie jest to jednak konieczne. Można to zrobić w prostszy sposób.

Na rysunku 16.2 widać zawartość niewielkiego pliku CSV o nazwie miesiące.csv. Poniżej znajdują się instrukcje, jak dane z tego pliku zaimportować do zakresu, którego początek znajduje się w komórce C3.

 

Rysunek 16.2. Plik CSV, który zostanie zaimportowany do zakresu

1. Kliknij opcję Dane/Pobieranie danych zewnętrznych/Z tekstu, aby wyświetlić okno dialogowe Importowanie pliku tekstowego.

2. Przejdź do folderu zawierającego Twój plik tekstowy.

3. Wybierz plik z listy i kliknij przycisk Importuj, aby wyświetlić okno Kreator importu tekstu.

4. W oknie Kreator importu tekstu należy określić sposób importu pliku. W pierwszym oknie kreatora nic nie zmieniaj, tylko kliknij przycisk Dalej.

5. Ponieważ chcesz zaimportować plik CSV, w drugim oknie kreatora, w sekcji Ograniczniki, usuń zaznaczenie pola wyboru Tabulator i zaznacz pole Przecinek.

6. Kliknij przycisk Zakończ. Excel wyświetli okno dialogowe Importowanie danych widoczne na rysunku 16.3.

7. W oknie dialogowym Importowanie danych kliknij przycisk Właściwości, aby wyświetlić okno dialogowe Właściwości zakresu danych zewnętrznych.

8. W oknie Właściwości zakresu danych zewnętrznych usuń zaznaczenie opcji Zapisz definicję kwerendy i kliknij przycisk OK, aby wrócić do okna dialogowego Importowanie danych.

 

Rysunek 16.3. Okno dialogowe Importowanie danych w trakcie importowania do arkusza zawartości pliku CSV

9. W oknie dialogowym Importowanie danych określ, w którym miejscu mają zostać zaimportowane dane (można wyznaczyć komórkę w istniejącym lub nowym arkuszu).

10. Kliknij przycisk OK, aby zaimportować dane (rysunek 16.4).

 

Rysunek 16.4. Ten zakres zawiera dane zaimportowane z pliku CSV

 

Uwaga

Jeśli importowane dane mogą się zmieniać, to możesz pominąć punkt 8. na powyższej liście. Dzięki zapisaniu definicji kwerendy można łatwo odświeżyć zawartość zakresu, klikając dowolną z jego komórek i wybierając opcję Odśwież dane.

Kopiowanie i wklejanie danych

Jeśli żadna inna metoda nie daje oczekiwanego efektu, zawsze można spróbować zwykłego kopiowania tekstu. Gdy skopiuje się dane z jakiejś aplikacji (np. edytora tekstu albo pliku PDF), to są spore szanse, że będzie je można wkleić do arkusza. Podczas wklejania wypróbuj kilka opcji z okna Narzędzia główne/Schowek/Wklej/Wklej specjalnie. Zazwyczaj dane wklejone w ten sposób trzeba jeszcze trochę uporządkować.

Techniki porządkowania danych

W tym podrozdziale znajduje się opis różnych technik porządkowania danych w arkuszach.

 

Odwołanie

W rozdziale 5. znajduje się jeszcze więcej przykładów formuł dotyczących pracy z tekstem, które mogą być przydatne w porządkowaniu danych.

Usuwanie duplikatów wierszy

Jeśli dane pochodzą z różnych źródeł, mogą wśród nich wystąpić duplikaty wierszy, które najczęściej są niepotrzebne. Kiedyś duplikaty usuwało się ręcznie, chociaż dało się te czynności zautomatyzować przy użyciu skomplikowanych i trudnych do zrozumienia filtrów. Od Excela 2007 usuwanie duplikatów wierszy jest bardzo łatwe dzięki narzędziu usuwania duplikatów.

Aby skorzystać z tej funkcji, umieść kursor w dowolnej komórce zakresu. Kliknij opcję Dane/ Narzędzia danych/Usuń duplikaty, aby wyświetlić widoczne na rysunku 16.5 okno dialogowe Usuwanie duplikatów.

 

Uwaga

Jeśli dane znajdują się w tabeli, można też użyć polecenia Narzędzia tabel/Projektowanie/Narzędzia/Usuń duplikaty.

W oknie dialogowym Usuwanie duplikatów znajduje się lista wszystkich kolumn zaznaczonego zakresu lub wybranej tabeli. Przeszukane zostaną te kolumny, których pola wyboru zostaną zaznaczone. Najczęściej trzeba przeszukać wszystkie kolumny, które są zaznaczone domyślnie. W takim przypadku wystarczy kliknąć przycisk OK, aby Excel usunął wszystkie duplikaty wierszy i wyświetlił informację, ile ich znalazł. Przydałaby się funkcja umożliwiająca jedynie zaznaczanie podejrzanych wierszy bez ich usuwania, aby można je było przejrzeć. Niestety nie ma takiej funkcji. Jeżeli program usunie za dużo wierszy, można to cofnąć za pomocą funkcji Cofnij (Ctrl+Z).

 

Rysunek 16.5. Usuwanie duplikatów wierszy za pomocą narzędzi w oknie Usuwanie duplikatów

Jeśli w oknie Usuwanie duplikatów zostaną zaznaczone wszystkie kolumny, Excel usunie tylko te wiersze, które mają identyczne wartości we wszystkich kolumnach. Jeśli jednak zawartość niektórych kolumn Cię nie obchodzi, możesz usunąć ich zaznaczenie w oknie Usuwanie duplikatów. Na przykład każdy wiersz może mieć unikatowy identyfikator. Gdyby brać pod uwagę identyfikatory, program nie znalazłby ani jednego duplikatu. Dlatego można usunąć zaznaczenie kolumny identyfikatorów, aby nie była brana pod uwagę.

Gdy zostaną znalezione duplikaty, zachowany zostaje pierwszy wiersz, a wszystkie pozostałe są usuwane.

 

Ostrzeżenie

Duplikaty są wyszukiwane wg wartości wyświetlanych w komórkach - nie tego, co się w nich istotnie znajduje. Załóżmy na przykład, że dwie komórki zawierają tę samą datę. Jedna jest w formacie 2013-05-15, a druga - 15 maja 2013. Przy usuwaniu duplikatów te dwie komórki dla Excela są różne. To samo dotyczy wartości liczbowych o różnych formatach. Na przykład 1209,32 zł to nie to samo co 1209,32. Jeśli więc chcesz mieć pewność, że żadne duplikaty nie zostaną przez przypadek pominięte, powinieneś zastosować jednolite formatowanie dla wszystkich kolumn.

Identyfikowanie duplikatów wierszy

Jeśli chcesz znaleźć duplikaty wierszy bez ich usuwania, możesz skorzystać z innej metody. W odróżnieniu od techniki opisanej w poprzednim podrozdziale opisana tutaj technika sprawdza rzeczywiste, a nie sformatowane wartości.

Po prawej stronie swoich danych utwórz formułę łączącą wszystkie komórki znajdujące się po lewej. W poniższej formule przyjęte jest, że dane znajdują się w kolumnach A - F:

Formułę tę wpisz w komórce G2:

=A2&B2&C2&D2&E2&F2

Potrzebna jest jeszcze jedna formuła, którą należy ją wpisać w H2. Wyświetla ona, ile razy występuje wartość z kolumny G:

=LICZ.JEŻELI(G:G;G2)

Skopiuj te formuły w dół dla wszystkich wierszy z danymi.

W kolumnie H wyświetlona jest liczba wystąpień duplikatów w poszczególnych wierszach. Wartość 1 oznacza, że nie ma powtórzeń. Każda większa wartość oznacza, ile razy powtarza się dany wiersz w zbiorze danych.

Na rysunku 16.6 przedstawiono prosty przykład. Jeśli któraś z kolumn nie ma znaczenia, usuń ją z formuły w kolumnie G. Gdybyśmy na przykład chcieli wyszukiwać duplikaty, nie uwzględniając tego, co znajduje się w kolumnie Status, z formuły tej usunęlibyśmy kolumnę D.

 

Rysunek 16.6. Szukanie duplikatów wierszy za pomocą formuły

Dzielenie tekstu

Podczas importowania danych do jednej kolumny może zostać zaimportowanych wiele wartości. Problem tego typu jest przedstawiony na rysunku 16.7.

 

Wskazówka

Dane widoczne na rysunku 16.7 są wyświetlone przy użyciu czcionki o stałej szerokości znaków Courier, aby było widać, jak układają się w równe kolumny.

 

Rysunek 16.7. Zaimportowane dane zostały umieszczone w jednej kolumnie zamiast w kilku kolumnach

Jeśli wszystkie fragmenty tekstu mają taką samą długość - jak w tym przykładzie - to można napisać serię formuł pobierających dane do osobnych kolumn. Do realizacji tego celu potrzebne będą funkcje LEWY, PRAWY i FRAGMENT.TEKSTU (przykłady ich użycia znajdują się w rozdziale 5.).

Oprócz tego w Excelu dane można dzielić na kolumny przy użyciu dwóch metod niewymagających stosowania formuł. Są to narzędzia Tekst jako kolumny i Wypełnianie błyskawiczne.

Zastosowanie narzędzia Tekst jako kolumny

Narzędzie Tekst jako kolumny umożliwia dzielenie łańcuchów tekstu na części.

Po prawej stronie kolumny zawierającej dane przeznaczone do podzielenia powinna znajdować się liczba pustych kolumn wystarczająca na pomieszczenie nowych części. Gdy wszystko jest gotowe, użyj narzędzia Dane/Narzędzia danych/Tekst jako kolumny.

Kliknięcie tego polecenia spowoduje pojawienie się okna dialogowego Kreator konwersji tekstu na kolumny. Są w nim wyświetlane polecenia, które należy wykonać, aby utworzyć kolumny ze zbioru danych. Na rysunku 16.8 pokazane jest pierwsze okno kreatora, w którym wybiera się typ danych:

Rozdzielany: dane, które mają zostać podzielone, są rozdzielone jakimiś znakami, np. przecinkami, spacjami albo ukośnikami. Stała szerokość: wszystkie składniki zawierają po tyle samo znaków.

Dokonaj wyboru jednej z dwóch możliwości i kliknij przycisk Dalej, aby przejść do kroku 2., który zależy od opcji wybranej w kroku 1.

Jeśli pola są czymś oddzielane, podaj, jaki znak lub jakie znaki są używane do ich rozdzielania. Na dole widać podgląd efektu zastosowanych ustawień. Jeśli zaznaczona jest opcja stałej szerokości, to po przejściu do kroku 2. można ustawiać linie podziału bezpośrednio w oknie podglądu.

 

Rysunek 16.8. Pierwsze okno dialogowe Kreatora konwersji tekstu na kolumny

Po osiągnięciu satysfakcjonującego podziału na kolumny kliknij przycisk Dalej, aby przejść do 3. etapu. Tu można kliknąć kolumnę w oknie podglądu, aby określić dla niej formatowanie albo zaznaczyć, że kolumna ta ma zostać pominięta. Kliknij przycisk Zakończ, aby podzielić dane według zdefiniowanych kryteriów. Oryginalne dane zostaną zastąpione nowymi.

Zastosowanie narzędzia Wypełnianie błyskawiczne

Narzędzie Tekst jako kolumny jest przydatne w wielu sytuacjach, ale niektórych zbiorów danych za jego pomocą podzielić się nie da. Przykładowo nie skorzystamy z niego, gdy kolumny danych są różnej szerokości i nie mają znaków oddzielających. W takich przypadkach można próbować ratować się narzędziem Wypełnianie błyskawiczne.

 

Nowość

Wypełnianie błyskawiczne to nowe narzędzie w Excelu 2013.

Narzędzie Wypełnianie błyskawiczne umożliwia wydobywanie i łączenie danych poprzez definiowanie wzorców. Wystarczy wpisać kilka przykładów w kolumnie sąsiadującej z danymi i kliknąć opcję Dane/Narzędzia danych/Wypełnianie błyskawiczne (albo nacisnąć klawisze Ctrl+E). Excel przeanalizuje wzory i spróbuje na ich podstawie wypełnić pozostałe komórki. Jeśli efekt nie będzie zadowalający, można nacisnąć klawisze Ctrl+Z, dodać parę kolejnych przykładów i spróbować jeszcze raz.

Na rysunku 16.9 widoczny jest arkusz zawierający trochę tekstu w jednej kolumnie. Naszym celem jest pobranie liczby z każdej komórki i przeniesienie jej do osobnej komórki. Za pomocą narzędzia Tekst jako kolumny nie da się tego zrobić, bo znaki rozdzielające nie są zastosowane w sposób jednolity. Można by było napisać formułę tablicową, ale byłaby ona bardzo skomplikowana.

 

Rysunek 16.9. Celem jest wydobycie liczb z kolumny A

Innym rozwiązaniem jest napisanie funkcji arkusza w języku VBA. Jednak narzędzie wypełniania błyskawicznego też powinno się tu sprawdzić.

Aby skorzystać z funkcji wypełniania błyskawicznego, wpisz w komórce B1 liczbę 20. Następnie w komórce B2 wpisz liczbę 2. Czy narzędzie rozpozna, że chodzi o wydobycie liczb i wypełni nimi pozostałe kolumny? Kliknij opcję Dane/Narzędzia danych/Wypełnianie błyskawiczne (albo naciśnij klawisze Ctrl+E), aby się przekonać (rysunek 16.10).

 

Rysunek 16.10. Używając wzorca wprowadzonego w komórkach B1 i B2, Excel popełnił kilka błędów

Wygląda to nieźle. W kolumnie znalazły się same liczby, ale jeśli się im przyjrzysz, zauważysz, że w liczbach ułamkowych są błędy. Precyzję rozpoznawania można zwiększyć, dodając więcej przykładów - np. z liczbą ułamkową. Usuń zaproponowane przez program wartości i w komórce B6 wpisz 35,65, a następnie naciśnij klawisze Ctrl+E. Teraz Excel wszystko rozpozna prawidłowo, co widać na rysunku 16.11.

Przykład ten zwraca uwagę na dwie ważne kwestie:

Po użyciu funkcji wypełniania błyskawicznego należy dokładnie przyjrzeć się otrzymanym wynikom. To, że kilka pierwszych wierszy zawiera poprawne wartości, nie znaczy, że wszystkie pozostałe również takie są. Im więcej poda się przykładów, tym wypełnianie błyskawiczne jest dokładniejsze.

 

Rysunek 16.11. Mając przykład liczby ułamkowej, Excel prawidłowo rozpoznał wszystkie wartości

Na rysunku 16.12 pokazany jest jeszcze jeden przykład. Tym razem celem było wyłuskanie imienia, drugiego imienia i nazwiska z kolumny A. W kolumnie B funkcja zadziałała doskonale, mając podane tylko dwa przykłady (Marek i Tomasz). Udało się także poprawnie wydobyć nazwiska przy użyciu przykładów Kowalski i Nowak. Drugiego imienia nie udawało się wyłuskać, aż w końcu doszedłem, że pomaga w tym wstawienie spacji na początku i końcu przykładu.

 

Rysunek 16.12. Wydobywanie imion i nazwisk za pomocą funkcji wypełniania błyskawicznego

 

Odwołanie

W rozdziale 5. znajduje się przykładowa formuła służąca do rozdzielania imion i nazwisk.

Oto kolejny przykład użycia funkcji wypełniania błyskawicznego. Mamy listę adresów internetowych i chcemy z nich wydobyć nazwy plików (czyli tekst znajdujący się za ostatnim ukośnikiem).

Na rysunku 16.13 widać arkusz z listą adresów. Funkcji wypełniania błyskawicznego wystarczył jeden przykład nazwy pliku w kolumnie B, aby poprawnie pobrać wszystkie pozostałe nazwy plików. Równie dobrze spisała się przy usuwania nazwy plików z adresów w kolumnie C.

 

Rysunek 16.13. Zastosowanie narzędzia wypełniania błyskawicznego do pobrania nazw plików z adresów URL

Narzędzie Wypełnianie błyskawiczne Excela to bardzo ciekawa funkcja, ale jego wielkim ograniczeniem w porównaniu z formułami jest brak dynamiki. Jeśli zmieni się coś w pierwotnym zbiorze danych, wypełnione kolumny pozostaną bez zmian.

Funkcja ta działa niezawodnie, gdy dane są bardzo spójne, ale i tak dobrze jest dokładnie przejrzeć wyniki, aby mieć pewność, że wszystko jest w porządku. Szczególnie jeśli informacje są bardzo ważne, należy wszystko sprawdzić z wyjątkową starannością. Jest to jedyny możliwy sposób weryfikacji.

 

Uwaga

Za pomocą narzędzia wypełniania błyskawicznego można też tworzyć nowe dane z wielu kolumn. Wystarczy podać kilka przykładów, jak te dane mają wyglądać, a Excel rozpozna wzór i wypełni całą kolumnę.

Zmienianie wielkości liter

Czasami trzeba ujednolicić wielkość liter we wszystkich komórkach kolumny. W Excelu nie ma bezpośredniej metody na zrobienie tego, ale łatwo można poradzić sobie przy użyciu formuł. Zobacz ramkę "Przekształcanie danych przy użyciu formuł".

Trzy funkcje, których się w tym celu używa, to:

LITERY.WIELKIE zamienia cały tekst na wielkie litery. LITERY.MAŁE zamienia cały tekst na małe litery. Z.WIELKIEJ.LITERY zamienia pierwszą literę każdego wyrazu na wielką.

Sposób użycia tych funkcji jest w miarę prosty. Działają tylko na literach, a wszystkie inne znaki ignorują, zwracając je bez zmian.

Jeśli użyjesz funkcji Z.WIELKIEJ.LITERY, zapewne będzie konieczne dokonanie pewnych poprawek, aby wszystko wyglądało tak jak należy. Oto kilka przykładów przekształceń dokonanych przy użyciu tej funkcji, które pewnie by Ci się nie spodobały:

 

Jeśli piszesz coś po angielsku, to na wielką zostaną zmienione wszystkie litery znajdujące się za apostrofami, jak np. w wyrażeniu Don'T. Czasami jest to przydatne, bo jest np. wydawnictwo o nazwie O'Reilly, ale to wyjątki. Funkcja Z.WIELKIEJ.LITERY nie obsługuje nazwisk zawierających wielką literę w środku, np. McDonald. Funkcja zamienia na wielkie także krótkie wyrazy typu "i" czy "lub". Na przykład w nazwie kraju "United States of America" słowo "of" nie powinno być napisane wielką literą.

Usterki tego typu często można szybko wyeliminować za pomocą funkcji znajdowania i zamieniania.

Usuwanie niepotrzebnych spacji

W danych raczej nie powinno być niepotrzebnych spacji, a przecież nie da się ich zauważyć, jeśli znajdują się np. na końcu tekstu. Spacje mogą powodować wiele problemów, w szczególności przy porównywaniu łańcuchów tekstu. Łańcuch "lipiec" nie jest tym samym co "lipiec " (ze spacją na końcu) - pierwszy zawiera sześć znaków, a drugi siedem.

Aby pozbyć się spacji, można utworzyć formułę z użyciem funkcji USUŃ.ZBĘDNE.ODSTĘPY usuwającej odstępy z początku i końca łańcuchów oraz zamieniającej ciągi odstępów na pojedyncze wewnątrz łańcuchów. Poniższa formuła zwraca napis "Przychody w czwartym kwartale" (bez zbędnych spacji):

=USUŃ.ZBĘDNE.ODSTĘPY("Przychody w czwartym kwartale ")

W danych importowanych ze stron internetowych często można znaleźć różne rodzaje białych znaków i spacji, m.in. spacje niełamliwe oznaczane w kodzie HTML za pomocą encji  . W Excelu znak ten można uzyskać za pomocą poniższej formuły:

=ZNAK(160)

Aby zamienić spacje niełamliwe na zwykłe, można użyć poniższej formuły:

=PODSTAW(A2;ZNAK(160);" ")

Poniższa formuła zastępuje spacje niełamliwe zwykłymi i usuwa nadmiarowe spacje:

=USUŃ.ZBĘDNE.ODSTĘPY(PODSTAW(A2;ZNAK(160);" "))

Usuwanie dziwnych znaków

Dane importowane do Excela często zawierają różne dziwne niedrukowalne znaki. Aby się ich pozbyć, można użyć funkcji OCZYŚĆ. Jeśli dane do oczyszczenia znajdują się w komórce A2, to wystarczy użyć poniższej formuły:

=OCZYŚĆ(A2)

 

Uwaga

Funkcja OCZYŚĆ może pominąć niektóre niedrukowalne znaki Unicode, ponieważ rozpoznaje tylko 32 pierwsze znaki z 7-bitowego zestawu ASCII. Informacje na temat tego, jak pozbyć się niedrukowalnych znaków z zestawu Unicode, można znaleźć w pomocy Excela (szukaj funkcji OCZYŚĆ).

Konwertowanie wartości

Czasami trzeba przekonwertować wartości wyrażone w jednej jednostce miary na inną. Na przykład zaimportowaliśmy bazę danych zawierającą wartości w uncjach objętości, a chcemy mieć wartości w mililitrach. Do tego i wielu innych zadań doskonale nadaje się funkcja KONWERTUJ.

Jeśli w komórce A2 znajduje się wartość w uncjach, to poniższa formuła zamieni ją na mililitry:

=KONWERTUJ(A2; "oz"; "ml")

Jest to niezwykle wszechstronna funkcja, za pomocą której można zamienić większość powszechnie używanych jednostek.

 

Odwołanie

Więcej informacji o funkcji KONWERTUJ znajduje się w rozdziale 10.

Excel ma też funkcje do zamiany liczb o różnych podstawach. Możemy na przykład zaimportować plik zawierający liczby szesnastkowe, które chcielibyśmy mieć w formacie dziesiętnym. W takim przypadku pomocna będzie funkcja SZESN.NA.DZIES. Przykładowo poniższa formuła zwraca liczbę 1279, która jest dziesiętnym odpowiednikiem szesnastkowego argumentu użytego w jej funkcji:

=SZESN.NA.DZIES("4FF")

Ponadto można konwertować liczby binarne na dziesiętne (DWÓJK.NA.DZIES) i ósemkowe na dziesiętne (ÓSM.NA.DZIES).

Są też funkcje działające w drugą stronę: DZIES.NA.SZESN, DZIES.NA.DWÓJK oraz DZIES.NA.ÓSM.

 

Nowość

W Excelu 2013 dodano nową funkcję o nazwie PODSTAWA, zamieniającą liczby dziesiętne na liczby o dowolnej podstawie. Nie ma jednak funkcji działającej w drugą stronę, tzn. nie ma funkcji zamieniającej liczby o dowolnej podstawie na dziesiętne. Można to robić tylko z liczbami binarnymi, ósemkowymi i szesnastkowymi.

Klasyfikowanie wartości

Czasami wartości z jakiegoś zboru trzeba zaklasyfikować do różnych grup. Przykładowo: jeżeli mamy zbiór danych o wieku osób, możemy chcieć te osoby zaklasyfikować do różnych grup wiekowych typu do 17 lat, 18 - 24, 25 - 34 itd.

Najłatwiejszym sposobem na dokonanie takiego podziału jest użycie tabeli wyszukiwania. Na rysunku 16.14 widać arkusz, którego kolumna A zawiera dane dotyczące wieku osób, a kolumna B - ich klasyfikacje. W kolumnie tej użyta jest tabela wyszukiwania znajdująca się w zakresie D2:E9. W komórce B2 znajduje się następująca formuła:

=WYSZUKAJ.PIONOWO(A2;$D$2:$E$9;2)

Formuła ta została skopiowana w dół.

Tabeli wyszukiwania można też użyć z danymi nieliczbowymi. Na rysunku 16.15 pokazany jest arkusz, w którym przypisane zostały regiony do stanów.

 

Rysunek 16.14. Użycie tabeli wyszukiwania do przypisywania osób do grup wiekowych

 

Rysunek 16.15. Użycie tabeli wyszukiwania w celu przypisania regionów do stanów

Składająca się z dwóch kolumn tabela wyszukiwania znajduje się w zakresie D2:E52. W komórce B2 znajduje się poniższa formuła, która została skopiowana w dół:

=WYSZUKAJ.PIONOWO(A2;$D$2:$E$52;2;FAŁSZ)

 

Wskazówka

Dodatkową korzyścią z użycia funkcji WYSZUKAJ.PIONOWO jest to, że zwraca ona fałsz w przypadkach, gdy dopasowanie nie jest dokładne. Jest to dobry sposób na znalezienie literówek. Wartość FAŁSZ w ostatnim argumencie tej funkcji sprawia, że wymagane jest dokładne dopasowanie.

Łączenie kolumn

Do łączenia danych z kilku kolumn można używać operatora &. Na przykład poniższa formuła łączy zawartość komórek A1, B1 i C1:

=A1&B1&C1

Często, np. gdy kolumny zawierają stopień naukowy, imię i nazwisko, między wartościami z poszczególnych komórek trzeba wstawić spacje. Przy użyciu powyższej formuły otrzymalibyśmy wynik typu mgrŁukaszPiwko. Aby dodać spacje, można zmienić formułę w następujący sposób:

=A1&" "&B1&" "&C1

Kolumny można też połączyć bez użycia formuł za pomocą funkcji wypełniania błyskawicznego. Należy podać dwa przykłady w sąsiedniej kolumnie i nacisnąć klawisze Ctrl+E.

Zmienianie kolejności kolumn

Jeśli trzeba zmienić kolejność kolumn, można utworzyć pustą kolumnę, a następnie przeciągnąć na nią dowolną inną kolumnę. Ale wtedy powstanie luka, którą trzeba zlikwidować.

Poniżej znajduje się opis prostszego sposobu:

1. Kliknij nagłówek kolumny, którą chcesz przenieść.

2. Kliknij opcję Narzędzia główne/Schowek/Wytnij.

3. Kliknij nagłówek kolumny znajdującej się po prawej stronie tej kolumny, do której chcesz wstawić wycięte komórki.

4. Kliknij prawym przyciskiem myszy zaznaczony nagłówek i wybierz opcję Wstaw wycięte komórki.

Powtórz te czynności dla wszystkich kolumn, które chcesz przenieść.

Losowe mieszanie wierszy

Jeśli chcesz ustawić wiersze w losowej kolejności, to możesz to zrobić w bardzo łatwy sposób. W pierwszej komórce kolumny znajdującej się po prawej stronie danych, które chcesz pomieszać, wpisz poniższą formułę i skopiuj ją w dół:

=LOS()

Następnie posortuj dane przy użyciu tej kolumny. Wiersze zostaną ustawione w losowej kolejności i będzie można usunąć kolumnę.

Znajdowanie tekstu z listy

Czasami mamy zbiór danych, które trzeba porównać z jakąś listą. Na przykład możemy chcieć znaleźć wiersze, w których dane w określonej kolumnie znajdują się na innej liście. Prosty przykład pokazany jest na rysunku 16.16. Dane znajdują się w kolumnach A - B. Naszym celem jest zidentyfikowanie wierszy, których Numer członka występuje na liście Wypisani w kolumnie F. Wiersze te można usunąć.

 

Rysunek 16.16. Celem jest znalezienie numerów członków znajdujących się na liście wypisanych

Poniższa formuła została wpisana w komórce D2 i skopiowana w dół:

=JEŻELI(LICZ.JEŻELI($F$2:$F$17;B2)>0;"Wypisany";"")

Formuła ta wyświetla słowo Wypisany, jeśli numer członka wpisany w kolumnie B występuje na liście wypisanych członków w kolumnie F. Jeśli numeru nie ma na tej liście, formuła zwraca pusty łańcuch. Listę można posortować według kolumny D, aby szybko usunąć wszystkich członków, którzy się wypisali.

Technikę tę można oczywiście dostosować także do innych tego typu zadań.

Zamienianie pionowych danych na poziome

Na rysunku 16.17 przedstawiony jest typowy zbiór zaimportowanych danych. Każdy rekord składa się z trzech kolejnych komórek w tej samej kolumnie: imię i nazwisko, dział oraz lokalizacja.

 

Rysunek 16.17. Pionowe dane, które trzeba zamienić na poziome

Naszym celem jest sprawienie, aby każdy rekord znajdował się w osobnym wierszu obejmującym trzy kolumny.

Dane te można przekształcić przy użyciu kilku metod, ale jedna z nich wyróżnia się spośród wszystkich prostotą. Wymaga niewiele przygotowywania i napisania tylko jednej formuły, którą należy skopiować w całym zakresie.

Najpierw utwórz numeryczne poziome i pionowe nagłówki, jak widać na rysunku 16.18. Kolumna C zawiera liczby odpowiadające pierwszemu wierszowi każdego rekordu (w tym przypadku zawierającemu imię i nazwisko). W tym przykładzie w kolumnie tej zostały wpisane liczby 1, 4, 7, 10, 13, 16 i 19. Szereg tych liczb można łatwo wygenerować przy użyciu prostej formuły.

Pozioma lista nagłówków zawiera kolejne liczby całkowite od 1. Jako że każdy rekord zawiera trzy wiersze danych, nagłówki te zawierają liczby 1, 2 i 3.

Poniżej znajduje się formuła, którą należy wpisać w komórce D2:

=PRZESUNIĘCIE($A$1;$C2+D$1-2;0)

Skopiuj tę formułę do dwóch następnych kolumn i do sześciu kolejnych wierszy. Efekt tej czynności jest przedstawiony na rysunku 16.19.

 

Rysunek 16.18. Nagłówki służące do przekonwertowania pionowych danych na wiersze

 

Rysunek 16.19. Efekt przekształcenia danych na poziome za pomocą jednej formuły

Technikę tę można łatwo dostosować do pionowych danych zawierających różne liczby wierszy. Gdyby na przykład każdy rekord składał się z dziesięciu wierszy, to w nagłówkach w kolumnie C znajdowałyby się wartości 1, 11, 21, 31 itd., a poziome nagłówki miałyby wartości od 1 do 10.

Zwróć uwagę na użycie w przedstawionej formule bezwzględnego odwołania do komórki A1. Odwołanie to nie zmienia się podczas kopiowania formuły, dzięki czemu wszystkie kopie odwołują się do komórki A1 jako bazowej. Jeżeli początek danych znajdowałby się w innej komórce, należałoby adres $A$1 zmienić na inny.

W drugim argumencie funkcji PRZESUNIĘCIE użyte zostało odwołanie mieszane. W odwołaniu do komórki C2 znajduje się symbol dolara przed literą C, co oznacza, że kolumna C jest w tym odwołaniu częścią bezwzględną. W odwołaniu do komórki D1 znak dolara znajduje się przed liczbą 1, dzięki czemu bezwzględną częścią jest wiersz 1. Więcej informacji o odwołaniach względnych i bezwzględnych znajduje się w rozdziale 2.

Zapełnianie luk w zaimportowanym raporcie

Po zaimportowaniu danych do arkusza można czasami otrzymać taką bazę danych, jak widać na rysunku 16.20. Jest to często spotykany sposób formatowania raportów. Wpis w kolumnie A odnosi się do kilku wierszy. Gdybyśmy posortowali taką listę, brakujące dane spowodowałyby spory bałagan i nie dałoby się z tego wyczytać kto, kiedy i ile sprzedał.

 

Rysunek 16.20. Raport zawierający luki w jednej kolumnie

Jeżeli raport jest niewielki, to brakujące komórki można uzupełnić ręcznie albo wielokrotnie używając polecenia Narzędzia główne/Edytowanie/Wypełnij/W dół (uruchamianego także skrótem klawiszowym Ctrl+D). Jeśli jednak lista jest długa, to lepiej zastosować rozwiązanie opisane poniżej:

1. Zaznacz zawierający luki zakres (w tym przykładzie jest to zakres A3:A14).

2. Kliknij polecenie Narzędzia główne/Edytowanie/Znajdź i zaznacz/Przejdź do - specjalnie.

3. W oknie dialogowym Przechodzenie do - specjalnie zaznacz opcję Puste i kliknij przycisk OK. Spowoduje to zaznaczenie wszystkich pustych komórek w wybranym zakresie.

4. W pasku formuły wpisz znak równości i adres pierwszej komórki w kolumnie zawierającej wpis (=A3) i naciśnij klawisze Ctrl+Enter.

5. Ponownie zaznacz cały pierwotny zakres i naciśnij klawisze Ctrl+C, aby skopiować zaznaczone komórki.

6. Kliknij polecenie Narzędzia główne/Schowek/Wklej/Wklej wartości/Wklej (W), aby przekonwertować formuły na wartości.

Gdy wykonasz te czynności, luki zostaną poprawnie zapełnione, a arkusz będzie wyglądał tak jak na rysunku 16.21.

 

Rysunek 16.21. Luki zniknęły, dzięki czemu listę można sortować

Sprawdzanie pisowni

Jeśli używasz edytora tekstu, to z pewnością korzystasz z jego funkcji sprawdzania pisowni. W tekście literówki są zazwyczaj tylko drobną usterką, ale w danych w arkuszu kalkulacyjnym mogą powodować poważne problemy. Przykładowo: jeśli w tabeli przestawnej przedstawisz dane według miesięcy i jeden miesiąc będzie źle napisany, to może się okazać, że rok w Twoim arkuszu ma 13 miesięcy.

Aby użyć funkcji sprawdzania pisowni Excela, należy kliknąć polecenie Recenzja/Sprawdzanie/ Pisownia albo nacisnąć klawisz F7. Jeżeli chcesz sprawdzić pisownię tylko w wybranym zakresie, najpierw zaznacz ten zakres i dopiero potem użyj funkcji sprawdzania pisowni.

Jeśli moduł sprawdzania pisowni znajdzie jakieś błędy, wyświetli okno dialogowe Pisownia zawierające opcje, które nie wymagają objaśnienia.

Podmienianie i usuwanie tekstu komórek

Czasami trzeba usunąć określone znaki z całej kolumny danych albo zastąpić wybrane znaki innymi, np. ukośniki wsteczne przednimi. W wielu przypadkach do wykonania tego zadania wystarczy funkcja znajdowania i zamieniania Excela. Aby usunąć wybrane znaki z komórek, wystarczy pozostawić pole Zamień na puste.

Czasami jednak może być konieczne użycie do tego celu formuły. Spójrz na dane widoczne na rysunku 16.22. Celem jest zamiana drugiego łącznika na dwukropek. Nie da się tego zrobić za pomocą narzędzia znajdowania i zamieniania, ponieważ nie da się w nim określić, że zamieniony ma zostać tylko drugi łącznik w każdej komórce.

 

Rysunek 16.22. Do zamiany tylko drugiego łącznika w każdej komórce w tym arkuszu narzędzie Znajdź i zamień się nie nadaje

Można jednak sobie poradzić, pisząc prostą formułę zamieniającą drugi łącznik na dwukropek:

=PODSTAW(A2;"-";":";2)

Aby usunąć drugi łącznik, wystarczy opuścić trzeci argument funkcji PODSTAW:

=PODSTAW(A2;"-";;2)

Jest to także jedna z sytuacji, w której można użyć funkcji wypełniania błyskawicznego.

 

Uwaga

Jeśli znasz jakiś język programowania, to możliwe, że umiesz posługiwać się wyrażeniami regularnymi. Wyrażenie regularne to bardzo zwięzły (i często trudny do zrozumienia) kod definiujący wzór tekstowy. Excel nie obsługuje wyrażeń regularnych, ale w internecie można znaleźć wiele poradników, jak ich używać w języku VBA. Są też dodatki umożliwiające korzystanie z tej techniki w arkuszach kalkulacyjnych.

 

 

Dodawanie tekstu do komórek

Jeśli trzeba dodać tekst do komórki, to jedynym wyjściem jest użycie nowej kolumny z formułami. Oto kilka przykładów.

Ta formuła dodaje tekst "ID:" na początku komórki:

="ID: "&A2

Ta formuła dodaje tekst ".mp3" na końcu komórki:

=A2&".mp3"

Ta formuła wstawia łącznik za trzecim znakiem w komórce:

=LEWY(A2;3)&"-"&PRAWY(A2;DŁ(A2)-3)

Do dodawania tekstu do komórek można też użyć narzędzia wypełniania błyskawicznego.

Ustawianie znaku minusa na właściwym miejscu

Czasami w importowanych danych znak minusa znajduje się za liczbą zamiast przed nią, np. 3498- zamiast -3498. Excel nie konwertuje takich wartości, a nawet nie traktuje ich jako liczbowych.

Rozwiązanie jest tak proste, że pewnie będziesz zaskoczony:

1. Zaznacz dane zawierające znak minusa za liczbą. W zaznaczeniu mogą też znajdować się dodatnie liczby.

2. Kliknij polecenie Dane/Narzędzia danych/Tekst jako kolumny.

3. W oknie dialogowym Tekst jako kolumny kliknij przycisk Zakończ.

Technika ta działa dzięki domyślnym ustawieniom w oknie dialogowym Zaawansowane ustawienia importu tekstu (którego normalnie nawet nie widzimy). Aby je wyświetlić, należy dojść do kroku 3. w kreatorze narzędzia Tekst jako kolumny i kliknąć przycisk Zaawansowane (rysunek 16.23).

 

Rysunek 16.23. Dzięki opcji Znak minus na końcu liczb ujemnych ustawienie znaku minusa w odpowiednim miejscu jest bardzo łatwe

Problem z minusami można też naprawić za pomocą funkcji błyskawicznego wypełniania. Jeśli zakres zawiera także wartości dodatnie, może być konieczne podanie kilku przykładów.

Lista kontrolna czynności do wykonania przy porządkowaniu danych

Poniżej znajduje się lista kontrolna czynności, jakie należy wykonać przy porządkowaniu danych. Oczywiście nie każdy punkt ma zastosowanie do wszystkich możliwych sytuacji.

Czy kolumny mają unikatowe i opisowe nagłówki? Czy kolumny danych mają jednolite formatowanie? Czy nie ma duplikatów ani brakujących wierszy? Czy w danych tekstowych wielkości liter wszędzie są takie, jak powinny? Czy dane zawierają jakieś niedrukowalne znaki? Czy dane nie zawierają literówek? Czy dane nie zawierają niepotrzebnych odstępów? Czy kolumny są we właściwej kolejności? Czy nie ma pustych komórek, które nie powinny być puste? Czy znaki minusa znajdują się zawsze przed liczbą? Czy szerokość kolumn jest wystarczająca do wyświetlenia danych?

Eksportowanie danych

Początek rozdziału zawiera opis metod importu danych do Excela, więc na zakończenie nie widzę innego wyjścia, jak opisać techniki eksportu danych z tego programu do niestandardowych dla niego formatów plików.

Eksportowanie danych do pliku tekstowego

Gdy klikniesz polecenie Plik/Zapisz jako, zostanie wyświetlone okno dialogowe, w którym można wybrać kilka tekstowych formatów plików:

CSV - plik wartości rozdzielanych przecinkami, TXT - plik wartości oddzielanych tabulatorami, PRN - tekst sformatowany.

Poniżej znajduje się zwięzły opis każdego z tych formatów.

Format CSV

W pliku CSV dane są zapisywane tak, jak je widać. Inaczej mówiąc: jeśli w komórce znajduje się liczba 12,8312344 w formacie wyświetlającym tylko dwa miejsca po przecinku, to w pliku zostanie zapisana tylko część 12,83.

Komórki są oddzielane przecinkami, a wiersze - znakiem powrotu karetki i nowego wiersza.

 

Uwaga

W komputerach Macintosh wiersze są oddzielane tylko znakiem powrotu karetki (bez nowego wiersza).

Jeśli komórka zawiera przecinek, jej wartość jest zapisywana w cudzysłowie. Jeśli komórka zawiera cudzysłów, znak ten jest podwajany.

Jeżeli arkusz zawiera znaki Unicode, to należy go eksportować przy użyciu wersji Unicode. W przeciwnym razie znaki należące tylko do tego zestawu zostaną zapisane jako znaki zapytania.

Format TXT

Eksportowanie danych do pliku TXT odbywa się prawie identycznie jak do pliku CSV. Jedyna różnica polega na tym, że komórki są oddzielane tabulatorami zamiast przecinkami.

Format PRN

Plik w formacie PRN przypomina wydrukowany obraz arkusza kalkulacyjnego. Komórki są oddzielane wieloma spacjami. Dodatkowo maksymalna długość wiersza wynosi 240 znaków. Jeśli jakiś wiersz jest dłuższy, to zostaje zawinięty. Pliki w formacie PRN są rzadko używane.

Inne formaty eksportu

W Excelu istnieje możliwość zapisania danych jeszcze w kilku innych formatach:

DIF (format wymiany danych) - są to pliki z rozszerzeniem .dif. Nie są one zbyt często używane. Sylk (łącze symboliczne) - są to pliki z rozszerzeniem .slk. Nie są one zbyt często używane. Plik PDF - są to pliki z rozszerzeniem .pdf. Jest to bardzo popularny format plików tylko do odczytu. Dokument XPS - są to pliki z rozszerzeniem .xps. Jest to mało popularna alternatywa Microsoftu dla plików PDF. Strona sieci Web - są to pliki z rozszerzeniem .html. Wraz z właściwym plikiem w tym formacie dodatkowo często tworzony jest katalog zawierający różne pliki pomocnicze pozwalające prawidłowo wyświetlać stronę internetową. Arkusz kalkulacyjny OpenDocument - są to pliki z rozszerzeniem .ods rozpoznawane przez różne otwarte arkusze kalkulacyjne.