Urutkan Excel Dengan Rumus Menggunakan SORT dan SORTBY - Tips Excel

Minggu ini di Ignite Conference di Orlando Florida, Microsoft meluncurkan serangkaian formula array baru yang lebih mudah di Excel. Saya akan membahas formula baru ini setiap hari minggu ini, tetapi jika Anda ingin membaca sebelumnya:

Senin membahas rumus baru = A2: A20, kesalahan SPILL, dan fungsi TUNGGAL baru yang diperlukan sebagai pengganti Persimpangan Implisit
Hari ini akan membahas SORT dan SORTBY
Rabu akan meliput FILTER
Kamis akan membahas UNIK
Friday akan membahas fungsi SEQUENCE dan RANDARRAY

Mengurutkan dengan Rumus di Excel biasanya membutuhkan kombinasi rumus yang gila. Lihatlah data ini yang akan digunakan di seluruh artikel ini.

Data dalam A3: C11.

Untuk mengurutkan ini dengan rumus sebelum minggu ini, Anda hanya perlu menyelesaikan RANK, COUNTIF, MATCH, INDEX, dan INDEX. Setelah Anda menyelesaikan kumpulan rumus ini, Anda akan siap untuk tidur siang.

Cara lama mengurutkan dengan rumus

Joe McDaid dan timnya telah membawakan kami SORT dan SORTBY.

Mari kita mulai dengan SORT. Berikut sintaksnya=SORT(Array, (Sort Index), (Sort Order), (By Column))

Fungsi SORT

Katakanlah Anda ingin mengurutkan A3: C16 berdasarkan bidang Skor. Skor adalah kolom ketiga dalam larik, jadi Indeks Urutan Anda akan menjadi 3.

Pilihan untuk Sort Order adalah 1 untuk ascending atau -1 untuk descending. Saya tidak mengeluh, tetapi tidak akan pernah ada dukungan untuk Urutkan menurut Warna, Urutkan menurut Formula, atau Urutkan menurut Daftar Kustom menggunakan fungsi ini.

Tentukan 3 sebagai kolom pengurutan dan -1 sebagai urutan pengurutan untuk menurun.

Argumen keempat akan jarang digunakan. Dimungkinkan dalam dialog Urutkan untuk mengurutkan menurut kolom alih-alih baris. 99,9% orang mengurutkan berdasarkan baris. Jika Anda perlu mengurutkan menurut kolom, tentukan True di argumen terakhir. Argumen ini opsional dan defaultnya adalah False.

Jika Anda perlu mengurutkan berdasarkan kolom, gunakan True dalam argumen ke-4

Berikut hasil rumusnya. Berkat mesin kalk baru, rumus tersebut tumpah ke sel yang berdekatan. Satu formula dalam O2 menghasilkan solusi ini.

Tidak perlu menekan Ctrl + Shift + Enter

Data asli diurutkan

Bagaimana jika Anda membutuhkan jenis dua tingkat? Urutkan berdasarkan kolom 2 naik dan kolom 3 turun? Berikan konstanta array untuk argumen ke-2 dan ke-3:=SORT(A2:C17,(2;3),(1;-1))

Jenis dua tingkat

Fungsi SORTBY memungkinkan Anda mengurutkan berdasarkan sesuatu yang tidak ada dalam hasil

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

SORTBY sesuatu yang lain

Kembali ke data asli. Katakanlah Anda ingin mengurutkan berdasarkan Tim lalu Skor, tetapi hanya menampilkan nama. Anda bisa menggunakan SORTBY seperti yang ditunjukkan di sini.

Urutkan kolom A berdasarkan kolom B dan kolom C

Pengujian Obat Secara Acak dan Acak Tanpa Pengulangan

Skenario sulit seperti Random Drug Testing dan Random with No Repeats menjadi sangat sederhana saat Anda menggabungkan SORT dengan RANDARRAY.

Pada gambar di bawah ini, Anda ingin mengurutkan 13 nama secara acak tanpa pengulangan. Gunakan =SORTBY(A4:A16,RANDARRAY(13)). Baca lebih lanjut tentang RANDARRAY pada hari Jumat.

Menyortir secara acak tanpa pengulangan

Apakah Ctrl + Shift + Enter benar-benar mati? Tidak. Masih ada gunanya. Katakanlah Anda hanya menginginkan 3 hasil teratas dari fungsi SORT. Anda dapat memilih tiga sel, ketik fungsi SORT dan ikuti dengan Ctrl + Shift + Enter. Ini akan mencegah hasil yang keluar melebihi batas rumus aslinya.