Zosefera zopingasa mu Excel

Ngati simuli wogwiritsa ntchito novice, ndiye kuti mwazindikira kale kuti 99% ya zonse mu Excel zidapangidwa kuti zizigwira ntchito ndi matebulo oyimirira, pomwe magawo kapena mawonekedwe (minda) amadutsa mzati, ndi chidziwitso cha zinthu kapena zochitika. m'mizere. Pivot tables, subtotals, kukopera mafomu ndikudina kawiri - chilichonse chimapangidwa makamaka pamtundu wa datawu.

Komabe, palibe malamulo opanda kuchotserapo komanso pafupipafupi pafupipafupi ndimafunsidwa choti ndichite ngati tebulo lomwe lili ndi mawonekedwe opingasa a semantic, kapena tebulo lomwe mizere ndi mizati imakhala ndi tanthauzo lofanana, idapezeka muntchitoyi:

Ndipo ngati Excel ikudziwabe kusanja mopingasa (ndi lamulo Deta - Sanjani - Zosankha - Sanjani mizati), ndiye kuti zinthu zosefera ndizoyipa kwambiri - palibe zida zopangira zosefera, osati mizere mu Excel. Chifukwa chake, ngati mukukumana ndi ntchito yotereyi, muyenera kubwera ndi ma workaround osiyanasiyana ovuta.

Njira 1. Ntchito yatsopano ya FILTER

Ngati muli pamtundu watsopano wa Excel 2021 kapena kulembetsa kwa Excel 365, mutha kugwiritsa ntchito mwayi womwe wangotulutsidwa kumene. FILTER (SEFA), yomwe imatha kusefa deta yochokera osati mizere yokha, komanso ndi mizere. Kuti agwire ntchito, ntchitoyi imafuna mzere wothandizira wopingasa mbali imodzi, pomwe mtengo uliwonse (WOONA kapena WABODZA) umatsimikizira ngati tikuwonetsa kapena, mosiyana, kubisa gawo lotsatira patebulo.

Tiyeni tiwonjezere mzere wotsatira pamwamba pa tebulo lathu ndikulemba momwe gawo lililonse lilimo:

• Tiyerekeze kuti nthawi zonse timafuna kuwonetsa mizati yoyamba ndi yomaliza (mitu ndi ziwerengero), kotero kwa iwo m'maselo oyambirira ndi otsiriza a gululo timayika mtengo = TRUE.
• Pamizati yotsalayo, zomwe zili m'maselo ofananirako zidzakhala chilinganizo chomwe chimayang'ana momwe timafunikira kugwiritsa ntchito ntchito И (NDI) or OR (KAPENA). Mwachitsanzo, kuti chiwerengerocho chili pakati pa 300 mpaka 500.

Pambuyo pake, zimangotsalira kugwiritsa ntchito ntchitoyi FILTER kusankha mizati pamwamba pomwe gulu lathu lothandizira lili ndi mtengo wa TRUE:

Mofananamo, mukhoza kusefa mizati ndi mndandanda anapatsidwa. Pankhaniyi, ntchitoyi ithandiza COUNTIF (COUNTIF), yomwe imayang'ana kuchuluka kwa zomwe zachitika pagawo lotsatira kuchokera pamutu wa tebulo pamndandanda wololedwa:

Njira 2. Pivot tebulo m'malo mwachizolowezi

Pakadali pano, Excel yakhazikitsa zosefera zopingasa ndi mizati m'matebulo a pivot, ndiye ngati titha kusintha tebulo lathu loyambirira kukhala tebulo la pivot, titha kugwiritsa ntchito izi. Kuti tichite izi, tsamba lathu loyambira liyenera kukwaniritsa izi:

• khalani ndi mzere wamutu wa mzere umodzi "wolondola" wopanda maselo opanda kanthu ndi ophatikizana - apo ayi sizingagwire ntchito popanga tebulo la pivot;
• musakhale ndi zobwerezedwa m'mizere ya mizere ndi mizati - "adzagwa" mu chidule chake kukhala mndandanda wazinthu zapadera zokha;
• zili ndi manambala okha mumitundu yosiyanasiyana (pamzere wa mizere ndi mizati), chifukwa tebulo la pivot lidzagwiritsanso ntchito mtundu wina wa kuphatikizira kwa iwo (chiwerengero, chiŵerengero, ndi zina zotero) ndipo izi sizigwira ntchito ndi malembawo.

