• Архив

    «   Сентябрь 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      

Новая статья: Обновляемый курс валют в 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, которая умеет собирать таблицы даже с разной структурой:


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

Обновление надстройки PLEX v.2021.1

Выпустил очередное обновление моей надстройки PLEX для Microsoft Excel до версии 2021.1. Из новых "плюшек" имеются:
Дубликаты и уникальные

Вместо старых инструментов Извлечение уникальных и Удаление дубликатов теперь будет новый - Дубликаты и уникальные:

 

Возможности стали на порядок мощнее:

 

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

Работа с гиперссылками и адресами email
 В раздел Книга добавлен новый инструмент для работы с гиперссылками в ячейках листа:

 

 

Умеет:

  • «оживлять» текстовые ссылки, превращая их в полноценные рабочие
  • Удалять гиперссылки, оставляя в ячейках только текст (т.е. ссылки перестают работать)
  • Открывать все ссылки в выделенном диапазоне в браузере на отдельных вкладках или в Outlook в виде новых сообщений.
  • Заменять текст гиперссылок на их фактический адрес вида http…
  • Трансформировать прямые гиперссылки в функцию Excel ГИПЕРССЫЛКА (HYPERLINK).
  • Исправить, заменяя в ссылке один фрагмент на другой, например “yandx” на “yandex”
  • Извлечь ссылки или адреса эл.почты из выделенного диапазона, содержащего мешанину из текста и ссылок.
Заполнение пустых ячеек во всех направлениях

Инструмент Заполнить пустые теперь умеет делать это не только вниз, но и в остальных направлениях:

 

Фильтр + действие

Вместо трёх старых инструментов Фильтровать и копировать / перенести / удалить добавлен один универсальный:

 

Выделяете любую ячейку с данными и запускаете - выводится вот такое диалоговое окно:

 

