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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дано:
Столбец 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 - Все оружие 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.3 - Самое сильное и слабое оружие 1 тира

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

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

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

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

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.

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

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

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

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

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

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

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

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

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