Агуулга
Эх бичвэрээс түлхүүр үг хайх нь өгөгдөлтэй ажиллахад хамгийн түгээмэл ажлуудын нэг юм. Дараах жишээг ашиглан түүний шийдлийг хэд хэдэн аргаар харцгаая.
Та бид хоёр түлхүүр үгсийн жагсаалт - машины брэндүүдийн нэрс - бүх төрлийн сэлбэг хэрэгслийн том хүснэгттэй байна гэж бодъё, хэрэв сэлбэг хэрэгсэл нь нэгээс илүү тохирох бол тайлбар нь заримдаа нэг эсвэл хэд хэдэн ийм брэндийг агуулж болно. машины брэнд. Бидний даалгавар бол өгөгдсөн тусгаарлах тэмдэгтээр (жишээ нь таслал) хөрш зэргэлдээ нүднүүдээс илрүүлсэн бүх түлхүүр үгсийг олж харуулах явдал юм.
Арга 1. Power Query
Мэдээжийн хэрэг, эхлээд бид гарын товчлолыг ашиглан хүснэгтээ динамик ("ухаалаг") болгон хувиргадаг Ctrl+T эсвэл тушаалууд Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах), тэдэнд нэр өгнө үү (жишээлбэл Марки Сэлбэг эд анги) болон таб дээрээс сонгоод Power Query засварлагч руу нэг нэгээр нь ачаална уу Өгөгдөл - Хүснэгтээс/Мужаас (Өгөгдөл - Хүснэгтээс/Мужаас). Хэрэв танд Power Query-г тусдаа нэмэлт болгон суулгасан Excel 2010-2013 хувилбарын хуучин хувилбарууд байгаа бол таб дээр хүссэн товчлуур байх болно. Эрчим хүчний асуулга. Хэрэв танд Excel 365-ийн цоо шинэ хувилбар байгаа бол товчлуур Хүснэгтээс/Мужаас одоо тэнд дуудлаа Навчтай (Хуудаснаас).
Хүснэгт бүрийг Power Query-д ачаалсны дараа бид командын тусламжтайгаар Excel рүү буцна Нүүр хуудас — Хаах ба ачаалах — Хаах болон ачаалах... — Зөвхөн холболт үүсгэх (Нүүр хуудас — Хаах & Ачаалах — Хаах & Ачаалах... — Зөвхөн холболт үүсгэх).
Одоо давхар хүсэлт үүсгэцгээе Сэлбэг эд ангидээр нь хулганы баруун товчийг дараад сонгоно Давхардсан хүсэлт (Давхардсан асуулга), дараа нь үүссэн хуулбар хүсэлтийн нэрийг өөрчил үр дүн мөн бид түүнтэй үргэлжлүүлэн ажиллах болно.
Үйлдлийн логик нь дараах байдалтай байна.
- Нарийвчилсан таб дээр Багана нэмж байна баг сонгох Тусгай багана (Багана нэмэх - Тусгай багана) болон томъёог оруулна уу = Брэнд. Дарсны дараа OK Бид шинэ багана авах бөгөөд нүд бүрт бидний түлхүүр үгсийн жагсаалт бүхий үүрлэсэн хүснэгт байх болно - automaker brands:
- Нэмэгдсэн баганын толгой хэсэгт байрлах давхар сумтай товчлуурыг ашиглан бүх үүрлэсэн хүснэгтийг өргөжүүлнэ үү. Үүний зэрэгцээ, сэлбэг хэрэгслийн тайлбар бүхий мөрүүд нь брэндийн тооноос хэд дахин нэмэгдэх бөгөөд бид "сэлбэг хэрэгслийн брэнд" гэсэн бүх боломжит хос хослолыг авах болно.
- Нарийвчилсан таб дээр Багана нэмж байна баг сонгох Нөхцөлт багана (Нөхцөлт багана) мөн эх бичвэрт (хэсгийн тайлбар) түлхүүр үг (брэнд) байгаа эсэхийг шалгах нөхцөлийг тавь:
- Хайлтын жижиг үсгийг хайхрамжгүй болгохын тулд томъёоны мөрөнд гурав дахь аргументыг гараар нэмнэ үү Compare.OrdinalIgnoreCase тохиолдлыг шалгах функц руу Текст. Агуулж байна (хэрэв томьёоны мөр харагдахгүй бол таб дээр идэвхжүүлж болно тойм):
- Бид үүссэн хүснэгтийг шүүж, сүүлчийн баганад зөвхөн нэгийг нь үлдээж, өөрөөр хэлбэл таарч, шаардлагагүй баганыг устгана. Тохиолдлууд.
- Ижил тайлбаруудыг тушаалаар бүлэглэх Бүлэг таб Хувиргах (Өөрчлөх - Бүлэглэх). Нэгтгэх үйл ажиллагааны хувьд сонгоно уу Бүх мөрүүд (Бүх мөр). Гаралтын үед бид сэлбэг хэрэгсэл бүрийн бүх мэдээллийг агуулсан хүснэгт бүхий баганыг авдаг, үүнд бидэнд шаардлагатай автомашин үйлдвэрлэгчдийн брэндүүд орно.
- Хэсэг бүрийн дүнг гаргахын тулд таб дээр өөр тооцоолсон багана нэмнэ үү Багана нэмэх - Тусгай багана (Багана нэмэх - Тусгай багана) Хүснэгтээс бүрдсэн томъёог ашиглана уу (тэдгээр нь манай баганад байрладаг Дэлгэрэнгүй) болон задалсан баганын нэр:
- Бид үүссэн баганын толгой хэсэгт байрлах давхар сумтай товчлуур дээр дараад командыг сонгоно Утгыг задлах (Утгыг задлах)Хүссэн хязгаарлагч тэмдэгтээр марк гаргахын тулд:
- Шаардлагагүй баганыг устгаж байна Дэлгэрэнгүй.
- Үүссэн хүснэгтэд тайлбар дээр ямар ч брэнд олдоогүй, алга болсон хэсгүүдийг нэмэхийн тулд бид асуулга нэгтгэх процедурыг гүйцэтгэдэг. үр дүн анхны хүсэлтээр Сэлбэг эд анги товч Хосолсон таб Нүүр хуудас (Нүүр хуудас - Асуултуудыг нэгтгэх). Холболтын төрөл - Гаднах нэгдэх баруун (Баруун гадна талын нэгдэл):
- Үлдсэн зүйл бол нэмэлт багануудыг устгаж, үлдсэн баганын нэрийг өөрчлөх явдал бөгөөд бидний даалгавар шийдэгдлээ:
Арга 2. Томъёо
Хэрэв танд Excel 2016 болон түүнээс хойшхи хувилбар байгаа бол бидний асуудлыг шинэ функцийг ашиглан маш авсаархан, гоёмсог байдлаар шийдэж болно. ХОЛБОО (TEXTJOIN):
Энэ томъёоны цаадах логик нь энгийн:
- Чиг үүрэг SEARCH (ОЛОХ) Тухайн хэсгийн одоогийн тайлбараас брэнд тус бүрийн илрэлийг эрэлхийлж, тухайн брэндийг олсон тэмдэгтийн серийн дугаар эсвэл алдаа #VALUE! Хэрэв брэнд нь тайлбарт байхгүй бол.
- Дараа нь функцийг ашиглана уу IF (Хэрэв) и ЕОШИБКА (АЛДАА) Бид алдааг хоосон текст мөр ""-ээр, тэмдэгтүүдийн дарааллын тоог брэндийн нэрээр сольж өгдөг.
- Үр дүнд нь хоосон нүднүүд болон олдсон брэндүүдийг функцийг ашиглан өгөгдсөн тусгаарлах тэмдэгтээр дамжуулан нэг мөрөнд нэгтгэдэг. ХОЛБОО (TEXTJOIN).
Speedup-д зориулсан гүйцэтгэлийн харьцуулалт болон Power Query Query буфер
Гүйцэтгэлийн туршилтын хувьд эхний өгөгдөл болгон 100 сэлбэг хэрэгслийн тодорхойлолтын хүснэгтийг авч үзье. Үүн дээр бид дараах үр дүнг авна.
- Томъёогоор дахин тооцоолох хугацаа (2-р арга) – 9 сек. эхлээд томьёог бүхэл багананд хуулахдаа 2 сек. at давтан (буфер нь нөлөөлж магадгүй).
- Power Query асуулгын шинэчлэлтийн хугацаа (1-р арга) хамаагүй муу байна - 110 секунд.
Мэдээжийн хэрэг, тухайн компьютерийн техник хангамж, Office-ийн суулгасан хувилбар, шинэчлэлтүүдээс их зүйл шалтгаална, гэхдээ миний бодлоор ерөнхий дүр зураг тодорхой байна.
Power Query хүсэлтийг хурдасгахын тулд хайлтын хүснэгтийг буфер болгоё Марк, учир нь энэ нь асуулга гүйцэтгэх явцад өөрчлөгддөггүй бөгөөд үүнийг байнга дахин тооцоолох шаардлагагүй (Power Query de facto хийдэг шиг). Үүний тулд бид функцийг ашигладаг Хүснэгт. Буфер суурилуулсан Power Query хэлнээс M.
Үүнийг хийхийн тулд асуулга нээнэ үү үр дүн болон таб дээр тойм товчийг дарна уу Нарийвчилсан редактор (Харах — Нарийвчилсан редактор). Нээгдсэн цонхонд шинэ хувьсагчтай мөр нэмнэ Марки 2, энэ нь манай автомашин үйлдвэрлэгчийн лавлахын буферлэгдсэн хувилбар байх бөгөөд энэ шинэ хувьсагчийг дараа нь дараах хайлтын командад ашиглана уу:
Ийм сайжруулсны дараа бидний хүсэлтийг шинэчлэх хурд бараг 7 дахин нэмэгддэг - 15 секунд хүртэл. шал өөр зүйл 🙂
- Power Query дээрх бүдэг текст хайлт
- Томьёотой текстийг бөөнөөр нь солих
- List.Accumate функц бүхий Power Query дотор текстийг бөөнөөр нь солих