Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Суммировать данные с нескольких листов по условию
 
#1
12.07.2016 00:05:55
Добрый вечер, помогите правильно преобразовать формулу для корректной работы в vba.
.FormulaLocal работать отказалась
Код
Range("Q7").FormulaR1C1 = _
        "=SUM(INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-15]C[-16]),1,FIND(""]"",CELL(""имяфайла"",R[-15]C[-16])),"""")-1,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-7]C[-2])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-15]C[-16]),1,FIND(""]"",CELL(""имяфайла"",R[-15]C[-16])),"""")-2,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-7]C[-2])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-15]C[-16" & _
        "D(""]"",CELL(""имяфайла"",R[-15]C[-16])),"""")-3,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-7]C[-2])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-15]C[-16]),1,FIND(""]"",CELL(""имяфайла"",R[-15]C[-16])),"""")-4,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-7]C[-2])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-15]C[-16]),1,FIND(""]"",CELL(""имяфайла"",R[-15]C[-16])),"""")-5,""ДД.ММ.ГГ"")" & _
        "ROW(R[-7]C[-2])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-15]C[-16]),1,FIND(""]"",CELL(""имяфайла"",R[-15]C[-16])),"""")-6,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-7]C[-2])),R[-7]C[-2],)"
 
 
 
#2
12.07.2016 00:18:24
Что делает эта формула?
 
 
 
#3
12.07.2016 08:31:40
Формула через двссыл собирает числа с шести листов и ссылкой на ячейку с седьмого.
Код
=ДВССЫЛ("'"&ТЕКСТ(ЗАМЕНИТЬ(ЯЧЕЙКА("имяфайла";A1);1;НАЙТИ("]";ЯЧЕЙКА("имяфайла";A1));"")-1;"ДД.ММ.ГГ")&"'!o"&СТРОКА(O9))
при вставке одной ссылки работает нормально в виде
Код
ActiveCell.FormulaR1C1 = _
        "=INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-19]C[-15]),1,FIND(""]"",CELL(""имяфайла"",R[-19]C[-15])),"""")-1,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-11]C[-1]))"
полная формула
Скрытый текст
через макрорекодер
Код
ActiveCell.FormulaR1C1 = _
        "=SUM(INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-20]C[-15]),1,FIND(""]"",CELL(""имяфайла"",R[-20]C[-15])),"""")-1,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-12]C[-1])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-20]C[-15]),1,FIND(""]"",CELL(""имяфайла"",R[-20]C[-15])),"""")-2,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-12]C[-1])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-20]C[-" & _
        "IND(""]"",CELL(""имяфайла"",R[-20]C[-15])),"""")-3,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-12]C[-1])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-20]C[-15]),1,FIND(""]"",CELL(""имяфайла"",R[-20]C[-15])),"""")-4,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-12]C[-1])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-20]C[-15]),1,FIND(""]"",CELL(""имяфайла"",R[-20]C[-15])),"""")-5,""ДД.ММ.Г" & _
        "o""&ROW(R[-12]C[-1])),INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-20]C[-15]),1,FIND(""]"",CELL(""имяфайла"",R[-20]C[-15])),"""")-6,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-12]C[-1])),R[-12]C[-1],)"
получается вроде аналогично с первой, но при записи в ячейку выдает ошибку "run-time error 1004 application-defined or object-defined error"
 
 
 
#4
12.07.2016 08:59:24
Формула слишком длинная для вставки макросом. Надо укорачивать. У Вас слишком часто повторяется эта часть:
ТЕКСТ(ЗАМЕНИТЬ(ЯЧЕЙКА("имяфайла";A1);1;НАЙТИ("]";ЯЧЕЙКА("имяфайла";A1));"")
зачем их 6 раз писать? Какова цель работы этой формулы? Может поможем упростить.
Если упростить не хотите - тогда создайте из этой формулы именованную(Ctrl+F3) и вставляйте макросом имя, а не саму формулу.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#5
12.07.2016 09:52:00
Формула суммирует данные из ячейки O9 шести предыдущих листов и листа на котором находиться, то есть за неделю. В книге имена листов организованы по датам в порядке возрастания в формате ДД.ММ.ГГ
В формуле отталкиваясь от имени текущего листа, например 10.07.15 через двссыл получаем 9.07.15 / 8.07.15 - 4.07.15 и данные их ячеек O9.
Попробовал сделать через именованную, но там формула сразу привязалась к активному листу и независимо от размещения выдавала значения только с него.
Можно через макрос вставлять необходимую формулу адресно обращаясь к именам листов, но не хотелось бы делать такую жесткую привязку.
 
 
 
#6
12.07.2016 10:01:26
Так не пробовали?
Как просуммировать данные с нескольких листов, в том числе по условию
Даже самый первый вариант должен более чем подойти. Тупо кодом вычислить имя шестого листа перед активным и вставить его имя в формулу.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#7
12.07.2016 14:53:10
Первый вариант для фиксированных страниц работает, =СУММ('27.06.16:03.07.16'!O9),
но как в макросе вставить имя листа в формулу в виде переменной?
Код
ActiveCell.FormulaR1C1 = "=SUM('27.06.16:03.07.16'!R[-7]C[-1])"
 
 
 
#8
12.07.2016 20:12:16
В Вашем случае, наверное, так:
Код
ActiveCell.FormulaR1C1 = "=SUM('" & Activeworkbook.sheets(ActiveSheet.index-6).name & ":" & ActiveSheet.Name "'!R[-7]C[-1])"
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#9
18.07.2016 15:30:28
Спасибо, но как ни пробовал выдает ошибку синтаксиса.
INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-20]C[-15]),1,FIND(""]"",CELL(""имяфайла"",R[-20]C[-15])),""""),""ДД.ММ.ГГ"")&""'!o""&ROW(R[-12]C[-1]))
INDIRECT(""'""&TEXT(REPLACE(CELL(""имяфайла"",R[-20]C[-15]),1,FIND(""]"",CELL(""имяфайла"",R[-20]C[-15])),"""")-6,""ДД.ММ.ГГ"")&""'!o""&ROW(R[-12]C[-1]))
Может быть можно через двссыл, вписать две страницы в виде диапазона?
 
 
 
#10
18.07.2016 16:46:09
Цитата
Phurba написал: выдает ошибку синтаксиса
ну значит неправильно пишите формулу или еще что-то в коде. Вы пробовали функцию записать на лист руками? Если да - запишите это действие макросом и посмотрите как должен выглядеть код. А ДВССЫЛ не работает с трехмерными ссылками, поэтому можете в общем-то и не пробовать - даже если запишите, работать формула не будет.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
Страницы: 1
Читают тему
Наверх