Как сделать калькулятор в гугл таблице
Перейти к содержимому

Как сделать калькулятор в гугл таблице

  • автор:

9 простых, но важных лайфхаков по работе с Google Таблицами

Были времена, когда «эксель» считали лишь размером одежды, а бухгалтера пересчитывали сметы за электронными таблицами на калькуляторе. Сейчас же решения строятся на всестороннем анализе цифр, а Big Data давно стали мировым трендом. Да и работа в Google Spreadsheets давно стала нормой и обыденностью. Вот только многие ограничиваются общими знаниями о них, не вникая глубже в возможности. Так появляются стереотипы о том, что Google Spreadsheets — это сложно, нудно и лучше с ними дела лишний раз не иметь.

Основатель Ringostat Александр Максименюк — настоящий фанат Google Spreadsheets и знает о таблицах чуть более, чем все. С его подачи (а также просто потому, что это очень удобно) мы работаем в Spreadsheets постоянно: составляем медиапланы и отчеты, строим прогнозы, графики и дашборды разных уровней. За время работы у нас накопилось солидное количество лайфхаков, частью из которых мы делимся в этой статье.

1. Нейминг

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

Называйте документы так, чтобы сразу найти нужный, стукнув кулаком по клавиатуре в четыре часа утра после бурной вечеринки. Шутка, конечно, но прописать ключевые слова в названии таблицы будет совсем не лишним. У нас в Ringostat такой шаблон оформления названий:

Что в документе / Зачем он нужен / Кому он нужен / Ключевые слова

Например, если оформить документ так, его можно будет найти, вбив в поиске любое слово, связанное с таблицами и оформлением:

2. Горячие клавиши

Совет «используйте горячие клавиши» настолько банален, что его дают даже тренеры личностного роста и тайм-менеджмента. Доведенное до автоматизма использование горячих клавиш экономит время и требует меньшей концентрации внимания, чем поиск нужной функции в меню и выпадающих окнах. Если вы пока не освоили горячие клавиши, начните со списка ниже. А если уже встали на путь работы без мышки, все равно пробегитесь по списку из справки, там много полезного.

3. Оформление

  1. Закрепите «шапку» таблицы. Если информация не помещаются на одном экране, пользователю приходится скролить туда-обратно в поисках строки или столбца, описывающих данные. Это объективно неудобно и нерационально. Поэтому выберите в меню «Вид — Закрепить» и настройте строки и столбцы, которые не будут пролистываться.
  2. Настройте выравнивание данных. Тексты стоит выравнивать по левому краю, ведь читаем мы слева направо. Числа же, наоборот, логичнее выстроить по правому краю, чтобы не сбивалась разрядность. К слову, разрядность устанавливается кнопками в меню редактирования. 156147895728_kiss_10kb.png
  3. Удалите лишние ячейки. Зачем нагружать себя «белым шумом» пустых клеток, когда можно просто уменьшить таблицу? Законченная таблица, с четкими границами даст пользователю понимание, что вся нужна информация внутри этих границ и искать данные где-то ниже или правее бесполезно. Используйте аддон Crop Sheet, чтобы удалить пустые ячейки.
  1. Выделяйте данные цветом. Так вы сможете лучше ориентироваться в документе. При этом используйте светлые тона, на их фоне лучше видны надписи.

4. Валидация

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

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

Можно запретить любые данные — хоть даты в неправильном формате, хоть текст без буквы «а». При проверке уже заполненных ячеек, валидация указывает на ошибки. Позже эта функция, в зависимости от настройки, либо блокирует ввод некорректных данных, либо предупреждает об ошибке.

5. Предсказания

В электронных таблицах многое строится на подсчете, формулах и функциях. Вы наверняка знакомы с такими формулами как SUM, AVERAGE или ROUND, поэтому сегодня мы расскажем нечто более захватывающее.

Функция FORECAST, или ПРЕДСКАЗ в русской локализации, выстраивает прогноз того, как будут заполнены последующие клетки, исходя из заполнения предыдущих. Машинный подсчет будет несколько груб, но он может обрисовать тенденцию.

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

156148191463_kiss_6kb.png

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

ПРЕДСКАЗ — это сама формула;

A8 — это показатель, для которого будет рассчитываться результат формулы;

$B$2:$B$8 — это диапазон известных данных, на основе которых Google Spreadsheets составляет прогноз;

$A$2:$A$8 — это независимые данные, в нашем случае — месяца.

Знак $ нужен, чтобы формула не «сползла» в случае переноса.

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

6. Диаграммы

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

b_5d1cab5089f3a.jpg

  • столбчатые;
  • линейчатые;
  • круговые;
  • точечные;
  • географические;
  • графики;
  • прочие.

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

Чтобы построить диаграмму, выбираем в меню «Вставка — Диаграмма». Поверх таблицы появится активное поле элемента, а в правой части экрана откроется меню «Редактор диаграмм». Выберите внешний вид графика, в поле «Диапазон данных» отметьте цифры, которые будут отражаться в виде графика, в нашем случае — столбец с количеством посетителей. В поле диаграммы появляется синяя линия, иллюстрирующая разницу между показателями. Значение оси Y автоматически подтянулось, исходя из разряда чисел. В поле «Ось X» выбираем столбец с номерами месяцев, после чего на диаграмме появляется сетка координат и можно сориентироваться, какому месяцу соответствует каждый показатель.

7. Чек-листы

В Google Spreadsheets есть возможность превратить ячейку в чекбокс, благодаря чему таблица становится еще и чек-листом. Для этого выбираем в меню «Вставка — Флажок». Например, при подготовке выставочного стенда, чекбоксы можно добавить прямо в смету и мониторить готовность к мероприятию в одном документе. Чекбоксы пригодятся и для мониторинга эффективности команды: сотрудники могут записывать недельный план в общую таблицу и отмечать задачи по мере выполнения. А подсчитать продуктивность поможет формула:

ЕСЛИОШИБКА — сама формула.

СЧЁТЕСЛИ — формула подсчета выполненных задач

B1:B5 — ячейки, в которых находятся чекбоксы

«TRUE» — уточнение, что чекбокс должен быть отмечен

СЧЁТЗ — формула подсчета поставленных задач

A1:A5 — ячейки, в которые должны быть вписаны задачи

В итоге таблица сама посчитает, насколько сотрудник выполнил план

156148832170_kiss_11kb.png

8. Работа с текстом

  • GOOGLETRANSLATE(текст; язык_оригинала; язык_перевода) — Переводит текст с одного языка на другой.
  • PROPER(текст) — преобразует первые буквы слов в заглавные.
  • LOWER(текст) — преобразует буквы заданной ячейки в нижний регистр.
  • UPPER(текст) — преобразует буквы заданной ячейки в верхний регистр.
  • ISMAIL(текст) — проверяет, является ли указанный текст адресом электронной почты.
  • TRIM (текст) — проверяет на наличие двойных пробелов и удаляет лишние.
  • LEN ([ячейка_с_текстом]) — считает длину текста в знаках с пробелами.

9. Волшебная ARRAYFORMULA

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

b_5d1cab5c9c45c.jpg

Используйте эти хаки и вы полюбите Google Spreadsheets так же, как любим их мы 🙂

Талмуд по формулам в Google SpreadSheet

Обычно мы пишем про хостинги, в частности про зарубежный shared хостинг в США. Но чтобы писать, нужно иметь аналитические данные под рукой. Вот как раз тут требуется помощь Google Docs, если файл получится предположительно меньше 400 000 строк.

За несколько месяцев работы с таблицами Google пришлось много раз анализировать посредством формул разного рода данные. Как и ожидалось — то, что можно было решить в MS Excel, можно реализовать и в Google таблицах. Но многочисленные попытки решить проблемы с помощью любимого поисковика приводили только к новым вопросам и почти к нулевым ответам.
Посему, было решено облегчить жизни другим и прославить себя.

Кратко о главном

  • буквенно — цифровое (БУКВА = СТОЛБЕЦ; ЦИФРА = СТРОКА) например «А1».
  • стилем R1C1, в системе R1C1 и строки и столбцы обозначаются цифрами.

ok
Рисунок 2
Как видно из Рисунка 3, значения ячеек идут относительно той ячейки, в которой будет написана формула со знаком равно. Для сохранения эстетичного вида формул, в них прописаны символы [0], которые можно и не писать: R[0]C[1] = RC[1].

ok
Рисунок 3
Отличие Рисунка 2 от Рисунка 3 в том, что Рисунок 3 — это универсальная формулировка, не привязанная к строкам и столбцам (смотрите на значения строк и столбцов), чего не скажешь о рисунке 2. Но стиль RC в spreadsheet, в основном, используется для написания скриптов javascript.

Типы ссылок (типы адресации)

  • Относительные ссылки (пример, A1);
  • Абсолютные ссылки (пример, $A$1);
  • Смешанные ссылки (пример, $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Относительные ссылки

Относительная ссылка «запоминает», на каком расстоянии (в строках и столбцах) вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили » https://habrastorage.org/r/w1560/getpro/habr/post_images/419/55a/02d/41955a02d1e46ecf4854c5cd1711bb29.png» alt=»ok» data-src=»https://habrastorage.org/getpro/habr/post_images/419/55a/02d/41955a02d1e46ecf4854c5cd1711bb29.png»/>
Рисунок 4
Упростим пример, применив знак $ (Рисунок 5).

ok
Рисунок 5
Но не всегда нужно закреплять все столбцы и строки, иногда используется закрепление только строки или только столбца.(Рисунок 6)

ok
Рисунок 6
Обо всех формулах можно почитать на официальном сайте support.google.com
Важно: Данные, которые необходимо обрабатывать в формулах, не должны находиться в разных документах, это возможно делать только при помощи скриптов.

Ошибки формул

Если вы неправильно напишете формулу, об этом вас известит комментарий о синтаксической ошибке в формуле (Рисунок 7).

ok
Рисунок 7
Хотя ошибки могут быть не только синтаксические, но и, например, математические, такие как деление на 0 (Рисунок 7) и другие (Рисунок 7.1, 7.2, 7.3). Для того чтобы увидеть примечание, в котором показана какая ошибка произошла, наведите курсор на красный треугольник в правом верхнем углу ошибки.

ok
Рисунок 7.1
ok
Рисунок 7.2
ok
Рисунок 7.3
Для удобства восприятия таблицы все ячейки с формулами будем окрашивать в фиолетовый цвет.
Для того чтобы увидеть формулы «в живую» необходимо нажать горячую клавишу Ctrl + или выбрать в меню сверху Вид (Просмотр) > Все формулы. (Рисунок 8).

ok
Рисунок 8

О том, как пишутся формулы

В формулировке формул в справочнике и в формулах, которые используются для работы на данный момент, присутствуют отличия. Они заключаются в том, что вместо «запятой», которая использовалась раньше во многих формулах, уже используется «точка с запятой» (изменения произошли более полугода назад).
Для того чтобы посмотреть, на что ссылается формула на данной странице (Рисунок 9), необходимо щелкнуть мышкой в строке формул справа от надписи Fx (Fx находится под основным меню, слева).

ok
Рисунок 9
ВАЖНО: Для правильного функционирования формул, они должны быть написаны ЛАТИНСКИМИ буквами. Русская (кириллическая) “А” или “С” и латинская “А” или “С” для формулы — это 2 разные буквы.

Формулы

Арифметические формулы.

Сложение, вычитание, умножение, деление.

  • Описание: формулы сложения, вычитания, умножения и деления.
  • Вид формулы: “Ячейка_1+Ячейка_2”, “Ячейка_1-Ячейка_2”, “Ячейка_1*Ячейка_2”, “Ячейка_1/Ячейка_2”
  • Сама формула: =E22+F22, =E23-F23, =E24*F24, =E25/F25.

ok
Рисунок 10

Прогрессия.

  • Описание: формула для увеличения всех последующих ячеек на единицу (нумерация строк и столбцов).
  • Вид формулы: =Предыдущая ячейка + 1.
  • Сама формула: =D26+1

ok

Рисунок 11

Округление.

  • Описание: формула для округления числа в ячейке.
  • Вид формулы: =ROUND(ячейка с числом); счетчик (сколько цифр надо округлить после запятой).
  • Сама формула: =ROUND(E28;2).

ok
Рисунок 12
Округление “ROUND” происходит по математическим законам, если после запятой стоит цифра 5 или больше, то целая часть увеличивается на единицу, если 4 и меньше, то остается неизменной, также округление можно сделать с помощью меню ФОРМАТ — > Числа -> «1000,12» 2 десятичных знака (Рисунок 13). Если же вам необходимо большее количество знаков, то нужно нажать ФОРМАТ — > Числа -> Персонализированные десятичные -> И указать количество знаков.

ok
Рисунок 13

Сумма, если ячейки идут не последовательно.

  • Описание: суммирование чисел, которые находятся в разных ячейках.
  • Вид формулы: =SUM(число_1; число_2;… число_30).
  • Сама формула: «=SUM(E30;H30)» пишем через «;» если разные ячейки.
  • Описание: суммирование чисел, которые идут друг за другом (последовательно).
  • Вид формулы: =SUM(число_1: число_N).
  • Сама формула: =SUM (E31:H31)» пишем через «:» если это непрерывный диапазон.
  • Имеем начальные данные в диапазоне ячеек E31:H31, а результат в ячейке D31 (Рисунок15).

Среднее арифметическое.

  • Описание: суммируется диапазон чисел и делится на количество ячеек в диапазоне.
  • Вид формулы: =AVERAGE (ячейка с числом либо число_1; ячейка с числом либо число_2;… ячейка с числом либо число_30).
  • Сама формула: =AVERAGE(E32:H32)

ok
Рисунок 16
Конечно, есть и другие, но мы идем дальше.

Текстовые формулы.

Склеивание текстовых значений (формулой).

  • Описание: «склеивание» текстовых значений (вариант А).
  • Вид формулы: =CONCATENATE(ячейка с числом/текстом либо текст_1; ячейка с числом/текстом либо текст_2; …, ячейка с числом/текстом либо текст_30).
  • Сама формула: =CONCATENATE(E36;F36;G36;H36).

ok
Рисунок 17

Склеивание числовых значений.

  • Описание: “склеивание” текстовых значений руками, без использования специальных функций (вариант B — ручное написание формулы, сложность формулы любая.).
  • Вид формулы: =ячейка с числом/текстом 1&» «&ячейка с числом/текстом 2&» «&ячейка с числом/текстом 3&» «& ячейка с числом/текстом 4 (» » — пробел, знак & означает склеивание, все текстовые значения пишутся в кавычках “”).
  • Сама формула: =E37&» «&F37&» «&G37&» «&H37.

ok
Рисунок 18

Склеивание числовых и текстовых значений.

  • Описание:«склеивание» текстовых значений руками, без использования специальных функций (вариант С — смешанный тип, сложность формулы любая).
  • Вид формулы: = «текст_1 » &ячейка_1&«текст_2»&ячейка_2&«текст_3»&ячейка_3
  • Важно: весь текст, который будет написан в “” будет неизменным для формулы.
  • Сама формула: =«Еще 1 » &E38&» использования «&F38&» как НАМ «&G38.

ok
Рисунок 19

ЛОГИЧЕСКИЕ И ПРОЧИЕ

Перенос данных из любых листов одного и того же файла.

  • Описание: перенос данных из любых листов одного и того же файла (для Excel можно как переносить из листа одной книги в другой лист той же книги, так и из листа одной книги в лист другой книги).
  • Вид формулы: = «Название_Листа»! ячейка_1
  • Сама формула:=Data!A15 (Data — лист, А15 — ячейка на том листе).

ok
Рисунок 20
ok
Рисунок 20.1

Массив формул.

Суммирование ячеек с условием ЕСЛИ.

  • Описание: суммирование ячеек с условием ЕСЛИ (формула SUMIF).
  • Вид формулы: = SUMIF(‘Лист’! диапазон; критерии; ‘Лист’! суммарный_диапазон)

ok
Рисунок 21
Имеем начальные данные в листе Data (Рисунок 21), а результат на листе Formula в столбце D (Рисунок 22). В столбцах E, F, G показаны аргументы, применяемые в формуле, а в столбце H общий вид формулы, которая находится в столбце D и высчитывает результат.

ok
Рисунок 22
Пример выше показывает общий вид работы формулы “Сумма Если” с одним условием, но чаще всего используется “Сумма ЕСЛИ” (с множеством условий).

Суммирование ячеек ЕСЛИ, множество условий.

  • Описание: сумма ЕСЛИ (с множеством условий).
  • Вид формулы: = SUMIF(‘Data’! диапазон_1&‘Data’! диапазон_2; критерии_1&критерий_2; ‘Data’! суммарный_диапазон).
  • Сама формула:=(ARRAYFORMULA(SUMIF((Data!E:E&Data!F:F);(B53&C53);Data!G:G)))

ok
Рисунок 23
Допустим, что на листе Formula, в ячейке В53 (критерий_1 = Пиво) должно быть название напитка, а ячейка С53 (критерий_2 = 2), это количество друзей, которые принесут Пиво. В итоге в ячейке D53 окажется результат, что нам нужно докупить 15 бутылок пива. (Рисунок 23.1) то есть, формула определит сумму по двум критериям — пиво и количество друзей.

ok
Рисунок 23.1
Если таких позиций будет больше, строки 16 и 21(Рисунок 24), то количество пузырей в колонке G суммируется (Рисунок 24.1).

ok
Рисунок 24
Итого:

ok
Рисунок 24.1

Теперь приведем более интересный пример:

Ха… вечеринка продолжается, и вы вспоминаете, что нужен торт, но непростой, а супер – мега торт, с разными специями, которые, как назло, еще и зашифрованы под цифровые обозначения. Задача состоит в том, чтобы купить специи в нужном количестве пакетиков каждой из специи. Нужное количество повар зашифровал в таблицу (Рисунок. 25.1), столбцы A и B (в соседних столбцах делаем наши вычисления).
Каждая специя имеет свой порядковый номер: 1,2,3,4. (Рисунок 25).

  • Описание: подсчет количества одинаковых цифр в больших массивах при дополнительных условиях.
  • Вид формулы: СЧИТАТЬ ЕСЛИ(‘Formula’! диапазон_A55: А61+’Formula’! диапазон_B55:B61; УсловиеА”Специи”+УсловиеБ”число от 1 до 4”; Лист”Formula’! диапазон_B55:B61)/УсловиеБ ”число от 1 до 4”)
  • Сама формула: =((ARRAYFORMULA(SUMIF(‘Formula’!$A$55:$A$61&’Formula’!$B$55:$B$61; $F$55&$E59;’Formula’!$B$55:$B$61)))/$E59)
  • Описание: вычисление процента специй.
  • Вид формулы: Количество*100%/Общее_количество
  • Сама формула: =F58*$G$56/F$56

Подсчет значений в объединенных ячеек.

  • Описание: формула для подсчета значений, в которых присутствует символ @.
  • Вид формулы: СЧИТАТЬ ЕСЛИ(В столбце F листа “Formula” есть текст с содержимым @).
  • Сама формула: =COUNTIF(‘Formula’!F65:F68; «*@*»).

Подсчитывает количество чисел в списке аргументов.

  • Описание: подсчет количества ячеек, содержащих цифры без текстовых переменных.
  • Вид формулы: COUNT(значение_1; значение_2; … значение_30)
  • Сама формула: =COUNT(E45;F45;G45;H45)

ok
Рисунок 27.
Ячейки, содержащие текст и цифры также не считаются.

ok
Рисунок 27.1.

Подсчет количества ячеек содержащих цифры с текстовыми переменными.

  • Описание: подсчет количества ячеек, содержащих цифры с текстовыми переменными.
  • Вид формулы: COUNTA(значение_1; значение_2; … значение_30)
  • Сама формула: =COUNTA(E46:H46)

ok
Рисунок 28.
Также, формула считает ячейки, содержащие только знаки препинания, табуляции, но не считает пустые ячейки.

ok
Рисунок 28.1

Подстановка значений при условиях.

  • Описание: подстановка значений при условиях.
  • Вид формулы: «=IF(AND((Условие1);(Условие2)); Результат равен 0, если условие 1 и 2 выполняется; если не выполняется, то результат равен 1)»
  • Сама формула: «=IF(AND((F73=5);(H73=5));0;1)»
  • Вид формулы:»=COUNTA(Диапазон_А)-COUNTIF(Диапазон_А; «автоответ»)-COUNTIF(Диапазон_А; «-«)-COUNTIF(Диапазон_А; «занято»)»
  • Сама формула: =COUNTA($E74:$H75)-COUNTIF($E74:$H75; «автоответ»)-COUNTIF($E74:$H75; «-«)-COUNTIF($E74:$H75; «занято»)

