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

Вход

Регистрация

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

 

= Мир MS Excel/Вставить ВПР в запрос QUERY для Гугл Таблиц - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Вставить ВПР в запрос QUERY для Гугл Таблиц (Формулы/Formulas)
Вставить ВПР в запрос QUERY для Гугл Таблиц
book Дата: Пятница, 31.05.2019, 11:37 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Репутация: 8 ±
Замечаний: 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

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

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


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

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

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

Excel 2016
Спасибо Kashimirush !

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

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

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

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


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

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

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

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

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

Автор - book
Дата добавления - 28.08.2019 в 13:13
Kashimirush Дата: Среда, 28.08.2019, 15:44 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 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
book Дата: Понедельник, 02.08.2021, 15:17 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
Kashimirush, добрый день!
Примите, пожалуйста, очень запоздалое спасибо.
Сегодня опять стоит задача объединения таблиц и поиск выдал забытую мной тему…


--
С уважением,
Андрей.
 
Ответить
СообщениеKashimirush, добрый день!
Примите, пожалуйста, очень запоздалое спасибо.
Сегодня опять стоит задача объединения таблиц и поиск выдал забытую мной тему…

Автор - book
Дата добавления - 02.08.2021 в 15:17
Kashimirush Дата: Вторник, 03.08.2021, 08:50 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
book, Благодарности плюсом в карму, звучат гораздо благодарнее)))
К сообщению приложен файл: 2467839.jpg (8.3 Kb)


Работа, работа, перейди на Федота...
 
Ответить
Сообщениеbook, Благодарности плюсом в карму, звучат гораздо благодарнее)))

Автор - Kashimirush
Дата добавления - 03.08.2021 в 08:50
book Дата: Вторник, 03.08.2021, 09:22 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
Благодарности плюсом в карму
Сделано. PS Вопрос, обозначенный в теме еще актуален. Узнав немного про слияние запросов (таблиц) в Power Query Excel, теперь хочу такого и от Гугл Таблиц. Пока пришел к выводу, что чудес не бывает...


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


Сообщение отредактировал book - Вторник, 03.08.2021, 09:33
 
Ответить
Сообщение
Благодарности плюсом в карму
Сделано. PS Вопрос, обозначенный в теме еще актуален. Узнав немного про слияние запросов (таблиц) в Power Query Excel, теперь хочу такого и от Гугл Таблиц. Пока пришел к выводу, что чудес не бывает...

Автор - book
Дата добавления - 03.08.2021 в 09:22
Pelena Дата: Вторник, 03.08.2021, 09:35 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19167
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Kashimirush, Дамир, при всём уважении, читаем Правила п.5p


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеKashimirush, Дамир, при всём уважении, читаем Правила п.5p

Автор - Pelena
Дата добавления - 03.08.2021 в 09:35
Kashimirush Дата: Вторник, 03.08.2021, 12:47 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
Pelena, Никакой накртуки, чисто ради справедливости, напоминаю :D


Работа, работа, перейди на Федота...
 
Ответить
СообщениеPelena, Никакой накртуки, чисто ради справедливости, напоминаю :D

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

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