змест
Адно з самых прагляданых відэа на маім канале YouTube - гэта відэа пра Flash Fill у Microsoft Excel. Сутнасць гэтага інструмента заключаецца ў тым, што калі вам трэба нейкім чынам пераўтварыць зыходныя дадзеныя, то вам проста трэба пачаць уводзіць вынік, які вы хочаце атрымаць, у суседнім слупку. Пасля некалькіх набраных ўручную вочак (звычайна дастаткова 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 перайдзіце на ўкладку Даданне слупка – Слупок з прыкладаў і запаўняем пару вочак ўручную, каб праграма зразумела, што менавіта мы хочам атрымаць:
Бінга!
Зноў жа, варта паглядзець на верхнюю частку акна, каб пераканацца, што Query правільна згенераваў формулу – у гэтым выпадку яна ўтрымлівае функцыю Тэкст. Выберыце, які, як можна здагадацца, здабывае дадзеныя сімвалы з зыходнага тэксту ў адпаведнасці са спісам. У далейшым гэты спіс, вядома, пры неабходнасці можна лёгка адрэдагаваць у радку формул.
Прыклад 5: толькі тэкст
Як і ў папярэднім прыкладзе, можна выцягнуць і наадварот – толькі тэкст, выдаліўшы ўсе лічбы, знакі прыпынку і інш.
У дадзеным выпадку выкарыстоўваецца ўжо супрацьлеглая па сэнсе функцыя – Text.Remove, якая выдаляе сімвалы з зыходнага радка па зададзеным спісе.
Прыклад 6: Выманне дадзеных з літарна-лічбавай кашы
Power Query можа дапамагчы і ў больш складаных выпадках, калі з літарна-лічбавай кашы ў вочку трэба атрымаць карысную інфармацыю, напрыклад, атрымаць нумар рахунку з апісання прызначэння плацяжу ў выпісцы з банка:
Звярніце ўвагу, што формула пераўтварэння, згенераваная Power Query, можа быць даволі складанай:
Для зручнасці чытання і разумення яго можна пераўтварыць у значна больш разумную форму з дапамогай бясплатнага інтэрнэт-сэрвісу. Фарматавальнік Power Query:
Вельмі зручная рэч – рэспект стваральнікам!
Прыклад 7: Пераўтварэнне дат
Інструмент Слупок з прыкладамі можа прымяняцца да слупкоў даты і часу. Калі вы ўводзіце першыя лічбы даты, Power Query паслужліва адлюструе спіс усіх магчымых варыянтаў пераўтварэння:
Такім чынам, вы можаце лёгка пераўтварыць зыходную дату ў любы экзатычны фармат, напрыклад, «год-месяц-дзень»:
Прыклад 8: Катэгарызацыя
Калі мы выкарыстоўваем інструмент Слупок з прыкладамі у слупок з лікавымі дадзенымі, ён працуе па-іншаму. Выкажам здагадку, што ў нас ёсць вынікі тэстаў супрацоўнікаў, загружаныя ў Power Query (умоўныя балы ў дыяпазоне 0-100), і мы выкарыстоўваем наступную ўмоўную градацыю:
- Майстры – тыя, хто набраў больш за 90 балаў
- Эксперты – ад 70 да 90 балаў
- Карыстальнікаў - ад 30 да 70
- Пачаткоўцы – тыя, хто набраў менш за 30 балаў
Калі мы дадамо ў спіс слупок з прыкладаў і пачнем уручную расстаўляць гэтыя градацыі, то вельмі хутка Power Query падхопіць нашу ідэю і дадасць слупок з формулай, дзе аператары ўкладзеныя адзін у аднаго if будзе рэалізавана логіка, вельмі падобная на тое, што нам трэба:
Зноў жа, можна не даціскаць сітуацыю да канца, а клікаць далей OK а потым выпраўце парогавыя значэння, якія ўжо ёсць у формуле - гэта хутчэй:
Высновы
Безумоўна, інструмент Слупок з прыкладамі не з'яўляецца «чароўнай таблеткай» і рана ці позна ўзнікнуць нестандартныя сітуацыі або асабліва запушчаныя выпадкі «калгаса» ў дадзеных, калі Power Query дасць збой і не зможа зрабіць тое, што мы хочам. правільна для нас. Аднак у якасці дапаможнага сродкі ён вельмі добры. Акрамя таго, вывучаючы згенераваныя ім формулы, вы можаце пашырыць свае веды аб функцыях мовы М, што заўсёды спатрэбіцца ў будучыні.
- Разбор тэксту з дапамогай рэгулярных выразаў (RegExp) у Power Query
- Пошук недакладнага тэксту ў Power Query
- Флэш-запаўненне ў Microsoft Excel