Ngatiti iwe une runyorwa umo, neakasiyana madhigirii e "kururama," yekutanga data inonyorwa - semuenzaniso, kero kana mazita ekambani:
 |  |
Zvinonyatsoonekwa kuti guta rimwe chete kana kambani iripo pano mumotley variants, iyo, sezviri pachena, ichagadzira matambudziko akawanda pakushanda nematafura aya mune ramangwana. Uye kana iwe uchifunga zvishoma, unogona kuwana yakawanda mienzaniso yemabasa akafanana kubva kune dzimwe nzvimbo.
Iye zvino fungidzira kuti data rakashata rakadaro rinouya kwauri nguva dzose, kureva kuti iyi haisi nguva imwe chete "igadzirise nemaoko, kanganwa" nyaya, asi dambudziko nguva dzose uye muhuwandu hwemasero.
Kuita sei? Usatsiva nemaoko mavara akatsveyama 100500 nguva neakakodzera kuburikidza nebhokisi re "Tsvaga uye Replace" kana nekudzvanya. Ctrl+H?
Chinhu chekutanga chinouya mumusoro mumamiriro ezvinhu akadaro ndechekuita kutsiva kwevanhu vakawanda maererano nebhuku rekare-rakaunganidzwa rekuenzanisa sarudzo dzisiridzo uye dzakaringana - seizvi:
Nehurombo, nekuwanda kuri pachena kwebasa rakadaro, Microsoft Excel haina nzira dzakareruka dzakavakirwa-mukati dzekurigadzirisa. Kutanga, ngationei kuti tingazviita sei nemafomati, tisingabatanidzi "heavy artillery" nenzira ye macros muVBA kana Power Query.
Nyaya 1. Kutsiva kwakazara
Ngatitangei nenyaya iri nyore - mamiriro ekuti iwe unofanirwa kutsiva iyo yekare yakamonyoroka chinyorwa neitsva. zvakazara.
Ngatitii tine matafura maviri:
Mune yekutanga - yekutanga variegated mazita emakambani. Muchipiri - bhuku rekutsvaga retsamba. Kana isu tikawana muzita rekambani mune yekutanga tafura chero izwi kubva mukoramu Kuwana, ipapo iwe unofanirwa kutsiva zvachose zita iri rakatsveyama nerakarurama - kubva pambiru Substitute tafura yechipiri yekutarisa.
Kuti zvive nyore:
- Matafura ese ari maviri anoshandurwa kuita simba ("smart") uchishandisa nzira yekudimbudzira yekhibhodi Ctrl+T kana timu Pinza – Tafura (Isa - Tafura).
- Pane tab inooneka Constructor (Dhizaini) tafura yekutanga yakanzi Data, uye tafura yechipiri yereferensi - Kutsiva.
Kuti titsanangure pfungwa yefomula, ngatiendei zvishoma kubva kure.
Tichitora kambani yekutanga kubva muchitokisi A2 semuenzaniso uye nekukanganwa kwenguva pfupi nezve mamwe makambani, ngatiedzei kuona kuti ndeipi sarudzo kubva mukoramu. Kuwana anosangana ipapo. Kuti uite izvi, sarudza chero sero isina chinhu muchikamu chemahara chepepa uye pinda basa ipapo KUWANA (TSVAGA):
Iri basa rinotarisa kana iyo yakapihwa tambo inosanganisirwa (yekutanga nharo ndeyese kukosha kubva kukoromo Kuwana) muzvinyorwa zvekwakabva (kambani yekutanga kubva patafura yedata) uye inofanira kuburitsa kana nhamba yeordinal yemavara kubva pakawanikwa chinyorwa, kana kukanganisa kana substring isina kuwanikwa.
Chinongedzo apa ndechekuti sezvo isu takataura kwete imwe chete, asi akati wandei senharo yekutanga, basa iri richadzoka zvakare semhedzisiro kwete imwechete kukosha, asi hurongwa hwezvinhu zvitatu. Kana iwe usina yazvino vhezheni yeHofisi 3 inotsigira dynamic arrays, saka mushure mekupinda iyi fomula nekudzvanya pa. pinda iwe uchaona iyi array pane pepa:
Kana iwe uine yapfuura shanduro dzeExcel, ipapo mushure mekudzvanya pa pinda tichangoona kukosha kwekutanga kubva mumhedzisiro array, kureva kukanganisa #VALUE! (#VALUE!).
Iwe haufanirwe kutya 🙂 Muchokwadi, fomula yedu inoshanda uye iwe uchiri kukwanisa kuona iyo yese rondedzero yemhedzisiro kana ukasarudza iro rakapinda basa mubhawa yeformula wodzvanya kiyi. F9(usakanganwa kudzvanya Esckudzokera kune formula):
Izvo zvabuda zvakabuda zvinoreva kuti mune yekutanga zita rekambani yakatsveyama (GK Morozko OAO) yezvese kukosha muchikamu Kuwana yakawana yechipiri chete (Morozko), uye kutanga kubva ku4th character mumutsara.
Zvino ngatiwedzerei basa kune yedu formula ONA(TARISA MUDENGA):
Iri basa rine nharo nhatu:
- Ukoshi hunodiwa - Unogona kushandisa chero nhamba yakakura zvakakwana (chinonyanya kukosha ndechekuti inodarika kureba kwechero chinyorwa mune sosi data)
- Viewed_vector - chiyero kana dura ratiri kutsvaga kukosha kwatinoda. Heino basa rakambounzwa KUWANA, iyo inodzosa muunganidzwa {#VALUE!:4:#VALUE!}
- Vector_zvawanikwa - chiyero kubva kwatinoda kudzorera kukosha kana kukosha kunodiwa kunowanikwa musero rinoenderana. Heano mazita echokwadi kubva muchikamu Substitute tafura yedu yereferensi.
Chinhu chikuru uye chisiri pachena pano ndechekuti basa racho ONA kana pasina machisi chaiwo, gara uchitsvaga diki iri padyo (yapfuura) kukosha. Nokudaro, nekutsanangura chero nhamba huru (semuenzaniso, 9999) sehuwandu hunodiwa, tichamanikidza. ONA tsvaga sero rine nhamba diki iri padyo (4) muhurongwa {#VALUE!:4:#VALUE!} uye wodzora kukosha kunofambirana kubva kune yabuda vhekita, kureva zita rekambani chairo kubva muchikamu. Substitute.
Yechipiri nuance ndeyekuti, tekinoroji, fomula yedu ndeye array formula, nekuti basa KUWANA inodzoka semhedzisiro kwete imwe chete, asi nhevedzano yemhando nhatu. Asi sezvo basa ONA inotsigira arrays kunze kwebhokisi, saka isu hatifanirwe kuisa iyi fomula seyekirasi array formula - uchishandisa keyboard nzira yekudimbudzira. Ctrl+kusuduruka+pinda. Zviri nyore zvichakwana pinda.
Ndizvo zvose. Ndinovimba wawana zvine musoro.
Inosara kuendesa fomula yakapedzwa kune yekutanga sero B2 yekoramu wakasimba - uye basa redu rinogadziriswa!
Ehe, nematafura akajairwa (kwete akangwara), iyi fomula inoshandawo zvakanaka (ingo usakanganwa nezve kiyi F4 nekugadzirisa zvinongedzo zvinoenderana):
Nyaya 2. Kutsiviwa kwehuwandu
Nyaya iyi idiki diki. Zvakare tine matafura maviri e "smart":
Tafura yekutanga ine kero dzakatsveyama dzinoda kugadziriswa (ndakadaidzira Dhata2) Yechipiri tafura ibhuku rereferenzi, maererano nezvaunoda kuita chikamu chekutsiva chidimbu mukati mekero (ndakadaidza iyi tafura. Kutsiva2).
Musiyano wakakosha pano ndewekuti iwe unofanirwa kutsiva chete chidimbu che data rekutanga - semuenzaniso, kero yekutanga ine isiriyo. “St. Petersburg” kurudyi “St. Petersburg”, ichisiya imwe kero (zip code, street, house) sezvairi.
Iyo yakapedzwa fomula ichaita senge iyi (kuitira nyore kuona, ndakaipatsanura kuita mitsetse mingani inoshandisa alt+pinda):
Basa guru pano rinoitwa neyakajairwa Excel text function SUBSTITUTE (SUBSTITUTE), iyo ine nharo nhatu:
- Kwakabva mameseji - kero yekutanga yakatsveyama kubva kuKero column
- Zvatiri kutsvaga - pano isu tinoshandisa hunyengeri nebasa ONA (TARISA MUDENGA)kubva kune yakapfuura nzira yekudhonza kukosha kubva pambiru Kuwana, iyo inosanganisirwa sechidimbu mukero yakakomberedzwa.
- Chii chatinofanira kutsiva nacho - nenzira imwecheteyo tinowana kukosha kwakakodzera kunoenderana nayo kubva kukoromo Substitute.
Pinda fomula iyi ne Ctrl+kusuduruka+pinda haidiwe pano zvakare, kunyangwe iri, chokwadi, iri array formula.
Uye zvinoonekwa zvakajeka (ona #N/A zvikanganiso pamufananidzo wapfuura) kuti fomula yakadaro, yekunaka kwayo yese, ine akati wandei anokanganisa:
- basa SUBSTITUTE inobata nyaya, saka "Spb" mumutsara wekupedzisira hauna kuwanikwa mutafura yekutsiva. Kugadzirisa dambudziko iri, unogona kana kushandisa basa ZAMENIT (REPLACE), kana kuti tanga waunza matafura ese ari maviri kurejista imwechete.
- Kana chinyorwa pakutanga chakarurama kana mariri hapana chidimbu chinotsiviwa (mutsara wekupedzisira), ipapo fomula yedu inokanda chikanganiso. Iyi nguva inogona kudzoreredzwa nekubata uye kutsiva zvikanganiso uchishandisa basa IFERROR (IFERROR):
- Kana chinyorwa chekutanga chine zvimedu zvakawanda kubva mudhairekitori kamwechete, ipapo fomula yedu inotsiva chete yekupedzisira (mumutsara wechisere, Ligovsky «Avenue« Change to "pr-t", Asi "S-Pb" on “St. Petersburg” kwete, nokuti "S-Pb” yakakwira mudhairekitori). Dambudziko iri rinogona kugadziriswa nekumhanyisazve yedu fomula, asi yatove pamwe nekoramu wakasimba:
Haina kukwana uye inonetsa munzvimbo, asi zviri nani kupfuura iyo yakafanana yekutsiva bhuku, handiti? 🙂
PS
Muchinyorwa chinotevera, isu tichaona maitiro ekuita iyo yakawanda inotsiva uchishandisa macros uye Power Query.
- Mashandiro anoita SUBSTITUTE basa kutsiva mavara
- Kutsvaga Chaiyo Chinyorwa Machisi Uchishandisa EXACT Basa
- Nyaya yekutsvaga nekutsiviwa kwenyaya (nyaya inonzwa VLOOKUP)