[Risolto] Estrazione dati unici da elenco + collegamento

Discussioni sull'applicazione per i fogli di calcolo
Rispondi
Radagast
Messaggi: 19
Iscritto il: giovedì 21 settembre 2017, 23:35

[Risolto] Estrazione dati unici da elenco + collegamento

Messaggio da Radagast »

Buongiorno, scusate il titolo un po' criptico ma non mi veniva niente di più preciso.
Come vedrete nel file di esempio che ho allegato (spero molto più esplicativo di quanto dirò di seguito), avrei bisogno di estrarre valori univoci da un elenco di nomi.
Accanto a questi nomi estratti, avrei bisogno di poter mettere nuovi dati.
Vorrei che questi dati rimanessero collegati ai nomi accanto a cui li ho messi inizialmente, anche dopo aver aggiornato l'elenco per l'eventuale inserimento o cancellazione di nuovi nominativi.
Il tutto dovrebbe essere il più automatico possibile, meglio se con formule ma, se semplificasse molto le cose, andrebbe bene anche una macro.

Ho provato usando i filtri standard per eliminare i duplicati e poi copia/incolla, ma diventa macchinoso e a volte sfugge il fatto che può essere stato introdotto un nuovo fornitore e quindi va aggiornata la lista.
Ho provato anche con la tabella pivot, ma non riesco a far rimanere i dati delle colonne aggiuntive collegati ai nomi (v. file esempio).
Per finire ho usato anche l'opzione Filtro (di area dati) --> copia risultati in... ---> foglio dati 2 nell'esempio. Sembra la soluzione più vicina a quello che cerco, in quanto facilmente aggiornabile (aggiorna area), e ordinando manualmente tutti i dati, però alla cancellazione di un fornitore si ripresenta il problema dei dati delle altre colonne non più collegati ai giusti fornitori...

Spero che con il file d'esempio qualcuno riesca a capire quello che ho scritto :P

Grazie per l'aiuto che mi saprete dare!
Allegati
Esempio.ods
(12.71 KiB) Scaricato 272 volte
Ultima modifica di Radagast il martedì 16 aprile 2019, 23:49, modificato 1 volta in totale.
Libreoffice 5.2.7.2 su Windows 8
Gaetanopr
Volontario
Volontario
Messaggi: 3300
Iscritto il: mercoledì 21 novembre 2012, 20:07

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Gaetanopr »

Ciao, ti allego soluzione con MACRO, la macro riporta i dati sul Foglio2, ho creato un nome definito Fornitori per l'area $Fatture.$B$2:$E$100.
Se il tuo file reale è diverso, bisognerà apportare qualche modifica alla macro.
La macro si attiva in maniera automatica all'apertura del Foglio2
Allegati
Esempio (3).ods
(16.37 KiB) Scaricato 280 volte
LibreOffice 7.2.2.2 windows 10
Openoffice 4.1.13 su windows 10
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1719
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da gioh66 »

Buondì, con le formule, supponendo che tu voglia la tua lista nel Foglio2, in A2 metti:

Codice: Seleziona tutto

=SE.ERRORE(INDICE(Fatture!$B$2:$B$1000;CONFRONTA(0;INDICE(CONTA.SE($A$1:A1;Fatture!$B$2:$B$1000);0);0));"")
e trascina in basso.
Se il tuo range di nomi è più ampio di Fatture!$B$2:$B$1000 ti basta aumentare il valore di righe da esaminare.
Allegati
dati univoci.ods
(16.56 KiB) Scaricato 208 volte
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
Radagast
Messaggi: 19
Iscritto il: giovedì 21 settembre 2017, 23:35

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Radagast »

Innanzitutto un grande ringraziamento a Gaetanopr e gioh66 per le loro soluzioni! La formula di gioh66 sarebbe perfetta ma ha un solo difetto, ovvero non mi permette di ordinare i dati in ordine alfabetico.
Credo che userò la soluzione di Gaetanopr, aggiungendo un tasto macro per un facoltativo ordinamento alfabetico.
Immagino che non ci sia un metodo per evitare il prompt ad abilitare le macro senza abbassare il livello di sicurezza (ad es certificando _una_ sola macro), vero?
Lascio aperto il thread ancora per un po' nel caso venissero proposte altre soluzioni (tutto da imparare per me, quindi ben vengano!)
Libreoffice 5.2.7.2 su Windows 8
Avatar utente
gioh66
Volontario
Volontario
Messaggi: 1719
Iscritto il: lunedì 31 luglio 2017, 14:57
Località: Friuli

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da gioh66 »

Se ti servono in ordine alfabetico prova questa, sempre in A2 da trascinare in basso:

Codice: Seleziona tutto

=SE.ERRORE(INDICE(Fatture!$B$2:$B$100;CONFRONTA(0;INDICE(CONTA.SE(Fatture!$B$2:$B$100;"<"&Fatture!$B$2:$B$100)-MATR.SOMMA.PRODOTTO(CONTA.SE(A$1:A1;Fatture!$B$2:$B$100)););0));"")
Allegati
dati univoci (bis).ods
(13.37 KiB) Scaricato 160 volte
...se sei soddisfatto delle risposte ricevute metti il [Risolto] https://forum.openoffice.org/it/forum/v ... f=9&t=5661

Libreoffice 6/7 Ubuntu 22.04 - PcLinuxOS - LinuxMint 21
Gaetanopr
Volontario
Volontario
Messaggi: 3300
Iscritto il: mercoledì 21 novembre 2012, 20:07

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Gaetanopr »

Radagast ha scritto: avrei bisogno di estrarre valori univoci da un elenco di nomi.
Accanto a questi nomi estratti, avrei bisogno di poter mettere nuovi dati.
Vorrei che questi dati rimanessero collegati ai nomi accanto a cui li ho messi inizialmente, anche dopo aver aggiornato l'elenco per l'eventuale inserimento o cancellazione di nuovi nominativi.
Scusate ma sfugge un piccolo particolare, non dicevi che le x dovevano rimanere ancorate ai precedenti valori??? con la formula rimangono nelle righe di origine, non seguono il fornitore.
Fai test

Se vuoi i dati ordinati, posso modificare la macro
LibreOffice 7.2.2.2 windows 10
Openoffice 4.1.13 su windows 10
Radagast
Messaggi: 19
Iscritto il: giovedì 21 settembre 2017, 23:35

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Radagast »

Gaetanopr ha scritto:
Radagast ha scritto: avrei bisogno di estrarre valori univoci da un elenco di nomi.
Accanto a questi nomi estratti, avrei bisogno di poter mettere nuovi dati.
Vorrei che questi dati rimanessero collegati ai nomi accanto a cui li ho messi inizialmente, anche dopo aver aggiornato l'elenco per l'eventuale inserimento o cancellazione di nuovi nominativi.
Scusate ma sfugge un piccolo particolare, non dicevi che le x dovevano rimanere ancorate ai precedenti valori??? con la formula rimangono nelle righe di origine, non seguono il fornitore.
Fai test

Se vuoi i dati ordinati, posso modificare la macro
Vero, perde le x.
La macro potrebbe andare bene così, in quanto preferisco lasciare l'ordinamento come facoltativo perchè mi permette di vedere per ultime quelle mai inserite prima. Così posso aggiungere "x" e altri dati e poi riordinare in seguito.
O meglio, forse potrei usare un sistema di colori (altre x :lol: ), cioè colorare le caselle accanto a quelle che ho già processato, così se viene inserita una nuova questa avrà una casella non colorata accanto. Oppure colorare le caselle contenenti il nome stesso, a meno che il refresh non cancelli eventuali formattazioni.
A questo punto saresti così gentile da mandarmi anche la macro con l'ordinamento automatico?
PS: ho provato ad adattare la macro al mio foglio, assegnando un nome con range B2:E2000, ma la macro restituisce un errore "IndexOutOfBoundsException", quindi credo che sia oltre il range previsto... Dove sbaglio? O meglio cosa dovrei modificare?

Aggiornamento: forse ho capito il problema. Nel file ho un'altra colonna dati tra la colonna N.doc e Importo. Ho quindi modificato così:
Dati() = Sh2.getCellRangeByPosition(0, 1, 2, 3, LastRow).getDataArray
Sh2.getCellRangeByPosition(0, 1, 2, 3, LastRow).ClearContents(1 +2 +3 +4)

Altra richiesta al volo: sarebbe possibile avere la macro autoaggiornante che elenca i fornitori dalla casella A3? :oops: (Risolto impostando For i = 2 To Dic.Count. E' giusto o comporta qualche problema?)
Ultima modifica di Radagast il domenica 14 aprile 2019, 20:07, modificato 1 volta in totale.
Libreoffice 5.2.7.2 su Windows 8
Gaetanopr
Volontario
Volontario
Messaggi: 3300
Iscritto il: mercoledì 21 novembre 2012, 20:07

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Gaetanopr »

Le modifiche che hai apportato sono errate, allega file con schema uguale in tutto all'originale che ti aggiorno la macro
LibreOffice 7.2.2.2 windows 10
Openoffice 4.1.13 su windows 10
Radagast
Messaggi: 19
Iscritto il: giovedì 21 settembre 2017, 23:35

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Radagast »

Gaetanopr ha scritto:Le modifiche che hai apportato sono errate, allega file con schema uguale in tutto all'originale che ti aggiorno la macro
Eccolo qua, rispecchia abbastanza fedelmente l'originale.
PS: ho notato che copiando la tua macro nel mio foglio (prima di provare a modificarla), ha perso l'auto-aggiornamento ogni volta che apro il Foglio2 (ora chiamato Calcoli). Why? :|
Allegati
Esempio.ods
(16.62 KiB) Scaricato 133 volte
Libreoffice 5.2.7.2 su Windows 8
Gaetanopr
Volontario
Volontario
Messaggi: 3300
Iscritto il: mercoledì 21 novembre 2012, 20:07

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Gaetanopr »

Ho modificato la macro e commentato tutte le parti modificate, in modo da essere più comprensibile.
La macro è associata all'evento "attiva documento" del foglio Calcoli, per associare la macro al tuo file originale clicca sulla linguetta del foglio - tasto dx - eventi foglio - attiva documento - macro.
Allegati
Esempio (4).ods
(16.68 KiB) Scaricato 105 volte
LibreOffice 7.2.2.2 windows 10
Openoffice 4.1.13 su windows 10
Radagast
Messaggi: 19
Iscritto il: giovedì 21 settembre 2017, 23:35

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Radagast »

Grazie per i commenti nella macro, magari mi aiutano a capirla un po' meglio!
Alcune domande prima di provare ad adattarla al mio foglio:
1) Ho notato che non mette l'elenco in ordine alfabetico, è possibile farglielo fare automaticamente?
2) La macro è ovviamente case-sensitive perchè mi inserisce nell'elenco sia pluto che Pluto, tuttavia la somma riportata è uguale per entrambe le voci, come se la somma non fosse case-sensitive. (Non dovrebbe essere un problema perchè i nomi dei fornitori dovrebbero essere generati standard da un pc, quindi sempre uguali. _Dovrebbero_)
Libreoffice 5.2.7.2 su Windows 8
Gaetanopr
Volontario
Volontario
Messaggi: 3300
Iscritto il: mercoledì 21 novembre 2012, 20:07

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Gaetanopr »

Ho aggiunto la parte per riordinare i dati, ora la somma è pure case sensitive(almeno così ho capito)
Allegati
Esempio (4).ods
(16.1 KiB) Scaricato 108 volte
LibreOffice 7.2.2.2 windows 10
Openoffice 4.1.13 su windows 10
Radagast
Messaggi: 19
Iscritto il: giovedì 21 settembre 2017, 23:35

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Radagast »

Ci siamo quasi! Abbi ancora un po' di pazienza, ma anche io sto sperimentando con questo nuovo foglio e man mano sorgono nuove necessità!
Ho notato che se inserisco testo o numeri nelle colonne Dati del foglio Calcoli va tutto bene. Se inserisco delle formule, al refresh me le trasforma in numeri o addirittura in testo (apice davanti ai numeri).
Un esempio di formula potrebbe essere (B3*100/SOMMA($B$3:$B$500)), incollata su tutta la colonna D a partire da B3.
Libreoffice 5.2.7.2 su Windows 8
Gaetanopr
Volontario
Volontario
Messaggi: 3300
Iscritto il: mercoledì 21 novembre 2012, 20:07

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Gaetanopr »

