Текст книги "Секреты приложений Google"

Глава 3
Таблицы

Таблицы, если вдуматься, окружают нас повсюду. Просто мы привыкли их не замечать. Записные книжки, списки рецептов, отчеты о выигравших лотерейных билетах, курсы валют и ценных бумаг, расписания поездов – все это при переводе в электронную форму удобнее всего сохранять именно в виде таблиц. Любые повторяющиеся данные невольно организуются в структуру. Так с ними гораздо проще работать: искать те или иные значения, суммировать, сортировать, строить графики. Поэтому, если для создания текстов любого объема правильнее и эффективнее всего будет обратиться к Документам, то для остального многообразия цифрового мира лучше Таблиц не найти. В повседневной жизни электронные таблицы можно использовать для ведения различных списков дел, километража и расхода бензина личного автомобиля, затрат и доходов семейного бюджета. Ну а в деловом мире применений электронным таблицам давно уже придумано несчетное множество – это и инструмент для анализа данных, и система учета всего и вся, а также незаменимый помощник при создании всевозможных графиков.

Помимо простейших функций, доступных практически всем современным электронным таблицам, Google Spreadsheets (именно так звучит название Таблиц Google в оригинале) обладает набором уникальным особенностей. В умелых руках они превращаются в серьезный аналитический и статистический инструмент с возможностью автоматического сбора, обработки и публикации данных, а также опцией групповой работы в режиме реального времени. Мало того: функциональность так называемых гаджетов позволяет расширять способности Таблиц практически неограниченно. Добавьте сюда набор шаблонов на все случаи жизни, возможность рассылки оповещений и… эй! Не слишком ли мы увлеклись описанием огромного набора функций?! Пожалуй. Гораздо лучше рассказать обо всем по порядку.

Обзор

Солидный набор встроенных опций совершенно не мешает Таблицам оставаться простыми и понятными, давая пользователям возможность начать работу с данными без каких-либо знаний об особенностях системы. Выбирая команду «Новый – Таблица» в основном экране Документов Google, мы создаем новую таблицу, приступить к работе с которой можно прямо сейчас (рис. 3.1).

Рисунок 3.1. Новая таблица

Перед нами – бесконечная «простыня» ячеек, раскинувшихся по вертикали и горизонтали. Строго говоря, число ячеек ограничено – их не может быть более 200 000. Но мы ведь пока не собираемся создавать базу данных обо всем на свете, правда? А это значит, что ограничения на ячейки и столбцы – тех не может быть более 256 – для нас пока несущественны: доступного количества хватит с лихвой для работы над любым разумным набором данных. Как и в каждой электронной таблице, ячейки подчиняются стандартным правилам адресации: у каждой из них есть имя, составленное из буквы столбца и номера колонки. Но почему первый ряд колонок отделен от других полосой стального цвета? Это первое бросающееся в глаза отличие от привычных электронных таблиц, таких как Excel или Open Office Calc. Авторы Таблиц резонно предполагают, что первая строка будет использована для создания заголовков, а потому она:

• не должна скрываться при «проматывании» больших массивов данных на несколько экранов;

• не участвует в суммировании и сортировке.

Кстати, именно функция сортировки станет доступна, если щелкнуть мышкой непосредственно по разделительной полосе. Что ж, пожалуй, здесь без примеров не обойтись. Давайте создадим простую таблицу, состоящую всего из двух колонок. В первой у нас будут имена людей, во второй – их возраст. Назначение такой таблицы придумайте сами: это может быть журнал спортивных достижений, регистр паспортного стола или список приглашенных на свадьбу.

Рисунок. 3.2. Люди и их возраст. Мы посчитали всех

Наведите курсор на разделительную полосу. Щелкнув по стрелке справа от надписи «Сортировать», выберем направление сортировки – по возрастанию (А – Я) или по убыванию (Я – А). Здесь угадывается намек на упорядочивание по алфавиту, но этот способ одинаково хорошо работает как с текстовыми, так с цифровыми значениями. Одновременно с сортировкой выбранного столбца меняют свое положение и остальные колонки на листе – считается, что все они входят в одну и ту же таблицу. Так, в нашем примере после сортировки персон по возрасту в колонке B их имена в колонке A тоже изменят свой порядок (рис. 3.3).

Рисунок 3.3. Таблица отсортирована по имени человека

Если же мы хотим, чтобы в сортировке не участвовали, скажем, первые три строки (а не одна, как предложено по умолчанию: часто для заголовка таблицы одной строчки недостаточно), то все, что нам нужно, – перетащить небольшой серый бегунок, расположенный в зоне нумерации строк, на несколько позиций вниз (рис. 3.4). Для закрепления доступно до 10 строк включительно.

Рисунок 3.4. Увеличиваем число строк в заголовке

Жаль, но на данный момент Таблицы не поддерживают сортировку по нескольким столбцам – в нашем примере нам не удастся упорядочить людей по возрасту, а затем уже в группах ровесников провести сортировку по имени. Однако вполне возможно, что такая функция появится в пакете очень скоро, ведь ее необходимость очевидна. По аналогии с закреплением строк работает и закрепление столбцов – бегунок чуть меньшего размера расположен над заголовком строки 1, и c его помощью можно фиксировать положение колонок от A до E. Дать команду на закрепление строк и столбцов можно и с помощью меню «Инструменты»: выпадающие меню «Закрепить строки» и «Закрепить столбцы» позволят выбрать необходимое количество «замораживаемых» элементов. Кстати, в меню «Инструменты» находятся и описанные выше команды сортировки.

Добавлять столбцы и строки в таблицах можно двумя способами. Способ первый: щелкнем правой кнопкой мыши на заголовке нужного столбца или строки и выберем пункт контекстного меню «вставить 1 слева», «вставить 1 справа» в случае столбцов или «вставить 1 выше», «вставить 1 ниже» в случае строк. Способ второй: выбор аналогичных команд из меню «Вставить». В противовес добавлению любую выбранную строку можно удалить (команда «Удалить строку» в контекстном меню или «Изменить – Удалить строку» в основном меню) или же просто очистить ее содержимое (контекстное меню: «Очистить строку», команда основного меню: «Изменить – Очистить выделенную область». Последний способ позволяет стирать данные в любом диапазоне таблицы, выделенном мышью). Нужно ли говорить о том, что действия, касающиеся удаления и стирания содержимого столбцов, полностью аналогичны (рис. 3.5)?

Рисунок 3.5. Добавляем и удаляем столбцы, не забывая делать то же самое со строками

Бывают ситуации, когда те или иные строки или столбцы совсем не хочется видеть – например, потому, что в них содержатся те или иные вспомогательные вычисления или ненужные в текущий момент данные, которые мешают увидеть ясную и четкую картину. Удаление и очистка в данной ситуации, естественно, не выход – столь решительные действия просто уничтожат введенные данные! Конечно, можно «сжать» соответствующие колонки или строки до практически нулевой величины (при должной сноровке воспроизвести этот трюк не составит труда), но работать с такой таблицей, а уж тем более возвращать «сжатые» элементы обратно очень неудобно. Вместо этого стоит воспользоваться встроенной в Таблицы функцией «Скрыть»: щелкните правой кнопкой мыши на заголовке строки или столбца и выберите пункт «Скрыть строку/столбец» – от элемента с данными останется лишь небольшая квадратная иконка (рис. 3.6).

Рисунок 3.6. Прячем ненужное с глаз долой

Иконки нужны для того, чтобы напоминать о самом существовании скрытых данных: по своему опыту знаю, что наличие подобного рода «невидимок» приводит в замешательство при разборе какой-нибудь сложной формулы, а такие напоминания позволяют не забыть о спрятанных данных. Вернуть сокрытое на свое законное место просто – один щелчок левой кнопкой мыши по иконке немедленно восстановит спрятанный элемент.

Когда данные перестают помещаться даже на самых широких мониторах, самое время вспомнить о том, что у таблиц есть листы! Обратите внимание на нижнюю часть экрана. Сейчас там находится лишь одна закладка с именем «Лист 1», но никто не мешает нам добавить к ней еще несколько. Для этого щелкаем по очевидной кнопке «Добавить лист» слева от закладок, и новая белоснежно-пустая таблица тут же возникает на экране. Думаю, что основные функции, доступные при щелчке правой кнопкой мыши на небольшом треугольнике рядом с названием листа, говорят сами за себя: лист можно удалить, создать его полную копию, переименовать, защитить от изменений, а также передвинуть вправо или влево относительно соседей (рис. 3.7).

Рисунок 3.7. Все, что можно делать с листами

Важный момент: как только мы начнем вносить значения в новую таблицу, в правом нижнем углу экрана появится предложение включить механизм автосохранения. Для этого нужно просто щелкнуть по предложенной ссылке «Запустить автосохранение» и ввести название нового файла (рис. 3.8). Кстати, вы заметили, что привычная иконка дискеты в интерфейсе Таблиц отсутствует? Все правильно: подход к сохранению всех действий здесь отличается от общепринятого. Автоматически записывается любая произведенная вами операция, а потому нужда в принудительном сохранении попросту отпадает! В том, что заветный режим включен и успешно функционирует, можно убедиться по сообщению «Автосохранение Х: ХХ» в правом верхнем углу экрана, где вместо знаков Х будет стоять время последнего учтенного изменения.

Рисунок 3.8. Запустить автосохранение – и перестать беспокоиться!

