Perkongsian kemahiran Excel praktikal: Bagaimana untuk mengekstrak nombor?

Dalam artikel sebelumnya "Berkongsi kemahiran Excel praktikal: 7 cara untuk meningkatkan kecekapan melihat jadual", kami mempelajari tentang 7 petua untuk meningkatkan kecekapan melihat meja. Hari ini kita akan bercakap tentang mengekstrak nombor dalam Excel dan memperkenalkan beberapa situasi pengekstrakan biasa untuk dijelaskan.

Gunakan formula untuk mengekstrak nombor dalam sel Anda perlu mencari corak tertentu berdasarkan struktur data khusus untuk mereka bentuk formula yang sepadan. Sudah tentu, terdapat juga apa yang dipanggil formula pengekstrakan sejagat, tetapi ia sangat rumit dan memerlukan banyak pengiraan, jadi ia hanya dijelaskan secara ringkas pada akhir artikel.

Kategori 1: Nombor di sebelah kiri

Contoh 1: Nombor di sebelah kiri, dan nombor itu Nombor itu tetap.

Dalam contoh ini, data adalah sangat teratur, dan nombor semuanya dalam tiga digit di sebelah kiri Untuk mengekstrak nombor pelajar secara berasingan, hanya gunakan fungsi kiri : =LEFT(A2,3)

Formulanya juga sangat mudah Parameter pertama ialah sel untuk mengekstrak nombor, dan parameter kedua ialah bilangan digit untuk diekstrak (daripada Mengira dari kiri). Untuk jenis data dalam Contoh 1 ini, cuma ubah suai parameter kedua mengikut panjang nombor semasa menggunakannya.

Contoh 2: Nombor berada di sebelah kiri, bilangan digit tidak tetap, tetapi terdapat pemisah yang jelas.

Jika panjang nombor bukan tiga digit tetap, kaedah sebelumnya tidak akan berfungsi, seperti yang ditunjukkan di bawah. Pada masa ini, anda perlu mencari peraturan sumber data dan kemudian menggunakan peraturan untuk beroperasi. Data semasa secara seragam mempunyai tanda "-" Anda boleh menentukan panjang nombor berdasarkan kedudukan tanda "-", dan kemudian gunakan kiri untuk mengekstraknya.

Dalam kes ini, fungsi lain perlu digunakan untuk membantu, iaitu fungsi cari.

Gunakan formula =FIND("-",A2,1) untuk menentukan kedudukan "-".

Fungsi find mempunyai tiga parameter Parameter pertama ialah kandungan yang hendak dicari, parameter kedua ialah tempat mencarinya, dan parameter ketiga adalah daripada perkataan Mula mencari (membilang dari kiri).

=FIND("-",A2,1) bermaksud mencari "-" dalam sel A2, bermula dari awal (yang pertama di sebelah kiri). Hasil formula ialah nombor, yang mewakili kedudukan "-" dalam sel (karakter mana Pada masa ini, panjang nombor yang perlu kita ekstrak adalah hasil cari tolak 1, jadi kaedahnya). : =LEFT(A2,FIND("-",A2,1)-1)

Kaedah ini mempunyai pelbagai aplikasi asalkan anda boleh menemui simbol pemisahan yang jelas (yang boleh berupa simbol, aksara Cina, huruf, dll. ), anda boleh menggunakan kaedah ini.

Contoh 3: Nombor di sebelah kiri, bilangan digit tidak tetap dan tiada pemisah yang jelas.

Jika data seperti ini, maka kaedah sebelumnya tidak boleh digunakan Panjang nombor tidak pasti dan tidak ada pemisah Satu-satunya peraturan ialah data hanya mengandungi nombor dan aksara Cina (tanpa huruf dan simbol lain).

Dalam Excel, terdapat dua jenis aksara separuh lebar (nombor, huruf dan simbol yang dimasukkan dalam mod bahasa Inggeris) mempunyai panjang 1, manakala aksara lebar penuh (aksara Cina dan simbol Cina, dsb.) mempunyai panjang 2. Anda boleh memahami perkara ini melalui contoh:

Fungsi len ialah fungsi yang secara khusus mengira panjang kandungan sel Ia tidak membezakan antara lebar penuh dan separuh lebar , dan panjangnya selaras dengan pemahaman biasa kami , bersamaan dengan "bilangan" aksara. Mari kita lihat perbezaan antara lebar penuh dan separuh lebar:

Fungsi lenb berfungsi sama seperti fungsi len daripada kandungan, separuh lebar dan lebar penuh dibezakan Daripada keputusan, anda boleh Lihat perbezaan yang jelas. Panjang teks yang dikira berdasarkan lebar penuh adalah dua kali ganda lebar penuh dan separuh lebar tanpa membezakan, manakala nombor dan huruf adalah sama dalam kedua-dua kes.

Untuk kes Contoh 3, anda perlu menggunakan len dan lenb untuk menentukan panjang nombor, dan kemudian gunakan left untuk mengekstraknya.

Gunakan formula =LEN(A2)*2-LENB(A2) untuk menentukan panjang nombor, seperti yang ditunjukkan dalam rajah di bawah.

