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

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

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

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

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

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

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

Дано:
Таблица 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 должен быть отсортирован по возрастанию, иначе формула будет работать неправильно.

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

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

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

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

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

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

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

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

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

К сожалению, это одна из немногих функций 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 — Таблица соответствий

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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