Статьи

Полезности для Google Spreadsheets

Статья для того, кто уже знаком с основами работы в Excel или Google Spreadsheets и хочет хотя бы немного упростить работу безо всяких курсов или штудирования талмудов с формулами. Я опишу только те функции, которые мне приходилось использовать для работы и лайфхаки, которые пришлось выискивать опытным путём.

Обычно, для решения любой задачи я гуглю её (представь себе!), но надеюсь, что данная статья оставит у тебя в голове несколько полезных функций, и тебе не придётся тратить в будущем время, чтобы их нагуглить.


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

  • SUM, MULTIPLY
  • IF, AND, OR
  • MIN, MAX, AVERAGE
  • POW, SQRT
  • ROUND, ROUNDDOWN, ROUNDUP, TRUNC
  • COUNT, COUNTA
  • VLOOKUP, HLOOKUP

Даже этого перечня уже достаточно для решения многих простых и незамысловатых задач. Правда, недавно выяснилось, что о VLOOKUP и HLOOKUP знают не только лишь все. Вот с них и начнём.


VLOOKUP и HLOOKUP

Позволяет выполнить вертикальный или горизонтальный поиск содержимого указанной ячейки в указанном диапазоне и вернуть значение в ячейке с выбранным смещением по колонке или строке.

=VLOOKUP(search_key, range, index, [is_sorted])

=HLOOKUP(search_key, range, index, [is_sorted])

Допустим, в таблице юнитов данная функция позволит отыскать в любом диапазоне все значения по названию юнита (например, для сравнения):

1-NGo7XP2RO0jCS5Rcu23qEAВместо search_key вставляем ссылку на ячейку с именем, вместо range — ссылку на диапазон (поиск будет производиться в первой колонке диапазона), далее index — это порядковый номер колонки в указанном диапазоне, из которой будет взято значение. Обязательно указывайfalse четвёртым аргументом — не надо нам ничего сортировать!

1-Xw8V1DJUlBgsXdL7zIKxng
В результате мы получаем значения из таблицы напротив выбранных юнитов

NB: не забывай делать диапазон статичным при помощи $, чтобы при сдвиге формулы диапазон оставался неизменным.

FILTER

Отличный инструмент для быстрой выборки по указанным параметрам. FILTER возвращает отфильтрованный по определённым условиям список (даже в несколько столбцов):

=FILTER(range, condition1, [condition2, …])

1-t3dYiUPXxmq5ZDP28vxKawЕсть список всех предметов, которые персонаж может надеть. У каждого есть требования к силе и ловкости. FILTER поможет отобрать только те предметы, которые подходят по заданным требованиям. Для этого в range указываем таблицу с предметами и требованиями целиком, в condition1 делаем сравнение столбца с силой и ячейки с указанной силой, в condition2 делаем то же самое для ловкости.

Теперь можно указывать любые значения силы и ловкости и проверять, какие предметы подходят по указанным требованиям:1-_COPTcfl2SMK3EQwtLi8jQ

Для других полезных методов выборки советую почитать про MODEUNIQUE, SMALL и LARGE.

DATA VALIDATION

Удобный способ для создания выпадающих списков. В самом первом примере мы могли выбирать 2 юнитов из списка всех юнитов без необходимости вводить название юнита руками.

Для этого щёлкаем правой кнопкой мыши на ячейку, которая станет списком (или сразу несколько) и выбираем Data validation.1-8etzHu6qEU30Ll69Xw48HA

 Щёлкаем на иконку таблицы в поле ввода Criteria и выбираем диапазон для значений выпадающего списка (названия юнитов или предметов). Сохраняем.1-SuUgW7i6MVM4TWo9rsSj1Q

NB: если тебе нужен просто список значений, которые не нужны в текущем документе, то можно создать новую страницу, ввести туда значения, указать их для Data Validation, а саму страницу со значениями спрятать.

TRANSPOSE

Когда нужно введённые в столбик значения перенести в строку:

=TRANSPOSE(array_or_range)

1-YcSnWL4jAxa1KZQpyWaRJw

Просто выбирай нужный диапазон и готово:1-Zaue9dFkbRcSlvZCXtfI7g

NB: данные в строке ссылаются на указанный диапазон. Если ты хочешь убить столбец, то сначала выбираешь любую ячейку в строке и быстро клацаешь

Cmd+A Cmd+CCmd+Shift+V

или (Ctrl+A → Ctrl+C → Ctrl+Shift+V), если у тебя Windows.

Теперь можно смело удалять никому не нужный столбец.

ARRAYFORMULA

Совершенно неясно, как жить без этой формулы. Она позволяет забыть про copy-paste формул на сотню ячеек (на самом деле, она позволяет использовать любые формулы для работы с массивом данных, а не с одной ячейкой).

Давай представим, что у тебя есть таблица, в которую откуда-то подхватываются рецепты всех предметов в игре (штук 5000, например) со списком материалов, которые нужны для их крафта, и сохраняются вот в таком формате:1-xFx6ifNiLZ3DU1YSVqGiWA

А тебе нужен удобный интерфейс, который будет отображать конкретный рецепт с необходимыми материалами (или тебе надо будет подсчитать общее количество материалов для 20 разных рецептов). Вспоминаем DATA VALIDATION, VLOOKUP и читаем про COLUMN:1-fyPX0TG6DKJhEnmxy-4VmQ

  • блокируем сдвиги для рецепта по колонкам — $A2,
  • блокируем таблицу рецептов целиком (не задавая последнюю строку, чтобы при увеличении таблицы мы ничего не потеряли и ссылались на неё целиком)—Recipes!$A$2:$D
  • делаем автоматическое определение колонки для текущей ячейки и ничего тут не блокируем (очень важно, чтобы порядок колонок в нашем интерфейсе совпадал с форматом таблицы рецептов)—COLUMN(B2) (где B2—это ячейка нашей формулы)

Таким образом мы получаем автоматический поиск первого компонента в зависимости от выбранного рецепта.

Чтобы проделать то же самое по вертикали, мы можем выделить весь нужный нам диапазон и нажать Ctrl+Enter, тогда формула скопируется столько раз, сколько нужно. Но при добавлении новых строк, придётся каждый раз копировать формулу заново. НЕУДОБНО!

Магия происходит, если мы нажмём Ctrl+Shift+Enter. Тогда вокруг нашей текущей формулы появится заветная ARRAYFORMULA. Превращаем ячейку рецепта в массив рецептов ($A2 → $A2:$A) и дальше всё произойдёт само. И будет происходить каждый раз, когда ты добавишь строку с новым рецептом.1-LymLxcdOXaOVkqP6imBPCg

Если мы скопируем формулу в соседние ячейки по-горизонтали, то благодаря COLUMN мы всегда будем видеть верное значение, аARRAYFORMULA заполнит по-вертикали всё остальное сама.1-9XWS0-eswEyJKVhIx9mAmQ

NB: тебя тоже бесят #N/A в строках без рецепта? Предлагаю достаточно нетрудный лайфхак для их скрытия (если у кого-то есть более простой и лаконичный способ — поделитесь, пожалуйста):1-66N4ArD6eI3MOsjYXX_xhg


Конфетки напоследок: SPARKLINE

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

=SPARKLINE(data, [options])

1-4rOnUjS-N7wH2X8nlcABbQ

=SPARKLINE(D7:D15,{“charttype”,”column”}) и =SPARKLINE(D7:D15,{“charttype”,”line”})

line используется по умолчанию, а для столбцов используйте column. На самом деле, настроек там тьма и для копания сразу идите в полный гайд.

Конфетки напоследок: IMAGE

IMAGE простой способ вставить в таблицу немного картинок:

=IMAGE(url, [mode], [height], [width])

Скорее всего придётся настраивать, так как таблицы всё же не для картинок. Но раз Google даёт нам такую возможность, почему бы не воспользоваться?1-3Y436aPaZDglsvsif7PungВставляете ссылку на картинку (в кавычках) и, собственно, всё. Можно настроить режим отображения (fit, stretch, original, custom) и задать нужную width и height.


Думаю, что пока хватит этих функций, а в каком-нибудь из вариантов будущего я насобираю ещё кейсов и формул и напишу вторую статью (ведь нужно пройтись по импорту, условному форматированию, QUERY, рандомайзерам и вероятностям).

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

В общем:

  • Не забывай блокировать сдвиги с помощью $
  • Пользуйся COLUMN и ROW для выбора в VLOOKUP и HLOOKUP
  • Используй Data Validation
  • Используй ARRAYFORMULA
  • Делай всё хорошо и чисто, и тогда баланс посчитается сам

5 комментариев

Добавить комментарий