Страницы: 1 2 След.
RSS
Макрос суммирование ячеек из множества вкладок
 
Добрый день.
Не могли бы помочь с vba или массивами.
Исходные данные такие:

1. Есть вкладка: "Тест".
В данную вкладку должны суммироваться все данные из вкладок по названию месяцев (Январь; Февраль; Март и тд)

2. Есть вкладка "Январь" (затем будут созданы вкладки по другим месяцам)
В данную вкладку должны суммироваться все данные из вкладок по неделям (или дням). Количество вкладок равно количеству дней в месяце.

3. Какие сложности(хотелки):
в каждой вкладке должны быть ФИО менеджеров (сейчас указала как Менеджер 1; Менеджер 2 и тд), надо чтобы из каждой вкладки суммировались именно данные по ФИО менеджера пример по формуле экселя:

=сумма(='01.01'!C8+'02.01'!C8+'03.01'!C8......) - но тут она прописана в ручную и ячейке определенного менеджера, НО бывает что порядок ФИО менеджеров меняется и в ручную необходимо переписывать форуму.

Файл с примером - вложила.
Изменено: Анастасия Румянцева - 27.12.2018 17:09:44
 
Цитата
Не могли бы помочь с макросом или vba.
Вы полагаете, что это разные понятия?
 
Kuzmich, спасибо на указание ошибки. Я перепутала массивы с макросами
 
Добрый день!
СУММА() не поможет, но есть другие, которые можно использовать для этого случая, например:
СУММЕСЛИ()
ВПР()
 
Делаете цикл по ФИО и ищете (Find) каждую фамилию на нужных вкладках и суммируете данные в конкретых[ ячейках
 
AnastasiaSchaste,

Не до конца понял Вашу хотелку с менеджерами, но если структура листов всегда одинакова и необходимо просто консолидировать данные на отдельных листах, то можно использовать такой подход (см. вложение). Добавил формулы в ячейки E8 на разных листах.
Изменено: Aleksei_Zhigulin - 27.12.2018 17:14:22
 
Цитата
Aleksei_Zhigulin написал: но если структура листов всегда одинаков
К сожалению структура может меняться...

Спасибо больше за пример! Им можно пользоваться как вариант)))
Но хотелось бы сделать макрос чтобы файл не был тяжелым, потому что в нем будет более 365 вкладок с формулами. Примерно 1 файл на месяц весит около 7мб..

Alex_I_S,В файле 2018 пользуюсь формулой "сумма" - но файл за 1 месяц становиться тяжелым и медленным из-за формул (примерно вес файла 7мб)

Kuzmich, я ищу макрос чтобы не делать именно такую структура в формулах, т.е. еще более автоматизировать файл  
 
Ищите цикл по листам
Цикл по менеджерам
Поиск - Find
 
Анастасия Румянцева,

Power Query рассматривали как вариант?
 
Цитата
Анастасия Румянцева написал:
в нем будет более 365 вкладок с формулами
Скорее всего можно как-то иначе устроить структуру файла, что и решение сделает более простым и эффективным
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
По хорошему Вам стоит задуматься о том, чтобы изменить порядок ввода данных в Вашу базу:
1. если Вы сумеете организовать один лист для ввода данных
2. то вывод отчетов (ежедневных, понедельных, ежемесячных и годовых) можно будет организовать всего на четырех листах (вместо 360+)...
 
Цитата
IKor написал:
можно будет организовать всего на четырех листах (вместо 360+)...
Или даже на 1 листе в 4 сводных таблицах
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
IKor, Dyroff,к сожалению формам согласован руководящим лицом компании. Формат не изменим :(

Aleksei_Zhigulin, не вижу пока что нужных там инструментов
Изменено: AnastasiaSchaste - 27.12.2018 17:54:48
 
Цитата
AnastasiaSchaste написал: не вижу пока что нужных там инструментов
ИМХО, PQ для подобной задачи использовать можно (а может быть и нужно), но оптимальнее это делать при изменении подхода к подготовке данных. В первую очередь, чтобы листы за каждый день хранились в отдельных файлах.
 
Aleksei_Zhigulin, Посмотрела (честно бегло) мне нужно чтобы в моей форме отчета были. А там предлагали сводные данные в другой форме  
Изменено: AnastasiaSchaste - 27.12.2018 18:05:41
 
AnastasiaSchaste,

Имею в виду использовать PQ для консолидации данных, а дальше подтягивать формулами (например, СУММЕСЛИМН , GetPivotData или формулы кубов) в ту форму отчёта, какую хотите.
 
Цитата
Aleksei_Zhigulin написал:
использовать PQ для консолидации данных
Это не есть то, что мне необходимо.
Аналогично по времени получиться что я буду делать еженедельные/ежемесячные вкладки с формулами сумма...  
 
Цитата
AnastasiaSchaste написал:
Это не есть то, что мне необходимо.
Что Вам необходимо - Вам, конечно, виднее. Только непонятен Ваш вывод о большом количестве времени на обновление отчёта. Однажды настроив модель в 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.

А уж для суммирования со всех месяцев можно обойтись и простым перечислением(прям как в статье и описано).
Изменено: Дмитрий(The_Prist) Щербаков - 27.12.2018 19:16:31
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
AnastasiaSchaste, то, что Вы называете вкладками, в Excel (а мы на форуме по Excel) принято называть листами.
 
... а то, что называете "помочь" на самом деле "сделайте за меня".
 
Цитата
Aleksei_Zhigulin написал:
Однажды настроив модель в PQ потом просто обновляете запрос и всё.
Добрый день. Поясню почему у меня такой вывод, для предоставления ежедневной отчетности необходимо заполнять за вчерашний день по трем проектам, по формуле на текущий момент момент сводится: неделя, месяц, тест (данные за большой период). Все исходные данные берутся из 5-ти файлов от различных отделов и сводится в одном файле.
По PQ это еще одна форма данного отчета.. поэтому я думаю на текущий момент он не совсем подходит для автоматизирования человеческих ресурсов по отчетности..

Дмитрий(The_Prist) Щербаков, спасибо большое, пойду изучать :)

