Fungsi Sort Excel - Data Susun Auto Menggunakan Formula

Tutorial menunjukkan cara menggunakan fungsi jenis untuk menyusun array data secara dinamik. Anda akan mempelajari formula untuk menyusun mengikut abjad dalam Excel, mengatur nombor dalam urutan menaik atau menurun, disusun mengikut pelbagai lajur, dan banyak lagi.

Fungsi jenis telah lama wujud. Tetapi dengan pengenalan tatasusunan dinamik di Excel 365, terdapat cara yang sangat mudah untuk disusun dengan formula. Keindahan kaedah ini adalah bahawa keputusan kemas kini secara automatik apabila data sumber berubah.

• Fungsi jenis Excel
• Formula jenis asas
• Cara Mengasingkan Data dalam Excel Menggunakan Formula - Contoh
  • Sort Excel mengikut lajur
  • Disusun mengikut pelbagai lajur dalam urutan yang berbeza
  • Susun dan penapis dalam Excel
  • Susun dan dapatkan nilai atas atau bawah
• Cara mendapatkan array jenis untuk berkembang secara automatik
• Fungsi jenis excel tidak berfungsi

Fungsi jenis Excel

Fungsi jenis dalam Excel menyusun kandungan array atau julat dengan lajur atau baris, dalam urutan menaik atau menurun.

Susun milik kumpulan fungsi array dinamik. Hasilnya adalah array dinamik yang secara automatik tumpah ke sel -sel jiran secara menegak atau mendatar, bergantung kepada bentuk array sumber.

Sintaks fungsi jenis adalah seperti berikut:

Sort (array, [sort_index], [sort_order], [by_col])

Di mana:

Array (diperlukan) - adalah pelbagai nilai atau pelbagai sel untuk disusun. Ini boleh menjadi apa -apa nilai termasuk teks, nombor, tarikh, masa, dll.

Sort_index (pilihan) - Integer yang menunjukkan lajur atau baris mana yang akan disusun oleh. Jika ditinggalkan, indeks lalai 1 digunakan.

Sort_order (pilihan) - mentakrifkan susunan jenis:

• 1 atau ditinggalkan (lalai) - Perintah menaik, iaitu dari terkecil hingga terbesar
• -1 - Perintah menurun, iaitu dari terbesar hingga terkecil

BY_COL (Pilihan) - Nilai logik yang menunjukkan arah penyortiran:

• Palsu atau ditinggalkan (lalai) - Susun mengikut baris. Anda akan menggunakan pilihan ini sepanjang masa.
• Benar - Susun mengikut lajur. Gunakan pilihan ini jika data anda dianjurkan secara mendatar dalam lajur seperti dalam contoh ini.

Fungsi Sort Excel - Petua dan Nota

Sort adalah fungsi array dinamik baru dan oleh itu ia mempunyai beberapa spesifikasi yang harus anda ketahui:

• Pada masa ini fungsi jenis hanya terdapat di Microsoft 365 dan Excel 2021. Excel 2019, Excel 2016 tidak menyokong formula array dinamik, jadi fungsi jenis tidak tersedia dalam versi ini.
• Jika array yang dikembalikan oleh formula jenis adalah hasil akhir (iaitu tidak diserahkan kepada fungsi lain), Excel secara dinamik mencipta julat bersaiz yang sesuai dan populasi dengan nilai -nilai yang disusun. Oleh itu, pastikan anda sentiasa mempunyai sel kosong yang cukup ke bawah atau/dan di sebelah kanan sel di mana anda memasuki formula, jika tidak, ralat #Spill berlaku.
• Keputusan mengemas kini secara dinamik apabila data sumber berubah. Walau bagaimanapun, array yang dibekalkan kepada formula tidak dilanjutkan secara automatik untuk memasukkan entri baru yang ditambah di luar array yang dirujuk. Untuk memasukkan item tersebut, anda perlu mengemas kini rujukan array dalam formula anda, atau menukar julat sumber ke jadual seperti yang ditunjukkan dalam contoh ini, atau membuat julat bernama dinamik.

Formula Susun Excel Asas

