Jak oczyścić zdobyte dane. Jak zmienić format na liczbowy, usunąć nazwy walut i inne śmieci.

I co? Policzysz to? Tak, ale jak próbowałem wkleić to się rozsypało i poproszę by przysłali w innym formacie. Albo siądę i to przeklikam, bo mam wolną godzinkę. Albo niech praktykant to zrobi…

Zdarza się, że kopiujesz skądś dane by później je mnożyć lub dodawać. Ale to co wklejasz czy zaciągasz z innego pliku czy strony internetowej nie chce się dodawać i pluje błędami.

Tu jest plik roboczy ze wszystkimi przykładami. Jeśli chcesz go edytować – zrób sobie kopię (Plik –> Utwórz kopię).

Czy arkusz na pewno widzi liczbę? Czy tylko znaki?

Często powodem jest zły format. Coś co wyświetla się jako 10, może nie być wcale czytane jako dziesiątka, a jako ciąg znaków 1 i 0. Coś czego się nie da pomnożyć czy dodać. Najczęściej można to poznać po wyrównaniu tekstu. Domyślnie Arkusze Google wyrównują tekst do lewej a liczby do prawej.

Jeśli mamy dane z różnych źródeł i nie jesteśmy pewni co jest czym, możemy wprowadzić komórki sprawdzające czy w komórce jest liczba. Funkcja =ISNUMBER(A1) daje wartość PRAWDA, gdy w komórce A1 jest liczba i FAŁSZ gdy trafia na coś innego.

Możemy też zrobić szybki test i w wolną komórkę wpisać =A1+1. Jeśli to nie jest liczba, arkusz wypluje błąd.

Jak zamienić ciąg znaków (string) w liczbę lub liczbę w ciąg znaków?

Służy do tego funkcja VALUE. Wystarczy, że w nowej komórce wpiszemy =value(B6) i uzyskamy wartość liczbową

Najczęściej funkcja VALUE jest zagnieżdżana z innymi funkcjami obrabiającymi ciągi znaków. Zobaczycie to w następnych sekcjach.

Natomiast jeśli sprawa dotyczy jednorazowej operacji, wystarczy wybrać z menu Formatuj —> Liczba —> Liczba, i jeśli tylko zawartość komórki lub zakresu nadaje się do przerobienia, to można to zrobić w ten sposób.

Czasem potrzebna jest operacja w drugą stronę. Mamy w ręce format liczbowy, a potrzebujemy użyć ciągu. Do tego służy funkcja TO_TEXT

=TO_TEXT(A1) – oznacza, że Arkusze przestaną widzieć wartość, a zobaczą ciąg znaków. To się czasem przydaje np. w czasie wyszukiwania w tabelach. Gdy jedna tabela jest sformatowana tekstowo, a druga liczbowo. Jeśli użyjemy funkcji VLOOKUP, a ona trafi na dwa różne typy danych – nie rozpozna ich.

Zmianę formatu na tekstowy możemy też zrobić poprzez menu: Formatuj —> Liczba —> Zwykły tekst.

Co zrobić jak wkradły się separatory tysięcy i 1 000 nie jest już czytany jako liczba? Co zrobić z obcymi formatami i zamianą kropek i przecinków?

To się zdarza często jak wklejamy obce dane. Jeśli robimy to jednokrotnie – pół biedy, możemy to zrobić przez Znajdź / Zamień

Znajdź i zamień znajdziecie w menu –> Edycja. Ale szybciej się wywołuje skrót klawiszowy Ctrl + H.

Gorzej jeśli dane są pobierane z innego arkusza, strony internetowej, pliku .csv na którego format nie mamy wpływu. Wówczas musimy użyć funkcji SUBSTITUTE (PODSTAW)

SUBSTITUTE(tekst,czego szukamy,na co zamieniamy, [ile razy - opcjonalne])

• tekst – tu sprawa oczywista, możemy wstawić tu tekst w cudzysłowie, ale częściej podajemy komórkę.
• Czego szukamy – np. przecinka – więc wpisujemy “,”
• Na co zamieniamy – możemy tu wstawić nic “” – wówczas przecinki po prostu znikną. Możemy wstawić np. kropki: “.”, jeśli tak potrzebujemy
• ile razy – możemy umówić się, że interesuje nas wywalenie tylko pierwszego napotkanego przecinka. Wówczas pozostałe zostaną nieruszone.

To bardzo użyteczna funkcja i warto pamiętać o niej. Usuwamy więc niepotrzebną spację, a do tego dorzucamy funkcję value, żeby otrzymany wynik był czytany jako liczba.

Zdarza się, że znaków do usunięcia jest więcej – zarówno kropki jak i przecinki. Wówczas SUBSTITUTE trzeba zagnieździć 2 krotnie i na koniec znów dodać funkcję VALUE:

Jak usunąć nazwy walut wklejane do komórki razem z wartością?

Tutaj działamy identycznie jak w poprzedniej sekcji – za pomocą SUBSTITUTE (PODSTAW) zamieniamy daną wartość (np. ” USD”) na pusty zakres.

Tutaj nawet skróciliśmy zapis. Po średniku nie ma “”, jest od razu nawias, ale arkusz się domyśla, że chodzi o pusty zares.

Jak odseparować liczbę, gdy w komórce jest więcej treści niż potrzeba?

