Все записи на тему

Google Таблицы

Google Таблицы — прекрасный редактор таблиц, позволяющий работать с данными в режиме онлайн. И во многом превосходящий Excel. Особенно они хороши для совместной работы.
В этом разделе я делюсь советами и лайфхаками для максимально эффективной работы в Таблицах. Читайте о функциях, графиках и возможностях Google Таблиц.
А если захотите основательно изучить Таблицы – прочитайте мою книгу или приходите на онлайн-курс.

Каскадная диаграмма в Google Таблицах (waterfall, водопад)

Каскадная диаграмма (waterfall, водопад)

Это классическая диаграмма для проведения анализа “план-факт”, факторного анализа (за счет чего достигли/из-за чего не достигли запланированного результата). В Google Таблицах она находится в разделе “Другие” в редакторе:

Выбор типа диаграммы в Редакторе

Диаграмма позволяет наглядно показывать результаты факторного анализа, анализа отклонений, изменения показателей.

В примере — диаграмма с анализом выполнения плана по выручке и влиянием трех отдельных направлений на него:

Каскадная (или waterfall) диаграмма — анализируем, как каждое из трех направлений повлияло на перевыполнение плана продаж.

Данные, на основе которых построена диаграмма, находятся в столбцах A-C, но для самой диаграммы нужны только данные из столбцов D-E (начальное значение, отклонения, итоговое значение):

То есть вы указываете в первой строчке план (изначальное значение). В столбце слева можно указать подписи.

Далее со второй строки указываете название фактора/направления/составляющей (например, “Продажа книг” в примере) и отклонение — его/ее влияние на итоговый результат (со знаком плюс или минус — смотря какое было влияние).

И так попарно в таком количестве, сколько факторов у вас есть. Последняя строка — значение, которое получилось в итоге. Оно может быть больше или меньше изначального. В нашем случае два направления выросли существенно и только одно упало в продажах — так что в итоге получился рост относительно плана на 21 тысячу.

Генерация случайных чисел в Google Таблицах

Функции для генерации случайных чисел

В Google Таблицах есть две функции для генерации случайных чисел.

Это СЛЧИС / RAND и СЛУЧМЕЖДУ / RANDBETWEEN.

Первая генерирует случайное число от 0 до 1, а вторая — целые числа в заданном диапазоне.
Соответственно, у СЛЧИС() аргументов нет — она всегда возвращает число от 0 до 1.

У СЛУЧМЕЖДУ два аргумента — границы, в которых нужно сгенерировать число.

Например, эта функция будет возвращать число от 3 до 27:

=СЛУЧМЕЖДУ(3;27)

Эти функции могут пригодиться для моделирования какой-нибудь ситуации, когда реальных данных. Например, я использовал их при создании некоторых примеров для нашего курса. А также можно использовать их для жеребьевки, например — такой пример рассмотрим чуть ниже.

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

Генерация случайной даты и времени

Если вспомнить, что дата и время в Google Таблицах — это числа, то мы поймем, что можем без труда генерировать случайные даты и время.

Напомню, дата — это целое число.

Чтобы конвертировать дату из привычного нам формата в числовой, можно использовать функцию ДАТАЗНАЧ / DATEVALUE.

Так, следующая функция будет возвращать случайные даты в промежутке между 01.01.2018 и 01.01.2019:

=СЛУЧМЕЖДУ(ДАТАЗНАЧ(«01.01.2018»);ДАТАЗНАЧ(«01.01.2019»))

Ну а чтобы сгенерировать случайное время — достаточно воспользоваться СЛЧИС. Ведь она генерирует число от 0 до 1, а время для Google Таблиц и есть число от 0 до 1 (напомню, полдень, например = 0,5).

Соответственно, если хочется сгенерировать случайную дату и случайное время, нужно сложить две функции:

=СЛУЧМЕЖДУ(ДАТАЗНАЧ(«01.01.2018»);ДАТАЗНАЧ(«01.01.2019»)) + СЛЧИС()

