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

Excel 2016
Добрый день, уважаемые форумчане!

Есть несколько таблиц (файлов), из которых собирается отчет по банку. В этих исходных таблицах отсутствует один параметр (столбец) – «Группа Статей Бюджета», – который необходим в итоговом сводном отчете.

Вопрос:
Можно ли без макросов(!) настроить запрос QUERY так, чтобы он выполнял функцию ВПР?

Или
Добавить недостающий столбец в исходные данные? (Понимаю, что это проще, но хотелось бы этого избежать).

Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…

https://docs.google.com/spreads....sharing

Спасибо.


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

Есть несколько таблиц (файлов), из которых собирается отчет по банку. В этих исходных таблицах отсутствует один параметр (столбец) – «Группа Статей Бюджета», – который необходим в итоговом сводном отчете.

Вопрос:
Можно ли без макросов(!) настроить запрос QUERY так, чтобы он выполнял функцию ВПР?

Или
Добавить недостающий столбец в исходные данные? (Понимаю, что это проще, но хотелось бы этого избежать).

Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…

https://docs.google.com/spreads....sharing

Спасибо.

Автор - book
Дата добавления - 31.05.2019 в 11:37
Kashimirush Дата: Среда, 28.08.2019, 09:14 | Сообщение № 2
Группа: Пользователи
Ранг: Участник
Сообщений: 78
Репутация: 5 ±
Замечаний: 0% ±

Excel 2010
У себя решил данный вопрос написанием функции ВПР через формулу массива
Если этот ваш комментарий:
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…

О том что нет автоматизации когда нужно допротягивать формулу.
В вашем примере я бы написал в столбец H2:


Работа, работа, перейди на Федота...
 
Ответить
СообщениеУ себя решил данный вопрос написанием функции ВПР через формулу массива
Если этот ваш комментарий:
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…

О том что нет автоматизации когда нужно допротягивать формулу.
В вашем примере я бы написал в столбец H2:

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

Excel 2016
Спасибо Kashimirush !

Вы правы - я говорил о том, что надо было бы протягивать формулу.

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

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

Еще раз спасибо.


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

Вы правы - я говорил о том, что надо было бы протягивать формулу.

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

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

Еще раз спасибо.

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

Excel 2010
book,
Работает так:
{} - Означает что внутри массив данных, через точку с запятой ставятся данные которые массив будет выводить в строки, можно было обойтись и без этих скобок, тогда надо было ставить формулу в ячейку Н3 - что не удобно т.к. это поле с данными , а его иногда нужно сортировать и т.д.
Допустим пример простой {1;2;3;4} выведет 1 в первой строчке, 2 во второй , 3 в третьей и т.д. - поэтому первый аргумент в этой строчке название вашего столбца.
Далее начнем по порядку, простое использование ВПР, формула выглядела бы так: VLOOKUP(F3;A3:B8;2;0) - она искала бы в диапазоне А3:В8, только значение из ячейки F3, а вам нужно отслеживать все значения в диапазоне F3:F, если написать:
VLOOKUP(F3:F;A3:B8;2;0) - ячейка выдаст ошибку, для работы с массивом вводных аргументов требуется формулу записать как формулу массива для этого используется функция ArrayFormula.
Если записать формулу вида ArrayFormula(VLOOKUP(F3:F;A3:B8;2;0)) - она выдаст массив данных, в котором использует ВПР для всех значений в диапазоне F3:F.
Но для пустых ячеек и тех ячеек которые не содержат данные из диапазона A3:B8 - формула выдаст ошибку, поскольку ВПР ничего не найдет.
Поэтому для более удобной визуализации я скрыл ошибочные значения ВПРа функцией iferror (еслиошибка) - т.е. если ошибка в ВПР она возвращает в ячеку пробел, либо значение которое вы сами можете прописать в кавычках.
Понятно объяснил? ХД
Забегая вперед у функций массивов нет возможности отображения данных вручную, т.е. если попытаетесь вписать самостоятельно статью затрат - формула выдаст ошибку и удалит все выводимые значения.


Работа, работа, перейди на Федота...
 
Ответить
Сообщениеbook,
Работает так:
{} - Означает что внутри массив данных, через точку с запятой ставятся данные которые массив будет выводить в строки, можно было обойтись и без этих скобок, тогда надо было ставить формулу в ячейку Н3 - что не удобно т.к. это поле с данными , а его иногда нужно сортировать и т.д.
Допустим пример простой {1;2;3;4} выведет 1 в первой строчке, 2 во второй , 3 в третьей и т.д. - поэтому первый аргумент в этой строчке название вашего столбца.
Далее начнем по порядку, простое использование ВПР, формула выглядела бы так: VLOOKUP(F3;A3:B8;2;0) - она искала бы в диапазоне А3:В8, только значение из ячейки F3, а вам нужно отслеживать все значения в диапазоне F3:F, если написать:
VLOOKUP(F3:F;A3:B8;2;0) - ячейка выдаст ошибку, для работы с массивом вводных аргументов требуется формулу записать как формулу массива для этого используется функция ArrayFormula.
Если записать формулу вида ArrayFormula(VLOOKUP(F3:F;A3:B8;2;0)) - она выдаст массив данных, в котором использует ВПР для всех значений в диапазоне F3:F.
Но для пустых ячеек и тех ячеек которые не содержат данные из диапазона A3:B8 - формула выдаст ошибку, поскольку ВПР ничего не найдет.
Поэтому для более удобной визуализации я скрыл ошибочные значения ВПРа функцией iferror (еслиошибка) - т.е. если ошибка в ВПР она возвращает в ячеку пробел, либо значение которое вы сами можете прописать в кавычках.
Понятно объяснил? ХД
Забегая вперед у функций массивов нет возможности отображения данных вручную, т.е. если попытаетесь вписать самостоятельно статью затрат - формула выдаст ошибку и удалит все выводимые значения.

Автор - Kashimirush
Дата добавления - 28.08.2019 в 15:44
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Вставить ВПР в запрос QUERY для Гугл Таблиц (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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