ok
Рисунок 30
Вот мы и подошли к концу нашего маленького ликбеза по формулам в Google SpreadSheet и у меня большие надежды, что я пролил свет на некоторые аспекты аналитической работы с формулами.
Формулы, честно говоря, были в прямом смысле выстраданы. Каждая из них создавалась в течение долгого времени. Надеюсь, вам понравилась моя статья и примеры, приведенные в ней.
И в завершение, в качестве подарка. И да простят меня разработчики!

Формула «УБИЙЦА ДОКУМЕНТА».

Если Вам необходимо скрыть документ от чужих глаз навсегда, то эта формула для Вас.
Сама формула:»=(ARRAYFORMULA(SUMIF($A:$A&$C:$C;$H:$H&F$2; $C:$C)))». $H:$H регулирует распространение формулы. После того как фомлулу запустите (Рисунок 31), ниже в ячейках она начнет размножать следующую функцию CONTINUE(ячейка; строка; столбец).

ok
Рисунок 31
Формула циклически добавляет в весь столбец формулы. Для того чтобы убить документ нужно немножко постараться, создать N-ое количество ячеек и прописать формулу в первых ячейках N-го количества столбцов. Все! Документ больше ни кто исправить и проверить не сможет!
Вот что говорит страница помощи гугла о загруженности и ограничениях — http://support.google.com/drive/bin/answer.py?hl=ru&p=spreadsheets_timeout&answer=2505921
Обещанный документ «Талмуд» по формулам в Google SpreadSheet шел как основа.

