controllo stringa/estrazione dati/calcolo

[gabriele.calcinaia]

Ciao a tutti, vorrei riuscire a risolvere un problema che mi assilla da giorni
tra l'altro non so neanche che titolo dare a questo post
ringrazio anticipatamente chi si soffermerà a leggere.

Ho un elenco di codici (COLONNA a) derivanti dalla riparazione di alcuni prodotti.
Questi sono codici composti da valori esistenti in altre celle (vedi riquadro in D2)
Con il tempo, si formerà un elenco infinito di questi "codici completi"
Mi piacerebbe poter fare la somma dei numeri finali (che equivalgono al tempo di lavorazione) dei codici che hanno la prima parte identica
Esempio in C18

Dovrei scrivere una formula che seleziona le righe in base alla prima parte del codice (composta da tre valori divisi da un punto)
dopo di che, dovei sommare i vari numeri ricavati

L'idea che mi era venuta, era quella di usare delle "maschere" dedicate ad ogni pagina, nella quale faccio comparire solo
i codici contenenti determinati valori, e poi posso procedere con la somma dei numeri, estrapolando i numeri stessi.
Il problema è che ho tantissimi codici diversi, e dovrei fare altrettante pagine..


Allego il file d'esempio
Grazie a tutti

[charlie]

Ciao e benvenuto sul forum.
Se intanto ti vuoi presentare puoi farlo qui: viewforum.php?f=16
Per una panoramica delle regole del forum consulta il Manuale di sopravvivenza: http://forum.openoffice.org/it/forum/vi ... hp?f=1&t=2
Buon proseguimento.

[gioh66]

Buondì, potresti creare una colonna dei codici univoci, io nel tuo esempio l'ho messo da E18 in giù con questa formula matriciale da copiare nelle celle in basso (le formule matriciali si confermano con la combinazione di tasti (CTRL+MAIUSCOLO+INVIO)

Codice: Seleziona tutto

=SE(VAL.ERRORE(INDICE(RIMPIAZZA($A$4:$A$200;TROVA(".";$A$4:$A$200;TROVA(".";$A$4:$A$200;TROVA(".";$A$4:$A$200)+1)+1);10;"");CONFRONTA(0;INDICE(CONTA.SE($E$18:E18;RIMPIAZZA($A$4:$A$200;TROVA(".";$A$4:$A$200;TROVA(".";$A$4:$A$200;TROVA(".";$A$4:$A$200)+1)+1);10;"")));0)));"";INDICE(RIMPIAZZA($A$4:$A$200;TROVA(".";$A$4:$A$200;TROVA(".";$A$4:$A$200;TROVA(".";$A$4:$A$200)+1)+1);10;"");CONFRONTA(0;INDICE(CONTA.SE($E$18:E18;RIMPIAZZA($A$4:$A$200;TROVA(".";$A$4:$A$200;TROVA(".";$A$4:$A$200;TROVA(".";$A$4:$A$200)+1)+1);10;"")));0)))

Poi a fianco, con la seguente formula, messa in F18 e trascinata in basso

Codice: Seleziona tutto

=SE(E18="";"";MATR.SOMMA.PRODOTTO(VAL.NUMERO(TROVA(E18;$A$4:$A$12));VALORE(RIMPIAZZA($A$4:$A$12;1;TROVA(".";$A$4:$A$12;TROVA(".";$A$4:$A$12;TROVA(".";$A$4:$A$12)+1)+1);""))))

