BelajarVBA 012 - Formula

Coretan Mr. Kid

Selamat Tahun Baru 1435 H. Mari bersegera melakukan hijrah ke sesuatu yang lebih baik menurut tuntunan agama.

Mari kita lanjutkan obrolan tentang VBA yang sempat tertunda beberapa saat ini. Pembahasan kali ini adalah tentang memasang formula ke suatu area range dengan VBA. File yang bisa digunakan untuk mencoba secara mandiri adalah BelajarVBA012.xlsm.

Excel Reference Style

Excel menyediakan dua style dalam mereferensi ke suatu range, yaitu A1 dan R1C1. Style tersebut adalah tentang cara menuliskan rujukan ke suatu range. Karakteristik rujukan dan fungsional dari kedua style adalah sama. Pada A1 Style, karakter A mewakili cara penulisan indeks kolom Excel dan karakter 1 mewakili indeks baris Excel. Pada R1C1 Style, karakter R berarti Row yang diikuti dengan nomor indeks baris Excel yang diwakili oleh karakter 1 dibelakang karakter R. Sedangkan karakter C berarti Column yang diikuti dengan nomor indeks kolom Excel yang diwakili oleh karakter 1 dibelakang karakter C. Contoh A1 Style adalah sebuah alamat cell yang ditulis dengan $C$17. Karakter C menunjukkan indeks kolom Excel ke-3 dan karakter 17 mewakili indeks baris Excel ke-17.  Jika alamat cell ber-A1 Style tadi ditulis dalam R1C1 style, maka menjadi R17C3 yang berarti Row ke-17 dan Column ke-3.

Banyak pengguna VBA yang berpendapat bahwa penggunaan R1C1 style dalam menyusun formula suatu range melalui VBA lebih mudah daripada menggunakan A1 style. Banyak juga pengguna VBA yang berpendapat bahwa penggunaan A1 style dalam menyusun formula suatu range melalui VBA lebih mudah daripada menggunakan R1C1 style. Pendapat saya pribadi, jika pengguna VBA saat berkegiatan di worksheet merasa lebih mudah menggunakan A1 style, biasanya akan lebih mudah menggunakan A1 style dalam menyusun formula suatu range melalui VBA. Begitu juga sebaliknya, jika pengguna VBA saat berkegiatan di worksheet merasa lebih mudah menggunakan R1C1 style, biasanya akan lebih mudah menggunakan R1C1 style dalam menyusun formula suatu range melalui VBA. Ketika pengguna VBA saat berkegiatan di worksheet merasa nyaman menggunakan style apapun, maka akan ada banyak opsi saat menyusun formula suatu range melalui VBA. Jadi, tetaplah pada apa yang telah dikuasai dan mulailah untuk dikembangkan. Tidak perlu memperdebatkan style mana yang lebih mudah digunakan dalam VBA.

Pemasangan formula dengan VBA adalah dengan memanfaatkan properti milik object range yang bernama :

1. Formula untuk A1 style dengan syntax :
   object_range.Formula = "=bunyi formula"


2. FormulaR1C1 untuk R1C1 style dengan syntax :
   object_range.FormulaR1C1 = "=bunyi formula"

Memasang formula ke suatu range dengan VBA

Data yang akan digunakan dalam pembahasan ini seperti yang ada di sheet bernama VBA dalam file BelajarVBA012.xlsm yang berisi seperti gambar berikut ini :

Pada gambar diatas, data menggunakan A1 style dan formula yang dibutuhkan adalah untuk menghitung :

1. Total Pembelian pada kolom total_beli dengan formulasi nilai kolom beli * nilai kolom qty
2. Total Penjualan pada kolom total_jual dengan formulasi nilai kolom jual * nilai kolom qty

Dari keadaan yang ada, maka dapat dijabarkan bahwa :

• Karena kolom total_beli dan total_jual merujuk ke kolom qty, maka pada rujukan ke kolom qty dalam formula membutuhkan absolute reference pada sisi kolom ketika merujuk ke kolom qty.
• Kolom total_beli berdampingan dengan kolom total_jual dan kolom beli yang dibutuhkan oleh formulasi total_beli juga berdampingan dengan kolom jual yang dibutuhkan formulasi total_jual, sehingga rujukan ke kolom beli saat menghitung total_beli, tidak perlu absolute reference pada sisi kolom agar formula bisa di-copy ke kolom total_jual yang secara otomatis akan membuat formulasi pada kolom total_jual akan merujuk ke kolom jual akibat proses copy formula tersebut.
• Baris yang diproses adalah pada baris yang sama dan baris data lainnya juga merujuk ke baris masing-masing. Artinya, pada sisi baris yang merujuk ke kolom beli, kolom jual, dan kolom qty tidak membutuhkan absolute reference, sehingga formula bisa di-copy ke baris data berikutnya.

Langkah-langkah pemasangan formula ke area range diseluruh record data pada kolom total_beli dan total_jual adalah sebagai berikut :

