Если 0 то пустая ячейка Гугл таблицы

Нужно отфильтровать данные с помощью query в googlesheets. Не сумирует ячейки с значением пустыми значениями. Знает ли кто как решить данную пробему? Как вариант вбить во все пустые ячейки нули, но это не то решение которое ожидается. Можно ли как то решить проблему без нулей в пустых ячейках? Ссылка на таблицу Фомула

=query( ‘исходная’!A1:AK; «select sum(I)+sum(J)+sum(K) where (B=’Виктор’ AND month(C) = month(date ‘2010-02-01’) AND year(C) = year(date ‘2020-02-01’)) group by B label sum(I)+sum(J)+sum(K)»»; 0 )

• google-spreadsheet
• googlesheets

Отслеживать
3,510 2 2 золотых знака 15 15 серебряных знаков 41 41 бронзовый знак
задан 19 янв 2020 в 11:24
41 3 3 бронзовых знака

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

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

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

ФУНКЦИЯ ЕПУСТО + ЕСЛИ В EXCEL

Если использовать формулу QUERY, то результат будет неожиданным

=QUERY( ‘Данные’!A1:AK; «select B, sum(I)+sum(J)+sum(K) group by B label sum(I)+sum(J)+sum(K)’Сумма'»; 1 )

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

=QUERY( ARRAYFORMULA(<‘Данные’!A1:HIFERROR(VALUE(‘Данные’!I1:AK);0)>); «select Col2, sum(Col9)+sum(Col10)+sum(Col11) group by Col2 label sum(Col9)+sum(Col10)+sum(Col11)’Сумма'»; 1 )

Надеюсь, это то, что надо.

Источник: ru.stackoverflow.com

Отображение и скрытие нулевых значений

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

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

Проверяем ячейку на несколько условий (несколько ЕСЛИ) — Таблицы Google

Скрытие и отображение всех нулевых значений на листе

1. Выберите Файл >Параметры >Дополнительно.
2. В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
3. Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
4. Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.

Скрытие нулевых значений в выделенных ячейках

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

Отображение скрытых значений.

1. Выделите ячейки со скрытыми нулевыми значениями.
2. Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.
3. Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК.

Скрытие нулевых значений, возвращенных формулой

1. Выделите ячейку, содержащую нулевое (0) значение.
2. На вкладке Главная щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».
3. В левом поле введите 0.
4. В правом поле выберите Пользовательский формат.
5. В поле Формат ячейки откройте вкладку Шрифт.
6. В списке Цвет выберите белый цвет и нажмите кнопку ОК.

Отображение нулей в виде пробелов или тире

Для решения этой задачи воспользуйтесь функцией ЕСЛИ.

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

• =ЕСЛИ(A2-A3=0;»»;A2-A3)

Вот как читать формулу. Если результат вычисления (A2-A3) равен «0», ничего не отображается, в том числе и «0» (это указывается двойными кавычками «»). В противном случае отображается результат вычисления A2-A3. Если вам нужно не оставлять ячейки пустыми, но отображать не «0», а что-то другое, между двойными кавычками вставьте дефис «-» или другой символ.

Скрытие нулевых значений в отчете сводной таблицы

1. Выберите отчет сводной таблицы.
2. На вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.
3. Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
4. Изменение отображения ошибки В поле Формат установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
5. Изменение отображения пустой ячейки Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

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

Отображение и скрытие всех нулевых значений на листе

1. Выберите Файл >Параметры >Дополнительно.
2. В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
3. Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
4. Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.

Скрытие нулевых значений в выделенных ячейках с помощью числового формата

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

• Скрытые значения отображаются только в строке формул или в ячейке, если вы редактируете ее содержимое. Эти значения не печатаются.
• Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.

Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования

1. Выделите ячейку, содержащую нулевое (0) значение.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование, наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно.
3. В левом поле введите 0.
4. В правом поле выберите Пользовательский формат.
5. В диалоговом окне Формат ячеек откройте вкладку Шрифт.
6. В поле Цвет выберите белый цвет.

Использование формулы для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Источник: support.microsoft.com

Получить последнюю непустую ячейку в столбце в Google Таблицах

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

Создан 13 ноя.

Аналогичный вопрос: stackoverflow.com/questions/4169914/… — Jon Schneider

17 ответы

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

=INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) )
Итак, если вы объедините его с вашей текущей функцией, это будет выглядеть так:
=DAYS360(A2,INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ))
Создан 17 ноя.

Я закончил сравнение с пустой строкой, а не с использованием ISBLANK, который обрабатывает некоторые пустые ячейки (например, формулы, возвращающие пустое значение, такие как = «», как непустые. Таким образом: ‘= index (filter (A: A, A: A <> «»), строки (filter (A: A, A: A <> «»))) ‘- кругpi314

У меня тоже были проблемы с ISBLANK из-за формул. Но используя = ИНДЕКС (ФИЛЬТР (F3: F; F3: F <> «»)); ROWS (FILTER (F3: F; F3: F <> «»)) приводит к ошибке синтаксического анализа формулы. Есть идеи, что не так? — хлор

Вопрос. Если вместо A: A есть importrange (), как это можно переписать, не выполняя тот же importrange () 4 раза? — Ирина Рапопорт

Слегка упрощенный ответ Дуга Брэдшоу, который также обрабатывает пробелы: =INDEX(FILTER(A1:A,NOT(ISBLANK(A1:A))),COUNTA(A1:A)) (можно изменить начальную строку A1) полное описание по адресу: stackoverflow.com/a/27623407/539149 — Зак Моррис

Есть ли способ игнорировать нечисловые значения (например, ячейки заголовка в том же столбце)? — Шридхар Сарнобат

Чтобы найти последнюю непустую ячейку, вы можете использовать INDEX и MATCH работает так:

=DAYS360(A2; INDEX(A:A; MATCH(99^99;A:A; 1)))
Думаю, это немного быстрее и проще.
Создан 09 янв.

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

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

Предполагается, что столбец отсортирован — Андрей Адаменко
Также может быть выполнено как =DAYS360(A2;VLOOKUP(99^99;A:A;1)) — встепанюк

Если A2: A содержит даты подряд, то INDEX (A2: A, COUNT (A2: A)) вернет последнюю дату. Окончательная формула

=DAYS360(A2,INDEX(A2:A,COUNT(A2:A)))
ответ дан 25 мар ’16, в 22:03

Мой любимый:

=INDEX(A2:A,COUNTA(A2:A),1)
Итак, для необходимости OP:
=DAYS360(A2,INDEX(A2:A,COUNTA(A2:A),1))
ответ дан 31 мар ’17, в 14:03

. хотя «MAX», опубликованный Poul выше, выглядит чище в случае, когда последняя дата всегда самая поздняя. — Эрик Смоллинг

Он имел в виду +1 не, 1 — Ньюбрикт

Это не отвечает на вопрос — выбирается последнее значение, но не последнее непустое значение. — Нейтс

Хотя на вопрос уже дан ответ, есть красноречивый способ сделать это.

Use just the column name to denote last non-empty row of that column.

Например: Если ваши данные в A1:A100 и вы хотите добавить еще несколько данных в столбец A, скажем, это может быть A1:A105 или даже A1:A1234 позже вы можете использовать этот диапазон:

Итак, чтобы получить последнее непустое значение в диапазоне, мы будем использовать 2 функции:

• COUNTA
• ИНДЕКС

Ответ на этот вопрос =INDEX(B3:B,COUNTA(B3:B)) .

COUNTA(range) возвращает количество значений в диапазоне, мы можем использовать это, чтобы получить количество строк.

INDEX(range, row, col) возвращает значение в диапазоне в позиции row и col ( col=1 если не указано)

INDEX(A1:C5,1,1) = A1 INDEX(A1:C5,1) = A1 # implicitly states that col = 1 INDEX(A1:C5,1,2) = A2 INDEX(A1:C5,2,1) = B1 INDEX(A1:C5,2,2) = B2 INDEX(A1:C5,3,1) = C1 INDEX(A1:C5,3,2) = C2

Для изображения выше наш диапазон будет B3:B . Итак, посчитаем, сколько значений находится в диапазоне B3:B by COUNTA(B3:B) первый. В левой части будет производиться 8 поскольку есть 8 значений, пока он будет производить 9 в правой части. Мы также знаем, что последнее значение находится в 1-м столбце диапазона B3:B так что col параметр INDEX должно быть 1 и row параметр должен быть COUNTA(B3:B) .

Создан 24 июля ’20, 23:07

Мне это нравится, но я видел проблемы, когда иногда он возвращал предпоследний элемент вместо последнего. — Эрик Смоллинг

Не работает. Откуда у тебя цитата, кстати? Я думал, что это из документации Google Sheet. — Atul

Кажется, что последний параметр 1 не нужен. — Альфа Хуанг

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

Окончательная формула будет такой:

=DAYS360(A2; MAX(A2:A))

ответ дан 02 авг.

=indirect(«A»>»»,row(A:A),»»))))

С окончательным уравнением:

=DAYS360(A2,indirect(«A»>»»,row(A:A),»»)))))

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

=max(arrayformula(if(A:A<>»»,row(A:A),»»)))

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

Кроме того, у этого есть дополнительное преимущество, заключающееся в том, что он работает для любого типа данных в любом порядке, и вы можете иметь пустые строки между строками с содержимым, и он не учитывает ячейки с формулами, которые оцениваются как «». Он также может обрабатывать повторяющиеся значения. В целом это очень похоже на уравнение, которое использует здесь max ((G: G <> «») * row (G: G)), но упрощает извлечение номера строки, если это то, что вам нужно. .

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

function lastRow(sheet,column) < var ss = SpreadsheetApp.getActiveSpreadsheet(); if (column == null) < if (sheet != null) < var sheet = ss.getSheetByName(sheet); >else < var sheet = ss.getActiveSheet(); >return sheet.getLastRow(); > else < var sheet = ss.getSheetByName(sheet); var lastRow = sheet.getLastRow(); var array = sheet.getRange(column + 1 + ‘:’ + column + lastRow).getValues(); for (i=0;i> if (final != null) < return final; >else < return 0; >> >

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

=LASTROW()

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

=LASTROW(«Sheet1″,»A»)

И для последней строки конкретного листа в целом:

=LASTROW(«Sheet1»)

Затем, чтобы получить фактические данные, вы можете использовать косвенные:

=INDIRECT(«A»
return sheet.getRange(column + lastRow).getValue();

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

Наверное, перебор, но все возможно.

Источник: stackovergo.com