Cara Membuat Sel hanya bisa di isi dengan angka di Excel

Rumusexcel.com - Dalam kondisi tertentu terkadang kita membutuhkan untuk membuat sel yang hanya bisa di isi dengan angka saja, tujuannya supaya hanya angka saja yang bisa dimasukkan dalam sel tersebut, misalnya saja pada entry data ID Pelanggan, Nomor Telepon dan sebagainya.
Untuk membuat sel hanya bisa di isi angka saja di Microsoft Excel caranya pun gak sulit, hanya dengan memanfaatkan fungsi Data Validation, Tutorial mengenai Data Validation pun sudah pernah di bahas di blog ini.

1. Cara Menggunakan Data Validation di Excel
2. Membuat List dengan Data Validation di Excel

Jadi bagi yang sudah sering berkunjung ke blog ini tentu sudah tidak asing lagi dengan penggunaan Data Validation, Untuk menggunakan Data Validation agar bisa membuat sel tertentu hanya bisa di isi angka saja caranya adalah sebagai berikut:

1. Pilih Sel mana yang ingin di buat hanya bisa di isi angka saja, misal B2.
2. Klik Tab Data - Data Validation - Data Validation...





3. Pada Tab Settings - Validation Criteria
   
   + Pada Allow: pilih Whole Number.
   + Pada Data: pilih greater than.
   + Pada Minimum: masukkan angka 0.





4. Klik Pada Tab Error Alert dan isikan Box Title dan Error Message-nya, nantinya jika kita mengisi sel tersebut dengan data bukan angka, atau angka tapi hanya 0 saja maka pesan ini akan di tampilkan.





5. Selanjtunya klik OK, dan cobalah isi sel yang tadi telah di beri validasi (pada contoh sel B2) jika berhasil hasilnya akan seperti ini:

Ketika sel tersebut di isi data bukan angka (ABC) dan di enter maka akan tampil sebuah pesan seperti yang sebelumnya kita tulis, dan ketika sel di isi dengan angka maka tidak ada pesan apapun yang tampil.

Sekian tutorial Cara Membuat Sel hanya bisa di isi dengan angka di Excel, Selamat Mencoba dan Semoga Bermanfaat...

Cara Mencegah Input Data Ganda di Excel

Rumusexcel.com - Dalam mengerjakan proses input data atau entry data dengan Microsoft Excel, terkadang kita mendapati data yang harus di buat unik (Primary Key) yang artinya data tersebut tidak boleh sama antara satu dengan yang lainnya, Misalnya saja untuk data ID Pelanggan, No Induk, Kode Produk dan sebagainya.

Tidak seperti pada Microsoft Access yang menyediakan pilihan Primary key, di Microsoft Excel tidak tersedia pilihan tersebut, untuk itu agar menghindari penginputan data ganda di Excel, kita bisa memanfaatkan fungsi Data Validation, sehingga nantinya ketika kita memasukkan atau menginputkan data pada sel, maka akan langsung di validasi, jika data tersebut belum ada maka akan di tampilkan dan jika data yang dimasukkan ternyata sudah ada maka akan tampil pesan bahwa data tersebut sudah ada, supaya tidak di masukkan lagi.

Berikut cara mencegah input data ganda atau duplikat data dengan menggunakan data validation:

1. Pilih Range (sel-sel) mana saja yang di validasi, misalnya saja paaa sel A2:A10, maka blok sel tersebut.
2. Pilih Tab Data - Data Validation - Data Validation.





3. Pada Tab Settings - Validation Criteria
   
   - Pada Allow: pilih Custom
   - Pada Formula: masukkan rumus =COUNTIF($A$2:$A$10;A2)=1





4. Selanjutnya klik pada tab Alert, isi bagian Title dan Error Message-nya, Pesan error Message ini kan di tampilkan bila data yang di masukkan ternyata sudah ada alias sudah di input sebelumnya.






Selanjutnya klik OK, dan sekarang pada sel A2:A1 sudah di terapkan validasi data ganda, jika pada sel tersebut di masukkan data ( angka maupun teks) dan ternyata data tersebut sudah ada maka akan di tolak dan akan tampil pesan ada data ganda.

Sekian Tutorial Cara Mencegah Input Data Ganda di Excel, Selamat Mencoba dan Semoga bermanfaat...

Cara Menggunakan Fungsi HYPERLINK di Excel

Rumusexcel.com - Dalam pembuatan sebuah data di Excel, sering kali kita bekerja dengan beberapa worksheet bahkan beberapa workbook sekaligus, Terkadang juga workbook yang ingin di buka tersimpan di internet maupun berada di Drive Komputer yang berbeda.

Untuk memudahkan pemanggilan workbook yang ingin kita buka, kita bisa memanfaatkan salah satu fungsi atau rumus di Microsoft Excel yaitu Fungsi HYPERLINK.

Fungsi HYPERLINK digunakan untuk membuka atau menampilkan dokumen atau workbook yang di simpan di server jaringan, internet maupun intranet, atau biasa juga sekedar untuk membuka sebuah link website.

Penulisan syntax fungsi HYPERLINK adalah:

=HYPERLINK(Link_location; [Friendly_name])

Link_location : merupakan link lokasi yang ingin ditampilkan atau di buka, bisa mengacu ke sel atau range tertentu dalam workbook Excel atau bisa juga mengarah ke File atau folder yang di simpan di Drive Komputer maupun mengarah ke sebuah URL atau link website.

Friendly_name : merupakan pilihan teks pintasan (shortcut) atau nilai numerik yang ditampilkan di sel. Friendly_name secara default ditampilkan dengan warna biru dengan garis bawah (underline), meski begitu kita juga biasa mengubah warna dan menghilangkan garis bawahnya. Jika Friendly_name diabaikan, maka sel akan menampilkan link_location sebagai teks pintasannya.

Contoh penggunaan Fungsi HYPERLINK Excel:

Contoh Pertama: membuka worksheet dalam workbook yang sama:

=HYPERLINK("[Book1]Sheet3!A1";"Sheet3 Sel A1")

Jika Teks Sheet3 Sel A1 yang ada di sel A5 di klik maka akan diarahkan ke Sel A1 pada Sheet3.

Contoh Kedua: membuka link website/internet.

=HYPERLINK("http://www.rumusexcel.com";"Rumus Excel")

Contoh Ketiga: membuka file contoh.xlsx dari website/internet.

=HYPERLINK("http://www.rumusexcel.com/contoh.xlsx"; "Contoh Excel")

Contoh  Keempat: membuka file contoh.xlsx yang di simpan di drive C.

=HYPERLINK("C:\contoh.xlsx";"Contoh Excel")

Contoh Kelima: membuka ]file contoh.xlsx dan menampilkan Sheet2 sel A1.

=HYPERLINK("[C:\contoh.xlsx]Sheet2!A1";"Contoh Excel")

Sekian tutorial Cara Menggunakan Fungsi HYPERLINK di Excel. Selamat Mencoba dan Semoga bermanfaat...

Fungsi Logika IF di Microsoft Excel

RumusExcel.com - Fungsi IF adalah fungsi logika dalam Microsoft Excel yang sering digunakan karena kemudahan penggunaannya untuk menyelesaikan pernyataan yang berhubungan dengan Logika. Untuk memahami tentang fungsi logika IF, maka kita akan mempelajari beberapa hal diantaranya:

1. Operator Relasi
2. Fungsi Logika IF 
3. Fungsi IF dengan Dua Tes Logika 
4. Fungsi IF Bercabang 

Berikut Penjelasan selengkapnya:

1. Operator Relasi

Untuk menggunakan fungsi logika di microsoft excel, terlebih dahulu kita harus mengetahui tentang operator relasi. Operator relasi merupakan operator untuk membandingkan dua atau lebih data dalam satu cell. Jika benar (True) maka nilainya 1 dan jika salah (False) nilainya 0.

Adapun jenis-jenis operator relasi yaitu:

1. Sama Dengan ( = ) 
2. Lebih Besar ( > ) 
3. Lebih Besar atau Sama Dengan ( >= ) 
4. Lebih Kecil ( < )
5. Lebih Kecil atau Sama Dengan ( <= ) 
6. Tidak Sama Dengan ( <> ) 
7. AND, OR dan NOT. 

