Konwertuj liczby jako tekst na normalne liczby

Jeśli format tekstu został ustawiony dla dowolnych komórek w arkuszu (może to zrobić użytkownik lub program podczas przesyłania danych do Excela), to liczby wprowadzone później do tych komórek Excel zaczyna traktować jako tekst. Czasami takie komórki są oznaczone zielonym wskaźnikiem, który najprawdopodobniej widziałeś:

A czasami taki wskaźnik się nie pojawia (co jest znacznie gorsze).

Ogólnie rzecz biorąc, pojawienie się liczb jako tekstu w twoich danych zwykle prowadzi do wielu bardzo niefortunnych konsekwencji:

• sortowanie przestaje działać normalnie – „pseudonumery” są wyciśnięte i nie są ułożone w oczekiwanym porządku:

  
  

• funkcje typu WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO) nie znajdź wymaganych wartości, ponieważ dla nich liczba i ta sama liczba jako tekst są różne:

  
  

• podczas filtrowania pseudoliczby są wybierane błędnie
  
• wiele innych funkcji Excela również przestaje działać poprawnie:
• itd.

Szczególnie zabawne jest to, że naturalna chęć zmiany formatu komórki na numeryczny nie pomaga. Tych. dosłownie zaznaczasz komórki, klikasz na nie prawym przyciskiem myszy, wybierasz Format komórki (Formatuj komórki), zmień format na Liczbowy (numer), ściskać OK — i nic się nie dzieje! W ogóle!

Być może „to nie jest błąd, ale funkcja”, oczywiście, ale to nam nie ułatwia. Przyjrzyjmy się więc kilku sposobom naprawienia sytuacji – jeden z nich na pewno Ci pomoże.

Metoda 1. Zielony róg wskaźnika

Jeśli zobaczysz zielony róg wskaźnika na komórce z liczbą w formacie tekstowym, uważaj się za szczęściarza. Możesz po prostu zaznaczyć wszystkie komórki z danymi i kliknąć wyskakującą żółtą ikonę z wykrzyknikiem, a następnie wybrać polecenie Konwertuj na liczbę (Konwertuj na numer):

Wszystkie liczby w wybranym zakresie zostaną przekonwertowane na liczby pełne.

Jeśli w ogóle nie ma zielonych narożników, sprawdź, czy są one wyłączone w ustawieniach programu Excel (Plik – Opcje – Wzory – Liczby sformatowane jako tekst lub poprzedzone apostrofem).

Metoda 2: Ponowne wejście

Jeśli nie ma wielu komórek, możesz zmienić ich format na numeryczny, a następnie ponownie wprowadzić dane, aby zmiana formatu zaczęła obowiązywać. Najłatwiej to zrobić, stojąc na celi i naciskając kolejno klawisze F2 (wejdź w tryb edycji, komórka zacznie migać kursorem), a następnie Wchodzę. Również zamiast F2 możesz po prostu dwukrotnie kliknąć komórkę lewym przyciskiem myszy.

Nie trzeba dodawać, że jeśli jest dużo komórek, ta metoda oczywiście nie zadziała.

Metoda 3. Formuła

Możesz szybko przekonwertować pseudoliczby na normalne, jeśli obok danych utworzysz dodatkową kolumnę z podstawową formułą:

Podwójny minus w tym przypadku oznacza w rzeczywistości dwukrotne pomnożenie przez -1. Minus o minus da plus i wartość w komórce się nie zmieni, ale sam fakt wykonania operacji matematycznej przełącza format danych na potrzebny nam numeryczny.

Oczywiście, zamiast mnożenia przez 1, można zastosować dowolną inną nieszkodliwą operację matematyczną: dzielenie przez 1 lub dodawanie i odejmowanie zera. Efekt będzie taki sam.

Metoda 4: Wklej specjalnie

Ta metoda była używana w starszych wersjach programu Excel, gdy pod stół poszli nowocześni skuteczni menedżerowie  w zasadzie nie było jeszcze zielonego rogu wskaźnika (pojawił się dopiero w 2003 r.). Algorytm jest następujący:

• wpisz 1 w dowolnej pustej komórce
• skopiuj to
• zaznacz komórki z liczbami w formacie tekstowym i zmień ich format na numeryczny (nic się nie stanie)
• kliknij prawym przyciskiem myszy komórki z pseudo-numerami i wybierz polecenie Wklej specjalnie (Wklej specjalnie) lub użyj skrótu klawiaturowego Ctrl + Alt + V
• w oknie, które się otworzy, wybierz opcję Wartości (Wartości) и Mnożyć (Zwielokrotniać)

W rzeczywistości robimy to samo, co w poprzedniej metodzie – mnożąc zawartość komórek przez jeden – ale nie za pomocą formuł, a bezpośrednio z bufora.

Metoda 5. Tekst według kolumn

Jeśli pseudoliczby, które mają zostać przekonwertowane, są również zapisywane z nieprawidłowymi separatorami dziesiętnymi lub tysiącami, można zastosować inne podejście. Wybierz zakres źródłowy z danymi i kliknij przycisk Tekst według kolumn (Tekst do kolumn) Zakładka Dane (Data). W rzeczywistości to narzędzie jest przeznaczone do dzielenia przyklejonego tekstu na kolumny, ale w tym przypadku używamy go w innym celu.

Pomiń pierwsze dwa kroki, klikając przycisk Następna (Kolejny), a na trzecim użyj przycisku do tego (Zaawansowane). Otworzy się okno dialogowe, w którym możesz ustawić znaki separatora aktualnie dostępne w naszym tekście:

Po kliknięciu koniec Excel przekonwertuje nasz tekst na normalne liczby.

Metoda 6. Makro

Jeśli musisz często wykonywać takie przekształcenia, warto zautomatyzować ten proces za pomocą prostego makra. Naciśnij Alt+F11 lub otwórz kartę wywoływacz (Deweloper) I kliknij Visual Basic. W wyświetlonym oknie edytora dodaj nowy moduł za pomocą menu Wstaw – moduł i skopiuj tam następujący kod:

Sub Convert_Text_to_Numbers() Selection.NumberFormat = "Ogólne" Selection.Value = Selection.Value End Sub  

Teraz po wybraniu zakresu zawsze możesz otworzyć zakładkę Deweloper – Makra (Deweloper — Makra), wybierz nasze makro z listy, naciśnij przycisk run (Biegać) – i natychmiast zamienia pseudoliczby na pełnowartościowe.

Możesz również dodać to makro do osobistej książki makr, aby później użyć go w dowolnym pliku.

PS

Ta sama historia dzieje się z datami. Niektóre daty mogą być również rozpoznawane przez program Excel jako tekst, więc grupowanie i sortowanie nie będzie działać. Rozwiązania są takie same jak dla liczb, tylko format należy zastąpić datą-godziną zamiast liczbową.

• Dzielenie przyklejonego tekstu na kolumny
• Obliczenia bez formuł przez specjalne wklejanie
• Konwertuj tekst na liczby za pomocą dodatku PLEX