Скоро наш журнал обновит свой дизайн
Close
Будь в курсе
Подпишитесь на рассылку, чтобы не пропускать новые выпуски журнала
лекция
Аналитика и маркетинг
Автоматизация гугль‑таблиц. Скрипты и собственные формулы
Введение и примеры

Привет, с вами Куличевский, и мы продолжаем работать с гугль‑таблицами. В прошлый раз мы обсудили преимущества гугль‑таблиц перед Экселем, перед другими системами, и я коротко упомянул, что одно из преимуществ — это возможность практически неограниченного расширения функционала. Сегодня поговорим об этом подробнее. Как раньше в Экселе можно было писать макросы, так и в гугль‑таблицах можно писать скрипты. С их помощью можно автоматизировать разные операции над таблицами. Например, когда вы создаёте новую таблицу, она сразу же как‑то оформляется по шаблону, у неё загружаются какие‑то данные из гугль‑аналитикс. Можно добавлять кастомные пункты в меню, которые тоже будут делать какие‑то операции. Ещё можно создавать собственные формулы. Об этом поговорим более подробно, так как на самом деле это единственное, что я более‑менее использую в практике. Научившись создавать формулы, вы поймёте, как работают вообще скрипты, и я вам дам литературу, если захотите углубиться дальше, то сможете это сделать самостоятельно.

План сегодняшнего занятия:

  1. Как работают скрипты:
    • куда записывать скрипты;
    • учите JavaScript.
  2. Создаём формулы:
    • самая простая формула;
    • превращаем дату в гггг‑мм;
    • считаем RMSE.

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

Прежде чем мы к этому перейдём, я хочу вам дать вообще небольшую демонстрацию: что мы будем делать, к чему мы придём. У меня есть специальная табличка, которую я подготовил. Вот один из возможных кейсов, которые мы сегодня будем разбирать. Если вы уже сделали задание по прогнозированию, то вы помните, что частью задания было посчитать RMSE модели, посчитать, насколько она точная.

Вот я тут подготовил немного данных, у нас есть как будто наши реальные значения из тестовой выборки, вот у нас есть как будто какая‑то модель, которая спрогнозировала значения, и мы хотим понять, насколько модель точно описывает эти данные. В уроке я вам показывал, что для того чтобы посчитать RMSE, нужно сделать три шага. Сначала вы для каждой точки считаете отклонение, потом вы возводите его в квадрат, а потом считаете среднее квадратичное отклонение, и у вас получается MSE. Затем вы берёте квадратный корень от него, и у вас получается RMSE. И это надо использовать как основную меру отклонения от реальности. Всё классно, но это занимает кучу времени, кучу шагов, мы заняли огромное количество места на листе, и вообще это всё очень непонятно. Вот было бы классно, если бы существовала формула, в которой вы бы дали один столбец с вашими реальными значениями, один столбец с вашими предсказанными значениями, и она сразу бы всё посчитала. По умолчанию такой формулы не существует, но вы можете её создать. Я создал такую формулу, RMSE, сначала мы туда вставляем предсказанные значения, потом мы туда вставляем реальные значения, и — вуаля! — мы получили то же самое значение, только без всех этих сложностей, просто подставив всё в формулу. Сегодня покажу вам, как эту формулу делать.

Другой пример. Допустим, у вас есть в исходных данных дата. Например, у вас список продаж, и для каждой покупки, для каждого заказа есть дата заказа. И вы хотите построить когортный анализ по месяцам. Ваша задача — перевести дату в формат «год и месяц». Вы можете использовать обычные экселевские формулы. Например, вы можете сделать отдельно год, месяц и объединить это всё вместе. Всё вроде бы получается, только это уже текст. По идее, вы хотите, чтобы у вас было 1, 2, 3, 4, 5, 6, 7 и т. д., как сейчас. Если вы отсортируете это по алфавиту, то у вас получится вот такая фигня: 1, 10, 12, а потом 2. Для того чтобы этого избежать, вам нужно добавить 0 перед 1, чтобы у вас месяц всегда кодировался двумя символами. И тут начинается городьба. Пишем иф, если месяц меньше 10, то добавляем сюда 0, если нет, то не добавляем, всё, классно. Но опять какой‑то страшный геморрой. Какие‑то бешеные формулы, и наверняка есть способ лучше. Да, он есть. Можно сделать формулу, в которой вы просто передаёте сам массив дат, она немного грузится и возвращает вам данные в нужном формате. Экономите кучу времени, кучу ресурсов, у вас гораздо меньше места, чтобы совершить ошибку, вы, сделав эту формулу один раз, можете её использовать везде. В общем, одни плюсы, никаких минусов. Это я вам тоже сегодня покажу как делать. Давайте разберёмся с языком, на котором всё это дело пишется.

