Иваз кардани матни оммавӣ бо формулаҳо

Фарз мекунем, ки шумо рӯйхате доред, ки дар он маълумотҳои ибтидоӣ бо дараҷаҳои гуногуни "ростӣ" навишта шудаанд - масалан, суроғаҳо ё номҳои ширкат:

Баръало дида мешавад, ки як шаҳр ё ширкат дар ин ҷо дар вариантҳои ранга мавҷуд аст, ки бешубҳа, дар оянда ҳангоми кор бо ин ҷадвалҳо мушкилоти зиёде ба вуҷуд меорад. Ва агар шумо каме фикр кунед, шумо метавонед аз дигар соҳаҳо мисолҳои ба ин монандро пайдо кунед.

Акнун тасаввур кунед, ки чунин маълумотҳои каҷ ба шумо мунтазам меоянд, яъне ин достони якдафъаинаи «дастӣ ислоҳ кардан, фаромӯш кардан» нест, балки мушкилии мунтазам ва дар шумораи зиёди ҳуҷайраҳо мебошад.

Чи бояд кард? Матни каҷро 100500 маротиба бо матни дуруст ба воситаи қуттии "Ҷустуҷӯ ва иваз кардан" ё бо пахш кардани тугма ба таври дастӣ иваз накунед. Ctrl+H?

Аввалин чизе, ки дар чунин вазъият ба хотир меояд, ин иваз кардани оммавӣ аз рӯи маълумотномаи пешакӣ тартибдодашуда оид ба мувофиқати вариантҳои нодуруст ва дуруст аст - ба монанди:

Мутаассифона, бо паҳншавии возеҳи чунин вазифа, Microsoft Excel усулҳои оддии дарунсохташудаи ҳалли онро надорад. Барои оғоз, биёед бифаҳмем, ки чӣ гуна ин корро бо формулаҳо бидуни ҷалби "артиллерияи вазнин" дар шакли макросҳо дар VBA ё Power Query иҷро кунем.

Ҳолати 1. Ивазкунии пурра

Биёед бо як ҳолати нисбатан содда оғоз кунем - вазъияте, ки шумо бояд матни каҷшудаи кӯҳнаро бо матни нав иваз кунед. пурра.

Фарз мекунем, ки мо ду ҷадвал дорем:

Дар аввал - номҳои аслии ширкатҳои гуногунранг. Дар дуюм — маълумотномаи мукотиба. Агар дар номи ширкат дар чадвали якум ягон калимаи сутунро пайдо кунем Ёфтан, пас шумо бояд ин номи каҷро пурра бо номи дуруст иваз кунед - аз сутун Тағирдиҳӣ ҷадвали ҷустуҷӯи дуюм.

Барои роҳат:

• Ҳарду ҷадвал бо истифода аз миёнабурҳои клавиатура ба динамикӣ ("интеллектуалӣ") табдил дода мешаванд Ctrl+T ё даста Ворид кардан - Ҷадвал (Ворид — Ҷадвал).
  
• Дар ҷадвале, ки пайдо мешавад Конструктор (Тарроҳӣ) ҷадвали аввалин номгузорӣ шудааст Маълумот, ва ҷадвали дуюми истинод - Тағйирот.

Барои фахмонда додани мантики формула каме аз дур меравем.

Ширкати аввалро аз ячейкаи A2 ҳамчун намуна гирифта, муваққатан дар бораи боқимондаи ширкатҳо фаромӯш карда, биёед кӯшиш кунем, ки кадом вариантро аз сутун муайян кунем. Ёфтан дар он чо вомехурад. Барои ин, ягон ячейкаи холии қисми холии варақро интихоб кунед ва дар он ҷо функсияро ворид кунед ЁФТАН (ЁФТА):

Ин функсия муайян мекунад, ки оё зерсатри додашуда дохил карда шудааст (аргументи аввал ҳамаи арзишҳои сутун аст Ёфтан) ба матни сарчашма (ширкати аввал аз ҷадвали додаҳо) ва бояд ё рақами тартибии аломате, ки матн аз он пайдо шудааст, ё хатогӣ дар сурати пайдо нашудани зерсатри.

