Анализ N-грамм в Excel

Для поисковой рекламы и SEO анализ n-грамм — один из самых эффективных методов. Однако долгое время n-gram анализ оставался в силу сложности реализации алгоритма доступен только крупным агентствам с программистами в штате, или продвинутым специалистам со знанием программирования. Чтобы популяризовать подход и сделать его доступным всем, у кого есть Windows и Excel, инструменты для анализа…

https://semtools.guru/ru/sem-seo-tools/n-gram-analysis/

Небольшое обновление надстройки:
- Появилась процедура удаления дублирующихся слов внутри ячейки с учетом словоформ. "Дешевые платья дешево" станут "дешевые платья".
- Функционал парсинга метатегов стал доступен в бесплатной версии. Инструкция скоро.
https://semtools.guru/ru/deletion-tools/delete-words/delete-repeating-words/

Обновление 9.11.14!
Сегодня появились 8 новых макросов, по 2 в каждой группе.
Все находятся в меню "ячейки". (обнаружить ячейки по повторяемости, удалить ячейки по повторяемости и т.д.)
Теперь можно выделять диапазон и моментально находить, удалять, извлекать и заменять своим значением ячейки по признаку "повторяемость".
Вариантов 2 - ячейка либо уникальна (встречается в диапазоне 1 раз), либо повторяющаяся.
В маркетинге актуально при анализе клиентской базы и RFM-анализе.
Обновляемся!
https://semtools.guru/ru/download/

Я люблю анализировать поисковые запросы, в том числе приходящие на мой собственный сайт. Поисковые запросы часто дают множество инсайтов о реальных потребностях посетителя. Зная инсайты, можно дать пользователю понимание, как ее решить.
Вот, например, реальный поисковый запрос:
как проверить русских словах найдены латинские символы или обнаружены русские буквы в словах на латинице!
Я понимаю, почему в конце восклицательный знак. Этот человек перелопатил уже полрунета, чтобы найти эту функцию.
В !SEMTools есть такая возможность, но даже она не была до сегодняшнего дня четко описана.
А можно это сделать с помощью регулярных выражений. Спойлер, регулярочка:
([a-zA-Z].*[а-яА-ЯёЁ]|[а-яА-ЯёЁ].*[a-zA-Z])
Как найти такие слова, так и извлечь их.
Читаем, применяем:
https://semtools.guru/ru/finding-tools/find-symbols/find-latin/

Лучшая формула транслита в Excel без использования VBA и надстроек задействует 64 уровня вложенности - это максимальное возможное количество уровней вложенности в Excel. И вот она:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;»А»;»A»);»Б»;»B»);»В»;»V»);»Г»;»G»);»Д»;»D»);»Е»;»E»);»Ё»;»Yo»);»Ж»;»Zh»);»З»;»Z»);»И»;»I»);»Й»;»Y»);»К»;»K»);»Л»;»L»);»М»;»M»);»Н»;»N»);»О»;»O»);»П»;»P»);»Р»;»R»);»С»;»S»);»Т»;»T»);»У»;»U»);»Ф»;»F»);»Х»;»Kh»);»Ц»;»Ts»);»Ч»;»Ch»);»Ш»;»Sh»);»Щ»;»Sch»);»Ы»;»Y»);»Э»;»E»);»Ю»;»Yu»);»Я»;»Ya»);»а»;»a»);»б»;»b»);»в»;»v»);»г»;»g»);»д»;»d»);»е»;»e»);»ё»;»yo»);»ж»;»zh»);»з»;»z»);»и»;»i»);»й»;»y»);»к»;»k»);»л»;»l»);»м»;»m»);»н»;»n»);»о»;»o»);»п»;»p»);»р»;»r»);»с»;»s»);»т»;»t»);»у»;»u»);»ф»;»f»);»х»;»kh»);»ц»;»ts»);»ч»;»ch»);»ш»;»sh»);»щ»;»sch»);»ъ»;»y»);»ы»;»y»);»ь»;»»);»э»;»e»);»ю»;»yu»);»я»;»ya»)
Но если установить !SEMTools, можно пользоваться простым аналогом: =Translit(A1)
Подробнее о преимуществах использования обычных формул, UDF, макросах и других методах транслитерации в !SEMTools:
https://semtools.guru/ru/change-replace-tools/change-symbols/cyr-lat-lat-cyr/

Для пользователей Office 2016, у кого возникают проблемы при запуске функций надстройки, есть решение. Нужно пройти процедуру восстановления пакета. Лицензия при этом не слетает. Восстанавливать нужно по сети. После восстановления все работает. Что там может быть не так в дистрибутивах Microsoft Office 2016, не знаю :)

Удалить лишние пробелы в Excel

Основная и досадная проблема с лишними пробелами, которая мешает их удалению вручную — пробел является невидимым символом. Между слов лишние пробелы еще заметны по увеличивающемуся между ними расстоянию, пробел с начала строки тоже легко выявить, т.к. строка смещается от левой границы ячейки. А вот если пробел в строке идет после последнего символа, или в ячейке…