JavaScript и начало

Писать макросы можно было ещё в Экселе, я, кстати, не помню, можно ли там было создавать формулы. В любом случае, в Экселе на виндоус для макросов использовался язык Visual Basics, а на маках для тех же самых скриптов использовался язык AppleScript, который другой. Уже была проблема коммуникации. Более того, насколько я знаю, сейчас особо нигде в мире эти оба языка не используются. Ну, почти везде они используются очень редко. Если вы потратите ресурсы на то, чтобы их выучить, скорее всего, вам это никогда больше не пригодится для решения каких‑то задач. В гугль‑таблицах всё по‑другому. Все скрипты основаны на языке JavaScript. Формально язык, который использует гугл, называется AppScript, но на самом деле это просто обёртка на JavaScript. Давайте я вам покажу, как выглядят скрипты. Они могут выглядеть немного странно. Вот, например, вся функция, превращающая даты в формат гггг‑мм.

Это самый обычный JavaScript. У меня вообще изначально была идея, перед тем как мы перейдём к дизайну формул, дать вам ликбез по JavaScript, чтобы вы все были на одном уровне, там нет ничего сложного, но потом я понял, что это будет неэффективно, потому что я наверняка расскажу вам материал хуже, чем вы получите его от каких‑то профессиональных ребят по JavaScript. Поэтому я вот что сейчас хочу: у вас будет первое задание, это видео ставьте на паузу, идите на codeacademy.com, если вы ещё там не зарегистрировались, то регистрируйтесь, и я хочу, чтобы вы прошли бесплатный курс по JavaScript, по его основам. Я зашёл сейчас через фейсбук, дальше у меня там какие‑то курсы есть, это не так важно, иду в каталог, ищу здесь JavaScript. Здесь у вас есть самый первый курс, абсолютно бесплатный Introduction to JavaScript. Тут есть баннер, что он стоит 200$, это не нужно, вам нужен наверху бесплатный интродакшн ту джаваскрипт. Нажимаете старт и начинаете учиться. На кодакадемии всё работает таким образом: у вас в левой части теория и задания, а в правой части вы пишете код, сразу получаете фидбек и учитесь.

Всё это не должно занять у вас слишком много времени. Что вам нужно узнать? Вам нужно узнать, что такое переменные, какие есть типы данных, какие есть типы математических и логических операций: сложение, умножение, деление, затем «и», «или» и логическое отрицание; как работают условные операторы и как работают циклы. Пройдите курс и возвращайтесь к видео. Будем считать, что вы поставили на паузу, прошли и вернулись учиться. Я предполагаю, что в этот момент у вас уже есть общие знания о JavaScript. Вы знаете, как он работает, что такое переменная, что такое циклы, что такое условие и т. д. Окей, погнали дальше.

Допустим, у вас есть гугль‑таблицы, вы хотите создать какой‑то скрипт, какую‑то кастомную формулу. Куда вам идти? Вам идти в tools → script editor. У вас открывается вот такая фигня. Единственное, у меня она уже заполнена, а у вас нет. Давайте создадим пустой файл и там начнём что‑то делать. Снова заходим в tools → script editor. У нас в отдельном окне открывается script editor. И у вас по умолчанию здесь стоит пустая функция.

Архитектура формул

Функция — это именованный кусочек кода. Вы какую‑то функциональность пакуете внутрь функции, даёте ей имя, и потом вы можете эту функциональность вызывать, используя это имя и передавая ей какие‑то параметры. У любой функции есть имя, есть скобочки, в которые вы передаёте параметры, над которыми она будет производить какие‑то операции. Дальше внутри, в теле функции, вы описываете эти операции, и обязательно нужно добавить команду return и то значение, которое функция возвращает. Для того чтобы всё это заработало, именно в гугль‑таблицах, надо функцию сохранить. Для этого есть либо иконка с дискеткой, либо — шорткат cmd+s на маке, ctrl+s на виндоус. Давайте вернёмся в таблицу. Вспомните, что любая формула, с которой мы работали, — она выглядит на самом деле очень похоже. Вы её вызываете по какому‑то имени, вы передаёте в скобках какие‑то параметры, формула производит над этими параметрами какие‑то операции и возвращает какое‑то значение. На самом деле формула — это та же самая функция. И для того чтобы создать новую формулу, вам просто нужно создать соответствующую функцию. Сейчас мы создали функцию myfunction, которая возвращает цифру 1. Мы можем уже её взять и вызвать в таблице. Вот она, цифра 1. В общем, это основная универсальная архитектура формул, дальше мы будем с ней работать.

Простая формула

Я долго думал над тем, какой будет первая формула, какое задание ей можно дать. Придумал следующее: допустим, у вас есть числовые данные, и вам нужно оттуда вычистить отрицательные значения. Вот, есть какие‑то данные.

Задача: сделать так, чтобы если число положительное, то оно бы не изменилось, а если число отрицательное, то ячейка бы осталась пустой. Давайте попробуем это сделать. Перейдем в скрипты. Назовём её clean_neg. В скобочки мы будем передавать какой‑то number, и дальше нам нужно будет что‑то с ним сделать. Я предполагаю, что вы прошли курс по JavaScript и вы знаете, как работают условные операторы. Ещё раз повторим логику, что нам нужно сделать. Если число отрицательное — возвращаем пустое значение, если число неотрицательное — возвращаем это число. Пишем: [ if(number < 0) return " " ]. С новой строки: [ else return number; ].

Сохраняем, проверяем. Пишем нашу формулу, копируем‑вставляем. Сработало! Уже хорошо, давайте развивать дальше. Построим уже то, что я вам предлагал вначале с датами.

Даты

Запишем вместо чисел в первый столбик какие‑нибудь даты, а во втором столбике у нас будет дата формата гггг‑мм.

С чего начать? Я вам буду рассказывать честно, с чего я начинаю, и, я думаю, это будет как раз тот же путь, который вы будете проходить, если вы, конечно, не выйдете на вершину профессионализма, где вы будете помнить все возможные функции, формулы и синтаксис, будете его держать спокойно в голове и вспоминать. Я обычно, когда решаю подобные задачи, начинаю с того, что я гуглю, потому что я не помню, как оно работает, это нормально. Давайте сначала логику. У нас есть какая‑то дата, нам нужно из этой даты достать что‑то такое [2016–01], где 2016 — это год, а 01 — это месяц. Сначала нам нужно заэкстрактить из даты год, потом заэкстрактить из даты месяц, потом их вместе объединить и это же вывести. Как экстрактить год из даты в AppScript? Фиг его знает. Идём в Гугл и пишем: «App script get year from data». Вышло сразу много статей. Видите, некоторые из них я уже читал, когда готовился к уроку и всё искал. Видите здесь две прочитанные статьи, они в документации самого Гугла. Первая о том, как в принципе работать с датами, а вторая про профнабор функций. Пока они открываются, давайте вернёмся на секундочку в документ. Я немного перескочил один маленький шаг, сейчас вернёмся назад. Первый шаг, я не бегу в документацию, а я создаю саму формулу. Называться она будет вот так: yyyy‑mm. В скобочках пишем date. И она будет ретёрнить пока что саму date. Вот, дефис нельзя использовать, удаляем его из названия тогда. Вот у нас есть функция, yyyy — это четырёхсимвольное обозначение года, а mm — это двухсимвольное обозначение месяца. Пока она выводит саму дату. Вот, работает. Теперь мы начнём шаг за шагом с этой формулой работать, добавляя туда функционал, чтобы она делала то, что нам нужно. Первое, что нам нужно сделать, это достать из даты год и номер месяца. Поехали обратно к статье. Вот у нас здесь есть всякие интересные штуки, например getYear и getMonth, похожие на то, что нам нужно, но если почитать подробнее, то здесь идёт речь об обработке объекта класса дата. Я не уверен, что то, что мы передаём, передаётся как дата. Может, и так, а может, и нет. Поэтому нам нужно ввести новую переменную d и превратить её в дату [d = Date(date);], попробуем вернуть переменную d, посмотрим, что случилось. Вот, смотрите, что произошло. Она действительно у нас преобразилась из числового значения какого‑то в формат даты с кучей всякой интересной информации типа тайм‑зоны и времени до секунды. Дальше мы можем с этой переменной d работать. Например, пробуем достать из неё год. Мы видели формулу для этого getYear. Пишем year, вводим новую переменную, равно getYear от d. [year = getYear(d);] Сохраняем. Теперь как нам понять, что у нас всё правильно работает? Давайте попробуем вывести year. Ошибка. Пишет какую‑то фигню, типа формулы getYear на самом деле нет, хотя она здесь явно есть. Давайте кликнем по ней на сайте и посмотрим. А‑а, getYear — это тоже другая функция, которая тоже делает какое‑то действие над нужными нам штуками. Их можно писать не только вот в таком формате, но и вот в таком [year = d.getYear();]. Снова ошибка, что теперь? Непонятно. А если вот так? [year = date.getYear();] Вот. Получилось. Тогда мы сделали лишний шаг, вот это нам не нужно [d = Date(date);]. Смотрите, на что здесь обратить внимание? Видите, я иду маленькими шажками, и часто шажки приводят не совсем туда, куда нужно. Делаете шажок, проверяете, получаете какую‑то непонятную ошибку. Идёте читаете документацию, исправляете и добиваетесь того, что у вас всё работает. Ровно такой процесс у всех. Когда вы пишете любую программу, вы идёте ровно такими шажками. Единственное, матёрые программисты, которые такие программы пишут уже много лет, они таких ошибок делают меньше и двигаются быстрее, но они всё равно их делают. Поэтому я специально не буду это редактировать, чтобы вы видели, как оно всё на самом деле работает. Ещё я хотел сказать потом, что вот не всегда понятно, когда нужно getYear в скобках date или другая функция в скобках параметр, а когда нужно переменная точка getYear. Мне тоже это не всегда понятно. На самом деле есть чёткое разграничение, что в объектно‑ориентированном программировании... хотя на самом деле там тоже нет чёткого разграничения. Почему так, а не по‑другому? Потому что кто‑то когда‑то вот так вот сделал. Почему он сделал именно так? Хрен его знает. Как правильно? Правильно так, как работает. Попробуйте таким образом, попробуйте другим образом и почитайте документацию. Раз на раз не приходится.

