Hai Mr. Kid,
Penjelasan yang Mr. Kid berikan sangat memuaskan dan bisa dipahami, sehingga
pertanyaan ke 2 sudah bisa diatasi dan sudah tahu apa yang harus dilakukan pada
formula tersebut.
Saat ini saya sudah bisa menerapkan 3 cara untuk mengatasi kasus "Pengelompokan
Umur Berdasarkan Kriteria" berdasarkanpenjelasan yang Mr. Kid berikan ditambah
1 cara yang deberikan Pak Agus. Terima Kasih Mr. Kid, Pak Agus dan BeExcel
semua.
Salam,
Leo
On Thursday, September 11, 2014 2:10 PM, "'Mr. Kid' mr.nm...@gmail.com
[belajar-excel]" <belajar-excel@yahoogroups.com> wrote:
Hai Leo,
Tentang :
1. Formula berbunyi : (cell D5 sebagai cell yang dipasangi formula alias cell
aktif si formula)
=REPT("x",LOOKUP(DATEDIF($B5,$D$1,"m"),{1,7,13,25,72,156,181},{1,2,3,4,5,6,7})=COLUMNS($D$4:D$4))
>> Bagian :
Rept( teks , N ) -> untuk menulis teks berulang-ulang sebanyak N kali. Jika N
adalah 0 maka hasilnya NULLSTRING.
LookUp()=Columns() -> berupa perbandingan, yang hasilnya TRUE setara 1 atau
FALSE setara 0. Hasil perbandingan ini sebagai pengisi nilai N pada fungsi Rept
di atas. Artinya, dibutuhkan nilai 1 atau TRUE jika di kolom itu harus ditulisi
sesuatu karena umur data sesuai untuk grup di kolom itu. Grup umur bisa diberi
kode nomor grup dari 1 sampai sekiun. Hal ini sama saja dengan jumlah kolom
mulai suatu cell yang tetap sampai cell aktif disebelah kanannya.
Columns() -> menghitung jumlah kolom dari area yang dirujuk. COLUMNS($D$4:D$4)
-> dari cell yang selalu D4 sampai kolom yang sedang aktif di baris 4 nya (pada
formula, cell aktif sedang di kolom D di baris 5).
LookUp() -> fungsi ini digunakan untuk menghasilkan nomor item berdasar umur
dalam bulan.
Jadi,
lookup_value => umur dalam bulan, yaitu dengan
DATEDIF($B5,$D$1,"m") lookup_vector (area lookup) =>
daftar batas bawah umur setiap grup, yaitu dengan array berbunyi
{1,7,13,25,72,156,181}
result_vector (daftar nilai yang diambil) => daftar nomor
item grup umur,
yaitu :
grup 1 (batas mulai umur 1 bulan sampai 6
bulan)
grup 2 (batas mulai umur 7 bulan sampai 1
tahun alias 12 bulan)
grup 3 (batas mulai umur lebih dari 1 tahun
alias 13 bulan sampai 2 tahun alias 24 bulan)
dst sampai ada 7 grup (mulai lebih dari 15
tahun alias 15 tahun lebih 1 bulan sampai tak hingga tahun dan bulan)
yang ditulis dalam bentuk array berbunyi
{1,2,3,4,5,6,7}
Jadi, jika batas bawah umur setiap grup diletakkan disuatu area range sebaris
(1 baris x 7 kolom), yang jika dimulai dari D3 maka akan sampai ke J3, maka D3
akan berisi nilai 1, E3 berisi 7, F3 berisi 13, dst.
Dengan demikian, bagian {1,7,13,25,72,156,181} bisa diganti menjadi $d$3:$j$3
Sampai disini, formula bagian lookup akan menjadi :
=REPT("x",LOOKUP(DATEDIF($B5,$D$1,"m"), $d$3:$j$3
,{1,2,3,4,5,6,7})=COLUMNS($D$4:D$4))
Bagian yang biru adalah kode grup, yaitu dari grup 1 sampai sekian. Biru dalam
lookup adalah daftar seluruh kode grup umur, sedangkan biru yang berupa fungsi
Columns adalah kode grup umur dilokasi cell yang sedang aktif. Kalau cell aktif
di D5, berarti grup 1.
Bagian Biru dalam lookup bisa diganti menjadi Column( $d$3:$j$3 ) - 3 karena
Column(d3) akan menghasilkan nilai 4 padahal pada saat cell aktif di kolom D,
kode grup harus bernilai 1, yang artinya nilai Column( d3 ) harus dikurangi 3
agar menghasilkan 1 (nilai kode grup yang benar untuk kolom D).
Maka formula akan berubah menjadi :
=REPT("x",LOOKUP(DATEDIF($B5,$D$1,"m"), $d$3:$j$3 ,Column( $d$3:$j$3 )
- 3 )=COLUMNS($D$4:D$4))
Kalau sudah paham sampai bagin ini, maka dilanjutkan lagi...
Kalau kode grup (yang 1,2,3,dst) bisa diwakili oleh batas bawah setiap grup
(1,7,13,dst) yang sudah ditulis di D3:J3, berarti perbandingan bagian :
LookUp() yang menghasilkamn kode grup = Columns() yang
menghasilkan kode grup
bisa diganti dengan perbandingan :
LookUp() menghasilkan nilai batas bawah grup = cell berisi batas
bawah grup untuk cell aktif
Bagian LookUp() menghasilkan nilai batas bawah grup bisa didapatkan dengan
menghilangkan bagian result_vector, artinya bagian lookup akan berubah menjadi :
LOOKUP(DATEDIF($B5,$D$1,"m"), $d$3:$j$3 ,)
Bagian cell berisi batas bawah grup untuk cell aktif bisa didapatkan dengan
merujuk selalu ke baris 3 di kolom aktif. Karena cell aktif (cell yang
dipasangi formula) adalah cell D5, maka bagian ini adalah merujuk ke baris 3
kolom D alias cell D$3 (selalu alias absolute). Artinya, bagian Columns( blabla
) berubah menjadi D$3
Secara lengkap, formula akan berubah menjadi :
=REPT("x",LOOKUP(DATEDIF($B5,$D$1,"m"), $d$3:$j$3 )=D$3 )
dan formula akan terikat pada cells bantu di range $d$3:$j$3 yang berupa
nilai-nilai batas bawah grup umur.
Sampai disini, maka dapat diketahui bahwa wajarnya, suatu data kelompok
(grouping) ditentukan dengan pengkodean dan nilai batas-batasnya (batas bawah
dan atas). Bunyi teks yang harus ditampakkan ke pemirsa data sekalian adalah
tergantung nilai batas-batas tersebut.
Akan lebih bagus lagi jika D1:J1 berisi kode, D2:J2 berisi batas atas setiap
grup, D3:J3 berisi batas bawah setiap grup, D4:J4 berisi teks nama grup yang
disajikan ke mata pemirsa data.
2. bagaimana jika kriteria umur (D4:K4) diubah atau diganti dengan
kriteria yang lain, dan apa yang harus dilakukan pada formula.
>> Jika sudah disusun seperti nomor 1 bagian akhir, mestinya sudah tahu apa
>> yang harus dilakukan.
Wassalam,
Kid.
2014-09-10 18:25 GMT+10:00 leo pcs leopc...@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com>:
>
>Terima Kasih Mr. Kid atas pencerahannya. formula yang disajikan sangat menarik
>"WOW"....
>
>namun saya masih bingung mengartikan atau melogikakan formula tersebut.
>mohon kiranya Mr. Kid dapat :
>1. memberikan sedikit penjelasan mengenai formula
>=REPT("x",LOOKUP(DATEDIF($B5,$D$1,"m"),{1,7,13,25,72,156,181},{1,2,3,4,5,6,7})=COLUMNS($D$4:D$4))
>terutama pada lookup_vector, [result_vector], serta menghadirkan
>=COLUMNS($D$4:D$4)
>2. bagaimana jika kriteria umur (D4:K4) diubah atau diganti dengan kriteria
>yang lain, dan apa yang harus dilakukan pada formula.
>
>
>salam
>Leo
>
>
>
>
>
>
>
>
>On Wednesday, September 10, 2014 1:42 PM, "'Mr. Kid' mr.nm...@gmail.com
>[belajar-excel]" <belajar-excel@yahoogroups.com> wrote:
>
>
>
>
>Hai Leo,
>
>File terlampir menggunakan formula :
>=REPT("x",LOOKUP(DATEDIF($B5,$D$1,"m"),{1,7,13,25,72,156,181},{1,2,3,4,5,6,7})=COLUMNS($D$4:D$4))
>
>Wassalam
>Kid.
>
>
>
>
>
>
>2014-09-10 15:29 GMT+10:00 leo pcs leopc...@yahoo.com [belajar-excel]
><belajar-excel@yahoogroups.com>:
>
>
>>
>>hai all
>>mohon pencerahan BeExceler semua untuk kasus pengelompokan umur yang telah
saya coba dan masih belum sesuai dengan yang diharapkan.
>>
>>
>>
>>Salam BeExcelLeo
>>
>>
>
>
>
