Tabella pivot: formula per concatenare concatenare 2 campi (o altra soluzione)

[draleo]

Ho un elenco di dati ordinati in base alla colonna nome (nella quale vi sono molti doppioni)
Nome prodotto spesa
Bianchi Ada ramipril 2
Bianchi Ada ramipril 2
Bianchi Ada ramipril 2
Rossi Mario tavor 5
Rossi Mario ramipril 2
Rossi Mario ramipril 2

Riesco a realizzare una tab pivot che somma la spesa delle varie descrizioni
Prodotto spesa
Tavor 5
Ramipril 10

Vorrei aggiungere una colonna che CONTEGGI il num delle persone che prendono quel prodotto ,ma in questa colonna, le varie accoppiate (nome e prodotto) dovrebbero essere contata una sola volta , per ottenere la seguente tab pivot
Prodotto somma di spesa conteggio di nomi
Tavor 5 1
Ramipril 10 2

grazie mille
draleo

[r]

Penso che dovrai usare una colonna d'appoggio con una formula tipo 1/totale_occorrenze sai farlo?

[draleo]

Il giorno giovedì 24 ottobre 2013 09:24:29 UTC+2, r ha scritto:
> Penso che dovrai usare una colonna d'appoggio con una formula tipo 1/totale_occorrenze sai farlo?

Non credo. Questa colonna d'appoggio dove va messa ? nella tab orinaria oppure nella tab pivot? E come è questa formula che calcola le ricorrenze ?
Grazie

[r]

=1/somma((a2=a$2$:a$100$)*(b2=b$2$:b$100$))

Aggiungi una colonna nella tabella con questa formula dalla cella e2 poi la trascini, matriciale. Nella pivot sará nel campo valori come somma ... Fai sapere perché non ho provato nulla è scrivo dal cellulare
saluti
r

[draleo]

la formula, scritta come dici, da errore.
se invece la modifico in questo modo (sempre matriciale)
=1/SOMMA((A2=$A$2:$A$100)*(B2=$B$2:$B$100))
allora funziona; ma restituisce dei num decimali (0,25-0,333 ecc).
Che significano ? Trattandosi di contare dei nomi di persone, credo dovrei ottenere num interi.
draleo

[draleo]

Ho capito !in ritardo, ma poi ho capito. Sommando i vari valori decimali, ottengo degli interi ,che corrisponde al numero cecato. Ottimo
grazie
draleo

[draleo]

Torno sull'argomento. La formula matriciale proposta da r va benissimo, ma quando viene utilizzata su 20 mila righe (come nel mio caso), rallenta notevolmente la formazione della tab pivot. Il mio Pc (non più giovanissimo, ma neanche da buttare) impiega circa 90 secondi per completare l'operazione . C'è possibilità di applicare qualche altra formula più "umana" ?(cioè NON matriciale. ho infatti notato che se metto la stessa formula NON matriciale, i risultati sono errati, ma la tab pivot appare istantaneamente, quindi deduco sia la matriciale a rallentare il tutto)
draleo

[plinius]

Il 27/10/2013 10:27, draleo ha scritto:

> Il giorno gioved� 24 ottobre 2013 22:07:50 UTC+2, draleo ha scritto:
>> Ho capito !in ritardo, ma poi ho capito. Sommando i vari valori decimali, ottengo degli interi ,che corrisponde al numero cecato. Ottimo
>>
>> grazie
>>
>> draleo
>

> Torno sull'argomento. La formula matriciale proposta da r va benissimo, ma quando viene utilizzata su 20 mila righe (come nel mio caso), rallenta notevolmente la formazione della tab pivot. Il mio Pc (non pi� giovanissimo, ma neanche da buttare) impiega circa 90 secondi per completare l'operazione . C'� possibilit� di applicare qualche altra formula pi� "umana" ?(cio� NON matriciale. ho infatti notato che se metto la stessa formula NON matriciale, i risultati sono errati, ma la tab pivot appare istantaneamente, quindi deduco sia la matriciale a rallentare il tutto)
> draleo
>

Se a:
=1/SOMMA((A2=$A$2:$A$20000)*(B2=$B$2:$B$20000))
matriciale sostituisci:
=1/MATR.SOMMA.PRODOTTO((A2=$A$2:$A$20000)*(B2=$B$2:$B$20000))
NON matriciale, risparmi qualcosa in termini di tempo, ma sicuramente
non abbastanza.

Se vuoi dare un taglio sensibile ai tempi devi usare una colonna di
appoggio ulteriore dove inserisci nome e prodotto concatenati.

Metti in G2
=A2&B2

Quindi in H2
=1/CONTA.SE($G$2:$G$20000;A2&B2)

da copiare entrambe, in basso, fino a riga 20000


Ciao,
E.

[draleo]

Grazie. Funzionano entrambi i 2 sistemi. Ma per quanto riguarda i tempi, entrambi non si discostano molto dalla soluzione con Formula Matriciale. D'altra parte se non c'è di meglio, mi armerò di pazienza...

[plinius]

Il 28/10/2013 18:18, draleo ha scritto:
>
> Grazie. Funzionano entrambi i 2 sistemi. Ma per quanto riguarda i tempi, entrambi non si discostano molto dalla soluzione con Formula Matriciale. D'altra parte se non c'č di meglio, mi armerň di pazienza...
>
> draleo
>
>

Ho fatto un test per verificare i tempi impiegati dalle 3 formule.

