Condividi:        

[Excel]: formula per somme "tipo" subtotali

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

[Excel]: formula per somme "tipo" subtotali

Postdi karug64 » 30/04/19 18:22

Salve a tutti.

In allegato ho messo un estratto (molto sintetizzato) di un file excel composto da oltre 50.000 righe.

L'operazione che dovrei fare è quella di fare dei subtotali, ma se li imposto, nonostante il pc sia abbastanza performante, praticamente la barra di scorrimento per l'inserimento dei subtotali è praticamente ferma.

Dovendo fare il lavoro a mano (!!) ho inserito allora una colonna di controllo cambio valore e una formula nelle colonne "prima somma" e "seconda somma" per creare il totale al cambio del valore.

Questa operazione funziona se siamo in presenza di un solo cambio dati (da 1 a 0), ma se dopo l'uno ci sono più zeri, non va.

Vi chiedo, c'è un modo per avere (tramite formula o macro), per esempio, alla riga 88 la somma delle righe da 85 a 88 (in modo che poi io manualmente provvederò a sostituirlo nella riga 85 delle colonne Imp1 e Imp2 cancellando le righe da 86 a 88).

Grazie


https://we.tl/t-sDyvnvkUSS
Office 2010
karug64
Utente Senior
 
Post: 746
Iscritto il: 20/11/11 21:22

Sponsor
 

Re: [Excel]: formula per somme "tipo" subtotali

Postdi klingklang » 30/04/19 19:55

Ciao. Dall'esempio che hai allegato non si capisce se c'è un criterio che indica quali e quante sono le righe da sommare ogni volta. Se esiste una colonna che non vediamo, al cui cambio di valore corrisponde un subtotale da inserire, forse ti può aiutare questo strumento del mio programma:

http://www.excelswissknife.com/help/rig ... parazione/

In caso contrario, invece che avere una sequenza di 1 e 0 forse aiuterebbe avere un numero progressivo che indica di volta in volta quante sono le righe superiori da sommare. Nel tuo esempio se alla riga 88 ci fosse un 4 anziché uno 0, si potrebbe fare una formula che in automatico prende le quattro righe superiori e le somma.
Enrico
Windows 7 + Office 2016 64bit / Windows 10 + Office 365 32/64bit
Avatar utente
klingklang
Utente Junior
 
Post: 97
Iscritto il: 23/11/18 15:01
Località: San Giovanni in Persiceto

Re: [Excel]: formula per somme "tipo" subtotali

Postdi karug64 » 30/04/19 20:18

Ciao.
Il criterio per sommare è dato proprio dal valore 0 e 1. Vanno create le somme per ogni riga che ha valore 0 e per quella immediatamente precedente con valore 1. Nell'esempio, vanno sommate le righe 86,87,88 (che hanno valore zero) e la 85 che è quella immediatamente superiore all'inizio della sequenza degli zero.

Grazie
Office 2010
karug64
Utente Senior
 
Post: 746
Iscritto il: 20/11/11 21:22

Re: [Excel]: formula per somme "tipo" subtotali

Postdi Anthony47 » 30/04/19 23:30

Vanno create le somme per ogni riga che ha valore 0 e per quella immediatamente precedente con valore 1
Ma chi mette gli 1 e gli 0? Che se fossero invece, ad esempio, 0, 0, .. (invece che 1) e 1, 2, 3, 4, ... (invece che 0) sarebbe molto piu' semplice.
Non che non ci sia una formula che possa fare quello che chiedi con i dati presenti, ma visto che sei qui perche' il calcolo tramite Subtotale era troppo lento allora ad inserire tante formule a matrice che abbracciano un numero alto di righe forse finiamo peggio.
Quindi, quale e' la regola con cui sono stati messi quegli inutili 1 /0 (mi rifiuto di pensare che li hai messi a mano su 50mila righe) così vediamo se riusciamo a trasformarla in una regola che produca dati piu' utili e semplici da elaborare.
E poi: ti serve veramente farlo via formule (che il risultato viene ricalcolato sempre in realtime con impatto inevitabile sulla fluidita' del foglio) o si potrebbe fare anche via macro (ma il risultato viene ricalcolato solo su richiesta)?

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel]: formula per somme "tipo" subtotali

