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 BeExcel
> Leo
>
>
>
>
>
>
