Формулалар арқылы жаппай мәтінді ауыстыру

Сізде әр түрлі дәрежедегі «тікелейлікпен» бастапқы деректер жазылған тізім бар делік, мысалы, мекенжайлар немесе компания атаулары:

Бір қала немесе компанияның мұнда түрлі-түсті нұсқалары бар екені анық байқалады, бұл болашақта осы кестелермен жұмыс істеу кезінде көптеген қиындықтар туғызатыны анық. Ал аздап ойлансаңыз, басқа облыстардан осыған ұқсас тапсырмалардың көптеген мысалдарын табуға болады.

Енді елестетіп көріңізші, мұндай қисық деректер сізге тұрақты түрде келеді, яғни бұл бір реттік «оны қолмен түзетіңіз, оны ұмытыңыз» әңгімесі емес, тұрақты негізде және көптеген ұяшықтардағы мәселе.

Не істеу? Қисық мәтінді 100500 рет дұрыс мәтінмен «Табу және ауыстыру» жолағы арқылы немесе түймесін басу арқылы қолмен ауыстырмаңыз. Ctrl+H?

Мұндай жағдайда бірінші ойға келетін нәрсе - дұрыс емес және дұрыс нұсқаларды сәйкестендіру туралы алдын ала құрастырылған анықтамалық бойынша жаппай ауыстыруды жасау болып табылады, мысалы:

Өкінішке орай, мұндай тапсырманың айқын таралуымен Microsoft Excel бағдарламасында оны шешудің қарапайым кірістірілген әдістері жоқ. Алдымен, VBA немесе Power Query-де макростар түріндегі «ауыр артиллерияны» тартпай, формулалармен мұны қалай жасау керектігін анықтайық.

Жағдай 1. Жаппай толық ауыстыру

Салыстырмалы түрде қарапайым жағдайдан бастайық - ескі қисық мәтінді жаңасымен ауыстыру қажет жағдайда. толық.

Бізде екі кесте бар делік:

Біріншісінде – компаниялардың түпнұсқа атаулары. Екіншісінде – хат алмасу анықтамалығы. Бірінші кестеде компанияның атауында бағандағы кез келген сөзді тапсақ Табу, содан кейін бұл қисық атауды дұрыс атаумен толығымен ауыстыру керек – бағандағы Ауыстыру екінші іздеу кестесі.

Ыңғайлы болу үшін:

• Екі кесте де пернелер тіркесімін пайдаланып динамикалық («ақылды») түрлендіріледі Ctrl+T немесе команда Кірістіру – Кесте (Кірістіру — Кесте).
  
• Пайда болған қойындыда Конструктор (Дизайн) бірінші кесте аталған мәліметтер, және екінші анықтамалық кесте – Ауыстырулар.

Формуланың логикасын түсіндіру үшін алыстан аздап барайық.

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

Бұл функция берілген ішкі жолдың қосылғанын анықтайды (бірінші аргумент - бағандағы барлық мәндер Табу) бастапқы мәтінге (деректер кестесіндегі бірінші компания) және мәтін табылған таңбаның реттік нөмірін немесе ішкі жол табылмаса, қатені шығаруы керек.

Мұндағы қулық мынада, біз бірінші аргумент ретінде бір емес, бірнеше мәндерді көрсеткендіктен, бұл функция нәтиже ретінде бір мәнді емес, 3 элементтен тұратын массивті қайтарады. Динамикалық массивтерді қолдайтын Office 365 жүйесінің соңғы нұсқасы болмаса, осы формуланы енгізіп, түймесін басқаннан кейін кіру сіз бұл массивті тікелей парақта көресіз:

Excel бағдарламасының алдыңғы нұсқалары болса, оны басқаннан кейін кіру біз нәтиже массивінен бірінші мәнді ғана көреміз, яғни қате #VALUE! (#VALUE!).

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

Нәтижелердің жиыны бастапқы қисық компания атауын білдіреді (Г.К. Морозко ОАО) бағандағы барлық мәндердің Табу екіншісін ғана тапты (Морозко), және қатардағы 4-ші таңбадан бастап.

Енді формуламызға функция қосамыз VIEW(ІЗДЕУ):

Бұл функцияның үш аргументі бар:

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