Генерация случайных комбинаций с помощью ИНДЕКС и СЛУЧМЕЖДУ

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

Задача состоит из двух этапов:

Выбрать случайные числа от 1 до N, где N — количество команд в лиге (функция СЛУЧМЕЖДУ);

Вернуть названия команд, соответствующие этим номерам, из списка (функция ИНДЕКС, которая, как мы помним, возвращает элемент из массива по его номеру).

Формула для одного из клубов будет выглядеть следующим образом:

=ИНДЕКС(список_клубов; СЛУЧМЕЖДУ(1;N))

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

=ИНДЕКС(список_клубов1; СЛУЧМЕЖДУ(1;N))&» : «&ИНДЕКС(список_клубов2; СЛУЧМЕЖДУ(1;M))

Формула, выбирающая случайный клуб из списка в B4:B23

Таким же образом можно генерировать и другие случайные сочетания — от ФИО из набора имен и фамилий до случайных телефонов или электронных адресов

Функция GOOGLEFINANCE для загрузки котировок акций и курсов валют

Файл с примером

Обсудим функцию GOOGLEFINANCE, которая позволяет загружать в Таблицы котировки акций и курсы валют — как в динамике, так и на текущий момент.

Валюты

Для получения биржевого курса достаточно ввести два трехбуквенных кода валют внутри этой функции. Например, “USDRUB”:

USDRUB — текстовое значение, мы указываем его в кавычках внутри функции.

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

Такую функцию можно протянуть и сразу получить результат в нескольких валютных парах.

Объединение кодов валют из ячеек внутри функции GOOGLEFINANCE (BTC — это код биткоина)

Чтобы загрузить курс валюты за определенный период, нужно добавить аргументов — помимо кода валюты, указать второй аргумент “PRICE” и даты начала и окончания периода (третий и четвертый аргументы):

=GOOGLEFINANCE(«USDRUB”;»PRICE»;»01.01.2017″;»01.02.2019″)

Или сослаться на ячейки с кодами валют и датами:

Получили информацию о курсе валюты за длительный период. Формат вывода результатов — два столбца, с датой и курсом.

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

Акции

Для получения информации об акциях нужно использовать вместо двух кодов валют тикер, четырехбуквенный код — например, AAPL для акций компании Apple.

В случае с акциями функция может загрузить большое количество параметров — цену открытия, цену закрытия, самую низкую или высокую цену за день, объем торгов, капитализацию компании и много других — все они перечислены в справке.

Тикер указывается в первом аргументе функции. Параметр указывается во втором аргументе функции.

Соответственно, следующая функция

=GOOGLEFINANCE(«AAPL”;»VOLUME»;»01.01.2017″;»01.02.2019″)

Будет возвращать объемы торгов акциями Apple с первого января 2017 по 1 февраля 2019 года.

Все аргументы — тикер, параметры и даты — можно указывать как в самой функции, так и в ячейках, на которые вы будете из функции ссылаться.

В следующем примере мы получаем максимальные цены дня по Apple за период с 1 января 2014 по 19 февраля 2019 года. Все параметры мы получаем из ячеек, на которые ссылаемся из функции:

Загружаем максимальные цены (high) по акциям с помощью GOOGLEFINANCE

Фильтры в Google Таблицах. Совместная работа (+видеоурок)

Давайте обсудим правила совместной работы с фильтрами в Google Таблицах.
Они особенно актуальны, когда пользователей в документе много.

Читать дальше…

Условное форматирование в Google Таблицах

Условное форматирование — это автоматическое форматирование ячеек в при соблюдении некоторых критериев.

Например:

Все ячейки с числами больше 20000 выделяются зеленым
Все ячейки со словом “Книга” выделяются полужирным шрифтом
Все ячейки с прошедшими датами выделяются серым

Читать дальше…

Темы, на которые я пишу: