Susun Excel Dengan Formula Menggunakan SORT dan SORTBY - Petua Excel

Minggu ini di Persidangan Ignite di Orlando Florida, Microsoft memulakan siri formula array baru yang lebih mudah di Excel. Saya akan merangkumi formula baru ini setiap hari minggu ini, tetapi jika anda ingin membaca lebih awal:

Isnin merangkumi formula = A2: A20 yang baru, kesalahan SPILL, dan fungsi SINGLE baru yang diperlukan untuk menggantikan Persimpangan Tersirat
Hari ini akan merangkumi SORT dan SORTBY
Hari Rabu akan meliputi FILTER
Hari Khamis akan meliputi UNIK
Hari Jumaat akan merangkumi fungsi SEQUENCE dan RANDARRAY

Menyusun dengan Formula di Excel yang digunakan untuk memerlukan kombinasi formula yang tidak siuman. Lihatlah data ini yang akan digunakan sepanjang artikel ini.

Data dalam A3: C11.

Untuk menyelesaikannya dengan formula sebelum minggu ini, anda hanya perlu mengalahkan RANK, COUNTIF, MATCH, INDEX dan INDEX. Setelah anda menyelesaikan set formula ini, anda akan bersedia untuk tidur siang.

Cara lama untuk menyusun dengan formula

Joe McDaid dan pasukannya telah membawa kami SORT and SORTBY.

Mari mulakan dengan SORT. Inilah sintaksnya=SORT(Array, (Sort Index), (Sort Order), (By Column))

Fungsi SORT

Katakan anda mahu menyusun A3: C16 mengikut medan Skor. Skor adalah lajur ketiga dalam array, jadi Indeks Urutan anda akan menjadi 3.

Pilihan untuk Urutan Urut adalah 1 untuk menaik atau -1 untuk menurun. Saya tidak mengeluh, tetapi tidak akan pernah ada sokongan untuk Sort by Color, Sort by Formula, atau Sort by Custom List menggunakan fungsi ini.

Nyatakan 3 sebagai lajur urut dan -1 sebagai susunan urutan untuk menurun.

Hujah keempat jarang digunakan. Ada kemungkinan dalam dialog Susun untuk menyusun mengikut lajur dan bukannya baris. 99.9% orang mengisih mengikut baris. Sekiranya anda perlu menyusun mengikut lajur, tentukan Benar dalam argumen terakhir. Hujah ini adalah pilihan dan lalai untuk Salah.

Sekiranya anda perlu menyusun mengikut lajur, gunakan True dalam argumen ke-4

Berikut adalah hasil formula. Terima kasih kepada enjin calc yang baru, formula tumpah ke sel yang berdekatan. Satu formula dalam O2 menghasilkan penyelesaian ini.

Tidak perlu menekan Ctrl + Shift + Enter

Data asal disusun

Bagaimana jika anda memerlukan jenis dua peringkat? Isih mengikut lajur 2 menaik dan lajur 3 menurun? Bekalkan pemalar array untuk argumen ke-2 dan ke-3=SORT(A2:C17,(2;3),(1;-1))

Jenis dua peringkat

Fungsi SORTBY membolehkan anda menyusun sesuatu yang tidak ada dalam hasil

Sintaks fungsi SORTBY adalah =SORTBY(array, by_array1, sort_order1,)

SORTBY sesuatu yang lain

Kembali ke data asal. Katakan anda mahu menyusun mengikut Pasukan kemudian Skor, tetapi hanya menunjukkan namanya. Anda boleh menggunakan SORTBY seperti yang ditunjukkan di sini.

Isih lajur A mengikut lajur B dan lajur C

Ujian Dadah Rawak dan Rawak Tanpa Ulangan

Senario sukar seperti Ujian Ubat Rawak dan Rawak Tanpa Ulangan menjadi sangat mudah apabila anda menggabungkan SORT dengan RANDARRAY.

Dalam gambar di bawah, anda ingin menyusun 13 nama secara rawak tanpa mengulang. Gunakan =SORTBY(A4:A16,RANDARRAY(13)). Baca lebih lanjut mengenai RANDARRAY pada hari Jumaat.

Menyusun secara rawak tanpa berulang

Adakah Ctrl + Shift + Enter mati sepenuhnya? Tidak. Masih ada kegunaannya. Katakan anda hanya mahukan 3 hasil teratas dari fungsi SORT. Anda boleh memilih tiga sel, taip fungsi SORT dan ikuti dengan Ctrl + Shift + Enter. Ini akan mengelakkan hasil daripada tumpah melebihi batas formula asal.