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

Advertisements
51 Comments

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.