[Risolto] Selezione automatica di valori etichettati

[Dom Benedetto]

Pax!
Buongiorno a tutti.
Sono troppo inesperto per fare quel che vorrei, ma sono convinto che chi ha familiarità con la programmazione dei fogli elettronici potrà aiutarmi. Essendo un principiante ho troppe carenze a livello di conoscenza di funzioni e, soprattutto, del modo di gestire i campi.
Il problema: devo tenere la contabilità nella mia comunità e allo stato attuale faccio quello che è riportato nel file allegato al mio post.
Lo descrivo.
Nella prima scheda segno i movimenti di cassa, siano essi contanti o via banca. Questo foglio lo compilo a mano via via che si fanno le spese. Abbastanza ovviamente segno la data, i movimenti in entrata o in uscita nella cassa contanti o nella banca e la causale. L'ultima colonna riporta chi ha fatto la spesa (cellerario e procuratore sono i nomi di due incaricati nella comunità per le spese di manutenzione e di approvvigionamento rispettivamente). L'ho messa con l'idea di sviluppare quel che voglio. Infatti dopo che segno una spesa (prendiamo la prima, del procuratore) vado sulla scheda che ha lo stesso nome e segno anche lì la spesa, in modo da avere su una scheda unica tutte le spese fatte dal procuratore. E così per il cellerario. Compilo a mano anche queste schede meno l'importo, che collego al foglio della cassa, in modo da non introdurre errori di trascrizione (scrivendo nella cella qualcosa tipo

Codice: Seleziona tutto

=$Cassa.C6

). Così ho pronto il prospetto spese per la relazione per ognuno degli incaricati.
Quello che cerco di ottenere è: nei fogli del procuratore e del cellerario mettere una qualche formula che vada a leggere il foglio della cassa e prenda le informazioni che appartengono (logicamente) alla scheda. La colonna "tag" dovrebbe servire a questo, perché così etichetto la riga della spesa della cassa direttamente con il nome della scheda in cui dovranno andare i dati. Se riesco ad automatizzare questa lettura io non ho da impazzire a riportare a mano nelle varie schede le spese, ma mi limito a riempire la scheda della cassa.
La lettura della scheda Cassa dovrebbe quindi crearmi in automatico i dati delle schede procuratore e cellerario, leggendo solo i dati di interesse, che ovviamente non sono ordinati in modo standard (cambiano in maniera casuale le spese in contanti o via banca e cambia in maniera altrettanto casuale chi fa le spese). Notate che nelle schede di riepilogo del cellerario e del procuratore non ho più interesse a distinguere gli acquisti fatti in contanti o via banca.
Qualcuno può dare suggerimenti? Non ho trovato richieste simili nel forum ma non escludo di aver cercato male, per inesperienza. Potrebbe anche essere utile riorganizzare la scheda Cassa per fare meglio l'operazione di estrazione, ma non ho una strategia in mente non sapendo affrontare il problema.
Grazie mille per l'aiuto,
d. Benedetto

[charlie]

Ciao e benvenuto sul forum.
Se intanto ti vuoi presentare, farai cosa gradita. Puoi farlo in questa sezione -> viewforum.php?f=16

L’utente che apre un quesito si impegna:

• a seguirlo dando riscontro quanto prima alle risposte ricevute nel giro di qualche giorno;
  - inserire il [Risolto] e la ✔ verde come previsto;
• rispettare le altre regole implicite del forum ( Istruzioni per nuovi arrivati - Manuale di sopravvivenza ).

In caso di inosservanza saremo costretti ad azioni di “richiamo”, sospensione o chiusura del profilo utente.

Buon proseguimento.

[gioh66]

Buonasera! Con una modifica al tuo file, cioè spostando i totali in alto (vedi allegato) si potrebbe fare così. Foglio Cellerario A5:

Codice: Seleziona tutto

=SE.ERRORE(INDICE(Cassa!$B$2:$B$1000;PICCOLO(SE(Cassa!$A$2:$A$1000=$A$1;RIF.RIGA(Cassa!$B$2:$B$1000)-1);RIF.RIGA(A1)));"")