Умеет фильтровать на листе или в текущей таблице строки по любому из заданных условий:

  • Равные/не равные заданному значению
  • Содержащие/не содержащие заданную подстроку
  • Соответствующие заданному шаблону (с использованием символов *#? и т.д.)

Найденные строки можно скопировать или перенести на новый лист или в новую книгу или же удалить.

АнтиФильтр

В раздел Фильтры добавлен инструмент Антифильтр:

 

Выполняет инвертирование всех отфильтрованных в данный момент строк,  т.е. скрытые фильтром строки делает видимыми, а видимые – скрывает.

Удаление скрытых / не скрытых фильтром строк

Добавлены два инструмента для выполнения простых, но весьма частых  операций – удаления скрытых или, наоборот, не скрытых в данный момент  фильтром строк:

 

Сравнение диапазонов цветом

Доработан имеющийся инструмент Парная подсветка дубликатов:

 

Теперь его можно использовать при выделении двух (или более)  диапазонов для цветового выделения совпадающих значений и визуального  сравнения:

 

Также увеличено с 20 до 90 количество цветовых пар заливка-шрифт для выделения найденных дубликатов.

Типы ссылок для аргументов в Библиотеке Формул

Важное усовершенствование, о котором давно просили. Для пользовательских формул в Библиотеке формул теперь можно задавать тип ссылки (абсолютная, относительная, смешанные…) для каждого аргумента:

 

Удаление не-ASCII символов

В инструмент Текст на вкладку Очистить добавлена  возможность удаления всевозможных нестандартных Unicode-символов не из  ASCII набора: иероглифов, диакритических знаков и т.п.

 

Также добавлена аналогично работающая пользовательская функция OnlyASCII:

 

Выбора стандарта транслитерации

В инструменте Текст на вкладке Другое добавлена возможность выбора системы при транслитерации кириллицы:

 

Функции GetEmail и GetEmail2 для извлечения адресов эл.почты

Добавлены несколько новых пользовательских функций для извлечения из текста адресов электронной почты и гиперссылок.

 GetEmail(ячейка; разделитель) – извлекает из текста в исходной  ячейке все найденные адреса эл.почты и выводит их одной строкой через  заданный символ-разделитель:

 

Если символ-разделитель не задан, то по умолчанию используется точка с  запятой с пробелом. При желании, можно указать любой разделитель или  использовать функцию Excel СИМВОЛ (CHAR) с кодом нужного знака, например СИМВОЛ(10) вставит между адресами символ переноса строки (Alt+Enter).

Также добавлена функция GetEmail2, выполняющая аналогичное  извлечение адресов эл.почты из текста, но возвращающая сразу все  результаты в виде горизонтального динамического массива (работает только  в Office 365 с установленными обновлениями поддержки динамических  массивов):

 

Если нужно, чтобы результаты располагались вертикально в столбец –  можно использовать эту функцию в связке со стандарной экселевской  функцией ТРАНСП (TRANSPOSE):

 

Функции GetURL и GetURL2 для извлечения адресов сайтов

Эти функция делает то же самое, что и предыдущие, но для URL-адресов веб-страниц:

 

Новые функции регулярных выражений

 RegExpExtract2(Текст; Шаблон; УчитыватьРегистр) – работает  аналогично уже имеющейся функции RegExpExtract, т.е. извлекает из  исходного текста все фрагменты соответствующие заданному шаблону  регулярного выражения, однако выводит сразу все результаты в виде  динамического массива (работает только в Office 365 с установленными  обновлениями поддержки динамических массивов).

 RegExpTest(Текст; Шаблон; УчитыватьРегистр) – проверяет  соответствие текста заданному шаблону регулярного выражения. Последний  аргумент определяет, нужно ли (1) или нет (0) учитывать регистр (по  умолчанию не учитывается). На выходе функция выдаёт логические значения  ЛОЖЬ (FALSE) или ИСТИНА (TRUE), соответственно.

 RegExpReplace(Текст; Шаблон; Новый_текст; УчитыватьРегистр) – ищет в исходном тексте фрагменты соответствующие заданному шаблону регулярного выражения и заменяет их на новый текст.

Среднее арифметическое по цвету заливки/шрифта

К имеющимся функциям суммирования и подсчета количества ячеек с определенным цветом заливки (SumByCellColor, CountByCellColor) и шрифта (SumByFontColor, CountByFontColor) добавлены аналогичные функции расчета среднего арифметического: AverageByCellColor и AverageByFontColor.

Также теперь во втором аргументе всех этих функций в качестве ячейки с  образцом цвета теперь можно указывать диапазон из нескольких ячеек с  разными цветами.

14 новых формул в Библиотеке формул

Помимо пользовательских макрофункций PLEX, в Библиотеку формул добавлены новые полезные формулы:  
  1. Извлечение текста в скобках
  2. Кол-во совпадений в двух диапазонах (без учета регистра)
  3. Кол-во точных совпадений с заданным значением (с учетом регистра)
  4. Кол-во уникальных числовых значений в диапазоне
  5. Кол-во уникальных текстовых значений в диапазоне
  6. Подсчет кол-во определенных символов в ячейке
  7. Нормализация текста (удаление знаков препинания, лишних пробелов…)
  8. Проверка вхождения числа в заданный интервал
  9. Гиперссылка на первое вхождение заданного значения в список
  10. Создание гиперссылки для отправки email с заданными параметрами
  11. Вывод имени текущего листа (без макросов)
  12. Вывод имени текущей книги (без макросов)
  13. Вывод названия текущей папки (без макросов)
  14. Вывод полного пути к текущему файлу (без макросов)
Плюс много мелких доработок, улучшений и исправлений ошибок. Детальное описание всех новых функций и изменений можно посмотреть здесь.

Скачать последнюю версию надстройки PLEX.

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

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

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

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

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



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

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

Как разобрать данные из одной таблицы сразу на несколько листов по  заданному критерию.Разбираем два способа - с обновлением (через Power  Query) и без (только VBA).

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

Новый онлайн-курс: Сводные таблицы от и до

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

Выложил очередной и долгожданный многими курс - по сводным таблицам в Microsoft Excel. 30 уроков, более 3 часов видео, файлы упражнений и самостоятельной отработки и финальный тест для самоконтроля - всё как положено.

Основные темы курса:
  • Подготовка данных для анализа в сводной. Как правильно организовать исходную информацию, чтобы с ней удобно было работать в сводной таблице.
  • Построение сводной и первичные настройки. Как строить, трансформировать, допиливать внешний вид под корпоративные требования.
  • Визуализация. Добавляем наглядность и красоту вашим отчетам с помощью условного форматирования, спарклайнов, гистограмм, значков и т.п.
  • Хитрости сортировки, фильтрации и группировки данных в сводных таблицах. О, тут много нюансов!
  • Вычисления в сводной - как считать что-то посложнее банальной суммы. Рассмотрим вычисления долей, процентов, нарастающих итогов, рангов, индексов значимости и прочей экзотики, включая вычисления по собственной логике с помощью вычисляемых полей и объектов.
  • Основы работы в Модели Данных Power Pivot и преимущества построения сводной по ней по сравнению с классической сводной. Будущее уже здесь :)

