Condividi:        

cerca nomi e valori e somma

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

cerca nomi e valori e somma

Postdi Paolo67 » 28/10/23 11:40

Ciao a tutti,
ho un foglio di lavoro così strutturato:
4 colonne
Nella 1°e nella 3° ci sono i nomi dei frutti (che possono essere anche presenti in entrambe le colonne)
Nella 2° e nella 4° la quantità.

pere 10 mele 15
mele 20 ananas 3
arance 15 albicocche 5
kiwi 12 arance 10
pere 5 melone 50

Vorrei far comparire in una zona del foglio;
In una colonna i nomi di tutti i frutti presenti in entrambe le colonne (la 1° e la 3°)
Nella colonna di fianco la somma delle quantità di ciascun frutto
Esempio:

pere 15
mele 35
arance 25
kiwi 12
albicocche 5
melone 50

Stò provando con il CERCA.VERT ma non riesco a venirne a capo
Un aiutino?
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Sponsor
 

Re: cerca nomi e valori e somma

Postdi Paolo67 » 28/10/23 22:43

Allora ho visto che utilizzando la funzione SOMMA.SE

scrivendo ad esempio nella cella G2:
=SOMMA.SE(A2:A7:D2:D7; "pere";B2:B7:E2:E7)
ottengo il risultato corretto 15

scrivendo nella cella G3:
=SOMMA.SE(A2:A7:D2:D7; "mele";B2:B7:E2:E7)
ottengo l risultato corretto 45

e così per tutti gli altri frutti.

Come faccio però adesso a far comparire di fianco ad ogni quantitativo totale il relativo nome?
Come faccio ad evitare di riscrivere continuamente la stessa formula dovendo cambiare sempre il nome del frutto?

Immagine
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Paolo67 » 29/10/23 09:13

Vorrei cioè ottenere questo in maniera automatizzata.
Ovviamente tutte le colonne A-B-D-E potrebbero contenere anche 100 righe o più
Nota:erroneamente la colonna D anzichè chiamarla Frutto l'ho chiamata Nome
Immagine
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Marius44 » 29/10/23 10:53

Ciao
Premetto che con le formule non ci vado a nozze ma ti suggerisco di far così (secondo il tuo esempio)

in K2 (atteso che K1 deve restare vuota) =SE.ERRORE(INDICE(A$2:A$6;CONFRONTA(0;INDICE(CONTA.SE(K$1:K1;A$2:A$6&""););0)) - ;SE.ERRORE(INDICE(D$2:D$6;CONFRONTA(0;INDICE(CONTA.SE(K$1:K1;D$2:D$6&""););0));""))

La formula è tutta su un rigo. Ho dovuto spezzarla ed aggiungere uno spazio e un meno (che devi togliere) altrimenti non si vedrebbe bene.

in L2 la tua formula così corretta
=SOMMA.SE($A$2:$A$6:$E$2:$E$6; $K2;$B$2:$B$6:$F$2:$F$6)

Copia entrambe in giù fin dove serve.
Fai sapere. Ciao,
Mario
Marius44
Utente Senior
 
Post: 658
Iscritto il: 07/09/15 22:00

Re: cerca nomi e valori e somma

Postdi Paolo67 » 29/10/23 11:55

Ciao Mario,
innanzitutto grazie per la risposta.
Sei sicuro che nella formula che hai allegato la cella deve essere K1 e non K2 e nella formula mia "corretta" la cella è K2?
Ho provato a mettere K2 in tutte ed ottengo 0 (vedi immagine che allego)
Provando a mettere K1 nella sola tua formula ottengo nella cella k2 #NOME?

Nota:nel mio esempio le righe arrivavano fino a 7 e non 6 come scritto in formula
Immagine

Immagine
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Marius44 » 29/10/23 12:22

Marius44
Utente Senior
 
Post: 658
Iscritto il: 07/09/15 22:00

Re: cerca nomi e valori e somma

Postdi Paolo67 » 29/10/23 14:17

Mario,stavo impazzendo!
Non capivo perchè avevo sempre l'errore.
Ho controllato nell' elenco delle funzioni ed ho scoperto che la formula SE.ERRORE non è prevista in Excel 2003.
Come risolvo adesso?
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Paolo67 » 29/10/23 15:20

Niente,ho istallato la versione OFFICE 2007
Vverifico se funziona!
Ultima modifica di Paolo67 su 29/10/23 15:28, modificato 1 volte in totale.
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Ricky0185 » 29/10/23 15:22

Se invece vuoi automatizzare sul 2003 con macro (sulla base del tuo post delle 8.13)
Codice: Seleziona tutto
Sub SommaValoriRighe()
'Come prima cosa riallinea su unica colonna i dati
Range("A2:B7").Select 'da personalizzare con la tua area
    Selection.Copy
    Range("G2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D2:E7").Select  'da personalizzare con la tua seconda area
    Application.CutCopyMode = False
    Selection.Copy
    Range("G8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Poi somma i valori delle celle doppioni-Mi raccomando SCEGLI L'AREA TU e non quella che ti propone
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "Somma Doppioni"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Scegli Area", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
    Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

Puoi lanciarla con Ctrl+z
Sul 2003 le formule di Marius non funzionano.
Facci sapere.
Ciao
R
Ricky0185
Utente Senior
 
Post: 304
Iscritto il: 10/12/19 20:38

Re: cerca nomi e valori e somma

Postdi Marius44 » 29/10/23 15:24

Ciao
Scusami per non aver fatto caso alla tua versione di Excel.
Sono contento che tu abbia risolto.
Ciao,
Mario
Marius44
Utente Senior
 
Post: 658
Iscritto il: 07/09/15 22:00

Re: cerca nomi e valori e somma

Postdi Paolo67 » 29/10/23 16:49

Mario ho dovuto modificare la formula in quanto esistevano dei riferimenti di cella sbagliati
=SOMMA.SE($A$2:$A$6:$E$2:$E$6; $K2;$B$2:$B$6:$F$2:$F$6)
in realtà doveva essere
=SOMMA.SE($A$2:$A$6:$E$2:$E$6; $K2;$B$2:$B$6:$D$2:$D$6)

La formula funge però se voglio aggiungere nuove righe sono costretto di continuo a modificare le 2 celle in K2 ed L2 sostituendo in entrambe il numero della riga.
Se ad esempio i nomi arrivano fino alla linea 10 devo scrivere:
=SE.ERRORE(INDICE(A$2:A$10;CONFRONTA(0;INDICE(CONTA.SE(K$1:K1;A$2:A$10&""););0));SE.ERRORE(INDICE(D$2:D$10;CONFRONTA(0;INDICE(CONTA.SE(K$1:K1;D$2:D$10&""););0));""))

=SOMMA.SE($A$2:$A$10:$E$2:$E$10; $K2;$B$2:$B$10:$D$2:$D$10)

C'è un modo per automatizzare il tutto?
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Paolo67 » 29/10/23 16:50

Ricky0185 ha scritto:Se invece vuoi automatizzare sul 2003 con macro (sulla base del tuo post delle 8.13)
Codice: Seleziona tutto
Sub SommaValoriRighe()
'Come prima cosa riallinea su unica colonna i dati
Range("A2:B7").Select 'da personalizzare con la tua area
    Selection.Copy
    Range("G2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D2:E7").Select  'da personalizzare con la tua seconda area
    Application.CutCopyMode = False
    Selection.Copy
    Range("G8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Poi somma i valori delle celle doppioni-Mi raccomando SCEGLI L'AREA TU e non quella che ti propone
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "Somma Doppioni"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Scegli Area", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
    Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

Puoi lanciarla con Ctrl+z
Sul 2003 le formule di Marius non funzionano.
Facci sapere.
Ciao
R

Grazie Ricky proverò anche questa
:)
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Ricky0185 » 29/10/23 18:49

Con la macro puoi aggiungere quante righe vuoi e lanciandola con Ctrl+z ti darà sempre il risultato nelle colonne G ed H.
Codice: Seleziona tutto
Sub SommaValoriRighe1()
'Come prima cosa riallinea su unica colonna i dati
Range(Sheets("Sheet1").Range("a2"), Sheets("Sheet1").Range("b2").End(xlDown)).Select 'questo se non hai celle vuote nella colonna oppure
'puoi sostituire con la prima area in esame: Range("A2:B13").Select
    Selection.Copy
    Range("G2").Select 'dove vuoi che inizi la colonna unica e successivamente i totali
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range(Sheets("Sheet1").Range("d2"), Sheets("Sheet1").Range("e2").End(xlDown)).Select 'oppure Range("D2:E13").Select Stesso ragionamento come per le colonne D ed E
    Selection.Copy
    Range("G1000").End(xlUp).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Application.CutCopyMode = False
   
'Poi somma i valori delle celle doppioni-Mi raccomando SCEGLI L'AREA TU e non quella che ti propone
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "Somma Doppioni"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Scegli Area", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
    Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

Mettila in un modulo standard poi torni sul foglio e digiti Ctrl+z e voilà, i doppioni con relativi totali li avrai sempre nelle colonne G ed H, qualunque sia la lunghezza delle righe nelle colonne A,B e D,E.
Ciao
Ricky0185
Utente Senior
 
Post: 304
Iscritto il: 10/12/19 20:38

Re: cerca nomi e valori e somma

Postdi Marius44 » 29/10/23 19:00

Ciao
Provo a fare ammenda proponendo la stessa formula compatibile con versioni precedenti

Codice: Seleziona tutto
=SE(VAL.ERRORE(INDICE(A$2:A$6;CONFRONTA(0;INDICE(CONTA.SE(K$1:K1;A$2:A$6&""););0);));INDICE(D$2:D$6;CONFRONTA(0;INDICE(CONTA.SE(K$1:K1;D$2:D$6&""););0););INDICE(A$2:A$6;CONFRONTA(0;INDICE(CONTA.SE(K$1:K1;A$2:A$6&""););0);))


Fai sapere. Ciao,
Mario
Marius44
Utente Senior
 
Post: 658
Iscritto il: 07/09/15 22:00

Re: cerca nomi e valori e somma

Postdi Ricky0185 » 30/10/23 10:04

Buongiorno, in questo file, preparato per paolo67 dopo aver letto la sua richiesta nel primo messaggio, poi assolta con una megaformula da Marius44 (che saluto e che incontro spesso ne iprogrammatori, nel forumexcel, in excelVBA, in excelacademy, etc), io, essendo negato per le formule complesse, ho suggerito invece una soluzione tramite macro.
Aprendo il file e lanciando la macro compare una Msgbox per la scelta dell’area da trattare. Vorrei
- poter posizionare la MsgBox a piacimento. In merito ho inserito nel Modulo2 del file anche una piccola Sub (estrapolata da altra mia macro) che, secondo i valori inseriti in XPos e YPos sposta la Msgbox a piacimento, ma non so come “iniettarla” nella macro SommaValoriRighe. Ho trovato anche soluzioni qui e qui, ma troppo complesse e laboriose per me e per un problema così piccolo; inoltre vorrei
- avere la Msgbox, dove si inserisce l’area da considerare, pulita e non con parzialmente inserita un’area che poi corrisponde al secondo passaggio del copia-incolla.
Aprendo il file e letto il primo messaggio, lanciando la macro (Ctrl+z) risulta tutto più chiaro.
Se necessario posso aprire nuovo topic.
Ringrazio dell'attenzione e saluto
R
Ricky0185
Utente Senior
 
Post: 304
Iscritto il: 10/12/19 20:38

Re: cerca nomi e valori e somma

Postdi Anthony47 » 30/10/23 10:23

Nonostante l'abbondanza di opzioni, provo anche io a dare un contributo con una Function ad hoc, che corrisponde al sequente codice:
Codice: Seleziona tutto
Function myResumeF(ByRef iRan As Range) As Variant
Dim oArr(), I As Long, J As Long, oInd As Long
Dim fRows As Long, iRows As Long
Dim myMatch
'
iRows = iRan.Rows.Count * iRan.Columns.Count / 2
fRows = Application.Parent.Caller.Rows.Count
'
ReDim oArr(1 To 2, 1 To iRows)
For I = 1 To iRan.Rows.Count
    For J = 1 To iRan.Columns.Count Step 2
        If iRan.Cells(I, J) <> "" Then
            myMatch = Application.Match(iRan.Cells(I, J), Application.WorksheetFunction.Index(oArr, 1, 0), False)
            If IsError(myMatch) Then
                oInd = oInd + 1
                oArr(1, oInd) = iRan.Cells(I, J)
                oArr(2, oInd) = iRan.Cells(I, J + 1)
            Else
                oArr(2, myMatch) = oArr(2, myMatch) + iRan.Cells(I, J + 1)
            End If
        End If
    Next J
Next I
If oInd >= fRows Then
    ReDim Preserve oArr(1 To 2, 1 To oInd)
Else
    ReDim Preserve oArr(1 To 2, 1 To fRows)
End If
If oInd > fRows Then
    oArr(1, fRows) = "+" & (oInd - fRows)
ElseIf oInd < fRows Then
    For I = oInd + 1 To fRows
        oArr(1, I) = "--"
        oArr(2, I) = "--"
    Next I
End If
myResumeF = Application.WorksheetFunction.Transpose(oArr)
End Function

Il codice va messo in un Modulo standard del vba. A questo punto sul foglio excel e' disponibile la funzione myResumeF
La sintassi da usare e'
myResumeF(RangeDati)

RangeDati puo' includere anche righe vuote, che verranno ignorate

Nell'ipotesi che i dati siano in colonna A (descrizione, B (qt), C (descrizione), D (qt):
Codice: Seleziona tutto
=myResumef(A2:D20)

La formula va immessa sotto forma di "formula a matrice", tenendo presente che restituisce N righe x 2 colonne. Quindi:
-selezionare un'area di N righe e 2 colonne
-inserire la formula nella barra della formula
-confermare la formula con Contr-Maiusc-Enter, non il solo Enter

RangeDati non e' obbligatorio che sia di 4 colonne, l'importante e' che l'intervallo comprenda "coppie di colonne" contigue; quindi sono valide formule tipo
Codice: Seleziona tutto
=myResumef(A2:B30)     '1 sola coppia, A-B
=myResumef(B1:E30)     '2 coppie, B-C e D-E
=myResumef(A2:F30)     '3 coppie: A-B, C-D, E-F


Se le righe non sono sufficienti per i risultati che devono essere visualizzati, allora in colonna 1 dell'ultima riga comparira' la scritta "+X", che indica che bisogna aggiungere ulteriori X righe alla formula. A questo scopo:
-selezionare l'area corrente dei risultati
-estendere la selezione per ulteriori "X" righe
-premere F2 (edit formula), confermare con Contr-Maiusc-Enter
NB: questo e' il modo naturale di lavorare con le formule a matrice

Se la formula si estendesse su piu' righe rispetto ai risultati restituiti, le righe superflue verranno compilate con "--"; questa scelta e' stata preferita al lasciare le celle vuote per rendere evidente il fatto che la formula occupa quel tot di celle, ma comunque puo' essere modificata nel codice.

Un saluto a tutti
Avatar utente
Anthony47
Moderatore
 
Post: 19465
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: cerca nomi e valori e somma

Postdi Paolo67 » 30/10/23 20:30

Ringrazio tutti x l'aiuto.
Vista l'abbondanza di materiale messomi a disposizione, faccio un pò di prove e faccio sapere!
Grazie ancora
Paolo
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Paolo67 » 31/10/23 06:47

Buongiorno a tuttii,
Anthony ho seguito alla lettera il tuo post ma continuo ad avere errore e non so dove sbaglio.
Allego immagini

Immagine

Immagine
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Re: cerca nomi e valori e somma

Postdi Marius44 » 31/10/23 06:49

Ciao
Hai provato il mio suggerimento (con formula adatta alla tua versione)? Ti dà errore? e se si, quale?

Ciao,
Mario
Marius44
Utente Senior
 
Post: 658
Iscritto il: 07/09/15 22:00

Re: cerca nomi e valori e somma

Postdi Paolo67 » 31/10/23 06:52

Ciao Mario,no non ancora!
Per non incasinarmi sto' partendo a ritroso
Ti faccio sapere ;-)
OFFICE 2003 - OFFICE 2007
Paolo67
Utente Senior
 
Post: 121
Iscritto il: 20/04/21 20:35

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "cerca nomi e valori e somma":


Chi c’è in linea

Visitano il forum: Nessuno e 11 ospiti