Томьёотой текстийг бөөнөөр нь солих

Танд янз бүрийн түвшний "шулуун байдлын" анхны өгөгдөл бичигдсэн жагсаалт байна гэж бодъё, тухайлбал, хаяг эсвэл компанийн нэр:

Энд нэг хот эсвэл компани нь алагласан хувилбараар байгаа нь тодорхой харагдаж байгаа бөгөөд энэ нь ирээдүйд эдгээр хүснэгттэй ажиллахад маш их асуудал үүсгэх нь тодорхой юм. Хэрэв та бага зэрэг бодож байгаа бол бусад салбараас ижил төстэй ажлуудын олон жишээг олж болно.

Ийм гажуудалтай мэдээлэл танд байнга ирдэг гэж төсөөлөөд үз дээ, өөрөөр хэлбэл энэ нь нэг удаагийн "гараар зас, март" гэсэн түүх биш, харин байнга, олон тооны эсүүдэд тохиолддог асуудал юм.

Юу хийх вэ? Тахир бичвэрийг 100500 удаа "Ол, солих" нүдээр эсвэл дарж гараар зөвөөр сольж болохгүй. Ctrl+H?

Ийм нөхцөлд санаанд орж ирдэг хамгийн эхний зүйл бол урьдчилан эмхэтгэсэн лавлахын дагуу буруу, зөв ​​сонголтуудыг тааруулж бөөнөөр нь солих явдал юм.

Харамсалтай нь, ийм даалгавар илт тархсан тул Microsoft Excel-д үүнийг шийдвэрлэх энгийн аргууд байдаггүй. Эхлэхийн тулд үүнийг VBA эсвэл Power Query дахь макро хэлбэрээр "хүнд их буу" оролцуулалгүйгээр томъёогоор хэрхэн хийхийг олж мэдье.

Тохиолдол 1. Бөөнөөр бүрэн солих

Харьцангуй энгийн тохиолдлоос эхэлцгээе - хуучин муруй текстийг шинэ текстээр солих шаардлагатай нөхцөл байдал. бүрэн.

Бидэнд хоёр хүснэгт байна гэж бодъё:

Эхнийх нь компаниудын анхны олон янзын нэрс юм. Хоёрдугаарт - захидал харилцааны лавлах ном. Хэрэв бид эхний хүснэгтэд байгаа компанийн нэр дээр баганаас ямар ч үг олдвол Олох, дараа нь та энэ муруй нэрийг зөв нэрээр бүрэн солих хэрэгтэй – баганаас Орлуулагч Хоёр дахь хайлтын хүснэгт.

Тохиромжтой болгох үүднээс:

• Хоёр хүснэгтийг гарын товчлолыг ашиглан динамик ("ухаалаг") болгон хувиргадаг Ctrl+T эсвэл баг Оруулах - Хүснэгт (Оруулах - Хүснэгт).
  
• Харагдах таб дээр байгуулагч (Дизайн) нэртэй анхны хүснэгт Өгөгдөл, мөн хоёр дахь лавлах хүснэгт - орлуулалт.

Томъёоны логикийг тайлбарлахын тулд холоос жаахан явъя.

А2 нүднээс эхний компанийг жишээ болгон авч, бусад компаниудыг түр мартаж, баганаас аль сонголтыг тодорхойлохыг хичээцгээе. Олох тэнд уулздаг. Үүнийг хийхийн тулд хуудасны чөлөөт хэсэгт байгаа хоосон нүдийг сонгоод функцийг оруулна уу ОЛОХ (ОЛОХ):

Энэ функц нь өгөгдсөн дэд мөрийг оруулсан эсэхийг тодорхойлдог (эхний аргумент нь баганын бүх утгууд юм Олох) эх бичвэрт (өгөгдлийн хүснэгтээс эхний компани) оруулах ба текст олдсон тэмдэгтийн дарааллын дугаар эсвэл дэд мөр олдоогүй тохиолдолд алдаа гарна.

Энд байгаа заль мэх нь бид эхний аргумент болгон нэг биш, хэд хэдэн утгыг зааж өгсөн тул энэ функц нь нэг утгыг биш, харин 3 элементийн массивыг буцаана. Хэрэв танд динамик массивыг дэмждэг Office 365-ийн хамгийн сүүлийн хувилбар байхгүй бол энэ томъёог оруулсны дараа дээр дарна уу. оруулна уу Та энэ массивыг шууд хуудсан дээр харах болно:

Хэрэв танд Excel-ийн өмнөх хувилбарууд байгаа бол товшсоны дараа оруулна уу Бид үр дүнгийн массиваас зөвхөн эхний утгыг харах болно, өөрөөр хэлбэл алдаа #VALUE! (#ҮНЭ ЦЭНЭ!).

Та айх хэрэггүй 🙂 Үнэн хэрэгтээ манай томъёо ажилладаг бөгөөд хэрэв та томьёоны мөрөнд оруулсан функцийг сонгоод товчлуурыг дарвал үр дүнг бүхэлд нь харах боломжтой. F9(Зүгээр л дарахаа бүү мартаарай Escтомьёо руу буцах):

Үр дүнгийн массив нь анхны муруй компанийн нэрээр байна гэсэн үг юм (Г.К. Морозко ОАО) багана дахь бүх утгуудын Олох хоёр дахь нь л олдсон (Морозко), мөн дараалсан 4 дэх тэмдэгтээс эхэлнэ.

Одоо томьёодоо функц нэмж оруулъя УРУУЛАХ(ХАЙХ):

Энэ функц нь гурван аргументтай:

1. Хүссэн үнэ цэнэ - та хангалттай том тоог ашиглаж болно (хамгийн гол нь энэ нь эх өгөгдлийн аль ч текстийн уртаас хэтэрсэн явдал юм)
   
2. Үзсэн_вектор – бидний хүссэн утгыг хайж буй муж эсвэл массив. Энд өмнө нь танилцуулсан функц байна ОЛОХ, энэ нь {#VALUE!:4:#VALUE!} массивыг буцаана.
   
3. Вектор_үр дүн – харгалзах нүдэнд хүссэн утга олдвол утгыг буцаахыг хүссэн муж. Энд баганын зөв нэрс байна Орлуулагч бидний лавлах хүснэгт.
   

Энд байгаа гол бөгөөд тодорхой бус шинж чанар нь функц юм УРУУЛАХ хэрэв яг таарахгүй бол хамгийн ойрын (өмнөх) утгыг үргэлж хай. Тиймээс, ямар ч том тоог (жишээ нь, 9999) хүссэн утга болгон зааж өгснөөр бид хүчлэх болно. УРУУЛАХ {#VALUE!:4:#VALUE!} массиваас хамгийн ойр (4) тоотой нүдийг олж, үр дүнгийн вектороос харгалзах утгыг буцаана, өөрөөр хэлбэл баганаас компанийн нэрийг зөв оруулна. Орлуулагч.

Хоёрдахь нюанс бол техникийн хувьд бидний томъёо нь массивын томьёо юм, учир нь функц ОЛОХ үр дүн нь нэг биш, харин гурван утгын массивыг буцаана. Гэхдээ функцээс хойш УРУУЛАХ хайрцагнаас массивыг дэмждэг бол бид энэ томьёог гарын товчлолыг ашиглан сонгодог массивын томъёо болгон оруулах шаардлагагүй болно. Ctrl+Shift+оруулна уу. Энгийн нэг нь хангалттай байх болно оруулна уу.

Тэгээд л болоо. Та логикийг олж авсан гэж найдаж байна.

Дууссан томъёог баганын эхний B2 нүд рүү шилжүүлэхэд л үлддэг Тогтмол - Тэгээд бидний даалгавар шийдэгдсэн!

Мэдээжийн хэрэг, энгийн (ухаалаг биш) хүснэгтүүдийн хувьд энэ томъёо нь маш сайн ажилладаг (түлхүүрийнхээ талаар бүү мартаарай) F4 болон холбогдох холбоосыг засах):

Тохиолдол 2. Бөөнөөр хэсэгчлэн солих

Энэ хэрэг арай илүү төвөгтэй юм. Дахин хэлэхэд бидэнд хоёр "ухаалаг" ширээ байна:

Засварлах шаардлагатай муруй бичсэн хаягтай эхний хүснэгт (би үүнийг дуудсан Мэдээлэл2). Хоёрдахь хүснэгт нь лавлах ном бөгөөд үүний дагуу та хаяг доторх дэд мөрийг хэсэгчлэн солих шаардлагатай (би энэ хүснэгтийг дуудсан) Сэлгээ 2).

