• Архив

    «   Май 2022   »
    Пн Вт Ср Чт Пт Сб Вс
                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          

Новая статья: Переключение вычислений в сводной таблице с помощью срезов

Срезы в сводных таблицах можно использовать не только классическим  образом - для фильтрации исходных данных, но и для переключения между  различными типами вычислений в области значений:

 

Реализовать такое очень легко - потребуется лишь пара формул на DAX, вспомогательная таблица и сводная по модели данных Power Pivot.

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

Новая статья: Как правильно разворачивать таблицы в Power Query

Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только
выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)

В новой версии Excel эта возможность встроена, а в старых придётся чуть-чуть пошаманить с макросами, чтобы её имитировать.

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

Новая статья: Как правильно разворачивать таблицы в Power Query

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

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

Новая статья: Симулятор лотереи в Microsoft Excel

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

В этой статье (и видеоуроке) мне хотелось бы ответить на часть этих вопросов. Благо, в  Excel для решения таких задач достаточно инструментов, многие из  которых, кстати, могут пригодиться и в более прозаических рабочих  ситуациях.


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

Новая статья: Столбец из примеров - искусственный интеллект в Power Query

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

Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?

На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искусственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать.  Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.



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

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

Выпустил очередное плановое обновление надстройки PLEX для Microsoft Excel. Добавлено несколько весьма приятных, надеюсь, фич :)

Функция онлайн-перевода Translate

  Добавлена функция Translate, которая переводит содержимое указанной  ячейки с одного языка на другой, используя онлайн-переводчик Google:


Синтаксис прост:

=Translate(Text; FromLang; ToLang)

где:

  • Text – исходная ячейка с текстом для перевода
  • FromLang – текстовый двухбуквенный код языка, с которого производится перевод (т.е. язык исходного текста).
  • ToLang – текстовый двухбуквенный код языка, на который нужно перевести

  Подсмотреть необходимые коды языков можно на странице Google   Переводчика в адресной строке после выбора нужных языков в форме:


Блок команд для Power Query

Добавлен набор инструментов для упрощения и ускорения работы с Power Query:

 

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

Также можно быстро удалить все запросы Power Query или  существующие подключения к данным (если вы, например, планируете  поделиться текущим файлом с другими пользователями, но не хотите  отдавать им запросы к данным).