Anchoret, Юрий М, и вам хорошего дня :)
 
Цитата
Kuzmich написал:
Ищите цикл по листам
Цикл по менеджерам
Представляю себе, сколько времени всё это великолепие, да с Find'ом в самом центре, будет отрабатывать на 365 листах, умноженных на фиг знает сколько менеджеров...
Изменено: StoTisteg - 28.12.2018 10:44:39
 
Анастасия, я ни хрена не понял. Вы говорите о том что вам мешает то, что в pq вы фактически создадите отчёт, который отличается от принятых в компании?.

Если да, то никто вам не мешает формулами вытащить из pq результат и оформить его в вашем шаблоне привычной формы.

Формулы для таких задач это даже не позапрошлый,  а каменный век.  
 
Цитата
AnastasiaSchaste написал:
формам согласован руководящим лицом компании
Интересно, этот руководятел сам-то видел согласованный им "формам"?...
 
В форму данные из pq подставляются формулами. То есть все вычисления в pq, а формулы нужны чтобы натянуть результат на существующий шаблон.

Кстати, что-то я раньше не проверял, если в формуле использовать имя connection only запроса pq, будет ли это работать? Люди, проверьте кто нибудь пожалуйста, до ноута доберусь вечером.

Кстати, как я заметил, руководятлы и бухгалтерия всех мастей обожают делать таблицы, максимально далёкие от первой нормальной формы (объединяют ячейки  цветами кодиоуют информацию, разносят данные по листу и т. д.) , чем только топят себя и других в болоте тупой, ненужной работы.
 
StoTisteg,видел :( это еще очень сжатый вариант :( в идеале хочет видеть все и сразу, желательно все чтобы было в хорошем формате и распечатано на 1-ом листе.

Цитата
Alexey_Spb написал:
Формулы для таких задач это даже не позапрошлый,  а каменный век.
Я согласна с вами. Я уже в целом в другой компании ушла от формирования отчетности в экселе (все было в 1С и других CRM системах..), но тут все не так...
Пытаюсь перевести и тут отчетность в CRM-системы... но компания считает что это не в приоритете...
Цитата
Alexey_Spb написал:
Кстати, как я заметил, руководятлы и бухгалтерия всех мастей обожают делать таблицы, максимально далёкие от первой нормальной формы
синдром: "создания видимости работы..."
 
Если у вас в исходных файлах тоже формы, а не объекты-таблицы, то это будет big problem для pq, тогда только vb.  

Цитата
AnastasiaSchaste написал: но компания считает что это не в приоритете...
Для маленькой фирмы это может быть разумно. В этом случае вы можете сделать нормальную систему отчёта для  Excel.
Если она будет удобной и будет решать задачи, которые перед ней стоят, ей будут пользоваться.
На данный момент отчётность маленькой фирмы может быть реализована средствами Excel.  
 
Цитата
Alexey_Spb написал:
исходных файлах тоже формы
Аналогичная форма, у меня просто пример по одному проекту (т.е. 4 разных файла по одному такому проекту мне утром присылают разные отделы).
Еще данный файл содержит 2 разные формы по 2-ум разным проектам..

Я почему и склоняюсь к тому что vba из-за формы.. ее не изменить..
Все выгрузки (читайте полотна), я формирую через сводные как минимум..
Цитата
Alexey_Spb написал:
Для маленькой фирмы это может быть разумно.
фирма разбросана по РФ среднее число сотрудников 5 000....
 
Цитата
AnastasiaSchaste написал:
для предоставления ежедневной отчетности необходимо заполнять за вчерашний день по трем проектам, по формуле на текущий момент момент сводится: неделя, месяц, тест (данные за большой период). Все исходные данные берутся из 5-ти файлов от различных отделов и сводится в одном файле. По PQ это еще одна форма данного отчета.. поэтому я думаю на текущий момент он не совсем подходит для автоматизирования человеческих ресурсов по отчетности..
Подозреваю, Вы не совсем понимаете, для чего нужен PQ в данной задаче. Он нужен как раз для того, чтобы избавить Вас от ручной обработки данных. Вы просто на входе получаете "сырые" данные, запросами PQ они обрабатываются и выводятся на лист Excel или в модель данных. Поскольку у Вас особая форма отчёта, подтягиваете в неё данные формулами. Т.е. общая мысль - один раз настраиваете отчёт, дальше просто нажимаете кнопку и всё обновляется.
Страницы: 1 2 След.
Наверх