Есть несколько таблиц (файлов), из которых собирается отчет по банку. В этих исходных таблицах отсутствует один параметр (столбец) – «Группа Статей Бюджета», – который необходим в итоговом сводном отчете.
Вопрос: Можно ли без макросов(!) настроить запрос QUERY так, чтобы он выполнял функцию ВПР?
Или Добавить недостающий столбец в исходные данные? (Понимаю, что это проще, но хотелось бы этого избежать).
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
Есть несколько таблиц (файлов), из которых собирается отчет по банку. В этих исходных таблицах отсутствует один параметр (столбец) – «Группа Статей Бюджета», – который необходим в итоговом сводном отчете.
Вопрос: Можно ли без макросов(!) настроить запрос QUERY так, чтобы он выполнял функцию ВПР?
Или Добавить недостающий столбец в исходные данные? (Понимаю, что это проще, но хотелось бы этого избежать).
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
О том что нет автоматизации когда нужно допротягивать формулу. В вашем примере я бы написал в столбец H2:
{"Группа статей бюджета";ArrayFormula(iferror(VLOOKUP(F3:F;A3:B8;2;0);""))}
У себя решил данный вопрос написанием функции ВПР через формулу массива Если этот ваш комментарий:
Использовать ВПР в итоговом отчете – сложно, т.к. в нем несколько тысяч строк и он постоянно дополняется новыми строками. Автоматизация сходит на «нет» тогда…
О том что нет автоматизации когда нужно допротягивать формулу. В вашем примере я бы написал в столбец H2:
{"Группа статей бюджета";ArrayFormula(iferror(VLOOKUP(F3:F;A3:B8;2;0);""))}
Вы правы - я говорил о том, что надо было бы протягивать формулу.
Ваша формула работает на примере. Хотя я пока не понял, как именно она работает. И раньше не сталкивался с такой конструкцией, где первым аргументом идет название столбца. Потом попробую на реальных данных...
Для решения своей задачи, мне пришлось таки добавить столбец "Группа статей бюджета" в исходные данные...
Еще раз спасибо.
Спасибо Kashimirush !
Вы правы - я говорил о том, что надо было бы протягивать формулу.
Ваша формула работает на примере. Хотя я пока не понял, как именно она работает. И раньше не сталкивался с такой конструкцией, где первым аргументом идет название столбца. Потом попробую на реальных данных...
Для решения своей задачи, мне пришлось таки добавить столбец "Группа статей бюджета" в исходные данные...
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
Kashimirush, добрый день! Примите, пожалуйста, очень запоздалое спасибо. Сегодня опять стоит задача объединения таблиц и поиск выдал забытую мной тему…
Kashimirush, добрый день! Примите, пожалуйста, очень запоздалое спасибо. Сегодня опять стоит задача объединения таблиц и поиск выдал забытую мной тему…book
Сделано. PS Вопрос, обозначенный в теме еще актуален. Узнав немного про слияние запросов (таблиц) в Power Query Excel, теперь хочу такого и от Гугл Таблиц. Пока пришел к выводу, что чудес не бывает...
Сделано. PS Вопрос, обозначенный в теме еще актуален. Узнав немного про слияние запросов (таблиц) в Power Query Excel, теперь хочу такого и от Гугл Таблиц. Пока пришел к выводу, что чудес не бывает...book
-- С уважением, Андрей.
Сообщение отредактировал book - Вторник, 03.08.2021, 09:33
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
