2 ta ustundan iborat jadvalni formulalar yordamida tartiblaymiz. Jadval ustunlaridan biri bo'yicha saralaymiz (2 ta masalani hal qilamiz: jadvalni son bo'yicha tartiblash va matn ustuni bo'yicha tartiblash). Saralash formulalarini shunday o'rnatamizki, manba jadvaliga yangi ma'lumotlar qo'shilganda tartiblangan jadval dinamik ravishda o'zgaradi. Bu foydalanuvchi aralashuvisiz har doim tartiblangan jadvalga ega bo'lish imkonini beradi. Shuningdek, biz ikki darajali saralashni amalga oshiramiz: birinchi navbatda raqamli, keyin (takroriy raqamlar uchun) matn ustuni bo'yicha.

2 ta ustundan iborat jadval bo'lsin. Bitta ustun matn: Mevalar ro'yxati; ikkinchisi esa raqamli Sotish hajmi(masalan, faylga qarang).

1-muammo (jadvalni raqamlar ustuni bo'yicha saralash)

Jadval satrlarini raqamli ustunning mazmuni bo'yicha (Sotuv hajmi bo'yicha) tartiblashingiz kerak. Foydalanuvchi doimiy ravishda jadval satrlarini to'ldiradi deb taxmin qilinadi, shuning uchun qo'shilgan qiymatlarni hisobga olgan holda formulalarni yozish kerak.

Aniqlik uchun ustundagi qiymatlar Sotish hajmi() bilan ta'kidlangan. Takroriy qiymatlar ham sariq rangda ta'kidlangan.

Eslatma: Bir ustunni (ro'yxatni) saralash muammosi maqolalarda hal qilindi va.

Yechim 1

Agar raqamli ustunda hech qanday qiymat yo'qligi kafolatlangan bo'lsa, muammoni hal qilish oson:

• Raqamli ustunni LARGE() funksiyasidan foydalanib tartiblang (maqolaga qarang);
• VLOOKUP() funksiyasi yoki bir qator funksiyalar INDEX()+MATCH() matn ustunidan qiymatlarni tegishli raqamli qiymati bo'yicha tanlang.

Biroq, haqiqiy masalalarda sonli ustun takrorlashlarni o'z ichiga olishi mumkin va VLOOKUP() funksiyasi takrorlanganda har doim yuqoridan faqat birinchi qiymatni tanlaganligi sababli (maqolaga qarang), bu yondashuv mos kelmaydi (nomlari Mevalar noto'g'ri ko'rsatiladi).

Shuning uchun saralash mexanizmi boshqacha tarzda amalga oshirilishi kerak bo'ladi.

INDEX(Sotish;
ROUND(REMAT(eng katta()
--(COUNTIF(Sotuv;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
ROW()-QATR($E$6));1)*1000;0)
)

Ushbu formula ustunni tartiblaydi Sotish hajmi(dinamik diapazon Sotish) pasayish. Manba jadvalidagi bo'shliqlarga yo'l qo'yilmaydi. Manba jadvalidagi qatorlar soni 1000 dan kam bo'lishi kerak.

Keling, formulani batafsil ko'rib chiqaylik:

• Formula COUNTIF(Sotish;"<"&Продажи) massivni qaytaradi (4:5:0:2:7:1:3:5). Bu 64 raqami (hujayradan B7 asl jadval, ya'ni. diapazondagi birinchi raqam Sotish) bir xil diapazondan 4 dan ortiq qiymat; 74 raqami (hujayradan B8 asl jadval, ya'ni. diapazondan ikkinchi raqam Sotish) bir xil diapazondan 5 dan ortiq qiymat; keyingi raqam 23 eng kichik (u hech kimdan katta emas) va hokazo.
• Endi yuqoridagi butun sonlar massivini kasr qismi bo‘lgan sonlar massiviga aylantiramiz, bunda kasr qismi massivdagi sonning joylashuv raqamini o‘z ichiga oladi: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,08 ). Bu ifoda orqali amalga oshiriladi &","&REPEAT("0";3-UZUNLIK(QATIR(Sotuv)-QATR($E$6)))&QATR(Sotuv)-QATR($E$6)) Formulaning ushbu qismida manba jadvalida 1000 dan ortiq qator cheklovi mavjud (yuqoriga qarang). Agar so'ralsa, uni osongina o'zgartirish mumkin, ammo bu ma'nosiz (quyida hisoblash tezligi bo'limiga qarang).
• LARGE() funksiyasi yuqoridagi massivni tartiblaydi.
• ROD() funksiyasi 0,005 kabi pozitsiya raqamlari/1000 bo'lgan sonning kasr qismini qaytaradi.
• ROUND() funktsiyasi 1000 ga ko'paytirilgandan so'ng butun songa yaxlitlanadi va pozitsiya raqamini qaytaradi. Endi barcha pozitsiya raqamlari ustun raqamlariga mos keladi Sotish hajmi, kamayish tartibida tartiblangan.
• INDEX() funktsiyasi, pozitsiya raqami berilgan, unga mos keladigan raqamni qaytaradi.

Xuddi shunday formulani qiymatlarni ustunda ko'rsatish uchun yozish mumkin Mevalar=INDEKS(Meva,DUMALAMA(...))

Misol faylida, hisoblash tezligini hisobga olgan holda (pastga qarang), formulaning bir xil turdagi qismi, ya'ni. ROUND() funksiyasi ichidagi hamma narsa alohida ustunga joylashtirilgan J . Shunday qilib, tartiblangan jadvaldagi yakuniy formulalar quyidagicha ko'rinadi: =INDEX(Meva,J7) Va =INDEX (Sotuvlar, J7)

Shuningdek, massiv formulasida LARGE() funksiyasini KICHIK() ga o‘zgartirib, biz o‘sish tartibida tartiblashni olamiz.

Aniqlik uchun ustundagi qiymatlar Sotish hajmi bilan ta'kidlangan ( Bosh sahifa/ Uslublar/ Shartli formatlash/ Gistogrammalar). Ko'rib turganingizdek, saralash ishlaydi.

Sinov

Endi asl jadvalga yangi qator qo'shamiz. Dinamik tartiblangan jadvallarda biz tegishli tartibni olishimiz kerak.

