Как быстро извлечь имена доменов из URL в Excel

Несколько хитростей и советов помогут Вам извлечь имена доменов из списка URL при помощи формул Excel. Два варианта формулы позволяют извлечь имена доменов с WWW или без, независимо от протокола URL (поддерживаются http, https, ftp и прочие). Решение работает во всех современных версиях Excel от 2003 до 2013.

Если Вы заботитесь о продвижении своего сайта (как я) или выполнении профессионального СЕО-продвижения сайтов Ваших клиентов за деньги, то часто будете сталкиваться с задачей обработки и анализа огромных списков URL: Google Analytics предоставляет отчёты о трафике, инструменты вебмастера сообщают о новых ссылках, об обратных ссылках на сайты Ваших конкурентов (которые содержат множество интересных фактов) и так далее.

Чтобы обрабатывать такие списки от десятка до миллиона ссылок, Microsoft Excel становится идеальным инструментом. Он мощный, гибкий, расширяемый и позволяет Вам отправлять отчёты клиентам прямо из листа Excel.

Почему мы называем диапазон от 10 до 1000000? – спросите Вы меня. Потому что Вам определённо не нужен инструмент для обработки меньше, чем 10 ссылок, и вряд ли он Вам нужен, если количество входящих ссылок у Вас больше миллиона. Готов поспорить, что в таком случае у Вас уже есть какое-то программное обеспечение, разработанное специально для Вас и адаптированное специально для нужд Вашего бизнеса. Плюс в этом случае я бы изучал Ваши статьи, а не наоборот 🙂

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

• Как извлечь доменные имена за 5 простых шагов
• Как обрабатывать список URL по столбцу с доменным именем
  
  • Группируем URL по доменному имени
  • Выделяем вторые и все последующие URL в общем домене
  • Проверяем две таблицы, ищем повторяющиеся значения

5 простых шагов, чтобы извлечь имена доменов из списка URL

В качестве примера давайте возьмём фрагмент отчёта об обратных ссылках, сгенерированного для сайта ablebits.com сервисом Google Webmaster Tools.

Совет: Я рекомендую пользоваться сервисом ahrefs.com, чтобы своевременно определять новые ссылки для Вашего собственного сайта и сайтов Ваших конкурентов.

1. Добавьте столбец Domain в конец Вашей таблицы.Мы экспортировали данные из файла CSV, поэтому сейчас они занимают простой диапазон. Нажмите Ctrl+T, чтобы преобразовать его в таблицу Excel, поскольку так работать будет значительно удобнее.
2. В первой ячейке столбца Domain (B2) введите формулу для извлечения имени домена:
   • Извлекаем домен с «WWW.» если он присутствует в URL:

     =MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
=ПСТР(A2;НАЙТИ(":";A2;4)+3;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3)

   • Пропускаем «WWW.» и извлекаем чистое имя домена:

     =IF(ISERROR(FIND("//www.",A2)),MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3), MID(A2,FIND(":",A2,4)+7,FIND("/",A2,9)-FIND(":",A2,4)-7))
=ЕСЛИ(ЕОШИБКА(НАЙТИ("//www.";A2));ПСТР(A2;НАЙТИ(":";A2;4)+3;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3);ПСТР(A2;НАЙТИ(":";A2;4)+7;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-7))

   

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

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

Замечание: Технически, «WWW» – это домен третьего уровня, хотя во всех нормальных сайтах «WWW.» используется как приставка к имени домена первого уровня. На заре развития интернета Вы могли сказать по телефону или в рекламе на радио: Вэ-Вэ-Вэ, Наш_Крутой_Сайт, Точка Ком, – и все прекрасно понимали и запоминали, где Вас можно найти… конечно, если имя Вашего крутого сайта было не http://www.llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch.com/ 🙂

Вам необходимо оставить все другие доменные имена 3-го уровня, иначе Вы можете перемешать ссылки с разных сайтов, например, с доменом CO.UK или из разных аккаунтов на BLOGSPOT.COM и так далее.

3. Так как мы имеем полноценную таблицу, Excel автоматически копирует формулу во все строки столбца.

Готово! У нас получился столбец с извлечёнными доменными именами.

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

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

• Кликните по любой ячейке в столбце Domain и нажмите Ctrl+Space (Пробел), чтобы выделить все ячейки в этом столбце.
• Нажмите Ctrl+C, чтобы скопировать данные в буфер обмена, затем откройте вкладку Home (Главная), нажмите кнопку выпадающего списка Paste (Вставить) и выберите вариант Value (Значения).

Обработка списка URL по столбцу с доменным именем

В этой части статьи Вы найдёте несколько советов из моего собственного опыта по дальнейшей обработке списка URL.

Группируем URL по доменному имени

1. Кликните любую ячейку в столбце Domain.
2. Отсортируйте таблицу по столбцу Domain: на вкладке Data (Данные) нажмите кнопку A-Z (А-Я).
3. Преобразуйте таблицу обратно в диапазон: кликните любую ячейку таблицы, откройте вкладку Design (Конструктор) и нажмите кнопку Convert to the range (Преобразовать в диапазон).
4. На вкладке Data (Данные) кликните иконку Subtotal (Промежуточный итог).
5. В диалоговом окне Subtotal (Промежуточные итоги) установите вот такие параметры: в строке At each change in (При каждом изменении в) – значение «Domain»; в строке Use function (Операция) – значение «Count» (Количество); в строке Add subtotal to (Добавить итоги по) – значение «Domain».
6. Нажмите ОК.

Excel создаст структуру Ваших данных в левой части экрана. Структура в нашем случае состоит из 3 уровней. То, что Вы видите на снимке экрана ниже, это развёрнутый вид или вид 3 уровня. Кликните цифру 2 в верхнем левом углу экрана, чтобы отобразить итоговые данные по доменам. Далее Вы можете нажимать знаки плюс и минус (+/-), чтобы развернуть/свернуть детализацию по каждому домену.

Выделяем цветом второй и все последующие URL в общем домене

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

Более подробно об этом методе читайте в статье Как в Excel автоматически выделить дубликаты.

Сравниваем URL из различных таблиц по столбцу доменных имён

У Вас может быть один или несколько отдельных листов Excel, в которых содержится список доменных имён. Ваши таблицы могут содержать ссылки, с которыми Вы не хотите возиться, такие как спам или домены, которые Вы уже обработали. Возможно, Вам потребуется сохранить список доменов с интересными ссылками и удалить остальные.

Например, моя задача – выделить красным цветом все домены, которые занесены в мой чёрный список спамеров.

Не теряя много времени, Вы можете сравнить Ваши таблицы и удалить ненужные ссылки. Об этом читайте в статье Как сравнить два столбца и удалить дубликаты в Excel.