Okuqukethwe
Kubasebenzisi abaningi be-Excel, lapho igama elithi "ukuhlunga idatha" livela ekhanda labo, kuphela isihlungi esivamile esivela kuthebhu. Idatha – Hlunga (Idatha — Isihlungi):
Isihlungi esinjalo siyinto evamile, ngokungangabazeki, futhi ezimweni eziningi sizokwenza. Kodwa-ke, kunezimo lapho udinga ukuhlunga ngenani elikhulu lezimo eziyinkimbinkimbi kumakholomu amaningana ngesikhathi esisodwa. Isihlungi esijwayelekile lapha asilungile kakhulu futhi ngifuna okuthile okunamandla kakhulu. Ithuluzi elinjalo lingaba isihlungi esithuthukisiwe, ikakhulukazi "ngokuqeda ngefayela" (ngokwesiko).
Isisekelo
Ukuze uqalise, faka imigqa embalwa engenalutho ngaphezu kwethebula lakho ledatha bese ukopisha unhlokweni wethebula lapho - lokhu kuzoba ububanzi obunemibandela (egqanyiswe ngokuphuzi ukuze kucace):
Okungenani kufanele kube nomugqa owodwa ongenalutho phakathi kwamaseli aphuzi kanye netafula lokuqala.
Kusemangqamuzaneni aphuzi lapho udinga ukufaka khona imibandela (imibandela), ngokusho ukuthi ukuhlunga kuzokwenziwa kanjani. Isibonelo, uma udinga ukukhetha ubhanana eMoscow "Auchan" engxenyeni ye-III, izimo zizobukeka kanje:
Ukuze uhlunge, khetha noma yiliphi iseli kububanzi elinedatha yomthombo, vula ithebhu Idatha Bese uqhafaza Ukwengeza (Idatha - Ithuthukisiwe). Efasiteleni elivulekayo, ibanga elinedatha kufanele selifakwe ngokuzenzakalelayo futhi kuzodingeka sicacise kuphela ububanzi bemibandela, okungukuthi, A1:I2:
Sicela uqaphele ukuthi ububanzi bemibandela abukwazi ukwabiwa “ngemajini”, okungukuthi awukwazi ukukhetha imigqa ephuzi eyengeziwe engenalutho, ngoba iseli elingenalutho ebangeni lemibandela libonwa yi-Excel njengokungabikho kombandela, futhi lonke elingenalutho. line njengesicelo sokubonisa yonke idatha ngokungakhethi.
Shintsha Kopishela umphumela kwenye indawo izokuvumela ukuthi uhlunge uhlu hhayi khona lapho kuleli shidi (njengangesihlungi esivamile), kodwa ukuze wehlisele imigqa ekhethiwe kobunye ububanzi, obuzodinga ukucaciswa ensimini. Beka umphumela ebangeni. Kulokhu, asisebenzisi lo msebenzi, siyahamba Hlunga uhlu endaweni bese uchofoza OK. Imigqa ekhethiwe izoboniswa eshidini:
Ukwengeza iMacro
"Awu, kuphi ukunethezeka lapha?" ubuza uzobe uqinisile. Awudingi nje ukufaka izimo kumaseli aphuzi ngezandla zakho, kodwa futhi uvule ibhokisi lengxoxo, faka amabanga lapho, cindezela OK. Kuyadabukisa, ngiyavuma! Kodwa "yonke into iyashintsha lapho ifika ©" - ama-macros!
Ukusebenza ngesihlungi esithuthukisiwe kungasheshiswa kakhulu futhi kwenziwe lula kusetshenziswa i-macro elula ezosebenzisa ngokuzenzakalelayo isihlungi esithuthukisiwe lapho izimo zifakwa, okungukuthi ukushintsha noma iyiphi iseli ephuzi. Chofoza kwesokudla kuthebhu yeshidi lamanje bese ukhetha umyalo Umbhalo ongumthombo (Ikhodi Yomthombo). Ewindini elivulayo, kopisha bese unamathisela ikhodi elandelayo:
I-Private Sub Worksheet_Change(ByVal Target As Range) Uma Ingaphambani(Ithagethi, Ububanzi("A2:I5")) Alilutho Bese Kuyiphutha Qalisa Ububanzi obulandelayo be-ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Ububanzi("A1").Isifunda Samanje Siphela Uma Siqeda Ingxenye Le nqubo izosebenza ngokuzenzakalelayo lapho kuguqulwa noma iyiphi iseli ekhasini lokusebenzela lamanje. Uma ikheli leseli elishintshiwe liwela ebangeni eliphuzi (A2:I5), khona-ke le macro isusa zonke izihlungi (uma zikhona) futhi iphinde isebenzise isihlungi esinwetshiwe kuthebula ledatha yomthombo kuqala ngo-A7, okungukuthi yonke into izohlungwa khona manjalo, ngokushesha. ngemva kokungena esimweni esilandelayo:
Ngakho konke kungcono kakhulu, akunjalo? 🙂
Ukusebenzisa imibuzo eyinkimbinkimbi
Manje njengoba yonke into isihlungwa ngokuhamba kwesikhathi, singangena sijule kancane kuma-nuances futhi sihlukanise izindlela zemibuzo eyinkimbinkimbi kakhulu kusihlungi esithuthukisiwe. Ngokungeziwe ekufakeni okufanayo ngqo, ungasebenzisa izinhlamvu ze-wildcard (* kanye ?) kanye nezimpawu zokungalingani kwezibalo kububanzi bemibandela ukuze usebenzise ukusesha okulinganiselwe. Icala lomlingiswa alinandaba. Ukuze kucace, ngifingqe zonke izinketho ezikhona kuthebula:
| Isikhombisi | Result |
| gr* noma gr | wonke amaseli aqala ngawo GrIe Grindlebe, Gri-apefruit, Granat njll |
| = anyanisi | wonke amaseli ncamashi futhi ngegama kuphela Bow, okungukuthi ukufana ngqo |
| *phila* noma *phila | amaseli aqukethe Liv dwebela kanjani, isb ОLivukuthi, Livep, NgokushoLiv njll |
| =p*v | amazwi aqala ngawo П futhi iphetha ngokuthi В ie Пkuqalaв, Пetherв njll |
| a*s | amazwi aqala ngawo А nokuqukatha okwengeziwe СIe Аukhaloсin, Аnanaс, Asai njll |
| =*s | amagama agcina ngo С |
| =???? | wonke amaseli anombhalo wezinhlamvu ezingu-4 (izinhlamvu noma izinombolo, kuhlanganise nezikhala) |
| =m??????n | wonke amaseli anombhalo wezinhlamvu eziyi-8 aqala ngawo М futhi iphetha ngokuthi НIe Мandariн, Мukukhathazekaн njll |
| =*n??a | wonke amagama agcina ngokuthi А, iphi incwadi yesi-4 evela ekugcineni НIe I-Beamнikа, Ngokushoнozа njll |
| >=e | wonke amagama aqala ngawo Э, Ю or Я |
| <>*o* | wonke amagama angenalo uhlamvu О |
| <>*vich | wonke amagama ngaphandle kwalawo agcina ngo HIV (isibonelo, hlunga abesifazane ngamagama aphakathi) |
| = | wonke amaseli angenalutho |
| <> | wonke amaseli angenalutho |
| > = 5000 | wonke amaseli anenani elikhulu noma elilingana no-5000 |
| 5 noma =5 | wonke amaseli anenani elingu-5 |
| >> = 3/18/2013 | wonke amaseli anedethi engemuva kukaMashi 18, 2013 (okufakiwe) |
Amaphuzu acashile:
- Uphawu * lusho noma iyiphi inombolo yanoma yiziphi izinhlamvu, futhi ? – yimuphi umlingiswa oyedwa.
- I-logic ekucubunguleni umbhalo kanye nemibuzo yezinombolo ihluke kancane. Ngakho, isibonelo, iseli yesimo enenombolo 5 ayisho ukucinga zonke izinombolo eziqala ngezinhlanu, kodwa iseli yesimo enohlamvu B ilingana no-B*, okungukuthi izobheka noma yimuphi umbhalo oqala ngohlamvu B.
- Uma umbuzo wombhalo ungaqali ngophawu =, ungakwazi ukubeka * ekugcineni.
- Amadethi kufanele afakwe ngefomethi yase-US yenyanga yosuku lwenyanga nangeqhezu (ngisho noma une-Excel kanye nezilungiselelo zesifunda).
Izixhumi ezinengqondo KANYE-NOMA
Izimo ezibhalwe ngamaseli ahlukene, kodwa emugqeni ofanayo, zibhekwa njengezixhunywe u-opharetha onengqondo И (FUTHI):
Labo. hlunga ubhanana kimi engxenyeni yesithathu, ngokuqondile eMoscow futhi ngesikhathi esifanayo kusukela e-Auchan.
Uma udinga ukuxhumanisa izimo no-opharetha onengqondo OR (OR), khona-ke badinga nje ukufakwa emigqeni ehlukene. Isibonelo, uma sidinga ukuthola wonke ama-oda omphathi we-Volina wamapentshisi aseMoscow nawo wonke ama-oda ka-anyanisi engxenyeni yesithathu yaseSamara, khona-ke lokhu kungacaciswa kuhlu lwezimo kanje:
Uma udinga ukubeka imibandela emibili noma ngaphezulu kukholomu eyodwa, ungakwazi kalula ukuphinda isihloko sekholomu ebangeni lemibandela bese ufaka eyesibili, yesithathu, njll. ngaphansi kwayo. imigomo. Ngakho-ke, ngokwesibonelo, ungakhetha konke okwenziwayo kusuka ngoMashi kuya kuMeyi:
Ngokuvamile, ngemva "kokuqeda ngefayela", isihlungi esithuthukisiwe siphuma sibe ithuluzi elihloniphekile, kwezinye izindawo alibi kakhulu kunesihlungi esizenzakalelayo sakudala.
- Isihlungi esikhulu kumamakhro
- Ayini ama-macro, kuphi futhi kanjani ukufaka ikhodi ye-macro ku-Visual Basic
- Amathebula ahlakaniphile ku-Microsoft Excel