Агуулга
Миний YouTube сувгийн хамгийн их үзсэн видеонуудын нэг бол Microsoft Excel-ийн Flash Fill-ийн тухай видео юм. Энэхүү хэрэгслийн мөн чанар нь хэрэв та ямар нэгэн байдлаар эх мэдээллээ өөрчлөх шаардлагатай бол зэргэлдээ баганад авахыг хүссэн үр дүнгээ бичиж эхлэх хэрэгтэй. Хэд хэдэн гараар бичсэн нүднүүдийн дараа (ихэвчлэн 2-3 хангалттай байдаг) Excel нь танд хэрэгтэй өөрчлөлтүүдийн логикийг "ойлгох" бөгөөд таны бичсэн зүйлийг автоматаар үргэлжлүүлж, танд тохирсон бүх ажлыг гүйцэтгэнэ.
Үр ашгийн гол чанар. Бид бүгдийн маш их хайрладаг шидэт "зөв хийх" товчлуур, тийм үү?
Үнэн хэрэгтээ Power Query-д ийм хэрэгслийн аналог байдаг - үүнийг тэнд нэрлэдэг Жишээнүүдийн багана (Жишээнээс авсан багана). Үнэн хэрэгтээ энэ бол Power Query-д суурилуулсан жижиг хиймэл оюун ухаан бөгөөд таны өгөгдлөөс хурдан суралцаж, дараа нь хувиргах боломжтой. Бодит ажлуудад бидэнд хаана хэрэгтэй болохыг ойлгохын тулд түүний чадварыг хэд хэдэн практик хувилбараар нарийвчлан авч үзье.
Жишээ 1. Текстийг наах/тайрах
Бид Excel-д ажилчдын талаархи мэдээлэл бүхий ийм "ухаалаг" хүснэгттэй гэж бодъё.
Үүнийг товчлуураар Power Query-д стандарт аргаар ачаална уу Хүснэгтээс/Мужаас таб Өгөгдөл (Өгөгдөл - Хүснэгтээс/Мужаас).
Ажилтан бүрийн овог нэр, эхний үсэг бүхий багана (эхний ажилтанд Иванов С.В. гэх мэт) нэмэх шаардлагатай гэж бодъё. Энэ асуудлыг шийдэхийн тулд та хоёр аргын аль нэгийг ашиглаж болно.
- эх өгөгдөл бүхий баганын гарчиг дээр хулганы баруун товчийг дараад командыг сонгоно уу Жишээнүүдээс багана нэмнэ үү (Жишээнээс багана нэмэх);
- таб дээр өгөгдөл бүхий нэг буюу хэд хэдэн баганыг сонгоно уу Багана нэмж байна баг сонгох Жишээнүүдийн багана. Энд, унждаг жагсаалтаас та сонгосон баганыг бүхэлд нь эсвэл зөвхөн дүн шинжилгээ хийх шаардлагатай эсэхийг зааж өгч болно.
Дараа нь бүх зүйл энгийн - баруун талд гарч ирэх баганад бид хүссэн үр дүнгийн жишээг оруулж эхлэх бөгөөд Power Query-д суурилуулсан хиймэл оюун ухаан нь бидний хувиргах логикийг ойлгохыг хичээж, цааш нь өөрөө үргэлжлүүлнэ.
Дашрамд хэлэхэд, та энэ баганын аль ч нүдэнд зөв сонголтуудыг оруулж болно, өөрөөр хэлбэл дээрээс доош, дараалсан байх албагүй. Мөн та гарчгийн талбар дахь тэмдэглэгээг ашиглан дүн шинжилгээ хийх баганыг хялбархан нэмж эсвэл хасах боломжтой.
Цонхны дээд талд байгаа томъёонд анхаарлаа хандуулаарай - энэ нь ухаалаг Power Query нь бидэнд хэрэгтэй үр дүнг авахын тулд бүтээдэг зүйл юм. Дашрамд хэлэхэд энэ нь энэ хэрэгсэл ба хоёрын үндсэн ялгаа юм Шууд дүүргэх Excel дээр. Шуурхай дүүргэлт нь "хар хайрцаг" шиг ажилладаг - тэдгээр нь өөрчлөлтийн логикийг харуулахгүй, харин зүгээр л бэлэн үр дүнг өгдөг бөгөөд бид үүнийг энгийн зүйл гэж үздэг. Энд бүх зүйл ил тод бөгөөд та өгөгдөлд яг юу болж байгааг тодорхой ойлгох боломжтой.
Хэрэв та Power Query нь "санааг олж авсан" гэж үзвэл товчлуурыг аюулгүйгээр дарж болно OK эсвэл гарын товчлол Ctrl+оруулна уу – Power Query-ийн зохион бүтээсэн томъёо бүхий захиалгат багана үүснэ. Дашрамд хэлэхэд, үүнийг дараа нь энгийн гараар үүсгэсэн багана болгон хялбархан засах боломжтой (командын тусламжтайгаар Багана нэмэх - Тусгай багана) алхамын нэрний баруун талд байгаа арааны дүрс дээр дарж:
Жишээ 2: Өгүүлбэр дэх тохиолдол
Хэрэв та текст бүхий баганын гарчиг дээр хулганы баруун товчийг дараад командыг сонгоно уу Хувиргах (Өөрчлөх), дараа нь та бүртгэлийг өөрчлөх үүрэгтэй гурван тушаалыг харж болно:
Тохиромжтой, дажгүй, гэхдээ энэ жагсаалтад би хувьдаа өөр нэг сонголт үргэлж дутагдаж байсан - өгүүлбэрт том үсгээр бичих нь үг бүрийн эхний үсэг биш, харин нүдний эхний үсэг болох тохиолдол байдаг. Энэ нь жижиг (жижиг) үсгээр харагдах үед текстийн үлдсэн хэсэг.
Энэхүү дутуу функцийг хиймэл оюун ухаанаар хэрэгжүүлэхэд хялбар байдаг Жишээнүүдээс авсан баганууд – Power Query-г ижил байдлаар үргэлжлүүлэхийн тулд хэд хэдэн сонголтыг оруулаарай:
Энд томьёоны хувьд Power Query нь олон тооны функцуудыг ашигладаг Текст. Дээд и Текст. Доод, текстийг том, жижиг үсэг болгон хөрвүүлэх, функцууд Текст. Эхлэх и Текст. Дунд - Excel-ийн LEFT ба PSTR функцуудын аналогууд нь текстээс зүүн ба дундаас дэд мөрийг гаргаж авах боломжтой.
Жишээ 3. Үг солих
Заримдаа хүлээн авсан өгөгдлийг боловсруулахдаа нүдн дэх үгсийг өгөгдсөн дарааллаар дахин байрлуулах шаардлагатай болдог. Мэдээжийн хэрэг, та баганыг салгагчаар тусад нь үгийн баганад хувааж, дараа нь заасан дарааллаар нааж болно (хоосон зай нэмэхээ бүү мартаарай), гэхдээ хэрэгслийн тусламжтайгаар Жишээнүүдийн багана бүх зүйл илүү хялбар байх болно:
Жишээ 4: Зөвхөн тоо
Өөр нэг чухал ажил бол эсийн агуулгаас зөвхөн тоо (тоо) гаргаж авах явдал юм. Өмнөхтэй адил Power Query руу өгөгдөл ачаалсны дараа таб руу очно уу Багана нэмэх - Жишээнүүдээс багана програм нь яг юу авахыг хүсч байгаагаа ойлгохын тулд гараар хэд хэдэн нүдийг бөглөнө үү.
Bingo!
Дахин хэлэхэд Query нь томьёог зөв үүсгэсэн эсэхийг шалгахын тулд цонхны дээд хэсгийг харах нь зүйтэй - энэ тохиолдолд энэ нь функц агуулна. Текст. Сонго, энэ нь таны таамаглаж байгаагаар жагсаалтын дагуу эх бичвэрээс өгөгдсөн тэмдэгтүүдийг гаргаж авдаг. Дараа нь энэ жагсаалтыг мэдээжийн хэрэг шаардлагатай бол томъёоны мөрөнд хялбархан засварлаж болно.
Жишээ 5: Зөвхөн текст
Өмнөх жишээний нэгэн адил та зөвхөн текстийг гаргаж авах боломжтой, бүх тоо, цэг таслал гэх мэтийг устгаж болно.
Энэ тохиолдолд аль хэдийн эсрэг утгатай функцийг ашигладаг - Text.Remove, энэ нь өгөгдсөн жагсаалтын дагуу эх мөрөөс тэмдэгтүүдийг устгадаг.
Жишээ 6: Үсэг, тоон будаанаас өгөгдөл гаргаж авах
Power Query нь нүдэн дэх үсэг тоон будаанаас хэрэгтэй мэдээллийг гаргаж авах, жишээлбэл, банкны хуулга дээрх төлбөрийн зорилгын тайлбараас дансны дугаарыг авах шаардлагатай үед илүү хэцүү тохиолдолд туслах болно.
Power Query-ийн үүсгэсэн хувиргах томъёо нь нэлээд төвөгтэй байж болохыг анхаарна уу:
Унших, ойлгоход хялбар болгох үүднээс үүнийг үнэгүй онлайн үйлчилгээг ашиглан илүү эрүүл саруул хэлбэр болгон хувиргаж болно. Power Query Formatter:
Маш хэрэгтэй зүйл - бүтээгчиддээ хүндэтгэл үзүүлье!
Жишээ 7: Огноог хөрвүүлэх
арга хэрэгсэл Жишээнүүдийн багана огноо эсвэл огноо цагийн баганад бас хэрэглэж болно. Таныг огнооны эхний цифрүүдийг оруулах үед Power Query нь хөрвүүлэх боломжтой бүх сонголтуудын жагсаалтыг харуулах болно.
Тиймээс та анхны огноог "жил-сар-өдөр" гэх мэт ямар ч чамин формат руу хялбархан хөрвүүлэх боломжтой.
Жишээ 8: Ангилал
Хэрэв бид хэрэгслийг ашигладаг бол Жишээнүүдийн багана тоон өгөгдөл бүхий баганад, энэ нь өөрөөр ажилладаг. Бид Power Query-д ажилчдын тестийн үр дүнг ачааллаа гэж бодъё (0-100-ийн муж дахь нөхцөлт оноо) ба бид дараах нөхцөлт зэрэглэлийг ашигладаг:
- Мастерууд - 90-ээс дээш оноо авсан хүмүүс
- Мэргэжилтнүүд - 70-аас 90 оноо авсан
- Хэрэглэгчид - 30-аас 70 хүртэл
- Эхлэн суралцагчид - 30-аас доош оноо авсан хүмүүс
Хэрэв бид жагсаалтад жишээнүүдээс багана нэмж, эдгээр зэрэглэлийг гараар цэгцэлж эхэлбэл тун удахгүй Power Query бидний санааг авч, операторууд хоорондоо уялдаатай байгаа томьёо бүхий баганыг нэмэх болно. if Логик хэрэгжих бөгөөд энэ нь бидэнд хэрэгтэй зүйлтэй маш төстэй юм.
Дахин хэлэхэд та нөхцөл байдлыг эцэс хүртэл дарж чадахгүй, гэхдээ товшино уу OK Дараа нь томъёонд байгаа босго утгыг аль хэдийн засаарай - энэ нь илүү хурдан юм:
Дүгнэлт
Мэдээж хэрэг хэрэгсэл Жишээнүүдийн багана Энэ нь "шидэт эм" биш бөгөөд эрт орой хэзээ нэгэн цагт стандарт бус нөхцөл байдал эсвэл өгөгдөлд "нэгдэл ферм"-ийн үл тоомсорлосон тохиолдлууд гарч ирэх бөгөөд Power Query амжилтгүй болж, бидний хүссэн зүйлийг хийж чадахгүй болно. бидний хувьд зөв. Гэсэн хэдий ч туслах хэрэгслийн хувьд энэ нь маш сайн. Нэмж дурдахад, түүний үүсгэсэн томъёог судалснаар та M хэлний функцүүдийн талаархи мэдлэгээ өргөжүүлэх боломжтой бөгөөд энэ нь ирээдүйд үргэлж хэрэг болно.
- Power Query дахь ердийн илэрхийлэлтэй (RegExp) текстийг задлах
- Power Query дээрх бүдэг текст хайлт
- Microsoft Excel дээр Flash бөглөх