Comprendere le funzioni di Excel e le funzioni di intervallo in VBA

Excel è potente. Se la usi molto, probabilmente conosci già molti trucchi usando le formule o l'autoformattazione, ma facendo uso di cellule e Gamma funzioni in VBA, puoi potenziare l'analisi di Excel a un livello completamente nuovo.

Il problema con l'utilizzo delle funzioni Celle e Gamma in VBA è che ai livelli avanzati, molte persone hanno difficoltà a capire come funzionano effettivamente queste funzioni. Usarli può diventare molto confuso. Ecco come puoi utilizzarli in modi che probabilmente non avresti mai immaginato.

La funzione delle cellule

Le funzioni Celle e Gamma ti permettono di dire al tuo script VBA 4 Errori che puoi evitare durante la programmazione di macro di Excel con errori di VBA 4 che puoi evitare durante la programmazione di macro di Excel con VBA Il codice semplice e le macro sono le chiavi dei superpoteri di Microsoft Excel. Persino i non programmatori possono facilmente aggiungere funzionalità straordinarie ai propri fogli di lavoro con Virtual Basics for Applications (VBA). Basta evitare questi errori di programmazione per principianti! Leggi di più esattamente dove si trova il foglio di lavoro che si desidera ottenere o inserire i dati. La principale differenza tra le due celle è ciò a cui si riferiscono.

cellule di solito riferimento a una singola cella alla volta, mentre Gamma fa riferimento a un gruppo di celle contemporaneamente. Il formato per questa funzione è Celle (riga, colonna).

Questo fa riferimento a ogni singola cella nell'intero foglio. È l'unico esempio in cui la funzione Celle non fa riferimento a una singola cella:

Fogli di lavoro ( "Sheet1"). Cells

Questo fa riferimento alla terza cella da sinistra, della riga superiore. Cella C1:

Fogli di lavoro ( "Sheet1"). Le cellule (3)

Il seguente codice fa riferimento alla cella D15:

Fogli di lavoro ( "Sheet1"). Le cellule (15,4)

Se lo si desidera, è anche possibile fare riferimento alla cella D15 con “Le cellule (15,”D”)”-ti è permesso usare la lettera della colonna.

C'è molta flessibilità nel poter fare riferimento a una cella usando un numero per colonna e cella, specialmente con script che possono loop through un numero elevato di celle (ed eseguono calcoli su di esse) molto rapidamente. Ci arriveremo più in dettaglio qui sotto.

La funzione Range

In molti modi, la funzione Intervallo è molto più potente dell'uso delle celle, perché consente di fare riferimento a una singola cella oa un intervallo specifico di celle, tutto in una volta. Non si desidera eseguire il ciclo di una funzione Range, poiché i riferimenti per le celle non sono numeri (a meno che non si incorpori la funzione Celle all'interno di essa).

Il formato per questa funzione è Intervallo (cella n. 1, cella n. 2). Ogni cella può essere designata con un numero di lettera.

Diamo un'occhiata ad alcuni esempi.

Qui, la funzione di intervallo fa riferimento alla cella A5:

Fogli di lavoro ( "Sheet1"). Range ( "A5")

Qui, la funzione range fa riferimento a tutte le celle tra A1 e E20:

Fogli di lavoro ( "Sheet1") Gamma. ( "A1: E20")

Come accennato in precedenza, non è necessario utilizzare assegnazioni di celle con lettere numeriche. In realtà potresti utilizzare due funzioni di celle all'interno di una funzione di intervallo per identificare un intervallo sul foglio, in questo modo:

 Con fogli di lavoro ("Foglio1") .Range (.Cells (1, 1), _ .Cells (20, 5)) Fine con 

Il codice sopra fa riferimento allo stesso intervallo della gamma (“A1: E20”) la funzione fa. Il valore del suo utilizzo è che ti permetterebbe di scrivere codice che funzioni dinamicamente con gli intervalli usando i loop. Come funzionano i loop While nella programmazione del computer. Come funzionano i cicli di lavoro mentre i loop sono uno dei primi tipi di controllo? Imparerò nella programmazione. Probabilmente sai su while e for loops, ma cosa fa un ciclo do-while? Leggi di più .