1. Hujayraga A15 manba jadvaliga so'zni kiriting Sabzi;
2. Hujayraga B15 kiriting Sotish hajmi Sabzi = 25;
3. Ustunlardagi qiymatlarni kiritgandan so'ng D Va E avtomatik ravishda kamayish tartibida tartiblangan jadval ko'rsatiladi;
4. Saralangan jadvalda yangi qator oxirgidan ikkinchisiga ko'rsatiladi.

Formulani hisoblash tezligi

Ishlash bo'yicha "o'rtacha" kompyuterda, bunday juftlikni qayta hisoblash massiv formulalari, 100 qatorda joylashgan, amalda ko'rinmas. 300 qatorli jadvallar uchun qayta hisoblash vaqti 2-3 soniya davom etadi, bu noqulay. Yoki siz varaqni avtomatik qayta hisoblashni o'chirib qo'yishingiz kerak ( Formulalar/Hisob-kitoblar/Hisoblash imkoniyatlari) va tugmani vaqti-vaqti bilan bosing F9, yoki massiv formulalaridan foydalanishdan voz keching, ularni mos formulalar bilan ustunlar bilan almashtiring yoki standart yondashuvlardan foydalanish foydasiga dinamik tartiblashdan butunlay voz keching (keyingi bo'limga qarang).

Jadvallarni saralashning muqobil yondashuvlari

Keling, standart filtr yordamida manba jadvalining qatorlarini tartiblaymiz (manba jadvalining sarlavhalarini tanlang va tugmani bosing. CTRL+SHIFT+L). Ochiladigan ro'yxatdan kerakli saralashni tanlang.

Jadvalning biznikiga o'xshash versiyasini olamiz, lekin jadvalga yangi qiymatlarni qo'shganda filtrni yana qo'llashimiz kerak bo'ladi.

Saralash vositasidan ham foydalanishingiz mumkin ( Ma'lumotlar/Sort va Filtr/Sart). Buni amalga oshirish uchun siz sarlavhani hisobga olmaganda, manba jadvalining barcha qiymatlarini tanlashingiz kerak, Saralash vositasiga qo'ng'iroq qiling, saralashni xohlagan ustunni va saralash opsiyasini tanlang.

Jadvalning biznikiga o'xshash versiyasini olamiz, lekin yangi qiymatlarni qo'shganda filtrni yana qo'llashimiz kerak bo'ladi.

Oldingi masalada bo'lgani kabi, saralash amalga oshirilayotgan ustunda takrorlashlar bor deb faraz qilaylik (mevalarning nomlari takrorlanadi).

Jadvalni saralash uchun siz 2 ta xizmat ustunini (D va E) yaratishingiz kerak bo'ladi.

=COUNTIF($B$7:$B$14;"<"&$B$7:$B$14)+1

Ushbu formula matn qiymatlariga o'xshash (ro'yxatdagi boshqa qiymatlarga nisbatan qiymatning o'rni). Alifboda pastroq bo'lgan matn qiymati yuqoriroq "darajali" ga ega. Misol uchun, olma qiymati maksimal 7 (takrorlashlar bilan birga) maksimal "darajali" ga to'g'ri keladi.

E ustuniga odatdagi formulani kiriting:

=COUNTIF($D$6:D6,D7)+D7

Ushbu formula matn qiymatlarining takrorlanishini hisobga oladi va "darajali" ni moslashtiradi. Endi olmalarning turli qiymatlari turli "darajalar" ga to'g'ri keladi - 7 va 8. Bu tartiblangan qiymatlar ro'yxatini ko'rsatishga imkon beradi. Buning uchun formuladan foydalaning (G ustuni):

=INDEX($B$7:$B$14, MATCH(QATIR()-QATR($G$6),$E$7:$E$14,0))

Shunga o'xshash formulada tegishli savdo hajmi ko'rsatiladi (H ustuni).

Muammo 2.1 (Ikki darajali saralash)

Endi asl jadvalni yana savdo hajmi bo'yicha saralaymiz. Ammo endi qiymatlarni takrorlash uchun (A ustunida uchta qiymat mavjud 74), biz tegishli qiymatlarni alifbo tartibida ko'rsatamiz.

Buning uchun 1.1-masala va 2-masala natijalaridan foydalanamiz.

Tafsilotlar Vazifa 2 varaqidagi misol faylida.

Siz roʻyxatni alifbo, son yoki xronologik tartibda uchta maydon boʻyicha saralashingiz mumkin. Har bir ustun uchun o'sish va kamayish bo'yicha tartiblash tartibini belgilashingiz mumkin.

O'sish bo'yicha tartiblash:

Raqamlar eng kichik manfiydan katta musbat songacha tartiblanadi.

Sana va vaqt qiymatlari eng eski qiymatdan oxirgi qiymatgacha saralanadi.

Matn alifbo tartibida tartiblanadi. Bunday holda, birinchi navbatda matn sifatida ko'rsatilgan raqamli qiymatlar joylashadi.

Mantiqiy qiymatlarni o'sish tartibida tartiblashda birinchi navbatda FALSE qiymati, keyin esa TRUE qiymati ko'rsatiladi.

Xato qiymatlari ular duch kelgan tartibda saralanadi (tartiblash nuqtai nazaridan ularning barchasi teng).

Saralangan ro'yxat oxirida bo'sh katakchalar paydo bo'ladi.

Kamayish bo'yicha tartiblash:

Kamayuvchi tartiblash bo'lsa, Excel yozuvlarni yuqorida tavsiflangan teskari tartibda tartiblaydi. Ro'yxat oxirida faqat bo'sh hujayralar ko'rsatiladi.

Maxsus tartiblash tartibi:

O'sish va kamayish bo'yicha tartiblash o'rniga, belgilangan maxsus ro'yxat bilan aniqlangan maxsus tartiblash tartibidan ham foydalanishingiz mumkin.

Ro'yxatni saralash

Ro‘yxatni saralash uchun katak ko‘rsatkichini ro‘yxat ichiga qo‘ying va Ma’lumotlar – Saralash buyrug‘ini bajaring.

Excel avtomatik ravishda ro'yxatni tanlaydi va saralash opsiyalarini belgilashingiz mumkin bo'lgan Saralash diapazoni dialog oynasini ko'rsatadi.

Siz uchta ro'yxat maydoni bo'yicha saralashni o'rnatishingiz va har bir maydon uchun boshqa tartiblash tartibini belgilashingiz mumkin.

Agar nomlarni o'z ichiga olgan katakchalar formati ma'lumotlarni o'z ichiga olgan hujayralar formatidan farq qilsa, Excel maydon nomlarini avtomatik ravishda taniydi.

Rangni saralash dialog oynasi.

Agar dastur tomonidan amalga oshirilgan diapazonni tanlash to'liq to'g'ri bo'lmasa, dialog oynasining pastki qismidagi kalitni kerakli joyga o'rnating (maydonlarni "yorliqlar (diapazonning birinchi qatori)" yoki "varaq ustunlari belgilari" bo'yicha aniqlang).

