Мазмұны
Менің 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 көптеген функцияларды пайдаланады Мәтін. Жоғарғы и Мәтін.Төменгі, мәтінді сәйкесінше бас және кіші әріптерге түрлендіру және функциялары Мәтін.Бастау и Мәтін.Орта – мәтіннен сол жақтан және ортадан ішкі жолды шығаруға қабілетті LEFT және PSTR Excel функцияларының аналогтары.
Мысал 3. Сөздердің орнын ауыстыру
Кейде алынған мәліметтерді өңдеу кезінде ұяшықтардағы сөздерді берілген реттілікпен қайта орналастыру қажет болады. Әрине, сіз бағанды бөлгіш арқылы бөлек сөз бағандарына бөліп, содан кейін оны көрсетілген ретпен желімдей аласыз (бос орындарды қосуды ұмытпаңыз), бірақ құралдың көмегімен Мысалдардан алынған баған бәрі әлдеқайда оңай болады:
4-мысал: Тек сандар
Тағы бір өте маңызды міндет - ұяшық мазмұнынан тек сандарды (сандарды) шығарып алу. Бұрынғыдай Power Query ішіне деректерді жүктегеннен кейін қойындыға өтіңіз Бағанды қосу – Мысалдардағы баған Бағдарлама нақты не алғымыз келетінін түсінуі үшін бірнеше ұяшықты қолмен толтырыңыз:
Бинго!
Қайтадан, 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 толтыру