Kune ruzhinji rwevashandisi veExcel, kana izwi rekuti "data kusefa" rinouya mumisoro yavo, chete yakajairwa kirasi sefa kubva patebhu. Data – Sefa (Data - Sefa):
Sefa yakadaro chinhu chakajairika, pasina kupokana, uye kazhinji kazhinji ichaita. Nekudaro, pane mamiriro ezvinhu apo iwe unofanirwa kusefa nehuwandu hukuru hwemamiriro akaomarara mumakoramu akati wandei kamwechete. Iyo yakajairwa sefa pano haina kunyatso nyanyo uye ndinoda chimwe chinhu chine simba. Chishandiso chakadaro chinogona kuva advanced sefa, kunyanya nediki "kupedzisa nefaira" (maererano netsika).
Basis
Kuti utange, isa mitsetse mishoma isina chinhu pamusoro petafura yako yedata uye wokopa musoro wetafura ipapo - iyi ichave nhanho ine mamiriro (yakasimbiswa yero kuti ijekeswe):
Panofanira kunge paine mutsara umwechete usina chinhu pakati peyero maseru netafura yekutanga.
Iri muyero maseru kuti iwe unofanirwa kuisa maitiro (mamiriro), maererano nekusefa kunozoitwa. Semuenzaniso, kana iwe uchida kusarudza mabhanana muMoscow "Auchan" muchikamu chechitatu, ipapo mamiriro acho achataridzika seizvi:
Kusefa, sarudza chero sero mune renji ine sosi data, vhura iyo tab Data Uye chengetedza Kuwedzerwa (Data - Advanced). Mufafitera rinovhurika, renji ine data inofanira kutoiswa otomatiki uye isu tichango tsanangura huwandu hwemamiriro, kureva A1: I2:
Ndokumbira utarise kuti huwandu hwemamiriro ezvinhu hahugone kugovaniswa "nemargin", kureva kuti haugone kusarudza mamwe mitsara yero isina chinhu, nekuti sero risina chinhu muhuwandu hwemamiriro ezvinhu rinoonekwa neExcel sekushaikwa kwechiyero, uye yakazara isina chinhu. mutsara sechikumbiro chekuratidza data rese zvisina tsarukano.
chinja Kopira mhinduro kune imwe nzvimbo ichakubvumidza kusefa rondedzero kwete ipapo papepa rino (sekune sefa yenguva dzose), asi kuburitsa mitsara yakasarudzwa mune imwe nhanho, iyo inozoda kutsanangurwa mumunda. Isa mhedzisiro muhuwandu. Muchiitiko ichi, isu hatishandisi basa iri, tinosiya Sefa rondedzero panzvimbo uye baya OK. Mitsara yakasarudzwa icharatidzwa papepa:
Kuwedzera Macro
"Zvakanaka, ndekupi kuri nyore pano?" unobvunza uye uchave wakarurama. Kwete chete iwe unofanirwa kuisa mamiriro muyero maseru nemaoko ako, asi zvakare vhura dialog bhokisi, pinda marenji ipapo, dzvanya. OK. Zvinosuruvarisa, ndinobvuma! Asi "zvese zvinoshanduka kana zvauya ©" - macros!
Kushanda nesefa yepamberi kunogona kukwidziridzwa zvakanyanya uye kurerutswa uchishandisa yakapusa macro iyo inomhanya otomatiki sefa yepamberi kana mamiriro apinzwa, kureva kuchinja chero yero sero. Tinya-kurudyi pane tebhu yepepa razvino uye sarudza rairo Kwakabva mavara (Source Code). Muhwindo rinovhurwa, kopira uye unamate inotevera kodhi:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Hapana Chiripo Ipapo PaKukanganisa Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End Kana End Sub Maitirwo aya achaita otomatiki kana chero sero riri papepa rekushanda riripo rachinjwa. Kana iyo kero yesero yakashandurwa ikawira muyero renji (A2: I5), saka macro iyi inobvisa ese mafirita (kana iripo) uye inoshandisazve iyo yakawedzera sefa kune iyo sosi yedata tafura kutanga neA7, kureva kuti zvese zvinosefa ipapo, nekukasira. mushure mekupinda mune inotevera mamiriro:
Saka zvese zviri nani, handiti? 🙂
Kuita mibvunzo yakaoma
Izvozvi zvese zvave kusefa panhunzi, isu tinokwanisa kuenda kwakadzika zvishoma mune nuances uye disassemble masisitimu emibvunzo yakaoma mufirita yepamusoro. Pamusoro pekupinda mumachisi chaiwo, unogona kushandisa mavara akasiyana emusango (* uye ?) uye zviratidzo zvemasvomhu zvekusaenzana mumamiriro ezvinhu akasiyana siyana kuti uite fungidziro yekutsvaga. Nyaya yehunhu haina basa. Kuti zvive pachena, ndapfupikisa zvese zvingasarudzwa mutafura:
| chokuratidza | mugumisiro |
| gr* kana gr | maseru ese kutanga GrIe Grnzeve, Grapefruit, Granat etc. |
| = hanyanisi | masero ose chaizvo uye chete neshoko uta, kureva kuenderana chaiko |
| *rarama* kana *rarama | masero ane Liv kutara sei pasi, kureva ОLivkuti, Livep, maereranoLiv etc. |
| =p*v | mazwi anotanga nawo П uye kupedza ne В ie Пchekutangaв, Пetherв etc. |
| a*s | mazwi anotanga nawo А uye zvimwe zvine СIe Аskinсin, АNanaс, Asai etc. |
| =*s | mazwi anopera С |
| =???? | maseru ese ane mavara mana (mavara kana manhamba, kusanganisira nzvimbo) |
| =m??????n | maseru ese ane mavara emavara masere kutanga nawo М uye kupedza ne НIe Мandariн, Мkufunganyaн etc. |
| =*n??a | mazwi ese achipera А, tsamba 4 kubva kumagumo iripi НIe Beamнikа, maereranoнozа etc. |
| >=e | mazwi ese kutanga nawo Э, Ю or Я |
| <>*o* | mazwi ese asina vara О |
| <>*vich | mazwi ese kunze kweaya anopera HIV (semuenzaniso, sefa vakadzi nezita repakati) |
| = | ese asina chinhu maseru |
| <> | ese asina chinhu maseru |
| > = 5000 | maseru ese ane kukosha kukuru kupfuura kana kuenzana ne5000 |
| 5 kana =5 | maseru ese ane kukosha 5 |
| >=3/18/2013 | ese maseru ane zuva rakatevera Kurume 18, 2013 (inosanganisira) |
Subtle points:
- Chiratidzo che * chinoreva nhamba ipi zvayo yemavara, uye ? - chero chimiro chimwe.
- Iyo logic mukugadzirisa zvinyorwa uye nhamba dzemibvunzo yakasiyana zvishoma. Saka, semuenzaniso, cell cell ine nhamba 5 hairevi kutsvaga nhamba dzese kutanga neshanu, asi cell cell ine vara B inoenzana neB*, kureva kuti ichatsvaga chero chinyorwa chinotanga nebhii B.
- Kana mubvunzo wezvinyorwa usingatangi ne = chiratidzo, saka unogona kuisa * mupfungwa kumagumo.
- Mazuva anofanirwa kuiswa muUS fomati mwedzi-zuva-gore uye kuburikidza nechikamu (kunyangwe iwe uine Excel uye nenzvimbo dzedunhu).
Zvinonzwisisika zvinobatanidza AND-OR
Mamiriro ezvinhu akanyorwa mumasero akasiyana, asi mumutsara mumwe chete, anoonekwa seakabatanidzwa nemunhu ane musoro И (UYE):
Avo. sefa mabhanana kwandiri muchikamu chechitatu, chaizvo muMoscow uye panguva imwe chete kubva kuAuchan.
Kana uchida kubatanidza mamiriro neane logic operator OR (KANA), zvino vanongoda kupinzwa mumitsara yakasiyana. Semuyenzaniso, kana tichida kuwana mirairo yose yemaneja Volina yeMoscow mapichisi uye mirairo yose yehanyanisi muchikamu chechitatu muSamara, zvino izvi zvinogona kutsanangurwa mumamiriro ezvinhu anotevera:
Kana iwe uchida kumanikidza mamiriro maviri kana anopfuura pane imwe mbiru, saka unogona kungo dzokorora musoro wekoramu mumhando yemaitiro uye woisa yechipiri, yechitatu, nezvimwe pasi payo. mazwi. Saka, semuenzaniso, unogona kusarudza zvese zvekutengesa kubva munaKurume kusvika Chivabvu:
Kazhinji, mushure me "kupedza nefaira", sefa yepamberi inobuda kuita yakanaka chishandiso, mune dzimwe nzvimbo haina kushata kupfuura yekare autofilter.
- Superfilter pane macros
- Chii macros, kupi uye sei kuisa macro kodhi muVisual Basic
- Smart matafura muMicrosoft Excel