Sommo le cifre dopo l'ultimo punto.
Ps.: se metti la formula in un'altra cella devi correggere, nella prima formula, la prima parte del CONTA.SE inserendo i riferimenti della cella in cui inserisci la formula. Per capirci, io l'ho messa in E18 per cui la formula è CONTA.SE($E$18:E18...
Se ad esempio invece tu la mettessi in B1 allora dovresti modificarla così CONTA.SE($B$1:B1...Tutto il resto non cambia.

[gabriele.calcinaia]

Ciao gioh66, grazie mille per la risposta...
sono uno che scrive delle stringhe lunghe e complicate, però mi devo ricredere vedendo le tue

prima di andare avanti, devo studiarmi il funzionamento della formula matriciale (al momento a leggerla mi viene solo fame )
e devo leggermi le nuove funzioni per me, la INDICE, RIMPIAZZA, MATR.SOMMA.PRODOTTO

e formule matriciali si confermano con la combinazione di tasti (CTRL+MAIUSCOLO+INVIO)

non capisco cosa vuol dire, scusa

GRAZIE ANCORA

[gioh66]

Buondì, le formule matriciali a differenza delle formule normali, che una volta scritte basta dare invio, necessitano della combinazione di tasti ctrl+ maiuscolo+invio per essere confermate. Inoltre a differenza delle formule normali non le puoi trascinare in basso, ma le devi copiare e incollare per applicarle alle celle sottostanti (o se hai a sinistra una colonna piena puoi anche applicarla in basso con un doppio click sulla maniglia della cella, il quadratino nero in basso a destra della cella attiva).
Le formule matriciali poi sono molto comode, come in questo caso, e servono a "costringere" le formule che di solito lavorano solo con una cella (in questo caso RIMPIAZZA) a lavorare invece con un intervallo.

Per quanto riguarda la lunghezza della prima formula è dovuta alla mancanza in openoffice della funzione SE.ERRORE. Per fare in modo che nelle celle in cui non c'è corrispondenza non si visualizzi un errore tipo #N/D, #VALORE ecc... ho messo la formula in un SE(VAL.ERRORE(... che richiede di immettere la stessa due volte. In pratica la sintassi è
=SE(VAL.ERRORE(formula);"";formula)
In libreoffice, che è più aggiornato e dove è disponibile la funzione SE.ERRORE, la formula sarebbe stata
=SE.ERRORE(formula;"")
decisamente più corta.

[gabriele.calcinaia]

Buon giorno gioh66, mi sono preso un po di tempo per capire quello che avevi scritto, ma non ci sono riuscito.
Ho preso la tua formula e ho iniziato a sezionarla, in modo da riuscire a comprendere il funzionamento dai piccoli calcoli.
Ma non ci sono riuscito ugualmente, da una parte perchè le singole operazioni, prese da sole, non danno un risultato.

allego il file che la funzione smembrata, per capire passo dopo passo che cosa calcola la funzione e perche. Se non capisco non so neanche come modificarla.
Intanto volevo iniziare con le formule della colonna E, poi passiamo alla F
In più, non ho ben chiaro le formule matriciali, ho provato a a utilizzare i tre tasti, ma non ho nessun risultato.
Devo prima copiare la formula e poi incollarla con i tre tasti?
Grazie

[gioh66]

Buondì, ho rivisto leggermente le formule e ho creato, nell'allegato, uno schema delle formule scomposte.
Allora, partendo dalla colonna A, dove ci sonio i dati di partenza, in B4, da trascinare in basso, ho messo

Codice: Seleziona tutto

=SOSTITUISCI(A4;".";"@";3)

Questa formula sostituisci il terzo . (punto) con @, questo per inserire un carattere univoco che servirà per le altre formule.

Poi in C4, da trascinare in basso, per estrarre il testo fino a "@" ho messo

Codice: Seleziona tutto

=RIMPIAZZA(B4;TROVA("@";B4);100;"")

Proseguendo in D4, da trascinare in basso, ho messo, per estrarre il numero dopo @

Codice: Seleziona tutto

=VALORE(STRINGA.ESTRAI(B4;TROVA("@";B4)+1;100))

Poi in E4, per estrarre la lista univoca dei codici, ricavati con la formula in B4, ho messo la formula matriciale (da confermare con ctrl+maiuscolo+invio e copiare in basso)

Codice: Seleziona tutto

=INDICE($C$4:$C$15;CONFRONTA(0;INDICE(CONTA.SE($E$4:E4;$C$4:$C$15));0))

E infine in F4, da trascinare in basso, per sommare i numeri estrapolati in C4, ho usato

Codice: Seleziona tutto

=SE($E4="";"";MATR.SOMMA.PRODOTTO(VAL.NUMERO(TROVA(E4;$A$4:$A$15));VALORE($D$4:$D$15)))

Qui la prima parte =SE($E4="";"";ecc...) serve solo a non visualizzare gli 0 nel caso che la cella e fosse vuota.

Per non fare tutte queste colonne d'appoggio si può accorpare le formule e per il codice univoco, in H4 ho usato la formula matriciale

Codice: Seleziona tutto

=SE(VAL.ERRORE(INDICE(RIMPIAZZA(SOSTITUISCI($A$4:$A$15;".";"@";3);TROVA("@";SOSTITUISCI($A$4:$A$15;".";"@";3));100;"");CONFRONTA(0;INDICE(CONTA.SE($H$4:H4;RIMPIAZZA(SOSTITUISCI($A$4:$A$15;".";"@";3);TROVA("@";SOSTITUISCI($A$4:$A$15;".";"@";3));100;"")));0)));"";INDICE(RIMPIAZZA(SOSTITUISCI($A$4:$A$15;".";"@";3);TROVA("@";SOSTITUISCI($A$4:$A$15;".";"@";3));100;"");CONFRONTA(0;INDICE(CONTA.SE($H$4:H4;RIMPIAZZA(SOSTITUISCI($A$4:$A$15;".";"@";3);TROVA("@";SOSTITUISCI($A$4:$A$15;".";"@";3));100;"")));0)))

Qui la formula è un po' corposa perchè per non visualizzare gli errori e mancando ancora in Openoffice calc la formula SE.ERRORE, la cui sintassi sarebbe
=SE.ERRORE(formula;"")
si deve usare il =SE(VAL.ERRORE(formula);"";formula)
che la fa lievitare.

Mentre in I4 per la somma dei numeri ho usato

Codice: Seleziona tutto

=SE($H4="";"";MATR.SOMMA.PRODOTTO(VAL.NUMERO(TROVA($H4;$A$4:$A$15));VALORE(STRINGA.ESTRAI(SOSTITUISCI($A$4:$A$15;".";"@";3);TROVA("@";SOSTITUISCI($A$4:$A$15;".";"@";3))+1;100))))

Un appunto sulle formule matriciali, quando le copi in una cella è meglio se lo fai in modalità modifica (F2 o doppio click sulla cella). Se invece la copi nella cella attiva potresti avere un errore, ma per correggerlo basta aprire la cella con F2 o doppio click, cancellare l'ultima parentesi, rimetterla (in pratica simulare una modifica) e confermare con la combinazione di tasti ctrl+maiuscolo+invio
Inoltre le formule matrice non possono essere trascinate in basso (o a destra) ma vanno copiate e incollate nelle celle interessate.

[unlucky83]

Ciao, propongo quanto segue:
- Colonna di appoggio B per scoprire la lunghezza della prima parte del codice perchè dagli esempi risulta essere di lunghezza variabile (xxx.xxx.xxx) usando

Codice: Seleziona tutto

=TROVA(".";A4;TROVA(".";A4;TROVA(".";A4)+1 )+1)-1

- Colonna di appoggio C per estrarre la prima parte del codice (xxx.xxx.xxx) usando

Codice: Seleziona tutto

=SINISTRA(A4;B4)

- Colonna di appoggio D per estrarre la seconda parte del codice (xxx), riguardante i tempi, usando

Codice: Seleziona tutto

=VALORE(DESTRA(A4;LUNGHEZZA(A4)-B4-1))

- Tabella pivot con area sorgente le colonne B:D
Nell'esempio che allego ho scelto come area $Foglio1.$C$3:$D$12000, così non è necessario modificare la pivot fino a che non superi il rigo 12000 e come spia puoi usare la voce "vuoto" che compare nella pivot: fino a che c'è questa voce, l'area sorgente è maggiore dei dati inseriti, quando questa voce scompare hai raggiunto il limite dell'area sorgente o addirittura l'hai superata quindi rischi di avere dei dati parziali nella Pivot. Quando la voce "vuoto" scompare dall'elenco, quindi, sai che devi aumentare l'area sorgente della pivot.
La pivot non si aggiorna automaticamente, quindi di tanto in tanto devi cliccarci sopra con il tasto destro e selezionare "Aggiorna Pivot"

[gioh66]

Buondì @unlucky83. Se guardi bene i dati, pur essendo le stringhe di lunghezza diversa sono tutte caratterizzate dall'essere separate da tre punti e dall'avere la cifra dopo l'ultimo punto.
Grazie a questo fattore ho costruito le due ultime formule del mio ultimo messaggio senza bisogno di fare colonne d'appoggio.
Le prime formule, in cui anch'io avevo fatto le colonne d'appoggio, servivano a Gabriele per comprendere la sintassi di queste due ultime formule, come richiesto nel suo messaggio precedente al mio, e che gli avevo già proposto, leggermente diverse nel mio primo intervento.
Questo solo per un chiarimento sul mio lavoro, che non vuole assolutamente mettere in discussione la tua soluzione, anzi mi piacerebbe più spesso vedere più variabili alla soluzione di un problema.

[unlucky83]

L'idea che mi era venuta, era quella di usare delle "maschere" dedicate ad ogni pagina, nella quale faccio comparire solo
i codici contenenti determinati valori, e poi posso procedere con la somma dei numeri, estrapolando i numeri stessi.
Il problema è che ho tantissimi codici diversi, e dovrei fare altrettante pagine..

Ciao @gioh66
Leggendo questo passaggio, ho pensato che invece di creare chissà quali pagine o selezionare manualmente chissà quali sottocodici, fosse più pratico avere una tabella sintetica con tutte le somme. La pivot consente questo e può anche ordinare in maniera alfabetica i sottocodici, così da permettere facilmente di individuare la somma cercata. Siccome è uno strumento poco usato, nonostante che sia molto potente, ho voluto proporre una procedura che ne prevede l'utilizzo.
Lo svantaggio della Pivot è che richiede necessariamente minimo due colonne di appoggio (le colonne C e D del mio esempio). La colonna ausiliaria B non è funzionale alla pivot, ma solo alla comprensione delle formule di estrazione del sottocodice e le formule in essa contenute possono essere inserite nelle formule delle colonne C e D.

Se guardi bene i dati, pur essendo le stringhe di lunghezza diversa sono tutte caratterizzate dall'essere separate da tre punti e dall'avere la cifra dopo l'ultimo punto.

La formula in colonna B si basa proprio sui tre "." e mi pare funzionare.

Vien da sè, che se non è possibile usare le colonne di appoggio, la mia proposta è da cestinare perchè senza di esse è impraticabile

[gioh66]

Ciao @Unlucky, effettivamente io non ho molta dimestichezza con le pivot, per cui non ci penso, ma effettivamente se non disturbano le colonne d'appoggio permettono di evitare formule complesse, così ho voluto provarci anch'io e la mia idea è questa.
Partendo dal primo file postato da @gabriele.calcinaia, in C3 ho messo questa formula

Codice: Seleziona tutto

=SOSTITUISCI(A4;".";"@";3)

per sostituire l'ultimo punto con la chiocciola e tirato in basso.
Poi con la colonna ancora selezionata sono andato su Dati>testo a colonne e scelto come separatore la chiocciola. Così facendo la colonna C si è divisa in due colonne, dove in C c'è il codice e in D il valore. Ho aggiunto un'intestazione alle due colonne e fatto una tabella Pivot.
Devo ammetterlo è un sistema rapido e abbastanza semplice, dovrò studiarmele meglio stè Pivot.