Re: ]] XL-mania [[ Sorting Data Text dengan formula

 

alternatif lain,
(CSE Formula)

ascending:
=INDEX(B8:B17, MATCH(SMALL( COUNTIF(B8: B17,"<"&B8:B17),ROW( 1:10)),COUNTIF( B8:B17,"<"&B8:B17),0))

descending:
=INDEX(B8:B17, MATCH(SMALL( COUNTIF(B8: B17,">"&B8:B17),ROW( 1:10)),COUNTIF( B8:B17,">"&B8:B17),0))


>semoga bermanfaat

---

From: siti Vi <setiyowati.devi@gmail.com>
To: XL-mania@yahoogroups.com
Sent: Mon, February 1, 2010 7:20:05 AM
Subject: ]] XL-mania [[ Sorting Data Text dengan formula

 

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)

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

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: B$17,"<"&B8)	6
zulkeplek	=COUNTIF(B$8: B$17,"<"&B9)	9
ahmad dina	=COUNTIF(B$8: B$17,"<"&B10)	0
ponco karso	=COUNTIF(B$8: B$17,"<"&B11)	8
budiyanto	=COUNTIF(B$8: B$17,"<"&B12)	5
budiman	=COUNTIF(B$8: B$17,"<"&B13)	4
budi subudi	=COUNTIF(B$8: B$17,"<"&B14)	3
arya salaka	=COUNTIF(B$8: B$17,"<"&B15)	2
eko prasetyo	=COUNTIF(B$8: B$17,"<"&B16)	7
ahmad sutimbul	=COUNTIF(B$8: B$17,"<"&B17)	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.

---

__._,_.___

Reply to sender | Reply to group

Messages in this topic (2)

Recent Activity:

• New Members 112

Visit Your Group Start a New Topic

+-:: 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              |
+-------------------------------------------------------------------+

MARKETPLACE

Going Green: Your Yahoo! Groups resource for green living

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___