Энд байгаа үндсэн ялгаа нь та анхны өгөгдлийн зөвхөн хэсгийг солих хэрэгтэй болно - жишээлбэл, эхний хаяг буруу байна. “Гэгээн. Петербург" баруун талд “Гэгээн. Петербург", хаягийн үлдсэн хэсгийг (зип код, гудамж, байшин) хэвээр үлдээнэ.

Дууссан томъёо нь иймэрхүү харагдах болно (ойлголтыг хөнгөвчлөхийн тулд би үүнийг хэдэн мөрөнд хуваасан Alt+оруулна уу):

Энд гол ажил нь Excel-ийн стандарт текст функцээр хийгддэг ОРОЛЦУУЛАХ (ОРЛУУЛАХ), 3 аргументтай:

1. Эх текст – Хаяг баганын эхний муруй хаяг
2. Бидний хайж байгаа зүйл бол энд функцтэй заль мэхийг ашигладаг УРУУЛАХ (ХАЙХ)баганаас утгыг татах өмнөх аргаас Олох, энэ нь муруй хаяг дахь фрагмент хэлбэрээр орсон.
3. Юугаар солих вэ - ижил аргаар бид баганаас түүнд тохирох зөв утгыг олох болно Орлуулагч.

Энэ томъёог ашиглан оруулна уу Ctrl+Shift+оруулна уу Энэ нь үнэндээ массив томьёо боловч энд бас хэрэггүй.

Ийм томьёо нь бүх дэгжин байдлаа үл харгалзан хэд хэдэн сул талуудтай байдаг нь тодорхой харагдаж байна (өмнөх зурган дээрх # Үгүй алдааг үзнэ үү).

• Чиг үүрэг SUBSTITUTE нь том жижиг жижиг үсгийг мэдэрдэг, тиймээс эцсийн өмнөх мөрөнд байгаа "Spb" нь орлуулах хүснэгтээс олдсонгүй. Энэ асуудлыг шийдэхийн тулд та функцийг ашиглаж болно ЗАМЕНИТ (солих), эсвэл хоёр хүснэгтийг нэг бүртгэлд урьдчилан авчрах.
• Хэрэв текст эхэндээ зөв эсвэл дотор нь байвал солих ямар ч фрагмент байхгүй (сүүлийн мөр), дараа нь бидний томъёо алдаа гаргадаг. Энэ мөчийг функцийг ашиглан алдааг таслан зогсоох, солих замаар саармагжуулж болно АЛДАА (ҮГҮЙ):

  

• Хэрэв эх бичвэрт байгаа бол лавлахаас хэд хэдэн фрагментийг нэг дор, дараа нь бидний томъёо нь зөвхөн сүүлчийнх нь орлоно (8-р мөрөнд, Лиговский «өргөн чөлөө« болж өөрчлөгдсөн "pr-t", Гэхдээ "S-Pb" on “Гэгээн. Петербург" байхаа больсон, учир нь “S-Pb” нь лавлахад илүү өндөр байна). Энэ асуудлыг өөрсдийн томъёог дахин ажиллуулах замаар шийдэж болно, гэхдээ аль хэдийн баганын дагуу Тогтмол:

  
  

Хаа газар төгс биш, төвөгтэй биш, гэхдээ ижил гараар солихоос хамаагүй дээр, тийм үү? 🙂

PS

Дараагийн өгүүллээр бид макро болон Power Query ашиглан ийм бөөнөөр орлуулалтыг хэрхэн хэрэгжүүлэх талаар олж мэдэх болно.

• Текстийг орлуулахын тулд ОРЛУУЛАХ функц хэрхэн ажилладаг
  
• EXACT функцийг ашиглан яг тохирох текстийг олох
• Том үсгийн мэдрэмжтэй хайлт ба орлуулалт (үсгийн мэдрэмжтэй VLOOKUP)