Cara Efektif Memisahkan Konten Sel
Apa gunanya split cell dan bagaimana caranya memisahkan content sel menjadi beberapa bagian? Prosedur ini sangat berkhasiat untuk membantu mempermudah analisa data. Ada bebagai alternatif cara untuk melaksanakan split cells. Kita sanggup memakai fitur Text to Columns, Flash Fill dan Formula. Lebih lanjut dalam catatan pelajaran excel kali ini akan dibahas banyak sekali alternative cara memisahkan konten sel sehingga kita sanggup menentukan tehnik terbaik dan cocok sesuai kebutuhan serta pola data yang dihadapi.
- Split Cell Menggunakan Fitur Text To Columns
- Memisahkan Konten Sel Menggunakan Fill Flash (Excel 2013 Atau Lebih Baru)
- Split Data memakai Formula
Pada umumnya, pemisahan konten sel excel diharapkan pada dua kasus.
Yang paling sering yaitu saat kita meng-import data dari sumber external dimana semua informasi berada dalam satu kolom. Mau tidak mau data tersebut perlu dipecah atau dipisahkan menjadi beberapa kolom supaya sanggup diolah lebih lanjut.
Atau mungkin kita mempunyai sebuah tabel, tetapi ada satu kolom dalam tabel tersebut yang berisi adonan beberapa informasi. Nah, supaya tabel lebih gampang dianalisa, (misalnya memakai filtering, sorting dan pivot table) maka kolom yang berisi informasi adonan ini perlu di pecah menjadi beberapa kolom yang berisi informasi tunggal.
Cara Memisahkan Konten Sel Menggunakan Text To Columns.
Fitur Text to Columns ini tampaknya tersedia pada semua versi excel, setidaknya pada versi excel yang sudah saya cek yaitu versi excel 2003, 2007, 2010, 2013 dan 2016.
Fitur ini memungkinkan kita untuk memisahkan data text menurut pemisah text (delimiter) yang dipakai ibarat koma, titik koma, spasi atau adonan beberapa karakter yang berperan sebagai delimiter.
Mari kita lihat bagaimana fitur ini bekerja.
Memisahkan Cells Content menurut delimiter
Anggaplah kita memliki data yang di-import dari jadwal external. Data tersebut berisi list nama product, ID product, konsumen dan sebagainya. Satu baris data tersimpan dalam satu sel. Ada beberapa baris data dengan sruktur yang sama, semuanya terletak dalam kolom yang sama
Perhatikan rujukan tabel di atas. Semua data terletak pada satu sel dalam kolom A. Tiap item data dipisahkan oleh delimiter berupa tanda koma (,).
Kemudian kita ingin memidahkan data tersebut menjadi beberapa kolom sehingga memudahkan dalam proses olah data selanjutnya.
Untuk memisahkan data menjadi beberapa kolom, maka lakukan langkah berikut.
- Seleksi kolom A (atau tergantung lokasi kolom data)
- Kemudian dari tab Data, grup Data Tools, klik Text To Columns
- Langkah selanjutnya: pilih opsi Delimited, kemudian klik tombol NEXT
- Centang checkbox Comma, kemudian perhatikan pada kotak Data Preview untuk memastikan bahwa pemisahan konten sel sudah sesuai dengan yang diharapkan.
- Jika yakin sudah benar, maka selanjutnya klik tombol Next.
Setelah itu maka kita akan dibawa pada kotak obrolan berikutnya yang berkhasiat untuk pengaturan format dan destinasi sel untuk menyimpan hasil pemisahan konten.
Format Data:
Secara default, excel membaca semua format sel sebagai general. Namun kita sanggup memberitahu excel bila ada beberapa fragmen data yang harus diperlakukan sebagai format lainnya. Misalnya sebagai format tanggal. Ini diharapkan bila ada bab kontent sel yang berupa tanggal.
Misalnya pada rujukan di atas, tanggal terletak pada bab final atau kolom paling kanan. Pada Data preview, seleksi kolom tersebut, kemudian pada opsi date, pilih format tanggal yang sesuai. Dalam rujukan diatas yaitu YMD.
Destination:
Secara default, range destinasi akan menimpa kolom original dengan penambahan kolom berikutnya sesuai jumlah kolom hasil pemisahan. Namun destinasi ini sanggup kita rubah dengan cara klik collapse obrolan icon yang terletak disebelah kanan kotak destination. Kemudian dengan memakai mouse, pilih cell pada range distinasi. Kita sanggup juga mengetik referensi sel secara eksklusif dalam box.
Catatan:
Jika beberapa kolom tidak kita perlukan, maka kita sanggup mencegah kolom tertentu supaya tidak di-import sebagai hasil pemisahan. Caranya seleksi kolom tersebut pada data preview, kemudian tick opsi Do Not Import Column (skip) pada bab data format.
Langka terakhir: klik tombol Finish. Sampai pada tahap ini, kita sudah berhasil memisahkan konten sel menjadi beberapa bab dalam kolom terpisah, ibarat sanggup dilihat pada screenshot di bawah ini.
Untuk memudahkan analisa dan olah data lebih lanjut, kita sanggup menambahkan header kolom yang sesuai terhadap tabel di atas.
Bagaimana memisahkan text menurut lebar (fixed width)
Bagian ini menjelaskan bagaimana untuk membagi konten sel menurut jumlah karakter yang ditentukan. Untuk memudahkan pemahaman, silahkan perhatikan rujukan berikut:
Anggaplah kita mempunyai data Nomor Induk Karyawan (NIK) dan nama karyawan yang terletak dalam satu kolom. NIK dan nama karyawan dipisahkan oleh spasi.
Kemudian kita ditugaskan untuk memisahkan NIK dan Nama Karyawan pada dua kolom yang terpisah. Karena semua NIK terdiri dari jumlah angka yang konstan yaitu 6 karakter, maka salah satu cara yang efektif untuk mengerjakan kiprah ini yaitu memakai Text to Columns Fixed width.
Mulai Convert text to columns sebagaimana dijelaskan pada rujukan pertama. Kemudian pilih Fixed width and click Next.
Set lebar masing – masihg kolom dengan memakai Data Preview, sebagaimana diperlihakan dalam screenshot di bawah.
Perhatikan garis vertikal pada gambar di atas. Garis tersebut mewakili pemisah kolom. Untuk membuat sebuah garis pemisah gres maka anda cukup klik posisi yang diinginkan.
Untuk menghilangkan garis pemisah, cukup lakukan double klik garis yang akan dihapus tersebut. Untuk memindahkan garis ke posisi lainnya tinggal geser atau drag memakai mouse.
Langkah selanjutnya, pilih data format dan destination untuk memisahkan sel secara sempurna sebagaimana dilakukan pada rujukan sebelumnya, dan klik tombol Finish untuk menyelesaikannya.
Memisahkan konten sel memakai Fill Flash
Jika anda memakai excel 2013 atau versi yang lebih baru, maka anda akan sangat terbantu dengan adanya fitur Flash Fill. Fitur ini tidak hanya sanggup mengisi sel dengan data, tetapi juga sanggup memecah konten sel menjadi beberapa bagian. Dan hebatnya lagi, kita sanggup menyisipkan text tertentu.
Fitur Flash Fill bekerja dengan cara menganalisa pola data yang anda input pada sebuah sel, kemudian meneruskan fill sel lainnya mengikuti pola tersebut.
Dengan memakai rujukan sebelumnya berupa data Nomor Induk + Nama Karyawan.
- Ketik pada salah satu sel disamping kolom data tersebut, contohnya ketik nama depan.
- Seleksi range yang ingin diisi hingga baris final tabel.
- Dari tab Data, klik Flash Fill
Perhatikan screenshot berikut:
Maka baris lainnya akan terisi data mengikuti pola data yang sudah diketik duluan.
Cobalah untuk berexperimen mengetikan pola text tertentu. Misalnya menambahkan text pemanis ibarat Sdr pada salah satu sel dilanjutkan dengan proses Flash Fill. Maka akhirnya sangat mengagumkan: semua sel akan mendapat pemanis text Sdr.
Untuk mengambil bab data lainnya maka sanggup melaksanakan flash fill lagi pada kolom selanjutnya. Misalnya untuk mengambil Nomor Induk Karyawan (NIK)
Ketik Nomor induk karyawan pada satu sel yang sesuai atau sebaris dengan data originalnya. Kemudian seleksi range yang akan diisi, dan klik tombol Flash Fill ibarat diilustrasikan dalam gambar berikut:
Shortcut Flash Fill.
Selain dengan cara klik tombol Flash Fill, untuk menjalan kan fitur ini, kita juga sanggup memakai shortcut keyboard CTR + E. Cara ini tentu saja sangat menghemat waktu anda.
Alternative kedua yaitu dengan memakai kursor mouse. Caranya posisikan kursor pada bab pojok bawah sel yang sudah diisi hingga kursor menjelma tanda plus (+), kemudkan klik kanan dan drag ke arah bawah. Selanjutnya lepaskan mouse, makan akan muncul contextual menu. Lalu klik Flash Fill pada contextual menu.
Untuk lebih jelasnya perhatikan sreenshot berikut:
Flash Fill juga sanggup bekerja secara otomatis. Kita cukup menyeleksi range disamping kolom data original, kemudian ketikan 2 atau 3 sel, maka otomatis excel akan memperlihatkan list pada sel berikutnya. Jika anda merasa cocok dengan pola data tersebut, eksklusif saja tekan tombol Enter.
Catatan:
Jika fitur Flash Fill otomatis tidak aktif, maka kita sanggup mengatifkannya melalui Tab File > Options, kemudian klik Advanced. Selanjutnya pada bab Opsi Editing, centang checkbox Automatically Flash Fill
Memisahkan Konten Sel Menggunakan Formula.
Cara ketiga untuk memisahkan konten sel yaitu memakai rumus atau formula. Tentu saja fungsi yang dipakai dalam formula yaitu fungsi-fungsi yang terkait untuk mengolah atau manipulasi text ibarat LEFT, RIGHT, MID, LEN, SEARCH dan FIND. Pembahasan mengenai fungsi - fungsi tersebut sanggup dilihat pada artikel Fungsi Pengolah Text
Formula untuk memisahkan konten sel bila jumlah karakter diketahui.
Dengan memakai rujukan data dari pembahasan sebelumnya yaitu berupa NIK dan Nama karyawan, maka rumus yang sanggup dipakai untuk memisahkan atau mengambil fragmen data NIK dan nama karyawan yaitu sebagai berikut:
Data original (gabungan) terletak pada kolom A
Maka rumus untuk mendapat data NIK pada baris pertama adalah:
=LEFT(A1,6)
Sedangkan rumus untuk mendapat data nama adalah:
=MID(A1,8,100)
Atu ibarat sanggup dilihat pada gambar dibawah ini.
Contoh di atas merupakan yang paling sederhana dari cara memakai formula untuk memisahkan konten sel. Hal ini alasannya yaitu pola data originalnya sederhana dan relatif teratur, dimulai dengan data NIK dengan jumlah karakter yang sama sehingga sanggup ditentukan jumlahnya secara explicit yaitu 6 karakter sebagai parameter num_chars dalam fungsi LEFT.
Demikian pula fungsi MID sanggup memakai angka 8 sebagai parameter start_num. Hal ini alasannya yaitu posisi karakter pertama dari Nama karyawan semuanya sama yaitu karakter ke-8 dalam text konten sel original. Sedangkan bilangan 100 sebagai parameter num_chars dalam fungsi MID diasumsikan bahwa nama karyawan tidak ada yang lebih dari 100 karakter.
Formula untuk memisahkan konten sel bila jumlah karakter tidak diketahui.
Misalnya kita mempunyai data awal berupa nama Perusahaan dan Kota. Data terletak dalam satu kolom, dipisahkan oleh koma, ibarat gambah di bawah ini.
Selanjutnya kita ingin memisahkan data nama perusahaan dan lokasi kotanya pada dua kolom yang berbeda.
Karena jumlah karakter penyusun nama perusahaan berbeda-beda, maka kita tidak sanggup menentukan angka konstan sebagai parameter num_chars dalam fungsi LEFT.
Untungnya jumlah karakter penyusun nama perusahaan sanggup dicari dengan memanfaatkan keberadaan tanda koma yang posisinya sempurna satu karakter sehabis nama perusahaan. Untuk kiprah ini, kita sanggup memanfaat kan fungsi FIND atau fungsi SEARCH.
Misalnya:
Rumus =FIND(",",A1)-1 atau =SEARCH(",",A1)-1 akan menghasilkan bilangan 8 yang merupakan jumlah karakter nama perusahaan yang terletak pada sel A1 yaitu “CV Setia”
Rumus =FIND(",",B1)-1 atau =SEARCH(",",B1)-1 akan menghasilkan bilangan 10 yang merupakan jumlah karakter nama perusahaan yang terletak pada sel B1 yaitu “CV Mandiri”
Perbedaan fungsi FIND dan SEARCH yaitu bagaimana kedua fungsi tersebut mendeteksi karakter kapital dan karakter kecil. FIND menganggap berbeda antara karakter kapital dan karakter kecil. Sebaliknya SEARCH menganggap karakter kapital dan kecil sama saja. Dikarenakan tidak ada jenis kapital dan kecil untuk tanda koma, maka dalam rujukan masalah ini penggunaan fungsi FIND dan SEARCH sama saja dan sanggup saling menggantikan.
Selanjutnya rumus FIND atau SEARCH ini sanggup dimasukan dalam fungsi LEFT untuk menempati argumen num_chars. Sehingga rumus untuk memisahkan nama perusahaan sanggup dituliskan sebagai berikut:
=LEFT(A1,FIND(",",A1)-1)
Pertanyaan berikutnya: rumus macam mana yang sanggup dipake untuk memisahkan nama Kota? Dikarenakan nama Kota terletak di sebelah kanan, dan tidak ada data lain di sebelah kanan nama kota, maka untuk kiprah ini kita sanggup memakai fungsi RIGHT
Fungsi RIGHT berkhasiat untuk mengambil beberapa karakter dari sebelah kanan. Jumlah karakter penyusun nama Kota sanggup dihitung dari jumlah karakter peyusun sel original dikurangi nomor urut posisi tanda koma, dan dikurangi 1 lagi yaitu tanda spasi.
Rumus untuk mendapat jumlah karakter penyusun nama kota sanggup dituliskan sebagai berikut
LEN(A1)-FIND(",",A1)-1
Rumus di atas akan menghasilkan bilangan 7 yang merupakan jumlah karakter penyusun nama kota Bandung yang terletak pada Sel A1. Adapun cara kerja rumus tersebut sanggup dijelaskan sebagai berikut.
LEN(A1) dipakai untuk menghitung jumlah karakter pada sel A1
FIND(",",A1) dipakai untuk mencari posisi tanda koma pertama pada sel A1
Angka 1 dipakai sebagai pengurang alasannya yaitu adanya tanda spasi yang bukan bab dari nama kota.
Sehingga bila fragmen-fragmen rumus tersebut diganti menjadi value yang merupakan hasil kalkulasinya maka sanggup diuraikan sebagai berikut:
= LEN(A1)-FIND(",",A1)-1
= 17 – 9 – 1
= 7
Selanjutnya, rumus LEN(A1)-FIND(",",A1)-1 sanggup ditempatkan sebagai argumen num_chars dalam fungsi RIGHT untuk mendapat nama kota. Sehingga rumusnya sanggup dituliskan ibarat dibawah ini:
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)
Dalam memakai rumus untuk memisahkan konten sel, kita sanggup mencoba dengan banyak sekali variasi adonan fungsi. Misalnya sanggup juga memakai adonan fungsi MID, LEN dan FIND untuk mendapat nama kota. Disitulah kebijaksanaan excel kita bermain.
Sampai disini pembahasan cara memisahkan konten sel pada excel. Anda sanggup menentukan mana yang sesuai dan mana yang paling efektif apakah memakai fitur Text To Columns, Flash Fill, ataupun dengan Formula / rumus. Tentu saja anda yang lebih tahu mana yang paling efektif sesuai kebutuhan dan masalah data yang dihadapi.
Demikian biar bermanfaat.
Catatan Pelajaran Excel Yang Terkait:
0 Response to "Cara Efektif Memisahkan Konten Sel"
Posting Komentar