Filter Data Di Microsoft Excel

MS Excel dapat diibaratkan sebagai sebuah miniatur sistem basis data dimana didalamnya kita bisa menyimpan data dan melakukan pengolahan data, termasuk pencarian salah satunya. Proses pencarian akan sangat mudah apabila data yang dimiliki relatif sedikit, namun akan sangat menyulitkan jika data yang dimiliki relatif banyak (ribuan baris data).

Untuk yang terbiasa dengan database akan lebih familiar dengan istilah QUERY yaitu prose pengolahan data salah satunya pencarian, excel sendiri memiliki fitur query namun tidak secanggih aplikasi database seperti MS Access atau sekelas SQL Server, dengan fitur yang ada cukup membantu dalam proses pencarian dan perekapan data.

Sebagai contoh kasus perhatikan gambar diatas, tampak pada gambar diatas terdapat data mahasiswa dari universitas antah berantah, dari data tersebut bagaimana caranya kita menampilkan data mahasiswa dengan kriteria sebagai berikut :

1. Mahasiswa dari program studi Akuntansi, atau
2. Mahasiswa yang merupakan mahasiswa studi lanjut dari D3 ke S1, atau
3. Mencari mahasiswa yang memiliki nama Ade , dan lain sebagainya akan semakin banyak pertanyaan jika dikaji lebih dalam dari data tersebut.

Untukmenjawab pertanyaan-pertanyaan seperti itu maka MS Excel menyediakan fitur FILTER DATA yang bisa diakses di ribbon DATA di Grup Sort & Filter (lihat gambar)

Adapun cara untuk menggunakannya adalah sebagai berikut

Blok judul kolom dari data yang anda miliki

Klik Icon Filter pada griup Sort & Filter di Ribbon Data (lihat gambar),

Maka, hasilnya pada setiap judul kolom data akan memiliki tanda panah kebawah (Dropdown) seperti tampak pada gambar berikut :

JIka sudah tampil seperti gambar diatas, maka proses filter data (pencarian) sudah bisa digunakan, sebagai contoh kita akan mencarai data mahasiswa program studi Akuntansi maka, pada tanda panah kebawah (dropdown) dikolom program keahlian di klik, kemudian hilangkan semua ceklist dan pilih program keahlian yang akan ditampilkan dalam hal ini contoh akuntansi (lihat gambar), kemudian klik tombol Ok

Maka hasilnya bisa dilihat, data yang ditampilkan adalah seluruh mahasiswa program keahlian akuntansi, perhatikan gambar dibawah terdapat perubahan yaitu nomor baris berubah warna biru menunjukan data sedang di filter dan proses filtering terjadi di kolom program studi ditandai dengan tanda corong (lihat gambar dilingkari).

Untuk proses filtering yang lain prosesnya sama, nantikan artikel berikutnya untuk proses filtering yang lebih lanjut.

Memecah Kolom Tempat Tanggal Lahir

 

Adalah kebiasaan orang indonesia ketika membuat data tentang tanggal lahir dalam format kolom selalu disatukan sehingga memiliki format seperti berikut, misal :

Kuningan, 19 Januari 1980

Perhatikan gambar diatas pada kolom D, meskipun secara mudah data bisa dibaca namun apabila data disajikan seperti pada gambar diatas tidak bisa dilakukan pengolahan lebih lanjut dikarenakan adanya penggabungan dua buah field yang berbeda yaitu string (untuk data tempat lahir) dan date (untuk tanggal lahir).

Namun apabila data sudah terlanjur seperti tampak pada gambar diatas bagaimana apabila kita ingin memecahnya menjadi dua bagian yaitu kolom tempat lahir dan kolom tanggal lahir !.

Berikut cara memecah kolom tersebut :

1. Menggambil nama tempat lahir gunakan fungsi berikut :=LEFT(D2,FIND(“,”,D2)-1)
2. Mengambil tanggal lahir gunanakn fungsi berikut :
   =MID(D2,RIGHT(FIND(“,”,D2))+2,LEN(D2))

