Ada banyak keadaan di mana kami menerima informasi dengan banyak titik data di dalam satu sel. Ini sering terjadi ketika tujuan asli data sedikit berbeda dari cara kami menggunakannya. Dalam keadaan ini, kita sering perlu membagi sel menjadi bagian-bagian penyusunnya. Posting ini akan membahas pemecahan masalah ini dan mempelajari cara membagi sel di Excel
Skenario yang kami coba selesaikan adalah;
Jika kita memiliki sepuluh sel, itu bukan masalah besar; . Tetapi jika kita memiliki ratusan atau ribuan sel, kita perlu mencari cara lain. Syukurlah kita dapat beralih ke berbagai fitur Excel
Unduh file contoh
Saya sarankan Anda mengunduh file contoh untuk posting ini. Kemudian Anda akan dapat bekerja bersama dengan contoh dan melihat solusinya dalam tindakan, ditambah file tersebut akan berguna untuk referensi di masa mendatang
Unduh file. 0051 Pisahkan sel di Excel. xlsx
Tonton videonya
Tonton videonya di YouTube
Isi
Data contoh
Dalam semua solusi kami, kami akan menggunakan kumpulan data berikut
Dalam posting ini, kami membahas 4 opsi yang memungkinkan. Bergantung pada data Anda, satu metode dapat memberikan hasil yang lebih baik daripada yang lain. Semakin rendah tingkat konsistensi data, semakin sulit prosesnya
Pisahkan sel menggunakan Text to Columns Wizard
Teks ke kolom adalah fitur di Excel yang tersembunyi di depan mata. Itu ada di pita sebagai ikon, tapi kecuali Anda tahu itu ada dan apa fungsinya, Anda mungkin belum pernah menggunakannya sebelumnya
Langkah-langkah untuk membagi sel menjadi beberapa kolom dengan Text to Columns adalah
- Pilih sel atau sel yang berisi teks yang akan dipisah
- Dari pita, klik Data > Alat Data [Grup] > Teks ke Kolom
- Kotak dialog Convert Text to Columns Wizard akan terbuka
- Pilih opsi Dibatasi. Ini memungkinkan kita untuk membagi teks pada setiap kemunculan karakter tertentu. Dalam kasus kami, karakter spasi adalah pembatas kami. Klik Berikutnya
- Langkah 2 di Wisaya Konversi Teks ke Kolom terbuka. Di grup Pembatas, pilih opsi yang relevan untuk skenario Anda. Saya telah memilih Space untuk skenario ini. Klik Berikutnya
- Terakhir, di Langkah 3 Panduan Teks ke Kolom, kami menentukan format untuk hasilnya. Karena kami menggunakan teks, format Umum adalah opsi yang masuk akal. Pilih sel Tujuan tempat menampilkan hasilnya. Saya telah memilih Sel B2. Klik Selesai
Itu dia. Hanya dengan beberapa klik dan selesai 🙂
Jangkauan kami sekarang harus dibagi dengan sempurna menjadi beberapa kolom
Catatan tentang Teks ke Kolom
Teks ke kolom bukanlah fitur dinamis;
Teks ke kolom berfungsi paling baik pada sel yang memiliki format yang konsisten. Misalnya, jika satu sel memiliki 1 spasi, sedangkan yang lain memiliki 2 spasi, mungkin tidak memberikan hasil yang diinginkan
Pisahkan sel menggunakan Flash Fill
Flash Fill adalah fitur fantastis yang diperkenalkan di Excel 2013. Anda mungkin sudah menggunakannya tanpa menyadarinya. Sangat mudah digunakan untuk membelah sel di Excel dengan pola sederhana
Itu bisa berjalan dengan dua cara
- Di latar belakang, Excel mencoba menemukan pola dalam teks dan membuat saran secara otomatis
- Dipicu oleh pengguna pada waktu tertentu
Eksekusi latar belakang
Untuk eksekusi latar belakang
- Dengan sel sumber di sebelah kiri, ketikkan elemen teks yang ingin kita ekstrak di baris pertama kolom pertama [sel B2 pada tangkapan layar di bawah]
Belum ada yang terjadi - Mulailah mengetik elemen teks yang ingin kita ekstrak dari sel kedua. Sekarang Isi Cepat beraksi dan membuat rekomendasi
- Tekan tombol Tab atau Enter untuk menerima isian yang disarankan
Ini memberi kita nama depan
Eksekusi manual
Sekarang mari kita picu Flash Fill secara manual untuk mendapatkan nama belakang
- Dengan sel sumber di sebelah kiri, ketikkan elemen teks yang akan diekstrak ke dalam satu baris
- Pilih sel dalam rentang yang akan diisi dengan nilai [sel C2 hingga C11 dalam contoh kita], klik Beranda > Isi [dropdown] > Isi Cepat
Dan kita sudah selesai. Baik nama depan dan nama belakang telah dipisahkan menjadi kolom. Saya telah menunjukkan latar belakang dan metode manual di atas;
Catatan tentang Flash Fill
Meskipun Isi Cepat di Excel sangat bagus dalam mengidentifikasi pola, fitur ini tidak selalu mengembalikan nilai yang Anda harapkan dalam skenario yang lebih kompleks. Perlu ada beberapa pola logis untuk menemukannya
Flash Fill mengembalikan nilai statis. Jika sel sumber berubah, kita perlu menjalankan kembali prosesnya
Isi Cepat juga tersedia di Data [tab] > Isi Cepat
Jika Excel tidak dapat memahami polanya, itu akan mengembalikan pesan kesalahan
Mudah-mudahan, Anda setuju bahwa masa depan ini adalah transformasi cepat apa pun yang mungkin ingin kami lakukan pada sel di Excel
Pisahkan sel menggunakan Power Query
Opsi lain untuk memisahkan beberapa sel di Excel adalah menggunakan Power Query. Ini telah tersedia sejak Excel 2016. Sebelumnya, ada add-in untuk Excel 2010 dan 2013
Pertama, mari muat sel kita ke editor Power Query
- Pilih sel apa pun di kumpulan data, lalu klik Data [tab] > Dari Tabel/Rentang [atau Data [tab] > Dari Lembar di versi Excel yang lebih baru] dari pita
- Jika sel yang dipilih belum menjadi bagian dari tabel Excel, kotak Buat Tabel akan terbuka. Pastikan rentang penuh dipilih, dan tabel saya memiliki opsi tajuk dicentang. Lalu klik Oke
- Editor Power Query akan terbuka dengan semua data yang ditampilkan
- Dari pita Power Query, pilih Beranda > Pisahkan Kolom [turun bawah] > Menurut Pembatas
- Bergantung pada kerumitan teks Anda, ada banyak opsi untuk membagi konten sel dengan posisi yang berbeda. Untuk skenario kami, karakter Space dan setiap kemunculan pembatas adalah opsi yang bagus. Klik Oke
- Di jendela pratinjau data, kami sekarang membagi nama menjadi dua kolom berbeda
- Di jendela pratinjau data, klik dua kali tajuk dan ubah judul menjadi Pertama dan Terakhir
- Sekarang kita perlu mengembalikan data ke Excel. Klik Beranda > Tutup dan Muat [tarik-turun] > Tutup dan Muat Ke…
- Dari kotak dialog Impor Data, pilih Tabel dan pilih tempat memuat kembali informasi. Dalam contoh, saya telah memilih lembar yang ada di sel C1. Klik Oke
Data yang diubah sekarang akan dimuat kembali ke dalam sel di Excel
Catatan tentang Power query
Jika sel sumber kami berubah atau lebih banyak nama ditambahkan, kami cukup mengklik Data [tab] > Segarkan Semua, untuk menyegarkan keluaran
Metode ini akan mengubah sel sumber menjadi tabel Excel meskipun Anda tidak menginginkannya
Jika memisahkan dengan pembatas tidak cukup kuat, Power Query bisa mengubah string yang lebih kompleks menggunakan fitur Kolom dari Contoh
Power Query menyediakan banyak alat canggih untuk menangani manipulasi data yang lebih kompleks. Jika Anda belum sering menggunakan Power Query, lihat seri Pengantar Power Query saya
Pisahkan sel menggunakan rumus Excel
Dalam menjawab pertanyaan bagaimana membagi sel di Excel, opsi terakhir kami adalah menggunakan rumus standar Excel. Ini memberi kami kemampuan untuk membagi konten sel menggunakan aturan apa pun yang dapat kami programkan ke dalam formula kami. Meskipun rumus sangat ampuh, rumus juga membutuhkan keahlian kita daripada menggunakan antarmuka tunjuk-dan-klik
Fungsi Excel yang berguna untuk membagi sel
Fungsi paling berguna untuk membagi banyak sel adalah
KIRI
Mengembalikan jumlah karakter yang ditentukan dari awal string teks
Contoh
=LEFT["Doris Green", 5]
Hasil. Doris
Argumen kedua dari 5 memberi tahu fungsi untuk mengembalikan 5 karakter pertama dari teks "Doris Green"
BAIK
Mengembalikan jumlah karakter yang ditentukan dari akhir string teks
Contoh
=RIGHT["Kathryn West", 4]_
Hasil. Barat
Argumen kedua dari 4 mengatakan fungsi untuk mengembalikan 4 karakter terakhir dari teks "Kathryn West"
PERTENGAHAN
MID mengembalikan karakter dari tengah string teks, diberi posisi awal dan panjang
Contoh
=MID["Ann Lewis", 5, 3]
Hasil. Lew
Argumen kedua mewakili karakter ke-n untuk memulai, dan argumen terakhir adalah panjang string. Dari teks "Ann Lewis", 3 karakter yang dimulai dari posisi 5 adalah "Lew"
LEN
Mengembalikan jumlah karakter dalam string teks
Contoh
=LEN["Amaliya Dixon"]
Hasil. 13
Ada 13 karakter dalam string [termasuk spasi]
CARI
SEARCH mengembalikan jumlah karakter di mana karakter atau string teks tertentu pertama kali ditemukan. SEARCH dibaca dari kiri ke kanan dan tidak peka huruf besar-kecil
Contoh
=SEARCH[" ","Charles Bell",1]_
Hasil. 8
Karakter spasi pertama kali muncul di posisi ke-8 string “Charles Bell”;
Argumen terakhir bersifat opsional; . Jika dikecualikan, penghitungan akan dimulai dari karakter pertama
Catatan. FIND adalah padanan peka huruf besar kecil dari fungsi SEARCH
PENGGANTI
SUBSTITUTE mengganti semua instance string teks yang ada dengan string teks baru
Contoh
=SUBSTITUTE["Juan Dixon","Dix","Wils",1]
Hasil. Juan Wilson
String teks Dix telah diganti dengan Wils. Argumen terakhir menentukan instance mana yang akan diganti. Dalam rumus di atas, ini hanya menggantikan instance pertama. Jika kami mengecualikan argumen ini, itu akan menggantikan setiap contoh
Contoh skenario
Skenario kami cukup sederhana, untuk membagi sel menjadi beberapa kolom, kami dapat menggunakan fungsi LEFT, RIGHT, LEN dan SEARCH. Kita dapat mengekstrak nama depan dan belakang sebagai berikut
Nama depan
=LEFT[A2,SEARCH[" ",A2]-1]_
- SEARCH menemukan posisi karakter spasi, yang untuk catatan pertama dalam sel contoh "Amaliya Allen" adalah posisi ke-8
- Kami tidak ingin karakter spasi dimasukkan dalam string, jadi kami mengurangi 1 dari hasil untuk memberi kami nilai 7
- KIRI lalu mengekstrak 7 karakter pertama dari string teks
nama keluarga
=RIGHT[A2,LEN[A2]-SEARCH[" ",A2]]
- LEN menemukan panjang string teks, yang untuk "Amaliya Allen" adalah 13
- SEARCH menemukan posisi karakter spasi, yaitu 8
- 13 dikurangi 8 memberi kita jumlah karakter setelah spasi pertama
- KANAN kemudian digunakan untuk mengekstrak karakter setelah spasi
Dengan dua rumus ini, kita kembali ke hasil yang sama seperti sebelumnya;
Catatan tentang penggunaan rumus
Rumus adalah satu-satunya pilihan dalam posting ini yang sepenuhnya dinamis. Jika teks asli berubah, hasil rumus juga diperbarui secara otomatis
Bergantung pada kerumitan string teks yang akan dipisahkan, rumus ini bisa menjadi sangat rumit
Kesimpulan
Dengan 4 teknik ini, Anda telah mempelajari cara membagi sel di Excel. Setiap opsi telah menambahkan lebih banyak kekuatan untuk menghadapi kompleksitas. Opsi mana yang Anda pilih tergantung pada skenario spesifik Anda. Biasakan diri Anda dengan semua opsi, dan kemudian Anda akan dapat membuat keputusan terbaik
Tentang Penulis
Hai, saya Mark, dan saya menjalankan Excel Off The Grid
Orang tua saya memberi tahu saya bahwa pada usia 7 tahun saya menyatakan akan menjadi akuntan yang berkualitas. Saya adalah paranormal atau tidak memiliki imajinasi, karena itulah yang sebenarnya terjadi. Namun, baru pada usia 35 tahun perjalanan saya benar-benar dimulai
Pada 2015, saya memulai pekerjaan baru, di mana saya bekerja secara teratur setelah jam 10 malam. Akibatnya, saya jarang melihat anak-anak saya selama seminggu. Jadi, saya mulai mencari rahasia mengotomatisasi Excel. Saya menemukan bahwa dengan membuat sejumlah kecil alat sederhana, saya dapat menggabungkannya dengan berbagai cara untuk mengotomatiskan hampir semua tugas rutin saya. Ini berarti saya dapat bekerja lebih sedikit [dan saya mendapat kenaikan gaji. ]. Hari ini, saya mengajarkan teknik-teknik ini kepada para profesional lain dalam program pelatihan kami sehingga mereka juga dapat menghabiskan lebih sedikit waktu di tempat kerja [dan lebih banyak waktu dengan anak-anak mereka dan melakukan hal-hal yang mereka sukai]
Apakah Anda memerlukan bantuan untuk menyesuaikan pos ini dengan kebutuhan Anda?
Saya menduga contoh dalam posting ini tidak sesuai dengan situasi Anda. Kita semua menggunakan Excel secara berbeda, jadi tidak mungkin menulis postingan yang memenuhi kebutuhan semua orang. Dengan meluangkan waktu untuk memahami teknik dan prinsip dalam posting ini [dan di tempat lain di situs ini], Anda harus dapat menyesuaikannya dengan kebutuhan Anda.