Mundarija
YouTube kanalimdagi eng ko'p ko'rilgan videolardan biri bu Microsoft Excel dasturida Flash Fill haqida video. Ushbu vositaning mohiyati shundan iboratki, agar siz manba ma'lumotlaringizni qandaydir tarzda o'zgartirishingiz kerak bo'lsa, u holda siz qo'shni ustunga olishni xohlagan natijani yozishni boshlashingiz kerak. Bir nechta qo'lda terilgan kataklardan so'ng (odatda 2-3 dona kifoya qiladi), Excel sizga kerak bo'lgan o'zgarishlar mantiqini "tushunadi" va siz yozgan narsalarni avtomatik ravishda davom ettirib, siz uchun barcha monoton ishlarni yakunlaydi:
Samaradorlikning kvintessensiyasi. Biz hammamiz yaxshi ko'radigan sehrli "to'g'ri bajaring" tugmasi, to'g'rimi?
Aslida, Power Query-da bunday vositaning analogi mavjud - u erda chaqiriladi Misollardan ustun (Misollardan ustun). Aslida, bu Power Query-ga o'rnatilgan kichik sun'iy intellekt bo'lib, u sizning ma'lumotlaringizni tezda o'rganishi va keyin uni o'zgartirishi mumkin. Haqiqiy vazifalarda qayerda foydali bo'lishi mumkinligini tushunish uchun uning imkoniyatlarini bir nechta amaliy stsenariylarda batafsil ko'rib chiqaylik.
Misol 1. Matnni yopishtirish/kesish
Aytaylik, Excel-da xodimlar to'g'risidagi ma'lumotlarga ega shunday "aqlli" jadval mavjud:
Uni Power Query-ga standart usulda - tugma bilan yuklang Jadval/diapazondan tab ma'lumotlar (Ma'lumotlar - Jadvaldan/diapazondan).
Aytaylik, har bir xodim uchun familiyalar va bosh harflar bilan ustun qo'shishimiz kerak (birinchi xodim uchun Ivanov SV va boshqalar). Ushbu muammoni hal qilish uchun siz ikkita usuldan birini qo'llashingiz mumkin:
- manba ma'lumotlari bilan ustun sarlavhasini o'ng tugmasini bosing va buyruqni tanlang Misollardan ustun qo'shing (Misollardan ustun qo'shing);
- ma'lumotlar va yorliqda bir yoki bir nechta ustunlarni tanlang Ustun qo'shish jamoani tanlang Misollardan ustun. Bu erda, ochiladigan ro'yxatda siz barcha yoki faqat tanlangan ustunlarni tahlil qilish kerakligini belgilashingiz mumkin.
Keyin hamma narsa oddiy - o'ng tomonda paydo bo'lgan ustunda biz kerakli natijalar misollarini kiritishni boshlaymiz va Power Query-ga o'rnatilgan sun'iy intellekt bizning transformatsiya mantig'imizni tushunishga harakat qiladi va o'z-o'zidan davom etadi:
Aytgancha, ushbu ustunning istalgan kataklariga to'g'ri variantlarni kiritishingiz mumkin, ya'ni yuqoridan pastga va qatorga bo'lishi shart emas. Bundan tashqari, sarlavha satridagi belgilash katakchalari yordamida tahlildan ustunlarni osongina qo'shishingiz yoki olib tashlashingiz mumkin.
Oynaning yuqori qismidagi formulaga e'tibor bering - bu biz kerakli natijalarni olish uchun aqlli Power Query yaratadi. Aytgancha, bu ushbu vosita va o'rtasidagi asosiy farq Darhol to'ldirish Excelda. Bir zumda to'ldirish "qora quti" kabi ishlaydi - ular bizga o'zgarishlar mantig'ini ko'rsatmaydi, balki shunchaki tayyor natijalarni beradi va biz ularni odatiy hol sifatida qabul qilamiz. Bu erda hamma narsa shaffof va siz har doim ma'lumotlar bilan nima sodir bo'layotganini aniq tushunishingiz mumkin.
Agar Power Query "g'oyani ushlaganini" ko'rsangiz, tugmachani ishonch bilan bosishingiz mumkin OK yoki klaviatura yorlig'i Ctrl+kiriting – Power Query tomonidan ixtiro qilingan formulaga ega maxsus ustun yaratiladi. Aytgancha, uni keyinchalik oddiy qo'lda yaratilgan ustun sifatida osongina tahrirlash mumkin (buyruq bilan Ustun qo'shish - Maxsus ustun) qadam nomining o'ng tomonidagi tishli belgini bosish orqali:
2-misol: Gaplardagi kabi holat
Agar siz matnli ustun sarlavhasini o'ng tugmasini bosing va buyruqni tanlang O'tkazish (O'zgartirish), keyin siz registrni o'zgartirish uchun mas'ul bo'lgan uchta buyruqni ko'rishingiz mumkin:
Qulay va zo'r, lekin bu ro'yxatda, masalan, shaxsan menga har doim yana bitta variant etishmayapti - jumlalarda bo'lgani kabi, katta harf (katta) har bir so'zning birinchi harfiga emas, balki katakchadagi birinchi harfga aylanadi va Bu kichik (kichik) harflarda ko'rsatilganda matnning qolgan qismi.
Ushbu etishmayotgan xususiyatni sun'iy intellekt yordamida amalga oshirish oson Misollardan ustunlar – Power Query xuddi shu ruhda davom etishi uchun bir nechta variantni kiriting:
Bu erda formula sifatida Power Query bir qator funktsiyalardan foydalanadi Matn. Yuqori и Matn. Pastki, matnni mos ravishda katta va kichik harflarga aylantirish va funksiyalar Matn. Boshlash и Matn.Oʻrta - Excelning LEFT va PSTR funksiyalarining analoglari, matndan pastki qatorni chapdan va o'rtadan ajratib olish imkoniyatiga ega.
3-misol. So‘zlarni almashtirish
Ba'zan olingan ma'lumotlarni qayta ishlashda hujayralardagi so'zlarni ma'lum ketma-ketlikda qayta joylashtirish kerak bo'ladi. Albatta, siz ustunni ajratuvchi orqali alohida so'z ustunlariga bo'lishingiz va keyin uni belgilangan tartibda yopishtirishingiz mumkin (bo'shliqlarni qo'shishni unutmang), lekin asbob yordamida Misollardan ustun hamma narsa ancha oson bo'ladi:
4-misol: Faqat raqamlar
Yana bir juda muhim vazifa - bu hujayra tarkibidan faqat raqamlarni (raqamlarni) chiqarib tashlashdir. Avvalgidek, Power Query-ga ma'lumotlarni yuklaganingizdan so'ng, yorliqga o'ting Ustun qo'shish - misollardan ustun va dastur biz nimani olishni xohlayotganimizni tushunishi uchun bir nechta kataklarni qo'lda to'ldiring:
Bingo!
Shunga qaramay, Query formulani to'g'ri yaratganiga ishonch hosil qilish uchun oynaning yuqori qismiga qarashga arziydi - bu holda u funktsiyani o'z ichiga oladi. Matn. Tanlang, siz taxmin qilganingizdek, ro'yxatga muvofiq manba matndan berilgan belgilarni chiqaradi. Keyinchalik, ushbu ro'yxat, agar kerak bo'lsa, formulalar satrida osongina tahrirlanishi mumkin.
5-misol: Faqat matn
Oldingi misolga o'xshab, siz tortib olishingiz mumkin va aksincha - faqat matnni, barcha raqamlarni, tinish belgilarini va boshqalarni o'chirib tashlashingiz mumkin.
Bunday holda, ma'nosi allaqachon qarama-qarshi bo'lgan funktsiyadan foydalaniladi - Text.Remove, berilgan ro'yxatga muvofiq asl satrdan belgilarni olib tashlaydi.
6-misol: Alfanumerik bo'tqadan ma'lumotlarni ajratib olish
Power Query shuningdek, qiyinroq holatlarda, yacheykadagi alfanumerik bo'tqadan foydali ma'lumotlarni olish kerak bo'lganda yordam berishi mumkin, masalan, bank ko'chirmasidagi to'lov maqsadi tavsifidan hisob raqamini oling:
E'tibor bering, Power Query tomonidan yaratilgan konvertatsiya formulasi juda murakkab bo'lishi mumkin:
O'qish va tushunish uchun qulaylik uchun uni bepul onlayn xizmatdan foydalangan holda ancha oqilona shaklga aylantirish mumkin. Power Query Formatter:
Juda qulay narsa - ijodkorlarga hurmat!
7-misol: Sanalarni o'zgartirish
asbob Misollardan ustun sana yoki sana va vaqt ustunlariga ham qo'llanilishi mumkin. Sananing birinchi raqamlarini kiritganingizda, Power Query barcha mumkin bo'lgan aylantirish opsiyalari ro'yxatini ko'rsatadi:
Shunday qilib, siz asl sanani har qanday ekzotik formatga osongina o'zgartirishingiz mumkin, masalan, "yil-oy-kun":
8-misol: Turkumlashtirish
Agar asbobdan foydalansak Misollardan ustun raqamli ma'lumotlarga ega ustunga, u boshqacha ishlaydi. Aytaylik, bizda Power Query-ga xodimlarning test natijalari yuklangan (shartli ballar 0-100 oralig'ida) va biz quyidagi shartli gradatsiyadan foydalanamiz:
- Magistrlar - 90 dan ortiq ball to'plaganlar
- Mutaxassislar - 70 dan 90 ballgacha
- Foydalanuvchilar - 30 dan 70 gacha
- Yangi boshlanuvchilar - 30 dan kam ball to'plaganlar
Agar biz ro'yxatga misollardan ustun qo'shsak va bu darajalarni qo'lda tartibga solishni boshlasak, tez orada Power Query bizning fikrimizni qabul qiladi va operatorlar bir-biriga joylashtirilgan formulali ustunni qo'shadi. if Bizga kerak bo'lgan narsaga juda o'xshash mantiq amalga oshiriladi:
Shunga qaramay, siz vaziyatni oxirigacha bosishingiz mumkin emas, lekin bosing OK va keyin formulada allaqachon chegara qiymatlarini to'g'rilang - bu tezroq:
Xulosa
Albatta, vosita Misollardan ustun Bu "sehrli tabletka" emas va ertami-kechmi Power Query muvaffaqiyatsizlikka uchragan va biz xohlagan narsani bajara olmaydigan ma'lumotlarda nostandart vaziyatlar yoki ayniqsa e'tibordan chetda qolgan "kolxoz" holatlari bo'ladi. biz uchun to'g'ri. Biroq, yordamchi vosita sifatida, bu juda yaxshi. Bundan tashqari, u yaratgan formulalarni o'rganish orqali siz M tilining funktsiyalari haqidagi bilimingizni kengaytirishingiz mumkin, bu kelajakda har doim foydali bo'ladi.
- Power Query-da oddiy iboralar (RegExp) bilan matnni tahlil qilish
- Power Query-da noaniq matn qidirish
- Microsoft Excel-da Flash to'ldirish