Извлечение доменных имен из списка URL-адресов в Excel
Несколько советов и советов помогут вам получить доменные имена из списка URL-адресов с помощью формул Excel. Два варианта формулы позволяют извлекать доменные имена с www и без него. независимо от протокола URL (поддерживаются http, https, ftp и т. д.). Решение работает во всех современных версиях Excel с 2010 по 2016 год.
Если вы занимаетесь продвижением своего веб-сайта (как и я) или занимаетесь SEO на профессиональном уровне, продвигая веб-сайты клиентов за деньги, вам часто приходится обрабатывать и анализировать огромные списки URL-адресов: отчеты Google Analytics о привлечении трафика, Инструменты для веб-мастеров сообщают о новых ссылках, сообщают об обратных ссылках на веб-сайты ваших конкурентов (которые действительно содержат массу интересных фактов 😉 ) и так далее, и тому подобное.
Для обработки таких списков, от десяти до миллиона ссылок, Microsoft Excel является идеальным инструментом. Это мощное, гибкое, расширяемое приложение позволяет отправлять отчеты вашему клиенту прямо из листа Excel.
«Почему именно этот диапазон, от 10 до 1 000 000?» вы можете спросить меня. Потому что вам определенно не нужен инструмент для обработки менее 10 ссылок; и вряд ли они вам понадобятся, если у вас более миллиона внешних ссылок. Могу поспорить, что в этом случае у вас уже было специально разработанное специально для вас программное обеспечение с бизнес-логикой, специально приспособленной для ваших нужд. И именно я буду читать ваши статьи, а не наоборот 🙂
При анализе списка URL-адресов часто требуется выполнить следующие задачи: получить доменные имена для дальнейшей обработки, сгруппировать URL-адреса по доменам, удалить ссылки с уже обработанных доменов, сравнить и объединить две таблицы по доменным именам и т. д.
5 простых шагов для извлечения доменных имен из списка URL-адресов
В качестве примера возьмем фрагмент отчета об обратных ссылках на сайтеablebits.com, созданный инструментами Google для веб-мастеров.
Совет: я рекомендую использовать ahrefs.com своевременно обнаруживать новые ссылки на свой сайт и сайты конкурентов.
- Добавьте столбец «Домен» в конец таблицы.
Мы экспортировали данные из CSV файл, поэтому с точки зрения Excel наши данные находятся в простом диапазоне. Нажмите Ctrl + T, чтобы преобразовать их в таблицу Excel, потому что с ней гораздо удобнее работать.
- В первой ячейке “Домен” (B2) введите формулу для извлечения доменного имени:
- Извлеките домен с www. если он присутствует в URL:
=СРЕДНЕ(A2,НАЙТИ(“:”,A2,4)+3,НАЙТИ(” - Опустить www. и получить чистое доменное имя:
=ЕСЛИ(ОШИБКА(НАЙТИ(“//www.”,A2)), MID(A2,НАЙТИ(“:”,A2,4)+3,НАЙТИ(“, MID(A2,НАЙТИ(“:”,A2 ,4)+7,НАЙТИ(”
Вторая формула может показаться слишком длинной и сложной, но только если вы не видели по-настоящему длинных формул. Недаром Microsoft увеличила максимальную длину формул до 8192 символов в новых версиях Excel 🙂
Хорошо, что нам не нужно использовать ни дополнительный столбец, ни макрос VBA. На самом деле использовать макросы VBA для автоматизации задач Excel не так сложно, как может показаться, см. очень хорошую статью – как создавать и использовать макросы VBA. Но в данном конкретном случае они нам на самом деле не нужны, проще и быстрее использовать формулу.
Примечание. Технически www является доменом 3-го уровня, хотя со всеми нормальными веб-сайтами www. это просто псевдоним основного домена. На заре Интернета можно было сказать «двойное ю, двойное ю, двойное ю наше крутое имя точка ком» по телефону или в радиорекламе, и все прекрасно понимали и запоминали, где вас искать, конечно, если только твое крутое имя было что-то вроде www.llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch.com 🙂
Все остальные доменные имена 3-го уровня нужно оставить, иначе вы перепутаете ссылки с разных сайтов, например с доменом «co.uk» или с разных аккаунтов на blogspot.com и т.д.
- Извлеките домен с www. если он присутствует в URL:
- Так как у нас есть полноценная таблица, Excel автоматически копирует формулу во все ячейки столбца.
Сделанный! У нас есть столбец с извлеченными доменными именами.
В следующем разделе вы узнаете, как обрабатывать список URL-адресов на основе столбца «Домен».
Совет: Если позже вам может понадобиться отредактировать имена доменов вручную или скопировать результаты на другой лист Excel, замените результаты формулы значениями. Для этого выполните следующие действия:
- Щелкните любую ячейку в столбце Домен и нажмите Ctrl+Пробел, чтобы выбрать все ячейки в этом столбце.
- Нажмите Ctrl + C, чтобы скопировать данные в буфер обмена, затем перейдите к Дом нажмите кнопку “Вставитькнопку и выберите “Ценность” из выпадающего меню.
Обработка списка URL-адресов с использованием столбца Имя домена
Здесь вы найдете несколько советов по дальнейшей обработке списка URL, основанных на моем собственном опыте.
Группировать URL-адреса по домену
- Нажмите на любую ячейку в Домен столбец.
- Отсортируйте таблицу по домену: перейдите на Данные вкладку и нажмите кнопку Аризона кнопка.
- Превратите таблицу обратно в диапазон: нажмите на любую ячейку в таблице, перейдите к Дизайн вкладку и нажмите кнопку “Преобразовать в диапазон” кнопка.
- Перейти к Данные вкладку и нажмите кнопку “Промежуточный итог” значок.
- В диалоговом окне «Промежуточный итог» выберите следующие параметры: При каждом изменении в: “Домен” использовать функцию Граф и Добавить промежуточный итог к Домен.
- Нажмите «ОК».
Excel создал схему ваших данных в левой части экрана. Существует 3 уровня схемы, и то, что вы видите сейчас, — это расширенное представление или представление уровня 3. Нажмите цифру 2 в верхнем левом углу, чтобы отобразить окончательные данные по доменам, а затем вы можете нажать знаки плюс и минус (+/-), чтобы развернуть/свернуть детали для каждого домена.
Выделите второй и все последующие URL-адреса в том же домене.
В предыдущем разделе мы показали, как группировать URL-адреса по домену. Вместо группировки вы можете быстро раскрасить повторяющиеся записи одного и того же доменного имени в своих URL-адресах.
Для получения дополнительной информации см. Как автоматически выделять дубликаты в Excel.
Сравните свои URL-адреса из разных таблиц по столбцу домена.
У вас может быть один или несколько отдельных рабочих листов Excel, на которых вы храните список доменных имен. Ваши таблицы могут содержать ссылки, с которыми вы не хотите работать, например спам или домены, которые вы уже обработали. Вам также может понадобиться вести список доменов с интересными ссылками и удалять все остальные.
Например, моя задача — закрасить красным цветом все домены, находящиеся в моем спамерском черном списке:
Чтобы не тратить много времени, вы можете сравнить свои таблицы, чтобы удалить ненужные ссылки. Для получения полной информации, пожалуйста, прочитайте Как сравнить два столбца Excel и удалить дубликаты.
Лучший способ — объединить две таблицы по доменному имени.
Это самый продвинутый способ, который лично я предпочитаю.
Предположим, у вас есть отдельный лист Excel со справочными данными для каждого домена, с которым вы когда-либо работали. В этой книге хранятся контакты веб-мастеров для обмена ссылками и дата, когда ваш сайт был упомянут в этом домене. Также могут быть типы/подтипы сайтов и отдельная колонка с вашими комментариями, как на скриншоте ниже.
Как только вы получите новый список ссылок, вы сможете сопоставить две таблицы по имени домена и объединить информацию из таблицы поиска домена и листа новых URL-адресов всего за две минуты.
В результате вы получите доменное имя, категорию сайта и ваши комментарии. Это позволит вам увидеть URL-адреса из списка, которые вам нужно удалить, и те, которые вам нужно обработать.
Сопоставьте две таблицы по имени домена и объедините данные:
- Загрузите и установите последнюю версию мастера слияния таблиц для Microsoft Excel.
Этот отличный инструмент мгновенно сопоставит и объединит два листа Excel 2013-2003. Вы можете использовать один или несколько столбцов в качестве уникального идентификатора, обновить существующие столбцы на основном листе или добавить новые из таблицы поиска. Не стесняйтесь читать больше о Merge Tables Wizard на нашем сайте.
- Откройте список URL-адресов в Excel и извлеките доменные имена как описано выше.
- Выберите любую ячейку в таблице. Затем перейдите к Данные об аблебитах вкладку и щелкните значок «Объединить две таблицы», чтобы запустить надстройку.
- нажмите Следующий дважды и выберите свой рабочий лист с информацией о доменах в качестве Справочная таблица.
- Установите флажок рядом с доменом, чтобы определить его как соответствующий столбец.
- Выберите, какую информацию о домене вы хотите добавить в список URL-адресов, и нажмите «Далее».
- нажмите Заканчивать кнопка. Когда обработка завершится, надстройка покажет вам сообщение с подробностями слияния.
Всего несколько секунд – и вы сразу получите всю информацию о каждом доменном имени.
Вы можете скачать Мастер слияния таблиц для Excel, запустить его на своих данных и посмотреть, насколько он может быть полезен.
Если вы хотите получить бесплатную надстройку для извлечения доменных имен и подпапок корневого домена (.com, .edu, .us и т. д.) из списка URL-адресов, просто напишите нам комментарий. При этом укажите свою версию Excel, например, Excel 2010 64-bit, и введите свой адрес электронной почты в соответствующее поле (не беспокойтесь, он не будет отображаться публично). Если у нас будет приличное количество голосов, мы создадим такую надстройку и я дам вам знать. Заранее спасибо!
