Զանգվածային տեքստի փոխարինում բանաձևերով

Ենթադրենք, դուք ունեք մի ցուցակ, որտեղ տարբեր աստիճանի «ուղղակի» գրված են նախնական տվյալները, օրինակ՝ հասցեները կամ ընկերության անվանումները.

Հստակ երևում է, որ նույն քաղաքը կամ ընկերությունն այստեղ ներկա է խայտաբղետ տարբերակներով, ինչը, ակնհայտորեն, հետագայում շատ խնդիրներ կստեղծի այս սեղանների հետ աշխատելիս։ Եվ եթե մի փոքր մտածեք, կարող եք գտնել նմանատիպ առաջադրանքների բազմաթիվ օրինակներ այլ ոլորտներից:

Հիմա պատկերացրեք, որ նման խեղաթյուրված տվյալներ ձեզ պարբերաբար գալիս են, այսինքն՝ սա ոչ թե մեկանգամյա «ձեռքով շտկել, մոռացեք» պատմություն է, այլ կանոնավոր և մեծ թվով բջիջների խնդիր:

Ինչ անել? «Գտնել և փոխարինել» վանդակի միջոցով կամ սեղմելով՝ ձեռքով մի փոխարինեք ծուռ տեքստը 100500 անգամ ճիշտով Ctrl+H?

Առաջին բանը, որ գալիս է մտքում նման իրավիճակում, զանգվածային փոխարինում կատարելն է՝ ըստ նախապես կազմված սխալ և ճիշտ տարբերակների համապատասխան տեղեկատու գրքի, այսպես.

Ցավոք, նման առաջադրանքի ակնհայտ տարածվածության պայմաններում Microsoft Excel-ը չունի այն լուծելու պարզ ներկառուցված մեթոդներ: Սկսենք, եկեք պարզենք, թե ինչպես դա անել բանաձևերով, առանց «ծանր հրետանի» ներգրավելու VBA-ի կամ Power Query-ում մակրոների տեսքով:

Դեպք 1. Զանգվածային ամբողջական փոխարինում

Սկսենք համեմատաբար պարզ դեպքից. իրավիճակ, երբ պետք է փոխարինել հին ծուռ տեքստը նորով: լրիվ.

Ենթադրենք, մենք ունենք երկու աղյուսակ.

Առաջինում՝ ընկերությունների բնօրինակ խայտաբղետ անվանումները։ Երկրորդում՝ նամակագրության տեղեկագիրք: Եթե ​​առաջին աղյուսակում գտնենք ընկերության անվանման մեջ սյունակի որևէ բառ Գտնել, ապա պետք է ամբողջությամբ փոխարինել այս ծուռ անունը ճիշտով՝ սյունակից Փոխարինեք երկրորդ որոնման աղյուսակը.

Հարմարության համար.

• Երկու աղյուսակները փոխակերպվում են դինամիկ («խելացի») ստեղնաշարի դյուրանցման միջոցով Ctrl+T կամ թիմ Ներդիր – Աղյուսակ (Ներդիր - Աղյուսակ).
  
• Հայտնվող ներդիրում շինարար (Դիզայն) անվանված առաջին աղյուսակը Ամսաթիվև երկրորդ տեղեկատու աղյուսակը – Substitutions.

Բանաձեւի տրամաբանությունը բացատրելու համար մի փոքր հեռվից գնանք։

Օրինակ վերցնելով A2 բջիջից առաջին ընկերությունը և ժամանակավորապես մոռանալով մնացած ընկերությունների մասին՝ փորձենք սյունակից որոշել, թե որ տարբերակը Գտնել հանդիպում է այնտեղ։ Դա անելու համար թերթի ազատ մասում ընտրեք ցանկացած դատարկ բջիջ և այնտեղ մուտքագրեք գործառույթը ԳՏՆԵԼ (ԳՏՆԵԼ):

Այս ֆունկցիան որոշում է, թե արդյոք տվյալ ենթատողը ներառված է (առաջին փաստարկը սյունակի բոլոր արժեքներն են Գտնել) սկզբնաղբյուր տեքստի մեջ (տվյալների աղյուսակից առաջին ընկերությունը) և պետք է դուրս բերի կամ նիշի հերթական համարը, որից գտնվել է տեքստը, կամ սխալ, եթե ենթատողը չի գտնվել:

