Seach gu bheil liosta agad anns a bheil, le diofar ìrean de “sìmplidh,” sgrìobhte dàta tùsail - mar eisimpleir, seòlaidhean no ainmean chompanaidhean:
 |  |
Tha e soilleir gu bheil an aon bhaile-mòr no companaidh an làthair an seo ann an caochlaidhean motley, a chruthaicheas, gu follaiseach, tòrr dhuilgheadasan nuair a bhios tu ag obair leis na bùird sin san àm ri teachd. Agus ma smaoinicheas tu beagan, gheibh thu tòrr eisimpleirean de ghnìomhan co-chosmhail bho raointean eile.
A-nis smaoinich gu bheil an leithid de dhàta cam a’ tighinn thugad gu cunbhalach, ie chan e sgeulachd aon-ùine a tha seo “càradh le làimh, dìochuimhnich e”, ach duilgheadas gu cunbhalach agus ann an àireamh mhòr de cheallan.
Dè a nì thu? Na cuir an teacsa ceart an àite an teacsa cam 100500 uair le làimh tron bhogsa “Lorg is Ath-chuir” no le bhith a’ cliogadh Ctrl+H?
Is e a’ chiad rud a thig gu inntinn ann an suidheachadh mar seo a bhith a’ dèanamh mòr-àite a rèir leabhar fiosrachaidh a chaidh a chur ri chèile ro-làimh mu bhith a’ maidseadh roghainnean ceàrr is ceart - mar seo:
Gu mì-fhortanach, leis cho follaiseach ‘s a tha a leithid de ghnìomh, chan eil dòighean sìmplidh aonaichte aig Microsoft Excel airson a fhuasgladh. Sa chiad dol a-mach, leig dhuinn a-mach ciamar a nì sinn seo le foirmlean, gun a bhith a’ toirt a-steach “làmhachas trom” ann an cruth macros ann an VBA no Power Query.
Cùis 1. Bulk làn ath-chur
Feuch an tòisich sinn le cùis gu math sìmplidh - suidheachadh far am feum thu fear ùr a chuir an àite an t-seann theacsa cam. làn.
Canaidh sinn gu bheil dà chlàr againn:
Anns a 'chiad - an ainmean tùsail eadar-dhealaichte de chompanaidhean. Anns an dàrna - leabhar fiosrachaidh conaltraidh. Ma lorgas sinn ann an ainm a’ chompanaidh sa chiad chlàr facal sam bith bhon cholbh Gus a lorg, feumaidh tu an t-ainm cam seo a chuir na àite gu tur leis an fhear cheart - bhon cholbh Neach-ionaid an dàrna clàr sgrùdaidh.
Airson goireasachd:
- Tha an dà chlàr air an tionndadh gu fiùghantach (“smart”) a’ cleachdadh ath-ghoirid meur-chlàr Ctrl+T no sgioba Cuir a-steach - Clàr (Cuir a-steach - Clàr).
- Air an taba a nochdas Neach-togail (Dealbhadh) a’ chiad chlàr air ainmeachadh Dàta, agus an dara clàr iomraidh — Luchd-ionaid.
Gus loidsig na foirmle a mhìneachadh, rachamaid beagan air falbh.
A’ toirt a’ chiad chompanaidh bho chealla A2 mar eisimpleir agus a’ dìochuimhneachadh airson ùine ghoirid mun chòrr de na companaidhean, feuchaidh sinn ri faighinn a-mach dè an roghainn bhon cholbh Gus a lorg a' coinneachadh ann. Gus seo a dhèanamh, tagh cealla falamh sam bith ann am pàirt an-asgaidh na duilleige agus cuir a-steach an gnìomh an sin A FHAOBH (FAIGHINN):
Bidh an gnìomh seo a’ dearbhadh a bheil an substring a chaidh a thoirt a-steach (is e a’ chiad argamaid na luachan uile bhon cholbh Gus a lorg) a-steach don teacsa tùsail (a’ chiad chompanaidh bhon chlàr dàta) agus bu chòir dhaibh an dàrna cuid àireamh òrdail a’ charactar bhon deach an teacsa a lorg, no mearachd mura deach am fo-thalamh a lorg.
Is e an cleas an seo, leis nach do shònraich sinn aon, ach grunn luachan mar a’ chiad argamaid, gun till an gnìomh seo mar thoradh air sin chan e aon luach, ach sreath de 3 eileamaidean. Mura h-eil an tionndadh as ùire de Office 365 agad a bheir taic do arrays fiùghantach, an uairsin às deidh dhut am foirmle seo a chuir a-steach agus cliog air Cuir a-steach chì thu an t-sreath seo dìreach air an duilleig:
Ma tha tionndaidhean roimhe de Excel agad, an uairsin às deidh dhut briogadh air Cuir a-steach chan fhaic sinn ach a’ chiad luach bhon raon thoraidhean, ie mearachd #VALUE! (#LUACH!).
Cha bu chòir eagal a bhith ort 🙂 Gu dearbh, tha am foirmle againn ag obair agus chì thu fhathast an raon iomlan de thoraidhean ma thaghas tu an gnìomh a chaidh a chuir a-steach sa bhàr fhoirmeil agus brùth air an iuchair F9(dìreach na dìochuimhnich brùth Esca dhol air ais chun na foirmle):
Tha an sreath de thoraidhean a thig às a’ ciallachadh sin ann an ainm tùsail a’ chompanaidh cam (GK Morozko OAO) de gach luach ann an colbh Gus a lorg lorg ach an dàrna fear (Morozko), agus a’ tòiseachadh bhon 4mh caractar ann an sreath.
A-nis leig dhuinn gnìomh a chuir ris an fhoirmle againn VIEW(THOIR SÙIL AIR):
Tha trì argamaidean aig a’ ghnìomh seo:
- Luach miannaichte - faodaidh tu àireamh sam bith mòr gu leòr a chleachdadh (is e am prìomh rud gu bheil e nas àirde na fad teacsa sam bith san dàta stòr)
- Air fhaicinn_vector - an raon no an raon far a bheil sinn a’ coimhead airson an luach a tha thu ag iarraidh. Seo an gnìomh a chaidh a thoirt a-steach roimhe A FHAOBH, a thilleas sreath {#VALUE!:4:#VALUE!}
- Vector_toraidhean - an raon bhon a tha sinn airson an luach a thilleadh ma lorgar an luach a tha thu ag iarraidh anns a’ chill fhreagarrach. Seo na h-ainmean ceart bhon cholbh Neach-ionaid ar clàr-fiosrachaidh.
Is e am prìomh fheart agus nach eil follaiseach an seo gu bheil an gnìomh VIEW mura h-eil maids ceart ann, an-còmhnaidh coimhead airson an luach as lugha (roimhe) as fhaisge. Mar sin, le bhith a’ sònrachadh àireamh hefty sam bith (mar eisimpleir, 9999) mar an luach a tha thu ag iarraidh, cuiridh sinn an gnìomh VIEW lorg an cealla leis an àireamh as lugha as fhaisge (4) san raon {#VALUE!:4:#VALUE!} agus thoir air ais an luach co-fhreagarrach bhon vectar toraidh, ie ainm ceart na companaidh bhon cholbh Neach-ionaid.
Is e an dàrna nuance gu bheil, gu teicnigeach, ar foirmle mar fhoirmle rèite, air sgàth gnìomh A FHAOBH tilleadh mar thoraidhean chan e aon, ach sreath de thrì luachan. Ach bhon a tha an gnìomh VIEW a’ toirt taic do arrays a-mach às a’ bhogsa, an uairsin chan fheum sinn am foirmle seo a chuir a-steach mar fhoirmle rèite clasaigeach - a’ cleachdadh ath-ghoirid meur-chlàr Ctrl+Shift+Cuir a-steach. Bidh fear sìmplidh gu leòr Cuir a-steach.
Sin e. An dòchas gum faigh thu an loidsig.
Tha e fhathast gus an fhoirmle chrìochnaichte a ghluasad chun chiad chealla B2 den cholbh Chàir - agus tha an obair againn air fhuasgladh!
Gu dearbh, le bùird àbhaisteach (chan eil e glic), tha am foirmle seo cuideachd ag obair gu math (dìreach na dìochuimhnich mun iuchair F4 agus a’ càradh nan ceanglaichean iomchaidh):
Cùis 2. Ath-nuadhachadh pàirt mhòr
Tha a 'chùis seo beagan nas duilghe. A-rithist tha dà chlàr “smart” againn:
A 'chiad chlàr le seòlaidhean cam sgrìobhte a dh'fheumar a cheartachadh (dh'ainmich mi e Dàta2). Is e leabhar fiosrachaidh a th’ anns an dàrna clàr, a rèir am feum thu pàirt a chuir an àite fo-thalamh taobh a-staigh an t-seòlaidh (dh’ ainmich mi an clàr seo Luchd-ionaid2).
Is e an eadar-dhealachadh bunaiteach an seo nach fheum thu ach criomag den dàta tùsail a chuir na àite - mar eisimpleir, tha mearachd ceàrr aig a’ chiad sheòladh “St. Petersburg" air an làimh dheis “St. Petersburg", a’ fàgail a’ chòrr den t-seòladh (còd zip, sràid, taigh) mar a tha.
Bidh am foirmle crìochnaichte a 'coimhead mar seo (airson a bhith furasta a thuigsinn, roinn mi e a-steach don àireamh de loidhnichean a bha a' cleachdadh Alt+Cuir a-steach):
Tha a’ phrìomh obair an seo air a dhèanamh leis a’ ghnìomh àbhaisteach teacsa Excel SUBSTITUIBH (SUBSTITUTE), aig a bheil 3 argamaidean:
- Teacs stòr - a’ chiad sheòladh cam bhon cholbh Seòladh
- Na tha sinn a 'sireadh - an seo bidh sinn a' cleachdadh an cleas leis a 'ghnìomh VIEW (THOIR SÙIL AIR)bhon dòigh roimhe gus an luach a tharraing bhon cholbh Gus a lorg, a tha air a ghabhail a-steach mar chriomag ann an seòladh lùbte.
- Dè a thèid a chur na àite - san aon dòigh lorg sinn an luach ceart a fhreagras air bhon cholbh Neach-ionaid.
Cuir a-steach am foirmle seo le Ctrl+Shift+Cuir a-steach chan eil feum air an seo nas motha, ged a tha e, gu dearbh, foirmle rèite.
Agus tha e ri fhaicinn gu soilleir (faic # N/A mearachdan san dealbh roimhe) gu bheil dà eas-bhuannachd aig foirmle mar sin, airson a h-uile eireachdas:
- Dleastanas Tha SUBSTITUTE mothachail air cùis, mar sin cha deach “Spb” anns an loidhne mu dheireadh a lorg anns a’ chlàr ùr. Gus an duilgheadas seo fhuasgladh, faodaidh tu an dàrna cuid an gnìomh a chleachdadh ZAmenIT (ATH-LEASACHADH), no thoir an dà chlàr chun an aon chlàr ro-làimh.
- Ma tha an teacsa ceart an toiseach no ann chan eil criomag ri chur na àite (loidhne mu dheireadh), an uairsin bidh am foirmle againn a’ tilgeil mearachd. Faodar a’ mhionaid seo a neodachadh le bhith a’ gabhail a-steach agus a’ cur an àite mhearachdan a’ cleachdadh a’ ghnìomh IFERROR (IFERROR):
- Ma tha an teacsa tùsail ann grunn chriomagan bhon eòlaire aig an aon àm, an uairsin bidh am foirmle againn a’ dol an àite an tè mu dheireadh a-mhàin (anns an 8mh loidhne, Ligovsky «Avenue« atharrachadh gu "pr-t", Ach “S-Pb” on “St. Petersburg" chan eil tuilleadh, oir “S-Pb” nas àirde san eòlaire). Faodar an duilgheadas seo fhuasgladh le bhith ag ath-ruith ar foirmle fhèin, ach mu thràth air feadh a’ cholbh Chàir:
Chan eil e foirfe agus trom ann an àiteachan, ach fada nas fheàrr na an aon ath-nuadhachadh làimhe, ceart? 🙂
PS
Anns an ath artaigil, bidh sinn a’ faighinn a-mach mar a chuireas tu a leithid de ionadachadh mòr an gnìomh a ’cleachdadh macros agus Power Query.
- Mar a tha an gnìomh SUBSTITUTE ag obair an àite teacsa
- A' lorg maidsean teacsa ceart a' cleachdadh an gnìomh EXACT
- Rannsachadh cùis-mhothachail agus ionadachadh (VLOOKUP a tha mothachail air cùis)