Sebagai Bahan latihan Silahkan download file-nya dibawah ini !

DOWNLOAD

Increase Decimal versus Fungsi Roundup

Pembulatan angka menggunakan excel merupakan hal mudah untuk dilakukan terutama untuk memforamt tampilan agar angka dibelakang decimal bisa terlihat seragam misal menjadi dua angka dibelakang desimal, pembulatan bisa dilakukan dengan dua cara yaitu :

1. Menggunakan tombol increase / decrease button
   
   
   
   
2. Menggunakan fungsi round,roundup atau roundown

Sekilas hasilnya akan tampak sama dilayar yaitu menampilkan angka menjadi memiliki dua desimal dibelakang koma, namun INGAT hal itu hanya tampilan dilayar, hasilnya akan berbeda dan menjadi salah jika digunakan untuk pengambilan keputusan, untuk lebih jelasnya perhatikan gambar dibawah ini :

Perhatikan gambar diatas, penulis memiliki nilai asli pada cell B3, dimana cell tersebut memiliki banyak desimal dan belum diformat , berikutnya pada cell C3, diformat menggunakan increase decimal sehingga tampil menjadi 7.71.

Berikutnya penulis akan mengambil sebuah keputusan dengan ktentuan jika nilainya lebih dari sama dengan 7.51 maka hasilnya lulus dan jika tidak maka hasilnya tidak lulus.

Untuk itu penulis membandingkan dua formula antara pembulatan dengan tombol increase decimal dan Fungsi RoundUp ;

1. Dengan tombol increase decimal, maka formulanya :=IF(C3>=7.51,”Lulus”,”Tidak Lulus”)dan hasilnya TIDAK LULUS (padahal seharusnya LULUS)
2. Dengan Fungsi RoundUP maka formulanya :=IF(ROUNDUP(C3,2)>=7.51,”Lulus”,”Tidak Lulus”)dan hasilnya Lulus

Mana yang lebih tepat ? ya Fungsi Roundup lebih tepat ! jadi untuk pengambilan keputusan Fungsi Roundup harus digunakan ketika akan menggunakan angka yang akan dibulatkan dan ingat Tombol Increase / Decrease Decimal hanya untuk Tampilan di layar saja, bukan untik diproses lebih lanjut !, Semoga bermanfaat

Teknik Import Data Ke Excel #4

Masih membahas tentang import data ke excel, setelah sebelumnya menjelaskan import data ke excel dengan sumber data berasal dari file text, halaman web dan MS Access, kali ini akan dibahas import file dari sumber lain dalam hal ini dari database server Microsoft SQL Server 2000.

Untuk menjalankan tutorial ini asumsinya komputer anda sudah terinstall MS SQL Server baik V2000 atau versi diatasnya.

Adapun langkah-langkahnya adalah sebagai berikut :

1. Pada ribbon data piih from other source -> pilih From SQL Server (lihat gambar 1)
   
   
   
   
2. Kemudian akan tampil wizard import data, pada kotak dialog data conection wizard isikan parameter disesuaikan dengan ketika anda meng-install MS SQL Server, pada contoh di gambar 2, text box server name diisi dengan tanda titik, ini artinya MS SQL server satu komputer dengan MS Excel yang anda gunakan, jika lokasi MS Server berbeda komputer bisa diisi dengan IP Address atau alamat internet. Kemudian isi juga username dan password yang digunakan saat anda install atau yang diberikan oleh database administrator. JIka telah selesai klik Next
   
   
   
   
3. Jika pada langkah sebelumnya seluruh isian telah diisi dengan benar maka anda langsung terkoneksi ke database server dan disediakan drop down database yang akan diimport datanya, dalam hal ini penulis memilih database Nortwhind, kemudian ceklist pilihan COnect to spesific table, kemudian anda pilih daftar tabel yng akan diimport datanya, kemudian klik next.
   
   
   
   
4. Selanjutnya langsung anda klik Fisnish pada kotak dialog dibawah ini.
   
   
   
   
5. Selanjutnya tentukan jenis penampilan data dalam hal ini penulis memilih jenis tampilan akan berupa tabel, dan tentukan area penyimpanan data dengan memilih Exisiting worksheet, kemudian klik OK.
   
   
   
   
6. Karena data bersumber dari database server yang menerapkan otentifikasi maka seblum ditampilkan data, anda akan ditanya lagi usernam dan password untuk mengakses server database serta host dari server database, isi dengan parameter ketika instalasi server atau sesuai yang diinpormasikan oleh database administrator. Kemudian klik OK
   
   
   
   
7. Ketika anda mengklik ok pada langkah sebelumnya maka akan dirampilkan data dari server, namun data tersebut masih terkoneksi ke database server, untuk mengakhiri proses import data maka anda cukup meng-klik Convert To Range pada ribbon group Tools.
   
   
   
   
8. Gambar dibawah adalah hasil akhir dari proses import data dari SQL Server.
   
   
   
   

Teknik Import Data Ke Excel #3

Masih membahas membahas tentang import data, setelah mengetahui cara meng-import data dari file text dan dari halaman web, kali ini akan membahas import data dari microsoft acces.

Untuk mengimport data dari access masih menggunakan ribbon group external data seperti tampak pada gambar dibawah ini

Gambar 1

Untuk lebih jelasnya berikut langkah-langkah import data dari microsoft access.

1. Buat lembar kerja kosong di excel
2. Pada ribbon group get external data (gambar 1) klik From Acces
3. Kemudian akan keluar kotak dialog seperti tampak pada gambar dibawah ini, untuk memilih file access, silahkan pilih file access
4. Setelah terpilih maka akan ditampilkan seluruh objek (table, view, wuery, dlll) yang terdapat dalam file access, silahkan pilih sesuai kebutuhan anda, dalam hal ini penulis akan mengambil data dari objek berupa table.
   
5. Setelah mengklik OK pada gambar 4, maka akan keluar kotak dialog jenis penampilan data apakah berupa table, atau pivot table atau pivot chart, untuk kasus kali ini kita pilih table, kemudian klik OK.
   
6. Dan hasilnya tampak seperti berikut,
   
7. Data pada gambar diatas belum spenuhnya menjadi file kerja karena masih terkoneksi ke aksess, untuk merubah menjadi data excel murni klik tombol convert to range pda ribon group Tools (lihat gambar).
   

Teknik Import Data Ke Excel #2

Setelah kita membahas teknik import data dari file text pada artikel Teknik Import Data Ke Excel #1, selanjutnya akan kita bahas bagaimana mengimport data dari halaman web ke lembar kerja Excel.

Pada contoh kali ini akan mengambil data pendaftar dari halaman web SMK N 2 Kuningan, berikut langkah-langkahnya

1. Pada ribbon group Get External Data pilih From Web.
   Gambar 1
2. Maka akan keluar tampilan seperti browser, langsung saja ketik URL pada kotak yang disediakan.
   Gambar 2
3. Ketika halaman web terbuka, maka excel akan mendeteksi table dalam halaman web dengan memberikan tanda panah ke kanan didalam kotak ecil berwarna kuning (lihat gambar 2).
4. untuk memilih data yang akan diimpor klik tanda panah tersebut, sehingga berubah menjadi tanda ceklist didalam kotak kecil berwarna hijau.
   
   Gambar 3
5. Pada gambar 3 diatas, selanjutnya klik tombol import, dan akan keluar kotak dialog dimana anda akan menyimpan hasil import, jika akan disimpan di worksheet baru maka pilih new worksheet, atau bila di worksheet yang aktif, anda tinggal tentukan cell nya misal A1, jika telah selesai klik ok.
   
   Gambar 3

    

