Масавая замена тэксту формуламі

Дапусцім, у вас ёсць спіс, у якім з рознай ступенню «прамалінейнасці» прапісаны зыходныя дадзеныя - напрыклад, адрасы або назвы кампаній:

Відавочна, што адзін і той жа горад ці кампанія прысутнічае тут у пярэстых варыянтах, што, відавочна, створыць масу праблем пры працы з гэтымі табліцамі ў будучыні. А калі крыху падумаць, то можна знайсці масу прыкладаў падобных задач з іншых абласцей.

А цяпер уявіце, што такія скажоныя дадзеныя прыходзяць да вас рэгулярна, г.зн. гэта не аднаразовая гісторыя «выправіць уручную, забудзьцеся», а праблема рэгулярна і ў вялікай колькасці вочак.

Што рабіць? Не замяняйце ўручную крывы тэкст 100500 разоў на правільны праз поле «Знайсці і замяніць» або націскаючы Ctrl+H?

Першае, што прыходзіць у галаву ў такой сітуацыі, - зрабіць масавую замену па загадзя складзеным даведніку супастаўлення няправільных і правільных варыянтаў - так:

На жаль, пры відавочнай распаўсюджанасці падобнай задачы, Microsoft Excel не мае простых убудаваных метадаў яе вырашэння. Для пачатку давайце разбярэмся, як гэта зрабіць з дапамогай формул, без прыцягнення «цяжкай артылерыі» ў выглядзе макрасаў ў VBA або Power Query.

Выпадак 1. Масавая поўная замена

Пачнем з адносна простага выпадку – сітуацыі, калі трэба замяніць стары крывы тэкст на новы. цалкам.

Дапусцім, у нас ёсць дзве табліцы:

У першай – арыгінальныя стракатыя назвы кампаній. У другім – даведнік перапіскі. Калі мы знойдзем у назве кампаніі ў першай табліцы любое слова з калонкі Для таго, каб знайсці, то трэба цалкам замяніць гэтую крывую назву на правільную – з слупка сурагат другая табліца пошуку.

Для зручнасці:

• Абедзве табліцы пераўтвараюцца ў дынамічныя («разумныя») з дапамогай спалучэння клавіш Ctrl+T або каманда Устаўка – Табліца (Устаўка — Табліца).
  
• На ўкладцы, якая з'явіцца Канструктар (Дызайн) першая табліца наз Дата, а другая даведачная табліца – Замены.

Каб растлумачыць логіку формулы, адыдзем крыху здалёк.

Узяўшы ў якасці прыкладу першую кампанію з ячэйкі А2 і часова забыўшыся пра астатнія кампаніі, давайце паспрабуем вызначыць, які варыянт з слупка Для таго, каб знайсці сустракаецца там. Для гэтага ў вольнай частцы ліста вылучыце любую пустую вочка і ўвядзіце туды функцыю ЗНАЙСЦІ (ЗНАЙСЦІ):

Гэтая функцыя вызначае, ці ўключаны дадзены падрадок (першы аргумент - гэта ўсе значэнні са слупка Для таго, каб знайсці) у зыходны тэкст (першая кампанія з табліцы даных) і павінен вывесці альбо парадкавы нумар знака, з якога знойдзены тэкст, альбо памылку, калі падрадок не знойдзены.

Хітрасць тут у тым, што паколькі ў якасці першага аргументу мы паказалі не адно, а некалькі значэнняў, то гэтая функцыя таксама будзе вяртаць у выніку не адно значэнне, а масіў з 3 элементаў. Калі ў вас няма апошняй версіі Office 365, якая падтрымлівае дынамічныя масівы, то пасля ўводу гэтай формулы і націску на Уводзіць вы ўбачыце гэты масіў прама на лісце:

Калі ў вас папярэднія версіі Excel, то пасля націску на Уводзіць мы ўбачым толькі першае значэнне з масіва вынікаў, г.зн. памылку #ЗНАЧЭННЕ! (#ЗНАЧЭННЕ!).

Вы не павінны баяцца 🙂 На самай справе, наша формула працуе, і вы ўсё яшчэ можаце ўбачыць увесь масіў вынікаў, калі выберыце ўведзеную функцыю ў радку формул і націсніце клавішу F9(толькі не забывайце націскаць Escкаб вярнуцца да формулы):

Атрыманы масіў вынікаў азначае, што ў арыгінале крыва назва кампаніі (ААТ "Г.К. Марозька") усіх значэнняў у слупку Для таго, каб знайсці знайшлі толькі другое (Марозка), і пачынаючы з 4-га знака ў радку.

Зараз дадамо функцыю да нашай формулы ПОГЛЯД(ШУКАЦЬ):

Гэтая функцыя мае тры аргументы:

1. Пажаданае значэнне – можна выкарыстоўваць любую досыць вялікую лічбу (галоўнае, каб яна перавышала даўжыню любога тэксту ў зыходных дадзеных)
   