Contoh ini menunjukkan formula asas untuk menyusun data dalam Excel dalam urutan menaik dan menurun.

Katakan data anda disusun mengikut abjad seperti yang ditunjukkan dalam tangkapan skrin di bawah. Anda ingin menyusun nombor dalam lajur B tanpa memecahkan atau mencampurkan data.

Formula untuk menyusun urutan menaik

Untuk menyusun nilai dalam lajur B dari terkecil hingga terbesar, inilah formula untuk digunakan:

=SORT(A2:B8, 2, 1)

Di mana:

• A2: B8 adalah array sumber
• 2 adalah nombor lajur untuk disusun mengikut
• 1 adalah urutan jenis menaik

Oleh kerana data kami dianjurkan dalam baris, hujah terakhir boleh ditinggalkan untuk lalai kepada False - Sort by Rows.

Hanya masukkan formula dalam mana -mana sel kosong (D2 dalam kes kami), tekan Enter , dan hasilnya akan tumpah secara automatik kepada D2: E8.

Formula untuk menyusun dalam urutan menurun

Untuk menyusun data menurun, iaitu dari yang terbesar hingga terkecil, tetapkan argumen sort_order kepada -1 seperti ini:

=SORT(A2:B8, 2, -1)

Masukkan formula di sel kiri atas julat destinasi dan anda akan mendapat hasil ini:

Dengan cara yang sama, anda boleh menyusun nilai teks dalam susunan abjad dari a ke z atau dari z ke A.

Cara menyusun data dalam excel menggunakan formula

Contoh-contoh di bawah menunjukkan beberapa kegunaan biasa fungsi jenis dalam Excel dan beberapa yang tidak remeh.

Sort Excel mengikut lajur

Apabila anda menyusun data dalam Excel, sebahagian besar anda menukar urutan baris. Tetapi apabila data anda dianjurkan secara mendatar dengan baris yang mengandungi label dan lajur yang mengandungi rekod, anda mungkin perlu menyusun dari kiri ke kanan, dan bukannya dari atas ke bawah.

Untuk menyusun mengikut lajur dalam Excel, tetapkan hujah BY_COL kepada Benar. Dalam kes ini, sort_index akan mewakili baris, bukan lajur.

Sebagai contoh, untuk menyusun data di bawah oleh QTY. Dari tertinggi hingga terendah, gunakan formula ini:

=SORT(B1:H2, 2, 1, TRUE)

Di mana:

• B1: H2 adalah data sumber untuk disusun
• 2 adalah indeks jenis, kerana kita menyusun nombor di baris kedua
• -1 menunjukkan urutan jenis menurun
• Benar bermaksud menyusun lajur, bukan baris

Sort dengan pelbagai lajur dalam urutan yang berbeza (jenis pelbagai peringkat)

Apabila bekerja dengan model data yang kompleks, anda mungkin memerlukan jenis pelbagai peringkat. Bolehkah itu dilakukan dengan formula? Ya, mudah! Apa yang anda lakukan ialah membekalkan pemalar array untuk argumen sort_index dan sort_order .

Sebagai contoh, untuk menyusun data di bawah terlebih dahulu mengikut rantau (lajur A) dari A hingga Z, dan kemudian oleh Qty . (lajur c) Dari terkecil hingga terbesar, tetapkan hujah berikut:

• Array adalah data dalam A2: C13.
• Sort_index adalah array constant {1,3}, kerana kita mula -mula menyusun oleh rantau (lajur ¹ ), dan kemudian oleh qty . (3 ^lajur RD).
• Sort_order adalah array constant {1, -1}, kerana lajur pertama hendaklah disusun dalam urutan menaik dan lajur 3 ^RD dalam urutan menurun.
• BY_COL ditinggalkan kerana kita menyusun baris, yang lalai.

Meletakkan hujah -hujah bersama, kami mendapat formula ini:

=SORT(A2:C13, {1,3}, {1,-1})

Dan ia berfungsi dengan sempurna! Nilai teks dalam lajur pertama disusun mengikut abjad dan nombor dalam lajur ketiga dari terbesar hingga terkecil:

Susun dan penapis dalam Excel

Sekiranya anda ingin menapis data dengan beberapa kriteria dan meletakkan output dalam rangka, gunakan fungsi jenis dan penapis bersama -sama:

Sort (penapis (array, kriteria_range = kriteria ), [sort_index], [sort_order], [by_col])

Fungsi penapis mendapat pelbagai nilai berdasarkan kriteria yang anda tentukan dan lulus array ke argumen pertama jenis.

Perkara terbaik mengenai formula ini ialah ia juga mengeluarkan hasilnya sebagai julat tumpahan yang dinamik, tanpa anda perlu menekan CTRL Shift Enter atau meneka berapa banyak sel untuk menyalinnya. Seperti biasa, anda menaip formula A di bahagian paling atas dan tekan kekunci Enter.

Sebagai contoh, kita akan mengekstrak item dengan kuantiti yang sama dengan atau lebih daripada 30 (> = 30) dari data sumber dalam A2: B9 dan mengatur hasil dalam urutan menaik.

Untuk ini, kami mula -mula menubuhkan keadaan, katakan, dalam sel E2 seperti yang ditunjukkan dalam imej di bawah. Dan kemudian, bina formula jenis Excel kami dengan cara ini:

=SORT(FILTER(A2:B9, B2:B9>=E2), 2)

Selain daripada array yang dihasilkan oleh fungsi penapis, kami hanya menentukan argumen sort_index (lajur 2). Dua lagi argumen yang ditinggalkan kerana lalai berfungsi dengan tepat seperti yang kita perlukan (semacam menaik, mengikut baris).

Dapatkan nilai terbesar atau terkecil dan menyusun hasilnya

Apabila menganalisis pukal besar jika maklumat, seringkali keperluan untuk mengekstrak sejumlah nilai tertinggi. Mungkin bukan hanya ekstrak, tetapi juga mengaturnya dalam urutan yang dikehendaki. Dan idealnya, pilih lajur mana yang hendak dimasukkan ke dalam hasilnya. Kedengarannya rumit? Tidak dengan fungsi array dinamik baru!

Berikut adalah formula generik:

Indeks (sort (...), urutan ( n ), { column1_to_return , column2_to_return , ...})

Di mana n ialah bilangan nilai yang anda mahu kembali.

Dari set data di bawah, anggap anda ingin mendapatkan senarai teratas 3 berdasarkan nombor dalam lajur C.

Untuk melakukannya, anda mula -mula menyusun array A2: C13 oleh lajur 3 ^RD dalam urutan menurun:

SORT(A2:C13, 3, -1)

Dan kemudian, sarang formula di atas dalam argumen pertama ( array ) fungsi indeks untuk mempunyai array yang disusun dari yang tertinggi hingga terkecil.

Untuk hujah kedua ( row_num ), yang menunjukkan berapa banyak baris untuk kembali, menghasilkan nombor berurutan yang diperlukan dengan menggunakan fungsi urutan. Seperti yang kita perlukan 3 nilai atas, kita menggunakan urutan (3), yang sama seperti membekalkan array menegak {1; 2; 3} secara langsung dalam formula.

Untuk hujah ketiga ( col_num ), yang mentakrifkan berapa lajur untuk kembali, membekalkan nombor lajur dalam bentuk pemalar array mendatar. Kami mahu mengembalikan lajur B dan C, jadi kami menggunakan array {2,3}.

Akhirnya, kami mendapat formula berikut:

=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), {2,3})

Dan ia menghasilkan hasil yang kita mahukan:

Untuk mengembalikan 3 nilai bawah , susunkan data asal dari terkecil ke yang terbesar. Untuk ini, tukar hujah sort_order dari -1 hingga 1:

=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), {2,3})

Mengembalikan nilai yang disusun dalam kedudukan tertentu

Melihat dari sudut lain, bagaimana jika anda hanya mahu mengembalikan kedudukan jenis tertentu? Katakanlah, hanya yang pertama, hanya ke -2, atau hanya rekod ke -3 dari senarai yang disusun? Untuk melakukannya, gunakan versi mudah formula jenis indeks yang dibincangkan di atas:

Indeks (sort (...), n , { column1_to_return , column2_to_return , ...})

