Power Query көмегімен әртүрлі Excel файлдарынан кестелерді құрастыру

Мәселені тұжырымдау

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 бағдарламасынан)

Содан кейін ашылған терезеде есептер бар қалтаға өтіп, қала файлдарының кез келгенін таңдаңыз (қайсысы маңызды емес, өйткені олардың барлығы тән). Бірнеше секундтан кейін Навигатор терезесі пайда болуы керек, сол жақта бізге қажет парақты таңдау керек (Сатулар) және оның мазмұны оң жақта көрсетіледі:

Осы терезенің төменгі оң жақ бұрышындағы түймені бассаңыз жүктеу (Жүктеу), содан кейін кесте бастапқы пішінінде дереу параққа импортталады. Бір файл үшін бұл жақсы, бірақ біз осындай көптеген файлдарды жүктеуіміз керек, сондықтан біз сәл басқаша жүріп, түймені басамыз. Түзету (Өңдеу). Осыдан кейін Power Query сұрау өңдегіші кітаптағы деректерімізбен бөлек терезеде көрсетілуі керек:

Бұл кестені бізге қажетті көрініске дейін «аяқтауға» мүмкіндік беретін өте қуатты құрал. Тіпті оның барлық функцияларын үстірт сипаттау шамамен жүз бетті алады, бірақ, егер өте қысқа болса, осы терезені пайдалану арқылы сіз:

• қажет емес деректерді, бос жолдарды, қателері бар жолдарды сүзіңіз
• деректерді бір немесе бірнеше бағандар бойынша сұрыптау
• қайталаудан арылыңыз
• жабысқақ мәтінді бағандар бойынша бөлу (бөлгіштер, таңбалар саны және т.б.)
• мәтінді ретке келтіру (қосымша бос орындарды алып тастау, регистрді түзету, т.б.)
• деректер түрлерін барлық мүмкін түрде түрлендіру (мәтін сияқты сандарды қалыпты сандарға және керісінше айналдыру)
• кестелерді ауыстыру (бұру) және екі өлшемді айқас кестелерді жалпақ кестелерге кеңейту
• кестеге қосымша бағандарды қосыңыз және олардағы формулалар мен функцияларды Power Query ішіне енгізілген M тілін пайдаланып пайдаланыңыз.
• ...

Мысалы, кейінірек жиынтық кесте есептерін құрастыру оңай болуы үшін кестемізге айдың мәтіндік атауы бар бағанды ​​қосамыз. Мұны істеу үшін баған тақырыбын тінтуірдің оң жақ түймешігімен басыңыз датажәне пәрменді таңдаңыз Көшірме баған (Көшірме баған), содан кейін пайда болатын қайталанатын бағанның тақырыбын тінтуірдің оң жақ түймешігімен басып, Пәрмендер тармағын таңдаңыз. Трансформация – Ай – Ай атауы:

Әрбір жол үшін айдың мәтіндік атаулары бар жаңа баған құрылуы керек. Баған тақырыбын екі рет басу арқылы оның атын өзгертуге болады Көшіру күні ыңғайлырақ аймысалы.

Егер кейбір бағандарда бағдарлама деректер түрін дұрыс танымаса, оған әр бағанның сол жағындағы пішім белгішесін басу арқылы көмектесе аласыз:

Қарапайым сүзгіні пайдаланып қателері бар жолдарды немесе бос жолдарды, сондай-ақ қажет емес менеджерлерді немесе тұтынушыларды алып тастай аласыз:

Сонымен қатар, барлық орындалған түрлендірулер оң жақ панельде бекітілген, онда оларды әрқашан артқа айналдыруға (айқастыруға) немесе параметрлерін өзгертуге (беріліс) болады:

Жеңіл және талғампаз, солай емес пе?

2-қадам. Сұранысты функцияға түрлендіру

Әрбір импортталған кітап үшін жасалған барлық деректер түрлендірулерін кейіннен қайталау үшін біз жасалған сұрауымызды функцияға түрлендіруіміз керек, ол кейін өз кезегінде барлық файлдарымызға қолданылады. Мұны істеу шын мәнінде өте қарапайым.

Сұрау өңдегішінде Көрініс қойындысына өтіп, түймені басыңыз Жетілдірілген редактор (Қарау — Кеңейтілген редактор). Біздің барлық алдыңғы әрекеттеріміз M тіліндегі код түрінде жазылатын терезе ашылуы керек. Мысал үшін импортталған файлға жол кодта қатты кодталғанын ескеріңіз:

Енді бірнеше түзетулер енгізейік:

Олардың мағынасы қарапайым: бірінші жол (файл жолы)=> процедурамызды аргументі бар функцияға айналдырады файл жолы, ал төменде біз осы айнымалының мәніне бекітілген жолды өзгертеміз. 

Барлық. Басыңыз Аяқтау және мынаны көру керек:

Деректер жоғалып кетті деп қорықпаңыз – шын мәнінде бәрі жақсы, бәрі осылай көрінуі керек 🙂 Біз өзіміздің теңшелетін функциямызды сәтті жасадық, мұнда деректерді импорттау және өңдеудің барлық алгоритмі белгілі бір файлға байланбай есте сақталады. . Оған түсінікті атау беру қалады (мысалы деректер алу) өрістің оң жағындағы панельде аты және сіз орып аласыз Негізгі бет — Жабу және жүктеп алу (Үйге — Жабу және жүктеу). Мысал үшін біз импорттаған файлдың жолы кодта қатты кодталғанын ескеріңіз. Сіз Microsoft Excel бағдарламасының негізгі терезесіне ораласыз, бірақ оң жақта біздің функцияға жасалған қосылымы бар панель пайда болуы керек:

3-қадам. Барлық файлдарды жинау

Ең қиынның бәрі артта қалды, жағымды және оңай бөлігі қалады. Қойындыға өтіңіз Деректер – Сұраныс жасау – Файлдан – Қалтадан (Деректер — Жаңа сұрау — Файлдан — Қалтадан) немесе, егер сізде Excel 2010-2013 болса, қойындыға ұқсас Қуат сұрауы. Пайда болған терезеде біздің барлық бастапқы қалалық файлдар орналасқан қалтаны көрсетіңіз және басыңыз OK. Келесі қадамда осы қалтада (және оның ішкі қалталарында) табылған барлық Excel файлдары және олардың әрқайсысына арналған мәліметтер тізімделген терезе ашылуы керек:

басыңыз өзгеріс (Өңдеу) және қайтадан таныс сұраныс редакторының терезесіне кіреміз.

Енді біз кестеге біздің құрылған функциямызбен басқа бағанды ​​қосуымыз керек, ол әрбір файлдан деректерді «тартып алады». Мұны істеу үшін қойындыға өтіңіз Баған қосу – теңшелетін баған (Баған қосу — реттелетін баған қосу) және пайда болған терезеде біздің функцияны енгізіңіз деректер алу, ол үшін әр файлдың толық жолын дәлел ретінде көрсету:

Басқаннан кейін OK құрылған баған оң жақтағы кестемізге қосылуы керек.

Енді барлық қажет емес бағандарды жойайық (Excel-дегі сияқты, тінтуірдің оң жақ түймешігі арқылы – кетіру), тек қосылған бағанды ​​және файл атауы бар бағанды ​​қалдырыңыз, себебі бұл атау (дәлірек айтқанда, қала) әрбір жолдың жалпы деректерінде болуы пайдалы болады.

Ал енді «уау сәт» – функциямыз бар қосылған бағанның жоғарғы оң жақ бұрышындағы өз көрсеткілері бар белгішені басыңыз:

… құсбелгіні алып тастаңыз Бастапқы баған атауын префикс ретінде пайдаланыңыз (Бағананың бастапқы атауын префикс ретінде пайдаланыңыз)және нұқыңыз OK. Біздің функция жазылған алгоритм бойынша және бәрін ортақ кестеде жинай отырып, әрбір файлдағы деректерді жүктейді және өңдейді:

Толық сұлулық үшін файл атаулары бар бірінші бағаннан .xlsx кеңейтімдерін жоюға болады – стандартты «ештеңе» дегенге ауыстыру арқылы (баған тақырыбын тінтуірдің оң жақ түймешігімен басыңыз – Ауыстыру) және осы бағанның атын өзгерту сезім. Сондай-ақ күні көрсетілген бағандағы деректер пішімін түзетіңіз.

Барлық! Басыңыз Басты – Жабу және жүктеңіз (Үйге — Жабу және жүктеу). Барлық қалалар бойынша сұрау арқылы жиналған барлық деректер ағымдағы Excel парағына «ақылды кесте» пішімінде жүктеледі:

Жасалған қосылымды және біздің құрастыру функциямызды кез келген жолмен бөлек сақтаудың қажеті жоқ – олар ағымдағы файлмен бірге әдеттегідей сақталады.

Болашақта қалтадағы (қалаларды қосу немесе жою) немесе файлдардағы (жолдар санын өзгерту) кез келген өзгерістермен кестені немесе оң жақ панельдегі сұрауды тінтуірдің оң жақ түймешігімен нұқу жеткілікті болады. пәрмен Жаңарту және сақтау (Жаңарту) – Power Query бірнеше секунд ішінде барлық деректерді қайта «қайта жасайды».

PS

Түзету. 2017 жылғы қаңтардағы жаңартулардан кейін Power Query Excel жұмыс кітаптарын өздігінен жинауды үйренді, яғни енді бөлек функция жасаудың қажеті жоқ – ол автоматты түрде орындалады. Осылайша, осы мақаланың екінші қадамы қажет емес және бүкіл процесс айтарлықтай қарапайым болады:

1. таңдау Сұраныс жасау – Файлдан – Қалтадан – Қалта таңдау – ОК
2. Файлдар тізімі пайда болғаннан кейін түймесін басыңыз өзгеріс
3. Сұрау өңдегіші терезесінде екілік бағанды ​​қос көрсеткімен кеңейтіп, әр файлдан алынатын парақ атауын таңдаңыз.

Және бұл бәрі! Өлең!

• Айқас кестені жиынтық кестелерді құруға жарамды тегіс етіп қайта жасау
• Power View бағдарламасында анимациялық көпіршік диаграммасын құру
• Әртүрлі Excel файлдарындағы парақтарды бір файлға жинау үшін макрос