Zosefera zapamwamba komanso zamatsenga

Kwa ogwiritsa ntchito ambiri a Excel, mawu oti "sefa ya data" akatuluka m'mutu mwawo, fyuluta yanthawi zonse yochokera pa tabu. Zosefera - Zosefera (Deta - Sefa):

Fyuluta yotereyi ndi yodziwika bwino, mosakayikira, ndipo nthawi zambiri idzachita. Komabe, pali zochitika zomwe muyenera kusefa ndi kuchuluka kwa zovuta m'mizere ingapo nthawi imodzi. Zosefera wamba pano sizothandiza kwambiri ndipo ndikufuna china champhamvu kwambiri. Chida choterocho chingakhale zosefera zapamwamba, makamaka ndi "kumaliza ndi fayilo" pang'ono (malinga ndi mwambo).

Maziko

Kuti muyambe, ikani mizere ingapo yopanda kanthu pamwamba pa tebulo lanu la deta ndikujambulani mutu wa tebulo pamenepo - uwu udzakhala wosiyana ndi mikhalidwe (yosonyezedwa muchikasu kuti imveke bwino):

Payenera kukhala mzere umodzi wopanda kanthu pakati pa ma cell achikasu ndi tebulo loyambirira.

Ndi m'maselo achikasu omwe muyenera kuyikamo (mikhalidwe), malinga ndi zomwe kusefa kudzachitidwa. Mwachitsanzo, ngati mukufuna kusankha nthochi mu Moscow "Auchan" mu kotala III, ndiye zinthu zidzawoneka motere:

Kuti musefe, sankhani selo iliyonse yomwe ili mugawo lomwe lili ndi deta yochokera, tsegulani tabu Deta Ndipo dinani Kuwonjezera apo (Zambiri - Zapamwamba). Pazenera lomwe limatsegulidwa, mndandanda womwe uli ndi deta uyenera kulowetsedwa kale ndipo tidzangofotokozera momwe zinthu zilili, mwachitsanzo, A1: I2:

Chonde dziwani kuti kuchuluka kwazinthu sikungagawidwe "ndi malire", mwachitsanzo, simungasankhe mizere yachikasu yopanda kanthu, chifukwa selo lopanda kanthu pamikhalidwe yosiyanasiyana limadziwika ndi Excel ngati kusakhalapo kwa muyeso, komanso chopanda kanthu. mzere ngati pempho kuti muwonetse deta yonse mosasankha.

Sinthani Lembani zotsatira kumalo ena zidzakulolani kuti musefe mndandandawo osati pa pepala ili (monga ndi fyuluta wamba), koma kuti mutulutse mizere yosankhidwa mumtundu wina, womwe udzafunika kufotokozedwa m'mundamo. Ikani zotsatira munjira. Pankhaniyi, sitigwiritsa ntchito ntchitoyi, timachoka Zosefera zomwe zilipo ndipo dinani OK. Mizere yosankhidwa idzawonetsedwa papepala:

Kuwonjezera Macro

"Chabwino, kuli kuti komweko?" mukafunsa ndipo mudzakhala olondola. Sikuti mumangofunika kulowa m'maselo achikasu ndi manja anu, komanso mutsegule bokosi la zokambirana, lowetsani mizere pamenepo, dinani OK. Zachisoni, ndikuvomereza! Koma "chilichonse chimasintha akabwera ©" - macros!