6. Hasil akhir dari import adalah seperti tampak dibawah ini.
   Gambar 4

    

7. Satu hal yang berbeda dengan teknik import dari file text adalah, file yang sudah diimport bisa di singkronisasi dengan halaman we, artinya jika halaman web berubah maka file di lembar kerja excelpun akan berubah, dengan catatan anda melakukan refresh data dengan menekan tombol Refresh.
   Gambar 5
8. Perhatikan gambar dibawah untuk siswa atas nama AAM AMALIA, pada tahap peng-import-an pertama tempat lahir masih berupa tulisan Kuningan, setelah penulis menekan tombol Refresh all pada ribbon group Connections, maka hasil lembar kerja excel manjadi up to date seperti tampak pada gamabr dibawah ini :
   Gambar 6

 

Selamat mencoba !!, Don’t do copy paste manually to get data from external data !! 🙂

Teknik Import Data Ke Excel #1

Untuk mempercepat pekerjaan dalam mengambil data dari sumber lain ke spreedsheet excel, cara konvensional adalah dengan copy dan paste, namun cara tersebut memiliki kelemahan terutama apabila data dari sumber yang berbeda misal dari MS Access, File Text, Database server atau dari halaman web.

Untuk mempermudah pekerjaan ini Microsoft Excel telah menyediakan tools untuk mengambil data dari sumber lain (Import Data), keseluruhan tool untuk import data terletak di ribon group Get External Data (lihat gambar).

Gambar 1

Dari tools tersebut dengan MS Excel kita dimungkinkan mengambil data dari sumber lain yang berupa file MS Access, dari halaman internet, dari file text dan dari sumber lain (Database Server, ODBC, dll).

Gambar 2

Pada session kali ini penulis akan memberikan contoh proses import data yang bersumber dari file text, file yang penulis gunakan berformat *.CSV (TXT juga biasa), berikut isi file CSV tersebut.

Isi file tersebut akan kita pindahkan ke MS Excel, berikut langkah-langkahnya.

1. Siapkan file baru
2. Pada gambar 1 pilih From Text
3. Maka akan tampil kotak dialog pemilihan file text, pilih file text yang akan di-import datanya
4. Kemudian akan tampil kotak dialog proses import langkah ke-1 (lihat gambar ), pilih delimited pada gambar dibawah, karena pada data sumber tiap data dipisahkan oleh tanda pemisah (delimiter, yaitu tanda titik koma),
   
   Gambar 3
5. Klik Next pada gambar diatas maka anda diminta menentukan tanda pemisah data pada file sumber dalam hal ini gunakan tanda titik koma sesuai dengan yang tertera pada gambar 2.
   
   Gambar 4
6. Klik Next pada gambar 4 diatas, kemudian akan tampil pemilihan tipe data untuk tiap kolom yang akan diimpor, bahkan anda bisa memilih kolom untuk tidak diimpor dengan memilih pilihan Don’t import Coloumn (skip).
   
   Gambar 5
7. Berikutnya klik next pada gambar lima jika pengaturan import sudah yakin benar, selanjutnya anda diminta menentukan lokasi untuk menyimpan data, dalam hal ini misal disimpan di cell A1, kemudia klik OK.
   
   Gambar 6
8. Hasil akir adalah data sekarang berpidah dari file text ke lembar kerja excel.
   
   Gambar 7

 

Selamat Mencoba !, Contoh berikutnya akan dibahas menggunakan data sumber dari file Microsoft Access.

Menghapus Cell Kosong Secara Cepat Di Excel

Kasus kali ini penulis temukan pada saat membuat laporan akademik semseteran untuk dilaporkan ke kopertis (DIKTI), data asal adalah excel yang nantinya akan diimport ke foxpro. Namun data asal yang penulis miliki terdapat masalah yaitu adanya baris kosong (blak cell) disetiap akhir baris data, jelasnya perhatikan gambar berikut :

