Меню

Как мне скрыть DIV 0 ошибка когда указанная ячейка пуста



Булева алгебра и логические выражения в гугл таблицах (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 в Гугл Таблицах

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

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

  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, здесь приведу пример того, как мы можем использовать эту кляузу.

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

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

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

Источник

Adblock
detector