Home-theater-designers
Excel è potente. Se lo usi molto, probabilmente conosci già molti trucchi usando le formule o la formattazione automatica, ma facendo uso di cellule e Gamma funzioni in VBA, puoi portare le tue analisi di Excel a un livello completamente nuovo.
Il problema con l'utilizzo delle funzioni Celle e Intervallo in VBA è che ai livelli avanzati, la maggior parte delle persone ha 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 Intervallo ti permettono di dirlo il tuo script VBA esattamente dove sul foglio di lavoro si desidera ottenere o inserire i dati. La principale differenza tra le due celle è ciò a cui fanno riferimento.
cellule di solito fa 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 dell'intero foglio. È l'unico esempio in cui la funzione Celle non fa riferimento a una singola cella:
Worksheets('Sheet1').CellsQuesto fa riferimento alla terza cella da sinistra, della riga superiore. Cella C1:
Worksheets('Sheet1').Cells(3)Il codice seguente fa riferimento alla cella D15:
Worksheets('Sheet1').Cells(15,4)Se lo desideri, puoi anche fare riferimento alla cella D15 con 'Cells(15,'D')'---puoi utilizzare 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 gran numero di celle (ed eseguire calcoli su di esse) molto rapidamente. Ci arriveremo più in dettaglio di seguito.
La funzione di gamma
In molti modi, la funzione Intervallo è molto più potente dell'utilizzo di Celle, perché consente di fare riferimento a una singola cella o a un intervallo specifico di celle, tutto in una volta. Non vorrai scorrere una funzione Intervallo, perché i riferimenti per le celle non sono numeri (a meno che non incorpori la funzione Celle al suo interno).
Il formato per questa funzione è Intervallo (cella n. 1, cella n. 2) . Ogni cella può essere designata da una lettera-numero.
Diamo un'occhiata ad alcuni esempi.
come creare un'icona per Windows 10
Qui, la funzione di intervallo fa riferimento alla cella A5:
Worksheets('Sheet1').Range('A5')Qui, la funzione di intervallo 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 numeri e lettere. Potresti effettivamente utilizzare due funzioni Celle all'interno di una funzione 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 nell'usarlo è che ti permetterebbe di scrivere codice che funzioni dinamicamente con gli intervalli usando i loop .
Ora che hai capito come formattare le funzioni Celle e Intervallo, esaminiamo come puoi utilizzare in modo creativo queste funzioni nel tuo codice VBA.
Elaborazione dei dati con la funzione celle
La funzione Celle è molto utile quando si dispone di una formula complessa che si desidera eseguire su più intervalli di celle. Questi intervalli possono esistere anche su più fogli.
Facciamo un semplice esempio. Supponiamo che tu gestisca un team di vendita di 11 persone e che ogni mese desideri esaminare le loro prestazioni.
Potresti avere Foglio1 che tiene traccia del conteggio delle vendite e del volume delle vendite.
Sopra Foglio2 è dove tieni traccia della loro valutazione di feedback per gli ultimi 30 giorni dai clienti della tua azienda.
Se vuoi calcolare il bonus sul primo foglio utilizzando i valori dei due fogli, ci sono diversi modi per farlo. Potresti scrivere una formula nella prima cella che esegua il calcolo utilizzando i dati sui due fogli e trascinarla verso il basso. Funzionerà.
Un'alternativa a ciò è la creazione di uno script VBA che viene attivato per l'esecuzione ogni volta che si apre il foglio o attivato da un pulsante di comando sul foglio in modo da poter controllare quando viene calcolato. Potresti comunque utilizzare uno script VBA per estrarre tutti i dati di vendita da un file esterno.
Allora perché non attivare semplicemente i calcoli per la colonna bonus nello stesso script in quel momento?
La funzione delle cellule in azione
Se non hai mai scritto VBA in Excel prima, dovrai abilitare la voce di menu Sviluppatore. Per farlo, vai su File > Opzioni . Clicca su Personalizza il nastro . Infine, scegli Sviluppatore dal riquadro di sinistra, Aggiungere nel riquadro di destra e assicurati che la casella di controllo sia selezionata.
Ora, quando clicchi ok e torna al foglio principale, vedrai l'opzione di menu Sviluppatore.
Puoi usare il Inserire menu per inserire un pulsante di comando, o semplicemente fare clic Visualizza codice per iniziare a codificare.
In questo esempio eseguiremo lo script ogni volta che viene aperta la cartella di lavoro. Per farlo, basta fare clic Visualizza codice dal menu sviluppatore e incolla la seguente nuova funzione nella finestra del codice.
Private Sub Workbook_Open()
End SubLa tua finestra del codice sarà simile a questa.
Ora sei pronto per scrivere il codice per gestire il calcolo. Utilizzando un singolo ciclo, puoi scorrere tutti gli 11 dipendenti e con la funzione Celle inserire le tre variabili necessarie per il calcolo.
Ricorda che la funzione Celle ha righe e colonne come parametri per identificare ogni singola cella. Faremo 'x' la riga, utilizzeremo 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 il conteggio delle vendite, 3 per il volume delle vendite e 2 dal foglio 2 per il punteggio di feedback.
Il calcolo finale utilizza le seguenti percentuali per aggiungere fino al 100% del punteggio bonus totale. Si basa su un conteggio delle vendite ideale pari a 50, un volume delle vendite pari a $ 50.000 e un punteggio di feedback di 10.
- (Conteggio vendite/50) x 0,4
- (Volume di vendita/50.000) x 0,5
- (Punteggio feedback/10) x 0,1
Questo semplice approccio offre agli addetti alle 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. Tuttavia, qualsiasi cosa sotto perfetta su qualsiasi fattore riduce il bonus. Qualsiasi cosa migliore dell'ideale aumenta il bonus.
Ora diamo un'occhiata a come tutta quella logica può essere tirata fuori in 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
Ecco come apparirà l'output di questo script.
cosa si può fare con un lampone pi
Se desideri che la colonna Bonus mostri l'effettivo bonus in dollari anziché la percentuale, puoi moltiplicarlo per l'importo massimo del bonus. Meglio ancora, inserisci tale importo in una cella su un altro foglio e fai riferimento ad esso nel tuo codice. Ciò renderebbe più semplice modificare il valore in un secondo momento senza dover modificare il codice.
La bellezza della funzione Celle è che puoi costruire una logica piuttosto creativa da cui estrarre i dati molte cellule su molti fogli diversi ed eseguire alcuni 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 ulteriormente, dai un'occhiata a Pagina Microsoft MSDN per l'oggetto Celle.
Formattare le celle con la funzione di intervallo
Per scorrere più celle una alla volta, la funzione Celle è perfetta. Ma se vuoi applicare qualcosa a un intero intervallo di celle contemporaneamente, la funzione Intervallo è molto più efficiente.
Un caso d'uso potrebbe essere quello di formattare un intervallo di celle utilizzando lo script, se vengono soddisfatte determinate condizioni.
Ad esempio, supponiamo che se il conteggio di tutto il volume delle vendite di tutti i dipendenti delle vendite supera $ 400.000 in totale, si desidera evidenziare tutte le celle nella colonna del bonus in verde per indicare che il team ha guadagnato un bonus di squadra aggiuntivo.
Diamo un'occhiata a come puoi farlo con un'istruzione IF .
Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End SubQuando viene eseguito, se la cella si trova sopra l'obiettivo della squadra, tutte le celle nell'intervallo verranno riempite in verde.
Questo è solo un semplice esempio delle molte azioni che puoi eseguire su gruppi di celle utilizzando la funzione Intervallo. Altre cose che puoi fare includono:
- Applicare un contorno intorno 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 il Pagina Microsoft MSDN per l'oggetto Range per vedere tutte le possibilità.
Porta Excel al livello successivo
Ora che hai compreso le differenze tra le funzioni Cells e Range, è il momento di portare il tuo script 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 che possono accumulare valori in tutti i tuoi set di dati molto rapidamente.
E se hai appena iniziato con VBA in Excel, non dimenticare che abbiamo un fantastico guida introduttiva a Excel VBA anche per te.
Condividere Condividere Tweet E-mail Canon vs Nikon: quale marca di fotocamere è migliore?Canon e Nikon sono i due più grandi nomi nel settore delle fotocamere. Ma quale marca offre la migliore gamma di fotocamere e obiettivi?
Leggi Avanti Argomenti correlati- Produttività
- Programmazione
- Programmazione Visual Basic
- Microsoft Excel
Ryan ha una laurea in ingegneria elettrica. Ha lavorato 13 anni nell'ingegneria dell'automazione, 5 anni nell'IT e ora è un ingegnere delle app. Ex caporedattore di MakeUseOf, ha parlato a conferenze nazionali sulla visualizzazione dei dati ed è apparso su TV e radio nazionali.
Altro da Ryan DubeIscriviti alla nostra Newsletter
Iscriviti alla nostra newsletter per consigli tecnici, recensioni, ebook gratuiti e offerte esclusive!
Clicca qui per iscriverti