Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Формулу =СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;..... в макрос - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Формулу =СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;..... в макрос (Макросы/Sub)
Формулу =СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;..... в макрос
Gjlhzl Дата: Пятница, 03.02.2023, 15:42 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

При создании листов макросом, требуется вставка формулы в B2:B5
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$7;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК($B$7:$B$20)))-1;))*$B$7:$B$20*($D$7:$D$20=$A2))

как прописать ее в макросе учитывая что диапазон суммирования до последней строки таблицы, которая в создаваемых файлах отличается кол-вом строк
пример прилагаю
К сообщению приложен файл: 9189897.xlsb (14.6 Kb)
 
Ответить
СообщениеПри создании листов макросом, требуется вставка формулы в B2:B5
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$7;СТРОКА(ДВССЫЛ("1:"&ЧСТРОК($B$7:$B$20)))-1;))*$B$7:$B$20*($D$7:$D$20=$A2))

как прописать ее в макросе учитывая что диапазон суммирования до последней строки таблицы, которая в создаваемых файлах отличается кол-вом строк
пример прилагаю
Автор - Gjlhzl
Дата добавления - 03.02.2023 в 15:42
Gjlhzl Дата: Пятница, 03.02.2023, 21:33 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

то есть к любой такой таблице что бы макросом вставлялась и все верно считало
 
Ответить
Сообщението есть к любой такой таблице что бы макросом вставлялась и все верно считало
Автор - Gjlhzl
Дата добавления - 03.02.2023 в 21:33
cmivadwot Дата: Пятница, 03.02.2023, 21:49 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 533
Репутация: 97 ±
Замечаний: 0% ±

365
Gjlhzl, а точно не работает? вроде умная таблица сама там все меняет при добавлении строк.
 
Ответить
СообщениеGjlhzl, а точно не работает? вроде умная таблица сама там все меняет при добавлении строк.
Автор - cmivadwot
Дата добавления - 03.02.2023 в 21:49
Nic70y Дата: Суббота, 04.02.2023, 08:47 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 9005
Репутация: 2369 ±
Замечаний: 0% ±

Excel 2010
[vba]
Код
    u = Cells(Rows.Count, "b").End(xlUp).Row
    v = u - 6
    Range("b2:b5").FormulaR1C1 = "=SUMPRODUCT(SUBTOTAL(3,OFFSET(R7C2,ROW(R1:R" & v & ")-1,))*R7C2:R" & u & "C2*(R7C4:R" & u & "C4=RC1))"
[/vba]

ЮMoney 41001841029809
 
Ответить
Сообщение[vba]
Код
    u = Cells(Rows.Count, "b").End(xlUp).Row
    v = u - 6
    Range("b2:b5").FormulaR1C1 = "=SUMPRODUCT(SUBTOTAL(3,OFFSET(R7C2,ROW(R1:R" & v & ")-1,))*R7C2:R" & u & "C2*(R7C4:R" & u & "C4=RC1))"
[/vba]
Автор - Nic70y
Дата добавления - 04.02.2023 в 08:47
Gjlhzl Дата: Суббота, 04.02.2023, 09:26 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

Nic70y, спасибо
только если протянуть формулу потом ошибка НД. формула не работает при изменении диапазона...кол-ва строк
 
Ответить
СообщениеNic70y, спасибо
только если протянуть формулу потом ошибка НД. формула не работает при изменении диапазона...кол-ва строк
Автор - Gjlhzl
Дата добавления - 04.02.2023 в 09:26
Nic70y Дата: Суббота, 04.02.2023, 09:29 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 9005
Репутация: 2369 ±
Замечаний: 0% ±

Excel 2010
Цитата cmivadwot, 03.02.2023 в 21:49, в сообщении № 3 ()
вроде умная таблица сама

[vba]
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$7;СТРОКА(Табл[Сумма])-7;))*Табл[Сумма]*(Табл[Валюта]=$A2))
[/vba]
Gjlhzl, не понял Вас.
куда протягиваете формулу?
Цитата Gjlhzl, 04.02.2023 в 09:26, в сообщении № 5 ()
формула не работает при изменении диапазона

Цитата Gjlhzl, 03.02.2023 в 15:42, в сообщении № 1 ()
в создаваемых файлах отличается кол-вом строк

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Суббота, 04.02.2023, 09:36
 
Ответить
Сообщение
Цитата cmivadwot, 03.02.2023 в 21:49, в сообщении № 3 ()
вроде умная таблица сама

