Este tutorial mostrará como usar as funções SUMIF e SUMIFS do Excel no VBA
O VBA não tem um equivalente das funções SUMIF ou SUMIFS que você pode usar - um usuário deve usar as funções embutidas do Excel no VBA usando o WorkSheetFunction objeto.
SUMIF WorksheetFunction
O objeto WorksheetFunction pode ser usado para chamar a maioria das funções do Excel que estão disponíveis na caixa de diálogo Inserir Função no Excel. A função SUMIF é uma delas.
| 123 | Sub TestSumIf ()Range ("D10") = Application.WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))End Sub |
O procedimento acima só adicionará as células no intervalo (D2: D9) se a célula correspondente na coluna C = 150.
Atribuição de um resultado SUMIF a uma variável
Você pode querer usar o resultado de sua fórmula em outro lugar no código, em vez de escrevê-lo diretamente de volta para o intervalo do Excel. Se for esse o caso, você pode atribuir o resultado a uma variável para usar posteriormente em seu código.
| 1234567 | Sub AssignSumIfVariable ()Dim result as Double'Atribuir a variávelresultado = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))'Mostra o resultadoMsgBox "O total do resultado correspondente ao código de vendas 150 é" & resultadoEnd Sub |
Usando SUMIFS
A função SUMIFS é semelhante à função SUMIF WorksheetFunction, mas permite que você verifique mais de um critério. No exemplo abaixo, estamos procurando somar o preço de venda se o código de venda for 150 E o Preço de custo for maior que 2. Observe que, nesta fórmula, o intervalo de células a somar está na frente dos critérios, enquanto na função SUMIF, está atrás.
| 123 | Sub MultipleSumIfs ()Range ("D10") = WorksheetFunction.SumIfs (Range ("D2: D9"), Range ("C2: C9"), 150, Range ("E2: E9"), "> 2")End Sub |
Usando SUMIF com um objeto Range
Você pode atribuir um grupo de células ao objeto Range e, em seguida, usar esse objeto Range com o WorksheetFunction objeto.
| 123456789101112 | Sub TestSumIFRange ()Dim rngCriteria As RangeDim rngSum como intervalo'atribuir o intervalo de célulasDefina rngCriteria = Range ("C2: C9")Definir rngSum = Range ("D2: D9")'use o intervalo na fórmulaRange ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'libere os objetos de alcanceDefinir rngCriteria = NothingDefinir rngSum = NothingEnd Sub |
Usando SOMASE em vários objetos de intervalo
Da mesma forma, você pode usar SOMASE em vários objetos de intervalo.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeDim rngCriteria2 as RangeDim rngSum como intervalo'atribuir o intervalo de célulasDefina rngCriteria1 = Range ("C2: C9")Defina rngCriteria2 = Range ("E2: E10")Defina rngSum = Range ("D2: D10")'use os intervalos na fórmulaRange ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'libere o objeto de alcanceDefinir rngCriteria1 = NadaDefinir rngCriteria2 = NadaDefinir rngSum = NothingEnd Sub |
Observe que, como você está usando um sinal de maior que, os critérios maiores que 2 precisam estar entre parênteses.
Fórmula SUMIF
Quando você usa o WorksheetFunction.SUMIF para adicionar uma soma a um intervalo em sua planilha, uma soma estática é retornada, não uma fórmula flexível. Isso significa que quando seus números no Excel mudam, o valor que foi retornado pelo WorksheetFunction não mudará.
No exemplo acima, o procedimento somou Range (D2: D9) onde SaleCode é igual a 150 na coluna C e o resultado foi colocado em D10. Como você pode ver na barra de fórmulas, esse resultado é uma figura e não uma fórmula.
Se qualquer um dos valores mudar em qualquer intervalo (D2: D9) ou intervalo (C2: D9), o resultado em D10 será NÃO mudança.
Em vez de usar o WorksheetFunction.SumIf, você pode usar o VBA para aplicar uma função SUMIF a uma célula usando o Fórmula ou FormulaR1C1 métodos.
Método de Fórmula
O método da fórmula permite que você aponte especificamente para um intervalo de células, por exemplo: D2: D10 conforme mostrado abaixo.
| 123 | Sub TestSumIf ()Intervalo ("D10"). FormulaR1C1 = "= SOMASE (C2: C9,150, D2: D9)"End Sub |
Método FormulaR1C1
O método FormulaR1C1 é mais flexível porque não o restringe a um determinado intervalo de células. O exemplo abaixo nos dará a mesma resposta que o anterior.
| 123 | Sub TestSumIf ()Intervalo ("D10"). Fórmula R1C1 = "= SOMASE (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "End Sub |
No entanto, para tornar a fórmula mais flexível, poderíamos alterar o código para ficar assim:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SOMASE (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"End Sub |
Onde quer que você esteja em sua planilha, a fórmula irá somar as células que atendem aos critérios diretamente acima dela e colocar a resposta em seu ActiveCell. O intervalo dentro da função SOMASE deve ser referido usando a sintaxe de linha (R) e coluna (C).
Ambos os métodos permitem que você use fórmulas do Excel Dinâmico no VBA.
Haverá agora uma fórmula em D10 em vez de um valor.
