Contents [show]
Jika anda perlu mengisih senarai, maka terdapat banyak cara untuk perkhidmatan anda, yang paling mudah ialah butang isih pada tab atau dalam menu Tarikh (Data — Isih). Walau bagaimanapun, terdapat situasi apabila pengisihan senarai perlu dilakukan secara automatik, iaitu formula. Ini mungkin diperlukan, sebagai contoh, apabila menjana data untuk senarai juntai bawah, semasa mengira data untuk carta, dsb. Bagaimana untuk mengisih senarai dengan formula dengan cepat?
Kaedah 1. Data berangka
Jika senarai mengandungi hanya maklumat berangka, maka menyusunnya boleh dilakukan dengan mudah menggunakan fungsi KELEBIHAN (KECIL) и LINE (ROW):
fungsi KELEBIHAN (KECIL) menarik keluar dari tatasusunan (lajur A) unsur terkecil ke-n dalam satu baris. Itu. SMALL(A:A;1) ialah nombor terkecil dalam lajur, SMALL(A:A;2) ialah nombor kedua terkecil, dan seterusnya.
fungsi LINE (ROW) mengembalikan nombor baris untuk sel yang ditentukan, iaitu ROW(A1)=1, ROW(A2)=2 dll. Dalam kes ini, ia digunakan hanya sebagai penjana jujukan nombor n=1,2,3… untuk senarai disusun kami. Dengan kejayaan yang sama, adalah mungkin untuk membuat lajur tambahan, mengisinya secara manual dengan urutan berangka 1,2,3 … dan merujuknya dan bukannya fungsi ROW.
Kaedah 2. Senarai teks dan formula biasa
Jika senarai mengandungi bukan nombor, tetapi teks, maka fungsi KECIL tidak akan berfungsi lagi, jadi anda perlu pergi ke laluan yang berbeza, lebih panjang sedikit.
Mula-mula, mari tambah lajur perkhidmatan dengan formula di mana nombor siri setiap nama dalam senarai diisih masa hadapan akan dikira menggunakan fungsi COUNTIF (COUNTIF):
Dalam versi bahasa Inggeris ia akan menjadi:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Sebutan pertama ialah fungsi untuk mengira bilangan sel yang kurang daripada sel semasa. Yang kedua ialah jaring keselamatan sekiranya sebarang nama muncul lebih daripada sekali. Kemudian mereka tidak akan mempunyai yang sama, tetapi berturut-turut meningkatkan bilangan.
Sekarang nombor yang diterima mesti disusun secara berurutan dalam tertib menaik. Untuk ini anda boleh menggunakan fungsi KELEBIHAN (KECIL) dari cara pertama:
Akhirnya, ia kekal hanya untuk mengeluarkan nama dari senarai dengan nombor mereka. Untuk melakukan ini, anda boleh menggunakan formula berikut:
fungsi LEBIH TERDEDAH (PERLAWANAN) mencari dalam lajur B untuk nombor siri yang dikehendaki (1, 2, 3, dsb.) dan, sebenarnya, mengembalikan nombor baris di mana nombor ini berada. Fungsi INDEKS (INDEX) tarik keluar dari lajur A nama pada nombor baris ini.
Kaedah 3: Formula Tatasusunan
Kaedah ini, sebenarnya, algoritma peletakan yang sama seperti dalam Kaedah-2, tetapi dilaksanakan oleh formula tatasusunan. Untuk memudahkan formula, julat sel C1:C10 telah diberi nama senarai (pilih sel, tekan Ctrl + F3 dan butang Buat):
Dalam sel E1, salin formula kami:
=INDEX(Senarai; PADANAN(KECIL(COUNTIF(Senarai; “<"&Senarai); ROW(1:1)); COUNTIF(Senarai; "<"&Senarai); 0))
Atau dalam versi bahasa Inggeris:
=INDEX(Senarai, PADANAN(KECIL(COUNTIF(Senarai, «<"&Senarai), ROW(1:1)), COUNTIF(Senarai, "<"&Senarai), 0))
dan tolak Ctrl + Shift + Enteruntuk memasukkannya sebagai formula tatasusunan. Kemudian formula yang terhasil boleh disalin ke seluruh panjang senarai.
Jika anda mahu formula mengambil kira bukan julat tetap, tetapi boleh melaraskan apabila menambah elemen baharu pada senarai, maka anda perlu mengubah sedikit strategi.
Pertama, julat Senarai perlu ditetapkan secara dinamik. Untuk melakukan ini, semasa membuat, anda perlu menentukan bukan julat tetap C3:C10, tetapi formula khas yang akan merujuk kepada semua nilai yang tersedia, tanpa mengira bilangannya. klik Alt + F3 atau buka tab Formula – Pengurus Nama (Formula — Pengurus Nama), cipta nama baharu dan dalam medan pautan (Rujukan) masukkan formula berikut (saya menganggap bahawa julat data yang hendak diisih bermula dari sel C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Kedua, formula tatasusunan di atas perlu diregangkan dengan margin – dengan jangkaan data tambahan dimasukkan pada masa hadapan. Dalam kes ini, formula tatasusunan akan mula memberikan ralat #NUMBER pada sel yang belum diisi. Untuk memintasnya, anda boleh menggunakan fungsi tersebut IFERROR, yang perlu ditambah "sekitar" formula tatasusunan kami:
=IFERROR(INDEX(Senarai; PADANAN(KECIL(COUNTIF(Senarai; “<"&Senarai); ROW(1:1)); COUNTIF(Senarai; "<"&Senarai); 0));»»)
=IFERROR(NDEX(Senarai, PADANAN(KECIL(COUNTIF(Senarai, «<"&Senarai), ROW(1:1)), COUNTIF(Senarai, "<"&Senarai), 0));"")
Ia menangkap ralat #NUMBER dan menghasilkan kekosongan (petikan kosong) sebaliknya.
:
- Isih julat mengikut warna
- Apakah formula tatasusunan dan mengapa ia diperlukan
- Isih menyusun dan tatasusunan dinamik dalam Office 365 baharu