Funzione SOMMA con riferimenti dinamici

Hai un problema con Excel?

Nuova discussione

Funzione SOMMA() con riferimenti dinamici
di peopeo (utente non iscritto) data: 17/08/2013 13:32:53
Salve!

sto sviluppando piu tabelle tramite uno script VBA dinamicamente su più colonne, fino ad ora ho utlizzato egregiamente le funzioni indiretto(indirizzo()) per impostare le celle nelle formule. ma la funzione SOMMA() non accetta questo tipo di riferimento

non so se ho reso l'idea, ho riportato un semplice esempio del mio codice so che potrei fare la somma in automatico selezionando tutte e 10 le celle del tipo =(INDIRETTO(INDIRIZZO(1;1)))+(INDIRETTO(INDIRIZZO(2;1)))....

ma mi interesserebbe snellire ove possibile il codice e saper usare anche la funzione SOMMA senza i riferimenti delle colonne a lettere.

grazie.

dim rigo as byte
dim colonna as byte
dim contatore_rigo as byte
dim contatore_colonne as byte

rigo = 1
colonna = 1

for contatore_colonne = 1 to 20
   
     for contatore_rigo = 1 to 10
           Cells(rigo, colonna).FormulaLocal = "=10*20"
           rigo = rigo + 1
     next 
          cells(rigo + 2, colonna).FormulaLocal = "=somma()" 'devo sommare le 10 celle della colonna di riferimento
     rigo = 1
     colonna = colonna + 1
next

di Grograman data: 17/08/2013 13:58:36
E' possibile vedere il file di esempio?

In ogni caso sconsiglio di dichiarare come byte variabili che vanno utilizzate per riferimenti di righe e colonne.
Ancora ancora una integer per le colonne, ma per le righe è d'uopo utilizzare una variabile di tipo long!

di Grograman data: 17/08/2013 14:09:49
Intanto ecco un esempio per riportare la somma che hai scritto prima senza ai due cicli (sono inutili e ridondanti trattandosi di formulelocal).

Lasciamo perdere per ora la paternità degli oggetti, ma guarda come ti basta utilizzare due variabili e applicare le somme contemporaneamente su tutte le celle interessate senza farlo riga per riga.

Sub somme()
    Dim x As Long, lngRighe As Long, lngColonne As Long
    lngRighe = 10
    lngColonne = 20
    Range(Cells(1, 1), Cells(lngRighe, lngColonne)).FormulaLocal = "=10*20"
    x = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range(Cells(x, 1), Cells(x, lngColonne)).FormulaLocal = "=somma(a1:a" & x - 1 & ")" 'devo sommare le 10 celle della colonna di riferimento
End Sub

di peopeo (utente non iscritto) data: 20/08/2013 01:11:03
l'esempio fatto era solo per i dati a disposizione....solitamente uso integer ;)

l'esempio riportato non rispecchia l'algoritmo che uso per classificare i dati, ho una matrice di 600 righe per 4 colonne, ogni 10 righe rappresenta un gruppo di osservazioni

devo creare una tabella che analizza queste 10 osservazioni e riorganizzarle in colonne, ecco perchè utilizzo indiretto(indirizzo(riga; colonna)) e i cicli for; non devo creare un algoritmo in vba che mi analizzi i dati devo creare un algoritmo che mi crei una tabella che analizza i dati della matrice e che mi restituisca un valore....una volta creata la tabella (e tante altre) verranno distribuite ad altre persone che non possono eseguire codice vba quindi un foglio di excel sprovvisto di macro.....senza entrare nel dettaglio

mi serve una funzione tipo somma() che non funzioni con i normali parametri di excel numeri/lettere ma che funzioni con un numero che identifica il rigo e un numero che identifica la colonna ovviamente di un range di celle

grazie per le risposte date :)
di peopeo (utente non iscritto) data: 20/08/2013 01:30:07
non esiste un =somma(indirizzo(1;1):indirizzo(1;10))?????? sarebbe l'equivalenti di somma(a1:a10)

lo stile r1c1 è un pò macchinoso dovrei riabituarmi prima di poter scrivere righe e righe di codice senza commettere centinaia di errori
di isy data: 20/08/2013 08:10:47
Ciao

Se alleghi un esempio delle formule necessarie è possibile utilizzare anche il vba.
Puoi utilizzare una formula come ...
=SOMMA(SCARTO(C$1:C$2;RIF.RIGA(A1);0))
di Vecchio Frac data: 20/08/2013 09:45:44
cit. "=somma(indirizzo(1;1):indirizzo(1;10))?????? sarebbe l'equivalenti di somma(a1:a10) "
---> indirizzo(1;1) restituisce $A$1 ma indirizzo(1;10)) non restituisce $A$10 (bensì $J$1) perchè il 10 si riferisce alla colonna, non alla riga.
Quello che intendi fare tu si può ottenere con questa formula:
=SOMMA(INDIRETTO(INDIRIZZO(1;1)&":"&INDIRIZZO(10;1)))
concatenando le espressioni stringa che rappresentano l'intervallo desiderato, valutandolo poi con indirizzo una volta ottenuta la stringa che rappresenta il range.

cit. "mi serve una funzione tipo somma() che non funzioni con i normali parametri di excel numeri/lettere ma che funzioni con un numero che identifica il rigo e un numero che identifica la colonna "
---> Allora te la devi costruire come function definita dall'utente.

@Grograman
Benvenuto nel team di utenti esperti ^_^
Per cortesia contattami qui: staff@excelvba.it
di peopeo (utente non iscritto) data: 20/08/2013 13:19:46
QUOTE by: Vecchio Frac

hai ragione scusa ; l'orario mi ha confuso ;) la formula giusta è =indiretto(indirizzo(riga;colonna)) ho confuso i valori

in ogni caso tornando a noi la tua formula è più che corretta; non sapevo che nella funzione SOMMA() si inserisse una STRINGA come variabile; ecco perchè mi dava errore perchè come la formulavo io riconosceva il contenuto delle parentesi come la divisione fra due stringhe, ovvero errato.

grazie mille hai capito al volo il mio intento posso continuare con il mio progetto

grazie anche agli altri per l'interessamento mostrato :)
di Grograman data: 20/08/2013 13:37:57
Già che siamo in tema, ecco come estendere il range fino all'ultima cella piena della colonna
=SOMMA(INDIRETTO(INDIRIZZO(1;1)&":"&INDIRIZZO(CONTA.NUMERI(A:A);1)))
di Vecchio Frac data: 20/08/2013 14:31:23
@Grograman
Sì, a patto che non ci siano righe vuote nella colonna, altrimenti sballa.
Se ci sono righe vuote, CONTA.NUMERI restituisce un valore inferiore a quello dell'ultima riga valorizzata e quindi il riferimento restituito da INDIRIZZO non è quello corretto.
Una formula più mostruosa ma corretta è quella che propongo, da inserirsi in forma matriciale; e qui attendo chi è più esperto di me :)

p.s. la parte dopo MAX restituisce la riga dell'ultima cella valorizzata nel range A1:A65535. Contributo di Harry di qualche tempo fa ;)
=SOMMA(INDIRETTO(INDIRIZZO(1;1)&":"&INDIRIZZO(MAX(SE($A$1:$A$65535<>"";RIF.RIGA($A$1:$A$65535);""));1)))

Vuoi Approfondire?