Այստեղ հնարքն այն է, որ քանի որ մենք որպես առաջին արգումենտ նշել ենք ոչ թե մեկ, այլ մի քանի արժեք, այս ֆունկցիան նույնպես արդյունքում կվերադարձնի ոչ թե մեկ արժեք, այլ 3 տարրերից բաղկացած զանգված: Եթե ​​չունեք Office 365-ի վերջին տարբերակը, որն աջակցում է դինամիկ զանգվածներ, ապա այս բանաձևը մուտքագրելուց և սեղմելուց հետո Մտնել դուք կտեսնեք այս զանգվածը հենց թերթիկի վրա.

Եթե ​​ունեք Excel-ի նախորդ տարբերակները, ապա սեղմելուց հետո Մտնել արդյունքի զանգվածից կտեսնենք միայն առաջին արժեքը, այսինքն՝ #VALUE սխալ: (#ԱՐԺԵՔ!).

Դուք չպետք է վախենաք 🙂 Փաստորեն, մեր բանաձևը աշխատում է, և դուք դեռ կարող եք տեսնել արդյունքների ամբողջ զանգվածը, եթե ընտրեք մուտքագրված գործառույթը բանաձևի տողում և սեղմեք ստեղնը: F9(պարզապես մի մոռացեք սեղմել Escվերադառնալ բանաձևին).

Արդյունքների արդյունքում ստացված զանգվածը նշանակում է, որ ընկերության սկզբնական կեղծ անվանմամբ (GK Morozko OAO) բոլոր արժեքները սյունակում Գտնել գտել է միայն երկրորդը (Մորոզկո), և սկսած 4-րդ անընդմեջ նիշից։

Հիմա եկեք մի ֆունկցիա ավելացնենք մեր բանաձեւին VIEW(ՓՆՏՐԵԼ):

Այս ֆունկցիան ունի երեք արգումենտ.

1. Ցանկալի արժեք - կարող եք օգտագործել ցանկացած բավականաչափ մեծ թիվ (գլխավորն այն է, որ այն գերազանցում է աղբյուրի տվյալների ցանկացած տեքստի երկարությունը)
   
