Страницы: 1
RSS
Сумма по условию из разных файлов ежемесячно макросом
 
Добрый день Дорогие Форумчане! :)
Мне необходимо каждый месяц подсчитывать суммы по материалам из трех файлов, которые называются всегда по-разному.
Я решил написать код для себя и моих коллег, чтобы в дальнейшем не приходилось мучиться с написанием формулы или подсчетом в ручную. Воспользовавшись поиском, я написал код, который вставляет формулу СУММПРОИЗВ. (Но на деле мне необходимы только значения, пытался изменить код, но ничего не вышло) Запустив данный код с января по август, мой файл стал тормозить и весить 18мб  :(  Я пробовал отключать все возможное в начале кода и включать в конце, но это не помогло.
Я сделал пример очень коротким, но оригинал - это длинная портянка.
Помогите пожалуйста сделать адекватный код.

Заранее всех благодарю за помощь!
Код
Sub Июль()
    Range("K6").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC3="""","""",SUMPRODUCT(ISNUMBER(SEARCH(""*""&RC3&""*""," & [K2] & "!R2C8:R25000C8))*(" & [K2] & "!R2C1:R25000C1=RC1)," & [K2] & "!R2C17:R25000C17))"
    Range("K6").Select
    Selection.Copy
    Range("K6:K15,K17:K26,K28:K37,K39:K48,K50:K59,K61:K70").Select
    ActiveSheet.Paste

    Range("K105").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC3="""","""",SUMPRODUCT(ISNUMBER(SEARCH(""*""&RC3&""*""," & [K4] & "!R2C13:R25000C13))*(" & [K4] & "!R2C1:R25000C1=RC1)," & [K4] & "!R2C30:R25000C30))"
    Range("K105").Select
    Selection.Copy
    Range("K105:K114,K116:K125,K127:K136").Select
    ActiveSheet.Paste

    Range("Y105").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC17="""","""",SUMPRODUCT(ISNUMBER(SEARCH(""*""&RC17&""*""," & [K4] & "!R2C13:R25000C13))*(" & [K4] & "!R2C1:R25000C1=RC1)," & [K4] & "!R2C30:R25000C30))"
    Range("Y105").Select
    Selection.Copy
    Range("Y105:Y114,Y116:Y125,Y127:Y136").Select
    ActiveSheet.Paste
   
    Range("Y72").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC17="""","""",SUMPRODUCT(ISNUMBER(SEARCH(""*""&RC17&""*""," & [K3] & "!R2C13:R25000C13))*(" & [K3] & "!R2C1:R25000C1=RC1)," & [K3] & "!R2C30:R25000C30))"
    Range("Y72").Select
    Selection.Copy
    Range("Y72:Y81,Y83:Y92,Y94:Y103,Y138:Y147,Y149:Y158").Select
    ActiveSheet.Paste
End Sub
Изменено: evgeniygeo - 09.10.2019 07:25:08
 
А для чего используете формулы в макросе. Делайте все расчеты внутри кода, а на лист выкладывайте уже обработанные данные, и ничего тормозить не будет.
Файлы можно класть в отдельную папку, и указывать путь к ней через диалоговое окно. Если структура файлов одинаковая, то не вижу проблем загнать данные по каждому файлу в массив в массиве се пересчитать, а затем выложить все на лист. Поерьте это будет в разы быстрей, чем городить огород из формул в коде. А "Select" и "Activate", еще более тормозят работу кода, лучше пользоваться конструкцией
Код
With Какая_то_Книга_Какой_то_Лист
    'код
End With
Изменено: Nordheim - 09.10.2019 08:49:44
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,
сделал формулы, потому что совсем не знаю, как написать цикл вместо СУММПРОИЗВ :cry:
Но если даже адаптирую что-то из интернета, не врубаюсь, как сделать цикл с разрывом. (ячейки идут не подряд)
Если Вам не сложно, не могли бы помочь мне?

Нашел под себя вот такой вариант, он работает в таком варианте: :sceptic:
Код
Sub test()
  With ActiveSheet.Range("L6:L15")
    .FormulaR1C1 = "=IF(RC3="""","""",SUMPRODUCT(ISNUMBER(SEARCH(""*""&RC3&""*""," & [L2] & "!R2C8:R25000C8))*(" & [L2] & "!R2C1:R25000C1=RC1)," & [L2] & "!R2C17:R25000C17))"
    .Value = .Value
  End With
End Sub
Но когда я делаю вот так, он не работает :(
Код
Sub test()
  With ActiveSheet.Range("L6:L15,L17:L26")
    .FormulaR1C1 = "=IF(RC3="""","""",SUMPRODUCT(ISNUMBER(SEARCH(""*""&RC3&""*""," & [L2] & "!R2C8:R25000C8))*(" & [L2] & "!R2C1:R25000C1=RC1)," & [L2] & "!R2C17:R25000C17))"
    .Value = .Value
  End With
End Sub
Изменено: evgeniygeo - 09.10.2019 10:14:37
 
Пока к сожалению на работе завал, если будет время посмотрю, но нужны данные как есть и как нужно да и файлы из которых тянутся значения не помешают,Ю хотя бы один. Только не нужно оригиналы, сделайте небольшой пример, нужно понимание структуры.
"Все гениальное просто, а все простое гениально!!!"
 
evgeniygeo, здравствуйте!
Получить расчёт нужно ВНУТРИ каждого файла или в каком-то отдельном?
Организуйте пример файла-источника и файла-назначения (если вставлять нужно в другой файл) с вычисленными данными формулой
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Nordheim, Jack Famous,
необходимо произвести расчет в одном файле-назначения "Пример" из 3х файлов (файл-источник Conso August).
Поставил формулы в файл "Пример", чтобы было понятно.
 
evgeniygeo, не сказал бы, что это пример (куча ненужного), хотя всё лучше, чем ничего…
Итак, задача комплексная (как мне кажется), поэтому вам либо в ветку Работа (платно) или разбивать на самостоятельные темы в этой ветке (бесплатно), а пока вот вам алгоритм, как бы я писал код:
    0. запуск макроса производится из файла, где нужно всё собрать
    1. собираем ключи, по которым будет производится поиск. В примере это столбцы "C" и "Q". Нужны ли "A" и "B" я так и не понял
    2. отображаем диалоговое окно выбора файлов и получаем массив полных путей до выбранных файлов
    3. в цикле открываем каждый файл, просматриваем (неточный поиск) столбец с ключами (в примере это столбец "H", а про столбец "A" непонятно). Если подходит, то запоминаем значение текущей строки из столбца значений (в примере - "Q")
    4. вычисляем всё, что нужно
    5. выгружаем результат в файл-сборник
Изменено: Jack Famous - 09.10.2019 12:53:10
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous,
спасибо и на том, хотя мне это мало, чем помогло. Буду копать дальше.
 
Сумма по условию По какому условию? Нужен ключ-совпадение, по которому собственно и будет строится отчет.
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,
два условия, первое - столбец "№" (R1,R2,R3) и второе - столбец "Поиск" (часть названия материала)
К каждой строчке свое условие.

В ячейке L6 я ищу в диапазонах C6 и A6:
=ЕСЛИ(C6="";"";СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК("*"&C6&"*";'[conso August.XLSX]Лист1'!$H$2:$H$50))*('[conso August.XLSX]Лист1'!$A$2:$A$50=$A6);'[conso August.XLSX]Лист1'!$Q$2:$Q$50))
А в ячейке L7:
=ЕСЛИ(C6="";"";СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК("*"&C7&"*";'[conso August.XLSX]Лист1'!$H$2:$H$50))*('[conso August.XLSX]Лист1'!$A$2:$A$50=$A7);'[conso August.XLSX]Лист1'!$Q$2:$Q$50))
Изменено: evgeniygeo - 09.10.2019 13:39:39
 
