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

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

Предыдущие статьи цикла:

Введение и общая функциональность Google таблиц

Google Sheets для геймдизайнера. Часть 1: Сортировка данных

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

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

Страница 2: Работа с текстом

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

Содержание статьи:

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

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

Задача 1

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

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

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

Решение:

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

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

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

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

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

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

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

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

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

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

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

На простом примере:

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

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

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

Задача 2

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

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

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

Решение:

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

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

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

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

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

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

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

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

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

На простом примере:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задача 3

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

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

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

Решение:

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

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

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

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

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

Задача 4

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

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

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

Решение

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

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

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

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

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

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

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

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

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

На простом примере:

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

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

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

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

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

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

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

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

Задача 5

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

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

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

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

Решение

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

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

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

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

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

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

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

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

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

На простом примере:

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

Формула находит точное соответствие названия товара в столбце D и возвращает значение ячейки соответствия.

Теперь посмотрим, как это работаем с числами и отсортированным столбцом.

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

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

Задача 6

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

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

Вопрос:
Как соединить текст из разных строчек в одной ячейке?

Решение:

Используем нашу знакомую формулу JOIN.

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

В качестве параметра delimiter мы указываем две кавычки и пробел между ними. Кавычки означают, что значение внутри текстовое.

Задача 7

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

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

Вопрос:
Как создать автоматический переводчик коротких текстов условий квестов на английский язык?

Решение:

К сожалению, это одна из немногих функций Excel, которых нет в Google Sheets. Но тут нам приходит на помощь функция Add-ons, которой в Excel нет.

Идем в Add-ons — Get add-ons. В открывшемся окне через поиск находим расширение Flookup и добавляем его в Google Sheets для своего аккаунта.

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

Сайт разработчика:
https://www.getflookup.com/

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

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

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

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

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

В нашем случае есть неизменное условие квеста (продай, собери и проч.) и меняющие свои окончания предметы (Драконье стекло, Драконьего стекла, и так далее). Соответственно, чтобы сделать все более универсальным, нужно разбить условия на части, иначе придется для каждого предмета в таблице соответствий писать все варианты сочетаний, вроде «собери веточку», «продай веточку», «укради веточку» (а в таком случае, можно обойтись обычной VLOOKUP).

Итак, формула:

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

Сначала разберем все, что идет перед FLOOKUP.

Ячейка в условии может быть
а) пустой
б) числовой
в) текстовой

Чтобы не было лишних ошибок, через уже знакомую формулу IFS исключим первые два случая.

Возвращает пустоту, если ячейка изначально была пустой.

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

Возвращает (копирует) число в точности, если в исходной ячейке было число.

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

Текстовыми значениями занимается FLOOKUP.

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

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

Где:
lookupValue — та ячейка, для которой мы будем искать соответствие (как и в VLOOKUP);
tableArray — диапазон, в котором мы будем искать соответствие (как и в VLOOKUP);
lookupCol — столбец, в котором мы ищем соответствие (в VLOOKUP это всегда первый столбец по умолчанию, а здесь он указывается);
indexNum — столбец, из которого мы будем возвращать соответствие;
threshold — минимальный процент соответствия содержимого ячеек, в котором поиск будет удачным. Значения от 0 (полное несоответствие) до 1 (полное соответствие);
rank — в случае, если находится несколько соответствий, то порядковый номер соответствия, которое надо вернуть (по умолчанию 1).

На простом примере это:

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

Формула проходится по значениям и проверяет их на соответствие.

Вот и получается, что 40% и более соответствия со словом «клубника» имеют:
КЛУБНИКА
КЛУБНИчКА
КЛУБНИчечКА

На клубнику не похожи, но 40% и более соответствия со словом «клубеша» имеют:
КЛУБЕнь
КЛУБЕШник

Совсем ни на что не похоже:
КЛУБусик

Процент соответствия, возможно, придется подбирать вручную, но это вполне реально.

Далее соединяем столбцы N и O с помощью уже известной формулы JOIN и разделителя в виде пробела.

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

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

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

Больше статей можно найти в моем блоге Вконтакте:

7373 показа
9.7K9.7K открытий
34 комментария

А в чем прикол держать все наработки в удобном сервисе, который может все удалить или в "вдруг" особенно в последних реалиях к ним может отвалиться доступ?


Что мешает за 10 минут развернуть какой нибудь бесплатный комьюнити сервер onlyoffice и работать небольшой командой имея чат, общие документы. проекты, почту, календарь и прочее, но не боясь потерять эти данные?

Ответить

Не знаю каковы на ваш взгляд предпосылки к тому, что к сервисам Google (которыми является не только Диск, но и тысячи других) вдруг будет закрыт доступ, я много лет пользуюсь Google Диском и никогда не испытывала с ним никаких проблем. Я не знакома с Only Office, но что-то мне с трудом верится, что по функциональности он не уступает Google таблицам. Но даже если это и не так, не понимаю, зачем изобретать велосипед там, где уже все давно изобрели за вас. Если вы переживаете за потерю данных (которая со мной за 10 лет не случалась никогда), установите Google диск на компьютер и храните бэкап документов в оффлайн режиме.

 
Оценка мобильного приложения в сторе у Only Office, кстати, не впечатляет. И сомневаюсь, что он настолько распространен, чтобы быть интегрированным в другие рабочие сервисы, как продукты Google.

Ответить

Кстати Excel Тьюринг-полный, так что на нем можно сразу и игру написать!

Ответить

Ну, тип, да, можно. https://www.exceltrick.com/interesting/excel-games-free-download/

p.s. ссылка не та, сейчас, тут просто встраивают флеш, похоже.

https://www.youtube.com/watch?v=6jyOJsJlLhI

Ответить

Комментарий недоступен

Ответить

Google диском? Да, можно.

Ответить

Комментарий недоступен

Ответить