Меню

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

QUERY. Функция для создания запросов в Google-Таблицах

Спасибо Евгению Намоконову за помощь в подготовке материала.

Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.

=QUERY(данные; запрос; [заголовки])

  • данные — это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
  • запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
  • заголовки — количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.

Итак, правила формирования запросов:

  1. Запрос указывается в кавычках.
  2. В запросе используются ключевые слова:
    1. SELECT — определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
    2. WHERE — ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
    3. GROUP BY — группирует значения по заданным полям.
    4. PIVOT — позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
    5. ORDER BY — задает сортировку. Например: «ORDER BY C DESC» — сортировка по столбцу C по убыванию.
    6. LIMIT — ограничивает количество возвращаемых строк. Например: «LIMIT 50».
    7. OFFSET — пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
    8. FORMAT — определяет формат определенных столбцов по заданному шаблону.
    9. LABEL — позволяет переименовать столбцы в выдаче. Например, «LABEL MAX(D) ‘Среднее в 2016 году'». Вместо max 2016 в сформированной выдаче будет заголовок «Среднее в 2016 году».

Рассмотрим несколько примеров применения QUERY на практике.

Простой пример: выбираем книги определенной тематики из таблицы

Из простой исходной таблицы будем формировать список книг по тематике:

При этом тематику будем выбирать из выпадающего списка на отдельном листе:

Функция QUERY для решения этой задачи будет выглядеть следующим образом:

Мы извлекаем данные из столбцов A и C в диапазоне ‘Книги’!A1:C. Фильтруем данные по столбцу B (тематике) этого диапазона по выбранному критерию из выпадающего списка в ячейке A1. Сортируем по убыванию по столбцу C исходного диапазона и добавляем к нашей выборке заголовки (последний аргумент функции QUERY = 1).

Группируем данные с помощью GROUP BY и PIVOT

Сгруппировать данные, используя QUERY, можно с помощью двух ключевых слов: GROUP BY и PIVOT, ниже рассмотрим примеры с ними.

Таблица, с которой мы будем работать:

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

Начнем с GROUP BY, текст функции будет таким:

=QUERY(‘Книги‘!A1:C6;»select B, sum(C) group by B»)

Обратите внимание: чтобы функция работала, помимо группировки (group by B) нужна хотя бы одна аггрегирующая функция, в нашем случае это sum(C). Напишу, на всякий случай, все аггрегирующие функции для QUERY: sum(), max(), min(), avg() и count().

Результат нашей формулы:

С помощью GROUP BY возможна группировка и по нескольким столбцам, для этого просто перечислите их, как в функции ниже и не забудьте добавить эти столбцы в SELECT:

Группировка с помощью PIVOT.

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

Пока отличие в том, что сгрупированные элементы расположены по столбцам, а не по строкам, как в GROUP BY.

Добавим еще один столбец для группировки.

Видите — два сгруппированных столбца отображаются в одном поле через запятую. В этом ключевое отличие PIVOT от GROUP BY, если там каждый столбец группировки занимает отдельный столбец, то в PIVOT получается нечто вроде сводной таблицы с уникальными полями из нескольких элементов. По этим полям, кстати, потом можно довольно просто искать нужное значение с помощью ГПР или ПОИСКПОЗ.

Строим сводную таблицу со средними/максимальными значениями по тематикам

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

Мы используем похожий диапазон (в отличие от предыдущего в нем есть продажи за 2015 и 2016 годы), извлекаем средние значения по столбцам C и D (SELECT avg(C), avg(D)) и группируем их по столбцу B (тематика).

Читайте также:  Комплекс разминки из 12 упражнений на уроках физической культуры

Полученный результат транспонируем для удобного отображения (с помощью функции TRANSPOSE (ТРАНСП)):

Можно использовать и другие функции вместо avg (среднего), например max (максимальные значения):

Или отобразить и среднее, и максимум, но только по столбцу D:

Кейс «Считаем средний чек, выбирая данные с определенной даты»

На скриншоте массив данных, с которым мы будем работать:

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

Начнем. Создадим QUERY с умножением количества клиентов (столбец B) на средний чек (столбец С) начиная с определенной даты:

Правильно использовать дату в формуле QUERY так:

  • QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
  • перед датой и перед апострофом нужно написать date;
  • можно и не делать ссылку на ячейку с датой, а написать ее сразу в QUERY, тогда формула будет выглядеть так:

  • дата с двух сторон обрамляется одиночными кавычками (‘).

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

Это построчные произведения количества клиентов на средний чек. Нам нужно просуммировать их, для этого введем перед формулой СУММ (SUM):

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

Берем предыдущую формулу, меняем B*C на sum(B) и получаем такую конструкцию:

Наконец, совмещаем формулы:

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

Кейс «QUERY и выпадающий список»

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

Итак, выпадающий список. Вначале создадим новый лист (допустим, наша исходная таблица огромна, и всю аналитику мы хотим производить на другом листе). Кликаем правой кнопкой мыши на ячейку А1, выбираем Проверка данных.

В Правилах выбираем Значение из списка, перечисляем все наши тематики через запятую и нажимаем Сохранить:

Список получился вот таким:

В соседнюю ячейку А2 впишем следующую формулу:

И разберем ее по частям:

  • ‘Книги’!A1:C13 — исходный диапазон, таблица с продажами, книгами и тематиками.
  • SELECT A, C — в сформированную функцией таблицу попадут данные из этих столбцов, то есть названия книг и продажи.
  • WHERE B = ‘»&A1&»‘ отбирает только те книги, тематика (в столбце B) которых соответствует указанной в ячейке A1. Обратите внимание на синтаксис: текст из ячейки указывается между апострофов, которые относятся к тексту запроса. После них идут кавычки (мы закрываем текст запроса), амперсанд (присоединяем к тексту запроса текст из ячейки), адрес ячейки, еще один амперсанд, после которого в кавычках продолжается текст запроса.
  • ORDER BY C DESC — сортируем данные по столбцу B (продажам) по убыванию.

Изменив тематику в ячейке А1 на философию, мы получим книги только по философии, отсортированные по продажам. Удобно.

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

Query по нескольким диапазонам данных

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

Диапазоны указываются через точку с запятой в фигурных скобках:

Важно отметить: в таком случае столбцы внутри запроса обозначаются не буквами, как в других случаях (A, B, AH, CZ и так далее), а в виде ColN, где N — номер столбца.

Канал «Google Таблицы — это просто» в Телеграме

Спасибо Евгению Намоконову за помощь в подготовке кейсов для этой статьи. Мы с Евгением ведем канал в Телеграме по Google Таблицам.

Регулярно публикуем новые кейсы и советы. Заглядывайте в гости:

Источник

Выделение строки цветом по значению в ячейке в Гугл таблицах и Excel

Полезно для визуального разделения информации цветовой индикацией

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

Читайте также:  Описание и характеристика кросса

Для этого нужно использовать условное форматирование

Встаём на ячейку со статусом и в условном форматировании выбираем собственную формулу.

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

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

Разберём её подробнее.

SEARCH (ПОИСК) возвращает числовой порядковый номер искомого значения в ячейке. В нашем примере это будет 1. Если в ячейке стояло бы иное значение, например «Лучшие» или «Хорошие», то формула вернет ошибку и выделение по цвету не сработает.

ISNUMBER (ЕЧИСЛО) является булевой функцией и возвращает значение ИСТИНА / ЛОЖЬ. Если SEARCH() возвращает число (иначе говоря находит искомое значение в ячейке), то функция ISNUMBER возвращает ИСТИНА и формула срабатывает, строка выделяется цветом. Если не возвращает, то формула не работает.

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

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

Источник



Функция ЕПУСТО() в MS EXCEL

16 декабря 2013 г.

Задача функции ЕПУСТО() , английский вариант ISBLANK(), — проверять есть ли в ячейке число, текстовое значение, формула или нет. Если в ячейке А1 имеется значение 555, то формула = ЕПУСТО(А1) вернет ЛОЖЬ, а если ячейка А1 пуста, то ИСТИНА.

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

Использование функции

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

1. Если в проверяемой ячейке содержится число, текстовое значение, формула, то функция вернет логическое значение ИСТИНА.

2. Если проверяемая ячейка пуста, то функция также вернет логическое значение ЛОЖЬ.

Проверка диапазона ячеек

Функция ЕПУСТО() проверяет содержимое только одной ячейки. Чтобы подсчитать количество пустых ячеек в диапазоне, то используйте функцию СЧИТАТЬПУСТОТЫ() , но если ячейки содержат значение Пустой текст («»), то функция СЧИТАТЬПУСТОТЫ() будет подсчитывать также и эти ячейки наряду с действительно пустыми. Об этом читайте в статье Подсчет пустых ячеек .

