[EXCEL] Compilare una formula con il contenuto delle celle

[Lorevigo]

Ciao a tutti, sto creando un grosso foglio di calcolo Excel per effettuare delle analisi e non riesco a venire a capo di una sezione di quest'ultimo che ho strutturato su due fogli come segue:

Foglio 1:
- Una colonna (A:A) con dei valori numerici
- Una seconda colonna (B:B) adiacente alla prima con altri valori numerici differenti
- Una terza colonna (C:C) sempre adiacente alle prime due dove vorrei che venisse restituito, al verificarsi di determinate condizioni, un etichetta contenuta in una tabella presente nel foglio 2

Foglio 2:
- Tabella composta da:
- una colonna (Foglio2!A1:A5) contenente le etichette testuali che vorrei assegnare
- una colonna (Foglio2!B1:B5) contenente i valori soglia da confrontare con la colonna A del foglio 1
- una colonna adiacente (Foglio2!C1:C5) contenente l'operatore da utilizzare in riferimento alla colonna A (< o > o <= o >= etc.)
- una colonna (Foglio2!D1:D5) contenente il valore soglia da confrontare con la colonna B del foglio 1
- una colonna adiacente (Foglio2!E1:E5) contenente l'operatore da utilizzare in riferimento alla colonna A (< o > o <= o >= etc.)

Ora il funzionamento che vorrei ottenere per garantire la massima flessibilità di funzionamento, modularità ed immediatezza nella modifica dei parametri e quindi delle etichette assegnate sarebbe dato da una formula che andasse a "pescare" le sue componenti direttamente dalle celle e quindi di questo tipo (facendo un esempio sulla prima riga con imputazione in Foglio1!C:C :

=SE(A1&[operatore contenuto in Foglio2!C1]&Foglio2!B1;Foglio2!A1;SE(A1&[operatore contenuto in Foglio!2C2]&Foglio2!B2;Foglio2!A2; e via così arrivando a coprire le 5 possibilità identificate dalle 5 righe della tabella presente al foglio 2.

La parte problematica che non sono riuscito in nessun modo a far digerire a Excel è A1&[operatore contenuto in Foglio2!C1]&Foglio2!B1 e cioè quella che prende il valore da una cella, dovrebbe prendere l'operatore di confronto da un'altra cella ed unirlo infine al secondo valore da confrontare da un'altra cella.

Qualcuno mi saprebbe aiutare nel districarmi da questo groviglio dove sono andato ad infilarmi ?

Grazie mille in anticipo a chiunque voglia cimentarsi nell'impresa.

P.S. in allegato un file Excel esemplificativo di quanto scritto.
https://gofile.io/?c=LUW5Ua

[wallace&gromit]

Ciao,
ovviamente concatenando un testo otterrai un codice alfanumerico e non un'operazione eseguibile in una formula.
Il tema va quindi affrontato diversamente, per es. con una funzione "Confronta"
Da quanto si evince dai dati, la struttura è piuttosto semplice, nel senso che i valori di confronto sono sempre crescenti, quindi questa operazione non dovrebbe essere troppo difficile.
Prima di proseguire devi però specificare una cosa: che succede se i due parametri danno un'etichetta diversa?
Inoltre sei conscio che lasci sempre un buco tra la penultima e l'ultima categoria?

[Lorevigo]

Ciao,
ti ringrazio molto per esserti interessato al mio problema. Per quanto riguarda le tue due perplessità sono errori miei nel creare il foglio di test e nello spiegare la logica di funzionamento, ho creato un nuovo foglio esemplificativo corretto:
- il buco lasciato tra le categorie non ci deve essere ovviamente (risolto)
- i due parametri non possono dare un etichetta diversa in quanto devono agire come criteri combinati e cioè al soddisfare di entrambi i criteri si restituisce l'etichetta.

Nel foglio aggiornato ho inserito le formule che lo fanno funzionare secondo la logica che vorrei ottenere ma in modo statico (non pescando gli operatori di confronto dalla tabellina ma avendoli inseriti a mano nella formula). Il passaggio che vorrei ottenere dallo status quo è quello appunto di fare in modo che la formula vada a prendere il criterio da utilizzare nel test di confronto del SE dalla tabella dove sono inserite le definizioni.

Sapresti aiutarmi su questo punto ?

Spero di non aver lasciato qualche altro errore in giro e ti ringrazio molto in anticipo in ogni caso.

A presto e buona serata.

Ecco il link al nuovo file: https://easyupload.io/0c7yzx

[Anthony47]

Le formule contengono parole chiave che non possono essere ottenuti concatenando stringhe di testo, quindi in prima battuta quello che hai immaginato "non si puo' fare".
Come gia' anticipato da W&G, puoi ovviamente usare altre prestazioni di Excel per ottenere risultati analoghi.
Purtroppo la descrizione che hai usato non collima con il formato della tabella, quindi non so che cosa avevi in mente; ma la funzione Cerca.Vert, usata con "Intervallo" omesso (vedi l'help on line della Cerca.Vert) certamente puo' darci una mano
Prova infatti riscrivendo la tua tabella (prima parte) in questo modo:

Codice: Seleziona tutto

   Col A       Col B
    0       Etichetta 1
    5,001   Etichetta 2
   10,001   Etichetta 3
   50,001   Etichetta 4
 1000,001   Etichetta 5
 .....         ......


(se aggiungi altri dati, fai in modo che in col A siano sempre crescenti)

Poi usa la formula

Codice: Seleziona tutto

=CERCA.VERT(D10;A1:B19;2)


In questo modo quando D10 e' inferiore a 5 ti sara' restituito "Etichetta"; da 5,001 in avanti avrai Etichetta2; e cosi' via come da tabella.
Quando "Intervallo" e' omesso l'elenco deve essere in ordine crescente e la colonna 1 (quella con la Chiave) ha il significato "Da questo valore in avanti"

Stesso risultato si potrebbe ottenere con le funzioni Indice + Confronta.

Un'altra opzione e' creare una "funzione personalizzata". Ad esempio, inserisci questo codice in un "Modulo standard del tuo vba"

Codice: Seleziona tutto

Function StrEval(ByVal myStr As String)
'Application.Volatile
StrEval = Evaluate(myStr)
End Function

(per come fare, vedi viewtopic.php?f=26&t=103893&p=647675#p647675)

Poi sul tuo foglio puoi crearti una stringa che corrisponde a una formula valida ma per la versione Inglese di Excel, e ottenerne il calcolo tramite la funzione StrEval cosi' creata.
Ad esempio, in I1 del tuo Foglio2 crea la "stringa formula" con questa formula (vera formula):

Codice: Seleziona tutto

="if("&H1&B3&C3&",8,18)"


Se in H1 c'e' scritto 5, la stringa sara' if(5<5,8,18)

Ora in una cella scrivi la formula

Codice: Seleziona tutto

=StrEval(I1)

Puoi ora modificare il "criterio" in B3, la soglia in C3, e il valore di prova in H1 e otterrai (dovresti ottenere...) il risultato sempre coerente.

Il problema e' calcolare correttamente la stringa di formula in Inglese, ma un aiuto potra' dartelo ad esempio questo sito: https://it.excel-translator.de/translator/

Ciao, fai sapere...

[Lorevigo]

Ciao Anthony,
grazie mille per il tuo aiuto! Inizio subito col dirti che grazie alle tue dritte sono riuscito a risolvere il mio rompicapo brillantemente. Mi hai davvero aperto un mondo, non sapevo nemmeno della possibilità di creare "funzioni personalizzate" da VBA richiamabili poi direttamente come formule per eseguire delle interazioni. Anche il cerca verticale, che uso spesso, non sapevo si potesse utilizzare in questa variante senza "Intervallo".

Come applicazione definitiva ho scelto proprio quella della "funzione personalizzata" che mi ha da subito colpito e interessato molto. Usando le tue indicazioni sono riuscito ad integrare in una sola formula tutto il codice necessario ed ottenere finalmente il risultato sperato. Il sito per la traduzione automatica è davvero ottimo e fa risparmiare parecchio tempo, mi sono invece divertito ad aggiungere mille apici ed e commerciali.

Hai ragione purtroppo la descrizione che avevo dato inizialmente non collimava più con il secondo esempio di foglio di calcolo. Ti allego ora il risultato finale per renderti partecipe di come è venuto il lavoro che mi hai aiutato a fare e per rendere più chiaro lo scopo e il funzionamento del tutto.

Nel primo foglio si trovano tre colonne: le prime due di dati da confrontare secondo i criteri contenuti nella tabella al secondo foglio e la terza invece dove verrà assegnata l'etichetta corrispondente ed all'interno della quale ho inserito la formula costruita grazie ai tuoi consigli.
Nel secondo foglio è invece presente una tabellina di cinque colonne e cinque righe, tante quante le etichette presenti nella prima colonna, da assegnare in dipendenza dagli scaglioni riportati nelle colonne seguenti insieme agli operatori di confronto da utilizzare nel test.

Ora finalmente modificando un qualsiasi valore soglia o operatore di confronto (è proprio nell'operatore la novità) dalla tabellina, questa modifica verrà applicata dinamicamente ed in automatico a tutte le etichette inserite nel foglio 1.

Giusto per scrupolo ti sottopongo una stranezza (magari solo per me) cui ho dovuto far fronte: nella formula presente nella colonna delle etichette, la condizione di verifica delle due colonne di input che va a controllare che non siano vuote e se così restituisce ND non sono riuscito in alcun modo ad inserirla all'interno della stringa da far elaborare alla "funzione personalizzata" ma ho dovuto metterla come formula primaria annidando al suo interno la "funzione personalizzata". Una cosa che all'atto pratico non crea nessuna differenza ma che mi è risultata curiosa in quanto non sono riuscito a capirne il perchè anche con mille prove. Dividendo le due formule in formato stringa in una parte di verifica colonne input ed una parte di confronto ed utilizzandole singolarmente sempre come "funzioni personalizzate" entrambe funzionavano perfettamente ma una volta unite insieme non c'era più verso di farle funzionare.

Ecco il file: https://easyupload.io/izdk6y

Per pura curiosità, sei a conoscenza di altri utilizzi delle "funzioni personalizzate" come quella di calcolo di una stringa che abbiamo utilizzato in questo caso oppure di materiale sul quale fare affidamento per documentarmi a riguardo? Mi affascina molto l'argomento e credo possa essere interessante approfondirlo essendone io totalmente a digiuno.

Ti ringrazio ancora molto per il tuo aiuto competente e preciso e ti auguro una buona serata.

Colgo l'occasione anche per augurare buone feste.

Ciao, a presto.

[Anthony47]

Per le Funzioni Personalizzate comincia dall'help on line di Excel, alla voce "Creare funzioni personalizzate in Excel"

Quanto alla combinazione di due formule, non ho capito quale "formula" hai creato in formato stringa. Comunque hai scoperto che le funzioni personalizzate si possono combinare come tutte le funzioni native di excel.

sei a conoscenza di altri utilizzi delle "funzioni personalizzate" come quella di calcolo di una stringa che abbiamo utilizzato in questo caso oppure di materiale sul quale fare affidamento per documentarmi a riguardo?

Con una Funzione Utente ci puoi fare tutto quello che il linguaggio vba consente, cioe' tantissimo.
Se fai una ricerca google con la stringa

Codice: Seleziona tutto

"End Function" excel site:pc-facile.com

otterrai molte situazioni che sono state affrontate in questo modo.

Ciao

[Lorevigo]

Ciao Anthony,

scusa il ritardo nella risposta ma con le feste ho perso di vista l'argomento. Grazie mille per gli ulteriori spunti di riflessione e approfondimento.

Colgo l'occasione per augurarti buon anno e buone feste.

Ciao