Agar siz yangi foydalanuvchi bo'lmasangiz, Excel-dagi hamma narsaning 99 foizi vertikal jadvallar bilan ishlashga mo'ljallanganligini, bu erda parametrlar yoki atributlar (maydonlar) ustunlar bo'ylab o'tishi va ob'ektlar yoki hodisalar haqidagi ma'lumotlar joylashganligini allaqachon payqagan bo'lsangiz kerak. satrlarda. Pivot jadvallari, oraliq jamilar, ikki marta bosish bilan formulalarni nusxalash - barchasi ushbu ma'lumotlar formati uchun maxsus moslashtirilgan.
Biroq, istisnolarsiz qoidalar yo'q va juda muntazam chastotada, agar ishda gorizontal semantik yo'naltirilgan jadval yoki qatorlar va ustunlar bir xil ma'noga ega bo'lgan jadval uchrasa nima qilish kerakligi so'raladi:
Va agar Excel hali ham gorizontal ravishda qanday tartiblashni bilsa (buyruq bilan Ma'lumotlar - Saralash - Variantlar - Ustunlarni saralash), keyin filtrlash bilan bog'liq vaziyat yomonroq - Excelda satrlarni emas, balki ustunlarni filtrlash uchun o'rnatilgan vositalar mavjud emas. Shunday qilib, agar siz bunday vazifaga duch kelsangiz, turli darajadagi murakkablikdagi vaqtinchalik echimlarni topishingiz kerak bo'ladi.
1-usul. Yangi FILTER funksiyasi
Agar siz Excel 2021 yangi versiyasida yoki Excel 365 obunasida bo‘lsangiz, yangi kiritilgan funksiyadan foydalanishingiz mumkin. Filtrni (FILTR), bu manba ma'lumotlarini nafaqat satrlar, balki ustunlar bo'yicha ham filtrlashi mumkin. Ishlash uchun bu funktsiya yordamchi gorizontal bir o'lchovli massiv qatorini talab qiladi, bu erda har bir qiymat (TRUE yoki FALSE) jadvaldagi keyingi ustunni ko'rsatish yoki yashirishimizni aniqlaydi.
Keling, jadvalimiz tepasiga quyidagi qatorni qo'shamiz va undagi har bir ustunning holatini yozamiz:
- Aytaylik, biz har doim birinchi va oxirgi ustunlarni (sarlavhalar va jami) ko'rsatishni xohlaymiz, shuning uchun ular uchun massivning birinchi va oxirgi kataklarida = TRUE qiymatini o'rnatamiz.
- Qolgan ustunlar uchun mos keladigan kataklarning mazmuni funksiyalar yordamida kerakli holatni tekshiradigan formula bo'ladi. И (VA) or OR (OR). Masalan, jami 300 dan 500 gacha.
Shundan so'ng, faqat funktsiyadan foydalanish qoladi Filtrni Yordamchi massivimiz TRUE qiymatga ega bo'lgan ustunlarni tanlash uchun:
Xuddi shunday, siz ustunlarni berilgan ro'yxat bo'yicha filtrlashingiz mumkin. Bunday holda, funktsiya yordam beradi COUNTIF (COUNTIF), bu ruxsat etilgan ro'yxatdagi jadval sarlavhasidan keyingi ustun nomining takrorlanish sonini tekshiradi:
Usul 2. Odatdagi o'rniga pivot jadvali
Hozirgi vaqtda Excelda faqat pivot jadvallardagi ustunlar bo'yicha o'rnatilgan gorizontal filtrlash mavjud, shuning uchun agar biz asl jadvalimizni pivot jadvalga aylantira olsak, biz ushbu o'rnatilgan funksiyadan foydalanishimiz mumkin. Buning uchun bizning manba jadvalimiz quyidagi shartlarga javob berishi kerak:
- bo'sh va birlashtirilgan hujayralarsiz "to'g'ri" bir qatorli sarlavha chizig'iga ega bo'ling - aks holda pivot jadvalini yaratish ishlamaydi;
- satrlar va ustunlar yorliqlarida dublikatlarni o'z ichiga olmaydi - ular xulosada faqat noyob qiymatlar ro'yxatiga "yiqilib tushadi";
- qiymatlar oralig'ida faqat raqamlarni o'z ichiga oladi (satr va ustunlar kesishmasida), chunki pivot jadval ularga albatta qandaydir yig'ish funktsiyasini qo'llaydi (yig'indi, o'rtacha va hokazo) va bu matn bilan ishlamaydi.
Agar ushbu shartlarning barchasi bajarilsa, bizning asl jadvalimizga o'xshab ko'rinadigan pivot jadvalini yaratish uchun uni (asl jadval) o'zaro faoliyat jadvaldan tekisga (normallashtirilgan) kengaytirish kerak bo'ladi. Buning eng oson yo'li 2016 yildan beri Excelga o'rnatilgan kuchli ma'lumotlarni o'zgartirish vositasi bo'lgan Power Query plaginidir.
Bular:
- Keling, jadvalni "aqlli" dinamik buyruqqa aylantiramiz Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash).
- Buyruq bilan Power Query-ga yuklanmoqda Ma'lumotlar - Jadvaldan / diapazondan (Ma'lumotlar - Jadvaldan / diapazondan).
- Biz chiziqni jami bilan filtrlaymiz (xulosa o'z jamiga ega bo'ladi).
- Birinchi ustun sarlavhasini o'ng tugmasini bosing va tanlang Boshqa ustunlarni yechish (Boshqa ustunlarni ochish). Barcha tanlanmagan ustunlar ikkiga aylantiriladi - xodimning nomi va uning ko'rsatkichining qiymati.
- Ustunni ustunga kirgan jamilar bilan filtrlash sifatlari.
- Buyruq bilan hosil bo'lgan tekis (normallashtirilgan) jadvalga muvofiq pivot jadvalini quramiz Bosh sahifa — Yopish va yuklash — Yopish va yuklash… (Uy — Yopish va yuklash — Yopish va yuklash…).
Endi siz pivot jadvallarida mavjud bo'lgan ustunlarni filtrlash qobiliyatidan foydalanishingiz mumkin - nomlar va elementlar oldidagi odatiy tasdiq belgilari Imzo filtrlari (Yorliq filtrlari) or Qiymat bo'yicha filtrlar (Qiymat filtrlari):
Va, albatta, ma'lumotlarni o'zgartirganda, siz so'rovimiz va xulosani klaviatura yorlig'i bilan yangilashingiz kerak bo'ladi. Ctrl+Alt+F5 yoki jamoa Ma'lumotlar - Hammasini yangilash (Ma'lumotlar - Hammasini yangilash).
3-usul. VBA-da makro
Oldingi barcha usullar, siz osongina ko'rib turganingizdek, aniq filtrlash emas - biz asl ro'yxatdagi ustunlarni yashirmaymiz, balki asl nusxadan berilgan ustunlar to'plami bilan yangi jadval hosil qilamiz. Agar manba ma'lumotlaridagi ustunlarni filtrlash (yashirish) talab etilsa, u holda tubdan boshqacha yondashuv, ya'ni so'l kerak bo'ladi.
Aytaylik, biz jadval sarlavhasidagi menejerning nomi sariq katak A4da ko'rsatilgan niqobga mos keladigan ustunlarni filtrlashni xohlaymiz, masalan, "A" harfi bilan boshlanadi (ya'ni, "Anna" va "Artur" ni oling. " Natijada).
Birinchi usulda bo'lgani kabi, biz birinchi navbatda yordamchi diapazon qatorini qo'llaymiz, bunda har bir katakda mezonimiz formula bo'yicha tekshiriladi va ko'rinadigan va yashirin ustunlar uchun mos ravishda TRUE yoki FALSE mantiqiy qiymatlari ko'rsatiladi:
Keyin oddiy makrosni qo'shamiz. Varaq yorlig'ini o'ng tugmasini bosing va buyruqni tanlang manba (Manba kodi). Quyidagi VBA kodini ko'chiring va ochilgan oynaga joylashtiring:
Private Sub Worksheet_Change(ByVal Maqsad diapazon sifatida) Agar Target.Address = "$A$4" bo'lsa, keyin diapazondagi har bir katak uchun("D2:O2") Agar hujayra = rost bo'lsa, u holda cell.EntireColumn.Hidden = False Boshqa hujayra.EntireColumn.Hidden = True End If Next yachey End If End Sub Uning mantig'i quyidagicha:
- Umuman olganda, bu hodisa ishlov beruvchisi Ish varag'ini_o'zgartirish, ya'ni joriy varaqdagi istalgan katakdagi har qanday o'zgarishlarda ushbu makros avtomatik ravishda ishga tushadi.
- O'zgartirilgan katakka havola har doim o'zgaruvchida bo'ladi maqsad.
- Birinchidan, foydalanuvchi katakchani (A4) mezon bilan o'zgartirganligini tekshiramiz - bu operator tomonidan amalga oshiriladi. if.
- Keyin tsikl boshlanadi Har biriga… har bir ustun uchun TRUE / FALSE indikator qiymatlari bilan kulrang katakchalarni (D2: O2) takrorlash.
- Agar keyingi kulrang katakning qiymati TRUE (to'g'ri) bo'lsa, u holda ustun yashirilmaydi, aks holda biz uni yashiramiz (xususiyat). yashirin).
- Office 365 dan dinamik massiv funksiyalari: FILTER, SORT va UNIC
- Power Query yordamida ko'p qatorli sarlavhali pivot jadval
- Makroslar nima, ularni qanday yaratish va ishlatish