Срезы в сводных таблицах можно использовать не только классическим образом - для фильтрации исходных данных, но и для переключения между различными типами вычислений в области значений:
https://www.planetaexcel.ru/upload/medialibrary/e8e/pdolaj1cwmqz1mw5k18hezwy08od4l52/pivot-select-cals-animated.gif
Реализовать такое очень легко - потребуется лишь пара формул на DAX, вспомогательная таблица и сводная по модели данных Power Pivot.
Читать статью полностью
Николай Павлов
Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только
выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)
В новой версии Excel эта возможность встроена, а в старых придётся чуть-чуть пошаманить с макросами, чтобы её имитировать.
//www.youtube.com/embed/mtGbhDgPQU4?feature=oembed
Читать статью полностью
Николай Павлов
Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
//www.youtube.com/embed/ik0KWTV10S8?feature=oembed
Читать статью полностью
Николай Павлов
С завидной регулярностью (а в последнее время - всё чаще) мне пишут люди с просьбами помочь в различных вычислениях, связанных с лотереями. Кто-то хочет реализовать в Excel свой секретный алгоритм подбора выигрышных чисел, кто-то - найти закономерности в выпавших номерах прошедших тиражей, кто-то - подловить организаторов лотереи на нечестной игре.
В этой статье (и видеоуроке) мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие ...
... меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill ...
Хотите, чтобы диаграмма строилась только по данным из текущей строки, т. е. строки, куда вы поставили активную ячейку? Примерно так:
https://www.planetaexcel.ru/upload/medialibrary/ec1/68f8e9innbxy95ubq0cubvs9ev0wyy27/active-cell-chart-gif.gif
Реализовать такое очень легко - потребуется лишь две формулы и один крохотный макрос в 3 строки.
Читать статью полностью
Николай Павлов
Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
https://www.planetaexcel.ru/upload/medialibrary/1da/1vfnb43catgl6vmqnbjb3s39m3tp2atj/collect-and-unpivot1.png
А на выходе нам нужно собрать данные из всех таблиц в одну плоскую нормированную таблицу, удобную для последующего анализа и построения сводной, т.е. вот в такую: https://www. ...
https://www.planetaexcel.ru/upload/medialibrary/d5b/zo3mul76fs1kzlk1r36zpeoimvn3b3tb/running-totals1.png
Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft Excel разными способами:
формулами,
в сводной таблице,
в запросе Power Query.
//www.youtube.com/embed/6TWvTQoMlbE?feature=oembed
Читать статью полностью
Николай Павлов
... /0pbd3fr6d6ap754pfbcu6p2onxkdui0f/lambda6.png
Плюс подробное видео:
//www.youtube.com/embed/U9u1bWwvH40 ...
Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"
Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в ...
... механизма - в новой статье и видео:
//www.youtube.com/embed/pLk8DcebWG4 ...
Дополнительно к уже разобранным способам создания связанных выпадающих списков добавил ещё один - на основе неотсортированных справочников и функции СМЕЩ (OFFSET):
//www.youtube.com/embed/nxPxp-CLIqA?feature=oembed
Читать статью полностью
Николай Павлов
Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:
В старых версиях Excel 2007-2013 это можно было сделать с помощью прямого веб-запроса.
Начиная с 2010 года это можно очень удобно делать с помощью надстройки Power Query.
К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций ВЕБСЛУЖБА и ФИЛЬТР.XML.
//www.youtube.com/ ...
Записал очередной видеоурок по созданию простой пользовательской функции на VBA для подсчета суммы, среднего или количества ячеек с заданным цветом заливки и/или шрифта:
//www.youtube.com/embed/zxhjLGX524E?feature=oembed
Читать статью полностью
Николай Павлов
Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.
https://www.planetaexcel.ru/upload/medialibrary/d69/ ...
Если вы хотя бы в общих чертах знакомы с функцией ВПР (VLOOKUP) (если нет, то сначала бегом сюда), то должны понимать, что эта и другие похожие на неё функции (ПРОСМОТРХ, ИНДЕКС и ПОИСКПОЗ, ВЫБОР и т.д.) всегда выдают в качестве результата значение - число, текст или дату, которые мы ищем в заданной таблице. Но что, если вместо значения нам хочется получить живую гиперссылку, щёлкнув по которой мы могли бы мгновенно перепрыгнуть к найденному совпадению в другой таблице, чтобы посмотреть на ...
Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере: https://www.planetaexcel.ru/upload/medialibrary/fd8/g5gvz11zk5stkblizf7r11qtqpboq9h5/find-keywords1.png
Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если ...
Сборка таблиц - классическая задача для любого пользователя Excel. Особенно "весело" она решается, если шапки в этих таблицах точно не совпадают, а имеют какие-либо различия.
Решить проблему можно весьма изящно - используя надстройку Power Query, которая умеет собирать таблицы даже с разной структурой:
//www.youtube.com/embed/WXWXHt_15AI?feature=oembed
Читать статью полностью
Николай Павлов
На практике весьма часто возникают случаи, когда нам с вами нужно найти ближайшее значение в наборе (таблице) по отношению к заданному числу. Это может быть, например:
Расчет скидки в зависимости от объема.
Вычисление размера бонусов в зависимости от выполнения плана.
Калькуляция тарифов на доставку в зависимости от расстояния.
Подбор подходящей тары для товара и т.д.
Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.
Есть несколько способов ...
Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию.Разбираем два способа - с обновлением (через Power Query) и без (только VBA).
//www.youtube.com/embed/iiW9ZGymH4w?feature=oembed
Читать статью полностью
Отсортировано по релевантности | Сортировать по дате