Возможно экспортировать М-код выбранных запросов Power Query во внешние текстовые файлы и, наоборот, импортировать М-код  из внешних текстовых файлов и создать запросы на их основе. Это  позволяет использовать огромное количество готовых М-функций, написанных  другими пользователями (см., например, https://github.com/KiaraGrouwstra/pquery).

Команда Обновить все запросы в папке выведет на экран диалоговое окно, где можно выбрать папку и задать при необходимости критерий для отбора обновляемых запросов:

 

После нажатия на кнопку Обновить макрос по очереди откроет все  файлы из заданной папки и обновит все или только указанные запросы Power  Query в каждом файле. При необходимости можно также вывести детальный  отчет по результатам обновления на новом листе:


Команда Создать таблицу параметров создаёт заготовку «умной»  таблицы с параметрами, которые можно будет использовать в ваших запросах  и специальную функцию для их загрузки в Power Query.

И, наконец, команда Тест скорости открывает окно, где можно протестировать ваши запросы, замерив длительность их выполнения:


Прочие изменения, улучшения и исправления ошибок

Традиционно проведена большая работа над ошибками и полировка-оптимизация уже имеющихся инструментов и функций:
  • Большинство иконок перерисованы для качественного отображения на мониторах высокого разрешения.
  • Исправлена ошибка, возникающая при вычислении функцией CountByColor на ячейках с датой.
  • Добавлена возможность отката (undo) при удалении дубликатов.
  • Оптимизирован и значительно ускорен инструмент Изменение размеров в группе Трансформация.
  • Исправлена некорректная работа функции FIO с некоторыми именами и фамилиями.
  • Инструмент Поменять местами теперь не требует выбора объекта  (строки, столбцы, диапазон), а определяет это автоматически. Также  исправлена ошибка, возникающая при обмене соседних строк-столбцов.
  • Исправлена ошибка, возникающая при использовании инструмента Сводные таблицы – Все поля в сумму на сводных таблицах по Модели данных.
  • Исправлена некорректная работа функции Propis при нестандартных числовых разделителях.
  • Обновлен дизайн Менеджера листов.
  • Оглавление в Менеджере листов теперь можно создавать только для выделенных листов.
  • В Менеджере листов добавлена кнопка для сохранения выделенных листов в новую книгу.
  • Исправлена ошибка, возникающая при отсутствии в папке PLEX файла с пользовательскими формулами из Библиотеки формул.
  • Исправлена некорректная работа инструмента Перейти к источнику, если ссылка ведет на внешний файл, расположенный на SharePoint или в облаке OneDrive.
  • Исправлена ошибка, возникающая на ячейках с ошибками #ИМЯ, #ЧИСЛО и т.д. при использовании инструмента Фильтр+действие.
  • Исправление УФ-ада больше не удаляет примечания, заметки и пользовательское форматирование.
  • Исправлена ошибка, возникающая при откате изменений на ячейках с текстом длиной больше 8000 знаков.
  • В инструмент Перехват ошибок добавлен флажок Не добавлять к тем ячейкам, где такая функция уже есть, чтобы избежать дублирования.
  • Функция RegExpReplace возвращает исходную строку, если ничего не находит для замены.
  • В инструмент Объединить добавлена опция Все в одну ячейку.
  • Исправлена некорректная работа функции TextMid.
  • Исправлена ошибка, возникающая в инструменте Скриншот при сохранении файла, где в имени есть точки.
  • В инструмент Случайно добавлены новые списки: стороны света, греческий и фонетический алфавиты, модели Lego и т.д.
  • Ускорена работа инструмента В значения.
Где скачать
Скачать последнюю версию, как обычно, можно в разделе PLEX сверху в главном меню или по ссылке.
Распакуйте архив и замените все файлы в вашей папке PLEX на файлы из скачанного архива с заменой. Если добавляли свои функции в Библиотеку функций, то на заменяйте файл flib.xlsx.

Ваши мысли по поводу работы новой версии, пожелания, жалобы и найденные баги можно смело озвучивать в комментариях к этому посту.
Enjoy!  ;)  

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

Хотите, чтобы диаграмма строилась только  по данным из текущей строки, т. е. строки, куда вы поставили активную ячейку? Примерно так:


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

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

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

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

 

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

 

Само собой, использовать для этого будем наиболее подходящий инструмент - Power Query:



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

Новая статья: Нарастающие итоги в Excel


Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft  Excel разными способами:
  • формулами,
  • в сводной таблице,
  • в запросе Power  Query.


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

Новая статья: LAMBDA - новая суперфункция Excel

Подробный разбор новой оберточной суперфункции LAMBDA, недавно добавленной в Excel. Её синтаксис, варианты применения, нюансы и особенности - от простых формул до динамических массивов и рекурсии:


Плюс подробное видео:



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

Новая статья: Сохранение истории обновлений запросов Power Query

Почти на каждом тренинге по Power Query, когда мы доходим до способов  обновления созданных запросов и люди видят как новые данные заменяют при  обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно  сделать так, чтобы при обновлении старые данные тоже где-нибудь  сохранялись и было видно всю историю обновлений?"

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

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

Новая статья: Деление слипшегося текста функцией ФИЛЬТР.XML

Функцию ФИЛЬТР.XML можно использовать не только для импорта XML-данных из интернета - основной  задачи, для которой эта функция, собственно, и предназначена. Есть ещё одно - неожиданное и красивое - применение этой функции для быстрого деления слипшегося текста по ячейкам:


Подробный разбор механизма - в новой статье и видео:



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

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

Открыть страницу с описанием курса
Страницы: 1 | 2 | 3 | 4 | 5 | ... | 28 | След.
Наверх