Menghitung Unhide Row

Jika kita ingin mengetahui berapa jumlah baris data setelah data tersebut di filter,
Cara pertama adalah menggunakan blocking pada cell
Klik pada data paling atas, kemudian drag hingga yang paling bawah.
Maka di status bar akan muncul count = n

Cara kedua menggunakan formula Subtotal
Formula ini cukup flexible, karena dengan 1 formula bisa menghitung beberapa jenis fungsi.
Syntax : SUBTOTAL(calculation_type, rangedata)

Berikut adalah List dari Calculation Type
1 -> AVERAGE

2 -> COUNT
3 -> COUNTA
4 -> MAX
5 -> MIN
6 -> PRODUCT
7 -> STDEV
8 -> STDEVP
9 -> SUM
10 -> VAR
11 -> VARP

Contoh penggunaan : subtotal(2, A1:A10) -> menghitung jumlah row dari A1 hingga A10 List calculation type diatas adalah untuk menghitung semua baris dalam range yang ditentukan, termasuk baris yang dalam posisi hide. Bagaimana untuk menghitung baris yang unhide saja ? anda bisa menggunakan Calculation Type berikut ini   101 -> AVERAGE
102 -> COUNT
103 -> COUNTA
104 -> MAX
105 -> MIN
106 -> PRODUCT
107 -> STDEV
108 -> STDEVP
109 -> SUM
110 -> VAR
111 -> VARP

Contoh penggunaan : subtotal(102, A1:A10) -> menghitung jumlah row dari A1 hingga A10 tanpa baris/row yang hide.


Bagaimana menghitung baris/row unhide melalui macro ?
Berikut ini adalah perintah untuk menghitung baris unhide

Range("DataTable").SpecialCells(xlCellTypeVisible).Rows.Count

Round, RoundUp, RoundDown, Floor, Ceilling

Excel menyediakan beberapa fungsi untuk pembulatan , antara lain :
1. Round
2. RoundUp
3. RoundDown
4. Floor
5. Ceiling

Ada yang bingung mau pakai fungsi pembulatan yang mana ?
Mari kita bahas satu persatu beserta contoh hasil dari formulanya

Syntax nya adalah sbb :
Round "=ROUND(angka, digit)"
RoundUp "=ROUNDUP(angka, digit)"
RoundDown "=ROUNDDOWN(angka, digit)"
Floor "=FLOOR(angka, significance)"
Ceiling "=CEILING(angka, significance)"

Gunanya adalah sbb :

Round
Digunakan untuk pembulatan matematis
Jika "<= 0.5" akan dibulatkan keatas, "<5 akan="" dibulatkan="" kebawah="" span="">
pada sytax digunakan untuk mengatur berapa digit setelah koma yang akan dibulatkan.

Contoh :
"=ROUND(0.49,0)" hasilnya adalah 0
"=ROUND(0.5,0)" hasilnya adalah 1
"=ROUND(4.5,1)" hasilnya adalah 5
"=ROUND(5.4,1)" hasilnya adalah 5
"=ROUND(5,-1)" hasilnya adalah 10
"=ROUND(4.9,-1)" hasilnya adalah 0

RoundUp
Digunakan untuk pembulatan keatas
Pembulatan ini tidak melihat berapa nilai dibelakang koma, 0.1 pun akan dibulatkan menjadi 1
Contoh :

"=ROUNDUP(0.49,0)" hasilnya adalah 1
"=ROUNDUP(0.5,0)" hasilnya adalah 1
"=ROUNDUP(4.5,1)" hasilnya adalah 5
"=ROUNDUP(5.4,1)" hasilnya adalah 6
"=ROUNDUP(5,-1)" hasilnya adalah 10
"=ROUNDUP(4.9,-1)" hasilnya adalah 10

RoundDown
Digunakan untuk pembulatan kebawah Pembulatan ini tidak melihat berapa nilai dibelakang koma, 0.9 pun akan dibulatkan menjadi 0
Contoh :
"=ROUNDDOWN(0.49,0)" hasilnya adalah 0
"=ROUNDDOWN(0.5,0)" hasilnya adalah 0
"=ROUNDDOWN(4.5,1)" hasilnya adalah 4
"=ROUNDDOWN(5.4,1)" hasilnya adalah 5
"=ROUNDDOWN(5,-1)" hasilnya adalah 0
"=ROUNDDOWN(4.9,-1)" hasilnya adalah 0

Floor
Digunakan untuk pembulatan kebawah.
Bedanya dengan RoundDown adalah pembulatan menggunakan Floor adalah pembulatan kebawah dengan kelipatan angka tertentu, misal dibulatkan setiap kelipatan 2, maka angka 5 akan menjadi 4

Contoh :
"=FLOOR(112,1)" hasilnya adalah 112
"=FLOOR(112,3)" hasilnya adalah 111
"=FLOOR(112,5)" hasilnya adalah 110
"=FLOOR(112,7)" hasilnya adalah 112
"=FLOOR(112,9)" hasilnya adalah 108

Ceiling
Digunakan untuk pembulatan keatas.
Bedanya dengan RoundUp adalah pembulatan menggunakan Ceiling adalah pembulatan keatas dengan kelipatan angka tertentu, misal dibulatkan setiap kelipatan 2, maka angka 5 akan menjadi 6
Contoh :
"=CEILING(112,1)" hasilnya adalah 112
"=CEILING(112,3)" hasilnya adalah 114
"=CEILING(112,5)" hasilnya adalah 115
"=CEILING(112,7)" hasilnya adalah 112
"=CEILING(112,9)" hasilnya adalah 117

Koneksi ke MS Access 2003 vs 2007/2010

Filetype dari file database MS Access (dan Office lainnya) mengalami perubahan , yaitu
2003 : MDB
2007/2010 : ACCDB

Selain itu koneksi database dari macro excel juga menggunakan engine yang berbeda, sehingga kode untuk mengakses database tersebut juga berubah

Berikut ini adalah perubahannya
2003 "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\Student.mdb;User Id=admin;Password="
2007/2010 "Provider=Provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\Student.accdb;User Id=admin;Password="

Default Sheet on First Load

Untuk menentukan sheet mana yang akan dibuka diawal pada saat file excel dibuka caranya cukup mudal, anda bisa menggunakan macro sederhana seperti dibawah ini :

Private Sub Workbook_Open()
  Sheets(1).Activate
End Sub

angka 1 adalah sebagai index atau nomor urut sheet yang akan dibuka, nomor ini bisa diganti dengan nama sheet tertentu, karena dikuatirkan sheet akan diubah urutannya.

Private Sub Workbook_Open()
  Sheets("Sheet3").Activate
End Sub

Get Column Letter From Number

Berikut ini adalah macro untuk konversi dari number ke column character.
(dibuat hingga 3 digit kolom, bagi pengguna Microsoft Excel 2010)

Function m_GetCell(iColumn As Integer) As String
  Dim i, vColumn1, vColumn2, vColumn3 As Integer
  Dim sColumn As String
 
  If iColumn <= 26 Then
    vColumn1 = iColumn + 64
  End If
  If iColumn > 26 And iColumn <= 26 * 26 Then
    vColumn1 = (iColumn - (iColumn Mod 26)) \ 26 + 64
    vColumn2 = iColumn Mod 26 + 64
  End If
  If iColumn > 26 * 26 And iColumn <= 26 * 26 * 26 Then
    vColumn1 = (((iColumn - (iColumn Mod 26)) \ 26) - ((iColumn - (iColumn Mod 26)) \ 26) Mod 26) \ 26 + 64
    vColumn2 = (iColumn - (iColumn Mod 26)) \ 26 Mod 26 + 64
    vColumn3 = iColumn Mod 26 + 64
  End If
  m_GetCell = Chr(vColumn1) & Chr(vColumn2) & Chr(vColumn3)
End Function

Sub test()
    MsgBox m_GetCell(16383)
End Sub

Semoga membantu,

Thanks

Excel 2010 : SUMIFS & COUNTIFS

Excel 2010 : Sumif & Countif with multiple condition 

Sebenarnya hal ini sudah pernah saya jelaskan dengan menggunakan Excel 2003.

(Lihat SumIf with multiple condition) 

Nah kali ini bahasan serupa akan saya ulas kembali menggunakan Excel 2010. 

Dengan menggunakan excel 2010, maka anda sudah tidak perlu lagi melakukan Ctrl+Shift+Enter untuk membuat sumif dengan array data. Cukup dengan menggunakan SUMIFS dan COUNTIFS saja 

Format : 

SUMIFS(Sum_Range,Range_Data1,Criteria1,Range_Data2,Criteria2,…) COUNTIFS(Range_Data1,Criteria1,Range_Data2,Criteria2,…) 

Sebagai contoh array data adalah sebagai berikut : 

{1,2,3,4,5,6,7,8,9,0} 

Untuk sum data yang lebih besar dari 6 -> 7+8+9 = 24, dan jumlah data yang lebih besar dari 6 adalah 3  

Caranya adalah sebagai berikut :

=SUMIFS(B19:K19,B19:K19,">6")
=COUNTIFS(B19:K19,">6")

Selamat mencoba....

Formula DMIN, DMAX, DSUM, DAVERAGE.....

Mungkin sudah banyak yang tahu, kalau di excel ada formula yang sedikit berbeda dengan formula standard.
Untuk penjumlahan biasanya kita menggunakan SUM, dan penjumlahan bersyarat bisa menggunakan SUMIF.
Nah, bagaimana dengan mencari nilai minimum dengan suatu kondisi..?
1. Bisa menggunakan array
2. Bisa menggunakan DMIN

Karena penggunaan array sudah pernah saya jelaskan, maka kali ini kita akan mencoba formula DMIN.
Syntax nya adalah sbb : DMIN(database, field, criteria)
Yang dimaksud dengan database disini adalah table yang ada header kolomnya, field adalah kolomnya dan criteria adalah kondisi yang harus dipenuhi berupa table yang ada header kolomnya juga.
Nama kolom harus sama antara database dan criteria

Contoh :

Jika kolom B3 diganti maka kolom C3 juga akan menghasilkan angka sesuai dengan syarat kolom B3 tersebut

Thanks