Открыть страницу с описанием курса

Новая статья: Одновременная фильтрация нескольких сводных таблиц

Новая статья с видео о том, как реализовать одновременную фильтрацию сразу нескольких сводных таблиц одним общим срезом. Если сводные построены на основе одного источника, то всё просто. Если на основе разных, то придётся немного пошаманить с 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:


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

Новый онлайн-курс "Функции Excel - самое нужное"


Ура! Сделал и выложил новый дистанционный онлайн-курс "Функции Excel - самое нужное".

На сегодняшний день в Microsoft Excel 477 встроенных функций. Это число немного пугает :)

На самом деле, для решения 90% задач, возникающих при работе в Excel,  достаточно знать одну десятую от этого количества. Этот курс как раз и  посвящен подробному разбору этих 50 ключевых функций, без которых сейчас  невозможно представить ни один современный проект в Microsoft Excel.

Спектр изучения - от СУММ до ВПР-ИНДЕКС-ПОИСКПОЗ, т.е. те самые 20%, которые решают 80% проблем.

В этом курсе вы научитесь:

  • Быстро и легко вводить, редактировать и комбинировать между собой любые функции Excel разными способами.
  • Склеивать, резать и зачищать текст с помощью удобных текстовых функций Excel.
  • Выполнять любые операции с датами, считать сроки, длительности, стаж или возраст в календарных или рабочих днях
    с помощью функций дат и времени.
  • Задавать проверки и сложные условия для реализации хитрой логики в ваших расчетах.
  • Использовать мощные функции поиска и подстановки (ВПР, ИНДЕКС, ПОИСКПОЗ...) для связывания таблиц.
Курс состоит из 6 глав, 22 уроков и обучающих видео общей длительностью  более 3 часов. К каждому уроку приложен текстовый конспект, файл-пример и  ДЗ для самостоятельной проработки всех навыков.


Посмотреть страницу курса

Книжка по DAX от ДМК Пресс

Коллеги из издательства "ДМК Пресс" еще раз обрадовали новостью - ушла в печать переведенная на русский язык монументальная книжка самых известных в мире DAX экспертов Альберто Феррари и Марко Руссо - "Подробное руководство по DAX". 700 страниц глубочайшей проработки всех функций языка DAX и нюансов их применения на практике для всех, кто использует в работе Power Pivot в Excel или Power BI:



Ссылка на книгу на сайте издательства

По секрету делюсь промокодом на 30% скидку "для своих", любезно предоставленным мне издательством (спасибо им большое):
DAX_Pavlov_2020

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

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


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

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

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

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

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


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