Mengapa len*2-lenb mendapat panjang nombor?

Menurut hasil perbandingan dalam dua kes sebelumnya, panjang statistik lenb, panjang teks adalah dua kali nilai statistik len, dan panjang nombor konsisten dengan nilai statistik len, jadi formula len*2 -lenb boleh dikira Panjang nombor.

Selepas mengira panjang nombor, penyelesaiannya adalah jelas: =LEFT(A2,LEN(A2)*2-LENB(A2))

Contoh 3 juga merupakan situasi biasa, cuma ingat bahawa ia adalah len* 2-lenb sudah memadai Jika anda benar-benar tidak ingat, anda boleh mengiranya secara berasingan apabila anda menghadapi masalah Mula-mula mengira panjang nombor, dan kemudian gabungkan ke kiri.

Contoh 4: Nombor berada di sebelah kiri dan mengandungi aksara separuh lebar seperti huruf.

Situasi ini sangat rumit, satu-satunya peraturan ialah nombor itu di sebelah kiri. Mari lihat bagaimana untuk menyelesaikannya.

boleh guna formula: =-LOOKUP(1,-LEFT(A2,ROW(:))).

Mengenai formula ini, ia akan mengambil masa 5,000 patah perkataan untuk dijelaskan, jadi anda hanya perlu mengingati rutin yang boleh diubah suai ialah 9 pada penghujungnya. Apabila nombor terpanjang melebihi 9 digit, 9 mesti diubah suai Ini boleh dilakukan secara langsung dengan 99.

Akhir sekali, formula ini berfungsi untuk sebarang situasi di mana nombor berada di sebelah kiri .

Situasi jenis kedua: nombor berada di sebelah kanan

Situasi di mana nombor berada di sebelah kanan sangat serupa dengan nombor di sebelah kiri. Berikut hanyalah penjelasan ringkas:

Jika bilangan digit ditetapkan, gunakan kanan untuk mengekstrak terus; bilangan digit tidak tetap tetapi terdapat pemisah, gunakan fungsi cari dan fungsi pertengahan untuk melengkapkan pengekstrakan:

=MID(A2,FIND("-",A2,1) 1,9)(

Nota:

Nilai parameter terakhir "9" dalam formula perlu diubah suai mengikut bilangan digit maksimum Contohnya, nombor yang diekstrak mempunyai yang terbesar Jika bilangan digit ialah 10, ia perlu ditulis sebagai 10 atau a. nombor lebih besar daripada 10.)

Jika bilangan digit tidak tetap dan tiada pemisah, anda masih boleh menggunakan gabungan len dan lenb:

=RIGHT(A2,LEN(A2)*2-LENB(A2))Teruskan biarkan carian menghantar pergerakan besar tanpa sebarang corak:

=-LOOKUP(1,-RIGHT(A2,ROW(:)))

Jenis situasi ketiga: Nombor di tengah

Nombor di tengah adalah sangat istimewa Situasi ini biasanya tidak berlaku dalam sumber data yang lebih standard Mari kita berikan contoh:

<.>Sebagai contoh, untuk data seperti dalam gambar, jika panjang sebelum atau selepas nombor ditetapkan, anda boleh menggunakan fungsi untuk menukar kandungan ke dalam situasi yang dibincangkan sebelum ini dan kemudian memprosesnya:

Formulanya agak mudah, jadi saya tidak akan menerangkannya bagaimana untuk melakukan langkah seterusnya, anda sudah tahu berdasarkan kajian terdahulu, bukan?

Kaedah yang dinyatakan di bawah ialah kaedah yang lebih umum Sesetengah orang memanggilnya formula pengekstrakan universal:

Array. formula, anda perlu menekan tiga kekunci "=-LOOKUP(0,-MID(A1,MIN(FIND(ROW(:)-1,A1&1/17)),ROW(:)))Ctrl shift + Enter

" untuk menamatkan.

Ringkasan

Mengenai cara menggunakan formula untuk mengekstrak nombor dalam sel, perkara di atas telah menerangkan kebanyakan situasi, tetapi sudah tentu bukan semua , sebagai contoh, apabila ia mengandungi perpuluhan, ia lebih istimewa kerana titik perpuluhan.

Formula tatasusunan terakhir boleh dianggap sebagai formula yang lebih umum. Perlu ditekankan bahawa kebanyakan masalah menjadi sangat menyusahkan kerana sumber data yang tidak standard Oleh itu, bangunkan tabiat yang baik untuk menyimpan hanya satu atribut data dalam sel, seperti nama dan kuantiti, Harga dan maklumat lain disimpan secara berasingan, yang akan meningkatkan kecekapan statistik.

Sudah tentu, dari perspektif meningkatkan keupayaan menggunakan formula, masalah seperti pengekstrakan nombor juga merupakan soalan yang baik untuk latihan fungsi. Saya harap anda boleh menggabungkan fungsi yang telah anda pelajari sebelum ini dan memikirkan lebih banyak formula untuk mengekstrak nombor.

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Perkongsian kemahiran Excel praktikal: Bagaimana untuk mengekstrak nombor?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!