Nyaring kolom horisontal dina Excel

Upami anjeun sanés pangguna novice, maka anjeun kedah perhatikeun yén 99% tina sadayana dina Excel dirancang pikeun dianggo sareng tabel nangtung, dimana parameter atanapi atribut (widang) ngalangkungan kolom, sareng inpormasi ngeunaan objék atanapi acara ayana. dina garis. Pivot tabel, subtotals, nyalin rumus ku klik ganda - sagalana geus tailored husus pikeun format data ieu.

Nanging, henteu aya aturan tanpa pengecualian sareng kalayan frekuensi anu cukup teratur kuring ditaroskeun naon anu kudu dilakukeun upami méja kalayan orientasi semantis horisontal, atanapi méja dimana barisan sareng kolom gaduh beurat anu sami dina artos, sumping dina padamelan:

Sareng upami Excel masih terang kumaha nyortir sacara horisontal (kalayan paréntah Data - Urut - Pilihan - Urut kolom), teras kaayaan nyaring langkung parah - teu aya alat anu diwangun pikeun nyaring kolom, sanés barisan dina Excel. Janten, upami anjeun disanghareupan ku tugas sapertos kitu, anjeun kedah ngadamel jalan-jalan anu béda-béda tingkat pajeulitna.

Métode 1. Fungsi FILTER anyar

Upami anjeun nganggo vérsi énggal Excel 2021 atanapi langganan Excel 365, anjeun tiasa ngamangpaatkeun fitur anu nembé diwanohkeun. saringan (FILTER), nu bisa nyaring data sumber teu ukur ku baris, tapi ogé ku kolom. Pikeun digawé, fungsi ieu merlukeun hiji bantu horizontal hiji diménsi Asép Sunandar Sunarya-baris, dimana unggal nilai (BENER atawa PALSU) nangtukeun naha urang némbongkeun atawa, Sabalikna, nyumputkeun kolom salajengna dina tabél.

Hayu urang tambahkeun garis ieu di luhur méja urang sareng tuliskeun status unggal kolom di dinya:

• Sebutkeun urang sok hoyong nampilkeun kolom kahiji sareng panungtung (header sareng total), ku kituna pikeun aranjeunna dina sél mimiti sareng panungtung tina Asép Sunandar Sunarya kami nyetél nilai = BENER.
• Pikeun kolom sésana, eusi sél pakait bakal rumus nu mariksa kaayaan urang kudu ngagunakeun fungsi. И (jeung) or OR (ATAWA). Salaku conto, jumlahna aya dina kisaran 300 dugi ka 500.

Saatos éta, éta ngan ukur nganggo fungsina saringan pikeun milih kolom di luhur anu susunan bantu urang ngagaduhan nilai BENER:

Nya kitu, anjeun tiasa nyaring kolom ku daptar anu dipasihkeun. Dina hal ieu, fungsi bakal mantuan COUNTIF (COUNTIF), nu mariksa jumlah kajadian tina ngaran kolom salajengna tina lulugu tabel dina daptar diwenangkeun:

Métode 2. Pivot tabel tinimbang nu biasa

Ayeuna, Excel geus diwangun-di nyaring horisontal ku kolom ukur dina tabel pangsi, jadi lamun urang ngatur pikeun ngarobah tabel aslina urang kana tabel pangsi, urang bisa ngagunakeun fungsi diwangun-di ieu. Jang ngalampahkeun ieu, tabel sumber urang kedah nyugemakeun kaayaan di handap ieu:

• boga "bener" garis lulugu hiji-garis tanpa sél kosong tur dihijikeun - disebutkeun eta moal jalan mun ngawangun tabel pangsi;
• ulah ngandung duplikat dina labél baris jeung kolom - aranjeunna bakal "runtuh" ​​dina kasimpulan kana daptar ngan nilai unik;
• ngan ukur ngandung angka dina kisaran nilai (di simpang baris sareng kolom), sabab tabel pangsi pasti bakal nerapkeun sababaraha jinis fungsi agrégat ka aranjeunna (jumlah, rata-rata, jsb) sareng ieu moal tiasa dianggo sareng téks.

Upami sadayana kaayaan ieu kacumponan, teras pikeun ngawangun méja pangsi anu katingali sapertos méja asli urang, éta (aslina) kedah dilegaan tina tab silang janten datar (dinormalisasi). Sareng cara panggampangna pikeun ngalakukeun ieu nyaéta ku Power Query add-in, alat transformasi data anu kuat anu diwangun kana Excel saprak 2016. 

Ieu nyaéta:

1. Hayu urang ngarobah tabel kana paréntah dinamis "pinter". Imah - Format salaku méja (Imah - Format salaku Méja).
2. Loading kana Power Query kalawan paréntah Data – Tina Tabél / Rentang (Data – Tina Tabél / Rentang).
3. Urang nyaring garis kalawan totalna (ringkesan bakal boga total sorangan).
4. Klik katuhu dina judul kolom kahiji tur pilih Uncollapse kolom séjén (Lepaskeun Pivot Kolom Lain). Sadaya kolom anu henteu dipilih dirobih janten dua - nami karyawan sareng nilai indikatorna.
5. Nyaring kolom kalayan total anu asup kana kolom atribut.
6. Urang ngawangun tabel pangsi nurutkeun kana hasil datar (dinormalisasi) tabel kalawan paréntah Imah - Tutup sareng Muatan - Tutup sareng Muat dina… (Imah - Tutup & Muat - Tutup & Muat ka…).

Ayeuna anjeun tiasa nganggo kamampuan pikeun nyaring kolom anu sayogi dina tabel pangsi - tanda centang biasa di payuneun nami sareng barang. Saringan Signature (Saringan Label) or Saringan ku nilai (Saringan Nilai):

Sareng tangtosna, nalika ngarobih data, anjeun kedah ngapdet pamundut kami sareng kasimpulan nganggo potong kompas keyboard Ctrl+Alt+F5 atawa tim Data - Refresh Sadaya (Data - Refresh Sadaya).

Métode 3. Macro dina VBA

Kabéh métode saméméhna, anjeun bisa kalayan gampang tingali, teu persis nyaring - urang teu nyumputkeun kolom dina daptar aslina, tapi ngabentuk tabel anyar kalawan susunan kolom tina aslina. Upami diperyogikeun pikeun nyaring (nyumputkeun) kolom dina data sumber, maka pendekatan anu béda-béda diperyogikeun, nyaéta makro.

Anggap urang hoyong nyaring kolom dina laleur dimana nami manajer dina header tabel nyugemakeun topéng anu ditunjuk dina sél konéng A4, contona, dimimitian ku hurup "A" (nyaéta, kéngingkeun "Anna" sareng "Arthur". " salaku hasilna). 

Sapertos dina metodeu anu munggaran, urang mimiti nerapkeun barisan bantu, dimana dina unggal sél kriteria urang bakal dipariksa ku rumus sareng nilai logis BENER atanapi PALSU bakal ditingalikeun pikeun kolom anu katingali sareng disumputkeun, masing-masing:

Lajeng hayu urang tambahkeun hiji makro basajan. -Klik katuhu dina tab lambar tur pilih paréntah sumber (Kode sumber). Salin sareng témpél kodeu VBA di handap ieu kana jandela anu muka:

Swasta Sub Worksheet_Change(ByVal Target Salaku Rentang) Lamun Target.Address = "$A$4" Lajeng Pikeun Unggal sél Dina Range ("D2: O2") Lamun sél = Leres Lajeng cell.EntireColumn.Hidden = Palsu Cell.EntireColumn.Hidden = Leres Tungtung Lamun salajengna sél Tungtung Lamun Tungtung Sub  

Logika na nyaéta kieu:

• Sacara umum, ieu mangrupikeun panangan acara Lembar Kerja_Robah, nyaéta makro ieu sacara otomatis bakal dijalankeun dina parobahan naon waé kana sél mana waé dina lambaran ayeuna.
• Rujukan kana sél anu dirobih bakal salawasna aya dina variabel tujuan.
• Kahiji, urang pariksa yén pamaké geus robah persis sél jeung kriteria (A4) - ieu dipigawé ku operator. if.
• Lajeng siklus dimimitian Pikeun Unggal… pikeun iterate leuwih sél abu (D2: O2) kalawan nilai indikator BENER / SALAH pikeun tiap kolom.
• Upami nilai sél abu salajengna BENER (leres), maka kolom henteu disumputkeun, upami henteu urang nyumputkeunana (harta disumputkeun).

•  Fungsi array dinamis tina Office 365: FILTER, SORT, sareng UNIC
• Tabel pangsi sareng header multiline nganggo Power Query
• Naon macros, kumaha carana nyieun sarta ngagunakeun éta