Как сократить ссылки в excel

Обзор функций для работы с текстом

Мы подготовили обзорное видео, где собрали полезные сочетания текстовых функций в Excel и Google таблицах.

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

Скачать файлы из этой статьи

Рабочие файлы

Сохраняйте, чтобы знать, где искать ответы на похожие задачи.

Обзор полезных текстовых функций смотрите ниже. Приятного просмотра!

Как привести телефонные номера к единому формату?

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

В идеальном мире все данные в таблицах упорядочены и имеют одинаковый вид.

Что же касается реальной жизни — так данные немного в хаосе.

Посмотрите на вид хранения телефонов в одной из баз. Лишние символы – дефисы, пробелы, двоеточия, пояснительная бригада в виде надписей – номер телефона, сотовый, тел.

Согласитесь, это, во-первых, выглядит неопрятно, во-вторых – обрабатывать такие данные ой как не просто.

Поэтому, давайте договоримся, если мы и ведем свои базы в Excel или Google таблицах, то делаем это грамотно. Все номера в одном формате. В конце концов, Вы же не просто так эти номера собираете? Предполагается работа с этими номерами. Любому сотруднику будет удобнее ориентироваться в упорядоченной базе, плюс ко всему, по корректному списку номеров без проблем можно запустить массовую рассылку (например, в WhatsApp).

Ну а если Ваша база уже в легком хаосе, то самое время привести ее в порядок. Так, сочетание функций ПРАВСИМВ() и ПОДСТАВИТЬ() помогут преобразовать различные варианты записи телефона в стандартный вид.

="+7"&ПРАВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;"(";"");")";"");" ";"");"-";"");" ";"");10)

Аналогичные функции применяются к данным в Google таблице.

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

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

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

Сочетание функций для извлечения домена: =ПРАВСИМВ(B2;ДЛСТР(B2)-ПОИСК("@";B2))

Аналогичное сочетание функций работает для Google таблиц.

Как сократить длинные ссылки?

Следующая задача, немного похожа на предыдущую. Мы запускали парсер, который собрал нам ссылки на различные источники данных. По итогу, у нас имеется список полных URL.

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

Чтобы получить короткий URL воспользуемся уже знакомым сочетанием функций ПОДСТАВИТЬ(), НАЙТИ(), ДЛСТР() и ЛЕВСИМВ().

=ЛЕВСИМВ(B2;ДЛСТР(B2)-(ДЛСТР(B2)-НАЙТИ("/@";ПОДСТАВИТЬ(B2;"/";"/@";3))+1))

Аналогичное сочетание функций работает для Google таблиц.

Как перевести ФИО в сокращенный вид?

Следующая задача, скорее больше знакома менеджерам, ведущим клиентскую базу или эйчарам. Есть полная база ФИО и дата рождения.

Хотим получить сокращенную форму ФИО, а также возраст сотрудника и по возможности определить пол.

Получить сокращенную форму ФИО очень просто. Для этого используются функции СЦЕПИТЬ() и ЛЕВСИМВ().

=СЦЕПИТЬ(B2;" ";ЛЕВСИМВ(C2;1);".";ЛЕВСИМВ(D2;1);".")

Как вычислить возраст по дате рождения?

Для вычисления возраста по дате рождения используется функция РАЗНДАТ(). История с функцией РАЗНДАТ() очень интересная. Вы не найдете её в справке. Более того, её нет в мастере функций, и даже при наборе вручную первых букв названия функции никакой подсказки вам не выйдет!

=РАЗНДАТ(E2;СЕГОДНЯ();"y")

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

Чтобы ей воспользоваться, задаем начальную дату, конечную дату и в скобках указываем в каких именно единицах нужно вернуть разницу дат. В нашем случае – число полных лет в периоде.

В Google таблицах используются аналогичные функции.

Как определить пол по отчеству?

Определить пол на 100% мы вряд ли сможем, особенно если у нас нестандартные варианты отчества, но для большей части данных – это реально. Так, самым простым вариантом, является проверка последней буквы отчества.

=ЕСЛИ(ПРАВСИМВ(D2)="ч";"М";ЕСЛИ(ПРАВСИМВ(D2)="а";"Ж";"н/о"))

Если отчество заканчивается на букву Ч, Антонович, Арсеньевич – то пол мужской, если отчество заканчивается на букву А – Сергеевна, Валерьевна – то пол женский, в противном случае пол не определен.

Это один из вариантов. Конечно, можно добавить проверку и по другим критериям. Например, добавить развернутый список женских и мужских имен и проверять имена по нему. В общем, вариант есть и не один, все зависит от конкретной задачи.

В Google таблицах все работает аналогично.

