Cara menyusun nombor dan teks bercampur, nombor bertingkat di Excel

Tutorial menerangkan masalah biasa dengan memerintahkan rentetan alfanumerik dalam Excel dan menyediakan penyelesaian kerja untuk menyusun nombor sebagai teks dan teks sebagai nombor.

Sorting teks dan nombor secara berasingan dalam Excel semudah ABC atau 123 :) Tetapi mengatur semula ID produk, SKU dan nilai-nilai lain yang mempunyai kedua-dua huruf dan digit di dalamnya boleh menyebabkan masalah. Jenis abjad Excel tidak dapat membezakan bahagian angka rentetan untuk mengendalikannya secara berasingan. Jika anda ingin mempunyai lebih banyak kawalan ke atas bagaimana sengatan alfanumerik disusun, lihat melalui contoh -contoh di bawah.

• Nombor menyusun sebagai teks
• Susun nombor dan teks campuran
• Susun tali mengikut teks dan nombor sekaligus
• Susun nombor bertingkat / hierarki
• Susun nombor bertingkat yang diawali dengan teks

Susun nombor sebagai teks dalam Excel

Apabila menyusun lajur nombor dari terkecil hingga terbesar di Excel, bukannya mempunyai ini:

1, 2, 3, 11, 12, 13, 110, 120, 130

Anda kadang -kadang mendapat sesuatu seperti ini:

1, 11, 110, 12, 120, 13, 130, 2, 3

Itu berlaku apabila nombor sebenarnya teks dan mereka menyusun sebagai teks seperti kata -kata yang disusun berdasarkan huruf mereka, "angka teks" disusun berdasarkan digit mereka dan bukannya nilai -nilai mereka.

Jika lajur sasaran mengandungi nombor yang diformat sebagai teks, semua yang diperlukan untuk nombor untuk disusun secara normal adalah untuk menukar teks ke nombor. Hasilnya adalah dalam lajur C dalam tangkapan skrin di bawah.

Sebaliknya, jika anda ingin menyusun nombor sebagai teks , tukar nombor ke teks terlebih dahulu, dan kemudian klik tab Laman Utama > Kumpulan Pengeditan > Sort & Filter > Sort A - Z.

Hasilnya akan menjadi seperti dalam lajur A dalam tangkapan skrin sebelumnya.

Cara menyusun nombor campuran dan teks dalam excel

Dengan ciri jenis Excel terbina dalam, rentetan alfanumerik (teks dan nombor gabungan) sentiasa disusun sebagai teks, iaitu huruf demi huruf, digit-oleh-digit. Untuk menyusun lajur nombor yang mengandungi awalan huruf atau akhiran sebagai nombor , lakukan langkah -langkah di bawah. Dan inilah hasil yang kita cuba capai.

Untuk mengelakkan Excel daripada mengendalikan nombor dalam rentetan alfanumerik sebagai teks, kami akan mengekstrak nombor tersebut dalam lajur pembantu, dan disusun mengikut lajur itu. Ini akan membolehkan anda menyimpan data asal tidak berubah tetapi menyusun semula cara yang anda suka.

1. Dalam lajur di sebelah nilai asal, masukkan formula yang mengekstrak nombor dari rentetan.

   Dalam contoh ini, kita hanya boleh mengekstrak semua watak selepas tanda hubung ("-"). Dalam Excel 365, ini boleh dilakukan menggunakan fungsi TextAfter. Seperti mana -mana fungsi teks lain, outputnya sentiasa teks, tanpa mengira sama ada anda mengambil huruf atau digit, jadi kami membiak hasilnya dengan 1 untuk menukar rentetan teks ke nombor.

   =TEXTAFTER(A2, "-")*1

   Dalam versi Excel yang lebih lama, hasil yang sama dapat dicapai dengan menggunakan gabungan tiga fungsi yang berbeza yang mengekstrak teks selepas watak tertentu. Di samping itu, operasi *1 dilakukan untuk menukar output teks ke nombor:

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

   

   Petua. Untuk cepat mengekstrak nombor dari mana -mana kedudukan dalam sel, anda boleh menggunakan alat Ekstrak yang disertakan dengan Suite Ultimate kami untuk Excel.

   

