Excel Lambat ??? (Series 2)

Melanjutkan dari artikel sebelumnya Excel Lambat ??? (Series 2), akan saya tambahkan mengapa kelemotan excel bisa terjadi.

Selanjutnya adalah Kesalahan Design, design excel yang baik adalah berurutan penempatan data dan perhitungan, urutan yang baik adalah dari Kiri Atas ke Kanan Bawah.
Mengapa demikian ? karena excel menggunakan metode forward reference yang cara perhitungannya adalah dari kiri atas ke kanan bawah, jika excel anda kecil, hal ini tidak akan terlalu terlihat, jika excel anda besar, maka hal sepele ini akan menyumbangkan kelemotan.

Next , Hapus baris dan kolom yang tidak digunakan di posisi baris paling akhir dari data anda kebawah, atau kolom paling akhir dari data anda ke kanan.
Karena terkadang setelah kita menggunakan baris atau kolom yang kita lakukan adalah delete datanya saja, sehingga excel masih akan mencatat baris dan kolom tersebut digunakan.

Berikutnya adalah formula IF, terkadang kita tidak ingin menampilkan hasil dari formula kita dalam bentuk #N/A, #VALUE, dll, sehingga kita menggunakan fungsi IF untuk menghindari hal itu.
Nah formula yang biasa dituliskan adalah sbb :
=IF(iserror(expression), true_result, false_result)
Nah untuk menggantikan cara ini anda bisa menggunakan IFERROR
=IFERROR(expression, result)
(Kembali lagi, harus dilihat apakah memungkinkan untuk mengganti kan IF dengan IFERROR ini)

-. Jika memungkinkan ganti formula sumproduct dengan sumifs dan countifs
-. Untuk calculation range tertentu gunakan trik ini , block cell tertentu kemudian tekan CTRL + H find "=" kemudian replace "="

Nah apalagi ya yang lain ?

Excel Lambat ??? (Series 1)

Pernah mengalami performa file excel anda lambat ?
Jika anda bekerja dengan jumlah data yang banyak, dan menggunakan perhitungan yang banyak pula , maka file excel anda akan mengalami penurunan kecepatan dalam perhitungannya...

Kira kira penyebabnya apa ya ?

Pernah dengan istilah Volatile Function ? fungsi-fungsi yang dikategorikan sebagai volatile ini sifatnya akan dikalkulasi ulang setiap :
1. Klik pada Row dan Column divider
2. Insert, Delete Baris dan Kolom
3. Membuat, menghapus dan mengubah Define Name
4. Mengubah nama Worksheet
5. Mengubah urutan Worksheet
6. Menjalankan Filter, Hide & Unhide Baris dan Kolom
7. Open dan Save Workbook

WOW ??? mengagetkan bukan ?

Nah fungsi apa saja sih yang dikategorikan sebagai Volatile Function :
1. RAND()
2. NOW()
3. TODAY()
4. OFFSET()
5. CELL()
6. INDIRECT()
7. INFO()

Selain fungsi-fungsi diatas ternyata Conditional Formating juga mempunyai sifat Volatile.

Penyebab kelemotan excel anda berikutnya adalah Link data ke File Excel lain, dan juga Link data ke Worksheet yang lain, oleh sebab itu selalu disarankan untuk meletakkan semua perhitungan dalam 1 Worksheet (jika memungkinkan)

Untuk penyebab kelemotan lainnya akan saya posting pada artikel selanjutnya.

Membuat Daftar Sheet Dengan Formula

Terkadang kita bekerja dengan menggunakan banyak Sheet, dan untuk mempermudah kita membuka sheet tertentu, akan lebih baik jika kita mempunyai Sheet Menu, dimana di dalam sheet tersebut berisikan daftar Sheet yang ada.
Proses manual tetap saja bisa dilakukan, tetapi untuk orang "malas" sudah bukan jamannya lagi pakai cara manual...

Nah bagaimana cara membuatnya ?
Ada 2 cara, yang pertama menggunakan macro, bagi macro mania pasti mudah sekali ya, cukup looping sejumlah sheet, kemudian tuliskan pada sheet Menu.
Cara kedua yang akan saya bahas disini adalah menggunakan Formula.

Caranya adalah sebagai berikut :
1. Buatlah Name dengan nama DaftarSheet, scopenya Workbook dan Formulanya adalah :

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Syntax GET.WORKBOOK bisa anda lihat disini

Match With Multi Criteria

Fungsi MATCH berfungsi untuk mendapatkan posisi suatu nilai dalam suatu array.

Syntax : MATCH(Lookup_Value, Array_Data, Match_Type)

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data

http://excelformulaandmacro.blogspot.com/2010/10/fungsi-match.html

Bagaimana jika ada lebih dari 1 kondisi ?

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

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

Penggunaan Array

Sebagai tambahan informasi pada tulisan saya sebelumnya yaitu tentang SumIf with multiple condition , caranya adalah dengan memanfaatkan array. Nah cara ini tidak hanya berlaku untuk SUM, tetapi anda juga bisa mengaplikasikan dengan fungsi-fungsi yang lain seperti MAX, MIN, Average, dll

Dan yang perlu diingat bahwa penggunaan array sebaiknya seminimal mungkin, karena terlalu banyak array dalam worksheet anda akan mempengaruhi performa excel anda. Jadi gunakan cara ini jika memang fungsi standard di excel sudah mencukupi kebutuhan anda....

SumIf with multiple condition

Sumif pada excel hanya bisa digunakan untuk 1 kondisi saja, misalnya ">2" atau "=0".
Syntax SumIf : "=sumif(range, criteria, [sum_range])"

Bagaimana jika kita ingin melakukan sumif tetapi dengan menggunakan lebih dari 1 kondisi ?
Solusinya adalah kita bisa memanfaatkan array, bagi yang belum mengerti tentang istilah array, artinya adalah sekumpulan/himpunan data.

Himpunan data bisa digambarkan sebagai berikut :
{1,2,3,4,5,6,7,8,9,0}

Syntac untuk menggunakan sumif dengan array sedikit berbeda dengan sumif pada umumnya.
Syntac untuk Sumif dengan multi kondisi adalah sbb :
=Sum(if(filter,if(filter,if(filter,[sum_range]))))
akhiri dengan menekan tombol Shift + Ctrl + Enter
Jika penekanan tombol sudah benar maka hasilnya akan tampak pada kolom formula sbb :
{=Sum(if(filter,if(filter,if(filter,[sum_range]))))}

contoh untuk pengisian filter adalah sbb:
$A1:$A10 > 5
(Cara baca : dalam cell A1 hingga A10 manakah yang nilainya lebih besar dari 5)
Jika kita lakukan evaluate formula maka hasilnya akan nampak sbb :
{False,False,False,False,False,False,True,True,True,True}

Note : Anda boleh saja mengartikan False = 0 dan True = 1

Dan sum_range yang akan masuk dalam perhitungan adalah posisi nilai , dimana semua filternya menghasilkan True, jika ada satu saja dari filter pada posisi nilai tersebut menghasilkan False, maka nilai tersebut tidak akan masuk dalam perhitungan.

Misal :
Kolom A1:A6 --> Data : {1,2,3,4,5,6}
Filter 1 : Data >3 --> Hasilnya adalah : {False,False,False,True,True,True}
Filter 2 : Data <6 --> Hasilnya adalah : {True,True,True,True,True,False}

Maka jika diformulakan : "=Sum(if(A1:A6>3,if(A1:A6<6, A1:A6)))"
Hasilnya adalah 9
Mengapa bisa 9 ? karena posisi array yang semuanya menghasilkan True ada pada posisi ke 4 dan 5

Semoga membantu
Thanks

Multi Condition for Conditional Formatting

Conditional Formatting adalah feature yang disediakan oleh excel untuk membantu kita dalam pembacaan data. Dengan conditional formatting ini, banyak hal yang bisa kita lakukan, misalnya mengubah warna cell/font jika nilai dalam cell tersebut sesuai dengan ketentuan
Untuk mengakses menu conditional Formating lihat pada gambar dibawah :

Akan muncul form berikut

Sebagai contoh, jika kita ingin mengetahui jumlah pengunjung yang lebih dari 400, lakukan :

1. Block kolom jumlah pengunjung

2. Masuk dalam menu conditional formatting

3. Pada Kolom 1 isi dengan Cell Value Is

4. Pada Kolom kedua isi dengan Greater Than

5. Pada kolom ketiga isikan angka 400

6. Kemudian tekan tombol Format

7. Pilih tab Patterns, kemudian pilih warna merah

8. Sebagai gambaran bisa dilihat pada image dibawah :

 

Hasil dari conditional formating bisa di lihat dibawah :

 

Nah, sekarang bagaimana caranya untuk membuat conditional formatting lebih dari 1 syarat ?

Misalnya yang diminta adalah tandai data Bulan July dengan Tahun 2010

Caranya adalah sbb:

1. Pilih Formula Is

2. Isikan formula seperti pada gambar

=AND($B74=$B$72,$C74=$C$72)

Fungsi INDIRECT

Fungsi INDIRCT berfungsi untuk mengubah text menjadi formula.
Sebagai contoh :
Cell A1 diisi dengan angka 5
kemudian cell A2 diisi dengan =INDIRECT("A1")
maka hasilnya adalah angka 5

Fungsi indirect juga bisa digunakan untuk lintas sheet
misal =INDIRECT("Sheet1!A1")

Kelemahan fungsi INDIRECT adalah jika digunakan cukup banyak dalam satu file excel, maka kinerja excel akan sangat lambat, hal ini disebabkan pada saat kita melakukan perhitungan di excel, maka akan dijalankan secara otomatis autocalculation.

Fungsi INDIRECT ini bisa juga disebut sebagai volatile function
Selain INDIRECT ada beberapa volatile function antara lain :
CELL() 
OFFSET()  
TODAY()  
NOW()  
INFO()  
RAND()

Untuk menghindari penurunan performance, sebaiknya seminimal mungkin menggunakan volatile function tersebut

Thanks
Ferry Setiawan

Fungsi MATCH dalam VLOOKUP

Seringkali dalam membuat suatu perhitungan , kita menggunakan fungsi VLOOKUP untuk mendapatkan nilai sesuai dengan lookup_value yang kita kehendaki

Syntax : VLOOKUP(lookup_value, array_date,column_index,[range_lookup])

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data
Column_Index : Nomor kolom berdasarkan array_data
Range_Lookup :
-. Jika diisi dengan True, maka hasilnya akan sesuai dengan lookup_value, tetapi jika lookup_value tidak ditemukan dalam array_data maka hasilnya adalah nilai terbesar yang paling mendekati dengan lookup_value yang akan muncul
-. Jika diisi dengan False, maka hasilnya akan sesuai dengan lookup_value, tetapi jika lookup_value tidak ditemukan dalam array_data maka hasilnya adalah #N/A

Bagaimana jika dilakukan insert column pada column C ?

hasilnya adalah sebagai berikut :

Pada column Result hasilnya menjadi kacau, hal ini disebabkan karena Column_Index bersifat statis, tidak bisa mengikuti perubahan column.

Bagaimana solusinya ?

Solusinya kita bisa menggunakan Fungsi MATCH di dalam VLOOKUP

Setelah di lakukan insert Column C, hasilnya adalah sebagai berikut

Hasilnya sama dengan sebelum di lakukan insert column

Semoga bermanfaat...

Fungsi MATCH

Fungsi MATCH berfungsi untuk mendapatkan posisi suatu nilai dalam suatu array.

Syntax : MATCH(Lookup_Value, Array_Data, Match_Type)

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data

Match Type :

-. Jika 1, hasilnya adalah posisi dimana Lookup_Value berada, atau posisi nilai terbesar yang paling mendekati dengan Lookup_Value, syaratnya adalah array_data harus berurutan (kecil ke besar)

-. Jika 0, hasilnya adalah posisi dimana lookup_value berada, dan datanya tidak harus berurutan

-. Jika -1, hasilnya adalah posisi dimana Lookup_Value berada, atau posisi nilai terkecil yang paling mendekati dengan Lookup_Value, syaratnya adalah array_data harus berurutan (besar ke kecil)

-. Jika tidak diisi maka secara default akan dianggap 1

Contoh

Thanks

Ferry Setiawan

Fungsi INDEX

Fungsi INDEX dalam excel dapat kita gunakan untuk mendapatkan suatu nilai dari baris dan kolom yang kita kehendaki.

ntax:

INDEX(array, row, column)












array : adalah range untuk data , dalam contoh ini adalah di column B
row : adalah baris data dari array
column : adalah kolom data dari array, dalam contoh dituliskan 1, karena array data hanya 1 kolom (perhitungan nomor kolom tidak dihitung dari kolom A, tetapi dimulai berdasarkan kolom dalam range data, dalam contoh adalah kolom B)

Mengubah Baris Menjadi Kolom

Bagaimana cara untuk mengubah tampilan row data menjadi column data ?

Ada beberapa cara :
1. Manual, yaitu dengan menggunakan copy & paste special ,aktifkan option transpose
2. Formula, misal data yang ada berada dalam range (A1:A5), kemudian akan di mengubah menjadi column data (misal range(B1:F1)), lakukan langkah langkah berikut :

• Block range(B1:F1)
• ketikkan formula "=transpose(B1:F1)"
• tahan Ctrl+Shift
• masih dalam keadaan menekan tombol Ctrl+Shift, tekan tombol enter