Как посчитать количество символов в ячейке гугл таблицы
Перейти к содержимому

Как посчитать количество символов в ячейке гугл таблицы

  • автор:

Подсчитайте количество символов в ячейке в Google Таблицах

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

В этом случае я могу быстро поместить эти заголовки в Google Таблицы и использовать формулы для подсчета символов.

В этом уроке я покажу вам, как подсчитать количество символов в ячейке / диапазоне в Google Таблицах.

Подсчет символов в ячейке в Google Таблицах

Это довольно просто — в конце концов, в Google Таблицах есть специальная функция для подсчета символов в ячейке.

Предположим, у вас есть набор данных, как показано ниже:

Ниже приведена формула, по которой будет подсчитано количество символов в каждой ячейке:

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

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

Например, в приведенном ниже примере функция LEN дает результат в ячейке B6 как 27 (вместо 25), потому что есть начальный пробел и двойной пробел.

Это происходит потому, что функция LEN считает все символы (включая пробел).

Чтобы убедиться, что вы не считаете лишние пробелы, вы можете использовать функцию ОБРЕЗАТЬ (TRIM) вместе с функцией LEN.

Ниже приведена формула, которая игнорирует любые начальные, конечные или двойные пробелы в Google Таблицах:

Подсчет символов в диапазоне в Google Таблицах

Вы также можете подсчитать общее количество символов в диапазоне ячеек.

Например, предположим, что у вас есть набор данных ниже, и вы хотите подсчитать общее количество символов в ячейке A2: A6

Ниже приведена формула, которая сделает это:

В приведенной выше формуле СУММПРОИЗВ (SUMPRODUCT) используется для получения суммы всего количества символов во всем диапазоне (что выполняется функцией LEN).

Вы также можете сделать то же самое, используя приведенную ниже функцию СУММ:

Но поскольку функция СУММ (SUM) не может обрабатывать массивы, вам придется использовать Control + Shift + Enter вместо обычного Enter (удерживайте клавиши Control и Shift, а затем нажмите клавишу Enter / Return).

Или, что еще лучше, используйте следующую формулу:

Подсчет появления определенного символа в Google Таблицах

Вы также можете подсчитать появление определенного символа (или строки в ячейке) с помощью формул.

Например, предположим, что вы хотите знать, сколько раз алфавит «а» использовался в названии каждого фильма,

Вы можете сделать это, используя следующую формулу:

В приведенной выше формуле используется функция ЗАМЕНА для удаления строчной буквы «а» из ячейки, а затем функция ДЛСТР подсчитывает общее количество символов без того, который мы хотим подсчитать.

Затем это значение вычитается из общей длины исходной длины, и это дает мне количество символов алфавита «а».

Обратите внимание, что функция ЗАМЕНА чувствительна к регистру. Если вы хотите, чтобы он учитывал как нижний, так и верхний регистры алфавита A, вы можете использовать следующую формулу:

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

Функция LEN (ДЛСТР) в Google таблицах

Функция LEN (ДЛСТР) в Google таблицах

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

Как посчитать количество букв в тексте?

Под такую задачу в google docs существует специальная функция LEN (ДЛСТР).

Text (Текст) — ячейка, длину (кол-во символов) которой нужно вычислить.

Функция LEN (ДЛСТР) считает количество всех символов, включая пробелы и непечатаемые символы. В случае, если функция LEN возвращает неожиданное значение, убедитесь в том, что текст не содержит скрытых символов.

Функция LEN (ДЛСТР) в Google таблицах

Как посчитать количество знаков в Google таблицах из нескольких ячеек?

Указать диапазон ячеек, в которых мы хотим посчитать количество символов в рамках одной функции, к сожалению, не получится. Но есть очень простой выход: воспользоваться функцией сцепки!

Функция CONCATENATE (СЦЕПИТЬ) легко объединит несколько ячеек с текстом в одну большую структуру. Так же, мы объединим ячейки с текстом через « & » (Амперсанд). Мы просто вложим функцию сцепки в функцию подсчета символов:

