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

Ingin Menghasilkan Nilai Rata-rata yang Berbeda? Mari Berkenalan Dengan Fungsi AverageA

menggunakan AverageA dalam ms. Excel

Dari sekian banyak formula yang ditawarkan Microsoft Excel, barangkali formula AverageA ada dalam daftar yang tidak masuk dalam kategori formula favorit, atau mungkin sangat jarang digunakan dalam mengolah data dalam lembar kerja Microsoft Excel.

Ya, dibandingkan dengan saudaranya, hasil yang didapatkan dari formula AverageA memiliki perbedaan yang cukup signifikan dan sangat tidak disarankan untuk digunakan jika ingin mencari nilai rata-rata yang sifatnya umum seperti, rata-rata nilai ujian, rata-rata gaji karyawan perusahaan, atau yang lainnya. Karena konsep dari AverageA ini adalah menghitung rata-rata dari semua nilai yang ada dalam argumen (range) yang terpilih dengan tanpa menghiraukan isi dari sel tersebut apakah berisi angka atau bukan.

Itulah salah satu faktor penting perbedaan antara formula Average dengan formula AverageA dalam mencari rata-rata nilai. Untuk lebih jelasnya, silahkan anda lihat perbandingan antara keduanya...
perbedaan nilai rata-rata
Secara penulisan, keduanya memiliki kesamaan namun memiliki perbedaan hasil akhir. Di mana formula Average hanya menghitung rata-rata dari setiap data yang berisi angka, sementara AverageA akan menghitung semua data yang ada dalam rentang yang akan dihitung dengan tidak mempedulikan apakah data tersebut berisi angka atau bukan.

Dari gambar yang saya miliki di atas, jika di hitung secara manual dengan menggunakan rumus matematika, maka penjabarannya kurang lebih seperti berikut:
Average : (7.50 + 6.00 + 7.00 + 9.20) / 4 = 7.43
AverageA : (7.50 + 6.00 + x + 7.00 + 9.20) / 5 = 5.94 

Sekarang anda sudah mengetahui bagaimana perbedaan antara formula Average dan AverageA dalam mencari nilai rata-rata. Semoga tutorial ini ada guna dan manfaatnya untuk anda. See you soon

Fungsi Vlookup - Hlookup Menghasilkan Nilai Error Jika Referensi Tidak Sama, Ini Buktinya!

formula error di microsoft excel

Salah satu fungsi Microsoft Excel untuk kategori lookup yang paling umum dan sering digunakan adalah fungsi VLOOKUP, dimana ia akan melihat dan membaca susunan tabel yang tersusun secara vertikal kemudian menampilkan hasil yang sesuai dengan kata kunci atau nilai yang dituliskan.

kata kunci yang biasa disebut dengan istilah lookup values atau nilai yang ingin dicari ini, sebenarnya tidak bisa berdiri sendiri melainkan membutuhkan bantuan dari sebuah tabel yang berisi data² lengkap dengan nilai² atau yang memuat sebuah frase seperti yang terdapat di nilai lookup values. Tabel ini biasa disebut dengan istilah tabel bantu.
coba perhatikan struktur data yang saya miliki berikut:
syarat menggunakan fungsi vlookup

Dari data yang saya miliki di atas nampak terlihat bahwa kata yang ingin saya cari (P02) terdapat di dalam tabel bantu dan sejajar dengan nilai ini atau di kolom sebelah kanannya terdapat informasi yang nanti akan ditampilkan sebagai hasil dari pencarian. Inilah syarat utama yang harus dimiliki agar fungsi VLOOKUP berhasil dijalankan, yakni data-data yang dimuat dalam tabel bantu setidaknya harus memiliki nilai yang sama dengan nilai yang akan dicari, kalau tidak demikian pasti yang akan didapatkan adalah nilai atau hasil error seperti #N/A.

Namun beberapa waktu yang lalu saya diminta untuk membantu menyelesaikan soal dari mata pelajaran TIK atau mungkin KKPI, entah kelas atau tingkat berapa, yang tampilan soalnya seperti berikut :
Soal mata pelajaran KKPI - TIK
Jika anda perhatikan soal di atas, siswa diminta untuk mencari Harga Batik yang mana tabel bantu sudah disediakan. Akan tetapi setelah diamati secara seksama, sepertinya ada ada ketidaksamaan antara kode yang ada di tabel utama (kata yang ingin dicari) dengan tabel bantu, ambil contoh nilai yang terdapat di baris ke-4, di mana seorang pemebeli bernama ERMALIA membeli sebuah batik dengan kode TB002, namun data-data yang terdapat di tabel bantu yang ada adalah kode TBN002 (nilai harga batik 1.200.000), Nah, di sinilah letak 'kesalahannya'.

Seharusnya kasus di atas dapat diselesaikan secara mudah dengan menggunakan formula HLOOKUP (namun saya yakin anda atau saya lebih suka jika menyelesaikannya menggunakan VLOOKUP, hehehe...), jika saja kode batik yang tertera adalah sama. Namun sayangnya tidak demikian.
Oke, anggaplah soal tersebut memang benar terdapat salah cetak, di mana nilai yang terdapat di kolom C4:C8 harusnya mengikuti nilai-nilai yang terdapat di range C11:E11 untuk nilai Kode Batik atau mungkin sebaliknya, maka formula yang cukup sederhana berikut bisa ditulis di sel D4 untuk mengetahui harga batik untuk kode yang dimaksud.
=HLOOKUP(C4;C11:E12;2)
Selanjutnya silahkan kumpulkan kumpulkan lembar jawaban dan berharap guru mata pelajaran anda membenarkan jawaban ini..hehehe.

Namun bagaimana jika soal ini memang seperti apa yang dicetak dalam buku mata pelajaran tersebut dan tidak ada kesalahan cetak ?!. Maka, sudah pasti jawaban menggunakan formula di atas adalah 100% SALAH - karena jawaban yang akan diberikan oleh Microsoft Excel adalah #N/A alias Not Available.

Lantas bagaimana? Formula apa yang harus digunakan untuk menyelesaikan soal ini? Apakah mungkin jika mengubah susunan Tabel Bantu secara vertikal kemudian menyelesaikannya menggunakan formula VLOOKUP bisa menghasilkan nilai yang ingin di cari ?!

Jawabannya adalah TIDAK... dan ini sudah terbukti. Silahkan anda coba sendiri utak-utik formula tersebut jika masih belum percaya.

Oke, karena kita sudah menyepakati bersama bahwa soal yang terdapat dalam buku tersebut memang benar alias tidak salah cetak, maka saya bisa menyelesaikan ini dengan menggunakan dua macam formula yang bisa anda gunakan. Namun sebelumnya saya akan berikan dan jelaskan formula inti untuk menyelesaikan kasus ini. Dan formula inti tersebut adalah sebagai berikut :
=MATCH("*"&RIGHT(C4;3);$C$11:$E$11;0)
Saya mengkombinasikan formula MATCH dan RIGHT untuk mengetahui keberadaan nilai yang sama berdasarkan 3 (tiga) nilai terakhir yang ada di sebelah kanan serta menggunakan bantuan WILDCARDS (*), yang mana formula ini akan menghasilkan nilai berupa angka dan nantinya akan kita gunakan sebagai referensi indeks kolomnya.
Jika kita pecah formula ini, maka akan tampak seperti berikut
  1. =MATCH
    Formula yang digunakan untuk mengetahui nilai yang memiliki kesamaan dengan apa yang akan di cari dan akan menghasilkan nilai berupa angka, dan angka inilah nanti yang akan kita gunakan untuk membaca indeks kolomnya
  2. "*"&RIGHT(C4;3)
    Ini adalah kombinasi antara Wildcards (*) dengan formula teks, dalam hal ini adalah formula RIGHT. Untuk kasus ini saya awali dengan mengecek nilai yang ada di sel C4 yakni sebuah nilai atau kata TB001. Sehingga bunyi dari formula ini adalah "membaca kata apa saja yang 3 angka di belakangnya memiliki nilai 001"
  3. $C$11:$E$11
    Merupakan posisi pencarian yang terletak di sel C11 hingga E11, saya gunakan alamat sel absolut ($) agar nanti ketika formula ini di copy tidak mengalami perubahan dalam membaca alamat range ini.
  4. 0
    Adalah pilihan nilai yang mesti di cantumkan dalam syntak penulisan formula MATCH, di mana nilai 0 memiliki makna hasil pencarian yang benar-benar cocok.
Coba lihat skema penyelesaian dan pembacaan untuk mencari hasil kode batik berikut:
kombinasi formula excel live

Setelah anda memahami cara membaca formula di atas, maka selanjutnya adalah menggabung dengan formula lainnya. Dan untuk kasus ini saya akan memberikan 2 (dua) cara dalam menyelesaikan soal tersebut.

Formula 1 - Match & Index
=INDEX($C$11:$E$12;2;MATCH("*"&RIGHT(C4;3);$C$11:$E$11;0))

Formula 1 - Match & Offset
=OFFSET($B$10;2;MATCH("*"&RIGHT(C4;3);$C$11:$E$11;0))

Selamat.... sekarang anda sudah berhasil mengetahui bagaimana caranya mencari nilai yang tidak berhasil dilakukan menggunakan formula VLOOKUP maupun HLOOKUP.
Jangan lupa kunjungi kami lain waktu untuk mengetahui kehebatan Microsoft Excel lainnya. Ciao Adios

3 Kode Macro VBA Untuk Menyembunyikan Kolom

menyembunyikan kolom

Kolom adalah salah satu bagian dari lembar kerja (Worksheet) Microsoft Excel yang disusun secara melintang vertikal dan ditandai dengan huruf A,B,C, dan seterusnya hingga abjad XFD. Jumlah maksimal dari jumlah kolom (16.384 kolom) mulai di kenalkan pada Microsoft Excel versi 2007, sementara untuk versi Microsoft Excel dibawahnya anda hanya akan diberikan sebanyak 256 kolom saja atau maksimal sampai dengan abdaj IV. Hal ini sejalan dengan perbedaan jumlah baris yang di miliki antara versi Microsoft Excel 2007 dengan versi sebelumnya.

Secara khusus Microsoft Excel memang ingin membedakan identitas antara kolom dan baris seperti yang telah saya singgung di atas, namun ternyata penulisan kolom tidak selamanya harus ditulis menggunakan abjad, ini jika kita menuliskannya sebagai baris kode Macro VBA yang disematkan pada parameter Cells. Lihat contoh berikut :
Sel A1 : Cells(1,1)
Sel B5 : Cells(5,2)
Sel Z8 : Cells(8,26)
dan seterusnya.

Pada umumnya ketika kita menulis alamat sel selalu di awali dengan penyebutan kolom kemudian dilanjutkan dengan baris, akan tetapi ketika ingin merujuk alamat sel dengan menggunakan Kode Macro VBA maka penulisannya diawali dengan baris dilanjutkan dengan kolom. Inilah pemahaman dasar yang perlu diketahui agar mudah mengikuti tutorial kita kali ini, yakni menyembunyikan kolom dalam lembar kerja Microsoft Excel.

