Страницы: 1
RSS
VBA. Сумирование затрат со всех листов книги, необходима замена формул на макросы
 
Добры день!
Уважаемые форумчане, прошу Вас помочь в решении следующей задачи.

Ежемесячно в книгу excel собирается отчетs по затратам со всех структурных подразделений предприятия. Там же создан лист сводного формата который собирает данные со всех листов с помощью формулы следующего вида:
Код
=СУММЕСЛИ(ДВССЫЛ(D$1&"!B:B");$B14;ДВССЫЛ(D$1&"!C:C"))

Все работает отлично формула дает именно тот результат который необходим, есть одно !НО, конструкция ДВССЫЛ сильно замедляет работу файла (Количество структурных подразделений, сдающих отчет более 50, число строк со статьями затрат более 100).
В связи с этим возникает вопрос, как можно реализовать функционал данной формулы с помощью макроса, что бы в итоге ячейки заполнялись результатами вычислений в виде значений, а не формул. Или может быть вы сможете подсказать другой более изящный вариант решения данной задачи.

Оговорюсь, сводный формат менять нельзя, он утвержден вышестоящей организацией и направляется отчет именно в таком формате!

Заранее спасибо за помощь!
 
Пример
 
Цитата
Количество структурных подразделений, сдающих отчет более 50
Название подразделений в строке 1 Листа1 (Цех1, Цех2 и т.д.) и название соответствующих листов совпадает?
Лист3 тогда должен называться Цех2.
Цитата
число строк со статьями затрат более 100
Это для каждого вида мебели ( Стулья, Столы, Кресла) или количество различной мебели может доходить до 100 ?
 
Цитата
Название подразделений в строке 1 Листа1 (Цех1, Цех2 и т.д.) и название соответствующих листов совпадает?
Да, все верно, названия листов соответствуют названию заголовков в строке 1 на листе 1: Цех1, Цех2, Цех 3 и т.д.[
Для каждого вида мебели количество строк расходов может доходить до 100, самих видов мебели строго 3
Изменено: alver92 - 27.03.2019 09:34:32
 
Цитата
Для каждого вида мебели количество строк расходов может доходить до 100
Эти 100 видов расходов заранее прописаны на сводном листе1 в столбце А или их нужно вытаскивать с каждого листа подразделений?
Расходы для разных подразделений тоже могут быть различны?
 
Расходы заранее прописаны, для всех подразделений список расходов идентичен. Кроме того все статьи расходов прописаны и в форматах сдаваемых структурными подразделениями, даже если они нулевые. (хочу этим сказать что сдаваемые отчеты унифицированы, набор граф и строк одинков для всех подразделений)
 
  Для двух цехов, макрос в стандартный модуль, привяжите к кнопке "Собрать отчеты"
Код
Sub Sbor()
Dim i As Long
'Dim iLastRow As Long
'Dim iLastColumn As Integer
Dim iStolb As Integer
Dim Row_Zatraty As Long
Dim Row_Itogo As Long
Dim KolRowsZatraty As Integer
Dim Row_Stylja As Long
Dim Row_Stoly As Long
Dim Row_Kresla As Long
Dim Nomer as Integer
Application.ScreenUpdating = False
'   iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
'   iLastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
   Range("D18:E27,D33:E41,D47:E55").ClearContents  'очищаем области для данных двух цехов
   'ищем на сводном листе в столбце А слова Статья затрат
   Row_Zatraty = Columns(1).Find("Статья затрат", , xlValues, xlWhole).Row
   'ищем на сводном листе в столбце А слово ИТОГО
   Row_Itogo = Columns(1).Find("ИТОГО", , xlValues, xlWhole).Row
   KolRowsZatraty = Row_Itogo - Row_Zatraty
 
   Row_Stylja = Row_Zatraty + KolRowsZatraty + 4   'нач.строка затрат стулья
   Row_Stoly = Row_Stylja + KolRowsZatraty + 3     'нач.строка затрат столы
   Row_Kresla = Row_Stoly + KolRowsZatraty + 3     'нач.строка затрат кресла
 
 Dim Sht As Worksheet
     For Each Sht In Worksheets
       If Sht.Name <> "Лист1" Then        ' кроме листа1
         With Sht
           Nomer = Mid(Sht.Name, 4)
           iStolb = 3 + Nomer
       .Range("C2:C" & 1 + KolRowsZatraty).Copy Cells(Row_Stylja, iStolb) 'стулья
       .Range("D2:D" & 1 + KolRowsZatraty).Copy Cells(Row_Stoly, iStolb)    'столы
       .Range("E2:E" & 1 + KolRowsZatraty).Copy Cells(Row_Kresla, iStolb)   'кресла
         End With
       End If
     Next
 Application.ScreenUpdating = True
 End Sub
Изменено: Kuzmich - 27.03.2019 23:39:19 (подсократил код)
 
Kuzmich, Доброе утро!

Большое Вам спасибо!
 
Насколько я понял, макрос копирует диапазоны из листов с отчетами подразделений (Цех1, Цех2 и т.д. вместе с межблочными формулами и форматированием и вставляет их в соответствующие столбцы и ячейки на сводном листе.
Можно ли скорретировать код макроса, что бы копировались не диапазоны, а значения по условию (при сопадении кодов статей затрат) и не убивал межблочные формулы и форматирование в сводном листе?
 
Если строк не более 100, то зачем в функции такой интервалище указывать?
Она из-за этого долго работает. Попробуйте вот такую формулу:
Код
=СУММЕСЛИ(ДВССЫЛ(D$1&"!B1:B150");$B14;ДВССЫЛ(D$1&"!C1:C150"))
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Михаил Лебедев, Добрый день! Размер реального рабочего формата составляет 636 строк Х 57 столбцов, так что ограничение диапазона проверяемых и суммируемых ячеек коренныи образом не влияет на работоспособность файла, вся проблемма в ДВССЫЛ, очень полезный инструмент в плане функциональности, но тяжелый
Изменено: alver92 - 28.03.2019 12:58:43
 
Цитата
alver92 написал:
636 строк Х 57 столбцов
1048576 строк / 636 строк = 1648 - это во сколько раз диапазон, обрабатываемый формулами, больше, чем Ваш рабочий диапазон.
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
Страницы: 1
Наверх