Category Archives: Trick

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.

balnk_cell_1

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.

Mencari Hari Pasaran Jawa Dengan Excel

Hari pasaran merupakan hari dalam kalender jawa dimana dalam satu minggu terdiri dari 5 hari berbeda dengan kalender masehi dimana hari minggu terdapat 7 hari. Urutan hari dalam hari pasaran terdiri dari Pahing, Pon, Wage, Kliwon dan Legi, dengan menggunakan excel kita bisa melakukaan pencarian hari pasaran dengan menggunakan fungsi lookup dan mod. Tentang hari pasaran perhitungan manualnya bisa anda baca di “Blog Spektrum Pemikiranku

Untuk jelasnya perhatikan gambar dibawah ini :

Pada gambar diatas hari pasaran disimpan pada awal hari dengan no index array 0 (bukan 1 !) dimulai dengan nama hari pasaran kliwon. Setelah anda membuat data seperti pada tampilan diatas blok cell A1 sampai dengan F2 dan beri nama dengan nama array_hari menggunakan fasilitas insert name define.

Jika anda ingin mengetahui hari pasaran dan hari kalender masehi , maka cukup memasukan pada kolom tanggal saja, tanggal berapa yang akan dicari hari pasarannya dan hari kalender masehi-nya.

Untuk mengetahui hari kalender masehi (cell B5) gunakan formula :

=TEXT(B4;”ddd”)

Rumus diatas merubah Cell B4 dengan fungsi Text dengan format ddd (coba anda ganti format nya dengan single d atau double d, apa yang terjadi ? )

Sedangkan untuk mencari hari pasaran pada cell B6 adalah :

=HLOOKUP(MOD(B4;5);array_hari;2)

Formula diatas mencari hasil sisa pembagian (modulus) tanggal dengan fungsi mod dimana nilai pembagi nya adalah 5 (sesuai jumlah hari pasaran dalam satu minggu nya) dan hasil pembagi tersebut dicocokan ke tabel array dengan fungsi HLOOKUP.

Sebagai latihan silahkan downoad file kerja artikel ini.

DOWNLOAD

Menyembunyikan Error Perhitungan dengan Conditional Formating

Meskipun excel mampu melakukan perhitungan dengan tepat namun error dalam perhitungan matematis maupun dengan fungsi jika salah satu input bernilai salah maka akan ditampilkan error, error tersebut kadang tidak enak dipandang apabila hasil perhitungan merupakan sebuah laporan yang akan dipublikasikan, nah sebagai ilustrasi perhatikan contoh kasus dibawah ini :

Pada gambar diatas adalah contoh laporan penjualan yang akan dipublikasikan, dari gambar tersebut terdapat error pada cell D5 error terjadi karena dilakukan perhitungan pembagian namun angka pembagi karena bernilai 0 (nol) maka hasiilnya akan ditampilkan warning berupa pesan #DIV/0 (Division by zero), seperti kita ketahui bahwa bilangan berapapun tidak bisa dibagi dengan nol, nah dari kasus diatas bagaimana caranya agar pesan error tersebut tidak ditampilkan ? cara yang akan kita lakukan adalah menyembunyikannya dengan conditional formating.

Silahkan anda buat lembar kerja seperti tampak pada gambar diatas, lakukan perhitungan untuk kolom rata-rata pada cell D4 dengan formula :

=ROUNDDOWN(C4/B4,0)

Rumus diatas melakukan pembagian dan hasilnya dibulatkan kebawah 0 decimal dibelakang koma dengan fungsi ROUNDDOWN, kemudian lakukan peng-copy-an untuk baris dibawahnya.
Tahap berikutnya kita sembunyikan pesan error dengan conditional formating
1. blok cel d4 sampai d7 (kolom rata-rata)
2. Klik icon conditional formating, pilih manage rule (Pada Excel 2003 Pilih Menu FOrmat – Conditional Formating)

3. Pada kotak dialog Conditional Formating Rule Manager, klik New Rule

4. Pada kotak dialoh new formating rule pilih use formula to determine wich cell to format

5. kemudian pada kotak Format value where this formula is true isi dengan
=ISERROR(D4)
6. Kemudiak klik tombol format
7. Pada tab font pilih warna font yang senada / sama warnanya dengan warna bacground cell dalam hal ini hijau

8. Klik OK pada semua kotak dialog sampai hilang kotak dialog
9. Sebagai hasil akhir maka anda akan melihat bahwa error #DIV/0 kini telah hilang !

Menghitung Kata Dalam Sebuah Cell

Berbeda dengan Microsoft Word sebagai pengolah kata, excel tidak memiliki fasilitas bahkan fungsi untuk menghitung kata,
Meskipun tidak terlalu penting untuk apa kita menghitung kata dalam sebuiah cell di excel namun suatu saat pasti kita membutuhkan cara bagaimana kita menghitung kata dalam Cell di excel.
Seperti telah disebutkan dimuka bahwa excel tidak memiliki fungsi atau menu untuk menhitung kata maka kita harus membuat formula nya sendiri sebagai contoh anda perhatikan lembar kerja dibawah ini :

Untuk menghitung kata seperti pada contoh diatas gunakan formula sebagai berikut :

=LEN(A1)-LEN(SUBSTITUTE(A1;” “;””))+1

Penjelasan :
Logika untuk menyelesaikan kasus menghitung kata adalah menghitung panjang karakter dari cell A1 dengan fungsi LEN, serta mengurangi hasilnya dengan menghitung kembali panjang karakter Cell A1 sambil menghilangkan karakter spasi dengan fungsi SUBTITLE dan hasilnya ditambah satu.
Sehinga apabila dirinci Formula diatas terdiri dari formula dengan perincian sebagai berikut :

1. Len(A1) menghasilkan angka 114
2. LEN(SUBSTITUTE(A1;” “;””)) menghasilkan angka 97
3. sehingga formula diatas akan menjadi (114-97)+1 = 18 kata !

Bagaimana mudah kan ?? dengan catatan penulisan tanda koma, titik dua dan tanda baca lainnya juga akan dihitung apabila menggunakan spasi sebagai tanda pemisahnya.