2. Susun data asal dengan nombor yang diekstrak. Dengan nombor yang dipilih, pergi ke tab Rumah > Sort & Filter > Sort terkecil hingga terbesar . Dalam kotak dialog yang muncul, pilih Expen the Opstion Opstion dan klik Sort . Untuk maklumat lanjut, lihat cara menyusun dan menyimpan baris bersama.

   

   Sekarang rentetan yang mengandungi huruf dan digit disusun sebagai nombor, dan anda boleh mengeluarkan atau menyembunyikan lajur pembantu jika perlu.

   

Susun tali mengikut teks dan nombor pada satu masa

Sekiranya rentetan sumber anda mempunyai beberapa elemen yang berbeza, anda boleh mengekstrak setiap elemen dalam lajur yang berasingan, dan kemudian disusun mengikut pelbagai lajur. Akibatnya, rentetan akan diatur seperti dalam lajur D dalam tangkapan skrin di bawah:

Memandangkan semua rentetan dalam dataset kami mempunyai corak yang sama, cara paling mudah untuk memisahkan teks dan nombor ke dalam lajur yang berbeza adalah dengan menggunakan ungkapan biasa. Ini boleh dilakukan dengan bantuan fungsi tersuai bernama RegexPExtract. Pertama, anda menambah kodnya ke Excel anda seperti yang dijelaskan dalam tutorial ini. Selepas itu, gunakan formula berikut.

Untuk mengekstrak kejadian pertama teks dari sel A2, formula dalam B2 adalah:

=RegExpExtract(A2, "[^\d] ", 1)

Untuk mengekstrak nombor pertama, formula dalam C2 adalah:

=RegExpExtract(A2, "\d ", 1)*1

Untuk mengekstrak nombor kedua, formula dalam D2 adalah:

=RegExpExtract(A2, "\d ", 2)*1

Dalam formula 2 ^ND dan 3 ^RD , kami membiak output regexpextract dengan 1 untuk menjadikan substring yang diekstrak ke dalam nombor.

Pengguna suite muktamad kami dapat memanfaatkan alat regex untuk mencapai hasil yang sama. Mereka hanya perlu melakukan satu lagi operasi cepat untuk menukar rentetan angka yang diekstrak (lajur C dan D) ke nombor:

Sebaik sahaja teks dan nombor dibahagikan, susunkan dataset anda dengan pelbagai lajur:

1. Pilih semua lajur (A2: D16 dalam kes kami).
2. Pada tab Data , dalam kumpulan Susun & Penapis , klik butang Susun .
3. Tambah tahap jenis yang anda perlukan dan pilih pesanan jenis yang dikehendaki.
4. Apabila selesai, klik OK .

Dalam kes kami, julat yang dipilih terlebih dahulu disusun mengikut teks dari A hingga Z, kemudian dengan nombor 1 , dan kemudian dengan nombor 2 dari terkecil hingga terbesar:

Hasilnya adalah apa yang kita cari:

Petua. Untuk mengatur semula rentetan secara berbeza, tukar urutan tahap dalam kotak dialog jenis .

Cara menyusun nombor bertingkat / hierarki di Excel

Dalam Microsoft Excel, nombor bertingkat seperti 1.1, 1.1.1, 1.1.2 adalah rentetan, dan mereka menyusun sebagai teks, bukan nombor:

Untuk menyusun rentetan bertingkat sebagai nombor, anda boleh menggunakan fungsi yang ditentukan pengguna tersuai yang mempunyai sintaks berikut:

Hierarchynumber (Range, del, max_level)

Di mana:

• Julat adalah julat untuk disusun.
• Del adalah pembatas yang digunakan untuk memisahkan tahap, biasanya satu titik (.).
• Max_level adalah bilangan maksimum tahap dalam nombor hierarki.

Dan inilah kod fungsi:

Fungsi tersuai untuk menyusun nombor hierarki

Fungsi hierarchynumber (julat sebagai julat, del sebagai rentetan , max_level sebagai integer ) sebagai bahagian dua dimalit sebagai variasi bahagian = split (range.value, del) untuk indeks = 0 ke ubound (bahagian ) hierarchynumber = hierarchynumber bahagian (indeks) * (10 ^

Apa fungsi yang dihasilkan adalah menjana nombor yang membolehkan menyusun rentetan angka bertingkat. Lebih tepat lagi, ia memecahkan rentetan hierarki ke dalam nombor individu (bahagian), memproses setiap bahagian berdasarkan algoritma di bawah, dan menambah hasilnya:

part(Index) * (10 ^ ((level - Index) * 2))

Sebagai contoh, untuk 1.1, fungsi menghasilkan hasil ini:

1*(10^((2-0)*2)) 1*(10^((2-1)*2)) = 10100

Untuk 1.2, itu:

1*(10^((2-0)*2)) 2*(10^((2-1)*2)) = 10200

Cara menyusun nombor hierarki menggunakan fungsi tersuai:

1. Masukkan kod fungsi hierarchynumber ke dalam modul kod standard buku kerja anda dan simpannya sebagai buku kerja yang dibolehkan makro (.xlsm). Arahan terperinci ada di sini.
2. Dalam sel kosong di sebelah rentetan hierarki pertama, masukkan formula hierarchynumber dan tekan kekunci Enter .
3. Seret formula ke sebilangan besar baris yang diperlukan.
4. Susun dataset anda berdasarkan lajur formula.

Katakan anda mempunyai rentetan angka bertingkat dengan maksimum 3 tahap dalam lajur A. Formula untuk B2 adalah:

=HierarchyNumber(A2, ".", 3)

Selepas menyalin formula ke bawah, anda akan mendapat hasil ini:

Selepas itu, anda menyusun keseluruhan dataset (A2: B19) dengan lajur formula dari terkecil hingga terbesar:

Dan dapatkan rentetan angka pelbagai peringkat disusun sebagai nombor:

Susun nombor bertingkat yang diawali dengan teks

Contoh ini membincangkan kes yang sedikit berbeza di mana nombor bertingkat diawali dengan beberapa teks, katakan "Tahap 1.1.1". Pada pandangan pertama, tugas itu lebih rumit, tetapi sebenarnya penyelesaiannya lebih mudah :)

Pertama, anda mengekstrak substring angka bertingkat ke dalam lajur yang berasingan. Untuk ini, anda boleh menggunakan alat Ekstrak yang disertakan dengan Suite Ultimate kami:

Atau anda boleh menulis formula untuk menarik semua aksara selepas ruang:

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

Dan kemudian, lakukan penyortiran tersuai. Dalam kotak dialog Susun Excel, tambahkan dua tahap - pertama dengan nombor hierarki yang diekstrak, dan kemudian dengan rentetan asal - dan klik OK:

Amaran berikut akan muncul, di mana anda memilih untuk " menyusun nombor dan nombor yang disimpan sebagai teks secara berasingan ".

Akibatnya, rentetan yang mengandungi nombor pelbagai peringkat disusun mengikut nombor:

Itulah cara menyusun rentetan angka dan nombor pelbagai peringkat dalam Excel. Terima kasih kerana membaca dan berjumpa dengan anda di blog kami minggu depan!

Muat turun yang ada

Susun nombor campuran dan teks dalam Excel - Contoh (.xlsm Fail) Versi Percubaan Ultimate Suite Fungsional (.exe File)

Atas ialah kandungan terperinci Cara menyusun nombor dan teks bercampur, nombor bertingkat di Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!