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

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

Введение доступно по ссылке:

Кроме введения, которое содержит в себе небольшое описание базовых функций Google таблиц, в рамках данного цикла статей мы рассмотрим следующие темы:

  • сортировка данных;
  • работа с текстом;
  • рандомайзеры;
  • создание таблиц с рецептами крафта;
  • упрощение работы с большим объемом данных.

Пойдем, конечно, от простого к сложному.

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

Итак, поехали.

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

Страница 1: Сортировка данных

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

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

  1. Присвоение свойств по значению (IFS)
  2. Подсчет количества ячеек, соответствующих условию (COUNTIF)
  3. Округление (IFS, CEILING)
  4. Сортировка части диапазона по параметрам (SORT, FILTER)
  5. Усложненная сортировка с ограничением количества итоговых значений (SORTN, SORT, FILTER)
  6. Сортировка по алфавиту (SORT)

Таблица 1: Присвоение свойств по значению

Задача 1

Дано:
Столбец B - уровни, на которых оружие становится доступным игроку;
Столбец C - название оружия;
Столбец D - урон оружия.

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

Вопрос:
К какому тиру относится оружие с определенным уроном?

Решение

Вспомогательная таблица 1.1 - Параметры тиров.

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

Тир в данном случае — это уровень редкости оружия. Каждый тир имеет определенную дельту урона.

Например,
Тир 1 - это урон 0-150
Тир 2 - это урон 151-250
и так далее.

Формула определения тира оружия по урону

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

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

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

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

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

Где:
condition1 - какое-либо условие;
value1 - значение в случае, если условие исполняется;
[condition2, ...], [value2, ...] - альтернативные условия и значения.

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

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

В нашем случае формула расшифровывается так:

Если (ячейка C7 больше или равна 150, Обычный, ячейка C7 больше или равна 250 — Редкий) и так далее.

Задача 2

Дано:
Столбец B - уровни, на которых оружие становится доступным игроку;
Столбец C - название оружия;
Столбец D - урон оружия;
Столбец E - тип тира оружия по урону.

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

Вопрос:
Сколько оружия каждого тира есть в табличке?

Решение:

Вспомогательная таблица 1.2 - Подсчет количества оружия по тирам

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

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

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

COUNTIF - это формула, которая подсчитывает количество ячеек, соответствующих определенному условию.

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

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

Где:
range - диапазон, в котором формула производит поиск и подсчет;
criterion - условие, по которому производится проверка.

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

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

В нашем случае это:

Считаем если (ищем в диапазоне $D$7:$D$12, Обычное)
Считаем если (ищем в диапазоне $D$7:$D$12, Редкое)
И так далее
.

Таблица 2: Сортировка результатов

Задача 1

Дано:
Столбец G - уровень, на котором открывается оружие;
Столбец H - название оружия;
Столбец J - тир, к которому принадлежит оружие.

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

Вопрос:
Как посчитать показатель урона в соответствии с тиром, присвоенным оружию?

Решение:

Вспомогательная таблица 2.1 - Модификаторы тиров

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

Для того, чтобы подсчитать урон, каждому тиру мы присвоим определенный модификатор. Это простой и быстрый способ получить цифры для тестовой таблицы, ваши вычисления, конечно же, могут быть намного сложнее. Здесь мы рассматриваем не логику самого подсчета, а еще один вариант использования формулы IFS и способ округления результата до красивого числа.

Для этого мы использовали формулу:

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

Уровень игрока мы умножили на базовый урон 50, а после умножили на модификатор урона, используя известную уже формулу IFS, а затем округлили полученное значение до кратного пяти.

Как округлять?

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

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

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

Где:
value - значение, которое нужно округлить;
factor - число, кратно которому вы хотите округлиться.

=CEILING (23, 5) — будет равен 25
=CEILING (0,035, 0,01) — будет равен 0,04

Задача 2

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

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

Вопрос:
Как вывести только оружие одного тира и отсортировать его по урону?

Решение:

Вспомогательная таблица 2.2 - Все оружие 1 тира

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

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

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

Здесь мы использовали сразу две формулы - SORT и FILTER.

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

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

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

Где:
range - диапазон, который мы хотим отсортировать;
sort_column - колонка, по значениям которой будет производиться сортировка;
is_ascending - если напишите TRUE, то сортировка будет выполнена по возрастанию, если FALSE - по убыванию;
[sort_column2, ...], [is_ascending2, ...] - дополнительные колонки, по которым может производиться сортировка, но в порядке очереди. То есть, приоритетной будет все-таки первая колонка в формуле, остальные сортировки будут второстепенными и не будут конфликтовать с основной.

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

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

Кстати, обратите внимание, что в результате идет сначала Лента, а затем Дикси. Это потому, что я дополнительно отсортировала таблицу по убыванию по первому столбцу, а именно — названию магазина.

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

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

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

