Excel: subtotale, somma.se, conta.se

[stevesteve]

ho una colonna di risultati a fianco di una colonna di date.

Voglio calcolare alcuni valori riferiti a risultati in base a diversi filtri.

Con la funzione subtotale, applicando il numero iniziale nella versione 10x che esclude i valori nascosti, ottengo il totale, il numero di operazioni, la media, la migliore, la peggiore.

Non riesco però ad ottenere numero e valore di operazioni positive e negative, perchè sia conta.se che somma.se non tengono conto dei filtri.

Insomma mi servirebbe una funzione tipo subtotale(112, a1;a100; >0). Come la posso costruire con le funzioni esistenti? (ma possibile che non ci abbiano pensato?)

Grazie

Stefano

 

[Chi Unque]

http://www.contextures.com/sam_sumproduct_filtered.zip

Vedi se ti trovi con questo file e riesci ad adattarlo
Ora non ho tempo comunque se non ti trovi ci sono altri modi con MATR.SOMMA.PRODOTTO se non ti trovi chiedi pure

 

[stevesteve]

intanto grazie, poi ti dico.

 

[gridlock]

buongiorno.
Approfitto per una domanda su delle funzioni 'data' molto semplici.
Nella colonna A ho la data in formato gg/mm/aa e nella colonna B vorrei far apparire il giorno della settimana corrispondente in automatico.

Inoltre vorrei sapere se c'e' un modo per far apparire in automatico anche la data della colonna A che pero' non deve cambiare una volta terminata la giornata.
Se usassi la funzione =giorno per tutta la colonna anche mettendomi la data di oggi cambierebbe in automatico anche quella di ieri che invece vorrei restasse fissa col passare delle gironate.

dovrei trovarmi una cosa del genere

A B C
03/10/16 lun fattura n°
03/10/16 lun fattura n° 2
03/10/16 lun bolla n°
04/10/16 mar fattura n°
04/10/16 mar ricevuto acc. su ft
..........
........

Scusate ma ho iniziato da poco ad usare excel.

Volevo anche sapere se c'e' un manuale o un sito che spieghi come sono strutturate le funzioni con la logica che c'e' dietro la composizione e non semplicemente una semplice descizione con copia e incolla.
Grazie.

 

[Chi Unque]

Per la prima domanda TESTO(GIORNO.SETTIMANA(A1);"gggg") supponendo che la data sia nella cella A1
Per la seconda domanda, non esiste una funzione specifica per bloccare i valori. O copi e incolli manualmente i dati alla fine del giorno, o crei una routine vba o di altro genere che fa questa operazione
Per la terza domanda, ci sono decine di siti, a cominciare dalla assistenza ufficiale microsoft. La cosa più semplice è che inserisci in Google il nome della funzione seguito da parole come "Excel" "tutorial" etc. e cominci a girare per i vari siti. Se sai l'inglese siti molto attendibili di solito sono quelli dei MVP, "Microsoft Most Valuable Professionals". Ci sono anche forum specializzati, soprattutto in inglese

 

[gridlock]

Per la prima domanda TESTO(GIORNO.SETTIMANA(A1);"gggg") supponendo che la data sia nella cella A1
Per la seconda domanda, non esiste una funzione specifica per bloccare i valori. O copi e incolli manualmente i dati alla fine del giorno, o crei una routine vba o di altro genere che fa questa operazione
Per la terza domanda, ci sono decine di siti, a cominciare dalla assistenza ufficiale microsoft. La cosa più semplice è che inserisci in Google il nome della funzione seguito da parole come "Excel" "tutorial" etc. e cominci a girare per i vari siti. Se sai l'inglese siti molto attendibili di solito sono quelli dei MVP, "Microsoft Most Valuable Professionals". Ci sono anche forum specializzati, soprattutto in inglese

GRAZIE.
Appena posso provo le funzioni.
Io pero' non vorrei impararle a memoria ma semplicemente studiarle per capire perche' vengono scritte in quel modo. Ad esempio il significato del punto e virgola; cosa si mette tra le parentesi ;cosa si mette prima quando ssi scrive etc... Non vorrei utilizzare il semplice copia e incolla
Premetto che non ho nessuna base di informatica.

 

[Chi Unque]

Ti faccio un esempio

Al link sotto c'è riportato il significato degòo gli operatori principali nelle formule Excel, compreso il punto e virgola
Introduzione alle Formule e Funzioni di Excel | Come applicare le Formule e Funzioni di Excel | Regole Formule e Funzioni di MIcrosoft Excel

Poi semplicemente ti conviene prendere dei file con formule già creati da qualcun altro e ricostruirli da zero, cercando di capire i dettagli delle varie formule
Visto che sei all'inizio ti consiglio di esercitarti sulle due funzioni che a mio avviso sono le più importanti ossia cerca.vert e somma.se
Poi prova a esercitarti a fare delle tabelle pivot
Ciao

 

[stevesteve]

http://www.contextures.com/sam_sumproduct_filtered.zip

Vedi se ti trovi con questo file e riesci ad adattarlo
Ora non ho tempo comunque se non ti trovi ci sono altri modi con MATR.SOMMA.PRODOTTO se non ti trovi chiedi pure

non ce l'ho fatta ad adattarlo, perchè se ho capito bene lavora su almeno due colonne selezionando i valori di una in base al contenuto dell'altra.

Io invece voglio fare una cosa non so se più semplice o più complicata, e cioè sommare, in una sola cella, i valori che di una colonna sono >0 e, in un'altra cella, i valori della stessa colonna <0. Il che è facile da fare con somma.se sulla colonna così com'è, ma io voglio farlo con diversi filtri applicati ad altre colonne. Spero di essermi spiegato bene.

 

[gridlock]

Ti faccio un esempio

Al link sotto c'è riportato il significato degòo gli operatori principali nelle formule Excel, compreso il punto e virgola
Introduzione alle Formule e Funzioni di Excel | Come applicare le Formule e Funzioni di Excel | Regole Formule e Funzioni di MIcrosoft Excel

Poi semplicemente ti conviene prendere dei file con formule già creati da qualcun altro e ricostruirli da zero, cercando di capire i dettagli delle varie formule
Visto che sei all'inizio ti consiglio di esercitarti sulle due funzioni che a mio avviso sono le più importanti ossia cerca.vert e somma.se
Poi prova a esercitarti a fare delle tabelle pivot
Ciao

Grazie! Aggiunto ai preferiti.

 

[stevesteve]

non ce l'ho fatta ad adattarlo, perchè se ho capito bene lavora su almeno due colonne selezionando i valori di una in base al contenuto dell'altra.

Io invece voglio fare una cosa non so se più semplice o più complicata, e cioè sommare, in una sola cella, i valori che di una colonna sono >0 e, in un'altra cella, i valori della stessa colonna <0. Il che è facile da fare con somma.se sulla colonna così com'è, ma io voglio farlo con diversi filtri applicati ad altre colonne. Spero di essermi spiegato bene.

toc toc.... ?

 

[Chi Unque]

Sto provando a costruire una formula del genere che dici, non è semplice perché bisogna incassare varie formule
Se riesco ti faccio sapere

 

[Chi Unque]

=MATR.SOMMA.PRODOTTO(SUBTOTALE(9;SCARTO(A12;RIF.RIGA(A12:A526)-RIF.RIGA(A12);;1));--(A12:A526<0))

Dove A12 è la prima cella dell'intervallo ancora non filtrato e A526 è l'ultima cella dell'intervallo non filtrato
Questa sopra è la somma dei valori <0 nelle celle filtrate

 

[Chi Unque]

Qui di seguito la formula per calcolare la somma dei valori >0

=MATR.SOMMA.PRODOTTO(SUBTOTALE(9;SCARTO(R12;RIF.RIGA(R12:R526)-RIF.RIGA(R12);;1));--(R12:R526>0))

 

[Chi Unque]

In quest'ultima formula i dati erano nella colonna R

 

[stevesteve]

=MATR.SOMMA.PRODOTTO(SUBTOTALE(9;SCARTO(A12;RIF.RIGA(A12:A526)-RIF.RIGA(A12);;1));--(A12:A526<0))

Dove A12 è la prima cella dell'intervallo ancora non filtrato e A526 è l'ultima cella dell'intervallo non filtrato
Questa sopra è la somma dei valori <0 nelle celle filtrate

accidenti, non pensavo proprio potesse essere così complicato.... sembra incredibile che i signori di microsoft non abbiano pensato a questa cosa che tutto sommato è in sè piuttosto semplice.

Comunque, grazie!

Purtroppo, dà come risultato "#VALORE!".

Ho verificato che nella colonna ci sono solo valori numerici, le celle che risultavano vuote le ho fatte diventare = 0, ma resta l'errore, che credo sia nella formula, perchè se uso somma per lo stesso intervallo dà il corretto risultato. Altre ipotesi?
.

 

[Chi Unque]

accidenti, non pensavo proprio potesse essere così complicato.... sembra incredibile che i signori di microsoft non abbiano pensato a questa cosa che tutto sommato è in sè piuttosto semplice.

Comunque, grazie!

Purtroppo, dà come risultato "#VALORE!".

Ho verificato che nella colonna ci sono solo valori numerici, le celle che risultavano vuote le ho fatte diventare = 0, ma resta l'errore, che credo sia nella formula, perchè se uso somma per lo stesso intervallo dà il corretto risultato. Altre ipotesi?
.

Il tipo di messaggio di errore deriva probabilmente dal punto della formula in cui compare lo SCARTO
La formula è esatta (l'ho testata prima di riportarla), c'è forse un problema con gli intervalli riportati sopra
Puoi caricare un file di esempio, che te lo rimando con la formula corretta?
Se preferisci non postare il file sul forum puoi mandarmi in messaggio privato il tuo indirizzo email

 

[stevesteve]

confermo: la formula era corretta, mio errore nella selezione delle celle.

Grazie!

 

[stevesteve]

.... e quando mi serve di contare (invece che sommare)?

Ho provato - proprio "provato" alle semi cieca, perchè pur essendomici impegnato non ho capito del tutto come funzionava la precedente formula, e alla fine mi sono accontentato che funzionasse - a sostituire a "SOMMA.PRODOTTO" conta.se, conta.più.se, conta.valori ma restituisce "#NOME".

 

[uuu]

.... e quando mi serve di contare (invece che sommare)?

Ho provato - proprio "provato" alle semi cieca, perchè pur essendomici impegnato non ho capito del tutto come funzionava la precedente formula, e alla fine mi sono accontentato che funzionasse - a sostituire a "SOMMA.PRODOTTO" conta.se, conta.più.se, conta.valori ma restituisce "#NOME".

perchè richiami una formula che non esiste

 

[stevesteve]

perchè richiami una formula che non esiste

vabbè, vuoi far risplendere la mia ignoranza , l'ho detto che sono andato a tentoni .

Formulo diversamente la mia richiesta: con subtotale posso avere qualsiasi somma da una colonna applicando qualsiasi filtro. Vorrei raggiungere il risultato di ottenere il numero di valori positivi (o negativi) contenuto in una colonna, che vari al variare dei filtri applicati (quindi delle righe di vota in volta visibili). Conta.se mantiene sempre lo stesso valore di tutta la colonna.

Quindi, quale soluzione è possibilr?

Grazie