Kuchuja safu mlalo katika Excel

Ikiwa wewe si mtumiaji wa novice kabisa, basi lazima uwe tayari umeona kwamba 99% ya kila kitu katika Excel imeundwa kufanya kazi na meza za wima, ambapo vigezo au sifa (mashamba) hupitia safu, na habari kuhusu vitu au matukio iko. katika mistari. Jedwali badilifu, jumla ndogo, fomula za kunakili kwa kubofya mara mbili - kila kitu kimeundwa mahususi kwa umbizo hili la data.

Walakini, hakuna sheria bila ubaguzi na kwa masafa ya kawaida ninaulizwa nini cha kufanya ikiwa meza iliyo na mwelekeo wa usawa wa semantic, au meza ambayo safu na safu wima zina uzani sawa katika maana, zitapatikana kwenye kazi:

Na ikiwa Excel bado anajua jinsi ya kupanga kwa usawa (na amri Data - Panga - Chaguzi - Panga safu wima), basi hali na kuchuja ni mbaya zaidi - hakuna tu zana zilizojengwa za kuchuja nguzo, si safu katika Excel. Kwa hivyo, ikiwa unakabiliwa na kazi kama hiyo, itabidi uje na suluhisho za viwango tofauti vya ugumu.

Njia ya 1. Kazi mpya ya FILTER

Ikiwa unatumia toleo jipya la Excel 2021 au usajili wa Excel 365, unaweza kuchukua fursa ya kipengele kipya kilicholetwa. Kichungi (CHUJA), ambayo inaweza kuchuja data ya chanzo sio tu kwa safu, lakini pia kwa safu. Ili kufanya kazi, chaguo hili la kukokotoa linahitaji safu-saidizi ya mlalo yenye mwelekeo mmoja, ambapo kila thamani (TRUE au FALSE) huamua ikiwa tunaonyesha au, kinyume chake, kuficha safu wima inayofuata kwenye jedwali.

Wacha tuongeze safu ifuatayo juu ya jedwali letu na tuandike hali ya kila safu ndani yake:

• Wacha tuseme kila wakati tunataka kuonyesha safu wima za kwanza na za mwisho (vichwa na jumla), kwa hivyo kwao katika seli za kwanza na za mwisho za safu tunaweka thamani = TRUE.
• Kwa safu wima zilizobaki, yaliyomo kwenye seli zinazolingana itakuwa fomula inayokagua hali tunayohitaji kwa kutumia vitendaji И (NA) or OR (OR). Kwa mfano, kwamba jumla iko katika safu kutoka 300 hadi 500.

Baada ya hayo, inabakia tu kutumia kazi Kichungi ili kuchagua safu wima juu ambayo safu yetu kisaidizi ina thamani ya TRUE:

Vile vile, unaweza kuchuja safu kwa orodha fulani. Katika kesi hii, kazi itasaidia COUNTIF (COUNTIF), ambayo hukagua idadi ya matukio ya jina la safu wima inayofuata kutoka kwa kichwa cha jedwali kwenye orodha inayoruhusiwa:

Njia ya 2. Jedwali la egemeo badala ya ile ya kawaida

Kwa sasa, Excel ina uchujaji wa mlalo uliojengewa ndani kwa safu wima katika majedwali egemeo pekee, kwa hivyo ikiwa tunaweza kubadilisha jedwali letu asili kuwa jedwali badilifu, tunaweza kutumia utendakazi huu uliojengewa ndani. Ili kufanya hivyo, jedwali letu la chanzo lazima likidhi masharti yafuatayo:

• kuwa na mstari wa kichwa "sahihi" wa mstari mmoja bila seli tupu na zilizounganishwa - vinginevyo haitafanya kazi kujenga meza ya pivot;
• usiwe na nakala katika lebo za safu na safu - "zitaanguka" katika muhtasari katika orodha ya maadili ya kipekee pekee;
• vyenye nambari tu katika anuwai ya maadili (katika makutano ya safu na safu wima), kwa sababu jedwali la egemeo hakika litatumia aina fulani ya chaguo la kukokotoa kwao (jumla, wastani, n.k.) na hii haitafanya kazi na maandishi.

Ikiwa masharti haya yote yametimizwa, basi ili kuunda jedwali la egemeo linalofanana na jedwali letu la asili, (ile ya asili) itahitaji kupanuliwa kutoka kwa kichupo cha msalaba hadi gorofa (iliyosawazishwa). Na njia rahisi zaidi ya kufanya hivyo ni kwa kutumia Power Query, zana yenye nguvu ya kubadilisha data iliyojengwa katika Excel tangu 2016. 

Hizi ni:

1. Wacha tubadilishe jedwali kuwa amri ya "smart" yenye nguvu Nyumbani - Fomati kama meza (Nyumbani - Umbizo kama Jedwali).
2. Inapakia kwenye Hoja ya Nguvu kwa amri Data - Kutoka kwa Jedwali / Masafa (Takwimu - Kutoka kwa Jedwali / Masafa).
3. Tunachuja mstari na jumla (muhtasari utakuwa na jumla yake).
4. Bofya kulia kwenye kichwa cha safu wima ya kwanza na uchague Tendua safu wima zingine (Ondoa safu wima Nyingine). Nguzo zote zisizochaguliwa zinabadilishwa kuwa mbili - jina la mfanyakazi na thamani ya kiashiria chake.
5. Kuchuja safu na jumla zilizoingia kwenye safu Sifa.
6. Tunaunda meza ya egemeo kulingana na jedwali la gorofa (iliyosawazishwa) na amri Nyumbani - Funga na Pakia - Funga na Pakia ndani... (Nyumbani — Funga & Pakia — Funga na Pakia kwa…).

Sasa unaweza kutumia uwezo wa kuchuja safu wima zinazopatikana katika majedwali egemeo - alama tiki za kawaida mbele ya majina na vipengee. Vichujio vya Sahihi (Vichujio vya Lebo) or Vichujio kulingana na thamani (Vichujio vya Thamani):

Na bila shaka, unapobadilisha data, utahitaji kusasisha hoja yetu na muhtasari kwa njia ya mkato ya kibodi. Ctrl+Alt+F5 au timu Data - Onyesha upya Wote (Data - Onyesha upya Zote).

Njia ya 3. Macro katika VBA

Njia zote za awali, kama unaweza kuona kwa urahisi, sio kuchuja hasa - hatuficha safu katika orodha ya awali, lakini tengeneza meza mpya na seti fulani ya safu kutoka kwa moja ya awali. Ikiwa inahitajika kuchuja (kujificha) safu wima kwenye data ya chanzo, basi mbinu tofauti kabisa inahitajika, yaani, macro.

Tuseme tunataka kuchuja safu wima kwenye nzi ambapo jina la msimamizi kwenye kichwa cha jedwali linakidhi kinyago kilichoainishwa kwenye seli ya manjano A4, kwa mfano, huanza na herufi "A" (hiyo ni, pata "Anna" na "Arthur. " matokeo yake). 

Kama ilivyo kwa njia ya kwanza, kwanza tunatekeleza safu-saidizi ya safu, ambapo katika kila seli kigezo chetu kitaangaliwa na fomula na maadili ya kimantiki TRUE au FALSE yataonyeshwa kwa safu wima zinazoonekana na zilizofichwa, mtawalia:

Kisha tuongeze macro rahisi. Bonyeza kulia kwenye kichupo cha karatasi na uchague amri chanzo (Msimbo wa chanzo). Nakili na ubandike nambari ifuatayo ya VBA kwenye dirisha linalofungua:

Laha ya Kazi ya Kibinafsi_Mabadiliko(Lengo la ByVal Kama Masafa) Ikiwa Target.Address = "$A$4" Kisha Kwa Kila kisanduku Katika Masafa("D2:O2") Ikiwa kisanduku = Kweli Kisha kisanduku.SafuMzima.Iliyofichwa = Seli Nyingine Siyo.Safuwima.Imefichwa = Mwisho wa Kweli Ikiwa kisanduku Kifuatacho Kinaisha Ikiwa Komesha Sub  

Mantiki yake ni kama ifuatavyo:

• Kwa ujumla, huyu ni msimamizi wa hafla Mabadiliko_ya_Kazi, yaani jumla hii itaendesha kiotomatiki mabadiliko yoyote kwa seli yoyote kwenye laha ya sasa.
• Marejeleo ya kisanduku kilichobadilishwa yatakuwa kwenye kigezo kila wakati Lengo.
• Kwanza, tunaangalia kwamba mtumiaji amebadilisha kiini hasa na kigezo (A4) - hii inafanywa na operator. if.
• Kisha mzunguko huanza Kwa kila… ili kusisitiza juu ya seli za kijivu (D2:O2) zenye thamani za viashirio vya TRUE/FALSE kwa kila safu.
• Ikiwa thamani ya seli inayofuata ya kijivu ni TRUE (kweli), basi safu haijafichwa, vinginevyo tunaificha (mali Mbegu).

•  Vitendaji vya safu inayobadilika kutoka Ofisi ya 365: FILTER, SORT, na UNIC
• Jedwali la egemeo lenye vichwa vya mistari mingi kwa kutumia Hoja ya Nishati
• Macros ni nini, jinsi ya kuunda na kuitumia