Форматы

Любая ячейка таблицы имеет определенный формат – способ представления данных на экране. В случае имен людей или их возраста из предыдущего примера все просто: система автоматически определяет, что данные первого столбца являются текстом, а цифры во второй колонке следует представлять в виде целых чисел без десятичной запятой и нулей после нее. Но ведь есть примеры и посложнее! Как насчет дат, времени, денежных единиц? Что, если мне хочется показать на экране стоимость привилегированных акций с точностью до третьего знака после запятой? Или вывести дату рождения любимого пса в виде «день – название месяца – год»? На помощь приходят форматы: любой ячейке, столбцу, строке, да и вообще произвольно выделенному нами диапазону мы можем указать вид представления введенной информации. Для этого используются ровно три кнопки инструментальной панели. Вот они (рис. 3.9).

Рисунок 3.9. Кнопки инструментальной панели, отвечающие за формат данных

Форматирование срабатывает для выбранного диапазона или, если не выбрано ничего, ячейки, на которой в данный момент находится курсор. Пробежимся по форматам. Кнопка с сокращенным названием русского рубля активно намекает нам на финансовую составляющую. Все правильно – нажатием на нее мы добавляем к любому цифровому значению букву «р» с точкой. Для форматирования данных, представленных в виде различного рода процентов, предназначена вторая кнопка форматирования. Число 0,11 она превратит в 11 %, а 2,2 – в заоблачные 220 %.

Да, первые две кнопки предназначены для быстрого выбора формата. Организацией же представления всех остальных видов данных занимается последняя кнопка форматирования с надписью «123». Кроме представления собственно чисел (в формате можно настроить отображение желаемого числа знаков после запятой) в выпадающем меню также выбирается представление даты и времени. Следует помнить, что большинство компьютерных систем запоминают внутри себя дату и время как число дней, прошедших с того или иного момента (так называемой эпохи) – 1 января 1970 г., 1 января 1900 г. В нашем случае за начало эпохи принято 30 декабря 1899 г. Поэтому, если мы захотим вывести, например, число 3 в виде даты, то Таблицы в ответ покажут нам 1 января 1900 г. Не стоит удивляться подобному преобразованию! Однако в большинстве случаев система автоматически «понимает», что введенное значение 27.06.81 13:45 является не чем иным, как датой, «на лету» преобразует его в число дней, прошедшее с начала эпохи (время в данном случае является дробной частью числа), запоминает значение в ячейке, а на экран как ни в чем не бывало выводит введенную вами дату. Ух! Здорово, правда? А вот если мы хотим изменить это представление – например, убрать из ячейки время или указать название месяца (июнь, сентябрь), а не номера, то все, что нужно, – выбрать соответствующий формат из выпадающего меню.

«Другие форматы» предназначены также для отображения финансовых данных в валютах, отличных от рубля, – в выпадающем списке «More currencies» («Другие валюты») есть возможность выбора доллара США, английского фунта, хорватской куны и еще 25 валют мира. Если же и этого набора будет недостаточно, всегда есть возможность добавить собственные, пользовательские обозначения – для этого нужно выбрать пункт «Пользовательские валюты» в самом низу списка, после чего ввести желаемое сокращение и его положение – слева или справа от числа. Думаю, вы понимаете, что подобную возможность можно использовать не только для валют, но и для других нужных вам единиц измерения? Туда, например, можно внести часто используемые в пищевой промышленности гектолитры, а энергетикам явно понравится возможность завести единицу измерения под названием «кВт·ч».

Что касается текстового форматирования ячеек, то здесь доступны привычные инструменты работы со шрифтом: полужирное выделение, курсив и подчеркивание (пункты меню Формат – Жирный, Курсив, Подчеркивание), а также возможность перечеркнуть введенное значение. Перечеркивание может быть полезно, когда мы хотим показать, что цифра или текст недействительны (например, в случае ошибочного ввода), но стирать его не хотим. Для перечеркивания текста выберите пункт «Формат – Перечеркнуть» или нажмите на кнопку с перечеркнутыми символами «Abc». Очень удобно, что вводимый в ячейку текст по умолчанию, без необходимости указания каких-либо дополнительных параметров, автоматически переносится по строкам, занимая все доступное в ячейке пространство и при необходимости увеличивая ее высоту. При кажущейся простоте и естественности подобного решения производители традиционных офисных систем до сих пор не могут его реализовать, заставляя пользователей принудительно указывать опцию переноса по словам для каждой ячейки. Двигаясь по панели инструментов слева направо, отметим изменение размера шрифта, раскраску текста и фона во все цвета радуги, а также управление выравниванием по горизонтали и вертикали (рис. 3.10).

Рисунок 3.10. Рамкой отмечены: кнопка изменения размера шрифта, управление цветом текста и фона и, наконец, выпадающее меню выравнивания по высоте и ширине

