Dear,
Terima kasih atas penjelasan dan rumusnya.
Kalau seandainya rujukan tabelnya dibuat lebih dari 1 kolom bisa gak...???
Misalnya rumus:
=IF(COUNTIF('DAFTAR PINJAM'!$C$3:$C$51,B2) > COUNTIF('DAFTAR
KEMBALI'!$C$3:$C$26,B2),
INDEX('DAFTAR PINJAM'!$B$3:$B$51,
MAX(IF('DAFTAR PINJAM'!$C$3:$C$51=B2,'DAFTAR
PINJAM'!$A$3:$A$51))),"Ada")
kita jadikan seperti ini:
=IF(COUNTIF('DAFTAR PINJAM'!$C$3:$G$1000,B2) > COUNTIF('DAFTAR
KEMBALI'!$C$3:$G$990,B2),
INDEX('DAFTAR PINJAM'!$B$3:$B$1000,
MAX(IF('DAFTAR PINJAM'!$C$3:$G$1000=B2,'DAFTAR
PINJAM'!$A$3:$A$1000))),"Ada")
Hal tersebut dikarenakan peminjam bisa meminjam lebih dari 1 buku (maksima 5
buku per satu kali pinjam). Dan bagaimana memberi informasi pada kolom
keterangan bahwa buku yang telah dipinjam user peminjam dinyatakan hilang
(TIDAK KEMBALI)...???
Mohon berkenan solusinya.
Terima kasih,
Harry
N/B:
1. Kolom C s/d G pada Sheet "DAFTAR PINJAM" dan "DAFTAR KEMBALI" sebagai sudah
terisi dan sebagaian besar masih kosong karena belum diisi user peminjam. Mis.
C3 s/d G51 sudah terisi sedangkan C52 s/d G1000 masih kosong).
2. Sheet "DAFTAR KEMBALI" dibuat sampai dengan $G$990 dikarenakan untuk
memperkirakan buku hilang (TIDAK KEMBALI).
3. Saya juga sudah mencoba (dan terus mencoba) mengkotak-katik rumus tersebut
yang mengacu pada rumus yang pernah dijabarkan oleh Mr. Kid (dari topik
pembahasan lain - "Lacak Angka") tapi sampai sekarang masih belum berhasil.
________________________________
Dari: o' Seno <[email protected]>
Dikirim: Kamis, 2 Februari 2012 10:01
Cara menggabung formula perantara (helper) menjadi mega formula
Formula perantara (helper) ada di kolom J sampai N
Lihat cell J2
=COUNTIF('DAFTAR PINJAM'!$C$3:$C$51,B2)
ternyata tidak mengandung formula perantara lain, dia langsung merujuk ke tabel
rujukan
jadi di dalam formula ini tidak ada bagian (komponen formula) yg perlu di
SUBSTITUSI dengan
formula / cell lain, cukup diciuekin saja...
Demikian juga dengan Cell K2
=COUNTIF('DAFTAR KEMBALI'!$C$3:$C$26,B2)
Sekarang lihat Cell L2
= K2 < J2
Nah dia mengandung cell lain yg berisi formula_perantara (helper); bahkan ada
dua cell helper
yg dirujuk nya; Jadi sekarang gantilah formula di cell L2 sbb
Text "K2" (didalam formula LS) diganti dengan TEXT FORMULA yg ada di cell K2;
sehingga formula L2 menjadi
=COUNTIF('DAFTAR KEMBALI'!$C$3:$C$26,B2) > J2
Satu lagi, gantilah TEXT "J2" yg ada di cell K2 itu dengan TEXT FORMULA yg
ada di cell J2 sehingga formula di L2 manjadi
=COUNTIF('DAFTAR PINJAM'!$C$3:$C$51,B2) > COUNTIF('DAFTAR
KEMBALI'!$C$3:$C$26,B2)
Berikutnya lihat array formula Cell M2
{=MAX(IF('DAFTAR PINJAM'!$C$3:$C$51='DAFTAR BUKU'!B2,'DAFTAR
PINJAM'!$A$3:$A$51))}
karena tidak mengandung helper di lain cell, maka kita diamkan apa adanya.
Nantinya M2 ini lah yg akan diisikan ke formula akhir.
Tahap Terakhir:
Text Formula yg ada di M2 di copykan di P2; Lihatlah text formula yg sekarang
ada di P2 tsb
dengan "seksama dan dlm tempoh yg sesingkat-singkatnya"
=IF(L2,INDEX('DAFTAR PINJAM'!$B$3:$B$51,M2),"Ada")
Di situ ada L2 dan M2
Nah sekarang TextFormula yg ada di cell L2 di copy textnya lalu disubstitusikan
(untuk mengganti
"L2" yg ada di Formula P2
Demikain juga Text formula yg ada di cell M2 disubstitusikan ke :M2: yg ada di
formula P2.
Detail caranya
Cara mengopy formula / cell berisi formula adalah LAIN dengan cara mengcopy
TEXT formula-nya
Text Formula, bisa di copy di FORMULA BAR
- select Cellnya (misal M2)
- Klik Formula Bar ( atau tekan tombol F2)
- Blok lah seluruh TEXT yg ada di Formula Bar
- Tekan Ctrl + C (dicopy ke clip board)
Tekan Esc
Menpaste ke bagian dari formula Tujuan
- Select cell Tujuan, (misal P2)
- Blok BAGIAN (part) Text pada formula tsb yg akan disubstitusi, misal contoh
di atas: "M2" yg ada di cell P2
- tekan Ctrl + V (mempaste data yg ada di clip board)
Maka "M2" sudah disubstitusi oleh text formula lain ( ex cell M2)
Jangan lupa di-akhirinya dengan Ctrl+Shit+Enter (karena formula tsb adlaah
Array Formula
Hasil akhir penggabunan cel helper itu akira kira sbb:
=IF(COUNTIF('DAFTAR PINJAM'!$C$3:$C$51,B2) > COUNTIF('DAFTAR
KEMBALI'!$C$3:$C$26,B2),
INDEX('DAFTAR PINJAM'!$B$3:$B$51,
MAX(IF('DAFTAR PINJAM'!$C$3:$C$51=B2,'DAFTAR
PINJAM'!$A$3:$A$51))),"Ada")
Dan mulai hari ini anda sudah bisa membuat MEGA FORMULA (formula panjang yg
didalamnya memanfatkan
/berisi banyak fungsi) ! Dan formula yang ini = array formula pula !
Mungkin anda heran sendiri terhadap hasil kerja anda ini.
Selamat.., dan jangan bingung lagi cara menggabung formula.
Pelajaran seperti itu ada di BUKU (excel tingkat II), dan kalau anda hari ini
masih bilang belum bisa (padahal
anda sudah main makro sejak lama) sebetulnya cukup mengherankan juga, Kok bisa
ya ?
----- cut / mods ----
