Dalam sesetengah kes, anda mesti memisahkan sel dalam Excel.Ini mungkin anda mendapat data daripada pangkalan data, atau menyalin data daripada Internet atau mendapatkan data daripada rakan sekerja.
Contoh mudah di mana anda perlu membelah sel dalam Excel ialah apabila anda mempunyai nama penuh dan ingin membahagikannya kepada nama pertama dan nama keluarga.
Atau anda mendapat alamat' dan anda ingin membahagikan alamat supaya anda boleh menganalisis bandar atau kata laluan secara berasingan.
Bagaimana untuk Memisahkan Sel dalam Excel
Dalam tutorial ini, anda akan belajar cara membelah sel dalam Excel menggunakan teknik berikut:
- Gunakan ciri Teks ke Lajur.
- Gunakan fungsi teks Excel.
- Gunakan Flash Fill (tersedia pada 2013 dan 2016).
Mari mulakan!
Pisahkan sel dalam Excel dengan teks ke lajur
Di bawah saya telah menyenaraikan nama beberapa watak fiksyen kegemaran saya yang ingin saya pisahkan kepada sel yang berasingan. :
Berikut ialah langkah-langkah untuk memisahkan nama ini kepada nama pertama dan nama keluarga:
- Pilih sel yang mengandungi teks yang anda mahu belah (dalam kes ini, A2:A7).
- Klik tab Data
- Dalam kumpulan Alat Data, klik Teks ke Lajur.
- Dalam wizard Tukar Teks kepada Lajur:
Ini akan segera memisahkan teks sel kepada dua lajur berbeza.
nota:
- Ciri Teks ke Lajur membahagikan kandungan sel berdasarkan pembatas.Walaupun ini berfungsi dengan baik jika anda ingin memisahkan nama pertama dan nama akhir, dalam kes nama pertama, tengah dan akhir ia akan membahagikannya kepada tiga bahagian.
- Hasil yang diperoleh menggunakan fungsi Text to Column adalah statik.Ini bermakna jika terdapat sebarang perubahan dalam data asal, anda perlu mengulangi proses tersebut untuk mendapatkan hasil yang dikemas kini.
Pisahkan sel dalam Excel menggunakan fungsi teks
Fungsi teks Excel berguna apabila anda ingin memotong dan memotong rentetan teks.
Walaupun fungsi Teks ke Lajur memberikan hasil statik, hasil yang diperoleh menggunakan fungsi adalah dinamik dan dikemas kini secara automatik semasa anda menukar data asal.
Pisahkan nama dengan nama pertama dan nama keluarga
Katakan anda mempunyai data yang sama seperti ini:
nama ekstrak
Untuk mendapatkan nama pertama daripada senarai ini, gunakan formula berikut:
=LEFT(A2,SEARCH(" ",A2)-1)
Formula ini akan mencari aksara ruang pertama dan kemudian mengembalikan semua teks sebelum aksara ruang itu:
Formula ini menggunakan fungsi SEARCH untuk mendapatkan kedudukan aksara ruang.Dalam contoh Bruce Wayne, watak angkasa lepas berada di tempat ke-6.Kemudian, ia menggunakan fungsi LEFT untuk mengekstrak semua aksara di sebelah kirinya.
Ekstrak nama keluarga
Begitu juga, untuk mendapatkan nama keluarga, gunakan formula berikut:
=KANAN(A2,LEN(A2)-CARI(" ",A2))
Formula ini menggunakan fungsi SEARCH untuk mencari lokasi bar ruang menggunakan fungsi SEARCH.Ia kemudian menolak nombor ini daripada jumlah panjang nama (diberikan oleh fungsi LEN).Ini memberikan bilangan aksara dalam nama keluarga.
Kemudian gunakan fungsi RIGHT untuk mengekstrak nama akhir ini.
Nota:Fungsi ini mungkin tidak berfungsi dengan betul jika terdapat ruang hadapan, mengekor atau berganda dalam nama.Klik di sini untuk mengetahui cara mengalih keluar ruang hadapan/belakang/berganda dalam Excel.
Pisahkan nama dengan nama pertama, tengah dan akhir
Dalam sesetengah kes, anda mungkin mendapat gabungan nama, sesetengah daripadanya juga mempunyai nama tengah.
Formula dalam kes ini agak rumit.
nama ekstrak
Untuk mendapatkan nama:
=LEFT(A2,SEARCH(" ",A2)-1)
Ini adalah formula yang sama yang kami gunakan tanpa nama tengah.Ia hanya mencari watak ruang pertama dan mengembalikan semua aksara sebelum ruang.
ekstrak nama tengah
Untuk mendapatkan nama tengah:
=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-CARI(" ",A2)),"")
Fungsi MID bermula dengan aksara ruang pertama dan menggunakan perbezaan dalam kedudukan aksara ruang pertama dan kedua untuk mengekstrak nama tengah.
Jika tiada nama tengah, fungsi MID mengembalikan ralat.Untuk mengelakkan ralat, ia dibungkus dalam fungsi IFERROR.
Ekstrak nama keluarga
Untuk mendapatkan nama keluarga, gunakan formula berikut:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2) -SEARCH(" ",A2,SEARCH(" ",A2)+1)))
Formula ini menyemak nama tengah (dengan mengira bilangan aksara ruang).Jika terdapat hanya 1 aksara ruang, ia hanya mengembalikan semua teks ke sebelah kanan aksara ruang.
Tetapi jika terdapat 2, maka ia mencari aksara ruang kedua dan mengembalikan bilangan aksara selepas ruang kedua.
Nota: Formula ini berfungsi dengan baik apabila anda hanya mempunyai nama pertama dan akhir, atau nama pertama, tengah dan akhir.Walau bagaimanapun, jika anda mempunyai gabungan akhiran atau sapaan, maka anda perlu mengubah suai formula tersebut dengan lebih lanjut.
Pisahkan sel dalam Excel dengan QuickFill
Flash Fill ialah ciri baharu yang diperkenalkan dalam Excel 2013.
Ia boleh menjadi sangat berguna apabila anda mempunyai corak dan ingin mengekstrak bahagiannya dengan cepat.
Sebagai contoh, mari dapatkan data nama pertama dan keluarga:
Isi Pantas berfungsi dengan mengenali corak dan menyalinnya ke semua sel lain.
Berikut ialah cara menggunakan Isian Kilat untuk mengekstrak nama daripada senarai:
Bagaimanakah Flash Fill berfungsi?
Flash Fill mencari corak dalam set data dan menyalinnya.
Isian Flash ialah ciri yang sangat pintar yang berfungsi dengan baik dalam kebanyakan kes.Tetapi ia juga gagal dalam beberapa kes.
Sebagai contoh, jika saya mempunyai senarai nama dengan gabungan nama, sesetengahnya mempunyai nama tengah dan yang lain tidak.
Jika saya mengekstrak nama tengah dalam kes ini, Flash Fill akan salah mengembalikan nama akhir sekiranya tiada nama pertama.
Sejujurnya, ini masih merupakan anggaran yang baik untuk arah aliran.Namun, ini bukan yang saya mahukan.
Tetapi ia masih merupakan alat yang cukup baik untuk disimpan dalam senjata anda dan digunakan apabila diperlukan.
Berikut ialah satu lagi contoh pengisian cepat yang berfungsi dengan baik.
Saya mempunyai satu set alamat yang saya ingin keluarkan bandar dengan cepat.
Untuk mendapatkan bandar dengan cepat, masukkan nama bandar alamat pertama (dalam contoh ini, London dalam sel B2) dan gunakan autolengkap untuk mengisi semua sel.Sekarang gunakan Flash Fill dan ia akan memberikan anda nama bandar untuk setiap alamat dengan serta-merta.
Begitu juga, anda boleh memisahkan alamat dan mengekstrak mana-mana bahagian alamat.
Ambil perhatian bahawa ini memerlukan alamat menjadi set data homogen dengan pembatas yang sama (koma dalam kes ini).
Jika anda cuba menggunakan Flash Fill tanpa corak, ia akan menunjukkan ralat seperti ini:
Dalam tutorial ini, saya merangkumi tiga cara berbeza untuk memisahkan sel dalam Excel kepada berbilang lajur (menggunakan Teks ke Lajur, Formula dan Isi Pantas)
Saya harap anda mendapati tutorial Excel ini berguna.
