Как заключать ключевые слова в кавычки в Excel
Обновлено: 21.11.2024
Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.
Обзор
Эта статья содержит и описывает формулы, которые вычисляют следующее:
- Количество вхождений текстовой строки в диапазоне ячеек.
- Количество вхождений символа в одной ячейке.
- Количество вхождений символа в диапазоне ячеек.
- Количество слов (или текстовых строк), разделенных символом в ячейке.
Дополнительная информация
Формула для подсчета количества вхождений текстовой строки в диапазоне
=СУММ(ДЛСТР(диапазон)-ДЛСТР(ПОДСТАВИТЬ(диапазон,"текст","")))/ДЛСТР("текст")
Где диапазон — это рассматриваемый диапазон ячеек, а «текст» заменяется конкретной текстовой строкой, которую вы хотите подсчитать.
Приведенная выше формула должна быть введена как формула массива. Чтобы ввести формулу в виде массива в Excel для Windows, нажмите клавиши CTRL+SHIFT+ВВОД. Чтобы ввести формулу в виде массива в Excel для Macintosh, нажмите COMMAND+RETURN.
Формула должна быть разделена на длину текстовой строки, поскольку сумма длин символов диапазона уменьшается на кратное число каждого вхождения текстовой строки. Эта формула может заменить все последующие формулы в этой статье, кроме формулы для подсчета количества слов в ячейке.
Пример 1. Подсчет количества вхождений текстовой строки в диапазоне
Запустите Excel и откройте новую книгу.
Введите на листе 1 следующее:
Значение ячейки A8 равно 4, поскольку текст "яблоко" встречается в диапазоне четыре раза.
Формула для подсчета количества вхождений одного символа в одной ячейке
=LEN(cell_ref)-LEN(ЗАМЕНИТЬ(cell_ref,"a",""))
Где cell_ref — это ссылка на ячейку, а «a» заменяется символом, который вы хотите подсчитать.
Эту формулу не нужно вводить как формулу массива.
Пример 2. Подсчет количества вхождений символа в одной ячейке
Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество вхождений символа "p" в A7. Введите следующую формулу в ячейку A9:
Значение ячейки A9 равно 3, поскольку символ "p" встречается в ячейке A7 три раза.
Формула для подсчета количества вхождений одного символа в диапазоне
=СУММ(ДЛСТР(диапазон)-ДЛСТР(ПОДСТАВИТЬ(диапазон,"а","")))
Где диапазон — это рассматриваемый диапазон ячеек, а «а» заменяется символом, который вы хотите подсчитать.
Приведенная выше формула должна быть введена как формула массива. Чтобы ввести формулу в виде формулы массива в Excel, нажмите клавиши CTRL+SHIFT+ВВОД.
Пример 3. Подсчет количества вхождений символа в диапазоне
Используйте те же данные из предыдущего примера; если вы хотите подсчитать количество вхождений или символ "p" в A2:A7. Введите следующую формулу в ячейку A10:
Приведенная выше формула должна быть введена как формула массива. Чтобы ввести формулу в виде формулы массива в Excel, нажмите клавиши CTRL+SHIFT+ВВОД.
Значение ячейки A10 равно 11, так как символ "p" встречается 11 раз в A2:A7.
Формула для подсчета количества слов, разделенных символом в ячейке
=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,""))+1)
Где cell_ref — ссылка на ячейку, а char — символ, разделяющий слова.
В приведенной выше формуле нет пробелов; несколько строк используются только для того, чтобы поместить формулу в этот документ. Не включайте пробелы при вводе в ячейку. Эту формулу не нужно вводить как формулу массива.
Пример 4. Подсчет количества слов, разделенных пробелом в ячейке
Чтобы подсчитать количество слов в ячейке, в которой слова разделены пробелом, выполните следующие действия:
Запустите Excel и откройте новую книгу.
Введите на листе 1 следующее:
Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенные пробелами. Если слова разделены несколькими пробелами или слова начинаются или заканчиваются пробелом, это не имеет значения. Функция TRIM удаляет лишние пробелы, а также начальные и конечные пробелы в тексте ячейки.
В Excel вы также можете использовать макрос для подсчета вхождений определенного символа в ячейку или диапазон ячеек.
Ссылки
Для получения дополнительных сведений о подсчете вхождений текста щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:
89794 Как использовать Visual Basic для приложений для подсчета вхождений символа в выделенном фрагменте в Excel
Когда большинство людей думают об анализе ключевых слов, они, скорее всего, думают об этом в контексте поисковой оптимизации, то есть о том, какие ключевые слова или поисковые фразы привлекают посетителей на веб-сайты.Однако применение анализа ключевых слов не ограничивается маркетинговыми стратегиями. В мире аудита поиск по ключевым словам можно использовать для анализа текстовых полей в главной бухгалтерской книге организации или других финансовых документах. Поиск по определенным ключевым словам помогает определить определенные виды деятельности, которые, возможно, должны быть проверены аудиторами. Сюда могут входить наиболее часто встречающиеся ключевые слова, использование ключевых слов сотрудником или необычные действия, которые аудитор не должен ожидать в больших объемах.
HeinfeldMeech использует программное обеспечение для анализа данных Caseware IDEA для выполнения нашей аналитической тестовой работы. Одним из преимуществ программного обеспечения является то, что оно позволяет нам быстро и тщательно искать в файлах аудита наших клиентов определенные ключевые слова и варианты слов. Однако при правильной настройке анализ ключевых слов можно выполнить и в Excel.
Следующие шаги позволят вам выполнить простой анализ ключевых слов в текстовом столбце электронной таблицы в Excel:
Шаг 1
Определите столбец для анализа ключевых слов. Все ячейки в этом столбце не должны содержать знаков препинания, лишних пробелов и заглавных букв. Поэтому необходимо создать новый столбец, чтобы очистить данные из ячеек исходного столбца, используя приведенную ниже формулу.
[ПРИМЕЧАНИЕ: обязательно измените ссылку на ячейку (выделена красным), чтобы она соответствовала вашей электронной таблице]
Шаг 2
Добавьте новые столбцы для всех ключевых слов, которые вы хотите найти в данных. Определите, должно ли каждое ключевое слово быть целым словом, корнем слова (например, "adjusting" или "adjust") или аббревиатурой слова (например, "transfer" или "xfr").
Введите эти ключевые слова в качестве заголовков новых столбцов, а затем введите приведенную ниже формулу в каждую ячейку столбцов ключевых слов. Эта формула ссылается на каждый заголовок столбца ключевого слова и ищет этот текст в очищенном столбце данных. «1» означает, что ключевое слово встречается; «0» означает, что этого не происходит.
[ПРИМЕЧАНИЕ: обязательно измените ссылки на ячейки (выделены красным), чтобы они соответствовали вашей электронной таблице]
Шаг 3
Столбцы ключевых слов теперь должны быть заполнены нулями и единицами, указывающими, где встречается каждое ключевое слово. Однако формат ячеек еще не позволяет их суммировать.
Чтобы сгенерировать сумму вхождений для каждого ключевого слова, ячейки необходимо сначала переформатировать, выполнив следующие действия:
Сначала введите число "1" в неиспользуемую ячейку электронной таблицы. Скопируйте ячейку.
Затем выберите ячейки, которые необходимо переформатировать, затем щелкните правой кнопкой мыши внутри выделенных ячеек и нажмите «Специальная вставка» (слова, а не стрелка).
Чтобы классифицировать текст по ключевым словам с совпадением "содержит", можно использовать функцию ПОИСК с помощью ИНДЕКС и ПОИСКПОЗ. В показанном примере формула в C5 выглядит так:
где ключевые слова — это именованный диапазон E5:E14, а категории — это именованный диапазон F5:F14.
Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter.
По сути, это функция ИНДЕКС и ПОИСКПОЗ.
Внутри функции ПОИСКПОЗ мы используем функцию ПОИСКПОЗ для поиска ячеек в столбце B по каждому ключевому слову в именованном диапазоне ключевых слов (E5:E14):
Поскольку мы ищем несколько элементов (в именованных ключевых словах диапазона), мы получим несколько результатов, например:
Чтобы преобразовать эти результаты в более удобный формат, мы используем функцию ISNUMBER, которая преобразует все значения в TRUE/FALSE следующим образом:
Этот массив входит в функцию ПОИСКПОЗ как lookup_array, где lookup_value установлено как TRUE. ПОИСКПОЗ возвращает позицию первого найденного значения ИСТИНА в массиве (в данном случае 7), которое предоставляется функции ИНДЕКС в виде номер_строки:
ИНДЕКС возвращает седьмой элемент в категории "Авто" в качестве конечного результата.
С помощью XLOOKUP
С помощью функции XLOOKUP эту формулу можно несколько упростить. XLOOKUP может использовать ту же логику, что и вышеприведенная функция ПОИСКПОЗ, поэтому эквивалентная формула:
XLOOKUP находит первое TRUE в массиве и возвращает соответствующее значение из категорий.
Предотвращение ложных совпадений
Одна из проблем с этим подходом заключается в том, что вы можете получить ложные совпадения из подстрок, которые появляются внутри более длинных слов. Например, если вы попытаетесь сопоставить «dr», вы также можете найти «Andrea», «drink», «dry» и т. д., поскольку внутри этих слов появляется «dr».Это происходит потому, что ПОИСК автоматически выполняет поиск "содержит".
Для быстрого решения вы можете добавить пробелы вокруг поисковых слов (например, "dr" или "dr"), чтобы избежать попадания "dr" в другое слово. Но это не удастся, если "dr" появится первым или последним в ячейке, или появится со знаками препинания и т. д.
Если вам нужно более точное решение, один из вариантов — сначала нормализовать текст во вспомогательном столбце, позаботившись также о добавлении начального и конечного пробелов. Затем вы можете искать целые слова, окруженные пробелами.
Вот список некоторых менее известных формул и макросов Excel, которые регулярно пригодятся специалистам по маркетингу ключевых слов. Это могут быть SEO-специалисты, PPC или любой, кто работает с большими электронными таблицами, содержащими ключевые слова и связанные данные, такие как объем поиска, цена за клик и категории. Воспринимайте его как шпаргалку Excel, предназначенную для специалистов по маркетингу ключевых слов, но полезную для всех, кто хочет расширить свой набор приемов Excel.
Получить домен из URL:
Получить субдомен из URL:
Когда вам просто нужны поддомены в большом списке из набора URL-адресов разного формата. Эта формула работает вне зависимости от наличия протокола. Недостаток элегантности он с лихвой компенсирует полезностью.
Удалить первые X символов из ячейки:
Если есть что-то последовательное, что вы хотите удалить из начала данных в своих ячейках, например тег html, например , вы можете использовать это, чтобы удалить его, указав его длину в символах в этой формуле, поэтому X будет равно 7 в этом случае.
Удалить последние X символов из ячейки:
Вы можете использовать это, чтобы удалить завершающую косую черту из списка URL-адресов, например, где X равен 1.
Автоматически группировать ключевые фразы на основе содержащихся в них слов:
Этот малыш заслуживает отдельного поста в блоге, и он его получил. Вот что он делает:
Пример. Массовая категоризация ключевых слов по группам цвета и типа волос.
Использование формулы для группировки ключевых слов:
- $C$2:$C$200 — это ваш диапазон строк для поиска (список всех возможных слов, которые вы хотите проверить в ключевом слове).
- $D$2:$D$200 – это ваша метка, которая будет возвращена при обнаружении строки, поместите ее в следующий столбец в строке (это может быть просто слово, которое вы проверяете, если хотите, – то же, что и выше)
- A2 – это ячейка, содержащая строку ключевого слова, которую вы ищете, чтобы увидеть, содержит ли она какую-либо из перечисленных строк, чтобы вы могли пометить ее соответствующим образом.
- «/» — это то, что возвращается, если ни одна из строк не совпадает.
Использование формулы
Количество слов:
Узнайте, сколько слов содержится в вашем ключевом слове, чтобы определить, является ли оно «длинным хвостом», и оценить потенциальное намерение.
Узнать, существует ли значение в диапазоне других значений:
Это мой любимый метод, поэтому часто нам просто нужно знать, содержатся ли URL-адреса в списке A в списке B. Нет необходимости подсчитывать столбцы vlookup или iferror. Это дает ИСТИНА или ЛОЖЬ.
Получить TRUE или FALSE, если слово или строка находятся в ячейке:
Если вы хотите отдохнуть от использования фильтра "содержит", это может быть способом сделать что-то быстрее и более универсальным способом.
Удалить первое слово из ячейки (все до и включая 1-й пробел):
Чтобы удалить последнее слово, просто используйте LEFT вместо RIGHT.
Заменить первое слово в ячейке другим словом:
«X» — это слово, которым вы хотите заменить действующее первое слово, или это может быть ссылка на ячейку.
Суперобрезка — более надежная обрезка пробелов в ячейках:
Иногда использование =TRIM() не удается из-за нетрадиционного символа пробела из того, что вы вставили в Excel. Это доставит их всех.
Выполнить преобразование текста в столбцы с помощью формулы:
Это удобно для создания шаблонов. Он предоставляет способ автоматического преобразования текста в столбцы с помощью формул с использованием указанного вами разделителя. В примере в качестве разделителя используется пробел.
Извлеките окончательный путь к папке из URL-адреса:
Хорошо, когда вам нужно получить только последнюю часть URL, относящуюся к конкретной странице:
Извлеките путь к первой папке из URL-адреса:
Хорошо подходит для извлечения языковой папки.
Удалить весь текст после X-го экземпляра определенного символа:
Скажем, вы хотите удалить последнюю папку из URL-адреса или вернуть кластер ключевых слов на предыдущий уровень иерархии. «/» — это символ, в котором произойдет разделение, измените его на то, что хотите. «-0» в конце отсекает все после последнего экземпляра. Если изменить его на -1, все после предпоследнего экземпляра будет отсечено и т. д.
Создайте алфавитный список букв столбцов для использования в других формулах A,B,C…AA,BB и т. д.:
В отличие от чисел, Excel не дает автоматически следующую букву алфавита, если вы перетаскиваете мышью вниз после выбора ячеек с буквами «a» и «b», но вы можете использовать это для достижения этого эффекта. Он проходит через столбцы, поэтому он будет работать после Z, давая вам AA и AB и т. д. Это удобно для косвенных ссылок в формулах.
Подсчет экземпляров символа в ячейке:
Countif "*"&"x"&"*" не подходит для этой задачи, потому что он подсчитывает ячейки, а не вхождения. Пример здесь для символа пробела « ».
Подсчитайте, сколько раз определенное слово появляется в ячейке:
Вышеприведенная формула работает для отдельных символов, но если вам нужно подсчитать слова целиком, она сработает. Она удобна для проверки включения ключевых слов в текст целевой страницы для SEO. В примере B2 должно содержать слово, количество экземпляров которого подсчитывается в пределах A2.
Возвращает TRUE, если в ячейке нет чисел:
Измените конец на >0, чтобы показать TRUE, если присутствуют цифры. Удобен для изоляции и удаления ячеек данных, которые могут быть идентифицированы как нежелательные по наличию или отсутствию числа, например сочетание названий элементов и кодов продуктов, когда вам нужны только названия элементов.
Получить текущую букву столбца для использования в других формулах:
Если вы используете косвенные ссылки и хотите быстро добавить букву текущего столбца в формулу, используйте это.
Поместите ключевые слова в пронумерованные пакеты для получения данных о сезонных запросах:
Чтобы вам не приходилось каждый раз отсчитывать 2500 ключевых слов. Это группирует их, поэтому вам просто нужно отфильтровать номер партии, ctrl A, ctrl C, ctrl V — 43 — это количество ключевых слов в вашем списке, деленное на 2500, что является пределом планировщика ключевых слов. Используйте макрос вставки пустых строк, чтобы упростить выбор пакетов.
Переключатель порядка слов:
Превращает "белые свадебные платья" в "белые свадебные платья". Используйте его пошагово внутри себя для дальнейшей перестановки слов в другом порядке.
Найти максимальное числовое значение в диапазоне строк и вернуть заголовок столбца:
Поэтому, если ваши заголовки столбцов представляют собой месяцы или категории, это возвращает тот из них, который содержит наибольшее значение для этой строки. Полезно, чтобы показать, в каком месяце наибольшее количество запросов по ключевому слову.
Найти позицию n-го вхождения символа в ячейку:
Полезно в составе других формул.
Получить все символы после последнего экземпляра строки:
Получает "категорию 3" из "категория 1 > категория 2 > категория 3" с разделением на последний ">".
Получить все символы после первого экземпляра строки:
То же, что и выше, но исключает первую категорию, например. получает «категория 2 > категория 3» из «категория 1 > категория 2 > категория 3», разделяя на первый «>».
Получить URL-адрес из URL-адреса в формате Google Analytics:
Получить следующие x символов после строки:
Есть 3 детали, которые вам нужно изменить.
- Замените STRING своей строкой для поиска, например. «идентификатор продукта:»
- Замените «+6» на длину искомой строки, чтобы для «идентификатора продукта:» было бы «+11».
- Замените следующее число, «6», на количество символов, которые вы хотите захватить после конца строки для поиска. Таким образом, чтобы получить «0123» из «идентификатор продукта: 0123», вы должны поставить «4».
Это немного похоже на захват регулярных выражений. Я использовал это, чтобы получить ширину и высоту изображений в необработанном HTML.
Определить ячейки, содержащие не буквенно-цифровые символы
Это возвращает TRUE, если ваша ячейка содержит какой-либо символ, который не является цифрой или буквой. Он отлично подходит для отсеивания нежелательных ключевых слов с ошибками в Google Search Console, таких как "синие туфли для танго" и "/синие туфли для танго"
.Он возвращает TRUE, если находит что-либо, отличное от x, где x — это символы в кавычках в конце. Вы можете изменить это, например, чтобы возвращать TRUE, если он находит числа, удаляя числа между кавычками. По независящим от меня причинам он не идентифицирует вопросительные знаки.
Используйте только первую букву в ячейке с заглавной буквы
На ленте Excel все символы в ячейке могут быть прописными, а первые в каждом слове — ПРОПИСНЫМИ. Но что, если вы просто хотите, чтобы самая первая буква ячейки была заглавной? Это сделает то, что вам нужно:
Это особенно удобно для подготовки метаданных нижнего регистра к загрузке.
VBA выполняет действия с вашими электронными таблицами нажатием кнопки. Обычно на это уходит много времени (или это сложно/невозможно) с использованием обычной ленты Excel и возможностей формул.
- Сохраните книгу в формате .xlsm.
- Снова откройте его и нажмите клавиши ALT + F11.
- В меню вставьте > модуль
- Вставьте код
- Нажмите кнопку воспроизведения.
Нет необходимости разбираться в коде.Но не забудьте сохранить резервную копию своей книги перед запуском любого из них — их нельзя отменить с помощью Ctrl + Z!
Преобразуйте все неактивные URL-адреса в электронной таблице в интерактивные гиперссылки:
Таким образом, вы можете легко посещать URL-адреса, если вам нужно, например. для оптимизации большого количества страниц, поэтому вам не нужно дважды щелкать каждую из них, чтобы подготовить ее.
Условное форматирование по значению строки:
Поэтому интенсивность цвета относится только к каждой строке, а не ко всему диапазону. Вы должны использовать это, чтобы заполнить вкладку сезонности документа ландшафта поиска.
Удалить дубликаты по отдельности по столбцу:
Если у вас много столбцов, каждый из которых требует удаления дубликатов по отдельности, например. если у вас есть ряд таксономий категорий для очистки — вы не можете сделать это из меню:
Объединить соседние ячейки в диапазоне на основе одинакового значения:
Чтобы вам не приходилось делать это индивидуально, когда вам нужно, чтобы электронная таблица выглядела хорошо:
Удалить все экземпляры любого текста между двумя символами включительно из ячейки (в данном примере это ):
Особенно хорошо подходит для удаления тегов HTML из извлечений кричащих лягушек, что-то вроде замены регулярных выражений.
Выделить слова с ошибками:
Это может помочь вам идентифицировать искаженные или бессмысленные ключевые слова из большого набора или просто проверить орфографию в Excel, если вам нужно.
Зафиксировать все слайсеры в нужном положении:
Если вы отправляете документы Excel клиентам, в которых есть слайсеры, вы можете опасаться, что они в конечном итоге будут перемещать слайсеры при попытке их использовать — плохой опыт, из-за которого ваш документ будет выглядеть менее профессионально. Но есть способ обойти это — запустите этот код, и ваши слайсеры будут заблокированы на всех листах, оставаясь при этом в рабочем состоянии. Этот эффект сохраняется при повторном сохранении документа как обычного файла .xlsx.
Разделить значения в ячейке с разделителями на несколько строк с сохранением ключевого столбца:
Строку с разделителями (ключевое слово,объем,цена за клик) легко поместить в столбцы с помощью преобразования текста в столбцы, но что, если вместо этого вы хотите разделить ее по вертикали на строки? Это может помочь:
Создание нескольких копий листа одновременно:
Если вы, например, создаете шаблон отчета и хотите получить таблицы за все 12 недель, созданные за один раз:
Добавить определенное количество новых строк на основе значения ячейки:
Неоднократно сохраняет с помощью вставки строки, нажатия F4 и т. д.:
Укладчик колонн:
Это удобно, когда у вас много столбцов с информацией, которые вы хотите объединить в один главный столбец:
Сверхбыстрый поиск и замена для огромных наборов данных:
Чтобы сопоставить неполную ячейку, измените на «X1Part».
Вставьте все ячейки как значения на листе в активном диапазоне:
На случай, если ваша электронная таблица слишком медленная, чтобы делать это вручную
Отформатируйте все ячейки в общем формате или в любом другом, не выделяя их:
Еще один хороший вариант, когда ваша электронная таблица работает слишком медленно.
Активация формулы — вставка равенства в начале диапазона ячеек:
Если вы делаете что-то сложное с большим количеством формул, которые вы пока не хотите включать, но хотите иметь возможность использовать другие формулы одновременно (т. е. не можете отключить расчеты), это может помощь. Это также удобно для простого добавления элементов в начало ячеек:
Быстрое удаление именованных столбцов в электронной таблице, которая медленно отвечает:
Иногда нельзя просто «удалить столбец». Это для тех времен.
Найти и заменить на основе таблицы на другом листе:
Используйте X1Part для сопоставления и замены строки в ячейке или X1 integer для сопоставления и замены всей ячейки:
Показать все листы в книге:
Рассказывают ли ваши скрытые листы историю 1000 предыдущих клиентов? Вам не нужно, чтобы я говорил вам, что это может выглядеть непрофессионально. Вытащите эти скрытые листы из мусора за один раз с помощью этого кода vba, чтобы вы могли их удалить. В противном случае вам придется утомительно показывать их по одному — в интерфейсе нет возможности сделать это все сразу.
Изменить источник данных сводной таблицы для всех сводных таблиц на листе:
Ваш источник данных изменился. У вас есть 12 сводных таблиц для обновления. Ты только что потерял свой обеденный перерыв. Или ты? Чтобы обновить все их источники данных одним махом, замените WORKSHEETNAME на имя вашего рабочего листа, а DATA на имя вашего источника данных:
Преобразовать все «числа, хранящиеся в виде текста» в общие:
"Число сохранено как текст!" .Мы все это видели. Мы все были раздражены этим. Мне нужно было преобразовать несколько тысяч строк, и это заняло минуты, а не секунды.
Пропустить все это, заменив диапазон:
Есть и другие способы сделать это, но если у вас большой набор данных, это самый быстрый способ.
Читайте также: