Денис Балуев - Секреты приложений Google
Рисунок 3.13. Функция PMT рассчитывает сумму ежемесячного ипотечного платежа
В ответ на введенные данные система выдаст вам сумму ежемесячного платежа с точностью до копейки. Проверив собственный ипотечный кредит таким образом, я выяснил, что мой банк не закладывает в выплаты скрытых процентов. А ваш? Еще одна весьма полезная формула находится в разделе «Поиск» и называется VLOOKUP. О, это просто классная вещь! Представьте, что у вас есть некая таблица нормативов – допустим, времени, затрачиваемого на то или иное действие: сборку станком сложных деталей, поездку на поезде в населенные пункты, бег на различные дистанции – что угодно! Ведя в другой таблице запись реальных данных, мы хотим сверяться с нормативом и для каждого нового значения находить соответствующее ему время. Вот, к примеру, таблица времени на изготовление деталей (рис. 3.14).
В другой таблице мы ведем учет реального времени на изготовление деталей и хотим иметь возможность сравнивать действительное время с нормативом (рис. 3.15).
Рисунок 3.14. Таблица нормативов. Изготовить винт можно в три раза быстрее, чем болт
Рисунок 3.15. Учет времени изготовления деталей. Реальность может расходиться с нормативом. Но как это проверить?
Для этого в ячейке колонки «Норматив» введем вот такую формулу (рис. 3.16):
Рисунок 3.16. Решаем задачу с помощью функции VLOOKUP
Из чего состоит формула? Первый аргумент – значение, по которому будет осуществляться поиск. В ячейке F2 мы ввели название детали, чтобы искать по ней соответствующее стандартное время изготовления. Где будем искать? Ответ на этот вопрос нужно давать во втором аргументе. Смотрите – там указан диапазон A2: B6 – это координаты нашей первой таблицы. Знаки «$» нужны для того, чтобы при копировании формулы в другие ячейки данный диапазон не менялся, был зафиксирован. Третий аргумент – указание, из какого по порядку столбца брать значение, ведь, вообще говоря, в таблице нормативов могут находиться не только время, но и себестоимость продукции, ссылки на файлы с инструкциями, другая информация. Поэтому в качестве номера колонки с результатом мы ставим цифру 2. Последний аргумент необходимо указывать равным False (Ложь) – иначе для правильного поиска исходную таблицу придется сначала отсортировать по возрастанию. Скопируйте полученную формулу в остальные ячейки колонки «Норматив» – теперь система для каждой новой записи будет находить и выводить на экран соответствующий норматив (рис. 3.17). Ну разве не замечательная формула?
Рисунок 3.17. Кстати, отклонение от норматива можно подсветить предупреждающим красным цветом
Работу формул можно сделать еще более наглядной, если использовать механизм именованных диапазонов. Что это такое? Несмотря на мудреное название – донельзя простая вещь. Вместо непонятных названий ячеек мы даем им осмысленные имена. Да будет «Премия» вместо B12, «Объем» вместо C1 и «Месяцы» вместо диапазона E15: E27! Работать с именами в формулах можно точно так же, как и с обычными кодами ячеек, отчего вычисления приобретают более наглядный вид. Пример: «=SUM (Премия; Ставка; Надбавка)» гораздо яснее, чем какое-то «=SUM (С8; Р5; D9)». Для создания именованных диапазонов направляемся прямиком в пункт меню «Изменить – Именованные диапазоны – Определить новый диапазон…» и в появившемся окне последовательно вводим нужные нам диапазоны и их псевдонимы, не забывая каждый раз нажимать кнопку «Добавить» (рис. 3.18).
Рисунок 3.18. Безымянные ячейки обретают имя. И смысл
Удалить именование можно тут же, щелкнув по крестику справа от диапазона, или позже, из пункта меню «Изменить – Именованные диапазоны – Управление диапазонами…». Удобно, что после подобного именования любой из них можно выделить на экране – для этого достаточно найти его имя в выпадающем списке «Изменить – Именованные диапазоны».
Иногда вам кажется, что, несмотря на все ваши усилия, какая-то формула или вычисленное значение все же будут непонятны тому человеку, который станет их просматривать. Возможно даже, что этим человеком будете через некоторое время вы сами! Нет ничего более обескураживающего, чем смотреть на собственные творения и не понимать ровным счетом ничего! В этом случае на помощь придут комментарии. Просто щелкните правой кнопкой мыши на любой ячейке и выберите пункт контекстного меню «Вставить комментарий», после чего в небольшом окошке рядом с ячейкой, своим желтым цветом напоминающем листочки Post-It, введите поясняющий текст (рис. 3.19).
Рисунок 3.19. Комментарии к ячейкам
О наличии ремарки теперь будет напоминать пожелтевший правый верхний уголок ячейки. Просмотреть содержание комментария можно, удерживая курсор на ячейке, – всплывающая подсказка не замедлит появиться. Для удаления комментария выберите пункт «Удалить комментарии» все того же всплывающего меню.
Цвета по правилам
Девяносто процентов информации мы воспринимаем с помощью глаз, визуально. Поток данных, обрушивающийся на нас с экранов мониторов, огромен. Среди тысяч цифр немудрено пропустить тот или иной важный тренд, намечающуюся проблему, аварийное отклонение! Для облегчения восприятия и придумана визуализация – различные способы более наглядного представления данных. Самый простой и эффективный способ визуализации – выделение цветом. С детства нам знаком принцип работы светофора: зеленый – иди, красный – стой. Почему бы не выводить данные, скажем, об объемах продаж торговой сети по тому же принципу? Магазины, выполнившие план продаж за месяц, светятся спокойным зеленым – здесь все в порядке, а потому на цифры можно бросить лишь беглый взгляд. Красный цвет – аварийный: что-то идет не так, месячные обороты упали ниже допустимого уровня, и ситуация требует экстренного вмешательства. Желтый цвет применяется для пограничных ситуаций – например, значение находится в требуемом диапазоне, но достигнутый в текущем месяце объем продаж явно ниже рекордов предыдущего месяца.
Разумеется, подобная визуализация не ограничивается финансовыми отчетами. Вместо сумм торгового оборота можно подставить число ежедневных отжиманий от пола в таблице личных рекордов, расход бензина служебного автомобиля, ежемесячные затраты на развлечения. Подсветка упрощает анализ, сокращает время на контроль ситуации и предотвращает простейшие, но оттого не менее обидные ошибки (такие как, например, ввод лишнего нуля в числовом значении – 100 вместо 10, 1000 вместо 100 и т. д.).
Для реализации столь удобной и упрощающей жизнь вещи, как визуализация, в Таблицах присутствует опция под названием «Изменить цвета в соответствии с правилами». Она умеет устанавливать оформление выбранных ячеек в зависимости от ряда условий. Каких? Давайте выясним! Обратимся к нашему примеру – представим себе, что в списке молодых людей мы хотим выделить красным цветом тех, кому еще нет 16 лет. Тех, кому от 17 до 25, отметим зеленым, а тех, кому ровно 16, выкрасим в желтый. Выберем колонку, отвечающую за возраст. Команда «Формат – Изменить цвета в соответствии с правилами» выводит на экран окно с выбором условий и действий над ячейками. Создадим первое условие – значение ячейки должно находиться в диапазоне от 0 до 15 включительно (рис. 3.20).
Рисунок 3.20. Условное форматирование. Определяем вид условия
Выберем действие, которое будет произведено над ячейкой в случае выполнения условия: установка красного цвета фона. Для этого проставим флажок рядом с надписью «Фон» и выберем ярко-алый цвет из предлагаемой палитры. Кроме очевидного цветового выделения ячеек доступно и еще одно действие – раскраска собственно текста. Действия можно объединять: никто не мешает удовлетворяющее условию значение выделить, например, белым и одновременно поместить его на приятный глазу сиреневый фон.
Аналогичным образом добавляются и остальные условия – в нашем случае на кнопку «Добавить новое правило» нужно будет нажать еще два раза, но в общем случае число налагаемых на диапазон условий не ограничено. Условия могут быть разными – тест на соответствие той или иной строке (раскрасить в зеленый цвет всех обладателей фамилии «Степанюк»), попадание в диапазон дат (показать счастливцев, чей отпуск в прошлом году выпал на три летних месяца) и даже проверка на то, что ячейка пуста. Последнее условие удобно применять для поиска еще не заполненных или пропущенных по невнимательности значений. После окончания работы над условиями необходимо нажать кнопку «Сохранить правила» и полюбоваться на загоревшийся тремя цветами «светофор» (рис. 3.21).
Рисунок 3.21. Совершеннолетним – зеленый свет!
