MENGURUTKAN/MERANKING STRING/TEXT DENGAN FUNGSI “LARGE” DAN “SMALL” – Belajar Microsoft Excel : Tips, Tricks & Tutorial

MENGURUTKAN/MERANKING STRING/TEXT DENGAN FUNGSI “LARGE” DAN “SMALL”

artikel ini adalah artikel pendahuluan dari satu artikel yang akan saya publish belakangan 🙂 … ada baEknya jugaK kalO sOdara membaca beberapa tulisan dengan tag RANK

BiYasanya yang dirangking adalah ANGKA yang bisa diukur nilainya … namun bisakah STRING/TEKS yang berisi karakter bukan angka diranking untuk menghasilkan urutan berdasarkan alpabet ?

untuk melakukanya kita bisa menkombinasikan beberapa fungsi Excel yang akan membentuk formulanya … beberapa fungsi tersebut adalah : IF, ISBLANK, TEXT, CODE, MID, UPPER, REPT, COLUMN, VALUE, ROW, INDEX, MATCH, SMALL, LARGE, RANK … 😯 buanyak bangedh … tak bosan2nya saya menganjurkan untuk mempelajari fungsi2 tersebut pada MS Excel Help sOdara … jangan di blog ini 🙂

beberapa fungsi diatas telah saya bahas sebelumnya silahkan diubek2 arsip saya … beberapa belOm pernah saya senggol dalam artikel2 di blog ini… seperti

fungsi ISBLANK

fungsi ini akan memeriksa apakah suatu cell kosong mlompong atO ada isinya … kalo kosong hasElnya TRUE kalO isi hasElnya FALSE .. ndak perlu contoh saya yakin langsung Paham

fungsi CODE

fungsi ini akan menghasilkan kode angka dari huruP awal sebuah kata/kumpulan karakter …  contoh

  • =CODE(“T”) hasElnya 84
  • =CODE(“t”) hasElnya 116
  • =CODE(“teh”) hasElnya 116

fungsi COLUMN

menghasilkan kolom keberapa dari suatu cell atau range … contoh

  • cell A1 diisi formula =COLUMN() hasElnya 1
  • cell G26 diisi formula =COLUMN() hasElnya 7
  • cell A1 diisi formula =COLUMN(B3) hasElnya 2
  • cell A1 diisi formula =COLUMN(G5:H7) hasEnya 7

fungsi ROW

mirip fungsi COLUMN tApi yang ini untuk baris

  • cell A1 diisi formula =ROW() hasElnya 1
  • cell G26 diisi formula =ROW() hasElnya 26
  • cell A1 diisi formula =ROW(B3) hasElnya 3
  • cell A1 diisi formula =ROW(G5:H7) hasEnya 5

fungsi SMALL

menghasilkan data terkecil ke-# dari suatu daftar … contoh

  • cell A1=45
  • cell A2=25
  • cell A3=33
  • cell A4=18
  • cell A5=23

formula =SMALL($A$1:$A$5;2) akan menghasilkan 23

formula =SMALL($A$1:$A$5;3) akan menghasilkan 25

fungsi LARGE

menghasilkan data terkecil ke-# dari suatu daftar … contoh tabel seperti pada fungsi SMALL

formula =LARGE($A$1:$A$5;2) akan menghasilkan 33

formula =LARGE($A$1:$A$5;3) akan menghasilkan 25

baEklah sOdara liYat tabel superhero dari contoh file MengurutkanTeks.xls yang bisa di donloth

Untuk meranking teks kita harus merubah dulu agar teks tersebut mempunyai NILAI … jika urutan yang kita inginkan sesuai dengan urutan alphabet kita dapat memanfaatkan fungsi CODE … fungsi ini akan menghasilkan kode angka dari huruP awal sebuah kata/kumpulan karakter … karena yang di proses hanya huruP awalnya saja maka kata tersebut harus dipisahkan masing2 huruPnya dengan fungsi MID… untuk menentukan hurup urutan yang keberapa kita pakE fungsi COLUMN … pada Cell D3 kita kasi formula :

=MID($B3;COLUMN(A3);1)

hasElnya S … hasElnya mangsih case sensitive dimana hurup B akan ditempatkan lebih awal dari huruP a … untuk mengatasinya harus kita samakan mau pakE hurup besar atau kecil semua … bisa pilih fungsi UPPER atau LOWER … hingga formulanya berubah

=MID(UPPER($B3);COLUMN(A3);1)

sekarang kita cari nilai dari karakter ini dengan fungsi CODE

=CODE(MID(UPPER($B3);COLUMN(A3);1))

