[Excel]: formula per somme "tipo" subtotali

[karug64]

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

[klingklang]

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.

[karug64]

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

[Anthony47]

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

[karug64]

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 !!!

[Anthony47]

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...

[klingklang]

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.

[karug64]

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]

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

[klingklang]

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)

[Anthony47]

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

[karug64]

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