[vba excel] formula e concatenazione apici

[john_revelator]

Salve a tutti. Ho un problema relativo all'uso dell'istruzione formula all'interno di excel e della concatenazione apici.

La mia formula è la seguente:

=MATR.SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO("'"&D1:D6&"'!A21:A24");2;INDIRETTO("'"&D1:D6&"'!B21:B24")))

che tradotta in inglese dovrebbe essere così:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D6&"'!A21:A24"),2,INDIRECT("'"&D1:D6&"'!B21:B24")))

Se adesso volessi utilizzarla all'interno di vba dovrei racchiuderla tra doppie virgolette

Sheets("foglio1").Range("A1").Formula = "mia_formula"

il problema è che mi incasino con gli apici.
Ho fatto diverse prove con chr(34) e simili ma non ne vengo fuori.
Qualcuno sarebbe così gentile da spiegarmi l'esatta procedura?
Grazie fin da ora.

[MarcoGG]

Ecco un classico esempio di qualcosa che personalmente non farei mai con le formule !

Cmq, vediamo di andare per gradi, cominciando da un pezzo di quella formula :

=INDIRETTO("'"&D1:D6&"'!A21:A24")

Esattamente cosa dovrebbe fare ?
Ho fatto qualche prova, ma ottengo sempre l'errore #RIF!
Sicuro che quella formula, se messa direttamente in una cella, funziona ?

Fammi un esempio funzionante, solo con la parte che ho quotato e con i valori giusti nei range "D1 : D6" e "A21 : A24" e vedo di passarti il codice per inserirla via VBA...

[ses4]

La formula di john_revelator serve per sommare tutti quei valori presenti in B21:B24 a cui corrisponde il valore 2 nella stessa riga di colonna A dei fogli di lavoro riportati nel range D1: D6.
La formula in VBA si scrive:

Sheets("Foglio1").Range("A1").Formula = "=SUMPRODUCT(SUMIF(INDIRECT(""'""&D1:D6&""'!A21:A24""),2,INDIRECT(""'""&D1:D6&""'!B21:B24"")))"

Ciao

[john_revelator]

Grazie a entrambi per l'interessamento.
Ciao Marco. Ero certo che saresti intervenuto.

Scusami, hai ragione. Ieri notte ero un pò stanco e non mi sono spiegato molto bene.

L'intervallo d1:d6 contiene l'elenco dei fogli. (es.foglio1,foglio2,....,foglio6) come scritto da ses4.
Poichè il numero di fogli non lo conosco a priori, avevo pensato tramite una semplice macro di ciclarli tutti e farli apparire nella colonna D. In questo modo posso dinamicamente recuperare l'intervallo fino all'ultima cella scritta e applicare la formula matr.somma.prodotto dinamicamente.

Spero di essere stato chiaro questa volta.

[ses4]

Non ho capito.
La funzione che ho postato soddisfa le tue esigenze oppure stai chiedendo altro?
Ciao

[john_revelator]

Originariamente inviato da ses4

Non ho capito.
La funzione che ho postato soddisfa le tue esigenze oppure stai chiedendo altro?
Ciao

Perdonami ses4. La tua soluzione è perfetta. Grazie mille.
Quindi se ho ben capito è sufficiente raddoppiare tutti i doppi apici all'interno della stringa. Grazie ancora. Adesso posso proseguire col mio lavoro.
Buon fine settimana e saluti anche a Marco che è sempre disponibilissimo

[MarcoGG]

Ciao john, .
Sì in realtà sono arrivato tardi perchè quella funzione INDIRETTO non l'ho proprio mai usata, e mi stavo chiedendo appunto a cosa ti servisse, nella speranza tu ti ravvedessi ( dopo N discussioni passate insieme su queste pagine ), invece noto che sei deciso ad intraprendere la strada delle funzioni-kilometro.
[ scherzo ! ]

[john_revelator]

