Домашняя страница 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 Дата: Суббота, 09.02.2019, 17:31 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!

Подскажите, пожалуйста, как в результате запроса QUERY получить именно пустые столбцы между заполненными?

Для примера, указываем диапазон А:С. В результате надо получить табличку, в которой 2-й столбец будет пустой. Данные из А попадут в А (другого листа), В будет пустой, данные из В попадут в С, из С - в D. Если в запросе указать select ' ', то получим столбец с каким-то количеством пробелов в каждой ячейке, но не пустой...

Заранее спасибо.


--
С уважением,
Андрей.
 
Ответить
СообщениеДобрый день!

Подскажите, пожалуйста, как в результате запроса QUERY получить именно пустые столбцы между заполненными?

Для примера, указываем диапазон А:С. В результате надо получить табличку, в которой 2-й столбец будет пустой. Данные из А попадут в А (другого листа), В будет пустой, данные из В попадут в С, из С - в D. Если в запросе указать select ' ', то получим столбец с каким-то количеством пробелов в каждой ячейке, но не пустой...

Заранее спасибо.

Автор - book
Дата добавления - 09.02.2019 в 17:31
krosav4ig Дата: Суббота, 09.02.2019, 20:38 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 919 ±
Замечаний: 0% ±

Excel 2007,2010,2013
book, [vba]
Код
select ... , null as B , ... from ...
[/vba]


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениеbook, [vba]
Код
select ... , null as B , ... from ...
[/vba]

Автор - krosav4ig
Дата добавления - 09.02.2019 в 20:38
book Дата: Суббота, 09.02.2019, 21:14 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
krosav4ig,
Спасибо за ответ. Но... как это часто бывает... я не полностью дал информацию в вопросе... Прошу извинить...
Речь идет о Гугл Таблицах. И если написать вот так
Код
=query(A:D; "select A, null as B, B, C")

то выдает ошибку #ЗНАЧ!


--
С уважением,
Андрей.
 
Ответить
Сообщениеkrosav4ig,
Спасибо за ответ. Но... как это часто бывает... я не полностью дал информацию в вопросе... Прошу извинить...
Речь идет о Гугл Таблицах. И если написать вот так
Код
=query(A:D; "select A, null as B, B, C")

то выдает ошибку #ЗНАЧ!

Автор - book
Дата добавления - 09.02.2019 в 21:14
book Дата: Суббота, 09.02.2019, 21:46 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Пока ждал ответ нашел "обходное" решение.

Указываю заведомо более широкий диапазон и потом вывожу заведомо пустые столбцы между столбцов с данными.
Фрагмент формулы выглядит вот так
Код
select Col1, Col14, Col5*-1, Col12
где Col14 - пустой столбец.

Т.к. данные находятся в другой книге, то указываю Col... с номером столбца вместо А,В,С.

Это удобнее тем, что нет нужды потом указывать "пустое" название каждого пустого столбца. В предыдущем случае еще и каждый раз добавлял
Код
LABEL '  ' '', '     ' ''
и т.д.

Вот только не знаю, как сильно это будет тормозить работу с файлом...


--
С уважением,
Андрей.
 
Ответить
СообщениеПока ждал ответ нашел "обходное" решение.

Указываю заведомо более широкий диапазон и потом вывожу заведомо пустые столбцы между столбцов с данными.
Фрагмент формулы выглядит вот так
Код
select Col1, Col14, Col5*-1, Col12
где Col14 - пустой столбец.

Т.к. данные находятся в другой книге, то указываю Col... с номером столбца вместо А,В,С.

Это удобнее тем, что нет нужды потом указывать "пустое" название каждого пустого столбца. В предыдущем случае еще и каждый раз добавлял
Код
LABEL '  ' '', '     ' ''
и т.д.

Вот только не знаю, как сильно это будет тормозить работу с файлом...

Автор - book
Дата добавления - 09.02.2019 в 21:46
book Дата: Воскресенье, 23.06.2019, 13:23 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день всем!
Хотелось бы вернуться к теме.

Повторю. Есть исходная таблица, в которой два (два - для примера - реально больше) столбца с данными: "Дата", "Сумма".
Как написать запрос в функции QUERY, чтобы в итоговой таблице был пустой столбец без заголовка: "Дата", пустой столбец, "Сумма".

Может кто-нибудь предложить более изящное решение, чем то, которое указано мной же выше.

Спасибо!


--
С уважением,
Андрей.
 
Ответить
СообщениеДобрый день всем!
Хотелось бы вернуться к теме.

Повторю. Есть исходная таблица, в которой два (два - для примера - реально больше) столбца с данными: "Дата", "Сумма".
Как написать запрос в функции QUERY, чтобы в итоговой таблице был пустой столбец без заголовка: "Дата", пустой столбец, "Сумма".

Может кто-нибудь предложить более изящное решение, чем то, которое указано мной же выше.

Спасибо!

Автор - book
Дата добавления - 23.06.2019 в 13:23
Gustav Дата: Понедельник, 24.06.2019, 13:35 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1727
Репутация: 692 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
В целом, поддерживая Ваше решение из сообщения #4, я бы сделал некую дополнительную манипуляцию - чтобы пустые столбцы не мешались в основной области данных.

Заготовьте "где-то сбоку" пустой диапазон, равный по размерам исходному НЕпустому диапазону. Например, удобно это сделать на другом (пустом) листе в тех же границах, т.е. с тем же адресом. Оба диапазона объедините в составной диапазон - в фигурных скобках через обратный слэш. А дальше в SELECT'е перемежайте столбцы первого и второго диапазона, что собственно Вы и делали:

[vba]
Код
=QUERY({'Лист1'!A1:C20 \'Лист2'!A1:C20};"select Col1, Col4, Col2, Col5, Col3, Col6 where Col1 is not null")
[/vba]

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

[vba]
Код
=QUERY({'Лист1'!A1:C20 \ArrayFormula(IF(ISBLANK('Лист1'!A1:C20);"";""))};"select Col1, Col4, Col2, Col5, Col3, Col6 where Col1 is not null")
[/vba]

Вместо радикала:
[vba]
Код
ArrayFormula(IF(ISBLANK('Лист1'!A1:C20);"";""))
[/vba]
можно написать что-нибудь в том же духе, но покороче:
[vba]
Код
ArrayFormula(T(--('Лист1'!A1:C20="")))
[/vba]


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Понедельник, 24.06.2019, 14:01
 
Ответить
СообщениеВ целом, поддерживая Ваше решение из сообщения #4, я бы сделал некую дополнительную манипуляцию - чтобы пустые столбцы не мешались в основной области данных.

Заготовьте "где-то сбоку" пустой диапазон, равный по размерам исходному НЕпустому диапазону. Например, удобно это сделать на другом (пустом) листе в тех же границах, т.е. с тем же адресом. Оба диапазона объедините в составной диапазон - в фигурных скобках через обратный слэш. А дальше в SELECT'е перемежайте столбцы первого и второго диапазона, что собственно Вы и делали:

[vba]
Код
=QUERY({'Лист1'!A1:C20 \'Лист2'!A1:C20};"select Col1, Col4, Col2, Col5, Col3, Col6 where Col1 is not null")
[/vba]

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

[vba]
Код
=QUERY({'Лист1'!A1:C20 \ArrayFormula(IF(ISBLANK('Лист1'!A1:C20);"";""))};"select Col1, Col4, Col2, Col5, Col3, Col6 where Col1 is not null")
[/vba]

Вместо радикала:
[vba]
Код
ArrayFormula(IF(ISBLANK('Лист1'!A1:C20);"";""))
[/vba]
можно написать что-нибудь в том же духе, но покороче:
[vba]
Код
ArrayFormula(T(--('Лист1'!A1:C20="")))
[/vba]

Автор - Gustav
Дата добавления - 24.06.2019 в 13:35
book Дата: Понедельник, 24.06.2019, 20:48 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо Gustav,

можно создать виртуальный массив пустых строк

А сработает ли такая штука? Дело в том, что "исходная" таблица находятся в другом файле, она постоянно дополняется. Поэтому я использую комбинацию двух функций

Код
=query(IMPORTRANGE(ФайлCF;"БанкТочкаРУБ!A3:Y");'Формулы'!$B$5;0)

и задаю диапазон вниз на весь лист A3:Y. А Вы предлагаете его увеличить в два раза...

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

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


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


Сообщение отредактировал book - Понедельник, 24.06.2019, 20:50
 
Ответить
СообщениеСпасибо Gustav,

можно создать виртуальный массив пустых строк

А сработает ли такая штука? Дело в том, что "исходная" таблица находятся в другом файле, она постоянно дополняется. Поэтому я использую комбинацию двух функций

Код
=query(IMPORTRANGE(ФайлCF;"БанкТочкаРУБ!A3:Y");'Формулы'!$B$5;0)

и задаю диапазон вниз на весь лист A3:Y. А Вы предлагаете его увеличить в два раза...

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

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

Автор - book
Дата добавления - 24.06.2019 в 20:48
Gustav Дата: Вторник, 25.06.2019, 11:41 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1727
Репутация: 692 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
А сработает ли такая штука?

Сработает, вот так у меня - работает:
[vba]
Код
=QUERY({IMPORTRANGE("...другой файл...";"Лист1!A1:C20") \ArrayFormula(T(--(IMPORTRANGE("...другой файл...";"Лист1!A1:C20")="")))};"select Col1, Col4, Col2, Col5, Col3, Col6 where Col1 is not null")
[/vba]
и задаю диапазон вниз на весь лист A3:Y. А Вы предлагаете его увеличить в два раза...

И что? Я бы, может быть, поёжился, если бы Вы задали A3:YY (т.е. еще 26 раз по 26 колонок), а лишь в два-то раза - по-моему, ни о чём. Есть единый неделимый предел - максимум 5 млн. ячеек в одном файле. Всё, что меньше - пожалуйста.


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

Сработает, вот так у меня - работает:
[vba]
Код
=QUERY({IMPORTRANGE("...другой файл...";"Лист1!A1:C20") \ArrayFormula(T(--(IMPORTRANGE("...другой файл...";"Лист1!A1:C20")="")))};"select Col1, Col4, Col2, Col5, Col3, Col6 where Col1 is not null")
[/vba]
и задаю диапазон вниз на весь лист A3:Y. А Вы предлагаете его увеличить в два раза...

И что? Я бы, может быть, поёжился, если бы Вы задали A3:YY (т.е. еще 26 раз по 26 колонок), а лишь в два-то раза - по-моему, ни о чём. Есть единый неделимый предел - максимум 5 млн. ячеек в одном файле. Всё, что меньше - пожалуйста.

Автор - Gustav
Дата добавления - 25.06.2019 в 11:41
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Вывод пустых столбцов в запросе QUERY (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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