Spreadsheets что это такое простыми
9 простых, но важных лайфхаков по работе с Google Таблицами
Были времена, когда «эксель» считали лишь размером одежды, а бухгалтера пересчитывали сметы за электронными таблицами на калькуляторе. Сейчас же решения строятся на всестороннем анализе цифр, а Big Data давно стали мировым трендом. Да и работа в Google Spreadsheets давно стала нормой и обыденностью. Вот только многие ограничиваются общими знаниями о них, не вникая глубже в возможности. Так появляются стереотипы о том, что Google Spreadsheets — это сложно, нудно и лучше с ними дела лишний раз не иметь.
Основатель Ringostat Александр Максименюк — настоящий фанат Google Spreadsheets и знает о таблицах чуть более, чем все. С его подачи (а также просто потому, что это очень удобно) мы работаем в Spreadsheets постоянно: составляем медиапланы и отчеты, строим прогнозы, графики и дашборды разных уровней. За время работы у нас накопилось солидное количество лайфхаков, частью из которых мы делимся в этой статье.
1. Нейминг
Многие забывают прописывать названия документов или относятся к этой части работы, как персонаж известного мультфильма: «И так сойдет!». Документов становится все больше и вот уже команда утопает в хаосе безымянных файлов, выискивая один-единственный нужный.
Называйте документы так, чтобы сразу найти нужный, стукнув кулаком по клавиатуре в четыре часа утра после бурной вечеринки. Шутка, конечно, но прописать ключевые слова в названии таблицы будет совсем не лишним. У нас в Ringostat такой шаблон оформления названий:
Что в документе / Зачем он нужен / Кому он нужен / Ключевые слова
Например, если оформить документ так, его можно будет найти, вбив в поиске любое слово, связанное с таблицами и оформлением:
2. Горячие клавиши
Совет «используйте горячие клавиши» настолько банален, что его дают даже тренеры личностного роста и тайм-менеджмента. Доведенное до автоматизма использование горячих клавиш экономит время и требует меньшей концентрации внимания, чем поиск нужной функции в меню и выпадающих окнах. Если вы пока не освоили горячие клавиши, начните со списка ниже. А если уже встали на путь работы без мышки, все равно пробегитесь по списку из справки, там много полезного.
3. Оформление
4. Валидация
Если вы работаете с большим массивом цифр, велика вероятность ошибиться по невнимательности. Например, если среди диапазона чисел притаится одна ячейка с процентом, таблица не сможет посчитать формулу.
Во избежание таких недочетов, используйте проверку данных. Она пригодится как при ручном вводе значений, так и при «подтягивании» информации извне. Чтобы начать валидацию, выбираем в меню «Данные — Проверка данных» и настраиваем ее под свои нужды.
Можно запретить любые данные — хоть даты в неправильном формате, хоть текст без буквы «а». При проверке уже заполненных ячеек, валидация указывает на ошибки. Позже эта функция, в зависимости от настройки, либо блокирует ввод некорректных данных, либо предупреждает об ошибке.
5. Предсказания
В электронных таблицах многое строится на подсчете, формулах и функциях. Вы наверняка знакомы с такими формулами как SUM, AVERAGE или ROUND, поэтому сегодня мы расскажем нечто более захватывающее.
Функция FORECAST, или ПРЕДСКАЗ в русской локализации, выстраивает прогноз того, как будут заполнены последующие клетки, исходя из заполнения предыдущих. Машинный подсчет будет несколько груб, но он может обрисовать тенденцию.
Попробуем предсказать изменение аудитории сайта. Для этого возьмем данные о посещении и запишем их рядом с номером соответствующего месяца. Добавим еще пару строк для месяцев, которые будем предсказывать:
Затем в ячейке B8, где мы хотим увидеть данные на будущее, прописываем такую формулу:
ПРЕДСКАЗ — это сама формула;
A8 — это показатель, для которого будет рассчитываться результат формулы;
$B$2:$B$8 — это диапазон известных данных, на основе которых Google Spreadsheets составляет прогноз;
В результате таблица показывает, как аудитория сайта вырастет, если продолжать работать в обычном темпе.
6. Диаграммы
В предыдущем примере нам достаточно было «на глаз» оценить показатели, чтобы понять: аудитория растет, при том почти равномерно. Но для работы с сотнями и тысячами показателей, беглого взгляда на цифры будет мало. Тут на помощь приходят диаграммы, которые преобразуют массив информации в понятную схему.
Каждая из категорий имеет свои подвиды, есть также инструменты для создания блок-схем, лепестковых диаграмм других нестандартных решений.
Чтобы построить диаграмму, выбираем в меню «Вставка — Диаграмма». Поверх таблицы появится активное поле элемента, а в правой части экрана откроется меню «Редактор диаграмм». Выберите внешний вид графика, в поле «Диапазон данных» отметьте цифры, которые будут отражаться в виде графика, в нашем случае — столбец с количеством посетителей. В поле диаграммы появляется синяя линия, иллюстрирующая разницу между показателями. Значение оси Y автоматически подтянулось, исходя из разряда чисел. В поле «Ось X» выбираем столбец с номерами месяцев, после чего на диаграмме появляется сетка координат и можно сориентироваться, какому месяцу соответствует каждый показатель.
7. Чек-листы
В Google Spreadsheets есть возможность превратить ячейку в чекбокс, благодаря чему таблица становится еще и чек-листом. Для этого выбираем в меню «Вставка — Флажок». Например, при подготовке выставочного стенда, чекбоксы можно добавить прямо в смету и мониторить готовность к мероприятию в одном документе. Чекбоксы пригодятся и для мониторинга эффективности команды: сотрудники могут записывать недельный план в общую таблицу и отмечать задачи по мере выполнения. А подсчитать продуктивность поможет формула:
ЕСЛИОШИБКА — сама формула.
СЧЁТЕСЛИ — формула подсчета выполненных задач
B1:B5 — ячейки, в которых находятся чекбоксы
«TRUE» — уточнение, что чекбокс должен быть отмечен
СЧЁТЗ — формула подсчета поставленных задач
A1:A5 — ячейки, в которые должны быть вписаны задачи
В итоге таблица сама посчитает, насколько сотрудник выполнил план
8. Работа с текстом
9. Волшебная ARRAYFORMULA
Используйте эти хаки и вы полюбите Google Spreadsheets так же, как любим их мы 🙂
Пользователи электронной таблицы корректировать любое сохраненное значение и наблюдать влияние на вычисленные значения. Это делает электронную таблицу полезной для анализа «что, если», поскольку многие случаи можно быстро исследовать без ручного анализа. Современное программное обеспечение для работы с электронными таблицами может иметь несколько используемых текстовых листов и отображать данные в виде и цифр или в графической форме.
Содержание
Использование
Электронная таблица состоит из таблицы ячеек, упорядоченных по строкам и столбцам и обозначенным координатам X и Y. Расположение X, столбцы обычно представлены буквами «A», «B», «C» и т. Д., В то время как обычно представлены числами 1, 2, 3 и т. Д. Одна ячейка может быть обозначена путем обращения к строке и столбцу, например «C10». Эта электронная концепция ссылок на ячейки впервые введена в LANPAR (язык программирования массивов в случайном порядке) (совместно изобретенный Рене Пардо и Реми Ландау) и вариант использования в VisiCalc и известный как «нотация A1». Кроме того, электронные таблицы имеют понятие диапазона, группы ячеек, обычно связанных. Например, можно ссылаться на первые десять ячеек в первом столбце с диапазоном «A1: A10». LANPAR представил инновационные решения с прямым обращением / вычислением естественного порядка, которые не появлялись повторно в Lotus 123 и Microsoft MultiPlan версии 2.
В современных приложениях для работы с электронными таблицами несколькими таблицами, называемыми компьютерими таблицами или просто листами, собираются вместе для сформировать рабочую тетрадь. Рабочая книга физически представлена, содержит все данные для книги, листов и ячеек с листами. Рабочие листы обычно представлены вкладками, которые переключаются между страницами, каждая из которых содержит один из листов, хотя Числа меняют эту модель. Ячейки в многолистовой книге имя листа к своей ссылке, например, «Лист 1! C10 ». Некоторые системы расширяют этот синтаксис, чтобы разрешить ссылки на ячейки для разных книг.
История
Таблицы на бумаге происходит
Ранние неотъемлемые реализации
Пакетный генератор отчетов в виде электронных таблиц
В Applied Data Resources был препроцессор FORTRAN под названием Empires.
В конце 1960-х годов Xerox использовала BCL для разработки более сложной версии своей системы разделения времени.
Компилятор электронных таблиц LANPAR
Язык программирования Autoplan / Autotab для электронных таблиц
GE Information Services, которая управляла службой расширения разделения времени, также запустила свою собственную система электронных таблиц, Язык финансового анализа (FAL), примерно в 1974 году. Позднее она была дополнена дополнительным языком, электронными таблицами TABOL, который разработан независимым автором Оливером Веллакоттом из Великобритании. И FAL, и TABOL интегрированы с системой баз данных GEIS, DMS.
Система финансового планирования и контроля IBM
язык моделирования APLDOT
VisiCalc
Потому что Дэн Бриклин и Боб Франкстон реализовали VisiCalc на Apple II в 1979 году и IBM PC в 1981 году, концепция электронных таблиц стала широко известна в начале 1980-х годов. VisiCalc была первой электронной таблицей, которая объединила в себе все основные функции современных приложений для работы с электронными таблицами (за исключением прямой ссылки / пересчета в естественном порядке), такие как WYSIWYG интерактивный пользовательский интерфейс, автоматический пересчет, строки состояния и формул, копирование диапазона с относительным и абсолютные ссылки, построение формулы путем выбора ссылочных ячеек. В то время, не зная о LANPAR, журнал PC World назвал VisiCalc первой электронной таблицей.
SuperCalc
Lotus 1-2-3 и другие электронные таблицы MS-DOS
Принятие IBM PC после своего появления в августе 1981 года начинал медленно, потому что большинство доступных для него программ были переводами с других компьютерных моделей. Ситуация резко изменилась с появлением Lotus 1-2-3 в ноябре 1982 года и выпуском в продажу за январь 1983 года. Он был написан специально для IBM PC, он хорошая производительность и стал убийцей. приложение для этого ПК. Lotus 1-2-3 привел к увеличению продаж ПК за счет улучшения скорости и графики по сравнению с VisiCalc на Apple II.
Microsoft. Excel
Электронные таблицы на основе Интернета
Электронные таблицы для мэйнфреймов
Другие электронные таблицы
Известное текущее программное обеспечение для работы с электронными таблицами:
Снятое производство программного обеспечения для работы с электронными таблицами:
Другие продукты
Таблица 2000 попытка значительно упростить построение формул, но в целом безуспешно.
Концепции
Ячейки
| A | B | C | D | |
|---|---|---|---|---|
| 01 | Продажи | 100000 | 30000 | 70000 |
| 02 | ки | 25490 | 30 | 200 |
Значения
Значение можно настроить с клавиатуры компьютера, набрав его непосредственно в ячейку. В качестве альтернативного значения может быть основано на формуле (см. Ниже), которая может выполнять вычисление, отображать текущую дату или извлекать внешние данные, такие как котировка акций или значение из базы данных.
Правило значения электронной таблицы
Автоматический пересчет
Эта дополнительная функция, стандартная для электронных таблиц с 1980-х годов, устраняет необходимость вручную запрашивать программу электронных таблиц для пересчета значений (в настоящее время обычно используется по умолчанию, если специально не «выключено» для больших таблиц, обычно для повышения производительности). Некоторые более ранние электронные таблицы требуют пересчета больших или сложных электронных таблиц часто снижают скорость ввода данных. Многие современные электронные таблицы по-прежнему поддерживает этот параметр.
Обновление в реальном времени
Эта функция относится к периодическому обновлению содержимого с использованием значения из внешнего источника, например, ячейки в «удаленной» электронной таблице. Для общих таблиц применяемых к «эффективным ячеек», обновленным другим пользователям. Все зависимые ячейки также должны быть обновлены.
Заблокированная ячейка
После ввода выбранных ячеек (или вся электронная таблица) при желании могут быть «заблокированы» для исключения случайной перезаписи. Используется такой коэффициент преобразования килограмм / фунт (2,20462262 до восьми знаков после запятой). Несмотря на то, что отдельные отдельные помечены как заблокированные, данные электронной таблицы не защищены, пока эта функция не будет активирована в настройках файла.
Формат данных
При желании можно определить ячейку или диапазон, чтобы указать способ отображения значений. Формат отображения по умолчанию обычно задается его исходным содержимым, если это не было специально установлено заранее, так, например, «31/12/2007» или «31 декабря 2007» по умолчанию будет форматом ячейки даты. Точно так же добавление знака% после числового значения пометит ячейку как процентный формат ячейки. Этот формат не изменяет содержимое ячейки, только отображаемое значение.
. Это может допускать недопустимые операции (например, выполнение умножения на ячейку, содержащаяся), что приводит к нелогичным результатам без рекомендованных предупреждений.
Форматирование ячеек
В зависимости от возможностей приложения для работы с электронными таблицами каждая ячейка (как и ее аналог «стиля» в текстовом процессоре ) может быть отформатирована отдельно с помощью атрибуты либо содержимое (размер точки, цвет, полужирный или курсив), либо ячейки (толщина границы, затенение фона, цвет). Чтобы облегчить читаемость электронной таблицы, к данным можно условно применить форматирование ячеек; например, отрицательное число может быть красным цветом.
Форматирование ячеек обычно не влияет на ее содержимое, и в зависимости от того, как в ячейках содержатся или копируются на другие листы или приложения, форматирование может не переноситься с содержимым.
Именованные ячейки
Ссылка на ячейку
Вместо именованной ячейки альтернативным подходом является использование ссылки на ячейку (или сетку). В той же электронной таблице указаны ссылки на другие ячейки, указанные в другой электронной таблице. приложение.
Когда компьютер вычисляет формулу в одной ячейке для обновления отображаемого значения этой ячейки, ссылка (я) на ячейку в этой ячейке, называя другую ячейку (я), заставляет компьютер извлекать значение названная ячейка (и).
Ячейка на том же «листе» обычно адресуется как:
Ячейка на другом листе той же таблицы обычно адресуется как:
Некоторые реализации электронных таблиц в Excel позволяют ссылаться на ячейки другой электронной таблицы (не текущего открытого и активного файла) на том же компьютере или в локальной сети. Он также может относиться к ячейке в другой открытой и активной электронной таблице на том же компьютере или сети, которая определена как используемая. Эти ссылки содержат полное имя файла, например:
В электронной таблице ссылки в ячейках автоматически обновляется при вставке или удалении новых строк или столбцов. Однако следует проявлять осторожность при добавлении строк непосредственно перед набором итоговых значений столбцов.
Диапазоны ячеек
Подобным образом, вместо использования именованного диапазона ячеек, можно использовать ссылку на диапазон. Ссылка на диапазон ячеек типична для формы (A1: A6), которая определяет все ячейки в диапазоне от A1 до A6. Формула, такая как «= СУММ (A1: A6)», добавит все ячейки и поместит результат в ячейку, содержащую саму формулу.
Таблицы
В самых ранних электронных таблицах ячейки представляли собой простую двухмерную сетку. Со временем модель расширилась, включив в нее третье измерение и в некоторых случаях серию именованных сеток, называемых листами. Наиболее продвинутые примеры позволяют выполнять операции инверсии и вращения, которые могут разрезать и проецировать набор данных способов.
Формулы
Формула присваивает значение ячейки или диапазону ячеек и обычно имеет формат:
Доступные варианты допустимых формул различных вариантов реализации электронной таблицы, но, как правило, большинство арифметических операций и довольно сложных вложенных условных операций могут использоваться большинством современных электронных таблиц. Современные реализации также предоставляются функции для доступа к удаленным настраиваемым функциям, удаленным и приложениям.
= ЕСЛИ (СУММ (A1: A6)>100, «Более 100%», СУММ (A1: A6))
Функции
Подпрограммы
Сами функции не могут записывать в рабочий лист, а просто возвращают свою оценку. Однако в подпрограммы Microsoft Excel могут записывать значения или текст, найденные в подпрограмме, непосредственно в электронной таблице. На рисунке показан код Visual Basic для подпрограммы, которая вычисляет каждый член именованной столбца x, вычисляет его квадрат и записывает это значение в соответствующий элемент именованной столбца y. Столбец y не содержит формулы, потому что его значения вычисляются в подпрограмме, а просто записываются в электронной таблице.
Удаленная электронная таблица
Всякий раз, когда делается ссылка на ячейку или группу ячеек, которые не защищены в текущем физическом файле электронной таблицы, это считается доступом к «удаленной» электронной таблице. К содержимому первой ячейки можно получить доступ либо при ссылке с обновлением вручную, либо в случае электронных таблиц на базе Интернета, как значение, близкое к реальному времени, с заданным интервалом автоматического обновления.
Диаграммы
Многомерные электронные таблицы
Трапеция, электронная таблица для Mac, пошла дальше и явно поддерживала не только столбцы таблицы, но и матричные операторы.
Логические электронные таблицы
Проблемы программирования
Так же, как ранние языки программирования были разработаны для создания распечаток электронных таблиц, сами методы программирования эволюционировали для обработки таблиц (известные также как электронные таблицы или матрицы ) данные более эффективно в самом компьютере.
Разработка для конечных пользователей
Программы электронных таблиц
Таблицы обычно устанавливают автоматически обновлять ячейки, когда зависят от изменений. В первых таблицах использовалась простая тактика, современная электронная таблица рассчитывают, следуя минимальному порядку пересчета из графа зависимостей. Более поздние электронные таблицы также включают ограниченную возможность изменения значений в обратном порядке, с помощью исходных значений так, чтобы конкретный ответ был в данной ячейке. Однако, как формулы ячеек электронной таблицы обычно не обратимы, этот метод имеет несколько ограниченную ценность.
Многие концепции, общие для последовательностей программирования, имеют аналоги в мире электронных таблиц. Например, последовательная модель индексированного цикла обычно представлена в виде таблицы ячеек с похожими формулами (обычно различающимися только ячейками, на которые они ссылаются).
Недостатки
Хотя электронные таблицы представляют собой важный шаг вперед в количественном моделировании, у них есть недостатки. К их недостаткам относится недружелюбие буквенно-цифровых ячеек.
Другие проблемы, связанные с электронными таблицами. включают:
Несмотря на то, что для настольных приложений с электронными таблицами существуют встроенные и сторонние инструменты, которые устраняют некоторые из этих недостатков, осведомленность о них и их использование обычно невысоки. Хорошим примером этого является то, что 55% профессионалов рынка капитала «не знают», как проверяются их электронные таблицы; только 6% инвестируют в стороннее решение
Риск электронных таблиц
Несмотря на это, исследование, проведенное ClusterSeven, показало, что около половины (48%) Руководители высшего звена и старшие менеджеры фирм, отчитывающихся о годовой выручке более 50 млн фунтов стерлингов, заявили, что либо вообще не было контроля за использованием, либо плохо применялись ручные процессы по использованию таблиц в фирмах.
Базовые советы для удобной работы в Google Spreadsheets
Александр Максименюк, основатель и CVO Ringostat, еще со времен университета активно работает с таблицами. Поэтому к нему обращаются за советом, как написать формулу или обработать данные. По его словам, самая частая ошибка — это слишком «тяжелые» решения для задач, которые можно решить проще. Или когда вручную делается то, что можно автоматизировать. Для тех, кто работает с таблицами Google и хотел бы делать это лучше, Александр подготовил подборку советов — от простых к сложным.
Время чтения 14 минут
Вы можете перейти сразу к интересующему разделу:
Здесь мы не будем разбирать, как создать Google Таблицу или вписывать в нее информацию. Инструкции на эту тему можно легко найти. В этой статье мы поговорим про неочевидные лайфхаки при работе с Google Sheets.
Английский интерфейс и американская локаль
Русскоязычный интерфейс для большинства понятней, но в нем кроется подвох — разделителем аргументами функции здесь является точка с запятой. В английском же языке — запятая. При этом все решения, о которых поговорим ниже, настроены под запятую. Локаль также влияет на разделитель дробной части в цифрах — в англоязычной версии точка, в русскоязычной запятая. Из-за чего появляются проблемы при импорте, например, из базы данных.
Почему именно американская локаль: в ней точно будут точки, она является нативной и самой распространенной — так что в ней минимум багов. Но есть и пара недостатков: дата представлена в формате месяц, число, год, а разделителем выступает слеш.
Проверка данных (валидация)
Особенно важна, когда разные сотрудники вручную вносят данные в документ — а значит, могут делать ошибки. Валидация настраивается в разделе Data — Data Validation и бывает двух типов:
Нейминг
Называйте таблицу максимально понятно — причем не только вам, но и тем людям, которым к ней откроете доступ. Так вы избежите ситуации, когда вам нужно что-то срочно найти, а в списке открываются сплошные «новые документы». Пример названия: «Отчет об эффективности сотрудников / Иванов / Май 2018 / Компания N».
Мы обычно шутим, что документ нужно называть так, чтобы его можно было найти, стукнув во сне кулаком по клавиатуре после вечеринки в 4 утра.
Ringostat для маркетолога
Оформление
Советы, описанные в этой статье, могут показаться сложными. Особенно, если вы владелец бизнеса или маркетолог, которому отчеты нужны, чтобы понимать эффективность рекламы и окупаемость вложений в нее. В этом случае вам подойдет готовый инструмент — сквозная аналитика. Она сама подтягивает ключевые данные и автоматически рассчитывает окупаемость вложений в рекламу — ROI.
При необходимости вы также можете загружать расходы вручную. Это полезно, если вы продвигаетесь не только в тех системах, с которыми у Ringostat есть готовые интеграции. Например, вы можете оценить рентабельность вложений в SEO, баннерную рекламу и т. д
Работа с формулами
Приучайте себя делать все расчеты с использованием формул — даже если нужно посчитать 2+2. Расчетные (зависимые) величины могут быть без формул только в двух случаях:
Общие советы для начинающих
Модификаторы формул
IF и IFS работают для множества функций, но нужно помнить хотя бы основные:
Если добавить к ним IF, то будет проверка на одно определенное условие, а если IFS, то таких условий может быть много. И только при выполнении всех из них будут выводиться значения.
Еще одна полезная формула — UNIQUE, которая возвращает уникальные строки в указанном диапазоне, убирая дубликаты. На примере ниже таблица с источниками и каналами, названия которых дублируются. Нужно посчитать количество сессий и коэффициент конверсии для каждого из них. При этом нам нужно суммировать данные по каждому из каналов — например, все сессии из google cpc. Что мы делаем:
ARRAYFORMULA
Отображает значения, полученные с помощью формулы массива, в нескольких строках и столбцах. При своей простоте, сильно упрощает работу с таблицами. Допустим, есть большой массив данных, который нужно обработать — посчитать conversion rate на примере ниже. Мы могли бы значение одной ячейки поделить на другую и растянуть.
В чем преимущество ARRAYFORMULA:
ARRAYFORMULA можно использовать и для более сложных задач. Допустим, нужно посчитать сумму и сделать проверку по REGEX — точному соответствию определенному тексту. Если делать проверку на соответствие регулярному выражению, то не существует формулы, которая работает с массивом.
Представим, документ, где приведено количество переходов из всех источников. Нас интересуют именно переходы из Facebook. При этом в документе есть пять меток со словом Facebook (например, Facebook_buisness) и еще три с сокращением Fb (Fb_123). Благодаря подвиду этой формулы REGEXMATCH, мы можем суммировать показатели, где в тексте есть упоминание слова Facebook и Fb.
С ARRAYFORMULA можно использовать любые формулы, кроме INDEX, SEARCH, QUERY, в некоторых случаях некорректно работает SUM и т. д.. Когда выделяете два или несколько массивов, они должны быть одинакового размера.
Еще одно применение ARRAYFORMULA — перенос данных с одного листа на другой в рамках одного документа. Допустим, в документе формируется дэшборд, в котором в котором 200 строк и тридцать столбцов. Импортируем данные на отдельный лист в том же документе, и с помощью ARRAYFORMULA затягиваем огромный диапазон — горизонтально и вертикально. Например, так можно затянуть одной формулой пять строк данных за три года.
Формулы, которые важно помнить
Полезные фишки
Если документ тяжелый и с ним неудобно работать
Лимиты
Рекомендуем конспект доклада с примерами дашбордов для техподдержки и отдела продаж — все они построены с помощью Spreadsheets.