Google Sheets для геймдизайнера. Часть 2: Работа с текстом

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

Идем в File — Make a copy — выбираем папку на своем диске Google, куда вы хотите скопировать таблицу.

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

  1. Подсчет букв в ячейке (LEN, SUBSTITUTE)
  2. Подсчет слов в ячейке (IF, SUBSTITUTE, ISBLANK, TRIM)
  3. Подсчет ячеек, содержащих определенное слово (COUNTIF)
  4. Подсчет количества повторения слова в группе ячеек (LEN, JOIN, SUBSTITUTE)
  5. Поиск соответствия для значения (VLOOKUP)
  6. Соединение значений ячеек в одной (JOIN)
  7. Размытый поиск по частичному совпадению (FLOOKUP, IFS, ISTEXT)

Дано:
Таблица 1
Столбец A — Персонаж, который произносит реплику
Столбец B — Текст реплики

Вопрос:
Сколько символов без пробелов содержится в ячейке? Полученное значение записать в столбец C.

Общий вид формулы:

LEN — это формула, которая просто подсчитывает длину строки, то есть, количество символов в ячейке.

Синтаксис у формулы следующий:

=LEN (“ягода”) — это 5
=LEN (“1 2 33”) — это 6

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

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

Синтаксис у формулы следующий:

Где:
text_to_search — ячейка, или диапазон в котором нужно искать совпадения
search_for — значение, которое нужно найти, чтобы его заменить
replace_with — текст, на который нужно заменить найденное значение
occurrence_number — порядковый номер совпадения, которое нужно заменить

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

=Подсчитать длину строки (Заменить (в ячейке B5, найти пробелы, заменить на ничто))

Дано:
Таблица 1

Вопрос:
Сколько слов в ячейке?

Здесь мы будем использовать целое множество формул. Это связано со следующими нюансами:

  • мы не хотим, чтобы в результате формулы получалась ошибка, если ячейка пустая;
  • мы хотим, чтобы формула корректно считала слова типа «А-а-а-а», или «когда-нибудь», а именно — как одно слово;
  • мы хотим, чтобы формула при этом считала фразу «оранжевый — это цвет» — как три слова, а не как четыре.

Общий вид формулы:

Выглядит сложновато, но на самом деле все просто. Начнем с формул составляющих.

IF — это очень крутая формула, которая, используется довольно часто ввиду своей универсальности. Она очень похожа на IFS , о которой речь шла ранее, только она производит проверку не по нескольким условиям, а по одному, при этом возвращая в ячейку одно из двух значений: в случае, если ячейка отвечает условию, и в случае, если не отвечает. Под условием может быть как прямое значение, или текст, так и другая формула.

Синтаксис формулы следующий:

Где:
logical_expression — логическое выражение, то есть, наше условие;
value_if_true — если ответ на условие — «правда», то формула возвращает это значение;
value_if_false — если ответ на условие — «ложь», то формула возвращает это значение;

Обратите внимание, что текст формула проверяет без учета регистра, если вам нужна чувствительная проверка — ее нужно будет дополнить.

ISBLANK — это формула, которая просто производит проверку ячейки на заполненность. При применении к ячейке она возвращает TRUE, если ячейка пустая и FALSE, если в ней есть содержимое.

Синтаксис формулы:

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

Синтаксис формулы простой:

Теперь соберем все вместе:

Сначала мы используем функцию IF, с помощью которой мы возвращаем значение 0, если ячейка пустая:

=Если (Ячейка пустая (B5), если правда пустая пишем 0, …)

Если ячейка не пустая, то мы применяем к ней нашу формулу подсчета слов в ячейке.

