Меню

Функция умножения google таблицы



Булева алгебра и логические выражения в гугл таблицах (GS12)

Привет, дорогие чатлане!
Сегодня рассмотрим логические выражения, булеву алгебру, функции if(), and(), or().
Сначала короткий экскурс в математическую подложку (пара слов про булеву алгебру и таблицы истинности).
Логическое выражение — конструкция, которая на входе имеет ряд аргументов и операций между ними, а на выходе — 0 или 1, т.е. ЛОЖЬ и ИСТИНА.
Пример логического выражения:
1+1 = 2 == 1 (Истина)
1+1 = 3 == 0 (Ложь)
[1+1 = 2] или [1+1 = 3] == 1 (Истина) — т.к. одно из выражений (первое) — является истиной, то вся конструкция является Истиной.
[1+1 = 2] и [1+1 = 3] == 0 (Ложь) — т.к. по крайней мере одно выражение (второе) — является ложным, то вся конструкция является ложной.

По своей сути оператор ИЛИ очень похож по своему поведению на сложение, а оператор И — на умножение.
Таблица истинности — это конструкция, которая позволяет определить различные исходы логического выражения для различных значений аргументов. Например у нас есть логическое выражение: a или b, при этом и a и b могут быть и истиной и ложью. Тогда таблица истинности будет выглядеть следующим образом:

Для выражения a и b — следующим образом:

Как это нам поможет в гугл таблицах?
Простой пример — нужно найти все отрицательные записи в реестре:

Функция IF проверяет условие. Далее, если оно истинно — срабатывает первая часть формулы, если нет — вторая. Чуть усложним, добавим проверку даты. Предположим нас интересуют только записи позже 1.01.2020:

И здесь мы видим как формула отрабатывает неправильно. 1/06/2020 явно больше чем 1/01/2020. Здесь вступает в силу нюанс с форматами. Если мы возьмем дату и отформатируем ее как число, то увидим следующее:

Источник

Текстовые функции Google Таблиц

В этой заметке описаны основные текстовые функции Google Таблиц. На скриншоте — четыре столбца:

  1. Название функции;
  2. Исходный текст;
  3. Текст после применения функции (ячейка с формулой);
  4. Текст самой формулы.

Text functions

Ниже — описание этих функций.

В скобках указаны названия функций-аналогов из Excel.

TRIM (СЖПРОБЕЛЫ) удаляет лишние пробелы между словами, а также до первого и после последнего слова в тексте. Остается лишь по одному пробелу между словами. Единственный аргумент — ячейка с текстом (теоретически можно и текст в кавычках указать прямо в самой формуле, но на практике вряд ли такое понадобится)

UPPER (ПРОПИСН) и LOWER (СТРОЧН) меняют регистр текста на верхний и нижний соответственно. У них тоже один аргумент.

Функция LEN (ДЛСТР) определяет длину текста. Учитываются все символы, включая пробелы. Ее можно использовать как формулу массива — см. строку 7 в примере. В таком случае удастся посчитать сумму длин текстов из целого диапазона.

Функция FIND (в Excel 2 аналога — НАЙТИ учитывает регистр, ПОИСК — нет) определяет позицию вхождения слова или символа в текст.
Первый аргумент — текст, который мы ищем, второй — ячейка с текстом, в котором будем искать. Есть третий — необязательный — аргумент: позиция начала поиска. Искать можно не с начала. В примере слово кефир в исходном тексте стоит на 26 позиции.

Функция SUBSTITUTE (ЗАМЕНИТЬ) меняет в тексте одно слово (символ, текст) на другое.
Например, функция ниже:
=SUBSTITUTE(«пробежал я давеча марафон за 3:15″;»3:15»;»2:55″)
выдаст такой приятный результат:

пробежал я давеча марафон за 2:55

Функция TEXT (ТЕКСТ) нужна в тех случаях, когда вы с помощью формул составляете текстовую фразу, в которой используются числовые значения или даты из ячеек вашей таблицы.
В примере:
=»сегодня «&B13
выдает дату в неотформатированном виде, т.е. как число:

А функция TEXT позволяет получить красивый результат:
=»сегодня «&TEXT(B14;»DD/MM/YYYY»)

Функции LEFT (ЛЕВСИМВ) И RIGHT (ПРАВСИМВ) вырезают из текста (первый аргумент) определенное (второй аргумент) количество знаков.
=ЛЕВСИМВ(«Слово»;3) = «Сло»