1. Susun formula universal pada worksheet untuk mengkalkulasi total_beli pada record pertamanya.
   • Bunyi formula sesuai reference style worksheet : (pilih salah satu)
     1. Formula dengan A1 style di cell i2 adalah =F2*$H2
     2. Formula dengan R1C1 style di cell R2C9 adalah =RC[-3]*RC8
   • Buat sebuah general module dan sebuah prosedur sub untuk memasang formula. Misalkan nama prosedurnya adalah PasangFormula dengan bentuk blok prosedur sebagai berikut :

     Public Sub PasangFormula()
     
     End Sub

2. Definisikan variabel kerja
   Dalam prosedur tersebut diberi baris kode deklarasi variabel kerja sebagai berikut :
   

   Dim rngData As Range    'area data (termasuk header)
   Dim rngTarget As Range  'area yang dipasangi formula
   Dim lRecords As Long    'jumlah record

3. Definisikan range yang akan dipasangi formula
   Pendefinisian dilakukan dengan informasi sebagai berikut :
   
   1. Data berada di sheet bernama VBA, yang dimulai pada cell pertama (baris ke-1 dan kolom ke-1) dan berbentuk tabel yang baik. Area data seluruhnya bisa didefiniskan dengan properti CurrentRegion dari Cells(1,1) pada sheet VBA.
   2. Jumlah baris yang akan dipasangi formula adalah sebanyak jumlah record data. Jumlah record data adalah jumlah baris data seluruhnya (termasuk header) dikurangi jumlah baris header (pada kasus ini, header membutuhkan 1 baris saja).
   3. Area range yang akan dipasangi formula adalah area data seluruhnya yang diubah dimensi barisnya (resize pada sisi baris) menjadi sebanyak jumlah record data.
   4. Jumlah kolom yang akan dipasangi formula yang berdampingan adalah sebanyak 2 kolom, yaitu kolom total_beli dan kolom total_jual yang berdampingan. Artinya, dibutuhkan pengubahan dimensi kolom dari seluruh area data menjadi sebanyak 2 kolom (resize pada sisi kolom menjadi 2).
   5. Range yang akan dipasangi formula terletak mulai dibawah header (mulai baris ke-2), artinya, diperlukan offset sebanyak jumlah baris header dari seluruh area data (termasuk header). Pada kasus ini, header data hanya membutuhkan 1 baris saja, maka dilakukan offset sebanyak 1 baris.
   6. Kolom pertama yang akan dipasangi formula terletak pada kolom ke-9, artinya, diperlukan offset sebanyak 8 kolom kekanan dari kolom pertama data.
   
   Dari pendefinisian diatas, maka dapat disusun baris kode untuk mendapatkan area range yang akan dipasangi formula sebagai berikut :
   

   Set rngData = Sheets("VBA").Cells(1, 1).CurrentRegion
   lRecords = rngData.Rows.Count - 1
   Set rngTarget = rngData.Resize(lRecords, 2).Offset(1, 8)

4. Memasang formula pada area range
   Informasi yang diketahui adalah sebagai berikut :
   
   • Area range yang akan dipasangi formula telah didefinisikan dalam variabel rngTarget. Artinya, properti milik variabel rngTarget yang akan digunakan.
   • Bunyi formula yang disusun dalam worksheet adalah : (pilih salah satu reference style)
     1. A1 style berbunyi =F2*$H2
     2. R1C1 style berbunyi =RC[-3]*RC8
   • Formula dalam worksheet telah bersifat universal atau dapat di-copy langsung ke seluruh area range yang harus dipasangi formula.
   
   Dari informasi diatas, baris kode untuk memasang formula pada area range rngFormula adalah sebagai berikut : (pilih style sesuai kenyamanan masing-masing)
   
   1. A1 style :

      rngTarget.Formula = "=F2*$H2"

   2. R1C1 style :

      rngTarget.FormulaR1C1 = "=RC[-3]*RC8"

Bentuk prosedur bernama PasangFormula akan menjadi :
Public Sub PasangFormula()
    '------------------------'
    'deklarasi variabel kerja'
    '------------------------'
    Dim rngData As Range        'area data (termasuk header)
    Dim rngTarget As Range      'area yang dipasangi formula
    Dim lRecords As Long        'jumlah record
   
    '-----------------------------------------------'
    'pendefinisian area range yang dipasangi formula'
    '-----------------------------------------------'
    '1. pendefinisian area data seluruhnya
    'data tersusun sebagai tabel yang baik -> currentregion
    Set rngData = Sheets("VBA").Cells(1, 1).CurrentRegion
   
    '2. pendefinisian jumlah record data
    'nilai 1 adalah jumlah baris untuk header data
    lRecords = rngData.Rows.Count - 1
   
    '3 sampai 6 pendefinisian area range yang dipasangi formula
    '- rngData diubah dimensinya (resize) menjadi lRecords baris dan 2 kolom
    '- kemudian di-offset sebanyak 1 baris dan 8 kolom
    Set rngTarget = rngData.Resize(lRecords, 2).Offset(1, 8)
   
    '-----------------------------------------------'
    'pemasangan formula'
    '-----------------------------------------------'
    rngTarget.Formula = "=F2*$H2"
End Sub

Memasang Array Formula

Array formula selalu menghasilkan suatu blok array. Range yang masuk dalam area blok array tersebut tidak dapat diubah kecuali seluruh range yang ada dalam blok array tersebut yang diubah. Dimensi terkecil dari array blok adalah 1 baris x 1 kolom yang berisi sebuah cell berarray formula. Karakteristik lain dari blok array adalah tidak dapat diubah isinya dengan paste ketika salah satu range atau seluruh range dari blok array tersebut sedang aktif dikenai proses copy.

Misalnya, cell C17 berisi array formula berbunyi =MIN( IF( A1:A5>0 , A1:A5 ) ) yang akan membentuk sebuah blok array berdimensi 1 baris x 1 kolom. Ketika formula di C17 juga akan dipasang pada area range D17 sampai F17, maka cell C17 di-copy ke clipboard dengan menekan CTRL C. Jika range C17 sampai F17 diblok dan dilakukan paste (menekan CTRL V), maka proses paste akan gagal karena cell C17 adalah blok array yang sedang aktif di-copy. Maka proses paste ke area D17 sampai F17 harus dengan cara memblok D17 sampai F17 tanpa menyertakan C17, yang kemudian dilakukan paste dengan menekan CTRL V.

Array formula dipasang ke suatu range dengan memanfaatkan properti FormulaArray milik object range dengan syntax :
   object_range.FormulaArray = "=bunyi array formula"
Ketika properti FormulaArray digunakan pada suatu object range yang berisi banyak cells, maka seluruh range yang dikenai properti FormulaArray akan membentuk sebuah blok array. Misalnya, ada sebuah variabel range yang bernama rngTarget yang berisi range D5:F9. Kemudian rngTarget dikenai properti FormulaArray dan diberi bunyi teks array formula apapun. Maka range D5:F9 akan membentuk sebuah blok array yang tidak akan mungkin diubah salah satu isi dari range D5:F9 tersebut.

Dengan demikian, pemasangan array formula membutuhkan ketelitian lebih dalam menentukan range yang akan diberi array formula agar blok array yang terbentuk sesuai kebutuhan. Pemasangan array formula dilakukan pada satu lokasi lebih dulu, kemudian di-copy ke lokasi lain. Artinya, diperlukan pendefinisian area range yang akan dipasangi array formula sebagai lokasi pertama dan pendefinisian area range yang akan menjadi lokasi paste dari array formula yang telah terpasang di lokasi pertama tadi. Syaratnya, area range lokasi paste tidak berpotongan sama sekali dengan area range lokasi pemasangan array formula pertama.

Contoh pemasangan array formula bisa memanfaatkan data yang ada di sheet bernama 'Array' seperti gambar berikut :

Kasus untuk memperlajari pemasangan array formula akan menggunakan A1 style. Contoh kasus tersebut adalah sebagai berikut :

1. Cell E2 diberi array formula berbunyi =A2, sehingga dalam cell E2 akan tampil di formula bar sebagai {=A2}. Kemudian array formula di E2 akan di-copy sampai E4. Misalkan nama prosedurnya adalah ArrayFormula1, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula1()
       Dim rngPertama As Range  'range pertama yang diberi array formula
       Dim rngPaste As Range    'range lokasi paste array formula
      
       'lokasi pasang array formula yang akan di-copy
       Set rngPertama = Sheets("Array").Range("E2")
      
       'lokasi paste array formula di rngPertama
       Set rngPaste = Sheets("Array").Range("E3:E4")
      
       'pasang array formula di lokasi pertama
       rngPertama.FormulaArray = "=A2"
      
       'proses copy paste formula
       rngPertama.Copy
       rngPaste.PasteSpecial xlPasteFormulas
   End Sub


2. Cell G2 diberi array formula berbunyi =A2, sehingga dalam cell G2 akan tampil di formula bar sebagai {=A2}. Kemudian array formula di G2 akan di-copy sampai H4. Proses paste dilakukan dalam 2 tahap, yaitu tahap 1 adalah ke area paste pertama di G3:G4 dan tahap 2 ke area paste kedua di H2:H4. Misalkan nama prosedurnya adalah ArrayFormula2, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula2()
       Dim rngPertama As Range   'range pertama yang diberi array formula
       Dim rngPaste1 As Range    'range lokasi paste 1 array formula
       Dim rngPaste2 As Range    'range lokasi paste 2 array formula
      
       'lokasi pasang array formula yang akan di-copy
       Set rngPertama = Sheets("Array").Range("G2")
      
       'lokasi paste array formula di rngPertama
       Set rngPaste1 = Sheets("Array").Range("G3:G4")
       Set rngPaste2 = Sheets("Array").Range("H2:H4")
      
       'pasang array formula di lokasi pertama
       rngPertama.FormulaArray = "=A2"
      
       'proses copy paste formula
       rngPertama.Copy
       rngPaste1.PasteSpecial xlPasteFormulas
       rngPaste2.PasteSpecial xlPasteFormulas
   End Sub


