SumProduct sur les ensembles de cellules (non contigus)

J'ai un total de l'ensemble de données pour les 4 différents groupements. L'une des valeurs est le temps moyen, l'autre est de compter. Pour le Total, j'ai multiplier et diviser par le total du comte. J'utilise actuellement:

=SUM(D32*D2,D94*D64,D156*D126,D218*D188)/SUM(D32,D94,D156,D218)

Je préfère utiliser un SumProduct si je peux pour le rendre plus lisible. J'ai essayé de faire:

=SUMPRODUCT((D2,D64,D126,D188),(D32,D94,D156,D218))/SUM(D32,94,D156,D218)

Mais comme vous pouvez le dire par mon annonce ici, cela ne fonctionne pas. Est il possible de le faire SumProduct comme je veux?

J'ai mis à jour ma réponse avec une possible fonction personnalisée de la solution.

OriginalL'auteur Craig | 2010-03-09

excel worksheet-function

5

Il pourrait être possible avec la magistrale excel-fu, mais même si cela peut être fait, il n'est pas susceptible d'être plus lisible que l'original de votre solution. Le problème est que, même après plus de 20 ans, Excel borks plages discontinues. Le fait de les nommer ne fonctionne pas, les formules de tableau ne fonctionne pas et comme vous le voyez avec SUMPRODUCT, ils ne fonctionnent généralement pas en tuple-sage fonctions de tableau. Votre meilleur pari ici est de venir avec une fonction personnalisée.

Mise à JOUR

Vous êtes à la question m'a fait réfléchir sur la façon de gérer plages discontinues. Ce n'est pas quelque chose que j'ai eu à traiter avec beaucoup dans le passé. Je n'ai pas le temps de donner une meilleure réponse quand vous avez posé la question mais maintenant que j'ai quelques minutes, je vous ai préparé une fonction personnalisée qui permettra de faire ce que vous voulez:
```
Function gvSUMPRODUCT(ParamArray rng() As Variant)

    Dim sumProd As Integer
    Dim valuesIndex As Integer
    Dim values() As Double

    For Each r In rng()
        For Each c In r.Cells
            On Error GoTo VBAIsSuchAPainInTheAssSometimes
                valuesIndex = UBound(values) + 1
            On Error GoTo 0
            ReDim Preserve values(valuesIndex)
            values(valuesIndex) = c.Value
        Next c
    Next r
    If valuesIndex Mod 2 = 1 Then
        For i = 0 To (valuesIndex - 1) /2
            sumProd = sumProd + values(i) * values(i + (valuesIndex + 1) /2)
        Next i
        gvSUMPRODUCT = sumProd
        Exit Function
    Else
        gvSUMPRODUCT = CVErr(xlErrValue)
        Exit Function
    End If

VBAIsSuchAPainInTheAssSometimes:
    valuesIndex = 0
    Resume Next

End Function
```
Quelques remarques:
- Excel énumère les plages par colonne, ligne, donc si vous avez une gamme continue où les données sont organisées par colonne, vous devez sélectionner les plages séparées: gvSUMPRODUCT(A1:A10; B1:B10) et pas gvSUMPRODUCT(A1:B10).
- La fonction fonctionne par paires multipliant la première moitié de cellules avec la seconde, puis en additionnant ces produits: gvSUMPRODUCT(A1,C3,L2,B2,G5,F4) = A1*B2 + C3*G5 + L2*F4. I. e. l'ordre des questions.
- Vous pouvez étendre la fonction d'inclure n-sage de multiplication en faisant quelque chose comme gvNSUMPRODUCT(n,plages).
- Si il y a un nombre impair de cellules (ne plages), elle renvoie l'erreur #VALEUR.
Donc, je crois que je vais accepter cette réponse, et juste à conserver la méthode actuelle ou ajouter un article quand j'ai automatiser cela avec VBA qui ajoute chaque chacun des groupes, la superficie Totale de la zone...
Voir mise à jour pour une solution possible.

OriginalL'auteur gvkv
7

Je suis d'accord avec le commentaire: "Il pourrait être possible avec la magistrale excel-fu, mais même si cela peut être fait, il n'est pas susceptible d'être plus lisible que l'original de votre solution"

Une solution possible consiste à incorporer le CHOOSE() fonction au sein de votre SUMPRODUCT (cette astuce est en fait assez pratique pour vlookups, trouver conditionnelle maximums, etc.).

Exemple:
Disons que vos données a huit observations et est en deux colonnes (colonnes B et C), mais vous ne voulez pas inclure certaines observations (à l'exclusion des observations dans les lignes 4 et 5). Puis le SUMPRODUCT code ressemble à ceci...
```
=SUMPRODUCT(CHOOSE({1,2},A1:A3,A6:A8),CHOOSE({1,2},B1:B3,B6:B8))
```
J'ai effectivement pensé à ce à la volée, donc je ne sais pas les limites et comme vous pouvez le voir ce n'est pas que jolie.

Espérons que cette aide! 🙂

Je vais essayer les Choisir() pour voir comment il fonctionne car c'est la première fois que j'ai entendu parler de lui. Est-il limité à Excel 2010 et au-dessus, le savez-vous?
Merci! CHOISIR, c'est ce que j'ai besoin d'utiliser de GRANDS sur un discontinu ensemble de valeurs 🙂 E. g. ajouter les deux premiers des trois: LARGE(CHOISIR({1,2,3},J54,N54,U54),1)+GRANDE(CHOISIR({1,2,3},J54,N54,U54),2)

OriginalL'auteur Chris
1

Noter que sumproduct(a, b) = sumproduct(a1, b1) + sumproduct(a2, b2) où la plage est divisée en plages a1 et a2 (et pareil pour b)

OriginalL'auteur Dirk Nachbar
0

Il pourrait être utile de créer une table intermédiaire qui résume les données que vous utilisez pour calculer la somme des produits. Ce serait aussi faire le calcul plus facile à suivre.

OriginalL'auteur guitarthrower

Vous devez vous connecter pour publier un commentaire.