Google Sheets для геймдизайнера.
Часть 1: Сортировка данных.
В рамках данного цикла статей мы рассмотрим следующие темы:
- сортировка данных;
- работа с текстом;
- рандомайзеры;
- создание таблиц с рецептами крафта;
- упрощение работы с большим объемом данных.
Пойдем, конечно, от простого к сложному.
В каждой теме я постараюсь охватить максимальное количество базовых приемов, которые пришли мне в голову и показались потенциально полезными, так как могут использоваться не только в чистом виде, но и в различных вариациях, которые подойдут именно вам.
Итак, поехали.
Для того, чтобы было нагляднее, я создала табличку на Google диске. Вы можете ее открыть, скопировать к себе на диск, пощупать все формулы и посмотреть более подробно как они сделаны. Предполагается, что вы откроете таблицу и будете смотреть на нее параллельно обращаясь к статье для упрощения процесса чтения формул.
Ссылка на табличку
Страница 1: Сортировка данных
На данной странице мы попробуем рассмотреть различные варианты сортировки данных и вывода интересующей нас информации из таблицы с расчетами.
Содержание статьи:
- Присвоение свойств по значению (IFS)
- Подсчет количества ячеек, соответствующих условию (COUNTIF)
- Округление (IFS, CEILING)
- Сортировка части диапазона по параметрам (SORT, FILTER)
- Усложненная сортировка с ограничением количества итоговых значений (SORTN, SORT, FILTER)
- Сортировка по алфавиту (SORT)
Таблица 1: Присвоение свойств по значению
Задача 1
Дано:
Столбец B — уровни, на которых оружие становится доступным игроку;
Столбец C — название оружия;
Столбец D — урон оружия.
Вопрос:
К какому тиру относится оружие с определенным уроном?
Решение
Вспомогательная таблица 1.1 — Параметры тиров.
Тир в данном случае — это уровень редкости оружия. Каждый тир имеет определенную дельту урона.
Например,
Тир 1 — это урон 0–150
Тир 2 — это урон 151–250
и так далее.
Формула определения тира оружия по урону
Общий вид формулы:
IFS — это формула, позволяющая задать группу пар условие — значение. Формула осуществляет проверку этих условий и если хоть одно из них является правдивым, то формула возвращает идущее в паре с первым правдивым условием значение.
Синтаксис этой формулы следующий:
Где:
condition1 — какое-либо условие;
value1 — значение в случае, если условие исполняется;
[condition2, …], [value2, …] — альтернативные условия и значения.
На простом примере:
В нашем случае формула расшифровывается так:
Если (ячейка C7 больше или равна 150, Обычный, ячейка C7 больше или равна 250 — Редкий) и так далее.
Задача 2
Дано:
Столбец B — уровни, на которых оружие становится доступным игроку;
Столбец C — название оружия;
Столбец D — урон оружия;
Столбец E — тип тира оружия по урону.
Вопрос:
Сколько оружия каждого тира есть в табличке?
Решение:
Вспомогательная таблица 1.2 — Подсчет количества оружия по тирам
Общий вид формулы:
COUNTIF — это формула, которая подсчитывает количество ячеек, соответствующих определенному условию.
Синтаксис этой формулы следующий:
Где:
range — диапазон, в котором формула производит поиск и подсчет;
criterion — условие, по которому производится проверка.
На простом примере:
В нашем случае это:
Считаем если (ищем в диапазоне $D$7:$D$12, Обычное)
Считаем если (ищем в диапазоне $D$7:$D$12, Редкое)
И так далее.
Таблица 2: Сортировка результатов
Задача 1
Дано:
Столбец G — уровень, на котором открывается оружие;
Столбец H — название оружия;
Столбец J — тир, к которому принадлежит оружие.
Вопрос:
Как посчитать показатель урона в соответствии с тиром, присвоенным оружию?
Решение:
Вспомогательная таблица 2.1 — Модификаторы тиров
Для того, чтобы подсчитать урон, каждому тиру мы присвоим определенный модификатор. Это простой и быстрый способ получить цифры для тестовой таблицы, ваши вычисления, конечно же, могут быть намного сложнее. Здесь мы рассматриваем не логику самого подсчета, а еще один вариант использования формулы IFS и способ округления результата до красивого числа.
Для этого мы использовали формулу:
Уровень игрока мы умножили на базовый урон 50, а после умножили на модификатор урона, используя известную уже формулу IFS, а затем округлили полученное значение до кратного пяти.
Как округлять?
CEILING — это формула, которая округляет значение вверх до значения, кратного указанному. По умолчанию, если число не указано, округление будет производиться до единицы.
Синтаксис формулы:
Где:
value — значение, которое нужно округлить;
factor — число, кратно которому вы хотите округлиться.
=CEILING (23, 5) — будет равен 25
=CEILING (0,035, 0,01) — будет равен 0,04
Задача 2
Дано:
Таблица 2
Вопрос:
Как вывести только оружие одного тира и отсортировать его по урону?
Решение:
Вспомогательная таблица 2.2 — Все оружие 1 тира
Общий вид формулы:
Здесь мы использовали сразу две формулы — SORT и FILTER.
SORT — это формула, которая сортирует ряды указанного вами диапазона по значениям одной или нескольких колонок этого диапазона.
Синтаксис формулы
Где:
range — диапазон, который мы хотим отсортировать;
sort_column — колонка, по значениям которой будет производиться сортировка;
is_ascending — если напишите TRUE, то сортировка будет выполнена по возрастанию, если FALSE — по убыванию;
[sort_column2, …], [is_ascending2, …] — дополнительные колонки, по которым может производиться сортировка, но в порядке очереди. То есть, приоритетной будет все-таки первая колонка в формуле, остальные сортировки будут второстепенными и не будут конфликтовать с основной.
На простом примере:
Кстати, обратите внимание, что в результате идет сначала Лента, а затем Дикси. Это потому, что я дополнительно отсортировала таблицу по убыванию по первому столбцу, а именно — названию магазина.
FILTER — это функция, которая фильтрует выбранный вами диапазон по одному, или нескольким условиям и отображает только результаты, подходящие под условие.
Синтаксис формулы:
Где:
range — это диапазон, который мы хотим отфильтровать
condition1, [condition2, …] — условия, по которым будет осуществляться фильтрация.
На простом примере:
Соберем вместе две формулы и рассмотрим их относительно нашей таблицы:
Через функцию FILTER определяем диапазон, который хотим отсортировать, а именно выбираем из таблицы только строчки с оружием первого тира.
Через функцию SORT мы сортируем нашу таблицу 2 по второй колонке с уроном оружия в возрастающем порядке значений.
Формулу SORT не нужно растягивать, вы вводите ее только в одну ячейку и она сама заполняет остальные. Если ячейки, которые нужно будет заполнить формуле SORT уже содержат какие-либо значения, то она не станет их перезаписывать и выдаст соответствующую ошибку.
Задача 2
Дано:
Таблица 2
Вопрос:
Как вывести только самое сильное и самое слабое оружие определенного тира?
Решение:
Вспомогательная таблица 2.3 — Самое сильное и слабое оружие 1 тира
Общий вид формулы:
SORTN — это формула, которая так же как и SORT сортирует ряды диапазона по значениям одной или более колонок, но в отличие от обычной функции SORT, вы можете дополнительно указать, сколько значений вы хотите вывести и каким образом.
Синтаксис формулы:
Где:
range — это диапазон, который мы хотим отсортировать;
n — это количество результатов, которые мы хотим вывести, минимум — одно значение;
display_ties_mode — это способ, по которому должны выводиться совпадения:
- если поставить здесь 0, то формула выведет вам указанное в параметре n количество строк (или меньше, если энных строк не наберется столько);
- если поставить здесь 1, то формула выведет вам указанное в параметре n количество строк и дополнительно все строки, идентичные строке с порядковым номером, указанным в параметре n;
- если поставить здесь 2, то формула выведет вам указанное в параметре n количество строк, но при этом исключит все повторяющиеся значения;
- если поставить здесь 3, то формула выведет вам указанное в параметре n количество строк, но покажет только уникальные строки, сгруппированные с их точными копиями.
sort_column — это колонка, по значениям которой будет производиться сортировка;
is_ascending — если напишите TRUE, то сортировка будет выполнена по возрастанию, если FALSE — по убыванию.
На простом примере:
Табличка, с которой дальше будем работать для примера
Если мы выставим display_ties_mode (способ показа) 0, то получим такой результат:
Формула отсортировала таблицу по цене товара и показа нам указанное в параметре display_ties_mode количество строк. Получилась табличка с тремя самыми дешевыми товарами из исходных данных.
Если мы выставим display_ties_mode 1, то получим следующее:
Три самых дешевых товара, как и при display_ties_mode 0, плюс товар, цена которого дублирует цену из третьей строки (картошка 35 и клубника 35). То есть, указанное в параметре количество строк, плюс дубли третьей строки.
Если мы выставим display_ties_mode 2, то получим следующее:
Три товара с уникальной ценой, расположенные по возрастанию цены. То есть, формула отсортировала таблицу по цене товара и показала нам три самых дешевых товара, исключив все прочие с дублирующей ценой. Поэтому вместо Яблок из Ашана за 30 рублей, как было с display_ties_mode 0, у нас появилась Картошка за 45.
Если мы выставим display_ties_mode 3, то получим следующее:
Три товара, плюс их дубли по цене из списка. Дубли при этом группируются вместе с товарами, то есть, по факту наши искомые три товара — это по прежнему Лента — Картошка — 30, Пятерочка — Картошка — 35 и Ашан — Картошка — 45. Если у Клубники за 35 изменить цену на 36 рублей, то картошка пропадет из списка, выводимого формулой, так как третьим значением станет клубника.
В нашем случае это:
(SORT (FILTER (G7:I12, I7:I12 = 1), 2, TRUE) — это формула из предыдущего примера, которая выбирает только значения оружия первого тира
Функцией SORTN мы показываем, что хотим получить только 1 значение, и отобразить только его, поэтому выбираем display_ties_mode 0. В качестве колонки сортировки выбираем вторую, с уроном оружия.
Чтобы отобразить самое слабое оружие, мы указываем is_ascending — TRUE, самое сильное — FALSE.
Задача 3
Дано:
Таблица 2
Вопрос:
Как отсортировать диапазон по алфавиту?
Решение:
Вспомогательная таблица 2.4 — Сортировка всего оружия по алфавиту
Используем обычную функцию SORT
В нашем случае это:
= Сортируем (Выделяем всю таблицу, сортируем по 1 столбцу, сортируем в порядке возрастания)
Простые формулы, разобранные в статье, мне показались полезными для решения обычных рабочих задач. Конечно, в каждом конкретном случае у вас могут быть дополнительные условия, которые усложнят весь способ подсчета или сортировки, добавят в формулы новые надстройки. Составление формул для уникального баланса — это всегда творческий процесс.Тем не менее, зная то, как работают базовые формулы, слепить из них что-то свое довольно просто.
Спасибо, я надеюсь, что статья была вам полезна!