Идём дальше. Мы получили год, теперь нам нужно получить месяц. Мы здесь видели, что есть функция getMonth. Сейчас у нас будет всё гораздо проще. [month = date.getMonth();] По идее, всё должно работать. Проверяем, добавляем month. [return year → return month] Опа, 0. Что значит 0? Почему 0, когда должен быть 1? Непонятно. Давайте ещё раз погуглим. App script data format yyyymm, гуглим. Вот у нас есть ещё одна статья, working with dates and times. Давайте посмотрим, что тут есть. О, смотрите, какая интересная штука, тут есть команда utilities.formatDate, и в неё можно передавать, как эту дату форматировать. Давайте попробуем её применить. Идём в скрипты, можем даже сразу вставить это в return и посмотреть, что получится. [return Utilities.formatDate(date, 'America/New_York', 'MMMM dd, yyyy HH:mm:ss Z')] О, сработало. То есть что в этой функции? У нас дата, с которой мы работаем, дальше мы говорим таймзону, относительно которой мы хотим выводить информацию, то есть я сейчас нахожусь в Сингапуре, и относительно Сингапура дата, которую я ввёл, в –5 часов. Данные по умолчанию в компьютере хранятся в таймзоне UTC, которая она же GMT, она же London, то есть Greenwich Mean Time. У меня в Сингапуре +5 часов от GMT, а в Нью‑Йорке –5 часов от GMT, поэтому вот здесь –5.

Если мы здесь изменим America/New York на GMT, то у нас всё пересчитается и будет +0000. Возвращаемся к тому, что мы хотели сделать. Мы хотим, чтобы у нас дата превратилась в формат год — месяц. Мы уже в одном шаге от результата. Вот этот формат описывает то, как будет выглядеть дата.

