programación - vba excel 2016 pdf español

Código de VBA para SUMIDERS?

Question

Intento escribir una función personalizada que me permita recuperar una celda de la primera fila en un rango que cumple x número de criterios. Imagino que esto sería muy similar a la forma en que SUMIFS funciona, simplemente porque no continúa procesando después de la primera coincidencia.

¿Alguien sabe el código para reproducir la función SUMAR.SI (excel 07) en VBA?

Entonces, por ejemplo, si tengo una tabla en Excel como:

W X Y Z
a b 6 1
a b 7 2
b b 7 3

Quiero poder escribir una función que me dé el valor en la columna Z donde las columnas W = a, X = b, Y> = 7 (en otras palabras, el valor 2).

SUMARIOS puede hacer esto aproximadamente, suponiendo que el registro que quiero es único y estoy buscando devolver un número. Sin embargo, para mis propósitos, esas suposiciones no funcionarán.

Answer 1

Un ejemplo usando ADO.

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
''I want to be able to write a function that will give me the value ''
''in column Z where columns W=a, X=b, Y>=7 ''
''(in other words the value 2).''
strSQL = "SELECT Top 1 Z " _
& "FROM [Sheet1$] " _
& "WHERE W=''a'' And X=''b'' And Y>=7"
rs.Open strSQL, cn
Result = rs.Fields("Z")

Answer 2

IMHO ADO no es adecuado para usar en las funciones de la hoja de cálculo de Excel (rendimiento deficiente y no se puede usar fácilmente en la hoja de trabajo que contiene los datos). aquí hay una alternativa de VBA:

Function MFind(theRange As Range, ParamArray Tests() As Variant) As Variant
''
'' Parameters are:
'' The Range to be searched
'' the values to be searched for in successive columns
'' all search values except the last use =
'' the last search value uses >=
'' the function returns the value from the last column in the range
''
Dim vArr As Variant
Dim j As Long
Dim k As Long
Dim nParams As Long
Dim blFound As Boolean

vArr = theRange.Value2
nParams = UBound(Tests) - LBound(Tests) + 1
If nParams >= UBound(vArr, 2) Then
MFind = CVErr(xlErrValue)
Exit Function
End If
For j = 1 To UBound(vArr)
blFound = True
For k = LBound(Tests) To nParams - 2
If vArr(j, k + 1) <> Tests(k) Then
blFound = False
Exit For
End If
Next k
If blFound Then
If vArr(j, nParams) >= Tests(nParams - 1) Then
MFind = vArr(j, UBound(vArr, 2))
Exit For
End If
End If
Next j

 Función final

Answer 3

Deeno, tener un UDF para esto es muy útil, pero también puedes usar plain old =VLOOKUP() .

VLOOKUP() solo funciona buscando una "clave", pero puede hacer una clave concatenada en una columna auxiliar a la izquierda. p.ej:

W X Y Z AA
a b 6 ab6 1
a b 7 ab7 2
b b 7 bb7 3

Entonces =VLOOKUP(A1,$Z$1:$AA$3,2,FALSE) si A1 tenía el valor que está buscando. Si sus datos son más complicados, puede unir los datos con un carácter no utilizado (p. Ej .: una tubería) para que tenga un | B | 6 en lugar de ab6.