[vba]
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$7;СТРОКА(Табл[Сумма])-7;))*Табл[Сумма]*(Табл[Валюта]=$A2))
[/vba]
Gjlhzl, не понял Вас.
куда протягиваете формулу?
Цитата Gjlhzl, 04.02.2023 в 09:26, в сообщении № 5 ()
формула не работает при изменении диапазона

Цитата Gjlhzl, 03.02.2023 в 15:42, в сообщении № 1 ()
в создаваемых файлах отличается кол-вом строк
Автор - Nic70y
Дата добавления - 04.02.2023 в 09:29
Gjlhzl Дата: Суббота, 04.02.2023, 09:54 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

Nic70y, неправильно выразился, имел ввиду если добавлять в таблицу строки.....то не пашет
 
Ответить
СообщениеNic70y, неправильно выразился, имел ввиду если добавлять в таблицу строки.....то не пашет
Автор - Gjlhzl
Дата добавления - 04.02.2023 в 09:54
Nic70y Дата: Суббота, 04.02.2023, 09:56 | Сообщение № 8
Группа: Друзья
Ранг: Экселист
Сообщений: 9005
Репутация: 2369 ±
Замечаний: 0% ±

Excel 2010
Gjlhzl, повесить макрос на событие - изменение ячеек
или использовать формулу с ссылками на умную таблицу
Цитата Nic70y, 04.02.2023 в 09:29, в сообщении № 6 ()
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$7;СТРОКА(Табл[Сумма])-7;))*Табл[Сумма]*(Табл[Валюта]=$A2))

ЮMoney 41001841029809
 
Ответить
СообщениеGjlhzl, повесить макрос на событие - изменение ячеек
или использовать формулу с ссылками на умную таблицу
Цитата Nic70y, 04.02.2023 в 09:29, в сообщении № 6 ()
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$7;СТРОКА(Табл[Сумма])-7;))*Табл[Сумма]*(Табл[Валюта]=$A2))
Автор - Nic70y
Дата добавления - 04.02.2023 в 09:56
Gjlhzl Дата: Суббота, 04.02.2023, 10:06 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

Nic70y, так а макросом если формулу вставлять...как код будет выглядеть?
 
Ответить
СообщениеNic70y, так а макросом если формулу вставлять...как код будет выглядеть?
Автор - Gjlhzl
Дата добавления - 04.02.2023 в 10:06
Gjlhzl Дата: Суббота, 04.02.2023, 10:24 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

создается файл в него подставляются формулы, после с этим файлом работают добавляют данные
 
Ответить
Сообщениесоздается файл в него подставляются формулы, после с этим файлом работают добавляют данные
Автор - Gjlhzl
Дата добавления - 04.02.2023 в 10:24
Nic70y Дата: Суббота, 04.02.2023, 10:40 | Сообщение № 11
Группа: Друзья
Ранг: Экселист
Сообщений: 9005
Репутация: 2369 ±
Замечаний: 0% ±

Excel 2010
[vba]
Код
    a = Range("a6").ListObject.Name
    Range("b2:b5").FormulaR1C1 = "=SUMPRODUCT(SUBTOTAL(3,OFFSET(R7C2,ROW(" & a & "[Сумма])-7,))*" & a & "[Сумма]*(" & a & "[Валюта]=RC1))"
[/vba]

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Суббота, 04.02.2023, 10:41
 
Ответить
Сообщение[vba]
Код
    a = Range("a6").ListObject.Name
    Range("b2:b5").FormulaR1C1 = "=SUMPRODUCT(SUBTOTAL(3,OFFSET(R7C2,ROW(" & a & "[Сумма])-7,))*" & a & "[Сумма]*(" & a & "[Валюта]=RC1))"
[/vba]
Автор - Nic70y
Дата добавления - 04.02.2023 в 10:40
cmivadwot Дата: Суббота, 04.02.2023, 13:22 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 533
Репутация: 97 ±
Замечаний: 0% ±

365
Gjlhzl, у меня при добавлении данных ниже таблицы(в сдедующую строку после завершения таблицы), автоматом умная таблица протягивается ниже и формулы автоматом учитывают добавившиеся строки.
 
Ответить
СообщениеGjlhzl, у меня при добавлении данных ниже таблицы(в сдедующую строку после завершения таблицы), автоматом умная таблица протягивается ниже и формулы автоматом учитывают добавившиеся строки.
Автор - cmivadwot
Дата добавления - 04.02.2023 в 13:22
Gjlhzl Дата: Суббота, 04.02.2023, 16:57 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

