Sortowanie alfanumeryczne w programie Excel

Czy w programie Excel można sortować alfanumeryczne od a-1, a-2, a-3 ... a-123 zamiast a-1, a-10, a-100, a-11?

Sortowanie od najstarszego do najnowszego lub przez AZ, zdecydowanie nie da mi pożądanego rezultatu. Próbowałem sformatować komórki jako liczbę, ale to nie pomogło.

Utknąłem.

Użytkownik @ fixer1234 ma rację, prawdopodobnie chcesz użyć funkcji ciągu. Oto jeden ze sposobów, aby to zrobić.

Krok 1

[Zaktualizowano]

W kolumnie „Liczby” podświetl zakres, a następnie podziel tekst przy łączniku: do ...

Data> Text to Columns> Delimited> Next> Other: ->Finish

Zauważ, że potrzebujesz łącznika ( - ) w polu Other:tekstowym. I upewnij się, że sąsiednia kolumna (po prawej) jest pusta, zanim to zrobisz, aby nie zastąpić ważnych danych.

Możesz również użyć tej funkcji, aby wyodrębnić liczbę z kolumny A:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2))

Krok 2

Teraz, jak możemy zobaczyć na powyższym zrzucie ekranu, zrobimy w następnym kroku, dodając zera na początku każdej liczby, która ma mniej cyfr niż największa liczba. Umożliwi to sortowanie tych liczb w pożądany sposób.

Ale najpierw musimy trochę ustalić fakty. Jeśli możesz łatwo powiedzieć, która jest największa liczba, po prostu policz cyfry w tej największej liczbie - będzie to liczba zer, których chcesz użyć w naszej następnej funkcji. Istnieje inny sposób ustalenia najdłuższej liczby bez konieczności ręcznego liczenia cyfr największej liczby.

[AKTUALIZACJA] Możesz użyć następującej funkcji (choć do tej pory nie wiemy dlaczego ), aby ustalić najdłuższą liczbę:

=MAX(INDEX(LEN(C2:C14),,1))

Alternatywnie możesz po prostu wpisać następującą formułę w komórce (widzisz to na powyższym obrazku jako komórkę podświetloną na pomarańczowo), ale zamiast po prostu ENTERnacisnąć klawisz, aby ustawić komórkę, naciśnij klawisz skrótu CTRL-SHIFT-ENTER. Spowoduje to zmianę charakteru funkcji, przekształcając ją w formułę tablicową , bez konieczności zabawy z funkcjami takimi jak INDEX().

=MAX(LEN(C2:C14))

(Upewnij się, że zakres jest dokładny dla konkretnego używanego arkusza kalkulacyjnego).

Po naciśnięciu CTRL-SHIFT-ENTERzawartość komórki zmieni się na tę, ale ręczne wpisanie nic nie zrobi:

{=MAX(LEN(C2:C14))}

Jakkolwiek chcesz to zrobić, jest w porządku. Wystarczy określić, ile cyfr stanowi największą liczbę na liście: „1” oczywiście ma 1 cyfrę, „10” ma 2 cyfry, „100” ma 3 cyfry i tak dalej.

Krok 3

Na koniec w kolumnie „Rozwiń” ta funkcja przekształci liczby z kolumny „Liczby” na tekst z liczbą poprzedzających zer, które określiłeś, że powinieneś użyć w kroku 2.

=TEXT(C2,"000")

Upewnij się, że wstawiasz cudzysłowy wokół zer.

Jeśli największa liczba ma 8 cyfr, wówczas twoja funkcja będzie wyglądać następująco:

=TEXT(C2,"00000000")

Prostym rozwiązaniem jest użycie funkcji łańcuchowych do umieszczenia wartości liczbowych w nowej kolumnie jako liczby. Następnie posortuj według tej kolumny. Nie określiłeś, w jaki sposób wartości mogą się różnić ani jak są ułożone, ale przykład:

Załóżmy, że przedrostek „a-” nigdy się nie zmienia, wpisy znajdują się w kolumnie A rozpoczynającej się w wierszu 2, a kolumna B jest dostępna. W B2 wstawisz coś w stylu: = wartość (mid (a2,3, len (a2) -2)), a następnie skopiuj formułę w razie potrzeby. Aby posortować, zaznacz obie kolumny i sortuj na B.

Jeśli mogą istnieć inne prefiksy, powiedzmy b-, c- itd., Podobnie podziel część tekstową na inną kolumnę. Następnie posortuj kolumnę tekstową jako pierwszą kolumnę sortującą, a kolumnę liczbową jako drugą.

Wszystko, co zaznaczysz, zostanie posortowane według kolumn sortowania.

Aby objaśnić funkcje ciągu, zacznij od funkcji mid, która wyodrębnia ciąg znaków z ciągu znaków. mid (a2,3, len (a2) -2) patrzy na tekst w a2, zaczyna się od trzeciego znaku (pierwsza cyfra przy założeniu, że wszystkie prefiksy to „a-”), a następnie przyjmuje liczbę znaków, która jest dwa mniej niż liczba w oryginalnym tekście (długość oryginalnego tekstu minus znaki „a-”). Funkcja wartości przekształca następnie wynik w liczbę zamiast ciągu znaków numerycznych.

Mam następujące dane:

[Student Name]  [Regn. No (AlphaNum)]    [Test Score]

Lisa            DD38                      90

Aletta          BC36                      85

Ava             AB40                      95

Sunny           BB34                      91

Sofia           CD36                      89

Johnny          CM07                      80

Chcę, aby uczniowie i oceny były ułożone według ich numerów rejestracyjnych, które są ciągami alfanumerycznymi. Końcowy wynik powinien wyglądać następująco:

[Student Name]  [Regn. No (AlphaNum)]    [Test Score]

Ava             AB40                      95

Sunny           BB34                      91

Aletta          BC36                      85

Sofia           CD36                      89

Johnny          CM07                      80

Lisa            DD38                      90

Jak:

Chociaż powyższa migawka jest oczywista, kroki są następujące:

1. Wyodrębnij część alfabetyczną z kolumny za pomocą =LEFT(B4, 2)formuły, gdzie „2” pokazuje liczbę alfabetów w ciągu, a moje ciągi zaczynają się B4.

2. Przypisz numer do tej części alfabetycznej, używając =COLUMN(INDIRECT(E4&1))

3. Wyodrębnij część numeryczną numeru rejestracyjnego, używając =RIGHT(B4,2)jako formuły. Tutaj „2” to nie. liczb w ciągu.

4. Połącz dwie części w 2 i 3

5. Przekształć je w liczby „czyste” za pomocą =VALUE(H4)

6. Skopiuj ostatnią kolumnę do oryginalnej tabeli i użyj opcji Wklej jako „Wartości”

   

7. Teraz posortuj według ostatniej kolumny i wybierz „Rozwiń zaznaczenie”

   

   

8. Gotowy!!!

   

Ważne: Pro-Tip -> Połącz kroki od 1 do 5 za pomocą =VALUE(CONCATENATE(COLUMN(INDIRECT(LEFT(B4,2)&1)),RIGHT(B4,2)))