Postdi karug64 » 30/04/19 23:52

Ciao.
Allora gli 1 e 0 sono messi tramite una formula.
Spiego:
la colonna prima degli uno/zero (non inserita nel foglio di test) contiene una chiave univoca creata concatenando quattro valori di altrettante colonne del foglio. Tutto il foglio risulta ordinato in base a questa colonna.

La formula dice, molto semplicemente, =SE(a2<>a1;1;0) trascinata per le 50.000 righe.

In questo modo ho creato dei segnaposti che mi indicano quando cambia la chiave.

Beh, si, come dicevo nel primo post, anche una macro sarebbe comoda. (Se poi, la stessa, riuscisse a:
intercettare il "gruppo" da sommare, effettuasse la somma delle due colonne, scrivesse il totale sovrascrivendo il valore delle celle in corrispondenza del valore "1" e cancellasse le sottostanti righe con valore "0" ...... beh, sarei disposto a lascialo lavorare tutta la notte al posto mio ...)

Praticamente, su 50.000 e rotti righe, circa 3.500 andrebbero sottoposte a questo processo (in quanto presenti zero).
E lo sto facendo a mano ...... sono appena arrivato a riga 800 !!!
Office 2010
karug64
Utente Senior
 
Post: 746
Iscritto il: 20/11/11 21:22

Re: [Excel]: formula per somme "tipo" subtotali

Postdi Anthony47 » 01/05/19 00:10

Quel che chiedi si fa, su 50mila righe, in meno di 1 secondo di elaborazione.
Pero' serve una versione piu' attendibile del file, senza nessuna elaborazione preliminare...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel]: formula per somme "tipo" subtotali

Postdi klingklang » 01/05/19 00:27

Prova con
=SE(a2<>a1;0;a1+1)

In realtà, anche senza colonna di appoggio, basterebbe un SOMMA.PIÙ.SE sulle 4 colonne chiave e con un'unica formula avresti risolto. Pure con una pivot, di farebbe in un attimo. NON farlo a mano: mi piange il cuore a leggerlo, e gli errori sono assicurati.
Avatar utente
klingklang
Utente Junior
 
Post: 97
Iscritto il: 23/11/18 15:01
Località: San Giovanni in Persiceto

Re: [Excel]: formula per somme "tipo" subtotali

Postdi karug64 » 01/05/19 00:43

Anthony47 ha scritto:Quel che chiedi si fa, su 50mila righe, in meno di 1 secondo di elaborazione.
Pero' serve una versione piu' attendibile del file, senza nessuna elaborazione preliminare...


Meno di un secondo?!?! (un'intera giornata per 1000 righe.....)

Ma il file completo con le 50000 righe contiene troppi dati sensibili che è impossibile modificare per essere pubblicato.....

Come posso fornirlo?
karug64
Utente Senior
 
Post: 746
Iscritto il: 20/11/11 21:22

Re: [Excel]: formula per somme "tipo" subtotali

Postdi karug64 » 01/05/19 00:45

klingklang ha scritto:Prova con
=SE(a2<>a1;0;a1+1)

In realtà, anche senza colonna di appoggio, basterebbe un SOMMA.PIÙ.SE sulle 4 colonne chiave e con un'unica formula avresti risolto.


Saresti così gentile da farmi vedere come? Grazie
karug64
Utente Senior
 
Post: 746
Iscritto il: 20/11/11 21:22

Re: [Excel]: formula per somme "tipo" subtotali

Postdi klingklang » 01/05/19 01:47

karug64 ha scritto:
klingklang ha scritto:Prova con
=SE(a2<>a1;0;a1+1)

In realtà, anche senza colonna di appoggio, basterebbe un SOMMA.PIÙ.SE sulle 4 colonne chiave e con un'unica formula avresti risolto.


Saresti così gentile da farmi vedere come? Grazie


Se ad esempio le 4 colonne chiave sono A:D e la colonna da sommare è la E:
=SOMMA.PIÙ.SE($E$1:$E$50000;$A$1:$A$50000;A1;$B$1:$B$50000;B1;$C$1:$C$50000;C1;$D$1:$D$50000;D1)
Avatar utente
klingklang
Utente Junior
 
Post: 97
Iscritto il: 23/11/18 15:01
Località: San Giovanni in Persiceto

Re: [Excel]: formula per somme "tipo" subtotali

Postdi Anthony47 » 01/05/19 09:48

Non dovrebbe essere difficile creare un file ridotto con le colonne giuste, bastano quelle con cui generi la chiave, sostituendo i Nominativi con equivalenze di fantasia.
Comunque, lavorando sul layout che hai pubblicato potresti usare questa macro:
Codice: Seleziona tutto
Sub NonSoChe()
Dim shP As Worksheet, cPil As String, WArr, LastCP As Long
Dim oArr(), LB0, FI As Long, I As Long, myTim As Single
'
cPil = "A"                                  '<<< La colonna con gli 1/0
'Set shP = ThisWorkbook.Sheets("Foglio1")    '*** Il Foglio di lavoro
Set shP = ActiveSheet                       '*** Alternativa alla istruz precedente
'
myTim = Timer
LastCP = shP.Cells(Rows.Count, cPil).End(xlUp).Row
WArr = shP.Cells(1, cPil).Resize(LastCP + 1, 5).Value
LB0 = LBound(WArr)
ReDim oArr(LB0 To UBound(WArr), 1 To 2)
For I = LBound(WArr) + 1 To UBound(WArr) - 1
    If WArr(I + 1, LB0) = 0 Or (WArr(I + 1, LB0) = 1 And FI > 0) Then
        If FI = 0 Then FI = I - 1
        oArr(FI, 1) = oArr(FI, 1) + WArr(I, LB0 + 3)
        oArr(FI, 2) = oArr(FI, 2) + WArr(I, LB0 + 4)
        If WArr(I + 1, LB0) = 1 Then FI = 0
    End If
Next I
shP.Cells(2, cPil).Offset(0, 1).Resize(LastCP, 2) = oArr
MsgBox ("Completato, sec: " & Format(Timer - myTim, "0.00"))
End Sub

Presuppone che ci sia la colonna "pilota" (A, nell'esempio), con accanto due colonne libere e subito accanto le due colonne con i valori da sommare.
L'istruzione marcata <<< permette di dichiarare una colonna diversa.

Poi basta lanciare la Sub NonSoChe; un messaggio informa del completamento.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel]: formula per somme "tipo" subtotali

Postdi karug64 » 01/05/19 14:37

klingklang ha scritto:
karug64 ha scritto:
klingklang ha scritto:Prova con
=SE(a2<>a1;0;a1+1)

In realtà, anche senza colonna di appoggio, basterebbe un SOMMA.PIÙ.SE sulle 4 colonne chiave e con un'unica formula avresti risolto.


Saresti così gentile da farmi vedere come? Grazie


Se ad esempio le 4 colonne chiave sono A:D e la colonna da sommare è la E:
=SOMMA.PIÙ.SE($E$1:$E$50000;$A$1:$A$50000;A1;$B$1:$B$50000;B1;$C$1:$C$50000;C1;$D$1:$D$50000;D1)


Soluzione adotta e funzionante.
Grazie
Office 2010
karug64
Utente Senior
 
Post: 746
Iscritto il: 20/11/11 21:22


Torna a Applicazioni Office Windows


Topic correlati a "[Excel]: formula per somme "tipo" subtotali":


Chi c’è in linea

Visitano il forum: Gianca532011 e 88 ospiti