• Архив

    «   Июль 2021   »
    Пн Вт Ср Чт Пт Сб Вс
          1 2 3 4
    5 6 7 8 9 10 11
    12 13 14 15 16 17 18
    19 20 21 22 23 24 25
    26 27 28 29 30 31  

Новая статья: 5 способов поиска ближайшего числа в Microsoft Excel

На практике весьма часто возникают случаи, когда нам с вами нужно  найти ближайшее значение в наборе (таблице) по отношению к заданному  числу. Это может быть, например:

  • Расчет скидки в зависимости от объема.
  • Вычисление размера бонусов в зависимости от выполнения плана.
  • Калькуляция тарифов на доставку в зависимости от расстояния.
  • Подбор подходящей тары для товара и т.д.

Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.

Есть несколько способов - очевидных и не очень - для решения такой задачи.



Читать статью полностью

Новая статья: Разделение таблицы по листам

Как разобрать данные из одной таблицы сразу на несколько листов по  заданному критерию.Разбираем два способа - с обновлением (через 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 описанный там подход уже не работает и нужен другой метод:



Само-собой, к статье приложен и видеоурок:

Читать статью и смотреть видео

Новая статья: Как правильно использовать Стили в Excel

Стиль в Microsoft Excel - это сохраненная совокупность параметров  форматирования ячейки. Единожды создав стиль, его затем можно  многократно применять к другим ячейкам, моментально оформляя их нужным  вам образом, что неимоверно ускоряет повседневную работу в Excel.
Главная прелесть стилей в том, что с их помощью можно не просто раскрашивать ячейки, а создавать крайне полезные в работе нестандарные форматы:
  • Стили для своих единиц измерения
  • Стили тысяч или миллионов
  • Стили с цветом и процентами для плана
  • Стили со спецзначками и спецсимволами
  • Стили со сложными условиями и эмодзи
Разбираем всё это в очередной статье с видео:


Читать статью полностью

Новая статья: Вафельная диаграмма в Excel

Разбираем два способа построить вафельную диаграмму (waffle chart) в Excel - с помощью условного форматирования и отрихтованной линейчатой диаграммы (bar chart), чтобы получить вот такую красоту:



Отлично подойдет для отображения прогресса по любым задачам.

Читать статью полностью и смотреть видео
 

Новая статья: Массовая замена текста в Power Query функцией List.Accumulate

Продолжая начатую в прошлой статье тему про массовую замену текста, разбираемся с решением этой задачи уже в Power Query.


Использовать будем одну из не самых простых для понимания функций языка М - List.Accumulate:


Читать статью полностью

Новая статья: Массовая замена текста формулами

Выложил новую статью и видео про решение одной из очень распространенных задач - как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).


К сожалению, в MS Excel нет простых встроенных инструментов для решения этой проблемы, поэтому придется привлекать относительно сложные формулы массива (но Ctrl+Shift +Enter жать не придется).

В следующей статье разберём, как реализовать подобное с помощью макросов и хитрого запроса Power Query.

Читать статью полностью

Новая статья: Производственный календарь в Excel

Как при помощи Power Query сделать в Excel "вечный" производственный  календарь - автоматически обновляющийся список нерабочих дней за все  годы для использования в своих расчетах.


Читать статью полностью

Новая статья: Регулярные выражения (RegExp) в Power Query

Пару лет назад я уже делал большую статью с видео о том, как добавить в Microsoft Excel поддержку регулярных выражений (RegExp). Это здорово развязывает нам руки при анализе и парсинге текста и предоставляет в наше распоряжение мощный и гибкий функционал, рядом с которым стандартные текстовые функции Excel и рядом не стояли.
Открытым, однако, остался вопрос - можно ли добавить поддержку регулярных выражений в Power Query? Справка и тех.поддержка Microsoft отвечают на этот вопрос сугубо отрицательно, но, на самом деле, есть способ обойти это ограничение...



Читать статью полностью >>>

Новая статья: Обманчивая простота функции ПОСЛЕД (SEQUENCE)



Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в Excel в Office 365. Обновление Office 365, которое подарило Microsoft Excel новый  вычислительный движок с поддержкой динамических массивов, также добавило  к нашему арсеналу 7 новых функций, заточенных специально для работы с  массивами. Про три самых важных функции: СОРТ (SORT), ФИЛЬТР (FILTER) и УНИК (UNIQUE) я уже писал, а в этой статье хотел разобрать ещё одну весьма приятную обнову - функцию ПОСЛЕД (SEQUENCE).
Эта функция предназначена для совершенно, на первый взгляд, банальной  задачи - генерации числовых последовательностей (рядов, арифметических  прогрессий) с определённым шагом и от заданного стартового значения. Звучит скучно, но не ведитесь на первое обманчивое впечатление - этой функции есть, чем вас удивить!

Читать статью полностью

Новая статья: ВПР и числа-как-текст

Разбор классической проблемы с функцией ВПР (VLOOKUP), которая отказывается находить числовые значения, если они отформатированы как текст:



Читать статью полностью

Новая статья: План-факт анализ в сводной с Power Pivot и Power Query

Большой комплексный пример: пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями "многие-ко-многим", простых мер на DAX и допиливании входных данных с помощью Power Query.



Читать статью полностью

Новое видео: Выделение дубликатов цветом

Обновил старую статью про выделение дубликатов в списке цветом с помощью условного форматирования и добавил к ней видео с разбором 3 способов:
  • быстрое выделение дубликатов-ячеек
  • правило УФ на основе формулы для выделения всей строки
  • правило УФ с формулой массива(!), когда нет ключевого столбца с уникальными значениями и нужно делать склейку всех значений в строке



Читать статью полностью
Страницы: 1 | 2 | 3 | 4 | 5 | След.
Наверх