Panduan ini akan menunjukkan cara mengurutkan abjad di Excel menggunakan fitur Sort and Filter, dan rumus untuk mengatur dan memformat data secara berurutan.
Microsoft Excel adalah perangkat lunak yang efektif untuk mengatur data penting, seperti keuangan, data penjualan, informasi pelanggan, dll. Mengabjadkan data dalam lembar kerja adalah salah satu cara terbaik untuk mengatur, mengakses, dan mereferensikan data tersebut dengan cepat. Mengabjadkan rentang sel berarti mengurutkan rentang menurut abjad di excel. Data dapat diurutkan dengan dua cara, baik dalam urutan menaik atau menurun.
Misalnya, katakanlah Anda memiliki kumpulan data besar informasi pesanan pelanggan dan Anda ingin menemukan riwayat pesanan pelanggan tertentu. Menyisir seluruh daftar akan menjadi tugas yang cukup berat. Daripada membuang waktu untuk menggulir, Anda dapat menyusun kolom menurut abjad untuk menemukan data Anda dengan cepat.
Ada berbagai cara yang bisa Anda gunakan untuk mengurutkan data menurut abjad di Excel, bergantung pada tipe kumpulan data yang sedang Anda kerjakan. Excel memungkinkan Anda untuk mengurutkan satu kolom atau satu baris atau rentang yang dipilih atau seluruh lembar kerja, serta opsi untuk mengelompokkan beberapa kolom atau baris bersamaan saat Anda mengurutkannya. Mari pelajari cara menyusun data menurut abjad di lembar bentang Excel menurut baris atau kolom.
Mengapa Mengabjadkan data di Excel
Mengabjadkan data akan memiliki banyak keuntungan:
- Itu membuat data lebih masuk akal dan membuatnya lebih mudah dibaca.
- Ini memudahkan pengguna untuk mencari nilai atau nama pelanggan tertentu dalam lembar data Excel.
- Ini membantu pengguna untuk secara visual mengidentifikasi catatan duplikat sehingga kami dapat mencegah kesalahan entri data.
- Ini memungkinkan pengguna untuk dengan mudah mengelompokkan kolom atau daftar bersama sehingga pengguna dapat melihatnya secara berdampingan.
Abjad (mengurutkan berdasarkan abjad) adalah cara yang mudah dan umum untuk mengurutkan informasi Anda dengan cepat. Ini sangat berguna untuk kumpulan data yang sangat besar. Secara total, ada 4 cara untuk mengurutkan data di Excel: tombol A-Z atau Z-A, fitur Sortir, fungsi filter, dan rumus.
Mengabjadkan Kolom di Excel
Cara tercepat untuk mengurutkan berdasarkan abjad di Excel adalah dengan menggunakan fitur Sortir bawaan.
Pertama, buka spreadsheet yang berisi kumpulan data. Pilih sel atau kolom yang ingin Anda urutkan dan pastikan untuk tidak memilih sel kosong. Kemudian, buka tab 'Data' dan klik 'A-Z' untuk mengurutkan menaik atau 'Z-A' untuk mengurutkan menurun di grup Sortir dan Filter.
Jika ada sel kosong di antaranya, Excel mengasumsikan bahwa semua data telah diurutkan dan berhenti di atas sel hitam. Jika Anda ingin mengurutkan seluruh kolom cukup pilih sel mana saja di kolom dan gunakan opsi di atas.
Opsi yang sama juga dapat diakses di bawah alat 'Urutkan dan Filter' di bagian Pengeditan pada tab 'Beranda'.
Setelah Anda mengklik tombol, excel segera mengurutkan daftar Anda.
Cara Mengurutkan dan Menyimpan Baris Bersama di Excel
Anda dapat dengan mudah mengurutkan kolom jika Anda hanya memiliki satu kolom di lembar kerja Anda, tetapi akan menjadi sedikit rumit saat lembar kerja Anda berisi beberapa kolom di samping kolom yang ingin Anda urutkan. Juga, terkadang, kami mengerjakan spreadsheet di mana kami perlu menjaga agar baris yang sesuai tetap utuh setelah menyortir kolom.
Misalnya, jika kita memiliki spreadsheet daftar nilai siswa. Ketika kita mengurutkan kolom nama siswa dalam urutan abjad, kita juga ingin tanda yang dimasukkan pada baris di sebelahnya juga harus bergerak sesuai.
Dalam kasus seperti itu, Anda dapat menggunakan tombol 'A-Z' atau 'Z-A' untuk mengurutkan salah satu kolom dalam urutan abjad dan secara otomatis akan mengatur data di kolom lain, menjaga baris tetap bersama. Mari kita lihat cara kerjanya dengan contoh lain.
Pertama, pilih kolom pertama yang ingin Anda urutkan kolom lainnya. Kemudian pilih 'A-Z' atau 'Z-A' di bawah tab Data atau tab Beranda untuk mengurutkan data dalam satu kolom, dan Excel akan secara otomatis mengatur ulang kolom lain yang sesuai.
Setelah Anda mengklik tombol pengurutan, jendela dialog Sort Warning akan muncul. Pilih tombol radio untuk 'Perluas pilihan' dan klik tombol 'Urutkan'.
Ini akan menjaga baris tetap bersama saat menyortir kolom, tanpa ketidakcocokan data apa pun (seperti yang ditunjukkan di bawah).
Jika Anda memilih opsi 'Lanjutkan dengan pilihan saat ini', Excel hanya akan mengurutkan kolom yang dipilih.
Cara Mengurutkan Secara Abjad berdasarkan Beberapa Kolom
Jika Anda ingin mengurutkan kumpulan data menurut abjad lebih dari satu kolom, Anda dapat melakukannya dengan fitur Sortir Excel.
Sebagai contoh, kita ingin menyusun tabel berikut menurut abjad terlebih dahulu berdasarkan Negara, kemudian berdasarkan Nama Depan:
Pertama, pilih seluruh tabel yang ingin Anda urutkan berdasarkan abjad, lalu buka tab 'Data' dan di grup Sortir dan Filter, klik 'Urutkan'.
Atau klik ikon 'Urutkan & Filter' di bagian Pengeditan pada tab 'Beranda' dan pilih 'Urutkan Kustom' untuk mengakses fitur yang sama.
Kotak dialog Urutkan akan muncul dan menunjukkan kolom (parameter) pertama di mana tabel akan diurutkan. Di kotak tarik-turun 'Urutkan menurut', pilih kolom utama yang ingin Anda gunakan untuk mengurutkan data terlebih dahulu, 'Negara' dalam kasus kami, dan dalam tarik-turun 'Urutan' pilih 'A ke Z' atau 'Z ke A' .
Kemudian, klik tombol 'Tambah Level' untuk menambahkan level penyortiran kedua. Pilih kolom kedua (dalam kasus kami, Nama Depan) yang Anda inginkan untuk mengurutkan data menurut abjad di kotak 'Urutkan Menurut' dan pilih 'A ke Z' atau 'Z ke A'. Jika tabel Anda memiliki tajuk di bagian atas, centang kotak 'Data Saya Memiliki Tajuk' agar dapat melewati tajuk saat menyortirnya. Tambahkan lebih banyak tingkat pengurutan jika perlu, dan klik 'OK'.
Tabel disusun menurut abjad: pertama berdasarkan Negara, dan kemudian dengan Nama Depan seperti yang ditunjukkan di bawah ini.
Cara Mengurutkan Baris Secara Abjad di Excel
Terkadang Anda mungkin ingin mengurutkan baris di Excel, bukan kolom. Ini dapat dilakukan dengan menggunakan fitur Sortir Excel. Misalnya, katakanlah Anda memiliki spreadsheet di mana kolom baris pertama B hingga T berisi nama kota tempat jumlah toko yang tersedia di bawah berbagai jenis ritel dan Kolom A berisi jenis pengecer. Sel digunakan untuk melacak berapa banyak toko di setiap kota dalam kategori pengecer yang berbeda.
Pertama, pilih semua baris yang ingin Anda urutkan menurut abjad; Jika Anda tidak ingin memindahkan label baris, keluarkan dari pilihan Anda. Sekarang, navigasikan ke tab 'Data' dan klik 'Urutkan' di grup Sortir dan Filter.
Di kotak dialog Urutkan, klik tombol 'Opsi'.
Kotak dialog kecil lainnya akan muncul, memberi Anda opsi untuk memilih 'Urutkan dari kiri ke kanan' atau 'Urutkan dari atas ke bawah'. Pilih opsi 'Urutkan dari kiri ke kanan' untuk mengurutkan berdasarkan abjad dan klik 'OK' untuk kembali ke kotak dialog Urutkan.
Sekarang, pilih Baris yang ingin Anda urutkan berdasarkan abjad di daftar drop-down 'Urutkan berdasarkan' (Baris 1 dalam contoh ini). Di bidang lain, simpan Nilai Sel di kotak 'Urutkan', dan pilih 'A hingga Z' (urutan menaik) di kotak Urutan. Kemudian, klik 'OK' untuk menutup kotak dialog.
Sekarang, baris pertama di tabel kita diurutkan dalam urutan abjad (naik), dan baris yang tersisa disusun ulang seperti yang ditunjukkan di bawah ini.
Cara Mengurutkan Kolom Secara Abjad dengan Menggunakan Filter
Cara cepat lainnya untuk mengurutkan berdasarkan abjad di Excel adalah dengan menggunakan fitur Filter. Setelah Anda menerapkan filter ke kolom, opsi pengurutan untuk semua kolom dapat diakses hanya dengan satu klik mouse.
Untuk menambahkan filter ke kolom, pilih satu atau beberapa kolom dan klik opsi 'Filter' di grup Urutkan & Filter pada tab 'Data'. Jika Anda ingin menerapkan filter ke seluruh tabel, pilih sel mana saja di tabel dan klik opsi 'Filter'.
Anda juga dapat mengakses opsi Filter dengan mengklik alat 'Urutkan dan Filter' di grup Pengeditan pada tab 'Beranda', dan pilih 'Filter'.
Sebuah panah drop-down kecil akan muncul di setiap header kolom. Klik panah kolom mana pun yang ingin Anda urutkan menurut abjad, dan pilih 'Urutkan A ke Z' atau 'Urutkan Z ke A'.
Ini akan mengatur kolom yang Anda inginkan dalam urutan abjad yang Anda pilih, dan jika Anda memilih 'Urutkan A sampai Z', panah kecil ke atas akan muncul pada tombol filter yang menunjukkan urutan pengurutan (naik):
Penyortiran Tingkat Lanjut dalam Pesanan Khusus
Mengurutkan menurut abjad tidak ideal untuk setiap data abjad. Terkadang, data dapat diurutkan berdasarkan abjad, tetapi itu bukan cara yang paling efektif untuk mengurutkannya.
Mari kita bayangkan situasi di mana Anda mungkin memiliki daftar yang berisi nama bulan atau hari kerja, mengurutkan berdasarkan abjad tidak berguna dalam situasi itu. Lebih masuk akal untuk mengurutkan daftar itu secara kronologis. Jika Anda mengurutkan dari A sampai Z, itu akan mengatur bulan dalam urutan abjad, April akan datang lebih dulu, lalu Agustus, Februari, Juni, dan seterusnya. Tapi ini bukan yang Anda inginkan. Untungnya, sangat mudah untuk mengatur secara kronologis di Excel menggunakan opsi Advanced Custom Sort.
Mulailah dengan memilih tabel yang ingin Anda urutkan. Kemudian pilih 'Urutkan Kustom' di bawah 'Urutkan & Filter' di bagian Pengeditan pada tab Beranda.
Pada kotak dialog Urutkan, pilih kolom yang berisi bulan dalam setahun di bagian kolom, urutkan pada 'Nilai', seperti biasa, dan di bagian Urutan, pilih 'Daftar Kustom'.
Dalam dialog Daftar Kustom, Anda dapat membuat daftar Anda sendiri. Ada beberapa daftar kustom default termasuk hari dalam seminggu, bulan yang disingkat, bulan dalam setahun, dan sebagainya. Pilih opsi sortir yang sesuai sesuai kebutuhan Anda (dalam kasus kami Januari, Februari, ..) dan klik 'OK'. Kemudian klik 'OK' lagi untuk mengurutkan daftar Anda secara kronologis.
Anda akan melihat bahwa Anda telah berhasil mengurutkan daftar sesuai urutan bulan.
Cara Mengurutkan Secara Abjad di Excel Menggunakan Rumus Excel
Jika Anda adalah penggemar rumus, maka Anda dapat menggunakan rumus Excel untuk menyusun daftar menurut abjad. Dua rumus yang dapat digunakan untuk menyusun data menurut abjad adalah COUNTIF dan VLOOKUP.
Misalnya, kami memiliki daftar nama yang ingin kami urutkan berdasarkan abjad menggunakan rumus.
Untuk mengurutkan ini, kami akan menambahkan kolom sementara bernama 'Urutan pengurutan' ini ke tabel yang ada.
Di sel (A2) di sebelah data, gunakan rumus COUNTIF berikut:
=COUNTIF($B$2:$B$20,"<="&B2) Rumus di atas membandingkan nilai teks di sel B2 dengan semua nilai teks lainnya (B3:B20) dan mengembalikan peringkat relatifnya. Misalnya, di sel B2, ia mengembalikan 11, karena ada 11 nilai teks yang lebih rendah dari atau sama dengan teks 'Nancy' dalam urutan abjad. Hasilnya adalah urutan pengurutan nama karyawan di sel B2.
Kemudian gunakan gagang isian seret rumus ini untuk mengisinya di seluruh rentang. Ini memberikan urutan penyortiran masing-masing nama dalam daftar.
Sekarang, kita harus mengatur data yang ditunjukkan pada gambar di atas berdasarkan nomor urutan pengurutan, dan untuk melakukan ini kita akan menggunakan fungsi VLOOKUP.
Sintaks:
=VLOOKUP(,A:B,2,0)Di sini 'urutan nomor' mewakili angka dalam urutan menaik dari 1 – 20. Untuk urutan menurun, angkanya harus dari 20 – 1.
Di sini, kami memiliki Kolom D untuk nama yang diurutkan. Untuk sel D2, masukkan rumus VLOOKUP berikut:
=VLOOKUP(1,A:B,2,0)Demikian pula, untuk sel kedua dan ketiga, Anda harus menggunakan rumus sebagai:
=VLOOKUP(2,A:B,2,0) dan =VLOOKUP(3,A:B,2,0) dan seterusnya…
Setelah memasukkan rumus VLOOKUP ini ke setiap sel di sebelah data, daftar akan diurutkan menurut abjad.
Daripada memasukkan rumus yang disebutkan di atas secara manual (1-20) ke setiap sel, Anda juga dapat menggunakan fungsi baris untuk mempermudah pekerjaan Anda. Fungsi Baris () mengembalikan nomor baris sel saat ini. Jadi, dengan bantuan fungsi baris, rumusnya adalah:
=VLOOKUP(ROW()-1,A:B,2,0)Rumus ini akan memberikan hasil yang sama seperti rumus di atas.
Dan kemudian gunakan gagang isian untuk menyeret rumus ini untuk mengisinya di seluruh rentang.
Cara Mengabjadkan Entri dengan Nama Belakang
Terkadang, kita sering harus mengurutkan lembar data menurut abjad dengan nama belakang. Dalam kasus seperti itu, meskipun nama dimulai dengan nama depan, Anda perlu mengurutkannya menurut abjad dengan nama belakang. Anda dapat melakukan ini dengan rumus Teks Excel.
Untuk melakukannya, pertama, Anda harus mengekstrak nama depan dan nama belakang dari nama lengkap ke dalam dua kolom berbeda. Kemudian balikkan nama, urutkan, lalu balikkan kembali ke bentuk aslinya.
Dengan nama lengkap di A2, masukkan rumus berikut dalam dua sel berbeda (B2 dan C2), lalu salin rumus ke bawah kolom (menggunakan gagang isian) hingga sel terakhir dengan data:
Untuk mengekstrak nama depan, masukkan rumus ini di sel C2:
=KIRI(A2,SEARCH(” “,A2)-1)
Untuk mengekstrak nama belakang, masukkan rumus ini di sel D2:
=KANAN(A2,LEN(A2)-SEARCH(" ",A2,1))
Dan kemudian, di sel E2, gabungkan nama depan dan belakang dalam urutan terbalik yang dipisahkan oleh koma:
=D2&", "&C2
Kami telah memisahkan nama depan dan belakang dan membalikkannya. Sekarang kita harus menyusunnya menurut abjad dan membalikkannya kembali. Seperti yang Anda lihat di atas, saat kita mengekstrak nama menggunakan rumus, kolom C, D, dan E sebenarnya berisi rumus tetapi diformat agar terlihat seperti nilai. Jadi kita perlu mengubah rumus menjadi nilai.
Untuk melakukannya, pertama-tama pilih semua sel rumus (E1:E31) dan tekan Ctrl + C untuk menyalinnya. Klik kanan sel yang dipilih, klik ikon 'Nilai' di bawah 'Opsi Tempel', dan tekan tombol 'Enter'.
Kemudian, pilih sel mana saja di kolom yang dihasilkan, dan klik opsi 'A ke Z' atau 'Z ke A' pada tab 'Data' atau 'Beranda' sesuai kebutuhan Anda.
Kotak dialog Sort Warning akan muncul setelah Anda mengklik tombol. Pilih opsi 'Perluas Pilihan' dan klik tombol 'Urutkan'.
Seperti yang Anda lihat, seluruh kolom diurutkan menurut abjad nama belakang.
Sekarang, jika Anda ingin membalikkannya kembali ke 'Format Nama Depan Nama Belakang' asli, Anda hanya perlu memasukkan rumus yang sama tetapi dengan referensi sel yang berbeda.
Anda harus memisahkan nama (E2) menjadi dua bagian lagi dengan menggunakan rumus di bawah ini:
Di G2, ekstrak nama depan:
=KANAN(E2,LEN(E2)-SEARCH(" ",E2))
Di G2, tarik nama belakang:
=KIRI(E2,SEARCH(" ",E2)-2)
Dan gabungkan kedua bagian tersebut untuk mendapatkan nama lengkap asli:
=G2&" "&H2
Setiap kali Anda memasukkan rumus di sel pertama, pastikan untuk menyalin rumus ke bawah kolom (menggunakan gagang isian) hingga sel terakhir dengan data.
Sekarang yang perlu Anda lakukan adalah mengonversi rumus ke konversi nilai sekali lagi seperti yang kami lakukan di atas, dan selesai. ini dilakukan dan.