Полезности для Google Spreadsheets
Говорят, что мы используем их всего на 5%
Оригинал текста и обсуждение на GDCuffs.com
Статья для того, кто уже знаком с основами работы в 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])
Допустим, в таблице юнитов данная функция позволит отыскать в любом диапазоне все значения по названию юнита (например, для сравнения):
Вместо search_key вставляем ссылку на ячейку с именем, вместо range — ссылку на диапазон (поиск будет производиться в первой колонке диапазона), далее index — это порядковый номер колонки в указанном диапазоне, из которой будет взято значение. Обязательно указывай false четвёртым аргументом — не надо нам ничего сортировать!
NB: не забывай делать диапазон статичным при помощи $, чтобы при сдвиге формулы диапазон оставался неизменным.
FILTER
Отличный инструмент для быстрой выборки по указанным параметрам. FILTER возвращает отфильтрованный по определённым условиям список (даже в несколько столбцов):
=FILTER(range, condition1, [condition2, …])
Есть список всех предметов, которые персонаж может надеть. У каждого есть требования к силе и ловкости. FILTER поможет отобрать только те предметы, которые подходят по заданным требованиям. Для этого в range указываем таблицу с предметами и требованиями целиком, в condition1 делаем сравнение столбца с силой и ячейки с указанной силой, в condition2 делаем то же самое для ловкости.
Теперь можно указывать любые значения силы и ловкости и проверять, какие предметы подходят по указанным требованиям:
Для других полезных методов выборки советую почитать про MODE, UNIQUE, SMALL и LARGE.
DATA VALIDATION
Удобный способ для создания выпадающих списков. В самом первом примере мы могли выбирать 2 юнитов из списка всех юнитов без необходимости вводить название юнита руками.
Для этого щёлкаем правой кнопкой мыши на ячейку, которая станет списком (или сразу несколько) и выбираем Data validation.
Щёлкаем на иконку таблицы в поле ввода Criteria и выбираем диапазон для значений выпадающего списка (названия юнитов или предметов). Сохраняем.
NB: если тебе нужен просто список значений, которые не нужны в текущем документе, то можно создать новую страницу, ввести туда значения, указать их для Data Validation, а саму страницу со значениями спрятать.
TRANSPOSE
Когда нужно введённые в столбик значения перенести в строку:
=TRANSPOSE(array_or_range)
Просто выбирай нужный диапазон и готово:
NB: данные в строке ссылаются на указанный диапазон. Если ты хочешь убить столбец, то сначала выбираешь любую ячейку в строке и быстро клацаешь
Cmd+A → Cmd+C → Cmd+Shift+V
или (Ctrl+A → Ctrl+C → Ctrl+Shift+V), если у тебя Windows.
Теперь можно смело удалять никому не нужный столбец.
ARRAYFORMULA
Совершенно неясно, как жить без этой формулы. Она позволяет забыть про copy-paste формул на сотню ячеек (на самом деле, она позволяет использовать любые формулы для работы с массивом данных, а не с одной ячейкой).
Давай представим, что у тебя есть таблица, в которую откуда-то подхватываются рецепты всех предметов в игре (штук 5000, например) со списком материалов, которые нужны для их крафта, и сохраняются вот в таком формате:
А тебе нужен удобный интерфейс, который будет отображать конкретный рецепт с необходимыми материалами (или тебе надо будет подсчитать общее количество материалов для 20 разных рецептов). Вспоминаем DATA VALIDATION, VLOOKUP и читаем про COLUMN:
- блокируем сдвиги для рецепта по колонкам — $A2,
- блокируем таблицу рецептов целиком (не задавая последнюю строку, чтобы при увеличении таблицы мы ничего не потеряли и ссылались на неё целиком)—Recipes!$A$2:$D
- делаем автоматическое определение колонки для текущей ячейки и ничего тут не блокируем (очень важно, чтобы порядок колонок в нашем интерфейсе совпадал с форматом таблицы рецептов)—COLUMN(B2) (где B2—это ячейка нашей формулы)
Таким образом мы получаем автоматический поиск первого компонента в зависимости от выбранного рецепта.
Чтобы проделать то же самое по вертикали, мы можем выделить весь нужный нам диапазон и нажать Ctrl+Enter, тогда формула скопируется столько раз, сколько нужно. Но при добавлении новых строк, придётся каждый раз копировать формулу заново. НЕУДОБНО!
Магия происходит, если мы нажмём Ctrl+Shift+Enter. Тогда вокруг нашей текущей формулы появится заветная ARRAYFORMULA. Превращаем ячейку рецепта в массив рецептов ($A2 → $A2:$A) и дальше всё произойдёт само. И будет происходить каждый раз, когда ты добавишь строку с новым рецептом.
Если мы скопируем формулу в соседние ячейки по-горизонтали, то благодаря COLUMN мы всегда будем видеть верное значение, а ARRAYFORMULA заполнит по-вертикали всё остальное сама.
NB: тебя тоже бесят #N/A в строках без рецепта? Предлагаю достаточно нетрудный лайфхак для их скрытия (если у кого-то есть более простой и лаконичный способ — поделитесь, пожалуйста):
Конфетки напоследок: SPARKLINE
SPARKLINE — это прекрасная формула для быстрой отрисовки простейших графиков (только визуальную их часть) прямо внутри клетки. Т.е. делаете merge каких-нибудь клеток, чтобы можно было хоть разглядеть чего он там рисует.
=SPARKLINE(data, [options])
line используется по умолчанию, а для столбцов используйте column. На самом деле, настроек там тьма и для копания сразу идите в полный гайд.
Конфетки напоследок: IMAGE
IMAGE простой способ вставить в таблицу немного картинок:
=IMAGE(url, [mode], [height], [width])
Скорее всего придётся настраивать, так как таблицы всё же не для картинок. Но раз Google даёт нам такую возможность, почему бы не воспользоваться?
Вставляете ссылку на картинку (в кавычках) и, собственно, всё. Можно настроить режим отображения (fit, stretch, original, custom) и задать нужную width и height.
Думаю, что пока хватит этих функций, а в каком-нибудь из вариантов будущего я насобираю ещё кейсов и формул и напишу вторую статью (ведь нужно пройтись по импорту, условному форматированию, QUERY, рандомайзерам и вероятностям).
А пока напиши, пожалуйста, в комментариях о том, какие полезные приёмы знаешь ты, или как лаконичнее решить проблемы, решаемые мной выше.
В общем:
- Не забывай блокировать сдвиги с помощью $
- Пользуйся COLUMN и ROW для выбора в VLOOKUP и HLOOKUP
- Используй Data Validation
- Используй ARRAYFORMULA
- Делай всё хорошо и чисто, и тогда баланс посчитается сам
Оригинал текста и обсуждение на GDCuffs.com
