Hidlo colofnau llorweddol yn Excel

Os nad ydych chi'n ddefnyddiwr newydd, yna mae'n rhaid eich bod eisoes wedi sylwi bod 99% o bopeth yn Excel wedi'i gynllunio i weithio gyda thablau fertigol, lle mae paramedrau neu briodoleddau (meysydd) yn mynd trwy'r colofnau, a lleolir gwybodaeth am wrthrychau neu ddigwyddiadau. yn y llinellau. Tablau colyn, is-gyfansymiau, copïo fformiwlâu gyda chlic dwbl - mae popeth wedi'i deilwra'n benodol ar gyfer y fformat data hwn.

Fodd bynnag, nid oes unrhyw reolau heb eithriadau ac yn eithaf rheolaidd gofynnir i mi beth i'w wneud pe bai tabl gyda chyfeiriadedd semantig llorweddol, neu dabl gyda rhesi a cholofnau â'r un pwysau o ran ystyr, yn dod ar draws yn y gwaith:

Ac os yw Excel yn dal i wybod sut i ddidoli'n llorweddol (gyda'r gorchymyn Data – Trefnu – Opsiynau – Trefnu colofnau), yna mae'r sefyllfa gyda hidlo yn waeth - yn syml, nid oes unrhyw offer adeiledig ar gyfer hidlo colofnau, nid rhesi yn Excel. Felly, os ydych chi'n wynebu tasg o'r fath, bydd yn rhaid i chi feddwl am atebion o wahanol raddau o gymhlethdod.

Dull 1. Swyddogaeth FILTER newydd

Os ydych chi ar y fersiwn newydd o Excel 2021 neu danysgrifiad Excel 365, gallwch chi fanteisio ar y nodwedd sydd newydd ei chyflwyno Hidlo (HILYDD), a all hidlo'r data ffynhonnell nid yn unig yn ôl rhesi, ond hefyd gan golofnau. Er mwyn gweithio, mae'r swyddogaeth hon yn gofyn am arae-aráe un-dimensiwn llorweddol ategol, lle mae pob gwerth (CYWIR neu ANGHYWIR) yn pennu a ydym yn dangos neu, i'r gwrthwyneb, yn cuddio'r golofn nesaf yn y tabl.

Gadewch i ni ychwanegu'r llinell ganlynol uwchben ein tabl ac ysgrifennu statws pob colofn ynddo:

• Gadewch i ni ddweud ein bod bob amser eisiau arddangos y colofnau cyntaf ac olaf (penawdau a chyfansymiau), felly ar eu cyfer yng nghelloedd cyntaf ac olaf yr arae rydym yn gosod y gwerth = GWIR.
• Ar gyfer y colofnau sy'n weddill, bydd cynnwys y celloedd cyfatebol yn fformiwla sy'n gwirio'r cyflwr sydd ei angen arnom gan ddefnyddio swyddogaethau И (A) or OR (OR). Er enghraifft, bod y cyfanswm yn yr ystod o 300 i 500.

Ar ôl hynny, mae'n parhau i fod yn unig i ddefnyddio'r swyddogaeth Hidlo i ddewis colofnau y mae gan ein cyfres ategol WIR werth uwch eu pennau:

Yn yr un modd, gallwch hidlo colofnau yn ôl rhestr benodol. Yn yr achos hwn, bydd y swyddogaeth yn helpu COUNTIF (COUNTIF), sy'n gwirio nifer y digwyddiadau o enw'r golofn nesaf o bennawd y tabl yn y rhestr a ganiateir:

Dull 2. Tabl colyn yn lle'r un arferol

Ar hyn o bryd, mae Excel wedi ymgorffori hidlo llorweddol fesul colofn yn unig mewn tablau colyn, felly os llwyddwn i drosi ein tabl gwreiddiol yn dabl colyn, gallwn ddefnyddio'r swyddogaeth adeiledig hon. I wneud hyn, rhaid i'n tabl ffynhonnell fodloni'r amodau canlynol:

• bod â phennawd un llinell “gywir” heb gelloedd gwag ac unedig – fel arall ni fydd yn gweithio i adeiladu bwrdd colyn;
• nad ydynt yn cynnwys dyblygiadau yn y labeli rhesi a cholofnau – byddant yn “cwympo” yn y crynodeb i restr o werthoedd unigryw yn unig;
• cynnwys rhifau yn unig yn yr ystod o werthoedd (ar groesffordd rhesi a cholofnau), oherwydd bydd y tabl colyn yn bendant yn cymhwyso rhyw fath o swyddogaeth agregu iddynt (swm, cyfartaledd, ac ati) ac ni fydd hyn yn gweithio gyda'r testun

