2 New Messages
Digest #1800
Messages
Sat Sep 22, 2012 8:31 am (PDT) . Posted by:
"Kid Mr." nmkid.family@ymail.com
Cara 0 dan cara 1 ada di kolom G dan J, yang dimaksudkan untuk mengajak
memperhatikan karakteristik sort dalam Excel.
Cara 2 sampai 5 adalah contoh untuk memahami konsep penggunaan sebuah
composite key dalam pengurutan data.
Cara 6 adalah contoh penggunaan tabel referensi prioritas pengurutan yang
spesifik sesuai kehendak user. Dalam hal ini, prioritas pengurutan disusun
user melalui sebuah array formula, padahal sebenarnya bisa disusun dengan
berbagai cara termasuk penggunaan pivot table
Cara 7 adalah contoh penyusunan raw data yang dilengkapi dengan composite
key yang menjadi pengurut secara langsung. Bedanya, pada cara 4 sampai 6,
proses penyusunan composite key beserta proses pengurutan dilakukan pada
sisi hasil. Sedangkan cara 7 lebih mengutamakan pengolahan raw data yang
telah menyiapkan composite key pengurutan.
Untuk ungkapan "sepertinya problem aktual saya harus dgn makro", sepertinya
lebih sesuai jika "diinginkan dengan memanfaatkan VBA".
Ketika memanfaatkan VBA, konsep composite key bisa disederhanakan, sehingga
penyusun composite key cukup berupa
'Posisi Nama pertama yang sama yang telah tersusun descending nominalnya'
Hal ini bisa diperoleh dengan formula Match terhadap data nama.
Sebaiknya pada langkah-langkah penyusunan VBA nanti, ketika sampai pada
tahap untuk menentukan Posisi Nama pertama yang terurut, lakukanlah uji
coba secara langsung di worksheet untuk menemukan formulasinya. Setelah
didapatkan formulasinya, baru dipasang menggunakan VBA.
Berikut langkahnya :
1. Copy data (header 1 baris) ke sheet hasil. Untuk memudahkan proses,
paste ke baris 1 kolom A (di A1) lebih dulu.
2. Lakukan sort dengan 2 kolom kunci pengurut, yaitu kolom Nominal
descending (key1) dan kolom Nama Ascending (key2)
3. berhenti dan coba susun formula yang bisa mendapatkan bahwa semua nama
'tertentu' selalu memiliki id yang sama dengan yang pertama kali ditemukan
di tabel yang telah diurutkan oleh proses 2
Contoh : jika ada 5 Nama bernilai 'Kid', dan nama 'Kid' pertama ditemukan
pada baris ke 7, maka semua nama 'Kid' harus punya id bernilai 7.
Letakkan formula pada kolom kosong pertama dikanan tabel (rapat dengan
tabel yang diolah)
4. jika sudah menemukannya, pasang formula dengan VBA untuk semua baris
data di kolom kosong pertama di kanan tabel
5. lakukan kalkulasi sheet dan copy paste values seluruh tabel hasil
(termasuk yang berformula tadi)
6. lakukan sort berdasar 1 kolom key, yaitu kolom yang berisi formula tadi
(yang kini sudah tinggal valuesnya saja)
7. hapus data di kolom berformula tadi.
Wassalam,
Kid.
2012/9/22 <sin.ur4t@yahoo.com >
> **
>
>
> Makasih pak mrkid atensinya, saya akan coba pelajari pelan2. linknya jg
> nanti akan dibaca2 sambil nunggu barca vs granada :)..
> setelah menebak "alur fikir" dlm menyusun formula pd cara 2 sampai 5 (cara
> 1 nya koq gak keliatan ya hehe) - saya simpulkan bhw "prinsip dasar" dlm
> mengelola database ialah kita hrs berupaya agar setiap record dpt memiliki
> nilai unik (identitas) sbg penciri yg khas, yg nantinya rumus2 dibangun
> menggunakan identitas itu.
> Jika record tak memiliki identitas, maka membangun rumus2 akan sangat
> sulit.
> Benarkah kesimpulan ini pak??
>
> Btw, sepertinya problem aktual saya harus dgn makro, krn Tabel 1 (data)
> dlm lampiran kasus ini pun sbnrnya sdh merupakan hasil advanced filter dan
> sorting (data pd bbrp kolom digabung mjd satu kolom di sheet hasil pake
> makro) dari tabel mentahnya.
>
> Dari semua cara yg pak mrkid uraikan tadi, cara yg mana kira2 yg paling
> mudah disusun mjd bahasa makro? Dan jika berkenan, mohon diuraikan
> step-step makro (istilah kerennya algoritma manual bukan??)-nya. nanti saya
> coba menuliskan makronya berdasarkan step-step itu, kemudian hasilnya saya
> konsultasikan kembali ke bpk.
>
> Salam hormat,
> Sinurat
> Powered by Telkomsel BlackBerry®
> ------------------------------
> *From: * "Kid Mr." <mr.nmkid@gmail.com >
> *Sender: * belajar-excel@yahoogroups.com
> *Date: *Sat, 22 Sep 2012 15:45:01 +0700
> *To: *<belajar-excel@yahoogroups.com >
> *ReplyTo: * belajar-excel@yahoogroups.com
> *Subject: *Re: [belajar-excel] Menampilkan data secara urut dan
> berkelompok
>
> Supaya lengkap dan ndak bingung cara pakai tabel referensi prioritasnya.
> (Cara 6)
> Sekalian cara menyusun composite key. (Cara 7)
>
> Wassalam,
> Kid.
>
> 2012/9/22 Kid Mr. <mr.nmkid@gmail.com >
>
>> Assalamualaikum Wr. Wb.
>>
>> Proses mengurutkan data dengan multi kriteria menggunakan formula, pada
>> umumnya tergantung pada susunan composite key kriteria pengurutannya.
>> Sepertinya ruwet kalau dijelaskan dengan kata-kata ya...
>> Lihat file terlampir saja ya...
>> Konsep tentang Composite Key dan cara pengurutan data, bisa dilihat
>> disini<http://excel-mr-kid.blogspot.com/2012/05/formula-peringkat-data-teks.html >
>> .
>>
>> Wassalamualaikum Wr. Wb.
>> Kid.
>>
>>
>>
>> 2012/9/21 Imam Sinurat <sin.ur4t@yahoo.com >
>>
>>> **
>>>
>>>
>>> Salam be-excel..
>>>
>>> Yth. para pakar dan rekan2 excellers
>>>
>>> Bagaimana cara sorting dan mengelompokkan data di Tabel 1. agar tampil
>>> terurut (Tabel 2.) dengan Makro ??
>>>
>>> Penjelasan urutan dan pengelompokan data pada Tabel 2. :
>>> 1. Semua record "yang unik" disort secara descending
>>> 2. Berdasarkan urutan itu, lalu semua record yg "namanya sama"
>>> dikumpulkan dan diurutkan lagi secara descending
>>> 3. Hasilnya ditampilkan pada sheet 'hasil'
>>>
>>> Mohon dibantu, trima kasih sebelumnya
>>> wassalam
>>> Imam F. Sinurat
>>>
>>>
>>
>
>
memperhatikan karakteristik sort dalam Excel.
Cara 2 sampai 5 adalah contoh untuk memahami konsep penggunaan sebuah
composite key dalam pengurutan data.
Cara 6 adalah contoh penggunaan tabel referensi prioritas pengurutan yang
spesifik sesuai kehendak user. Dalam hal ini, prioritas pengurutan disusun
user melalui sebuah array formula, padahal sebenarnya bisa disusun dengan
berbagai cara termasuk penggunaan pivot table
Cara 7 adalah contoh penyusunan raw data yang dilengkapi dengan composite
key yang menjadi pengurut secara langsung. Bedanya, pada cara 4 sampai 6,
proses penyusunan composite key beserta proses pengurutan dilakukan pada
sisi hasil. Sedangkan cara 7 lebih mengutamakan pengolahan raw data yang
telah menyiapkan composite key pengurutan.
Untuk ungkapan "sepertinya problem aktual saya harus dgn makro", sepertinya
lebih sesuai jika "diinginkan dengan memanfaatkan VBA".
Ketika memanfaatkan VBA, konsep composite key bisa disederhanakan, sehingga
penyusun composite key cukup berupa
'Posisi Nama pertama yang sama yang telah tersusun descending nominalnya'
Hal ini bisa diperoleh dengan formula Match terhadap data nama.
Sebaiknya pada langkah-langkah penyusunan VBA nanti, ketika sampai pada
tahap untuk menentukan Posisi Nama pertama yang terurut, lakukanlah uji
coba secara langsung di worksheet untuk menemukan formulasinya. Setelah
didapatkan formulasinya, baru dipasang menggunakan VBA.
Berikut langkahnya :
1. Copy data (header 1 baris) ke sheet hasil. Untuk memudahkan proses,
paste ke baris 1 kolom A (di A1) lebih dulu.
2. Lakukan sort dengan 2 kolom kunci pengurut, yaitu kolom Nominal
descending (key1) dan kolom Nama Ascending (key2)
3. berhenti dan coba susun formula yang bisa mendapatkan bahwa semua nama
'tertentu' selalu memiliki id yang sama dengan yang pertama kali ditemukan
di tabel yang telah diurutkan oleh proses 2
Contoh : jika ada 5 Nama bernilai 'Kid', dan nama 'Kid' pertama ditemukan
pada baris ke 7, maka semua nama 'Kid' harus punya id bernilai 7.
Letakkan formula pada kolom kosong pertama dikanan tabel (rapat dengan
tabel yang diolah)
4. jika sudah menemukannya, pasang formula dengan VBA untuk semua baris
data di kolom kosong pertama di kanan tabel
5. lakukan kalkulasi sheet dan copy paste values seluruh tabel hasil
(termasuk yang berformula tadi)
6. lakukan sort berdasar 1 kolom key, yaitu kolom yang berisi formula tadi
(yang kini sudah tinggal valuesnya saja)
7. hapus data di kolom berformula tadi.
Wassalam,
Kid.
2012/9/22 <sin.ur4t@yahoo.
> **
>
>
> Makasih pak mrkid atensinya, saya akan coba pelajari pelan2. linknya jg
> nanti akan dibaca2 sambil nunggu barca vs granada :)..
> setelah menebak "alur fikir" dlm menyusun formula pd cara 2 sampai 5 (cara
> 1 nya koq gak keliatan ya hehe) - saya simpulkan bhw "prinsip dasar" dlm
> mengelola database ialah kita hrs berupaya agar setiap record dpt memiliki
> nilai unik (identitas) sbg penciri yg khas, yg nantinya rumus2 dibangun
> menggunakan identitas itu.
> Jika record tak memiliki identitas, maka membangun rumus2 akan sangat
> sulit.
> Benarkah kesimpulan ini pak??
>
> Btw, sepertinya problem aktual saya harus dgn makro, krn Tabel 1 (data)
> dlm lampiran kasus ini pun sbnrnya sdh merupakan hasil advanced filter dan
> sorting (data pd bbrp kolom digabung mjd satu kolom di sheet hasil pake
> makro) dari tabel mentahnya.
>
> Dari semua cara yg pak mrkid uraikan tadi, cara yg mana kira2 yg paling
> mudah disusun mjd bahasa makro? Dan jika berkenan, mohon diuraikan
> step-step makro (istilah kerennya algoritma manual bukan??)-nya. nanti saya
> coba menuliskan makronya berdasarkan step-step itu, kemudian hasilnya saya
> konsultasikan kembali ke bpk.
>
> Salam hormat,
> Sinurat
> Powered by Telkomsel BlackBerry®
> ------------
> *From: * "Kid Mr." <mr.nmkid@gmail.
> *Sender: * belajar-excel@
> *Date: *Sat, 22 Sep 2012 15:45:01 +0700
> *To: *<belajar-excel@
> *ReplyTo: * belajar-excel@
> *Subject: *Re: [belajar-excel] Menampilkan data secara urut dan
> berkelompok
>
> Supaya lengkap dan ndak bingung cara pakai tabel referensi prioritasnya.
> (Cara 6)
> Sekalian cara menyusun composite key. (Cara 7)
>
> Wassalam,
> Kid.
>
> 2012/9/22 Kid Mr. <mr.nmkid@gmail.
>
>> Assalamualaikum Wr. Wb.
>>
>> Proses mengurutkan data dengan multi kriteria menggunakan formula, pada
>> umumnya tergantung pada susunan composite key kriteria pengurutannya.
>> Sepertinya ruwet kalau dijelaskan dengan kata-kata ya...
>> Lihat file terlampir saja ya...
>> Konsep tentang Composite Key dan cara pengurutan data, bisa dilihat
>> disini<http://excel-
>> .
>>
>> Wassalamualaikum Wr. Wb.
>> Kid.
>>
>>
>>
>> 2012/9/21 Imam Sinurat <sin.ur4t@yahoo.
>>
>>> **
>>>
>>>
>>> Salam be-excel..
>>>
>>> Yth. para pakar dan rekan2 excellers
>>>
>>> Bagaimana cara sorting dan mengelompokkan data di Tabel 1. agar tampil
>>> terurut (Tabel 2.) dengan Makro ??
>>>
>>> Penjelasan urutan dan pengelompokan data pada Tabel 2. :
>>> 1. Semua record "yang unik" disort secara descending
>>> 2. Berdasarkan urutan itu, lalu semua record yg "namanya sama"
>>> dikumpulkan dan diurutkan lagi secara descending
>>> 3. Hasilnya ditampilkan pada sheet 'hasil'
>>>
>>> Mohon dibantu, trima kasih sebelumnya
>>> wassalam
>>> Imam F. Sinurat
>>>
>>>
>>
>
>
Sat Sep 22, 2012 8:48 am (PDT) . Posted by:
"Kid Mr." nmkid.family@ymail.com
Pak Aden,
Coba formula :
=SUMPRODUCT( ($E$7:$EV$7="masuk") * (E8:EV8>0) )
bahasa manusianya :
"Jumlahkan seluruh hasil perkalian kriteria berikut :
1. nama headernya adalah teks berbunyi 'masuk' [harus sama persis], kalau
sesuai, beri nilai TRUE (setara 1). Kalau tidak sesuai diberi nilai FALSE
(setara 0)
2. nilai sebarisnya (baris 8 dari E sampai EV) adalah lebih dari 0 (alias
ada isinya angka). Jika sesuai diberi TRUE, jika tidak diberi FALSE"
Jadi, ketika setiap kriteria tadi dikalikan, akan ada hasilnya 0 atau 1
(ini adalah tahap Product).
Kemudian semua hasil itu dijumlahkan (tahap Sum)
Jadi fungsi SumProduct adalah memproses hasil perkalian setiap itemnya,
kemudian menjumlahkannya menjadi sebuah nilai total.
Jika fungsi SumProduct diganti dengan Sum, maka proses peng-enter-an
(setiap usai mengedit atau menulis formula) harus dengan menekan CTRL
SHIFT ENTER bersamaan dan tidak bisa hanya dengan menekan ENTER saja.
Dari hal di atas,
1. Proses kalkulasi dengan kriteria sebaiknya tidak dilakukan berdasar
kriteria warna. Tidak ada fungsi bawaan Excel yang bisa melakukan komputasi
berdasar warna (setidaknya sampai versi 2007 -> belum coba semua hal di
2010).
2. Proses kalkulasi dengan kriteria sebaiknya dilakukan secara langsung
dengan menerjemahkan kriteria menjadi satu bahasa formula.
3. Penyusunan Conditional Formatting dengan lebih dari 1 kriteria sebaiknya
dilakukan dengan menggunakan opsi Use Formula (xl2007 ke atas) atau Formula
Is (xl2003)
Wassalam,
Kid.
2012/9/22 Aden S <adendum_81@yahoo.com >
> **
>
>
> Terima kasih untuk respon cepat dan pencerahannya Mr. Kid,
>
> Ada banyak hal yg bisa saya ambil pelajaran, tapi ada satu hal lagi yang
> saya ingin tanyakan, bagaimana jika kita memberi warna manual, semisal kita
> masuk di tanggal 23 saya hanya tinggal mewarnai (disalah satu jam masuk
> atau pulang) sehingga itu terhitung sebagai 1, tetapi jika di tanggal
> berikutnya tidak hadir, maka tidak akan saya warnai sehingga terhitung
> sebagai 0
>
> sekali lagi terimakasih atas ilmu yg telah di berikan
>
> Salam
>
>
> Aden
>
> ------------------------------
> *From:* Kid Mr. <mr.nmkid@gmail.com >
> *To:* belajar-excel@yahoogroups.com
> *Sent:* Saturday, September 22, 2012 5:23 PM
> *Subject:* Re: [belajar-excel] hitung warna
>
>
> Hai Aden,
>
> 1. Pastikan Conditional formatting (CF) bisa telah sesuai dengan yang
> dibutuhkan.
> >> Pada file Anda, tidak ada data yang bisa memenuhi syarat dalam CF yang
> terpasang. Pastikan tipe data dari nilai yang ada di dataset dengan yang
> diset dalam CF adalah sama.
>
> 2. Pastikan file Excel memiliki VBA Module yang berisi prosedur fungsi
> bernama CountCFColor.
>
> 3. Save as file dengan ekstensi .xlsm,.xlsb, atau .xls
> >> Jika di-save dengan ekstensi .xlsx, pasti VBA Module yang berisi
> prosedur fungsi CountCFColor akan dihilangkan oleh Excel.
>
> Wassalam,
> Kid.
>
> 2012/9/22 Aden S <adendum_81@yahoo.com >
>
> **
>
> Dear All,
>
>
> Bagaimana rumus cara menghitung dengan kriteria warna (contoh terlampir),
> kemarin saya mencoba dengan dengan file yang ada di arsip tapi tidak
> berhasil, terima kasih untuk pencerahannya
>
>
> Salam
>
>
> Aden
>
>
>
>
>
>
>
Coba formula :
=SUMPRODUCT( ($E$7:$EV$7=
bahasa manusianya :
"Jumlahkan seluruh hasil perkalian kriteria berikut :
1. nama headernya adalah teks berbunyi 'masuk' [harus sama persis], kalau
sesuai, beri nilai TRUE (setara 1). Kalau tidak sesuai diberi nilai FALSE
(setara 0)
2. nilai sebarisnya (baris 8 dari E sampai EV) adalah lebih dari 0 (alias
ada isinya angka). Jika sesuai diberi TRUE, jika tidak diberi FALSE"
Jadi, ketika setiap kriteria tadi dikalikan, akan ada hasilnya 0 atau 1
(ini adalah tahap Product).
Kemudian semua hasil itu dijumlahkan (tahap Sum)
Jadi fungsi SumProduct adalah memproses hasil perkalian setiap itemnya,
kemudian menjumlahkannya menjadi sebuah nilai total.
Jika fungsi SumProduct diganti dengan Sum, maka proses peng-enter-an
(setiap usai mengedit atau menulis formula) harus dengan menekan CTRL
SHIFT ENTER bersamaan dan tidak bisa hanya dengan menekan ENTER saja.
Dari hal di atas,
1. Proses kalkulasi dengan kriteria sebaiknya tidak dilakukan berdasar
kriteria warna. Tidak ada fungsi bawaan Excel yang bisa melakukan komputasi
berdasar warna (setidaknya sampai versi 2007 -> belum coba semua hal di
2010).
2. Proses kalkulasi dengan kriteria sebaiknya dilakukan secara langsung
dengan menerjemahkan kriteria menjadi satu bahasa formula.
3. Penyusunan Conditional Formatting dengan lebih dari 1 kriteria sebaiknya
dilakukan dengan menggunakan opsi Use Formula (xl2007 ke atas) atau Formula
Is (xl2003)
Wassalam,
Kid.
2012/9/22 Aden S <adendum_81@yahoo.
> **
>
>
> Terima kasih untuk respon cepat dan pencerahannya Mr. Kid,
>
> Ada banyak hal yg bisa saya ambil pelajaran, tapi ada satu hal lagi yang
> saya ingin tanyakan, bagaimana jika kita memberi warna manual, semisal kita
> masuk di tanggal 23 saya hanya tinggal mewarnai (disalah satu jam masuk
> atau pulang) sehingga itu terhitung sebagai 1, tetapi jika di tanggal
> berikutnya tidak hadir, maka tidak akan saya warnai sehingga terhitung
> sebagai 0
>
> sekali lagi terimakasih atas ilmu yg telah di berikan
>
> Salam
>
>
> Aden
>
> ------------
> *From:* Kid Mr. <mr.nmkid@gmail.
> *To:* belajar-excel@
> *Sent:* Saturday, September 22, 2012 5:23 PM
> *Subject:* Re: [belajar-excel] hitung warna
>
>
> Hai Aden,
>
> 1. Pastikan Conditional formatting (CF) bisa telah sesuai dengan yang
> dibutuhkan.
> >> Pada file Anda, tidak ada data yang bisa memenuhi syarat dalam CF yang
> terpasang. Pastikan tipe data dari nilai yang ada di dataset dengan yang
> diset dalam CF adalah sama.
>
> 2. Pastikan file Excel memiliki VBA Module yang berisi prosedur fungsi
> bernama CountCFColor.
>
> 3. Save as file dengan ekstensi .xlsm,.xlsb, atau .xls
> >> Jika di-save dengan ekstensi .xlsx, pasti VBA Module yang berisi
> prosedur fungsi CountCFColor akan dihilangkan oleh Excel.
>
> Wassalam,
> Kid.
>
> 2012/9/22 Aden S <adendum_81@yahoo.
>
> **
>
> Dear All,
>
>
> Bagaimana rumus cara menghitung dengan kriteria warna (contoh terlampir),
> kemarin saya mencoba dengan dengan file yang ada di arsip tapi tidak
> berhasil, terima kasih untuk pencerahannya
>
>
> Salam
>
>
> Aden
>
>
>
>
>
>
>
GROUP FOOTER MESSAGE
---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke:
belajar-excel@yahoogroups.com
berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe):
kirim mail kosong ke belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke:
belajar-excel@yahoogroups.com
berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe):
kirim mail kosong ke belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
Tidak ada komentar:
Posting Komentar