Как преобразовать несколько адресов электронной почты в гиперссылки в Excel?

Дубликаты и уникальные

Новый инструмент «Дубликаты и уникальности» был добавлен вместо старого инструмента «Извлечение уникальности»:

Имеются и другие возможности:

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

Работа с гиперссылками и адресами email

В раздел «Книга» добавлен новый инструмент для работы с гиперссылками в ячейках листа:

• «анимировать» текстовые ссылки, чтобы они стали полностью функциональными ссылками
• Удалить гиперссылки, чтобы в ячейках остался только текст (т.е. ссылки больше не работают)
• Открыть все ссылки в выбранной области в браузере в отдельных вкладках или в Outlook как новые сообщения
• Заменить текст гиперссылок на их фактический http-адрес…
• Преобразовать прямые гиперссылки в Excel функцией HYPERLINK.
• Исправить, заменив часть ссылки на другую, например, «yandx» на «yandex»
• Извлечь ссылки или адреса электронной почты из выделенной области, содержащей мешанину текста и ссылок.

Заполнение пустых ячеек во всех направлениях

Fill Blanks теперь способен делать это не только в направлении вниз, но и в других направлениях:

Вместо трех старых инструментов Фильтр и копирование/перенос/удаление (Фильтр и .) добавлен один универсальный инструмент:

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

Рабочий лист или строка текущей таблицы могут быть отфильтрованы по любому из указанных условий:

• Равен/не равен определенному значению
• Содержит/не содержит определенную подстроку
• Соответствует определенному шаблону (с использованием *#? и т.д.)

Найденные строки можно скопировать, переместить на новый лист или в книгу или удалить.

АнтиФильтр

Добавление инструмента «Анти-фильтр»:

Инвертирует все текущие отфильтрованные строки, т.е. делает строки, скрытые фильтром, видимыми, а видимые — скрытыми.

Удаление скрытых / не скрытых фильтром строк

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

Сравнение диапазонов цветом с помощью Парной подсветкой дубликатов

Усовершенствован существующий инструмент Попарное выделение дубликатов

Функция цветового кодирования теперь может использоваться для визуального сравнения совпадающих значений между двумя (или более) диапазонами:

Также увеличено с 20 до 90 количество пар цветов заливки и шрифта для подсветки найденных дубликатов.

Генерация случайных данных

Инструмент Random был обновлен новыми списками для создания тестовых данных сразу же:

• Бренды одежды
• Несуществующие названия продуктов
• Бренды чая
• Мебель
• Приборы
• Выдуманные бренды
• Цвета

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

Типы ссылок для аргументов в Библиотеке Формул

Это важное улучшение, о котором давно просили. Для пользовательских формул в библиотеке формул теперь можно указать тип ссылки (абсолютная, относительная, смешанная…) для каждого аргумента:

Удаление не-ASCII символов

В текстовом инструменте добавлена вкладка Очистить для удаления всех видов символов Unicode, не относящихся к ASCII: знаков, диакритических знаков и т. д.

Также существует пользовательская функция OnlyASCII, которая работает аналогично:

Выбора стандарта транслитерации

В инструменте «Текст» на вкладке «Другое» добавлена возможность выбора системы транслитерации для кириллицы:

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

• 0 – общепринятая
• 1 – по стандарту ИКАО (ICAO Doc 9303)
• 2 — по стандарту ГОСТ 7.79-2000 (ISO 9)
• 3 — по стандарту BGN/PCGN
• 4 — по стандарту BGN/PCGN с заменой окончаний «ий» и «ый» на «y»

Функции GetEmail и GetEmail2 для извлечения адресов эл.почты

Я добавил несколько пользовательских функций для извлечения адресов электронной почты и гиперссылок из текста:

GetEmail(cell; delimiter) — извлекает все найденные адреса электронной почты из текста исходной ячейки и выводит их в одну строку через заданный символ разделителя:

Если символ-разделитель не указан, по умолчанию используется точка с запятой и пробел. При желании можно указать любой разделитель или использовать функцию Excel CHAR с нужным кодом символа, например, CHAR(10) вставит между адресами символ перевода строки (Alt+Enter).

Также добавлена функция GetEmail2, которая выполняет то же извлечение адресов электронной почты из текста, но возвращает все результаты сразу в виде горизонтального динамического массива (работает только в Office 365 с установленными обновлениями для поддержки динамических массивов):

Если вы хотите получить результаты вертикально в столбце, вы можете использовать эту функцию в сочетании со стандартной функцией Excel ТРАНСПОЗИЯ:

Функции GetURL и GetURL2 для извлечения адресов сайтов

Эта функция выполняет те же действия, что и предыдущие, но для URL-адресов веб-сайтов:

Новые функции регулярных выражений

RegExpExtract2(Text; Template; ConsiderRegister) — работает аналогично существующей функции RegExpExtract, т.е. извлекает из исходного текста все фрагменты, соответствующие указанному шаблону регулярного выражения, но выводит все результаты сразу в виде динамического массива (работает только в Office 365 с обновлениями, поддерживающими динамические массивы).

RegExpTest(Text; Template; Match case) — проверяет соответствие текста заданному шаблону регулярного выражения. Последний аргумент указывает, учитывать ли данный случай (1) или нет (0). Результатом этой функции является FALSE или TRUE соответственно.

Функция RegExpReplace(Text; Template; New_text; ConsiderRegister) заменяет фрагменты исходного текста текстом, сопоставленным с указанным шаблоном регулярного выражения.

Среднее арифметическое по цвету заливки/шрифта

В дополнение к существующим функциям для суммирования и подсчета количества ячеек с заданным цветом заливки (SumByCellColor, CountByCellColor) и шрифтом (SumByFontColor, CountByFontColor), добавлены аналогичные функции для вычисления среднего арифметического: AverageByCellColor и AverageByFontColor.

Также во втором аргументе всех этих функций можно указать диапазон из нескольких ячеек разного цвета.

14 новых формул в Библиотеке формул

В дополнение к пользовательским макрофункциям PLEX в библиотеку формул добавлены новые полезные формулы:

Exceltip

Рабочий лист Excel можно подключить, нажав на гиперссылку на другой документ или рабочую книгу. Это может быть документ на бумажном носителе или страница в Интернете.

Создать гиперссылку для электронной таблицы можно следующими способами:

1) Непосредственно в клетке