Сначала мы считаем общее количество символов без лишних пробелов. Так, в примере видно, что лишние пробелы есть в начале второй и четвертой строчек текста. Если мы просто посчитаем длину строки LEN , то получим значение 177 (ячейка C2), но с формулой выше мы сначала обрезаем все лишние пробелы с помощью TRIM, и только потом уже считаем итоговую длину строки и получаем результат 173 символа.

Что выделяет слово в тексте и делает возможным осуществление подсчета? Правильно, пробелы, которыми оно отделяется от остальных слов. Именно поэтому «когда-нибудь» — это одно слово, а «когда ты» — два. Таким образом, подсчитав количество пробелов, мы сможем узнать количество слов. Для этого мы по известной нам формуле вычисляем длину строки без пробелов, она составляет 144 символа. Вычитаем это значение из предыдущего и получаем 173-144=29 слов. Тем не менее, после последнего слова текста и перед первым словом пробелы не ставятся, поэтому в любом тексте пробелов всегда меньше на один, чем слов. Например, «Стареем неизбежно мы» — два пробела, три слова. Поэтому мы прибавляем к получившемуся значению единицу. Получаем количество слов — 30 (ячейка B2).

Тем не менее, количество слов по-прежнему неверное, ведь наша формула считает « — » за слово, а это знак препинания. Получается, мы должны подсчитать количество тире в тексте и вычесть его из общего числа. Для этого, мы пользуемся такой же формулой, как и для подсчета пробелов: из общего числа символов мы вычитаем количество тире в тексте. Обратите внимание, что мы ищем именно сочетание символов «- », так как если мы добавим в формулу SUBSTITUTE тире без пробела, то она посчитает его и в слове «по-прежнему», а нам это не нужно. Таким образом, из общего количества символов LEN (A2) мы вычитаем количество символов без тире и получаем 177-175=2 символа. Полученное значение делим на два, так как в вычете участвовало два символа — и тире, и пробел.

Дано:
Таблица 1

Вопрос:
Сколько ячеек содержат слово «Cheese»?

Вспомогательная таблица 1.1 — Подсчет отдельных ячеек и значений

Общий вид формулы:

Тут мы используем нашу знакомую формулу COUNTIF , которая прекрасно справляется не только с поиском числовых значений, но и с поиском текста в ячейке. Проблема в том, что если ячейка содержит другой текст, кроме искомого «Cheese», то результат формулы будет 0. Поэтому, до и после искомого слова мы добавляем знак * , который в синтаксисе означает «любые другие символы».

Дано:
Таблица 1

Вопрос:
Сколько раз в ячейках таблицы повторяется слово «call»?

Вспомогательная таблица 1.1 — Подсчет отдельных ячеек и значений

Общий вид формулы:

Из нового тут только формула JOIN.

JOIN — это формула, которая объединяет значения нескольких ячеек в одну, вставляя между ними значение или символ, указанные в формуле как разделитель.

Синтаксис формулы такой:

Где:
delimiter — разделитель, который будет вставлен между объединяемыми ячейками;
value_or_array1 — диапазон ячеек, которые нужно объединить;
[value_or_array2, . ] — дополнительные диапазоны через запятую.

Теперь давайте разберем нашу формулу по подсчету слов.

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

Теперь, по уже знакомому нам принципу в тексте, объединенном через такой же JOIN , как и в первой части формулы, мы найдем с помощью SUBSTITUTE слово «вас» и заменим его на пустоту «». После этого, подсчитаем получившееся количество символов функцией LEN . Полученное значение мы вычтем из общего числа символов и получим 265-241=24 символа. Проще говоря, мы узнали, что между текстом, содержащим слово «вас» и не содержащим его, разница в 24 символа.

Теперь, когда мы знаем длину символов, составляющих сумму слов «вас» в тексте, там остается с помощью формулы LEN высчитать длину этого слова и разделить на получившееся число общее число лишних символов. Получается, 24/3=8 слов. Всего, выходит, 8 слов «вас» в тексте ячеек.

Дано:
Таблица 2

