Zapisz się na newsletter i odbierz DARMOWY EBOOK: 10 najprzydatniejszych porad excelowych

niedziela, 26 stycznia 2014

Zamiana tylko drugiej kropki w liczbie na przecinek, a usunięcie pierwszej

Jeden z czytelników napotyka w swojej codziennej pracy z Excelem następujący problem:

W firmie, w której pracuję, pracuję głównie na Excelu. Często zdarza się, że przeklejam dane z plików PDF, w których liczba w tysiącach wyraża się następująco 1.234.23 Nie mam pojęcia jak zamienić pierwszą kropkę na przecinek, a drugą pozostawić lub też sformatować tę liczbę kasując tylko jedną (pierwszą od lewej) kropkę tak, by liczba wyglądała tak 1234.23. Jest to dla mnie bardzo istotne ponieważ pracuję na dużych tabelach i bardzo utrudnia mi to liczenie!

W tym artykule odpowiem na to pytanie i dodatkowo zasugeruję inny wynik końcowy - moim zdaniem bardziej użyteczny.

Mamy do czynienia z liczbami w następującej formie (kolumna A):

Zamiana liczb z plików PDF na liczby zrozumiałe dla Excela - formatka

Są to liczby wyglądające tak, jak zaciągnięte z pliku PDF, o których pisze Czytelnik, czyli:
  • separator tysięcy jest kropką,
  • separator dziesiętny też jest kropką.
Opiszę teraz:

1. w jaki sposób pozbyć się pierwszej kropki i dodatkowo...
2. w jaki sposób zamienić powstałą liczbę (separator dziesiętny to kropka) w liczbę zrozumiałą dla Excela (można na niej wykonywać obliczenia).

Szczególnie polecam wykonać drugi krok, ponieważ wtedy Excel zrozumie powstałą wartość jak liczbę. Dzięki temu będzie można wykonywać na niej obliczenia. Warto jednak zdać sobie sprawę, że zaprezentowany przeze mnie sposób na usunięcie separatora tysięcy (pierwszej kropki), dotyczy tylko liczb mniejszych od miliona. Milion bowiem ma już tych separatorów dwa (1.000.000 ->1 000 000).

A więc...

1. Aby usunąć pierwszą kropkę (separator tysięcy) należy w komórkę B2 wpisać następującą formułę:
=ZASTĄP(A2;SZUKAJ.TEKST(".";A2);1;"")
i skopiować ja do pozostałych komórek w kolumnie B.

2. Aby zamienić drugą kropkę (separator dziesiętny) na przecinek, należy w komórkę C2 wpisać następującą formułę:
=JEŻELI.BŁĄD(ZASTĄP(B2;SZUKAJ.TEKST(".";B2);1;",");B2)*1
i skopiować ją do pozostałych komórek w kolumnie C. Formuła ta pozwoli dodatkowo wyeliminować błąd #ARG! w przypadku, gdy nie ma drugiej kropki - separatora dziesiętnego.

3. Ostatnim krokiem jest zmiana formatowania na takie, aby liczbę można było łatwo odczytać. Ja wstawiłam dodatkową kolumnę w moim przykładzie (kolumna Liczba sformatowana), aby pokazać tylko wynik formatowania. Polecam format Liczbowy, z separatorem tysięcy i dwoma miejscami po przecinku:

Zamiana liczb z plików PDF na liczby zrozumiałe dla Excela - formatowanie liczbowe

Oto wynik:

Zamiana liczb z plików PDF na liczby zrozumiałe dla Excela - wynik

Oczywiście, mając już liczbę tylko z jedną kropką - separatorem dziesiętnym - można również zamienić ją na przecinek w inny sposób. Należy:

1. Zaznaczyć liczby w zakresie B2:B6
2. Skopiować je
3. Ustawić się w komórce B2
4. Wkleić skopiowane liczby specjalnie jako wartości (przeczytasz o tym tutaj)
5. Za pomocą narzędzia Znajdowanie i zamienianie zamienić kropkę na przecinek (przeczytasz o tym tutaj lub - jeśli chcesz sprawę rozwiązać makrem - tutaj)

Gotowe!



Related Posts Plugin for WordPress, Blogger...

6 komentarzy:

  1. Zaproponowałbym inne rozwiązanie: usunąć wszystkie kropki przez Ctrl-H i potem wynik podizelić przez 100 oddzielną formułą.

    OdpowiedzUsuń
    Odpowiedzi
    1. Łukaszu, a co w przypadku jeżeli któraś z liczb nie ma części setnych, tylko np. dziesiętną 123.234.5 ?

      Usuń
  2. Super artykuł, bardzo pomocny :) Mam jednak jeszcze jeden problem - w moich danych występują spacje. Nie jestem w stanie usunąć ich za pomocą funkcji "zastąp". Masz pomysł jak sobie z tym poradzić? Z góry wielkie dzięki :)

    OdpowiedzUsuń
    Odpowiedzi
    1. Szafran - cieszę się, że artykuł Ci się przydał. Jeśli chodzi o pozbycie się spacji może spróbuj tak:
      =ZASTĄP(A2;SZUKAJ.TEKST(" ";A2);1;"")
      Ta formuła usunie spację. O to chodziło?

      Usuń
  3. Bardzo przydatne. Wielkie dzięki. Będę korzystał.

    OdpowiedzUsuń