Ngati zinthu zonsezi zakwaniritsidwa, ndiye kuti tipange tebulo la pivot lomwe limawoneka ngati tebulo lathu loyambirira, (loyamba) liyenera kukulitsidwa kuchokera pamtanda kupita ku lathyathyathya (lokhazikika). Ndipo njira yosavuta yochitira izi ndikuwonjezera Power Query, chida champhamvu chosinthira deta chomwe chamangidwa mu Excel kuyambira 2016. 

Izi ndi:

1. Tiyeni tisinthe tebulo kukhala lamulo la "smart" lamphamvu Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table).
2. Kutsegula mu Power Query ndi lamulo Deta - Kuchokera pa Table / Range (Deta - Kuchokera pa Table / Range).
3. Timasefa mzere ndi ziwopsezo (chidulecho chidzakhala ndi ziwerengero zake).
4. Dinani kumanja pa mutu woyamba ndikusankha Matulani zigawo zina (Onjezani Mizati Zina). Mizati yonse yosasankhidwa imasinthidwa kukhala ziwiri - dzina la wogwira ntchitoyo ndi mtengo wa chizindikiro chake.
5. Kusefa ndime ndi ziwopsezo zomwe zidalowa muzambiri Umunthu.
6. Timamanga tebulo la pivot molingana ndi tebulo lokhazikika (lokhazikika) ndi lamulo Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku...).

Tsopano mutha kugwiritsa ntchito kuthekera kosefa zipilala zomwe zikupezeka m'matebulo a pivot - zolembera zomwe zili patsogolo pa mayina ndi zinthu. Zosefera za Signature (Zosefera Zolemba) or Zosefera ndi mtengo (Zosefera Zamtengo):

Ndipo zowona, posintha deta, mudzafunika kusintha funso lathu ndi chidule chake ndi njira yachidule ya kiyibodi. Ctrl+alt+F5 kapena timu Deta - Tsitsani Zonse (Deta - Tsitsani Zonse).

Njira 3. Macro mu VBA

Njira zonse zam'mbuyo, monga momwe mukuonera, sizimasefa ndendende - sitimabisa mizati pamndandanda wapachiyambi, koma pangani tebulo latsopano ndi ndondomeko yoperekedwa kuchokera pachiyambi. Ngati pakufunika kusefa (kubisa) mizati muzochokera, ndiye kuti pakufunika njira yosiyana, yomwe ndi macro.

Tiyerekeze kuti tikufuna kusefa ndime pa ntchentche kumene dzina la woyang'anira pa tebulo mutu amakhutitsa chigoba chotchulidwa yellow selo A4 Mwachitsanzo, akuyamba ndi chilembo "A" (ndiko kuti, kupeza "Anna" ndi "Arthur". " zotsatira zake). 

Monga momwe zilili mu njira yoyamba, timayamba kugwiritsa ntchito mzere wothandizira, pomwe mu selo iliyonse muyeso wathu udzawunikidwa ndi chilinganizo ndipo mfundo zomveka za TRUE kapena FALSE zidzawonetsedwa pazigawo zowoneka ndi zobisika, motsatana:

Ndiye tiyeni tiwonjezere chosavuta macro. Dinani kumanja pa pepala tabu ndikusankha lamulo gwero (Source kodi). Koperani ndi kumata nambala ya VBA yotsatira pawindo lomwe limatsegula:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Ndiye Pa selo Lililonse Mu Range("D2:O2") Ngati selo = Zoona Ndiye cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Mapeto Oona Ngati Maselo Otsatira Atha Ngati Mapeto a Sub  

logic yake ndi motere:

• Kawirikawiri, uyu ndi wothandizira zochitika Worksheet_Change, mwachitsanzo, Macro iyi idzayenda yokha pakusintha kulikonse kwa selo iliyonse yomwe ili patsamba lino.
• Kufotokozera kwa selo losinthidwa kumakhala kosinthika nthawi zonse chandamale.
• Choyamba, timayang'ana kuti wogwiritsa ntchito wasintha chimodzimodzi selo ndi muyezo (A4) - izi zimachitika ndi wogwiritsa ntchito. if.
• Kenako kuzungulira kumayamba Kwa Aliyense… kubwereza ma cell otuwa (D2: O2) okhala ndi TRUE / FALSE chizindikiro pagawo lililonse.
• Ngati mtengo wa cell imvi yotsatira ndi TRUE (zoona), ndiye kuti gawolo silinabisike, apo ayi timabisa (katundu). Zabisika).

•  Ntchito zazikuluzikulu zochokera ku Office 365: FILTER, SORT, ndi UNIC
• Pivot tebulo yokhala ndi mitu yambiri pogwiritsa ntchito Power Query
• Kodi macros ndi chiyani, momwe mungapangire ndikuzigwiritsa ntchito