змест
Пастаноўка задачы
Давайце паглядзім на прыгожае рашэнне для адной з вельмі стандартных сітуацый, з якімі рана ці позна сутыкаецца большасць карыстальнікаў 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 павінен адкрыцца ў асобным акне з нашымі дадзенымі з кнігі:
Гэта вельмі магутны інструмент, які дазваляе «давесці» табліцу да патрэбнага выгляду. Нават павярхоўнае апісанне ўсіх яго функцый заняло б каля сотні старонак, але, калі вельмі коратка, з дапамогай гэтага акна можна:
- адфільтраваць непатрэбныя дадзеныя, пустыя радкі, радкі з памылкамі
- сартаваць даныя па адным або некалькіх слупках
- пазбавіцца ад паўтарэння
- падзяліць ліпкі тэкст па слупках (па раздзяляльніках, колькасці знакаў і г.д.)
- упарадкаваць тэкст (выдаліць лішнія прабелы, выправіць рэгістр і г.д.)
- пераўтвараць тыпы дадзеных усімі магчымымі спосабамі (ператвараць лічбы, як тэкст, у звычайныя лічбы і наадварот)
- транспанаваць (паварочваць) табліцы і разгортваць двухмерныя крыжаваныя табліцы ў плоскія
- дадаваць дадатковыя слупкі ў табліцу і выкарыстоўваць у іх формулы і функцыі з дапамогай мовы M, убудаванай у Power Query.
- ...
Давайце, напрыклад, дадамо ў нашу табліцу слупок з тэкставай назвай месяца, каб потым было лягчэй будаваць зводныя справаздачы. Для гэтага пстрыкніце правай кнопкай мышы на загалоўку слупка датаі абярыце каманду Дублікат слупка (Дублікат слупка), а затым пстрыкніце правай кнопкай мышы на загалоўку слупка-дубліката, які з'явіцца, і выберыце «Каманды». Пераўтварэнне – Месяц – Назва месяца:
Для кожнага радка павінен быць створаны новы слупок з тэкставымі назвамі месяцаў. Двойчы пстрыкнуўшы па загалоўку слупка, вы можаце перайменаваць яго з Капіяваць дату да больш зручнага месяц, напр.
Калі ў некаторых слупках праграма не зусім карэктна распазнала тып дадзеных, то вы можаце дапамагчы ёй, націснуўшы на значок фармату ў левай частцы кожнага слупка:
Вы можаце выключыць радкі з памылкамі або пустыя радкі, а таксама непатрэбных кіраўнікоў або кліентаў з дапамогай простага фільтра:
Больш за тое, усе выкананыя пераўтварэнні фіксуюцца ў правай панэлі, дзе іх заўсёды можна адкаціць назад (крос) або змяніць іх параметры (шасцярэнька):
Лёгкі і элегантны, ці не праўда?
Крок 2. Давайце ператворым наш запыт у функцыю
Каб пасля паўтарыць усе пераўтварэнні даных, зробленыя для кожнай імпартаванай кнігі, нам трэба пераўтварыць створаны намі запыт у функцыю, якая затым будзе прымяняцца, у сваю чаргу, да ўсіх нашых файлаў. Зрабіць гэта на самай справе вельмі проста.
У рэдактары запытаў перайдзіце на ўкладку «Выгляд» і націсніце кнопку Пашыраны рэдактар (Прагляд — пашыраны рэдактар). Павінна адкрыцца акно, дзе ў выглядзе кода на мове M будуць запісаны ўсе нашы папярэднія дзеянні. Калі ласка, звярніце ўвагу, што шлях да файла, які мы імпартавалі для прыкладу, жорстка зададзены ў кодзе:
Зараз давайце зробім пару карэкціровак:
Іх сэнс просты: першы радок (шлях да файла)=> ператварае нашу працэдуру ў функцыю з аргументам шлях да файла, а ніжэй мы змяняем фіксаваны шлях да значэння гэтай зменнай.
Усе. Націсніце на Заканчэнне і павінен бачыць гэта:
Не бойцеся, што дадзеныя зніклі – насамрэч, усё ў парадку, усё павінна выглядаць так 🙂 Мы паспяхова стварылі сваю карыстальніцкую функцыю, дзе ўвесь алгарытм імпарту і апрацоўкі дадзеных запамінаецца без прывязкі да пэўнага файла . Засталося даць яму больш зразумелую назву (напрыклад getData) на панэлі справа ў полі FirstName і можна жаць Галоўная — зачыніць і спампаваць (Галоўная — Зачыніць і загрузіць). Калі ласка, звярніце ўвагу, што шлях да файла, які мы імпартавалі для прыкладу, жорстка зададзены ў кодзе. Вы вернецеся ў галоўнае акно Microsoft Excel, але справа павінна з'явіцца панэль са створаным злучэннем з нашай функцыяй:
Крок 3. Збор усіх файлаў
Усё самае складанае ззаду, засталося прыемнае і лёгкае. Перайдзіце на ўкладку Дадзеныя – Стварыць запыт – З файла – З папкі (Дадзеныя — Новы запыт — З файла — З папкі) або, калі ў вас Excel 2010-2013, аналагічна ўкладцы Запыт на харчаванне. У якое з'явілася акне пакажыце тэчку, у якой знаходзяцца ўсе нашы зыходныя гарадскія файлы, і націсніце OK. На наступным этапе павінна адкрыцца акно, у якім будуць пералічаны ўсе файлы Excel, знойдзеныя ў гэтай тэчцы (і яе падпапках), а таксама дэталі кожнага з іх:
націсніце Мяняць (Рэдагаваць) і зноў трапляем у знаёмае акно рэдактара запытаў.
Цяпер нам трэба дадаць у нашу табліцу яшчэ адзін слупок з створанай намі функцыяй, якая будзе «выцягваць» дадзеныя з кожнага файла. Для гэтага перайдзіце на ўкладку Дадаць слупок – Карыстальніцкі слупок (Дадаць слупок — Дадаць карыстальніцкі слупок) і ў якое з'явілася акне увядзіце нашу функцыю getData, указаўшы для яго ў якасці аргументу поўны шлях да кожнага файла:
Пасля націску на кнопку OK створаны слупок трэба дадаць у нашу табліцу справа.
Цяпер выдалім усе непатрэбныя слупкі (як у Excel, з дапамогай правай кнопкі мышы – Выдаленне), пакідаючы толькі дададзены слупок і слупок з назвай файла, таму што гэтую назву (дакладней, горад) будзе карысна мець у агульных дадзеных для кожнага радка.
А цяпер «вау момант» - пстрыкніце па значку з уласнымі стрэлачкамі ў правым верхнім куце дабаўленай калонкі з нашай функцыяй:
… зніміце галачку Выкарыстоўвайце зыходнае імя слупка ў якасці прэфікса (Выкарыстоўваць зыходную назву слупка ў якасці прэфікса)і націсніце OK. І наша функцыя будзе загружаць і апрацоўваць дадзеныя з кожнага файла, прытрымліваючыся запісанага алгарытму і збіраючы ўсё ў агульную табліцу:
Для поўнай прыгажосці вы таксама можаце выдаліць пашырэнні .xlsx з першага слупка з назвамі файлаў - стандартнай заменай на "нічога" (пстрыкніце правай кнопкай мышы на загалоўку слупка - сурагат) і перайменаваць гэты слупок у Горад. А таксама выправіць фармат дадзеных у слупку з датай.
Усе! Націсніце на Галоўная - Зачыніць і загрузіць (Галоўная — Зачыніць і загрузіць). Усе дадзеныя, сабраныя запытам для ўсіх гарадоў, будуць загружаныя ў бягучую табліцу Excel у фармаце «разумная табліца»:
Створанае злучэнне і нашу функцыю зборкі ніякім чынам не трэба захоўваць асобна - яны захоўваюцца разам з бягучым файлам звычайным спосабам.
У далейшым пры любых зменах у тэчцы (даданне або выдаленне гарадоў) або ў файлах (змена колькасці радкоў) дастаткова будзе пстрыкнуць правай кнопкай мышы непасрэдна па табліцы або па запыце ў правай панэлі і выбраць загадваць Абнавіць і захаваць (Абнавіць) – Power Query «перабудуе» ўсе даныя зноўку праз некалькі секунд.
PS
Папраўка. Пасля абнаўленняў у студзені 2017 года Power Query навучыўся збіраць кнігі Excel самастойна, г.зн. больш не трэба ствараць асобную функцыю - гэта адбываецца аўтаматычна. Такім чынам, другі крок з гэтага артыкула больш не патрэбны, і ўвесь працэс прыкметна спрашчаецца:
- Выбіраць Стварыць запыт – З файла – З тэчкі – Выбраць тэчку – ОК
- Пасля з'яўлення спісу файлаў націсніце Мяняць
- У акне рэдактара запытаў разгарніце двайковы слупок двайной стрэлкай і выберыце імя аркуша, якое будзе ўзята з кожнага файла
І гэта ўсё! Песня!
- Рэдызайн крыжаванай табліцы ў плоскую, прыдатную для стварэння зводных табліц
- Стварэнне аніміраванай пузырчатой дыяграмы ў Power View
- Макрас для зборкі лістоў з розных файлаў Excel у адзін