Четыре большие М — это полное название месяца, дальше два d — номер дня в месяце, дальше четыре y — это год четырьмя символами, то есть 2015, а не 15, и т. д. Можете почитать в документации подробнее о том, как эти форматы выглядят. Для того чтобы использовать нужный формат, нам нужно написать четыре y и два больших M. ['yyyy‑MM'] Сохраняем, вуаля. Готово. Только почему‑то у нас не совсем готово. У нас здесь 2016–01, а здесь 2015–12. Проблема с таймзонами. Если бы тут было второе число, то всё бы сработало. Давайте вернёмся обратно к документации. Смотрим, какие бывают таймзоны. Он говорит, что тут есть какая‑то табличка с таймзонами. Я хочу найти, как туда вставить таймзону, в которой я сейчас. Ищем тут Сингапур, вот он. Возвращаемся в скрипты. Пишем вместо GMT Asia/Singapore. Вуаля, заработало. Теперь мы можем скопировать‑вставить, и всё классно получилось. В общем, первая формула готова, но если у вас таких дат не 20, а 200, а ещё хуже — 2000, а ещё хуже — 20 000, то копировать‑вставить у вас не сработает. Слишком много времени потребуется на вычисление, и гугль‑таблицы вообще не рекомендуют делать такие большие штуки. Если данных много, то они рекомендуют вам делать операцию над всем массивом сразу. Вспомните ту же сумму, вы можете вставлять туда не только отдельные значения, но и массив. Тут точно так же. Что нужно изменить? Давайте мы тут для красоты изменим date на dates, а дальше нужно сделать вот такую штуку.

Даты (продолжение)

Проверить, dates — это массив или отдельное значение. Делается это таким образом. Мы добавляем условие: if(dates.map). Он возвращает истинное значение, если dates — это массив, и ложь, если это не массив. Дальше у нас будет значение else (). Вот наша вся функция будет в этих двух условиях. Если это массив, то нам нужно сделать что‑то одно, если это не массив — что‑то другое. До сих пор мы вот это всё прописывали для того случая, когда это не массив. Кстати, вот эти две строки нам вообще не нужны.

Мы их больше не используем. Вот этот return нам решает всю задачу для того случая, когда dates — это единичное значение. Мы, соответственно, можем вставить return в else. Вопрос в том, что делать, если dates — это массив. Давайте пока попробуем написать в if return 'array', чтобы посмотреть, как это работает. Вот у нас есть куча примерно single, сейчас попробуем array.

В столбике single у нас передаётся только одно значение, а в столбике array давайте попробуем передать все значения. Вот, нам возвращается просто значение array. Это совсем не то, что нам нужно. Можем продвинуться на шаг ближе, а давайте попробуем просто вернуть те же самые dates. Посмотрим, что будет теперь. Вот, видите, мы вставили в формулу одно значение, и у нас все новые значения сразу вывелись вниз. Это очень удобно. А теперь покажу вам классный хак. Как нам из этих данных получить то, что нам нужно. При этом нужно добавить всего пару символов. Напишем этот формат здесь, вот и всё. [return dates.map(yyyymm)] Если здесь dates.map без параметров — это как бы проверка, массив это или нет, то map с параметрами — это функция. Что эта функция делает? Она применяет какую‑то другую функцию к каждому элементу массива. То есть у нас много дат в массиве, и она просто применяет какую‑то функцию к каждой дате массива и возвращает отдельный массив уже после того, как эта функция применена. Но самый кайфец в том, что мы можем применять сюда ту же функцию, которую мы пишем. Как это работает. Мы передаём массив, он проходит первую проверку, видит, что это массив, и говорит: «Примени функцию yyyymm к каждому элементу массива». Дальше идёт в цикле первый элемент массива, он запускает функцию, проверяет, массив это или нет, это не массив, тогда сделай вот это. Затем следующее точно так же, и так далее, пока элементы не кончатся. Давайте посмотрим, как это работает. Оп, всё, это работает отлично. Вот вы и сделали конвертер дат в формат гггг‑мм, и он будет отлично работать даже на большом количестве дат.

RMSE

Теперь создадим формулу, которая будет считать RMSE. Начнём с того, что объявим функцию. [function rmse()] Пока не будем передавать никакие параметры. Готово. Давайте продумаем логику. Я подготовил нам данные. Вот у нас есть фактические значения, вот у нас есть значения, прогнозируемые какой‑то моделью, линейной регрессией, например. Как нам посчитать RMSE? Давайте попробуем составить алгоритм. Можем пойти с конца. Что такое RMSE? Это корень квадратный от MSE. Что такое MSE? Это среднее от квадратов отклонений. Что такое среднее? Это сумма квадратов отклонений, деленная на их количество. Что такое квадраты отклонений? Это отклонение, возведённое в квадрат. Что такое отклонение? Это predicted — LTV_year. Получается, что для того чтобы посчитать RMSE, нам нужно вычесть из predicted фактическое значение, возвести в квадрат, потом посчитать среднее, значит, все их просуммировать и поделить на их количество. Потом взять от получившегося значения квадратный корень. Теперь давайте запишем всё, что у нас получилось, здесь в виде кода. Начнём с конца. [return rmse] Как будто мы всё уже посчитали. RMSE — это корень квадратный от MSE. [rmse = sqrt(mse);] Единственное, в JavaScript всякие математические функции типа квадратного корня или степени — они вынесены в отдельную библиотеку. Она у всех установлена, но её нужно указать. Библиотека называется Math. [rmse = Math.sqrt(mse);] Дальше MSE. MSE — это сумма отклонений, делённая на их количество. Давайте лучше для неё запилим отдельную переменную. [mse = sum_dif / count_dif;] Видите, я не сначала пишу переменные, а потом с ними делаю какие‑нибудь действия, а я иду с конца. Я потихоньку раскрываю результат, который хочу получить, как по пирамиде, к тем данным, что у нас есть. Теперь мне нужно посчитать сумму и их количество. Как это сделать? Сделаем небольшой прыжок вперёд и объявим наконец‑то переменные. У нас есть два массива данных. Это фактические данные и предсказанные. Так их и назовём, fact и predicted. [function rmse(fact, predicted)] Обратите внимание, что тут я могу называть переменные как угодно и мне вовсе не обязательно называть их как столбцы. Более того, их нежелательно называть вот так. От того, как я их назову, логика не поменяется. Просто нужно, чтобы люди, которые дальше будут работать с этой функцией, понимали, что эти значения означают. Теперь смотрите, нам нужно вычесть predicted — fact. Это массивы, а вычитать массивы — это не то, что нам нужно, совсем другая операция. Нам нужно последовательно из каждого элемента одного массива вычесть соответствующий элемент второго массива. Для этого есть циклы. Мы можем в цикле пробежаться от первого элемента до последнего и вычесть каждый из них, а результат сохранить в другой переменной.

Пишем for(i = 0, i < n, i++). Перевожу на русский. Для нумератора i, то есть какой‑то переменной, в которой будет бежать цикл, в котором она изменяется от 0 до n, причём n не включается (N в данном случае будет количество элементов во входящих массивах, сейчас мы её объявим). i++ соответственно увеличит i на 1. Мы хотим вычитать из fact predicted или из predicted fact, на самом деле, так как мы возводим в квадрат, — это неважно. Давайте мы объявим какую‑нибудь переменную x, какой‑то просто placeholder. [dif = fact — predicted] Вот оно, наше отклонение. Дальше, чтобы посчитать квадратичное отклонение, нам нужно посчитать диф квадрат. [dif_sqared = dif ^ 2] Этот цикл... а он ничего не будет делать, потому что у меня ошибка. Вот я сейчас делаю ровно то, что я вам говорил не делать. Из массива пытаюсь вычесть массив, а нам нужно не массивы вычитать, а элементы массива. За каждую итерацию цикла получить i‑тый элемент каждого массива и вычесть один из другого. Для этого мы ставим квадратные скобочки и i. [dif = fact[i] — predicted[i]] Теперь, когда у нас начинается цикл, i = 0. В первой строчке мы берём массив fact, берём i‑тый элемент, то есть нулевой, то есть самый‑самый первый (помните, что в массивах нумерация начинается с нуля), и вычитаем из него нулевой элемент массива predicted, потом возводим это значение в квадрат и храним в переменной dif_sqared. Потом идёт следующая, следующая, следующая, и так мы пробегаемся по всему массиву и каждый раз считаем квадратичное отклонение, но пока мы его нигде не храним. Это плохо. Для того чтобы его где‑то хранить, мы можем сделать переменную sum_dif, которую мы используем, но пока не объявили. Можем каждый раз dif_sqared добавлять в неё. Для этого мы можем использовать оператор плюс равно. [sum_dif += dif_sqared;] Сейчас это работать не будет, потому что нам нужно сначала объявить var. [var sum_dif = 0;] Вот, уже кое‑что. Теперь как будет работать цикл. У нас идёт нулевой элемент, сначала считаем отклонение, возводим его в квадрат, у нас появляется какое‑то квадратичное отклонение, и прибавляем это значение к переменной sum_dif. sum_dif по умолчанию у нас будет равно 0, соответственно, у нас 0 + квадратичное отклонение — получится первое квадратичное отклонение. Потом следующий цикл. i равно 1, берём второй элемент в массиве, вычитаем из него второй элемент в другом массиве, возводим в квадрат, добавляем, третий цикл и т. д. Таким образом мы получим сумму значений. Но для того чтобы посчитать MSE, нам нужно ещё их количество. Кстати, количество — это вот эта вот n, которую мы тоже ещё нигде не объявили. Давайте вот эту вот n заменим на count_dif. А теперь нам осталось последнее — объявить, что такое count_dif. [count_dif = fact.length]

fact.length — это длина массива fact. В применении к массивам длина — это количество элементов в нём. Так как у нас fact и predicted одной и той же длины, то мы можем использовать либо одно, либо другое. Количество элементов — это и есть то, на что нам нужно поделить сумму отклонений для того, чтобы посчитать её среднее. В общем, мы готовы. Кстати, имеет смысл здесь тоже прописать var. [var count_dif = fact.length;] Давайте теперь посмотрим, что у нас получилось. Смотрите, какая‑то фигня. Абсолютно нормально, когда вы запускаете код и у вас появляются какие‑то ошибки, этого бояться не нужно. Мне говорят, что я где‑то пропустил точку с запятой. Наверное, вот тут нужно точки с запятыми вместо запятых. Вот.

Давайте попробуем посчитать. Нам нужно сначала fact, потом predicted. [=rmse(A2:A726,B2:B762)] Что‑то посчиталось. Давайте это подальше перенесём и посчитаем руками, чтобы проверить, правильно или нет. Сначала отклонение, потом квадрат отклонений, среднее квадратов отклонений, квадратный корень. Они должны совпадать, но не совпадают. Где‑то мы совершили ошибку. Пойдём проверять. Здесь мы считаем как fact минус predicted, а здесь наоборот. Это не должно иметь никакого значения, но давайте попробуем изменить. Окей, немного изменилось из‑за округления. Проверяем дальше. Вроде всё правильно, но результат неправильный. Если получается такая штука, нужно идти пошагово назад. Например, давайте попробуем вывести не RMSE, а MSE. Значения разные, непонятно. Мы посчитали по всей табличке, по всем данным, а можно попробовать по нескольким значениям, например по трём. Пересчитываем, получаем что‑то очень странное.

Гипотеза, которая у меня есть, — это то, что вот здесь символ, который обычно используется для возведения в степень, считает что‑то другое. Это возможно. Давайте попробуем это заменить на более универсальный способ. Использовать функцию возведения в степень из библиотеки математики. Она выглядит вот так: math.pow(dif, 2). Она возводит одно число в степень, dif в степень 2. Сохраняем. О, исправили. Гипотеза была правильная. Теперь возвращаем. Не MSE считаем, а RMSE. Готово.

Основной функционал мы сделали. Формулы работают, и одна, и вторая. Хочу вам показать ещё несколько штук, которые позволят вам существенно увеличить качество вашей работы. Я сейчас открою файлик, который показывал вам в самом начале, и покажу ещё несколько крутых штук. Это формулы, которые я прописал, когда готовился к лекции. Вот у нас функция RMSE, и вот у нас функция yyyymm, превращающая даты в года.

Видите, здесь куча каких‑то штук, которые мы ещё не прописывали. Давайте сначала поговорим про RMSE. У нас здесь есть вот такая вот проверка. Здесь в принципе всё то же самое, просто немного по‑другому написано. И здесь я не считаю отдельно MSE, а сразу в переменную rmse считаю квадратный корень. Можно даже ещё сократить, написать это сразу в return. Но по поводу проверки, смотрите, мы предполагаем, что в данных в качестве параметров функций пользователь будет вводить массивы. Тут должно быть много значений, если тут будет одно значение, то RMSE будет невозможно посчитать, там выдаст сразу глупую ошибку. Но раз мы предполагаем, а давайте мы проверим и в случае чего скажем пользователю, что он сделал что‑то не так. Первая строчка именно это и делает. If не predicted.map или не fact.map, то throw сообщение об ошибке вот такое. [if (!predicted.map | | !fact.map) throw("predicted and fact should be arrays");] Что здесь что? Помните, мы здесь уже писали вот эту вот проверку if (dates.map), если это массив, то делай что‑то. Восклицательный знак — это проверка на «не», это отрицание. Две вертикальные черты — это логический оператор «или». Я надеюсь, вы это изучили в курсе по JavaScript. Получается, что если переменная predicted не массив или переменная fact не массив, то есть если хотя бы одна из них не массив, то верни ошибку. Predicted и fact должны быть массивами. Как это работает? Давайте прямо здесь проверим. Равно rmse и вставим одну ячейку. Он выдал ошибку, и если вы наведёте на неё, он вам конкретно покажет, что за ошибка. И здесь написано сообщение, которое я написал в проверке после throw. Дальше else if. Если они оба массивы, то всё классно. Но мы предполагали, что они оба будут одинаковой длины. Соответственно, давайте проверим, одинаковой ли они длины. Если длина массива predicted не равна длине массива fact, то снова выйдет другая ошибка. [else if (predicted.length != fact.length) throw ("length of predicted and fact arrays should be equal");] Аналогично, проверяем. Если массивы разной длины, то вычисление не имеет смысла, получается ерунда. Мы ввели массивы разной длины, и нам выдало нашу ошибку. Всё отлично работает. Если и это правильно, если они одинаковой длины, то дальше мы уже запускаем все наши вычисления. Это хорошая практика — добавлять в ваши функции проверку на то, что пользователь вводит в них ровно то, что вы ожидали.

И ещё одно. Видите, у меня есть вот такие вот штуки здесь.

Вот это комментарий. После двойного слеша я просто описываю текстом, что делает вот эта строка. Он не исполняется, он просто как заметка для меня и для любого программиста, который откроет потом этот код, для того чтобы было проще понять, что здесь происходит, что эта строка делает. Вот это, то, что пишется перед функцией, — это специальный тип комментария. Давайте я покажу, что он делает. Например, если вы используете какую‑то готовую функцию, то у вас выскакивает описание, что эта функция делает. Дальше вы открываете функцию, и у вас выходит подробное описание того, что куда нужно вводить, что означают переменные и что в итоге получится. Все вот эти данные и прописываются в этом разделе. Он начинается как слэш, двойная звёздочка и заканчивается звёздочка, слэш. Дальше каждая строчка начинается со звёздочки. Вы сначала пишете то, что называется summary, то есть в нескольких словах описываете, что конкретно делает эта функция. Дальше для каждого параметра, который вы передаёте, вы пишете собака, параметр, в фигурных скобочках название параметра, и описываете, что вы ожидаете, что человек сюда введёт. И так вы делаете для каждого вашего параметра. Дальше собака, return, и тут вы описываете, что функция возвращает. И дальше вы пишете, что это кастомная функция. Давайте посмотрим, как это выглядит в гугль‑таблицах. Начинаю вводить название функции, и у меня уже выскакивает Calculates rmse. Открываю скобочки и вижу example, summary, и дальше почему‑то не выводится то, что мне нужно.

Заключение

Прописывание таких штук делает ваши функции гораздо удобнее в использовании как для вас, так и для других пользователей, если вы будете это шерить. То есть представьте, что вы откроете этот документ через два года и попытаетесь вспомнить, что делает та или иная формула. Вот такие комментарии вам очень хорошо помогут вспомнить, что она делает. На этом у меня всё на сегодня. Что мы сегодня обсудили? Мы сегодня поговорили о том, как работают скрипты, как их заливают, и я надеюсь, что вы прошли курс по JavaScript, он вам очень сильно пригодится в жизни. Мы прошли то, как создавать формулы, начиная от самой простой и заканчивая более сложными вариантами. Вы точно так же можете делать неограниченное количество других функций, в зависимости от того, какие задачи вы решаете. Это вообще очень хороший способ ускорить свою работу. В литературе я выложу ссылку на документацию по формулам, отдельную ссылку на курс по JavaScript, а дальше уже предел развития — это только ваше воображение. Аналогичный функционал вы можете использовать для создания автоматизирующих скриптов, которые будут не формулы, а, например, аддоны. То есть видите, есть куча готовых аддонов. Можно делать кастомные пункты меню, которые будут делать совсем какие‑то разные вещи. С помощью AppScript можно связывать между собой гугль‑доки, гугль‑аналитикс, гугль‑адвордс, почту, какие‑то сторонние сервисы. В общем, открывается огромный простор для творчества, и практически любую задачу можно здесь решать. Вот. Так что на этом всё. Спасибо, пока.