[МУЗЫКА] [МУЗЫКА] Сейчас мы с вами рассмотрим кейс: расчет стоимости доставки. Предположим, что компания занимается доставкой в города четырех федеральных округов, причем коэффициент доставки различается в зависимости от федерального округа. Кроме того, в зависимости от того, когда производится доставка, в будние дни или выходные, коэффициент доставки тоже изменяется. Необходимо все эти данные собрать в единый отчет, удобный для работы менеджера. Итак, у нас имеется три таблички. Первая таблица содержит сведения о федеральных округах и городах, с которыми работает компания. Вторая — коэффициенты стоимости доставки, относящихся к каждому федеральному округу, и третья — коэффициент стоимости доставки в зависимости от того, когда производится доставка, в будние или в выходные дни. Сначала объединим таблички с коэффициентом стоимости доставки по федеральным округам, с таблицей, содержащей сведения о городах, с которыми работает компания. Например, необходимо коэффициент, относящийся к Центральному федеральному округу, поставить напротив каждого города, относящегося к этому округу, точно так же и для Северо-Западного округа необходимо каждый коэффициент, чтобы в таблице стоял напротив соответствующего города. После этого к новой созданной таблице необходимо присоединить сведения о доставке в будние и выходные дни. Поскольку это две категории, то появятся две строчки, относящихся к будним дням и выходным. Поэтому, например город Москва, Центральный федеральный округ, теперь должна будет встречаться с таблицей не один раз, а два раза. На последнем этапе необходимо рассчитать итоговый коэффициент стоимости доставки. Сначала для будних дней для соответствующих городов, а потом и для выходных дней. Решим эту задачу с помощью надстройки Power Query. Мы видим на одном листе все три справочные таблицы. Для наглядности мы будем работать в том же самом документе, в котором находятся наши данные. Тогда для того чтобы загрузить эти таблицы в надстройку Power Query, нам нужно их превратить в умные таблицы. Итак, выделяем первую табличку, на вкладке «Главное» выбираем «Форматировать как таблицу», выбираем дизайн этой таблицы, проверяем, что правильно таблица захватилась, и дадим ей более удобное название, давайте, наверное, «Коэффициент». Кратко, «Коэффициент». Точно так же выделяем вторую таблицу, форматируем ее как таблицу, окей. Назовем ее «Города». И третье — так же форматируем, как табличку. Окей. И назовем ее «Дни». Теперь необходимо все эти данные загрузить в надстройку Power Query. Выбираем Power Query. Давайте выберем сначала в первой таблице «Федеральный округ», выбираем «Загрузить из таблицы», ждем, пока загрузятся данные. Итак, таблица «Города». «Закрыть и загрузить в...». Создадим подключение к нашей книге. Теперь загрузим вторую таблицу, таблица «Коэффициент», «Закрыть и загрузить в...». И опять создадим подключение к нашей книге. И точно так же выделяем в третьей таблице. «Из таблицы». Дни «Закрыть и загрузить в...» и подключим ее к нашей книге. Так, теперь нам необходимо объединить таблицу «Города» с таблицей «Коэффициенты». Мы выбираем принцип объединения на вкладке Power Query, в данном случае это будет «Слияние». Нажимаем «Слияние», и мы будем собирать таблицу «Города» с таблицей «Коэффициенты». Итак, мы соединяем федеральный округ в первой и второй таблице. Не совсем хорошо у нас получилось, нам необходимо немного подправить в таблице федеральный округ. Давайте вернемся в «Запросы», переходим в таблицу «Города», посмотрите, у нас сейчас не совсем корректно отображены данные, поскольку у нас не в каждой строчке мы видим название округа, поэтому данные в таблице «Коэффициент» будут присоединены не совсем корректно. Давайте мы исправим эту ошибку. Итак, выделяем столбец «Федеральный округ», вкладка «Преобразование» и заполняем вниз. Вот теперь она уже стала у нас корректной таблицей. Вернемся в нашу итоговую таблицу. Теперь мы можем раскрыть данные. Итак, нам нужен коэффициент стоимости доставки добавить, убираем «Использовать исходное имя как префикс», добавили данные, и теперь мы видим, что корректно для каждого федерального округа мы знаем коэффициент стоимости доставки. Давайте переименуем наш отчет, назовем его «Отчет». Окей. Теперь нужно к данной таблице присоединить стоимость доставки в будние дни и выходные. Итак, находясь в таблице «Отчет», во вкладке «Главное», мы можем выбрать клавишу «Комбинировать», «Слияние запросов». И добавить к ней таблицу дней. Нам теперь необходимо выбрать ключевое поле, по которому мы будем соединять. Но проблема в том, что в первой таблице и во второй все поля разные, и нам никак не выбрать ключевые поля, и соединить эту таблицу мы не можем. То есть перед тем, как это сделать, необходимо применить некоторый трюк. Поэтому нажимаем клавишу «Отмена», возвращаемся в наш отчет, и сейчас мы сделаем следующее. Мы добавим новый пользовательский столбец к нашему отчету. Нам совершенно сейчас неважно, как он называется, но в нем будут содержаться одинаковые данные. Ну например, мы возьмем любую букву алфавита, вот букву A, например, мы добавим, ставим сюда в этот столбец, который содержит вот эту букву A. И то же самое мы должны сделать с таблицей дней. Мы добавим пользовательский столбец, название у него может быть и другим, но самое главное, что у нас должен быть столбец, содержащий точно такие же данные, как и вторая таблица. И вот теперь мы сможем к таблице «Отчет» присоединить таблицу дней. Итак, комбинируем слияние. Добавляем таблицу «Дни» и выделяем вот этот новый столбец, содержащий одинаковые сведения. Нажимаем Окей. Появился новый столбец «Дни», мы его раскрываем. И нам нужно выбрать день недели и повышающий коэффициент. Окей. Итак, мы видим теперь, вот у нас первая строчка Центральный округ и Москва теперь у нас появилась два раза. И мы видим стоимость доставки в будни и выходные дни. Столбец, который мы создали, нам больше не нужен. Мы его выделяем правой клавишей и удаляем. Осталось посчитать итоговый коэффициент стоимости доставки. Мы создаем пользовательский столбец, назовем его «Коэффициент». Итого коэффициент. [БЕЗ_ЗВУКА] Нам нужно умножить коэффициент стоимости доставки на повышающий коэффициент. Окей. И я думаю, что дополнительные столбцы, содержащие сведения о различных коэффициентах, нам больше не нужны. Выделяем столбец, удаляем и второй тоже. Удаляем. Осталось загрузить наши данные в таблицу. Итак, теперь мы видим итоговый отчет, содержащий сведения о стоимости доставки в разные дни, в разные города, и в этой таблице сведены все данные из справочных таблиц. Удобно для использования менеджером в итоговом отчете.
