Kitabı oxu: «Google Таблицы. Это просто. Функции и приемы»

Şrift:

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

* * *

Введение

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

Каждый из нас скептически относился к Google Таблицам и их возможностям в первое время после перехода с привычного и мощного Excel. Нас беспокоили вопросы безопасности, напрягало отсутствие полноценного функционала сводных таблиц и других инструментов.

Однако со временем оказалось, что Таблицы во многом не уступают Excel, а по ряду критериев опережают привычное приложение для работы с электронными таблицами. Сейчас нам сложно представить работу с документами и отчетностью внутри большой распределенной компании без помощи Таблиц.

Когда в файле работают одновременно 20 человек и он при этом не «тормозит», все данные меняются в реальном времени и не нужно пересылать друг другу файлы с названиями «Бюджет2015_Финал_v2_Иванов» или «Бюджет2015_FINAL_на согласование 11.02.2015» – это очень удобно.

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

Мы не собирались копировать справку Google Таблиц и делать описание всех функций или писать учебник. Справка доступна всем, и нет никакого смысла ее дублировать, а по Excel учебников много – и тот, кто найдет время на их изучение, сможет перенести большинство приемов в Google Таблицы.

Вместо этого мы решили собрать полезные, на наш взгляд, функции, рассказать о том, как с ними работать, и поделиться своим опытом.

В одних кейсах вы найдете готовые (и часто необычные) решения своих задач, примеры автоматизации, в других – источник для вдохновения и новые идеи.

Для кого будет полезна эта книга?

• Для тех, кто раньше работал в Excel, перешел на Google Таблицы и хотел бы знать, в чем их сходство и различие, какие функции существуют только в одной среде.

• Для тех, кто впервые начал работать с аналитикой и таблицами именно в Google Таблицах (уж извините за тавтологию).

• Для опытных пользователей Excel и Таблиц, которые хотели бы иметь под рукой справочное руководство и которым интересно ознакомиться с кейсами разной сложности.

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

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

Новые советы по Google Таблицам вы сможете найти на нашем канале в Телеграме – мы публикуем несколько кейсов в неделю с иллюстрациями и файлами-примерами:

https://telegram.me/google_sheets

В этой книге мы не стали рассматривать скрипты, позволяющие автоматизировать процессы в Google Таблицах и других приложениях Google Apps. Скрипты – это аналоги макросов в Excel, работающих на основе JavaScript. Для изучения этой темы рекомендуем вам книгу Going GAS издательства O’Reilly.

По любым вопросам пишите:

Ренат: renat@shagabutdinov.ru

Евгений: tr1kster@ya.ru

Будем признательны за комментарии и обратную связь.

ЧЕМ ТАБЛИЦЫ ЛУЧШЕ EXCEL

• Совместная работа в режиме онлайн.

• Бесплатный сервис (если вы используете его для себя, а не для бизнеса).

• SQL-запросы прямо в таблицах с помощью функции QUERY.

• Большое количество бесплатных надстроек (дополнений).

• Богатые возможности по поиску и загрузке котировок, данных с сайтов, RSS-фидов, другой информации из Сети.

• Работа с любого компьютера, планшета и (хотя это уже затруднительно) даже с телефона.

• Удобная передача файлов – достаточно открыть доступ и отправить ссылку. Для этого не нужен быстрый интернет и компьютер, достаточно даже смартфона.

• Возможность связать несколько документов: данные будут обновляться автоматически в режиме онлайн (в Excel – только при открытии файла-источника, на который ссылается ваша книга).

ЧЕМ ТАБЛИЦЫ ХУЖЕ EXCEL

• Сводные таблицы и диаграммы с урезанным функционалом.

• Нет расширенного фильтра.

• С большим количеством данных и формул Таблицы работают медленнее.

• Таблицы связаны с их владельцем. Теоретически все ваши файлы могут пропасть, если что-то произойдет с Google или его хранилищами. Это маловероятно, но в любом случае мы рекомендуем регулярно создавать резервные копии важных документов (это полезная привычка и в том случае, если вы работаете в офлайн-режиме в Excel).

ИМПОРТ ИЗ EXCEL

В меню Файл выберите пункт Импорт:

Затем перейдите на вкладку Загрузка, нажмите на единственную кнопку Выберите файл на компьютере или перетащите иконку с файлом в это окно:

После того как вы выберете или перетащите файл, появится следующее окно импорта:

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

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

ЭКСПОРТ В EXCEL

Чтобы сохранить таблицу на локальный диск в формате Excel, проделайте следующий путь:

Файл → Скачать как → Microsoft Excel (XLSX)

Книга сохранится на ваш локальный диск.

Обратите внимание, что при экспорте в Excel не сохранятся изображения, которые вы загрузили с помощью функции IMAGE (см. про эту функцию далее в соответствующей главе), а результаты работы функций, которых нет в Excel, сохранятся – но как значения. Это касается, например, функций SPLIT, IMPORTRANGE и других функций импорта (IMPORTXML, IMPORTDATA, IMPORTHTML), UNIQUE и COUNTUNIQUE, QUERY, REGEXEXTRACT, GOOGLEFINANCE.

Функции SPARKLINE превратятся в обычные спарклайны Excel.

Отсутствующие в Excel функции при экспорте превращаются в ЕСЛИОШИБКА (IFERROR), где в качестве первого аргумента будет запись вида _xludf.DUMMYFUNCTION (функция), которая и выдаст ошибку в Excel, а в качестве второго аргумента – то значение, которое возвращала эта функция в момент экспорта.

=ЕСЛИОШИБКА(__xludf.DUMMYFUNCTION("SPLIT(B21,"" "")");"Этот")

Как сделать документ легче и быстрее

• Удаляйте неиспользуемые строки на каждой вкладке (по умолчанию создается 1000 строк – если у вас на вкладке сейчас используется 200, удалите лишние 800, при необходимости просто добавьте нужное количество) и столбцы (аналогично). Можно воспользоваться надстройкой Crop Sheet или сделать это вручную.

• Оптимизируйте количество вкладок (попробуйте объединить в одну несколько вкладок с маленькими таблицами или списками).

• Если есть формулы поиска данных (ВПР/VLOOKUP, ИНДЕКС/INDEX, ПОИСКПОЗ/MATCH и другие), сохраняйте часть формул как значения (если не нужно будет эти значения обновлять). Например, если у вас подтягиваются данные за много месяцев с помощью VLOOKUP, оставляйте текущий месяц с формулами, а остальные данные сохраните как значения.

• Не заливайте строки/столбцы цветом целиком (и вообще старайтесь избегать излишнего форматирования).

• Проверьте, нет ли условного форматирования на (излишне) большом диапазоне ячеек.

• Не ставьте фильтр на все столбцы.

• Очистите примечания, если их много и они не нужны.

• Посмотрите, нет ли проверки данных на большом диапазоне ячеек.

Ренат: У нас в МИФе есть сводный файл со списком всех книг и большим количеством данных по ним, которые грузятся из разных источников. В какой-то момент некоторые коллеги перестали им пользоваться – ноутбуки перегревались, а файл иногда и не открывался:)
После оптимизации по большинству описанных пунктов он стал «летать».
Это работало и со многими другими документами.
В Excel, кстати, с помощью этих же правил бывали случаи уменьшения размера файла в разы, а несколько раз – даже на порядок (последнее, правда, случается только при пересохранении файла из старого формата XLS в новые XLSX, XLSM, XLSB).
Евгений: Иногда в документах приходится использовать ресурсоемкие формулы, которые ничем не заменить, например может потребоваться собирать в один файл данные из 20 разных документов формулой IMPORTRANGE. Если ничего не предпринять, то работа с таким документом может стать мучительной, формулы будут постоянно обновляться и все начнет тормозить.
В таких случаях я предлагаю следующее решение: написать скрипт, который будет вставлять формулы в требуемые ячейки, а потом сразу же заменять их на значения. Такой скрипт можно запускать как вручную, так и по расписанию, скажем, каждые два часа, и в этом случае необязательно находиться в файле – скрипт отработает в офлайн-режиме.

Работа с формулами и диапазонами

НЕСКОЛЬКО БАЗОВЫХ ПРАВИЛ

• Любая формула, как и в Excel, вводится со знака «равно».

