Сообщения блогов сайта "Планета" (www.planetaexcel.ru) http://www.planetaexcel.ru http://www.planetaexcel.ru ru http://backend.userland.com/rss092 Николай Павлов: Новая статья: Поиск кратчайшего пути В прошлом я уже делал разбор решения транспортной задачи на примере оптимизации доставки товаров со складов в магазины. Теперь же давайте попробуем использовать Microsoft Excel для решения другой классической задачи из того же раздела - прокладки кратчайшего маршрута из пункта А в пункт Б через несколько промежуточных точек транспортной сети. Частным случаем такой задачи ещё называют "задачу коммивояжера", когда нам нужно по кратчайшему маршруту обойти все точки, не заходя ни в одну из них дважды, а затем вернуться обратно в пункт отправления.

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


Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-poisk-kratchayshego-puti/ Николай Павлов: Новая статья: Обработка только видимых строк При работе в Microsoft Excel иногда возникают ситуации, когда нам с вами нужно как-то особо и отдельно обрабатывать только видимые или, наоборот, только скрытые пользователем строки. Например, считать какие-то хитрые отчёты именно по видимым ячейкам, или загружать только видимые ячейки для дальнейшей обработки в Power Query и так далее. Во всех этих случаях для каждой строки нам нужно каким-то образом однозначно определять - осталась ли она видимой и отображается сейчас на экране или скрыта от глаз пользователя.

Разбираем простой способ это реализовать с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS):

И несколько сценариев применения этой методики в реальных задачах:

  • подсчёт видимых ячеек в формулах (обычных и динамических массивах)
  • загрузку только видимых ячеек в Power Query (с прикольным срезом для выбора)
Читать статью полностью


Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-obrabotka-tolko-vidimykh-strok/ Николай Павлов: Новая статья: Группы вычислений в Power BI Подробно разбираемся с тем, как можно упростить и ускорить расчёты в Microsoft Power BI с помощью двух мощных инструментов - групп вычислений (calculation groups) и параметров полей (field parameters). Это позволит не создавать в вашей модели кучу однообразных мер:

А также даст возможность быстро и удобно переключаться между разными типами вычислений прямо в отчёте с помощью среза:



Читать статью полностью
Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-gruppy-vychisleniy-v-power-bi/ Николай Павлов: Новая статья: Автообновление сводных таблиц На днях компания Microsoft анонсировала, что в следующих версиях Excel появится возможность автоматического обновления сводных таблиц. За это будет отвечать кнопка Автообновление (Auto Refresh) на вкладке Анализ сводной таблицы (PivotTable Analyze):

Разбираем механизм работы этого нового инструмента, его плюсы и минусы. А для тех, у кого пока нет последней версии, пишем аналог на VBA.

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


Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-avtoobnovlenie-svodnykh-tablits/ Николай Павлов: Новая статья: Нечёткая текстовая кластеризация Как собрать разношёрстные названия одних и тех же объектов в группы (кластеры) по максимальной похожести?

Очень актуальная задача, если вы работаете с данными, которые вводили разные пользователи или собираются из разных источников.

На помощь приходит Power Query, а точнее специальная функция нечёткой текстовой кластеризации из встроенного в Power Query языка М.

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


Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-nechyetkaya-tekstovaya-klasterizatsiya/ Николай Павлов: Новая статья: 7 способов проверить условия в Excel Проверка условий и выполнение расчётов по разной логике в зависимости от выполнения или невыполнения этих условий - один из базовых навыков, который одним из первых осваивают все, кто работает в Microsoft Excel. Обычно для этой цели используют классическую функцию ЕСЛИ (IF). Если же необходимо проверить несколько критериев, то приходится вкладывать несколько функций ЕСЛИ друг в друга или использовать связки ЕСЛИ+И (ИЛИ), что уже не так просто и приводит к появлению страшноватых "матрёшек-монстров".

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

  • ЕСЛИ (IF)
  • ЕСЛИМН (IFS)
  • ПЕРКЛЮЧ (SWITCH)
  • ВПР с интервальным просмотром (VLOOKUP)
  • ВЫБОР (CHOOSE)
  • Прямое умножение на условия
  • Формула массива
Читать статью полностью


Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-7-sposobov-proverit-usloviya-v-excel/ Николай Павлов: Новая статья: Продвинутые техники использования функции ПРОСМОТРХ (XLOOKUP) В 2020 году Microsoft выпустила обновление, добавляющее в Microsoft Excel 365 новую функцию ПРОСМОТРХ (XLOOKUP), пришедшую на замену классическим функциям поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP) и связке функций ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH). В отличие от предшественников, новая функция имеет простой и понятный синтаксис, умеет искать как по вертикали, так и по горизонтали, а также может искать снизу вверх, а не сверху вниз. Базовый синтаксис и возможности функции ПРОСМОТРX я уже подробно разбирал в этой статье с сопутствующим видеоуроком.

Теперь же давайте посмотрим на более продвинутые техники использования функции ПРОСМОТРX и скрытые её возможности:

  • Множественные условия
  • Использование массива искомых значений
  • Результат в виде массива
  • Двумерный поиск
  • ПРОСМОТРX внутри СУММЕСЛИ
  • Поиск на нескольких листах
Читать статью полностью


Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-prodvinutye-tekhniki-ispolzovaniya-funktsii-prosmotrkh-x/ Николай Павлов: Новая статья: Мгновенная фильтрация в Excel
Само собой, можно включить классический фильтр через Данные - Фильтр (Data - Filter), развернуть выпадающий список в соответствующем столбце и ввести искомый текст в поле поиска, но ведь потом придётся куда-то скопировать результаты, очистить фильтрацию и повторить поиск ещё раз в другом столбце. А если столбцов для поиска будет не 2, а больше? Тоска зелёная, правда?

Но есть другой путь. Суть его в том, чтобы добавить на лист поле ввода ActiveX, куда мы будем вводить искомый текст, а затем написать формулу, которая будет искать и выводить все строки в таблице, где в заданных столбцах есть частичное текстовое совпадение с введёнными данными:

Читать статью полностью
Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-mgnovennaya-filtratsiya-v-excel/ Николай Павлов: Новая статья: Примечания на самоссылающемся запросе в Power Query
Используем для этого интересную технику в Power Query - запрос, ссылающийся сам на себя (в отличие от формул, где это приводит к циклическим ссылкам, в Power Query - это вполне ОК).

Читать статью полностью
Подробнее...]]> http://www.planetaexcel.ru/blog/novaya-statya-primechaniya-na-samossylayushchemsya-zaprose-v-power-que/ Николай Павлов: Новая статья: Сравнение версий файлов с помощью Inquire Предположим, с ходу видно, что внутри внесено несколько правок, но все их быстро идентифицировать и проконтролировать весьма непросто, тем более, что они могут быть неочевидными - например, изменение какой-то ячейки на этом листе может привести к пересчёту другой на соседнем и т.п. В общем, игра из серии "найди 10 отличий или премию не получишь" :)

Помочь в подобной ситуации может бесплатная надстройка от Microsoft с названием Inquire, появившаяся в Microsoft Excel, начиная с 2013 года.

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


Подробнее...]]> http://www.planetaexcel.ru/blog/868/