Seperti biasa, kita membutuhkan sebuah skenario sederhana yang akan digunakan sebagai bahan untuk menguji kode Macro VBA yang akan kita buat, dan skenario kita kali ini adalah :
Menyembuyikan kolom mulai dari kolom ke-2 atau kolom B sampai dengan kolom ke-26 atau kolom Z yang berisi nilai-nilai yang ada di baris 2 berdasarkan kriteria yang terdapat di sel A1

Ok, dalam skenario di atas setidaknya ada point-point penting yang perlu diperhatikan, antara lain:
- Sel A1 adalah sebuah sel yang digunakan sebagai kriteria untuk menyembunyikan kolom.
- Isi sel yang akan di jadikan acuan untuk menyembunyikan kolom A sampai dengan kolom Z adalah baris ke-2.
- Kolom-kolom yang akan disembunyikan jika terdapat nilai yang sesuai dengan kriteria adalah kolom A sampai kolom Z.
Dan kira-kira seperti inilah data yang ada di dalam lembar kerja kita.


Tahapan selanjutnya adalah menulis kode VBA untuk menerjemahkan skenario di atas, dan berikut langkah-langkahnya :
  1. Buka jendela Microsoft Visual Basic Editor dengan menekan tombol pintas ALT+F11 di keyboard
  2. Silahkan buat sebuah module baru dengan cara klik menu Insert > Module, kemudian silahkan tulis (copy paste) beberapa kode berikut didalamnya
    Kode Pertama
    Sub Satu()
    kriteria = [A1]
    For Kol = 2 To Range("Z2").End(xlToLeft).Column
        If Cells(2, Kol) = kriteria Then
            Cells(2, Kol).EntireColumn.Hidden = True
        End If
    Next Kol
    End Sub
    Kode ini di awali dengan menentukan lokasi alamat sel yang dijadikan sebagai kriteria, yakni sel A1. Kemudian dilanjutkan dengan menentukan indeks untuk kolom yang di mulai dari indeks 2 sampai dengan kolom Z, dan memiliki sifat dinamis. Perintah ini ada di dalam variabel Kol
    Setelah nilai untuk indeks kolom berhasil ditemukan, selanjutnya adalah menjalankan logika IF yang gunakan untuk mencari nilai yang berada di sepanjang baris 2 yang ada di setiap kolom yang tertulis di dalam nilai atau variabel Kol, apakah sesuai dengan kriteria atau nilai yang ada di sel A1 atau tidak, jika sesuai maka kolom yang isi selnya sama dengan kriteria akan di sembunyikan.

    Kode Kedua
    Sub Dua()
    Dim Isi As Range
    For Each Isi In Range("B2", Range("Z2").End(xlToLeft))
        Isi.EntireColumn.Hidden = (Isi = [A1])
    Next Isi
    End Sub
    Berbeda dengan kode sebelumnya, kode ini menggunakan parameter For Each yang mana kode di awali dengan mendeklarasikan sebuah nilai Isi sebagai nilai range. Selanjutnya baru baris kode inti akan di jalankan yakni dengan membaca bahwa setiap sel yang berada di rentang B2 hingga Z2 apakah memiliki isi yang sama dengan kriteria yang ada di sel A1 apakah tidak. Tentunya jika sama dengan kriteria maka tentu anda tahu apa yang akan terjadi bukan...

    Kode Ketiga
    Dim rng As Range
    For Each rng In Range("B2:Z2")
        If rng = [A1] Then
            rng.EntireColumn.Hidden = Not (rng.EntireColumn.Hidden)
        End If
    Next
    kode ini memiliki struktur yang hampir memiliki kesamaan dengan kode kedua, hanya saja untuk eksekusi nilai menggunakan logika IF, juga terdapat sebuah nilai NOT yang mana ia memiliki fungsi ganda yaitu menampilkan kolom dan juga menyembunyikannya tanpa harus menggunakan kode lainnya.

    Bonus Kode - Clear
    Sub Bersih()
        Range("B:Z").EntireColumn.Hidden = False
    End Sub
    Sebenarnya kode ini adalah kode pelengkap saja, karena fungsi utamanya adalah untuk menampilkan kembali kolom-kolom yang sebelumnya disembunyikan. Tapi jika anda menggunakan Kode Ketiga, maka anda tidak memerlukan kode ini untuk menampilkannya
  3. Hingga langkah ini sebenarnya anda sudah bisa mencobanya dengan cara menekan tombol F5 atau klik ikon yang ada di menubar. Tapi jika anda tidak ingin menjalankannya lewat jendela Visual Basic Editor dan ingin melihat perubahan secara langsung di lembar kerja, maka anda bisa menambahkan beberapa objek Form Controls yang diletakkan dalam lembar kerja seperti contoh berikut :
    Controls excel live

Untuk menyempurnakan tutorial kali ini, saya akan memberikan contoh hasil akhir seperti tampilan berikut :
hide column excel live

Sekarang anda sudah tahu bagaimana caranya menyembunyikan kolom dalam lembar kerja Microsoft Excel yang sesuai dengan kriteria, semoga tutorial excel ini ada guna dan manfaatnya.
Jangan lupa silahkan anda subscribe agar anda tidak ketinggalan materi-materi terbaru Microsoft Excel lainnya bersama excel live. Gracias

Lakukan Cara Ini Untuk Mengedit Isi Sel Secara Langsung Dari ListBox

change value directly

Seperti yang sudah diketahui bersama bahwa daftar atau isi yang terlihat di dalam ListBox selain bisa ditulis secara manual, juga bisa ditulis dan berasal dari sebuah lembar kerja Microsoft Excel yang fungsi utamanya adalah untuk menampilkan informasi yang ada pada sebuah database.

Variasi dan cara-cara mengisi daftar ListBox sudah pernah dibahas tuntas di artikel-artikel sebelumnya, silahkan cek disini :
Mau Mengisi ListBox dari Bawah ke Atas? Begini Caranya
Dua Cara Menampilkan Isi Dari Listbox Ke Dalam Kotak Pesan
Berbagai Cara Mengisi Daftar ListBox
Menampilkan Informasi Nama Bulan Sebelumnya Dari Tanggal Saat Ini
Mengisi ListBox Dengan Data Spesifik Dari Sebuah Lembar Kerja
Menggeser Isi Data ListBox Ke Atas Atau Ke Bawah
Cara Mengisi ListBox Dengan Nilai Unik dari Data Ganda

Pada kesempatan ini, excelive tidak akan membahas bagaimana cara mengisinya akan tetapi bagaimana cara merubah isinya secara langsung melalui ListBox itu sendiri tanpa harus mengubahnya lewat Lembar kerja dan hasilnya akan di update setelah perubahan dikonfirmasi oleh pengguna.

Sebagai catatan, tutorial ini hanya berlaku untuk model pengisian ListBox yang berasal dari lembar kerja dan bukan ditulis secara manual.

Ok, pertama saya akan membuat data yang terdapat di lembar kerja Microsoft Excel yang nantinya akan mengisi sebagai daftar di dalam ListBox disertai juga objek ListBox dan CommandButton yang saya letakkan di lembar kerja

Fungsi dari objek CommandButton adalah untuk memanggil data yang ada di lembar kerja dan kemudian menempelkannya ke dalam ListBox. Untuk melakukan hal ini, saya menggunakan kode Macro VBA sederhana (warna merah) seperti berikut
Private Sub CommandButton1_Click()
ListBox1.Clear
For ISI = 1 To Range("A1").End(xlDown).Row
    ListBox1.AddItem Cells(ISI, 1)
Next
End Sub
  • Baris awal dari kode di atas saya fungsikan untuk membersihkan daftar yang ada di ListBox agar nantinya ketika pengguna meng-klik tombol ini lebih dari satu kali, isi dari ListBox tidak akan bertambah.
  • Pada baris selanjutnya saya menggunakan statemen For Next yang berisi nilai 1 sampai dengan baris akhir yang berisi data di sepanjang kolom A. Nilai ini saya masukkan dalam variabel ISI yang nantinya saya fungsikan ia untuk membaca baris sebuah sel
  • Parameter yang saya gunakan untuk mengisi Daftar di dalam ListBox ini adalah AddItem dengan mengacu pada isi sebuah sel dengan ketentuan nilai sel berisi baris dan kolom. Di mana nilai untuk baris saya ambil dari variabel ISI, sementara untuk kolom saya tuliskan dengan angka 1 - yakni indeks untuk kolom pertama (A).
Sampai pada baris ini anda sudah berhasil memanggil data yang ada di dalam lembar kerja ke dalam ListBox dan memiliki sifat dinamis, artinya ia akan secara otomatis mengupdate isi ListBox ketika anda menambah atau mengurangi jumlah baris (record) yang ada di lembar kerja anda.

Langkah selanjutnya adalah memberikan kode untuk ListBox, di mana skenario yang akan kita jalankan adalah seperti berikut;
Ketika pengguna meng-klik isi dari ListBox, maka sebuah kotak berupa InputBox akan muncul dan difungsikan sebagai tempat memasukkan nilai baru terhadap isi yang dipilih. Dan ketika pengguna menekan tombol OK, maka perubahan nilai akan langsung di terapkan di lembar kerja maupun daftar ListBox itu sendiri
Ada beberapa hal penting dalam memaknai dan menerjemahkan skenario di atas, antara lain :
  • Prosedur yang digunakan adalah Klik pada ListBox,
  • InputBox untuk melihat dan mengganti nilai, dan
  • Update data secara otomatis.

Setelah memahami konsep dari skenario di atas, langkah selanjutnya yang merupakan inti dari tutorial kali ini adalah merancang kode Macro VBA, dan hasil akhir tertulis seperti berikut (warna merah) :
Private Sub ListBox1_Click()
    nmr = ListBox1.ListIndex + 1
    Isi = ListBox1.List(ListBox1.ListIndex, 0)
    kotak = Application.InputBox"item yang anda pilih :", "ganti isi", Isi)

    If kotak = False Then Exit Sub
    Cells(nmr, 1) = kotak

    CommandButton1_Click
End Sub
Beberapa variabel penting dari sekumpulan baris di atas antara lain:
  • nmr berfungsi untuk mencari indeks baris dari isi yang terpilih dalam ListBox, kemudian di tambahkan nilai + 1. Hal ini dikarenakan pembacaan indeks baris di ListBox dimulai dari angka 0, sementara data yang tertulis di lembar kerja di mulai dari baris 1.
  • Isi saya gunakan untuk mengambil nilai (isi) dari item yang terpilih dalam ListBox.
  • Kotak berfungsi untuk menampilkan jendela InputBox yang secara otomatis akan terisi dengan nilai dari item yang terpilih di ListBox
  • Kode berikutnya akan menjalankan logika IF yang berfungsi untuk mengecek apakah tombol OK pada kotak InputBox di klik (pilih) atau tidak. Dan jika tombol CANCEL yang dipilih, maka kode yang akan di jalankan adalah perintah Exit Sub. Sementara jika pengguna menekan tombol OK, maka baris kode yang akan dijalankan mengisi perubahan isi sel dengan nilai atau data yang tertulis di kotak InputBox.
  • CommandButton1_Click adalah perintah yang sebelumnya telah diisi namun untuk kali ini saya gunakan untuk mengupdate hasil pembaruan data yang dilakukan.

Berikut adalah tampilan akhir dari tutorial kita kali ini
ganti isi ListBox