https://semtools.guru/ru/deletion-tools/delete-symbols/remove-extra-spaces/

Нагрянуло хорошее обновление !SEMTools!
Из новых возможностей:
- Больше возможностей работы с регулярными выражениями - теперь практически любые сценарии по удалению и извлечению данных возможны. Расскажу подробнее в статье чуть позже.
- Поправлена работа макроса "удалить пунктуацию" - он удалял символы слишком по строгому правилу, что приводило к удалению символов других алфавитов
- Добавлен макрос извлечения букв любых алфавитов (пришлось потрудиться, регулярки VBA отличаются от других языков и менее функциональны в этом плане)
- На полную мощность теперь работает извлечение глаголов
- Добавлены макросы по удалению и извлечению 1 слова из ячеек
- Немного оптимизирована структура меню
Скачать надстройку: https://semtools.guru/ru/download/
Перед скачиванием рекомендуется почистить кэш или скачать в инкогнито - браузеры кэшируют xlam-файлы и не умеют сравнивать файл на сервере и на компьютере, чтобы заменить его, если он обновился. Не придумал, как решить эту проблему, еще.

Склонение минус-слов для AdWords

Досадная проблема со спецификой таргетинга в аукционе на поиске Google — AdWords отлично понимает словоформы для ключевых слов, но отказывается их понимать для минус-слов. В связи с чем все минус-слова нужно указывать во всех их словоформах. Вручную просклонять все минус-слова будет сложной задачей, поэтому популярны бесплатные сервисы склонения минус-слов. Их основной минус в том, что…

https://semtools.guru/ru/sem-seo-tools/sklonenie-minus-slov-dlya-google-adwords/

Новая статья с примерами на сайте - про изменение содержимого ячеек без его удаления
https://semtools.guru/ru/change-replace-tools/change-cells/izmenit-poryadok-slov/

С наступающим!
Последнее обновление !SEMTools в этом году - 9.12.27 :)
Среди исправлений:
Легкие доработки структуры меню:
- отказ от работы с операторов LIKE - их полностью заменяет REGEX, и, кажется, это было лишним
- Чуть упакованы извлечение и удаление слов по порядку (1, 2, 3 и N слов с конца/начала ячейки)
- Заполнить пустые ячейки нулями и значениями выше-ниже теперь в одном меню
Багфиксы:
- макросы парсинга поисковых подсказок Bing, Amazon и Youtube теперь корректно возвращают кириллические и любые другие Unicode - символы. Парсим на полную!
- макросы поиска, удаления и извлечения слов из списка не отдают ошибку, если в списке есть повторы
Новшества:
- макрос удаления формул в диапазоне работает даже если в нем есть отфильтрованные или скрытые значения
- добавлено удаление формул только в видимых ячейках выделенного диапазона
Обновляемся: https://semtools.guru/ru/download/

Обновление !SEMTools 9.13.11
Новые возможности:
- Улучшен алгоритм склонения по падежам обычных слов
- Добавлено склонение ФИО по всем падежам, по правилам русской грамматики, проверено на 350 000 ФИО
- В надстройке заработал Мануал (на скриншоте) - теперь все ссылки ведут на релевантные разделы сайта
- Добавлена процедура удаления всего содержимого ячеек, если соответствует заданному регулярному выражению. Пример с удалением ячеек с латиницей:
https://semtools.guru/ru/deletion-tools/delete-cells/udalit-yacheyki-s-latinitsey/

Интересный вопрос мне задали на Яндекс.Q (я там Эксперт :D !), ответил и приложил файл.
Кратко, задача состоит в том, чтобы фильтровать товары по одному значению одной характеристики, когда этих значений у нее много.

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

https://yadi.sk/i/LBTde2lBvYx2DA - Держите решение.
Напишу статью, добавлю ссылку на развернутое описание всех функций, пока можно поискать в интернете по кусочкам.
Так это выглядит: в ячейках B2 и C2 - выпадающие списки, основанные, соответственно, на диапазонах B13:B14 и C13:C16. Их можно свободно перенести на другой лист и расширять как вздумается, но нужно обновлять выпадающие списки.
Разделители "/" обязательны, чтобы, если одно значение фильтрации является подстрокой другого значения, оно его не фильтровало, например, чтобы "для крыши" не фильтровало "для крыши из черепицы".

Подстановочный символ "*" обеспечивает фильтрацию по вхождению, а не точному совпадению.

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

P.S. Для "обрамления" всех ячеек диапазона справа и слева символами в !SEMTools есть удобная процедура:

https://semtools.guru/ru/change-replace-tools/change-cells/dobavit-tekst-vo-vse-yacheyki/

Обновление 9.13.24
- Добавлены математические операции на месте (группа изменить)
- добавлен новый инструмент для PPC - массовое исключение синонимов Директа. Первый такой на рынке! Читаем статью в блоге.
https://semtools.guru/ru/blog/kak-udalit-sinonimy-v-yandex-direct/