Система учета заказов на Google Calendar и Excel
Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.
Само-собой, хотелось бы реализовать такой перенос не тупым копированием (что как раз несложно), а с автоматическим обновлением данных, чтобы в будущем всё внесенные в календарь изменения и новые заказы "на лету" отображались бы в Excel. Реализовать такой импорт можно за считанные минуты с помощью надстройки Power Query, встроенной в Microsoft Excel, начиная с 2016-й версии (для Excel 2010-2013 её можно скачать с сайта Microsoft и установить отдельно по ссылке).
Предположим, что мы используем для планирования бесплатный Google Calendar, в котором я, для удобства, создал отдельный календарь (кнопка со знаком "плюс" в правом нижнем углу рядом с Другие календари) с названием Работа. Сюда мы заносим все заказы, которые нужно выполнить и доставить клиентам по их адресам:
Открыв двойным щелчком любой заказ можно просмотреть или отредактировать его детали:
Обратите внимание, что:
- В названии мероприятия указано имя менеджера, который выполняет этот заказ (Елена) и номер заказа (45).
- Указан адрес доставки
- В примечании вписаны (отдельными строками, но в любом порядке) параметры заказа: тип оплаты, сумма, имя клиента и т.д. в формате Параметр=Значение.
Шаг 1. Получаем ссылку на Google Calendar
Сначала нам нужно получить веб-ссылку на наш календарь с заказами. Для этого щёлкаем по кнопке с тремя точками Параметры календаря Работа рядом с названием календаря и выбираем команду Настройки и общий доступ:
В открывшемся окне можно, при желании, сделать календарь общедоступным или открыть к нему доступ для отдельных пользователей. Нам же нужна ссылка для закрытого доступа к календарю в формате iCal:
Шаг 2. Загружаем данные из календаря в Power Query
Теперь открываем Excel и на вкладке Данные (если у вас Excel 2010-2013, то на вкладке Power Query) выбираем команду Из интернета (Data - From Internet). Затем вставляем скопированный путь к календарю и жмём ОК.
Распознавать формат iCal Power Query не умеет, но ему легко помочь. По сути, iCal - это обычный текстовый файл c двоеточием в качестве разделителя и внутри он выглядит примерно так:
Так что можно просто щелкнуть правой кнопкой мыши по значку загруженного файла и выбрать максимально близкий по смыслу формат CSV - и наши данные о всех заказах будут загружены в редактор запросов Power Query и разделены на два столбца по двоеточию:
Если присмотреться, то хорошо видно, что:
- Информация о каждом мероприятии (заказе) группируется в блок, начинающийся со слова BEGIN и заканчивающийся END.
- Дата-время начала и окончания хранится в строках с метками DTSTART и DTEND.
- Адрес доставки - это LOCATION.
- Примечание к заказу - поле DESCRIPTION.
- Название мероприятия (имя менеджера и номер заказа) - поле SUMMARY.
Шаг 3. Преобразуем в нормальный вид
Для этого выполним следующую цепочку действий:
- Удалим ненужные нам верхние 7 строк до первого BEGIN командой Главная - Удалить строки - Удаление верхних строк (Home - Remove rows - Remove top rows).
- Отфильтруем по столбцу Column1 строки, содержащие нужные нам поля: DTSTART, DTEND, DESCRIPTION, LOCATION и SUMMARY.
- На вкладке Добавление столбца выберем Столбец индекса (Add column - Index column), чтобы добавить к нашим данным колонку с порядковым номером строки.
- Там же на вкладке Добавление столбца выберем команду Условный столбец (Add column - Conditional column) и в начале каждого блока (заказа) выведем значение индекса:
- Заполним пустые ячейки в получившемся столбце Блок, щёлкнув по его заголовку правой кнопкой мыши и выбрав команду Заполнить - Вниз (Fill - Down).
- Удалим ненужный больше столбец Индекс.
- Выделим столбец Column1 и выполним по нему свёртку данных из столбца Column2 с помощью команды Преобразование - Столбец сведения (Transform - Pivot column). В параметрах обязательно выбрать Не агрегировать (Don't aggregate), чтобы к данным не применялась никакая математическая функция:
- В получившейся двумерной (кросс) таблице зачистить обратные слэши в столбце адреса (правой кнопкой по заголовку столбца - Замена значений) и удалить ненужный более столбец Блок.
- Чтобы превратить содержимое столбцов DTSTART и DTEND в полноценную дату-время, выделив их, выберем на вкладке Преобразование - Дата - Выполнить анализ (Transform - Date - Parse). Затем подправим код в строке формул, заменив функцию Date.From на DateTime.From, чтобы не терять значения времени:
- Затем, щёлкнув правой кнопкой мыши по заголовку, разделим столбец DESCRIPTION с параметрами заказов по разделителю - символу \n, но при этом в параметрах выберем деление на строки, а не на столбцы:
- Еще раз разделим получившийся столбец на два отдельных - параметр и значение, но уже по знаку "равно".
- Выделив колонку DESCRIPTION.1 выполним свёртку, как уже делали ранее, командой Преобразовать - Столбец сведения (Transform - Pivot column). Столбцом значений в этом случае будет колонка со значениями параметров - DESCRIPTION.2 В параметрах обязательно выбрать функцию Не агрегировать (Don't aggregate):
- Останется задать форматы для всех столбцов и переименовать их желаемым образом. И можно выгружать результаты обратно в Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...)
И вот наш список заказов, загруженный в Excel из Google-календаря:
В будущем, при изменении-добавлении новых заказов в календарь, достаточно будет лишь обновить наш запрос командой Данные - Обновить всё (Data - Refresh All).
Ссылки по теме
- Производственный календарь в Excel с обновлением из интернета через Power Query
- Трансформация столбца в таблицу
- Создание базы данных в Excel
Преобразование фала календаря ICS в CSV происходит не корректно, т.к. длина строки в файле CSV ограничена 75 символами, далее происходит перенос на новую строку.
Скрин:
Таким образом в Excel часть данных обрезается и происходит перенос на новую строку не возможно сделать корректный фильтр по Column1.
Скрин:
Скрин:
Как возможно решить данную проблему?
Файл календаря ICS прилагаю: