• Архив

    «   Ноябрь 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        

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

Выпустил досрочно-внеплановое обновление моей надстройки PLEX для Microsoft Excel - третье в этом году. Обновления и улучшения коснулись, в основном, импорта курсов валют.

Новые страны в Курсах валют


Теперь помимо уже имеющихся России, Украины, Беларуси и Казахстана, можно вставить курс любой валюты на любую дату для:

  • Грузии
  • Азербайджана
  • Таджикистана
  • Туркменистана
  • Молдовы

Для всех банков вставка производится с делением на соответствующую размерность, т.е. если, например, на сайте указан курс за 100 иен, то после вставки он будет поделен на 100.

Также все макросы импорта курсов валют переписаны на использование XML, RSS и JSON-потоков от банков, что ускоряет процесс загрузки и делает импорт независимым от будущих изменений дизайна сайтов банков.

Новые функции вставки курсов

Одновременно с добавлением новых стран (нац.банков) в диалоговом окне Курсы валют, были добавлены пользовательские функции для решения аналогичной задачи напрямую в ячейках листа:

=NBM(Дата;Валюта)  - вставка курсов Нац.Банка Молдовы

=NBTJ(Дата;Валюта)  - вставка курсов Нац.Банка Таджикистана

=NBTN(Дата;Валюта)  - вставка курсов Нац.Банка Туркменистана

=NBAZ(Дата;Валюта) - вставка курсов ЦБ Азербайджана

=NBG(Дата;Валюта)  - вставка курсов Нац.Банка Грузии

У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов:

  • Ячейка с датой, для которой нужно получить курс (если не указана, то берётся текущая дата).
  • Трёхбуквенный код валюты в кавычках ("USD", "EUR" и т.д.) или ссылка на ячейку, где он лежит (уже без кавычек). Если этот аргумент опущен, то по умолчанию берётся доллар США.

Исправления ошибок и улучшения

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

  • Исправлена ошибка, возникающая при вставке курсов при нестандартных числовых разделителях и нероссийских региональных настройках.
  • Исправлена ошибка, возникающая при удалении N-го по счету слова в инструменте Текст.
  • Исправлена ошибка, возникающая в инструменте Свертка при добавлении в область значений числовых полей.
  • Исправлена ошибка с регистром начальных символов, возникающая при транслитерации в инструменте Текст.

Как обычно, скачать последнюю версию можно из раздела PLEX.

Не забудьте после скачивания разблокировать файл надстройки.

Новая статья: Решение системы уравнений в Excel

Всем школьникам и студентам (и не только) посвящается :) Как решить систему уравнений в Microsoft Excel. Разбираем два принципиально разных подхода - с помощью обратной матрицы Крамера функциями МОБР и МУМНОЖ (для систем линейных уравнений) и подбором в надстройке Поиск решения (Solver).(для любых, в т.ч. и нелинейных).




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

Новая статья: РегистроНЕчувствительность в Power Query

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

Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.

На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.



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

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

Выпустил очередное обновление своей надстройки PLEX для Microsoft Excel - версия 2022.2, т.е. второе в этом году. Добавлено несколько "вкусных" инструментов, которые, я надеюсь, многие оценят.

Прицел

Включает / выключает выделение цветом фона текущей строки и столбца в пределах рабочей области листа. Очень удобно при работе с большими таблицами на больших мониторах:


Экспорт диапазона

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


Поддерживается экспорт в:

  • Изображения (PNG, GIF, JPG, BMP)
  • Файлы PDF
  • Текстовые файлы (CSV и TXT)
  • Универсальные текстовые форматы (XML, JSON)
  • Гипертекст (HTML)

Результаты можно выгрузить в файл или скопировать в буфер обмена

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

Если нужно экспортировать не реальное содержимое ячеек (длинные хвосты после запятой, например), а только то, что видно на экране, то пригодится флажок Выводить видимые, а не реальные значения.


Свёртка

