мазмуну
Проблеманы түзүү
Келгиле, Excel колдонуучуларынын көбү эртеби-кечпи дуушар болгон эң стандарттуу жагдайлардын бири үчүн кооз чечимди карап көрөлү: сиз көп сандагы файлдардан маалыматтарды тез жана автоматтык түрдө бир акыркы таблицага чогултушуңуз керек.
Бизде филиал шаарларынан алынган бир нече файлдарды камтыган төмөнкү папка бар дейли:
Файлдардын саны маанилүү эмес жана келечекте өзгөрүшү мүмкүн. Ар бир файлдын аталышы бар барак бар сатуумаалымат таблицасы кайда жайгашкан:
Таблицалардагы саптардын (заказдардын) саны, албетте, ар кандай, бирок мамычалардын жыйындысы бардык жерде стандарттуу.
Тапшырма: шаардык файлдарды же таблицалардагы саптарды кошуу же жок кылууда кийинки автоматтык түрдө жаңыртуу менен бардык файлдардан маалыматтарды бир китепке чогултуу. Жыйынтыктоочу консолидацияланган таблицага ылайык, андан кийин каалаган отчетторду, пивот таблицаларын, чыпкалоочу маалыматтарды ж.б. түзүүгө болот. Эң негизгиси чогулта билүү.
Биз курал тандайбыз
Чечим үчүн бизге Excel 2016нын эң акыркы версиясы (зарыл функция буга чейин демейки боюнча орнотулган) же Excel 2010-2013тун мурунку версиялары менен акысыз кошумча орнотулган болушу керек. күч суроо Microsoftтон (бул жерден жүктөп алыңыз). Power Query тышкы дүйнөдөн Excelге маалыматтарды жүктөө, андан кийин аны чечип жана иштетүү үчүн өтө ийкемдүү жана супер күчтүү курал. Power Query дээрлик бардык учурдагы маалымат булактарын колдойт - текст файлдарынан SQLге жана ал тургай Facebook 🙂
Эгер сизде Excel 2013 же 2016 жок болсо, андан ары окуй албайсыз (тамаша кылып). Excelдин эски версияларында мындай тапшырманы Visual Basic программасында макросту программалоо (бул үйрөнчүктөр үчүн өтө кыйын) же монотондуу кол көчүрүү (көп убакытты талап кылат жана каталарды жаратат) аркылуу гана аткарууга болот.
1-кадам. Үлгү катары бир файлды импорттоо
Биринчиден, Excel "идеяны кабыл алышы" үчүн, мисал катары бир иш китебинен маалыматтарды импорттойлу. Бул үчүн, жаңы бош китепти түзүп,…
- эгер сизде Excel 2016 болсо, анда өтмөктү ачыңыз маалыматтар жана андан кийин Сурам түзүү - Файлдан - Китептен (Маалымат - Жаңы суроо- Файлдан - Excelден)
- эгер сизде Power Query кошумчасы орнотулган Excel 2010-2013 болсо, анда өтмөктү ачыңыз күч суроо жана аны тандаңыз Файлдан – Китептен (Файлдан — Excelден)
Андан кийин, ачылган терезеде биздин папкага отчёттор бар жана каалаган шаардык файлдарды тандаңыз (кайсысы маанилүү эмес, анткени алардын бардыгы типтүү). Бир нече секунддан кийин Навигатор терезеси пайда болушу керек, анда бизге керектүү баракты тандоо керек (Сатуулар) сол тараптан, жана анын мазмуну оң жагында көрсөтүлөт:
Бул терезенин ылдыйкы оң бурчундагы баскычты бассаңыз Download (Жүктөө), анда таблица дароо баракка баштапкы түрүндө импорттолот. Бир файл үчүн бул жакшы, бирок биз көптөгөн файлдарды жүктөшүбүз керек, андыктан биз бир аз башкачараак барып, баскычты басыңыз түзөө (Түзөтүү). Андан кийин, Power Query суроо редактору китептеги биздин маалыматтар менен өзүнчө терезеде көрсөтүлүшү керек:
Бул бизге керектүү көрүнүшкө үстөлдү "бүтүрүү" мүмкүнчүлүгүн берген абдан күчтүү курал. Ал тургай, анын бардык функцияларын үстүртөн сүрөттөп берүү болжол менен жүз бетти талап кылат, бирок, кыскача айтканда, бул терезени колдонуп, сиз:
- керексиз маалыматтарды, бош сызыктарды, каталары бар саптарды чыпкалоо
- маалыматтарды бир же бир нече тилке боюнча сорттоо
- кайталануудан арылуу
- жабышчаак текстти мамычалар боюнча бөлүү (бөлүүчүлөр, белгилердин саны ж.б. боюнча)
- текстти иретке келтирүү (кошумча боштуктарды алып салуу, регистрди оңдоо ж.б.)
- маалымат түрлөрүн ар кандай жолдор менен айландыруу (текст сыяктуу сандарды кадимки сандарга жана тескерисинче айлантуу)
- таблицаларды которуу (айлантуу) жана эки өлчөмдүү кайчылаш таблицаларды жалпак таблицаларга кеңейтүү
- таблицага кошумча мамычаларды кошуп, алардагы формулаларды жана функцияларды Power Queryге орнотулган M тили аркылуу колдонуңуз.
- ...
Мисалы, келгиле, үстөлүбүзгө айдын тексттик аталышы бар тилкени кошолу, андыктан кийинчерээк пивот таблицасынын отчетторун түзүү оңой болот. Бул үчүн, тилкенин аталышын оң баскыч менен чыкылдатыңыз датажана буйрукту тандаңыз Кайталанма тилке (Кайталанма тилке), анан пайда болгон кайталанма тилкенин башын оң баскыч менен чыкылдатып, Буйруктарды тандаңыз Трансформация – Ай – Ай аты:
Ар бир сап үчүн айдын тексттик аталыштары менен жаңы тилке түзүлүшү керек. Мамычанын аталышын эки жолу чыкылдатуу менен, анын атын өзгөртсөңүз болот Көчүрмө датасы бир кыйла ыңгайлуу аймис.
Эгерде кээ бир тилкелерде программа маалымат түрүн туура тааныбаса, анда ар бир тилкенин сол жагындагы формат белгисин чыкылдатуу менен ага жардам бере аласыз:
Жөнөкөй чыпка аркылуу каталары бар саптарды же бош сызыктарды, ошондой эле керексиз менеджерлерди же кардарларды чыгара аласыз:
Мындан тышкары, бардык аткарылган трансформациялар оң панелде бекитилет, алар ар дайым артка жылдырылышы мүмкүн (кайчылаш) же параметрлерин өзгөртүүгө (тиштүү):
Жарык жана жарашыктуу, туурабы?
2-кадам. Суранычыбызды функцияга айландыралы
Ар бир импорттолгон китеп үчүн жасалган бардык маалыматтарды трансформациялоону кийинчерээк кайталоо үчүн биз түзүлгөн өтүнүчүбүздү функцияга айландырышыбыз керек, ал өз кезегинде бардык файлдарыбызга колдонулат. Бул иш жүзүндө абдан жөнөкөй.
Сурам редакторунда Көрүү өтмөгүнө өтүп, баскычты чыкылдатыңыз Өркүндөтүлгөн редактор (Көрүү — Өркүндөтүлгөн редактор). Мурунку бардык аракеттерибиз M тилиндеги код түрүндө жазыла турган терезе ачылышы керек. Сураныч, биз мисал үчүн импорттогон файлдын жолу коддо катуу коддолгонуна көңүл буруңуз:
Эми бир нече оңдоолорду жасайлы:
Алардын мааниси жөнөкөй: биринчи сап (файл жолу)=> процедурабызды аргумент менен функцияга айлантат файл жолу, жана төмөндө биз бул өзгөрмөнүн маанисине белгиленген жолду өзгөртөбүз.
Баары. чыкылдатыңыз бүтүрүү жана муну көрүшү керек:
Маалыматтар жок болуп кетти деп коркпоңуз – чындыгында баары жакшы, баары ушундай болушу керек 🙂 Биз өз ыңгайлаштырылган функциябызды ийгиликтүү түздүк, мында маалыматтарды импорттоо жана иштетүү алгоритми белгилүү бир файлга байланбастан эсте калат. . Ага түшүнүктүү ат берүү керек (мисалы getData) талаада оң жактагы панелде биринчи аты жана сен оруп аласың Башкы бет — Жабуу жана жүктөп алуу (Башкы бет — Жабуу жана жүктөө). Биз мисал үчүн импорттогон файлдын жолу коддо катуу коддолгонуна көңүл буруңуз. Сиз негизги Microsoft Excel терезесине кайтып келесиз, бирок оң жакта биздин функцияга түзүлгөн байланыш панели пайда болушу керек:
3-кадам. Бардык файлдарды чогултуу
Эң кыйыны артта калды, жагымдуу жана жеңил бөлүгү калды. Өтмөккө өтүңүз Берилиштер - Сурам түзүү - Файлдан - Папкадан (Маалымат — Жаңы суроо — Файлдан — Папкадан) же, эгер сизде Excel 2010-2013 болсо, өтмөккө окшош күч суроо. Пайда болгон терезеде, биздин бардык баштапкы шаар файлдары жайгашкан папканы көрсөтүп, чыкылдатыңыз OK. Кийинки кадам терезени ачуу керек, анда бул папкадагы бардык Excel файлдары (жана анын ички папкалары) жана алардын ар бири боюнча маалыматтар тизмеленет:
чыкылдатуу өзгөртүү (Түзөтүү) жана дагы биз тааныш суроо редакторунун терезесине киребиз.
Эми биз түзүлгөн функциябыз менен таблицага дагы бир мамычаны кошуубуз керек, ал ар бир файлдан маалыматтарды "тартып" алат. Бул үчүн, өтмөккө өтүңүз Мамыча кошуу - Ыңгайлаштырылган тилке (Мамыча кошуу — Ыңгайлаштырылган тилке кошуу) жана пайда болгон терезеде биздин функцияны киргизиңиз getData, ал үчүн аргумент катары ар бир файлдын толук жолун көрсөтүү:
Кийин басуу боюнча OK түзүлгөн мамычаны оң жактагы таблицага кошуу керек.
Эми бардык керексиз мамычаларды жок кылалы (Excelдегидей, чычкандын оң баскычын колдонуу менен – жок кылуу), кошумча тилкени жана файлдын аталышы бар тилкени гана калтырыңыз, анткени бул ат (тагыраак айтканда, шаар) ар бир сап үчүн жалпы маалыматка ээ болуу пайдалуу болот.
Ал эми азыр "wow учур" - биздин функция менен кошулган мамычанын жогорку оң бурчундагы өзүнүн жебелери бар сөлөкөтүн чыкылдатыңыз:
… белгини алып салыңыз Префикс катары түпнуска тилке атын колдонуңуз (Түпнуска тилке атын префикс катары колдонуңуз)жана чыкылдатуу OK. Жана биздин функция ар бир файлдан маалыматтарды жүктөйт жана иштетет, жазылган алгоритм боюнча жана бардыгын жалпы таблицага чогултат:
Толук кооздук үчүн, сиз .xlsx кеңейтүүлөрүн файл аталыштары менен биринчи тилкеден алып салсаңыз болот - стандарттуу "эч нерсе" менен алмаштыруу (тилкенин башын оң баскыч менен чыкылдатыңыз - алмаштыруу) жана бул тилкенин атын өзгөртүңүз сезимдер. Ошондой эле датасы менен тилкедеги маалымат форматын оңдоңуз.
Баары! чыкылдатыңыз Башкы бет – Жабуу жана жүктөө (Башкы бет — Жабуу жана жүктөө). Бардык шаарлар боюнча сурам менен чогултулган бардык маалыматтар учурдагы Excel барагына "акылдуу стол" форматында жүктөлөт:
Түзүлгөн байланышты жана биздин чогултуу функциябызды кандайдыр бир жол менен өзүнчө сактоонун кереги жок – алар учурдагы файл менен бирге кадимки жол менен сакталат.
Келечекте, папкадагы (шаарларды кошуу же алып салуу) же файлдардагы (саптардын санын өзгөртүү) ар кандай өзгөртүүлөр менен таблицага же оң панелдеги суроого чычкандын оң баскычын чыкылдатуу жетиштүү болот. буйрук Жаңыртуу & Сактоо (Жаңылоо) – Power Query бир нече секунданын ичинде бардык маалыматтарды кайрадан “кайра түзөт”.
PS
Түзөтүү. 2017-жылдын январь айындагы жаңыртуулардан кийин Power Query Excel жумушчу китептерин кантип чогултууну үйрөндү, башкача айтканда, мындан ары өзүнчө функция жасоонун кереги жок – ал автоматтык түрдө ишке ашат. Ошентип, бул макаланын экинчи кадамы мындан ары керек эмес жана бүт процесс кыйла жөнөкөй болуп калат:
- тандап алуу Сурам түзүү - Файлдан - Папкадан - Папканы тандоо - OK
- Файлдардын тизмеси пайда болгондон кийин, басыңыз өзгөртүү
- Query Editor терезесинде экилик тилкени кош жебе менен кеңейтиңиз жана ар бир файлдан алынуучу барактын атын тандаңыз
Жана баары ушул! Song!
- Кайчылаш таблицаны жалпак таблицага өзгөртүү
- Power View'де анимацияланган көбүк диаграммасын түзүү
- Ар кандай Excel файлдарынан барактарды бириктирүү үчүн макро