• Текст указывается в кавычках, после названий листов ставится восклицательный знак, названия листов берутся в апострофы, если в них есть пробелы (‘Название листа’!A1).

• Аргументы функций разделяются символом (каким именно – зависит от региональных настроек), для России это точка с запятой.

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

РАЗБИРАЕМ НА ПРИМЕРЕ СУММЕСЛИ (SUMIF), КАК ЗАДАТЬ (ВЫБРАТЬ) В ФОРМУЛЕ ДИАПАЗОНЫ И УСЛОВИЯ

Эта глава будет полезна тем, у кого совсем мало опыта в написании формул. В ней я по шагам расскажу, как начать формулу; как выбрать диапазон; что делать, если он на другом листе; что нажать, чтобы перейти от выбора диапазона к условию; как сделать ссылки абсолютными и не потратить на все это слишком много нервов.

Возьмем простую формулу СУММЕСЛИ (SUMIF).

По ссылке https://goo.gl/1dIZMI вы найдете Google Документ с примером, на котором можно потренироваться. Для редактирования выберите:

Файл → Создать копию.

Начнем.

Чтобы немного усложнить себе задачу, вводить формулу мы будем на одном листе, а диапазон суммирования и диапазон условия – на другом (оба листа должны находиться в одном документе – в отличие от Excel, где можно ссылаться и на другие книги).

Формулы всегда начинаются со знака «равно».

Итак, выделяем ячейку В2 и начинаем вводить формулу. Уже после нескольких символов =СУ появляются варианты формул с этим слогом в названии, выбираем мышкой СУММЕСЛИ и кликаем на нее:

Видим вот такое окно (формулу можно писать как в самой ячейке, так и в строке формул – это не принципиально):

Под формулой видим окно справки. В нем цветом подсвечивается тот элемент, который нужно ввести сейчас. У нас это диапазон условия (если справка не открылась, нажмите на? под формулой).

Выбираем лист «Диапазоны» и выделяем диапазон условия – для этого кликаем на его первой ячейке и «протягиваем» до последней (в данном примере это С1:C7). Выделять ячейки можно и в обратном порядке: начать с С7 и протянуть до С1; или можно кликнуть на названии столбца С, и он выберется целиком.

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

Диапазон выбран, но по умолчанию он будет относительным, то есть при копировании формулы сместится вслед за ней. Чтобы этого избежать, нажмите на клавиатуре F4. Теперь в адресе появились символы $, и он стал абсолютным – это означает, что и строки, и столбцы зафиксированы, ничего сдвигаться не будет.

(Если нажать F4 еще раз, то зафиксируются только строки, при повторном нажатии – только столбцы.)

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

Кликнем на вкладку Сводный отчет и на ячейку А2. Не будем делать условие абсолютным, так как планируем скопировать формулу на ячейку ниже и нам нужно, чтобы условие с А2 поменялось на А3.

Вводим точку с запятой, возвращаемся на вкладку Диапазоны и выбираем последний диапазон суммирования. Его тоже сделаем абсолютным.

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

Формула готова, теперь скопируем ее из ячейки B2 в ячейку В3 (можно через пункты меню Копировать и Вставить, можно кликнуть на В2 и протянуть на ячейку ниже, можно использовать сочетания клавиш Ctrl + C, Ctrl + V):

АБСОЛЮТНЫЕ, ОТНОСИТЕЛЬНЫЕ И СМЕШАННЫЕ ССЫЛКИ. ФУНКЦИЯ ДВССЫЛ (INDIRECT)

Ссылки в Таблицах, как и в Excel, могут быть абсолютными и относительными.

Ровно так же они изменяются с помощью клавиши F4 или путем ввода знака доллара перед номером строки и/или перед номером столбца.

Читать этот подраздел дальше имеет смысл, если вы не работали с абсолютными и относительными ссылками в Excel или хотите повторить этот материал.

Относительные ссылки – это обычные ссылки вида A1, D10, AX127, которые по умолчанию появляются при щелчке на ячейку во время ввода формул. Такие ссылки смещаются вместе с формулой. Они являются относительными, так как ссылаются не на конкретную ячейку, а на ячейку, отстоящую на N строк и M столбцов от той, в которую введены. Так, если вы ввели в ячейку B1 формулу