И, напоследок, суперлегкая, но все также часто запрашиваемая задача.

Получили «корявую» выгрузку с нестандартным количеством пробелов. Некорректный ручной ввод или баги выгрузки.

Удалить лишние пробелы и, тем самым привести список к корректному виду поможет функция сжатия пробелов — СЖПРОБЕЛЫ().

Аналогичная функция доступна в Google таблицах.

Почему знания Excel или Google таблиц сегодня важны каждому?

В век развития технологий и инноваций, умение работать с большими объемами данных становится преимуществом в глазах потенциальных работодателей.

Электронные таблицы вроде MS Excel, Google Sheets – универсальные и многофункциональные помощники, способные облегчить работу и сэкономить ваше время. С их помощью, можно без труда структурировать большие объемы информации в удобные форматы, визуализировать данные в графики и диаграммы, а также вычислять показатели и создавать отчеты.

Умение работать с электронными таблицами позволяет уверенно использовать навыки на практике в любой профессиональной области. Бухгалтеры производят анализ информации со сводными таблицами, составляют отчеты. Финансовые аналитики и экономисты проводят исследования рынка и создают прогнозы продаж, составляют наглядные дашборды. С помощью табличных редакторов, аналитики и администраторы баз данных производят создание и заполнение базы, фильтруют массивы информации по различным категориям.

Пользователи Microsoft Excel и Google таблиц уверенно решают различные многофункциональные задачи, оптимизируя рабочее время. Инструменты этих программ просты и понятны в использовании, при этом приносят колоссальное количество пользы.

О проекте BIRDYX

Мы оказываем помощь в получении полного представления о работе в программах MS Excel и Google Sheets.

На нашем YouTube канале мы регулярно публикуем видео о секретах, фишках и лайфхаках MS Excel и Google Sheets, а также делимся решениями реальных задач из практики. Там же доступен бесплатный курс «Основы Excel» для начинающих. Подписывайтесь на наш канал, там много полезной и интересной информации.

Тем, кто желает освоить MS Excel или Google Sheets в кратчайшие сроки, мы можем предложить персональные консультации онлайн в удобное время.

А также, Вы можете пройти бесплатные онлайн курсы по MS Excel с заданиями

Мы выполняем задачи любой сложности в Excel и Google таблицах: сложные формулы, дашборды, макросы и многое другое.

Резюме: utm-метки для маркировки рекламных кампаний помогают узнать какие кампании были успешными, какая из соцсетей работает качественно и приводит посетителей на сайт, какое из рекламных объявлений сработало лучше. Метки удобно использовать в qr-кодах, в описания на YouTube.

Содержание

— Что такое UTM-метки?
— Когда стоит использовать UTM-метки?
— Кто может использовать UTM-метки?
— Как создать utm-метку самостоятельно?
— Преимущества utm-меток на своем домене — адресом своего сайта

• Преимущество 1. Короткая ссылка вызывает больше доверия к сайту и бренду
• Преимущество 2. Короткую ссылку всегда можно отредактировать
• Преимущество 3. Лёгкое создание и хранение длинных ссыло
• Преимущество 4. Экономия времени редактора сайта
• Преимущество 5. Статистика для маркетолога
• Преимущество 6. Наши ссылки не блокируются

— Заключение

Что такое UTM-метки?

UTM-метка (от англ.: Urchin Tracking Module) — переменная, которая добавляется в URL-адрес рекламируемой страницы и позволяет через систему веб-аналитики узнать, откуда пришел посетитель.

Когда стоит использовать UTM-метки?

• для маркировки объявлений рекламных кампаний в Яндексе и Google: так вы поймете, по каким объявлениям приходят посетители на сайт
• для маркировки постов в соцсетях: так вы поймете, переходят ли читатели на ваш сайт
• для маркировки объявлений на таргет в соцсетях: так вы поймете, читает ли и какие действия совершает аудитория в соцсетях
• для маркировки писем email-рассылок
• для маркировки коммерческих сообщений во внешних источниках

Кто может использовать UTM-метки?

• маркетолог, интернет-маркетолог, продукт-менеджер
• контент-менеджер, smm-менеджер, email-менеджер

Как создать utm-метку самостоятельно?

Обычно маркетологи пользуются сторонними сервисами — специальными программами для генерации utm-меток. Эти сервисы генерируют «длинные» и «короткие» ссылки. Длинная ссылка содержит все utm-метки, но выглядит громоздко из-за своей длины, поэтому для конечной публикации сервисы сокращают ссылку (генерируют короткую ссылку).

Основные параметры UTM-меток:
utm_source — источник перехода
utm_medium — тип трафика
utm_campaign — название рекламной кампании
utm_content — тип информации, которая помогает различать объявления
utm_term — ключевая фраза

Параметры utm_source, utm_medium, utm_campaign — обязательные, а utm_content, utm_term — необязательные. 

Самостоятельно в эксель-файле

Используя параметры, указанные выше, можно создавать метки самостоятельно, в файле. Например, тут показан принцип создания длинных utm-меток. Но этот метод потребует использовать сервисы по сокращению ссылок, как ниже.

Генерация коротких ссылок бесплатными сервисами

Популярные бесплатные сервисы по генерации utm-меток: bitly.com, vk.cc, tilda.cc

Генерация utm-меток на платформе ADX CMS

Мы даем клиентам модуль генератор utm-меток и их «укорачивания» на платформе Adx CMS.

Автоматическая генерация коротких ссылок на собственном сайте имеет свои плюсы в продвижении брендового запроса бизнеса. А теперь давайте поговорим на эти темы подробнее.

Преимущества utm-меток на своем домене — адресом своего сайта

№1. Короткая ссылка вызывает больше доверия к сайту и бренду

Сервисы генерации коротких ссылок, такие, к примеру, как vk.cc и bitly.com, скрывают адрес вашего сайта, назначая им свое доменное имя, и многие пользователи по этим ссылкам не переходят. По какой причине? Они не видят, не понимают, куда их уведет такая ссылка, не доверяют ей. При наведении на подобную ссылку, случается, строка обрезается, не всегда всплывает полный адрес длинной ссылки (и не все пользователи знают такой способ проверки).

В целом, vk.cc и bitly.com и другие похожие ресурсы удобны лишь тогда, когда нужно просто сократить длинную ссылку, а инструментов подходящих нет.

С помощью же встроенного в сайт «генератора utm-меток» получится вот такая короткая ссылка:

https://ИмяСайта.ru/utm-метка/

По собственной брендированной ссылке видно, куда нужно перейти, так как ее основа — адрес вашего сайта.

№2. Короткую ссылку всегда можно отредактировать

Генераторы коротких ссылок в интернете задают произвольные окончания своим ссылкам (8 символов в конце), и их нельзя редактировать. Выглядят такие ссылки непривлекательно.

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

Например, в блоге есть статья про SEO в условиях кризиса. Генератор utm-меток создает длинную utm-метку и на ее основе — короткую ссылку как произвольно, так и с заданным именем (в пределах 8 символов): сокращенное название контента и дополнительная пометка, где будет размещена ссылка, например в FaceBook*:

Исходная ссылка без utm-меток: 
https://online-media.ru/blog/marketing-i-seo-v-usloviyah-krizisa/

Длинная ссылка с utm-метками:
https://online-media.ru/blog/marketing-i-seo-v-usloviyah-krizisa/? utm_source=fb&utm_medium=organic& utm_campaign=2020–04–17&utm_content=blog&utm_term=seo-krisis

Короткая ссылка для размещения на FaceBook*:
https://online-media.ru/s/SEOkrsfb/

№3. Лёгкое создание и хранение длинных ссылок

№4. Экономия времени редактора сайта

Если вам срочно нужна utm-метка, вы просто заходите в Adx CMS в нужный раздел сайта, ставите ссылку и после нескольких нажатий и пары напечатанных слов получаете сразу и длинную, и короткую ссылки. Создание ссылки занимает 2-3 минуты.

Если вы ошиблись, то не нужно всё делать заново: полученные ссылки можно редактировать и пересохранять при обнаружении ошибок.

№5. Статистика для маркетолога

Статистика переходов собирается в АдминПанели и доступна для маркетолога в реальном режиме времени.

№6. Наши ссылки не блокируются

Например, сервисы фейсбука блокируют ссылки, созданные с помощью vk.cc, и не позволяют разместить пост, пока ссылку не заменят.

Заключение

Генератор utm-меток на платформе adx CMS позволяет:

• брендировать ссылки с utm-метками
• быстро генерировать ссылки с utm-метками
• сокращать любые ссылки
• хранить все сгенерированные utm-метки в одном месте
• получать статистику переходов 

Еще статьи по теме:

• Регулярные выражения (regexp) для простых операций 
• Как сделать внешнюю ссылку на сайт в 2020 году
• Краткий обзор: Интернет в цифрах в 2008 г.
• Интернет и спрос
• Экспресс-методика оценки конкурентов

*Facebook — принадлежит компании Meta, которую признали экстремистской, запрещен в РФ.

Гиперссылки широко используются в Интернете для навигации по сайтам и документам. Работая с файлами Excel вы также можете создавать гиперссылки, как на интернет-ресурсы, так и на ячейки, файлы или форму отправку Email.