2. Դիտված_վեկտոր – տիրույթը կամ զանգվածը, որտեղ մենք փնտրում ենք ցանկալի արժեքը: Ահա նախկինում ներկայացված գործառույթը ԳՏՆԵԼ, որը վերադարձնում է զանգված {#VALUE!:4:#VALUE!}
   
3. Վեկտոր_արդյունքները – միջակայքը, որից մենք ցանկանում ենք վերադարձնել արժեքը, եթե համապատասխան բջիջում գտնվի ցանկալի արժեքը: Ահա սյունակի ճիշտ անունները Փոխարինեք մեր տեղեկատու աղյուսակը:
   

Այստեղ հիմնական և ոչ ակնհայտ առանձնահատկությունն այն է, որ գործառույթը VIEW եթե ճշգրիտ համընկնում չկա, միշտ փնտրում է ամենափոքր (նախորդ) արժեքը. Հետևաբար, որպես ցանկալի արժեք նշելով ցանկացած մեծ թիվ (օրինակ՝ 9999), մենք կստիպենք. VIEW Գտե՛ք {#VALUE!:4:#VALUE!} զանգվածում ամենափոքր թվով (4) բջիջը և արդյունքի վեկտորից վերադարձրեք համապատասխան արժեքը, այսինքն՝ ճիշտ ընկերության անվանումը սյունակից: Փոխարինեք.

Երկրորդ նրբությունն այն է, որ տեխնիկապես մեր բանաձևը զանգվածային բանաձև է, քանի որ ֆունկցիան ԳՏՆԵԼ որպես արդյունք վերադարձնում է ոչ թե մեկ, այլ երեք արժեքների զանգված: Բայց քանի որ գործառույթը VIEW աջակցում է արկղից դուրս զանգվածներ, այնուհետև մենք չպետք է մուտքագրենք այս բանաձևը որպես դասական զանգվածի բանաձև՝ օգտագործելով ստեղնաշարի դյուրանցում Ctrl+Հերթափոխություն+Մտնել. Պարզ մեկը բավական կլինի Մտնել.

Այսքանը: Հուսով եմ, որ դուք հասկանում եք տրամաբանությունը:

Մնում է պատրաստի բանաձևը փոխանցել սյունակի առաջին բջիջ B2 Կայուն - և մեր խնդիրը լուծված է:

Իհարկե, սովորական (ոչ խելացի) սեղանների դեպքում այս բանաձևը նույնպես հիանալի է աշխատում (պարզապես մի մոռացեք բանալին F4 և ամրացնելով համապատասխան հղումները):

Դեպք 2. Զանգվածային մասնակի փոխարինում

Այս գործը մի փոքր ավելի բարդ է։ Կրկին մենք ունենք երկու «խելացի» աղյուսակ.

Առաջին աղյուսակը՝ ծուռ գրված հասցեներով, որը պետք է ուղղել (ես անվանեցի Տվյալներ 2) Երկրորդ աղյուսակը տեղեկատու գիրք է, ըստ որի պետք է հասցեի ներսում ենթալարի մասնակի փոխարինում կատարել (ես անվանել եմ այս աղյուսակը Փոխարինումներ 2).

Այստեղ հիմնարար տարբերությունն այն է, որ դուք պետք է փոխարինեք սկզբնական տվյալների միայն մի հատված, օրինակ՝ առաջին հասցեն սխալ է: «Սբ. Պետերբուրգ» աջ «Սբ. Պետերբուրգ», մնացած հասցեն (փոստային ինդեքս, փողոց, տուն) թողնելով ինչպես կա։

Ավարտված բանաձևը կունենա այսպիսի տեսք (ընկալման հեշտության համար ես այն բաժանեցի քանի տողերի օգտագործմամբ ալտ+Մտնել):

Այստեղ հիմնական աշխատանքը կատարվում է ստանդարտ Excel տեքստային ֆունկցիայի միջոցով ՓՈԽԱՐITԱԿ (ՓՈԽԱՐԱԿԻՑ), որն ունի 3 փաստարկ.

1. Աղբյուրի տեքստ – առաջին ծուռ հասցեն Հասցե սյունակից
2. Այն, ինչ մենք փնտրում ենք. այստեղ մենք օգտագործում ենք ֆունկցիայի հնարքը VIEW (ՓՆՏՐԵԼ)արժեքը սյունակից հանելու նախորդ եղանակից Գտնել, որը որպես հատված ներառված է կոր հասցեում։
3. Ինչով փոխարինել – նույն կերպ մենք սյունակից գտնում ենք դրան համապատասխանող ճիշտ արժեքը Փոխարինեք.

Մուտքագրեք այս բանաձևը Ctrl+Հերթափոխություն+Մտնել այստեղ նույնպես պետք չէ, թեև դա, ըստ էության, զանգվածի բանաձև է։

Եվ պարզ երևում է (տես #N/A սխալները նախորդ նկարում), որ նման բանաձևը, չնայած իր ողջ նրբագեղությանը, ունի մի քանի թերություններ.

• ֆունկցիա SUBSTITUTE-ը մեծատառերի զգայուն է, ուստի նախավերջին տողում «Spb»-ը փոխարինման աղյուսակում չի գտնվել։ Այս խնդիրը լուծելու համար կարող եք կամ օգտագործել ֆունկցիան ԶԱՄԵՆԻԹ (ՓՈԽԱՐԻՆԵԼ), կամ նախապես բերեք երկու աղյուսակները նույն գրանցամատյանում։
• Եթե ​​տեքստը ի սկզբանե ճիշտ է կամ դրա մեջ փոխարինելու բեկոր չկա (վերջին տող), ապա մեր բանաձեւը սխալ է նետում: Այս պահը կարող է չեզոքացվել ֆունկցիան օգտագործելով սխալները ընդհատելով և փոխարինելով ԻՐԱER (ԻՖԵՐՈՐ):

  

• Եթե ​​բնօրինակ տեքստը պարունակում է գրացուցակից միանգամից մի քանի հատված, ապա մեր բանաձեւը փոխարինում է միայն վերջինին (8-րդ տողում՝ Լիգովսկի «Պողոտա« փոխվել է «pr-t», Բայց «S-Pb» on «Սբ. Պետերբուրգ» այլևս, քանի որ «S-Pb” գրացուցակում ավելի բարձր է): Այս խնդիրը կարող է լուծվել՝ նորից գործարկելով մեր սեփական բանաձևը, բայց արդեն սյունակի երկայնքով Կայուն:

  
  

Տեղ-տեղ կատարյալ և ծանրաբեռնված չէ, բայց շատ ավելի լավ է, քան նույն ձեռքով փոխարինելը, այնպես չէ՞: 🙂

PS

Հաջորդ հոդվածում մենք կպարզենք, թե ինչպես կարելի է իրականացնել նման զանգվածային փոխարինում՝ օգտագործելով մակրոները և Power Query-ը:

• Ինչպես է աշխատում SUBSTITUTE ֆունկցիան՝ տեքստը փոխարինելու համար
  
• Գտնել ճշգրիտ տեքստային համընկնումներ՝ օգտագործելով EXACT ֆունկցիան
• Գործի զգայուն որոնում և փոխարինում (գործերի զգայուն VLOOKUP)