мазмуну
Менин 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 бир топ функцияларды колдонот Текст.Жогорку и Текст.Төмөнкү, текстти тиешелүүлүгүнө жараша чоң жана кичине тамгага айландыруу жана функциялары Text.Start и Текст.Орто – Excelдин LEFT жана PSTR функцияларынын аналогдору, тексттен солдон жана ортодон субсапты чыгара алат.
Мисал 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 иштебей калып, биз каалаган нерсени ишке ашыра албай калганда, стандарттуу эмес жагдайлар же маалыматтарда өзгөчө көңүл бурулбаган "колхоз" учурлары болот. биз үчүн туура. Бирок, жардамчы курал катары, бул абдан жакшы. Мындан тышкары, ал түзгөн формулаларды изилдөө менен, келечекте ар дайым пайдалуу боло турган М тилинин функциялары жөнүндө билимиңизди кеңейте аласыз.
- Power Queryде кадимки туюнтмалар (RegExp) менен текстти талдоо
- Power Query'де бүдөмүк текст издөө
- Microsoft Excelде Flash толтуруу