2. Прагледжаны_вектар – дыяпазон або масіў, дзе мы шукаем патрэбнае значэнне. Вось уведзеная раней функцыя ЗНАЙСЦІ, які вяртае масіў {#VALUE!:4:#VALUE!}
   
3. Вектар_вынікі – дыяпазон, з якога мы хочам вярнуць значэнне, калі патрэбнае значэнне знойдзена ў адпаведнай ячэйцы. Вось правільныя назвы з калонкі сурагат нашу даведачную табліцу.
   

Галоўная і невідавочная асаблівасць тут у тым, што функцыя ПОГЛЯД калі няма дакладнага супадзення, заўсёды шукае бліжэйшае найменшае (папярэдняе) значэнне. Таму, указаўшы ў якасці жаданага значэння якое-небудзь важкае лік (напрыклад, 9999), мы прымусім ПОГЛЯД знайдзіце ячэйку з бліжэйшым найменшым лікам (4) у масіве {#ЗНАЧЭННЕ!:4:#ЗНАЧЭННЕ!} і вярніце адпаведнае значэнне з вектара выніку, г.зн. правільную назву кампаніі са слупка сурагат.

Другі нюанс заключаецца ў тым, што тэхнічна наша формула з'яўляецца формулай масіва, таму што функцыя ЗНАЙСЦІ вяртае ў якасці вынікаў не адно, а масіў з трох значэнняў. Але так як функцыя ПОГЛЯД падтрымлівае масівы з скрынкі, то нам не трэба ўводзіць гэту формулу як класічную формулу масіва - з дапамогай спалучэння клавіш Ctrl+Зрух+Уводзіць. Простага будзе дастаткова Уводзіць.

Гэта ўсе. Спадзяюся, вы разумееце логіку.

Засталося перанесці гатовую формулу ў першую вочка В2 слупка фіксаваны – і наша задача вырашана!

Вядома, са звычайнымі (не разумнымі) табліцамі гэтая формула таксама выдатна працуе (толькі не забывайце пра ключ F4 і выпраўленне адпаведных спасылак):

Выпадак 2. Масавая частковая замена

Гэты выпадак крыху больш складана. Зноў у нас ёсць дзве «разумныя» табліцы:

Першая табліца з крыва напісанымі адрасамі, якую трэба выправіць (я назваў яе Дадзеныя2). Другая табліца - даведнік, па якой трэба зрабіць частковую замену падрадка ўнутры адрасу (гэтую табліцу я назваў Замены2).

Прынцыповая розніца тут у тым, што замяніць трэба толькі фрагмент зыходных дадзеных - напрыклад, першы адрас мае няправільны “Св. Пецярбург” справа “Св. Пецярбург”, пакінуўшы астатнюю частку адраса (паштовы індэкс, вуліца, дом) як ёсць.

Гатовая формула будзе выглядаць так (для зручнасці ўспрымання я падзяліў яе на колькі радкоў з дапамогай Alt+Уводзіць):

Асноўную працу тут выконвае стандартная тэкставая функцыя Excel ЗАМЕННІК (ЗАМЕНА), які мае 3 аргументы:

1. Зыходны тэкст – першы крывы адрас са слупка Адрас
2. Што мы шукаем - тут мы выкарыстоўваем трук з функцыяй ПОГЛЯД (ШУКАЦЬ)з папярэдняга спосабу атрымаць значэнне са слупка Для таго, каб знайсці, які ўключаны як фрагмент у выгнуты адрас.
3. Чым замяніць – такім жа чынам знаходзім адпаведнае яму правільнае значэнне са слупка сурагат.

Увядзіце гэтую формулу з Ctrl+Зрух+Уводзіць тут таксама не патрэбны, хоць гэта, па сутнасці, формула масіву.

І добра відаць (гл. памылкі #N/A на папярэднім малюнку), што такая формула, пры ўсёй сваёй элегантнасці, мае некалькі недахопаў:

• функцыя SUBSTITUTE адчувальны да рэгістра, таму «Спб» у перадапошнім радку ў табліцы замен не знайшлося. Каб вырашыць гэтую праблему, вы можаце выкарыстоўваць функцыю ЗАМЕНІЦЬ (ЗАМЕНІЦЬ), або папярэдне прывесці абедзве табліцы ў адзін рэестр.
• Калі тэкст першапачаткова правільны або ў ім няма фрагмента для замены (апошні радок), то наша формула выдае памылку. Гэты момант можна нейтралізаваць шляхам перахопу і замены памылак з дапамогай функцыі КАЛІПАМЫЛКА (КАЛІПАМЫЛКА):

  

• Калі зыходны тэкст змяшчае адразу некалькі фрагментаў з каталога, то наша формула замяняе толькі апошнюю (у 8 радку Лігоўскі «Праспект« зменены на «пр-т», Але «С-Пб» on “Св. Пецярбург” больш не, таму што «С-Пб” знаходзіцца вышэй у каталогу). Гэтую праблему можна вырашыць паўторным запускам нашай уласнай формулы, але ўжо па слупку фіксаваны:

  
  

Не ідэальны і грувасткі месцамі, але значна лепш, чым тая ж ручная замена, праўда? 🙂

PS

У наступным артыкуле мы высветлім, як рэалізаваць такую ​​масавую замену з дапамогай макрасаў і Power Query.

• Як працуе функцыя SUBSTITUTE для замены тэксту
  
• Пошук дакладных тэкставых супадзенняў з дапамогай функцыі EXACT
• Пошук і замена з улікам рэгістра (VLOOKUP з улікам рэгістра)