
Решить проблему можно весьма изящно - используя надстройку Power Query, которая умеет собирать таблицы даже с разной структурой:
Вместо старых инструментов Извлечение уникальных и Удаление дубликатов теперь будет новый - Дубликаты и уникальные:
Возможности стали на порядок мощнее:
Умеет гибко работать со списками, содержащими дубликаты и быстро находить в них уникальные и повторяющиеся элементы. Поиск можно организовать сверху-вниз или в обратном направлении. Найденные элементы можно скопировать в отдельный список, выделить цветом, очистить их ячейки или совсем удалить из списка.
Работа с гиперссылками и адресами email
Умеет:
Инструмент Заполнить пустые теперь умеет делать это не только вниз, но и в остальных направлениях:
Фильтр + действие
Вместо трёх старых инструментов Фильтровать и копировать / перенести / удалить добавлен один универсальный:
Выделяете любую ячейку с данными и запускаете - выводится вот такое диалоговое окно:
Умеет фильтровать на листе или в текущей таблице строки по любому из заданных условий:
Найденные строки можно скопировать или перенести на новый лист или в новую книгу или же удалить.
АнтиФильтрВ раздел Фильтры добавлен инструмент Антифильтр:
Выполняет инвертирование всех отфильтрованных в данный момент строк, т.е. скрытые фильтром строки делает видимыми, а видимые – скрывает.
Удаление скрытых / не скрытых фильтром строк
Добавлены два инструмента для выполнения простых, но весьма частых операций – удаления скрытых или, наоборот, не скрытых в данный момент фильтром строк:
Доработан имеющийся инструмент Парная подсветка дубликатов:
Теперь его можно использовать при выделении двух (или более) диапазонов для цветового выделения совпадающих значений и визуального сравнения:
Также увеличено с 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 новых формул в Библиотеке формул
На практике весьма часто возникают случаи, когда нам с вами нужно найти ближайшее значение в наборе (таблице) по отношению к заданному числу. Это может быть, например:
Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.
Есть несколько способов - очевидных и не очень - для решения такой задачи.