Radagast ha scritto:Se inserisco delle formule, al refresh me le trasforma in numeri o addirittura in testo (apice davanti ai numeri).
Un esempio di formula potrebbe essere (B3*100/SOMMA($B$3:$B$500))
E' normale, tu non mi dai retta
Gaetanopr ha scritto:Le modifiche che hai apportato sono errate, allega file con schema uguale in tutto all'originale che ti aggiorno la macro
Se non riesci ad adattare la macro da solo e ti dico schema uguale, devi darmi uno schema identico, quindi devi indicarmi dove inserisci le formule testi e numeri.

Se ci sono colonne dove inserisci formule, puoi modificare tu stesso la macro, togliere la parte di codice che riguarda la compilazione di dette colonne( dovresti riuscirci, le righe di codice che le riguardano sono commentate).
LibreOffice 7.2.2.2 windows 10
Openoffice 4.1.13 su windows 10
Radagast
Messaggi: 19
Iscritto il: giovedì 21 settembre 2017, 23:35

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Radagast »

Gaetanopr ha scritto:
Radagast ha scritto:Se inserisco delle formule, al refresh me le trasforma in numeri o addirittura in testo (apice davanti ai numeri).
Un esempio di formula potrebbe essere (B3*100/SOMMA($B$3:$B$500))
E' normale, tu non mi dai retta
Gaetanopr ha scritto:Le modifiche che hai apportato sono errate, allega file con schema uguale in tutto all'originale che ti aggiorno la macro
Se non riesci ad adattare la macro da solo e ti dico schema uguale, devi darmi uno schema identico, quindi devi indicarmi dove inserisci le formule testi e numeri.

Se ci sono colonne dove inserisci formule, puoi modificare tu stesso la macro, togliere la parte di codice che riguarda la compilazione di dette colonne( dovresti riuscirci, le righe di codice che le riguardano sono commentate).
Hai perfettamente ragione, ma come avevo detto, il foglio non è ancora ben definito e quindi si stanno aggiungendo alcune necessità in corso d'opera.
Ho modificato la macro (era semplicissimo in effetti), però è sorto un altro problema:
nel foglio allegato, se copio/incollo la formula presente in Calcoli D8 nelle caselle sottostanti (D9 ecc), al momento del refresh la macro si pianta. Ho notato che dipende dalla funzione SE, almeno credo. Purtroppo la soluzione di questo problema è al di fuori della mia portata :/
Allegati
Esempio 5.ods
(17.75 KiB) Scaricato 78 volte
Libreoffice 5.2.7.2 su Windows 8
Gaetanopr
Volontario
Volontario
Messaggi: 3300
Iscritto il: mercoledì 21 novembre 2012, 20:07

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Gaetanopr »

Radagast ha scritto: Ho modificato la macro (era semplicissimo in effetti), però è sorto un altro problema:
nel foglio allegato, se copio/incollo la formula presente in Calcoli D8 nelle caselle sottostanti (D9 ecc), al momento del refresh la macro si pianta. Ho notato che dipende dalla funzione SE, almeno credo. Purtroppo la soluzione di questo problema è al di fuori della mia portata :/
E si ha più soddisfazione a farlo da solo, per quanto riguarda la formula la funzione SE non interferisce, è la macro, precisamente la funzione CercaVerticale che và in errore non trovando i dati.
Ti riallego il file, ora dovrebbe funzionare pure con le formule che inserisci al di sotto dei dati.
Allegati
Esempio 5.ods
(17.12 KiB) Scaricato 135 volte
LibreOffice 7.2.2.2 windows 10
Openoffice 4.1.13 su windows 10
Radagast
Messaggi: 19
Iscritto il: giovedì 21 settembre 2017, 23:35

Re: Estrazione dati unici da elenco + collegamento con dati

Messaggio da Radagast »

Applicato al foglio dati. Funziona perfettamente!
Ancora grazie, per l'aiuto e per la pazienza!
Libreoffice 5.2.7.2 su Windows 8
Rispondi