Jika Anda perlu mengurutkan daftar, maka ada banyak cara untuk melayani Anda, yang paling mudah adalah tombol sortir di tab atau di menu Data (Data — Urutkan). Namun, ada situasi di mana pengurutan daftar perlu dilakukan secara otomatis, yaitu rumus. Ini mungkin diperlukan, misalnya, saat membuat data untuk daftar drop-down, saat menghitung data untuk bagan, dll. Bagaimana cara mengurutkan daftar dengan rumus dengan cepat?
Metode 1. Data numerik
Jika daftar hanya berisi informasi numerik, maka pengurutan dapat dilakukan dengan mudah menggunakan fungsi PALING SEDIKIT (KECIL) и GARIS (BARIS):
fungsi PALING SEDIKIT (KECIL) menarik keluar dari array (kolom A) elemen terkecil ke-n berturut-turut. Itu. KECIL(A:A;1) adalah angka terkecil dalam kolom, KECIL(A:A;2) adalah angka terkecil kedua, dan seterusnya.
fungsi GARIS (BARIS) mengembalikan nomor baris untuk sel yang ditentukan, yaitu ROW(A1)=1, ROW(A2)=2 dll. Dalam hal ini, digunakan hanya sebagai generator dari urutan angka n=1,2,3… untuk daftar kami yang diurutkan. Dengan keberhasilan yang sama, dimungkinkan untuk membuat kolom tambahan, mengisinya secara manual dengan urutan numerik 1,2,3 … dan merujuknya alih-alih fungsi ROW.
Metode 2. Daftar teks dan rumus biasa
Jika daftar tidak berisi angka, tetapi teks, maka fungsi KECIL tidak akan berfungsi lagi, jadi Anda harus menempuh jalur yang berbeda, sedikit lebih panjang.
Pertama, mari tambahkan kolom layanan dengan rumus di mana nomor seri setiap nama di daftar yang diurutkan di masa mendatang akan dihitung menggunakan fungsi COUNTIF (HITUNGAN):
Dalam versi bahasa Inggris itu akan menjadi:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Istilah pertama adalah fungsi untuk menghitung jumlah sel yang kurang dari yang sekarang. Yang kedua adalah jaring pengaman jika ada nama yang muncul lebih dari satu kali. Maka mereka tidak akan memiliki jumlah yang sama, tetapi secara berurutan meningkat.
Sekarang nomor yang diterima harus diatur secara berurutan dalam urutan menaik. Untuk ini, Anda dapat menggunakan fungsi PALING SEDIKIT (KECIL) dari cara pertama:
Nah, akhirnya, tinggal mengeluarkan nama-nama dari daftar dengan nomor mereka. Untuk melakukan ini, Anda dapat menggunakan rumus berikut:
fungsi LEBIH TERBUKTI (COCOK) mencari di kolom B untuk nomor seri yang diinginkan (1, 2, 3, dll.) dan, pada kenyataannya, mengembalikan nomor baris tempat nomor ini berada. Fungsi INDEKS (INDEKS) menarik keluar dari kolom A nama di nomor baris ini.
Metode 3: Rumus Array
Metode ini sebenarnya adalah algoritma penempatan yang sama seperti pada Metode-2, tetapi diimplementasikan oleh rumus array. Untuk menyederhanakan rumus, rentang sel C1:C10 diberi nama Daftar (pilih sel, tekan Ctrl + F3 dan tombol membuat):
Di sel E1, salin rumus kami:
=INDEX(Daftar; MATCH(KECIL(COUNTIF(Daftar; “<"&Daftar); ROW(1:1)); COUNTIF(Daftar; "<"&Daftar); 0))
Atau dalam versi bahasa Inggris:
=INDEX(Daftar, MATCH(KECIL(COUNTIF(Daftar, «<"&Daftar), ROW(1:1)), COUNTIF(Daftar, "<"&Daftar), 0))
dan dorong Ctrl + Shift + Enteruntuk memasukkannya sebagai rumus array. Kemudian rumus yang dihasilkan dapat disalin ke seluruh panjang daftar.
Jika Anda ingin rumus memperhitungkan bukan rentang tetap, tetapi dapat menyesuaikan saat menambahkan elemen baru ke daftar, maka Anda perlu sedikit mengubah strategi.
Pertama, rentang Daftar perlu diatur secara dinamis. Untuk melakukan ini, saat membuat, Anda perlu menentukan bukan rentang tetap C3:C10, tetapi formula khusus yang akan merujuk ke semua nilai yang tersedia, berapa pun jumlahnya. Klik Alt + F3 atau buka tab Rumus – Manajer Nama (Rumus — Manajer Nama), buat nama baru dan di bidang Link (Referensi) masukkan rumus berikut (saya berasumsi bahwa rentang data yang akan diurutkan dimulai dari sel C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Kedua, rumus array di atas perlu diregangkan dengan margin – dengan harapan data tambahan dimasukkan di masa mendatang. Dalam hal ini, rumus array akan mulai memberikan kesalahan #NUMBER pada sel yang belum terisi. Untuk mencegatnya, Anda dapat menggunakan fungsi KESALAHAN, yang perlu ditambahkan "sekitar" rumus array kita:
=KESALAHAN(INDEX(Daftar; MATCH(KECIL(COUNTIF(Daftar; “<"&Daftar); ROW(1:1)); COUNTIF(Daftar; "<"&Daftar); 0));»»)
=IFERROR(NDEX(Daftar, MATCH(KECIL(COUNTIF(Daftar, «<"&Daftar), ROW(1:1)), COUNTIF(Daftar, "<"&Daftar), 0));"")
Ini menangkap kesalahan #NUMBER dan mengeluarkan void (tanda kutip kosong).
:
- Urutkan rentang berdasarkan warna
- Apa itu rumus array dan mengapa dibutuhkan
- SORT sortir dan larik dinamis di Office 365 baru