Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Гугл Таблицы. Что делать, если запрос QUERY не вернул резуль - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Гугл Таблицы. Что делать, если запрос QUERY не вернул резуль (Формулы/Formulas)
Гугл Таблицы. Что делать, если запрос QUERY не вернул резуль
book Дата: Понедельник, 02.08.2021, 09:35 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 98
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!
Для преобразования таблиц использую функцию 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, то соответствующая часть запроса возвращает #Н\Д и «слетает» вся формула.

Цель – получить суммы без НДС для определенных строк. Подскажите, пожалуйста, можно ли как-то обойти эту ошибку? или надо переделать всю логику таблицы?

Спасибо.

Ссылка на файл. В файле два листа. Проблемные ячейки выделены желтым.
https://docs.google.com/spreads....0663477


--
С уважением,
Андрей.


Сообщение отредактировал book - Понедельник, 02.08.2021, 09:37
 
Ответить
СообщениеДобрый день!
Для преобразования таблиц использую функцию 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, то соответствующая часть запроса возвращает #Н\Д и «слетает» вся формула.

Цель – получить суммы без НДС для определенных строк. Подскажите, пожалуйста, можно ли как-то обойти эту ошибку? или надо переделать всю логику таблицы?

Спасибо.

Ссылка на файл. В файле два листа. Проблемные ячейки выделены желтым.
https://docs.google.com/spreads....0663477

Автор - book
Дата добавления - 02.08.2021 в 09:35
Gustav Дата: Понедельник, 02.08.2021, 11:18 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1907
Репутация: 770 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Думаю, Вам должна помочь функция IFERROR. Ею можно обернуть результат, возвращаемый QUERY.

Посмотрите как у меня сделана здесь обработка ситуаций, когда листы с данными для SELECT еще не существуют в природе:
http://www.excelworld.ru/forum/23-32571-212775-16-1489162865

ПОЖЕЛАНИЕ: И лучше оформлять формулы Гугл тегом VBA, а то переводчик формул с RUS на ENG в данном случае оказывает медвежью услугу и представляет содержимое формулы в неудобоваримом виде (он просто пока еще не предназначен для некоторых формул Гугл - отсюда и "каша").


Мой tip box - яд 41001663842605
 
Ответить
СообщениеДумаю, Вам должна помочь функция IFERROR. Ею можно обернуть результат, возвращаемый QUERY.

Посмотрите как у меня сделана здесь обработка ситуаций, когда листы с данными для SELECT еще не существуют в природе:
http://www.excelworld.ru/forum/23-32571-212775-16-1489162865

ПОЖЕЛАНИЕ: И лучше оформлять формулы Гугл тегом VBA, а то переводчик формул с RUS на ENG в данном случае оказывает медвежью услугу и представляет содержимое формулы в неудобоваримом виде (он просто пока еще не предназначен для некоторых формул Гугл - отсюда и "каша").

Автор - Gustav
Дата добавления - 02.08.2021 в 11:18
Gustav Дата: Понедельник, 02.08.2021, 12:49 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1907
Репутация: 770 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Вот такая формула работает (в вашу таблицу тоже вставил в копию Реестра):
[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).


Мой tip box - яд 41001663842605
 
