Как перемножить слова в excel для директа

Обновлено: 08.01.2025

Привет! Сегодня хочу поделиться с вами способом сбора минус слов для Директа используя только Эксель, на мой взгляд метод получился очень даже рабочим и интересным, и я ни в коем случае не претендую на авторство, возможно я открыл Америку, но перейдем к делу.

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

Для примера у меня это "металлический декор", прям берем и выгружаем кейколлекотором (ну или собираем ручками, тут как вам удобнее) все что есть по этому запросу

Допустим я собирал вот так:

Вот она наша выгрузка, запросов получилось не много, 92 штуки всего)

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

Наш список фраз превращается вот в такой вот набор отдельных слов в ячейках.

Далее мы ищем и заменяем те слова которые сто процентов нам нужны, в данном случае у меня металлический декор и я убираю такие слова как: "металлический" "металлических" "металла" "металл". Лучше начать со словоформы подлиннее, что бы не наоставлять обрубков слов в ячейках.

Вот такая теперь у нас получилась табличка:

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

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

Далее выделяем и идем на вкладку "Данные" и "удалить дубликаты". В итоге получаем список уникальных слов + предлоги, которые встречаются в наших запросах. Остается только вручную пробежаться и проанализировать слова которые вы будете минусовать, и не убрать случайно что-то нужное у меня осталось всего 40 слов на минусацию, заняло минут 15.

При удалении дубликатов у вас все равно останется много словоформ других слов например: море, моря, морем и тд. Если вы уверены, что у вас не специфическая тематика, то можно воспользоваться лемматизатором слов.
Я использую для этого инструмент Арсёнкина, если не ошибаюсь он бесплатный. туда можно закинуть и значительно сократить кол-во запросов.

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

Панель инструментов !SEMTools

Новые объекты для взаимодействия

Скорость на уровне волшебства

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

Не требует прав администратора и установки

Простой интерфейс

Надстройка !SEMTools содержит множество функций и макросов.
Ниже основные действия, доступные в надстройке.

С !SEMTools можно:

Производить манипуляции с текстовыми данными

Обнаружить (найти)

Самый быстрый способ найти в огромном массиве данных нужные вам. Раздел отвечает за:

Удалить

Изменить (заменить)

Извлечь (удалить все, кроме)

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

Объединить/Комбинировать

!SEMTools умеет:
— генерировать все перестановки слов внутри ячеек (пока число не превысит 1048576)
— аналогично генерировать линейные комбинации элементов нескольких столбцов
— объединять диапазоны ячеек различными способами

И последнее, но не менее интересное:

Набор инструментов для PPC и SEO

Эта группа процедур поможет в множестве рутинных задач PPC или SEO-специалиста. Здесь:

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

Примеры с функцией ТЕКСТ в Excel
Раздел функций Текстовые
Название на английском TEXT
Волатильность Не волатильная
Похожие функции

Что делает эта функция?

Функция ТЕКСТ конвертирует число в текст в заданном числовом формате.

Это могут быть дата, время и их фрагменты, финансовые единицы, проценты.

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

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

И далее выбрать нужный тип в списке числовых форматов.

Синтаксис

Форматирование

Специального форматирования не требуется.

Логические значения не конвертируются в их численные эквиваленты, но становятся текстом.

Числа, отформатированные как текст, воспринимаются как числа.

Примеры применения функции

Пример 1

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

Специалист использовал формулу ТЕКСТ с пользовательским форматом.

Добавление дополнительных нулей к числам для обеспечения их одинаковой длины
Пример 2

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

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

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

В переводе на человеческий язык, это означает, что если в множестве А у нас, например, слова «белый» и «красный», а в множестве В «БМВ» и «Мерседес», то после декартова произведения этих двух наборов мы получим на выходе совокупность всех возможных вариантов фраз, составленных из слов обоих списков:
- белый БМВ
- красный БМВ
- белый Мерседес
- красный Мерседес
. т.е. как раз то, что нам нужно. Давайте рассмотрим пару способов решения этой задачи в Excel.

Способ 1. Формулы

Начнём с формул. Предположим, что в качестве исходных данных мы имеем три списка исходных слов в столбцах A, B и C, соответственно, причем количество элементов в каждом списке может меняться:

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

