Loh...
Pakai Excel kok masih bingung bin panik kalo lihat formula.
Kalau bingung, coba jongkok dan pegang daun telinga milik sendiri....
Biasanya bakal jadi lebih enakan...
hehehe...
Terus terang, saya juga bingung (udah jongkok sambil pegang telinga nih)....
Supaya bingung berjamaahnya rada berkurang, kita obrolin hal ini aja ya,
sambil jongkok dan pegang telinga...
Begini :
A. fokus di sheet Contoh untuk kasus nomor 1 tentang mencari status isian
user ya....
1. Ada cells isian user di L2:L5
> User bisa entry langsung disitu, atau
> kalau diberi data validation maka user bisa memilih dari data
validation list yang disediakan (silakan dibuat sendiri kalau mau)
> atau pakai userform (seperti permintaan pemilik kasus)
2. Ada cells hasil yang menunjukkan status isian user, yaitu di L6
> Cells hasil ini akan bernilai
TRUE : ketika user mengisi seluruh L2:L5 dan sesuai dengan
definisi di tabel referensi kolom F:I
FALSE : ketika user belum mengisi sesuatu atau isiannya tidak
sesuai definisi di tabel referensi kolom F:I
> Proses di cells hasil ini dilakukan oleh sebuah formula yang berbunyi
:
=IF(LEN(L2)*LEN(L3)*LEN(L4)*LEN(L5),
COUNTIFS(_tDT_[Nama],L2,_tDT_[Status],L3,_tDT_[Pilihan],L4,_tDT_[Pendapatan],L5)
>0)
> Bahasa manusianya :
"Cel, apa bener di L2:L5 ada isinya semua [
LEN(L2)*LEN(L3)*LEN(L4)*LEN(L5)] ?,
kalo beneran ada isinya semua, cek lagi deh,
apa benar kalo isiannya ada di tabel referensi [
COUNTIFS(_tDT_[Nama],L2,_tDT_[Status],L3,_tDT_[Pilihan],L4,_tDT_[Pendapatan],L5)
>0]"
> L2:L5 ada isinya semua kalau :
a. jumlah karakter di L2 minimal ada 1 bijik -> Len(L2) akan
menghitung jumlah karakter di L2, 0 kalau kosong, >0 kalau ada isinya
b. jumlah karakter di L3 minimal ada 1 bijik -> Len(L3) akan
menghitung jumlah karakter di L3, 0 kalau kosong, >0 kalau ada isinya
c. jumlah karakter di L4 minimal ada 1 bijik -> Len(L4) akan
menghitung jumlah karakter di L4, 0 kalau kosong, >0 kalau ada isinya
d. jumlah karakter di L5 minimal ada 1 bijik -> Len(L5) akan
menghitung jumlah karakter di L5, 0 kalau kosong, >0 kalau ada isinya
e. semua syarat a sampai d terpenuhi alias pakai logika AND yang
setara perkalian.
* kalau 1 aja dari a sampai d ada yang kosong, Len-nya akan bernilai
0, maka perkalian dengan 0 akan menghasilkan 0
* kalau semua (dari a sampai d) ada isinya, semua hasil Len selalu
>0 (tidak ada yang 0, dan minimal 1), sehingga perkaliannya akan
menghasilkan suatu nilai >0
> Jadi, bagian biru menghasilkan >0 (L2:L5 ada isinya semua), maka
bagian merah akan dikerjakan. Kalau bagian biru menghasilkan 0, maka *tidak
ada yang dikerjakan*, sehingga nilai hasil dari IF akan menggunakan default
bagian FALSE (pada formula, bagian ini dikosongkan karena memang *defaultnya
akan berupa nilai FALSE*)
> Pada kondisi biru >0 yang akan membuat si merah dikerjakan. Si
merah berisi
proses menghitung jumlah record yang sesuai (pakai CountIFs karena ada
banyak kriteria [4 kriteria bray... yaitu kriteria berdasar nilai-nilai di
L2:L5])
a. hitung jumlah records yang sesuai kriteria (ada banyak kriteria)
-> COUNTIFS( ...... )
b. kriteria 1 : referensi kolom Nama = isian Nama oleh user ->
_tDT_[Nama]
, L2
c. kriteria 2 : referensi kolom Status = isian Status oleh user
-> _tDT_[Status]
, L3
d. kriteria 3 : referensi kolom Pilihan = isian Pilihan oleh
user -> _tDT_[Pilihan]
, L4
e. kriteria 4 : referensi kolom Pendapatan = isian Pendapatan oleh
user -> _tDT_[Pendapatan] , L5
f. antar kriteria dihubungkan oleh karakter koma (regional setting
english ya...) -> ada 3 koma diantara jejeran definisi kriteria di atas
* Hasil si CountIFs() ini adalah 0 kalau di referensi *tidak
ditemukan record yang memenuhi seluruh kriteria*
* Hasil si CountIFs() ini adalah >0 kalau di referensi ada (minimal
1 record) yang memenuhi seluruh kriteria
g. karena yang dibutuhkan adalah status isian user yang bener sesuai
definisi tabel referensi, maka hasil CountIFs() di cek harus >0
itu sebabnya ada proses pembandingan berbunyi *>0*
h. hasil perbandingan (oleh bagian *>0*) akan menghasilkan
TRUE kalau beneran si CountIFs() menghasilkan *>0*
FALSe kalau ternyata si CountIFs() menghasilkan 0
> akhirnya, cells L6 akan berisi nilai :
TRUE : kalau isian user sesuai definisi referensi karena
CountIFs() >0
FALSE : kalau ::
1. ada 1 bijik aja dari L2:L5 yang masih
kosong akibat LEN(L2)*LEN(L3)*LEN(L4)*LEN(L5)
2. L2:L5 semuanya terisi, tapi hasil
CountIFs() adalah 0
> yang nomor 1 selesai disini....
Mohon dipahamken dulu bagian ini, supaya ndak bingung ketika beranjak ke
nomor 2...
Inti sari yang perlu dipahamken :
1. Bagaimana cara cek bahwa inputan yang dibutuhkan ada isinya ? (salah
satu contohnya, lihat bagian Len-Len-an yang dikali-kaliken)
2. Bagaimana cara cek bahwa ada suatu barang yang sesuai kriteria tertentu
? (salah satu contohnya, lihat bagian CountIFs)
Konsep ini bisa digunakan dimana saja, kapan saja, dan oleh siapa saja,
pada apa saja, bahkan bisa dengan cara apa saja yang mirip.
dah ya... asumsinya inti sari nomor 1 sudah nempel ya...
B. fokus di sheet Contoh untuk kasus nomor 2 tentang mencari nama-nama yang
sesuai kriteria pilihan user
1. ada cells inputan user di O2:O4
> User bisa entry langsung disitu, atau
> kalau diberi data validation maka user bisa memilih dari data
validation list yang disediakan (silakan dibuat sendiri kalau mau)
> atau pakai userform (seperti permintaan pemilik kasus)
2. Ada cells proses yang menunjukkan jumlah record yang sesuai kriteria
pilihan user, yaitu di O5
> Cells hasil ini akan bernilai
-1 : kalau O2:O4 ada yang masih kosong
0 : kalau O2:O4 semuanya terisi, tapi *tidak ada* record yang
ditemukan di referensi berdasar pilihan user
>0 : kalau O2:O4 semuanya terisi dan ditemukan minimal 1 record
di referensi yang sesuai kriteria pilihan user
> Cells ini diproses dengan formula berbunyi : (lihat kesesuaian warna
warni antara bunyi formula dengan penjelasan di atas)
=IF(LEN(O2)*LEN(O3)*LEN(O4),
COUNTIFS(_tDT_[Status],O2,_tDT_[Pilihan],O3,_tDT_[Pendapatan],O4),-1)
> Jadi, kalau si biru menghasilkan
0, hasilnya si merah
>0, si latar kuning akan diproses dan si latar
kuning bisa menghasilkan nilai :
0 yang berarti *tidak ada*
record yang ditemukan di referensi berdasar pilihan user
>0 yang berarti ditemukan
minimal 1 record di referensi yang sesuai kriteria pilihan user
> cells hasil ini lalu di format number (klik kanan cells O5 -> format
cells -> tab Number -> opsi Custom -> lihat isian textbox formatting)
0" Orang";;0" Orang"
bagian biru -> format yang dipakai ketika cells tersebut berisi
nilai >0 :: dipakai ketika CountIFs bernilai >0
bagian merah -> format yang dipakai ketika cells tersebut berisi
nilai =0 :: dipakai ketika CountIFs bernilai 0
bagian antara 2 bijik titik koma -> format yang dipakai ketika
cells tersebut berisi nilai <0 :: dipakai ketika Len-Len-an menghasilkan -1
3. Ada tabel referensi (kolom A:I) yang terdiri dari 3 bagian utama proses,
yaitu :
a. data dasar dari referensi -> kolom F:I :: kalau mau
menambah referensi baru, isi di kolom ini saja. Jangan sentuh kolom lainnya
b. kolom informasi pilihan user (2 kolom) -> kolom D:E, dengan :
kolom D : berisi formula membandingkan setiap nilai
data referensi dengan isian user di O2:O4 yang dikenai syarat harus sama
dan semuanya terpenuhi.
kolom E : berisi formula membuat nomor urut record
data dasar referensi yang sesuai kriteria pilihan user.
* yang dipakai adalah nomor pertama
yang ditemukan bila ada banyak nomor urut yang sama.
* contoh, kalau ada nomor urut 1 di
kolom D di beberapa baris, maka yang dipakai adalah yang ditemukan pertama
c. kolom data referensi tertata untuk proses output -> kolom
A:C :: kolom ini bisa ditambahkan bila output membutuhkan kolom lain
kolom A : (fix) bereisi nomor urut data referensi
tertata, selalu dimulai dari nomor 1 sampai sebanyak jumlah record data
dasar
kolom B : berisi formula lookup berdasar nomor urut
kolom A untuk mengambil data dasar yang dibutuhkan output
* pada kasus ini, yang dibutuhkan
adalah data dasar kolom NAMA, maka formula kolom B berbunyi :
=VLOOKUP(A2,_tDT_[[IDRec]:[Nama]],2,0)
dengan : A2 adalah nomor urut data
referensi tertata
_tDT_[[IDRec]:[Nama]] adalah sumber data pencarian sampai kolom yang
dibutuhkan
(selalu diawali dari kolom nomor urut record data dasar sesuai kriteria
yang pada kasus ini dimulai dari kolom E)
kolom C : berisi formula penggabungan nilai-nilai kolom
B agar membentuk 1 teks berisi seluruh nilai kolom B
* kolom ini dibuat karena dibutuhkan
sebagai output yang menuntut daftar nama berupa sebuah teks panjang
yang bisa ditulis ke dalam sebuah
cells saja, atau ke sebuah object control Label maupun TextBox.
* jika outputnya tidak membutuhkan
hal ini, maka kolom C bisa dihapus agar prosesnya menjadi ringan
4. Dengan adanya tabel referensi (nomor 3 di atas) yang sudah menyediakan
seluruh data untuk output, maka kebutuhan output untuk menampilkan daftar
nama sebagai sebuah teks panjang bisa ditampilkan di cells O6
> Cells O6 berisi formula untuk mengambil nilai terakhir yang OK (bukan
error value) dari kolom C (hasil penggabungan seluruh nama)
=IF(O5>0,MID(LOOKUP("z",_tDT_[Gabung]),3,999),"")
> Bahasa manusianya :
"Cel, kalau ada record referensi yang sesuai pilihan user [O5>0],
ambil nilai teks terakhir dari kolom C (gabungan teks
nama yang sesuai kriteria saja) [ LOOKUP("z",_tDT_[Gabung]) ]
lalu buang delimiter di awal nilai hasilnya (ambil mulai
karakter pertama yang bukan teks delimiter) [ MID( , 3 , 999 ) ].
Lah kalau tidak ada referensi yang sesuai pilihan user, diisi
karakter kosong saja deh [ "" ]."
> Jadi, hasil output di cells O5 ini nantinya bisa digunakan untuk
ditampilkan diberbagai media, seperti di control object label maupun
textbox, yang ada di suatu userform maupun di suatu worksheet. Bahkan bisa
ditampilkan melalui media shapes umumnya (bukan shapes berupa control
object) seperti shapes rectangle, circle, dsb, termasuk chart.
5. Kebutuhan untuk menampilkan output yang berupa suatu daftar nama berupa
list, arrays, records, bisa dilakukan dengan mengakses kolom B di tabel
referensi.
> Jadi, kolom B (bisa jadi ada kolom lainnya bila memang dibutuhkan dan
telah dibuat) dapat ditampilkan melalui berbagai media, seperti fitur Pivot
Table, control object ListBox maupun ComboBox yang membutuhkan suatu array
item (baik di userform mauun di worsheets), axis label suatu chart, dsb.
phew... selesai juga...
Simpulan....
A. Tetaplah berusaha untuk memiliki data yang baik (lihat kolom A:I yang
memiliki data dasar di F:I)
B. Manfaatkan kemampuan Excel seoptimum mungkin dengan berkreasi diluar
rutinitas cara akal Anda bekerja (bukan out of the box kok, cuman jangan
prosedural ikut sop atau kebiasaan nenek moyang yang gak episien)
C. Terus belajar dan jangan pernah jumawa dengan rasa sudah bisa atau sudah
pernah ketemu, karena jumawa adalah awal berhentinya kemampuan dan kemauan
untuk maju.
;)
Ayo terus belajar bersama dengan penuh semangat....
yaiy... hidup belajar... hidup berbagi... (kok cuman sendirian nih
teriak-teriaknya... hadeh... mlipir aja ah...)
Regards,
Kid
2016-01-19 8:53 GMT+07:00 Danny Prasetyo [email protected]
[belajar-excel] <[email protected]>:
>
>
> Wah... terimakasih master Kid. Ternyata bisa kaya gitu yaa, hehehe
> Tapi saya malah rumit lihat rumus yang di sheet nya.
>
> Regards,
>
> Danny Prasetyo
>
>
>
