содржина
Формулирање на проблемот
Ајде да погледнеме убаво решение за една од многу стандардните ситуации со кои се соочуваат повеќето корисници на 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)
- ако имате Excel 2010-2013 со инсталиран додаток Power Query, тогаш отворете ја картичката Барање за напојување и изберете на неа Од датотека – Од книга (Од датотека - од Excel)
Потоа, во прозорецот што се отвора, одете во нашата папка со извештаи и изберете која било од градските датотеки (не е важно која, бидејќи сите се типични). По неколку секунди, треба да се појави прозорецот Навигатор, каде што треба да го изберете листот што ни треба (Продажба) на левата страна, а неговата содржина ќе се прикаже на десната страна:
Ако кликнете на копчето во долниот десен агол на овој прозорец превземи (Вчитај), тогаш табелата веднаш ќе се увезе во листот во неговата оригинална форма. За една датотека, ова е добро, но треба да вчитаме многу такви датотеки, па ќе одиме малку поинаку и ќе кликнете на копчето Корекција (Уреди). После тоа, уредникот за барање Power Query треба да се прикаже во посебен прозорец со нашите податоци од книгата:
Ова е многу моќна алатка која ви овозможува да ја „завршите“ табелата до погледот што ни треба. Дури и површен опис на сите негови функции би траел околу сто страници, но, ако е многу кратко, користејќи го овој прозорец можете:
- филтрирајте непотребни податоци, празни линии, линии со грешки
- подредете ги податоците по една или повеќе колони
- ослободете се од повторувањето
- поделете леплив текст по колони (по разграничувачи, број на знаци, итн.)
- поставете го текстот во ред (отстранете ги дополнителните празни места, точни букви итн.)
- конвертирај типови на податоци на секој можен начин (претвори ги броевите како текстот во нормални броеви и обратно)
- транспонирајте (ротирајте) табели и проширете ги дводимензионалните вкрстени табели во рамни
- додадете дополнителни колони на табелата и користете формули и функции во нив користејќи го јазикот М вграден во Power Query.
- ...
На пример, да додадеме колона со текстуалното име на месецот во нашата табела, така што подоцна ќе биде полесно да се градат извештаи за стожерната табела. За да го направите ова, кликнете со десното копче на насловот на колоната датуми изберете ја командата Дупликат колона (Дупликат колона), а потоа кликнете со десното копче на заглавието на дупликатната колона што се појавува и изберете Commands Трансформација – Име на месец – месец:
Треба да се формира нова колона со текстуалните имиња на месецот за секој ред. Со двоен клик на насловот на колоната, можете да го преименувате од Датум на копирање до поудобно месец, на пр.
Ако во некои колони програмата не го препознала сосема правилно типот на податоци, тогаш можете да и помогнете со кликнување на иконата за формат на левата страна на секоја колона:
Можете да исклучите линии со грешки или празни линии, како и непотребни менаџери или клиенти, користејќи едноставен филтер:
Покрај тоа, сите извршени трансформации се фиксирани во десниот панел, каде што секогаш можат да се вратат назад (вкрстени) или да ги променат нивните параметри (запчаник):
Лесни и елегантни, нели?
Чекор 2. Да го трансформираме нашето барање во функција
Со цел последователно да ги повториме сите трансформации на податоци направени за секоја увезена книга, треба да го претвориме нашето креирано барање во функција, која потоа ќе се примени на сите наши датотеки. Да се направи ова е всушност многу едноставно.
Во Уредувачот на барање, одете во табулаторот View и кликнете на копчето Напреден уредник (Преглед - Напреден уредник). Треба да се отвори прозорец каде што сите наши претходни дејства ќе бидат напишани во форма на код на јазикот М. Ве молиме имајте предвид дека патеката до датотеката што ја увезовме за примерот е тврдокодирана во кодот:
Сега да направиме неколку прилагодувања:
Нивното значење е едноставно: првата линија (патека на датотека)=> ја претвора нашата постапка во функција со аргумент патека на датотеката, а подолу ја менуваме фиксната патека до вредноста на оваа променлива.
Сите. Кликнете на Заврши и треба да се види ова:
Не плашете се дека податоците исчезнаа – всушност, сè е во ред, сè треба да изгледа вака 🙂 Успешно ја создадовме нашата сопствена функција, каде што целиот алгоритам за увоз и обработка на податоци се памети без да биде врзан за одредена датотека . Останува да му се даде поразбирливо име (на пример добие податоци) во панелот од десната страна во полето Firstname и можеш да жнееш Дома — Затворете и преземете (Дома - Затвори и вчитај). Ве молиме имајте предвид дека патеката до датотеката што ја увезовме за примерот е тврдокодирана во кодот. Ќе се вратите во главниот прозорец на Microsoft Excel, но на десната страна треба да се појави панел со креираната врска со нашата функција:
Чекор 3. Собирање на сите датотеки
Сè најтешкиот дел е зад себе, пријатниот и лесен дел останува. Одете на јазичето Податоци – Креирајте барање – Од датотека – Од папка (Податоци - Ново барање - Од датотека - Од папка) или, ако имате Excel 2010-2013, слично на табот Барање за напојување. Во прозорецот што се појавува, наведете ја папката каде што се наоѓаат сите наши датотеки од изворниот град и кликнете OK. Следниот чекор треба да отвори прозорец каде што ќе бидат наведени сите датотеки на Excel пронајдени во оваа папка (и нејзините подпапки) и детали за секоја од нив:
Кликнете Промени (Уреди) и повторно влегуваме во познатиот прозорец за уредувач на прашања.
Сега треба да додадеме уште една колона во нашата табела со нашата креирана функција, која ќе ги „повлече“ податоците од секоја датотека. За да го направите ова, одете на јазичето Додај колона - Прилагодена колона (Додај колона - Додај приспособена колона) и во прозорецот што се појавува внесете ја нашата функција добие податоци, наведувајќи за него како аргумент целосната патека до секоја датотека:
По кликнување на OK креираната колона треба да се додаде на нашата табела десно.
Сега да ги избришеме сите непотребни колони (како во Excel, користејќи го десното копче на глувчето - Отстрани), оставајќи ги само додадената колона и колоната со името на датотеката, бидејќи ова име (поточно градот) ќе биде корисно да се има во вкупните податоци за секој ред.
И сега „wow moment“ – кликнете на иконата со сопствените стрелки во горниот десен агол на додадената колона со нашата функција:
… одштиклирајте Користете го оригиналното име на колоната како префикс (Користете го оригиналното име на колоната како префикс)и кликнете на OK. И нашата функција ќе ги вчита и обработува податоците од секоја датотека, следејќи го снимениот алгоритам и собирајќи сè во заедничка табела:
За целосна убавина, можете исто така да ги отстраните наставките .xlsx од првата колона со имиња на датотеки - со стандардна замена со „ништо“ (десен клик на заглавието на колоната - Заменик) и преименувајте ја оваа колона во Град. И, исто така, поправете го форматот на податоци во колоната со датумот.
Сите! Кликнете на Дома – Затвори и вчитај (Дома - Затвори и вчитај). Сите податоци собрани со барањето за сите градови ќе бидат поставени на тековниот лист на Excel во формат „паметна табела“:
Создадената врска и нашата функција за склопување не треба на кој било начин да се зачувуваат одделно - тие се зачувуваат заедно со тековната датотека на вообичаен начин.
Во иднина, со какви било промени во папката (додавање или отстранување градови) или во датотеки (промена на бројот на линии), ќе биде доволно да кликнете со десното копче директно на табелата или на барањето во десниот панел и изберете команда Ажурирајте и зачувајте (Освежи) – Power Query повторно ќе ги „изгради“ сите податоци за неколку секунди.
PS
Амандман. По ажурирањата во јануари 2017 година, Power Query научи како сама да ги собира работните книги на Excel, односно веќе нема потреба да прави посебна функција – тоа се случува автоматски. Така, вториот чекор од овој напис повеќе не е потребен и целиот процес станува значително поедноставен:
- Избери Креирај барање - Од датотека - Од папка - Изберете папка - ОК
- Откако ќе се појави списокот со датотеки, притиснете Промени
- Во прозорецот Уредувач на прашања, проширете ја колоната Бинарна со двојна стрелка и изберете го името на листот што треба да се земе од секоја датотека
И тоа е се! Песна!
- Редизајнирање на вкрстеното јазиче во рамно погодно за градење стожерни маси
- Изградба на анимирана табела со меурчиња во Power View
- Макро за собирање листови од различни датотеки на Excel во едно