Dear All Excelers..
Untuk kalian yang sudah sering pakai excel tentunya seringkali browsing kebutuhan khusus yang memang jarang ditemukan.
Kita terkadang merasa seharusnya bisa menjalankan suatu fungsi di luar fungsi umum. Nah pas saat browsing ketemu tapi kadang ga kita save dan kelupaan.
Nah inilah salah satu solusi saya untuk buat kumpulan rumus ini di blog saya, semoga ada yang pernah pakai dan terbantu dengan postingan ini.
Saya sengaja tidak menulis lengkap karena hanya untuk kebutuhan pribadi, mungkin suatu saat akan saya buat konten penjelasan jika diperlukan.
1) Automatic Adjust Cell Number
Terkadang kita ada kebutuhan untuk melakukan penambahan di identitas baris dalam cell. Misalnya jika kita ingin membuat cell A1 menjadi A3 tetapi dengan menggunakan rumus A(2+1). Apakah hal ini bisa dilakukan?? Tentu saja bisa..
Untuk hal ini kita bisa menggunakan fungsi contoh berikut :
=ADDRESS(2,3,1,1,"[Book1]Sheet1")
Result => '[Book1]Sheet1'!$C$2
Penjelasan per urutan syntax :
- 2 : Posisi Row (2)
- 3: Posisi Kolom (C)
- 1 : Absolut ($)
- 1 : Style Huruf, No Baris (bukan RC)
- Book1 : Nama Buku Kerja
- Sheet1 : Nama Sheet
Ini hanya sekedar merumuskan dari cell saja tapi untuk menampilkannya kita pakai Indirect
Contoh pemakaian : INDIRECT(ADDRESS(2,3,1,1,"[Book1]Sheet1")
Maka akan menampilkan value yang di cell [Book1]Sheet1'!$C$2
2) Lookup Vertikal Multiple Criteria
Biasanya kita pakai Vlookup untuk mencari suatu nilai dalam bentuk tabel secara vertikal. Tetapi jika ternyata kita memerlukan untuk lookup menggunakan lebih dari 1 kriteria bagaimanakah caranya?
Untuk hal ini kita pakai Fungsi index dan match yang sangat powerful.
Contoh untuk rumusnya seperti ini :
{=INDEX(range0,MATCH(1,(A1=range1)*(B1=range2)*(C1=range3),0))}
Penjelasan :
- Range 0 : Kolom source bagian hasil
- 1 : True
“Look through the rows in the data and return the row number where all of the criteria are TRUE”
- Range 1 : Kolom source bagian kriteria 1
- Range 2 : Kolom source bagian kriteria 2
- Range 3 : Kolom source bagian kriteria 3
- A1, B1, C1 : nilai yang ingin dilookup ke tabel source per kriteria
- 0 : Refer ke Column 1 (karena kita hanya menggunakan referensi 1 kolom tabel maka diisi 0)
Jangan lupa gunakan Ctrl + Shift + Enter untuk mengaktifkan fungsi array
3) Mencari Nilai Maximum sesuai Kriteria tertentu
a. 1 Kriteria
=MAX( IF(Range 1=Kriteria, Range Max) )
Penjelasan :
- Range 1 : Kolom source
- Range Max : Range yang ingin dicari nilai maxnya
Jangan lupa gunakan Ctrl + Shift + Enter untuk mengaktifkan fungsi array
b. Lebih dari 1 Kriteria
=MAXIFS(Range Max, Range 1, Kriteria 1, Range 2, Kriteria 2)
Penjelasan :
- Range 1 : Kolom Source 1
- Range 2 : Kolom Source 2
- Range Max : Range yang ingin dicari nilai maxnya
Misal ingin mencari nilai max berdasarkan kolom tanggal (source 1) dan nama (source 2)
Jangan lupa gunakan Ctrl + Shift + Enter untuk mengaktifkan fungsi array
4) Sumif Multiple Criteria menggunakan Or Criteria
=SUM(SUMIFS(Range Sum, Range 1 ,{"kriteria 1x","kriteria 1y"}, Range 2,{"kriteria 2x";"kriteria 2y";"kriteria 2z"}))
Penjelasan :
- Range Sum : Kolom yang akan dijumlahkan
- Range 1 : Kolom Source 1
- Range 2 : Kolom Source 2
- Kriteria 1x atau 1y : Salah 1 kriteria yang ada di kolom source 1
- Kriteria 2x atau 2y atau 2z : Salah 1 kriteria yang ada di kolom source 2
Untuk Single criteria rumus identik hanya saja ditukar posisi untuk Range sum dan Range sourcenya
=SUM(SUMIF(Range 1, {"kriteria 1x","kriteria 1y"}, Range Sum))
Jangan lupa gunakan Ctrl + Shift + Enter untuk mengaktifkan fungsi array
99) Others
- Sum if dengan kriteria semua yang ada nilainya
"*"
- Sum if dengan kriteria lebih besar dari nilai x
">x"
- Menambahkan karakter spasi pindah baris
char(10) lalu wrap text hasilnya
- Text merubah format desimal menjadi persentase
TEXT(value,"0.0%")
5) Membuat Time Stamp
Aktifkan dulu "Enable Iterative Calculation" di File => Option => Formula
Masukkan formula berikut di kolom yang akan diberi timestamp
=IF(Cell entry <>"",IF(Cell Timestamp <>"",Cell Timestamp,NOW()),"")
6) Menghitung huruf spesific di dalam kata
=LEN(Cell)-LEN(SUBSTITUTE(Cell, karakter ,""))
Contoh karakter : "-"