Catatan :
Kode di atas tidak berlaku jika daftar di ListBox di tampilkan dengan model urutan terbalik, atau dari baris terbawah ke baris atas. Silahkan lihat tutorial ini .

Sekarang anda sudah mengetahui bagaimana caranya merubah isi atau data yang ada dalam lembar kerja secara langsung dari ListBox dengan menggunakan bantuan InputBox.
Terimakasih anda sudah berkunjung di portal pembelajaran Microsoft Excel bersama excelive.com. Silahkan bookmark situs ini untuk memudahkan anda ketika mengunjungi kami lain waktu. Grazie

Mau Mengisi ListBox Secara Terbalik dari Bawah ke Atas? Begini Caranya

descending listbox

Ketika kita berbicara mengenai urutan data, maka kita mengenal istilah Ascending dan juga istilah Descending. Dua buah model cara mengurutkan data ini sangat penting ketika ingin mengetahui informasi dan mendapatkan nilai yang tepat, juga sangat membantu dalam memvisualisasikan data dengan lebih cepat dan lebih baik, menata dan menemukan data yang di inginkan, dan akhirnya membuat keputusan yang lebih efektif.

Secara default, bentuk pengurutan data yang paling umum digunakan adalah model pengurutan yang dimulai dari nilai terkecil ke nilai terbesar yang biasa kita sebut dengan istilah Ascending, atau jika data tersebut berupa string maka urutan ini akan mengurutkan teks dari huruf A ke Z. Selain mengurutkan teks atau angka, kita juga bisa mengurutkan tanggal dan waktu bahkan bisa juga kita melakukan pengurutan data berdasarkan warna sel. Model pengurutan Ascending ini pula yang digunakan secara default dalam menuliskan daftar di ListBox, hanya saja ia akan mengurutkan data-data yang berasal dari lembar kerja dimulai dari baris terkecil - dan tidak diurutkan berdasarkan abjad ataupun angka, kecuali kita menambahkan kode Macro VBA lainnya.

Coba perhatikan data di lembar kerja berikut, dan lihat pula hasil yang ditampilkan dalam daftar ListBox
susunan isi listbox
Dari gambar di atas dapat di tarik kesimpulan bahwa ListBox tidak bisa secara otomatis mengurutkan data dari nilai terkecil atau terendah ke nilai tertinggi atau terbesar, ia hanya menampilkan data dari urutan baris pertama ke baris terakhir.
Dan solusi sederhana jika ingin mengurutkan data seperti yang dimaksud di atas, maka cara cepatnya adalah dengan cara mengurutkan data yang ada di lembar kerja terlebih dahulu sebelum ditampilkan ke dalam daftar ListBox.

Kembali ke topik utama tutorial yakni cara mengisi daftar ListBox secara terbalik atau dari bawah (baris terakhir) ke atas (baris awal) atau yang lebih umum disebut dengan istilah Descending.
Perhatikan gambar berikut
susunan isi listbox terbalik
Jika kita perhatikan antara data yang tertulis di lembar kerja dengan daftar-daftar yang ada ListBox, keduanya memiliki susunan yang tidak sama, dimana daftar yang ada di ListBox mengambil data dari baris paling bawah terlebih dulu kemudian menuju data yang ada di baris atasnya.

Untuk melakukan teknik mengisi daftar ListBox secara terbalik, ikuti langkah berikut :
  1. Pastikan anda sudah membuat sekumpulan data yang ditulis dalam lembar kerja seperti contoh gambar di atas
  2. Langkah berikutnya silahkan buat sebuah CommandButton dan ListBox kemudian letakkan di dalam lembar kerja Microsoft Excel, atau bisa juga anda letakkan di dalam UserForm
    design isi listbox terbalik
  3. Klik ganda objek CommandButton yang sudah dibuat dilangkah sebelumnya untuk langsung menuju jendela Microsoft Visual Basic Editor, kemudian silahkan tulis (atau lebih tepatnya copy-paste) kode berikut ke dalam baris kode untuk prosedure CommandButton1_Click
    Dim Isi As Long
    Dim Data As Long
    ListBox1.Clear
    Data = Cells(Rows.Count, "A").End(xlUp).Row
    For Isi = Data To 1 Step -1
     ListBox1.AddItem Cells(Isi, 1)
    Next 
    
    • Kumpulan baris di atas diawali dengan mendeklarasikan nilai Isi dan Data, kemudian dilanjutkan dengan membersihkan isi daftar yang terdapat dalam listbox (ListBox1.Clear). Hal ini bertujuan agar nantinya ketika pengguna memanggil kode ini tidak menambahkan hasil yang sudah ada.
    • Selanjutnya adalah menentukan nilai untuk Data yang berfungsi untuk membaca baris terakhir yang terdapat dalam kolom A dengan cara melakukan pembacaan dari bawah menuju ke atas (xlUp). Silahkan ganti nilai "A" jika data-data tidak berada di kolom ini.
    • Baris kode yang berikutnya adalah mengisi statement Loop For Next yang ditandai dengan nilai Isi dengan cara membaca baris terakhir (nilai Data) menuju baris awal, kemudian dilanjutkan dengan mengisi daftar ListBox1 dengan menggunakan metode AddItem.
  4. Sebelum menguji kode yang sudah dibuat, pastikan terlebih dahulu posisi ikon Design Mode yang terdapat dalam tab Developer sudah dalam posisi disable. Kemudian silahkan anda klik tombol CommandButton untuk melihat hasilnya.
    descending listbox

Terimakasih anda sudah berkunjung di saluran materi pembelajaran Microsoft Excel excelive.com, mudah-mudahan anda dapat menemukan sesuatu yang bermanfaat di sini. Dan jangan lupa silahkan bookmark situs ini agar anda mudah mengaksesnya dikemudian hari. See you soon


Mencari dan Memberikan Warna Interior Pada Kata yang Terpilih

warna interior sel

Tutorial ini merupakan gabungan dari tutorial-tutorial sebelumnya dan salah satunya adalah yang membahas tentang cara menggunakan paremeter .FIND dalam kode Macro VBA dan menambahkan baris kode baru yang berfungsi untuk memberikan warna interior pada hasil pencarian yang ditemukan. Jadi, ada baiknya anda memahami isi materi dari tutorial sebelumnya.

Setidaknya ada 2 (dua) macam warna interior yang akan kita gunakan dalam tutorial kali ini, yakni warna background sel (interior) dan warna font. Kedua warna inilah yang nantinya akan kita kombinasikan dengan hasil pencarian. Dan sebelum memulai tutorialnya, saya akan berikan dulu kode penulisan beberapa warna standar (baca:konstan) yang digunakan dalam Macro VBA yang sangat mudah untuk diingat, yakni
vbWhite vbYellow vbCyan vbRed vbMagenta vbGreen vbBlue vbBlack

Kembali ke topik pembahasan.
Seperti biasa kita buat terlebih dahulu beberapa data di dalam lembar kerja Microsoft Excel yang diletakkan di sepanjang kolom A, yang mana nantinya akan kita berikan warna background sel maupun fontnya ketika berhasil ditemukan dengan bantuan kotak input.
Isi Lembar Kerja

Sekarang, saatnya kita mulai bekerja.
  1. Langkah pertama yang bisa anda lakukan setelah membuat data sederhana adalah dengan membuat sebuah objek Macro VBA berupa CommandButton yang diletakkan dalam lembar kerja yang digunakan untuk menampilkan jendela InputBox.
    CommandButton dalam lembar kerja
  2. Langkah selanjutnya adalah, klik 2x objek objek CommandButton yang telah dibuat pada langkah sebelumnya untuk langsung menuju jendela Microsoft Visual BAsic Editor. Namun sebelumnya, pastikan dulu bahwa objek Design Mode masih dalam posisi aktif
  3. Di dalam jendela kode yang berhasil dibuka, anda akan dihadapkan dengan beberapa baris perintah yang dimiliki oleh objek CommandButton yang didalamnya masih kosong
    empty code
  4. Sekarang isikan dalam baris kosong tadi dengan kode Macro VBA berikut
    On Error GoTo x
    Range("A1", Range("A" & Rows.Count).End(xlUp)).Font.Color = None
    Range("A1", Range("A" & Rows.Count).End(xlUp)).Interior.Color = xlNone
    Cari = Application.InputBox("Silahkan masukkan kata kunci yang ingin cari", ".find")
    
    If Cari <> False Then
      Set a = Range("A:A").Find(Cari, LookAt:=xlValue)
      Range(a.Address).Font.Color = vbWhite
      Range(a.Address).Interior.Color = vbRed
      MsgBox "Kata yang anda cari ada di sel " & vbCr & a.Address(False, False)
    End If
    
    Exit Sub
    x:MsgBox "Maaf. tidak ada hasil pencarian yang bisa ditampilkan"
    
    Penjelasan sederhana dari kode di atas adalah
    • Kode diawali dengan sebuah pernyataan error, yang mana ketika terjadi error (data tidak ditemukan) maka kode akan langsung lompat ke perintah x
    • Dua baris kode berikutnya adalah untuk mengembalikan warna font dan warna background sel ke warna asal.
      yang perlu diperhatikan adalah, ada perbedaan kode untuk warna default antara keduanya, yakni untuk font menggunakan perintah None, sementara untuk background sel menggunakan xlNone. Dan perintah untuk mengembalikan warna default tersebut hanya diberlakukan di sepanjang kolom A
    • Setelah warna dikembalikan ke bentuk asalnya, perintah selanjutnya yang akan dijalankan ada pada baris kode yang ditentukan oleh nilai Cari, yakni sebuah kode yang berfungsi untuk menampilkan jendela InputBox
    • Baris inti untuk memberikan warna pada sel terpilih diawali dengan statemen IF, yakni jika nilai Cari benar atau ketika pengguna menekan tombol OK, maka kode berikutnya akan dijalankan
    • Di dalam statemen IF, isi baris kode dimulai dengan menentukan terlebih dahulu nilai a yang berfungsi mencari nilai yang dimasukkan dari jendela InputBox di sepanjang kolom A. Kemudian di lanjutkan dengan mewarnai :
      - Font dengan warna putih
      - Background Sel dengan warna merah
      Selanjutnya memberikan infomasi kepada pengguna berupa kotak pesan yang berisi bahwa kata yang dicari terdapat pada sebuah sel
  5. Langkah terakhir yang harus dilakukan tentunya adalah langsung mengujinya dengan cara klik tombol CommandButton yang ada di dalam lembar kerja

Sekarang anda sudah berhasil mengetahui bagaimana caranya memberi warna interior pada sebuah sel atau sebuah teks yang berhasil ditemukan dari jendela InputBox. Semoga tutorial ini bisa memberikan manfaat kepada anda.
Terimakasih sudah berkunjung dan jangan lupa silahkan bookmark situs ini untuk mengetahui tutorial dan kehebatan Microsoft Excel lainnya. We'll be right back soon


Dua Cara Menampilkan Isi Dari Listbox Ke Dalam Kotak Pesan

dua cara