2. Fungsi Logika IF

Fungsi Logika terkenal sebagai fungsi IF, yang kegunaannya untuk mendefinisikan / mengisi suatu Cell berdasarkan Cell yang lain  atau kriteria tertentu, adapun rumusnya adalah: 

=IF ( Logical Test; Value if True; Value IF False )

Contoh:
 

Pada kolom keterangan kita akan memasukkan rumus jika kolom hutang sama dengan kolom bayar maka akan muncul keterangan "Lunas" Jika kolom hutang sama tidak dengan kolom bayar maka akan muncul keterangan "Masih Hutang". Caranya adalah dengan memasukkan rumus berikut:
- Pada cell D2 masukkan rumus: =IF(B2=C2;"Lunas";"Masih Hutang Rp."&B2-C2)
- Pada cell D3 masukkan rumus: =IF(B3=C3;"Lunas";"Masih Hutang Rp."&B3-C3)
- Pada cell D4 masukkan rumus: =IF(B4=C4;"Lunas";"Masih Hutang Rp."&B4-C4)


3. Fungsi IF dengan Dua Tes Logika

Fungsi IF dengan dua tes logika ini adalah untuk menentukan data dengan menggunakan dua kriteria tes. Contoh Menghitung gaji karyawan:



 Dengan ketentuan:

• Jika pendidikan S1 dan Pengalaman kerja <3 tahun, Rp.900.000
• Jika pendidikan S1 dan Pengalaman kerja <=3 tahun, Rp.1.200.000
• Jika pendidikan D3 dan Pengalaman kerja <3 tahun, Rp.600.000
• Jika pendidikan D3 dan Pengalaman kerja <=3 tahun, Rp.850.000

Penulisan rumus untuk menghitung gaji karyawan adalah dengan masukkan rumus berikut pada cell D2;

=IF(AND(B2="S1";C2<3);900000;IF(AND(B2="S1";C2>=3);1200000;IF(AND(B2="D3";C2<3); 650000;IF(AND(B2="D3";C2>=3);850000;""))))

Selanjutnya lakukan drag drop (klik tahan geser) ke bawah mulai dari pojok kiri bawah pada kolom D2 untuk mengcopy rumus ke baris berikutnya (D3 s.d D4)


4. Fungsi IF Bercabang

Fungsi IF Bercabang adalah Fungsi Logika yang ditentukan dengan rang-range atau tingkatan tententu. Contoh Menghitung Nilai Ujian.

Penulisan Rumus untuk menghitung nilai ujian adalah sebagai berikut:
Pada Cell F4:

=AVERAGE(C4:E4)

Pada Cell G4:

=IF(F4>=80;"A";IF(F4>=66;"B";IF(F4>=56;"C";IF(F4>=46;"D";"E"))))

Pada Cell H4:

=IF(OR(G4="A";G4="B");"Lulus";IF(G4="C";"Mengulang";"Gagal"))

Selanjutnya lakukan drag drop ke bawah pada kolom Rata-Rata, Nilai dan Keterangan untuk mengcopy rumus ke bawah.

Sekian tutorial penggunaan Fungsi Logika IF di Microsoft Excel, Semoga Bermanfaat....

Cara Menggunakan Rumus VLOOKUP di Excel

RumusExcel.com - Rumus VLOOKUP sangat banyak kegunaannya, fungsi ini sudah ada sejak Excel versi awal atau generasi pertama sampai Excel versi terbaru.
Fungsi VLOOKUP adalah fungsi Excel yang digunakan untuk mencari dan mengambil data dari suatu tabel referensi berdasarkan nilai tertentu.
Seperti namanya VLOOKUP yang diambil dari singkatan vertical lookup yang berarti mencari data secara vertikal, karena di Excel juga terdapat fungsi khusus untuk mencari data secara horizontal yaitu HLOOKUP atau horizontal lookup.

Penulisan Rumus VLOOKUP adalah sebagai berikut:

=VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])

 Keterangan:

lookup_value	:	nilai yang akan dicari di kolom pertama tabel atau rentang.
table_array	:	tabel referensi yang ingin dicari datanya yang kolom pertamanya digunakan sebagai acuan untuk nilai lookup_value
col_index_num	:	nomor kolom pada table_array yang ingin di cari datanya.
range_lookup	:	pilihan untuk menentukan nilai yang persis atau yang mendekati.
		TRUE (1) untuk mencari nilai yang mendekati.
		FALSE (2) untuk mencari nilai yang sama persis
		Jika range_lookup dikosongkan atau tidak di isi maka otomatis dianggap sebagai TRUE.

Contoh penerapannya seperti gambar berikut ini:

Pada gambar diatas kita mencari data nama dan alamat berdasarkan nomor urut, di gambar tersebut terlihat sel C13 sebagai lookup_value atau data yang ingin dicari, data ini berdasarkan kolom pertama dari kiri pada tabel referensi dan tabel referensinya (tabel_array) adalah sel B5 sampai D9.

Pada sel C14 karena kita ingin mencari data nama maka pada col_index_num-nya kita masukkan angka 2 karena kolom nama terletak di kolom kedua dari tabel referensi.

Pada cel C15 karena kita ingin mencari data alamat maka pada col_index_num-nya kita masukkan angka 3 karena kolom alamat terletak di kolom ketiga dari tabel referensi.

Pada range_lookup-nya di masukkan pilihan FALSE agar menampilkan nilai yang sama persis, dan bila pada sel C13 kita masukkan angka yang tidak terdapat dalam kolom pertama tabel referensi misal angka 6 atau 7 maka hasilnya adalah #N/A. Berbeda jika kita mengganti FALSE dengan TRUE yang berarti menampilkan nilai dari yang mendekati, misal kita memasukkan angka 6 atau 7 maka yang ditampilkan adalah data dari angka 5  karena yang lebih dekat ke 6 atau 7 adalah angka 5 maka hasilnya adalah Firman Jakarta.

Rumus VLOOKUP bisa diterapkan pada satu sheet yang sama, atau bisa juga berbeda sheet baik dengan memasukkan alamat sel langsung atau bisa juga dengan membuat define name.

Fungsi VLOOKUP dalam satu Sheet

Untuk menggunakan rumus VLOOKUP dalam satu sheet atau pada sheet yang sama adalah sama seperti contoh yang sudah saya jelaskan diatas jadi gak perlu saya ulangi lagi ya caranya, karena pasti anda sudah bisa atau mengerti dari contoh gambar diatas :)

Fungsi VLOOKUP dari Data pada Sheet Lain

Untuk menggunakan rumus VLOOKUP dari data sheet yang lain atau beda sheet, misal data tabel referensinya adalah di Sheet1 dan kita ingin menampilkan/memanggil data tersebut dari Sheet2 atau Sheet3 dan sebagainya maka penulisan rumusnya adalah:

=VLOOKUP(lookup_value;Sheet1!table_array;col_index_num;[range_lookup])

Anda bisa mengubah nama Sheet1 dengan nama sheets dimana data tabel referensi ditempatkan. contohnya sebagai gambar berikut ini:

Fungsi VLOOKUP dengan Define Name

Selain menuliskan alamat tabel_array langsung pada rumus VLOOKUP seperti B5:D9 atau Sheet1!B5:B9 kita juga bisa menggantinya dengan define name atau menamai range tersebut. caranya adalah:

1. Blok semua data yang dijadikan sebagai tabel referensi, contohnya pada sel B5 sampai D9.
2. Arahkan cursor ke Name Box dan ubah namanya menjadi apa saja, misal DataPenting setelah itu tekan Enter untuk mengahirinya.

Untuk penerapannya tinggal ubah saja data tabel_array dengan define name (name box) tersebut. contoh diterapkan pada VLOOKUP beda sheet. menjadi seperti gambar berikut ini:

Dengan memanfaatkan fungsi define name ini, pemanggilan data tabel referensi jadi lebih mudah dan praktis karena bisa diterapkan di Sheet mana saja. Untuk contoh file latihannya bisa di unduh disini.

Sekian pembahasan tentang cara menggunakan rumus VLOOKUP di Excel, Semoga Bermanfaat...