Добрый день Дорогие Форумчане! Мне необходимо каждый месяц подсчитывать суммы по материалам из трех файлов, которые называются всегда по-разному. Я решил написать код для себя и моих коллег, чтобы в дальнейшем не приходилось мучиться с написанием формулы или подсчетом в ручную. Воспользовавшись поиском, я написал код, который вставляет формулу СУММПРОИЗВ. (Но на деле мне необходимы только значения, пытался изменить код, но ничего не вышло) Запустив данный код с января по август, мой файл стал тормозить и весить 18мб Я пробовал отключать все возможное в начале кода и включать в конце, но это не помогло. Я сделал пример очень коротким, но оригинал - это длинная портянка. Помогите пожалуйста сделать адекватный код.
А для чего используете формулы в макросе. Делайте все расчеты внутри кода, а на лист выкладывайте уже обработанные данные, и ничего тормозить не будет. Файлы можно класть в отдельную папку, и указывать путь к ней через диалоговое окно. Если структура файлов одинаковая, то не вижу проблем загнать данные по каждому файлу в массив в массиве се пересчитать, а затем выложить все на лист. Поерьте это будет в разы быстрей, чем городить огород из формул в коде. А "Select" и "Activate", еще более тормозят работу кода, лучше пользоваться конструкцией
Nordheim, сделал формулы, потому что совсем не знаю, как написать цикл вместо СУММПРОИЗВ Но если даже адаптирую что-то из интернета, не врубаюсь, как сделать цикл с разрывом. (ячейки идут не подряд) Если Вам не сложно, не могли бы помочь мне?
Нашел под себя вот такой вариант, он работает в таком варианте:
Код
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, здравствуйте! Получить расчёт нужно ВНУТРИ каждого файла или в каком-то отдельном? Организуйте пример файла-источника и файла-назначения (если вставлять нужно в другой файл) с вычисленными данными формулой
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Nordheim, Jack Famous, необходимо произвести расчет в одном файле-назначения "Пример" из 3х файлов (файл-источник Conso August). Поставил формулы в файл "Пример", чтобы было понятно.
evgeniygeo, не сказал бы, что это пример (куча ненужного), хотя всё лучше, чем ничего… Итак, задача комплексная (как мне кажется), поэтому вам либо в ветку Работа (платно) или разбивать на самостоятельные темы в этой ветке (бесплатно), а пока вот вам алгоритм, как бы я писал код: 0. запуск макроса производится из файла, где нужно всё собрать 1. собираем ключи, по которым будет производится поиск. В примере это столбцы "C" и "Q". Нужны ли "A" и "B" я так и не понял 2. отображаем диалоговое окно выбора файлов и получаем массив полных путей до выбранных файлов 3. в цикле открываем каждый файл, просматриваем (неточный поиск) столбец с ключами (в примере это столбец "H", а про столбец "A" непонятно). Если подходит, то запоминаем значение текущей строки из столбца значений (в примере - "Q") 4. вычисляем всё, что нужно 5. выгружаем результат в файл-сборник
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
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))
Если пользователи будут писать названия, как программе узнать на какой месяц записывать данные? Тут нужно либо иметь для каждого месяца свое название файла, либо иметь список, который будет корректироваться (месяц - название файла). Первый вариант более надежный в плане сбора данных, заодно можно проверку на обработку файла поставить, если файла нет в списке допустимых, то показать какого именно и что его требуется переименовать, либо обработать в соответствии указанному месяцу, но это более сложная реализация.
Nordheim, все верно, пользователи будут ставить имя файла, после его выпуска. Сами файлы будут выкладываться в папки с номерами месяцев. Расчетный файл будет лежать перед папками с месяцами. Как пример прикладываю скрин.
Также я думаю, что ссылки на файлы по месяцам будут стоять выше названий месяцев и будет выше некий индикатор, что-то вроде "ДА". И если выше определенного месяца стоит "ДА", то необходимо обновить его. Как пример прикладываю скрин.
Т.е. в одной папке один файл? Какой смысл в записи файлов обведенных на скрине? неужели эту информацию кто то будет смотреть? По сути если в каждой папке всего 3 файла, то нет никакого смысла эти файлы записывать. Но пути к папкам должны быть жестко прописаны в коде. Любое отклонение и, либо будет ошибка, либо отчет буде не корректен.
"Все гениальное просто, а все простое гениально!!!"
Сам файл искать проще программно, нежели его внесет пользователь в лист. Вставьте файл в папку с вашим файлом "Пример.xlsm", и запустите макрос кнопкой
Это я к тому что не нужно ничего писать руками, остается брать данные из этих файлов, и по критериям делать пересчет выгружая итоги на лист. По мне так это очень трудоемко для одной темы. Это получается целая комплексная задача.
"Все гениальное просто, а все простое гениально!!!"