Еще две важные кнопки: объединение ячеек и перенос текста по словам. Очень странно, что объединять можно только ячейки, расположенные по горизонтали, – вертикального объединения Таблицы не поддерживают. Тем не менее с этим приходится мириться и учитывать подобный недостаток при построении собственных таблиц. Кнопка же переноса по словам действует для выделенного диапазона ячеек и включает или отключает столь удобное автоматическое изменение высоты ячеек в зависимости от длины введенного текста.

Формулы

Электронные таблицы не имели бы и сотой доли той популярности, которая есть у них на данный момент, если бы у них не было главного преимущества – возможности работать с формулами, на лету пересчитывая сотни и тысячи введенных значений, подводя промежуточные суммы и итоги, рассчитывая среднеквадратичные отклонения и извлекая квадратные корни. Многим любителям таблиц эти системы заменяют калькулятор. Например, очень удобно, введя в итоговую ячейку сложную формулу подсчета прибыли, «играть» ее параметрами в соседних ячейках – менять проценты наценки, уровень налогов, суммы затрат: ни один калькулятор на подобное «Что, если?» моделирование просто не способен! Таблицы Google обладают обширным набором формул на все случаи жизни – от простейших арифметических операций до серьезных финансовых и статистических расчетов. Мы не будем останавливаться лишь на самых легких примерах формул – опыт показывает, что даже специализированным вычислениям можно найти применение не только в науке, но и в повседневной жизни.

Начнем, однако, с самого простого – сложения и вычитания. Вообще, получить сумму значений ячеек в таблице очень просто безо всяких формул – достаточно выбрать нужный диапазон в столбце или строке и обратить внимание на правый нижний угол экрана – искомая сумма уже будет указана там (рис. 3.11).

Рисунок 3.11. Для того чтобы сложить, достаточно просто выделить. По крайней мере, в Документах Google

Ну а если мы все же хотим зафиксировать итог в той или иной ячейке, воспользуемся функцией SUM. В желаемом месте таблицы введем в ячейку символ «=» – это признак того, что она будет вычисляемой. После этого укажем нужную функцию – SUM. Сразу же после начала ввода символов с клавиатуры система пытается «угадать» названием вводимой функции, предлагая список доступных операций, подходящих под вводимое название. Непосредственно за именем функции следуют ее аргументы – вводная для расчета. В скобках после названия функции укажем вычисляемый диапазон – имя начальной и конечной ячейки, разделенные двоеточием (рис. 3.12).

Рисунок 3.12. Подсказка по формулам – тут как тут

Готово! Закрываем скобку, нажимаем Enter. Искомый результат не замедлит появиться в выбранной ячейке. Если мы хотим модифицировать формулу (например, добавив к рассчитанному значению сумму другого столбца), то для ее редактирования можно два раза щелкнуть мышкой по ячейке или просто нажать на клавиатуре клавишу F2. Я обычно предпочитаю второй способ.

Список всех доступных функций можно получить, вызвав команду «Вставить – Формула – Дополнительные формулы». Общий список из более чем сотни алгоритмов разбит на группы. Их ровно десять: Математические; Финансовые; Логические; Для работы с датами; Организации поиска; Статистические; Для операций с текстом; Инженерные (под этим загадочным термином на самом деле скрываются операции по переводу чисел в различные системы счисления); Информация (проверка содержимого ячеек на различные условия – наличия значения, ошибок в вычислениях), а также специальная группа Google, куда вошли несколько функций, о которых будет подробно рассказано в разделе «Экстра» в конце этой главы. Переведя курсор на любую из предлагаемых формул, в нижней части окна можно ознакомиться со списком ее аргументов, а перейдя по ссылке «еще», прочитать подробную справку о принципе работы (увы, только на английском язык; зато по адресу http://b23.ru/wed можно найти версию на русском). Двойной щелчок по найденной формуле немедленно вставит ее в текущую ячейку – и нам останется только указать аргументы. Найдите в финансовом разделе функцию PMT – она позволяет рассчитывать сумму аннуитетных (равными долями) платежей при погашении столь любимых многими потребительских кредитов. В этой функции в качестве первого параметра указывается ежемесячный процент (годовой процент, разделенный на 12 месяцев), вторым идет количество месяцев, в течение которых будут производиться выплаты, а на последнем месте – собственно сумма кредита (рис. 3.13).

Рисунок 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. Комментарии к ячейкам

О наличии ремарки теперь будет напоминать пожелтевший правый верхний уголок ячейки. Просмотреть содержание комментария можно, удерживая курсор на ячейке, – всплывающая подсказка не замедлит появиться. Для удаления комментария выберите пункт «Удалить комментарии» все того же всплывающего меню.