You've probably noticed

We What We Do

T.O.S

About Us


Expertise

We just share with you the tutorials that we ourselves have tested in the workplace and slightly refine it to make it easy for you.

Our Articles

Every article on this site is presented explicitly so that you can understand the purpose of each tutorial.

Efficiency

excelive.com provides examples of just for illustration only, and we do not guarantee that they can be used in all situations.

Contact

Feel free to contact us when u've got some question, and we'll try our best to find out the answer for you.

READ MORE

Do you have any questions ?
Feel free to contact us !

GET IN TOUCH

Class


OPEN SITEMAP

Let's Get Started

choose what suits you

Cara Mudah Memberikan Nilai Properti Berbeda Setiap Objek VBA Berdasarkan Kriteria

ganti atribut objek VBA

Sebenarnya penggunaan istilah Objek VBA hanya saya gunakan dalam mempermudah setiap pembelajaran Excel VBA. Dan seperti yang sudah diketahui bersama bahwa di dalam Microsoft Excel, objek-objek VBA seperti Label, ListBox, CommandButton, Frame, hingga SpinButton berada dalam kotak sebuah kotak yang bernama ToolBox yang berisi berbagai macam Controls seperti yang sudah saya sebutkan. Lihat gambar berikut
Objek VBA

Jadi, agar tidak menimbulkan makna yang ambigu - maka kata Controls saya ganti penyebutannya menjadi Objek VBA. Mudah-mudah hal ini bisa dimaklumi :)

Ok, terlepas dari penggunaan serta penulisan istilah yang berbeda di atas, untuk tutorial kali ini xcelive akan share sebuah script Macro VBA yang sangat efektif untuk memberikan atribut atau properti yang berbeda-beda dari setiap objek VBA yang ada dalam UserForm.
Dan sebelum mulai belajar membuatnya, ada baiknya anda baca terlebih dahulu tutorial terkait berikut:
Cara menggunakan properti TAG dalam Macro VBA
Menggunakan perintah Looping dalam script VBA

Skenario yang akan kita coba uji kali ini adalah seperti berikut
Merubah properti warna latar 1 objek TextBox yang sesuai dengan kriteria yang saya inginkan dari beberapa TextBox yang terdapat di dalam UserForm

Dari skenario tersebut, maka setidaknya kita harus menyiapkan terlebih dahulu design yang sesuai agar berhasil diterjemahkan menggunakan bahasa Macro VBA yang bisa dibilang cukup sederhana. Design yang saya maksud kurang lebih akan tampak seperti gambar di bawah ini
design objek VBA
Dari design di atas, saya menggunakan 7 buah TextBox utama yang nantinya akan berubah, sementara 1 buah TextBox lainnya berfungsi untuk mengisikan kriteria berupa angka 1 sampai 7 yang mewakili Objek VBA TextBox1 sampai TextBox7, dan yang terakhir saya menggunakan sebuah CommandButton untuk menjalankan kode yang saya tulis berikut ini :
Private Sub CommandButton1_Click()
Dim obj As Control
For Each obj In UserForm1.Controls
    If obj.Name Like "TextBox" & TextBox8 Then obj.BackColor = &HC0C0&
Next
End Sub
Baris kode di atas yang berfungsi untuk merubah warna latar dari TextBox yang memiliki Name yang sesuai dengan kriteria yang dimasukkan adalah obj.BackColor = &HC0C0&, sementara TextBox lainnya tidak akan mengalami perubahan warna latar. Hanya saja ketika anda mencoba mengganti kriteria beberapa kali, TextBox yang sudah berubah warna backgroundnya tidak akan kembali ke warna asal. Coba perhatikan demonstrasi berikut :
Ganti BackGround Objek VBA

Loading...
Untuk mengatasi "error" ini, maka tambahkan baris kode berikut dan letakkan di baris bagian atas sebelum eksekusi perubahan warna latar dilakukan
For i = 1 To 7
    Controls("textbox" & i).BackColor = &H80000005
Next
Sehingga jika kedua kode di atas digabungkan akan menjadi tampak seperti berikut
Private Sub CommandButton1_Click()
Dim obj As Control
Dim Pesan As Boolean

For i = 1 To 7
    Controls("textbox" & i).BackColor = &H80000005
Next
For Each obj In UserForm1.Controls
    If obj.Name Like "TextBox" & TextBox8 Then obj.BackColor = &HC0C0&
Next
End Sub
Untuk melihat hasilnya, silahkan anda coba tekan tombol yang terdapat di menubar Visual Basic Editor, dan coba silahkan ganti nilai di kotak kriteria.

Selamat...anda sekarang sudah bisa memberikan warna latar belakang yang berbeda setiap TextBox yang sesuai dengan kriteria. Jika anda bertanya, bagaimana cara menyisipkan sebuah kotak pesan ketika Textbox tidak ada yang sesuai dengan kriteria, maka struktur kode Macro VBA akan sedikit banyak mengalami perubahan. Untuk hasil akhirnya akan terlihat seperti berikut :
Pilih Objek dengan paeameter GOTO

Hasil di atas dikerjakan dengan menggunakan parameter GoTo, dan untuk pembahasannya akan saya posting di edisi berikutnya. Jadi, silahkan klik tombol Subscribe agar anda tidak ketinggalan tutorial xcelive berikutnya. See you soon


Loading...

Cara Mendeteksi Nilai Ganjil atau Genap Dengan Macro VBA Excel

Mencari angka ganjil genap dengan VBA

Jika ada cara yang mudah untuk dilakukan, ngapain harus menempuhnya dengan cara yang sulit?. Barangkali ini adalah sebuah kalimat pembuka yang sesuai untuk tutorial kali ini.

Ya, memang dalam Microsoft Excel ada sebuah pandangan atau pola berfikir bahwa kebutuhan mengolah angka harusnya bisa diselesaikan hanya dengan menggunakan Formula, akan tetapi jika dengan menggunakan cara ini tidak berhasil menyelesaikan masalah dalam hal mengolah angka tersebut, maka pilihannya akan jatuh pada Macro VBA, dan untuk pilihan Macro VBA bukan sebuah keharusan atau kewajiban. Mungkin gagalnya menyelesaikan masalah tersebut terletak pada ketidaktahuan pengguna semata. Sementara bagi beberapa pengguna Microsoft Excel lainnya, mereka memiliki anggapan bahwa antara Formula Microsoft Excel dan Macro VBA adalah saling melengkapi.

Contoh yang paling simpel yang dapat dilakukan hanya dengan menggunakan Formula Excel adalah ketika kita ingin mendeteksi apakah nilai yang terdapat dalam sebuah sel berisi nilai ganjil atau genap. Menyelesaikan kasus seperti ini memang sangat cepat dan sangat mudah dilakukan dengan menggunakan Formula, karena Microsoft Excel sudah menyediakan formulanya.

Untuk mendeteksi nilai genap, maka formula yang digunakan adalah =ISEVEN, sementara untuk mendeteksi sebuah sel yang berisi nilai ganjil menggunakan =ISODD. Contoh penggunaannya seperti gambar berikut :
formula cek ganjil-genap
Kedua formula tersebut akan menghasilkan nilai TRUE jika sel tersebut memuat informasi yang terkait, dan akan menghasilkan nilai FALSE jika salah.

Sederhananya penulisan kedua formula di atas, ternyata tidak berlaku jika anda ingin mendeteksi nilai ganjil atau genap menggunakan Macro VBA. Dan setidaknya, anda harus sedikit memiliki modal pengetahuan tentang Logika IF jika ingin tetap melakukannya melalui Macro VBA. Untungnya saya sudah menyiapkan beberapa baris Macro VBA yang bisa anda coba untuk mendeteksi nilai ganjil atau genap.
Sub ganjil_genap()
For i = 3 To [a3].End(xlDown).Row
If Cells(i, 1) Mod 2 = 0 Then
    MsgBox "Sel A" & i & " (" & Cells(i, 1) & ") = Genap"
    Else
    MsgBox "Sel A" & i & " (" & Cells(i, 1) & ") = Ganjil"
End If
Next
End Sub
Loading...
Parameter penting dari script kode di atas terletak pada operator MOD. Salah satu jenis operator yang juga terdapat di formula Excel ini digunakan untuk membagi dua angka dan hanya mengembalikan sisanya sebagai hasil akhir, dengan kata lain operator ini saya fungsikan untuk mendeteksi nilai tersebut ganjil atau genap. Sementara untuk hasil yang didapatkan dari operator MOD adalah berupa angka. Lebih jelasnya, lihat bagaimana menggunakan Formula ODD dalam lembar kerja Microsoft Excel berikut :
Formula