Гиперссылка в Excel это ссылка, нажав на которую, пользователь может быть перемещен на конкретную ячейку, документ или интернет-страницу.

Excel позволяет создавать гиперссылки для:

• Перехода в определенное место в текущей книге;
• Открытия другого документа или перехода к определенному месту в этом документе, например лист в файле Excel или закладке в документе Word;
• Перехода на веб страницу в Интернете;
• Создания нового файла Excel;
• Отправки сообщения электронной почты по указанному адресу.

Гиперссылку в Excel легко заметить, она выглядит как подчеркнутый текст, выделенный синим цветом:

В Excel существует два типа гиперссылок: абсолютные и относительные.

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

«https://excelhack.ru/funkciya-rept-povtor-v-excel/»

C:DesktopПрезентации

Относительные ссылки содержат в себе частичный путь, например:

funkciya-rept-povtor-v-excel/

DesktopПрезентации

Я рекомендую всегда использовать абсолютные ссылки, так как при переходе по относительным ссылкам в Excel файле, открытом на другом компьютере возможны ошибки.

Чтобы создать гиперссылку проделайте следующие шаги:

• Выделите ячейку, в которой вы хотите создать гиперссылку;
• Нажмите правую клавишу мыши;
• В выпадающем меню выберите пункт «Ссылка»:

• В диалоговом окне выберите файл или введите веб-адрес ссылки в поле «Адрес»:

• Нажмите «ОК»

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

• На другой документ;
• На веб-страницу;
• На конкретную область в текущем документе;
• На новую рабочую книгу Excel;
• На окно отправки Email.

Чтобы указать гиперссылку на другой документ, например Excel, Word или Powerpoint файлы:

• Откройте диалоговое окно для создания гиперссылки;
• В разделе «Связать с» выберите «Файлом, веб-страницей»;
• В поле «Искать в» выберите папку, где лежит файл, на который вы хотите создать ссылку;
• В поле «Текст» введите текст, который будет отображаться в качестве ссылки;
• Нажмите «ОК».

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

Чтобы указать гиперссылку веб-страницу:

• Откройте диалоговое окно для создания гиперссылки;
• В разделе «Связать с» выберите пункт «Файлом, веб-страницей»;
• Нажмите на кнопку «Интернет»;
• Введите адрес веб-страницы в поле «Адрес»;
• В поле «Текст» укажите текст, отображаемый в виде ссылки.

Для создания гиперссылки на конкретный лист текущего файла Excel или ячейки:

• Откройте диалоговое окно для создания гиперссылки;
• В левой колонке диалогового окна под надписью «Связать с» выберите «Файлом, веб-страницей»;
• В диалоговом окне нажмите кнопку «Закладка…» и выберите лист создания ссылки. В поле «Введите адрес ячейки» укажите ячейку.

Для вставки гиперссылки, после нажатия на которую будет создан новый Excel-файл:

• Откройте диалоговое окно для создания гиперссылки;
• В левой колонке диалогового окна под надписью «Связать с» выберите «Новый документ»;
• В поле «Текст» укажите текст ссылки;
• В поле «Имя нового документа» укажите название нового Excel файла;
• В поле «Путь» укажите место хранения, где будет сохранен новый файл;
• В поле «Когда вносить правку в новый документ» укажите настройку, когда следует приступить к редактированию нового файла после нажатия ссылки.
• Нажмите кнопку «ОК»

Для вставки гиперссылки, после нажатия на которую будет создан e-mail:

• Откройте диалоговое окно для создания гиперссылки;
• В левой колонке диалогового окна под надписью «Связать с» выберите «Электронная почта»;
• В поле «Текст» укажите текст ссылки;
• В поле «Адрес эл. почты» укажите E-mail адрес, куда будет отправлено письмо;
• В поле «Тема» укажите тему создаваемого письма;
• Нажмите кнопку «ОК»

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

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

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

• Перейдите на вкладку панели инструментов «Главная», затем в раздел «Стили ячеек»:

• Кликните на «Гиперссылка» правой кнопкой мыши и выберите пункт «Изменить» для редактирования формата ссылки:

• Кликните на «Открывавшаяся гиперссылка» правой кнопкой мыши и выберите пункт «Изменить» для редактирования формата ссылки;
• В диалоговом окне «Стили» нажмите кнопку «Формат»:

•  в диалоговом окне «Format Cells» перейдите на вкладки «Шрифт» и/или «Заливка» для настройки формата ссылок:

• Нажмите кнопку «ОК».

Удаление гиперссылки осуществляется в два клика:

• Нажмите правой клавишей мыши на ячейки со ссылкой;
• В выпадающем меню выберите пункт «Удалить гиперссылку».