Функция MID (ПСТР) вырезает из текста символы, начиная с определенной позиции (второй аргумент):
=MID(«Машина»;3;2) = «ши»

Функция SPLIT (Аналог в Excel — инструмент «Текст по столбцам», вкладка «Данные» на ленте)
разделяет текст (первый аргумент) по определенному разделителю (второй аргумент).
Разделитель — ибо это текст — нужно указать в кавычках (в примере это пробел):
=SPLIT(B21;» «)

Источник

Функция умножения google таблицы

Данная шпаргалка создана на основе материалов:

Возможные сложности в работе

  1. Попробовал сделать себе базу данных, которая содержит 240 тысяч строк и 10 столбцов с данными — хром завис и закрылся. Скорее всего, всё зависит от мощности компа, но нужно учесть, что большие объемы хром может и не потянуть.
  2. При обработке большого количества данных на разных вкладках — query может долго прогружаться. Скорость зависит от интернета и мощности компа.

Синтаксис QUERY

Данные — диапазон ячеек, для которого нужно выполнить запрос.
Запрос — запрос на выполнение, записанный на языке запросов API визуализации Google (упрощенный вариант SQL-запросов).

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

Заголовки (необязательный параметр) — указываем сколько первых строк будет считаться заголовками. В случае, если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержимого данных.

Примеры запросов

На примере выше мы выбираем данные из указанного диапазона и в запросе указываем, что хотим получить все столбцы. «Limit» означает, что будет выведено всего 10 первых строк.

Читайте также:  ПРИГОТОВЛЕНИЕ РАСТВОРОВ ЗАДАННОЙ КОНЦЕНТРАЦИИ

В данной формуле указывается диапазон, откуда берём данные. В запросе мы пишем, что хотим получить (под запросом имеется в виду то, что идёт в двойных кавычках). Если поочередно, то в запросе указывается следующее:

  1. Выбрать столбец A.
  2. Суммировать данные из столбца D.
  3. Берём только данные, которые содержат «YRSY» или «YSEA» в столбце B.
  4. Группировать по столбцу A.

Если в последнюю формулу не добавить группировку, то вылетит ошибка.
Ошибка

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

Выбираем данные с помощью кляузы SELECT

Для того, чтобы выбрать данные с которыми мы хотим работать — мы используем ключевое слово (кляузу) select.

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

В первой части формулы мы выбираем диапазон с данными.
Выбираем диапазон с данными из БД

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

Если в файле для тренировки вы напишите формулу, то будут выведен список всех РК.

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

Пример вывода данных с помощью query

Таким образом выглядит часть данных, которые находятся в файле для тестов.

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

Фильтруем данные с помощью WHERE

С помощью where можно фильтровать данные, которые содержит определенный столбец.

В данном случае, с помощью where мы фильтруем кампании, которые содержат «YRSY» или «YSEA».

Кляуза WHERE поддерживает логические операторы OR и AND и определенные ключевые слова с помощью которых можно фильтроваться.

Операторы =, и != можно применять в том числе для строк. Но рекомендую использовать только = и !=, так как в случае с операторами «больше” или «меньше» — поведение не очень предсказуемое.

Оператор Описание
= Равно
Больше
!= Не равно
AND Логическое «И». Т.е. все условия через И должны быть соблюдены.
OR Логическое «ИЛИ». Должно быть выполнено одно из условий.
NOT Логическое отрицание. Например, «not contains» в where будет означать «не содержит»
contains Проверяет содержание определённых символов в строке. Например, WHERE A contains ‘John’ вернёт в фильтр все значения из столбца A, в которых встречается John, например, John Adams, Long John Silver.
starts with Фильтрует значения по префиксу, то есть проверяет символы в начале строки. Например, starts with ‘en’ вернёт значения engineering и english.
ends with Фильтрует значения по окончанию строки. Например, строка ‘cowboy’ будет возвращена конструкцией «ends with ‘boy’» или «ends with ‘y’»
matches Соответствует регулярному выражению. Например: where matches ‘.*ia’ вернёт значения India и Nigeria.
like Упрощённая версия регулярных выражений, проверяет соответствия строки заданному выражению с использованиям символов подстановки. На данный момент like поддерживает два символа подстановки: «%» означает любое количество любых символов в строке, и «_» — означает один любой символ. Например, «where name like ‘fre%’» будет соответствовать строкам ‘fre’, ‘fred’, и ‘freddy’.