Di mana n adalah kedudukan kepentingan.

Sebagai contoh, untuk mendapatkan kedudukan tertentu dari atas (iaitu dari data yang disusun turun), gunakan formula ini:

=INDEX(SORT(A2:C13, 3, -1), F1, {2,3})

Untuk mendapatkan kedudukan tertentu dari bawah (iaitu dari data yang disusun naik), gunakan yang satu ini:

=INDEX(SORT(A2:C13, 3, 1), I1, {2,3})

Di mana A2: C13 adalah data sumber, F1 adalah kedudukan dari atas, i1 adalah kedudukan dari bawah, dan {2,3} adalah lajur yang akan dikembalikan.

Gunakan jadual Excel untuk mendapatkan array jenis untuk berkembang secara automatik

Seperti yang sudah anda ketahui, kemas kini array yang disusun secara automatik apabila anda membuat sebarang perubahan pada data asal. Ini adalah tingkah laku standard semua fungsi array dinamik, termasuk jenis. Walau bagaimanapun, apabila anda menambah penyertaan baru di luar array yang dirujuk, mereka tidak dimasukkan secara automatik dalam formula. Jika anda ingin formula anda untuk bertindak balas terhadap perubahan tersebut, tukar julat sumber ke jadual Excel yang berfungsi sepenuhnya dan gunakan rujukan berstruktur dalam formula anda.

Untuk melihat bagaimana ia berfungsi dalam amalan, sila pertimbangkan contoh berikut.

Katakan anda menggunakan formula jenis Excel di bawah untuk mengatur nilai dalam julat A2: B8 dalam susunan abjad:

=SORT(A2:B8, 1, 1)

Kemudian, anda memasukkan entri baru dalam baris 9 ... dan kecewa melihat bahawa entri yang baru ditambah dibiarkan daripada julat tumpahan:

Sekarang, tukar julat sumber ke meja. Untuk ini, pilih julat anda termasuk tajuk lajur (A1: B8) dan tekan Ctrl T. Apabila membina formula anda, pilih julat sumber menggunakan tetikus, dan nama jadual akan dimasukkan dalam formula secara automatik (ini dipanggil rujukan berstruktur):

=SORT(Table1, 1, 1)

Apabila anda menaip entri baru tepat di bawah baris terakhir, jadual akan berkembang secara automatik, dan data baru akan dimasukkan ke dalam julat tumpahan formula jenis:

Fungsi jenis excel tidak berfungsi

Jika formula jenis anda mengakibatkan kesilapan, kemungkinan besar kerana sebab -sebab berikut.

Ralat #Name: Versi Excel yang lebih tua

Susun adalah fungsi baru dan hanya berfungsi dalam Excel 365 dan Excel 2021. Dalam versi lama di mana fungsi ini tidak disokong, #name? ralat berlaku.

Kesalahan #spill: Sesuatu blok tumpahan

Jika satu atau lebih sel dalam julat tumpahan tidak sepenuhnya kosong atau digabungkan, #spill! Ralat dipaparkan. Untuk memperbaikinya, keluarkan penyumbatan. Untuk maklumat lanjut, sila lihat Excel #Spill! Ralat - Apa maksudnya dan bagaimana untuk memperbaiki.

Ralat #Value: Argumen tidak sah

Setiap kali anda masuk ke #Value! Ralat, periksa argumen sort_index dan sort_order . Sort_index tidak boleh melebihi bilangan lajur adalah array , dan sort_order hendaklah sama ada 1 (menaik) atau -1 (turun).

Ralat #REF: Buku kerja sumber ditutup

Oleh kerana array dinamik mempunyai sokongan terhad untuk rujukan antara buku kerja, fungsi jenis memerlukan kedua -dua fail dibuka. Jika buku kerja sumber ditutup, formula akan membuang #REF! ralat. Untuk memperbaikinya, buka fail yang dirujuk.

Itulah cara menyusun data dalam Excel menggunakan formula. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Menyusun Excel dengan Formula (fail .xlsx)

Atas ialah kandungan terperinci Fungsi Sort Excel - Data Susun Auto Menggunakan Formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!