Продолжаю потихоньку пилить онлайн-курсы для тех, кто не может пока участвовать в наших живых тренингах. Так чтобы назло ковидам и карантинам можно было удобно устроиться за компьютером с чашечкой чая-кофе и прокачиваться в выбранной теме не теряя времени.
Выложил очередной и долгожданный многими курс - по . 30 уроков, более 3 часов видео, файлы упражнений и самостоятельной отработки и финальный тест для самоконтроля - всё как положено.
Основные темы курса:
Подготовка данных для анализа в сводной. Как правильно организовать исходную информацию, чтобы с ней удобно было работать в сводной таблице.
Построение сводной и первичные настройки. Как строить, трансформировать, допиливать внешний вид под корпоративные требования.
Визуализация. Добавляем наглядность и красоту вашим отчетам с помощью условного форматирования, спарклайнов, гистограмм, значков и т.п.
Хитрости сортировки, фильтрации и группировки данных в сводных таблицах. О, тут много нюансов!
Вычисления в сводной - как считать что-то посложнее банальной суммы. Рассмотрим вычисления долей, процентов, нарастающих итогов, рангов, индексов значимости и прочей экзотики, включая вычисления по собственной логике с помощью вычисляемых полей и объектов.
Основы работы в Модели Данных Power Pivot и преимущества построения сводной по ней по сравнению с классической сводной. Будущее уже здесь
Новая статья с видео о том, как реализовать одновременную фильтрацию сразу нескольких сводных таблиц одним общим срезом. Если сводные построены на основе одного источника, то всё просто. Если на основе разных, то придётся немного пошаманить с Power Pivot и Power Query
Простой, но красивый трюк с добавлением на диаграмму картинок в качестве подписей. Подойдет для визуализации логотипов компаний, брендов, флагов стран и т.п.
Новая статья и видео с подробным разбором плюсов и преимуществ построения сводных таблиц по Модели Данных Power Pivot по сравнению с классическими сводными в Excel.
Из самого сочного:
Связи между таблицами без ВПР.
Любые сложные вычисления в сводной на языке DAX.
Подсчёт количества уникальных значений.
Пользовательские наборы элементов по строкам и столбцам вместо фильтрации.
Все классические функции поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP), ПОИСКПОЗ (MATCH)и им подобные имеют одну важную особенность - они ищут от начала к концу, т.е. слева-направо или сверху-вниз по исходным данным. Как только находится первое подходящее совпадение - поиск останавливается и найденным оказывается только первое вхождение нужного нам элемента.
Что же делать, если нам требуется найти не первое, а последнее вхождение? Например, последнюю сделку по клиенту, последний платёж, самую свежую заявку и т.д.?
Сплошь и рядом встречаются сайты, где при попытке импортировать с них данные в Excel Power Query просто в упор не видит там таблиц с нужной нам информацией. Причин для этого может быть несколько, но чаще всего это происходит потому, что веб-дизайнер при создании таблицы использовал в HTML-коде страницы не стандартную конструкцию с тегом <TABLE>, а её аналог - вложенные друг в друга теги-контейнеры <DIV>. Это весьма распространённая техника при вёрстке веб-сайтов, но, к сожалению, Power Query пока не умеет распознавать такую разметку и загружать такие данные в Excel. Тем не менее, есть способ обойти это ограничение
Давайте разберемся с этой задачей на примере парсинга цен и описаний товаров с маркетплейса Wildberries:
Имеем список объектов (например, товаров) с пометкой, к какому набору (корзине) каждый из них относится. Необходимо разложить объекты по своим наборам, сформировав таблицу как на рисунке справа:
Похожие задачи встречаются на практике весьма часто - в случаях, когда приходится распределять те или иные ресурсы:
сотрудников по командам
водителей по маршрутам
клиентов по менеджерам
товары по корзинам и т.д.
В прошлом я уже делал статью и видео о похожей задаче, где мы разбирали как переложить одномерный столбец с данными в двумерную таблицу, но там ситуация была проще, т.к. каждый набор (строка) имел одинаковый размер (число столбцов). Здесь же количество элементов в наборах заранее не известно и не равно друг другу, так что подход придётся изменить.
Чтобы у вас был выбор, давайте разберем несколько способов .
Одна из самых распространенных проблем (и частых вопросов на тренингах) в том, как построить сводную таблицу, если в качестве исходных данных тебе досталась вот такая "красота":
Выручить здесь может надстройка Power Query, при помощи которой можно за несколько минут превратить эту жесть в нормализованную плоскую таблицу, по которой строить сводную можно уже легко:
Хороший пример, если вы осваиваете возможности Power Query - тут они раскрываются во всей красе. Ну и, само-собой, видео для тех, кому лень читать
Обновил о том, как создать в Microsoft Excel выпадающий список с показом выбранного изображения. В новых версиях Excel описанный там подход уже не работает и нужен другой метод:
Стиль в Microsoft Excel - это сохраненная совокупность параметров форматирования ячейки. Единожды создав стиль, его затем можно многократно применять к другим ячейкам, моментально оформляя их нужным вам образом, что неимоверно ускоряет повседневную работу в Excel. Главная прелесть стилей в том, что с их помощью можно не просто раскрашивать ячейки, а создавать крайне полезные в работе нестандарные форматы:
Разбираем два способа построить вафельную диаграмму (waffle chart) в Excel - с помощью условного форматирования и отрихтованной линейчатой диаграммы (bar chart), чтобы получить вот такую красоту:
Отлично подойдет для отображения прогресса по любым задачам.
Ура! Сделал и выложил новый дистанционный онлайн-курс .
На сегодняшний день в Microsoft Excel 477 встроенных функций. Это число немного пугает
На самом деле, для решения 90% задач, возникающих при работе в Excel, достаточно знать одну десятую от этого количества. Этот курс как раз и посвящен подробному разбору этих 50 ключевых функций, без которых сейчас невозможно представить ни один современный проект в Microsoft Excel.
Спектр изучения - от СУММ до ВПР-ИНДЕКС-ПОИСКПОЗ, т.е. те самые 20%, которые решают 80% проблем.
В этом курсе вы научитесь:
Быстро и легко вводить, редактировать и комбинировать между собой любые функции Excel разными способами.
Склеивать, резать и зачищать текст с помощью удобных текстовых функций Excel.
Выполнять любые операции с датами, считать сроки, длительности, стаж или возраст в календарных или рабочих днях с помощью функций дат и времени.
Задавать проверки и сложные условия для реализации хитрой логики в ваших расчетах.
Использовать мощные функции поиска и подстановки (ВПР, ИНДЕКС, ПОИСКПОЗ...) для связывания таблиц.
Курс состоит из 6 глав, 22 уроков и обучающих видео общей длительностью более 3 часов. К каждому уроку приложен текстовый конспект, файл-пример и ДЗ для самостоятельной проработки всех навыков.
Коллеги из издательства "ДМК Пресс" еще раз обрадовали новостью - ушла в печать переведенная на русский язык монументальная книжка самых известных в мире DAX экспертов . 700 страниц глубочайшей проработки всех функций языка DAX и нюансов их применения на практике для всех, кто использует в работе Power Pivot в Excel или Power BI:
По секрету делюсь промокодом на 30% скидку "для своих", любезно предоставленным мне издательством (спасибо им большое): DAX_Pavlov_2020
Выложил новую статью и видео про решение одной из очень распространенных задач - как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
К сожалению, в MS Excel нет простых встроенных инструментов для решения этой проблемы, поэтому придется привлекать относительно сложные формулы массива (но Ctrl+Shift +Enter жать не придется).
В следующей статье разберём, как реализовать подобное с помощью макросов и хитрого запроса Power Query.
Как при помощи Power Query сделать в Excel "вечный" производственный календарь - автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.