Salah satu objek Macro VBA yang sering digunakan para pengembang aplikasi berbasis Microsoft Excel adalah ListBox, bahkan boleh dikatakan bahwa objek yang satu ini pasti ada dalam setiap aplikasi. Ya, objek ini memiliki fungsi utama untuk menampilkan serangkaian nilai yang disajikan dalam sebuah daftar berbentuk kotak (List: daftar, Box: Kotak), yang mana pengguna dapat memilih isi dalam daftar tersebut dengan mudah.

Beberapa metode atau cara dalam mengisi daftar ListBox pun bervariatif, tergantung dengan kebutuhan si pengembang aplikasi. Silahkan anda cek beberapa link berikut untuk mempelajari beberapa cara dalam mengisinya :
  Berbagai cara dalam mengisi daftar di ListBox
  Mengisi ListBox dengan data-data yang sesuai dengan kriteria
  Mengisi ListBox hanya dengan nilai unik saja
atau bisa juga anda lihat daftar postingan yang berhubungan dengan ListBox berikut ini
  Label ListBox

Dari beberapa link (posting) yang saya paparkan di atas dapat diambil kesimpulan bahwasanya Excel memberi kita keleluasaan dalam mengolah data untuk menampilkan informasi yang kita inginkan yang tentunya dapat disesuaikan dengan kebutuhan.

Beda cara mengisi daftar di dalam kotak ListBox, beda pula cara atau penulisan kode dalam menampilkan isinya. Dan dalam postingan kali ini, saya akan memberikan kepada anda 2 (dua) buah kode Macro VBA yang dapat anda gunakan untuk menampilkan isi sebuah nilai dalam ListBox ketika ia di klik (dipilih).

Ya, even yang akan kita gunakan untuk menampilkan isi dari salah satu daftar ListBox adalah dengan menggunakan even klik (Click), yakni sebuah even yang akan dijadikan sebagi pemicu untuk menjalankan kode Macro VBA ketika pengguna melakukan klik terhadap ListBox. Dan berikut ini adalah hasil akhir dari tutorial kita kali ini :
ListBox Excelive

Ok. sekarang mari kita mulai tutorial kali ini dengan mengawalinya dengan berdoa, mudah-mudahan apa yang akan kita lakukan ada guna dan manfaatnya.:). Untuk langkah selanjutnya, silahkan ikuti tahap demi tahap berikut ini :
  1. Buatlah sebuah data sederhana mulai dari sel A1 hingga A7 (untuk memudahkan dalam belajar tutorial ini, bisa juga anda isikan data-data di sel tersebut seperti tampilan hasil akhir di atas).
  2. Tekan tombol pintas (Shortcut) ALT + F11 untuk menuju jendela Microsoft Visual Basic Editor (VBE), dan mulailah menambahkan sebuah UserForm dan dilanjutkan dengan meletakkan objek ListBox ke dalamnya.
    Design ListBox
  3. Masih di jendela VBE, klik kanan objek UserForm yang ada di jendela VBAProject dan pilih menu View Code dari menu pop-up yang muncul. Hal ini bertujuan untuk mengganti tampilan objek (design) VBA dengan jendela kode VBA.
    Klik kanan objek VBA
  4. Anda akan dihadapkan dengan sebuah lembar kerja warna putih dan masih bersih karena belum di isi kode-kode atau baris perintah apapun. Di dalam jendela ini, mulailah memilih objek UserForm dan Prosedur Activate dari kotak DropDown masing-masing.
    Prosedur userform aktif
  5. Ok. sekarang kita akan menuliskan baris kode yang sangat sederhana untuk menampilkan data-data yang sebelumnya kita tuliskan dalam kerja untuk ditampilkan dalam kotak ListBox yang terdapat dalam UserForm. Kode-kode ini akan langsung di eksekusi secara otomatis ketika UserForm dibuka atau diaktifkan. Hal ini terjadi karena even atau prosedur yang kita pilih adalah even atau prosedur UserForm_Activate.
    Private Sub UserForm_Activate()
       ListBox1.RowSource = "A1:A7"
    End Sub
  6. Sekarang kita akan tambahkan baris kode yang berfungsi untuk memerintahkan Excel agar mengeksekusi kode ketika isi atau daftar dalam objek ListBox kita klik (pilih). Hal ini dapat dilakukan dengan cara memilih dulu objek serta prosedur yang akan kita gunakan, yakni objek ListBox dan prosedur Click.
    Prosedur Listbox klik
  7. Akhirnya kita sampai pada bagian inti dari tutorial ini, yakni memberikan baris perintah yang bertujuan untuk menampilkan salah satu daftar dalam ListBox ke dalam jendela Kotak pesan.
    Sesuai dengan judul posting, maka silahkan anda pilih salah satu dari dua buah kode berikut yang ingin anda gunakan dan dituliskan ke dalam even ListBox_Click:

    Cara 1
    Private Sub ListBox1_Click()
       a = ListBox1.List(ListBox1.ListIndex, 0) 
       msgbox a  
    End Sub
    Kode ini akan diawali dengan menentukan nilai a dengan perintah membaca seluruh daftar yang terdapat di ListBox, kemudian memilih daftar berdasarkan Index dan mengambil kolom pertama (kolom 0 dibaca sebagai kolom pertama). Kemudian hasil pencarian tersebut akan ditampilkan dalam kotak pesan yang berisi sesuai dengan nilai a

    Cara 2
    Private Sub ListBox1_Click()
       b = ListBox1.ListIndex + 1 
       MsgBox Cells(b, 1) 
    End Sub
    Cara yang ini diawali dengan menentukan nilai b yang berfungsi untuk membaca nilai yang dipilih dalam daftar ListBox berdasarkan index, kemudian ditambah dengan angka +1. Hal ini dikarenakan index 0 dibaca sebagi baris yang pertama dan seterusnya.
    Kemudian hasil pencariannya diambil dari lembar kerja dengan menggunakan paremeter Cells. Penulisan paremeter Cells yakni diawali dengan baris kemudian kolom, sehingga hasil akhir penulisan parameter ini adalah MsgBox Cells(b, 1), di mana nilai b digunakan untuk menentukan barisnya, sementara angka 1 dari parameter ini berfungsi untuk membaca kolom pertama yakni kolom A.

    Kekurangan dari baris kode ini adalah, anda harus mengganti nilai + 1 pada baris kode ListBox1.ListIndex + 1 sesuai dengan letak dimana baris pertama anda dituliskan dalam lembar kerja. Kelemahan lain dari kode ini adalah ai tidak bisa menampilkan isi sel dengan benar ketika daftar yang terisi di dalam ListBox disusun secara terbalik (Descending Order). Untuk tutorial pengisian ListBox dari urutan terbawah, silahkan anda pelajari materi ini Mau Mengisi ListBox dari Bawah ke Atas? Begini Caranya.
  8. Langkah terakhir yang harus dilakukan tentu adalah mengujinya, yakni bisa dilakukan dengan cara menekan tombol F5 di keyboard anda atau bisa juga menekan tombol di menubar.

Perbedaan yang paling mendasar dari dua cara di atas terletak pada metode mengambil nilai dari kotak ListBox, dimana Cara 1 mengambil nilai secara langsung dari kotak ListBox itu sendiri, sementara Cara 2 mengambil nilai dari lembar kerja dengan mengacu kepada hasil pilihan yang dilakukan pada ListBox.
Kesimpulan akhir dari tutorial ini ada pada pribadi masing-masing, mana dari kedua kode tersebut yang dirasa sesuai dengan kebutuhan. Adios.


Menggunakan Parameter Intersect Dalam Macro VBA Excel

Belajar Intersect Excel

Dalam bahasa indonesia, kata intersect dapat diartikan sebagai titik temu yang mana ia dapat diartikan sebagai pertemuan antara dua buah range dan mengambil sel-sel atau range yang menjadi pertemuan antara keduanya.
Pemahaman Intersect dapat di ilustrasikan dalam gambar berikut :
mengenal parameter intersect excel
Gambar di atas dapat dijelaskan bahwa pertemuan antara Range B2:D5 dan range C5:E7 adalah range C5:D5, inilah yang dinamakan dengan intersect.

Untuk menerjemahkan gambar di atas ke dalam bahasa Macro VBA excel, cara sederhananya adalah dengan menuliskan kode berikut ke dalam sebuah Module kemudian jalankan, maka hasilnya ia akan menampilkan alamat dari pertemuan antara sel-sel atau range tersebut
Sub Test1()
 MsgBox Intersect(Range("B2:D5"), Range("C5:E7")).Address
End Sub
Macro VBA intersect

Sekarang, mari coba kita rubah salah satu parameter yang digunakan dalam penulisan range diatas menjadi seperti berikut:
Sub Test2()
 MsgBox Intersect(ActiveCell, Range("C5:E7")).Address
End Sub
Jika anda menjalankan baris kode di atas dan jika alamat sel yang anda pilih saat ini tidak berada dalam lingkup Range C5:E7, maka pasti anda akan dihadapkan dengan pesan Run Time Error, hal ini terjadi karena parameter ini tidak mampu untuk membaca dan menemukan hasil pertemuan atau perpotongan sel. sebaliknya, jika sel yang saat ini aktif terpilih ada dalam rentang range tersebut, maka pesan yang berisi alamat sel akan ditampilkan.
intersect excel vba

Perbedaan mendasar antara module Test1 dan Test2 di atas terletak pada pemilihan alamat sel atau range yang dijadikan sebagai acuan untuk melihat perpotongan atau pertemuan sel. Anda tidak akan menemukan pesan error pada kode yang dituliskan pada module Test1, karena setiap alamat sel yang dituliskan memiliki perpotongan.
Sementara untuk module Test2, karena salah satu sel berisi parameter ActiveCell yang meminta pengguna untuk menentukan sendiri sel yang jadi perpotongan dengan acuan range C5:E7, maka akan sangat mungkin muncul pesan error jika alamat sel yang aktif terpilih berada diluar range tersebut.

Ok, untuk mencegah pesan error pada module Test2, bisa anda gunakan fungsi logika sederhana dengan menggunakan kata kunci (keyword) NOTHING. Kata kunci ini bertugas untuk menjelaskan kepada Excel bahwa tidak ada sel aktif yang sesuai dengan apa yang seharusnya dipilih.

Sekarang coba perhatikan penyempurnaan dari module Test2 berikut ini :
Sub Test2Lagi()
If Intersect(ActiveCell, Range("C5:E7")) Is Nothing Then
    MsgBox "Coba pilih alamat sel dalam rentang C5:E7"
    Else
    MsgBox Intersect(ActiveCell, Range("C5:E7")).Address
End If
End Sub
Penggunaan logika IF dengan disertai kata kunci Nothing seperti dalam kode di atas, mengganti pesan Run Time Error dengan kotak pesan (MsgBox) yang bisa kita isi sendiri.
intersect dan keyword nothing

Penggunaan yang paling umum dari metode Intersect ini biasanya digunakan dalam sebuah even ketika sebuah sel berubah, dalam bahasa Macro VBA even ini dikenal dengan even Worksheet_Change.
Penjelasan mengenai cara menggunakan Intersect dalam even ini akan diulas dalam tutorial berikutnya.
Jadi, jangan sampai ketinggalan. Dan silahkan anda subscribe atau bookmark situs ini untuk mengetahui kehebatan microsoft Excel lainnya bersama excelive.com. See you soon...


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 !