Formule de base en un régulier

Désolé pour la réponse tardive.

J'ai créé un UDF qui se concentre sur le calcul à plusieurs resockets sans courir plusieurs fois la plage entière.

 Public Function getCounts(AreaStr As Variant, AreaRng As Range, CustomerRng As Range, ProductRng As Range, SalesRng As Range, Optional ProductName As Ssortingng = "Product 1", Optional lessThan As Double = 5) As Variant 'make sure AreaStr is an array If TypeOf AreaStr Is Range Then AreaStr = AreaStr.Value2 If Not IsArray(AreaStr) Then AreaStr = Array(AreaStr) ReDim Preserve AreaStr(1 To 1) End If 'shorten the range (this way you can use whole columns) If SalesRng(SalesRng.Cells.Count).Formula = "" Then Set SalesRng = SalesRng.Parent.Range(SalesRng.Cells(1), SalesRng(SalesRng.Cells.Count).End(xlUp)) 'make sure all ranges have the same size Set AreaRng = AreaRng.Resize(SalesRng.Rows.Count) Set CustomerRng = CustomerRng.Resize(SalesRng.Rows.Count) Set ProductRng = ProductRng.Resize(SalesRng.Rows.Count) 'Load values in variables to increase speed Dim SalesValues As Variant, UserValues As Variant, ProductValues As Variant SalesValues = AreaRng UserValues = CustomerRng ProductValues = ProductRng 'create temporary arrays to hold the values Dim buffer() As Variant, expList() As Variant ReDim buffer(1 To UBound(UserValues)) ReDim expList(1 To UBound(AreaStr), 1 To 1) Dim i As Long, j As Double, k As Long For i = 1 To UBound(AreaStr) expList(i, 1) = buffer Next buffer = Array(buffer, buffer) buffer(0)(1) = 0 For i = 1 To UBound(UserValues) If ProductValues(i, 1) = ProductName Then 'this customer purchased our product j = Application.IfError(Application.Match(UserValues(i, 1), buffer(0), 0), 0) If j = 0 Then 'first time this customer in this calculation j = i buffer(0)(j) = UserValues(i, 1) 'remember the customer name (to not calculate him again later) If Application.SumIfs(SalesRng, CustomerRng, UserValues(i, 1), ProductRng, ProductName) < lessThan Then buffer(1)(j) = 1 'customer got less than "lessThan" -> remember that End If End If If buffer(1)(j) = 1 Then 'check if we need to count the customer k = Application.IfError(Application.Match(SalesValues(i, 1), AreaStr, 0), 0) 'check if the area is one of the areas we are looking for If k Then expList(k, 1)(j) = 1 'it is -> set 1 for this customer/area combo End If End If Next For i = 1 To UBound(AreaStr) 'sum each area expList(i, 1) = Application.Sum(expList(i, 1)) Next getCounts = expList 'output array End Function 

Je présume que vous pourrez l'inclure comme UDF sans mon aide.

Dans la feuille que vous utiliserez (pour votre exemple) E11: E16

 =getCounts(D11:D15,G2:G7,E2:E7,I2:I7,J2:J7) 

il suffit de sélectionner la gamme de E11: E16 et d'entrer la formule, puis confirmez avec CSE.

vous pouvez également utiliser uniquement =getCounts(D11,$G$2:$G$7,$E$2:$E$7,$I$2:$I$7,$J$2:$J$7) à E11 et puis copyz. mais ce serait très lent.

Le tour est que nous calculons la sum de l'set pour chaque client, qui l'a au less acheté une fois. Ensuite, nous stockons 1 si c'est less que vos critères. Cela vaut pour le tableau général. Chaque zone que vous searchz, aura également son propre choix. Ici, nous stockons également le 1 à la même position. Comme tous les coûts ne sont calculés qu'une seule fois, l'avoir plusieurs fois n'a pas d'importance.

la formule sera simplement utilisée comme ceci:

 getCounts(AreaStr,AreaRng,CustomerRng,ProductRng,SalesRng,[ProductName],[lessThan]) 

