Ejja ngħidu li għandek lista li fiha, bi gradi differenti ta’ “sempliċità”, tinkiteb id-dejta inizjali – pereżempju, indirizzi jew ismijiet tal-kumpaniji:
 |  |
Jidher ċar li l-istess belt jew kumpanija hija preżenti hawn f'varjanti motley, li, ovvjament, se joħolqu ħafna problemi meta jaħdmu ma 'dawn it-tabelli fil-futur. U jekk taħseb ftit, tista 'ssib ħafna eżempji ta' kompiti simili minn oqsma oħra.
Issa immaġina li data mgħaġġla bħal din tiġi għandek regolarment, jiġifieri din mhix storja ta 'darba "irranġaha manwalment, tinsaha", iżda problema fuq bażi regolari u f'numru kbir ta' ċelloli.
X'tagħmel? Tissostitwix manwalment it-test mgħawweġ 100500 darba b'dak it-tajjeb permezz tal-kaxxa "Sib u Ibdel" jew billi tikklikkja Ctrl+H?
L-ewwel ħaġa li tiġi f'moħħna f'sitwazzjoni bħal din hija li tagħmel sostituzzjoni tal-massa skont ktieb ta 'referenza miġbur minn qabel ta' tqabbil ta 'għażliet korretti u mhux korretti - bħal dan:
Sfortunatament, bil-prevalenza ovvja ta 'tali kompitu, Microsoft Excel m'għandux metodi sempliċi integrati biex issolviha. Biex tibda, ejja nsemmu kif tagħmel dan bil-formuli, mingħajr ma tinvolvi "artillerija tqila" fil-forma ta 'makros f'VBA jew Power Query.
Każ 1. Sostituzzjoni sħiħa bl-ingrossa
Nibdew b'każ relattivament sempliċi - sitwazzjoni fejn għandek bżonn tissostitwixxi t-test mgħawweġ l-antik b'wieħed ġdid. kompletament.
Ejja ngħidu li għandna żewġ tabelli:
Fl-ewwel - l-ismijiet varjati oriġinali tal-kumpaniji. Fit-tieni - ktieb ta 'referenza ta' korrispondenza. Jekk insibu fl-isem tal-kumpanija fl-ewwel tabella xi kelma mill-kolonna Issib, imbagħad għandek bżonn tissostitwixxi kompletament dan l-isem mgħawweġ b'dak korrett - mill-kolonna Sostitut it-tieni tabella ta' tfittxija.
Għall-konvenjenza:
- Iż-żewġ tabelli huma kkonvertiti għal dinamiċi (“intelliġenti”) bl-użu ta’ shortcut tat-tastiera Ctrl+T jew tim Daħħal – Tabella (Daħħal — Tabella).
- Fuq it-tab li tidher kostruttur (Disinn) l-ewwel tabella msemmija Data, u t-tieni tabella ta’ referenza – Sostituzzjonijiet.
Biex tispjega l-loġika tal-formula, ejja mmorru ftit mill-bogħod.
Nieħdu l-ewwel kumpanija miċ-ċellula A2 bħala eżempju u ninsew temporanjament dwar il-bqija tal-kumpaniji, ejja nippruvaw niddeterminaw liema għażla mill-kolonna Issib jiltaqa hemm. Biex tagħmel dan, agħżel kwalunkwe ċellula vojta fil-parti ħielsa tal-folja u daħħal il-funzjoni hemmhekk ISSIB (SIB):
Din il-funzjoni tiddetermina jekk is-substring mogħtija hijiex inkluża (l-ewwel argument huwa l-valuri kollha mill-kolonna Issib) fit-test sors (l-ewwel kumpanija mit-tabella tad-dejta) u għandha toħroġ jew in-numru ordinali tal-karattru li minnu nstab it-test, jew żball jekk is-substring ma nstabx.
Il-trick hawnhekk huwa li peress li speċifikajna mhux wieħed, iżda diversi valuri bħala l-ewwel argument, din il-funzjoni se terġa 'lura wkoll bħala riżultat mhux valur wieħed, iżda firxa ta' 3 elementi. Jekk ma jkollokx l-aħħar verżjoni ta' Office 365 li tappoġġja arrays dinamiċi, imbagħad wara li ddaħħal din il-formula u tikklikkja fuq Ikteb se tara din il-firxa dritt fuq il-folja:
Jekk għandek verżjonijiet preċedenti ta 'Excel, imbagħad wara li tikklikkja fuq Ikteb se naraw biss l-ewwel valur mill-firxa tar-riżultat, jiġifieri żball #VALUR! (#VALUR!).
M'għandekx tibża' 🙂 Fil-fatt, il-formula tagħna taħdem u xorta tista' tara l-firxa sħiħa tar-riżultati jekk tagħżel il-funzjoni mdaħħla fil-bar tal-formula u tagħfas iċ-ċavetta F9(biss tinsiex tagħfas Escbiex tmur lura għall-formula):
Il-firxa ta 'riżultati li tirriżulta tfisser li fl-isem oriġinali tal-kumpanija mgħawweġ (GK Morozko OAO) tal-valuri kollha f'kolonna Issib sabet biss it-tieni (Morozko), u tibda mir-4 karattru infila.
Issa ejja nżidu funzjoni mal-formula tagħna VIEW(ĦARES IL-FUQ):
Din il-funzjoni għandha tliet argumenti:
- Valur mixtieq – tista 'tuża kwalunkwe numru kbir biżżejjed (il-ħaġa prinċipali hija li jaqbeż it-tul ta' kwalunkwe test fid-dejta tas-sors)
- Viewed_vector – il-firxa jew l-array fejn qed infittxu l-valur mixtieq. Hawnhekk hawn il-funzjoni introdotta qabel ISSIB, li jirritorna firxa {#VALUE!:4:#VALUR!}
- Vector_riżultati – il-firxa li minnha rridu nirritornaw il-valur jekk il-valur mixtieq jinstab fiċ-ċellula korrispondenti. Hawn huma l-ismijiet korretti mill-kolonna Sostitut it-tabella ta' referenza tagħna.
Il-karatteristika ewlenija u mhux ovvja hawnhekk hija li l-funzjoni VIEW jekk ma jkunx hemm qbil eżatt, dejjem ifittex l-eqreb valur iżgħar (preċedenti).. Għalhekk, billi tispeċifika kwalunkwe numru kbir (per eżempju, 9999) bħala l-valur mixtieq, aħna se nisfurzaw VIEW sib iċ-ċellula bl-eqreb numru iżgħar (4) fl-array {#VALUE!:4:#VALUR!} u rritorna l-valur korrispondenti mill-vettur tar-riżultat, jiġifieri l-isem korrett tal-kumpanija mill-kolonna Sostitut.
It-tieni sfumatura hija li, teknikament, il-formula tagħna hija formula ta 'firxa, minħabba li l-funzjoni ISSIB jirritorna bħala riżultati mhux wieħed, iżda firxa ta' tliet valuri. Iżda peress li l-funzjoni VIEW jappoġġja arrays barra mill-kaxxa, allura m'għandniex għalfejn nidħlu din il-formula bħala formula array klassika - billi tuża shortcut tat-tastiera Ctrl+Shift+Ikteb. Wieħed sempliċi jkun biżżejjed Ikteb.
Dak kollox. Nittama li tikseb il-loġika.
Jibqa' li tittrasferixxi l-formula lesta għall-ewwel ċellola B2 tal-kolonna Fixed – u l-kompitu tagħna huwa solvut!
Naturalment, b'tabelli ordinarji (mhux intelliġenti), din il-formula taħdem ukoll tajjeb (biss ma ninsewx dwar iċ-ċavetta F4 u l-iffissar tal-links rilevanti):
Każ 2. Sostituzzjoni parzjali bl-ingrossa
Dan il-każ huwa ftit aktar delikat. Għal darb'oħra għandna żewġ tabelli "intelliġenti":
L-ewwel tabella b'indirizzi miktuba b'mod mgħawweġ li jeħtieġ li tiġi kkoreġuta (sejħitha Dejta2). It-tieni tabella hija ktieb ta 'referenza, li skontu għandek bżonn tagħmel sostituzzjoni parzjali ta' substring ġewwa l-indirizz (sejjaħt din it-tabella Sostituzzjonijiet2).
Id-differenza fundamentali hawnhekk hija li għandek bżonn tissostitwixxi biss framment tad-dejta oriġinali - per eżempju, l-ewwel indirizz għandu skorrett “St. Pietruburgu” fuq il-lemin “St. Pietruburgu”, li tħalli l-bqija tal-indirizz (kodiċi zip, triq, dar) kif inhu.
Il-formula lesta se tidher bħal din (għall-faċilità tal-perċezzjoni, qsejtha f'kemm linji tuża Alt+Ikteb):
Ix-xogħol ewlieni hawnhekk isir mill-funzjoni standard tat-test Excel SOSTITUT (SOSTITUT), li għandha 3 argumenti:
- Test tas-sors - l-ewwel indirizz mgħawweġ mill-kolonna Indirizz
- Dak li qed infittxu - hawnhekk nużaw it-trick bil-funzjoni VIEW (ĦARES IL-FUQ)mill-mod preċedenti biex tiġbed il-valur mill-kolonna Issib, li hija inkluża bħala framment f'indirizz mgħawweġ.
- X'għandek tissostitwixxi bi - bl-istess mod insibu l-valur korrett li jikkorrispondi għalih mill-kolonna Sostitut.
Daħħal din il-formula ma Ctrl+Shift+Ikteb mhix meħtieġa hawn lanqas, għalkemm hija, fil-fatt, formula array.
U jidher biċ-ċar (ara #N/A żbalji fl-istampa ta 'qabel) li formula bħal din, għall-eleganti kollha tagħha, għandha ftit żvantaġġi:
- funzjoni SOSTITUT huwa sensittiv għall-każ, għalhekk "Spb" fil-linja ta 'qabel l-aħħar ma nstabitx fit-tabella ta' sostituzzjoni. Biex issolvi din il-problema, tista 'jew tuża l-funzjoni ZAMENIT (Ssostitwixxi), jew preliminarjament iġib iż-żewġ tabelli fl-istess reġistru.
- Jekk it-test huwa inizjalment korrett jew fih m'hemm l-ebda framment li jissostitwixxi (l-aħħar linja), allura l-formula tagħna tarmi żball. Dan il-mument jista 'jiġi newtralizzat billi jiġu interċettati u sostitwiti l-iżbalji bl-użu tal-funzjoni JEKK Żball (JEKK ŻBALL):
- Jekk it-test oriġinali fih diversi frammenti mid-direttorju f'daqqa, allura l-formula tagħna tissostitwixxi biss l-aħħar waħda (fit-8 linja, Ligovsky «Vjal« inbidel għal "pr-t", Iżda “S-Pb” on “St. Pietruburgu” m'għadux, għax “S-Pb” huwa ogħla fid-direttorju). Din il-problema tista 'tiġi solvuta billi terġa' tħaddem il-formula tagħna stess, iżda diġà tul il-kolonna Fixed:
Mhux perfetta u ingombranti f'postijiet, iżda ħafna aħjar mill-istess sostituzzjoni manwali, hux? 🙂
PS
Fl-artiklu li jmiss, aħna ser insemmu kif nimplimentaw tali sostituzzjoni bl-ingrossa billi tuża macros u Power Query.
- Kif taħdem il-funzjoni SOSTITUT biex tissostitwixxi t-test
- Tfittxija ta 'Taqbila Eżatta tat-Test Bl-użu tal-Funzjoni EŻATTA
- Tfittxija u sostituzzjoni sensittiva għall-każ (VLOOKUP sensittiv għall-każ)