Добрый день! Для преобразования таблиц использую функцию QUERY.
Код
={query('План'!A2:W;"SELECT A,B,D,I, n , sum(O)/120*100,'PL', T,U,W WHERE E is not null AND E<>'конец таблицы' AND U=1 GROUP BY A,B,D,I,N, T,U,W LABEL 'PL' '', sum(O)/120*100 '' "); query('План'!A2:W;"SELECT A,B,D,I, n , sum(O),'PL', T,U,W WHERE E is not null AND E<>'конец таблицы' AND U=0 GROUP BY A,B,D,I,N, T,U,W LABEL 'PL' '', sum(O) '' ")}
Запрос делает из "горизонтальной" таблицы "вертикальную". В нем есть отбор WHERE по значению 0 или 1 в столбце U. Если в столбце источнике отсутствует либо 0, либо 1, то соответствующая часть запроса возвращает #Н\Д и «слетает» вся формула.
Цель – получить суммы без НДС для определенных строк. Подскажите, пожалуйста, можно ли как-то обойти эту ошибку? или надо переделать всю логику таблицы?
Добрый день! Для преобразования таблиц использую функцию QUERY.
Код
={query('План'!A2:W;"SELECT A,B,D,I, n , sum(O)/120*100,'PL', T,U,W WHERE E is not null AND E<>'конец таблицы' AND U=1 GROUP BY A,B,D,I,N, T,U,W LABEL 'PL' '', sum(O)/120*100 '' "); query('План'!A2:W;"SELECT A,B,D,I, n , sum(O),'PL', T,U,W WHERE E is not null AND E<>'конец таблицы' AND U=0 GROUP BY A,B,D,I,N, T,U,W LABEL 'PL' '', sum(O) '' ")}
Запрос делает из "горизонтальной" таблицы "вертикальную". В нем есть отбор WHERE по значению 0 или 1 в столбце U. Если в столбце источнике отсутствует либо 0, либо 1, то соответствующая часть запроса возвращает #Н\Д и «слетает» вся формула.
Цель – получить суммы без НДС для определенных строк. Подскажите, пожалуйста, можно ли как-то обойти эту ошибку? или надо переделать всю логику таблицы?
ПОЖЕЛАНИЕ: И лучше оформлять формулы Гугл тегом VBA, а то переводчик формул с RUS на ENG в данном случае оказывает медвежью услугу и представляет содержимое формулы в неудобоваримом виде (он просто пока еще не предназначен для некоторых формул Гугл - отсюда и "каша").
Думаю, Вам должна помочь функция IFERROR. Ею можно обернуть результат, возвращаемый QUERY.
ПОЖЕЛАНИЕ: И лучше оформлять формулы Гугл тегом VBA, а то переводчик формул с RUS на ENG в данном случае оказывает медвежью услугу и представляет содержимое формулы в неудобоваримом виде (он просто пока еще не предназначен для некоторых формул Гугл - отсюда и "каша").Gustav
Вот такая формула работает (в вашу таблицу тоже вставил в копию Реестра): [vba]
Код
=QUERY({ IFERROR(query('План'!A2:W;"SELECT A,B,D,I,N, sum(O)/120*100,'PL', T,U,W WHERE E is not null AND E<>'конец таблицы' AND U=1 GROUP BY A,B,D,I,N, T,U,W LABEL 'PL' '', sum(O)/120*100 '' ");{""\""\""\""\""\""\""\""\""\""}); IFERROR(query('План'!A2:W;"SELECT A,B,D,I,N, sum(O),'PL', T,U,W WHERE E is not null AND E<>'конец таблицы' AND U=0 GROUP BY A,B,D,I,N, T,U,W LABEL 'PL' '', sum(O) '' ");{""\""\""\""\""\""\""\""\""\""}) };"where Col1 is not null")
[/vba] Как видно, вместо ошибки во внутреннем запросе вставляется однострочный массив пустых значений {""\""\""\""\""\""\""\""\""\""} (пустых строк в нём = по кол-ву столбцов). Далее пустые строки результата удаляются во внешнем QUERY по условию "where Col1 is not null" (Col1 это в данном случае исходная колонка A).
Вот такая формула работает (в вашу таблицу тоже вставил в копию Реестра): [vba]
Код
=QUERY({ IFERROR(query('План'!A2:W;"SELECT A,B,D,I,N, sum(O)/120*100,'PL', T,U,W WHERE E is not null AND E<>'конец таблицы' AND U=1 GROUP BY A,B,D,I,N, T,U,W LABEL 'PL' '', sum(O)/120*100 '' ");{""\""\""\""\""\""\""\""\""\""}); IFERROR(query('План'!A2:W;"SELECT A,B,D,I,N, sum(O),'PL', T,U,W WHERE E is not null AND E<>'конец таблицы' AND U=0 GROUP BY A,B,D,I,N, T,U,W LABEL 'PL' '', sum(O) '' ");{""\""\""\""\""\""\""\""\""\""}) };"where Col1 is not null")
[/vba] Как видно, вместо ошибки во внутреннем запросе вставляется однострочный массив пустых значений {""\""\""\""\""\""\""\""\""\""} (пустых строк в нём = по кол-ву столбцов). Далее пустые строки результата удаляются во внешнем QUERY по условию "where Col1 is not null" (Col1 это в данном случае исходная колонка A).Gustav
Gustav, Спасибо! Я видел Вашу формулу. Хотелось бы сделать более компактно, т.к. реально будет не два запроса – больше.
Есть задумка к исходному массиву «приклеить» массив с нулями и единицами и уже потом обращаться через QUERY к объединенному массиву. Но что-то пошло не так…
Посмотрите, пожалуйста, на листе ВопросДляGustav. Выделено желтым.
Gustav, Спасибо! Я видел Вашу формулу. Хотелось бы сделать более компактно, т.к. реально будет не два запроса – больше.
Есть задумка к исходному массиву «приклеить» массив с нулями и единицами и уже потом обращаться через QUERY к объединенному массиву. Но что-то пошло не так…
Посмотрите, пожалуйста, на листе ВопросДляGustav. Выделено желтым.book
Есть задумка к исходному массиву «приклеить» массив с нулями и единицами и уже потом обращаться через QUERY к объединенному массиву. Но что-то пошло не так…
Вам надо разделить SELECT и WHERE в вашей исходной формуле: [vba]
[/vba] Обратите внимание, что я подавил заголовки во внутреннем QUERY при помощи константы 0 (а то они выходили из-под контроля за счет конкатенации первых строк), а также, что тип второго столбца во время исполнения внутреннего QUERY изменился на текстовый и теперь во внешнем QUERY надо искать не число 1, а текст '1'.
Вообще, будьте осторожнее со смешением в одном столбце текстовых и числовых значений - результаты могут неприятно удивлять. Тем более, раз вы генерируете некую фиктивную строку: просто оставляйте пустую строку или пробел в текстовых колонках и, например, 0 - в числовых.
Есть задумка к исходному массиву «приклеить» массив с нулями и единицами и уже потом обращаться через QUERY к объединенному массиву. Но что-то пошло не так…
Вам надо разделить SELECT и WHERE в вашей исходной формуле: [vba]
[/vba] Обратите внимание, что я подавил заголовки во внутреннем QUERY при помощи константы 0 (а то они выходили из-под контроля за счет конкатенации первых строк), а также, что тип второго столбца во время исполнения внутреннего QUERY изменился на текстовый и теперь во внешнем QUERY надо искать не число 1, а текст '1'.
Вообще, будьте осторожнее со смешением в одном столбце текстовых и числовых значений - результаты могут неприятно удивлять. Тем более, раз вы генерируете некую фиктивную строку: просто оставляйте пустую строку или пробел в текстовых колонках и, например, 0 - в числовых.Gustav
В данном случае. Потому что тип столбца QUERY определяется по наиболее часто встречающемуся типу среди данных, из которых делается выборка. Если это число, то всё, что не число, отвергается. Можно изменить тип, забив в изначально числовой столбец текстовые значения в количестве, превышающем количество чисел в нём - увидите что получится: даже числа станут текстом (что у вас и происходит).
В данном случае. Потому что тип столбца QUERY определяется по наиболее часто встречающемуся типу среди данных, из которых делается выборка. Если это число, то всё, что не число, отвергается. Можно изменить тип, забив в изначально числовой столбец текстовые значения в количестве, превышающем количество чисел в нём - увидите что получится: даже числа станут текстом (что у вас и происходит).
PS В реальных данных, оказалось, всегда есть пустые строки, в которых в "контрольном" столбце "0". Таким образом, ситуация, когда только "1" практически исключается. Но все равно еще раз спасибо. Информация (учеба) была полезной и даром не пропадет.
PS В реальных данных, оказалось, всегда есть пустые строки, в которых в "контрольном" столбце "0". Таким образом, ситуация, когда только "1" практически исключается. Но все равно еще раз спасибо. Информация (учеба) была полезной и даром не пропадет.book
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
