Iffiltrar tal-kolonna orizzontali f'Excel

Jekk m'intix pjuttost utent novizzi, allura trid tkun diġà ndunat li 99% ta 'kollox f'Excel huwa ddisinjat biex jaħdem ma' tabelli vertikali, fejn parametri jew attributi (oqsma) jgħaddu mill-kolonni, u tinsab informazzjoni dwar oġġetti jew avvenimenti fil-linji. Pivot tables, subtotali, ikkupjar ta 'formuli bi klikk doppja - kollox huwa mfassal speċifikament għal dan il-format tad-dejta.

Madankollu, m'hemm l-ebda regoli mingħajr eċċezzjonijiet u bi frekwenza pjuttost regolari jien mistoqsi x'għandi nagħmel jekk tabella b'orjentazzjoni semantika orizzontali, jew tabella fejn ringieli u kolonni għandhom l-istess piż fit-tifsira, tiltaqa 'ma' fix-xogħol:

Iffiltrar tal-kolonna orizzontali f'Excel

U jekk Excel għadu jaf kif issolvi orizzontalment (bil-kmand Dejta – Sort – Għażliet – Issortja kolonni), allura s-sitwazzjoni bil-filtrazzjoni hija agħar - sempliċement m'hemm l-ebda għodda integrata għall-iffiltrar ta 'kolonni, mhux ringieli f'Excel. Għalhekk, jekk tkun iffaċċjat b'tali kompitu, ser ikollok toħroġ b'soluzzjonijiet ta 'diversi gradi ta' kumplessità.

Metodu 1. Funzjoni ġdida FILTER

Jekk int fuq il-verżjoni l-ġdida ta' Excel 2021 jew abbonament ta' Excel 365, tista' tieħu vantaġġ mill-karatteristika li għadha kif ġiet introdotta Iffiltra (FILTRI), li jistgħu jiffiltraw id-dejta tas-sors mhux biss minn ringieli, iżda wkoll minn kolonni. Biex taħdem, din il-funzjoni teħtieġ awżiljarju orizzontali ta 'array-ringiela ta' dimensjoni waħda, fejn kull valur (VERU jew FALZ) jiddetermina jekk nurux jew, bil-maqlub, naħbi l-kolonna li jmiss fit-tabella.

Ejja nżidu l-linja li ġejja fuq it-tabella tagħna u ikteb l-istatus ta 'kull kolonna fiha:

Iffiltrar tal-kolonna orizzontali f'Excel

  • Ejja ngħidu li dejjem irridu nuru l-ewwel u l-aħħar kolonni (headers u totali), għalhekk għalihom fl-ewwel u l-aħħar ċelluli tal-firxa nissettjaw il-valur = VERU.
  • Għall-kolonni li jifdal, il-kontenut taċ-ċelloli korrispondenti se jkun formula li tiċċekkja l-kundizzjoni li għandna bżonn billi tuża funzjonijiet И (U) or OR (JEW). Per eżempju, li t-total huwa fil-medda minn 300 sa 500.

Wara dan, jibqa 'biss li tuża l-funzjoni Iffiltra biex tagħżel kolonni li fuqhom l-array awżiljarju tagħna għandu valur VERU:

Iffiltrar tal-kolonna orizzontali f'Excel

Bl-istess mod, tista 'tiffiltra kolonni minn lista partikolari. F'dan il-każ, il-funzjoni tgħin COUNTIF (COUNTIF), li jiċċekkja n-numru ta' okkorrenzi tal-isem tal-kolonna li jmiss mill-intestatura tat-tabella fil-lista permessa:

Iffiltrar tal-kolonna orizzontali f'Excel

Metodu 2. Pivot table minflok dik tas-soltu

Bħalissa, Excel inkorpora filtrazzjoni orizzontali b'kolonni biss f'tabelli tal-pern, għalhekk jekk jirnexxielna nikkonverti t-tabella oriġinali tagħna f'tabella tal-pern, nistgħu nużaw din il-funzjonalità integrata. Biex tagħmel dan, it-tabella tas-sors tagħna trid tissodisfa l-kundizzjonijiet li ġejjin:

  • għandhom linja ta 'header ta' linja waħda "korretta" mingħajr ċelluli vojta u magħquda - inkella mhux se taħdem biex tibni tabella tal-pern;
  • ma fihomx duplikati fit-tikketti tar-ringieli u l-kolonni - dawn se "kollassaw" fis-sommarju f'lista ta 'valuri uniċi biss;
  • fihom biss numri fil-firxa ta 'valuri (fl-intersezzjoni tar-ringieli u l-kolonni), minħabba li t-tabella tal-pern definittivament tapplika xi tip ta' funzjoni ta 'aggregazzjoni għalihom (somma, medja, eċċ.) U dan mhux se jaħdem mat-test

Jekk dawn il-kundizzjonijiet kollha jiġu ssodisfati, allura sabiex tinbena tabella tal-pern li tidher bħat-tabella oriġinali tagħna, hija (dik oriġinali) teħtieġ li tiġi estiża mill-crosstab għal waħda ċatta (normalizzata). U l-eħfef mod biex tagħmel dan huwa bil-Power Query add-in, għodda qawwija ta 'trasformazzjoni tad-dejta mibnija f'Excel mill-2016. 

