Открыть выгрузку, удалить шапку, пустые строки и строку «Итого».
Модуль 1
Три шага, записанные один раз: запрос сам отличает данные от мусора — по признаку, а не по номеру строки.
Курс Котодаты · для тех, кто каждый месяц чистит выгрузки
Power Query с нуля
Настройте обработку данных один раз — дальше одна кнопка
Power Query — часть Excel, которая запоминает, что вы сделали с таблицей, и повторяет это на новых данных. Курс учит собирать такие запросы: от «удалить три строки шапки» до отчёта из папки с файлами. Справа — настоящий запрос: пройдите по шагам и обновите данные.
990 ₽ за весь курс, один платёж без подписки. Первый урок открыт бесплатно — нужна только регистрация.
- 16 уроков и 5 проектов
- 81 задача с проверкой результата
- Редактор запросов в браузере
- Формулы Excel знать не обязательно
Попробуйте
Выгрузка за январьЩёлкайте по шагам, потом нажмите «Обновить»
| Точка | Выручка |
|---|---|
| Центр | 8070 |
| Парк | 5520 |
| Вокзал | 8840 |
Строк: 3
Выручка по кофейням.
Шаги не менялись — поменялись данные, и результат пересчитался сам. Данные учебные: выдуманная сеть кофеен.
Зачем это
Работа, которую вы делаете заново каждый месяц
Слева — что обычно делают руками. Справа — чем это заменяется и в каком модуле вы этому научитесь.
Исправить «1 250,00» текстом, даты через точку и «8 кг» вместо числа.
Модуль 1
Типы данных и замены: что можно превратить в число — превращается, остальное честно помечается, а не теряется.
Привести к одному виду «ООО Ромашка», «ромашка » и «РОМАШКА», разнести адрес по столбцам.
Модуль 2
Обрезка, регистр, разделение столбца — в том порядке, при котором повторы действительно находятся.
Переделать отчёт, где категории «лесенкой», а месяцы — столбцами, в таблицу для сводной.
Модуль 3
Заполнение вниз, отмена свёртывания и группировка: таблица «для чтения» становится таблицей для счёта.
Протянуть ВПР по всей таблице, чтобы подтянуть цену, и склеить три месяца копированием.
Модуль 4
Объединение и добавление запросов. А если файлы лежат в папке — новый месяц подхватится сам.
Через месяц сделать всё то же самое заново.
Весь курс
Нажать «Обновить».
Что вы будете уметь в конце
Собирать запросы, которые берут сырую выгрузку — или десяток выгрузок из папки — и выдают таблицу, пригодную для отчёта. И делать это так, чтобы запрос отработал и на данных следующего месяца. Профессию аналитика курс не заменяет и трудоустройства не обещает.
Как это устроено
Задача решена, только если запрос переживёт обновление
В каждом уроке — редактор запросов прямо на странице: лента действий, таблица и список шагов. Роликов нет: всё, что объясняется, тут же делается руками.
- 1 Проверяется результат, а не набор шагов
К нужной таблице можно прийти разными путями — засчитывается то, что получилось.
- 2 Потом проверка подменяет данные
Выгрузка следующего месяца, новый столбец, новый файл в папке. Запрос, который работал только на сегодняшних данных, не засчитывается — как не сработал бы в жизни.
- 3 И говорит, что именно пошло не так
Не просто «неверно»: «проверка обновила источник, в выгрузке появился столбец „Скидка“ — и он попал в результат». К каждой задаче — две подсказки и разбор после решения.
- 4 Шаги можно пройти по одному
В разборах — готовые запросы: щёлкаете по шагу и видите таблицу на этот момент. Так же ищут ошибку в чужом запросе — этому посвящён отдельный урок.
Проекты
5 проектов: от одной выгрузки до отчёта из папки
Весь курс — данные одной выдуманной сети кофеен: кассовые выгрузки, поставки, меню, планы. Проект — последние задачи модуля: несколько шагов, которые складываются в готовый запрос.
- Модуль 1
Выгрузка кассы — в рабочую таблицу
Месячная выгрузка кассовой программы: шапка, итоги, пустые строки, задвоенные чеки, всё текстом. Один запрос делает из неё таблицу, с которой можно считать, — и повторяет это на выгрузке следующего месяца.
- Модуль 2
Справочник поставщиков из хаоса
Список поставщиков, который вели разные люди: названия как попало, город и адрес в одной ячейке, телефоны в пяти видах. Запрос приводит его к справочнику, по которому можно искать.
- Модуль 3
Отчёт учётной программы — в плоскую таблицу
Отчёт «для чтения»: группы заголовками, месяцы столбцами, подытоги между строками. Запрос превращает его в таблицу «одна строка — одна запись», из которой строится любая сводная.
- Модуль 4
Продажи трёх кофеен за квартал
Выгрузки по месяцам, справочник меню и справочник точек. Запросы собирают их в одну таблицу продаж с категорией и городом — и находят позиции, которых в меню нет.
- Модуль 5
Отчёт, который обновляется сам
Итоговый проект курса: кассовые выгрузки, меню и план. На выходе — таблица «точка, месяц, выручка, план, выполнение». Проверка подменяет все источники данными следующего квартала.
Программа
5 модулей, 16 уроков
Сначала одна таблица и простые шаги, потом текст, форма таблицы, несколько источников — и в конце то, ради чего всё затевалось: запросы, которые не ломаются. Раскройте модуль — под каждым уроком написано, что вы в нём делаете.
1Первый запрос3 урока
- Запрос: источник, шаги и кнопка «Обновить»Вы превращает выгрузку с шапкой и подписями в таблицу с заголовками и видит, как запрос повторяет работу на новых данных.
- Типы данных и ошибки в ячейкахВы задаёт столбцам типы, находит значения, которые в тип не превращаются, и решает, что с ними делать.
- Строки и столбцы: оставить нужноеВы убирает лишние столбцы, фильтрует и сортирует строки, удаляет дубликаты — так, чтобы запрос не сломался от новой выгрузки.
Проект: Выгрузка кассы — в рабочую таблицу. Месячная выгрузка кассовой программы: шапка, итоги, пустые строки, задвоенные чеки, всё текстом. Один запрос делает из неё таблицу, с которой можно считать, — и повторяет это на выгрузке следующего месяца.
В итоге: Делает из «сырой» выгрузки чистую таблицу и понимает, что такое запрос: список шагов, который выполняется заново при каждом обновлении.
2Чистка текста3 урока
- Пробелы, регистр и замена значенийВы убирает лишние пробелы, приводит написание к одному виду и заменяет значения.
- Разделить, объединить, извлечьВы разбирает столбец «всё в одной ячейке» на части и собирает столбцы обратно.
- Свой столбец: формула и условиеВы добавляет столбцы с расчётом, с текстом и с условием if … then … else.
Проект: Справочник поставщиков из хаоса. Список поставщиков, который вели разные люди: названия как попало, город и адрес в одной ячейке, телефоны в пяти видах. Запрос приводит его к справочнику, по которому можно искать.
В итоге: Приводит текстовые столбцы в порядок и добавляет в таблицу то, чего в исходных данных не было.
3Форма таблицы3 урока
- Заполнение вниз и строки-заголовки группВы превращает отчёт с названиями групп «лесенкой» в таблицу, где группа записана в каждой строке.
- Отмена свёртывания: месяцы из столбцов в строкиВы разворачивает таблицу «показатели по столбцам» в плоский список.
- Группировка: итоги по группамВы считает суммы, количества и средние по группам прямо в запросе.
Проект: Отчёт учётной программы — в плоскую таблицу. Отчёт «для чтения»: группы заголовками, месяцы столбцами, подытоги между строками. Запрос превращает его в таблицу «одна строка — одна запись», из которой строится любая сводная.
В итоге: Узнаёт таблицу, с которой неудобно считать, и приводит её к виду «одна строка — одна запись».
4Несколько таблиц4 урока
- Объединение запросов: подтянуть данные из справочникаВы подтягивает столбцы из другой таблицы по совпадению значений — без ВПР.
- Виды соединения: чего нет и что не совпалоВы находит строки без пары: товары без цены, точки без продаж.
- Добавление запросов: таблицы друг под другомВы собирает таблицы одного вида в одну длинную.
- Папка с файлами: все месяцы одним запросомВы делает запрос, который сам подхватывает новый файл.
Проект: Продажи трёх кофеен за квартал. Выгрузки по месяцам, справочник меню и справочник точек. Запросы собирают их в одну таблицу продаж с категорией и городом — и находят позиции, которых в меню нет.
В итоге: Собирает данные из нескольких таблиц и файлов в одну и не теряет строки по дороге.
5Запрос, который не ломается3 урока
- Порядок шагов и запись шагаВы читает запись шага, находит шаг, на котором запрос сломался, и чинит его.
- Устойчивый запрос: новые столбцы, новые значения, пустые ячейкиВы строит запрос так, чтобы его не ломали изменения в источнике.
- Итоговый проект: от трёх источников к отчётуВы проходит весь путь: чистит, соединяет, группирует — и получает отчёт, который обновляется сам.
Проект: Отчёт, который обновляется сам. Итоговый проект курса: кассовые выгрузки, меню и план. На выходе — таблица «точка, месяц, выручка, план, выполнение». Проверка подменяет все источники данными следующего квартала.
В итоге: Строит запросы, которые переживают обновление данных, и чинит чужие.
Цена
Что входит в курс
- 16 уроков в 5 модулях. Разбор на примерах, готовые запросы, которые можно пройти по шагам, и свободный редактор.
- 81 задача. С проверкой результата на сегодняшних и на обновлённых данных, двумя подсказками и разбором.
- 5 проектов. От чистки одной кассовой выгрузки до отчёта «план и факт» из папки с файлами.
- Редактор запросов в браузере. Собственная учебная среда Котодаты: источник, шаги, типы, соединения, группировка — в объёме курса. Устанавливать ничего не нужно.
- Привычные названия. Действия и шаги названы так, как в русском Excel, — в настоящей программе вы узнаете их с первого взгляда.
- Свой темп. Нет ни групп, ни сроков. Доступ не заканчивается.
Для кого
Кому курс подойдёт — и что нужно знать заранее
Регулярно получаете выгрузки
Из учётной или кассовой программы, от коллег, из личных кабинетов — и каждый раз приводите их в порядок перед тем, как считать. Курс написан про эту работу.
Сводите данные из нескольких файлов
Копируете месяцы друг под друга, тянете ВПР на тысячи строк, ищете, что не сошлось. В четвёртом модуле это заменяется тремя действиями.
Формулы знаете плохо
Это не мешает: почти всё в курсе делается кнопками, а формулы запросов объясняются с начала. Нужно только понимать, что такое таблица, строка и столбец.
Границы курса
Чего в курсе нет
Лучше узнать об этом до покупки.
- Подключения к вашим файлам и базам. Источники в курсе учебные и лежат внутри урока. Как подключиться к файлу, папке или базе у себя, урок рассказывает, но делается это уже в Excel.
- Программирования на языке запросов. Вы научитесь читать запись шага и писать формулы для своих столбцов. Писать запросы текстом целиком, свои функции и параметры курс не учит.
- Power BI, Power Pivot и модели данных. Курс — про подготовку данных. Что делать с чистой таблицей дальше — отдельная тема.
- Самой программы. Редактор в уроках — учебный: он похож на настоящий устройством и названиями действий, но умеет только то, что нужно курсу. Некоторые действия в Excel выглядят иначе — уроки об этом предупреждают.
- Наставника, сертификата и обещаний про работу. Решения проверяет тренажёр, документа об образовании курс не даёт.
Вопросы
О чём спрашивают до покупки
Не нашли ответа — напишите на support@kotodata.ru.
Открыть первый урокЧто такое Power Query и где он находится?
Это встроенная часть Excel для загрузки и подготовки данных. В Excel 2016 и новее она находится на вкладке «Данные» — кнопки «Получить данные» и «Из таблицы/диапазона». Ставить отдельно ничего не нужно.
Нужен ли мне Excel, чтобы пройти курс?
Для курса — нет: редактор запросов открывается на странице урока. Чтобы применять изученное на работе, понадобится Excel 2016 или новее; в разных версиях набор возможностей может немного отличаться.
Я не знаю формул Excel. Справлюсь?
Курс на это рассчитан. Большинство действий делается кнопками, формулы запросов объясняются с нуля в шестом уроке. Курсы «Excel с нуля» и «Excel для анализа и отчётов» проходить перед этим не обязательно.
Чем это лучше макросов?
Макрос — программа, которую нужно написать и уметь чинить. Запрос собирается кнопками, каждый его шаг виден в списке и отменяется крестиком. Для чистки и сведения таблиц этого хватает, а программировать не приходится.
Сколько времени займёт курс?
Около 11 часов: от получаса до часа на урок. По два урока в неделю — два месяца, по уроку в будний день — три с небольшим недели. Сроков нет.
А если я застряну на задаче?
У каждой задачи две подсказки: первая наводит на мысль, вторая почти показывает путь. Проверка объясняет, чем результат отличается от нужного. Разбор открывается после решения.
Можно проходить с телефона?
Читать разборы — можно. Решать задачи нужно на компьютере: в редакторе запросов лента, таблица и список шагов стоят рядом, и на маленьком экране им тесно.
Соберите запрос один раз — дальше он работает сам
Первый урок — про выгрузку с шапкой, пустыми строками и итогом: три шага, и она становится таблицей. Он открыт бесплатно. Дальше — ещё 15 уроков и 5 проектов за 990 ₽.