[excel] somma se il giorno appartiene a un dato mese

[marcoc]

ciao a tutti,
avrei bisogno di una formula che mi calcoli, mese per mese, le entrate e le uscite del mio conto corrente.

nel foglio1 ho:
in colonna B ho i giorni dei movimenti,
in colonna D ed E ho i movimenti in entrata,
nelle colonne da F ad O ho i movimenti in uscita.

vorrei, nel foglio2, una tabella in cui:
nella colonna A ci sono i mesi (es: gen-08, feb-08, ...)
nella colonna B c'è la somma automatica delle entrate del mese
nella colonna C c'è la somma automatica delle uscite del mese.

che formula devo usare? ho pensato alla MATR.SOMMA.PRODOTTO ma non riesco a definire il mese. Ho provato mettendo >=primo del mese AND <=ultimo del mese, ma mi esce #VALORE!

che dite?

[Anthony47]

E invece una tabella pivot?
Aggiungi una colonna che intesti "Mese", in cui usi la formula =mese(B2) che poi copi verso il basso; ti fai una colonna di tutte le entrate (=D2+E2) e una di tutte le uscite (=somma(F2:O2)); poi imposti la tabella pivot con Mese nel campo Riga mentre Entrate e Uscite (Somma di..) andranno nel campo Dati.
Ottenuta la tabella puoi variarne il layout (ad esempio avere Entrate e Uscite affiancate in colonna invece che accodate in riga) e anche registrare una macro che aggiorna la tabella.
Se vuoi usare MATR.SOMMA.PRODOTTO, puoi usare una matrice che contenga --(mese(B2:B1000)=il tuo mese) e una seconda con (D2:D1000)+(E1:E1000)
Ho ipotizzato che i tuoi dati siano tra riga 2 e 1000, varia se hai bisogno di piu' righe. Analoga la formula per le uscite.

Ciao.

[marcoc]

scusa, non ho capito... com'è la formula completa della matrice somma prodotto?

[Anthony47]

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--(mese(B2:B1000)=il tuo mese);(D2:D1000)+(E1:E1000))


Ciao.

[marcoc]

grazie!!
funziona se al posto di "il tuo mese" metto il numero progressivo del mese; se però ci sono valori di più anni (es. 2008 e 2009), non mi fa la distinzione, e somma tutti quelli dei gennaio.

[Flash30005]

grazie!!
funziona se al posto di "il tuo mese" metto il numero progressivo del mese; se però ci sono valori di più anni (es. 2008 e 2009), non mi fa la distinzione, e somma tutti quelli dei gennaio.

Non ho avuto modo di provare ma penso tu possa modificare il codice di Anthony in questa maniera

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--anno(B2:B1000)&(mese(B2:B1000)=AAAAM);(D2:D1000)+(E1:E1000))


dove AAAA sta per tuo anno e M sta per tuo mese (es. 20081 per gen-08 oopure 200812 per dic-08)

ciao

[Anthony47]

La formula di Flash penso che funzioni, con l' accortezza di introdurre AAAAM come stringa; il mio siggerimento e' aggiungere una ulteriore matrice di check in formula:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--(mese(B2:B1000)=il tuo mese);(D2:D1000)+(E1:E1000);--(anno(B2:B1000)=il tuo anno))


Ovviamente metterai "il tuo mese" e "il tuo anno" in due celle, non nella formula (perche' e' piu' facile da modificare).

Ma la tabella pivot?

Ciao

[pietrol]

Ciao Anthony
ho seguito con interesse questa discussione, ma non ho capito una cosa:

=MATR.SOMMA.PRODOTTO(--(mese(B2:B1000)=il tuo mese);(D2:D1000)+(E1:E1000);--(anno(B2:B1000)=il tuo anno))

cosa significano i "--" nella formula?

Ciao
pietrol

[Anthony47]

(mese(B2:B1000)=il tuo mese) ritorna Vero o Falso; i "--" sono una doppia negazione che trasforma cio' in 1 o 0, con cui sara' possibile fare le moltiplicazioni in Matr.Somma.Prodotti

Ciao

[marcoc]

grazie per la formula e anche per spiegazione alla domanda di pietrol, anche io mi ero chiesto la stessa cosa.
sono un po' ostico alle tabelle pivot...

[pietrol]

Grazie Anthony
mi hai dato una dritta che mi permetterà finalmente di utilizzare appieno la funzione MATR.SOMMA.PRODOTTO.
Quante volte ho rinunciato perchè non capivo perchè non funzionasse!

pietrol

[marcoc]

ho un'altra domanda riguardo alla formula che riconosce il mese nella data:
Nella colonna B ho le date, nella colonna P ho i valori.
dovrei contare i valori >0 nella colonna P da Gennaio 2007 fino a Dicembre 2008.
E' tipo un SE (le date della colonna B sono di Gennaio 2007) ALLORA (conta i valori della colonna P se sono >0).
poi, nella cella sotto (le date della colonna B sono di Febbraio 2007) ALLORA (conta i valori della colonna P se sono >0), e così via.

[pietrol]

Ciao marcoc
grazie alla dritta di Anthony (e sfruttando il suo ultimo suggerimento) eccoti la soluzione:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--(mese(B2:B1000)=il tuo mese);--(anno(B2:B1000)=il tuo anno);--((P1:P1000)>0))


ciao
pietrol

[marcoc]

grazie pietrol.
ultima domanda (sempre + difficile...): in un altro file devo scrivere una formula simile alla precedente;
in pratica, devo applicare la formula che riconosce il mese e l'anno nella:
=SOMMA.SE(P10094:P10111;">0")/CONTA.SE(P10094:P10111;">=0").
che dite?

[marcoc]

forse non è molto chiaro...
in P ci sono i valori da sommare e da contare; in un'altra colonna (es: colonna A) ci sono le date, la formula deve sommare e contare i valori della colonna P in corrispondenza di un determinato mese e di un determinato anno nella A.

[pietrol]

A me sembra dalla tua formula che tu stia cercando di calcolare una media per anno/mese dei valori > 0.

Se ho capito bene, ipotizzando Tuo anno=E22 e Tuo mese=F22

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--(MESE(A2:A1000)=F22);--(ANNO(A2:A1000)=E22);--((P2:P1000)>0);P2:P1000)/MATR.SOMMA.PRODOTTO(--(MESE(A2:A1000)=F22);--(ANNO(A2:A1000)=E22);--((P2:P1000)>0))


Ciao
pietrol

P.S. Rispondendoti mi sono accorto che l'altra formula che ti avevo dato conteneva un errore (P1 invece di P2), questa è quella esatta:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--(mese(B2:B1000)=il tuo mese);--(anno(B2:B1000)=il tuo anno);--((P2:P1000)>0))


[Anthony47]

A questo punto conviene verificare anche l' uso della funzione DB.MEDIA; significato e sintassi dall' help on line.
Ciao.