Każdy, kto choć przez chwilę używał Excela prędzej czy później trafi na następujący problem:
- Mam mnóstwo liczb w formacie tekstowym ("popsute")
- Chcę je "naprawić" tj. zamienić tekst na liczby
Jak to ugryźć?
Metoda #1: mróweczka
Jeżeli wywalić słowo "mnóstwo" z punktu pierwszego, wówczas wystarczy:
- Zaznaczyć wszystkie interesujące nas komórki
- Usunąć formatowanie (w angielskiej wersji Excela: Alt-E-A-F)
- Przejść do pierwszej komórki w zakresie
- Nacisnąć F2 - Enter
- Przejść do kolejnej komórki
- Nacisnąć F2 - Enter
- Powtarzać podpunkty 5 i 6 aż "naprawimy" wszystkie komórki
Powyższy sposób, choć prosty, ma jednak tę wadę, że nadaje się do zakresów pięcio-, góra piętnastokomórkowych. Przy większych zakresach danych przydałaby się coś bardziej uniwersalnego.
Metoda #2: mnożymy kolumnę przez 1
Jeżeli nasze "popsute" liczby siedzą w pojedynczej kolumnie, wówczas:
- Wstawiamy pustą kolumnę obok (na przykład po prawej stronie)
- Wpisujemy w tej pustej kolumnie formułę "=A2*1" (zamiast A2 wpisujemy adres pierwszej komórki na samej górze naszej kolumny)
- Kopiujemy formułę w dół do końca danych
- Kopiujemy całą kolumnę z nową formułą
- Wklejamy spcjalnie (tylko wartości) do oryginalnej kolumny
- Usuwamy kolumnę dodaną w punkcie 1
Proste, szybkie, wygodne. Jedna wada - nie zadziała dla wielu kolumn. Albo inaczej: zadziała, ale będziemy potrzebowali wstawić obok tyle pustych kolumn, ile mamy kolumn z "popsutymi" liczbami. Przy większej ilości kolumn trzeba uważać, no i trwa to odrobinę dłużej.
Metoda #3: mnożymy wszystko przez 1, bez dodatkowych kolumn
Trzecia metoda jest najbardziej uniwersalna, a jednocześnie najmniej znana. Wstyd przyznać, sam poznałem ją stosunkowo niedawno:
- W dowolnej pustej komórce arkusza wpisujemy jedynkę.
- Zaznaczamy komórkę z tą jedynką i kopiujemy do schowka (Ctrl-C)
- Zaznaczamy wszystkie "popsute" komórki
- Wklej specjalnie, zaznaczamy "Multiply" (w polskiej wersji to pewnie będzie "Przemnóż"), pukamy Enter.
Co się właśnie wydarzyło?
Przemnożyliśmy wszystkie komórki docelowe przez 1, a jednocześnie nie musieliśmy tworzyć żadnych dodatkowych kolumn.
Proste?
Ja mam często problem z danymi przeniesionymi do OpenOffice Calc z innego programu. Często data, np. 2018-07-26 ma z przodu jak gdyby apostrof, którego nie da się usunąć poprzez skopiowanie go i akcję znajdź i zamień. Zabawne, ale wtedy zbiorowa zamiana np. 2 na 2 pomaga i data staje się datą.
Czyli tak samo, jak w JS najprostrza zmiana wartości tekstowej na liczbową
var liczba=1*tekst– zwłaszcza przy pytaniu przez formularze o jakieś wartości, które docelowo mają być liczbą…To je informatyka, tego nie zrozumiesz 😉
A nie wystarczy skorzystać z formuły =wartość(tekst)
Oczywiście, ale to wymaga użycia tylu komórek z formułą, ile mamy „popsutych” wartości, analogicznie do tego, co opisuję w metodzie #2. A metoda #3 wymaga tylko jednej dodatkowej komórki z jedynką – bardziej oszczędnie się nie da 😉
ps. Popracował byś nad konfiguracją subskrypcji komentarzy, bo coś nie tak do końca.
Np. mail o nowym komentarzu:
„Pojawiła się nowa odpowiedź, byłeś nią zainteresowany w
Anuluj subskrypcję”
Zerknę w wolnej chwili. Nikt mi wcześniej problemów nie zgłaszał w tym temacie, ale zerknę.
Brakuje mi w arkuszach kalkulacyjnych pewnego drobiazgu i ten brak często wkurza. Może za dużo bym chciał, ale gdy wklejam dane z kolumną zawierającą nr PESEL i zapominam wcześniej uprzedzić Excel/OO, że to będzie pole tekstowe, to zastanawiam się, czy tylko mi to przeszkadza?
Programista być może mógłby jakoś ogarnąć tę sytuację. Jeżeli w pole bez formatu wklejam ciąg znaków o długości >1 złożony tylko z cyfr i jeżeli zaczyna się on zerem, to cała kolumna powinna być tekstowa. Zaraz jednak myślę, że nie znam 100 powodów (które zna programista), dla których nie byłoby to działanie słuszne.
No więc tak: zaznaczasz wszystkie komórki (Ctrl-A), otwierasz menu formatowania komórek (Ctrl-jedynka), klikasz „Tekst” i potem „OK”. Przy odrobinie wprawy całość zajmie Ci półtora sekundy.
Alternatywa: piszesz sobie kawałek kodu VBA, który przy wklejaniu formatuje każdą komórkę liczbową zaczynającą się od zera jako tekst, wrzucasz go do AddIns, aktywujesz i gotowe.
(przepis tutaj: http://www.ozgrid.com/VBA/excel-add-in-create.htm
Pewnie jest jeszcze pierdylion innych sposobów.
🙂 tylko, że najczęściej najpierw wklejam, potem myślę…
W Excelu, inaczej niż w życiu, zawsze można zrobić Ctrl-Z 😉
Czy znasz jakiś sposób aby filtrować pola wg ich formatu? Najbardziej idiotyczne, co może zrobić życzliwy użytkownik, a najczęściej to będzie (dlaczego?!?!?!) ktoś wyżej w hierarchii dziobania, więc nie możesz mu powiedzieć nawet, że jest $%&^%*^&#$%#%& i powinien @#$!@&*%$, to na liście kilku tysięcy rekordów w excelu zaznaczyć niektóre pola na żółto – zaznaczyłem ci interesujące mnie pola, zrób mi z nich zestawienie.
Czyż można o większe szczęście?
W Open Office, bo zwyczajowo piszę excel mając na myśli arkusz, mogę zarejestrować makro w JS, albo OO Basic, Python albo BeanShell, ale w żadnym z tych języków nie wybrnę z kłopotu, bo kiedyś VB, czasem PascalScript, ale bardzo skromnie…