Съдържание
Много бизнес процеси (и дори цели бизнеси) в този живот включват изпълнение на поръчки от ограничен брой изпълнители до даден краен срок. Планирането в такива случаи става, както се казва, „от календара“ и често има нужда от прехвърляне на планираните в него събития (поръчки, срещи, доставки) в Microsoft Excel - за по-нататъшен анализ по формули, обобщени таблици, диаграми, и т.н.
Разбира се, бих искал да осъществя такова прехвърляне не чрез глупаво копиране (което просто не е трудно), а с автоматично актуализиране на данните, така че в бъдеще всички промени, направени в календара, и новите поръчки в движение да се показват в Excel. Можете да осъществите такова импортиране за няколко минути с помощта на добавката Power Query, вградена в Microsoft Excel, като се започне от версията 2016 (за Excel 2010-2013 може да бъде изтеглена от уебсайта на Microsoft и инсталирана отделно от връзката) .
Да предположим, че използваме безплатния календар на Google за планиране, в който за удобство създадох отделен календар (бутона със знак плюс в долния десен ъгъл до Други календари) със заглавието Работа. Тук въвеждаме всички поръчки, които трябва да бъдат изпълнени и доставени до клиентите на техните адреси:
Като щракнете двукратно върху всяка поръчка, можете да видите или редактирате нейните подробности:
Отбележи, че:
- Името на събитието е мениджъркойто изпълнява тази поръчка (Елена) и Номер на поръчка
- посочен адрес доставка
- Бележката съдържа (на отделни редове, но в произволен ред) параметрите на поръчката: вид плащане, сума, име на клиент и др. във формат Параметър=Стойност.
За по-голяма яснота нарежданията на всеки мениджър са осветени със собствен цвят, въпреки че това не е необходимо.
Стъпка 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 Calendar:
В бъдеще, когато променяме или добавяме нови поръчки към календара, ще бъде достатъчно само да актуализираме нашата заявка с командата Данни – обновяване на всички (Данни — Обновяване на всички).
- Фабричен календар в Excel, актуализиран от интернет чрез Power Query
- Трансформиране на колона в таблица
- Създайте база данни в Excel