зміст
Багато бізнес-процесів (і навіть цілих бізнесів) у цьому житті передбачають виконання замовлень обмеженою кількістю виконавців до заданого терміну. Планування в таких випадках відбувається, як кажуть, «з календаря» і часто виникає необхідність перенести заплановані в ньому події (замовлення, зустрічі, поставки) в Microsoft Excel – для подальшого аналізу за формулами, зведеними таблицями, діаграмами, тощо
Звичайно, хотілося б реалізувати таке перенесення не шляхом тупого копіювання (що якраз не складно), а з автоматичним оновленням даних, щоб надалі всі зміни в календарі та нові замовлення на льоту відображалися в Excel. Реалізувати такий імпорт можна за лічені хвилини за допомогою надбудови Power Query, вбудованої в Microsoft Excel, починаючи з версії 2016 (для Excel 2010-2013 її можна завантажити з сайту Microsoft і встановити окремо за посиланням) .
Припустимо, для планування ми використовуємо безкоштовний календар Google, в якому я для зручності створив окремий календар (кнопка з плюсиком в правому нижньому куті біля Інші календарі) із заголовком Work. Тут ми вводимо всі замовлення, які необхідно виконати та доставити клієнтам за їх адресами:
Двічі клацнувши будь-яке замовлення, ви можете переглянути або відредагувати його деталі:
Зауважте, що:
- Назва заходу менеджерхто виконує цей наказ (Олена) і Номер замовлення
- Вказано адреса доставка
- Примітка містить (окремими рядками, але в довільному порядку) параметри замовлення: вид платежу, суму, ім’я клієнта тощо у форматі Параметр=Значення.
Для наочності накази кожного керівника виділено своїм кольором, хоча це не обов'язково.
Крок 1. Отримайте посилання на Google Calendar
Спочатку нам потрібно отримати веб-посилання на наш календар замовлень. Для цього натисніть на кнопку з трьома крапками Параметри календаря працюють поруч із назвою календаря та виберіть команду Налаштування та спільний доступ:
У вікні, що відкриється, ви можете за бажанням зробити календар загальнодоступним або відкрити доступ до нього для окремих користувачів. Нам також потрібне посилання для приватного доступу до календаря у форматі iCal:
Крок 2. Завантажте дані з календаря в Power Query
Тепер відкрийте Excel і на вкладці дані (якщо у вас Excel 2010-2013, то на вкладці PowerQuery) виберіть команду З Інтернету (Дані — з Інтернету). Потім вставте скопійований шлях до календаря та натисніть 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