Mündəricat
Əgər siz kifayət qədər təcrübəsiz istifadəçi deyilsinizsə, onda Excel-də hər şeyin 99% -nin parametrlərin və ya atributların (sahələrin) sütunlardan keçdiyi və obyektlər və ya hadisələr haqqında məlumatların yerləşdiyi şaquli cədvəllərlə işləmək üçün nəzərdə tutulduğunu artıq görmüsünüz. sətirlərdə. Pivot cədvəlləri, yarımcəmlər, cüt kliklə düsturların surətini çıxarmaq - hər şey bu məlumat formatı üçün xüsusi olaraq hazırlanmışdır.
Bununla belə, istisnasız qaydalar yoxdur və kifayət qədər müntəzəm tezliklə məndən işdə üfüqi semantik oriyentasiyaya malik bir cədvəl və ya sətir və sütunların eyni çəkiyə malik olduğu bir cədvəl rast gələrsə, nə edəcəyimi soruşurlar:
Excel hələ də üfüqi olaraq necə çeşidləməyi bilirsə (komanda ilə Məlumat - Sort - Seçimlər - Sütunları çeşidləyin), onda filtrləmə ilə bağlı vəziyyət daha pisdir - Excel-də satırlar deyil, sütunları süzmək üçün daxili alətlər sadəcə yoxdur. Beləliklə, belə bir vəzifə ilə qarşılaşsanız, müxtəlif mürəkkəblik dərəcələrində həll yolları tapmalı olacaqsınız.
Metod 1. Yeni FILTER funksiyası
Excel 2021-in yeni versiyası və ya Excel 365 abunəsisinizsə, yeni təqdim edilmiş xüsusiyyətdən yararlana bilərsiniz. FILTER (FİLTRE), mənbə məlumatlarını təkcə sətirlərə görə deyil, həm də sütunlara görə filtrləyə bilər. Bu funksiyanın işləməsi üçün köməkçi üfüqi birölçülü massiv-sətir tələb olunur, burada hər bir dəyər (TRUE və ya FALSE) cədvəldə növbəti sütunu göstərib-göstərməməyimizi və ya əksinə, gizlətməyimizi müəyyən edir.
Cədvəlimizin yuxarısına aşağıdakı sətri əlavə edək və hər sütunun vəziyyətini orada yazaq:
- Tutaq ki, biz həmişə birinci və sonuncu sütunları (başlıqlar və yekunlar) göstərmək istəyirik, ona görə də onlar üçün massivin birinci və sonuncu xanalarında = TRUE dəyərini təyin edirik.
- Qalan sütunlar üçün müvafiq xanaların məzmunu funksiyalardan istifadə edərək ehtiyac duyduğumuz vəziyyəti yoxlayan bir düstur olacaqdır. И (VƏ) or OR (OR). Məsələn, cəmi 300-dən 500-ə qədərdir.
Bundan sonra yalnız funksiyadan istifadə etmək qalır FILTER yuxarıdakı köməkçi massivimizin TRUE dəyəri olan sütunları seçmək üçün:
Eynilə, verilmiş siyahı üzrə sütunları süzgəcdən keçirə bilərsiniz. Bu vəziyyətdə funksiya kömək edəcəkdir COUNTIF (COUNTIF), icazə verilən siyahıda cədvəl başlığından növbəti sütun adının baş vermə sayını yoxlayır:
Metod 2. Adi masanın yerinə pivot cədvəli
Hazırda Excel-də yalnız pivot cədvəllərdə sütunlar üzrə daxili üfüqi filtrasiya var, ona görə də orijinal cədvəlimizi pivot cədvəlinə çevirə bilsək, bu daxili funksionallıqdan istifadə edə bilərik. Bunun üçün mənbə cədvəlimiz aşağıdakı şərtlərə cavab verməlidir:
- boş və birləşdirilmiş xanalar olmadan "düzgün" bir sətirli başlıq xəttinə sahib olun - əks halda pivot cədvəli qurmaq işləməyəcək;
- sətir və sütunların etiketlərində dublikatlar olmamalıdır - onlar xülasədə yalnız unikal dəyərlər siyahısına "yıxılacaq";
- dəyərlər diapazonunda (sətir və sütunların kəsişməsində) yalnız nömrələri ehtiva edir, çünki pivot cədvəli onlara mütləq bir növ toplama funksiyasını tətbiq edəcək (cəm, orta və s.) və bu mətnlə işləməyəcək
Bütün bu şərtlər yerinə yetirilərsə, orijinal cədvəlimizə bənzəyən pivot cədvəli qurmaq üçün onu (orijinal) çarpaz cədvəldən düz birinə (normallaşdırılmış) genişləndirmək lazımdır. Bunu etmək üçün ən asan yol 2016-cı ildən bəri Excel-də quraşdırılmış güclü məlumat dəyişdirmə vasitəsi olan Power Query əlavəsidir.
Bunlar aşağıdakılardır:
- Gəlin cədvəli “ağıllı” dinamik əmrə çevirək Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format).
- Komanda ilə Power Query-ə yüklənir Məlumat - Cədvəldən / Aralıqdan (Məlumat - Cədvəldən / Aralıqdan).
- Cəmi ilə xətti süzgəcdən keçiririk (xülasə öz yekunlarına malik olacaq).
- Birinci sütun başlığına sağ klikləyin və seçin Digər sütunları yığışdırın (Digər Sütunları Açın). Bütün seçilməyən sütunlar ikiyə çevrilir - işçinin adı və onun göstəricisinin dəyəri.
- Sütunun sütuna daxil olan cəmi ilə süzülməsi atribut.
- Komanda ilə nəticələnən düz (normallaşdırılmış) cədvələ uyğun olaraq pivot cədvəli qururuq Əsas səhifə — Bağlayın və Yükləyin — Bağlayın və Yükləyin… (Ev - Bağla və Yüklə - Bağla və Yüklə...).
İndi siz pivot cədvəllərində mövcud olan sütunları süzgəcdən keçirmək qabiliyyətindən istifadə edə bilərsiniz – adların və elementlərin qarşısındakı adi işarələr İmza Filtrləri (Etiket Filtrləri) or Dəyər üzrə filtrlər (Dəyər Filtrləri):
Və əlbəttə ki, məlumatları dəyişdirərkən, sorğumuzu və xülasəmizi klaviatura qısayolu ilə yeniləməlisiniz. Ctrl+Dayandırmaq+F5 və ya komanda Məlumat - Hamısını Yeniləyin (Məlumat - Hamısını Yeniləyin).
Metod 3. VBA-da makro
Asanlıqla gördüyünüz kimi, əvvəlki bütün üsullar tam olaraq filtrlənmir - biz orijinal siyahıda sütunları gizlətmirik, lakin orijinaldan verilmiş sütun dəsti ilə yeni bir cədvəl yaradırıq. Əgər mənbə məlumatında sütunları süzgəcdən keçirmək (gizlətmək) tələb olunursa, o zaman əsaslı şəkildə fərqli yanaşma, yəni makro lazımdır.
Tutaq ki, cədvəlin başlığında menecerin adı A4 sarı xanasında göstərilən maskanı təmin edən sütunları süzgəcdən keçirtmək istəyirik, məsələn, “A” hərfi ilə başlayır (yəni “Anna” və “Artur”u alın. " nəticə olaraq).
Birinci üsulda olduğu kimi, biz əvvəlcə köməkçi sıra sırasını həyata keçiririk, burada hər bir xanada meyarımız düsturla yoxlanılacaq və görünən və gizli sütunlar üçün müvafiq olaraq TRUE və ya FALSE məntiqi dəyərləri göstəriləcəkdir:
Sonra sadə bir makro əlavə edək. Vərəq sekmesini sağ vurun və əmri seçin mənbə (Mənbə kodu). Aşağıdakı VBA kodunu kopyalayın və açılan pəncərəyə yapışdırın:
Private Sub Worksheet_Change(ByVal Target As Range) Əgər Hədəf.Ünvan = "$A$4" Əgər Aralıqdakı Hər Bir Hüceyrə üçün("D2:O2") Əgər xana = Doğrudursa, xana.Bütün Sütun.Gizli = Yanlış Başqa xana.Bütün Sütun.Gizli = True End If Next xana Əgər End Sub Onun məntiqi belədir:
- Ümumiyyətlə, bu hadisə idarəedicisidir İş vərəqi_Dəyişdir, yəni bu makro cari vərəqin istənilən xanasına edilən hər hansı dəyişiklikdə avtomatik olaraq işləyəcək.
- Dəyişən xanaya istinad həmişə dəyişəndə olacaq Hədəf.
- Əvvəlcə istifadəçinin (A4) kriteriya ilə xananı dəqiq dəyişdirdiyini yoxlayırıq – bunu operator edir. if.
- Sonra dövrə başlayır Hər biri üçün… hər sütun üçün TRUE / FALSE göstərici dəyərləri ilə boz xanalar (D2:O2) üzərində təkrarlamaq.
- Növbəti boz xananın dəyəri TRUE (doğru) olarsa, o zaman sütun gizlədilmir, əks halda onu gizlədirik (xüsusiyyət Gizli).
- Office 365-dən dinamik massiv funksiyaları: FILTER, SORT və UNIC
- Power Query istifadə edərək çoxsətirli başlıqlı pivot cədvəli
- Makrolar nədir, onları necə yaratmaq və istifadə etmək