karakter yang akan kita pakai sebagai dasar pengurutan adalah 4 karakter awal …. semakin banyak semakin baEk sih 🙂 … sehingga untuk nama superhero yang kurang dari 4 karakter akan terjadi error 🙁 jadi formulanya berubah lagi kita tambahin spasi 4 kali di belakang nama superhero untuk jaga2 dengan fungsi REPT

=CODE(MID(UPPER($B3)&REPT(” “;4);COLUMN(A3);1))

hasElnya 83 … hasEl tertinggi untuk fungsi CODE adalah 255 (3 digit) … jadi untuk yang dibawah 3 digit harus ditambahi angka 0 didepannya sampai 3 digit dengan fungsi TEXT

=TEXT(CODE(MID(UPPER($B3)&REPT(” “;4);COLUMN(A3);1));”000”)

hasElnya 083 … sekarang kita perlu mengurusi data2 nama superhero yang kosong … data yang kosong akan diurutkan terakhir … karena hasil tertinggi fungsi CODE adalah 255 maka kita pakE nilai sesudahnya yaitu 256 … sedangkan untuk mengecek suatu Cell kosong atO isi bisa pakE fungsi ISBLANK … ditambah fungsi IF didepan

=IF(ISBLANK($B3);256;TEXT(CODE(MID(UPPER($B3)&REPT(” “;4);COLUMN(A3);1));”000”))

kopikan ke range D3:G22

sekarang kita urusi kolom H …. di kolom ini kita gabung hasil dari formula pada kolom D,E,F,G … hasilnya barisan angka tapi dalam format teks … untuk merubahnya kita pake fungsi VALUE aja … Cell H3 kita kasi formula

=VALUE(D3&E3&F3&G3)

hasil dari formula tersebut masih mungkin terjadi nilai yang kembar … agar hasElnya unik angka tersebut kita tambahi nomor urut sesuE yang tercantum dalam tabel … untuk mengetahui nomor urut kita pake fungsi ROW aja … hasEl fungsi ROW sebelOm digabung dengan hasEl formula penggabungan kolom D,E,F,G harus diubah formatnya menjadi teks 2 digit (atau sebanyak jumlah digit banyaknya data) dengan fungsi TEXT

=VALUE(D3&E3&F3&G3&TEXT(ROW(H1);”00”))

hasElnya 8308007306801 … kopikan ke baris2 dibawahnya … sampe disini kita punya satu kolom yang berisi data2 yang bisa dirangking … tinggal sekarang mau pakE ranking Ascending atau Descending

kalO Ascending kita pakE fungsi SMALL … seperti pada contoh kolom J

pada cell J3 pertama kita cari nilai terkecil ke-1 dari daftar dalam kolom H dengan formula

=SMALL($H$3:$H$22;ROW(J1))

hasElnya 6508308408214 … setelah itu kita cari angka tersebut terletak pada baris keberapa pada daftar kolom H dengan formula MATCH

=MATCH(SMALL($H$3:$H$22;ROW(J1));$H$3:$H$22;0)

hasElnya 14 … sekarang kita cari baris ke-14 ini pada kolom B (nama superhero) dengan fungsi INDEX

=INDEX($B$3:$B$22;MATCH(SMALL($H$3:$H$22;ROW(J1));$H$3:$H$22;0);1)

hasElnYa Astro Boy … kita juga bisa menambahkan nomor urut dalam daftar (14) dengan formula

=INDEX($B$3:$B$22;MATCH(SMALL($H$3:$H$22;ROW(J1));$H$3:$H$22;0);1)&” – “&MATCH(SMALL($H$3:$H$22;ROW(J1));$H$3:$H$22;0)

hasElnya Astro Boy – 14 … OK deh tinggal kopikan ke bawah yah

kalO Descending kita pakE fungsi LARGE … seperti pada contoh kolom K … karena caranya sama kita tinggal kopi J3 ke K3 …ganti SMALL dengan LARGE … sehingga formulanya akan seperti berikOt

=INDEX($B$3:$B$22;MATCH(LARGE($H$3:$H$22;ROW(K1));$H$3:$H$22;0);1)&” – “&MATCH(LARGE($H$3:$H$22;ROW(K1));$H$3:$H$22;0)

perhatikan yang warna merah adalah formula yang berubah

sekiyan dan …. MDLMDL ….

bingung cara downloadnya ??

Baca aja halaman download dengan klik icon di samping

feed , email and my social media

51 Comments

Jika sOdara menemukan sesuatu yang bermanpaat di marih .... silahkan kasih komennya