[RISOLTO] Dati da più colonne ed ordinarli in riga

[piri]

Salve a tutti.

Scusate il titolo del topic un po' criptico, ma non so bene come sintetizzare il problema.

Non sono pratico di macro e affini, ergo cerco di risolvermi i problemi solo con le formule...
Ahimè utilizzando solo queste non sono ancora riuscito a trovare una soluzione.

Il mio problema è questo: ho quattro colonne in cui inserisco alcune date. Non tutte le colonne, eccetto la prima, vengono riempite sempre.

Quello che non riesco a fare adesso è prendere le date dalle quattro colonne e metterle su un'unica riga ordinate in modo crescente.
So che potrei copiare i valori, trasporli con l'incolla speciale e poi ordinare la riga, ma, visto che gli inserimenti saranno molteplici, avrei necessità che l'operazione avvenisse in modo automatico all'inserimento della data.

È utopia la mia o una cosa del genere è fattibile?

[lucky63]

È utopia la mia o una cosa del genere è fattibile?

Puoi risolvere utilizzando funzioni matriciali.

Ordinamento automatico date.png

Nell'esempio di cui all'immagine (cliccarci sopra per ingrandirla) le date inserite nel blocco di celle D2:G6 vengono impilate in un'unica colonna e automaticamente ordinate in colonne e righe in modalità crescente e decrescente.
Variando le date del blocco di celle D2:G6 le colonne/righe predisposte si riordinano automaticamente.

Allego il file di cui all'immagine.
Fai dei test
.
.

[piri]

Intanto grazie mille per la dritta, effettivamente il file pare fare esattamente quello di cui ho bisogno...adesso devo solo capirne logica e sintassi per riportarle sul mio.

Intanto metto il [Risolto], se avessi altri problemi mi rifarò vivo...

Grazie ancora!

[piri]

Non so se il problema sia dato dal fatto che ho messo, rispetto all'esempio, intervalli enormi, ma sul mio file non funziona...
Ho a disposizione poco meno di 1000 colonne, ma, verosimilmente, mi serve di poter inserire molto più di 250 righe di date...

Oltre a questo, ho visto che se nel file d'esempio inserisco due date uguali, la data doppia viene riportata due volte, mentre a me servirebbe un unico riferimento...

A questo punto mi chiedo se per la mole di date che devo inserire e il riferimento univoco alla data non sia il caso di utilizzare una macro...anche se, da questo punto di vista, non avrei idea di dove cominciare...


Consigli?

[lucky63]

a me servirebbe un unico riferimento...

L'estrazione delle date in modo univoco è facilmente superabile (vedasi Immagine e file allegato).

Test - Ordinamento automatico date 2.png

Non so se il problema sia dato dal fatto che ho messo, rispetto all'esempio, intervalli enormi, ma sul mio file non funziona...

Le formule matriciali sono un po' più "complicate" delle formule normali.
Forse hai fatto una errata impostazione della formula matriciale.
Inoltre i limiti di CALC sono 1024 colonne e 1048576 righe.

Dunque se devi inserire più di 250 righe di date su quattro colonne (anche se le celle non sono tutte piene) potrebbe esistere la possibilità che in riposizionamento su unica riga si possa superare (se celle tutte piene) il limite fisico del foglio di calcolo.

non sia il caso di utilizzare una macro

Come indicato sopra il problema dell'univocità delle date è facilmente superabile anche senza ricorrere a macro.
Viste le tue esigenze, utilizzando macro, persisterebbe comunque il limite di 1024 colonne (1024 celle su unica riga).

Consigli?

Riorganizzare il foglio posizionando le date anzichè su unica riga in un'unica colonna.
Volendo persistere su necessità riporto date in unica riga utilizzare le 1000-1024 disponibilità come massimo numero date estraibili e ordinabili.
In alternativa suddividere l'esigenza da riportare i più fogli.
In tal caso nel primo foglio fino a 1000-1024 celle e in foglio successivo da 1001-1025 a 2048, ...

