змест
Многія бізнес-працэсы (і нават цэлыя прадпрыемствы) у гэтым жыцці прадугледжваюць выкананне заказаў абмежаванай колькасцю выканаўцаў да зададзенага тэрміну. Планаванне ў такіх выпадках адбываецца, як кажуць, «з календара» і часта ўзнікае неабходнасць перанесці запланаваныя ў ім падзеі (заказы, сустрэчы, пастаўкі) у Microsoft Excel - для далейшага аналізу па формулах, зводных табліцах, пабудове дыяграм, г.д.
Вядома, хацелася б ажыццявіць такі перанос не тупым капіяваннем (што як раз не складана), а з аўтаматычным абнаўленнем дадзеных, каб у далейшым усе змены календара і новыя заказы на ляту адлюстроўваліся ў Excel. Ажыццявіць такі імпарт можна за лічаныя хвіліны з дапамогай надбудовы Power Query, убудаванай у Microsoft Excel, пачынаючы з версіі 2016 (для Excel 2010-2013 яе можна спампаваць з сайта Microsoft і ўсталяваць асобна па спасылцы) .
Выкажам здагадку, што для планавання мы выкарыстоўваем бясплатны Google Calendar, у якім я для зручнасці стварыў асобны каляндар (кнопка з плюсам у правым ніжнім куце побач з Іншыя календары) з назвай Працаваць. Тут мы ўводзім усе заказы, якія неабходна выканаць і даставіць кліентам па іх адрасах:
Двойчы пстрыкнуўшы любы заказ, вы можаце праглядаць або рэдагаваць яго дэталі:
Звярніце ўвагу, што:
- Назва мерапрыемства такое менеджэрхто выконвае гэты загад (Алена) і Нумар заказу
- Пазначана адрас дастаўка
- Заўвага змяшчае (асобнымі радкамі, але ў адвольным парадку) параметры замовы: тып плацяжу, суму, імя кліента і г.д. у фармаце Параметр=Значэнне.
Для нагляднасці распараджэнні кожнага кіраўніка вылучаны сваім колерам, хоць гэта не абавязкова.
Крок 1. Атрымайце спасылку на Google Calendar
Спачатку нам трэба атрымаць вэб-спасылку на наш каляндар заказаў. Для гэтага націсніце на кнопку з трыма кропкамі Параметры календара працуюць побач з назвай календара і абярыце каманду Налады і агульны доступ:
У якое адкрылася акне можна пры жаданні зрабіць каляндар агульнадаступным або адкрыць доступ да яго асобным карыстальнікам. Нам таксама патрэбна спасылка для прыватнага доступу да календара ў фармаце iCal:
Крок 2. Загрузіце даныя з календара ў Power Query
Цяпер адкрыйце Excel і на ўкладцы Дата (калі ў вас Excel 2010-2013, то на ўкладцы Запыт на харчаванне) выбраць каманду З Інтэрнэту (Дадзеныя — з Інтэрнэту). Затым устаўце скапіраваны шлях у каляндар і націсніце OK.
Праграма iCal Power Query не распазнае фармат, але з ёй лёгка дапамагчы. Па сутнасці, iCal - гэта звычайны тэкставы файл з двукроп'ем у якасці раздзяляльніка, а ўнутры ён выглядае прыкладна так:
Такім чынам, вы можаце проста клікнуць правай кнопкай мышы на абразку загружанага файла і выбраць найбольш блізкі па значэнні фармат CSV – і нашы дадзеныя аб усіх заказах будуць загружаны ў рэдактар запытаў Power Query і падзелены на два слупкі двукроп'ем:
Калі прыгледзецца, то выразна відаць, што:
- Інфармацыя аб кожнай падзеі (замове) згрупавана ў блок, які пачынаецца са слова ПАЧАТАК і заканчваецца КАНЕЦАМ.
- Даты пачатку і заканчэння захоўваюцца ў радках з пазнакамі DTSTART і DTEND.
- Адрас дастаўкі LOCATION.
- Заўвага да замовы – поле АПІСАННЕ.
- Назва падзеі (імя кіраўніка і нумар заказу) — поле РЕЗЮМЕ.
Засталося здабыць гэтую карысную інфармацыю і трансфармаваць яе ў зручную табліцу.
Крок 3. Пераўтварэнне ў звычайны выгляд
Для гэтага выканайце наступны ланцужок дзеянняў:
- Давайце выдалім 7 верхніх радкоў, якія нам не патрэбныя, перад першай камандай BEGIN Галоўная — Выдаліць радкі — Выдаліць верхнія радкі (Галоўная — Выдаліць радкі — Выдаліць верхнія радкі).
- Фільтр па слупку Column1 радкі, якія змяшчаюць патрэбныя нам палі: DTSTART, DTEND, DESCRIPTION, LOCATION і SUMMARY.
- На ўкладцы Дадаткова Даданне слупка выбіраць Слупок індэкс (Дадаць слупок — слупок індэкс)каб дадаць слупок нумар радка да нашых дадзеных.
- Тут жа, на ўкладцы. Даданне слупка выбраць каманду Умоўны слупок (Дадаць слупок — Умоўны слупок) і ў пачатку кожнага блока (ордэра) адлюстроўваем значэнне індэкса:
- Запоўніце пустыя клеткі атрыманага слупка Блакавацьпстрыкнуўшы правай кнопкай мышы на яго назве і выбраўшы каманду Запаўненне - Уніз (Запоўніць — Уніз).
- Выдаліце непатрэбны слупок індэкс.
- Выберыце слупок Column1 і выканаць згортку дадзеных са слупка Column2 выкарыстоўваючы каманду Пераўтварэнне – зводны слупок (Пераўтварэнне — зводны слупок). Абавязкова выберыце ў параметрах Не аб'ядноўвайце (Не аб'ядноўваць)каб да дадзеных не прымянялася матэматычная функцыя:
- У атрыманай двухмернай (крыжаванай) табліцы ачысціце зваротную касую рысу ў слупку адраса (пстрыкніце правай кнопкай мышы на загалоўку слупка – Замена значэнняў) і выдаліце непатрэбны слупок Блакаваць.
- Каб павярнуць змесціва слупкоў DTSTART и DTEND у поўнай даце-часе, вылучыўшы іх, абярыце на ўкладцы Пераўтварэнне – Дата – Выкананне аналізу (Пераўтварэнне — Дата — Разбор). Затым мы выпраўляем код у радку формул, замяняючы функцыю Дата.З on ДатаЧас.Зкаб не страціць значэння часу:
- Затым, пстрыкнуўшы правай кнопкай мышы на загалоўку, мы падзяляем слупок АПІСАННЕ з параметрамі парадку па падзельніку – сімвал n, але пры гэтым у параметрах абярэм падзел на радкі, а не на слупкі:
- Яшчэ раз дзелім атрыманы слупок на два асобных - параметр і значэнне, але ўжо знакам роўнасці.
- Выбар слупка АПІСАННЕ.1 выканайце згортванне, як мы рабілі раней, з дапамогай каманды Пераўтварэнне – зводны слупок (Пераўтварэнне — зводны слупок). Слупок значэнняў у дадзеным выпадку будзе слупком са значэннямі параметраў − АПІСАННЕ.2 Абавязкова абярыце функцыю ў параметрах Не аб'ядноўвайце (Не аб'ядноўваць):
- Засталося задаць фарматы для ўсіх слупкоў і перайменаваць іх па жаданні. І вы можаце загрузіць вынікі назад у Excel з дапамогай каманды Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у…)
А вось наш спіс заказаў, загружаных у Excel з календара Google:
У далейшым, пры змене або даданні новых заказаў у каляндар, дастаткова будзе толькі абнавіць наш запыт камандай Дадзеныя – абнавіць усе (Дадзеныя — Абнавіць усе).
- Завадскі каляндар у Excel абнаўляецца з Інтэрнэту праз Power Query
- Пераўтварэнне слупка ў табліцу
- Стварэнне базы дадзеных у Excel