Dawn huma:

  1. Ejja nikkonverti t-tabella fi kmand dinamiku "intelliġenti". Home – Format bħala tabella (Id-dar — Format bħala Tabella).
  2. Tagħbija f'Power Query bil-kmand Dejta – Minn Tabella / Medda (Dejta – Minn Tabella / Medda).
  3. Aħna niffiltraw il-linja bit-totali (is-sommarju se jkollu t-totali tiegħu stess).
  4. Ikklikkja bil-lemin fuq l-ewwel intestatura tal-kolonna u agħżel Uncollapse kolonni oħra (Unpivot Kolonni Oħrajn). Il-kolonni kollha mhux magħżula huma kkonvertiti fi tnejn - l-isem tal-impjegat u l-valur tal-indikatur tiegħu.
  5. Iffiltrar tal-kolonna bit-totali li marru fil-kolonna Attribut.
  6. Aħna nibnu tabella tal-pern skont it-tabella ċatta (normalizzata) li tirriżulta bil-kmand Id-dar — Agħlaq u Tagħbija — Agħlaq u Tagħbija fi... (Dar — Agħlaq u Tagħbija — Agħlaq u Tagħbija għal...).

Issa tista' tuża l-abbiltà li tiffiltra l-kolonni disponibbli fit-tabelli tal-pern - il-marki ta' kontroll tas-soltu quddiem l-ismijiet u l-oġġetti Filtri tal-Firem (Filtri tat-Tikketti) or Filtri skond il-valur (Filtri tal-Valur):

Iffiltrar tal-kolonna orizzontali f'Excel

U ovvjament, meta tbiddel id-dejta, ikollok bżonn taġġorna l-mistoqsija tagħna u s-sommarju b'shortcut tat-tastiera Ctrl+Alt+F5 jew tim Data – Aġġorna Kollha (Data — Aġġorna Kollha).

Metodu 3. Makro f'VBA

Il-metodi kollha ta 'qabel, kif tistgħu taraw faċilment, mhumiex eżattament iffiltrar - aħna ma naħbux il-kolonni fil-lista oriġinali, iżda niffurmaw tabella ġdida b'sett partikolari ta' kolonni minn dak oriġinali. Jekk ikun meħtieġ li jiġu ffiltrati (taħbi) il-kolonni fid-dejta tas-sors, allura huwa meħtieġ approċċ fundamentalment differenti, jiġifieri, makro.

Ejja ngħidu li rridu niffiltraw kolonni fuq il-fly fejn l-isem tal-maniġer fl-intestatura tat-tabella jissodisfa l-maskra speċifikata fiċ-ċellula safra A4, pereżempju, tibda bl-ittra "A" (jiġifieri, tikseb "Anna" u "Arthur". " b'riżultat ta 'dan). 

Bħal fl-ewwel metodu, l-ewwel nimplimentaw ringiela ta’ firxa awżiljarja, fejn f’kull ċellula l-kriterju tagħna jiġi ċċekkjat b’formula u l-valuri loġiċi VERU jew FALZ jintwerew għal kolonni viżibbli u moħbija, rispettivament:

Iffiltrar tal-kolonna orizzontali f'Excel

Imbagħad ejja żid makro sempliċi. Ikklikkja bil-lemin fuq it-tab tal-folja u agħżel kmand sors (Kodiċi tas-sors). Ikkopja u waħħal il-kodiċi VBA li ġej fit-tieqa li tiftaħ:

Private Sub Worksheet_Change(ByVal Target As Range) Jekk Target.Address = "$A$4" Imbagħad Għal Kull ċellula Fil-Medda ("D2:O2") Jekk cell = Veru Imbagħad cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub

Il-loġika tagħha hija kif ġej:

  • B'mod ġenerali, dan huwa handler tal-avvenimenti Worksheet_Change, jiġifieri din il-makro taħdem awtomatikament fuq kwalunkwe bidla għal kwalunkwe ċellula fuq il-folja attwali.
  • Ir-referenza għaċ-ċellula mibdula dejjem tkun fil-varjabbli Mira.
  • L-ewwel, niċċekkjaw li l-utent biddel eżattament iċ-ċellula bil-kriterju (A4) - dan isir mill-operatur if.
  • Imbagħad iċ-ċiklu jibda Għal kull… biex itenni fuq ċelloli griżi (D2:O2) b'valuri indikaturi VERU / FALZ għal kull kolonna.
  • Jekk il-valur taċ-ċellula griża li jmiss huwa VERU (veru), allura l-kolonna mhix moħbija, inkella naħbuha (proprjetà Moħbija).

  •  Funzjonijiet ta' firxa dinamika minn Office 365: FILTER, SORT, u UNIC
  • Pivot table b'header multiline bl-użu ta 'Power Query
  • X'inhuma macros, kif toħloqhom u tużahom

 

Ħalli Irrispondi