Чтобы ответить на вопрос «Есть ли хотя бы 1 пустая ячейка в B6:B11?» используйте формулу массива

Чтобы ответить на вопрос «Есть ли хотя бы 1 заполненная ячейка в B6:B11?» используйте формулу массива

Источник

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

от sasza

Когда вы анализируете данные в электронной таблице, подсчет пустых или пустых ячеек может помочь вам сосредоточиться на определенных областях. Вот почему такие функции, как COUNTBLANK, COUNTIF, COUNTIFS и SUMPRODUCT, так важны в Google Таблицах.

Однако одно предупреждение. Если у вас есть ячейка, содержащая пустую текстовую строку («») или формулу, возвращающую аналогичный результат, эта ячейка будет пустой, но технически не будет пустой. Если вы хотите узнать количество действительно пустых ячеек, вам необходимо использовать комбинацию функций СУММ, СТРОКИ, СТОЛБЦЫ и СЧЁТЕСЛИ.

Использование COUNTBLANK

Вы можете попробовать функцию СЧИТАТЬПУСТОТЫ, чтобы подсчитать количество пустых ячеек в электронной таблице Google Таблиц. Это самый быстрый способ найти количество пустых, но не пустых ячеек.

Ячейки, содержащие числа или текст, не учитываются, включая ячейки с нулевым номером. Однако, как мы упоминали, если ячейка выглядит пустой, но содержит пустую текстовую строку («»), это будет засчитано.

Чтобы использовать его, откройте свой Google Таблицы электронная таблица. Щелкните пустую ячейку и введите = СЧИТАТЬПУСТОТЫ (диапазон). Замените диапазон диапазоном ячеек.

Например, если вы хотите подсчитать количество пустых ячеек между столбцами A и C, введите = COUNTBLANK (A: C).

Функция СЧИТАТЬПУСТОТЫ, используемая для подсчета пустых ячеек в Google Таблицах.

В приведенном выше примере ячейки от A3 до H24 используются в пределах диапазона. Этот диапазон содержит четыре пустые ячейки (B4, C4, D4 и E4), что соответствует той же цифре, которая отображается в ячейке A1.

Использование COUNTIF и COUNTIFS

Хотя COUNTBLANK возвращает количество пустых ячеек, вы также можете использовать COUNTIF или COUNTIFS для достижения того же результата.

СЧЁТЕСЛИ подсчитывает количество ячеек, соответствующих критериям, которые вы определяете в самой формуле. Поскольку вы хотите подсчитывать пустые ячейки, вы можете использовать пустую текстовую строку в качестве критерия.

Читайте также:  Проверка таблицы умножения и деления 3 класс примеры

Чтобы использовать СЧЁТЕСЛИ, откройте электронную таблицу Google Таблиц и щелкните пустую ячейку. Введите = СЧЁТЕСЛИ (диапазон; ”»), заменив диапазон выбранным диапазоном ячеек.

Функция СЧЁТЕСЛИ, используемая для вычисления пустых ячеек в Google Таблицах.

В приведенном выше примере есть три пустые ячейки (B4, C4 и D4) в диапазоне от A3 до H24, при этом функция СЧЁТЕСЛИ в ячейке A1 возвращает такое же количество пустых ячеек.

Функцию СЧЁТЕСЛИМН можно использовать как альтернативу СЧЁТЕСЛИ. Используйте = СЧЁТЕСЛИМН (диапазон, ””), заменяя диапазон выбранным диапазоном ячеек.

Функция СЧЁТЕСЛИМН, используемая в электронной таблице Google Таблиц.

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

Использование SUMPRODUCT

Функция СУММПРОИЗВ предлагает немного более сложный способ подсчета количества пустых ячеек. Он подсчитывает количество ячеек, соответствующих определенным критериям, которые в данном случае будут пустой текстовой строкой («»).

Чтобы использовать SUMPRODUCT, откройте электронную таблицу Google Sheets и щелкните пустую ячейку. Введите = СУММПРОИЗВ (- (диапазон = ””)), заменив диапазон выбранным диапазоном ячеек.

Функция СУММПРОИЗВ, подсчитывающая пустые ячейки в Google Таблицах

