Срезы в сводных таблицах можно использовать не только классическим образом - для фильтрации исходных данных, но и для переключения между различными типами вычислений в области значений:
Реализовать такое очень легко - потребуется лишь пара формул на DAX, вспомогательная таблица и сводная по модели данных Power Pivot.
Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)
В новой версии Excel эта возможность встроена, а в старых придётся чуть-чуть пошаманить с макросами, чтобы её имитировать.
Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
С завидной регулярностью (а в последнее время - всё чаще) мне пишут люди с просьбами помочь в различных вычислениях, связанных с лотереями. Кто-то хочет реализовать в Excel свой секретный алгоритм подбора выигрышных чисел, кто-то - найти закономерности в выпавших номерах прошедших тиражей, кто-то - подловить организаторов лотереи на нечестной игре.
В этой статье (и видеоуроке) мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие из которых, кстати, могут пригодиться и в более прозаических рабочих ситуациях.
Одно из самых просматриваемых видео у меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill)в Microsoft Excel. Суть этого инструмента в том, что если вам надо как-то преобразовать ваши исходные данные, то достаточно просто начать набирать в соседнем с ними столбце тот результат, который вы хотите получить. После нескольких вручную набранных ячеек (обычно хватает 2-3) Excel "поймёт" логику нужных вам преобразований и автоматически продолжит набранное, завершив всю монотонную работу за вас.
Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?
На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искусственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать. Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.
Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
А на выходе нам нужно собрать данные из всех таблиц в одну плоскую нормированную таблицу, удобную для последующего анализа и построения сводной, т.е. вот в такую:
Само собой, использовать для этого будем наиболее подходящий инструмент - Power Query:
Подробный разбор новой оберточной суперфункции LAMBDA, недавно добавленной в Excel. Её синтаксис, варианты применения, нюансы и особенности - от простых формул до динамических массивов и рекурсии:
Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"
Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в абсолютном большинстве случаев). Однако, если очень хочется, то можно обойти это ограничение. Причем способ, как вы дальше увидите, весьма простой.
Функцию ФИЛЬТР.XML можно использовать не только для импорта XML-данных из интернета - основной задачи, для которой эта функция, собственно, и предназначена. Есть ещё одно - неожиданное и красивое - применение этой функции для быстрого деления слипшегося текста по ячейкам:
Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:
В старых версиях Excel 2007-2013 это можно было сделать с помощью .
Начиная с 2010 года это можно очень удобно делать с помощью .
К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций ВЕБСЛУЖБА и ФИЛЬТР.XML.
Дополнительно к уже разобранным способам создания связанных выпадающих списков добавил ещё один - на основе неотсортированных справочников и функции СМЕЩ (OFFSET):
Записал очередной видеоурок по созданию простой пользовательской функции на VBA для подсчета суммы, среднего или количества ячеек с заданным цветом заливки и/или шрифта:
Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.
Само-собой, хотелось бы реализовать такой перенос не тупым копированием (что как раз несложно), а с автоматическим обновлением данных, чтобы в будущем всё внесенные в календарь изменения и новые заказы "на лету" отображались бы в Excel. Реализовать такой импорт можно за считанные минуты с помощью надстройки Power Query, встроенной в Microsoft Excel.
Если вы хотя бы в общих чертах знакомы с функцией ВПР (VLOOKUP) (если нет, то сначала ), то должны понимать, что эта и другие похожие на неё функции (ПРОСМОТРХ, ИНДЕКС и ПОИСКПОЗ, ВЫБОР и т.д.) всегда выдают в качестве результата значение - число, текст или дату, которые мы ищем в заданной таблице.
Но что, если вместо значения нам хочется получить живую гиперссылку, щёлкнув по которой мы могли бы мгновенно перепрыгнуть к найденному совпадению в другой таблице, чтобы посмотреть на него в общем контексте?
Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:
Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).
Сделаем это разными способами: формулами и с помощью Power Query.
Сборка таблиц - классическая задача для любого пользователя Excel. Особенно "весело" она решается, если шапки в этих таблицах точно не совпадают, а имеют какие-либо различия. Решить проблему можно весьма изящно - используя надстройку Power Query, которая умеет собирать таблицы даже с разной структурой:
На практике весьма часто возникают случаи, когда нам с вами нужно найти ближайшее значение в наборе (таблице) по отношению к заданному числу. Это может быть, например:
Расчет скидки в зависимости от объема.
Вычисление размера бонусов в зависимости от выполнения плана.
Калькуляция тарифов на доставку в зависимости от расстояния.
Подбор подходящей тары для товара и т.д.
Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.
Есть несколько способов - очевидных и не очень - для решения такой задачи.
Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию.Разбираем два способа - с обновлением (через Power Query) и без (только VBA).
Новая статья с видео о том, как реализовать одновременную фильтрацию сразу нескольких сводных таблиц одним общим срезом. Если сводные построены на основе одного источника, то всё просто. Если на основе разных, то придётся немного пошаманить с 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 описанный там подход уже не работает и нужен другой метод:
