Filter kolom horisontal ing Excel

Yen sampeyan dudu pangguna anyar, mula sampeyan kudu ngerti manawa 99% kabeh ing Excel dirancang kanggo nggarap tabel vertikal, ing ngendi paramèter utawa atribut (lapangan) ngliwati kolom, lan informasi babagan obyek utawa acara dumunung. ing baris. Tabel pangsi, subtotal, nyalin rumus kanthi klik kaping pindho - kabeh wis dirancang khusus kanggo format data iki.

Nanging, ora ana aturan tanpa pangecualian lan kanthi frekuensi sing cukup reguler, aku ditakoni apa sing kudu ditindakake yen meja kanthi orientasi semantik horisontal, utawa tabel ing ngendi larik lan kolom duwe bobot sing padha, ditemokake ing karya:

Lan yen Excel isih ngerti carane ngurutake horisontal (kanthi perintah Data - Urut - Pilihan - Urut kolom), banjur kahanan nyaring luwih elek - ora ana alat sing dibangun kanggo nyaring kolom, dudu baris ing Excel. Dadi, yen sampeyan ngadhepi tugas kasebut, sampeyan kudu nggawe solusi sing beda-beda tingkat kerumitan.

Cara 1. Fungsi FILTER anyar

Yen sampeyan nggunakake versi anyar Excel 2021 utawa langganan Excel 365, sampeyan bisa njupuk kauntungan saka fitur sing mentas dikenalake. Nyaring (FILTER), sing bisa nyaring data sumber ora mung miturut larik, nanging uga miturut kolom. Kanggo bisa, fungsi iki mbutuhake tambahan horisontal larik larik siji-dimensi, ngendi saben nilai (TRUE utawa FALSE) nemtokake kita nuduhake utawa, Kosok baline, ndhelikake kolom sabanjuré ing meja.

Ayo nambahake baris ing ngisor iki ing ndhuwur tabel lan tulis status saben kolom ing kono:

• Ayo dadi ngomong kita tansah pengin nampilake kolom pisanan lan pungkasan (header lan total), supaya kanggo wong-wong mau ing sel pisanan lan pungkasan saka Uploaded kita nyetel Nilai = TRUE.
• Kanggo kolom sing isih ana, isi sel sing cocog bakal dadi rumus sing mriksa kondisi sing dibutuhake nggunakake fungsi. И (lan) or OR (UTAWA). Contone, yen total ana ing kisaran saka 300 nganti 500.

Sawise iku, tetep mung nggunakake fungsi kasebut Nyaring kanggo milih kolom ing ndhuwur sing array tambahan duweni nilai TRUE:

Kajaba iku, sampeyan bisa nyaring kolom miturut dhaptar sing diwenehake. Ing kasus iki, fungsi bakal mbantu COUNTIF (COUNTIF), sing mriksa nomer kedadeyan jeneng kolom sabanjure saka header tabel ing dhaptar sing diidini:

Cara 2. Tabel pangsi tinimbang sing biasa

Saiki, Excel duwe panyaring horisontal kanthi kolom mung ing tabel pivot, dadi yen kita bisa ngowahi tabel asli dadi tabel pivot, kita bisa nggunakake fungsi sing dibangun. Kanggo nindakake iki, tabel sumber kita kudu nyukupi syarat ing ngisor iki:

• duwe baris header siji-baris sing "bener" tanpa sel kosong lan digabungake - yen ora, ora bakal bisa mbangun tabel pivot;
• ora ngemot duplikat ing label baris lan kolom - bakal "ambruk" ing ringkesan dadi dhaptar mung nilai unik;
• mung ngemot angka ing sawetara nilai (ing persimpangan larik lan kolom), amarga tabel pivot mesthi bakal nggunakake sawetara fungsi aggregating kanggo wong-wong mau (jumlah, rata-rata, lan sapiturute) lan iki ora bakal bisa digunakake karo teks.

Yen kabeh syarat kasebut kapenuhan, mula kanggo nggawe tabel pivot sing katon kaya tabel asli, mula (sing asli) kudu ditambahi saka tab silang dadi rata (dinormalisasi). Lan cara paling gampang kanggo nindakake iki yaiku kanthi nambahake Power Query, alat transformasi data sing kuat sing dibangun ing Excel wiwit taun 2016. 