Ответить
СообщениеВот такая формула работает (в вашу таблицу тоже вставил в копию Реестра):
[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
Дата добавления - 02.08.2021 в 12:49
book Дата: Понедельник, 02.08.2021, 13:49 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 98
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
в вашу таблицу тоже вставил в копию Реестра

Gustav, Спасибо! Я видел Вашу формулу.
Хотелось бы сделать более компактно, т.к. реально будет не два запроса – больше.

Есть задумка к исходному массиву «приклеить» массив с нулями и единицами и уже потом обращаться через QUERY к объединенному массиву. Но что-то пошло не так…

Посмотрите, пожалуйста, на листе ВопросДляGustav. Выделено желтым.


--
С уважением,
Андрей.
 
Ответить
Сообщение
в вашу таблицу тоже вставил в копию Реестра

Gustav, Спасибо! Я видел Вашу формулу.
Хотелось бы сделать более компактно, т.к. реально будет не два запроса – больше.

Есть задумка к исходному массиву «приклеить» массив с нулями и единицами и уже потом обращаться через QUERY к объединенному массиву. Но что-то пошло не так…

Посмотрите, пожалуйста, на листе ВопросДляGustav. Выделено желтым.

Автор - book
Дата добавления - 02.08.2021 в 13:49
book Дата: Понедельник, 02.08.2021, 13:55 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 98
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
И лучше оформлять формулы Гугл тегом VBA

Спасибо за совет. Видел, но не знал, как исправить.


--
С уважением,
Андрей.
 
Ответить
Сообщение
И лучше оформлять формулы Гугл тегом VBA

Спасибо за совет. Видел, но не знал, как исправить.

Автор - book
Дата добавления - 02.08.2021 в 13:55
Gustav Дата: Понедельник, 02.08.2021, 15:27 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1907
Репутация: 770 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Есть задумка к исходному массиву «приклеить» массив с нулями и единицами и уже потом обращаться через QUERY к объединенному массиву. Но что-то пошло не так…

Вам надо разделить SELECT и WHERE в вашей исходной формуле:
[vba]
Код
=query( { 'План'!A2:X5;  'Сервис'!A2:X5 } ; "select Col1,Col2,Col3 WHERE Col2=1" )
[/vba]
на два QUERY внутри той же формулы:
[vba]
Код
=QUERY(query({'План'!A2:X5;'Сервис'!A2:X5 };"select Col1,Col2,Col3";0);"WHERE Col2='1'")
[/vba]
Обратите внимание, что я подавил заголовки во внутреннем QUERY при помощи константы 0 (а то они выходили из-под контроля за счет конкатенации первых строк), а также, что тип второго столбца во время исполнения внутреннего QUERY изменился на текстовый и теперь во внешнем QUERY надо искать не число 1, а текст '1'.

Вообще, будьте осторожнее со смешением в одном столбце текстовых и числовых значений - результаты могут неприятно удивлять. Тем более, раз вы генерируете некую фиктивную строку: просто оставляйте пустую строку или пробел в текстовых колонках и, например, 0 - в числовых.


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Есть задумка к исходному массиву «приклеить» массив с нулями и единицами и уже потом обращаться через QUERY к объединенному массиву. Но что-то пошло не так…

Вам надо разделить SELECT и WHERE в вашей исходной формуле:
[vba]
Код
=query( { 'План'!A2:X5;  'Сервис'!A2:X5 } ; "select Col1,Col2,Col3 WHERE Col2=1" )
[/vba]
на два QUERY внутри той же формулы:
[vba]
Код
=QUERY(query({'План'!A2:X5;'Сервис'!A2:X5 };"select Col1,Col2,Col3";0);"WHERE Col2='1'")
[/vba]
Обратите внимание, что я подавил заголовки во внутреннем QUERY при помощи константы 0 (а то они выходили из-под контроля за счет конкатенации первых строк), а также, что тип второго столбца во время исполнения внутреннего QUERY изменился на текстовый и теперь во внешнем QUERY надо искать не число 1, а текст '1'.

Вообще, будьте осторожнее со смешением в одном столбце текстовых и числовых значений - результаты могут неприятно удивлять. Тем более, раз вы генерируете некую фиктивную строку: просто оставляйте пустую строку или пробел в текстовых колонках и, например, 0 - в числовых.

Автор - Gustav
Дата добавления - 02.08.2021 в 15:27
book Дата: Понедельник, 02.08.2021, 15:43 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 98
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
тип второго столбца во время исполнения внутреннего QUERY изменился на текстовый и теперь во внешнем QUERY надо искать не число 1, а текст '1'.

Это в данном случае только? или QUERY всегда так работает?


--
С уважением,
Андрей.
 
Ответить
Сообщение
тип второго столбца во время исполнения внутреннего QUERY изменился на текстовый и теперь во внешнем QUERY надо искать не число 1, а текст '1'.

Это в данном случае только? или QUERY всегда так работает?

Автор - book
Дата добавления - 02.08.2021 в 15:43
book Дата: Понедельник, 02.08.2021, 15:44 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 98
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Вообще, будьте осторожнее со смешением в одном столбце текстовых и числовых значений

Да - это плохая практика. В данном случае поспешил, создавая пример. В реальных данных за этим всегда слежу.


--
С уважением,
Андрей.
 
Ответить
Сообщение
Вообще, будьте осторожнее со смешением в одном столбце текстовых и числовых значений

Да - это плохая практика. В данном случае поспешил, создавая пример. В реальных данных за этим всегда слежу.

Автор - book
Дата добавления - 02.08.2021 в 15:44
Gustav Дата: Понедельник, 02.08.2021, 16:16 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1907
Репутация: 770 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Это в данном случае только?

В данном случае. Потому что тип столбца QUERY определяется по наиболее часто встречающемуся типу среди данных, из которых делается выборка. Если это число, то всё, что не число, отвергается. Можно изменить тип, забив в изначально числовой столбец текстовые значения в количестве, превышающем количество чисел в нём - увидите что получится: даже числа станут текстом (что у вас и происходит).

Примерно то же самое здесь: http://www.excelworld.ru/forum/23-43396-287359-16-1573720806


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Это в данном случае только?

В данном случае. Потому что тип столбца QUERY определяется по наиболее часто встречающемуся типу среди данных, из которых делается выборка. Если это число, то всё, что не число, отвергается. Можно изменить тип, забив в изначально числовой столбец текстовые значения в количестве, превышающем количество чисел в нём - увидите что получится: даже числа станут текстом (что у вас и происходит).

Примерно то же самое здесь: http://www.excelworld.ru/forum/23-43396-287359-16-1573720806

Автор - Gustav
Дата добавления - 02.08.2021 в 16:16
book Дата: Понедельник, 02.08.2021, 16:23 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 98
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Gustav, большое спасибо! Очень ценная помощь и подробные объяснения.


--
С уважением,
Андрей.
 
Ответить
СообщениеGustav, большое спасибо! Очень ценная помощь и подробные объяснения.

Автор - book
Дата добавления - 02.08.2021 в 16:23
book Дата: Вторник, 03.08.2021, 14:05 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 98
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
PS В реальных данных, оказалось, всегда есть пустые строки, в которых в "контрольном" столбце "0". Таким образом, ситуация, когда только "1" практически исключается. Но все равно еще раз спасибо. Информация (учеба) была полезной и даром не пропадет.


--
С уважением,
Андрей.
 
Ответить
СообщениеPS В реальных данных, оказалось, всегда есть пустые строки, в которых в "контрольном" столбце "0". Таким образом, ситуация, когда только "1" практически исключается. Но все равно еще раз спасибо. Информация (учеба) была полезной и даром не пропадет.

Автор - book
Дата добавления - 03.08.2021 в 14:05
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Гугл Таблицы. Что делать, если запрос QUERY не вернул резуль (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!