Эту и другие статьи можно найти в моем блоге Вконтакте: https://vk.com/mistle_gamer
Предыдущие статьи цикла:
Страница 4: Рецепты, картинки
Давайте немного отдохнем от всяких сложных формул, которые мы разбирали в предыдущих статьях, и займемся наведением красоты в таблицах, а также созданием приятных и читаемых блоков с рецептами крафта. Вы можете использовать подобный метод группировки информации не только для рецептов, но и например для того, чтобы наглядно посчитать мощность сетов брони или силу отряда персонажей — любую информацию, которая имеет структурный вид «компоненты — итоговый результат».
В этой статье будет не очень много новых формул, скорее в ней будут примеры применения уже изученных нами формул на практике. Надеюсь, она вдохновит вас на решение каких-нибудь собственных интересных задач.
Напоминаю, что для наглядности я создала табличку на Google диске. Вы можете ее открыть, скопировать к себе на диск, пощупать все формулы и посмотреть более подробно как они сделаны и вообще экспериментировать с ней по вашему усмотрению. Ссылка на табличку:
https://docs.google.com/spreadsheets/d/1bsDebERvoNifQQwQd9asLiUQRwjGUbz5XG0IT2f7uas/edit#gid=1971814535
Идем в File — Make a copy — выбираем папку на своем диске Google, куда вы хотите скопировать таблицу.
Содержание статьи
- Вставка картинки в ячейку (IMAGE, Insert Image)
- Вертикальный поиск по столбцу слева и скрытие ошибок в ячейках(INDEX, MATCH, IFERROR)
- Вертикальный поиск (VLOOKUP)
- Кастомные формулы в Conditional formatting (IF, VLOOKUP)
- Создание цепочки производств без рекурсии
- Выпадающие списки по List from a range (Data validation)
Таблица 1: Рецепты с конечным продуктом
Задача 1
Дано:
Таблица 1
Столбцы B-C — результат крафта;
Столбец E — иконка компонента рецепта;
Столбец F — количество компонента;
Столбец G — название компонента;
Столбец H — цена компонента.
Вопрос:
Как вставить картинки зелий в столбец B?
Решение:
Раньше картинки вставлялись в таблицы только одним способом — при помощи формулы:
Где:
url — ссылка на картинку, помещенная в кавычки;
mode — режим вставки картинки в клетку (обрезать по размеру клетки, растянуть, сохранять размер, указать свой размер);
height, width — высота и ширина картинки, если в mode был выбран свой размер.
За неимением других вариантов, эта формула относительно сносно справлялась со своими обязанностями, но при работе с ней возникал ряд проблем.
- Нужно заливать все картинки на сторонние хостинги, типа imgbb, а соответственно вы становитесь зависимы от работоспособности хостинга и ограничены бесплатными тарифами.
- При открытии документа с большим количеством картинок, таблицы очень долго обрабатывают формулу в каждой строке и картинки подгружаются не сразу.
- Картинки очень часто отображаются с ошибками, или не подгружаются вовсе.
Не так давно Google зарелизил новую фичу для таблиц — возможность вставки картинки в ячейку через меню документа Insert. Я не могу сказать, что работа с большим количеством картинок стала проще, т.к. для вставки нет хоткея и приходится совершать ряд кликов, но тем не менее, картинки в ячейках отображаются без перебоев и длительных подгрузок. Единственный минус — копировать содержимое ячейки с картинкой вы можете только внутри одного документа, если вы попытаетесь скопировать ячейку в другую таблицу, то ячейка будет пустой. Но всегда можно скопировать в другую таблицу лист и уже с него скопировать те картинки, которые вам нужны.
Итак, добавляем картинку в ячейку.
- Выберите ячейку, в которую вы хотите вставить картинку и в верхнем меню выберите Insert — Image — Image in cell.
- В открывшемся меню зайдите во вкладку Your albums.
- Выберите альбом и нужную картинку в нем.
Задача 2
Дано:
Таблица 1
Вопрос:
Как вставить картинку в ячейку автоматически, опираясь только на название компонента?
Решение:
Для решения этой задачи можно было бы воспользоваться формулой VLOOKUP, но в данной таблице картинка у нас идет перед названием предмета. Формула VLOOKUP осуществляет поиск слева направо, то есть первый столбец диапазона — это условие по которому мы ищем совпадение, а в столбце справа от условия находится искомое соответствие, которое нужно вернуть в ячейку. Поэтому для нашей таблицы формула VLOOKUP не подходит: у нас искомое значение (картинка) находится слева от столбца с совпадением (имени).
Вместо этого мы найдем нужную картинку с помощью формул INDEX и MATCH.
Формула в столбце с картинками Компонента 1 выглядит следующим образом:
Здесь у нас две знакомые формулы и одна незнакомая — IFERROR. Начнем со знакомых формул.
С помощью формулы INDEX, о которой я рассказывала в предыдущей статье, мы вычисляем ячейку, в которой находится искомое значение. Напомню, что она вычисляет координаты ячейки: мы даем ей массив, в котором осуществляется поиск, а дальше цифрами указываем номер столбца и номер ячейки:
В нашем текущем случае, массив поиска (reference) состоит из одного столбца: $B$15:$B$31. Дальше мы вычисляем номер строки с помощью формулы MATCH, о которой я также рассказывала в предыдущей статье. MATCH — это формула, которая ищет совпадение в диапазоне и возвращает порядковый номер этого совпадения.
В нашем случае G5, а именно название компонента является критерием для поиска совпадения (search_key), а в качестве диапазона для поиска совпадений мы указываем массив с названиями компонентов в Таблице 1.2 — $C$15:$C$31 (range). Search_type указываем 0, так как нам нужно точное совпадение по названию. Результатом формулы MATCH станет цифра, которая будет использоваться в формуле INDEX в качестве аргумента row.
Так получается, что с помощью MATCH мы вычисляем порядковый номер ячейки в столбце, в которой находится нужное нам название ресурса, а с помощью формулы INDEX в столбце с картинками находим ячейку с этим порядковым номером и возвращаем результат в Таблицу 1.
Теперь перейдем к новой формуле — IFERROR.
IFERROR — это формула, которая позволяет скрыть ошибки других формул, или показывать вместо них те значения, которые вам нужны.
Обычно, если формула сработала некорректно, она показывает в ячейке ошибку, прикрепляя к ней комментарий о том, почему произошла ошибка.
Иногда эти ошибки происходят там, где они нам не нужны. Например, в Таблице 1 ошибкой будет отмечена каждая ячейка в которой нет компонента, потому что без названия компонента картинку не найти. Такие ошибки отвлекают от сути и являются мусором в таблице. Скрыть их можно при помощи формулы IFERROR.
Синтаксис формулы:
Где:
value — это результат корректно работающей формулы.
[value_if_error] — это результат, который будет показываться в случае ошибки. Этот аргумент можно не указывать, тогда ячейка в случае ошибки просто останется пустой (я по старинке пишу здесь "", потому что так для меня удобнее читается вся формула в целом).
На простом примере:
Задача 3
Дано:
Таблица 1
Вопрос:
Как вычислить стоимость зелья?
Решение:
В Таблице 1 мы считаем стоимость исходя из того, что нам заранее известна стоимость всех компонентов. Это так называемая простая таблица рецептов с конечным продуктом. Стоимость всех компонентов заранее указана в Таблице 1.1.
Вспомогательная таблица 1.1
Так как нам известна стоимость компонентов, все что нам нужно сделать — это найти ее с помощью формулы VLOOKUP, к которой мы обращались уже не раз, а затем умножить найденную стоимость на количество компонента, требуемое по рецепту. Формула в ячейке H5 выглядит следующим образом:
Быстренько пробежимся по частям формулы.
Ищем соответствие для названия компонента в ячейке G5.
Ищем это соответствие во массиве Вспомогательной таблицы 1.1.
А конкретно в четвертом столбце по счету, в нем находится цена.
Умножаем найденное число на количество компонента, F5.
Для того, чтобы вычислить общую стоимость зелья, мы просто складываем стоимость всех компонентов в ячейке D5 и далее вниз. Естественно, расчет стоимости можно усложнить еще целым рядом параметров: наценкой города, или купца, у которого продается зелье, уценкой в случае продажи, редкостью, временем приготовления и так далее. Но это уже совсем другая история, которая легко надстраивается на данную базовую модель.
Задача 4
Дано:
Таблица 1
Вопрос:
Как добавить фон ячейке с картинкой компонента в соответствии с его принадлежностью к определенному классу компонентов?
Решение:
Во Вспомогательной таблице 1.1 мы разделили компоненты по типам:
— растение;
— монстр;
— спирт.
Для наглядности было бы здорово и ячейки Таблицы 1 раскрасить в цвета, соответствующие типу компонента.
Как мы помним из самой первой статьи с введением в Google таблицы, в качестве параметра условного форматирования можно выбирать не тольно простые условия, но и сложную собственную формулу.
Выделяем нужные нам ячейки с компонентами и идем в Format — Conditional formatting, и внутри выбираем Custom formula is.
Формула для выбора цвета выглядит следующим образом:
Как вы видите, она состоит сплошь из знакомых нам формул. Но все равно разберем ее по частям, чтобы было не оставалось никаких вопросов.
Так как в каких-то случаях у нас ячейка раскрашивается, а в каких-то — нет, начнем с формулы IF. В данном примере ячейка раскрашивается только если компонент является растением.
Для того, чтобы выяснить является ли компонент растением, мы берем его название из столбца с названием компонентов и ищем его соответствие во Вспомогательной таблице 1.1, возвращая при этом значение из второго столбца диапазона, столбца с типом компонента.
Теперь мы составляем выражение, которое приравнивает полученный в предыдущей части результат к тексту между кавычками. В результате этого выражения у нас может получится два результата: оно либо правдиво, либо нет (TRUE или FALSE).
И последним делом с помощью остатков формулы IF мы возвращаем TRUE, если результат логического выражение также TRUE и FALSE, если нет. Формула раскрашивает ячейку только если результатом всей формулы будет значение TRUE.
Таблица 2: Цепочка производств
Задача 5
Дано:
Таблица 2
Вопрос:
Как создать и посчитать цепочку производств?
Решение:
В первой части этой статьи мы рассматривали производства, имеющие конечный вид. То есть, в результате такого крафта получается сразу готовый продукт, который не идет в последующие крафты. Но что если для расчета баланса нам требуется такая система крафта, в которой мы создаем множество компонентов, представляющих собой лишь промежуточное звено в цепочке производств?
Такие системы используются довольно часто в сложных производствах, когда из полена мы создаем дерево, из дерева — прутики, затем из прутиков и металла — стрелы и так далее. Фактически, игрок может торговать каждым из этих предметов на любом этапе, так как все они представляют определенную игровую ценность, тем не менее, их себестоимость влияет на себестоимость следующих в цепочке предметов.
Решение, которое будет приведено ниже довольно простое, и не содержит в себе новых формул, но думаю логика его будет любопытна.
Итак, в предыдущей части статьи при расчете цены итогового продукта производства мы пользовались вспомогательной табличкой: просто брали оттуда цены с помощью формулы VLOOKUP и вставляли их в нашу таблицу крафтов. В текущей задаче цены нам брать неоткуда, так как цена всех компонентов заранее нам неизвестна и вычисляется она как раз в табличке с рецептами.
Для того, чтобы начать, нам нужно определить самые базовые компоненты, которые получаются без предварительного крафта. Они могут быть продуктами собирательства, могут получаться из беззатратного убийства монстров, могут скликиваться раз в определенное время с построек — это не так важно. Главное, эти ресурсы получаются не путем крафта. Мы точно знаем цену этих компонентов: ее всегда можно рассчитать из фактической цены в магазине, или исходя из пересчета времени, затрачиваемого на их получение, в игровую валюту.
Вспомогательная таблица 2.1
В нашем случае такими базовыми компонентами являются Синий мутаген, Зеленый мутаген и Красный мутаген, каждому из которых мы назначили цену в золоте в столбце AC.
Обратимся снова к Таблице 2. Вычисляем стоимость первого ресурса, который создается полностью из базовых компонентов. В нашем примере это растение Мышехвост. Его цена вычисляется так же, как и цены эликсиров в Таблице 1: с помощью формулы VLOOKUP по названию компонента мы ищем его цену во Вспомогательной таблице 2.2, умножаем ее на количество ресурса в рецепте, а затем складываем цены компонентов в столбце L.
В состав Подорожника уже входит Мышехвост и его цену тоже надо откуда-то брать. Все дело в том, что во Вспомогательной табличке 2.1 мы с помощью все той же VLOOKUP берем итоговые цены ресурсов из Таблички 1, столбцы K и L. Выше мы рассматривали ошибку, связанную с рекурсией формул, но с этими двумя табличками такого не случится благодаря базовым компонентам с фиксированной ценой.
Таблица 3: Гибкие рецепты
Задача 6
Дано:
Таблица 3
Вопрос:
Как сделать более гибкие рецепты из фиксированных типов компонентов?
Решение:
Такой вид таблицы будет особенно удобен для списка, скажем, сетов брони: все они состоят их пяти частей. Шлем, туловище, перчатки, ноги и сапоги.
Проще всего реализовать подобную идею с помощью выпадающих списков. Я писала об этой возможности Google таблиц во введении в этот цикл. Выбираем ячейку, в которой хотим создать выпадающий список, и идем в Data — Data validation и выбираем там тип проверки List from a range.
Нажимаем на Select data range и выбираем диапазон ячеек, которые должны войти в ваш выпадающий список. Каждая ячейка станет новым пунктом в этого списка.
Обращаю ваше внимание, что если вы прямо сейчас смотрите на табличку, которую открыли по ссылке в начале статьи, то выпадающие списки не будут у вас отображаться из-за того, что таблица защищена от редактирования. Если вы скопируете таблицу себе на диск через File — Make a copy, то выпадающий список появится в таблице.