Булева алгебра и логические выражения в гугл таблицах (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. Здесь вступает в силу нюанс с форматами. Если мы возьмем дату и отформатируем ее как число, то увидим следующее:
Каждая дата для ГТ это число. Поэтому в таблице выше мы сравниваем число с текстом. Чтобы этого избежать — мой совет — всегда выписывать даты с которыми идет сравнение, чтобы не вдаваться в детали с тем как именно нужно их записывать.
Источник
Как обойти ошибку IMPORTRANGE: «Результаты слишком велики»?
Я пытаюсь IMPORTRANGE из диапазона, содержащего 240 000 ячеек (40 столбцов и 6000 строк). Функция IMPORTRANGE ошибочна: «Результаты слишком велики». Я не могу найти документацию о ограничениях функции.
Каковы ограничения IMPORTRANGE?
Как мне обойти это, чтобы я мог импортировать эти данные в свой листок?
4 ответа
У меня тоже была аналогичная проблема.
Попробуйте разделить диапазон импорта с помощью формулы массива.
Протестируйте это с помощью разных размеров данных, чтобы получить самую короткую версию, и вы можете делать то, что вам нужно.
Пустые клетки могут иметь значение. Мы наблюдали нарушение импорта в ячейках 23573×11 или 259k, типичный рост составлял около 10 рядов ежедневно, поэтому мы некоторое время находились в ячейках более 250 тысяч. Один столбец в основном пустой, у пары других есть несколько пробелов.
Я не мог заставить ARRAYFORMULA разобрать, как показано выше, или с другими догадками, поэтому я использовал это на своей скрытой вкладке «Ingest».
=importrange(«sheet», «A1:K10000») в ячейке A1
=importrange(«sheet», «A10001:K») в ячейке A10001
В моей рабочей /презентационной вкладке используется
, чтобы обеспечить постоянное форматирование, наши исходные листы перезаписываются ежедневно.
Используя ответ Сэма и документацию для чтения, я нашел способ получить результат BIG DATA без ошибок. Для этого вам нужно сделать шаг за шагом. В одном запросе. Например, если вам нужно экспортировать данные sheet!A3:X100000 .
Попробуйте сделать следующее: сначала сделайте запрос и выберите только
после получения результата просто отредактируйте запрос из
после получения данных снова отредактировать запрос
и продолжайте, пока вы не будете богаты
таким образом я мог бы импортировать около 800 000 ячеек с данными. Для моей задачи этого было достаточно, но я думаю, что если мне нужны более длинные данные результата, я мог бы продолжить, и он будет работать.
Вы также должны помнить, что таблицы Google имеют ограничение на один максимум документа, может содержать только 2 миллиона ячеек.
Из моего опыта использования IMPORTRANGE количество ячеек не было причиной вообще, но в любое время, когда я превысил 36 столбцов, это не получилось. Мои результаты могут составлять 600 строк или 6000 строк, если я не превысил 36 столбцов. По иронии судьбы вы можете обойти это, объединив функции IMPORTRANGE.
Обратите внимание на фигурные скобки <>, используемые до и после двух функций IMPORTRANGE
Источник
Как мне скрыть # DIV / 0! ошибка, когда указанная ячейка пуста?
В колонке С у меня есть Производство. В столбце D у меня есть цель. В столбце E у меня дисперсия%. Моя формула =(D11-C11)/D11
Тем не менее, как вы прячете клетки вниз по листу, пока вы не положите что-то в D11 & Амп; C11 прятаться #DIV/0! , Я пытался использовать IF формула, но, кажется, не так?
Функция IFERROR
Есть «особый» IF Тест предназначен только для обработки ошибок:
Это дает вычисленное значение (D11-C11) / D11, если только результат не является ошибкой, и в этом случае он возвращает пустое значение.
объяснение
Значение «if error», последний параметр, может быть любым; это не ограничено пустыми двойными кавычками. IFERROR работает для любого условия, которое возвращает значение ошибки (вещи, которые начинаются с # ), лайк:
Это удобно для отладки; функция может быть временно обернута вокруг формулы для возврата некоторого текста сообщения, когда формула выдает ошибку. Это также упрощенная форма теста IF; для его проверки не требуется включать выражение, а затем снова использовать его, чтобы использовать его результат.
Другие таблицы
Эта функция также доступна пользователям других программ электронных таблиц. Он был добавлен в LibreOffice Calc в версии 4.0 (пока не распространяется в некоторых дистрибутивах Linux). Однако, как отмечает @Kroltan, он еще более упорядочен в Google Sheets, где значение «if error» является необязательным; по умолчанию он пуст, если отсутствует. Так что в таком случае, когда вы просто хотите скрыть потенциальные значения ошибок, Google Sheets может сделать это с IFERROR(expression) ,
Источник
Использование функции query в Гугл Таблицах
Данная шпаргалка создана на основе материалов:
Возможные сложности в работе
- Попробовал сделать себе базу данных, которая содержит 240 тысяч строк и 10 столбцов с данными — хром завис и закрылся. Скорее всего, всё зависит от мощности компа, но нужно учесть, что большие объемы хром может и не потянуть.
- При обработке большого количества данных на разных вкладках — query может долго прогружаться. Скорость зависит от интернета и мощности компа.
Синтаксис QUERY
Данные — диапазон ячеек, для которого нужно выполнить запрос.
Запрос — запрос на выполнение, записанный на языке запросов API визуализации Google (упрощенный вариант SQL-запросов).
Значение параметра запрос должно быть заключено в кавычки или представлять собой ссылку на ячейку, содержащую соответствующий текст.
Заголовки (необязательный параметр) — указываем сколько первых строк будет считаться заголовками. В случае, если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержимого данных.
Примеры запросов
На примере выше мы выбираем данные из указанного диапазона и в запросе указываем, что хотим получить все столбцы. «Limit» означает, что будет выведено всего 10 первых строк.
В данной формуле указывается диапазон, откуда берём данные. В запросе мы пишем, что хотим получить (под запросом имеется в виду то, что идёт в двойных кавычках). Если поочередно, то в запросе указывается следующее:
- Выбрать столбец A.
- Суммировать данные из столбца D.
- Берём только данные, которые содержат «YRSY» или «YSEA» в столбце B.
- Группировать по столбцу A.
Если в последнюю формулу не добавить группировку, то вылетит ошибка.
В большинстве случаев подобные «подсказки» дают нам представление о том, что пошло не так и где это нужно исправить.
Выбираем данные с помощью кляузы SELECT
Для того, чтобы выбрать данные с которыми мы хотим работать — мы используем ключевое слово (кляузу) select.
По ссылке можно скопировать базу данных для тестов. Данные в базе вымышлены и все возможные совпадения случайны.
В первой части формулы мы выбираем диапазон с данными.
Во второй части мы в кавычках уже начинаем писать запрос. Все запросы начинаются с кляузы select.
Если в файле для тренировки вы напишите формулу, то будут выведен список всех РК.
В кляузе select мы перечисляем столбцы, которые нужно выбрать и сделать с ними что-то — либо вывести всё, либо отфильтровать, либо суммировать, либо разделить и т.д.
Таким образом выглядит часть данных, которые находятся в файле для тестов.
Если мы применим запрос, который указан выше, то будут выведены все значения из столбца 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
Форматировать данные можно с помощью двух способов:
- Привычные методы Гугл таблиц.
- С помощью кляузы 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, здесь приведу пример того, как мы можем использовать эту кляузу.
Задача:
Нужно по кампаниям построить таблицу, где по дням будет видна динамика конверсий, чтобы вовремя отслеживать проблемы.
Сделаем мы это с помощью следующего запроса:
В итоге, получается матрица, которую можно использовать для анализа:
Источник