Dalam 1 bulan kemarin (jan 2010) siti menerima 'pertanyaan yg sama' dari 9 orang.
Yg agak aneh "pembukaannya": walaupun lain cara mengemukakan, tetapi intinya juga sama :
" = bahwa ybs sudah mencoba browsing /searching di milis XL-mania maupun milis sebelah,
tetapi ternyata belum mendapatkan yg dikehendaki"
Inti pertanyaannya :
Dapatkah kita (tanpa bantuan makro) mensort data text dengan formula ??
"Dengan Formula" artinya hasil "sorting"nya akan berada di kolom lain bukan di kolom /
range dimana data berada seperti hasil sortng dengan menu Data Sort.
Soalnya: sungguh sulit menulis rumus di cell berdata, tanpa menghapus data tsb!! (^_*)
| tabel referensi | CONTOH hasil yang diinginkan | |||
| data (input) | | hasil (output 1) | | hasil (output 2) |
| type data : String | | ascending | | descending |
| burisrowo | ahmad dina | zulkeplek | ||
| zulkeplek | ahmad sutimbul | ponco karso | ||
| ahmad dina | arya salaka | eko prasetyo | ||
| ponco karso | budi subudi | burisrowo | ||
| budiyanto | budiman | budiyanto | ||
| budiman | budiyanto | budiman | ||
| budi subudi | burisrowo | budi subudi | ||
| arya salaka | eko prasetyo | arya salaka | ||
| eko prasetyo | ponco karso | ahmad sutimbul | ||
| ahmad sutimbul | zulkeplek | ahmad dina | ||
Jika kita mencobanya dengan fungsi SMALL atau LARGE secara langsung, hasilnya
adalah #NUM!, karena argument pertama untuk kedua fungsi tsb harus berupa bilangan.
Jadi idea kita seketika adalah bagaimana mengkonversi tiap data text menjadi bilangan
yg mewakili tinggi/rendahnya nilai text tsb.
Nantinya, kalau 'dah ketemu, akan di SeMoll atau di Large, agar terurut,
dan tugas berikutnya: mengkonversi kembali sorted-bilangan tsb menjadi data text asalnya.
Hal spt itu "jaman dulu" pernah dicoba dgn mengambil nilai Code ASCII tiap karakter
dan digabungkan; misalnya ABC = dikonversi menjadi 656667
Cara ini sangat terbatas karena di cell excel, bilangan hanya presisi max 15 digit,
sedangkan text yg akan diolah bisa lebih dari 8 karakter (=minimal 16 angka code.)
"KALOK GITU", kita harus cari cara lain:
Setiap data (dlm sekelompok data) dapat dibandingkan apakah dia lebihkecil /lebihbesar
daripada data lain.
Dengan data berupa TEXT: yg dimaksud lebih "lebih kecil" adalah nilai /code ASCII nya.
Misal pembandingan
="ABA" < "ABF" menghasilkan TRUE,
="XTAL" < "ATAL" hasilnya FALSE
Excel itu pintar karena dia tahu : apakah sebuah text = lebih besar / sama / lebih kecil
nilainya dibanding text lain; tetapi agak "oon" karena jika ditanya Berapa Nilai text tsb
dia "ogah" menjawab! (mungkin cara kita bertanya yg kurang baik); dan tadi sudah
dibuktikan : di-SeMoll juga menghasilkan #NUM!
Ketika sebuah data dibandingkan dengan SEMUA data (satu per satu), maka data tsb
punya nilai : BERAPA KALI DATA LAIN: BERSTATUS LEBIH KECIL dari Dirinya ?
(dgn kata lain: Berapa kali dia lebih besar d/p Data Lainnya)
(dgn kata lain: Berapa kali dia lebih besar d/p Data Lainnya)
Cara bertanya seperti itu dapat kita implementasikan dengan fungsi COUNTIF.
Jadi dapat disimpulkan
(*) Data yg SELALU LebihBesar d/p data lain (termasuk dirinya sendiri) akan punya nilai
(*) Data yg SELALU LebihBesar d/p data lain (termasuk dirinya sendiri) akan punya nilai
terbesar yaitu (N-1) kali, dia adalah data yg nilai ascii nya paling tinggi
(*) Data yg TIDAK PERNAH LebihBesar dari data lain, akan punya nilai 0 )terkecil);
dan dia ini adalah data yg nilai ascii nya paling rendah
(*) Di antara kedua nilai Extreem tsb tentunya data lain mempunyai nilai ranking masing-masing.
| date text | formula biasa | temp-hasil |
| burisrowo | =COUNTIF(B$8: | 6 |
| zulkeplek | =COUNTIF(B$8: | 9 |
| ahmad dina | =COUNTIF(B$8: | 0 |
| ponco karso | =COUNTIF(B$8: | 8 |
| budiyanto | =COUNTIF(B$8: | 5 |
| budiman | =COUNTIF(B$8: | 4 |
| budi subudi | =COUNTIF(B$8: | 3 |
| arya salaka | =COUNTIF(B$8: | 2 |
| eko prasetyo | =COUNTIF(B$8: | 7 |
| ahmad sutimbul | =COUNTIF(B$8: | 1 |
Nah.. (harusnya: "Lha !") kalau urutan nilai sudah kita ketahui.....
maka "URUSAN" mengurutkan bukan hal yg rumit lagi bukan ??
Kira-kira prosesnya spt ini
* Data Bilangan (kolom hasil sementara) diurutkan dengan fungsi SeMoll
* Tetapi sebelumnya; diTAMBAH dulu dengan 1/100 * (nilai urutannya)
ini menghasilkan bilangan pecahan misal 6 menjadi 6.01 (data 6 ex urutan ke 1)
3 menjadi 3.07 (data 3 ex urutan ke 7)
* Tambahan nilai (1/100 * AngkaUrutan) itu tidak akan mempengaruhi hasil sorting
karena nilai yg ditambahkan sudah di bawah ordo nilai yg disort.
* Hasil SMALL dihilangkan bagian bilangan UTUHnya, menyisakan bagian PECAHANNYA
BagianPecahan ini dikalikan seratus* untuk menjadi Nilai UTUH (integer)
( seratus* itu tepatnya = 10 pangkat MAX digit N data )
Anda tentu masih ingat bahwa Bagian Pecahan yg kita uber-uber ini adalah tak lain dari
nomor record data !!
| data text | peroses setèp bai setèp | hasil akhir | ||||
| 1 | burisrowo | 6 | 6.01 | 0.03 | 0.03 | 3 |
| 2 | zulkeplek | 9 | 9.02 | 1.10 | 0.10 | 10 |
| 3 | ahmad dina | 0 | 0.03 | 2.08 | 0.08 | 8 |
| 4 | ponco karso | 8 | 8.04 | 3.07 | 0.07 | 7 |
| 5 | budiyanto | 5 | 5.05 | 4.06 | 0.06 | 6 |
| 6 | budiman | 4 | 4.06 | 5.05 | 0.05 | 5 |
| 7 | budi subudi | 3 | 3.07 | 6.01 | 0.01 | 1 |
| 8 | arya salaka | 2 | 2.08 | 7.09 | 0.09 | 9 |
| 9 | eko prasetyo | 7 | 7.09 | 8.04 | 0.04 | 4 |
| 10 | ahmad sutimbul | 1 | 1.10 | 9.02 | 0.02 | 2 |
Setelah NomorRecord data suah ditangan ...!!
Well, mau apa lagi kita sekarang? kalau tidak segera mengINDEX data text tsb dengan
angka yg terakhir kita dapat tsb =INDEX(DataText, HasilAkhir)
mudah mudahan XL-tainment(©) ini ada manfaatnya....
desa bluewek / 1 feb 2010
siti
*Formula dengan kolom-bantu maupun formula langsung
untuk pengurutan ascending maupun descending : ada di lampiran...
"XL-tainment" adalah sebuah kata baru yg telah ada hak patentnya yg dipegang oleh Om PRIEL.
__._,_.___
+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Member ke 13,013 siapa ya? Sini mau dikirimin coklat :D |
| Member ke 31,031 dapet iPod dehh.... :D :D :D |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/ |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html |
+-------------------------------------------------------------------+
| Message lama ada di : |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com |
+-------------------------------------------------------------------+
| Member ke 13,013 siapa ya? Sini mau dikirimin coklat :D |
| Member ke 31,031 dapet iPod dehh.... :D :D :D |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/ |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html |
+-------------------------------------------------------------------+
| Message lama ada di : |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com |
+-------------------------------------------------------------------+
.
__,_._,___
Nenhum comentário:
Postar um comentário