Бесплатное дополнение "Калькулятор" для Google Docs

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

Это совершенно простой калькулятор, который поможет вам справиться с операциями сложения, вычитания, деления и умножения. Если вам понадобился калькулятор при работе в конкретном документе Google Docs, это дополнение точно поможет вам.

После установки дополнения будет доступно в боковой панели.

calculator-add-on-for-Google-Docs1

Выше на рисунке вы видите калькулятор в боковой панели.

Установка дополнения

Перейдите на домашнюю страницу этого Google Docs дополнения. Установите его, затем он попросит вашего разрешения для запуска.

Чтобы воспользоваться калькулятором при редактировании документа, перейдите к Дополнения → Калькулятор → Start.

calc

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

Конечно, хочется, чтоб калькулятор был более функциональным, чтобы можно было выполнять научные и многие другие сложные вычисления. Тем не менее, это лучше, чем ничего, и это дополнение делает Google Docs более удобным.

Как добавить и использовать вычисляемые поля в сводных таблицах Google Таблиц

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

Для повышения универсальности сводные таблицы также имеют функцию «Расчетное поле», которая позволяет дополнительно настраивать результаты с помощью функций и формул.

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

Что такое вычисляемые поля в Google Таблицах?

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

К ним относятся большинство стандартных сводных показателей, таких как среднее значение, медиана, дисперсия и т. Д. Однако часто необходимо выполнить определенные вычисления, которые могут быть недоступны во встроенных параметрах.

