Funzione Excel Data Validation "al contrario"

Question

Buon giorno,

mi trovo a dover affrontare una situazione legata all'utilizzo della funzione Data Validation su un foglio excel.

Infatti la Lista di elementi a cui essa fa riferimento è composta da Entità presenti nella stessa colonna del data base sia con segno positivo che con segno negativo (questi ultimi potrebbero essere assimilabili a codici sconto inseriti all'interno del db con un proprio codice).

La conseguenza è che quando richiamo un elemento della lista, nella visualizzazione sottostante dove ho inserito la formula con la relativa sommatoria per il raggruppamento di quella entità, il risultato che ne deriva è il SALDO NETTO tra i valori positivi e quelli negativi.

Io invece vorrei avere anche la possibilità di escludere i codici di quelle entità contraddistinte da importi negativi, selezionandoli tramite una seconda Lista (che inserirei sempre tramite la funzione di Data Validation) in modo che nella cella sottostante troverei visualizzato IL SALDO LORDO delle entità che non tiene conto degli importi negativi che invece ne abbattono il valore.

Non ho la possibilità di cambiare la struttura del db e quindi ho dovuto accantonare l'idea di aggiungere una nuova colonna dove spostare solamente i codici delle entità con valori negativi e creare una seconda Data Validation che facesse riferimento esclusivamente ad essi.

Con l'auspicio di essere stato chiaro la mia questione è se esista la possibilità di creare una Data Validation in "negativo", cioè che permetta di escludere alcune voci dalla base dati?

Grazie

Luca

Answer 1

Grazie della disponibilità Paolo ed immaginavo che la mia problematica potesse essere esposta in maniera poco chiara e quindi creare perplessità, ma d'altra parte è proprio il requisito che devo soddisfare che è ambiguo e difficilmente attuabile, almeno in base alle specifiche che ho a disposizione.

Infatti non si tratta solo di filtrare e sommare (o contare) dei valori presenti in una colonna come correttamente le tue preziose formule suggeriscono.

In attesa di stralciare parte del mio file, sintetizzo la situazione con i passaggi di seguito descritti:

1-nel mio archivio ho delle "Chiavi" le cui combinazioni identificano una serie di codici di articoli, tutti riconducibili  ad una sola famiglia/Raggruppamento per la quale viene generato un prospetto riepilogativo.

Alcuni di questi codici sono associati ad importi negativi ed altri ad importi positivi

2-Quando vado a selezionare la Famiglia/raggruppamento nella mia Lista associata alla Data Validation, attualmente nel prospetto riepilogativo mi appare il saldo algebrico di tutti gli importi, sia positivi che negativi, inerenti a tutti i codici articolo appartenenti a quella famiglia, senza alcuna possibilità di escluderne alcuni.

Es: Art. a1, a2,....an appartenenti alla Famiglia A (ma alcuni di essi hanno un importo negativo)

Saldo di A= a1+a2+...an 

La mia esigenza sarebbe

3-Aggiungere una nuova Lista basata sulla Data validation che mi facesse selezionare uno (o anche più) di quei codici articoli associati agli importi negativi

4-La selezione di tali codici farebbe si che i relativi importi (negativi) non rientrino più nella somma algebrica eseguita per la famiglia A e quindi sarebbe visualizzato nel prospetto riepilogativo solo il saldo generato dagli importi positivi (alternativamente potrebbe sorgere in seconda battuta la necessità di avere invece visualizzata sola la sommatoria degli importi negativi).

Di seguito riporto al momento la strada che potrei intraprendere per risolvere la questione ma con una modalità concettualmente diversa da quella per la quale sto chiedendo il supporto alla community; ovvero:

-Creare due fogli di calcolo ben separati ed in uno dei due sia presente una condizione di Sumif che prenda in considerazione solo i record che presentino un importo positivo.

-Creare sul mio Prospetto Riepilogativo un oggetto che mi dia due possibilità di scelta (Importi Lordi oppure solo importi positivi)

-Legare ciascuna di tali opzioni al rispettivo foglio di calcolo sopra citato

-Condizionare con un IF ed un ELSE ciascuna delle celle presenti nel prospetto Riepilogativo in modo che sia alternativamente visualizzato il risultato del calcolo a seconda della condizione prescelta.

Spero di essere stato un pò più chiaro per procedere alla risoluzione del problema

Grazie

Answer 2

Vediamo se ho capito: Tu vorresti una formula che faccia la somma di dati filtrati tenendo però conto solo dei valori positivi.

Se è così, la formula che ti ho suggerito, esegue il calcolo di valori filtrati (SUBTOTALE) con esclusione però dei valori negativi.

La tua osservazione circa l'argomento 103 all'interno della funzione SUBTOTALE è corretta in quanto, tale numero, esegue il conteggio anziché la somma dei valori, pertanto la formula andrebbe modificata così:

=MATR.SOMMA.PRODOTTO((A1:A100>0)*(SUBTOTALE(109;SCARTO(A1;RIF.RIGA(A1:A100)-MIN(RIF.RIGA(A1:A100));;1))))

La formula andrebbe collocata al posto di quella che ora esegue la somma senza distinguere fra positivi e negativi.

Il contenuto delle tue osservazioni però mi fa supporre che forse non ho ancora capito. Se tu potessi mettere a disposizione uno stralcio del tuo file sarebbe di aiuto.

Answer 3

Boon giorno Paolo,

grazie per la risposta ma oltre a non comprendere concettualmente alcuni passaggi della formula (Es: cosa rappresenta il valore 103 all'interno dell'argomento SUBTOTALE?) faccio fatica a capire l'ambito di applicazione della medesima al mio caso.

Infatti in quale momento essa andrebbe applicata?

Ricapitolando (e cercando di fare un pò più di chiarezza):

1-Come ho già scritto sopra, il Data Validation di default è corretto, cioè è ok il fatto che selezionando in prima istanza una voce dalla lista presente nel Data Validation venga fatta la somma algebrica di tutti gli importi -sia negativi che positivi- ad essa relativi (ed il risultato nella tabella dei dati riepilogativi sarà di conseguenza un SALDO/IMPORTO NETTO).

2-Nel secondo data validation che andrei ad inserire, e che avrebbe lo stesso identico range di applicazione di quello sopra illustrato, si andrebbero sempre a selezionare una (o più) voci della identica lista presente nel Data Validation, ma a cui stavolta corrispondono esclusivamente gli importi negativi.

Questa "selezione" avrebbe lo scopo di escludere tali valori dalla somma algebrica degli importi afferenti alla voce selezionata nel primo data validation (e quindi il risultato nella tabella dei dati riepilogativi sarà di conseguenza un IMPORTO LORDO).

Come informazione aggiuntiva faccio presente che oltre allo Sheet contenente i filtri del Data Validation e  la tabella di Riepilogo, entrano in gioco i seguenti sheet:

-Uno sheet che funge da archivio dati

-Uno sheet di trascodifica delle voci presenti nel Data Validation

-Uno sheet, intermedio tra lo Sheet di Riepilogo e quello di Archivio Dati, che funge da calcolatore per l'aggregazione dei valori.

In base a tutto ciò, ti chiedo in quale passaggio del processo aggregativo dovrebbe essere inserita la formula che hai inserito e che dovrebbe far scattare il calcolo Lordo o Netto degli importi?

Grazie

L.

Answer 4

Vedi qui:

http://answers.microsoft.com/it-it/office/forum/office_2010-excel/non-contare-valori-zero-filtrati/94a75b39-23fd-4e21-8b9f-92b9fa368d72

al posto di A1:A100=0 usa A1:A100>0

NB: nell'esempio è la colonna A che contiene i valori, ovviamente gestisci gli intervalli a seconda delle tue necessità.