Kenapa hasilnya berupa angka 0 dan 1 ? Bagaimana cara menghitungnya!!
Salah satu penyebabnya dikarenakan saya menggunakan angka 2 sebagai pembaginya, tujuannya adalah hasil yang nantinya didapatkan berupa dua angka saja, yakni angka 0 dan 1. Bagaimana cara kerja operator atau formula MOD ini?, Ok, kita ambil salah satu baris dari gambar di atas, sebagai contoh baris ke 4 yang memuat nilai 20, dan penyelesaiannya adalah seperti berikut :
=20-2*(pembulatan dari hasil(20/2)
=20-2*10
=20-20
=0
Sekarang mari kita coba dengan baris yang lain, ambil contoh baris ke-7 yang berisi angka 59, dan cara menghitungnya sama dengan cara di atas ya.
=59-2*(pembulatan dari hasil(59/2)
=59-2*29
=59-58
=1

Dalam script kode Macro VBA yang saya tulis di atas, syntax penulisan operator MOD tidak seperti ketika kita menulisnya di lembar kerja Microsoft Excel sebagai formula, ada sedikit perbedaan dalam hal menaruh angka yang ingin di bagi dan angka pembaginya - sehingga jika Formula Mod diterjemahkan ke dalam Macro VBA, maka syntax penulisannya akan menjadi seperti 59 Mod 2.

Dan akhirnya, hasil kode Macro VBA di atas yang sudah lulus uji akan tampak seperti tampilan di bawah ini :
ganjil genap Macro VBA

Selamat...anda sudah berhasil mengecek sebuah nilai ganjil atau genap yang terdapat di lembar kerja menggunakan Macro VBA excel. Semoga tutorial edisi kali ini membawa manfaat untuk anda. Jangan lupa untuk selalu mengunjungi situs ini secara berkala agar anda tidak ketinggalan tutorial Microsoft Excel lainnya. Gracias


Loading...

Panduan Membuat Formula Excel Dalam Mencari Ranking Sesuai Kelompok Tertentu & Bernilai Unik

ranking unik sesuai kondisi

Jika judul dari artikel ini terlalu panjang dan atau mungkin cukup susah dicerna, maka saya akan memecahnya kedalam beberapa point penting yang akan dibahas, antara lain; Membuat atau menentukan Ranking, Kondisi yang mempengaruhi hasil ranking, dan ranking bersifat unik atau tidak ganda. Tiga point penting inilah yang akan menjadi topik utama tutorial xcelive untuk kategori Formula.

Ranking adalah sebuah urutan yang mengidentifikasikan kedudukan sesuatu dalam sebuah kelompok. Umumnya, dalam menentukan ranking dipengaruhi oleh sebuah nilai berupa angka yang mewakili tinggi atau rendahnya kedudukan tersebut - tujuannya tidak lain adalah untuk mengetahui standing position dari waktu kewaktu. Dan salah satu lembaga yang seringkali menerapkan urutan kedudukan ini adalah lembaga pendidikan.

Ya, dalam dunia pendidikan, ranking dapat dijadikan sebagai salah satu acuan atau tolak ukur untuk mengetahui perkembangan peserta didiknya dari waktu kewaktu, bagaimana siswa atau siswi tersebut dalam menerima atau mencerna pelajaran yang diberikan selama berada di bangku sekolah atau kuliah - sehingga kedepannya, diharapkan dapat membantu pihak-pihak terkait dalam menentukan langkah yang tepat.

Baca artikel terkait lainnya
Ingin Menghasilkan Nilai Rata-rata yang Berbeda?
Menjumlahkan Nilai Meskipun Salah Dalam Menuliskan Format Angka
Solusi Handal Dalam Melakukan Pencarian Data

Dalam menentukan urutan kedudukan (baca:ranking) para peserta didik. Sekolah biasanya memberlakukan test atau ujian kepada mereka. Kemudian dari nilai-nilai yang terkumpul dari seluruh peserta didik, selanjutnya diolah untuk mengetahui siapa yang berada di kedudukan tertinggi atau terendah, sehingga siswa yang memperoleh nilai total tertinggi dalam setiap mata pelajaran yang diujikan, ia berkesempatan untuk mendapat kedudukan yang tertinggi pula, dan berlaku sebaliknya. Sementara untuk urusan mengolah data, hampir setiap sekolah mayoritas menggunakan aplikasi Microsoft Excel - karena aplikasi ini memiliki kemampuan untuk menghitung dan menentukan ranking dengan cepat, yakni dengan menggunakan Formula yang disebut dengan RANK.

Coba perhatikan daftar kumpulan nilai yang ada di salah satu lembaga berikut:
Daftar kumpulan nilai

Loading...
Jika anda memperhatikan dengan seksama kumpulan data di atas, dan jika anda memutuskan untuk mengetahui ranking peserta didik dengan dasar total nilai dengan menggunakan Formula RANK yang cukup fenomenal - maka, ada dua nama yang akan mendapat ranking yang sama. Ya, mereka adalah Bima dan Hilman, karena keduanya memiliki total nilai yang sama yakni 130. Sementara siswa yang akan dinobatkan sebagai Bintang Kelas adalah Farah, karena ia memperoleh nilai tertinggi diantara teman-temannya. Namun sayangnya, Farah bukanlah satu-satunya bintang kelas dalam daftar kumpulan nilai di atas, ada Aleya dan juga Dirly yang masing-masing meraih total nilai tertinggi di masing-masing tingkat.

Mungkin anda berfikir kalau harusnya data tersebut dipisah antar tingkat dan diletakkan dalam sheet atau lembar kerja yang berbeda sehingga lebih mudah dalam menentukan ranking. Ughh....sayangnya, format daftar kumpulan nilai di atas sudah paten dan tidak diperkenankan untuk mengubah format. Anda hanya diperkenankan mengisi Total Nilai serta menulis Rumus mencari ranking saja, tidak lebih dari itu. Memang sih terdengar agak sedikit memaksa, tapi dalam suatu kondisi tertentu mungkin memang begitulah adanya.

Nah, jika anda pernah atau barangkali sekarang sedang menghadapi problematika seperti cerita (fiksi) di atas, maka anda harus menanggalkan Formula RANK yang cukup fenomenal itu, dan beralih ke Formula COUNTIFs.

Apa.....
Bagaimana bisa Formula COUNTIFs digunakan untuk mencari ranking, ngigau ya...!!!

Hahaha...Nggak.
Memang formula RANK cukup efektif digunakan jika data-data yang terkumpul berada dalam satu kelompok, misal kelompok tingkat 7 berada di sheet yang berbeda dengan kelompok tingkat yang lainnya. Namun jika sudah terlanjur membuat data seperti tampilan di atas dan mungkin data tingkat dituliskan secara tidak berurutan, maka so pasti Formula RANK tidak akan mampu menjawabnya. Dan seperti yang saya tulis di atas, beralihlah ke formula COUNTIFs!

Jika masih belum percaya, silahkan anda tuliskan (copy-paste saja biar cepat) Formula berikut ke dalam sel D5, selanjutnya tinggal lakukan copy Cells ke sel yang lain.
FORMULA MICROSOFT EXCEL
=COUNTIFS($B$5:$B$13;$B5;$C$5:$C$13;">"&C5)+
COUNTIFS($B$5:$B5;$B5;C$5:C5;C5)


Singkatnya, formula di atas memiliki susunan sebagai berikut :
  1. COUNTIFS yang pertama berfungsi untuk menghitung berapa banyaknya jumlah sel yang memuat angka 7 dan total nilai lebih besar dari 141 (hasilnya = 0)
  2. COUNTIFS yang kedua memiliki fungsi menghitung berapa banyaknya sel yang memuat angka 7 dan total nilainya sama dengan 141 (hasilnya = 1)
  3. Jika kedua formula ini ditambahkan maka hasilnya adalah 1. dan seterusnya...

Perhatikan perbedaan hasil akhir dari kedua formula berikut:
Ranking siswa dengan formula COUNTIFS

Selamat... anda sudah berhasil mengganti Formula RANK dengan COUNTIFS untuk menentukan peringkat dalam sebuah kelas berdasarkan kelompok atau kategori tertentu. Semoga, tutorial ini bisa menambah wawasan serta membantu anda dalam mengolah data Microsoft Excel.
Jangan lupa, silahkan bookmark situs ini agar anda mudah mengunjungi kami lain waktu. Merci de votre visite


Loading...

Memberikan Warna yang Berbeda di Setiap Sel itu mudah! Dengan Menggunakan Conditonal Formatting

colorful background

Ini merupakan edisi lain dari excelive dalam membahas tutorial yang menggunakan Conditional Formatting, jadi semoga bisa menambah perbendaharaan serta variasi tutorial dan bisa menjadi salah satu pilihan situs alternatif terbaik untuk anda yang mengupas tuntas tentang kehebatan Microsoft Excel.

Oke, kita mulai dengan mengenal terlebih dahulu apa itu Conditional Formating.
Sesuai dengan namanya, jika diartikan ke dalam Bahasa Indonesia maka ia mempunyai makna "Memberikan format tertentu jika suatu kondisi terpenuhi". Kondisi-kondisi yang disediakan dalam fitur Conditional Formatting ditentukan berdasar sebuah aturan seperti:
  • Memformat semua sel berdasarkan nilai yang ada didalamnya
  • Memformat sel yang memiliki nilai tertentu
  • Memformat sel yang memiliki nilai terendah atau tertinggi saja
  • Memformat sel yang berisi nilai ganda atau unik
  • Menggunakan formula excel untuk menentukan sel mana yang akan di format

Dari beberapa pilihan aturan yang sudah saya sebutkan di atas, barangkali beberapa di antaranya sudah sering anda gunakan untuk menandai sel-sel yang sesuai dengan kebutuhan. Nah, untuk tutorial ini saya akan mengenalkan kepada anda cara menggunakan formula excel dan menggabungkannya ke dalam Conditional Formatting untuk memformat beberapa sel yang sesuai dengan kriteria.
Dan skenario yang akan kita coba jalankan adalah seperti berikut :
Memberikan warna sel yang berbeda antara sel yang memuat nilai ganjil dan genap dalam rentang atau range tertentu

Untungnya dalam Microsoft Excel sudah disediakan formula yang berfungsi untuk membaca nilai ganjil atau genap, sehingga membuat pekerjaan mewarnai ini menjadi lebih mudah. Dan formula yang dimaksud adalah :
  • =ISEVEN(alamat_range)
    digunakan untuk mengecek apakah sel berisi nilai GENAP
  • =ISODD(alamat_range)
    digunakan untuk mengecek apakah sel berisi nilai GANJIL

Sekarang saatnya mengaplikasikan formula-formula tersebut ke dalam lembar kerja Microsoft Excel, dan berikut langkahnya :
  1. Sorot beberapa sel, contoh sel A1 sampai dengan sel D10 yang akan diberikan efek warna, kemudian pilih ikon Conditional Formatting dan kemudian pilih New Rule
    Conditional Formatting Menu
  2. Di jendela "New Formatting Rule" pilih aturan "Use a formula...." kemudian isikan formula excel di kotak yang telah disediakan
    FORMULA MICROSOFT EXCEL
    =ISODD(A1)
    Lanjutkan dengan klik Format untuk menentukan warna
    Formula Conditional Formatting
  3. Ulangi langkah no.2 akan tetapi ganti formulanya menjadi seperti ini
    FORMULA MICROSOFT EXCEL
    =ISEVEN(A1)
    Kemudian lanjutkan klik tombol Format untuk menentukan warna yang berbeda dari langkah ke 2
  4. Tambahkan aturan baru yang berisi formula =IF(A1="";1;0) akan tetapi jangan berikan format cells, hal ini bertujuan agar sel yang kosong tidak ditandai sebagai nilai genap. Jangan lupa juga klik atau centang bagian "Stop If True" untuk aturan ini.

Waktunya untuk menguji warna-warna ini apakah berlaku jika sel berisi angka genap atau ganjil?. Dan berikut ini hasil akhir yang berhasil saya lakukan dengan Contional Formatting.


Bagaimana jika kriterinya bukan angka, melainkan sebuah teks?
Jawabannya tentu saja tidak bisa menggunakan kedua formula tersebut - karena keduanya hanya berlaku untuk membaca nilai berupa angka ganjil dan genap. Maka untuk menjawab pertanyaan ini, solusinya adalah dengan menggunakan formula lain, dan untuk kasus ini Formula IF adalah pilihan yang tepat.
=IF(A1="teks";TRUE;FALSE)
Formula ini adalah kebalikan dari formula sebelumnya. Untuk menggunakannya, silahkan baca kembali langkah-langkah di atas.

Selamat.... sekarang anda sudah mengetahui bagaimana memberikan warna yang berbeda di setiap sel dengan mudah dan cepat menggunakan fitur Conditional Formatting. Semoga tutorial kali ini cukup menyenangkan dan mudah di pahami. Danke


Loading...

Script VBA Untuk Membuat Kode Random (acak) Sederhana Secara Otomatis Seperti Token UNBK

kode random dengan excel

Saat ini khususnya di dunia maya, sudah bukan menjadi hal yang asing bagi kita ketika menemui sebuah tulisan acak yang terkadang susah untuk dibaca namun dituntut untuk menuliskannya kembali agar seorang pengguna bisa menikmati layanan yang disediakan. Tujuannya tidak lain adalah untuk memastikan bahwa yang menulis kode acak tersebut adalah seorang manusia.

Kode acak yang umumnya menggunakan kombinasi antara huruf dan angka yang muncul di layar secara otomatis (beberapa menyebut istilah ini dengan nama CAPTCHA), memang sangat berguna serta dapat digunakan untuk membantu kebutuhan verifikasi seperti ketika seorang pengguna akan membuat akun email baru, menulis komentar di sebuah situs, hingga akses ke halaman website tertentu yang membutuhkan model keamanan seperti ini - sehingga hanya mereka yang bisa menjawab tantangan kode captcha dengan benar yang bisa masuk. Hal ini tentu memiliki dampak positif yang pada akhirnya menghasilkan kualitas pengunjung yang benar-benar teruji serta dapat digunakan dalam mencegah masuknya spammer yang cukup meresahkan.

Selain contoh di atas, kode acak bisa juga digunakan untuk berbagai keperluan sesuai dengan kebutuhan dari masing-masing individu. Sebagai seorang programmer, mungkin kode acak digunakan sebagai sebuah password, atau bisa juga digunakan sebagai kode transaksi atau yang sekarang lagi nge-trend adalah digunakan sebagai TOKEN dalam Ujian Nasional Berbasis Komputer (UNBK) sebelum siswa/-siswi mulai mengerjakan soal-soal ujian, dan sebagainya.
Token UNBK

Terlepas dari kebutuhan masing-masing individu yang berbeda-beda, kali ini excelive akan share sebuah script kode Macro VBA untuk menghasilkan kode acak seperti captcha berbasis Microsoft Excel. Dan untuk edisi kali ini, saya hanya akan memberikan kode VBA yang berfungsi sebagai generator huruf acak saja. Namun sebelumnya, saya telah menyiapkan bahan-bahan untuk tutorial ini antara lain :
  • 1 UserForm dengan name UserForm1
  • 1 TextBox dengan name TextBox1
  • 2 commandbutton dengan name masing-masing CommandButton1 dan CommandButton2
Dan berikut ini tampilan akhir design dari ketiga bahan di atas yang bisa dibuat di jendela Microsoft Visual basic Editor yang bisa anda akses dengan menggunakan tombol Alt+F11
Form Captcha Excel
Setelah berhasil membuat desain seperti tampilan di atas (syukur-syukur jika anda bisa membuat lebih bagus lagi ...:), langkah selanjutnya yang merupakan point penting dari tutorial kali ini adalah memasang baris kode berikut ke dalam masing-masing commandButton.

COmmandButton1
Dim tx As String
Dim i As Integer
For i = 1 To 7
    tx = Chr(Int((26 - 1 + 1) * Rnd + 65)) & tx
Next i
TextBox1 = tx

COmmandButton2
Dim tx As String
Dim i As Integer
For i = 1 To 7
    tx = Chr(Int((26 - 1 + 1) * Rnd + 97)) & tx
Next i
TextBox1 = tx

Bagian penting dari kedua kode Macro VBA di atas antara lain :
  1. 1 to 7
    Panjang karakter (kode) yang ditampilkan maksimal berjumlah 7 karakter
  2. Rnd + 65
    Format karakter huruf kapital
  3. Rnd + 97
    Format karakter huruf kecil

Untuk memahami penggunaan karakter dalam pemrograman komputer, silahkan klik di sini untuk membaca tutorial yang terkait.

Langkah terakhir tentunya adalah menguji kode-kode tersebut dengan cara mengklik ikon di menu bar, atau bisa juga menggunakan tombol F5 yang ada di keyboard anda. Akan tetapi jika anda ingin mengintip hasil akhir dari tutorial ini sebelum memutuskan membuat sendiri, saya sudah menyiapkannya dalam tampilan berikut
generator huruf excel

Selamat...anda sekarang sudah berhasil membuat generator huruf acak dari Microsoft Excel yang bisa digunakan untuk berbagai kebutuhan. Dan jangan lupa, kunjungi situs ini secara berkala jika tidak ingin ketinggalan tutorial Membuat Kode Acak Kombinasi Antara Huruf Besar, Kecil, dan Juga Angka.
sebagai penutup untuk tutorial kali ini, saya berharap semoga materi yang saya tulis di atas memberikan manfaat untuk anda, dan semoga excelive bisa tetap memberikan karya terbaiknya serta bisa menjadi partner belajar Microsoft Excel terbaik anda. Vielen Dank


Loading...

Rahasia Menghapus Semua Baris yang Memiliki Kriteria Tertentu Hanya Dengan Sekali klik

del-row with vba excel

Sebuah dokumen Microsoft Excel yang saat ini sedang atau yang akan dibangun dari berbagai format atau model data, maka ia bisa dipastikan akan berada atau ditempatkan dalam sebuah kotak atau yang lebih umum dikenal dengan istilah sel, yakni sebuah kotakyang merupakan perpotongan antara kolom dan baris, dimana kolom diwakili dengan abjad sementara baris diwakili dengan angka, yang mana ketika keduanya digabungkan akan membentuk sebuah alamat sel.

Jadi, dengan ribuan kolom serta jutaan baris yang disediakan oleh Microsoft Excel, maka sudah pasti bahwa alamat-alamat tersebut tidak ada satupun yang sama, dan inilah salah satu kunci yang memudahkan seorang pengguna untuk mengolah data-data yang ada dalam dokumen Microsoft excel.

Dari penjelasan singkat di atas maka dapat ditarik sebuah kesimpulan sementara bahwasanya setiap sel atau range dapat kita jadikan sebagai kunci (baca:kriteria) yang dapat digunakan untuk mengatur, mengolah, atau memodifikasi isi dari alamat sel yang terkait. Inilah tugas kita sebagai seorang pengguna Microsoft Excel yang dituntut untuk lebih teliti dalam menentukan sel atau range mana yang akan dijadikan sebagai kriteria dan sel atau range mana yang akan diolah atau dimodifikasi.

Ada sebuah contoh kasus yang cukup menarik dan sedikit membuat saya jengkel - karena saya membutuhkan waktu sekitar kurang lebih 30 menit hanya untuk menyelesaikan masalah sederhana ini disebabkan ketidak-telitian dalam proses membaca alur perintah. Dan contoh kasus tersebut adalah :
Menghapus Semua Baris yang berisi sebuah data (kriteria) dengan menggunakan Macro VBA

Silahkan baca tutorial yang berkaitan dengan pembahasan kali ini
Menggunakan Looping For...Next
Menentukan posisi baris terakhir
Memahami konsep logika IF dalam Macro VBA

Ok, sebelum mulai melakukan percobaan, silahkan lihat data yang akan kita eksekusi berikut :
excel - hapus baris sama
Dari data di atas, terdapat dua buah kolom yang terdiri dari nama dan status, di mana kolom B yang memuat nilai x akan kita jadikan sebagai kunci untuk memodifikasi sel yang terkait dengannya, yakni berupa perintah untuk menghapus baris.

Ok, saya akan berikan contoh kode Macro VBA yang "salah" dalam membaca alur perintah yang telah membuat saya menghabiskan waktu beberapa menit untuk memahaminya.
Sub Salah()
a = Range("a1").End(xlDown).Row
For i = 2 To a
  If Cells(i, 2) = "x" Then
  Rows(i).Delete
End If
Next
End Sub

Dan berikut ini kode Macro VBA yang sudah saya revisi untuk melakukan perintah penghapusan baris jika sel-sel yang terdapat di kolom B memuat nilai x
Sub Revisi()
a = Range("a1").End(xlDown).Row
For i = a To 2 Step -1
  If Cells(i, 2) = "x" Then
  Rows(i).Delete
End If
Next
End Sub



Loading...

Apa yang membedakan antara kedua Kode Macro VBA di atas ?
Pada dasarnya perintah untuk menghapus baris sudah dibaca secara sempurna oleh Microsoft Excel, hanya saja keduanya memiliki perbedaan pada alur membaca perintah penghapusan, yakni; pada kode Macro VBA yang salah, dalam mengeksekusi perintah dilakukan dari baris atas ke bawah, Sementara untuk kode yang sudah direvisi dilakukan dari bawah ke atas.

Lantas, di mana letak kesalahannya ?
Jika membacanya dari atas ke bawah, dan pada saat proses hapus baris dilakukan - maka baris setelahnya akan naik dan mengisi baris sebelumnya yang telah terhapus, dan jika baris tersebut berisi kriteria yang dimaksud maka otomatis ia akan dilewati (inilah letak kesalahannya) dan proses pencarian akan dilanjutkan ke baris setelahnya.
sebaliknya, jika membaca dari bawah ke atas maka saat proses hapus baris dilakukan, ia tidak akan mengubah stuktur data yang ada di atanya, dan kode akan tetap membaca membaca kriteria yang yang ada di atasnya sampai dengan batas baris yang telah ditentukan.

Untuk melihat perbadaan antara kedua kode di atas, silahkan perhatikan demontrasi yang sudah saya siapkan untuk anda berikut ini
Perbedaan kode Macro VBA - Hapus Baris

Selamat...sekarang anda sudah berhasil mengetahui dan semoga memahami dengan baik bagaimana cara menghapus semua baris yang berisi nilai tertentu dengan tepat dan benar. Silahkan jadikan situs ini sebagai situs favorit anda dalam belajar Microsoft Excel. Ciao Adios.

Bonus untuk anda, model lain kode Macro VBA menghapus baris yang memiliki kriteria tertentu dengan menggunakan parameter AutoFilter
Sub Bonus_Hapus_Baris()
Application.ScreenUpdating = False
    With ActiveSheet.Range(Cells(1, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2))
        .AutoFilter Field:=1, Criteria1:="x"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

Loading...

Teknik Ini Bisa Anda Gunakan Untuk Menghitung Jumlah Kata Yang Muncul Dalam Setiap Sel

formula menghitung kata

Sebenarnya sudah banyak tutorial tentang bagaimana cara menghitung jumlah karakter atau kata di Microsoft Excel yang beredar di dunia maya, baik yang menggunakan formula excel, Macro VBA, dan bahkan ada yang menggunakan kombinasi antara keduanya. Semuanya memiliki hasil akhir yang sama, yakni menghitung berapa banyaknya kata tertentu yang muncul dalam sebuah sel.

Dengan hanya menggunakan modal 2 buah formula yang dikombinasikan dengan beberapa operator matematika, kita sudah bisa melihat hasil dari jumlah karakter (huruf) atau kata yang dimaksud. Dan inilah gabungan formula yang saya maksud :
Fungsi Microsoft Excel
=(LEN(A1)-LEN(SUBSTITUTE(A1;"excel";)))/LEN("excel")
Terjemahan dari baris kode di atas secara sederhananya adalah seperti berikut :
Menghitung jumlah karakter yang ada di sel A1 kemudian di kurangi dengan jumlah karakter SELAIN kata yang ingin dicari (termasuk spasi), dan langkah terakhir adalah membaginya dengan panjang karakter atau kata yang diinginkan. Bingung?? bagaimana jika diterjemakan ke dalam skema gambar berikut :
hitung kata

Dari sekian banyaknya situs atau blog yang memposting artikel atau tutorial tentang cara menghitung jumlah kata di excel, beberapa di antaranya menggunakan formula di atas meskipun ada beberapa di antaranya yang menggunakan kombinasi formula yang berbeda - namun tetap menghasilkan nilai yang sama. Dan jika anda memasukkan kata kunci "cara menghitung jumlah kata di excel" di google, anda akan di berikan jawaban atau sekitar 180 ribuan website yang memuat informasi terkait kata kunci yang anda ketikkan dan excelive.com termasuk di dalamnya. Lihat hasil pencarian dengan kata kunci yang saya lakukan berikut
hasil pencarian google

Bagaimana hasilnya jika menggunakan kata pencarian dalam bahasa inggris?!... silahkan anda temukan sendiri jawabannya.

Beberapa paragraf yang saya tulis di atas boleh dikatakan sebagai pembuka dari tutorial inti kita kali ini, dengan tujuan agar anda lebih paham menggunakan kombinasi beberapa formula untuk mendapatkan hasil yang diinginkan. Dan tentu saja saya tidak ingin memberikan atau berbagi sesuatu yang sudah ada dan sangat banyak dibagikan (posting) oleh website lainnya.

Apa yang akan saya share kali ini adalah cara alternatif lain dalam menghitung berapa banyaknya kata atau karakter yang muncul dalam sebuah sel dengan menggunakan Custom Function (UDF - User Defined Function), yakni sebuah fungsi atau formula yang di buat sendiri dengan menggunakan bantuan Macro VBA. Dan berikut ini langkah-langkahnya:
  1. Buka aplikasi Microsoft Excel jika belum aktif
  2. Setelah aplikasi terbuka, silahkan tekan kombinasi tombol Alt+F11 di keyboard anda untuk langsung menuju jendela Visual Basic Editor
  3. Di jendela Visual Basic Editor, awali dengan membuat module baru dengan cara klik menu Insert kemudian klik menu Module
    insert module
  4. Setelah berhasil membuat module baru yang masih kosong, langkah selanjutnya adalah ketikkan (copy-paste aja biar cepat...!) kode berikut ini di dalamnya
    Function Hitung(xRange As Range) As Long
    kata = Application.InputBox("Kata yang ingin di cari", "Cari apa ya?")
    
        For Each xSel In xRange
            hasil = (Len(xSel) - Len(Replace(xSel, kata, ""))) / Len(kata)
        Next xSel
        Hitung = hasil
    End Function
    insert custom function
  5. Tutup jendela Microsoft Visual Basic Editor dengan menekan tombol X yang ada di pojok kanan atas jendela aplikasi ini atau bisa juga menggunakan kombinasi tombol Alt+Q di keyboard anda agar langsung menuju ke aplikasi Microsoft Excel.

Ok, sekarang saatnya kita menggunakan fungsi baru yang tidak di miliki oleh Microsoft Excel yang bernama Fungsi Hitung. Namun sebelum menggunakannya, silahkan tulis beberapa kata atau kalimat di sel A1 atau sel lainnya kemudian silahkan ketikkan formula baru ini di sel B1 atau sel lainnya, dengan syntax penulisan seperti berikut : Fungsi Microsoft Excel
=Hitung(A1)
Dan inilah hasil akhirnya :
udf-hitung kata di excel
Loading...
Sekarang anda sudah mengetahui bagaimana teknik lain untuk menghitung jumlah kata atau karakter yang terdapat di sebuah sel dengan menggunakan fungsi custom. Terimakasih anda sudah berkunjung di portal pembelajaran Microsoft Excel bersama excelive.com. Silahkan bookmark situs ini untuk memudahkan anda ketika mengunjungi kami lain waktu. Gracias

Silahkan Gunakan Formula INDIRECT Jika Ingin Mengumpulkan Data Antar Lembar Kerja Secara Cepat

collecting data

Tujuan dari hadirnya Microsoft Excel adalah untuk mempermudah pengguna dalam mengolah data, sehingga diharapkan suatu pekerjaan dapat diselesaikan dalam waktu yang relatif lebih cepat. Ketidak-tahuan atau kurangnya memahami kegunaan formula Excel tentu saja mempengaruhi kecepatan proses menyelesaikan sebuah pekerjaan, dan ini akan berakibat pada turunnya reputasi seorang pengguna, terlebih jika ia bekerja sebagai seorang admin yang dituntut untuk menyelesaikan pekerjaan dalam waktu yang cepat.

Memang tidak ada sebuah keharusan bahwa seorang admin harus menguasai seluruh formula yang tersedia di dalam Microsoft Excel, akan tetapi jika bekerja sebagai seorang admin maka setidaknya menguasai sekitar 15% sampai 20% saja bisa dibilang lebih dari cukup, atau sekitar 50-an Formula dari total sekitar 340 Formula yang tersedia di Microsoft Excel versi 2007 yang terbagi dalam kategori sebagai berikut :
No Kategori Jumlah Formula
1 Financial 53
2 Date & Time 20
3 Math & Trig 60
4 Statistical 83
5 Lookup Reference 18
6 Database 12
7 Text 12
8 Logical 7
9 Information 17
10 Engineering 39
11 Cube 7

Formula yang akan di bahas pada tutorial ini ada pada bagian kategori Lookup & Reference dan berada satu kelas dengan formula INDEX, MATCH, VLOOKUP, serta Formula Lookup lain yang umum digunakan.

Apa sebenarnya fungsi dan kegunaan dari formula Indirect ini ?
Secara umum kegunaan formula ini adalah membaca referensi sel dari sebuah teks yang diberikan. Silahkan lihat tabel data yang saya miliki berikut ini :
data Indirect excelive
Sekarang coba ketikkan formula INDIRECT seperti berikut dan letakkan di sel D1
FORMULA MICROSOFT EXCEL
=INDIRECT("C2")
Hasil yang didapatkan dari formula di atas adalah kata "selasa" hal ini disebabkan karena formula diatas "dipaksa" untuk membaca isi dari sel C2. Sementara penulisan alamat sel harus di apit dengan tanda petik, yang menandakan bahwa nilai tersebut adalah sebuah teks sesuai dengan aturan yang diberikan oleh formula INDIRECT yang hanya bisa menerima referensi berupa teks atau string.

Model formula INDIRECT lain yang bisa digunakan adalah seperti ini
FORMULA MICROSOFT EXCEL
=INDIRECT("Sheet2!C2")


Loading...

Seperti pada penulisan formula sebelumnya, hanya saja pada bagian ini formula akan membaca sel C2 yang terdapat di lembar kerja Sheet2. Hal ini cukup efektif digunakan ketika anda menginginkan hasil yang ingin ditampilkan berada di sel atau lembar kerja lain.

Konsep penulisan rumus di atas dapat diperluas lagi guna menampilkan ringkasan semua data di semua lembar dalam buku kerja Excel, akan tetapi memiliki beberapa keterbatasan karena nilai-nilai dalam formula di atas ditulis secara langsung dalam satu rangkaian yang tentunya tidak bersifat dinamis, serta keharusan untuk menuliskan semua data dalam format yang sama antar lembar kerja.

Oke sekarang kita akan coba untuk memodifikasi formula ini ke tingkat yang lebih lanjut lagi, di mana skenario yang ingin saya coba jalankan adalah seperti ini :
Mengumpulkan dan kemudian menjumlahkan nilai-nilai yang berada di lembar kerja lain dalam satu lembar kerja berdasakan kriteria yang sudah ditentukan.

Coba perhatikan beberapa data yang saya miliki dalam tiap lembar kerja Microsoft Excel Berikut :
kumpulan nilai antar sheet

Agar tidak bolak-balik membuka antar lembar kerja untuk melihat nilai setiap siswa, maka saya berencana untuk mengumpulkannya ke dalam satu lembar kerja agar lebih enak dipandang dan mudah dalam pengolahannya.
Dan format lembar kerja sebagai tempat berkumpulnya nilai-nilai tersebut kurang lebih tampak seperti ini
rekap kumpulan nilai

Ok, tugas yang harus dilakukan adalah menuliskan rumus atau formula yang terdapat di kolom B hingga kolom D. Sementara untuk mengisi kolom E (Total Nilai) saya yakin 100% anda pasti dapat dengan mudah menyelesaikannya.

Untuk menyelesaikan kasus ini, dalam rangka mengumpulkan nilai ke dalam satu lembar kerja, saya hanya akan menuliskan satu buah rumus saja yang saya letakkan di sel B4 untuk selanjutnya tinggal di copy-paste saja ke sel-sel yang diinginkan. Dan formulanya adalah sebagai berikut :
FORMULA MICROSOFT EXCEL
=SUMIF(INDIRECT("'"&B$3&"'!A:A");$A4;INDIRECT("'"&B$3&"'!B:B"))
Secara singkatnya formula di atas dapat dijabarkan sebagai berikut :
  • B$3 adalah Nama Lembar kerja yang berisi nilai
  • A:A adalah nama kolom yang memuat nama siswa
  • $A4 adalah sebuah kriteria berupa nama siswa, dan
  • B:B adalah nama kolom dalam sheet yang perlu dijumlahkan.
Dari penulisan beberapa alamat sel di atas, terdapat beberapa yang dituliskan menggunakan acuan semi absolut, hal ini bertujuan agar nantinya ketika formula ini di copy-paste ke sel lain, alamat yang dimaksud tidak ikut berubah.

Dan seperti biasa, untuk melengkapi setiap tutorial yang disediakan di excelive.com, saya akan menampilkan hasil akhirnya seperti berikut :
Menggunakan Formula Indirect Untuk Mengumpulkan Data

Finish....
Loading...


Terimakasih anda sudah berkunjung dan belajar bersama kami. Jangan lupa silahkan kunjungi kami lain waktu untuk menemukan kehebatan Microsoft Excel lainnya bersama excelive.com. Adios

Coming Soon Tutorial
Stay Tuned For Something Awesome
Around here we don't look backwards for very long. we keep moving forward, opening up new doors and doing new things
because we're curious and keeps leading us down new paths. so stay with us, coz we are still working and designing the best tutorial for you ...

stay tuned

Our Services


design

all the design of each tutorial has passed the test for excel 2007 up

installation

every each codes on this site are provided "as is" and are easy to install

youtube

in order to facilitate you in learning excel, you can visit our channel here

download

to complete all excel lessons, we have provided a download link for you

Contact Us


EXCELIVE.com
is a blogger resources site who provides
best excel tutorial based on daily needs.
The main mission of excelive is to share
our little experience in excel
to deliver best time killer for your excel issues.
Singhasari Malang 65153
East Java

Interested for our works and services?
Get more of our update !

end of page