Логика здесь простая: если индекс в вышестоящей предыдущей ячейке уже дошёл до конца списка, т.е. равен количеству элементов в списке вычисленному функцией СЧЁТЗ (COUNTA) , то мы запускаем нумерацию заново. В противном случае - увеличиваем индекс на 1. Обратите особое внимание на хитрое закрепление диапазонов знаками доллара ($), чтобы можно было скопировать формулу вниз и вправо.

Теперь, когда у нас есть порядковые номера нужных нам слов из каждого списка, можно извлечь и сами слова с помощью функции ИНДЕКС (INDEX) в три отдельных столбца:

Если вы раньше не сталкивались в своей работе с этой функцией, то очень советую хотя бы по диагонали её изучить - она выручает в очень многих ситуациях и полезна ничуть не меньше (и даже больше!), чем популярная ВПР (VLOOKUP) .

Ну, а после останется только склеить построчно получившиеся фрагменты с помощью символа конкатенации (&):

. или (если у вас последняя версия Excel) с помощью удобной функции ОБЪЕДИНИТЬ (TEXTJOIN) , умеющей склеивать всё содержимое указанных ячеек через заданный символ-разделитель (пробел):

Способ 2. Через Power Query

Power Query - это мощная надстройка для Microsoft Excel, выполняющая две основные задачи: 1. загрузку данных в Excel из почти любых внешних источников и 2. всяческие трансформации загруженных таблиц. Power Query уже встроена в Excel 2016-2019, а для Excel 2010-2013 она устанавливается как отдельная надстройка (скачать можно с официального сайта Microsoft совершенно бесплатно). Если вы ещё не начали использовать Power Query в своей работе, то самое время об этом подумать, ибо преобразования типа вышеописанных делаются там легко и непринужденно, буквально за пару движений.

Сначала загрузим исходные списки как отдельные запросы в Power Query. Для этого для каждой таблицы выполним следующие действия:
1. Превратим таблицы в "умные" кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) или сочетанием клавиш Ctrl + T . Каждой таблице автоматически будет присвоено имя Таблица1,2,3. , которое, впрочем, можно при желании поменять на вкладке Конструктор (Design) .
2. Установив активную ячейку в таблицу, нажмем кнопку Из таблицы (From Table) на вкладке Данные (Data) или на вкладке Power Query (если она установлена у вас как отдельная надстройка для Excel 2010-2013).
3. В открывшемся окне редактора запросов выберем команду Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to..) и затем опцию Только создать подключение (Create only connection) . Это оставит загруженную таблицу в памяти и позволит обращаться к ней в будущем.
Если всё сделаете правильно, то на выходе в правой панели должны получиться три запроса в режиме Только подключение с именами наших таблиц:

Теперь щёлкнем правой кнопкой мыши по первому запросу и выберем команду Ссылка (Reference) , чтобы сделать его обновляемую копию, а затем добавим к данным дополнительный столбец через команду Добавление столбца - Настраиваемый столбец (Add Column - Custom Column) . В окне ввода формулы введём имя нового столбца (например, Фрагмент2) и предельно простое выражение в качестве формулы:

. т.е., другими словами, название второго запроса:

После нажатия на ОК мы увидим новый столбец, в каждой ячейке которого будет лежать вложенная таблица с фразами из второй таблицы (увидеть содержимое этих таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table):

Останется развернуть всё содержимое этих вложенных таблиц с помощью кнопки с двойными стрелками в заголовке полученного столбца и сняв при этом флажок Использовать исходное имя столбца как префикс (Use original column name as prefix) :

. и мы получим все возможные сочетания элементов из первых двух наборов:

Дальше всё аналогично. Добавляем еще один вычисляемый столбец с формулой:

…, а затем ещё раз разворачиваем вложенные таблицы – и вот у нас уже все возможные варианты перестановок слов из трёх наборов, соответственно:

Осталось выделить все три столбца слева-направо, удерживая Ctrl , и сцепить их содержимое через пробел, используя команду Объединить столбцы (Merge Columns) с вкладки Преобразование (Transform) :

Получившиеся результаты можно выгрузить обратно на лист знакомой уже командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to..) :

Если в будущем что-то изменится в наших исходных таблицах с фрагментами, то достаточно будет просто обновить созданный запрос, щёлкнув по результирующей таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или нажав сочетание клавиш Ctrl + Alt + F5 .

Читайте также:

Раздел функций	Текстовые
Название на английском	TEXT
Волатильность	Не волатильная
Похожие функции