Срезы в сводных таблицах можно использовать не только классическим образом - для фильтрации исходных данных, но и для переключения между различными типами вычислений в области значений:
Реализовать такое очень легко - потребуется лишь пара формул на DAX, вспомогательная таблица и сводная по модели данных Power Pivot.
Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только
Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
С завидной регулярностью (а в последнее время - всё чаще) мне пишут люди с просьбами помочь в различных вычислениях, связанных с лотереями. Кто-то хочет реализовать в Excel свой секретный алгоритм подбора выигрышных чисел, кто-то - найти закономерности в выпавших номерах прошедших тиражей, кто-то - подловить организаторов лотереи на нечестной игре.
В этой статье (и видеоуроке) мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие из которых, кстати, могут пригодиться и в более прозаических рабочих ситуациях.
Выпустил очередное плановое обновление надстройки PLEX для Microsoft Excel. Добавлено несколько весьма приятных, надеюсь, фич 
Функция онлайн-перевода Translate
Добавлена функция Translate, которая переводит содержимое указанной ячейки с одного языка на другой, используя онлайн-переводчик Google:
Синтаксис прост:
=Translate(Text; FromLang; ToLang)
где:
Подсмотреть необходимые коды языков можно на странице Google Переводчика в адресной строке после выбора нужных языков в форме:
Добавлен набор инструментов для упрощения и ускорения работы с Power Query:
Первые три команды этого списка позволяют быстро загрузить в Power Query в режиме подключения текущую «умную» таблицу, в которой сейчас стоит активная ячейка или же сразу все «умные» таблицы с листа или из книги.
Также можно быстро удалить все запросы Power Query или существующие подключения к данным (если вы, например, планируете поделиться текущим файлом с другими пользователями, но не хотите отдавать им запросы к данным).
Возможно экспортировать М-код выбранных запросов Power Query во внешние текстовые файлы и, наоборот, импортировать М-код из внешних текстовых файлов и создать запросы на их основе. Это позволяет использовать огромное количество готовых М-функций, написанных другими пользователями (см., например, ).
Команда Обновить все запросы в папке выведет на экран диалоговое окно, где можно выбрать папку и задать при необходимости критерий для отбора обновляемых запросов:
После нажатия на кнопку Обновить макрос по очереди откроет все файлы из заданной папки и обновит все или только указанные запросы Power Query в каждом файле. При необходимости можно также вывести детальный отчет по результатам обновления на новом листе:
Команда Создать таблицу параметров создаёт заготовку «умной» таблицы с параметрами, которые можно будет использовать в ваших запросах и специальную функцию для их загрузки в Power Query.
И, наконец, команда Тест скорости открывает окно, где можно протестировать ваши запросы, замерив длительность их выполнения:
Прочие изменения, улучшения и исправления ошибок
Хотите, чтобы диаграмма строилась только по данным из текущей строки, т. е. строки, куда вы поставили активную ячейку? Примерно так:
Реализовать такое очень легко - потребуется лишь две формулы и один крохотный макрос в 3 строки.
Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"
Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в абсолютном большинстве случаев). Однако, если очень хочется, то можно обойти это ограничение. Причем способ, как вы дальше увидите, весьма простой.
Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:
К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций ВЕБСЛУЖБА и ФИЛЬТР.XML.
Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.
Если вы хотя бы в общих чертах знакомы с функцией ВПР (VLOOKUP) (если нет, то сначала ), то должны понимать, что эта и другие похожие на неё функции (ПРОСМОТРХ, ИНДЕКС и ПОИСКПОЗ, ВЫБОР и т.д.) всегда выдают в качестве результата значение - число, текст или дату, которые мы ищем в заданной таблице.
Но что, если вместо значения нам хочется получить живую гиперссылку, щёлкнув по которой мы могли бы мгновенно перепрыгнуть к найденному совпадению в другой таблице, чтобы посмотреть на него в общем контексте?Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:
Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).
Сделаем это разными способами: формулами и с помощью Power Query.
Вместо старых инструментов Извлечение уникальных и Удаление дубликатов теперь будет новый - Дубликаты и уникальные:
Возможности стали на порядок мощнее:
Умеет гибко работать со списками, содержащими дубликаты и быстро находить в них уникальные и повторяющиеся элементы. Поиск можно организовать сверху-вниз или в обратном направлении. Найденные элементы можно скопировать в отдельный список, выделить цветом, очистить их ячейки или совсем удалить из списка.
Умеет:
Инструмент Заполнить пустые теперь умеет делать это не только вниз, но и в остальных направлениях:
Фильтр + действие
Вместо трёх старых инструментов Фильтровать и копировать / перенести / удалить добавлен один универсальный:
Выделяете любую ячейку с данными и запускаете - выводится вот такое диалоговое окно:
Умеет фильтровать на листе или в текущей таблице строки по любому из заданных условий:
Найденные строки можно скопировать или перенести на новый лист или в новую книгу или же удалить.
АнтиФильтрВ раздел Фильтры добавлен инструмент Антифильтр:
Выполняет инвертирование всех отфильтрованных в данный момент строк, т.е. скрытые фильтром строки делает видимыми, а видимые – скрывает.
Удаление скрытых / не скрытых фильтром строк
Добавлены два инструмента для выполнения простых, но весьма частых операций – удаления скрытых или, наоборот, не скрытых в данный момент фильтром строк:
Доработан имеющийся инструмент Парная подсветка дубликатов:
Теперь его можно использовать при выделении двух (или более) диапазонов для цветового выделения совпадающих значений и визуального сравнения:
Также увеличено с 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 новых формул в Библиотеке формул
На практике весьма часто возникают случаи, когда нам с вами нужно найти ближайшее значение в наборе (таблице) по отношению к заданному числу. Это может быть, например:
Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.
Есть несколько способов - очевидных и не очень - для решения такой задачи.