Ricordo che in occasione di alcuni test simili avevo gestito una sequenza di diecimila numeri in unica colonna ed erano funzionati regolarmente (...unico neo, forse, un leggerissimo rallentamento nell'esecuzione dei riordinamenti automatici ....).
.

[lucky63]

Vedi post precedente.
Prova a fornire queste indicazioni:
- area celle contenente le date che devi gestire (A2:D500 ??? delle 4 colonne interessate)
- area celle in cui inserire su unica riga (o colonna se cambi idea) date univoche con ordinamento crescente.

Sviluppo poi un foglio corrispondente alle esigenze.
Con riferimento alle immagini allegate in precedenza, le colonne di appoggio verrebbero poi nascoste ...
.

[piri]

Forse hai fatto una errata impostazione della formula matriciale.

Sono quasi certo di aver commesso un errore, non ho mai usato funzioni matriciali prima e nulla vieta che abbia commesso errori d'esecuzione sebbene abbia cercato di copiare pedissequamente l'esempio da te proposto.

Inoltre i limiti di CALC sono 1024 colonne e 1048576 righe.

Dunque se devi inserire più di 250 righe di date su quattro colonne (anche se le celle non sono tutte piene) potrebbe esistere la possibilità che in riposizionamento su unica riga si possa superare (se celle tutte piene) il limite fisico del foglio di calcolo.

Entro un po' più nello specifico del file che sto costruendo, giusto per non ragionare sull'astratto.
Sto facendo uno scadenziario dei pagamenti delle fatture che mi permetta di verificare quali fatture e che importi devo pagare, con relative scadenze.
Le scadenze sono spesso simili, a seconda del fornitore (ma ne ho una pletora) e della tipologia di pagamento. Sostanzialmente si riducono a tre/quattro giorni al mese (in genere il 4, il 9, il 28 e l'ultimo del mese), ma può capitare che ogni tanto un fornitore mi metta scadenze al 5 o al 14.
Ovviamente, oltre alle fatture dei fornitori, devo anche gestire quelle delle utenze, che hanno scadenza variabile.
Per esplicita richiesta di chi, oltre e più di me, utilizzerà il file, devo organizzare il file con una fattura per riga...considerando che un ordine mi viene spedito in più consegne e conseguenti più fatture, oltre alle utenze eccetera, devo inserire almeno 280/300 fatture l'anno e ho necessità di poter monitorare almeno un paio d'anni sullo stesso foglio...o quanto meno un anno intero più i primi tre/quattro mesi del successivo.
Le fatture di cui sopra hanno varie scadenze, da un minimo di una a, in genere, un massimo di 4.
È in previsione la suddivisione in fogli in copia corrispondenti ad anni diversi per avere un unico file, ma trattandosi, appunto, di una media di 300x(ipoteticamente) 4 date di pagamento all'anno, si va comunque ad olrepassare il limite di 1024 colonne.

non sia il caso di utilizzare una macro

Come indicato sopra il problema dell'univocità delle date è facilmente superabile anche senza ricorrere a macro.
Viste le tue esigenze, utilizzando macro, persisterebbe comunque il limite di 1024 colonne (1024 celle su unica riga).

In realtà, come detto sopra, le date utili (e conseguenti colonne) sebbene inserite ripetutamente, si riducono ad una trentina, massimo 40 all'anno e quindi il limite delle colonne verrebbe ampiamente rispettato.

Ricordo che in occasione di alcuni test simili avevo gestito una sequenza di diecimila numeri in unica colonna ed erano funzionati regolarmente (...unico neo, forse, un leggerissimo rallentamento nell'esecuzione dei riordinamenti automatici ....).
.

Quello è molto probabile succederà anche a me, in considerazione sia del fatto che potrebbero esserci molti dati da trasporre all'inserimento di una data, sia perché non uso macchine molto performanti, ma questo è un problema secondario.

Per ora, comunque, grazie infinite delle soluzioni.
Cercherò di capire meglio come inserire funzioni matriciali per vedere se, quanto meno come test, mi riesce di far funzionare questo file...

Giusto per curiosità:

Prova a fornire queste indicazioni:
- area celle contenente le date che devi gestire (A2:D500 ??? delle 4 colonne interessate)

Per "stare dalla parte dei bottoni" P2:P500;V2:V500;AB2:AB500;AH2:AH500, ma già con 350/400 riferimenti dovrei essere più che a posto

- area celle in cui inserire su unica riga (o colonna se cambi idea) date univoche con ordinamento crescente.

Ora come ora l'area a disposizione partirebbe da AS1 a fine foglio, forse potrei dover inserire altre colonne, ma dubito fortemente di andare tanto oltre BA1...

Ancora grazie, una rapidità e precisione nelle risposte del genere sono rare e preziosissime!

[lucky63]

Per "stare dalla parte dei bottoni" P2:P500;V2:V500;AB2:AB500;AH2:AH500

Partendo da un foglio vuoto ho riservato alle date inseribili i blocchi celle da te indicati.
Per una loro rapida individuazione gli ho impostato una colorazione diversa e subito dopo il loro termine ho inserito la parola "FINE".

I blocchi celle sono già stati occupati con una serie progressiva (univoca) di date per agevolarmi il controllo del regolare funzionamento delle formule/funzioni applicate.

Per gestire il tutto secondo le tue esigenze ho inserito all'inizio del foglio quattro nuove colonne.
Pertanto i tuoi riferimenti di colonna P, V, AB e AH sono diventati rispettivamente T, Z, AF e AL.
Le quattro colonne aggiunte emulano il comportanmento degli esempi precedenti.
Nella colonna A vengono impilate le date dei quattro blocchi (per agevolare riscontro ho mantenuto il colore corrispondente di ogni blocco impilato uno sopra l'altro).
Nella colonna B le date vengono ordinate in ordine crescente dalla più piccola alla più grande.
Nella colonna C le date vengono testate estraendo solo le date univoche.
Nella colonna D le date presenti nella colonna C (date univoche) vengono riassemblate l'una dietro l'altra.
Le colonne A,B,C e D sembrano al momento indifferenti, ovviamente per aver inserito nei blocchi di celle una serie di date progressiva (univoca). Basta cambiare qualche data nei blocchi e il "motore" fà il suo dovere.
Al termine dei test le colonne A,B,C e D vanno nascoste.

Ora come ora l'area a disposizione partirebbe da AS1 a fine foglio, forse potrei dover inserire altre colonne, ma dubito fortemente di andare tanto oltre BA1

Inizialmente ho riservato la riga per la trasposizione automatica a partire dalla cella BD1 (quindi oltre la tua BA1).
Con l'inserimento delle quattro colonne iniziali il riferimento BD1 è diventato BH1
A partire dunque da BH1 e fino quasi a fine foglio (ho lasciato libere per "sicurezza" le ultime 5 colonne) avviene la trasposizione automatica del primo blocco date.
Le celle corrispondenti della riga sottostante sono state numerate progressivamente da 1 a 960 per agevolare i dovuti riscontri. Queste celle potranno essere eliminate alla fine dei test.
Notare la colorazione del blocco in cui avviene la trasposizione delle date.Per agevolare rapidi riscontri corrisponde alla stessa colorazione del primo blocco della colonna D.

Il totale date gestibili sono 499+499+499+499 (ossia 499x4 = 1996)
Avendo trasposto sul primo foglio solo 960 delle 1996 date possibili ho continuato la trasposizione su tutto il secondo foglio e l'inizio del terzo. In questi due fogli il colore delle celle trasposte è diverso da foglio a foglio e anche dal foglio precedente che come per quello sono serviti per un rapido e più agevole riscontro dei riporti dalla colonna
D dove assumono la stessa colorazione.
La numerazione progressiva sotto le celle trasposte (celle azzurre) può essere rimossa dopo i test.
Se sono ritenute sufficenti le date trasposte (960) nel primo foglio i fogli 2 e 3 possono anche essere eliminati.

forse potrei dover inserire altre colonne

ATTENZIONE: l'inserimento o l'eliminazione di una nuova colonna fà saltare la corretta trasposizione sulla riga per un numero di celle pari al numero di colonne inserite o eliminate. Percui il numero delle colonne prima della trasposizione su riga singola devono rimanere uguali. Fare una prova posizionandosi poco prima dell'inizio della trasposizione (foglio1) e eliminare una colonna vuota. Si noti che la funzione matriciale lascia una casella iniziale vuota. Reinserendo una colonna vuota per tornare a pareggio tutto torna a posto. Inserendo invece una nuova colonna vuota apparentemente non succede nulla ma invece si noti come cambia l'ordine di trasposizione che sarà scalato, rispetto all'ordine di colonna D, per il numero di celle corrispondenti al numero di colonne inserite.
(Ricordarsi di togliere la colonna aggiuntiva inserita).
L'assoluta necessità di rimozione o aggiunta di una o più colonne potrebbe essere comunque gestita modificando il numero che viene portato in sottrazione nella formula matriciale che gestisce la trasposizione e contemporaneo ordinamento.
Nell'esempio a seguito la formula riporta un -59 (Meno Cinquanta Nove) per due volte.
=SE(VAL.ERRORE(PICCOLO(D2:D961;RIF.COLONNA(BI1:AMF1)-59));"";PICCOLO(D2:D961;RIF.COLONNA(BI1:AMF1)-59))
Questo -59 corrisponde al numero di colonne presenti prima dell'inizio della formula matriciale.
Per cui in caso di inserimento/eliminazione di una o più colonne bisognerebbe variare questo numero.
Si tenga presente che alla fine del foglio ci sono solo cinque colonne libere.
Una qualche variazione in tal senso renderebbe necessario le verifiche del caso alche sui successifi fogli 2 e 3 e che comunque se non necessari possono essere eliminati indipendentente dal funzionamento del primo foglio.

Ovviamente essendo inserite funzioni matriciali anche in colonna B e D valgono considerazioni simili. Nello specifico qui avrebbero effetti simili l'inserimento di righe prima dell'inizio della formula matriciale. Anche in tal caso di dovrebbe modificare il numero portato in sottrazione nella formula (nel caso specifico -1 che corrisponde al numero di righe prima dell'inizio della formula matriciale).

Allego il file per dei test.
.

[piri]

WOW.

Impressionante.

E io che credevo di essere stato un gallo scrivendo, per 500 colonne di fila: =D2 =D3 =D4 (eccetera)...

Farò delle prove per vedere se il tuo file fatica meno di quello impostato da me a trasporre tutti i dati.

Mi sento in debito per tutto lo sbattimento...
Grazie di cuore!

[lucky63]

WOW.

Impressionante.

E io che credevo di essere stato un gallo scrivendo, per 500 colonne di fila: =D2 =D3 =D4 (eccetera)...

Farò delle prove per vedere se il tuo file fatica meno di quello impostato da me a trasporre tutti i dati.

Mi sento in debito per tutto lo sbattimento...
Grazie di cuore!

Effettivamente avendo già implementato le formule che filtrano univocamente le date e le riorganizzano sequenzialmente (Colonne A:D) la trasposizione in orrizzontale con funzione matriciale risulta un supplus di carico di lavoro non facendo altro che quello che è già stato fatto in colonna D ma in Orrizzontale e per il blocco di celle interessato.

Avendo accennato di voler trasporre fino a fine foglio avevo pensato a limitare il tutto ad un'unica formula anziché 500-960-1966 poi suddivisa in tre per gestire tutte le 1966 caselle in virtu del vincolo fisico numero massimo colonne 1024.

La transposizione in orrizzontale impostata con formula matriciale aveva più senso se veniva meno la colonna D potendo in realtà puntare direttamente ai dati contenuti in colonna C così da eliminare la colonna D.

Volendo gestire un numero “limitato” dei riscontri (500 come accennavi) o più (solo fino al limite massimo permesso dal primo foglio) si poteva dunque ricorrere anche a =D2 =D3 =D4 …. Questo metodo di fatti garantisce maggior velocità al foglio.
Unica complicazione impostare formula cella per cella anziché il tutto con alcune.

La trasposizione in orrizzontale con formula matriciale e la necessità di modificarne la formula in caso inserimento o eliminazione colonne, può essere bypassata/eliminata “rendendo copiabili” le celle interessate della colonna D e poi utilizzando la trasposizione da Incolla speciale - Trasponi (abilitando anche casella “Collegamento”).

Con riferimento all'esempio precedente, inserire dunque un'ulteriore colonna aggiuntiva dopo la colonna D per mantenere le corrispondenze.
In cella E2 inserire =D2
Copiare/Trascinare verso il basso fino a D1997
Ora le date in colonna E sono copiabili nel blocco di dimensione desiderato e incollabili nella posizione desiderata mediante Incolla speciale - Trasponi (con collegamento).

Vengono cosi' eliminate le problematiche pregresse permettendo inoltre un'eventuale spostamento secondo necessità dell'intero blocco celle trasposte orrizzontalmente trascinandole semplicemente con il mouse dopo averle selezionate.