Este tutorial le mostrará cómo usar las funciones SUMIF y SUMIFS de Excel en VBA
VBA no tiene un equivalente de las funciones SUMIF o SUMIFS que puede usar: un usuario tiene que usar las funciones integradas de Excel en VBA usando el WorkSheetFunction objeto.
Hoja de trabajo SUMIF Función
El objeto WorksheetFunction se puede utilizar para llamar a la mayoría de las funciones de Excel que están disponibles en el cuadro de diálogo Insertar función en Excel. La función SUMIF es una de ellas.
| 123 | Sub TestSumIf ()Rango ("D10") = Application.WorksheetFunction.SumIf (Rango ("C2: C9"), 150, Rango ("D2: D9"))End Sub |
El procedimiento anterior solo sumará las celdas en el rango (D2: D9) si la celda correspondiente en la columna C = 150.
Asignar un resultado SUMIF a una variable
Es posible que desee utilizar el resultado de su fórmula en otro lugar del código en lugar de escribirlo directamente en un rango de Excel. Si este es el caso, puede asignar el resultado a una variable para usar más adelante en su código.
| 1234567 | Sub AssignSumIfVariable ()Atenuar el resultado como doble'Asignar la variableresult = WorksheetFunction.SumIf (Range ("C2: C9"), 150, Range ("D2: D9"))'Muestra el resultadoMsgBox "El total del resultado que coincide con el código de 150 ventas es" & resultEnd Sub |
Usando SUMIFS
La función SUMIFS es similar a SUMIF WorksheetFunction pero le permite verificar más de un criterio. En el siguiente ejemplo, buscamos sumar el precio de oferta si el código de oferta es 150 Y el precio de costo es mayor que 2. Observe que en esta fórmula, el rango de celdas para sumar está delante de los criterios, mientras que en la función SUMIF, está detrás.
| 123 | Sub MultipleSumIfs ()Rango ("D10") = WorksheetFunction.SumIfs (Rango ("D2: D9"), Rango ("C2: C9"), 150, Rango ("E2: E9"), "> 2")End Sub |
Usar SUMIF con un objeto de rango
Puede asignar un grupo de celdas al objeto Range y luego usar ese objeto Range con el Hoja de trabajo Función objeto.
| 123456789101112 | Sub TestSumIFRange ()Dim rngCriteria como rangoDim rngSum como rango'asignar el rango de celdasEstablecer rngCriteria = Rango ("C2: C9")Establecer rngSum = Rango ("D2: D9")'usa el rango en la fórmulaRango ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)'suelta los objetos de rangoEstablecer rngCriteria = NothingEstablecer rngSum = NadaEnd Sub |
Uso de SUMIFS en objetos de rango múltiple
Del mismo modo, puede utilizar SUMIFS en varios objetos de rango.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 como rangoDim rngCriteria2 como rangoDim rngSum como rango'asignar el rango de celdasEstablecer rngCriteria1 = Rango ("C2: C9")Establecer rngCriteria2 = Rango ("E2: E10")Establecer rngSum = Rango ("D2: D10")'usa los rangos en la fórmulaRango ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")'suelta el objeto de rangoEstablecer rngCriteria1 = NadaEstablecer rngCriteria2 = NadaEstablecer rngSum = NadaEnd Sub |
Tenga en cuenta que debido a que está utilizando un signo mayor que, los criterios mayores que 2 deben estar entre paréntesis.
Fórmula SUMIF
Cuando usa el WorksheetFunction.SUMIF para agregar una suma a un rango en su hoja de trabajo, se devuelve una suma estática, no una fórmula flexible. Esto significa que cuando sus cifras en Excel cambian, el valor devuelto por el Hoja de trabajo Función no cambiará.
En el ejemplo anterior, el procedimiento ha sumado Rango (D2: D9) donde SaleCode es igual a 150 en la columna C, y el resultado se puso en D10. Como puede ver en la barra de fórmulas, este resultado es una cifra y no una fórmula.
Si alguno de los valores cambia en Rango (D2: D9) o Rango (C2: D9), el resultado en D10 será NO cambio.
En lugar de usar el WorksheetFunction.SumIf, puede usar VBA para aplicar una función SUMIF a una celda usando el Fórmula o Fórmula R1C1 métodos.
Método de fórmula
El método de fórmula le permite apuntar específicamente a un rango de celdas, por ejemplo: D2: D10 como se muestra a continuación.
| 123 | Sub TestSumIf ()Rango ("D10"). FórmulaR1C1 = "= SUMIF (C2: C9,150, D2: D9)"End Sub |
Método FormulaR1C1
El método FormulaR1C1 es más flexible en el sentido de que no lo restringe a un rango establecido de celdas. El siguiente ejemplo nos dará la misma respuesta que el anterior.
| 123 | Sub TestSumIf ()Rango ("D10"). FórmulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "End Sub |
Sin embargo, para hacer que la fórmula sea más flexible, podríamos modificar el código para que se vea así:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"End Sub |
Dondequiera que se encuentre en su hoja de trabajo, la fórmula sumará las celdas que cumplen con los criterios directamente encima y colocará la respuesta en su ActiveCell. Se debe hacer referencia al rango dentro de la función SUMIF mediante la sintaxis de Fila (R) y Columna (C).
Ambos métodos le permiten utilizar fórmulas dinámicas de Excel dentro de VBA.
Ahora habrá una fórmula en D10 en lugar de un valor.
