Kathy ha un foglio di lavoro che include le informazioni per tutte le parti nel suo magazzino. In questo foglio, i numeri di parte sono mostrati nella colonna A utilizzando il formato 12345 XXX, dove XXX rappresenta un codice di posizione. Ciò significa che potrebbe avere più voci sul foglio di lavoro per gli stessi numeri di parte, ma ciascuna voce rappresenta una posizione diversa per quella parte. Kathy necessita di una formula che somma i valori associati a ciascun numero di parte, indipendentemente dal suo codice di posizione. Quindi, ha bisogno di un modo per sommare la colonna della quantità relativa alle parti 12345 ABC, 12345 DEF, 12345 GHI, ecc.

Ha bisogno di un modo per farlo senza dividere il codice di posizione in una colonna diversa.

C’è più di un modo per ottenere la risposta desiderata. Per il bene degli esempi in questo suggerimento, supponiamo che i numeri delle parti siano nella colonna A (come indicato da Kathy) e che le quantità per ciascuna parte siano nella colonna B. Sono queste quantità che devono essere sommate, basandosi solo su un parte di ciò che è in ciascuna cella nella colonna A. Inoltre, è possibile inserire il numero di parte (meno il codice posizione) desiderato nella cella D2.

La prima possibile soluzione è utilizzare la funzione SUMPRODUCT, in questo modo:

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

Questa formula controlla i valori nell’intervallo A2: A49. Assicurati che questo intervallo rifletta l’intervallo dei tuoi dati effettivi. Se generalizzi la formula in modo che esamini tutte le colonne A e B (come in A: A e B: B), otterrai un errore #VALORE, poiché cerca di applicare la formula alle celle vuote nelle colonne .

È possibile ottenere un risultato simile utilizzando una formula di matrice come questa:

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

Ricorda, ancora una volta, che questa è una formula di matrice, quindi devi inserirla premendo Maiusc + Ctrl + Invio. Si noti inoltre che questa formula converte il valore in D2 in testo per il confronto. Ciò non è stato fatto nella formula precedente perché lì la sottostringa selezionata dalla colonna A è stata convertita in un valore numerico utilizzando la funzione VALORE.

È inoltre possibile utilizzare la funzione DSUM per costruire una formula funzionante. Supponiamo che i numeri di parte (colonna A) abbiano un’intestazione di colonna nella cella A1.

Copia questa intestazione di colonna (come “Part Num”) in un’altra cella del foglio di lavoro, come la cella D1. Nella cella D2, immettere il numero di parte, senza il suo codice di posizione, seguito da un asterisco. Ad esempio, puoi inserire “12345 *” (senza virgolette) nella cella D2. Con quella specifica impostata, puoi quindi usare questa formula:

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

Questa formula utilizza la specifica nella cella D2 (i caratteri 12345 seguiti da qualsiasi cosa) come chiave per la somma dei valori della colonna B.

Infine, se nella cella D2 avessi la stessa specifica che hai usato con l’approccio DSUM, potresti usare una funzione SUMIF molto semplice, in questo modo:

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

Tieni presente che questo approccio ti consente di utilizzare gli intervalli di colonne completi (A: A e B: B) nella formula.

Se i tuoi numeri di parte (nella colonna A) non sono coerenti nel loro formato come potresti desiderare, potresti creare meglio una funzione definita dall’utente per trovare le tue quantità. Ad esempio, se i numeri di parte non sono sempre della stessa lunghezza o se i numeri di parte possono contenere sia cifre che lettere o trattini, una UDF è la strada da percorrere. Il seguente esempio funziona alla grande; preme sulla presenza di almeno uno spazio nel valore. (Kathy ha indicato che uno spazio separava il numero di parte dal codice della posizione.)

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

Per utilizzare la funzione, nel tuo foglio di lavoro chiamala utilizzando due intervalli e il numero di parte che desideri:

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

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (11469) si applica a Microsoft Excel 2007, 2010, 2013 e 2016.

Puoi trovare una versione di questo suggerimento per la vecchia interfaccia del menu di Excel qui:

Summing Based on Part of the Information in a Cell.