Kugwira ntchito ndi fyuluta yapamwamba kumatha kufulumizitsidwa kwambiri ndikusavuta kugwiritsa ntchito macro yosavuta yomwe imayendetsa yokha fyuluta yapamwamba ikalowa, mwachitsanzo, kusintha selo lililonse lachikasu. Dinani kumanja pa tabu ya pepala lamakono ndikusankha lamulo Mawu oyambira (Source kodi). Pazenera lomwe limatsegulidwa, koperani ndikumata nambala iyi:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Palibe Kanthu Ndiye Pa Cholakwika Bweretsani Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Njirayi imangoyenda yokha selo iliyonse yomwe ili patsamba lapano lasinthidwa. Ngati adilesi ya cell yomwe yasinthidwa igwera mumtundu wachikasu (A2: I5), ndiye kuti macro amachotsa zosefera zonse (ngati zilipo) ndikuyikanso fyuluta yowonjezereka ku tebulo lachidziwitso kuyambira ndi A7, mwachitsanzo, zonse zidzasefedwa nthawi yomweyo, nthawi yomweyo. mutalowa mu chikhalidwe chotsatira:

Ndiye zonse zili bwino, sichoncho? 🙂

Kukhazikitsa mafunso ovuta

Tsopano popeza zonse zikusefedwera pa ntchentche, titha kupita mozama pang'ono ndikusokoneza njira zamafunso ovuta kwambiri mu fyuluta yapamwamba. Kuphatikiza pa kuyika machesi enieni, mutha kugwiritsa ntchito zilembo za wildcard (* ndi ?) ndi zizindikiro zosafanana zamasamu m'mikhalidwe yosiyanasiyana kuti mufufuze kusaka. Mlandu wamunthu ulibe kanthu. Kuti zimveke bwino, ndafotokozera mwachidule zosankha zonse zomwe zingatheke patebulo:

Zosawoneka bwino:

• Chizindikiro * chimatanthauza nambala iliyonse ya zilembo, ndipo ? - khalidwe lililonse.
• Lingaliro pakukonza zolemba ndi mafunso amawerengero ndizosiyana pang'ono. Kotero, mwachitsanzo, cell cell yokhala ndi nambala 5 sikutanthauza kufufuza manambala onse kuyambira ndi zisanu, koma cell cell yokhala ndi chilembo B ndi yofanana ndi B*, mwachitsanzo, idzayang'ana mawu aliwonse kuyambira ndi chilembo B.
• Ngati funso lalemba siliyamba ndi = chizindikiro, ndiye kuti mutha kuyika * kumapeto.
• Madeti akuyenera kulembedwa m'mawonekedwe aku US mwezi-tsiku-chaka komanso kudzera pang'onopang'ono (ngakhale mutakhala ndi Excel ndi madera).

Zolumikizira zomveka NDI-OR

Mikhalidwe yolembedwa m'maselo osiyanasiyana, koma mumzere womwewo, imatengedwa kuti ndi yolumikizidwa ndi wogwiritsa ntchito zomveka И (NDI):

Iwo. sefa nthochi kwa ine mu gawo lachitatu, ndendende ku Moscow komanso nthawi yomweyo kuchokera ku Auchan.

Ngati mukufuna kulumikiza zinthu ndi wogwiritsa ntchito zomveka OR (KAPENA), ndiye amangofunika kulowetsedwa mizere yosiyana. Mwachitsanzo, ngati tifunika kupeza malamulo onse a woyang'anira Volina ku Moscow yamapichesi ndi malamulo onse anyezi mu kotala lachitatu Samara, ndiye kuti akhoza kutchulidwa mu osiyanasiyana zinthu motere:

Ngati mukufunika kuyika zikhalidwe ziwiri kapena zingapo pagawo limodzi, mutha kungobwereza mutu wagawo muzosankha ndikulowetsa chachiwiri, chachitatu, ndi zina zambiri pansi pake. mawu. Chifukwa chake, mwachitsanzo, mutha kusankha zochitika zonse kuyambira Marichi mpaka Meyi:

Nthawi zambiri, "mutatha ndi fayilo", fyuluta yapamwamba imakhala chida chabwino kwambiri, m'malo ena palibe choyipa kuposa chosefera chapamwamba.

• Superfilter pa macros
• Kodi macros ndi chiyani, momwe mungayikitsire macro code mu Visual Basic
• Ma tebulo anzeru mu Microsoft Excel