Tu jest trochę trudniej niż z walutami czy przecinkami, bo ta dodatkowa treść może być zróżnicowana (np. uwagi, przypisy, etc.). Jeśli wszystkie liczby mają tyle samo znaków (np. 3 cyfrowe kody i dodatki jak w modelach mercedesa), wówczas wystarczy, że wyciągniemy pierwsze 3 znaki. przy pomocy funkcji LEFT (LEWY)

Składnia: LEFT(tekst;ile znaków)

• Tekst to komórka z której pobierzemy dane.
• Ile znaków – ile znaków od lewej strony chcemy wziąć.

Jeśli liczby są różnej długości, ale następujący po nich ciąg zaczyna się zawsze tak samo (np. od nawiasu, myślnika, kropki, etc.) to musimy złożyć dwie operacje. Najpierw określić na której pozycji w ciągu jest ten np. nawias, a potem wziąć odpowiednią ilość znaków na lewo od niego.

Do określenia gdzie jest ten nawias używamy funkcji FIND (ZNAJDŹ)

FIND(co znaleźć; gdzie szukać; [od którego miejsca zacząć - opcjonalne])

• Co znaleźć – jeśli chodzi o pojedynczy znak czy ciąg – umieszczamy go w cudzysłowie. Np “(“
• Gdzie szukać – oczywiście tu podajemy adres komórki
• od którego miejsca zacząć – czy mamy startować od pierwszego znaku czy dalej (jeśli nic nie wpiszemy funkcja będzie szukać od początku)

W przykładzie poniżej zapis FIND(“(“;B23) podaje, na której pozycji jest nawias. A po odjęciu 1 Funkcja LEFT pobierze wszystkie znaki na lewo od znalezionego nawiasu.

Jeśli znaki, które nas nie interesują wpadają w różne miejsca, mają różną długość i formę, musiemy uciec się do funkcji opartych na wyrażeniach regularnych. Potężnego narzędzia, które często używają programiści.

=REGEXEXTRACT(tekst; wyrażenie_regularne)

tekst – prosta sprawa. Chodzi o zawartość komórki B24

wyrażenie regularne – tu jest skomplikowana sprawa. Chodzi o specjalny sposób opisywania ciągów znaków i ich specyfiki. Np. kod pocztowy ma charakterystyczną formę 00-000 . “Po ludzku” powiemy: “2 cyfry, myślnik i 3 cyfry”. Przy pomocy wyrażenia regularnego: [0-9]{2}-[0-9]{3}. Więcej o budowaniu wyrażeń możecie przeczytać na tym blogu.

Tutaj wyodrębniamy liczby całkowite, więc wystarczy, że jako wyrażenia regularnego użyjemy [0-9]+. Gdyby zdarzały się liczby z miejscami po przecinku, należałoby wpisać [0-9]+,[0-9]+.

Jak podzielić komórkę, gdy wpada do niej kilka wartości (np. 1, 203, 100)

To zdarza się gdy wklejamy dane ze schowka, z innego programu. Często z tabel. Zdarza się też przy importach.

Naszym podstawowym narzędziem jest tu funkcja SPLIT. Jej składnia to:

=SPLIT(tekst; separator;[dziel wg każdego - opcjonalnie];[usuń pusty tekst - opcjonalnie]

• tekst – chodzi tu najczęściej o komórkę z danymi do podzielenia
• separator – jakiego znaku będziemy używać jako granicy między komórkami – wpisujemy go w cudzysłowie
• dziel wg każdego – możemy zechcieć dzielić np. co drugi przecinek jeśli wiemy, że to wszystko kwoty zł i groszy oddzielone przecinkami, a nie chcemy wywalać groszy do osobnych komórek.
• usuń pusty tekst – domyślnie ustawiony jako PRAWDA. Pozwala zdecydować, czy w momencie gdy mamy np. dwa przecinki obok siebie, czy ma powstawać pusta komórka (ustawienie jako fałsz przydaje się gdy odtwarzamy tabelę).

Jeśli z jakiegoś powodu mamy do czynienia z kilkoma rodzajami znaków, które chcemy użyć jako separatory komórek, powinniśmy najpierw ujednolicić znaki przy pomocy funkcji SUBSTITUTE, a potem dopiero użyć funkcji SPLIT.

Jeśli interesuje Cię łączenie komórek -poświęciłem temu problemowi osobny wpis.

Jak działać na cyfrach rzymskich w arkuszu kalkulacyjnym?

Na koniec problem rzadki, ale upierdliwy. Zwykle cyfry rzymskie wprowadza się by odróżnić je od innych parametrów – np. do opisywania numerów dostaw, które mają własne liczbowe identyfikatory. Albo do numerowania wydziałów. Zwykle są to liczby małe.

Jeśli mają pozostać w naszym arkuszu, bo np. taka jest nomenklatura, ale potrzebujemy dodawać liczby rzymskie, przydatna jest funkcja ARABIC (ARABSKIE)

Składnia

=ARABIC(liczba rzymska)

Czyli jeśli ktoś używa rzymskich nazw miesięcy i chce nagle przesunąć wszystko o 2 miesiące to nie może sobie tak po prostu do komórki VII dodać 2, ale może odwołać się do niej i zrobić funkcję =ARABIC(A1)+2, która doda dwójkę. Wynikiem będzie 9. A jak wynik ma być znowu podany liczbą rzymską (IX) to trzeba dodać zagrznieżdżenie =ROMAN(ARABIC(A1)+2).

Użyta funkcja ROMAN (RZYMSKIE) ma identyczną składnię – dodatkowo posiada opcje kilku rzymskich zapisów o których możecie poczytać w dokumentacji Arkuszy.