• Архив

    «   Октябрь 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
                 

Новая статья: Обновляемый курс валют в Excel

Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:

К этим способам в последних версиях Microsoft Excel теперь можно  добавить ещё один - импорт данных из интернета в формате XML с помощью  встроенных функций ВЕБСЛУЖБА и ФИЛЬТР.XML.

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

Обновление в приемах: Связанные выпадающие списки по неотсортированному справочнику

Дополнительно к уже разобранным способам создания связанных выпадающих списков добавил ещё один - на основе неотсортированных справочников и функции СМЕЩ (OFFSET):



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

Новое видео: Вычисления по цвету ячеек в Excel

Записал очередной видеоурок по созданию простой пользовательской функции на VBA для подсчета суммы, среднего или количества ячеек с заданным цветом заливки и/или шрифта:


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

Новая статья: Система учета заказов на Google Calendar и Excel

Многие бизнес-процессы (и даже целые бизнесы) в этой жизни  предполагают выполнение заказов ограниченным количеством исполнителей к  заданным срокам. Планирование в таких случаях происходит, что  называется, "от календаря" и часто возникает потребность переноса  запланированных в нём событий (заказов, встреч, поставок) в Microsoft  Excel - для дальнейшего анализа формулами, сводными таблицами,  построения диаграмм и т.п.


Само-собой, хотелось бы реализовать такой перенос не тупым  копированием (что как раз несложно), а с автоматическим обновлением  данных, чтобы в будущем всё внесенные в календарь изменения и новые  заказы "на лету" отображались бы в Excel. Реализовать такой импорт можно  за считанные минуты с помощью надстройки Power Query, встроенной в  Microsoft Excel.


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

Новая статья: Динамические гиперссылки между таблицами

Если вы хотя бы в общих чертах знакомы с функцией ВПР (VLOOKUP) (если нет, то сначала бегом сюда),  то должны понимать, что эта и другие похожие на неё функции (ПРОСМОТРХ,  ИНДЕКС и ПОИСКПОЗ, ВЫБОР и т.д.) всегда выдают в качестве результата значение - число, текст или дату, которые мы ищем в заданной таблице.

Но что, если вместо значения нам хочется получить живую гиперссылку,  щёлкнув по которой мы могли бы мгновенно перепрыгнуть к найденному  совпадению в другой таблице, чтобы посмотреть на него в общем контексте?



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

Новая статья: Поиск ключевых слов в тексте

Поиск ключевых слов в исходном тексте - одна из очень распространенных  задач при работе с данными. Давайте рассмотрим её решение несколькими  способами на следующем примере:


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

Сделаем это разными способами: формулами и с помощью Power Query.

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

Новая статья: Сборка таблиц с разными шапками из нескольких книг

Сборка таблиц - классическая задача для любого пользователя Excel. Особенно "весело" она решается, если шапки в этих таблицах точно не совпадают, а имеют какие-либо различия.
Решить проблему можно весьма изящно - используя надстройку Power Query, которая умеет собирать таблицы даже с разной структурой:


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

Новая статья: 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 описанный там подход уже не работает и нужен другой метод:



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

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