Masalah diatas bisa diatasi denganmenghapus manual satu persatu baris kosong namun masalahnya terdapat ratusan baris kosong maka penulis harus mencari cara tercepat untuk menghapus data.

Untuk melakukan penghapusan baris kosong secara cepat berikut langkah-langkahnya :

1. Blok Seluruh data

2. Klik Home -> Editing -> Find & Select -> Go To

3. Kemudian pada kotak dialog Goto Teka ntombol Spesial

4. Pada kotak dialog Goto Special Berikut pilih Blanks

5. Maka secara otomatis seluruh cell kosong akan terpilih, sekarang saatnya menghapus cell, pilih Home -> Cells -> Delete -> Delete Sheet Rows

7. Hasil Akhir adalah sekarang semua baris ksong telah terhapus

Selamt Mencoba !

Memeriksa Jawaban Soal Pilihan Ganda dengan Excel

Kali ini penulis akan berbagi trik tentang cara memeriksa jawaban hasil ulangan/ujian dengan tipe soal pilihan ganda / multiple choice, kasus ini sengaja penulis buat untuk membantu menyelesaikan pemeriksaan hasil ulangan siswa. Secara garis besar berikut screenshoot dari lembar kerja excel yang akan dibuat :

Dari gambar diatas tugas anda hanyalah mengisi area berwarna putih dalam hal ini nama siswa dan jawaban siswa, namun sebelumnya anda harus mengisi kunci jawaban pada bagian atas tabel, sedangkan pada bagian yang berwarna kuning akan diisi secara otomatis.

Adapun langkah-langkah membuat formulanya adalah sebagai berikut :

1. Buatlah nama pada cell C* dengan nama kunci menggunakan fasilitas insert name define.

2. Mengisi Kolom Status

Adapun Formulanya adalah sebagai berikut :

=IF(D13=””,””,IF(LEN(D13)=LEN(kunci),”Ok”,IF(LEN(D13)>LEN(kunci),”Lebih”,”Kurang”)))

Maksud dari formula tersebut adalah, jika pada kolom D13 tidak ada isinya maka kolom status dikosongkan, dan jika banyaknya huruf dalam cell D13 sama dengan banyaknya huruf dalam cell C8 (kunci) maka tampilkan status OK, dan jika banyaknya huruf dalam cell D13 lebih besar dari banyaknya huruf dalam cell C8 (kunci) tampilkan status Lebih dan jika kurang maka tampilkan status Kurang.

3. Mengisi nilai pada kolom skore

Pada tahap ini akan diberikan contoh formula untuk menilai skore soal nomor satu untuk siswa ke satu.

=IF(F$11<=LEN($D12),IF(MID($D12,F$11,1)=MID(kunci,F$11,1),1,0),0)

Maksud dari formula diatas adalah jika banyaknya huruf di cell D12 kurang dari sama dengan isi cell F11 dalam hal ini 1, maka akan dilakukan lagi pengecekan kecocokan antara jawan dengan kunci jawaban, jika cocok bernilai 1 jika tidak bernilai 0, pengecekan jawaban menggunakan formula :

IF(MID($D12,F$11,1)=MID(kunci,F$11,1),1,0)

4. Perhatikan tanda $ pada alamat cell jika tanda dollar berada di depan alamat kolom maka alamat kolom tersebut tidak akan berubah jika dicopy kemanapun, begitu pula jika tanda $ berada didepan nomor baris, nomor baris tersebut tidak akan berubah jika dicopy kemanapun, ini lah yang disebut dengan cell absolut, untuk selanjutnya cell absolut akan dibahas pada bagian lain.

5. Tahap selanjutnya cell F12 ke smua kolom skore dan baris siswa, jika akan menambah siswa dan jumlah soal tinggal memodifikasinya saja.

6. Untuk mengisi nilai terbesar, terkecil dan jumlah serta rata-rata gunakan fungsi agregasi di excel

7. Sebagai bahan latihan, silahkan filenya di download DISINI.