3. Range J2:K2 (1 baris x 2 kolom) diberi array formula berbunyi =A2:B2 (juga 1 baris x 2 kolom), sehingga dalam cell J2 dan K2 akan tampil di formula bar sebagai {=A2:B2}. Range J2:K2 akan menjadi sebuah blok array. Kemudian array formula di J2:K2 akan di-copy sampai J4:K4. Misalkan nama prosedurnya adalah ArrayFormula3, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula3()
       Dim rngPertama As Range  'range pertama yang diberi array formula
       Dim rngPaste As Range    'range lokasi paste array formula
      
       'lokasi pasang array formula yang akan di-copy
       Set rngPertama = Sheets("Array").Range("J2:K2")
      
       'lokasi paste array formula di rngPertama
       Set rngPaste = Sheets("Array").Range("J3:J4")
      
       'pasang array formula di lokasi pertama
       rngPertama.FormulaArray = "=A2:B2"
      
       'proses copy paste formula
       rngPertama.Copy
       rngPaste.PasteSpecial xlPasteFormulas
   End Sub


4. Range M2:M4 (3 baris x 1 kolom) diberi array formula berbunyi =A2:A4 (juga 3 baris x 1 kolom), sehingga dalam range M2:M4 akan tampil di formula bar sebagai {=A2:A4}. Range M2:M4 akan menjadi sebuah blok array. Kemudian array formula di M2:M4 akan di-copy sampai N2:N4. Misalkan nama prosedurnya adalah ArrayFormula4, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula4()
       Dim rngPertama As Range  'range pertama yang diberi array formula
       Dim rngPaste As Range    'range lokasi paste array formula
      
       'lokasi pasang array formula yang akan di-copy
       Set rngPertama = Sheets("Array").Range("M2:M4")
      
       'lokasi paste array formula di rngPertama
       Set rngPaste = Sheets("Array").Range("N2")
      
       'pasang array formula di lokasi pertama
       rngPertama.FormulaArray = "=A2:A4"
      
       'proses copy paste formula
       rngPertama.Copy
       rngPaste.PasteSpecial xlPasteFormulas
   End Sub


5. Range P2:Q4 (3 baris x 2 kolom) diberi array formula berbunyi =A2:B4 (juga 3 baris x 2 kolom), sehingga dalam range P2:Q4 akan tampil di formula bar sebagai {=A2:B4}. Range P2:Q4 akan menjadi sebuah blok array. Misalkan nama prosedurnya adalah ArrayFormula5, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula5()
       Dim rngTarget As Range    'range yang diberi array formula
      
       'lokasi pasang array formula
       Set rngTarget = Sheets("Array").Range("P2:Q4")
      
       'pasang array formula di lokasi pertama
       rngTarget.FormulaArray = "=A2:B4"   
   End Sub

Catatan