Где:
range - это диапазон, который мы хотим отфильтровать
condition1, [condition2, …] - условия, по которым будет осуществляться фильтрация.

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

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

Соберем вместе две формулы и рассмотрим их относительно нашей таблицы:

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

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

Через функцию SORT мы сортируем нашу таблицу 2 по второй колонке с уроном оружия в возрастающем порядке значений.

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

Задача 2

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

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

Вопрос:
Как вывести только самое сильное и самое слабое оружие определенного тира?

Решение:

Вспомогательная таблица 2.3 - Самое сильное и слабое оружие 1 тира

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

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

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

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

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

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

Где:
range - это диапазон, который мы хотим отсортировать;
n - это количество результатов, которые мы хотим вывести, минимум - одно значение;
display_ties_mode - это способ, по которому должны выводиться совпадения:

  • если поставить здесь 0, то формула выведет вам указанное в параметре n количество строк (или меньше, если энных строк не наберется столько);
  • если поставить здесь 1, то формула выведет вам указанное в параметре n количество строк и дополнительно все строки, идентичные строке с порядковым номером, указанным в параметре n;
  • если поставить здесь 2, то формула выведет вам указанное в параметре n количество строк, но при этом исключит все повторяющиеся значения;
  • если поставить здесь 3, то формула выведет вам указанное в параметре n количество строк, но покажет только уникальные строки, сгруппированные с их точными копиями.

sort_column - это колонка, по значениям которой будет производиться сортировка;
is_ascending - если напишите TRUE, то сортировка будет выполнена по возрастанию, если FALSE - по убыванию.

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

Google Sheets для геймдизайнера. Часть 1: Сортировка данных
Табличка, с которой дальше будем работать для примера
Табличка, с которой дальше будем работать для примера
Google Sheets для геймдизайнера. Часть 1: Сортировка данных

Если мы выставим display_ties_mode (способ показа) 0, то получим такой результат:

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

Формула отсортировала таблицу по цене товара и показа нам указанное в параметре display_ties_mode количество строк. Получилась табличка с тремя самыми дешевыми товарами из исходных данных.

Если мы выставим display_ties_mode 1, то получим следующее:

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

Три самых дешевых товара, как и при display_ties_mode 0, плюс товар, цена которого дублирует цену из третьей строки (картошка 35 и клубника 35). То есть, указанное в параметре количество строк, плюс дубли третьей строки.

Если мы выставим display_ties_mode 2, то получим следующее:

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

Три товара с уникальной ценой, расположенные по возрастанию цены. То есть, формула отсортировала таблицу по цене товара и показала нам три самых дешевых товара, исключив все прочие с дублирующей ценой. Поэтому вместо Яблок из Ашана за 30 рублей, как было с display_ties_mode 0, у нас появилась Картошка за 45.

Если мы выставим display_ties_mode 3, то получим следующее:

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

Три товара, плюс их дубли по цене из списка. Дубли при этом группируются вместе с товарами, то есть, по факту наши искомые три товара - это по прежнему Лента - Картошка - 30, Пятерочка - Картошка - 35 и Ашан - Картошка - 45. Если у Клубники за 35 изменить цену на 36 рублей, то картошка пропадет из списка, выводимого формулой, так как третьим значением станет клубника.

В нашем случае это:

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

(SORT (FILTER (G7:I12, I7:I12 = 1), 2, TRUE) - это формула из предыдущего примера, которая выбирает только значения оружия первого тира

Функцией SORTN мы показываем, что хотим получить только 1 значение, и отобразить только его, поэтому выбираем display_ties_mode 0. В качестве колонки сортировки выбираем вторую, с уроном оружия.

Чтобы отобразить самое слабое оружие, мы указываем is_ascending - TRUE, самое сильное — FALSE.

Задача 3

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

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

Вопрос:
Как отсортировать диапазон по алфавиту?

Решение:

Вспомогательная таблица 2.4 - Сортировка всего оружия по алфавиту

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

Используем обычную функцию SORT

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

В нашем случае это:

= Сортируем (Выделяем всю таблицу, сортируем по 1 столбцу, сортируем в порядке возрастания)

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

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

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

121121
18 комментариев

Школьный урок информатики :)

7
Ответить

Повезло, у меня в школе такой информатики не было :)

11
Ответить

Очень полезно для тех кто только начинает, или меняет профиль

2
Ответить

Спасибо за статью, понятно и полезно. Узнал для себя несколько новых нюансов. Буду ждать продолжения.

2
Ответить

Ага залез почитать за геймдев

1
Ответить

Не могу понять, вы рады, или нет :)

3
Ответить

Это и есть геймдев :D Ничуть не в меньшей степени, чем работа художника или программиста.

2
Ответить