• AreaStr: l'indicatif régional que vous searchz. devrait être un set de cellules multiples pour que l'udf vaut l'utiliser
• AreaRng: la plage où les noms de zone sont stockés
• CustomerRng: ​​la plage où les noms de clients sont stockés
• ProductRng: la gamme où les noms de produits sont stockés
• SalesRng: la plage où les counts de vente sont stockés
• ProductName (facultatif): le produit que vous searchz. Sera «produit 1» s'il est interrompu
• lessThan (facultatif): le sharepoint triggersment de la sum des produits. Sera-t-il 5 si l'on omet

La plupart des parties devraient s'expliquer automatiquement, mais si vous avez encore des questions, requestz simplement;)

OK, je ne suis pas sûr de comprendre toutes les conditions et l'accumulation, mais voici une fonction VBA qui, je pense, devrait le faire.

D'abord, ouvrez VBA à partir du menu Développeur Excel. Ensuite, dans VBA, créez un nouveau module dans le menu Insertion (laissez-le simplement Module1). Ensuite, collez les 2 fonctions suivantes dans le module VBA.

 Public Function AreaUniqueCustomersLessThan(ReportAreaRange, AreaRange, ProductRange, SalesRange, CustomerRange) On Error GoTo Err1 Dim RptAreas() As Variant Dim Areas() As Variant, Products() As Variant, Sales() As Variant, Customers As Variant RptAreas = ArrayFromRange(ReportAreaRange) Areas = ArrayFromRange(AreaRange) Products = ArrayFromRange(ProductRange) Sales = ArrayFromRange(SalesRange) Customers = ArrayFromRange(CustomerRange) Dim r As Long, s As Long 'report and source rows indexes Dim mxr As Long, mxs As Long mxr = UBound(RptAreas, 1) mxs = UBound(Areas, 1) 'encode the ReportAreasList into accumulation array indexes Dim AreaCustomers() As Collection Dim i As Long, j As Long Dim colAreas As New Collection ReDim AreaCustomers(1 To mxr) For r = 1 To mxr On Error Resume Next 'Do we have the area already? j = colAreas(RptAreas(r, 1)) If Err.Number <> 0 Then 'Add a new area to the collection and array i = i + 1 colAreas.Add i, RptAreas(r, 1) Set AreaCustomers(i) = New Collection j = i End If Next r 'now scan the source rows, accumulating distinct customers ' for any ReportAreas For s = 1 To mxs 'is this row's Arera in the report Area list? i = 0 On Error Resume Next i = colAreas(Areas(s, 1)) On Error GoTo Err1 If i > 0 Then 'this is a report Area code, so check the conditions If Products(s, 1) = "Product 1" Then If Sales(s, 1) < 5 Then On Error Resume Next 'just ignore any duplicate errors AreaCustomers(i).Add Customers(s, 1), Customers(s, 1) On Error GoTo Err1 End If End If End If Next s 'finally, return to the report area codes, returning the distinct count ' of customers Dim count() As Variant ReDim count(1 To mxr, 1 To 1) For r = 1 To mxr count(r, 1) = AreaCustomers(colAreas(RptAreas(r, 1))).count Next r AreaUniqueCustomersLessThan = count ' "foo" Exit Function Err1: AreaUniqueCustomersLessThan = "%ERR(" & Str(Err.Number) & ")%" & Err.Description Exit Function Resume End Function 'handle all of the cases, checking and conversions to convert ' a variant range into an array of Variant(1 to n, 1 to 1) ' (we do this because it makes data access very fast) Function ArrayFromRange(varRange As Variant) Dim rng As Range Dim A() As Variant Set rng = varRange 'Check for degenerate cases If rng Is Nothing Then 'do nothing ElseIf rng.count = 0 Then 'do nothing ElseIf rng.count = 1 Then ReDim A(1 To 1, 1 To 1) A(1, 1) = rng.Value Else A = rng.Value End If ArrayFromRange = A End Function 

Enfin, allez dans votre zone de la formule de tableau et collez dans la formule de tableau suivante pour la list "Ventes <5": {=AreaUniqueCustomersLessThan(D$11:D$16, G$2:G$7, I$2:I$7,J$2:J$7,E$2:E$7)} Notez que la première gamme doit être de la même longueur que la gamme de formules Array elle-même. Et les quatre autres gammes (les plages de données source) devraient tous avoir la même longueur (ils ne doivent pas avoir la même longueur que la première plage).