Jak wyodrębnić liczbę z ciągu znaków w programie Excel
Biuro Microsoft Gabinet Microsoft Excel Bohater Przewyższać / / June 07, 2023
Opublikowany
Podczas importowania danych do programu Excel może być konieczne ich oddzielenie. Dowiedz się, jak wyodrębnić liczbę z ciągu w programie Excel, korzystając z tego przewodnika.
Excel to potężny arkusz kalkulacyjny, ale nie może zdziałać cudów. Jeśli importujesz dane, a te dane są bałaganem, program Excel nie będzie w stanie wiele z tym zrobić. Na przykład, jeśli masz wartości dołączone do innych znaków jako część łańcucha, nie będziesz mógł wykonywać na nich obliczeń, chyba że najpierw je wyodrębnisz.
Na szczęście można to zrobić dokładnie, chociaż może to być bardziej skomplikowane, niż mogłoby się wydawać. Oto jak wyodrębnić liczbę z ciągu w programie Excel.
Jak wyodrębnić liczbę z końca łańcucha za pomocą wypełniania błyskawicznego
Szybkim i łatwym sposobem wyodrębnienia liczby z końca łańcucha jest użycie programu Excel Błyskawiczne wypełnienie funkcja. Ta funkcja umożliwia szybkie wypełnienie zakresu komórek na podstawie wzorca wykrytego w jednej określonej komórce. Wadą tej metody jest to, że wymaga ona samodzielnego wykonania pierwszej ekstrakcji, a jeśli chcesz wyodrębnić tylko liczbę z pojedynczego ciągu, nie będzie to przydatne.
W większości przypadków wypełnianie błyskawiczne może poprawnie wyodrębnić liczbę z końca łańcucha, chociaż nie jest to nieomylne, dlatego zawsze należy dokładnie sprawdzać wyniki. Jeśli nie działa poprawnie, spróbuj ręcznie wypełnić pierwsze dwie lub trzy komórki zamiast tylko pierwszej.
Aby wyodrębnić liczbę z końca łańcucha za pomocą Flash Fill:
- Wybierz komórkę obok pierwszej komórki zawierającej ciągi znaków.
- Wprowadź liczbę, która pojawia się na końcu łańcucha.
- Jeśli wszystkie dane znajdują się w ciągłej kolumnie bez przerw, wybierz komórkę bezpośrednio pod komórką, w której właśnie wpisałeś liczbę.
- Jeśli dane zawierają luki w niektórych wierszach, zaznacz pierwszą komórkę i wszystkie komórki poniżej, z których chcesz wyodrębnić liczby, w tym puste wiersze.
- Naciskać Ctrl+E w systemie Windows lub Cmd+E na komputerze Mac, aby uruchomić funkcję Flash Fill.
- Pozostałe komórki zostaną automatycznie wypełnione na podstawie wzorca wykrytego przez program Excel. W większości przypadków liczby z końca łańcucha zostaną wyodrębnione poprawnie.
Jak wyodrębnić liczbę z końca łańcucha za pomocą formuły
Wadą metody Flash Fill jest to, że wymaga ona samodzielnego wyodrębnienia liczb z ciągu dla pierwszego wiersza. Możliwe jest wyodrębnienie liczby z końca łańcucha za pomocą formuły, chociaż formuła jest dość złożona.
Aby wyodrębnić liczbę z końca łańcucha za pomocą formuły:
- Kliknij komórkę, w której ma się pojawić wyodrębniona liczba.
- Wprowadź następującą formułę, zastępując „B2” komórką zawierającą Twój ciąg:
=WARTOŚĆ(PRAWY(B2;DŁUG(B2)-MAKS(JEŻELI(CZY.LICZBA(ŚRODEK(B2;WIERSZ(POŚR("1:"&DŁUG(B2))),1)*1)=FAŁSZ, WIERSZ(POŚR(" 1:"&DŁ(B2))),0))))
- Naciskać Wchodzić.
- Liczba powinna zostać wyodrębniona z ciągu znaków.
- Aby zastosować formułę do pozostałych komórek, kliknij uchwyt przeciągania w prawym dolnym rogu komórki.
- Przeciągnij w dół nad komórkami, w których chcesz powielić formułę.
- Zwolnij przycisk myszy, a formuła zostanie skopiowana do wybranych komórek.
- Komórki te powinny teraz zawierać wyodrębnione liczby.
- Każda komórka bez ciągu do wyodrębnienia zwróci błąd. Jeśli zamiast tego chcesz zwrócić pustą komórkę, możesz zamiast tego użyć formuły:
=JEŻELI.BŁĄD(WARTOŚĆ(PRAWY(B2;DŁUG(B2)-MAX(JEŻELI(CZY.LICZBA(ŚRODEK(B2;WIERSZ(POŚR("1:"&DŁUG(B2))),1)*1)=FAŁSZ, WIERSZ(POŚR). ("1:"&DŁ(B2))),0)))),"")
- Wszelkie puste komórki będą teraz zwracały puste miejsce.
Być może zastanawiasz się, co robi ta formuła. W skrócie, generuje tablicę o takiej samej długości jak łańcuch. Umieszcza każdy ze znaków z łańcucha w tej tablicy w kolejności. Następnie ocenia każdy z tych znaków, aby sprawdzić, czy są one liczbą, czy nie. Wszystkie znaki będące liczbami są konwertowane na zero. Wszystkie znaki, które nie są liczbami, są konwertowane na liczbę reprezentującą jej pozycję w ciągu.
Na przykład AB5HG23 przekonwertuje się na 1, 2, 0, 4, 5, 0, 0. Następnie szuka najwyższej wartości na tej liście, czyli pozycji ostatniego znaku, który nie jest liczbą. Na koniec zwraca wszystkie znaki po tym punkcie, które są liczbami na końcu łańcucha. Następnie funkcja WARTOŚĆ konwertuje to z powrotem na liczbę. Proste, prawda?
Jak wyodrębnić liczbę z początku ciągu za pomocą wypełniania błyskawicznego
Możesz także użyć wypełniania błyskawicznego, aby wyodrębnić liczbę z początku ciągu, wypełniając samodzielnie pierwszy przykład i pozwalając programowi Excel zatrzymać wzorzec.
Aby wyodrębnić liczbę z początku łańcucha za pomocą Flash Fill:
- Zaznacz komórkę po prawej stronie pierwszego ciągu zawierającego liczbę, którą chcesz wyodrębnić.
- Wprowadź liczbę, która pojawia się na początku ciągu.
- Jeśli wszystkie dane znajdują się w ciągłej kolumnie bez przerw, wybierz komórkę bezpośrednio poniżej.
- Jeśli dane zawierają luki, zaznacz pierwszą komórkę i wszystkie komórki poniżej, z których chcesz wyodrębnić liczby, w tym puste wiersze.
- Naciskać Ctrl+E w systemie Windows lub Cmd+E na komputerze Mac, aby rozpocząć Flash Fill.
- Wybrane komórki powinny teraz wyświetlać wyodrębnione liczby.
Jak wyodrębnić liczbę z początku ciągu za pomocą formuły
Możesz także użyć formuły, aby wyodrębnić liczbę z początku ciągu. Działa to w podobny sposób jak formuła z poprzedniej sekcji, ale znajduje pozycję pierwszego znaku nienumerycznego i zwraca wszystkie poprzedzające go znaki.
Aby wyodrębnić liczbę z początku łańcucha za pomocą formuły:
- Wybierz komórkę, w której ma się pojawić wyodrębniona liczba.
- Wprowadź następującą formułę, zastępując „B18” komórką zawierającą Twój ciąg:
=WARTOŚĆ(LEWA(B18,POZYCJA(FAŁSZ, CZY.LICZBA(ŚRODEK(B18,WIERSZ(POŚR("1:"&DŁ(B18)+1)),1)*1),0)-1))
- Naciskać Wchodzić i numer zostanie wyodrębniony.
- Możesz użyj autouzupełniania w Excelu, aby skopiować formułę do innych wymaganych komórek.
- Jeśli chcesz, aby dowolne puste wiersze zwracały pusty wynik, zamiast tego użyj następującej formuły:
=JEŻELI.BŁĄD(WARTOŚĆ(LEWA(B18,POZYCJA(FAŁSZ, CZY.LICZBA(ŚRODEK(B18,WIERSZ(POŚR("1:"&DŁ(B18)+1)),1)*1),0)-1))," ")
Jak wyodrębnić liczby z dowolnego miejsca w ciągu za pomocą wypełniania błyskawicznego
Jak wspomniano wcześniej, Flash Fill może nie działać, jeśli pierwsza komórka nie pasuje do formatu niektórych pozostałych komórek. W takim przypadku może być konieczne ręczne obliczenie dwóch lub trzech komórek, zanim funkcja Wypełnianie błyskawiczne da oczekiwany wynik.
Aby wyodrębnić liczby z dowolnego miejsca w ciągu za pomocą Flash Fill:
- W komórce obok pierwszego ciągu wpisz ręcznie liczby znajdujące się w ciągu.
- Jeśli wszystkie dane znajdują się w ciągłej kolumnie, wybierz komórkę bezpośrednio poniżej.
- Jeśli dane zawierają luki, zaznacz pierwszą komórkę i wszystkie komórki poniżej, z których chcesz wyodrębnić liczby, w tym puste wiersze.
- Naciskać Ctrl+E w systemie Windows lub Cmd+E na komputerze Mac, aby rozpocząć Flash Fill.
- Komórki powinny teraz wypełnić wyodrębnione liczby.
Jak wyodrębnić liczby z dowolnego miejsca w ciągu za pomocą formuły
Możesz użyć formuły, aby wyodrębnić wszystkie liczby z ciągu, bez względu na to, gdzie się pojawiają.
Aby wyodrębnić liczby z dowolnego miejsca w ciągu znaków za pomocą Formuły:
- Wybierz komórkę, w której ma się pojawić wyodrębniona liczba.
- Wprowadź następującą formułę, zastępując „B9” komórką zawierającą Twój ciąg:
=SUMA ILOCZYN(ŚRODEK(0&B9;DUŻY(INDEKS(CZY.LICZBA(--ŚRODEK(B9;WIESZ(POŚR("1:"&DŁUG(B9))),1))*WIER(POŚR("1:"&DŁUG(B9)) )),0),ROW(POŚR("1:"&DŁUG(B9))))+1,1)*10^ROW(POŚR("1:"&DŁUG(B9)))/10)
- Twoja formuła powinna wyodrębnić wszystkie liczby z ciągu.
- Jeśli chcesz, aby dowolne puste komórki zwracały pustą komórkę zamiast zera, użyj zamiast tego tej formuły:
=JEŻELI.BŁĄD(SUM.ILOCZYN(ŚRODEK(0&B9;DUŻY(INDEKS(CZY.LICZBA(--ŚRODEK(B9;WIERSZ(POŚREDNI("1:"&DŁUG(B9))),1))*ROW(POŚR("1:"&DŁUG() B9))),0),ROW(POŚR("1:"&DŁUG(B9))))+1,1)*10^ROW(POŚR("1:"&DŁUG(B9)))/10)," ")
- Możesz użyj autouzupełniania, aby zastosować formułę do innych komórek w arkuszu kalkulacyjnym.
- Wszelkie ciągi, które nie zawierają żadnych liczb, zwrócą zero. Możesz się tego nauczyć zamień zero na myślnik w Excelu jeśli chcesz to zmienić.
Rozwiń swoje umiejętności Excela
Nauczenie się, jak wyodrębnić liczbę z ciągu w programie Excel, może pomóc w szybkim wyodrębnieniu potrzebnych danych z listy ciągów. Chociaż wymagane formuły mogą być złożone, nie musisz dokładnie rozumieć, jak one działają; tylko, że to robią!
Istnieje więcej przydatnych sztuczek programu Excel, których możesz się nauczyć, aby przenieść swoje umiejętności na wyższy poziom. Możesz się uczyć jak liczyć komórki z tekstem w nich lub jak to zrobić obliczyć czyjś wiek na podstawie daty urodzenia. Możesz nawet stworzyć generator liczb losowych w Excelu.