Caro Marco, se esiste una soluzione alternativa son ben felice di adottarla.
Come ti accennavo il mio problema consiste nel fatto che non so a priori il numero di fogli della cartella di lavoro. Nell'esempio statico che ho fatto avevo messo nelle celle da d1 a d6 i nomi dei 6 singoli fogli. Poichè il numero di fogli è destinato a crescere ho pensato di lanciare la macro che genererà di volta in volta in una colonna l'elenco di tutti i fogli esistenti. Recupererò così il range d1:dn ed eseguirò la funzione-kilometro. Se è possibile ovviare diversamente son tutto occhi.

[MarcoGG]

Originariamente inviato da john_revelator

Caro Marco, se esiste una soluzione alternativa son ben felice di adottarla.
Come ti accennavo il mio problema consiste nel fatto che non so a priori il numero di fogli della cartella di lavoro. Nell'esempio statico che ho fatto avevo messo nelle celle da d1 a d6 i nomi dei 6 singoli fogli. Poichè il numero di fogli è destinato a crescere ho pensato di lanciare la macro che genererà di volta in volta in una colonna l'elenco di tutti i fogli esistenti. Recupererò così il range d1:dn ed eseguirò la funzione-kilometro. Se è possibile ovviare diversamente son tutto occhi.

Eh eh, Buon Appetito john ! La pappa chiama ... Domattina gli do un'occhiata senz'altro !

[ses4]

Originariamente inviato da john_revelator

Caro Marco, se esiste una soluzione alternativa son ben felice di adottarla.
Come ti accennavo il mio problema consiste nel fatto che non so a priori il numero di fogli della cartella di lavoro. Nell'esempio statico che ho fatto avevo messo nelle celle da d1 a d6 i nomi dei 6 singoli fogli. Poichè il numero di fogli è destinato a crescere ho pensato di lanciare la macro che genererà di volta in volta in una colonna l'elenco di tutti i fogli esistenti. Recupererò così il range d1:dn ed eseguirò la funzione-kilometro. Se è possibile ovviare diversamente son tutto occhi.

Puoi risolvere molto semplicemente utilizzando la tua formula originaria, per intenderci la prima che hai postato in italiano, basta trasformare il riferimento D1: D6 in un riferimento dinamico.
Per far questo si può ricorrere, onde evitare di allungare eccessivamente la formula, ai "Nomi"
Vai in Inserisci>Nome>Definisci
Introduci il nome nella casella di testo apposita, per es. "fogli"
nella casella "Riferito a:" inserisci la formula:

=SCARTO(Foglio1!$D$1;;;CONTA.VALORI(Foglio1!$D:$D))

clicca su aggiungi e dai l'ok.
Ora hai realizzato il tuo riferimento dinamico, inutile spiegarti come funziona, viste le formule che sei in grado di scrivere non avrai nessuna difficoltà a capirlo da solo ed eventualmente ad apportare modifiche.
La tua formula diventerà:

=MATR.SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO("'"&fogli&"'!A21:A24");2;INDIRETTO("'"&fogli&"'!B21:B24")))

Ovviamente il problema può essere risolto facilmente anche con VBA, ma poichè excel è un foglio di calcolo perchè non sfruttarne le potenzialità?
Forse, ci dovrei pensare, si può anche ridurre di qualche centimetro.
Ciao

[MarcoGG]

Originariamente inviato da ses4

Ovviamente il problema può essere risolto facilmente anche con VBA, ma poichè excel è un foglio di calcolo perchè non sfruttarne le potenzialità?

Pienamente d'accordo, a volte mi lascio prendere un po' la mano col VBA, perchè mi viene più naturale che stare lì a cercare / costruire formule un po' troppo prolisse per i miei gusti ...
In ogni caso, @john, ti posto ugualmente la mia soluzione che penso faccia lo stesso lavoro, senza peraltro sporcare il foglio con range intermedi e quant'altro. Poi vedrai tu... :

    Dim sommaValori As Variant
    Dim R As Range
    Dim i As Long
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        For i = 21 To 24
            If WS.Range("A" & i).Value = 2 Then sommaValori = sommaValori + WS.Range("B" & i).Value
        Next i
    Next WS

[ses4]

Originariamente inviato da MarcoGG