Столбец G — условие квеста;
Столбец H — название предмета, требующегося по квесту;
Столбец I — айдишник предмета.

Вопрос:
Как узнать id предмета по названию?

Вспомогательная таблица 2.1 — Таблица соответствий

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

Общий вид формулы:

VLOOKUP — это формула, которая осуществляет горизонтальный поиск по указанному диапазону соответствий к указанной ячейке, находит такое соответствие и возвращает искомое значение из указанного столбца.

Синтаксис формулы:

Где:
search_key — та ячейка, для которой мы будем искать соответствие;
range — диапазон, в котором мы будем искать соответствие. Он может быть любым количеству столбцов, но соответствия для искомой ячейки должно быть в первом столбце диапазона.То есть, если вы указываете диапазон со столбца B по столбец D, соответствия, среди которых должен быть search_key могут быть только в столбце B.
index — столбец из которого будем возвращать соответствие.
[is_sorted] — если вы укажете false, то формула будет искать точное совпадение search_key с первым столбцом диапазона range. Это единственный вариант работы с текстом. Если вы укажете true, или вообще ничего не укажете (а это true по умолчанию), то формула будет возвращать значение, приблизительно соответствующее искомому. При этом, первый столбец диапазона range должен быть отсортирован по возрастанию, иначе формула будет работать неправильно.

25+ функций Google Таблиц для работы с текстом

Google Таблицы (Google Spreadsheets) — табличный, а не текстовый редактор, тем не менее там часто приходится иметь дело именно с текстом. Рассказываем о соответствующих функциях.

ДЛСТР (LEN)

ДЛСТР (LEN) — функция для вычисления длины строки. Пригодится, если работаете в Google Sheets с контент-планами, текстами объявлений, метатегами — сразу можно проверить, соответствует текст нужным критериям или нет.

Синтаксис простой, аргумент один — текстовая строка (можно указать прямо в формуле или взять из ячейки). Например, =ДЛСТР("Блог CyberMarketing про интернет-маркетинг") вернет значение 42. Учитываются все символы, включая пробелы и переводы строки.

Если нужно проверить тайтлы страниц, а сами они пока неизвестны, можно вложить в LEN функцию IMPORTXML: =ДЛСТР(IMPORTXML(B2;"//title")) В данном случае результатом станет длина метатега TITLE, полученного для URL, который лежит в ячейке B2.

25+ функций Google Таблиц для работы с текстом

Так работает IMPORTXML, вложенный в LEN

Но вообще решать такие задачи с помощью парсера Click.ru гораздо проще и удобнее.

СТРОЧН (LOWER), ПРОПИСН (UPPER)

СТРОЧН (LOWER) приводит все символы к нижнему регистру, а ПРОПИСН (UPPER) — наоборот, к верхнему. Обе текстовые функции принимают один аргумент — строку в кавычках или ячейку с текстом. Получается, конструкция =ПРОПИСН(СТРОЧН("CYBERMARKETING")) выведет ту же самую строку — "CYBERMARKETING".

Но на практике часто можно обойтись без этих функций. Для поиска без учета регистра есть ПОИСК (SEARCH) — о ней чуть позже. А, например, у QUERY, которую мы разбирали в предыдущей статье, есть свои встроенные функции — upper() и lower() — для приведения строк к единому регистру.

СЦЕПИТЬ (CONCATENATE), JOIN, TEXTJOIN

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

CONCATENATE (СЦЕПИТЬ) дословно сцепляют несколько строк между собой по очереди. Синтаксис: =СЦЕПИТЬ("Hello";", ";"World";"!")

25+ функций Google Таблиц для работы с текстом

Простой пример использования CONCATENATE. Но такой способ, подойдет, пожалуй, только для немецкого языка.

Чтобы они не слипались между собой, придется каждый раз добавлять между ними пробел (или другой символ). Например: =СЦЕПИТЬ(A15;" ";B15;" ";C15;" ";D15) Или создавать таблицу, где в одном столбце — нужные слова, в другом — соединители. (Если в параметре указан диапазон — минимум по 2 ячейки в высоту и ширину — функция будет объединять значения по строкам.)

