Dlaczego Twoja formuła Excela nie oblicza?

W tym artykule, dowiemy się Dlaczego Twoja formuła Excela nie oblicza?

Scenariusz:

Wielokrotnie, gdy wyodrębniamy liczby z łańcucha, są one tekstem według właściwości. Nie można na nich wykonywać żadnych operacji na liczbach. Wtedy pojawia się potrzeba przekonwertowania tego tekstu na liczbę. Tak więc, aby przekonwertować dowolny tekst na liczbę możemy zastosować różne podejścia w zależności od sytuacji. Przyjrzyjmy się tym podejściom…

Użyj funkcji WARTOŚĆ aby przekonwertować tekst na liczbę

Jeśli pobrałeś dane z jakiejś strony internetowej, często zdarza się, że otrzymujesz pewne liczby jako tekst w formacie specjalnych dat. Po prostu zawiń ten tekst w funkcję VALUE. Zwróci ona czystą liczbę.

Składnia funkcji WARTOŚĆ

=WARTOŚĆ(tekst)

Więc aby zamienić tekst w komórce B2 na liczbę napisz formułę WARTOŚĆ.

=VALUE(B2)

Dodaj 0 do tekstu, aby przekonwertować tekst na liczbę

Jeśli dodasz dowolną liczbę za pomocą operatora + do liczby w formacie tekstowym, tekst zostanie przekonwertowany na liczbę, a następnie dodana zostanie podana liczba do przekonwertowanej liczby. Na koniec wynik będzie liczbą.

Na przykład, jeśli w B4 znajduje się liczba sformatowana tekstowo, to wystarczy dodać 0, aby przekonwertować ciąg znaków na liczbę.

=B4+0

Wynikiem będzie na pewno liczba.

Jak już mówiłem na początku, problem ten występuje gdy wyodrębniamy liczby z ciągów znaków. Więc jeśli chcesz, aby wyodrębniony tekst był numerowany po prostu dodaj 0 na końcu.

Na przykład wyodrębniam kod miasta z tekstu B6. Wyodrębniłem go używając funkcji LEFT. Ale nie jest to liczba, więc dodałem 0 w samej formule.

=LEFT(B6,6)+0

Używanie powiadomień Excela do zamiany tekstu na liczbę

Gdy liczba ma postać tekstu, Excel powiadamia Cię o tym, pokazując zielony róg komórki.

Kiedy klikniesz na komórkę, pojawi się mała ikonka wykrzyknika w lewym rogu komórki.

Kiedy klikniesz na nią, pojawi się opcja Konwertuj na liczbę. Kliknij na nią, a tekst zostanie przekonwertowany na liczbę.

Konwersja tekstu na liczbę za pomocą VBA
Jeśli masz stały zakres, który chcesz przekonwertować na tekst, użyj tego fragmentu VBA.

Sub ConvertTextToNumber() Range("A3:A8").NumberFormat = "General" Range("A3:A8").Value = Range("A3:A8").ValueEnd Sub

Gdy uruchomisz powyższy kod, przekonwertuje on tekst z zakresu A3:A8 na tekst.
Ten kod może wyglądać bardziej elegancko, jeśli napiszemy go w ten sposób.

Sub ConvertTextToNumber() With Range("A3:A8") .NumberFormat = "General" .Value = .Value End WithEnd Sub

Jak to działa?

Cóż, jest to dość proste. Najpierw zmieniamy format liczbowy zakresu na Ogólny. Następnie umieszczamy wartość tego zakresu w tym samym zakresie za pomocą VBA. W ten sposób całkowicie usuwamy formatowanie tekstu. Proste, prawda?

Zmiana formatowania liczby zakresu dynamicznego

W powyższym kodzie zmieniliśmy tekst na liczbę w powyższym kodzie zakresu stałego, ale w większości przypadków tak nie będzie. Aby zamienić tekst na liczbę w dynamicznych zakresach, możemy ocenić ostatnio używaną komórkę lub wybrać zakres dynamicznie.

Tak by to wyglądało:

Sub ConvertTextToNumber()With Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row).NumberFormat = "General" .Value = .ValueEnd With

W tym miejscu wiem, że zakres zaczyna się od A3. Ale nie wiem, gdzie może się kończyć.

Więc dynamicznie identyfikuję ostatni używany wiersz Excela, który ma w nim dane, używając snippetu VBA Cells(Rows.Count, 1).End(xlUp).Row. Zwraca on numer ostatnio używanego wiersza, który konkatenujemy z „A3:A”.

Uwaga: Ten snippet VBA będzie działał na aktywnym skoroszycie i aktywnym arkuszu. Jeśli twój kod przełącza się przez wiele arkuszy, lepiej będzie ustawić obiekt zakresu zamierzonego arkusza. Tak jak tutaj

Sub ConvertTextToNumber() Set Rng = ThisWorkbook.Sheets("Sheet1").Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row) With Rng .NumberFormat = "General" .Value = .Value End WithEnd Sub

Powyższy kod zawsze zmieni tekst na numer arkusza1 skoroszytu, który zawiera ten kod.

Pętla i CSng do zmiany tekstu na liczbę

Inną metodą jest zapętlenie każdej komórki i zmiana wartości komórki na liczbę za pomocą funkcji CSng. Oto kod.

Sub ConvertTextToNumberLoop() Set Rng = ThisWorkbook.Sheets("Sheet1").Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row) For Each cel In Rng.Cells cel.Value = CSng(cel.Value) Next celEnd Sub

W powyższym snippecie VBA używamy pętli VBA For do iteracji nad każdą komórką w zakresie i konwersji wartości każdej komórki na liczbę za pomocą funkcji CSng w VBA.

Tak więc, oto jak można zmienić tekst na liczbę w Excelu za pomocą VBA. Możesz użyć tych snippetów, aby przygotować swój arkusz, zanim wykonasz na nim jakąkolwiek operację numeryczną. Mam nadzieję, że wyjaśniłem wystarczająco dużo. Możesz pobrać plik roboczy tutaj.

Zmiana tekstu na liczbę przy użyciu VBA.

Mam nadzieję, że ten artykuł o tym, dlaczego Twoja formuła Excela nie oblicza, jest wystarczająco zrozumiały. Znajdź więcej artykułów na temat obliczania wartości i powiązanych formuł Excela tutaj. Jeśli podobały Ci się nasze blogi, podziel się nim z przyjaciółmi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy poprawić, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na [email protected].

Powiązane artykuły :

Jak uzyskać tekst & Liczba w odwróconym poprzez VBA w Microsoft Excel : Aby odwrócić liczbę i tekst używamy pętli i funkcji mid w VBA. 1234 zostanie przekonwertowane na 4321, „ty” zostanie przekonwertowane na „uoy”. Oto snippet.

Formatowanie danych za pomocą niestandardowych formatów liczbowych przy użyciu VBA w Microsoft Excel : Aby zmienić format liczbowy określonych kolumn w Excelu, użyj tego snippetu VBA. Konwertuje on format liczb z określonego na określony format za pomocą jednego kliknięcia.

Popularne artykuły :

Jak używać funkcji JEŻELI w Excelu : Instrukcja JEŻELI w Excelu sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDZIWY lub zwraca inną określoną wartość, jeśli FAŁSZ.

Jak korzystać z funkcji VLOOKUP w Excelu: Jest to jedna z najbardziej używanych i popularnych funkcji Excela, która jest używana do wyszukiwania wartości z różnych zakresów i arkuszy.

Jak korzystać z funkcji SUMIF w Excelu: Jest to kolejna funkcja niezbędna na tablicy rozdzielczej. Pomaga ona w sumowaniu wartości na określonych warunkach.

Jak korzystać z funkcji COUNTIF w Excelu: Licz wartości z warunkami przy użyciu tej niesamowitej funkcji. Nie musisz filtrować swoich danych, aby policzyć konkretne wartości. Funkcja Countif jest niezbędna do przygotowania tablicy rozdzielczej.