Excel

Membahagi nombor dari unit ukuran

Split Numbers From Units Measure

Rumus Excel: Membahagi nombor dari unit ukuranFormula generik
= MAX ( ISNUMBER ( VALUE ( MID (A1,{1,2,3,4,5,6,7,8,9},1)))*{1,2,3,4,5,6,7,8,9})+1
Ringkasan

Untuk memisahkan nombor dari nilai unit, anda boleh menggunakan formula berdasarkan beberapa fungsi: MAX , ISNUMBER , NILAI , dan MID . Dalam contoh yang ditunjukkan, formula dalam C5 adalah:



 
= MAX ( ISNUMBER ( VALUE ( MID (B5,{1,2,3,4,5,6,7,8,9},1)))*{1,2,3,4,5,6,7,8,9})+1

Catatan: ini adalah formula eksperimen yang menggunakan kod keras pemalar larik , tetapkan di sini untuk rujukan dan komen. Diuji secara santai sahaja, jadi berhati-hati jika anda menggunakan atau menyesuaikan diri.

Penjelasan

Kadang kala anda menemui data yang mencampurkan unit secara langsung dengan nombor (iaitu 8km, 12v, 7.5hrs). Malangnya, Excel akan menganggap nombor dalam format ini sebagai teks, dan anda tidak akan dapat melakukan operasi matematik pada nilai tersebut.





Untuk memisahkan nombor dari nilai unit, anda perlu menentukan kedudukan nombor terakhir . Sekiranya anda menambah 1 pada kedudukan itu, anda mempunyai permulaan teks unit. Formula ini menggunakan konsep ini untuk mengetahui di mana unit ukuran bermula.

jumlah formula excel jika lebih besar daripada 0

Dalam contoh yang ditunjukkan, formula dalam C5 adalah:



 
= MAX ( ISNUMBER ( VALUE ( MID (B5,{1,2,3,4,5,6,7,8,9},1)))*{1,2,3,4,5,6,7,8,9})+1

Formula ini menggunakan Fungsi MID untuk mengekstrak 9 nilai pertama dalam B5, satu watak pada satu masa. Hasilnya adalah susunan seperti ini:

 
{'8','0','v','','','','','',''}

Kami kemudian menggunakan fungsi NILAI untuk menukar nombor dalam format teks menjadi nombor sebenar. Hasilnya adalah:

cara menyusun mengikut warna sel di excel
 
{8,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

Kami menjalankan array ini melalui ISNUMBER untuk mendapatkan:

 
{TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

Kemudian gandakan kali ganda array lain dengan 9 nombor untuk mendapatkan:

 
{1,2,0,0,0,0,0,0,0}

Kemudian kami menggunakan MAX untuk mendapatkan nilai terbesar, iaitu kedudukan 'nombor terakhir'.

Akhirnya, kita menambah 1 pada kedudukan untuk mendapatkan kedudukan 'unit start'.

Akhirnya, kami menggunakan kedudukan ini dengan fungsi KIRI dan KANAN standard untuk memisahkan nombor dari unit:

cemerlang mencari padanan dalam dua lajur
 
= VALUE ( LEFT (B5,C5-1)) // number = TRIM ( RIGHT (B5, LEN (B5)-C5+1)) // unit

Perhatikan bahawa pemalar array nombor yang dikodkan keras adalah peretasan untuk kemudahan, dan hanya akan menangani nilai mentah hingga 9 aksara.

Pengarang Dave Bruns


^