Parzûnkirina stûna asoyî li Excel

Ger hûn ne bikarhênerek nû ne, wê hingê pêdivî ye ku we berê ferq kiriye ku 99% ji her tiştî li Excel ji bo xebitandina tabloyên vertîkal hatî çêkirin, ku li wir parametre an taybetmendî (zevdan) di stûnan re derbas dibin, û agahdariya li ser tişt an bûyeran hene. di rêzan de. Tabloyên pivot, subtotal, kopîkirina formulan bi du-klîk - her tişt bi taybetî ji bo vê formata daneyê hatî çêkirin.

Lêbelê, bêyî îstîsna û bi frekansek birêkûpêk ti rêgez nîn in, ji min tê pirsîn ku ez çi bikim ger tabloyek bi awirek semantîkî ya horîzontal, an tabloyek ku rêz û stûn di wateyê de heman giranî ne, di xebatê de rast hat:

Û heke Excel hîn jî dizane ku meriv çawa horîzontal (bi fermanê Dane - Rêzkirin - Vebijêrk - Stûnên birêkûpêk), wê hingê rewşa fîlterkirinê xirabtir e - tenê ji bo fîlterkirina stûnan, ne rêzikên li Excel, ne amûrên çêkirî hene. Ji ber vê yekê, heke hûn bi peywirek wusa re rû bi rû bimînin, hûn ê neçar bibin ku rêgezên cûrbecûr tevliheviyê peyda bikin.

Rêbaz 1. Fonksiyona FILTER a nû

Heke hûn li ser guhertoya nû ya Excel 2021 an abonetiyek Excel 365 in, hûn dikarin ji taybetmendiya nû hatî destnîşan kirin sûd werbigirin. PARZÛN (PARZÛN), ku dikare daneyên çavkaniyê ne tenê bi rêzan, lê di heman demê de bi stûnan jî fîltre bike. Ji bo xebatê, ev fonksiyon rêzek rêzek yek-alî ya horizontî ya alîkar hewce dike, ku her nirx (RAST an FALSE) diyar dike ka em stûna paşîn di tabloyê de nîşan bidin an, berovajî vê, veşêrin.

Ka em rêzika jêrîn li ser tabloya xwe zêde bikin û rewşa her stûnê tê de binivîsin:

• Em bibêjin ku em her gav dixwazin stûnên yekem û paşîn (sernivîs û berhevok) nîşan bidin, ji ber vê yekê ji bo wan di şaneyên yekem û paşîn ên rêzê de em nirx = TRUE destnîşan dikin.
• Ji bo stûnên mayî, naveroka hucreyên têkildar dê bibe formulek ku rewşa ku em hewce ne bi karanîna fonksiyonan kontrol dike. И (Û) or OR (AN). Ji bo nimûne, ku bi tevahî di nav rêza 300 heta 500 de ye.

Piştî wê, ew tenê dimîne ku fonksiyonê bikar bîne PARZÛN ji bo bijartina stûnên ku li jorê wan rêza alîkar a me nirxek RAST heye:

Bi heman rengî, hûn dikarin stûnan li gorî navnîşek diyarkirî fîlter bikin. Di vê rewşê de, fonksiyon dê alîkariyê bike COUNTIF (COUNTIF), ku ji sernavê tabloya di navnîşa destûr de hejmara qewimînên navê stûna paşîn kontrol dike:

Rêbaz 2. Li şûna ya asayî tabloya Pivot

Heya nuha, Excel xwedan fîlterkirina horizontî ya ji hêla stûnan ve tenê di tabloyên pivot de heye, ji ber vê yekê heke em bi rê ve bibin tabloya xweya orîjînal veguherînin tabloyek pivot, em dikarin vê fonksiyona çêkirî bikar bînin. Ji bo vê yekê, tabloya çavkaniya me divê şertên jêrîn bicîh bîne:

• xêzek sernivîsê ya yek-xêzek "rast" bêyî şaneyên vala û yekbûyî hebe - wekî din ew ê nexebite ku tabloyek pivot ava bike;
• di etîketên rêz û stûnan de dubareyan negirin - ew ê bi kurtasî di navnîşek tenê nirxên bêhempa de "hilweşin";
• Di nav rêza nirxan de tenê jimareyan vedihewîne (li hevberdana rêz û stûnan), ji ber ku tabloya pivot bê guman dê fonksiyonek berhevkirinê li wan bicîh bîne (hevok, navîn, hwd.) û ev ê bi nivîsê re nexebite.

Ger van şert û mercan tev bigerin, wê hingê ji bo ku meriv tabloyek pivot ku dişibe tabloya meya orîjînal were çêkirin, pêdivî ye ku ew (ya orîjînal) ji çarşefê berbi tabloyek davî (normalîzekirin) were berfireh kirin. Û riya herî hêsan a kirina vê yekê bi pêveka Power Query re ye, amûrek veguherîna daneya hêzdar a ku ji 2016-an vir ve li Excel hatî çêkirin. 

Ev in:

1. Ka em tabloyê veguherînin fermanek dînamîkî ya "aqilmend". Mal - Wek tabloyê format bikin (Mal - Format wek Tablo).
2. Bi fermanê re di Power Query de tê barkirin Daneyên - Ji Tabloyê / Rêzeyê (Daneyên - Ji Tabloyê / Rêzeyê).
3. Em rêza bi tevhevan fîlter dikin (kurteyek dê tevheviyên xwe hebin).
4. Li ser serê stûna yekem rast-klîk bikin û hilbijêrin Stûnên din venekin (Stûnên din veke). Hemî stûnên ne-hilbijartî di duduyan de têne veguheztin - navê karmend û nirxa nîşana wî.
5. Parzûna stûnê bi tevheviyên ku ketine stûnê Pêşbîr.
6. Em bi fermanê li gorî tabloya daîre (normalkirî) ya ku derketiye tabloyek pivot ava dikin Mal - Girtin û Barkirin - Girtin û Li… (Mal - Girtin & Barkirin - Girtin & Barkirin ...).

Naha hûn dikarin şiyana fîlterkirina stûnên ku di tabloyên pivot de hene bikar bînin - nîşaneyên asayî yên li ber nav û tiştan Parzûnên Îmzeyê (Fîlterên Labelê) or Parzûnên bi nirx (Parzeyên nirxê):

Û bê guman, dema ku daneyan biguhezînin, hûn ê hewce bikin ku pirsa me û kurtenivîsê bi kurtebirrek klavyeyê nûve bikin Ctrl+alt+F5 an tîm Daneyên - Hemî nûve bikin (Daneyên - Hemî nûve bikin).

Rêbaz 3. Macro di VBA de

Hemî rêbazên berê, wekî ku hûn bi hêsanî dibînin, ne tam fîlter in - em stûnan di navnîşa orîjînal de venaşêrin, lê bi komek stûnên ji ya orîjînal ve tabloyek nû ava dikin. Ger pêdivî ye ku stûnên di daneya çavkaniyê de were fîlterkirin (veşartin), wê hingê nêzîkatiyek cûda ya bingehîn hewce ye, ango, makro.

Bifikirin ku em dixwazin stûnên li ser firînê fîlter bikin ku navê rêveberê di serê tabloyê de maskeya ku di hucreya zer A4 de hatî destnîşan kirin têr dike, mînakî, bi tîpa "A" dest pê dike (ango, "Anna" û "Arthur" bistînin. " di encamê da). 

Wekî ku di rêbaza yekem de, em pêşî rêzek rêzek alîkar bicîh dikin, ku di her hucreyê de pîvana me dê bi formulakek were kontrol kirin û nirxên mantiqî TRUE an FALSE dê ji bo stûnên xuya û veşartî, bi rêzê, werin xuyang kirin:

Dûv re em makroyek hêsan lê zêde bikin. Li ser tabloya pelê rast-klîk bikin û fermanê hilbijêrin Kanî (Koda çavkaniyê). Koda VBA-ya jêrîn di pencereya ku vedibe de kopî bikin û bixin:

Bineya Taybet Worksheet_Change(ByVal Target Wek Rêze) Ger Target.Address = "$A$4" Paşê Ji bo Her şaneyek Di Rêjeyê de ("D2:O2") Ger şaneyek = Rast Paşê cell.EntireColumn.Hidden = Derewîn cell.EntireColumn.Hidden = Dawiya Rastî Ger Hucreya Paşê Dawiya Dawî Ger End Sub  

Mantiqa wê wiha ye:

• Bi gelemperî, ev rêvekerek bûyerê ye Worksheet_Change, ango ev makro dê bixweber li ser her guheztina her şaneyek li ser pelê heyî bimeşe.
• Referansa li ser şaneya guherî dê her dem di guhêrbar de be Armanc.
• Pêşîn, em kontrol dikin ku bikarhêner bi pîvana (A4) tam şaneyê guherandiye - ev ji hêla operator ve tê kirin. if.
• Paşê çerxa dest pê dike Ji bo her yekî… ji bo her stûnê bi nirxên nîşana RAST / FALSE li ser şaneyên gewr (D2:O2) dubare bikin.
• Ger nirxa şaneya gewr a din RAST (rast) be, wê hingê stûn ne veşartî ye, wekî din em wê vedişêrin (taybetî veşartî).

•  Fonksiyonên rêza dînamîkî ji Office 365: FILTER, SORT, û UNIC
• Tabloya pîvot bi sernavê pir rêzî bi karanîna Power Query
• Makro çi ne, çawa têne çêkirin û bikar anîn