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

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

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

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

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

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

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

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

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

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

… получится:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=Translate(Text; FromLang; ToLang)

где:

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

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

Добавлен набор инструментов для упрощения и ускорения работы с 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 и т.д.
  
• Ускорена работа инструмента В значения.