25+ функций Google Таблиц для работы с текстом

В таком случае только надо оставить последнюю ячейку пустой — иначе в конце созданной строки будет лишний пробел

Как вы понимаете, СЦЕПИТЬ далеко не самая удобная функция. JOIN же позволяет сразу задать единый разделитель для всех строк в массиве/диапазоне: =JOIN(" ";A16:A19) даст такой же результат, как на предыдущем скриншоте — при этом не надо беспокоиться за возможный лишний символ в конце строки.

В JOIN можно спокойно объединять сразу несколько массивов данных, например: =JOIN("&";<"https://site.ru/?";"utm_source=yandex">;<"utm_medium=cpc";"utm_campaign=campaign">) А если не указать разделитель, то все будет работать так же, как и у CONCATENATE.

Следующая функция подойдет лучше, если в массиве/диапазоне встречаются пустые строки — ведь в JOIN они создают лишние пробелы. А TEXTJOIN делает все то же самое, только есть дополнительный аргумент, который указывает: включать пустые строки или нет. Синтаксис: =TEXTJOIN(" ";ИСТИНА;A16:B20), где первый параметр передает пробел в качестве разделителя, второй говорит, что нужно игнорировать пустые строки, а третий — дает диапазон значений для соединения.

25+ функций Google Таблиц для работы с текстом

Здесь все правильно соединяется — ничего лишнего

Кстати, помимо функций, можно использовать такой оператор как ‘&’. К примеру, ="Cyber"&"Marketing" спокойно сработает и выдаст "CyberMarketing". Очень удобная фишка при создании всяких динамических таблиц (дашбордов/конструкторов), где нужно кастомизировать запрос для QUERY.

А подробнее о том, что делать с ключевыми словами, вы сможете узнать с помощью статей, вебинаров, курсов по SEO от CyberMarketing.

СЖПРОБЕЛЫ (TRIM)

Если проблема с пробелами все же есть, ее поможет решить специальная функция — СЖПРОБЕЛЫ (TRIM). Она чистит их в начале и конце текста, а также убирает повторяющиеся символы, например, двойные пробелы.

Синтаксис простой и понятный, аргумент один — строка целиком или ссылка на ячейку: =СЖПРОБЕЛЫ(" Hello, World! ")

25+ функций Google Таблиц для работы с текстом

Так работает СЖПРОБЕЛЫ

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

ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID)

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

  • =ЛЕВСИМВ("Привет, мир!";6) — тут получится просто "Привет".
  • =ПРАВСИМВ("Привет, мир!"; 4) — здесь от строки останется только "мир!".
  • =ПСТР("Привет, мир!"; 4; 3) — выведет лишь "вет".

С ЛЕВСИМВ и ПРАВСИМВ все должно быть и так понятно (кстати, последним параметром может быть ноль, тогда функция просто вернет пустую строку). А вот ПСТР работает так: вторым аргументом принимает номер символа, с которого нужно начать извлечение подстроки, а третьим — длину извлекаемой подстроки. (Важно: отсчет начинается с 1, а не с нуля.)

Конечно, в работе редко бывает заранее известно, где в какой строке и на сколько символов надо отступить. Как правило, такие функции используют в сочетании с другими, например, ДЛСТР и ПОИСК/НАЙТИ.

SPLIT

SPLIT разделяет текст — по одному или нескольким символам-разделителям — и выводит полученные фрагменты в разные ячейки. Сами символы при этом не возвращает. Простой пример: =SPLIT(A10;"/")

1. Текст, который нужно разделить на части. Строкой или ссылкой на ячейку.

2. Символ(-ы) для разделения — тоже строкой, в кавычках.