Iki minangka:

1. Ayo dadi ngowahi tabel dadi "pinter" printah dinamis Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel).
2. Loading menyang Power Query karo printah Data – Saka Tabel / Range (Data – Saka Tabel / Range).
3. Kita nyaring baris kanthi total (ringkesan bakal duwe total dhewe).
4. Klik-tengen ing judhul kolom pisanan banjur pilih Uncollapse kolom liyane (Unpivot Kolom Liyane). Kabeh kolom sing ora dipilih diowahi dadi loro - jeneng karyawan lan nilai indikator.
5. Nyaring kolom kanthi total sing mlebu ing kolom kasebut ngubungake.
6. Kita mbangun tabel poros miturut asil flat (normal) tabel karo printah Ngarep — Nutup lan Muat — Nutup lan Muat ing… (Ngarep - Tutup & Muat - Tutup & Muat menyang…).

Saiki sampeyan bisa nggunakake kemampuan kanggo nyaring kolom sing kasedhiya ing tabel pivot - tandha mriksa biasa ing ngarep jeneng lan item. Filter-filter tandha (Saringan Label) or Filter-filter miturut nilai (Saringan Nilai):

Lan mesthi, nalika ngganti data, sampeyan kudu nganyari pitakon lan ringkesan kanthi trabasan keyboard Ctrl+Alt+F5 utawa tim Data - Refresh Kabeh (Data - Refresh Kabeh).

Cara 3. Macro ing VBA

Kabeh cara sadurunge, kaya sing bisa dideleng kanthi gampang, ora persis nyaring - kita ora ndhelikake kolom ing dhaptar asli, nanging nggawe tabel anyar kanthi set kolom saka sing asli. Yen perlu kanggo nyaring (ndhelikake) kolom ing data sumber, banjur pendekatan dhasar beda dibutuhake, yaiku, makro.

Upaminipun kita pengin nyaring kolom ing fly ngendi jeneng manager ing header meja gawe marem topeng kasebut ing sel kuning A4, contone, diwiwiti karo huruf "A" (yaiku, njaluk "Anna" lan "Arthur". " akibate). 

Kaya ing cara pisanan, kita pisanan ngleksanakake baris tambahan tambahan, ing ngendi ing saben sel kriteria kita bakal dicenthang kanthi rumus lan nilai logis TRUE utawa FALSE bakal ditampilake kanggo kolom sing katon lan didhelikake:

Banjur ayo nambah makro sing prasaja. Klik-tengen ing tab sheet banjur pilih printah sumber (kode sumber). Salin lan tempel kode VBA ing ngisor iki menyang jendhela sing mbukak:

Private Sub Worksheet_Change(ByVal Target Minangka Range) Yen Target.Address = "$A$4" Banjur Kanggo Saben sel Ing Range("D2:O2") Yen sel = True Banjur cell.EntireColumn.Hidden = Palsu sel liyane.EntireColumn.Hidden = True End Yen Sabanjure sel End Yen End Sub  

Logikane kaya ing ngisor iki:

• Umumé, iki minangka pawang acara Lembar kerja_Ganti, IE makro iki bakal kanthi otomatis mbukak ing sembarang owah-owahan kanggo sel ing sheet saiki.
• Referensi menyang sel sing diganti bakal tansah ana ing variabel target.
• Pisanan, kita priksa manawa pangguna wis ngganti persis sel kanthi kriteria (A4) - iki ditindakake dening operator. if.
• Banjur siklus diwiwiti Kanggo saben… kanggo ngulang sel abu-abu (D2:O2) kanthi nilai indikator TRUE / FALSE kanggo saben kolom.
• Yen nilai sel abu-abu sabanjure TRUE (bener), banjur kolom ora didhelikake, yen ora, kita ndhelikake (properti Didhelikake).

•  Fungsi array dinamis saka Office 365: FILTER, SORT, lan UNIC
• Tabel pivot kanthi header multiline nggunakake Power Query
• Apa macro, carane nggawe lan nggunakake