Здесь на помощь приходят вычисляемые поля. Вычисляемые поля позволяют обрабатывать данные для предоставления более настраиваемых результатов в сводной таблице.

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

Однако что, если вы хотите, чтобы это происходило только в сводной таблице, а исходные данные оставить нетронутыми?

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

Как использовать вычисляемые поля в сводных таблицах в Google Таблицах

Допустим, у вас есть следующий набор данных:

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

  • Общий объем продаж различных товаров.
  • Сумма, полученная после добавления 5% к общей сумме продаж для каждого продукта.
  • Минимальное количество проданных единиц для каждого предмета.

Для этого нужно двигаться шаг за шагом. Это означает, что вам необходимо:

  • Создайте сводную таблицу, в которой будет отображаться общая сумма продаж для каждого продукта.
  • Добавьте вычисляемое поле, в котором будут отображаться результаты настраиваемой формулы после добавления 5% НДС к общей стоимости.
  • Добавьте вычисляемое поле, в котором будет отображаться настроенная формула после нахождения минимального количества проданных единиц для каждого продукта.

Давайте пройдемся по этим шагам один за другим.

Создание сводной таблицы для отображения общей суммы продаж для каждого продукта

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

  • Щелкните меню «Данные» на ленте меню.
  • В появившемся раскрывающемся меню выберите параметр «Сводная таблица».
  • Теперь вы должны увидеть окно с вопросом, хотите ли вы вставить сводную таблицу на существующий лист или на новый лист. Выберите тот вариант, который вам больше нравится. Для наглядности всегда лучше создавать его на новом листе.
  • Щелкните по кнопке Create.
  • Это должно создать вашу сводную таблицу либо на том же листе, либо на новом листе, в зависимости от того, что вы выбрали на шаге 3.
  • Ваша сводная таблица на этом этапе должна выглядеть как на снимке экрана, показанном ниже:
  • Должна быть сетка, отображающая «Строки», «Столбцы» и «Значения».
  • Теперь вы можете начать заполнять сводную таблицу необходимыми данными. В правой части окна вы должны увидеть редактор сводной таблицы. Это поможет вам указать, что должно быть в вашей сводной таблице.
  • Теперь мы хотим, чтобы в нашей сводной таблице было два столбца (изначально) — Товар и общая продажная цена. Итак, в категории «Строки» нажмите «Добавить».
  • В появившемся раскрывающемся списке выберите элемент. Это добавит каждое уникальное имя элемента в отдельные строки вашей сводной таблицы.
  • Затем мы хотим увидеть общую сумму продаж для каждого товара. Итак, в категории «Значения» нажмите «Добавить».
  • В появившемся раскрывающемся списке выберите «SalesPrice». Это отобразит сумму всех продажных цен для каждого товара.

Здесь отображается общая сумма продаж по каждому продукту, как показано ниже:

А что, если вы также хотите увидеть, что произойдет, если вы добавите 5% НДС к общей сумме продаж каждого продукта?

В категории «Значения», если вы щелкните раскрывающийся список под «Суммировать по», вы заметите, что нет опции для добавления 5%.

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

Добавление вычисляемого поля, суммированного с помощью SUM

Теперь вы хотите добавить 5% к общей сумме продаж каждого товара и отобразить ее в новом столбце. Поскольку расчет должен выполняться для общей суммы продаж (SUM значений SellingPrice для каждого элемента), ваше вычисленное поле необходимо суммировать с помощью SUM.

Вот шаги, которые необходимо выполнить, если вы хотите добавить 5% НДС к общей сумме продаж для каждого продукта:

  • Щелкните любую ячейку сводной таблицы.
  • В категории «Значения» нажмите «Добавить».
  • В появившемся раскрывающемся списке выберите параметр «Расчетное поле».
  • Теперь вы увидите новый столбец в сводной таблице с надписью «Расчетное поле».
  • Вы можете изменить это имя прямо в сводной таблице. Назовем его «Сумма после НДС».
  • Вы также должны увидеть некоторые параметры для вычисляемого поля в редакторе сводной таблицы.
  • В поле ввода в разделе Формула вы можете ввести формулу, которая будет использоваться для результатов вычисляемого поля.
  • Поскольку вы хотите отобразить сумму, полученную после добавления 5% к общей сумме продаж, введите формулу: = SalesPrice + ((5/100) * SalesPrice). Обратите внимание, что переменная SalesPrice здесь относится к столбцу SalesPrice в исходном наборе данных.
  • Теперь это должно отображать результаты нашей настраиваемой формулы в новом созданном вычисляемом поле.

Примечание. Поскольку мы хотели добавить сумму НДС к общему объему продаж для каждого продукта, мы оставили в поле «Суммировать по» значение по умолчанию «СУММ». Если вы щелкните раскрывающийся список под «Суммировать по», вы заметите, что у вас есть только два варианта: «СУММ» и «Пользовательский».

Если вы хотите отобразить минимальное количество проданных единиц для каждого предмета, вам нужно будет использовать отдельные значения «Единицы» из исходного набора данных в вашей настраиваемой формуле вместо СУММ. Мы увидим, как это сделать, в следующем разделе.

Добавление вычисляемого поля, обозначенного как Custom

Теперь мы хотим найти минимальное количество проданных единиц для каждого продукта. Обратите внимание, что мы хотим использовать для каждого продукта отдельные единицы, проданные в определенный день, а не СУММУ проданных единиц. Это означает, что наше вычисляемое поле не может быть суммировано с помощью SUM. Существует еще один вариант «Суммировать по», и это вариант «Пользовательский».

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

  • Щелкните любую ячейку сводной таблицы.
  • В категории «Значения» нажмите «Добавить».
  • В появившемся раскрывающемся списке выберите параметр «Расчетное поле».
  • Теперь вы увидите новый столбец в сводной таблице с надписью «Расчетное поле 2». Вы можете изменить это имя прямо в сводной таблице. Давайте переименуем его в «Минимальное количество проданных единиц».
  • Вы также должны увидеть некоторые параметры для вычисляемого поля в редакторе сводной таблицы.
  • В поле ввода в разделе Формула вы можете ввести формулу, которая будет использоваться для результатов вычисляемого поля.
  • Поскольку вы хотите отобразить минимальное количество проданных единиц, введите формулу: = MIN (единиц). Обратите внимание, что переменная «Единицы» здесь относится к столбцу «Единицы» в исходном наборе данных.
  • Щелкните раскрывающийся список под «Суммировать по» и выберите «Пользовательский».
  • Теперь это должно отображать результаты нашей настраиваемой формулы в новом созданном вычисляемом поле.

Примечание. Поскольку мы хотели найти минимальное количество проданных единиц для каждого продукта, мы изменили поле «Суммировать по» на «Пользовательский» вместо SUM.

Важные моменты о вычисляемых полях

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

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

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

Как видите, вычисляемые поля помогают сделать ваши сводные таблицы более мощными, поскольку они позволяют настраивать сводки и результаты по своему вкусу. Мы надеемся, что вам понравился этот урок и он оказался полезным.

Добавить комментарий

Ваш адрес email не будет опубликован.