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

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

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

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

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

Введение доступно по ссылке:
https://teletype.in/@mistle/9773

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

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

https://docs.google.com/spreadsheets/d/1bsDebERvoNifQQwQd9asLiUQRwjGUbz5XG0IT2f7uas/edit#gid=0

Страница 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.