Сортируем с помощью ORDER BY

Для того, чтобы отсортировать данные по убыванию или возрастанию, то нужно добавить кляузу order by.

По умолчанию — порядок asc, то есть по возрастанию. Если укажете после названия поля параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.

Вот так мы выводим 5 самых конверсионных кампаний за весь период:

Результат выглядит вот так:
Сортировка данных

Агрегирующие функции, группировка данных и переименование столбцов (Group by, Label)

Агрегирующие функции

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

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

Функция Описание Поддерживаемый тип данных Возвращаемый тип данных
avg() Возвращает среднее значение для группы Числовой Числовой
count() Возвращает количество значений в группе Любой Числовой
max() Возвращает максимальное значение для группы Любой Аналогичный полю, к которому применяется
min() Возвращает минимальное значение для группы Любой Аналогичный полю, к которому применяется
sum() Возвращает сумму значений в группе Числовой Числовой

Группировка данных с помощью GROUP BY

В случаях, когда используются агрегирующие функции, то указание group by обязательно. Иначе гугл выдаст ошибку.

Обычно в ошибке указывается, что пошло не так. Если добавим группировку по кампаниям, то всё будет ок.

Переименование заголовков с помощью LABEL

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

Мы это можем сделать с помощью следующей формулы:

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

Оператор Описание
+ Сложение
Вычетание
/ Деление
* Умножение

В результате работы формулы мы получим следующий результат (количество данных на скрине сократил, чтобы лучше было видно).

В случае со столбцом B, query взял в качестве заголовка 1 строку из нашей базы данных, где было указано «Название размещения». По остальным столбцам в заголовках добавилось указание первой строки из БД + название арифметической операции, которая использовалась для данного столбца.

Для того, чтобы это исправить и сделать более красиво, используется кляуза label.

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

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

Подобные перечисления разделяются между собой запятой. Если запятую пропустим, то query вернет ошибку.

Вот так теперь выглядят заголовки таблицы после использования label:

Форматирование с помощью кляузы Format

Форматировать данные можно с помощью двух способов:

  1. Привычные методы Гугл таблиц.
  2. С помощью кляузы Format.

На примере ниже видно, что в столбце, где мы рассчитали CTR, нужно данные форматировать в проценты:

И делаем мы это либо с помощью волшебной кнопки.

Либо меняем формулу из предыдущего примера на следующую:

В формуле появилась кляуза, которая приводит CTR в нужный нам вид:

Аналогичные операции можно сделать и для других столбцов.

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

Теперь выглядит всё еще лучше:

Единственное, что раздражает — если происходит ошибка деления на ноль (в случае с CPA), то остаются пустые ячейки.

Форматирование даты

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

Сделать мы это можем с помощью следующей формулы:

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

Неделя в данном случае начинается с воскресенья. Соответственно, у воскресенья порядковый номер = 1, а у субботы = 7.

Чтобы не запутать себя и окружающих, будет лучше отформатировать дни недели и привести их в привычный вид. Для этого в формулу нужно добавить кляузу format.

Теперь таблица выглядит понятнее:

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

Помимо функции dayofweek(), существуют и другие, которые мы можем применять в наших запросах.

Скалярные функции

