Panduan ini akan menunjukkan kepada anda cara menyusun abjad dalam Excel menggunakan ciri Isih dan Penapis serta formula untuk menyusun dan memformat data mengikut susunan.
Microsoft Excel ialah perisian yang berkesan untuk mengatur data penting, seperti kewangan, data jualan, maklumat pelanggan, dsb. Mengabjadkan data dalam lembaran kerja ialah salah satu cara terbaik untuk menyusun, mengakses dan merujuk data tersebut dengan cepat. Mengabjadkan julat sel bermakna menyusun julat mengikut abjad dalam excel. Data boleh diisih kedua-dua cara, sama ada dalam tertib menaik atau dalam tertib menurun.
Sebagai contoh, katakan anda mempunyai set data besar maklumat pesanan pelanggan dan anda ingin mencari sejarah pesanan pelanggan tertentu. Menyisir seluruh senarai akan menjadi tugas yang agak sukar. Daripada membuang masa untuk menatal, anda boleh menyusun abjad lajur anda untuk mencari data anda dengan cepat.
Terdapat pelbagai cara yang boleh anda gunakan untuk mengabjad data dalam Excel, bergantung pada jenis set data yang anda sedang usahakan. Excel membolehkan anda mengisih satu lajur atau satu baris atau julat yang dipilih atau keseluruhan lembaran kerja, serta pilihan untuk mengumpulkan berbilang lajur atau baris bersama semasa anda mengisihnya. Mari belajar cara mengabjad data dalam hamparan Excel mengikut baris atau lajur.
Mengapa Menghurufkan data dalam Excel
Mengabjadkan data akan mempunyai banyak kelebihan:
- Ia menjadikan data lebih masuk akal dan menjadikannya lebih mudah dibaca.
- Ia memudahkan pengguna mencari nilai atau nama pelanggan tertentu dalam lembaran data Excel.
- Ia membantu pengguna mengenal pasti rekod pendua secara visual supaya kami dapat mengelakkan ralat kemasukan data.
- Ia membolehkan pengguna mengumpulkan lajur atau senarai dengan mudah supaya pengguna boleh melihatnya secara bersebelahan.
Abjad (isih mengikut abjad) ialah cara yang mudah dan biasa untuk mengisih maklumat anda dengan cepat. Ia amat berguna untuk set data yang sangat besar. Secara keseluruhannya, terdapat 4 cara untuk mengabjad data dalam Excel: butang A-Z atau Z-A, ciri Isih, fungsi penapis dan formula.
Mengabjadkan Lajur dalam Excel
Cara terpantas untuk mengisih mengikut abjad dalam Excel adalah dengan menggunakan ciri Isih terbina dalam.
Mula-mula, buka hamparan yang mengandungi set data. Pilih sel atau lajur yang anda ingin isih dan pastikan anda tidak memilih mana-mana sel kosong. Kemudian, pergi ke tab 'Data' dan klik sama ada 'A-Z' untuk mengisih menaik atau 'Z-A' untuk mengisih menurun dalam kumpulan Isih dan Penapis.
Jika terdapat sebarang sel kosong di antaranya, Excel menganggap bahawa semua data telah diisih dan berhenti di atas sel hitam. Jika anda ingin mengisih keseluruhan lajur hanya pilih mana-mana sel dalam lajur dan gunakan pilihan di atas.
Pilihan yang sama juga boleh diakses di bawah alat 'Isih dan Penapis' dalam bahagian Penyuntingan tab 'Rumah'.
Sebaik sahaja anda mengklik butang, cemerlang abjad senarai anda dengan segera.
Cara Isih dan Simpan Baris Bersama dalam Excel
Anda boleh mengisih lajur dengan mudah jika anda hanya mempunyai satu lajur dalam lembaran kerja anda, tetapi ia menjadi sedikit rumit apabila lembaran kerja anda mengandungi berbilang lajur di sebelah lajur yang ingin anda isih. Selain itu, kadangkala, kami bekerja pada hamparan di mana kami perlu memastikan baris yang sepadan utuh selepas mengisih lajur.
Contohnya, jika kami mempunyai hamparan senarai markah pelajar. Apabila kami mengisih lajur nama pelajar dalam susunan abjad, kami juga mahu markah yang dimasukkan dalam baris di sebelahnya juga harus bergerak dengan sewajarnya.
Dalam kes sedemikian, anda boleh menggunakan butang 'A-Z' atau 'Z-A' untuk mengisih salah satu lajur dalam susunan abjad dan ia akan menyusun data secara automatik dalam lajur lain, mengekalkan baris bersama-sama. Mari lihat bagaimana ia berfungsi dengan contoh lain.
Mula-mula, pilih lajur pertama mengikut mana anda ingin mengisih lajur lain. Kemudian pilih sama ada 'A-Z' atau 'Z-A' di bawah tab Data atau tab Laman Utama untuk mengisih data dalam satu lajur, dan Excel akan menyusun semula lajur lain secara automatik dengan sewajarnya.
Sebaik sahaja anda mengklik butang pengisihan, tetingkap dialog Amaran Isih akan muncul. Pilih butang radio untuk ‘Kembangkan pilihan’ dan klik butang ‘Isih’.
Ini akan mengekalkan baris bersama-sama semasa mengisih lajur, tanpa memadankan sebarang data (seperti yang ditunjukkan di bawah).
Jika anda memilih pilihan 'Teruskan dengan pilihan semasa', Excel hanya akan mengisih lajur yang dipilih.
Cara Isih Mengikut Abjad dengan Berbilang Lajur
Jika anda ingin mengisih set data mengikut abjad mengikut lebih daripada satu lajur, anda boleh melakukannya dengan ciri Isih Excel.
Sebagai contoh, kami ingin menyusun jadual berikut mengikut abjad dahulu mengikut Negara, dan kemudian dengan Nama Pertama:
Mula-mula, pilih keseluruhan jadual yang anda ingin isikan mengikut abjad, kemudian pergi ke tab 'Data' dan dalam kumpulan Isih dan Penapis, klik pada 'Isih'.
Atau klik ikon 'Isih & Tapis' dalam bahagian Penyuntingan tab 'Rumah' dan pilih 'Isih Tersuai' untuk mengakses ciri yang sama.
Kotak dialog Isih akan muncul dan menunjukkan lajur pertama (parameter) di mana jadual akan diisih. Dalam kotak lungsur turun 'Isih mengikut', pilih lajur utama yang anda mahu abjadkan data dahulu, 'Negara' dalam kes kami dan dalam menu lungsur 'Urutan' pilih sama ada 'A hingga Z' atau 'Z ke A' .
Kemudian, klik butang 'Tambah Tahap' untuk menambah tahap pengisihan kedua. Pilih lajur kedua (dalam kes kami Nama Pertama) yang anda mahu abjadkan data dalam kotak 'Isih Mengikut' dan pilih sama ada 'A hingga Z' atau 'Z ke A'. Jika jadual anda mempunyai pengepala di bahagian atas, tandai kotak semak 'Data Saya Mempunyai Pengepala' supaya ia boleh melangkau pengepala semasa mengisihnya. Tambahkan lebih banyak tahap isihan jika perlu, dan klik 'OK'.
Jadual disusun mengikut abjad: pertama mengikut Negara, dan kemudian dengan Nama Pertama seperti yang ditunjukkan di bawah.
Cara Isih Baris Mengikut Abjad dalam Excel
Kadangkala anda mungkin mahu mengabjad baris dalam Excel, bukannya lajur. Ini boleh dilakukan dengan menggunakan ciri Sort Excel. Sebagai contoh, katakan anda mempunyai hamparan dengan lajur baris pertama B hingga T mengandungi nama bandar yang bilangan kedai tersedia di bawah jenis runcit yang berbeza dan Lajur A mengandungi jenis peruncit. Sel digunakan untuk menjejaki bilangan kedai di setiap bandar dalam kategori peruncit yang berbeza.
Mula-mula, pilih semua baris yang ingin anda abjadkan; Jika anda tidak mahu mengalihkan label baris, tinggalkan label tersebut daripada pilihan anda. Sekarang, navigasi ke tab 'Data' dan klik 'Isih' dalam kumpulan Isih dan Penapis.
Dalam kotak dialog Isih, klik butang 'Pilihan'.
Satu lagi kotak dialog kecil akan muncul, memberi anda pilihan untuk memilih 'Isih kiri ke kanan' atau 'Isih atas ke bawah'. Pilih pilihan 'Isih kiri ke kanan' untuk mengikut abjad mengikut baris dan klik 'OK' untuk kembali ke kotak dialog Isih.
Sekarang, pilih Baris yang anda ingin abjadkan dalam senarai juntai bawah 'Isih mengikut' (Baris 1 dalam contoh ini). Dalam medan lain, simpan Nilai Sel dalam kotak 'Isih pada' dan pilih 'A hingga Z' (tertib menaik) dalam kotak Pesanan. Kemudian, klik 'OK' untuk menutup kotak dialog.
Sekarang, baris pertama dalam jadual kami diisih mengikut susunan abjad (menaik), dan baris yang selebihnya disusun semula dengan sewajarnya seperti yang ditunjukkan di bawah.
Cara Menyusun Lajur Mengikut Abjad dengan Menggunakan Penapis
Satu lagi cara pantas untuk mengisih mengikut abjad dalam Excel ialah dengan menggunakan ciri Penapis. Sebaik sahaja anda menggunakan penapis pada lajur, pilihan pengisihan untuk semua lajur boleh diakses dengan hanya satu klik tetikus.
Untuk menambah penapis pada lajur, pilih satu atau lebih lajur dan klik pilihan 'Penapis' dalam kumpulan Isih & Tapis pada tab 'Data'. Jika anda ingin menggunakan penapis pada keseluruhan jadual, pilih mana-mana sel dalam jadual dan klik pilihan 'Penapis'.
Anda juga boleh mengakses pilihan Penapis dengan mengklik alat 'Isih dan Tapis' dalam kumpulan Penyuntingan tab 'Rumah', dan memilih 'Penapis'.
Anak panah lungsur kecil akan muncul dalam setiap pengepala lajur. Klik pada anak panah mana-mana lajur yang anda mahu susun mengikut abjad, dan pilih sama ada 'Isih A hingga Z' atau 'Isih Z ke A'.
Ini akan menyusun lajur yang anda inginkan dalam susunan abjad pilihan anda, dan jika anda memilih 'Isih A hingga Z', anak panah kecil ke atas akan muncul pada butang penapis yang menunjukkan susunan isihan (menaik):
Isih Lanjutan dalam Pesanan Tersuai
Mengisih mengikut abjad tidak sesuai untuk setiap data abjad. Kadangkala, data boleh mengikut abjad, tetapi ia bukan cara yang paling berkesan untuk mengisihnya.
Mari bayangkan situasi di mana anda mungkin mempunyai senarai yang mengandungi nama bulan atau hari bekerja, pengisihan mengikut abjad tidak berguna dalam situasi itu. Lebih masuk akal untuk mengisih senarai itu secara kronologi. Jika anda mengisih dari A hingga Z, ia akan menyusun bulan mengikut abjad, April akan datang dahulu, kemudian Ogos, Februari, Jun, dan seterusnya. Tetapi ini bukan yang anda mahukan. Nasib baik, sangat mudah untuk mengatur secara kronologi dalam Excel menggunakan pilihan Isih Tersuai Lanjutan.
Mulakan dengan memilih jadual yang ingin diisih. Kemudian pilih 'Isih Tersuai' di bawah 'Isih & Penapis' dalam bahagian Penyuntingan tab Laman Utama.
Dalam kotak dialog Isih, pilih lajur yang mengandungi bulan dalam setahun dalam bahagian lajur, isih pada 'Nilai', seperti biasa, dan dalam bahagian Pesanan, pilih 'Senarai Tersuai'.
Dalam dialog Senarai Tersuai, anda boleh membuat senarai anda sendiri. Terdapat beberapa senarai tersuai lalai termasuk hari dalam minggu, bulan yang disingkat, bulan dalam tahun dan sebagainya. Pilih pilihan isihan yang sesuai mengikut keperluan anda (dalam kes kami Januari, Februari, ..) dan klik 'OK'. Kemudian klik 'OK' sekali lagi untuk mengisih senarai anda mengikut kronologi.
Anda akan melihat bahawa anda telah berjaya mengisih senarai mengikut urutan bulan.
Cara Menyusun Mengikut Abjad dalam Excel Menggunakan Formula Excel
Jika anda seorang peminat formula, maka anda boleh menggunakan formula Excel untuk mengabjad senarai. Dua formula yang boleh digunakan untuk data abjad ialah COUNTIF dan VLOOKUP.
Sebagai contoh, kami mempunyai senarai nama yang ingin kami abjadkan menggunakan formula.
Untuk mengisih ini, kami akan menambah lajur sementara yang dinamakan 'Pesanan isihan' ini pada jadual sedia ada.
Dalam sel (A2) bersebelahan dengan data, gunakan formula COUNTIF berikut:
=COUNTIF($B$2:$B$20,"<="&B2) Formula di atas membandingkan nilai teks dalam sel B2 dengan semua nilai teks lain (B3:B20) dan mengembalikan kedudukan relatifnya. Sebagai contoh, dalam sel B2, ia mengembalikan 11, kerana terdapat 11 nilai teks yang lebih rendah daripada atau sama dengan teks 'Nancy' dalam susunan abjad. Hasilnya ialah susunan pengisihan nama pekerja dalam sel B2.
Kemudian gunakan pemegang isian seret formula ini untuk mengisi keseluruhan julat. Ini memberikan susunan pengisihan bagi setiap nama dalam senarai.
Sekarang, kita perlu menyusun data yang ditunjukkan dalam tangkapan skrin di atas berdasarkan nombor pesanan pengisihan, dan untuk melakukan ini kita akan menggunakan fungsi VLOOKUP.
Sintaks:
=VLOOKUP(,A:B,2,0)Di sini 'nombor susun' mewakili nombor dalam tertib menaik dari 1 – 20. Untuk susunan menurun, nombor hendaklah dari 20 – 1.
Di sini, kami mempunyai Lajur D untuk nama yang diisih. Untuk sel D2, masukkan formula VLOOKUP berikut:
=VLOOKUP(1,A:B,2,0)Begitu juga, untuk sel kedua dan ketiga, anda perlu menggunakan formula sebagai:
=VLOOKUP(2,A:B,2,0) dan =VLOOKUP(3,A:B,2,0) dan sebagainya…
Selepas memasukkan formula VLOOKUP ini ke setiap sel bersebelahan data, senarai akan mengikut abjad.
Daripada memasukkan formula yang disebut di atas secara manual (1-20) ke setiap sel, anda juga boleh menggunakan fungsi baris untuk memudahkan kerja anda. Fungsi Baris () mengembalikan nombor baris sel semasa. Jadi, dengan bantuan fungsi baris, formulanya ialah:
=VLOOKUP(ROW()-1,A:B,2,0)Formula ini akan memberikan anda hasil yang sama seperti formula di atas.
Dan kemudian gunakan pemegang isian untuk menyeret formula ini untuk mengisi keseluruhan julat.
Cara Menghurufkan Entri dengan Nama Akhir
Kadangkala, kita selalunya perlu mengisih lembaran data mengikut abjad dengan nama keluarga. Dalam kes sedemikian, walaupun nama bermula dengan nama pertama, anda perlu mengabjadkannya dengan nama akhir. Anda boleh melakukan ini dengan formula Teks Excel.
Untuk melakukan itu, pertama, anda perlu mengekstrak nama pertama dan nama akhir daripada nama penuh ke dalam dua lajur berbeza. Kemudian terbalikkan nama-nama itu, susunkannya, dan kemudian balikkannya kembali kepada bentuk asalnya.
Dengan nama penuh dalam A2, masukkan formula berikut dalam dua sel berbeza (B2 dan C2), dan kemudian salin formula ke bawah lajur (menggunakan pemegang isian) sehingga sel terakhir dengan data:
Untuk mengekstrak nama pertama, masukkan formula ini dalam sel C2:
=LEFT(A2,SEARCH(” “,A2)-1)
Untuk mengekstrak nama akhir, masukkan formula ini dalam sel D2:
=KANAN(A2,LEN(A2)-CARI(" ",A2,1))
Dan kemudian, dalam sel E2, menggabungkan nama pertama dan nama keluarga dalam susunan terbalik yang dipisahkan dengan koma:
=D2&", "&C2
Kami telah memisahkan nama pertama dan nama keluarga dan membalikkannya. Sekarang kita perlu mengabjadnya dan membalikkannya semula. Seperti yang anda lihat di atas, apabila kami mengekstrak nama menggunakan formula, lajur C, D dan E sebenarnya mengandungi formula tetapi ia diformatkan untuk kelihatan seperti nilai. Jadi kita perlu menukar formula kepada nilai.
Untuk berbuat demikian, mula-mula pilih semua sel formula (E1:E31) dan tekan Ctrl + C untuk menyalinnya. Klik kanan sel yang dipilih, klik pada ikon 'Nilai' di bawah 'Pilihan Tampal', dan tekan kekunci 'Enter'.
Kemudian, pilih mana-mana sel dalam lajur yang terhasil, dan klik pilihan 'A to Z' atau 'Z to A' pada tab 'Data' atau 'Home' mengikut keperluan anda.
Kotak dialog Isih Amaran akan muncul sebaik sahaja anda mengklik butang. Pilih pilihan 'Expand Selection' dan klik pada butang 'Isih'.
Seperti yang anda lihat, keseluruhan lajur mengikut abjad nama akhir.
Sekarang, jika anda ingin membalikkannya kembali kepada format asal 'First Name Last Name', anda hanya perlu memasukkan formula yang sama tetapi dengan rujukan sel yang berbeza.
Anda perlu memisahkan nama (E2) kepada dua bahagian sekali lagi dengan menggunakan formula di bawah:
Dalam G2, ekstrak nama pertama:
=KANAN(E2,LEN(E2)-CARI(" ",E2))
Dalam G2, tarik nama akhir:
=LEFT(E2,SEARCH(" ",E2)-2)
Dan gabungkan dua bahagian bersama-sama untuk mendapatkan nama penuh asal:
=G2&" "&H2
Setiap kali anda memasukkan formula dalam sel pertama, pastikan anda menyalin formula ke bawah lajur (menggunakan pemegang isian) sehingga sel terakhir dengan data.
Kini apa yang anda perlu lakukan ialah menukar formula kepada penukaran nilai sekali lagi seperti yang kami lakukan di atas dan anda telah selesai. ini dilakukan dan.