Добрый день. Не могли бы помочь с vba или массивами. Исходные данные такие:
1. Есть вкладка: "Тест". В данную вкладку должны суммироваться все данные из вкладок по названию месяцев (Январь; Февраль; Март и тд)
2. Есть вкладка "Январь" (затем будут созданы вкладки по другим месяцам) В данную вкладку должны суммироваться все данные из вкладок по неделям (или дням). Количество вкладок равно количеству дней в месяце.
3. Какие сложности(хотелки): в каждой вкладке должны быть ФИО менеджеров (сейчас указала как Менеджер 1; Менеджер 2 и тд), надо чтобы из каждой вкладки суммировались именно данные по ФИО менеджера пример по формуле экселя:
=сумма(='01.01'!C8+'02.01'!C8+'03.01'!C8......) - но тут она прописана в ручную и ячейке определенного менеджера, НО бывает что порядок ФИО менеджеров меняется и в ручную необходимо переписывать форуму.
Не до конца понял Вашу хотелку с менеджерами, но если структура листов всегда одинакова и необходимо просто консолидировать данные на отдельных листах, то можно использовать такой подход (см. вложение). Добавил формулы в ячейки E8 на разных листах.
Aleksei_Zhigulin написал: но если структура листов всегда одинаков
К сожалению структура может меняться...
Спасибо больше за пример! Им можно пользоваться как вариант))) Но хотелось бы сделать макрос чтобы файл не был тяжелым, потому что в нем будет более 365 вкладок с формулами. Примерно 1 файл на месяц весит около 7мб..
Alex_I_S,В файле 2018 пользуюсь формулой "сумма" - но файл за 1 месяц становиться тяжелым и медленным из-за формул (примерно вес файла 7мб)
Kuzmich, я ищу макрос чтобы не делать именно такую структура в формулах, т.е. еще более автоматизировать файл
По хорошему Вам стоит задуматься о том, чтобы изменить порядок ввода данных в Вашу базу: 1. если Вы сумеете организовать один лист для ввода данных 2. то вывод отчетов (ежедневных, понедельных, ежемесячных и годовых) можно будет организовать всего на четырех листах (вместо 360+)...
AnastasiaSchaste написал: не вижу пока что нужных там инструментов
ИМХО, PQ для подобной задачи использовать можно (а может быть и нужно), но оптимальнее это делать при изменении подхода к подготовке данных. В первую очередь, чтобы листы за каждый день хранились в отдельных файлах.
Имею в виду использовать PQ для консолидации данных, а дальше подтягивать формулами (например, СУММЕСЛИМН , GetPivotData или формулы кубов) в ту форму отчёта, какую хотите.
Что Вам необходимо - Вам, конечно, виднее. Только непонятен Ваш вывод о большом количестве времени на обновление отчёта. Однажды настроив модель в PQ потом просто обновляете запрос и всё.
Например, для ячейки С8 листа Январь вполне подойдет такая формула: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ({"01.01":"02.01":"03.01"}&"!B3:B100");B8;ДВССЫЛ({"01.01":"02.01":"03.01"}&"!C3:C100"))) Сделана для трех листов. Но в статье расписано как можно использовать отдельные ячейки для указания имен листов. Или иной подход: =СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ(СТРОКА(1:3);"00")&".01!B3:B100");B8;ДВССЫЛ(ТЕКСТ(СТРОКА(1:3);"00")&".01!C3:C100"))) СТРОКА(1:3) - это даты от 1 до 3. Т.е. если указать вместо 3 - 31, то будут суммироваться все листы с 01.01 по 31.01.
А уж для суммирования со всех месяцев можно обойтись и простым перечислением(прям как в статье и описано).
Aleksei_Zhigulin написал: Однажды настроив модель в PQ потом просто обновляете запрос и всё.
Добрый день. Поясню почему у меня такой вывод, для предоставления ежедневной отчетности необходимо заполнять за вчерашний день по трем проектам, по формуле на текущий момент момент сводится: неделя, месяц, тест (данные за большой период). Все исходные данные берутся из 5-ти файлов от различных отделов и сводится в одном файле. По PQ это еще одна форма данного отчета.. поэтому я думаю на текущий момент он не совсем подходит для автоматизирования человеческих ресурсов по отчетности..
Kuzmich написал: Ищите цикл по листам Цикл по менеджерам
Представляю себе, сколько времени всё это великолепие, да с Find'ом в самом центре, будет отрабатывать на 365 листах, умноженных на фиг знает сколько менеджеров...
Анастасия, я ни хрена не понял. Вы говорите о том что вам мешает то, что в pq вы фактически создадите отчёт, который отличается от принятых в компании?.
Если да, то никто вам не мешает формулами вытащить из pq результат и оформить его в вашем шаблоне привычной формы.
Формулы для таких задач это даже не позапрошлый, а каменный век.
В форму данные из pq подставляются формулами. То есть все вычисления в pq, а формулы нужны чтобы натянуть результат на существующий шаблон.
Кстати, что-то я раньше не проверял, если в формуле использовать имя connection only запроса pq, будет ли это работать? Люди, проверьте кто нибудь пожалуйста, до ноута доберусь вечером.
Кстати, как я заметил, руководятлы и бухгалтерия всех мастей обожают делать таблицы, максимально далёкие от первой нормальной формы (объединяют ячейки цветами кодиоуют информацию, разносят данные по листу и т. д.) , чем только топят себя и других в болоте тупой, ненужной работы.
StoTisteg,видел это еще очень сжатый вариант в идеале хочет видеть все и сразу, желательно все чтобы было в хорошем формате и распечатано на 1-ом листе.
Цитата
Alexey_Spb написал: Формулы для таких задач это даже не позапрошлый, а каменный век.
Я согласна с вами. Я уже в целом в другой компании ушла от формирования отчетности в экселе (все было в 1С и других CRM системах..), но тут все не так... Пытаюсь перевести и тут отчетность в CRM-системы... но компания считает что это не в приоритете...
Цитата
Alexey_Spb написал: Кстати, как я заметил, руководятлы и бухгалтерия всех мастей обожают делать таблицы, максимально далёкие от первой нормальной формы
Если у вас в исходных файлах тоже формы, а не объекты-таблицы, то это будет big problem для pq, тогда только vb.
Цитата
AnastasiaSchaste написал: но компания считает что это не в приоритете...
Для маленькой фирмы это может быть разумно. В этом случае вы можете сделать нормальную систему отчёта для Excel. Если она будет удобной и будет решать задачи, которые перед ней стоят, ей будут пользоваться. На данный момент отчётность маленькой фирмы может быть реализована средствами Excel.
Аналогичная форма, у меня просто пример по одному проекту (т.е. 4 разных файла по одному такому проекту мне утром присылают разные отделы). Еще данный файл содержит 2 разные формы по 2-ум разным проектам..
Я почему и склоняюсь к тому что vba из-за формы.. ее не изменить.. Все выгрузки (читайте полотна), я формирую через сводные как минимум..
Цитата
Alexey_Spb написал: Для маленькой фирмы это может быть разумно.
фирма разбросана по РФ среднее число сотрудников 5 000....
AnastasiaSchaste написал: для предоставления ежедневной отчетности необходимо заполнять за вчерашний день по трем проектам, по формуле на текущий момент момент сводится: неделя, месяц, тест (данные за большой период). Все исходные данные берутся из 5-ти файлов от различных отделов и сводится в одном файле. По PQ это еще одна форма данного отчета.. поэтому я думаю на текущий момент он не совсем подходит для автоматизирования человеческих ресурсов по отчетности..
Подозреваю, Вы не совсем понимаете, для чего нужен PQ в данной задаче. Он нужен как раз для того, чтобы избавить Вас от ручной обработки данных. Вы просто на входе получаете "сырые" данные, запросами PQ они обрабатываются и выводятся на лист Excel или в модель данных. Поскольку у Вас особая форма отчёта, подтягиваете в неё данные формулами. Т.е. общая мысль - один раз настраиваете отчёт, дальше просто нажимаете кнопку и всё обновляется.