Excel è potente. Se la usi molto, probabilmente conosci già molti trucchi usando le formule o l'autoformattazione, ma facendo uso delle funzioni Celle e Gamma in VBA, puoi aumentare 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.
Le celle di solito fanno riferimento a una singola cella alla volta, mentre Range 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:
Worksheets("Sheet1").Cells Questo fa riferimento alla terza cella da sinistra, della riga superiore. Cella C1:
Worksheets("Sheet1").Cells(3) Il seguente codice fa riferimento alla cella D15:
Worksheets("Sheet1").Cells(15, 4) Se lo si desidera, è anche possibile fare riferimento alla cella D15 con "Celle (15, " D ")" - è possibile utilizzare la lettera della colonna.
C'è molta flessibilità nel poter fare riferimento a una cella usando un numero per la colonna e la cella, specialmente con gli script che possono scorrere un numero elevato di celle (e eseguire 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 al suo interno).
Il formato per questa funzione è Range (Cell # 1, Cell # 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:
Worksheets("Sheet1").Range("A5") Qui, la funzione range fa riferimento a tutte le celle tra A1 e E20:
Worksheets("Sheet1").Range("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:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5)) End With Il codice sopra fa riferimento allo stesso intervallo della funzione Range ("A1: E20"). 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 conteggio delle vendite e del loro volume di vendita.
Su Sheet2 è dove si tiene traccia del proprio rating di feedback degli ultimi 30 giorni dai clienti della propria 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 ciò, vai su File > Opzioni . Fare clic su Personalizza barra multifunzione . Infine, scegli Sviluppatore dal riquadro di sinistra, Aggiungi al riquadro di destra e assicurati che la casella di controllo sia selezionata.
Ora, quando fai clic su OK e torni al foglio principale, vedrai l'opzione del menu Sviluppatore.
È possibile utilizzare il menu Inserisci per inserire un pulsante di comando o semplicemente fare clic su Visualizza codice per avviare la codifica.
In questo esempio eseguiremo lo script ogni volta che viene aperta la cartella di lavoro. Per fare ciò, basta fare clic su Visualizza codice dal menu sviluppatore e incollare la nuova funzione seguente 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. Creeremo "x" la riga, useremo un numero per richiedere i dati di ogni colonna. Il numero di righe è il numero di dipendenti, quindi questo sarà compreso tra 1 e 11. L'identificatore di colonna sarà 2 per Conteggio vendite, 3 per Volume vendite e 2 per Foglio riscontro per 2.
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() For x = 2 To 12 Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _ + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _ + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _ Next x End Sub 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 è possibile creare una logica piuttosto creativa per estrarre dati da molte celle su molti fogli differenti, ed eseguire con essi calcoli piuttosto complessi.
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 la 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() If 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 metodo "Trova"
- 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 è un potente strumento 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.