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