Мұндағы негізгі және айқын емес ерекшелігі - бұл функция VIEW егер дәл сәйкестік болмаса, әрқашан ең жақын ең кіші (алдыңғы) мәнді іздеңіз. Сондықтан, қалаған мән ретінде кез келген үлкен санды (мысалы, 9999) көрсету арқылы біз мәжбүрлейміз VIEW {#VALUE!:4:#VALUE!} массивіндегі ең жақын ең кіші саны (4) бар ұяшықты табыңыз және нәтиже векторынан сәйкес мәнді, яғни бағаннан дұрыс компания атауын қайтарыңыз Ауыстыру.

Екінші нюанс, техникалық тұрғыдан біздің формула массив формуласы болып табылады, өйткені функция ТАБУ нәтиже ретінде бір емес, үш мән массивін қайтарады. Бірақ функциядан бері VIEW қораптан тыс массивтерді қолдайды, онда біз бұл формуланы классикалық массив формуласы ретінде енгізудің қажеті жоқ – пернелер тіркесімін пайдаланып Ctrl+ауысым+кіру. Қарапайым біреуі жеткілікті кіру.

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

Дайын формуланы бағанның бірінші В2 ұяшығына көшіру қалады Тіркелген – және біздің міндетіміз шешілді!

Әрине, қарапайым (ақылды емес) кестелермен бұл формула тамаша жұмыс істейді (тек кілт туралы ұмытпаңыз F4 және тиісті сілтемелерді бекіту):

Жағдай 2. Жаппай ішінара ауыстыру

Бұл іс сәл күрделірек. Бізде тағы екі «ақылды» үстел бар:

Түзетуді қажет ететін қисық жазылған мекенжайлары бар бірінші кесте (мен оны атадым Деректер2). Екінші кесте анықтамалық болып табылады, оған сәйкес мекенжай ішіндегі ішкі жолды ішінара ауыстыру қажет (мен бұл кестені атадым. Ауыстырулар2).

Мұндағы түбегейлі айырмашылық - бастапқы деректердің бір бөлігін ғана ауыстыру керек - мысалы, бірінші мекенжайда қате бар «Ст. Петербург» оң жақта «Ст. Петербург», қалған мекенжайды (пошталық индекс, көше, үй) сол күйінде қалдырыңыз.

Дайын формула келесідей болады (қабылдауға ыңғайлы болу үшін мен оны қанша жолға бөлдім Alt+кіру):

Мұндағы негізгі жұмыс стандартты Excel мәтіндік функциясы арқылы орындалады СУБСТИТУТ (АЛМАСТЫРУ), оның 3 аргументі бар:

1. Бастапқы мәтін – Мекенжай бағанындағы бірінші қисық мекенжай
2. Біз іздеген нәрсе – мұнда біз функциямен трюкті қолданамыз VIEW (ІЗДЕУ)бағаннан мәнді алудың алдыңғы жолынан Табу, ол қисық адреске фрагмент ретінде енгізілген.
3. Немен алмастыру керек – дәл осылай бағаннан оған сәйкес дұрыс мәнді табамыз Ауыстыру.

Мына формуланы көмегімен енгізіңіз Ctrl+ауысым+кіру мұнда да қажет емес, бірақ бұл шын мәнінде массив формуласы.

Мұндай формуланың барлық талғампаздығына қарамастан, бірнеше кемшіліктері бар екені анық көрінеді (алдыңғы суреттегі #N/A қателерін қараңыз):

• функция SUBSTITUTE регистрді ескереді, сондықтан соңғы жолдағы «Spb» ауыстыру кестесінде табылмады. Бұл мәселені шешу үшін функцияны пайдалануға болады ЗАМЕНИТ (АЛУ), немесе алдын ала екі кестені бір регистрге әкеліңіз.
• Егер мәтін бастапқыда дұрыс болса немесе онда ауыстыратын фрагмент жоқ (соңғы жол), онда біздің формула қате жібереді. Бұл сәтті функцияны пайдаланып қателерді ұстап алу және ауыстыру арқылы бейтараптандыруға болады ҚАТЕЛІК (ҚАТЕ):

  

• Түпнұсқа мәтінде болса каталогтан бірден бірнеше фрагменттерді, онда біздің формула тек соңғысын ауыстырады (8-жолда, Лиговский «Avenue« өзгерді «пр-т», Бірақ «S-Pb» on «Ст. Петербург» енді жоқ, өйткені «S-Pb” каталогта жоғары). Бұл мәселені өз формуламызды қайта іске қосу арқылы шешуге болады, бірақ қазірдің өзінде баған бойымен Тіркелген:

  
  

Бір жерде мінсіз және қиын емес, бірақ бірдей қолмен ауыстырудан әлдеқайда жақсы, солай ма? 🙂

PS

Келесі мақалада біз макростар мен Power Query көмегімен осындай жаппай ауыстыруды қалай жүзеге асыру керектігін анықтаймыз.

• SUBSTITUTE функциясы мәтінді ауыстыру үшін қалай жұмыс істейді
  
• EXACT функциясы арқылы мәтіннің дәл сәйкестігін табу
• Регистрді ескеретін іздеу және ауыстыру (әріптерді ескеретін VLOOKUP)