Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Как правильно применить формулу массива СУММ() в VBA
 
#1
09.11.2009 17:35:11
Замучился с синтаксисом! Помогите, прошу.  
 
Хочу в VBA написать формулу, которая в excel'е выглядит так:  
{=СУММ(P2:P4*(A2:A4=A3)*(B2:B4=B3))}  
 
Сделал так:  
 
Sub test()  
   Dim ДиапазонСуммирования As Range  
   Dim ДиапазонУсловий1 As Range  
   Dim ДиапазонУсловий2 As Range  
   Dim ПеременнаяРезультат As Double  
     
   Set ДиапазонСуммирования = Worksheets("Лист2").Range("P2:P4")  
   Set ДиапазонУсловий1 = Worksheets("Лист2").Range("A2:A4")  
   Set ДиапазонУсловий2 = Worksheets("Лист2").Range("B2:B4")  
   ПеременнаяРезультат = WorksheetFunction.SumProduct((ДиапазонСуммирования) * _  
   (ДиапазонУсловий1 = Worksheets("Лист2").Range("A3")) * _  
   (ДиапазонУсловий2 = Worksheets("Лист2").Range("B3")))  
   MsgBox (ПеременнаяРезультат)  
End Sub  
 
выдает ошибку: Run-time error '13': Type mismatch  
 
Задача - получение в нужной ячейке результата, а не формулу.
 
 
 
#2
09.11.2009 18:15:34
Макрорекордер формулу Вашу пишет так    
Selection.FormulaArray = _  
       "=SUM((R2C16:R4C16)*(R2C1:R4C1=R3C1)*(R2C2:R4C2=R3C2))"
 
 
 
#3
09.11.2009 18:20:00
В таком случае в ячейке будет формула, а мне нужен сразу результат, ибо массив большой - формулы массива не блещут быстродействием.
 
 
 
#4
09.11.2009 18:25:52
{quote}{login=air}{date=09.11.2009 06:20}{thema=}{post}В таком случае в ячейке будет формула, а мне нужен сразу результат, ибо массив большой - формулы массива не блещут быстродействием.{/post}{/quote}  
Я не крупный спец запишите как СУММПРОИЗВ(()*()*()), макрорекордером  не будет массивов , а потом скопируйте ячейку саму на себя как значение.
 
 
 
#5
09.11.2009 18:32:19
{quote}{login=Микки}{date=09.11.2009 06:25}{thema=Re: }{post}{quote}{login=air}{date=09.11.2009 06:20}{thema=}{post}В таком случае в ячейке будет формула, а мне нужен сразу результат, ибо массив большой - формулы массива не блещут быстродействием.{/post}{/quote}  
Я не крупный спец запишите как СУММПРОИЗВ(()*()*()), макрорекордером  не будет массивов , а потом скопируйте ячейку саму на себя как значение.{/post}{/quote}  
 
SumProduct - это и есть СУММПРОИЗВ. На мой взгляд СУММПРОИЗВ отличается от формулы массива {СУММ} только тем, что во втором случае нужно нажать ctrl+shift+enter.  
А сначала записать формулу в ячейку, потом саму на себя копировать - выход, конечно, но уверен в том, что нужно просто правильно написать код)))
 
 
 
#6
09.11.2009 18:36:51
Имхо - в VBA быстрее не будет... Не такие уж и медленные формулы массива, в частности СУММПРОИЗВ()...
 
 
 
#7
09.11.2009 21:40:41
{quote}{login=Михаил}{date=09.11.2009 06:36}{thema=}{post}Имхо - в VBA быстрее не будет... Не такие уж и медленные формулы массива, в частности СУММПРОИЗВ()...{/post}{/quote}  
Пробовал, Михаил, на когда 13 тыс строк и 5 колонок с формулой массива получается совсем не быстро (пользуюсь excel 2007).  
Уже весь форум излазил, смотрел на других сайтах, смотрел help на вражеском языке, перепробовал различные варианты - ну никак не получается правильно написать функцию!! прямо отчаялся уже.
 
 
 