cmivadwot, при первом макросе - при изменении строк табл - ошибк НД
при втором вар от Nic70y, ошибка на сам макрос
попробуйте в пример вставить...
 
Ответить
Сообщениеcmivadwot, при первом макросе - при изменении строк табл - ошибк НД
при втором вар от Nic70y, ошибка на сам макрос
попробуйте в пример вставить...
Автор - Gjlhzl
Дата добавления - 04.02.2023 в 16:57
cmivadwot Дата: Суббота, 04.02.2023, 17:41 | Сообщение № 14
Группа: Проверенные
Ранг: Ветеран
Сообщений: 533
Репутация: 97 ±
Замечаний: 0% ±

365
Gjlhzl, у меня все работает, как написал выше, на файле выложенном вами в первом сообщении.... Без макросов...при добавлении строк таблица их захватывает ....и корректируюца формулы. Если копировать формулы и вставлять на чистый лист...формулы беруд данные с листа, откуда они скопированы(чудеса умной таблицы). Я зажал контрл+лев клавиша мыши.. стрелка на листе и делаю копию вашего листа...вот тогда в копии все работает как и на первом листе....все добавляется и формулы ......
 
Ответить
СообщениеGjlhzl, у меня все работает, как написал выше, на файле выложенном вами в первом сообщении.... Без макросов...при добавлении строк таблица их захватывает ....и корректируюца формулы. Если копировать формулы и вставлять на чистый лист...формулы беруд данные с листа, откуда они скопированы(чудеса умной таблицы). Я зажал контрл+лев клавиша мыши.. стрелка на листе и делаю копию вашего листа...вот тогда в копии все работает как и на первом листе....все добавляется и формулы ......
Автор - cmivadwot
Дата добавления - 04.02.2023 в 17:41
Gjlhzl Дата: Суббота, 04.02.2023, 17:55 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

cmivadwot, так весь смысл в том что сначала создаются файлы запросами с PQ потом вставляются формулы в нужные ячейки макросом...а уж потом работают с файлами.
попробуйте вставив формулы макросом , изменить умную табл ..
 
Ответить
Сообщениеcmivadwot, так весь смысл в том что сначала создаются файлы запросами с PQ потом вставляются формулы в нужные ячейки макросом...а уж потом работают с файлами.
попробуйте вставив формулы макросом , изменить умную табл ..
Автор - Gjlhzl
Дата добавления - 04.02.2023 в 17:55
cmivadwot Дата: Воскресенье, 05.02.2023, 19:40 | Сообщение № 16
Группа: Проверенные
Ранг: Ветеран
Сообщений: 533
Репутация: 97 ±
Замечаний: 0% ±

365
Gjlhzl, я наверно чет не понимаю....вот для 200 строк. формула считает только то, что заполнено и совпадает с условием, и наверно нет смысла подстраивать ее под количество строк
К сообщению приложен файл: 1244.mkv (489.3 Kb) · 9189897-2-2-.xlsb (25.6 Kb)


Сообщение отредактировал cmivadwot - Воскресенье, 05.02.2023, 21:27
 
Ответить
СообщениеGjlhzl, я наверно чет не понимаю....вот для 200 строк. формула считает только то, что заполнено и совпадает с условием, и наверно нет смысла подстраивать ее под количество строк
Автор - cmivadwot
Дата добавления - 05.02.2023 в 19:40
Nic70y Дата: Понедельник, 06.02.2023, 08:47 | Сообщение № 17
Группа: Друзья
Ранг: Экселист
Сообщений: 9005
Репутация: 2369 ±
Замечаний: 0% ±

Excel 2010
Цитата cmivadwot, 04.02.2023 в 13:22, в сообщении № 12 ()
попробуйте в пример вставить...

ну я на примере его (макрос) и писал, см. файл
К сообщению приложен файл: 0470371.xlsb (17.0 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата cmivadwot, 04.02.2023 в 13:22, в сообщении № 12 ()
попробуйте в пример вставить...

ну я на примере его (макрос) и писал, см. файл
Автор - Nic70y
Дата добавления - 06.02.2023 в 08:47
Gjlhzl Дата: Понедельник, 06.02.2023, 12:57 | Сообщение № 18
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 138
Репутация: 0 ±
Замечаний: 0% ±

Nic70y, спасибо...че то я сам напутал
 
Ответить
СообщениеNic70y, спасибо...че то я сам напутал
Автор - Gjlhzl
Дата добавления - 06.02.2023 в 12:57
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Формулу =СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;..... в макрос (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!