Agar oxirigacha o'qib chiqsangiz, bunday foydali Excel funktsiyasidan qanday foydalanishni bilib olasiz filtr... Endi men juda aniq misol bilan Excel filtrlari nima ekanligini va katta jadvallar bilan ishlashda vaqtni tejashni ko'rsataman. Bu umuman qiyin emas. Maqolaning oxirida siz elektron jadvalni yuklab olishingiz mumkin, men uni bu erda Excel filtrlari bilan ishlashga misol sifatida ishlataman.
Excel jadvallaridagi filtrlar nima uchun kerak?
Va keyin qodir bo'lish tez faqat kerakli ma'lumotlarni tanlang, yashirish keraksiz torlar jadvallar. Shunday qilib, filtr imkon beradi o'chirmasdan Excel jadval satrlari ularni vaqtincha yashiradi.
Filtrda yashiringan qatorlar yo'qolmaydi. Siz odatdagidek ularning balandligi nolga tenglashishini tasavvur qilishingiz mumkin (men qatorlarning balandligi va ustunlar kengligini o'zgartirish haqida gapirdim). Shunday qilib, filtr tomonidan yashirilmagan qolgan chiziqlar, go'yo "bir-biriga yopishtirilgan". Natijada filtrlangan jadval hosil bo'ladi.
Ma'lumotlar bazalari ma'lumotni saqlash uchun juda qulaydir, ammo zarurat tug'ilganda kerakli ma'lumotlarni olish uchun ularni yaratamiz.
Masalan, bizga juma kuni soat 16.00 dan keyin Moskvaga jo'naydigan poezd poezdlari jadvali kerak.
Kerakli ma'lumotlarni qidirish ma'lum bir mezonga mos keladigan satrlarni tanlash orqali amalga oshiriladi. Ko'pgina hollarda tanlov mezonlari hujayra tarkibining ma'lum qiymatga tengligi.
Yozuvlarni tanlashda tenglik uchun taqqoslashdan tashqari siz boshqa taqqoslash operatsiyalaridan foydalanishingiz mumkin. Masalan, katta, kichik, katta yoki teng, kichik yoki teng. Ushbu operatsiyalardan foydalanish so'rov mezonini aniqroq shakllantirishga imkon beradi. Masalan, agar familiyasi "Ku" bilan boshlanadigan shaxs haqida ma'lumot topishingiz kerak bo'lsa, unda mezon sifatida "familiya katakchasining tarkibi Ku dan katta yoki unga teng va familiya katakchasining tarkibi L dan kichik" qoidasidan foydalanishingiz mumkin.
Jadvaldan ma'lum bir mezon (qoida) ni qondiradigan qatorlarni tanlash jarayoni filtrlash, mezon (qatorlarni tanlash sharti) esa filtr deb ataladi.
- Jadvaldan ma'lum bir shartni qondiradigan qatorlarni tanlash uchun sizga quyidagilar kerak:
- 1. Ustunlar nomlarini o'z ichiga olgan jadval qatorini tanlang, Uy -\u003e Tahrirlash yorlig'ini oching va Saralash va Filtrlar ro'yxatidagi Filtr buyrug'ini tanlang. Natijada tanlangan qatorda jadval ustunlari nomlari yonida ochiladigan ro'yxat tugmalari paydo bo'ladi.
- 2. Ustun sarlavhasini bosish orqali ro'yxatni kengaytiring va ochilgan oynada avval o'chirgichni tozalang (Hammasini tanlang), so'ngra qatorlarni tanlash mezonlari sifatida ishlatilishi kerak bo'lgan qiymatni tanlang.
Yuqoridagi amallarni bajarish natijasida ekranda faqat so'rov shartini qondiradigan qatorlar qoladi.
Raqamli qiymatlarni o'z ichiga olgan ustunni tanlash mezonlari sifatida siz katak qiymatlari bo'lishi kerak bo'lgan oraliqni belgilashingiz mumkin. Masalan, 100 rubl oralig'ida bo'lgan xarajatlar ro'yxatini olish. 1000 rublgacha, Miqdor ustuni uchun filtr oynasini ochishingiz kerak, Raqamli filtrlar -\u003e o'rtasida tanlang va paydo bo'ladigan Custom AutoFilter oynasida oraliq chegaralarini kiriting.
Excelda ma'lumotlarni filtrlash orqali ma'lumotlarni bir / bir nechta parametrlar bo'yicha aks ettirishingiz mumkin.
Buning uchun ikkita vosita mavjud: AutoFilter va Advanced Filter. Ular o'chirmaydi, lekin shartga mos kelmaydigan ma'lumotlarni yashiradi. Avtofiltr eng oddiy operatsiyalarni bajaradi. Murakkab filtrda yana ko'plab imkoniyatlar mavjud.
Excelda AutoFilter va Advanced Filter
Formatlanmagan ham, ro'yxat sifatida e'lon qilinmagan ham oddiy jadval mavjud. Avtomatik filtrni asosiy menyu orqali yoqishingiz mumkin.
Agar ma'lumotlar oralig'ini jadval sifatida formatlasangiz yoki uni ro'yxat deb e'lon qilsangiz, darhol avtomatik filtr qo'shiladi.
AutoFilter-dan foydalanish oddiy: kerakli qiymatga ega bo'lgan yozuvni tanlashingiz kerak. Masalan, №4 do'konga etkazib berishni namoyish eting. Tegishli filtrlash shartiga qarama-qarshi belgi qo'ydik:
Biz darhol natijani ko'ramiz:
Asbobning xususiyatlari:
- Avtomatik filtr faqat doimiy diapazonda ishlaydi. Bitta varaqdagi turli jadvallar suzilmaydi. Agar ular bir xil turdagi ma'lumotlarga ega bo'lsa ham.
- Asbob yuqori satrni ustun sarlavhalari sifatida ko'rib chiqadi - bu qiymatlar filtrga kiritilmagan.
- Bir vaqtning o'zida bir nechta filtrlash shartlarini qo'llash mumkin. Biroq, har bir oldingi natija keyingi filtr uchun zarur bo'lgan yozuvlarni yashirishi mumkin.
Murakkab filtrda yana ko'plab imkoniyatlar mavjud:
- Kerakli miqdordagi filtrlash shartlarini belgilashingiz mumkin.
- Ma'lumotlarni tanlash mezonlari ko'rinadi.
- Kengaytirilgan filtrdan foydalanib, ko'p satrli qatorda noyob qiymatlarni osongina topishi mumkin.
Excelda rivojlangan filtrni qanday yaratish
Tayyor misol - Excelda kengaytirilgan filtrdan qanday foydalanish:
Asl jadvalda faqat "Moskva" qiymatini o'z ichiga olgan qatorlar qoladi. Filtrlashni bekor qilish uchun "Saralash va filtrlash" bo'limidagi "Tozalash" tugmachasini bosishingiz kerak.
Excelda rivojlangan filtrdan qanday foydalanish
"Moskva" yoki "Ryazan" so'zlarini o'z ichiga olgan qatorlarni tanlash uchun Excelda rivojlangan filtrdan foydalanishni ko'rib chiqamiz. Filtrlash shartlari bitta ustunda bo'lishi kerak. Bizning misolimizda, bir-birimiz ostida.
Kengaytirilgan filtr menyusini to'ldiring:
Biz berilgan mezon bo'yicha qatorlar tanlangan jadvalni olamiz:
"Do'kon" ustunida "# 1" qiymatini va xarajatlar ustunida "\u003e 1 000 000 rubl" qiymatini o'z ichiga olgan qatorlarni tanlaymiz. Filtrlash mezonlari shartli plitaning tegishli ustunlarida bo'lishi kerak. Bir qatorda.
Filtrlash parametrlarini to'ldiring. OK tugmasini bosing.
Jadvalda faqat "Viloyat" ustunidagi "Ryazan" so'zi yoki "Narx" ustunidagi "\u003e 10,000,000 rubl" qiymati bo'lgan qatorlarni qoldiramiz. Tanlov mezonlari har xil ustunlarni nazarda tutganligi sababli ularni tegishli satrlar ostida turli satrlarga joylashtiramiz.
Kengaytirilgan filtr vositasini qo'llaymiz:
Ushbu vosita formulalar bilan ishlashi mumkin, bu foydalanuvchiga massivlardan qiymatlarni tanlashda deyarli barcha muammolarni hal qilishga imkon beradi.
Asosiy qoidalar:
- Formulaning natijasi tanlov mezonidir.
- Yozilgan formulada TRUE yoki FALSE qaytadi.
- Asl diapazon mutlaq havolalar yordamida, tanlov mezonlari (formulalar shaklida) nisbiy havolalar yordamida ko'rsatiladi.
- Agar qaytish qiymati HAQQI bo'lsa, filtr qo'llanilgandan keyin satr ko'rsatiladi. FALSE - yo'q.
Miqdorni o'rtacha qiymatdan yuqori bo'lgan chiziqlarni namoyish etamiz. Buning uchun mezonlarga ega jadvaldan tashqari (I1 katakchada) "Eng" nomini kiriting. Quyida formula mavjud. O'RTASH funktsiyasidan foydalanamiz.
Asl diapazondagi istalgan katakchani tanlang va "Kengaytirilgan filtr" ga qo'ng'iroq qiling. I1: I2 ni tanlov mezonlari sifatida ko'rsatamiz (havolalar nisbiy!).
"Miqdor" ustunidagi qiymatlar o'rtacha qiymatdan yuqori bo'lgan jadvalda faqat o'sha qatorlar qoldi.
Jadvalda faqat takrorlanmaydigan qatorlarni qoldirish uchun "Kengaytirilgan filtr" oynasida "Faqat noyob yozuvlar" yonidagi katakchani belgilang.
OK tugmasini bosing. Ikki nusxadagi satrlar yashiriladi. Varaqada faqat noyob yozuvlar qoladi.
Ba'zan Excel elektron jadvallarida juda katta miqdordagi ma'lumotlar mavjud, masalan, yiliga sotib olinadigan sarf materiallari ro'yxati. Va ular orasida faqat sizning bo'limingizga tegishli ma'lumotlarni topishingiz kerak. Buni qanday qilish kerak?
Yozuvlarning umumiy massasidan faqat sizning shartingizga javob beradigan qismni tanlash uchun siz filtr deb nomlangan vositadan foydalanishingiz mumkin. Filtrni o'rnatish uchun sichqoncha yordamida jadval sarlavhasidagi sarlavhalarni yoki butun jadvalni tanlang, so'ngra yorliqda uy tugmachani bosing Saralash va filtrlash va paydo bo'lgan menyuda tanlang Filtr... Shaklda ko'rsatilgandek, jadval ustunlari sarlavhalari bo'lgan katakchalarning pastki o'ng burchagida pastga yo'naltirilgan o'q tugmalari paydo bo'ladi. 5.4.
Agar siz bunday tugmani bosgan bo'lsangiz, menyu paydo bo'ladi, unda siz ushbu ustun bo'yicha saralash yo'nalishini, shuningdek yozuvlarni tanlash shartlarini belgilashingiz mumkin (5.5-rasm).
Agar siz, masalan, ro'yxatdagi faqat S harfi bilan boshlanadigan nomlarni tanlashingiz kerak bo'lsa, paydo bo'lgan menyuda ustunlar nomidagi tugmani bosing, "Matn filtrlari" pastki menyusini oching va tanlang. bilan boshlang... Shaklda ko'rsatilgan oyna. 5.6. So'zlarning o'ng tomonidagi maydonga C harfini kiriting. Siz bir nechta harflarni kiritishingiz mumkin. Natijada, faqat harflarning belgilangan birikmasidan boshlanadigan nomlar tanlanadi va jadvaldagi qolgan yozuvlar ekrandan yashiriladi (5.7-rasm).
Filtr o'rnatilgan ustunning sarlavhasi bo'lgan katakchadagi tugma o'zgaradi va tegishli shaklga ega bo'ladi. Agar element o'rniga, raqamli ma'lumotlar bilan tugmachani bosgan bo'lsangiz Matn filtrlari menyu elementi paydo bo'ladi Raqam filtrlari... Bunday elementni tanlaganingizda, tanlangan yozuvlar uchun raqamlar qiymatlari oralig'ini belgilashingiz mumkin (5.8-rasm). Siz belgilagan qiymatdan katta yoki kichik bo'lgan barcha yozuvlarni tanlashingiz mumkin.
Biz ko'rib chiqayotgan misol uchun (5.4-rasmga qarang), 1973 yildan keyin tug'ilganlar ro'yxatini tanlaymiz. Buni amalga oshirish uchun paydo bo'lgan menyuda E1 katakchasidagi tugmani bosing (Tug'ilgan yili), pastki menyuni oching. Raqam filtrlarishaklda ko'rsatilganidek. 5.8 va elementni kattaroq tanlang. Shaklda ko'rsatilgan oyna. 5.9. So'zning o'ng tomonidagi maydonga 1973 qiymatini kiriting va OK tugmasini bosing. Natijada ro'yxatda faqat 1973 yildan keyin tug'ilgan odamlar qoladi (5.10-rasm).
Filtrni istalgan vaqtda o'zgartirish yoki olib tashlash mumkin. Buning uchun tugmani bosing va paydo bo'lgan menyuda elementni tanlang Filtrni ustundan olib tashlang.
Excel ma'lumotlarini filtrlash ikkita filtrni o'z ichiga oladi: AutoFilter va Advanced Filter. Sizda katta ma'lumotlar to'plami bor deb taxmin qiling, ammo butun massivdan ma'lum bir sana, aniq bir shaxs va boshqalar bilan bog'liq ma'lumotlarni ko'rishingiz yoki tanlashingiz kerak. Buning uchun filtrlar mavjud. Ushbu vositani birinchi marta uchratganlar uchun filtr o'chirmaydi, lekin siz ular uchun belgilagan filtrlash shartlariga javob bermaydigan yozuvlarni yashiradi.
Birinchisi, eng oddiy operatsiyalar uchun mo'ljallangan avtofiltr - ma'lum bir qiymatga ega yozuvlarni ta'kidlash (masalan, faqat LeBron Jeymsga tegishli yozuvlarni ta'kidlash), ma'lum bir oraliqda (yoki o'rtacha o'ndan yuqori yoki o'ndan yuqori) yotgan ma'lumotlar yoki ma'lum bir rangdagi shriftlar ( Aytgancha, juda qulay). Shunga ko'ra, uni ishlatish juda oson. Siz faqat filtrlangan ko'rmoqchi bo'lgan ma'lumotlarni tanlashingiz kerak. Keyin "Ma'lumotlar" / "Filtr" buyrug'i. Ro'yxat katakchasi yuqori jadvalning har bir ustki qismida paydo bo'ladi, har bir buyruqni tushunish allaqachon oson, o'rganish va tushuntirish juda oson, umid qilamanki, bundan keyin hojat yo'q, faqat avtofiltrdan foydalanishning nüansları:
1) Faqat doimiy diapazon bilan ishlaydi. Endi bitta varaqqa ikki xil ro'yxatni filtrlash mumkin bo'lmaydi.
2) Jadvalning eng yuqori qatori avtomatik ravishda sarlavha sifatida belgilanadi va filtrlashda qatnashmaydi.
3) Siz har qanday filtrlarni turli xil ustunlarda qo'llashingiz mumkin, ammo shuni yodda tutingki, filtrlar qo'llanilish tartibiga qarab, ba'zi shartlar qo'llanilmasligi mumkin. oldingi filtrlar allaqachon kerakli yozuvlarni yashirgan. Bu erda hech qanday muammo yo'q, bu yozuvlar baribir yashiringan bo'lar edi, lekin agar siz bir nechta filtrlar to'plamini ishlatmoqchi bo'lsangiz, eng kam dasturga ega bo'lgan shartlardan boshlashingiz yaxshiroqdir.
Ishda amaliy qo'llanma: masalan, siz xatolarni topish yoki ma'lumotlarni tekshirish uchun ushbu ro'yxatda ishlaysiz. Avtofiltrni qo'llaganingizdan so'ng, siz allaqachon ko'rib chiqilgan ma'lumotlarni ketma-ket belgilab, butun jadvalni birma-bir ko'rib chiqishingiz mumkin. "Tozalash" va "Qayta topshirish" tugmachalari shartlarni qo'llaganidan keyin stol ko'rinishini aniqlaydi. Keyin, jadval bilan ishlashni tugatgandan so'ng, shriftlarni ma'lumotlarning o'zini o'zgartirmasdan asl ko'rinishiga qaytarishingiz mumkin. Aytgancha, ba'zilar jadvaldagi barcha yozuvlar har qanday shartlarni qo'llaganidan keyin yo'q bo'lib ketishi bilan chalkashib ketishadi. Yaxshilab ko'rib chiqing, siz ushbu shartlarga javob beradigan yozuvlar bo'lmagan shartlarni belgilab qo'ydingiz. Jadvalning filtrlanganligi shundaki, jadval satrlari raqamlari ko'k rangda ta'kidlangan.
Endi rivojlangan filtrga o'tamiz. Bu avtofiltrdan aniqroq sozlashda, shuningdek ma'lumotlarni filtrlashda katta tanlovda farq qiladi. Jumladan:
1) Kerakli shartlarni belgilaydi.
2) noyob (takrorlanmaydigan) ma'lumotlarga ega kataklarni tanlashga imkon beradi. Bu ko'pincha ma'lumotlar bilan ishlashda kerak bo'ladi va parametr muammo bilan juda yaxshi ishlaydi.
3) Filtr natijasini asosiy massivga tegmasdan alohida joyga ko'chirishga imkon beradi.
Shunday qilib, ushbu filtr bilan ishlashning asosiy farqi shundaki, biz avval shartlar jadvalini tayyorlashimiz kerak. Bu oddiygina amalga oshiriladi. Asosiy jadvalning sarlavhalari ko'chiriladi va biz uchun qulay joyga kiritiladi (men asosiy jadvalning yuqorisida taklif qilaman). Ushbu jadvalda juda ko'p satrlar bo'lishi kerak, shunda shartlarni aniqlagandan so'ng siz asosiy jadvalga kirmaysiz.
Shartlarning namunalari:
1) ‘L *’ - L dan boshlanadigan kataklar
2) ‘\u003e 5’ - 5 dan katta ma'lumotlar
Agar siz filtrlangan jadvaldan qatorlarni o'chirib tashlasangiz, ular qo'shnilaringizni o'zingiz bilan olib ketmasdan o'chiriladi. O'sha. agar jadval suzilgan bo'lsa va 26-29 va 31-25 satrlarni ko'rsatsa, barcha satrlarni tanlash va ularni o'chirish 30-satrni o'chirmaydi. Bu qulay, shaxsan men uni makroslarni yozishda tez-tez ishlataman. Buning afzalligi nimada - ko'pincha biz ishchi shaklga keltirilishi kerak bo'lgan jadvallarni olamiz, ya'ni. o'chirish, masalan bo'sh satrlar. Biz nima qilamiz: jadvalni filtrlaymiz, faqat bizga kerak bo'lmagan qatorlarni ko'rsatamiz, so'ngra sarlavha bilan birga butun jadvalni o'chirib tashlaymiz. Keraksiz qatorlar va sarlavha o'chirilgan, jadvalda bo'sh joy yo'q va bitta qator. Oldindan tayyorlangan maydondan oddiy nusxalash operatsiyalari bilan sarlavha qatorini qo'shish mumkin. Makrolarni yozishda nima uchun bu muhim? Kiruvchi ma'lumotlar qaysi qatordan boshlanishi noma'lum va qaysi qatordan o'chirishni boshlash kerakligi aniq emas, butun jadvalni o'chirib tashlash bu muammoni tezda hal qilishga yordam beradi.