#8
09.11.2009 21:51:14
Я вот проверил на трех столбцах, заполнены  все строки (1048576),  формулу  
=СУММПРОИЗВ((A:A=A2)*(B:B=B2)*C:C) обсчитывал менее пяти сек....
 
 
 
#9
09.11.2009 21:58:27
{quote}{login=Михаил}{date=09.11.2009 09:51}{thema=}{post}Я вот проверил на трех столбцах, заполнены  все строки (1048576),  формулу  
=СУММПРОИЗВ((A:A=A2)*(B:B=B2)*C:C) обсчитывал менее пяти сек....{/post}{/quote}  
согласен  
СУММПРОИЗВ быстрее массива, у меня их 3000 на 60000 строк 3 мин  
а на UDF состариться можно
 
 
 
#10
09.11.2009 21:58:54
{quote}{login=Михаил}{date=09.11.2009 09:51}{thema=}{post}Я вот проверил на трех столбцах, заполнены  все строки (1048576),  формулу  
=СУММПРОИЗВ((A:A=A2)*(B:B=B2)*C:C) обсчитывал менее пяти сек....{/post}{/quote}  
Михаил, все же относительно. Может машинка старенькая, может надо формулы оптимизировать. Если бы не было проблем с пересчетом листа, то не обращался бы.  
Вопрос-то в другом - как правильно применить функцию?
 
 
 
#11
09.11.2009 22:19:38
Кажеся, начинаю понимать, в чев поблемма - у вас не одна формула... Вы б выложили кусочек файла (строк 10-15) что б было понять...  
Нов любом случае VBA в 2007 гораздо медленне формул (в 2007 VBA тормозит сильно). Только если алгорим другой подискивать для расчетов.
 
 
 
#12
09.11.2009 23:01:00
{quote}{login=Михаил}{date=09.11.2009 10:19}{thema=Air}{post}Кажеся, начинаю понимать, в чев поблемма - у вас не одна формула... Вы б выложили кусочек файла (строк 10-15) что б было понять...  
Нов любом случае VBA в 2007 гораздо медленне формул (в 2007 VBA тормозит сильно). Только если алгорим другой подискивать для расчетов.{/post}{/quote}  
 
Не получилось подключиться к рабочему, видимо комп вырубил. Завтра утром выложу обязательно.
 
 
 
#13
09.11.2009 23:27:04
Air, не нужен Ваш файл. В том виде как Вы хотите записать не выйдет. У Вас есть два варианта. Посчитать в цикле и вставить формулу, затем заменить значениями. Второй точно быстрее
 
 
 
#14
10.11.2009 09:16:29
{quote}{login=Лузер}{date=09.11.2009 11:27}{thema=}{post}Air, не нужен Ваш файл. В том виде как Вы хотите записать не выйдет. У Вас есть два варианта. Посчитать в цикле и вставить формулу, затем заменить значениями. Второй точно быстрее{/post}{/quote}  
 
Если возвращать формулу, то в любом случае будет пересчет листа, разве нет? Т.е. в производительности я никак не выиграю. Все же мало верится, что функция WorksheetFunction.SumProduct не может вернуть нужное мне значение. Ведь я пробовал также использовать    
WorksheetFunction.SumIf  
и  
WorksheetFunction.SumIfs  
и та и другая функция (критерий один) результат возвращают.  
А у меня критериев два.
 
 
 
#15
10.11.2009 10:47:29
Sub test()  
a = Application.WorksheetFunction.Sum([(Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12)])
End Sub  
но  [(Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12)] :: это array(1,9) of variants
так что возможно будет тормозить....
 
 
 
#16
10.11.2009 11:41:28
Sub test()  
a = Application.WorksheetFunction.Sum([(Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12)])
a = [Sum((Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12))] + 1
a = Application.Evaluate("sum((Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12))")  
a = Application.WorksheetFunction.Sum(Application.Evaluate("(Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12)"))  
End Sub
 
 
 