Pienamente d'accordo, a volte mi lascio prendere un po' la mano col VBA, perchè mi viene più naturale che stare lì a cercare / costruire formule un po' troppo prolisse per i miei gusti ...

Ciao MarcoGG,
pure io ho avuto questa tendenza, poichè trovavo più semplice creare una funzione o una subroutine in VBA, ne abusavo. Ora trovo molto più stimolante cercare di risolvere con le funzioni del foglio di calcolo e ricorro al VBA più di rado (spero di non essere passato da un eccesso all'altro).
Mi capita anche di fare entrambe le cose, quando la formula diventa troppo complessa passo al VBA, ma, se possibile, cerco ugualmente la soluzione con le sole potenzialità del foglio di calcolo per il semplice gusto di farlo e, anche se non la utilizzerò, resta la soddisfazione (o la frustrazione).
Tornando all'argomento del topic io non so cosa convenga o preferisca utilizzare john_revelator. Nel cercare di realizzare un suo progetto si è trovato di fronte un ostacolo, lo ha esposto e gli ho semplicemente indicato come superarlo senza entrare nel merito della validità della soluzione (non conoscendo il progetto non potrei comunque farlo), rispettando la strada da lui intrapresa.
In ogni caso le soluzioni prospettate contengono informazioni che possono sempre tornare utili in altre occasioni.
Ciao

[MarcoGG]

Originariamente inviato da ses4

Ciao MarcoGG,
pure io ho avuto questa tendenza, poichè trovavo più semplice creare una funzione o una subroutine in VBA, ne abusavo. Ora trovo molto più stimolante cercare di risolvere con le funzioni del foglio di calcolo e ricorro al VBA più di rado (spero di non essere passato da un eccesso all'altro).
Mi capita anche di fare entrambe le cose, quando la formula diventa troppo complessa passo al VBA, ma, se possibile, cerco ugualmente la soluzione con le sole potenzialità del foglio di calcolo per il semplice gusto di farlo e, anche se non la utilizzerò, resta la soddisfazione (o la frustrazione).
Tornando all'argomento del topic io non so cosa convenga o preferisca utilizzare john_revelator. Nel cercare di realizzare un suo progetto si è trovato di fronte un ostacolo, lo ha esposto e gli ho semplicemente indicato come superarlo senza entrare nel merito della validità della soluzione (non conoscendo il progetto non potrei comunque farlo), rispettando la strada da lui intrapresa.
In ogni caso le soluzioni prospettate contengono informazioni che possono sempre tornare utili in altre occasioni.
Ciao

Sì, molto dipende dallo stile di ognuno e dalla sua impostazione personale.
Per me è l'esatto opposto.
Ai miei primi approcci con Excel trovavo molto stimolante la ricerca della formula per ogni situazione. Adesso se una funzione va oltre N caratteri, o contiene più di 2 o 3 funzioni nidificate, butto tutto e vado con VBA ( ovvio che se devo fare un paio di SOMMA.SE uso le Fx... ).
In genere cmq ho sempre notato da parte di chi ( cliente, utente finale, amico ecc... ) doveva poi usare i miei lavori, una spiccata predilezione verso un tipo di WorkBook il più possibile automatizzato ( pulsanti, inserimenti non diretti sui Fogli, ma Fogli protetti e validazione tramite UserForms, ecc... ) e da qui la mia "deformazione professionale", dovuta anche al fatto che, in ogni caso, tutte le funzioni / formule / procedure guidate di Excel, messe assieme, costituiscono un piccolo sottoinsieme ( a mio parere circa il 30%, o anche meno ) di quanto sia possibile scrivendo codice VBA.
Quanto a leggibilità, personalmente trovo 100 caratteri di codice molto più leggibili/modificabili di una formula di 100 caratteri...
Con le formule si è costretti spesso ad inventare range temporanei, range di appoggio ecc...( basti pensare all'errore molto comune di riferimento circolare... ), che non sono invece necessari con VBA.
In questo caso la forzatura è tutta mia, dato che la risposta più immediata al quesito di john era quella che hai dato tu, ma mi piace ugualmente suggerire questo tipo di soluzione...

[john_revelator]

Grazie a entrambi per i preziosi suggerimenti.

@ses4: quando scrivi "Vai in Inserisci>Nome>Definisci" non mi è chiaro a quale range di celle devo applicare il nome. Devo selezionare l'intestazione di colonna D e applicarla a tutta la colonna? Sto facendo diverse prove ma ottengo sempre #RIF!. Sicuramente sto sbagliando qualcosa.
Nel frattempo mi documento tramite la guida in linea sulla funzione scarto() che non ho mai utilizzato.
Grazie per la pazienza.

@Marco: il tuo codice è perfetto (come sempre ). Grazie per l'ennesima volta.

[MarcoGG]

Originariamente inviato da john_revelator

@Marco: il tuo codice è perfetto (come sempre ). Grazie per l'ennesima volta.

[ses4]

Originariamente inviato da john_revelator

Grazie a entrambi per i preziosi suggerimenti.

@ses4: quando scrivi "Vai in Inserisci>Nome>Definisci" non mi è chiaro a quale range di celle devo applicare il nome. Devo selezionare l'intestazione di colonna D e applicarla a tutta la colonna? Sto facendo diverse prove ma ottengo sempre #RIF!. Sicuramente sto sbagliando qualcosa. ....

A nessun range, o meglio il range viene determinato dalla funzione scarto() che infatti deve sostituire qualsiasi cosa sia scritta nella casella "Riferito a:"
La funzione:

=SCARTO(Foglio1!$D$1;0;0;CONTA.VALORI(Foglio1!$D:$D))

significa: partendo dalla cella D1 di Foglio1, dopo esserti spostato di 0 righe in basso e di 0 colonne a destra (i valori 0 possono essere omessi), restituisci un'area delle dimensioni di un numero di righe pari al numero di celle valorizzate contenute nella colonna D e di un numero di colonne pari a 1.
Quindi ogni volta che inserirai od eliminerai il nome di un nuovo foglio in D il range "fogli" verrà ridimensionato automaticamente.

Avrai come risultato #RIF finchè non scriverai il nome di almeno un foglio a partire da D1.
Spero di essere riuscito a spiegarmi.
Ciao

[john_revelator]

Mah, avevo già provato ad inserire qualche nome di fogli ma continuo a ricevere quell'errore.

Qui trovi il file con la procedura che mi hai indicato (o almeno di come l'ho interpretata io).

http://myfreefilehosting.com/uplFinished/68ceaa7f84

[ses4]

Mah, avevo già provato ad inserire qualche nome di fogli ma continuo a ricevere quell'errore.

Ti sei dimenticato di sostituire i riferimenti a D1: D6 con il nome "fogli"

=MATR.SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO("'"&fogli&"'!A21:A24");2;INDIRETTO("'"&fogli&"'!B21:B24")))

Ciao

[john_revelator]

Originariamente inviato da ses4

Ti sei dimenticato di sostituire i riferimenti a D1: D6 con il nome "fogli"

=MATR.SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO("'"&fogli&"'!A21:A24");2;INDIRETTO("'"&fogli&"'!B21:B24")))

Ciao

Mi vergogno come un ladro.

Comunque la formula se ho ben capito per poter funzionare e dare risultati sempre aggiornati necessita che siano presenti nella colonna D i nomi di tutti i fogli. Se è così userò la tua formula e tramite una macro creerò l'elenco in colonna. Grazie ancora ses4.

[ses4]

Originariamente inviato da john_revelator

...Comunque la formula se ho ben capito per poter funzionare e dare risultati sempre aggiornati necessita che siano presenti nella colonna D i nomi di tutti i fogli. Se è così userò la tua formula e tramite una macro creerò l'elenco in colonna....

Diciamo che la formula calcola il risultato prendendo in considerazione solo i fogli il cui nome è presente in colonna D.
Nel file che hai preparato come esempio, in colonna D hai indicato solo due fogli, prova a scrivere in D3 il nome del terzo foglio e vedrai cambiare immediatamente il risultato della formula.
Ciao