Как создавать пустую строку при отсутствии возвращаемых данных?
Есть запрос QUERY к нескольким листам таблицы, где каждый лист содержит данные за один месяц.
Пример: =query({ '2020/01'!A1:F99; '2020/02'!A1:F99; '2020/03'!A1:F99}; " SELECT * WHERE (Col1 = "&J1&") ")
Если соблюдается условие WHERE (поиск по ID), то возвращаются запрашиваемые данные и заполняется строка таблицы. Всё ОК.
Но в том случае, когда в листе/месяце нет данных по искомому ID, то и возвращать получается нечего и таблица строится с пропуском данного месяца. Как сделать так, чтобы в случае отсутствия данных строка всё равно создавалась, (пустая или с нулями)? Я пробовал что-то навернуть с оператором IF, но ничего не вышло.
Подскажите пожалуйста решение. Если нет возможности это сделать в рамках QUERY запроса, то может быть можно придумать дополнительную конструкцию (формулу, дополнительный запрос к уже полученным данным)?
Сейчас получается так (2020/02 пропущен):
ID | Data | Date
21 | 45 | 2020/01
21 | 22 | 2020/03
А нужно чтобы было так:
ID | Data | Date
21 | 45 | 2020/01
21 | 0 | 2020/02
21 | 22 | 2020/03
Спасибо за ответ, но похоже мне не удалось объяснить задачу:
Я осуществляю выборку по ID, который я беру из ячейки J1: WHERE (Col1 = "&J1&"). В некоторых листах просто нет данных с таким ID и в этом случае я хотел бы получать пустую строку (или с нулями).
Конкретно по вашему решению:
1) Если я правильно понял, то ваша проверка заполняет нулями ячейки при уже имеющейся пустой строке, но в том то и дело что такой строки просто нет. В листе с данными за месяц отсутствует данные с определённым ID и потому QUERY запрос не возвращает никаких данных и строка не создаётся.
2) в первую очередь интересовало решение в рамках QUERY запроса. Подскажите пожалуйста каким образом эту проверку я могу совместить с QUERY запросом?
P.S. зря закрыли мой второй вопрос Как добавить в таблицу недостающие строки? Там рассматривалась упрощённая версия, без QUERY, на который я мог получить больше ответов.
Alex, не понимаю, как по отсутствующему ID вы сможете собрать себе нулевые (несуществующие) записи. У таких записей тоже должен быть ID. В данном случае у вас проблема со структурой данных. Скорее всего вам нужна репрезентация за каждый период. Моя формула построения массива данных для этого подходит. Оберните ее в QUERY и все. Или пришлите пример Таблицы для наглядности.
Alexander Ivanov, Я понимаю, что QUERY не может вернуть ответ по данным, которые не нашёл, но мне хотелось бы, чтобы в тот момент, когда при обращении к очередному листу никаких данных не возвращено, то создавалась бы пустая строка. Вероятно что в моём случае было бы проще разбить задачу. Сначала создавать таблицу с пропусками, а потом дополнять пропущенные строки с нулевыми данными. Потому я и создал второй вопрос: Как добавить в таблицу недостающие строки?
Поле A1 содержит предложенное вами решение
Поле I1 содержит мой QUERY
в G1 ввожу ID товара и получаю пропущенные месяца, если в этот месяц товар не продавался.
Alexander Ivanov, Спасибо большое за решение. Очень интересно, всё понял кроме самой формулы, которая реализует "нулевые записи со всеми возможными комбинациями". Поизучаю саму формулу, разберусь.
Похоже на перебор в цикле?
Боюсь правда что именно здесь в моём случае будет проблемное место. Дело в том, что листов несколько десятков (данные за несколько лет), а количество записей в каждом листе по несколько сотен. В итоге, сгенерированных нулевых записей будет очень много. Для тестового примера всё работает отлично, как будет на реальном пока не знаю. Попробую.
В любом случае, большое спасибо что помогли. Решение рабочее, есть от чего оттолкнуться.
Alex, генерация происходит на основе пересекающихся рядов порядковых номеров двух списков. Вроде того, что если один список 3 элемента, а другой 4, то массив позиций комбинаций будет выглядеть так
1 1
2 2
3 3
1 4
2 1
3 2
1 3
2 4
3 1
1 2
2 3
3 4
Посчитать проблемную область можно, умножив количество ID на количество листов.
Вам нужно менять только первую формулу в Placeholder.
Я попробовал. Не работает.
Видимо потому, что остутствие возвращаемых данных не является ошибкой.
IFFERROR срабатывает тогда, когда например обращешься к листу, которого не существует.
Yaros75, Спасибо. Играюсь. На первый взгляд всё отлично.
Видимо я где-то ошибся когда проверял в первый раз.
Или вы что-то изменили в формуле?
В любом случае, большое спасибо за ваш вариант!
Я думал об этом, но в данном случае это непринципиально - создавать правильный список изначально или добавлять недостающее строки потом, так как главная проблема в несовместимости работы QUERY с таким подходом (QUERY не возвращает строку при отсутствии данных). Моих знаний не хватает чтобы решить эту проблему.
Если у вас есть конкретные формулы или решения как это сделать, то подскажите пожалуйста.
Вот здесь можно попробовать https://docs.google.com/spreadsheets/d/157WTuThGFe...
Alex, ну раз работа штатной формулы Вас не устраивает, на полном серьёзе могу предложить разработать свою формулу, лишённую недостатков. Скрипт GS можно использовать как свою формулу, с обработкой данных по Вашему алгоритму и выводу данных в нужном формате.