#17
10.11.2009 12:12:23
Не могу понять почему, но SumProduct, видимо, и в самом деле не поможет.  
Вариант, предложенный dl, (вернее - варианты) работают.  
Только не совсем понял, зачем во втором варианте a = [Sum((Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12))] в конце "+1"?
 
 
 
#18
10.11.2009 13:54:49
{quote}{login=air}{date=10.11.2009 09:16}{thema=Re: }{post}{quote}  
Если возвращать формулу, то в любом случае будет пересчет листа, разве нет?{/post}{/quote}Давайте разберемся что в реальности Вам надо.  
Если я правильно понял, то Вы ПеременнаяРезультат вставляете в ячейку?  
И еще не в одну. И не хотите иметь там формулы, а только значения?  
Тогда вставляете формулу и тут же заменяете значениями:  
Range("C1:C100").FormulaArray = _  
"=SUM((R2C16:R4C16)*(R2C1:R4C1=R3C1)*(R2C2:R4C2=R3C2))"  
Range("C1:C100").Value = Range("C1:C100").Value  
Это будет быстрее чем  
for i=1 to 100  
Cells(i,3).Value = Application.WorksheetFunction.Sum([(Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12)])
next    
 
Если же ПеременнаяРезультат хранит некое промежуточное значение, то формулы от dl будут в самый раз.  
 
 
Про пересчет листа. в макросе можно (и часто нужно!) отменить пересчет:    
Application.Calculation = xlCalculationManual  
код    
затем вернуть пересчет  
Application.Calculation =xlCalculationAutomatic
Bite my shiny metal ass!      
 
 
 
#19
10.11.2009 13:54:49
Спасибо всем за помощь!  
Как и говорил Михаил, в скорости я ничего не выиграл.
 
 
 
#20
10.11.2009 13:58:27
глюкануло цитирование  
Давайте разберемся что в реальности Вам надо. Если я правильно понял, то Вы ПеременнаяРезультат вставляете в ячейку? И еще не в одну. И не хотите иметь там формулы, а только значения? Тогда вставляете формулу и тут же заменяете значениями:    
Range("C1:C100").FormulaArray = _ "=SUM((R2C16:R4C16)*(R2C1:R4C1=R3C1)*(R2C2:R4C2=R3C2))"    
Range("C1:C100").Value = Range("C1:C100").Value    
Это будет быстрее чем    
for i=1 to 100    
Cells(i,3).Value = Application.WorksheetFunction.Sum([(Q4:Q12>2000)*(Q4:Q12<4000)*(Q4:Q12)])
next    
Если же ПеременнаяРезультат хранит некое промежуточное значение, то формулы от dl будут в самый раз. Про пересчет листа. в макросе можно (и часто нужно!) отменить пересчет:  
Application.Calculation = xlCalculationManual    
код    
затем вернуть пересчет    
Application.Calculation =xlCalculationAutomatic
Bite my shiny metal ass!      
 
 
 
#21
10.11.2009 14:55:27
В принципе можно увеличить скорость за счет оптимизации алгоритма. но для этого нужно знать хотя-бы примерную структуру листа. В некоторых случаях можно ускорить в 10-20 раз.
 
 
 
#22
10.11.2009 17:16:21
На порядок увеличил скорость за счет отмены пересчета во время выполнения макроса по совету Лузера (и как я сам до этого не додумался?!).  
Пока терпимо.
 
 
 
#23
10.11.2009 21:25:13
Будет еще быстрее если Вы отмените обновление экрана и обработку событий (если они не входят в задачу макроса).  
Application.ScreenUpdating = False  
Application.EnableEvents = False
Bite my shiny metal ass!      
 
 
 
#24
10.11.2009 21:26:51
При наличии примера можно еще посоветовать.  
Тут любят за мс оптимизировать :)
Bite my shiny metal ass!      
 
 
 
Страницы: 1
Читают тему
Наверх