По названию файлов как узнать какой месяц?
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,
предполагал, что названия файлов будут писать пользователи отдельно в ячейку.
 
Если пользователи будут писать названия, как программе узнать на какой месяц записывать данные? Тут  нужно либо иметь для каждого месяца свое название файла, либо иметь список, который будет корректироваться (месяц - название файла). Первый вариант более надежный в плане сбора данных, заодно можно проверку на обработку файла поставить, если файла нет в списке допустимых, то показать какого именно и что его требуется переименовать, либо обработать в соответствии указанному месяцу, но это более сложная реализация.
Изменено: Nordheim - 09.10.2019 14:24:09
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,
все верно, пользователи будут ставить имя файла, после его выпуска. Сами файлы будут выкладываться в папки с номерами месяцев.
Расчетный файл будет лежать перед папками с месяцами.
Как пример прикладываю скрин.

Также я думаю, что ссылки на файлы по месяцам будут стоять выше названий месяцев и будет выше некий индикатор, что-то вроде "ДА". И если выше определенного месяца стоит "ДА", то необходимо обновить его.
Как пример прикладываю скрин.
Изменено: evgeniygeo - 09.10.2019 16:02:09
 
Т.е. в одной папке один файл? Какой смысл в записи файлов обведенных на скрине? неужели эту информацию кто то будет смотреть? По сути если в каждой папке всего 3 файла, то нет никакого смысла эти файлы записывать. Но пути к папкам должны быть жестко прописаны в коде. Любое отклонение и, либо будет ошибка, либо отчет буде не корректен.
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim, в каждой папке три файла, которые каждый месяц имеют разные названия, которые пользователи будут записывать в какой-либо ячейке.
 
Цитата
evgeniygeo написал:
которые пользователи будут записывать в какой-либо ячейке
Для чего?
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,мне казалось, что так проще найти сам файл для кода. Т.к. путь определенный до папки с месяцами, а сами файлы имеют разные названия.
 
Сам файл искать проще программно, нежели его внесет пользователь в лист.
Вставьте файл в папку с вашим файлом "Пример.xlsm", и запустите макрос кнопкой
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,
прошу прощения, но ничего не происходит при нажатии.
 
Если ничего не происходит зн. либо в папке с файлом нет других папок, либо эти папки пустые
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,
понял, отрабатывает правильно.
 
Это я к тому что не нужно ничего писать руками, остается брать данные из этих файлов, и по критериям делать пересчет выгружая итоги на лист.
По мне так это очень трудоемко для одной темы. Это получается целая комплексная задача.
"Все гениальное просто, а все простое гениально!!!"
Страницы: 1
Наверх