Дар ин ҷо ҳилла дар он аст, ки азбаски мо на як, балки якчанд арзишро ҳамчун далели аввал нишон додем, ин функсия низ дар натиҷа на як арзиш, балки массиви 3 элемент бармегардад. Агар шумо версияи охирини Office 365-ро надошта бошед, ки массивҳои динамикиро дастгирӣ мекунад, пас пас аз ворид кардани ин формула ва пахш кардани тугма даромадан шумо ин массивро рост дар варақ хоҳед дид:

Агар шумо версияҳои қаблии Excel дошта бошед, пас аз клик кардан даромадан мо танҳо арзиши аввалро аз массиви натиҷа мебинем, яъне хатои #VALUE! (#АРЗИШ!).

Шумо набояд тарсед 🙂 Дар асл, формулаи мо кор мекунад ва шумо ба ҳар ҳол метавонед тамоми массиви натиҷаҳоро бинед, агар шумо функсияи воридшударо дар сатри формула интихоб кунед ва тугмаро пахш кунед F9(фақат пахш карданро фаромӯш накунед Эсскбаргаштан ба формула):

Маҷмӯи натиҷаҳо маънои онро дорад, ки дар номи аслии ширкати каҷ (ОАО ГК Морозко) аз ҳама арзишҳо дар сутун Ёфтан танҳо дуюмро ёфт (Морозко), ва аз аломати 4-ум дар як саф сар карда.

Акнун биёед ба формулаи худ функсия илова кунем VIEW(ҶУСТУҶӮ КАРДАН):

Ин функсия се далел дорад:

1. Арзиши дилхоҳ - шумо метавонед ҳар як рақами ба қадри кофӣ калонро истифода баред (чизи асосиаш он аст, ки он аз дарозии ҳама гуна матн дар маълумоти сарчашма зиёд аст)
   