Per quanto riguarda
=1/SOMMA((A2=$A$2:$A$20000)*(B2=$B$2:$B$20000))
e
=1/MATR.SOMMA.PRODOTTO((A2=$A$2:$A$20000)*(B2=$B$2:$B$20000))
il tempo č notevole (oltre 3 minuti per 20000 righe) e sostanzialmente
identico per le due formule.

La terza formula
=1/CONTA.SE($D$2:$D$20001;A2&B2)
basata su una ulteriore colonna di appoggio, registra invece un tempo di
circa 18 secondi (un decimo rispetto alle altre 2).

La differenza mi pare abissale!

Questo č il file:
http://sharesend.com/tl7be7xs

Ciao,
E.

[plinius]

Pare che il link non funzioni, proviamo questo:
http://www.4shared.com/office/16txvL1y/Test_vel.html

[draleo]

Si , in effetti la tua ultima soluzione accorcia di molto i tempi. Ma ne ho adottata una che adopera il VBA, e che riduce ancora i tempi (è quasi istantanea). Consiste nel concatenare in una prima colonna d’appoggio (T) il nome e il prodotto; poi ordino tutto l’elenco in base a questa colonna. A questo punto uso un’altra colonna d’appoggio (U), dove metto un’altra semplice formula: se il valore della riga superiore è uguale a quello della riga in questione, metto 0 ( perché trattasi di un doppione e quindi non va contato) altrimenti metto 1. Mi sembra che funzioni e sia velocissima

Sub conta ()
Dim riga as integer
Dim zonaformula as range
With Worksheets("statis")
riga = .Range("A" & Rows.count).End(xlUp).Row
.Range("T1") = "Concatena"
Set zonaformula = .Range("T2:T" & riga)
zonaformula.FormulaR1C1 = "=CONCATENATE(RC[-19],RC[-2])"
zonaformula.Value = zonaformula.Value

'ordina il tutto in base alla colonna di concatenazione
.Range("A1").CurrentRegion.Sort Key1:=.Range("T1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

.Range("U1") = "Num paz"
.Range("U2") = 1

Set zonaformula = .Range("U3:U" & riga)
zonaformula.FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],1,0)"
zonaformula.Value = zonaformula.Value

End With

End Sub

> > La terza formula
>
> > =1/CONTA.SE($D$2:$D$20001;A2&B2)
>
> > basata su una ulteriore colonna di appoggio, registra invece un tempo di
>
> > circa 18 secondi (un decimo rispetto alle altre 2).
>
> >
>
> > La differenza mi pare abissale!
>
> >
>

> > Questo è il file:

[plinius]

Il 29/10/2013 19:42, draleo ha scritto:
> Si , in effetti la tua ultima soluzione accorcia di molto i tempi. Ma ne ho adottata una che adopera il VBA, e che riduce ancora i tempi (è quasi istantanea). Consiste nel concatenare in una prima colonna d’appoggio (T) il nome e il prodotto; poi ordino tutto l’elenco in base a questa colonna. A questo punto uso un’altra colonna d’appoggio (U), dove metto un’altra semplice formula: se il valore della riga superiore è uguale a quello della riga in questione, metto 0 ( perché trattasi di un doppione e quindi non va contato) altrimenti metto 1. Mi sembra che funzioni e sia velocissima
>
> Sub conta ()
> Dim riga as integer
> Dim zonaformula as range
> With Worksheets("statis")
> riga = .Range("A" & Rows.count).End(xlUp).Row
> .Range("T1") = "Concatena"
> Set zonaformula = .Range("T2:T" & riga)
> zonaformula.FormulaR1C1 = "=CONCATENATE(RC[-19],RC[-2])"
> zonaformula.Value = zonaformula.Value
>
> 'ordina il tutto in base alla colonna di concatenazione
> .Range("A1").CurrentRegion.Sort Key1:=.Range("T1"), Order1:=xlAscending, Header:= _
> xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> .Range("U1") = "Num paz"
> .Range("U2") = 1
>
> Set zonaformula = .Range("U3:U" & riga)
> zonaformula.FormulaR1C1 = "=IF(RC[-1]<>R[-1]C[-1],1,0)"
> zonaformula.Value = zonaformula.Value
>
> End With
>
> End Sub
>

Col VBA la musica è diversa, non c'è dubbio :-)

[r]

Il giorno martedì 29 ottobre 2013 14:47:40 UTC+1, plinius ha scritto:

> La terza formula =1/CONTA.SE($D$2:$D$20001;A2&B2)

Il tuo file non riesco proprio a scaricarlo ... poi con tutte quelle scritte download sono stato presto scoraggiato ... comunque, due cose:
la tua penso sarebbe un filo più performante così:
=1/CONTA.SE($D$2:$D$20001;D2)

una versione più performante però potrebbe essere questa (penso):
=(CONTA.SE($D$2:D2;D2)=1)*1

saluti
r

[plinius]

Il 30/10/2013 11:09, r ha scritto:

> Il giorno martedě 29 ottobre 2013 14:47:40 UTC+1, plinius ha scritto:
>
>> La terza formula =1/CONTA.SE($D$2:$D$20001;A2&B2)
>
>
> Il tuo file non riesco proprio a scaricarlo ... poi con tutte quelle scritte download sono stato presto scoraggiato ... comunque, due cose:

> la tua penso sarebbe un filo piů performante cosě:
> =1/CONTA.SE($D$2:$D$20001;D2)
>
> una versione piů performante perň potrebbe essere questa (penso):

> =(CONTA.SE($D$2:D2;D2)=1)*1
>
> saluti
> r
>

Entrambe piů veloci, naturalmente!
Il test te l'ho mandato per e-mail
Ciao :-)