Функция Описание
year() Возвращает номер года из «даты» или «даты и времени». Пример: year(date ‘2009-02-05’) вернет 2009. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
month() Возвращает номер месяца из «даты» или «даты и времени». Но в данном случае январь будет возвращать 0, февраль 1 и так далее. Началом отсчета для номера месяца является 0. Пример: month(date ‘2009-02-05’) вернет 1. Чтобы функция вернула номер месяца в привычном виде к ее результату прибавьте 1, month(date «2009-02-05»)+1 вернет 2. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число
day() Возвращает номер дня в месяце из «даты» или «даты и времени». Пример: day(date ‘2009-02-05’) вернет 5. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
hour() Возвращает номер часа в дне из «даты и времени» или «времени». Пример: hour(timeofday ‘12:03:17′) вернет 12. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
minute() Возвращает номер минуты в часе из «даты и времени» или «времени». Пример: minute(timeofday ‘12:03:17′) вернет 3. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
second() Возвращает номер секунды в минуте из «даты и времени» или «времени». Пример: second(timeofday ‘12:03:17′) вернет 17. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
millisecond() Возвращает номер миллисекунды в секунде из «даты и времени» или «времени». Пример: millisecond(timeofday ‘12:03:17.123′) вернет 123. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
quarter() Возвращает номер квартала в году из «даты и времени» или «времени». Базовым значением или началом отсчета является 1, соответственно, для первого квартала функция вернет значение 1, для второго 2 и так далее. Пример: quarter(date ‘2009-02-05’) вернет 1. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
dayOfWeek() Возвращает номер дня недели в неделе из «даты» или «даты и времени». Началом недели считается воскресенье, для воскресенья функция вернет значение 1, для понедельника 2 и так далее. Пример: dayOfWeek(date ‘2015-11-10’) вернет 3, так как 10 ноября 2015 года — вторник. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
now() Возвращает текущую дату и время в часовом поясе GTM. Запрашиваемые параметры: не требует ввода параметров. Тип возвращаемых данных: дата и время.
dateDiff() Возвращает разницу в днях между двумя датами. Пример: dateDiff(date ‘2008-03-13’ , date ‘2008-02-12’) вернет 29, так как 10 ноября 2015 года вторник. Запрашиваемые параметры: два параметра с типом «дата» или «дата и время». Тип возвращаемых данных: число.
toDate() Возвращает преобразованное в дату значение из «даты» или «даты и времени» или «числа». Пример: toDate(dateTime‘2013-03-13 11:19:22’) вернет дату ‘2013-03-13’. Запрашиваемые параметры: один параметр с типом дата, дата и время или число. Тип возвращаемых данных: дата.
upper() Преобразует все значения в строке в верхний регистр. Пример: upper( ‘foo’) вернет строку ‘FOO’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.
lower() Преобразует все значения в строке в нижний регистр. Пример: upper( ‘Bar’) вернет строку ‘bar’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.

Перекрестные таблицы (PIVOT)

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

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

Сделаем мы это с помощью следующего запроса:

В итоге, получается матрица, которую можно использовать для анализа:
Матрица с конверсиями по дням

Источник

Считают почасовой доход и хвалят за старательность: как гугл-таблицы облегчают мне жизнь

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

Екатерина Леснова любит гугл-таблицы Профиль автора

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

Вот что они умеют.

Планировать расходы

Что умеет таблица: вычислять сумму, которую мы с мужем можем потратить на «хотелки».

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

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

Вот что они значат.

Обязательно — это деньги, которые мы тратим на еду, жилье, детей, транспорт и здоровье. Раньше для учета расходов мы пользовались приложением «Дзен-мани», так что суммы взяли из его отчетов.

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

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

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

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

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

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

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

Как пользоваться. Записываем в таблицу общий доход за месяц и прикидываем траты. Затем настраиваем параметры во вспомогательной таблице:

  1. Определяем минимум для тех категорий, где это необходимо. Например, грустно остаться на работе без обеда — поэтому для этой обязательной категории я тоже создала пересчитываемую ячейку с минимумом 2000 Р.
  2. Записываем проценты от зарплаты, которые хотим откладывать на сберегательные счета.
  3. Указываем, как распределить свободные деньги. Например, по 35% от «лишних» денег уходит на личные траты мне и мужу, по 15% — на развлечения и обеды.

Источник

Как сделать формулы в гугл таблице?

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

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

Второй шаг. Чтобы посчитать умножение, поставим курсор в ячейку «С2», а дальше напишем простую формулу: =A2*B2, где A2 – первое число, а B2 – второе число. Ответ получим 10.

Третий шаг. Теперь посчитаем деление, для этого поставим курсор в ячейку «С3» и в ней напишем простую формулу: =A3/B3, где A3 – первое число, а B3 – второе число. Ответ получим 2.

Четвертый шаг. Выполним вычитание, для этого активируем ячейку «С4», где пишем формулу: =A4-B4, где A4 – первое число, а B4 – второе число. Ответ получим -2.

Пятый шаг. Осталось посчитать суммирование ячеек, для этого курсор ставим в ячейку «С5» и пишем формулу: =A5+B5, где A5 – первое число, а B5 – второе число. Ответ получим 10.

Источник