2. Дидани_вектор – диапазон ё массив, ки мо арзиши дилхоҳро меҷӯем. Ин аст функсияи қаблан ҷорӣшуда ЁФТАН, ки массивро бармегардонад {#VALUE!:4:#VALUE!}
   
3. вектор_натиҷаҳои – диапазон, ки аз он мо мехоҳем арзишро баргардонем, агар арзиши дилхоҳ дар чашмаки мувофиқ пайдо шавад. Дар ин ҷо номҳои дуруст аз сутун ҳастанд Тағирдиҳӣ ҷадвали истинодҳои мо.
   

Хусусияти асосй ва но-равшан дар ин чо он аст, ки функсия VIEW агар мувофиқати дақиқ вуҷуд надошта бошад, ҳамеша арзиши наздиктаринро (пештар) ҷустуҷӯ кунед. Аз ин рӯ, бо нишон додани ягон рақами вазнин (масалан, 9999) ҳамчун арзиши дилхоҳ, мо маҷбур мекунем VIEW чашмаки дорои рақами хурдтаринро (4) дар массиви {#VALUE!:4:#VALUE!} пайдо кунед ва арзиши мувофиқро аз вектори натиҷа, яъне номи дурусти ширкат аз сутун баргардонед Тағирдиҳӣ.

Нозуки дуюм ин аст, ки аз ҷиҳати техникӣ формулаи мо формулаи массив аст, зеро функсия ЁФТАН ҳамчун натиҷа на як, балки массиви се арзиш бармегардад. Аммо аз вазифаи VIEW массивҳоро аз қуттӣ дастгирӣ мекунад, пас мо набояд ин формуларо ҳамчун формулаи массиви классикӣ ворид кунем - бо истифода аз миёнабурҳои клавиатура Ctrl+Shift+даромадан. Як оддӣ кофӣ хоҳад буд даромадан.

Ҳамааш ҳамин. Умедворам, ки шумо мантиқро пайдо мекунед.

Барои интиқол додани формулаи тайёр ба ячейкаи якуми B2 сутун боқӣ мемонад собит — ва вазифаи мо хал шуд!

Албатта, бо ҷадвалҳои оддӣ (на оқилона), ин формула низ хуб кор мекунад (танҳо дар бораи калид фаромӯш накунед F4 ва истинодҳои дахлдор):

Парвандаи 2. Ивазкунии қисман оммавӣ

Ин парванда каме душвортар аст. Мо боз ду мизҳои "ақл" дорем:

Ҷадвали аввал бо суроғаҳои каҷ навишташуда, ки бояд ислоҳ карда шаванд (ман онро даъват кардам Маълумот 2). Ҷадвали дуюм китоби маълумотномаест, ки мувофиқи он шумо бояд қисман иваз кардани зерсатри дохили суроғаро анҷом диҳед (ман ин ҷадвалро даъват кардам Ивазҳо 2).

Фарқияти асосӣ дар он аст, ки шумо бояд танҳо як порчаи маълумоти аслиро иваз кунед - масалан, суроғаи аввал нодуруст дорад «Ст. Петербург" дар тарафи рост «Ст. Петербург", боқимондаи суроға (индекс, кӯча, хона) ҳамон тавре ки ҳаст.

Формулаи тайёр чунин хоҳад буд (барои осонии дарк ман онро ба чанд сатр тақсим кардам Alt+даромадан):

Кори асосиро дар ин ҷо функсияи стандартии матнии Excel анҷом медиҳад ИСТИФОДА (ҶАВОБ), ки 3 далел дорад:

1. Матни сарчашма – аввалин суроғаи каҷ аз сутуни Суроға
2. Он чизе ки мо меҷӯем - дар ин ҷо мо ҳилларо бо функсия истифода мебарем VIEW (ҶУСТУҶӮ КАРДАН)аз роҳи пештара барои кашидани арзиш аз сутун Ёфтан, ки ҳамчун порча дар суроғаи каҷ дохил карда шудааст.
3. Чӣ бояд иваз карда шавад - ҳамин тавр мо аз сутун арзиши дурусти ба он мувофиқро пайдо мекунем Тағирдиҳӣ.

Ин формуларо бо Ctrl+Shift+даромадан дар ин ҷо ҳам лозим нест, гарчанде ки он дар асл формулаи массив аст.

Ва ба таври возеҳ дида мешавад (ба хатогиҳои #N/A дар расми қаблӣ нигаред) ки чунин формула бо тамоми зебогии худ, якчанд нуқсонҳо дорад:

• функсия SUBSTITUTE ба ҳарфҳо ҳассос аст, бинобар ин, "Spb" дар хати охирин дар ҷадвали иваз ёфт нашуд. Барои ҳалли ин мушкилот, шумо метавонед функсияро истифода баред ЗАМЕНИТ (Иваз кардан), ё пешакӣ ҳарду ҷадвалро ба як реестр оваред.
• Агар матн дар аввал дуруст бошад ё дар он ягон порча барои иваз кардан нест (сатри охирин), пас формулаи мо хато мекунад. Ин лаҳзаро тавассути боздоштан ва иваз кардани хатогиҳо бо истифода аз функсия безарар кардан мумкин аст ХАТО (ИФЕРРОР):

  

• Агар матни аслӣ дошта бошад якчанд порча аз директория якбора, пас формулаи мо танҳо формулаи охиринро иваз мекунад (дар сатри 8, Лиговский «Avenue« иваз карда шуд "pr-t", Аммо "S-Pb" on «Ст. Петербург" дигар не, зеро «С-Пб” дар директория баландтар аст). Ин мушкилотро бо роҳи дубора иҷро кардани формулаи худамон ҳал кардан мумкин аст, аммо аллакай дар сутун собит:

  
  

Дар ҷойҳо комил ва душвор нест, аммо аз ҳамон ивази дастӣ хеле беҳтар аст, дуруст? 🙂

PS

Дар мақолаи навбатӣ, мо мефаҳмем, ки чӣ гуна чунин ивазкунии оммавӣ бо истифода аз макросҳо ва Power Query амалӣ карда мешавад.

• Чӣ тавр функсияи SUBSTITUTE барои иваз кардани матн кор мекунад
  
• Ҷустуҷӯи мувофиқати дақиқи матн бо истифода аз Функсияи EXACT
• Ҷустуҷӯ ва иваз кардани ҳарфҳои ҳассос (VLOOKUP ҳассос ба ҳарф)