Агуулга
Excel дээр тексттэй ажиллахад хамгийн их цаг хугацаа шаардсан, урам хугардаг ажлуудын нэг юм задлан шинжлэх – үсэг, тоон “будаа”-г бүрэлдэхүүн хэсгүүдэд задлан шинжилж, түүнээс бидэнд хэрэгтэй хэсгүүдийг гаргаж авна. Жишээлбэл:
- хаягнаас зип код гаргаж авах (зип код үргэлж эхэнд байх нь сайн, гэхдээ байхгүй бол яах вэ?)
- банкны хуулга дахь төлбөрийн тайлбараас нэхэмжлэхийн дугаар, огноог олох
- эсрэг талуудын жагсаалтад байгаа компаниудын олон янзын тайлбараас TIN гаргаж авах
- тайлбараас машины дугаар эсвэл нийтлэлийн дугаарыг хайх гэх мэт.
Ихэвчлэн ийм тохиолдолд хагас цагийн турш уйтгартай текстийг гараар сонгосны дараа энэ үйл явцыг автоматжуулах (ялангуяа маш их мэдээлэл байгаа бол) бодол төрж эхэлдэг. Хэд хэдэн шийдэл байдаг бөгөөд янз бүрийн түвшний нарийн төвөгтэй үр ашигтай байдаг:
- ашиглах суурилуулсан Excel текст функцууд Текстийг хайн хайчлах: LEVSIMV (Зүүн), БАРАА (ЗӨВ), PSTR (дунд), STsEPIT (ХОЛБОГДОХ) ба түүний аналогууд, ХОЛБОО (JOINTEXT), ЯГ (ЯГ) гэх мэт. Хэрэв текстэд тодорхой логик байгаа бол энэ арга нь сайн (жишээлбэл, индекс нь үргэлж хаягийн эхэнд байдаг). Үгүй бол томьёо нь илүү төвөгтэй болж, заримдаа массив томьёо хүртэл гарч ирдэг бөгөөд энэ нь том хүснэгтүүдийг ихээхэн удаашруулдаг.
- ашиглах текст ижил төстэй оператор шиг тусгай макро функцээр ороосон Visual Basic-ээс. Энэ нь орлуулагч тэмдэгтүүдийг (*, #,?, гэх мэт) ашиглан илүү уян хатан хайлт хийх боломжийг танд олгоно. Харамсалтай нь энэ хэрэгсэл нь текстээс хүссэн дэд мөрийг гаргаж авах боломжгүй - зөвхөн дотор нь байгаа эсэхийг шалгана уу.
Дээр дурдсанаас гадна мэргэжлийн програмистууд, вэб хөгжүүлэгчид болон бусад техникийн нарийн хүрээнийхэнд маш сайн мэддэг өөр нэг арга байдаг. тогтмол илэрхийллүүд (Тогтмол илэрхийлэл = RegExp = "regexps" = "regulars"). Хялбараар хэлбэл, RegExp нь текст дотор шаардлагатай дэд мөрүүдийг хайх, задлах эсвэл өөр текстээр солихын тулд тусгай тэмдэгт, дүрмийг ашигладаг хэл юм.. Тогтмол хэллэгүүд нь тексттэй ажиллах бусад бүх аргуудаас давуу талтай маш хүчирхэг, үзэсгэлэнтэй хэрэгсэл юм. Олон програмчлалын хэл (C#, PHP, Perl, JavaScript...) болон текст засварлагч (Word, Notepad++...) нь ердийн илэрхийллийг дэмждэг.
Харамсалтай нь Microsoft Excel-д RegExp дэмжлэг байхгүй, гэхдээ VBA-ийн тусламжтайгаар үүнийг хялбархан засах боломжтой. Таб дээрээс Visual Basic редакторыг нээнэ үү Developer (Хөгжүүлэгч) эсвэл гарын товчлол Alt+F11. Дараа нь цэсээр дамжуулан шинэ модулийг оруулна уу Оруулах - Модуль Дараах макро функцын текстийг тэнд хуулна уу:
Нийтийн функц RegExpExtract(Текстийг мөр хэлбэрээр, загвар нь мөр хэлбэрээр, нэмэлт зүйл нь бүхэл тоогоор = 1) Алдаа дээр мөр болгон GoTo ErrHandl тохируулах regex = CreateObject("VBScript.RegExp") regex.Pattern = Загвар regex.Global = Үнэн бол regex.Test. (Текст) Дараа нь таарч тохируулна уу = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Гарах функц дуусна ErrHandl бол: RegExpExtract = CVErr(xlErrValue) Төгсгөлийн функц Бид одоо Visual Basic Editor-ыг хааж, Excel рүү буцаж очоод шинэ боломжоо туршиж үзэх боломжтой. Түүний синтакс нь дараах байдалтай байна.
=RegExpExtract( Txt ; Загвар ; Зүйл )
хаана
- txt – бидний шалгаж байгаа текст бүхий нүд бөгөөд үүнээс бидэнд хэрэгтэй дэд мөрийг гаргаж авахыг хүсэж байна
- хэв маяг – дэд мөр хайхад зориулсан маск (загвар).
- Барааны - задлах дэд мөрийн дарааллын дугаар, хэрэв тэдгээр нь хэд хэдэн байвал (хэрэв заагаагүй бол эхний тохиолдол гарч ирнэ)
Эндээс хамгийн сонирхолтой зүйл бол мэдээжийн хэрэг Pattern - RegExp-ийн "хэл дээрх" тусгай тэмдэгтүүдийн загвар мөр бөгөөд бид яг юу, хаанаас олохыг хүсч байгааг тодорхойлдог. Таныг эхлүүлэх хамгийн энгийн зүйлс энд байна:
| Хээ | Тодорхойлолт |
| . | Хамгийн энгийн нь цэг юм. Энэ нь заасан байрлал дахь хээ дээрх дурын тэмдэгттэй таарч байна. |
| s | Хоосон зай шиг харагдах аливаа тэмдэгт (зай, таб эсвэл мөр таслах). |
| S | Өмнөх загварын эсрэг хувилбар, өөрөөр хэлбэл ямар ч хоосон зайгүй тэмдэгт. |
| d | Дурын дугаар |
| D | Өмнөх хувилбарын эсрэг хувилбар, өөрөөр хэлбэл ямар ч NOT цифр |
| w | Аливаа латин тэмдэгт (AZ), цифр эсвэл доогуур зураас |
| W | Өмнөх хувилбарын эсрэг хувилбар, өөрөөр хэлбэл латин биш, тоо биш, доогуур зураас ч биш. |
| [тэмдэгтүүд] | Дөрвөлжин хаалтанд та текстийн заасан байрлалд зөвшөөрөгдсөн нэг буюу хэд хэдэн тэмдэгтийг зааж өгч болно. Жишээлбэл Урлаг үгийн аль нэгтэй тохирно: хүснэгт or дарга. Та мөн тэмдэгтүүдийг тоолж болохгүй, гэхдээ тэдгээрийг зураасаар тусгаарлагдсан муж болгон тохируулна уу. [ABDCDEF] бичих [AF]. эсвэл оронд нь [4567] Нэвтрүүлэх [-4 7]. Жишээлбэл, бүх кирилл үсгийг тодорхойлохын тулд та загварыг ашиглаж болно [a-yaA-YayoYo]. |
| [^тэмдэгтүүд] | Хэрэв нээлтийн дөрвөлжин хаалтны дараа "таг" гэсэн тэмдгийг нэмнэ үү. ^, дараа нь багц нь эсрэг утгатай болно - текстийн заасан байрлалд жагсаасан тэмдэгтүүдээс бусад бүх тэмдэгтийг зөвшөөрнө. Тийм ээ, загвар [^ЖМ]ут олох болно Зам or Бодис or мартгэхдээ үгүй Аймшигтай or Мут, жишээ нь. |
| | | Булийн оператор OR (ЭСВЭЛ) заасан шалгууруудын аль нэгийг шалгах. Жишээлбэл (-аасПүрэвтэгш|нэхэмжлэх) заасан үгсийн аль нэгийг нь текстээс хайх болно. Ихэвчлэн сонголтуудын багцыг хаалтанд бичдэг. |
| ^ | Мөрийн эхлэл |
| $ | Мөрийн төгсгөл |
| b | Үгийн төгсгөл |
Хэрэв бид тодорхой тооны тэмдэгт, жишээлбэл, зургаан оронтой шуудангийн код эсвэл бүх гурван үсэгтэй бүтээгдэхүүний кодыг хайж байгаа бол бид аврах ажилд ирдэг. тоон үзүүлэлтүүд or тоон үзүүлэлтүүд хайлт хийх тэмдэгтүүдийн тоог заасан тусгай илэрхийллүүд юм. Өмнөх тэмдэгтэд тоологчийг хэрэглэнэ:
| Квантор | Тодорхойлолт |
| ? | Тэг эсвэл нэг тохиолдол. Жишээлбэл .? аль нэг тэмдэгт эсвэл түүний байхгүй гэсэн үг юм. |
| + | Нэг буюу хэд хэдэн оруулга. Жишээлбэл d+ дурын тооны цифр (өөрөөр хэлбэл 0 ба хязгааргүй тоонуудын хоорондох дурын тоо) гэсэн үг. |
| * | Тэг буюу түүнээс олон тохиолдол, өөрөөр хэлбэл ямар ч тоо хэмжээ. Тэгэхээр s* ямар ч тооны зайтай эсвэл хоосон зайгүй гэсэн үг. |
| {тоо} or {тоо1,тоо2} | Хэрэв та тодорхой тооны тохиолдлуудыг зааж өгөх шаардлагатай бол үүнийг буржгар хаалтанд зааж өгнө. Жишээлбэл г{6} гэдэг нь зургаан оронтой тоо, хээ гэсэн үг с{2,5} - хоёроос таван зай |
Одоо хамгийн сонирхолтой хэсэг болох бүтээгдсэн функцийн хэрэглээний дүн шинжилгээ, амьдралын практик жишээн дээр хэв маягийн талаар олж мэдсэн зүйл рүү шилжье.
Текстээс тоо гаргаж авах
Эхлэхийн тулд энгийн жишээнд дүн шинжилгээ хийцгээе - та үсгээр будсан будаагаас эхний тоог гаргаж авах хэрэгтэй, жишээлбэл, үнийн жагсаалтаас тасалдалгүй тэжээлийн хангамжийн хүчийг:
Тогтмол илэрхийллийн цаадах логик нь энгийн: d ямар ч цифр, тоологч гэсэн үг + Тэдний тоо нэг буюу түүнээс дээш байх ёстой гэж хэлдэг. Функцийн урд байгаа давхар хасах нь задалсан тэмдэгтүүдийг текстийн дугаараас бүтэн тоо болгон хувиргахад шаардлагатай.
Шуудангийн хайрцаг
Эхлээд харахад энд бүх зүйл энгийн байдаг - бид яг зургаан цифрийг дараалан хайж байна. Бид тусгай тэмдэгт ашигладаг d цифр болон тоологчийн хувьд 6 {} тэмдэгтийн тооны хувьд:
Гэсэн хэдий ч, мөрний индексийн зүүн талд, дараалсан өөр нэг том багц (утасны дугаар, TIN, банкны данс гэх мэт) байх нөхцөл байдал үүсч болно. Дараа нь манай ээлжит улирал эхний 6-г татах болно. үүнээс цифрүүд, өөрөөр хэлбэл зөв ажиллахгүй:
Үүнээс урьдчилан сэргийлэхийн тулд бид ердийн илэрхийлэлийнхээ ирмэг дээр өөрчлөгч нэмэх хэрэгтэй b үгийн төгсгөлийг илэрхийлдэг. Энэ нь Excel-д бидэнд хэрэгтэй фрагмент (индекс) нь өөр фрагментийн (утасны дугаар) хэсэг биш харин тусдаа үг байх ёстойг тодорхой болгоно.
Гар утас
Текстээс утасны дугаарыг олоход тулгардаг асуудал нь дугаар бичих олон сонголт байдаг - зураастай болон зураасгүй, зай завсар, хаалтанд бүсийн кодтой эсвэл байхгүй гэх мэт. Тиймээс миний бодлоор энэ нь илүү хялбар байдаг. эхлээд хэд хэдэн үүрлэсэн функцийг ашиглан эх текстээс эдгээр бүх тэмдэгтүүдийг цэвэрлэ ОРОЛЦУУЛАХ (ОРЛУУЛАХ)Ингэснээр энэ нь нэг бүхэл болж, дараа нь анхдагч тогтмол дүрд наалддаг г{11} 11 цифрийг дараалан гарга:
ITN
ТИН (манай улсад) 10 оронтой (хуулийн этгээдийн хувьд) эсвэл 12 оронтой (хувь хүний хувьд) байж болох тул энэ нь арай илүү төвөгтэй юм. Хэрэв та ялангуяа алдаа олж харахгүй бол ердийнхөөрөө сэтгэл хангалуун байх боломжтой г{10,12}, гэхдээ хатуухан хэлэхэд энэ нь 10-аас 12 тэмдэгт хүртэлх бүх тоог гаргаж авах болно, өөрөөр хэлбэл 11 цифрийг алдаатай оруулсан болно. Логик OR оператороор холбогдсон хоёр загварыг ашиглах нь илүү зөв байх болно | (босоо бар):
Асуулгад бид эхлээд 12 битийн тоог хайж, дараа нь 10 битийн тоог хайдаг болохыг анхаарна уу. Хэрэв бид ердийн илэрхийлэлээ эсрэгээр нь бичвэл энэ нь хүн бүрт, тэр ч байтугай урт 12 битийн TIN, зөвхөн эхний 10 тэмдэгт хүртэл гарч ирнэ. Өөрөөр хэлбэл, эхний нөхцөл үүссэний дараа нэмэлт баталгаажуулалтыг хийхээ больсон.
Энэ бол операторын үндсэн ялгаа юм | Excel-ийн стандарт логик функцээс OR (ЭСВЭЛ), аргументуудыг дахин цэгцлэх нь үр дүнг өөрчлөхгүй.
Бүтээгдэхүүний SKU
Олон компаниуд бараа, үйлчилгээнд өвөрмөц танигчийг өгдөг - нийтлэл, SAP код, SKU гэх мэт. Хэрэв тэдгээрийн тэмдэглэгээнд логик байгаа бол тэдгээрийг энгийн хэллэг ашиглан аливаа текстээс амархан гаргаж авах боломжтой. Жишээлбэл, хэрэв бид нийтлэлүүд нь англи хэлний гурван том үсэг, зураас, дараагийн гурван оронтой тооноос бүрддэг гэдгийг мэддэг бол:
Загварын цаана байгаа логик нь энгийн. [AZ] – Латин цагаан толгойн ямар ч том үсгийг хэлнэ. Дараагийн хэмжигч 3 {} яг гурван ийм үсэг байгаа нь бидний хувьд чухал гэж хэлэв. Зураасны дараа бид гурван оронтой тоог хүлээж байгаа тул төгсгөлд нь нэмнэ г{3}
Бэлэн мөнгөний дүн
Өмнөх догол мөртэй төстэй байдлаар та барааны тайлбараас үнийг (зардал, НӨАТ ...) гаргаж болно. Жишээлбэл, мөнгөн дүнг зураасаар тэмдэглэсэн бол:
Хээ d тоологчтой + зураас хүртэлх дурын тоог хайдаг ба г{2} дараа нь пенни (хоёр оронтой тоо) хайх болно.
Хэрэв та үнийг бус НӨАТ-ыг гаргаж авах шаардлагатай бол манай RegExpExtract функцийн гурав дахь нэмэлт аргументыг ашиглаж болно, энэ нь олборлох элементийн дарааллын дугаарыг зааж өгдөг. Мэдээжийн хэрэг та функцийг сольж болно ОРОЛЦУУЛАХ (ОРЛУУЛАХ) Үр дүнд нь стандарт аравтын таслагч руу зураас тавьж, эхэнд давхар хасах тэмдэг нэмснээр Excel олдсон НӨАТ-ыг ердийн тоо гэж тайлбарлах болно.
Машины улсын дугаар
Хэрэв та тусгай тээврийн хэрэгсэл, чиргүүл болон бусад мотоцикль авахгүй бол стандарт машины дугаарыг "үсэг - гурван тоо - хоёр үсэг - бүсийн код" зарчмын дагуу задлан шинжилдэг. Түүнчлэн, бүсийн код нь 2 эсвэл 3 оронтой байж болох бөгөөд зөвхөн латин цагаан толгойтой ижил төстэй кодыг үсэг болгон ашигладаг. Тиймээс дараах тогтмол илэрхийлэл нь текстээс тоо гаргаж авахад тусална.
цаг
HH:MM форматаар цагийг задлахад дараах тогтмол илэрхийлэл тохиромжтой.
Бүдүүн гэдэсний фрагментийн дараа [0-5]д, үүнийг ойлгоход хялбар тул 00-59 хооронд ямар ч тоог тогтооно. Хаалтанд байгаа хоёр цэгийн өмнө логик OR (хоолой)-оор тусгаарлагдсан хоёр загвар ажиллана:
- [0-1]д – 00-19 хүртэлх аль ч тоо
- 2[0-3] – 20-23 хүртэлх аль ч тоо
Хүлээн авсан үр дүнд та стандарт Excel функцийг нэмж ашиглаж болно TIME (БАГ)программд ойлгомжтой, цаашдын тооцоололд тохиромжтой цагийн формат руу хөрвүүлэх.
Нууц үг шалгах
Бид хэрэглэгчдийн зохиосон нууц үгүүдийн жагсаалтыг үнэн зөв эсэхийг шалгах хэрэгтэй гэж бодъё. Манай дүрмийн дагуу нууц үг нь зөвхөн англи үсэг (жижиг эсвэл том үсэг) болон тоо агуулсан байж болно. Хоосон зай, доогуур зураас болон бусад цэг таслалыг зөвшөөрөхгүй.
Шалгалтыг дараах энгийн хэллэг ашиглан зохион байгуулж болно.
Үнэн хэрэгтээ, ийм хэв маягаар бид эхлэлийн хооронд үүнийг шаарддаг (^) ба төгсгөл ($) бидний бичвэрт зөвхөн дөрвөлжин хаалтанд өгсөн багцын тэмдэгтүүд байсан. Хэрэв та мөн нууц үгийн уртыг шалгах шаардлагатай бол (жишээлбэл, дор хаяж 6 тэмдэгт), дараа нь тоологч + хэлбэрээр "зургаа ба түүнээс дээш" интервалаар сольж болно {6,}:
хаягаас хот
Бид хаягийн талбараас хотыг татах хэрэгтэй гэж бодъё. Энгийн програм нь "g"-ээс текстийг задлахад тусална. дараагийн таслал руу:
Энэ загварыг илүү нарийвчлан авч үзье.
Хэрэв та дээрх текстийг уншсан бол ердийн хэллэг дэх зарим тэмдэгтүүд (цэг, од, долларын тэмдэг гэх мэт) онцгой утгатай болохыг та аль хэдийн ойлгосон. Хэрэв та эдгээр тэмдэгтүүдийг өөрсдөө хайх шаардлагатай бол тэдгээрийн өмнө урвуу зураас (заримдаа гэж нэрлэдэг бамбай). Тиймээс, "g" хэсгийг хайхдаа. Бид тогтмол илэрхийллээр бичих ёстой Ноён Хэрэв бид нэмэхийг хайж байгаа бол + гэх мэт
Манай загварын дараагийн хоёр тэмдэгт болох цэг ба тоологч од нь дурын тооны тэмдэгт, өөрөөр хэлбэл дурын хотын нэрийг илэрхийлнэ.
Загварын төгсгөлд таслал байна, учир нь бид "g"-ээс текст хайж байна. таслал руу. Гэхдээ бичвэрт хэд хэдэн таслал байж болно, тийм үү? Зөвхөн хотын дараа ч биш, гудамж, байшин гэх мэтчилэн аль нь дээр нь бидний хүсэлт зогсох вэ? Үүний тулд асуултын тэмдэг тавьсан юм. Үүнгүйгээр бидний ердийн илэрхийлэл хамгийн урт мөрийг гаргаж авах болно:
Тогтмол хэллэгийн хувьд ийм хэв маяг нь "шуналтай" юм. Нөхцөл байдлыг засахын тулд асуултын тэмдэг хэрэгтэй - энэ нь тоон үзүүлэлтийг "харамч" болгодог - бидний асуулга "g"-ийн дараах эхний эсрэг таслал хүртэл текстийг авдаг:
Бүтэн замаас файлын нэр
Өөр нэг нийтлэг нөхцөл байдал бол файлын нэрийг бүтэн замаас гаргаж авах явдал юм. Маягтын энгийн илэрхийлэл энд туслах болно:
Энд байгаа заль мэх нь хайлт нь үнэндээ эсрэг чиглэлд - төгсгөлөөс эх хүртэл явагддаг, учир нь бидний загварын төгсгөлд $, мөн бид баруун талын эхний урвуу зураас хүртэл түүнээс өмнөх бүх зүйлийг хайж байна. Урвуу зураасыг өмнөх жишээн дээрх цэг шиг арилгасан.
PS
"Төгсгөлд нь" Дээр дурдсан бүх зүйл бол ердийн хэллэгээр хангадаг бүх боломжуудын өчүүхэн хэсэг гэдгийг би тодруулахыг хүсч байна. Олон тооны тусгай тэмдэгтүүд, тэдгээрийг ашиглах дүрмүүд байдаг бөгөөд энэ сэдвээр бүхэл бүтэн ном бичсэн (би ядаж үүнийг эхлүүлэхийг зөвлөж байна). Нэг ёсондоо байнгын хэллэг бичих нь бараг л урлаг. Бараг үргэлж, зохион бүтээсэн ердийн илэрхийлэлийг сайжруулж эсвэл нэмж оруулснаар илүү гоёмсог эсвэл өргөн хүрээний оролтын өгөгдөлтэй ажиллах боломжтой болгодог.
Бусад хүмүүсийн ердийн илэрхийлэлд дүн шинжилгээ хийх, задлан шинжлэх эсвэл өөрийн дибаг хийх хэд хэдэн тохиромжтой онлайн үйлчилгээ байдаг: RegEx101, RegExr ба түүнээс дээш
Харамсалтай нь сонгодог ердийн хэллэгүүдийн бүх шинж чанарууд нь VBA-д дэмжигдээгүй (жишээлбэл, урвуу хайлт эсвэл POSIX ангиуд) бөгөөд кирилл үсгээр ажиллах боломжтой боловч анх удаа танд таалагдахад хангалттай зүйл гэж би бодож байна.
Хэрэв та энэ сэдвийн талаар шинэ биш бөгөөд танд хуваалцах зүйл байгаа бол Excel дээр ажиллахад хэрэгтэй ердийн илэрхийлэлүүдийг доорх тайлбар хэсэгт үлдээгээрэй. Нэг ухаан сайн, харин хоёр гутал бол хос!
- ОРЛУУЛАХ функцээр текстийг солих, цэвэрлэх
- Текст дэх латин үсгийг хайх, тодруулах
- Хамгийн ойрын ижил төстэй текстийг хайх (Иванов = Ивонов = Иваноф гэх мэт)