Saralash muloqot oynasida ko'rsatgan diapazon va tartiblash opsiyalari saqlanadi va keyingi safar ochganingizda dialog oynasida ko'rsatiladi.

Shuningdek, qatorlar o'rniga diapazon ustunlarini saralashni tanlashingiz mumkin. Buni amalga oshirish uchun "Sartiblash diapazoni" dialog oynasidagi "Tanlovlar" tugmasini bosing va "Sartiblash parametrlari" muloqot oynasidagi "Tartiblash" guruhida o'tishni "Ustun diapazoni" holatiga o'rnating.

26. Excelda ma'lumotlarni filtrlash.

Ro'yxatdagi ma'lumotlarni filtrlash faqat belgilangan shartlarga javob beradigan yozuvlarni ko'rsatishga imkon beradi.

Avtofiltr

Avtofiltrni qo'llashda maydon nomlari ochiladigan ro'yxatlar bilan to'ldiriladi, ulardan siz kerakli maydon qiymatini tanlashingiz yoki maxsus mezon o'rnatishingiz mumkin.

Avtofiltrni kiritish

1. Yacheyka ko'rsatkichini ro'yxat ichiga joylashtiring.

2. Ma'lumotlar - Filtr pastki menyusida "Avtofiltr" buyrug'ini tanlang. Ro'yxatni ochish uchun bosishingiz mumkin bo'lgan maydon nomlari yonida o'q tugmalari paydo bo'ladi.

3. Qiymatini filtr sifatida ishlatmoqchi bo'lgan maydon ro'yxatini oching (tanlash mezoni). Ro'yxat tanlangan maydonning katakchalarining qiymatlarini ko'rsatadi.

4. Ro'yxatdan kerakli elementni tanlang. Ekranda faqat belgilangan filtrga mos keladigan yozuvlar ko'rsatiladi.

5. Agar kerak bo'lsa, boshqa maydon ro'yxatidan kerakli elementni tanlang. Ekranda faqat barcha belgilangan filtrlash shartlariga mos keladigan yozuvlar ko'rsatiladi (alohida maydonlar shartlari "VA" mantiqiy operatsiyasi yordamida birlashtiriladi).

Filtrlash shartlari ko'rsatilgan maydonlar uchun avtofiltrlarni ochish tugmalari ko'k rangga ega.

Avtomatik filtrlash buyrug'ini bajarishdan oldin bir yoki bir nechta ustunlarni tanlagan bo'lsangiz, ochiladigan ro'yxatlar faqat tegishli maydonlarga qo'shiladi.

Ro‘yxatdagi barcha yozuvlarni ekranda yana ko‘rsatish uchun Ma’lumotlar – Filtr pastki menyusidan “Hammasini ko‘rsatish” buyrug‘ini bajaring.

Ushbu maydon uchun avtofiltr ro'yxatidagi "Hammasi" bandini tanlab, alohida maydon uchun filtrlash mezonini olib tashlashingiz mumkin.

Avtofiltr funksiyasini o‘chirish (ochiladigan ro‘yxatlarni o‘chirish) uchun yana Ma’lumotlar – Filtr – Avtofiltr buyrug‘ini tanlang.

Maxsus avtofiltrni qo'llash

Maxsus avtofiltrdan foydalanib, siz mantiqiy operatorlar yordamida alohida yozuvlarni tanlash shartlarini ulashingiz mumkin.

Ma’lumotlar – Filtr – Avtofiltr buyrug‘ini tanlab, ro‘yxatga avtofiltrni kiriting.

Kerakli maydon uchun avtofiltr ro'yxatini oching va undagi elementni (Shart) tanlang.

Ochilgan "Maxsus avtofiltr" dialog oynasida (6.3.27-rasm) birinchi mezonni belgilang.

Birinchi va ikkinchi mezonlarni birlashtirgan mantiqiy operatorni tanlang.

"Maxsus avtofiltr" dialog oynasi.

Maxsus avtofiltrda alohida maydon uchun bir yoki ikkita mezonni belgilashingiz mumkin. Ikkinchi holda, ular "AND" yoki "OR" mantiqiy operatori bilan birlashtirilishi mumkin.

Ikkinchi mezonni belgilang.

“OK” tugmasini bosing. Excel siz ko'rsatgan mezonlar asosida yozuvlarni filtrlaydi.

Kengaytirilgan filtr

Ro'yxat ma'lumotlarini filtrlash uchun murakkab shartlarni o'rnatish uchun Excel foydalanuvchiga yordam berish uchun kengaytirilgan filtrni taqdim etadi.

Mezon diapazoni

Mezonlarni ish varag'idagi istalgan bo'sh joyga o'rnatish mumkin. Mezonlar oralig'ida siz ikki turdagi mezonlarni kiritishingiz va birlashtirishingiz mumkin:

Oddiy mezonlar: dastur maydonlar tarkibini belgilangan mezon bilan solishtiradi (avtofiltrdan foydalanishga o'xshash).

Hisoblangan mezonlar: Bu holda, ro'yxatda bo'lmagan formula bo'yicha qaytarilgan qiymatlarni ham belgilashingiz mumkin.

Oddiy mezonlarni belgilashda, avvalo, mezonlar oralig'ida maydon nomlarini ko'rsatishingiz kerak va siz faqat mezon ko'rsatayotgan maydon nomlarini kiritishingiz mumkin.

Maydon nomlari qatori ostidagi satr(lar) siz mezonlarni belgilagan joydir. Mezonlarni belgilashda Excel katta-kichik harflarga sezgir emas. Mezonlarni belgilashda joker belgilardan foydalana olasizmi? Va *.

Bitta satrda ko'rsatilgan barcha mezonlar bir vaqtning o'zida bajarilishi kerak ("VA" mantiqiy operatoriga mos keladi). "OR" operatori yordamida mezonlarning ulanishini belgilash uchun turli qatorlarda mezonlarni belgilang.

Kengaytirilgan filtrni qo'llash

Mezonlar oralig'ini yaratganingizdan so'ng, kengaytirilgan filtrni ishga tushirishingiz va ro'yxat ma'lumotlarini filtrlashingiz mumkin.

1. Yacheyka ko'rsatkichini ro'yxat ichiga joylashtiring. Bunday holda, Excel avtomatik ravishda ro'yxat oralig'ini taniydi va dialog oynasida diapazonga havolani taqdim etadi.

2. Ma’lumotlar – Filtr – Kengaytirilgan filtr buyrug‘ini bajaring. Kirish kursorini "Shartlar oralig'i" maydoniga qo'ying va ish varag'idagi tegishli diapazonni belgilang.

3. “OK” tugmasini bosish orqali dialog oynasini yoping. Endi ekranda belgilangan mezonlarga javob beradigan yozuvlar ko'rsatiladi.

Har bir ish varag'iga faqat bitta kengaytirilgan filtrni qo'llashingiz mumkin.

Kengaytirilgan filtrni qo'llash natijasida takroriy yozuvlar ko'rsatilishini istamasangiz, Kengaytirilgan filtr muloqot oynasidagi "Faqat noyob yozuvlar" katagiga belgi qo'ying.

Filtrdan so'ng ishchi varaqdagi barcha ro'yxat yozuvlarini ko'rsatishni o'rnatish uchun Ma'lumotlar - Filtr - Hammasini ko'rsatish buyrug'ini bajaring.

Jadvallarda joylashtirilgan ma'lumotlarni ko'rish va tahlil qilishda foydalanuvchi uchun qulay va qulay sharoitlar yaratish uchun MS Excel dasturi ma'lumotlarni saralash uchun juda keng imkoniyatlarni taqdim etadi. Excelda katta ma'lumotlar bazasidagi yozuvlarni saralash...

Foydalanuvchi uchun qiziqarli ma'lumotlarni topishni biroz osonlashtiradi.

Saralashda barcha ma'lumotlar bazasi qatorlari o'z joylashuvini biz tanlagan algoritmga muvofiq o'zgartiradi, shu bilan birga, albatta, o'zgarishsiz qoladi.

Excelda uchta asosiy saralash algoritmlari.

1. raqamli qiymatlar "eng kichikdan kattagacha" tamoyili bo'yicha saralanadi yoki aksincha.

2. Hujayralarni o'z ichiga olgan ustunlar matn qiymatlar alifbo tartibida oldinga yoki teskari yo'nalishda tartiblanadi.

3. Hujayralarni o'z ichiga olgan ustunlar sanalar, "eng qadimgidan eng yangigacha" yoki aksincha tamoyili bo'yicha saralanadi.

Excel 2007 va undan keyingi versiyalarida saralash qo'shimcha ravishda formatlash mezonlari bo'yicha amalga oshirilishi mumkin - hujayra to'ldirish rangi bo'yicha, hujayra shrifti rangi bo'yicha, shartli formatlash piktogramma to'plami.

Maqolada yaratilgan DB2 ma'lumotlar bazasi bilan ishlashni davom ettiramiz "Metal konstruksiyalarni №2 sayt bo'yicha ishlab chiqarish". ».

Ko'rib chiqilayotgan o'quv ma'lumotlar bazasi faqat 6 ta maydon (ustun) va 10 ta yozuvdan (qatordan) iborat. Haqiqiy ma'lumotlar bazalari odatda o'ndan ortiq maydonlarni va minglab yozuvlarni o'z ichiga oladi! Bunday jadvalda kerakli ma'lumotlarni topish oson emas! Biz keyingi harakatlarimizga ana shunday tushunish prizmasi orqali qarashimiz kerak.

Keyingi ishlar quyidagicha tuziladi: ushbu va ushbu seriyaning keyingi maqolalarida biz dasturning turli imkoniyatlaridan foydalanamiz. XONIM Excel , ishlab chiqarish uchastkasi rahbarining amaliy faoliyatida yuzaga keladigan turli savollar va vazifalarga tezkor javoblarni shakllantirish. Ya'ni, biz haqiqiy ishlab chiqarish muammolarini hal qilamiz!

Ma'lumotlar bazasini saqlash - ma'lumot to'plash - o'z-o'zidan hech narsa bermaydi, aksincha, bu bizga biroz vaqt talab etadi! Ammo ushbu ma'lumotni tahlil qilish natijalari turli xil savollarga aniq javob berishga imkon beradi, haqiqiy hayotda ma'lumotlar bazasi ob'ektlarini boshqarish qobiliyatini sezilarli darajada yaxshilaydi!

Ishlab chiqarishda ma'lumotlar bazalarini to'g'ri qurish va ehtiyotkorlik bilan saqlash, tahlil natijalaridan foydalanish bir xil maydonlar, uskunalar va bir xil tarkibdagi texnik va texnik jihozlardan foydalangan holda ishlab chiqarish hajmini ikki baravar, ba'zan esa uch baravar oshirish imkonini beradi.

Eng oddiy saralash.

Excelda eng oddiy saralash "Standart" asboblar panelida joylashgan "O'sish bo'yicha tartiblash" va "Kamayish bo'yicha tartiblash" tugmalari yordamida amalga oshiriladi. (Quyidagi rasmda bu tugmalar qizil ellips bilan o'ralgan.)

№1 vazifa:

Aniqlang: ishlab chiqarilgan mahsulotlardan qaysi biri eng og'ir va uning massasi qancha? Bu mahsulot qachon ishlab chiqarilgan?

1. MS Excel-da faylni oching.

2. Biz E7 katakchasini bosish orqali faollashtiramiz, "1 dona og'irligi, t" ustun sarlavhasi bilan (siz bizni qiziqtirgan ustundagi istalgan katakni faollashtirishingiz mumkin).

3. "Standart" asboblar panelidagi "Kamayish bo'yicha tartiblash" tugmasini bosing.

4. Biz ma'lumotlar bazasining eng yuqori satrida berilgan savolga javobni o'qiymiz (8-qator). Ma'lumotlar bazasidagi eng og'ir mahsulot 2-sonli buyurtma bo'yicha Beam 045. Beam 045 2014 yil 23 apreldan 25 aprelgacha ishlab chiqarilgan (Excel 8-10-sonli qatorlardagi yozuvlarga qarang).

5. Xuddi shu "Standart" asboblar panelidagi "Bekor qilish" tugmasini bosish orqali ma'lumotlar bazasini Excelda saralashdan oldin (agar kerak bo'lsa) formaga qaytarishingiz mumkin. Yoki ma'lumotlar bazasining Sana ustuniga o'sish tartibini qo'llashingiz mumkin.

Excelda bir nechta ustunlar bo'yicha saralash.

Shu tarzda saralash ikki yoki uchta ustun bo'ylab ketma-ket amalga oshirilishi mumkin.

Vazifa №2:

Buyurtmalar bo'yicha guruhlangan ma'lumotlar bazasi yozuvlarini va alifbo tartibida tartiblangan buyurtmalar ichidagi mahsulot nomlarini taqdim eting.

1. Biz sichqonchani bosish orqali har qanday ma'lumotlar bazasi katakchasini faollashtiramiz (masalan, C11 katak).

2. Asosiy menyuning "Ma'lumotlar" tugmasini bosing va "Sartiblash ..." ni tanlang.

3. Ochilgan "Sorting diapazoni" oynasida chapdagi skrinshotda ko'rsatilganidek, ochiladigan ro'yxatlardan qiymatlarni tanlang va "OK" tugmasini bosing.

4. 2-sonli vazifa bajarildi. Yozuvlar, birinchidan, buyurtma raqamlari bo'yicha tartiblanadi, ikkinchidan, har bir tartib doirasida ular mahsulot nomlari bo'yicha alifbo tartibida joylashtiriladi.

Natijalar.

Seriyaning ushbu ikkinchi maqolasida axborotni saqlash va ma'lumotlarni boshqarish haqida Biz Excelda ma'lumotlar bazasi yozuvlarini saralashning asosiy imkoniyatlarini ko'rib chiqdik. Excelning bu juda oddiy mexanizmi ko'pchiligingizga, aziz o'quvchilarga tanish. Umid qilamanki, men sizning vaqtingizni taniqli xizmat haqidagi oddiy hikoya bilan olmadim. Lekin... kompleksga yo'l oddiydan boshlanadi!

Rostini aytsam, amalda siz kamdan-kam hollarda saralashga murojaat qilishingiz kerak. Nega? Bu savolga javob seriyaning keyingi maqolalarida bo'ladi, ularda ma'lumotlarni tez va samarali qayta ishlash uchun boshqa, shu jumladan yanada rivojlangan va kuchli Excel mexanizmlari haqida gap boradi.

Men yolvoraman hurmatli muallif ishi obuna bo'ling maqola e'lonlari uchun har bir maqolaning oxirida joylashgan oynada yoki sahifaning yuqori qismidagi oynada!

Hurmatli o'quvchilar, sharhlar va sharhlaringizni sharhlarda yozing.

Pivot jadvalning satrlari va ustunlari sohasida joylashgan ma'lumotlarni saralash sukut bo'yicha o'sish tartibida (1a-rasm) yoki maxsus tartiblash ro'yxatlari yordamida amalga oshiriladi. Bu har doim ham foydalanuvchiga mos kelmaydi. Misol uchun, ro'yxatning yuqori qismida eng yuqori daromadga ega bo'lgan mijozlarni ko'rsatishni xohlaganingizda (1b-rasm). Agar sizning pivot jadvalingiz o'sish (kamayish) tartibida tartiblangan bo'lsa, maydon bo'yicha tartiblash tartibini boshqaradigan qoida yaratishingiz kerak. Bundan tashqari, ushbu qoida (ushbu maydon uchun) pivot jadvaliga yangi maydonlar qo'shgandan keyin ham qo'llaniladi (1c-rasm).

Guruch. 1. Maydon bo‘yicha saralash Mijoz: (a) sukut bo'yicha - A dan Z gacha; (b) daromadlarni kamaytirish tartibida; (c) maydon bo'yicha tartiblash Mijoz maydonni qo'shganda o'zgarmadi Sektor

Eslatmani yoki formatda yuklab oling, formatdagi misollar

Mijozlarni daromadning kamayishi tartibida saralash

Umumiy jadval satrlarini daromadning kamayishi tartibida saralash uchun ustundagi istalgan katakchani tanlang. Daromad sohasi bo'yicha miqdor, masalan, E4 (lekin sarlavha emas) va belgini bosing JA tabda joylashgan Ma'lumotlar(2-rasm). Ushbu tartiblash standartga o'xshaydi, ammo bu faqat yuzaki o'xshashlik. Umumiy jadvalni saralaganingizda, Excel umumiy jadvalga qo'shimcha o'zgarishlar kiritganingizdan keyin ishlashni davom ettiradigan qoida yaratadi.

G:I ustunlarida joylashgan pivot jadval misolidan foydalanib (1c-rasm), yangi tashqi qator maydoni qo'shgandan keyin nima bo'lishini ko'rishingiz mumkin. Sektor. Pivot jadval ma'lumotlarni har bir sektor bo'yicha daromadlarning kamayish tartibida saralashni davom ettiradi. Masalan, sektorda Ishlab chiqarish Birinchi o'rinda General Motors 750 163 dollar daromad bilan. Ford 622 794 dollar daromad bilan orqada. Maydonni o'chirib tashlasangiz ham Mijoz pivot jadvalidan qo'shimcha sozlamalarni o'rnating va bu maydonni orqaga qaytaring, lekin ustunlar maydoniga Excel mijozlarni daromadlarni kamaytirish tartibida saralashni eslab qoladi.

Shunday qilib, G: I (1c-rasm) ustunlarida joylashgan jamlama jadvalida tarmoqlar ham daromadning kamayish tartibida tartiblangan, siz uchta yo'ldan biriga o'tishingiz mumkin:

• G4 katakchasini tanlang, o'ng tugmasini bosing va tanlang Hammasini yig'ishmaydon mijozga tegishli barcha elementlarni yashirish. Ekranda faqat bitta sektor ko'rsatilgandan so'ng, I4 katakchani tanlang va yorliqdagi YA belgisini bosing Ma'lumotlar saralashni kamayish tartibida bajarish. Bu maydon uchun tartiblash qoidasini yaratadi Sektor. G4 katakchasini qayta tanlang, o'ng tugmasini bosing va tanlang Hammasini kengaytiringmaydon.
• Maydonni vaqtincha olib tashlang Mijoz pivot jadvalidan jadvalni daromadning kamayishi tartibida tartiblang (2-rasmda tasvirlangan usuldan foydalanib), so'ngra maydonni yana qaytaring. Mijoz.
• Jamoaning imkoniyatlaridan foydalaning Qo'shimchavariantlaritartiblash(Men aynan shu usuldan foydalanaman). Buyruqni chaqirish uchun: (a) G4 katakchani tanlang, o'ng tugmasini bosing va tanlang Tartiblash → Qo'shimchavariantlaritartiblash(3-rasm) yoki (b) maydondagi uchburchak belgisini bosing Sektor, va keyin tanlang Qo'shimchavariantlaritartiblash(4-rasm). Ikkala holatda ham oyna ochiladi Tartiblash(5-rasm). Kalitni holatiga o'rnating tushayotgan va qatorni tanlang Daromad sohasi bo'yicha miqdor.

Guruch. 3. Buyruqni chaqiring Qo'shimchavariantlaritartiblash o'ng tugmasini bosing

Guruch. 4. Buyruqni chaqiring Qo'shimchavariantlaritartiblash menyudan foydalanish Saralash va filtrlar dalalar Sektor

Guruch. 5. Oynada parametrlarni o'rnatish Sektor

Muloqot oynasining pastki chap burchagida Tartiblash Kengaytirilgan tugmasi mavjud... Ushbu tugmani bosgandan so'ng ekranda dialog oynasi paydo bo'ladi . Ushbu oynada siz: (a) birinchi kalit bo'yicha saralash uchun foydalaniladigan maxsus ro'yxatni belgilashingiz mumkin (quyida batafsil ma'lumotga qarang); (b) ustun o'rniga Umumiy jami Asosiy saralash ustuni sifatida boshqa ustunni tanlang.

Masalan, rasmda ko'rsatilgan pivot jadvali uchun. 6, siz saralashni umumiy daromad bo'yicha emas, balki bir turdagi tovarlarni sotishdan tushgan daromad bo'yicha belgilashingiz mumkin, masalan, Qurilmalar(mijozlar F ustuni bo'yicha emas, balki C ustuni bo'yicha tartiblanganligini unutmang).

Guruch. 6. Qo'shimcha parametrlar mijozlarni umumiy daromad bo'yicha emas, balki mahsulot sotishdan tushgan daromadlar bo'yicha saralash imkonini beradi Qurilma

Bu kabi tartiblash uchun:

1. A4 katagida joylashgan mijozlar ro'yxatini kengaytiring.
2. Ko'proq saralash opsiyalarini tanlang.
3. Muloqot oynasida Saralash (mijoz) tugmasini bosing Qo'shimcha…
4. Muloqot oynasida Qo'shimcha saralash imkoniyatlari (mijoz) Bo'limni tanlang Saralash tartibi va kalitni o'rnating Belgilangan ustundagi qiymatlar.
5. Havola oynasini bosing va keyin C5 katakchasini tanlang. E'tibor bering, siz qiymat kataklaridan birini bosishingiz kerak Qurilma, chunki sarlavhada Qurilma C4 katakchasini bosish mumkin emas.
6. Parametrlarni sozlashni yakunlash uchun OK tugmasini ikki marta bosing.

Xavotir olmang, bu bosqichma-bosqich algoritm tavsifi ko'proq ta'lim maqsadida berilgan. Excel 2013 dan boshlab, PivotTable ma'lumotlarini saralash ancha osonlashdi. Endi YA va AYA tugmalari yorliqda Ma'lumotlar aqlli tartiblash algoritmlaridan foydalaning. Ushbu tugmalar yordamida saralashga urinayotganda dastur tartiblash tugmachasini bosishdan oldin qaysi katak ajratilganligiga qarab foydalanuvchi niyatlarini bashorat qilishga harakat qiladi (7-rasm):

• A1, C1, D1, E1, F1, F2, A30, F30 – mavjud emas
• A2:A29 – A ustunidagi mijozlar ismlarini alifbo tartibida tartiblaydi
• V1, V2, S2, D2, E2 - 2-qatordagi tovarlarning nomlarini alifbo tartibida kiritadi.
• B30, C30, D30, E30 - 30-qatordagi daromad miqdorini kamayish (o'sish) tartibida tartibga soladi.
• ortib borayotgan (pasayib borayotgan) sotuvlar B3:B29 – modullar, C3:C29 – qurilmalar, D3:D29 – qismlar, E3:E29 – dorilar, F3:F29 – jami.

Qo'lda saralash

E'tibor bering, dialog oynasida Tartiblash(5-rasmga qarang) siz ma'lumotlarni saralash qoidalarini qo'lda belgilashingiz mumkin. Biroq, pivot jadvalini qo'lda saralash ham boshqacha, juda g'ayrioddiy usulda amalga oshiriladi. Shakldagi Pivot Table hisobotida. 8a alifbo tartibida saralangan mahsulot toifalari ketma-ketligini ko'rsatadi: Qism, modul, tayyorgarlik Va Qurilma. E'tibor bering, har bir toifaga sotilgan mahsulotlar hajmi Tafsilot, eng kattasi emas. Va bu toifani birinchi bo'lib ko'rsatishga arzimaydi. Sichqoncha kursorini E4 katakka qo'ying va so'zni kiriting Tafsilot. Shunchaki tugmani bosing Kirish Excel ustunni ko'chirishga qaror qilganingizni qanday aniqlaydi Tafsilot jadvalning oxirgi ustuniga. Ushbu mahsulot toifasiga tegishli barcha raqamli qiymatlar B ustunidan E ustuniga o'tadi. Boshqa mahsulot toifalariga tegishli qiymatlar chapga siljiydi. Bu xatti-harakat mantiqsiz ko'rinadi va faqat Excel pivot jadvallari uchun xosdir. Oddiy Excel ma'lumotlar to'plamini bu tarzda o'zgartirib bo'lmaydi. Shaklda. 8b-rasmda yangi ustun sarlavhasini E4 katakka o'tkazgandan so'ng pivot jadval ko'rsatilgan.

Guruch. 8. Qo'lda saralash: (a) mahsulot toifalari alifbo tartibida saralanadi, (b) Qism toifasi oxirgi o'rinda turadi

Sichqoncha ixlosmandlari kerakli ustun sarlavhalarini (yoki alohida satrlarni) sudrab olib tashlashlari mumkin. Ustun sarlavhasi maydonini bosing va sichqoncha ko'rsatkichini tanlangan katakchalar diapazoni chegarasida to'rt boshli o'qga o'zgarmaguncha ushlab turing. Hujayrani tanlangan joyga sudrab borishni boshlang; Ko'rsatkich qalin chiziq va seriflar ko'rinishida paydo bo'ladi. Sichqoncha tugmachasini qo'yib yuborganingizdan so'ng, raqamli qiymatlar darhol yangi ustunga o'tadi. E'tibor bering, qo'lda tartiblashdan foydalanganda ma'lumotlar manbasiga qo'shilgan mahsulotlar ro'yxat oxiriga qo'shiladi. Buning sababi, Excel yangi hududni aniq qaerga qo'shishni bilmaydi.

Maxsus ro'yxatlar bo'yicha ma'lumotlarni saralash

Maydonlarni ko'rsatish tartibini sozlash muammosining yana bir yechimi - bu maxsus ro'yxatlarni yaratish. Bunday ro'yxat yordamida kelajakda yaratilgan pivot jadvallar tartiblanadi. Odatiy bo'lib, Excel to'rtta maxsus ro'yxatni o'z ichiga oladi: hafta kunlari, yil oylari va hafta kunlari va yil oylari uchun qisqartirilgan nomlar. Dastur hafta kunlarining nomlarini tabiiy ketma-ketlikda, dushanbadan boshlab va Quyosh bilan tugaydigan (alfavit tartibida emas) tartiblaydi.

O'zingizning saralash ro'yxatini yaratish uchun quyidagi amallarni bajaring:

1. Ishchi varaqning ma'lumotlardan xoli maydoniga mahsulot toifalari nomlarini siz yaratayotgan maxsus ro'yxatga mos keladigan ketma-ketlikda kiriting. Har bir katakka bitta nom kiriting va nomlarni bitta ustunga joylashtiring (9-rasm).
2. Natijada mahsulot toifalari nomlari ro'yxatini tanlang (A10:A13 katakchalar).
3. Ip yorlig'ini tanlang Fayl va chapdagi oynada ko'rsatilgan navigatsiya panelining pastki qismida tugmani bosing Variantlar dialog oynasini ochish uchun Excel opsiyalari.
4. Kategoriyani tanlang Qo'shimcha, bo'limga o'ting Umumiy va tugmani bosing Ro'yxatlarni tahrirlash.
5. Muloqot oynasida Ro'yxatlar maydonda sarlavhalarning oldindan tanlangan ro'yxatini o'z ichiga olgan diapazonning manzili ko'rsatiladi Hujayralardan ro'yxatni import qilish(10-rasm). Tugmasini bosing Import ko'rsatilgan ma'lumotlar asosida mahsulot toifalarining yangi ro'yxatini yaratish. Hududning pastki qismiga yangi ro'yxat qo'shiladi Ro'yxatlar.
6. Muloqot oynasini yopish uchun OK tugmasini bosing Ro'yxatlar. Muloqot oynasini yopish uchun yana OK tugmasini bosing Excel opsiyalari.

Guruch. 10. Oyna Ro'yxatlar

Yangi yaratilgan ro'yxat dastur sozlamalarida saqlanadi va kelajakdagi Excel seanslarida mavjud bo'ladi. Endi kelajakda yaratilgan barcha pivot jadvallar ro'yxatda ko'rsatilgan tartibda avtomatik ravishda mahsulot maydoni bo'yicha saralanadi. Shaklda. 11-rasmda yaratilgan ro'yxat bo'yicha tartiblangan yangi pivot jadval (maxsus mahsulotlar ro'yxatini qo'shgandan keyin yaratilgan) ko'rsatilgan.

Oldindan yaratilgan pivot jadvallarni yangi maxsus ro'yxatga ko'ra tartiblash uchun quyidagi amallarni bajaring:

1. Maydonlar ro'yxatini kengaytiring Mahsulot va variantni tanlang Qo'shimcha saralash imkoniyatlari.
2. Muloqot oynasida Saralash (mahsulot) tanlash tugmasi maydon bo'yicha ko'tarilish (A dan Z gacha). ni bosing va ochiladigan ro'yxatdan tanlang Mahsulot.
3. Tugmasini bosing Qo'shimcha…
4. Muloqot oynasida Qo'shimcha saralash imkoniyatlari (Mahsulot) katagiga belgini olib tashlang Avtomatik tartiblash.
5. Roʻyxatni kengaytiring Birinchi kalit bo'yicha tartiblang va mahsulot toifalari nomlarini o'z ichiga olgan ro'yxatni tanlang (12-rasm).
6. OK tugmasini ikki marta bosing.

Eslatma Bill Jelen, Maykl Aleksandrning kitobi asosida yozilgan. . 4-bob.

Excelda ma'lumotlarni saralash jadval ma'lumotlarini kerakli tartibda tartibga solishdir, masalan, o'sish (eng kichikdan kattagacha) yoki kamayish (kattadan kichikgacha). Raqamli va matn qiymatlari, sana va vaqt qiymatlari hamda formatlar tartiblangan. Ma'lumotlarni saralash ham ustunlar, ham satrlar bo'yicha mumkin. Yashirin qatorlar va ustunlar saralashdan oldin ko'rsatilishi kerak.

Saralanadigan ma'lumotlar turlari va tartiblash tartibi

Excelda raqamli qiymatlarni saralash

Raqamli qiymatlarni o'sish bo'yicha saralash - bu qiymatlar eng kichikdan kattagacha (minimaldan maksimalgacha) tartibga solingan qiymatlarni tartibga solish.

Shunga ko'ra, raqamli qiymatlarni kamayish tartibida saralash qiymatlarni eng kattadan kichikga (maksimaldan minimalgacha) joylashtirishdir.

Excelda matn qiymatlarini saralash

"A dan Z gacha saralash" - ma'lumotlarni o'sish tartibida saralash;

"Z dan A gacha saralash" - ma'lumotlarni kamayish tartibida saralash.

Matn qiymatlarini alifbo tartibida saralash uchun bu qiymatlar bir-biri bilan taqqoslanadi. Ma'lumki, matnni saqlashda kompyuterda har bir belgi o'ziga xos noyob raqamga ega bo'lgan, belgilar kodi deb ataladigan sxemadan foydalanadi. Qaysi matn qiymati kattaroq va qaysi biri kamroq ekanligini aniqlash uchun aynan shu kodlar solishtiriladi.

Matn qiymatlari alifbo, raqamli va maxsus belgilarni o'z ichiga olishi mumkin. Bunday holda, raqamlar ham raqamli, ham matn formatida saqlanishi mumkin. Raqamli formatda saqlangan raqamlar matn formatida saqlangan raqamlardan kichikroq. Matn qiymatlarini to'g'ri saralash uchun barcha ma'lumotlar matn formatida saqlanishi kerak. Bundan tashqari, boshqa ilovalardan matn ma'lumotlari hujayralarga kiritilganda, ma'lumotlar etakchi bo'shliqlarni o'z ichiga olishi mumkin. Saralashni boshlashdan oldin saralanayotgan ma'lumotlardan bosh bo'shliqlarni (yoki boshqa chop etilmaydigan belgilarni) olib tashlashingiz kerak, aks holda tartiblash to'g'ri bajarilmaydi.

Matn ma'lumotlarini katta-kichik harflarga qarab tartiblashingiz mumkin. Buni amalga oshirish uchun saralash opsiyalarida "Ishni ko'rib chiqish" katagiga belgi qo'yishingiz kerak.

Odatda, katta harflar kichik harflarga qaraganda kichikroq raqamlarga ega.

Sana va vaqt qiymatlarini saralash

"Eng qadimgidan eng yangisiga saralash" sana va vaqt qiymatlarini eng eski qiymatdan oxirgi qiymatgacha saralashdir.

"Yangidan eskiga saralash" sana va vaqt qiymatlarini oxirgi qiymatdan eng dastlabki qiymatgacha saralashdir.

Saralash formatlari

Microsoft Excel 2007 va undan keyingi versiyalari formatlash bo'yicha saralashni ta'minlaydi. Ushbu tartiblash usuli hujayralar diapazoni hujayra to'ldirish rangi, shrift rangi yoki piktogramma to'plami yordamida formatlanganda qo'llaniladi. Excelda to'ldirish va shrift ranglari o'z kodlariga ega va aynan shu kodlar formatlarni saralashda ishlatiladi.

Maxsus roʻyxat boʻyicha saralash

Jadval ma'lumotlarini maxsus ro'yxatlar bo'yicha tartiblash mumkin, masalan, oylar ro'yxati, hafta kunlari ro'yxati, korxonaning tarkibiy bo'linmalari ro'yxati, xodimlarning shaxsiy raqamlari ro'yxati va boshqalar. Excel o'z ma'lumotlarni saralash ro'yxatlarini yaratish qobiliyatiga ega. Ushbu tartiblash tartibida saralanadigan ma'lumotlar va ro'yxat qiymatlari mos kelishi kerak.

Saralash opsiyalari

Ustun bo'yicha saralash

Excelning istalgan versiyasida ustunlar bo'yicha saralash sukut bo'yicha o'rnatiladi, ya'ni tanlangan ustun katakchalarining qiymatlari kerakli tartibda joylashtirilgan va diapazon satrlari katakning joylashishiga qarab almashtiriladi. tartiblangan ustunda.Jadvalni ustunlar bo'yicha saralash uchun jadvalning istalgan katagiga tanlash belgisini qo'ying va minimaldan maksimalgacha yoki maksimaldan minimalgacha tartiblashni anglatuvchi piktogrammalardan birini bosing. Jadval tanlov belgisini o'z ichiga olgan ustun bo'yicha tartiblanadi.

String boʻyicha saralash

Dastur sozlamalarida siz ustun bo'yicha saralashni satr bo'yicha saralashga o'zgartirishingiz mumkin. Agar satr bo'yicha saralash o'rnatilgan bo'lsa, tanlangan qator katakchalarining qiymatlari belgilangan tartibda joylashtiriladi va ustunlar tartiblangan qatordagi kataklarning joylashuviga qarab almashtiriladi.

Ko'p darajali saralash

Shunday qilib, agar siz ustun bo'yicha saralansangiz, u holda satrlar almashtiriladi, agar ma'lumotlar qator bo'yicha tartiblangan bo'lsa, ustunlar almashtiriladi.

Excel sizga ma'lumotlarni faqat bitta ustun yoki bitta satr bo'yicha emas, balki turli xil tartiblash darajalarini yaratish orqali saralashga imkon beradi. Misol uchun, Excel 2007 da 64 ta saralash darajalari qo'shilishi, o'chirilishi, nusxalanishi va almashtirilishi mumkin.

Boshqa narsalar qatorida, saralashda katta-kichik holatni hisobga olishingiz yoki e'tiborsiz qoldirishingiz mumkin.

Excelda ma'lumotlarni saralash uchun qo'shimcha

Microsoft Excel foydalanuvchilarga har xil turdagi qiymatlarni saralash uchun juda katta standart vositalar to'plamini taqdim etadi, ammo standart vositalar yordamida hal qilish uchun noqulay yoki ko'p vaqt talab qiladigan vazifalar mavjud, masalan, har bir satr/har bir ustunni saralash. tartiblash faqat satr/ustun ichida amalga oshiriladi va qo'shni hujayralarga ta'sir qilmaydi.