Процедуры из статьи доступны в !SEMTools Pro.
Список марок и моделей авто нужен всякий раз, когда в тексте надо отыскать «toyota camry», «kia rio» или «лада веста»: в каталоге автотоваров, в семантике, в отзывах, в заявках из CRM. В !SEMTools такой список уже встроен: больше 2500 написаний марок и моделей, латиницей и кириллицей, с разговорными вариантами и падежными формами. По нему можно в пару кликов найти ячейки с маркой, удалить марки и модели из текста или извлечь их в отдельный столбец.
Целиком список можно посмотреть в Google-таблице. Скачать его бесплатно, честно скажу, нельзя: он входит в надстройку, и я регулярно его пополняю. Собрать такой справочник с нуля, со всеми «Хундай» и «Ладу», — работа не на один вечер, а на недели, поэтому он и доступен в составе !SEMTools.
Что входит в список марок и моделей авто
Это не перечень брендов из десяти строк, а словарь того, как марки и модели реально пишут люди в поиске, объявлениях и отзывах:
| Что в списке | Сколько | Примеры |
|---|---|---|
| Всего написаний | 2502 | Toyota, Camry, Land Cruiser Prado 150, Солярис |
| Латиницей | 985 | Kia, Rio, BMW, X5, Focus, Polo |
| Кириллицей | 1550 | Киа, Рио, БМВ, Фокус, Поло, Гранта |
| Разговорные и ошибочные варианты | — | Кия, Хундай, Хендай, Тигго ФЛ |
| Падежные формы | — | Лады, Ладу, Приоры, Приору, Соляриса, Крету |
| Модели из 2–4 слов | 1342 | Land Cruiser Prado 90, Веста Кросс СВ, Рио Икс Лайн |
Падежи — главное преимущество перед самодельным списком. Люди пишут «чехлы на соляриса» и «коврики для лады весты», и поиск по слову «Солярис» такие фразы пропустит. В списке !SEMTools эти формы уже есть, поэтому находится и «Соляриса», и «Ладу».
Зачем искать марки авто в тексте
Задачи, с которыми ко мне приходили чаще всего:
- Интернет-магазин автотоваров — разметить каталог: какие позиции универсальные («коврики резиновые»), а какие для конкретной модели («коврики toyota camry»).
- Контекстная реклама — разнести семантику по группам объявлений: под каждую модель свой текст и своя посадочная. И наоборот, в кампании на универсальные товары марки становятся минус-словами, иначе объявление «щётки стеклоочистителя 600 мм» покажется по «щётки ford focus».
- SEO — собрать структуру разделов «по марке»: сколько запросов приходится на каждую модель и для каких нужны отдельные страницы.
- Аналитика отзывов и обращений — посчитать, о каких машинах пишут клиенты, не читая тысячи сообщений.
Подробнее о том, как такие списки используются в рекламе, — в статье 23 списка минус-слов для Яндекс Директа.
Найти марки авто в ячейках
Процедура отвечает на вопрос «есть ли в ячейке марка или модель?» и возвращает ИСТИНА или ЛОЖЬ:
- Выделите столбец с фразами.
- Откройте ОБНАРУЖИТЬ → Фразы → Найти маркеры… → Тематические → Марки и модели авто.
- Готово: у каждой фразы ИСТИНА, если в ней есть хотя бы одна марка или модель, и ЛОЖЬ, если нет.
Регистр не важен: «TOYOTA», «Toyota» и «toyota» находятся одинаково. Слова сравниваются целиком, поэтому «рио» внутри «рионит» марку не даст. Дальше по столбцу с ИСТИНА удобно отфильтровать таблицу и разнести фразы по группам. Если исходный столбец нужно сохранить, включите на панели флажок «Выводить результат справа».
Удалить марки и модели авто из текста
Обратная задача: получить «чистые» фразы без марок, например чтобы сгруппировать запросы по типу товара, а не по машине.
- Выделите ячейки.
- Откройте УДАЛИТЬ → Фразы → Удалить маркеры… → Тематические → Марки и модели авто.
- Марки и модели удалятся прямо в ячейках, лишние пробелы между оставшимися словами схлопнутся.
Длинные названия процедура удаляет целиком: из «амортизаторы toyota land cruiser prado 150» останутся только «амортизаторы», а не «амортизаторы 150». Удаление работает во всех выделенных столбцах сразу.
Извлечь марку и модель авто в отдельный столбец
Когда нужна не отметка, а сама марка — для сводной таблицы или фильтра, — её извлекают:
- Выделите столбец с фразами.
- Откройте ИЗВЛЕЧЬ → Фразы → Маркеры… → Тематические → Марки и модели авто.
- В ячейках останутся найденные марки и модели; если их несколько, они разделяются обратной косой чертой «\».
| Фраза | Результат извлечения |
|---|---|
| коврики в салон toyota camry | Toyota\Camry |
| чехлы kia rio 2020 | KIA\Rio |
| тормозные колодки bmw x5 | BMW\X5 |
| чехлы на соляриса | Соляриса |
| коврики для лады весты | Лады\Весты |
| коврики в салон резиновые | (пусто) |
Марка возвращается в том написании, в каком она записана в списке: «toyota» превращается в «Toyota», «kia» — в «KIA». Падежная форма сохраняется («Соляриса»). Чтобы свести формы к одной, примените после извлечения лемматизацию или массовую замену. Разделить «Toyota\Camry» на два столбца поможет «Текст по столбцам» с разделителем «\».
Найти марки авто формулой по своему списку
Если надстройки нет, то же можно сделать формулой, но список придётся собрать самостоятельно. Пусть он лежит в F2:F7. Проверка, есть ли в фразе слово из списка, и извлечение найденных слов в Excel 365:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Фраза | Есть марка? | Какие | Список | ||
| 2 | коврики в салон toyota camry | ИСТИНА=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A2&" ")))>0 | toyota\camry=ОБЪЕДИНИТЬ("\";ИСТИНА;ФИЛЬТР($F$2:$F$7;ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A2&" "));"")) | toyota | ||
| 3 | чехлы kia rio 2020 | ИСТИНА=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A3&" ")))>0 | kia\rio=ОБЪЕДИНИТЬ("\";ИСТИНА;ФИЛЬТР($F$2:$F$7;ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A3&" "));"")) | camry | ||
| 4 | фаркоп lada vesta | ИСТИНА=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A4&" ")))>0 | lada\vesta=ОБЪЕДИНИТЬ("\";ИСТИНА;ФИЛЬТР($F$2:$F$7;ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A4&" "));"")) | kia | ||
| 5 | коврики в салон резиновые | ЛОЖЬ=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A5&" ")))>0 | =ОБЪЕДИНИТЬ("\";ИСТИНА;ФИЛЬТР($F$2:$F$7;ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A5&" "));"")) | rio | ||
| 6 | чехлы на соляриса | ЛОЖЬ=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A6&" ")))>0 | =ОБЪЕДИНИТЬ("\";ИСТИНА;ФИЛЬТР($F$2:$F$7;ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A6&" "));"")) | lada | ||
| 7 | vesta |
Пробелы вокруг слов (" "&F2&" ") нужны, чтобы искать слово целиком: без них «rio» нашлось бы и внутри «rionit». «Соляриса» не нашлась: в своём списке нет ни модели, ни её падежных форм.
В английском Excel: =SUMPRODUCT(--ISNUMBER(SEARCH(" "&$F$2:$F$7&" "," "&A2&" ")))>0
Формула СУММПРОИЗВ проверяет каждое слово списка функцией ПОИСК, а вторая собирает найденные слова функциями ФИЛЬТР и ОБЪЕДИНИТЬ. Оговорюсь сразу о двух ограничениях. Первое — сам список: 6 моделей набрать легко, 2500 написаний с падежами и ошибками — нет. Второе — скорость: 10 000 фраз на 2500 слов списка — это 25 млн проверок, и пересчёт такой книги заметно тормозит. А удалить марки формулой без регулярных выражений и вовсе не выйдет.
Почему марка не находится или находится лишнее
- Знак препинания вплотную к слову: «toyota camry,» или «(kia)». Процедура делит текст по пробелам, поэтому «camry,» с запятой не совпадёт с «Camry». Сначала удалите знаки препинания.
- Дефис или слитное написание: «CR-V» и «CRV» в списке записаны как «CR V». Замените дефисы на пробелы или добавьте свой вариант через массовую замену.
- Новинки рынка. Список пополняется, но самые свежие модели могут появиться в нём не сразу. Чтобы получить последнюю версию, выберите в том же меню «Обновить все списки».
- Омонимы. «Поло» бывает футболкой, «Газель» — животным, «Нива» и «Калина» — обычными словами, «Ока» — рекой. На фразах не из автотематики проверяйте результат, а ещё лучше — извлекайте маркеры: сразу видно, какое слово сработало.
- Поиск и извлечение смотрят только первый столбец выделения. Удаление обрабатывает все выделенные столбцы.
В бесплатной версии процедуры работают с ограничением: в части ячеек вместо результата будет надпись «Доступно в полной версии !SEMTools».
Список марок и моделей авто — частые вопросы
Где взять список марок и моделей авто для Excel?
Посмотреть список можно в Google-таблице по ссылке из статьи. Бесплатно он не скачивается: список встроен в !SEMTools Pro и регулярно обновляется.
Сколько марок и моделей в списке?
Больше 2500 написаний: латиница и кириллица, разговорные варианты и падежные формы.
Как найти в Excel ячейки с маркой машины?
В !SEMTools: ОБНАРУЖИТЬ → Фразы → Найти маркеры… → Тематические → Марки и модели авто. Формулой по своему списку: =СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(" "&$F$2:$F$7&" ";" "&A2&" ")))>0.
Как удалить марки авто из запросов?
Процедурой УДАЛИТЬ → Фразы → Удалить маркеры… → Тематические → Марки и модели авто. Длинные названия моделей удаляются целиком.
Учитываются ли падежи?
Да, для популярных моделей в списке есть падежные формы: «Лады», «Ладу», «Соляриса», «Крету».
Найти, удалить или извлечь марки и модели авто в тысячах фраз можно без формул и без собственного справочника: список на 2500 написаний уже встроен в !SEMTools и обновляется одной кнопкой.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: список марок и моделей авто в Excel
| Задача | Процедура !SEMTools |
|---|---|
| Есть ли в фразе марка | ОБНАРУЖИТЬ → Фразы → Найти маркеры… → Тематические → Марки и модели авто |
| Убрать марки из фраз | УДАЛИТЬ → Фразы → Удалить маркеры… → Тематические → Марки и модели авто |
| Марку в отдельный столбец | ИЗВЛЕЧЬ → Фразы → Маркеры… → Тематические → Марки и модели авто |
| Свежая версия списка | Обновить все списки (в том же меню) |
Надеюсь, на этой странице вы нашли то, что искали. Формула по своему списку подойдёт, если моделей десяток и падежи не важны. Для реальных выгрузок — каталога, семантики, отзывов — быстрее встроенный список: не нужно ни собирать справочник, ни протягивать формулы на тысячи строк. Другие готовые списки — города, имена, цвета, минус-слова — собраны в разделе готовые списки слов для Excel.