Инструмент Редизайн кросс-таблицы в плоскую был переименован в Нормализацию и в пару к нему был добавлен новый инструмент обратного по смыслу действия – Свёртка:


По сути, эта штука позволяет создать невозможную в обычном Excel сводную таблицу с текстом в значениях. Например, из таблицы с данными продаж:


… можно легко получить график заказов по городам, где на пересечении даты и города будут стоять код заказа и его стоимость, т.е. своего рода диаграмму Ганта:


Если же, например, заменить дату на категорию, то можно вывести данные по всем заказам в каждый город по каждой категории (а это несколько заказов), разделённые переносом строки (Alt+Enter):


… получится:


Новые инструменты работы с текстом

В инструмент Текст на вкладки Язык и Удалить добавлены:

  • Удаление всех символов кириллицы / латиницы
  • Удаление символов из запрещенного списка
  • Удаление N-го по счёту слова
Новые функции для иерархий

Добавлены две функции для определения уровня вложенности.

Функция IndentLevel определяет количество отступов форматирования, которые так любит ставить 1С, например:


Вторая функция OutlineLevel похожа на предыдущую, но выдаёт уровень группировки для строки. Если на листе присутствуют вложенные группировки, созданные с помощью команд Данные – Группировка, то бывает полезно определить на каком уровне вложенности находится заданная строка.


Обе функции, как обычно, доступны как прямым вводом в ячейку, так и через Библиотеку Формул.

Улучшения инструмента "Курс валют"

В инструмент Курс валют в список доступных для загрузки валют добавлены Специальные права заимствования (XDR). Также курс теперь автоматически делится на размерность, т.к. курс некоторых валют на сайте ЦБ был указан не за единицу (например, курс узбекских сумов указывался за 10000, а норвежских крон за 10). Теперь всё приведено к одним единицам измерения.

Ну и, как обычно, исправлено энное количество ошибок, выполнена рутинная внутренняя оптимизация по мелочи, обновлены некоторые иконки и элементы дизайна интерфейса и т.д. Полный список всех изменений можно найти на странице Версии.

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

После распаковки архива не забудьте разблокировать файлы надстройки.
Для обновления просто замените затем старые файлы на новые в вашей папке PLEX (за исключением файла flib.xlsx, если вы сохраняли в него свои пользовательские формулы).

Новая статья: Склейка диапазонов новыми функциями VSTACK и HSTACK

Обычно для сборки данных из нескольких диапазонов в один используют либо , либо макросы или запросы Power Query. В 2022 году к этому списку добавился ещё один весьма изящный способ - новые функции VSTACK и HSTACK для вертикальной и горизонтальной склейки (stack = стопка, пачка, штабель). В русской версии эти функции получили немного странные названия ВСТОЛБИК и ГСТОЛБИК соответственно:



Особенно красиво эти функции работают с 3D-ссылками, позволяя автоматически собирать данные с любого количества листов оптом.


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

Новая статья: Динамический "Топ-N и другие" в сводной таблице

В аналитических отчетах и дашбордах весьма часто встречается потребность вывести итоговые значения в формате сводной таблицы вида "Топ-N + другие":

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


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

Новая статья: Путь к текущему файлу в Excel и новая функция LET

Как при помощи Листа Прогноза за пару минут сформировать по историческим данным прогноз на будущее по методу экспоненциального сглаживания.


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

Новая статья: Путь к текущему файлу в Excel и новая функция LET

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


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

Новая статья: Горизонтальная фильтрация столбцов в Excel

Разбираем три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.



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

Новая статья: Слушаем интернет-радио в Excel

Как за пару минут реализовать в Excel прослушивание интернет-радиостанций с помощью внедренного компонента Windows Media Player и макроса в две строки.
Спросите "зачем?"
Да просто потому, что можем!  8-)
Кроме того, полезно лишний раз потренироваться в создании элементов ActiveX и их управлении с помощью макросов.



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

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

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

 

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

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

Новая статья: Скопировать сумму выделенных ячеек в буфер

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

В новой версии 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:



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