2) Используйте объекты электронной таблицы (фигуры, диаграммы, WordArt…).

3) C помощью функции ГИПЕРССЫЛКА

4) Применение макросов

Добавление гиперссылки напрямую в ячейку

Чтобы добавить гиперссылку непосредственно в ячейку, щелкните правой кнопкой мыши в ячейке, в которой вы хотите разместить гиперссылку, и в выпадающем меню выберите Гиперссылка

Кроме того, соответствующую команду можно найти на ленте Вставка -> Ссылки -> Гиперссылки в рабочей книге.

Привязка гиперссылок к объектам рабочего листа

Также можно добавить гиперссылку на определенные объекты в рабочей книге: изображения, фигуры, подписи, объекты WordArt и диаграммы. Чтобы создать гиперссылку, щелкните объект правой кнопкой мыши и выберите в выпадающем меню пункт Гиперссылка.

Или, таким же образом, как добавление гиперссылки в ячейку, выделите объект и выберите команду на ленте. Ctrl + K открывает то же диалоговое окно при нажатии этой комбинации клавиш.

Обратите внимание, что если вы щелкните правой кнопкой мыши на графике, вы не сможете выбрать команду гиперссылки, поэтому выделите график и нажмите Ctrl + K.

Добавление гиперссылок с помощью формулы ГИПЕРССЫЛКА

Гиперссылку можно добавить с помощью функции Hyperlink, имеющей следующий синтаксис:

Адрес указывает местоположение в документе, например, конкретную ячейку или именованный диапазон. Адрес может указывать на файл на жестком диске или на страницу в Интернете.

Имя задает текст, который будет отображаться в ячейке с гиперссылкой. Этот текст будет синего цвета и подчеркнут.

Например, если я введу в ячейку формулу =ГИПЕРССЫЛКА(Лист2!A1; «Продажи»). На листе выглядеть она будет следующим образом и отправит меня на ячейку A1 листа 2.

Для перехода на страницу Интернета функция будет выглядеть следующим образом

=ГИПЕРССЫЛКА(«https://exceltip.ru/»;»Перейти на Exceltip»)

Добавление ключевого слова mailto к функции отправит письмо по указанному адресу:

Добавление гиперссылок с помощью макросов

Гиперссылки можно также создавать с помощью макросов VBA, используя следующий код

где,

SheetName: имя листа, на который должна быть помещена гиперссылка.

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

Адрес!Диапазон:Адрес ячейки, куда будет отправлена гиперссылка

Имя: текст, который будет отображаться в ячейке.

Виды гиперссылок

Когда вы добавляете гиперссылку непосредственно в ячейку (первый метод), вы работаете с диалоговым окном Вставка гиперссылки, которое предлагает 4 метода создания ссылки:

1) Файл, веб-страница — в поле навигации справа укажите файл, который вы хотите открыть при нажатии на гиперссылку

2) Расположение в документе — в этом случае гиперссылка будет отсылать нас к указанному месту в текущей рабочей книге

3) Новый документ — в данном случае Excel создает новый документ с указанным расширением в указанном месте

4) E-mail: открывает пустое окно электронной почты с адресом получателя, указанного в гиперссылке.

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

Изменить гиперссылку

Гиперссылку можно изменить, щелкнув по ней правой кнопкой мыши. В раскрывающемся меню выберите Редактировать гиперссылку.

Удалить гиперссылку

Этот же процесс можно использовать для удаления гиперссылки. Выберите Удалить гиперссылку во всплывающем меню после щелчка правой кнопкой мыши.

Вставить URL-адреса в таблицы в виде простого текста

Опцию «Сохранить только текст» можно выбрать при вставке нескольких URL-адресов в электронную таблицу, чтобы удалить форматирование гиперссылок. Скопируйте URL www.google.com, выбрав текст якоря гиперссылки и нажав «Ctrl + C». В Excel щелкните правой кнопкой мыши ячейку D3, чтобы открыть контекстное меню, показанное на скриншоте ниже.

Под опциями вставки находится значок буфера обмена. Это кнопка Сохранить только текст, которую можно выбрать, чтобы скопировать URL в ячейку без ссылки. Или нажмите маленькую стрелку на панели инструментов Excel. Кнопка «Вставить» для выбора опции «Сохранить только текст».

Для редактирования уже созданной гиперссылки, кликните по ячейке со ссылкой правой клавишей мыши и в выпадающем меню выберите «Edit Hyperlink».

В диалоговом окне выполните корректировку ссылки.

Можно ли предотвратить автоматическое создание ссылок при вводе текста? Вы можете. Вот как. Для Office 2010 и более поздних версий зайдите в «Файл», выберите «Параметры», затем «Правописание». Для Office 2007 и более ранних версий: нажмите на «Microsoft Office», выберите «Параметры», затем «Правописание». Затем нажмите на кнопку «Параметры автозамены» и откройте вкладку «Автоформат при вводе». Снимите флажок для интернет-адресов и других сетевых путей с гиперссылками. Теперь программа не выделяет и не распознает гиперссылки автоматически.

В нашей статье мы рассмотрели несколько способов создания гиперссылок в Wordboard. Мы также узнали, как их удалить/отключить и как отключить автоматическое создание гиперссылок в документе. Как вы уже поняли, ничего сложного в этом нет, главное — правильно и четко следовать алгоритму. Только так вы сможете добиться успеха. Желаем вам удачи!