Os bodlonir yr holl amodau hyn, yna er mwyn adeiladu bwrdd colyn sy'n edrych fel ein bwrdd gwreiddiol, bydd angen ei ehangu (yr un gwreiddiol) o'r crosstab i un gwastad (wedi'i normaleiddio). A'r ffordd hawsaf o wneud hyn yw gyda'r ategyn Power Query, sef offeryn trawsnewid data pwerus sydd wedi'i ymgorffori yn Excel ers 2016. 

Y rhain yw:

1. Gadewch i ni drosi'r tabl yn orchymyn deinamig “smart”. Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl).
2. Llwytho i Power Query gyda'r gorchymyn Data - O Dabl / Ystod (Data - O Dabl / Ystod).
3. Rydym yn hidlo'r llinell gyda'r cyfansymiau (bydd gan y crynodeb ei gyfansymiau ei hun).
4. De-gliciwch ar bennawd y golofn gyntaf a dewiswch Dad-gwympo colofnau eraill (Unpivot Colofnau Eraill). Trosir pob colofn nas dewiswyd yn ddwy – enw'r gweithiwr a gwerth ei ddangosydd.
5. Hidlo'r golofn gyda'r cyfansymiau a aeth i'r golofn Nodwedd.
6. Rydym yn adeiladu tabl colyn yn ôl y tabl fflat (normaleiddio) canlyniadol gyda'r gorchymyn Cartref - Cau a Llwythwch - Cau a Llwythwch i mewn… (Cartref - Cau a Llwytho - Cau a Llwytho i ...).

Nawr gallwch chi ddefnyddio'r gallu i hidlo colofnau sydd ar gael mewn tablau colyn - y marciau gwirio arferol o flaen yr enwau a'r eitemau Hidlau Llofnod (Hidlyddion Label) or Hidlau yn ôl gwerth (Hidlyddion Gwerth):

Ac wrth gwrs, wrth newid y data, bydd angen i chi ddiweddaru ein hymholiad a'r crynodeb gyda llwybr byr bysellfwrdd Ctrl+Alt+F5 neu dîm Data – Adnewyddu Pawb (Data - Adnewyddu Pawb).

Dull 3. Macro yn VBA

Nid yw'r holl ddulliau blaenorol, fel y gallwch weld yn hawdd, yn hidlo'n union - nid ydym yn cuddio'r colofnau yn y rhestr wreiddiol, ond yn ffurfio tabl newydd gyda set benodol o golofnau o'r un gwreiddiol. Os oes angen hidlo (cuddio) y colofnau yn y data ffynhonnell, yna mae angen ymagwedd sylfaenol wahanol, sef, macro.

Tybiwch ein bod am hidlo colofnau ar y hedfan lle mae enw'r rheolwr ym mhennyn y tabl yn bodloni'r mwgwd a nodir yn y gell felen A4, er enghraifft, yn dechrau gyda'r llythyren "A" (hynny yw, cael "Anna" ac "Arthur " fel canlyniad). 

Fel yn y dull cyntaf, rydym yn gyntaf yn gweithredu rhes-ystod ategol, lle ym mhob cell bydd ein maen prawf yn cael ei wirio gan fformiwla a bydd y gwerthoedd rhesymegol GWIR neu ANGHYWIR yn cael eu harddangos ar gyfer colofnau gweladwy a chudd, yn y drefn honno:

Yna gadewch i ni ychwanegu macro syml. De-gliciwch ar y tab taflen a dewis gorchymyn ffynhonnell (Cod ffynhonnell). Copïwch a gludwch y cod VBA canlynol i'r ffenestr sy'n agor:

Is-Daflen Waith Breifat_Change(ByVal Target As Range) If Target.Address = "$A$4" Yna Ar gyfer Pob cell Mewn Ystod ("D2:O2") Os cell = Gwir Yna cell.EntireColumn.Hidden = Ffug Arall cell.EntireColumn.Hidden = Diwedd Gwir Os Diwedd cell Nesaf Os Diwedd Is  

Mae ei resymeg fel a ganlyn:

• Yn gyffredinol, triniwr digwyddiad yw hwn Taflen waith_Newid, hy bydd y macro hwn yn rhedeg yn awtomatig ar unrhyw newid i unrhyw gell ar y ddalen gyfredol.
• Bydd y cyfeiriad at y gell wedi'i newid bob amser yn y newidyn Targed.
• Yn gyntaf, rydym yn gwirio bod y defnyddiwr wedi newid yn union y gell gyda'r maen prawf (A4) - gwneir hyn gan y gweithredwr if.
• Yna mae'r cylch yn dechrau Ar gyfer pob un… i ailadrodd dros gelloedd llwyd (D2:O2) gyda gwerthoedd dangosydd GWIR / ANGHYWIR ar gyfer pob colofn.
• Os yw gwerth y gell lwyd nesaf yn WIR (gwir), yna nid yw'r golofn wedi'i chuddio, fel arall byddwn yn ei chuddio (eiddo Cudd).

•  Swyddogaethau arae deinamig o Office 365: FILTER, SORT, ac UNIC
• Tabl colyn gyda phennawd aml-linell gan ddefnyddio Power Query
• Beth yw macros, sut i'w creu a'u defnyddio