В приведенном выше примере показано, что в диапазоне ячеек от A2 до H24 были обнаружены две пустые ячейки (B4 и C4).

Подсчет пустых ячеек

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

Обходной путь к этой проблеме — использовать СЧЁТЕСЛИ для подсчета количества ячеек с числовым значением, а затем использовать вторую формулу СЧЁТЕСЛИ для подсчета количества ячеек, содержащих текст или пустые текстовые строки.

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

Для начала откройте электронную таблицу Google Sheets, щелкните пустую ячейку и введите = ROWS (диапазон) * COLUMNS (диапазон), заменив значение диапазона диапазоном ячеек.

Функции ROWS и COLUMNS, используемые для вычисления количества ячеек в диапазоне в Google Таблицах.

Во второй пустой ячейке введите = СЧЁТЕСЛИ (диапазон, ”> = 0 ″), чтобы подсчитать количество ячеек с числовым значением. Еще раз замените диапазон на соответствующий диапазон ячеек для ваших данных.

Функция СЧЁТЕСЛИ в Google Таблицах, используется для подсчета количества ячеек с числовым значением.

Для поиска пустых ячеек или ячеек, содержащих текст, введите = СЧЁТЕСЛИ (диапазон, «*») в третьей пустой ячейке. При необходимости замените диапазон.

Функция СЧЁТЕСЛИ, подсчитывающая количество ячеек с текстом / пустыми текстовыми строками в Google Таблицах

Затем вы можете использовать СУММ для сложения двух значений СЧЁТЕСЛИ, вычитая это число из числа ячеек в вашем диапазоне, рассчитанного с помощью функций СТРОКИ и СТОЛБЦЫ.

В нашем примере общее количество ячеек можно найти в ячейке B8, количество ячеек с числовым значением в B9 и количество ячеек, содержащих текст или пустую текстовую строку, в B10.

Заменив эти значения ячеек своими собственными, вы можете использовать = B8-SUM (B9: 10), чтобы определить количество действительно пустых ячеек в вашем диапазоне.

Окончательный расчет пустых ячеек в Google Таблицах с использованием СУММ

Как показано в приведенном выше примере, в диапазоне из 20 ячеек (от A2 до E5) было обнаружено, что 19 ячеек содержат число, текст или пустую текстовую строку. Только одна ячейка, Е4, была полностью пустой.

Источник

Как в гугл таблицах составить формулу, вставить текст «требуемый ресурс» если соседняя ячейка содержит текст, а если соседняя пустая то тоже пусто?

При работе в гугл таблице помогите составить формулу, чтобы в ячейке столбца C появлялась фраза «Требуемый ресурс» при наличии любого текста в ячейке столбца D. если ячейка D пустая, то C тоже должны быть пустая.
5b12cd8129993688215172.jpeg

  • Вопрос задан более трёх лет назад
  • 750 просмотров
  • Facebook
  • Вконтакте
  • Twitter
  • Google

TosterModerator

zamboga

И протягиваете формулу вниз.

  • Facebook
  • Вконтакте
  • Twitter
  • Google

Войдите, чтобы написать ответ

Как найти наибольшее значение из диапазона по критерию?

  • 1 подписчик
  • вчера
  • 15 просмотров

Как в Google Sheet сделать фильтрацию по группе, выбрав только ячейки в которых есть данные отфильтровав пустые?

  • 1 подписчик
  • вчера
  • 17 просмотров

Как в подставить текст из ячейки в формулу?

  • 1 подписчик
  • 20 июл.
  • 23 просмотра

Как при выборе значения из выпадающего списка выводить соответствующий текст в соседнюю ячейку?

  • 1 подписчик
  • 18 июл.
  • 67 просмотров

Почему скрипт использует все ячейки A1 в таблице для своих подсчетов?

  • 1 подписчик
  • 18 июл.
  • 29 просмотров

Почему условное форматирование не закрашивает всю строку?

  • 1 подписчик
  • 17 июл.
  • 39 просмотров

Как разнести цифры числа по ячейкам при дополнительном условии?

  • 1 подписчик
  • 16 июл.
  • 72 просмотра

Как сделать, чтобы в ячейке первая буква была Заглавной?

  • 1 подписчик
  • 16 июл.
  • 84 просмотра

Как отправлять html-письмо через скрипт google в таблицах?

  • 2 подписчика
  • 16 июл.
  • 73 просмотра

Источник