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

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

Извлекаем имя домена при помощи Excel

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

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

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

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

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

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

Извлекаем имя домена при помощи Excel

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

  1. Добавьте столбец Domain в конец Вашей таблицы.Мы экспортировали данные из файла CSV, поэтому сейчас они занимают простой диапазон. Нажмите Ctrl+T, чтобы преобразовать его в таблицу Excel, поскольку так работать будет значительно удобнее.

    Извлекаем имя домена при помощи 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))

    Извлекаем имя домена при помощи Excel

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

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

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

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

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

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

Извлекаем имя домена при помощи Excel

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

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

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

Обработка списка 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».Извлекаем имя домена при помощи Excel
  6. Нажмите ОК.

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

Извлекаем имя домена при помощи Excel

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

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

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

Извлекаем имя домена при помощи Excel

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

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

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

Извлекаем имя домена при помощи Excel

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

Добавить комментарий

2 + шесть =

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: