Формулалар менен жапырт текст алмаштыруу

Сиздин тизмеңиз бар дейли, анда ар кандай деңгээлдеги "түз" баштапкы маалыматтар жазылган - мисалы, даректер же компаниянын аталыштары:

Бул жерде бир эле шаар же компаниянын ала-була варианттары бар экени айкын көрүнүп турат, бул келечекте бул таблицалар менен иштөөдө көптөгөн көйгөйлөрдү жаратаары анык. Ал эми бир аз ойлонсоңуз, башка аймактардан ушул сыяктуу тапшырмалардын көптөгөн мисалдарын таба аласыз.

Эми элестетип көрүңүзчү, мындай кыйшык маалыматтар сизге дайыма келип турат, башкача айтканда, бул бир жолку “кол менен оңдоп, унут” деген окуя эмес, дайыма жана көп сандагы клеткалардагы көйгөй.

Эмне кылуу керек? "Тап жана алмаштыруу" кутучасы же чыкылдатуу аркылуу кыйшык текстти 100500 жолу туурасына кол менен алмаштырбаңыз. Ctrl+H?

Мындай кырдаалда акылга келген биринчи нерсе, туура эмес жана туура варианттарды дал келүү боюнча алдын ала түзүлгөн маалымдама боюнча массалык алмаштырууну жасоо болуп саналат - бул сыяктуу:

Тилекке каршы, мындай тапшырманын айкын таралышы менен, Microsoft Excel аны чечүү үчүн жөнөкөй орнотулган ыкмалары жок. Баштоо үчүн, келгиле, VBA же Power Queryдеги макрос түрүндөгү “оор артиллерияны” тартпастан, формулалар менен муну кантип жасоону карап көрөлү.

Case 1. Жапырт толук алмаштыруу

Салыштырмалуу жөнөкөй окуядан баштайлы – эски кыйшык текстти жаңысына алмаштыруу керек болгон кырдаал. толугу менен.

Бизде эки үстөл бар дейли:

Биринчисинде – компаниялардын оригиналдуу түрдүү аталыштары. Экинчисинде - кат алышуу үчүн маалымдама. Эгерде биз биринчи таблицадагы компаниянын атынан тилкеден каалаган сөздү тапсак Табуу, анда сиз толугу менен туура бир бул ийри атын алмаштыруу керек - тилкеден алмаштыруу экинчи издөө таблицасы.

Ыңгайлуулук үчүн:

• Эки таблица тең баскычтоптун жардамы менен динамикага («акылдуу») айландырылат Ctrl+T же команда Кыстаруу – Таблица (Киргизүү — Таблица).
  
• Пайда болгон өтмөктө Конструктор (Дизайн) биринчи таблица аталган маалыматтар, жана экинчи маалымдама таблицасы – алмаштыруулар.

Формуланын логикасын түшүндүрүү үчүн алыстан бир аз баралы.

Мисал катары A2 уячасынан биринчи компанияны алып, калган компанияларды убактылуу унутуп, мамычадан кайсы вариантты аныктоого аракет кылалы Табуу ошол жерден жолугат. Бул үчүн, барактын бош бөлүгүндөгү каалаган бош уячаны тандап, ошол жерге функцияны киргизиңиз ТАБУУ (ТАП):

Бул функция берилген ички сап камтылганын аныктайт (биринчи аргумент тилкедеги бардык маанилер Табуу) баштапкы текстке (маалымат таблицасындагы биринчи компания) же текст табылган символдун иреттик номерин, же эгер подсап табылбаса катаны чыгарышы керек.

Бул жердеги куулук, биз биринчи аргумент катары бир эмес, бир нече маанини көрсөткөндүктөн, бул функция да натыйжада бир маанини эмес, 3 элементтен турган массивди кайтарат. Эгер сизде динамикалык массивдерди колдогон Office 365тин эң акыркы версиясы жок болсо, анда бул формуланы киргизип, чыкылдатыңыз кирүү сиз бул массивди барактан көрөсүз:

Эгерде сизде Excelдин мурунку версиялары болсо, анда чыкылдатыңыз кирүү биз натыйжа массивинен биринчи маанини гана көрөбүз, башкача айтканда ката #VALUE! (#VALUE!).

Сиз коркпошуңуз керек 🙂 Чынында, биздин формула иштейт жана формула тилкесинде киргизилген функцияны тандап, баскычты бассаңыз, натыйжалардын бардык массивдерин көрө аласыз. F9(басууну унутпаңыз Escформулага кайтуу үчүн):

Натыйжалардын жыйындысы баштапкы ийри компаниянын аталышын билдирет (Г.К. Морозко ОАО) тилкедеги бардык маанилердин Табуу экинчисин гана тапты (Морозко), жана катардагы 4-белгиден баштап.

Эми формулабызга функция кошолу КӨРҮҮ(ИЗДӨӨ):

Бул функциянын үч аргументи бар:

1. Каалаган баалуулук – сиз каалагандай чоң санды колдоно аласыз (негизгиси бул баштапкы маалыматтардагы каалаган тексттин узундугунан ашат)
   
2. Көрүлгөн_вектор – биз каалаган маанини издеп жаткан диапазон же массив. Бул жерде мурда киргизилген функция ТАБУУ, ал {#VALUE!:4:#VALUE!} массивин кайтарат
   
3. вектор_натыйжалары – тиешелүү уячада керектүү маани табылса, биз маанини кайтарууну каалаган диапазон. Бул жерде тилкеден туура ысымдар алмаштыруу биздин маалымдама таблицасы.
   

Бул жерде негизги жана ачык эмес өзгөчөлүгү болуп саналат КӨРҮҮ так дал келбесе, ар дайым эң жакынкы (мурунку) маанини издеңиз. Ошондуктан, каалаган чоң санды (мисалы, 9999) көрсөтүү менен, биз мажбурлайбыз КӨРҮҮ {#VALUE!:4:#VALUE!} массивинен эң жакын эң кичине саны (4) болгон уячаны табыңыз жана натыйжа векторунан тиешелүү маанини, б.а. мамычадан туура компаниянын аталышын кайтарыңыз алмаштыруу.

Экинчи нюанс, техникалык жактан биздин формула массив формуласы, анткени функция ТАБУУ натыйжа катары бир эмес, үч мааниден турган массивди кайтарат. Бирок функциядан бери КӨРҮҮ кутудан тышкары массивдерди колдойт, анда биз бул формуланы классикалык массив формуласы катары киргизүүнүн кереги жок – баскычтоптун жарлыгын колдонуу Ctrl+өзгөрүү+кирүү. Жөнөкөй эле жетиштүү болот кирүү.

Баары болду. Сиз логиканы аласыз деп үмүттөнөм.

Даяр формуланы тилкенин биринчи В2 уячасына өткөрүү калды туруктуу – жана биздин милдет чечилди!

Албетте, кадимки (акылдуу эмес) таблицаларда бул формула да сонун иштейт (ачкычты унутпаңыз F4 жана тиешелүү шилтемелерди бекитүү):

Case 2. Жапырт жарым-жартылай алмаштыруу

Бул иш бир аз татаалыраак. Бизде дагы эки "акылдуу" үстөл бар:

Оңдоо керек кыйшык жазылган даректери бар биринчи таблица (мен аны чакырдым Маалымат2). Экинчи таблица - бул маалымдама, ага ылайык даректин ичиндеги субсапты жарым-жартылай алмаштыруу керек (мен бул таблицаны чакырдым. Алмашуулар2).

Бул жерде негизги айырма, сиз баштапкы маалыматтардын бир гана фрагментин алмаштыруу керек - мисалы, биринчи дарек туура эмес «Ст. Петербург» укугу боюнча «Ст. Петербург», калган даректи (индекс, көчө, үй) ошол бойдон калтыруу.

Даяр формула ушундай болот (кабыл алуунун жеңилдиги үчүн мен аны канча сапка бөлдүм Alt+кирүү):

Бул жерде негизги иш стандарттуу Excel текст функциясы менен аткарылат АЛМАШТЫРУУ (АЛМАШТЫРУУ), анын 3 аргументи бар:

1. Булак тексти – Дарек тилкесиндеги биринчи кыйшык дарек
2. Биз издеп жаткан нерсе - бул жерде биз функция менен трюкту колдонобуз КӨРҮҮ (ИЗДӨӨ)мамычанын маанисин тартуу үчүн мурунку жолдон Табуу, ал ийри даректе фрагмент катары киргизилген.
3. Эмне менен алмаштыруу керек - ошол эле жол менен биз мамычадан ага туура келген туура маанини табабыз алмаштыруу.

Бул формула менен киргизиңиз Ctrl+өзгөрүү+кирүү бул жерде да кереги жок, бирок бул чындыгында массив формуласы.

Жана ачык көрүнүп турат (мурунку сүрөттө #N/A каталарын караңыз) мындай формула өзүнүн бардык кооздугуна карабастан, бир нече кемчиликтерге ээ:

• милдети SUBSTITUTE регистрге сезгич, ошондуктан акыркы сапта "Spb" алмаштыруу таблицада табылган жок. Бул көйгөйдү чечүү үчүн сиз функцияны колдонсоңуз болот ЗАМЕНИТ (АЛМАШТЫРУУ), же алдын ала эки таблицаны бир реестрге алып келиңиз.
• Эгерде текст башында туура же анда алмаштыруу үчүн эч кандай фрагмент жок (акыркы сап), анда биздин формула ката кетирет. Функцияны колдонуу менен каталарды кармоо жана алмаштыруу аркылуу бул учурду нейтралдаштырууга болот КАТА (КАТА):

  

• Эгерде баштапкы текст камтылса бир эле учурда каталогдон бир нече фрагменттерди, анда биздин формула акыркысын гана алмаштырат (8-сапта, Лиговский «жол« деп өзгөрдү "pr-t", Бирок "S-Pb" on «Ст. Петербург» мындан ары, анткени «S-Pb” каталогдо жогору турат). Бул көйгөйдү өзүбүздүн формулабызды кайра иштетүү жолу менен чечсе болот, бирок тилке боюнча туруктуу:

  
  

Жер-жерлерде кемчиликсиз жана түйшүктүү эмес, бирок ошол эле кол менен алмаштыруудан алда канча жакшы, туурабы? 🙂

PS

Кийинки макалада биз макросторду жана Power Query аркылуу мындай жапырт алмаштырууну кантип ишке ашырууну чечебиз.

• SUBSTITUTE функциясы текстти алмаштыруу үчүн кандай иштейт
  
• EXACT функциясынын жардамы менен тексттин так дал келүүсүн табуу
• Регистрге сезимтал издөө жана алмаштыруу (регистрди сезгич VLOOKUP)