Ora che hai capito come formattare le funzioni Celle e Gamma, immergiti nel modo in cui puoi utilizzare in modo creativo queste funzioni nel tuo codice VBA.

Elaborazione dei dati con la funzione delle celle

La funzione Celle è molto utile quando si ha una formula complessa che si desidera eseguire su più intervalli di celle. Questi intervalli possono anche esistere su più fogli.

Facciamo un semplice esempio. Diciamo che gestisci un team di vendita di 11 persone e che ogni mese desideri esaminare le loro prestazioni.

Potresti avere sheet1 che tiene traccia del loro conteggio delle vendite e del loro volume di vendite.

Sopra Foglio2 è dove si traccia il loro rating di feedback per gli ultimi 30 giorni dai clienti della vostra azienda.

Se vuoi calcolare il bonus sul primo foglio usando i valori dei due fogli, ci sono molti modi per farlo. È possibile scrivere una formula nella prima cella che esegue il calcolo utilizzando i dati tra i due fogli e trascinandoli verso il basso. Funzionerà.

Un'alternativa a ciò è la creazione di uno script VBA che si attiva quando si apre il foglio o attivato da un pulsante di comando sul foglio in modo da poter controllare quando calcola. È possibile utilizzare uno script VBA per inserire comunque tutti i dati di vendita da un file esterno.

Quindi, perché non attivare i calcoli per la colonna bonus nello stesso script in quel momento?

La funzione Celle in azione

Se non hai mai scritto VBA in Excel, devi abilitare la voce del menu Sviluppatore. Per fare questo, andare a File > Opzioni. Clicca su Personalizza il nastro. Infine, scegli Sviluppatore dal riquadro di sinistra, Inserisci nel pannello di destra e assicurarsi che la casella di controllo sia selezionata.

Ora, quando fai clic ok e torna al foglio principale, vedrai l'opzione del menu Sviluppatore.

Puoi usare il Inserire menu per inserire un pulsante di comando, o semplicemente fare clic Visualizza codice per iniziare la codifica.

In questo esempio eseguiremo lo script ogni volta che viene aperta la cartella di lavoro. Per fare ciò, basta fare clic Visualizza codice dal menu dello sviluppatore e incollare la seguente nuova funzione nella finestra del codice.

Private Sub Workbook_Open () End Sub

La tua finestra di codice sarà simile a questa.

Ora sei pronto per scrivere il codice per gestire il calcolo. Utilizzando un singolo ciclo, è possibile esaminare tutti gli 11 dipendenti e, con la funzione Celle, inserire le tre variabili necessarie per il calcolo.

Ricorda che la funzione Celle ha riga e colonna come parametri per identificare ogni singola cella. Lo faremo “X” la riga, utilizzare un numero per richiedere i dati di ogni colonna. Il numero di righe è il numero di dipendenti, quindi sarà compreso tra 1 e 11. L'identificatore di colonna sarà 2 per Conteggio vendite, 3 per Volume vendite e 2 per Foglio retroazione per Punteggio di feedback.

Il calcolo finale utilizza le seguenti percentuali per aggiungere fino al 100 percento del punteggio totale del bonus. Si basa su un numero di vendite ideale di 50, un volume di vendita di $ 50.000 e un punteggio di feedback di 10.

• (Conteggio vendite / 50) x 0,4
• (Volume vendite / 50.000) x 0,5
• (Punteggio di feedback / 10) x 0,1

Questo semplice approccio offre ai dipendenti delle vendite un bonus ponderato. Per un conteggio di 50, un volume di $ 50.000 e un punteggio di 10, ottengono l'intero bonus massimo per il mese. Comunque qualsiasi cosa sotto perfetto su qualsiasi fattore riduce il bonus. Qualunque cosa migliore dell'ideale aumenta il bonus.

Ora vediamo come tutta questa logica può essere tirata fuori da uno script VBA molto semplice e breve:

Private Sub Workbook_Open () Per x = Da 2 a 12 fogli di lavoro ("Foglio1"). Celle (x, 4) = (Fogli di lavoro ("Foglio1"). Celle (x, 2). Valore / 50) * 0,4 _ + (Fogli di lavoro ("Foglio1"). Celle (x, 3) .Valore / 50000) * 0,5 _ + (Fogli di lavoro ("Foglio2"). Celle (x, 2). Valore / 10) * 0,1 _ Prossimo x Fine Sotto 

Questo è l'aspetto di questo script.

Se si desidera che la colonna Bonus mostri il bonus in dollari effettivo anziché la percentuale, è possibile moltiplicarla per l'importo massimo del bonus. Meglio ancora, posiziona l'importo in una cella su un altro foglio e fai riferimento al codice. Ciò renderebbe più semplice modificare il valore in seguito senza dover modificare il codice.

La bellezza della funzione Celle è che puoi creare una logica piuttosto creativa da cui estrarre dati molte cellule su molti fogli differenti, ed esegui calcoli piuttosto complessi con loro.

Puoi eseguire tutti i tipi di azioni sulle celle usando la funzione Celle: cose come cancellare le celle, cambiare la formattazione dei caratteri e molto altro.

Per esplorare tutto ciò che puoi fare, controlla la pagina Microsoft MSDN per l'oggetto Cells.

Formattare le celle con la funzione Range

Per il looping di molte celle una alla volta, la funzione Cells è perfetta. Ma se vuoi applicare qualcosa all'intero intervallo di celle tutto in una volta, la funzione Intervallo è molto più efficiente.

Un caso d'uso per questo potrebbe essere quello di formattare un intervallo di celle usando lo script, se sono soddisfatte determinate condizioni.

Ad esempio, diciamo che se il conteggio di tutti i volumi di vendita tra tutti gli addetti alle vendite supera $ 400.000 in totale, si desidera evidenziare tutte le celle nella colonna dei bonus in verde per indicare che la squadra ha guadagnato un bonus extra per squadra.

Diamo un'occhiata a come è possibile farlo con una dichiarazione IF Come utilizzare le istruzioni IF in Microsoft Excel Come utilizzare le istruzioni IF in Microsoft Excel Che tu sia un esperto esperto o un principiante di fogli di calcolo, ti consigliamo di dare un'occhiata a questo guida alle istruzioni IF in Excel. Leggi di più .

Private Sub Workbook_Open () Se Worksheets ("Sheet1"). Cells (13, 3) .Value> 400000 Then ActiveSheet.Range ("D2: D12"). Interior.ColorIndex = 4 End If End Sub

Quando viene eseguito, se la cella supera l'obiettivo del team, tutte le celle dell'intervallo verranno riempite in verde.

Questo è solo un semplice esempio delle numerose azioni che puoi eseguire su gruppi di celle usando la funzione Range. Altre cose che puoi fare includono:

• Applica un contorno attorno al gruppo
• Controlla l'ortografia del testo all'interno di un intervallo di celle
• Cancella, copia o taglia le celle
• Cerca in un intervallo con il “Trova” metodo
• Molto di piu

Assicurati di leggere la pagina Microsoft MSDN per l'oggetto Range per vedere tutte le possibilità.

Porta Excel al livello successivo

Ora che comprendi le differenze tra le funzioni Cells e Range, è il momento di portare lo scripting VBA al livello successivo. L'articolo di Dann sull'utilizzo delle funzioni di conteggio e aggiunta in Excel ti consentirà di creare script ancora più avanzati in grado di accumulare valori in tutti i tuoi set di dati molto rapidamente.

E se hai appena iniziato con VBA in Excel, non dimenticare che abbiamo una guida introduttiva fantastica per Excel VBA. Esercitazione di programmazione Excel VBA per principianti Il tutorial VBA per la programmazione Excel per principianti VBA è uno strumento di alimentazione di Microsoft Office. Puoi usarlo per automatizzare le attività con macro, impostare trigger e molto altro. Ti presenteremo alla programmazione di base visiva di Excel con un semplice progetto. Leggi di più anche per te.

.