in B5

Codice: Seleziona tutto

=SE.ERRORE(INDICE(Cassa!$G$2:$G$41;PICCOLO(SE(Cassa!$A$2:$A$50=$A$1;RIF.RIGA(Cassa!$B$2:$B$41)-1);RIF.RIGA(A1)));"")

Queste due formule sono matriciali, cioè vanno copiate nella cella (o nella barra della formula) e confermate con la combinazione di tasti ctrl+maiuscolo+invio. Poi le celle vanno copiate e incollate in basso fin dove serve (nell'esempio le ho copiate fino alla riga 200)
In C5 invece metti:

Codice: Seleziona tutto

=SE(LUNGHEZZA(A5)=0;"";MATR.SOMMA.PRODOTTO(((Cassa!$A$2:$A$1000=$A$1)*(Cassa!$B$2:$B$1000=A5)*Cassa!$D$2:$D$1000)+((Cassa!$A$2:$A$1000=$A$1)*(Cassa!$B$2:$B$1000=A5)*Cassa!$F$2:$F$1000)))

Nel foglio Procuratore ci sono le stesse formule con le stesse indicazioni di sopra.

Un'altra cosa nei file Cellerario e Procuratore, nelle colonne A e D ho applicato una formattazione condizionale personalizzata 0;;;@ per nascondere gli 0 (zeri).
Ora man mano che aggiungi dati nel foglio Cassa questi verranno aggiunti agli altri due fogli. Come ti ho detto sopra questo avviene fino alla riga 200, se ti serve di più basta che copi e incolli l'ultima riga in basso.

[Dom Benedetto]

Pax!
Grazie,
ora mi metto a studiare la soluzione proposta. Ci metterò un po'... e ti faccio sapere. Perché, come immaginerai, dovrò verificare di sapere/poter esportare alla versione un po' più ricca del file reale della contabilità. Intanto prendo atto dell'esistenza delle formule matriciali e dei trucchetti di formattazione. A presto,
d. B.

[Dom Benedetto]

Pax!
Comincio con le domande (forse disordinatamente):
1. la funzione se.errore che ingloba tutto ha uno scopo puramente estetico? Per non fare venire messaggi di errore nelle celle quando non trova valori?
2. nella funzione indice, confrontando alla formula che vedo sul forum e quella sul file esempio, vedo la presenza in una e l'assenza in un'altra di un punto esclamativo: a che serve?
3. il primo argomento di indice dice di cercare in una matrice i valori da riportare. Quindi per la cella A5 si cerca nella colonna (lunga 1000) delle date, per la cella B5 si cerca nella colonna (ben più corta: 40 elementi) delle causali. Questa discrepanza è voluta o accidentale?
4. da PICCOLO in poi mi perdo. In particolare non capisco dove si seleziona il tag procuratore o cellerario. Mi puoi spiegare?
5. nella funzione invece un cui usi matr.somma.prodotto vedo ancora meno. Il primo se() serve a fare il calcolo solo se c'è una data nella riga. E così si visualizzano dati solo se si deve. Poi l'unica cosa che mi viene da intuire è che usi un prodotto di una matrice a una riga e due colonne per selezionare l'importo. Probabilmente moltiplicando per zero quando la cella è vuota e per uno quando la cella ha un numero. Ma anche qui: come metti lo zero e l'uno? E come selezioni il tag? Il metodo è estendibile facilmente alla situazione reali in cui ho più di due coppie di colonne? (perché in realtà oltre al conto in banca ci sono anche delle prepagate di cui registrare le transazioni. Ma credo basti aggiungere prodotti di matrici nella funzione matr.somma.prodotto)

Grazie,
d. B.

[gioh66]

1. la funzione se.errore che ingloba tutto ha uno scopo puramente estetico? Per non fare venire messaggi di errore nelle celle quando non trova valori?

Esatto!

2. nella funzione indice, confrontando alla formula che vedo sul forum e quella sul file esempio, vedo la presenza in una e l'assenza in un'altra di un punto esclamativo: a che serve?

...il mio solito problema io uso libreoffice con impostata la sintassi della formula per excel e me ne dimentico. Excel che usa il "!" al posto del "." per indicare il nome del file, come invece hai visto nelle formule dell'allegato. Libreoffice sostituisce automaticamente questi due segni quando si apre un file...

3. il primo argomento di indice dice di cercare in una matrice i valori da riportare. Quindi per la cella A5 si cerca nella colonna (lunga 1000) delle date, per la cella B5 si cerca nella colonna (ben più corta: 40 elementi) delle causali. Questa discrepanza è voluta o accidentale?

Accidentale...accidenti...doveva essere 1000 in tutti gli intervalli, questo perchè se aggiungi voci fino alla riga 1000, queste vengono automaticamente lette negli altri fogli.

4. da PICCOLO in poi mi perdo. In particolare non capisco dove si seleziona il tag procuratore o cellerario. Mi puoi spiegare?

Scusa ma nelle celle A1 dei fogli non hai scritto Cellerario e/o Procuratore?

5. nella funzione invece un cui usi matr.somma.prodotto vedo ancora meno. Il primo se() serve a fare il calcolo solo se c'è una data nella riga. E così si visualizzano dati solo se si deve. Poi l'unica cosa che mi viene da intuire è che usi un prodotto di una matrice a una riga e due colonne per selezionare l'importo. Probabilmente moltiplicando per zero quando la cella è vuota e per uno quando la cella ha un numero. Ma anche qui: come metti lo zero e l'uno? E come selezioni il tag? Il metodo è estendibile facilmente alla situazione reali in cui ho più di due coppie di colonne? (perché in realtà oltre al conto in banca ci sono anche delle prepagate di cui registrare le transazioni. Ma credo basti aggiungere prodotti di matrici nella funzione matr.somma.prodotto)

Hai intuito correttamente. I valori 1 e 0 sono dettati dalle condizioni.
Ad esempio questa condizione (Cassa!$A$2:$A$1000=$A$1) crea una matrice di 1 e 0 a seconda che nella colonna A ci sia scritto Cellerario e lo moltiplica per questa (Cassa!$B$2:$B$1000=A5) che a sua volta, genera una matrice di 1 e 0 a seconda se in colonna B del foglio cassa c'è una data che corrisponda al valore A5. Queste due matrici vengono poi moltiplicate per l'intervallo Cassa!$D$2:$D$1000 generando una matrice di 0 e dei valori della colonna D che sulla stessa riga hanno valore 1 dato dalle due altre condizioni.
Poi di questi valori ne viene fatta la somma.
Per aggiungere altre voci di spesa basta che sommi altre condizioni, all'interno della formula.
Spero di essere stato abbastanza esaustivo.

[Dom Benedetto]

Pax!
Torno a riguardare il mio foglio (il 2021 è arrivato!)
gloh66, se possibile, mi spieghi a cosa serve la funzione PICCOLO? Davvero non la capisco. Però sono disposto a fare copia incolla e usare una cosa funzionante...
Ma ancor più (ed è gradito un consiglio che magari tu dica: è meglio dimenticarsi questa divisione per preservare la facilità di programmazione) mi sono reso conto che mi fa comodo strutturare la cassa in trimestri e finora, appunto, l'ho fatto dedicando una scheda ad ogni trimestre. Ma se quadruplico le schede "cassa" (cassa I Trim, cassa II Trim, cassa III Trim e cassa IV Trim) come modifico il formulone che pesca le voci di spesa? Basta sommare quattro differenti funzioni matr.somma.prodotto? Ho fatto delle prove ma non riesco, forse per te è più semplice.
Fatto questo si chiude il quesito!
d. Benedetto

[Dom Benedetto]

Addendum
C'è un problema con la formulazione che hai adottato che usa la funzione matr.somma.prodotto.
Nel file allegato ho modificato la data della spesa per la benzina del procuratore e l'ho messa uguale alla data della spesa della frutta.
Nella scheda del procuratore entrambe le spese sono riportate con il valore della spesa che è la somma dei valori di entrambe. E in effetti la formula che hai inventato va a prendere tutti i valori di una data specificata e di una categoria specificata e li somma. se ci sono più valori mi trovo la somma e non i singoli valori riportati. Mi sono spiegato? Praticamente penso che come hai formulato possa andar bene solo nel caso in cui si fa una spesa al giorno.

d. B.

[lucky63]

.
Allego esempio alternativo come da immagini.

Nel foglio “Cassa”:

- le voci di Tag consentite sono “Entrate/Cellerario/Procuratore” e sono selezionabili anche tramite menu a tendina;
- la colonna A è nascosta e serve a implementare riscontri impostati nel foglio Riepilogo Spese”;
- al momento possono essere gestite le righe fino alla 1000 (mille).


Nel foglio “Riepilogo Spese”:

- è possibile modificare le date di inizio e fine periodo di cui si vuole attuare il riscontro;
- è possibile selezionare i Tag “Cellerario/Procuratore” tramite menu a tendina;
Sulla base delle predette impostazioni i riscontri avvengono automaticamente fino alla riga 130 (circa 3 pagine formato A4) .

Entrambi i fogli hanno una protezione foglio attivata (ma senza password).

.

[Dom Benedetto]

lucky63, grazie per il tuo lavoro. Sono un po' sconfortato perché mi rendo conto di dover compulsare una marea di documentazione per capire che cosa hai fatto. Figurati che ho scoperto solo ora che si possono proteggere i fogli (e come farlo), nascondere colonne e che mi hai costretto a scoprire come fare un menu a tendina senza creare una scheda apposita con i nomi da cui scegliere... Non so se mi sono imbarcato in un qualcosa di più grande di me ma è vero che così si impara!

La tua soluzione è molto bella, vorrei chiederti solo due o tre cosette:
1. Perché nel foglio riepilogo spese, nella colonna importo, sono presenti solo le voci negative selezionate con le colonne 5 e 7 della matrice A5:H1000? Hai voluto fare l'esempio solo per le uscite? Io metterei come nome della colonna "uscite" e farei accanto una colonna con nome "entrate" in cui selezioni le colonne 4 e 6 della matrice di cui sopra. Così ho le voci di attivo e di passivo. Faccio bene?
2. La mia idea iniziale era di fare una scheda per ogni attore di spesa. Ma mi sembra che la tua soluzione non permetta questo, perché nella colonna nascosta i numeri che identificano le righe con le spese del singolo attore dipendono appunto da quello che è selezionato nella scheda di riepilogo. Vedi possibilità diverse che possano far convivere più schede per i vari attori?
3. Per quanto riguarda invece la divisione in trimestri? Nulla impedirebbe di avere 4 schede cassa e poi nelle formule del riepilogo sommare sulle 4 schede (sempre eventualmente filtrando per data) o quadruplicare anche la scheda dei riepiloghi in modo che si specializzino sul singolo trimestre, dunque 4 coppie di schede cassa-riepilogo. Che ne pensi?
Grazie mille,
d. B.

[lucky63]

.
Punto 1.
Pensavo volessi solo le uscite.
Si può adattare per avere anche colonna “Entrate” ma per averne riscontro negli inserimenti in cassa dovrai associare alle entrate un Tag “Cellerario/Procuratore” (Rimuovo dal menu a tendina la specifica voce “Entrate”).

Punto 2.
Se vuoi schede Cellerario/Procuratore già ben distinte si può fare implementando in modo opportuno.

Punto 3.
Se preferisci 4 casse trimestrali posso predisporre.
Per i riepilogo preferisci:
- 1 Foglio in cui poter selezionare Cellerario/Procuratore e il trimestre secondo esigenza;
- 2 Fogli già distinti predisposti come Cellerario e Procuratore ove selezionare il Trimestre secondo esigenza;
- 4 Fogli ognuno già predisposto per il rispettivo trimestre in cui dovrai cambiare solo Cellerario/Procuratore;
- 8 Fogli (4 Cellerario + 4 Procuratore) già ben distinti e predisposti per il rispettivo trimestre.
.
.
Alternativa che suggerirei :
Fare un singolo file (vedi allegato) con fogli Cassa/Cellerario/Procuratore.
Tutto molto più semplice potendo usufruire del file vuoto originario per ogni trimestre o periodo di preferenza (salvandolo con nome desiderato).
.

[Dom Benedetto]

Pax!
lucky63, sei un drago.

1 e 2. Ho visto come hai fatto a implementare i due fogli separati per procuratore e cellerario: moltiplicazione delle colonne nascoste e via. Penso di poter estendere quindi il tuo foglio al numero delle schede che voglio (anche se ieri provando a scrivere le formule mi veniva ogni tanto fuori un Err:522 -> ci sono accorgimenti particolari da adottare nel scrivere le formule che non so?). Solo mi pare più bello che non compaiano gli zeri. Li ho eliminati mettendo un ulteriore funzione SE che calcola se il valore della cella che si dovrebbe scrivere è zero o no. Se è zero non scrive nulla, se non è zero si replica il valore trovato.

Codice: Seleziona tutto

=SE(A5="";"";SE(CERCA.VERT(RIF.RIGA($A1);$CASSA.$A$5:$CASSA.$I$1000;5;0) + CERCA.VERT(RIF.RIGA($A1);$CASSA.$A$5:$CASSA.$I$1000;7;0)=0;"";CERCA.VERT(RIF.RIGA($A1);$CASSA.$A$5:$CASSA.$I$1000;5;0) + CERCA.VERT(RIF.RIGA($A1);$CASSA.$A$5:$CASSA.$I$1000;7;0)))

Formula un po' lunga e ridondante (va a leggere due volte le celle) ma penso si debba fare così.
3. La motivazione naturale di avere un file unico è poi il resoconto annuale. Penso che il tuo suggerimento di fare un file singolo per trimestre sia molto saggio, basta che tu mi garantisca che riuscirò a fare un riepilogo annuale anche usando i 4 files distinti. Credo si possa fare, se non mi sbaglio sono collegabili anche celle di altri documenti aperti in libreoffice. Confermi?

Grazie mille. Lavoro un po' da solo sul tuo file e poi si va verso la chiusura del thread.
d. B.

[lucky63]

moltiplicazione delle colonne nascoste e via. Penso di poter estendere quindi il tuo foglio al numero delle schede che voglio

Esatto.

mi veniva ogni tanto fuori un Err:522

Non deve succedere. Devi aver sbagliato qualcosa.

“Err:522” Identifica che la formula fa riferimento direttamente o indirettamente a se stessa (“Riferimento circolare”).

mi pare più bello che non compaiano gli zeri. Li ho eliminati mettendo un ulteriore funzione SE

Se è semplicemente una questione estetica per non vedere i valori 0 puoi intervenire anche da:
Menu > Strumenti > Opzioni > Calc > Vista > Mostra > Togliere la spunta dalla casella "Mostra valori zero" > OK

Così otterresti lo stesso risultato visivo ma limitando le dimensioni delle formule e la loro ridondanza.

basta che tu mi garantisca che riuscirò a fare un riepilogo annuale anche usando i 4 files distinti

E' possibile farlo ma è meglio che prima fai delle prove con dei semplici file così da non doverti in seguito cimentare in altre difficoltà.

La motivazione naturale di avere un file unico è poi il resoconto annuale

Dal mio file puoi trarre spunto per crearti un unico file con tutte le schede che vuoi e facilitarti anche il resoconto annuale già integrato nello stesso file così da averne una gestione completamente automatizzata.
.

[Dom Benedetto]

Pax!

Sono riuscito a allargare il tutto alla mie esigenze. L'unico problema è che quando seleziono la voce dal menu a tendina il sistema ci mette tra i 5 e i 10 secondi a calcolare il tutto. Poi è più rapido. Teniamo conto che ho un computer del 2007 che va avanti solo grazie a linux...

Grazie mille per l'assistenza, metto il risolto!
d. Benedetto