Kathy a une feuille de travail qui comprend des informations sur toutes les pièces de son entrepôt. Dans cette feuille, les numéros de pièce sont indiqués dans la colonne A au format 12345 XXX, où XXX représente un code d’emplacement. Cela signifie qu’elle pourrait avoir plusieurs entrées sur la feuille de calcul pour les mêmes numéros de pièce, mais chaque entrée représentant un emplacement différent pour cette pièce. Kathy a besoin d’une formule qui additionne les valeurs associées à chaque numéro de pièce, quel que soit son code d’emplacement. Ainsi, elle a besoin d’un moyen de faire la somme de la colonne de quantité relative aux pièces 12345 ABC, 12345 DEF, 12345 GHI, etc.

Elle a besoin d’un moyen de le faire sans fractionner le code d’emplacement dans une colonne différente.

Il existe plusieurs façons d’obtenir la réponse souhaitée. Pour les exemples de cette astuce, supposons que les numéros de pièce sont dans la colonne A (comme Kathy l’a indiqué) et que les quantités pour chaque pièce sont dans la colonne B. Ce sont ces quantités qui doivent être additionnées, en se basant simplement sur un partie de ce qui se trouve dans chaque cellule de la colonne A. En outre, vous pouvez mettre le numéro de pièce (moins le code d’emplacement) souhaité dans la cellule D2.

La première solution potentielle est d’utiliser la fonction SOMMEPROD, de cette manière:

=SUMPRODUCT(--(VALUE(LEFT(A2:A49,FIND(" ",A2:A49)))=D2),B2:B49)

Cette formule vérifie les valeurs dans la plage A2: A49. Vous devez vous assurer que cette plage reflète la plage de vos données réelles. Si vous généralisez la formule pour qu’elle examine toutes les colonnes A et B (comme dans A: A et B: B), vous obtiendrez une erreur #VALUE, car elle essaie d’appliquer la formule aux cellules vides des colonnes. .

Vous pouvez obtenir un résultat similaire en utilisant une formule matricielle telle que celle-ci:

=SUM(B:B*(LEFT(A2:A49,5)=TEXT(D2,"@")))

Rappelez-vous, encore une fois, qu’il s’agit d’une formule matricielle, vous devez donc la saisir en appuyant sur Maj + Ctrl + Entrée. Notez également que cette formule convertit la valeur de D2 en texte pour la comparaison. Cela n’a pas été fait dans la formule précédente car la sous-chaîne sélectionnée dans la colonne A a été convertie en valeur numérique à l’aide de la fonction VALUE.

Vous pouvez également utiliser la fonction DSUM pour créer une formule de travail. Supposons que les numéros de pièce (colonne A) ont un en-tête de colonne dans la cellule A1.

Copiez cet en-tête de colonne (tel que «Part Num») dans une autre cellule de la feuille de calcul, telle que la cellule D1. Dans la cellule D2, entrez le numéro de pièce, sans son code d’emplacement, suivi d’un astérisque. Par exemple, vous pouvez entrer « 12345 * » (sans les guillemets) dans la cellule D2. Une fois cette spécification configurée, vous pouvez ensuite utiliser cette formule:

=DSUM($A$1:$B$49,$B$1,D1:D2)

Cette formule utilise la spécification de la cellule D2 (les caractères 12345 suivis de quoi que ce soit) comme clé à laquelle les valeurs de la colonne B doivent être additionnées.

Enfin, si vous aviez la même spécification dans la cellule D2 que celle utilisée avec l’approche DSUM, vous pourriez utiliser une fonction SUMIF très simple, de cette manière:

=SUMIF(A:A,D2,B:B)

Notez que cette approche vous permet d’utiliser les plages de colonnes complètes (A: A et B: B) dans la formule.

Si vos numéros de pièce (dans la colonne A) ne sont pas aussi cohérents dans leur format que vous le souhaiteriez, vous feriez peut-être mieux de créer une fonction définie par l’utilisateur pour trouver vos quantités. Par exemple, si vos numéros de pièce ne sont pas toujours de la même longueur ou si les numéros de pièce peuvent contenir à la fois des chiffres et des lettres ou des tirets, alors un UDF est la solution. L’exemple suivant fonctionne très bien; il clé sur la présence d’au moins un espace dans la valeur. (Kathy a indiqué qu’un espace séparait le numéro de pièce du code d’emplacement.)

Function AddPrtQty(ByVal Parts As Range, PartsQty As Range, _   FindPart As Variant) As Long     Dim Pos As Integer     Dim Pos2 As Integer     Dim i As Long     Dim tmp As String     Dim tmpSum As Long     Dim PC As Long

PC = Parts.Count     If PartsQty.Count <> PC Then         MsgBox "Parts and PartsQty must be the same length", vbCritical         Exit Function     End If

For i = 1 To PC         Pos = InStr(1, Parts(i), " ")

Pos2 = InStr(Pos + 1, Parts(i), " ")



If Pos2 > Pos And Len(Parts(i)) > Pos + 1 Then             tmp = CStr(Trim(Left(Parts(i), Pos2 - 1)))

ElseIf Pos > 0 And Len(Parts(i)) > 0 Then             tmp = CStr(Trim(Left(Parts(i), Pos - 1)))

End If

If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then             tmpSum = tmpSum + PartStock(i)

End If     Next i

AddPrtQty = tmpSum End Function

Pour utiliser la fonction, dans votre feuille de calcul, appelez-la en utilisant deux plages et le numéro de pièce que vous voulez:

=AddPrtQty(A2:A49,B2:B49,"GB7-QWY2")

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (11469) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.

Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

lien: / excel-Summing_Based_on_Part_of_the_Information_in_a_Cell [Sommation basée sur une partie des informations dans une cellule].