Mundarija
Agar siz ro'yxatni saralashingiz kerak bo'lsa, sizning xizmatingizda ko'plab usullar mavjud, ularning eng osoni yorliq yoki menyudagi tartiblash tugmalari. ma'lumotlar (Ma'lumotlar - Saralash). Biroq, ro'yxatni tartiblash avtomatik ravishda, ya'ni formulalar bilan amalga oshirilishi kerak bo'lgan holatlar mavjud. Bu, masalan, ochiladigan ro'yxat uchun ma'lumotlarni yaratishda, diagrammalar uchun ma'lumotlarni hisoblashda va hokazolarda talab qilinishi mumkin. Ro'yxatni tezda formula bilan qanday saralash mumkin?
1-usul. Raqamli ma'lumotlar
Agar ro'yxatda faqat raqamli ma'lumotlar mavjud bo'lsa, unda tartiblash funktsiyalar yordamida osongina amalga oshirilishi mumkin Eng kam (KICHIK) и LINE (QATR):
vazifa Eng kam (KICHIK) massivdan (A ustuni) qatordagi n-chi eng kichik elementni chiqaradi. Bular. KICHIK(A:A;1) - ustundagi eng kichik son, KICHIK(A:A;2) - ikkinchi eng kichik va hokazo.
vazifa LINE (QATR) belgilangan katak uchun qator raqamini qaytaradi, ya'ni ROW(A1)=1, ROW(A2)=2 va hokazo. bizning tartiblangan ro'yxatimiz. Xuddi shu muvaffaqiyat bilan qo'shimcha ustun yaratish, uni qo'lda 1,2,3 raqamli ketma-ketlik bilan to'ldirish va ROW funktsiyasi o'rniga unga murojaat qilish mumkin edi.
Usul 2. Matn ro'yxati va oddiy formulalar
Agar ro'yxatda raqamlar emas, balki matn bo'lsa, u holda SMALL funksiyasi endi ishlamaydi, shuning uchun siz boshqa, biroz uzunroq yo'lni bosib o'tishingiz kerak.
Birinchidan, kelajakda tartiblangan ro'yxatdagi har bir nomning seriya raqami funktsiya yordamida hisoblab chiqiladigan formulali xizmat ustunini qo'shamiz. COUNTIF (COUNTIF):
Inglizcha versiyada u quyidagicha bo'ladi:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Birinchi atama joriydan kamroq bo'lgan hujayralar sonini hisoblash funktsiyasidir. Ikkinchisi, agar biron bir nom bir necha marta sodir bo'lsa, xavfsizlik tarmog'i. Keyin ular bir xil bo'lmaydi, lekin ketma-ket ortib borayotgan sonlarga ega bo'ladi.
Endi olingan raqamlar o'sish tartibida ketma-ket joylashtirilishi kerak. Buning uchun funksiyadan foydalanishingiz mumkin Eng kam (KICHIK) birinchi usuldan:
Va nihoyat, ro'yxatdagi nomlarni raqamlari bo'yicha chiqarib tashlash kifoya. Buning uchun siz quyidagi formuladan foydalanishingiz mumkin:
vazifa KO'PROQ (MATCH) B ustunida kerakli seriya raqamini (1, 2, 3, va hokazo) qidiradi va aslida bu raqam joylashgan qatorning raqamini qaytaradi. Funktsiya INDEKS (INDEKS) A ustunidan ushbu qator raqamidagi nomni chiqaradi.
3-usul: massiv formulasi
Bu usul, aslida, 2-usuldagi kabi joylashtirish algoritmi, lekin massiv formulasi bilan amalga oshiriladi. Formulani soddalashtirish uchun C1:C10 katakchalar diapazoni nomi berildi ro'yxat (hujayralarni tanlang, bosing Ctrl + F3 va tugma yaratish):
E1 katakka formulamizdan nusxa oling:
=INDEX(Roʻyxat; MATCH(KICHIK(COUNTIF(Roʻyxat; “<"&Roʻyxat; QAT(1:1)); COUNTIF(Roʻyxat; "<"&Roʻyxat; 0))
Yoki inglizcha versiyada:
=INDEX(Roʻyxat, MATCH(KICHIK(COUNTIF(Roʻyxat, «<"&Roʻyxat), QAT(1:1)), COUNTIF(Roʻyxat, "<"&Roʻyxat), 0))
va surish Ctrl + Shift + Enter tugmalarini bosingmassiv formulasi sifatida kiritish uchun. Keyin olingan formula ro'yxatning butun uzunligi bo'ylab ko'chirilishi mumkin.
Agar siz formulani qat'iy emas, balki ro'yxatga yangi elementlar qo'shganda sozlashni xohlasangiz, strategiyani biroz o'zgartirishingiz kerak bo'ladi.
Birinchidan, Ro'yxat diapazoni dinamik ravishda o'rnatilishi kerak. Buni amalga oshirish uchun yaratishda siz C3: C10 sobit diapazonini emas, balki ularning sonidan qat'i nazar, barcha mavjud qiymatlarga murojaat qiladigan maxsus formulani ko'rsatishingiz kerak. bosing Alt + F3 yoki yorliqni oching Formulalar - ism menejeri (Formulalar - ism menejeri), yangi nom yaratish va maydonda aloqa (Malumot) quyidagi formulani kiriting (menimcha, saralanadigan ma'lumotlar diapazoni C1 katakchadan boshlanadi):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Ikkinchidan, yuqoridagi massiv formulasi kelajakda kiritiladigan qo'shimcha ma'lumotlarni kutish bilan chegara bilan qisqartirilishi kerak. Bunday holda, massiv formulasi hali to'ldirilmagan kataklarda # NUMBER xatosini berishni boshlaydi. Uni ushlab qolish uchun siz funktsiyadan foydalanishingiz mumkin XATO, bu bizning massiv formulamizga "atrofiga" qo'shilishi kerak:
=XATOLIK(INDEX(Roʻyxat; MATCH(KICHIK(COUNTIF(Roʻyxat; “<"&Roʻyxat; QAT(1:1)); COUNTIF(Roʻyxat; "<"&Roʻyxat; 0));»»)
=IFERROR(NDEX(Roʻyxat, MATCH(KICHIK(COUNTIF(Roʻyxat, «<"&Roʻyxat), QAT(1:1)), COUNTIF(Roʻyxat, "<"&Roʻyxat), 0));"")
U #NUMBER xatosini ushlaydi va uning o'rniga bekor (bo'sh tirnoq) chiqaradi.
:
- Rang bo'yicha diapazonni tartiblang
- Massiv formulalari nima va ular nima uchun kerak
- Yangi Office 365-da saralash va dinamik massivlarni SORT