3. Тип разделителя (необязательно). По умолчанию ИСТИНА — функция разделяет текст по каждому символу, который указан во втором параметре. Если поставить ЛОЖЬ, то разделение будет происходить только по всей последовательности символов.

25+ функций Google Таблиц для работы с текстом

25+ функций Google Таблиц для работы с текстом

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

4. Тоже необязательный параметр, который определяет, нужно ли удалять пустые ячейки после разделения. По умолчанию ИСТИНА — два последовательных разделителя будут считаться одним. Если ЛОЖЬ, то между этими разделителями будет создана пустая ячейка.

25+ функций Google Таблиц для работы с текстом

Пожалуй, это не пригодится, SPLIT и так занимает слишком много ячеек

Главный недостаток — множество лишних «технических» ячеек, которые могут мешать, может понадобиться их скрывать. Извлекать отдельные фрагменты текста можно и другими способами.

REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

Все эти три функции работают с регулярными выражениями — специальным языком для гибких манипуляций с текстами. REGEXMATCH ищет совпадения, REGEXEXTRACT извлекает по шаблону нужный фрагмент, а REGEXREPLACE заменяет одну часть текста на другую. Синтаксис похожий: первый аргумент — текст, а второй — само регулярное выражение; в REGEXREPLACE есть еще третий — текст, который нужно вставить.

  • =REGEXEXTRACT("https://www.cybermarketing.ru/video-courses/promo/poiskovoe-prodvizhenie.html";"[^/]+$") — результатом будет "poiskovoe-prodvizhenie.html".
  • =REGEXMATCH("mail_mail.123@gmail.com";"^([a-z0-9_-]+\.)*[a-z0-9_-]+@[a-z0-9_-]+(\.[a-z0-9_-]+)*\.[a-z]<2,6>$") — ИСТИНА, так как в строке корректный адрес электронной почты.
  • =REGEXREPLACE("Google — это один из самых посещаемых сайтов в мире, второй по популярности поисковик Рунета и лидер мобильного трафика. Поэтому нет никаких сомнений, что контекстная реклама Google Adwords — эффективный канал для продвижения бизнеса. Google Адвордс позволит охватить большую аудиторию поиска Google и пользователей мобильных устройств на базе Android.";"(Adwords)|(Адвордс)|(Эдвордс)";"Ads") — здесь в тексте будет везде правильно написано название сервиса.

Вообще регулярные выражения — тема для отдельного материала. Сначала кажется, что суперсложно, но когда начинаешь разбираться, становится гораздо легче. Практиковаться можно не только в Google Docs, но и на специальных тренажерах, например, regex101.com. Но имейте в виду, что Таблицы поддерживают не все возможности, которые есть в PCRE2 (PHP) или ECMAScript (JavaScript). Вся информация о синтаксисе RE2 (Google).

UNIQUE, COUNTUNIQUE

Функция UNIQUE берет только те строки, которые ни разу не повторяются, то есть позволяет почистить набор данных от дубликатов. Весьма полезная штука в SEO, PPC, веб-аналитике. Уникальные строки возвращаются в том же порядке, в котором располагаются в диапазоне/массиве.

Синтаксис: =UNIQUE(A2:A100) или =UNIQUE(<"вебинар";"видеокурс";"интенсив";"вебинар";"статья";"конференция">) — тут в столбец выйдут по порядку 5 элементов этого массива, а не 6, потому что два из них неуникальные.

Если нужно не выводить, а только подсчитать количество уников, есть функция COUNTUNIQUE. В нее можно передавать набор из произвольного числа аргументов: значений и диапазонов. Синтаксис: =COUNTUNIQUE(1; 1; 2; 3; 5; 8; 13; A2; B6:B9)

ПОИСК (SEARCH), НАЙТИ (FIND)

Обе функции делают одно и то же: возвращают порядковый номер символа, на котором запрос впервые встречается в тексте. Первым аргументом передаем, что искать, вторым — где искать, третьим — с какого символа искать (поиск идет с начала, учитываются все символы, в том числе пробелы). (В отличие от ЛЕВСИМВ и ПРАВСИМВ, нельзя передать ноль в качестве последнего аргумента.)

Разница лишь в том, что НАЙТИ (FIND) учитывает регистр, а ПОИСК (SEARCH) — нет. Примеры:

  • =НАЙТИ("!";"Привет, мир!";1) — результатом будет 12.
  • =НАЙТИ("привет";"Привет, мир!";1) — тут получится ошибка #ЗНАЧ, так как функция чувствительна к регистру.
  • =ПОИСК("привет»;"Привет, мир!";1) — здесь функция сработает нормально, вернется 1.
  • =ПОИСК(",";"Привет, мир!";4) — тут на выходе будет 7. (Важно: хотя поиск начинается с 4 символа, функция вернет позицию, где происходит совпадение, именно с начала строки — а не с того символа, с которого начинается поиск.)
  • =ПОИСК("р";"Привет, мир!";1) — ответ 2, потому что учитывается только первое совпадение, которое ближе к левой части, началу строки. Если встречается дальше повторно, уже никак не влияет.

Результаты выполнения функции — позиции символов/подстрок — пригодятся, например, для дальнейшей работы с ЛЕВСИМВ, ПРАВСИМВ или ПСТР. Но сами по себе они не понадобятся, если нужно просто проверить: есть в тексте нужное слово или нет. TRUE или FALSE вместо бесполезных цифр поможет получить ЕОШИБКА (ISERROR) (или похожая функция ЕСЛИОШИБКА) в сочетании с функциями IF или IFS (иногда еще нужен NOT). К примеру:

  • =IF(ISERROR(SEARCH("paid";"https://www.cybermarketing.ru/video-courses/paid/prodvinutyj-kurs-seo.html";1));"Курс бесплатный";"Курс платный") — здесь функция вернет именно "Курс платный".
  • =IFS(НЕ(ЕОШИБКА(НАЙТИ("paid";"https://www.cybermarketing.ru/video-courses/promo/kontekstnaya-reklama.html")));"Курс платный";НЕ(ЕОШИБКА(НАЙТИ("promo";"https://www.cybermarketing.ru/video-courses/promo/kontekstnaya-reklama.html")));"Курс бесплатный") — а здесь получится "Курс бесплатный" (для двух вариантов, конечно, слишком сложно, но для большого количества развилок — вполне).

ПОДСТАВИТЬ (SUBSTITUTE)

ПОДСТАВИТЬ (SUBSTITUTE) — текстовая функция Google Таблиц, которая сначала находит в тексте нужный фрагмент, затем заменяет его на другой (как обычно можно отправить строки как есть или ячейки с соответствующими данными). Аргументы:

  1. Текст, для которого нужны поиск и замена.
  2. Строка, которую нужно найти и удалить.
  3. Строка, которую нужно подставить вместо.
  4. Порядковый номер вхождения строки, которую нужно заменить (необязательно). (По умолчанию все совпадения будут заменяться.)

На злобу дня: =ПОДСТАВИТЬ("Привет, мир!";"мир";"карантин") — "Привет, карантин!". Еще пример: =ПОДСТАВИТЬ("Привет, мир!";"р";"г";2) —"Привет, миг!" (‘р’ встречается раньше, но специально указано, что брать следует второй символ по счету).

Важно: функция SUBSTITUTE не различает, является строка отдельным словом или частью другого. Замена будет производиться в любом случае. В более сложных кейсах лучше использовать функции REGEX — для работы с языком регулярных выражений.

DETECTLANGUAGE и GOOGLETRANSLATE

DETECTLANGUAGE умеет определять язык текста, а GOOGLETRANSLATE — переводить текст с одного языка на другой.

DETECTLANGUAGE принимает один аргумент — текст или диапазон. (Работает только с одномерным столбцом, при передаче диапазона вида A2:B8 выдаст ошибку — обойти можно только через массив.) Если в диапазоне будут тексты на нескольких языках, функция определит язык лишь первого попавшегося фрагмента.

GOOGLETRANSLATE работает с тремя параметрами (два последних необязательные):

  1. Текст, который нужно перевести.
  2. Язык оригинала в таком же формате, каким оперирует DETECTLANGUAGE. (Кстати, можно указать "auto" и он будет определен автоматически — то есть вкладывать одну функцию в другую для этих целей не нужно.)
  3. Язык перевода — аналогично — двумя буквами. (По умолчанию функция берет язык операционной системы, но это не всегда работает, поэтому лучше указывать отдельно.)

С диапазонами не получится, только с отдельными ячейками/строками.

ТЕКСТ (TEXT) И ЗНАЧЕН (VALUE)

Функция ТЕКСТ (TEXT) преобразует числовой формат в текстовый — это может понадобится, например, для динамических таблиц с использованием QUERY, красивого представления данных в отчетах. Первый аргумент — число, дата и/или время, второй — шаблон для форматирования. (‘?’, ‘*’, а также дробные форматы не поддерживаются.) Примеры:

  • =ТЕКСТ("21/12/2012";"mmm d yyyy") — здесь получится "дек. 21 2012".
  • =ТЕКСТ("199,99"; "#,###") — тут результатом будет 200.
  • =ТЕКСТ(100,789; "$0,00") — округление и представление в долларах — $101.
  • =ТЕКСТ("10000001"; "#,##0") — "10 000 001".

(Все варианты можно посмотреть в справке, а также в разделе «Формат» → «Числа» → «Другие форматы».)

ЗНАЧЕН (VALUE) делает совершенно противоположное — преобразует текст или дату в число. На вход принимает единственный аргумент — строку. Изменение формата может понадобиться, например, когда мы извлекаем данные из текста с помощью REGEXEXTRACT, а потом их нужно использовать для функций суммирования, подсчета среднего арифметического и т. п.

ЕТЕКСТ (ISTEXT)

ЕТЕКСТ (ISTEXT) проверяет, есть в ячейке текст или нет. Если там текстовое значение, возвращает ИСТИНА, если найдет число или пустую ячейку — ЛОЖЬ. Так:

  • =ЕТЕКСТ("123") — TRUE, так как данные, заключенные в кавычки, передаются как строка.
  • =ЕТЕКСТ(123) — FALSE, потому что числа передаются без кавычек.
  • =ЕТЕКСТ("") — тоже TRUE, строка пустая, но все же это строка.

Эта функция чаще используется в условных конструкциях (IF, IFS).

СОВПАД (EXACT)

СОВПАД (EXACT) — специальная функция Таблиц для сравнения двух строк: если они полностью совпадают, вернется TRUE, если нет — FALSE. Важны и пробелы, и регистры. К примеру, =СОВПАД("Cybermarketing";"CyberMarketing") выдаст ЛОЖЬ, так как по одному символу они отличаются.

Если регистр не важен, гораздо проще использовать обычные операторы, например: =ЕСЛИ("CyberMarketing"="Cybermarketing";"Совпадают";"Не совпадают") — здесь будет "Совпадают".

ГИПЕРССЫЛКА (HYPERLINK)

ГИПЕРССЫЛКА (HYPERLINK) создает в ячейку гиперссылку. Первый аргумент — полный URL, второй (необязательный) — текст ссылки.

Если протокол не указан, по умолчанию используется "http://". Если текстом ссылки указана пустая строка "", ячейка отображается пустой, но ссылка все равно будет работать.

На практике эта функция нужна редко, только если важно скрыть целый URL за анкором. В остальных случаях Google Таблицы автоматически делают адреса страниц/сайтов кликабельными.

А о том, как правильно работать со ссылками на сайте, вы сможете узнать с помощью статей, вебинаров, курсов по SEO от CyberMarketing.

Добавить комментарий

Ваш адрес email не будет опубликован.