1. Kaidah tentang penulisan data teks dalam VB/VBA terhadap karakter tanda petik dua (") :
   • Ketika formula dalam worksheet berisi karakter tanda petik dua ("), maka seluruh karakter tanda petik dua harus diubah menjadi 2 buah tanda petik dua ("") saat bunyi formula tersebut akan dipasang dalam VBA. Misal, formula dalam worksheet berbunyi :
     
     =17 & " kolom"
   • maka dalam VBA, setiap karakter tanda petik dua yang mengapit kata 'kolom' harus diubah menjadi 2 buah tanda petik dua sesuai kaidah penulisan karakter tanda petik dua dalam suatu data teks di-VB/VBA, sehingga bunyi formula yang dipasang dalam VBA berubah menjadi :
     =17 & "" kolom""
   • Bila range yang akan dipasangi formula adalah variabel bernama rngPasang, maka baris kode menjadi :
     rngPasang.Formula = "=17 & "" kolom"""
     atau
     rngPasang.FormulaR1C1 = "=17 & "" kolom"""
   • Pada formula bar akan tampak formula berbunyi :
     =17 & " kolom"
2. Pemasangan formula pada kolom yang tidak berdampingan (berjejer) dan tidak dapat disusunkan sebuah formula universal (yang dapat di-copy keseluruh area secara langsung), maka proses yang dijabarkan diatas harus dilakukan terhadap masing-masing kolom dengan formula masing-masing.
3. Properti Formula, FormulaR1C1, maupun FormulaArray dapat diberi bunyi formula dengan referensi A1 style maupun R1C1 style. Formula yang tampak pada formula bar akan mengikuti setting reference style yang digunakan oleh Excel setempat.

:)

Insya Allah pembahasan berikutnya adalah memasang tombol dalam worksheet berupa shape dan sebuah ActiveX CommandButton untuk menjalankan sebuah prosedur VBA. Pembahasan ini adalah pembukaan untuk pembahasan tentang object module UserForm.

<previous | next>

Fungsi String (Text)

#InfoExcel07 kembali dengan menuliskan fungsi string. apa sih fungsi string itu? Fungsi string ata fungsi teks adalah fungsi yang menempatkan dan mengolah data dengan format teks (text) pada lembar kerja dalam kasus ini saya menggunaakn Microsoft Excel 2007. Fungsi string dapat juga di fungsikan untuk merubah karakter pada cell tertentu dan memanggil cell tersebut untuk digunakan sebagai Data teks.

berikut ini, jenis dari fungsi string :

1. Fungi Left

Fungsi Left yaitu suatu fungsi yang dipakai untuk memanggil karakter yang di inginkan pada suatu cell di Microsoft Excel yang dimulai dari kiri.

penulisan rumus fungsi Left pada ms excel adalah sebagai berikut :

=Left(text;num_char)

keterangan : 

text adalah lokasi cell yang berisi data yang nantinya akan di panggil

num_char adalah berapa jumlah karakter yang hendak kita ambil dengan pembacaan dari kiri.

2. Fungsi Mid

Fungsi MID yaitu suatu fungsi yang difungsikan untuk memanggil karakter yang di inginkan pada suatu cell pada case ini saya mencobanya di Microsoft Excel 2007 yang dimulai dari tengah, kemudian kamu menentukan jumlah pengambilan karakter pada cell tersebut.

penulisan rumus fungsi mid pada ms excel adalah sebagai berikut :

=Mid(text;start_num;num_char)

keterangan : 

text adalah lokasi cell yang berisi data yang nantinya akan di panggil

star_num adalah karakter awal yang akan di panggil, di hitung dari kiri, 

num_char adalah berapa jumlah karakter yang hendak kita panggil.

3. Fungsi Right

Fungsi Right yaitu suatu fungsi yang dipakai untuk memanggil karakter yang di inginkan pada suatu cell di Microsoft Excel yang dimulai dari sebelah kanan.

penulisan rumus fungsi right pada ms excel adalah sebagai berikut :

=Right(text;num_char)

keterangan :

text adalah lokasi cell yang berisi data yang nantinya akan di panggil

num_char adalah berapa jumlah karakter yang hendak kita ambil dengan pembacaan dari kanan.

4. Fungus Upper

menggunakan fungsi UPPER akan mengubah teks pada suatu cell menjadi huruf besar semua. nanti lihat deh contoh di bawah ya untuk lebih jelasnya.

penulisan rumus fungsi upper pada ms excel adalah sebagai berikut :

= UPPER (text)

5. Fungsi Lower

memanfaatkan fungsi LOWER akan meringankan kamu untuk mengetik ulang. dengan menggunakan fungsi lower, huruf alfabet pada suatu cell yang di awali dengan huruf besar (kapital) akan berubah menjadi huruf kecil semua. 

penulisan rumus fungsi lower pada ms excel adalah sebagai berikut :

= LOWER (text)

6. Fungsi LEN

menghitunga karakter pada suatu cell dapat memanfaatkan fungsi LEN. sangat mudah dipahami dan di praktekkan. lihat saja penulisan rumus fungsinya berikut ini.

= LEN(text)

agar mudah dipahami, akan saya lampirkan videonya. silakan dilihat di bawah ini.

Semoga Tutorial, Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk bisnis bermanfaat bagi anda. Jika ada pertanyaan, silahkan sampaikan sebisa dan secepat mungkin nanti akan saya tanggapi.

cara menghitung bilangan pangkat dan akar pada excel 2007

kenapa saya membuat tulisan mengenai pangkat dan akar? jawabnya simpel sekali. ada seorang teman, sewaktu zaman SMA dulu (walaupun saya bukan SMA, tapi SMK). teman saya bertanya mengenai perhitungan excel 2007 menggunakan rumus untuk pangkat dan akar. ya, mungkin dia sedang dalam permasalahan dengan perhitungan rumus akar excel dan pangkat. sekalian saja, dari apa yang telah saya sampaikan ke teman saya. saya akan share juga cara mencari akar di excel dengan pembaca blog setia dari info excel. hehe (baca juga : Apa itu Range? dan ubah simbol dollar ke rupiah)

untuk bagian pertama, saya akan mengulas mengenai perhitungan bilangan pangkat pada excel 2007 adalah sebagai berikut :

1. dengan tanda ^ (pangkat atau kuadrat)

dapat untuk melakukan perhitungan bilangan pokok yang dengan tanda ^ kemudian besarnya angka (pangkat atau kuadrat). biasanya ada pada keyboard di angka 6, ada kan? cara menampilkannya tekan secara bersamaan tombol Shift+angka 6. mudah kan?

berikut ini merupakan sample (syntax formula) yang coba pada Excel:

=2^3 (apabila dibaca menjadi "dua pangkat tiga")

=5^2 (apabila dibaca menjadi "lima kuadrat atau lima pangkat dua")

berikut screen shotnya

tentu teman-teman tidak menyangka bahwa dapat melakukan perhitungan dengan menggunakan rumus untuk mencari nilai pangkat dari sebuah angka. ada 2 (dua) jenis rumus yang mudah untuk di praktekkan. simak tutorial excel berikut ini.

2. Dengan rumus POWER

menggunakan rumus POWER dapat mengetahui nilai pangkat dari suatu bilangan yang dicari. berikut ini saya contohkan penggunaan rumus POWER dengan syntax sebagai berikut:

=POWER(bilangan pokok; bilangan pangkat)

Contoh Penggunaan pada workbook Excel:

=POWER(2;3)

=POWER(5;2)

 

3. Dengan rumus Natural Logarithm pada angka

cara kedua agak sedikit ribet, yaitu perpaduan dari rumus EXP dengan rumus LN (Natural Logarithm) pada angka yang dipangkatkan. berikut contoh sederhana penggunaannya dengan syntax sebagai berikut:

=EXP(angka pangkat * LN(angka pokok)

Contoh Penggunaan pada workbook Excel:

=EXP(3 * LN(2))

=EXP(2 * LN(5))

 

setelah menghitung pangkat dari sebuah bilangan, tentunya ada lawannya dong. setiap rumus pasti memiliki lawan yaitu untuk kasus disini dengan menghitung akar dari bilangan tersebut. Pangkat vs akar. untuk bagian kedua, seperti yang telah dipaparkan sebelumnya. pada bagian kedua saya akan menjelaskan perhitungan akar pada excel 2007.

untun menghitung secara default akar pangkat dua atau akar kuadrat, dapat dengan mudah menggunakan rumus SQRT. penggunaan (syntax) penulisan rumus tersebut juga mudah sekali. berikut contohnya yah kawan

=SQRT(8)

=SQRT(25)

 

dalam menghitung akar, tentunya terkadang kita menemui soal yang berbeda. misalnya dengan perhitungan akar pangkat tiga? tentunya tidak bisa menggunakan rumus SQRT, solusinya adalah dengan menggunakan rumus ABS lebih dikenal dengan rumus absolut. penasaran dengan penggunaannya? mari simak saja tutorial penggunaan rumus ABS untuk menghitung akar pangkat tiga atau lebih pada workbook excel 2007 berikut ini.

=ABS(8)^(1/3)

=ABS(65536)^(1/8)

Hasil akan terlihat pada screen shot dibawah ini ya.

Bagaimana? mudah dipahami kan? lanjutkan untuk belajar excel 2007 agar dalam perhitungan akan selalu memperoleh data yang cepat dan tepat. baca juga : cara mengunci sheet di excel 2007.

Semoga Tutorial, Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk bisnis bermanfaat bagi anda. Jika ada pertanyaan, silahkan sampaikan sebisa dan secepat mungkin nanti akan saya tanggapi.

Cara Membuat Rekening Pribadi pada Excel 2007

pada zaman sekarang ini, hampir setiap manusia di bumi memiliki rekening. lalu, apa sih rekening itu? rekening dapat dikatakan seperti dompet. dompet yang dapat dibawa kemana-mana. untuk saat ini lebih trend dengan sebutan ATM (Anjungan Tunai Mandiri atau Automatic Teller Machine). sudah pahamkah mengenai rekening?

jika belum, segeralah untuk membuatnya di bank terdekat sekitar anda. karena dengan adanya rekening, kita dapat mengontrol arus kas yang keluar maupun masuk ke rekening. oke, untuk postingan sekarang saya akan membahas mengenai cara membuat rekening pribadi pada excel 2007.

Lho kenapa kok malah judul itu yang diambil? sebenarnya sederhana saja menjawab pertanyaan tersebut. karena saya ingin mengontrol arus keuangan pribadi saya sendiri. gaji dari hasil kerja saya gunakan untuk apa saja. sehingga, nantinya jelas mengenai belanja yang kurang sesuai akan dilakukan evaluasi. mana yang butuh, dan mana yang perlu.

tanpa panjang lebar, simak saja langkah sederhana dengan menggabungkan beberapa fungsi dari rumus excel 2007 yang pernah saya jelaskan. simak yah.

1. buatlah tampilan seperti dibawah ini ya.

keterangan:

format cell pada baris 4,5,6 kolom B adalah number. dengan cara di blok terlebih dahulu, klik kanan para area yang di blok tadi -> format cell -> number -> currency (jangan pake simbol Rp ya).

selanjutnya untuk baris 9 s/d 19 :

kolom A, formatnya ialah date. blok baris 9 s/d 19 kolom A -> klik kanan -> format cell -> custom -> pada kolom type, ketikkan dd mmm'yy -> OK.

Kolom B dan C, formatnya general saja.

Kolom D, tentu karena judulnya nominal ya isinya harus mengenai angka atau number.

untuk kolom jenis, dapat memanfaatkan conditional formatting untuk keperluan bisnis dengan kombinasi membuat list data pada sel. ubah sesuai kreatifitas anda yah. mau mencontoh? boleh kok.

2. tingkat lanjut dari penggunaan rumus diatas yaitu. misalkan data pada tiap cell telah di isi.

bagaimana? penasaran dengan rumusnya yah?

teknik apa saja yang dilakukan?

lanjut lihat tutorial cara membuat rekening pribadi pada excel 2007 pada nomor ke 3.

3. saya kemudian menampilkan rumus (show formula) sehingga kamu dapat melihat secara jelas rumus sederhana yang saya gunakan.

keterangan :

untuk baris 4,5,6 kolom B. saya menggunakan fungsi sumif. untuk range dan sum_range saya kunci (tekan tombol F4 pada saat kursor ada di sel tersebut. dengan maksud, agar data yang dimaksud valid dan tidak bergeser ke arah sel lain.

kolom tanggal, saya menggunakan rumus now. untuk mengetahui tanggal sekarang. untuk tanggal selanjutnya tinggal ditambah sesuai kebutuhan.

sekian trik singkat saya dalam cara membuat rekening pribadi pada excel 2007. terima kasih telah berkunjung ke blog saya.

Semoga Tutorial, Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk bisnis bermanfaat bagi anda. Jika ada pertanyaan, silahkan sampaikan sebisa dan secepat mungkin nanti akan saya tanggapi.

Cara Proteksi Cell, Sheet dan Workbook pada Excel 2007

seringkali info excel membagikan trik mengenai pengertian dan penggunaan rumus pada excel 2007. namun, untuk kali ini berbeda dari biasanya kawan. info excel akan memberikan trik mengenai proteksi file, baik itu cell, sheet dan workbook. proteksi file disini ialah dengan memberikan password untuk mengambil hak akses. ya semoga saja postingan saya dapat bermanfaat untuk kalian. tapi, pesan info excel gunakan dengan bijak. jangan buat iseng yah. hehe

dengan adanya proteksi pada file, akan menjaga tingkat kerahasiaan data kita yang ada di dalamnya. dengan mengandalkan proteksi file kita itu bisa juga menjadi boomerang bagi si pembuat. kenapa? lho kok bisa? bisa saja si pembuat password lupa mengenai pasword yang telah dibuatnya. parah toh? mudah-mudahan jangan kejadian pada kalian ya.hehe

# Bagian pertama

cara proteksi pada cell excel. silakan buka menyembunyikan rumus excel 2007.

pada pembahasan tersebut telah mengupas mengenai proteksi cell. hal ini biasanya digunakan  untuk menyembunyikan isi dari cell tersebut. seperti rumus/ formula, karakter yang terkandung di cell tersebut.

selanjutnya,

setelah dilakukan proteksi sheet, maka karakter yang berada di baris fx akan tersembunyi.

# Bagian kedua

cara proteksi sheet excel 2007. hmm, hal ini gak beda jauh dengan proteksi pada cell excel 2007. hanya saja memproteksi satu sheet saja. langkahnya ada sedikit berbeda. tapi, pada dasarnya sama.hehe

berikut cara singkat untuk memproteksi/ melindungi sheet dengan password pada excel 2007. silakan disimak ya?

1. klik kanan pada sheet yang dipilih -> protect sheet.

2. masukkan password yang sama sebanyak 2 kali.

3. yeeaahh,,akhirnya bisa juga melindungi cell dan sheet dengan password. 

cara buka proteksi sheet excel yaitu dengan klik kanan pada sheet yang dipilih -> uprotect sheet -> masukkan passwordnya -> ok.

jangan senang dulu, untuk lebih aman dalam hal proteksi file. lanjutkan ke trik ke tiga, yaitu :

# Bagian Ketiga

cara proteksi file pada workbook.

kalian tahu kan yang namanya workbook? dari artinya saja sudah bisa ditebak yaitu work artinya kerja, sedangkan book artinya buku. atau bisa diartikan juga buku kerja. nah, ini buku dari file excel. sedangkan halaman pada excel dinamakan sheet.

untuk memproteksi workbook, sangat mudah sekali dan perlu di ingat, cara ini cukup ampuh untuk melindungi data kita yang berada pada workbook tersebut. dalam sekali lihat, anda pasti sudah mahir daripada saya.hehe

perhatikan yah langkah singkat berikut ini

1. klik save as -> letakkan pada area yang dituju.

2. JANGAN terburu-buru untuk klik OK.

3. klik Tools, pilih tanda segitiga kecil di kanan. lalu pilih general option

selanjutnya,

password to open : (pasword/ sandi yang diminta untuk membuka file/ workbook excel tersebut).

password to modify : (pasword/ sandi yang diminta untuk melakukan perubahan pada file/ workbook excel tersebut).

4. untuk mengakhiri, klik OK.

ya begitulah kiranya beberapa trik proteksi/ melindungi cell, sheet dan workbook pada excel 2007. semoga dapat bermanfaat untuk teman semua. jangan patah semangat ya dalam belajar.

Oh iya, untuk membuka proteksi pada cell, sheet dan workbook dapat dilakukan dengan mengulangi hal yang sama kemudian unprotect atau unlock.

Semoga Tutorial, Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk bisnis bermanfaat bagi anda. Jika ada pertanyaan, silahkan sampaikan sebisa dan secepat mungkin nanti akan saya tanggapi.

Advance Filter dengan VBA untuk Lookup Records

Oleh: Zanul Ulum

Tulisan ini berisi sharing penggunaan advance filter yang sering saya gunakan untuk mencari record dari sebuah tabel database yang disimpan dalam format workbook atau microsoft excel. Agar tabel bisa diolah dengan baik oleh advance filter, silahkan dibaca ulasan dari Mr. Kid tentang bagaimana cara menyusun data sebagai tabel yang baik.

Selain untuk memudahkan dalam penggunaan, VBA atau macro untuk AdvancedFilter dimaksudkan agar hasil filter bisa ditampilkan dalam workbook dan sheet di luar tabel database. Jika tidak menggunakan VBA, secara default hasil advance filter harus ditampilkan dalam sheet yang sama dengan sheet tabel database atau dalam sheet yang aktif. Pesan di bawah akan muncul apabila kita mencoba untuk menampilkan hasil filter di sheet yang lain.

Pesan di atas tidak muncul apabila proses Advance Filter dilaksanakan melalui VBA atau macro dengan menggunakan method AdvancedFilter. Untuk mengetahui cara penggunaan dari method tersebut, tekan tombol F2 atau Object Browser saat di Microsoft Visual Basic Editor (VBE). Masukkan keyword atau kata kunci AdvancedFilter, kemudian click icon teropong.

Click atau pilih AdvancedFilter, kemudian tekan F1 untuk melihat keterangan detail dari method yang dipilih.

Dari keterangan object browser di atas bisa dijelaskan sebagai berikut:

Fungsi AdvancedFilter bekerja di object Excel.Range atau object Range dengan menggunakan parameter berupa Action, [CriteriaRange], [CopyToRange],[Unique].
Parameter Action harus ada sedangkan  [CriteriaRange], [CopyToRange],[Unique] bersifat optional atau pilihan. Ada dua pilihan untuk action yaitu xlFilterCopy dan xlFilterInPlace. xlFilterCopy dipilih untuk proses advance filter ini karena hasil filter akan ditampilkan di tempat lain bukan ditempat table yang sama (xlFilterInPlace).

Dalam contoh ini saya menggunakan dua buah file workbook yaitu workbook yang berisi tabel database dan workbook yang berisi macro atau vba. File database saya beri nama isbn.xlsx berisi tabel nomer isbn dan penerbitnya, sedangkan file isbn.xlsm berisi macro atau vba untuk menampilkan hasil lookup record. Kedua file tersebut harus diletakkan dalam folder yang sama.

Tabel database di file isbn.xlsx disimpan dalam sheet [Publisher] tersusun dari 4 (empat) header/field yaitu,
- [Publisher code]
- [Publisher]
- [Notes]
- [strKey]

Field [strKey] adalah header dummy hasil gabungan dari 3 (tiga) header sebelumnya. Header dummy ini dibuat secara otomatis dengan macro/vba saat file pertama kali dibuka atau melalui event Workbook_Open().

Maksud dari penggabungan ini adalah untuk membuat header bantuan sehingga jika kita memasukkan sembarang keyword atau kata kunci dalam satu cell, bisa didapatkan record antar header.

Dalam file isbn.xlsm terdapat sheet [LookUpPublisher] dengan tampilan sebagai berikut:

Range [B1] adalah tempat untuk memasukkan keyword atau kata kunci. Jika ingin mencari [publisher code] dengan kode yang diawali dengan 870, maka di [B1] dimasukkan kata kunci 870*. Record hasil pencarian akan ditampilkan mulai baris ke 3.

jika ingin mencari [publisher]=Autodesk, di [B1] dimasukkan kata kunci Autodesk. Apabila ingin dicari [Notes] yang berisi UK, di [B1] dimasukkan kata kunci UK.

Jadi dengan memasukkan satu kata kunci di [B1], program akan mencari kata kunci tersebut di header [Publisher Code], [Publisher] dan [Notes].

Untuk mengetahui alur program dan code vba-nya  download file berikut.

Optimalisasi rumus fungsi sum, if, dan average pada microsoft excel 2007

Bagaimana kabarnya sobat #InfoExcel ? mudah-mudahan semuanya dalam keadaan sehat, bagi yang sakit semoga segera diberi kesembuhan, bagi yang belum dapet jodoh semoga dipertemukan. maaf kok mimin jadi ngawur gini, lanjut ke topiknya ya. setelah sekian lama mimin gk update mengenai Tips dan Trik Excel, kali ini #InfoExcel akan memposting artikel mengenai optimalisasi rumus fungsi if, sum dan average secara bersamaan dengan penyampaian yang sangat sederhana sehingga mudah untuk dipahami oleh para sobat #InfoExcel.
Penasaran? cekidot deh ya

1. langkah pertama dan utama ialah harus ada aplikasi microsoft office excel 2007 (biar sama dan gak bingung).

2. buatlah data nilai seperti di bawah ini.

 

3. Kemudian, isilah data tersebut. Bisa sobat #InfoExcel mengarangnya sendiri, atau mengikuti tutorial ini agar memudahkan pemahaman terlebih dahulu. Selanjutnya, kreasikan ide dari sobat tersebut.

4. pada kolom "ket" baris ke 8, masukan logika dari rumus fungsi IF dengan nilai diatas 70 akan LULUS, dan nilai dibawah 70 akan GAGAL.

selanjutnya, isi logika tersebut ke dalam rumus.

setelah jadi, tinggal copas aja ke bawah. biar cepet. hehehe :D :D

5. Lalu, penulisan rumus sum.

6. dan terakhir penulisan rumus average (rata-rata). untuk mengetahui nilai rata-rata tiap anak dalam satu kelas terutama mata pelajaran matematika.

7. dan sekarang telah selesai.

maaf ya klo gambarnya jadi tutorialnya masih acak-acakan dna gak rapi. namanya juga tutorial. hehe

Oh iya, untuk memudahkan pemahaman kalian. mimin juga punya dalam bentuk videonya lho. Lihat VIDEO Optimalisasi rumus fungsi sum, if, dan average pada microsoft excel 2007.

Semoga Tutorial, Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk bisnis bermanfaat bagi anda. Jika ada pertanyaan, silahkan sampaikan sebisa dan secepat mungkin nanti akan saya tanggapi.