Come creare una macro in Excel. Avvio rapido: creazione di una macro

Proprio come in altre applicazioni Microsoft Office, c'è una cosa così interessante come una macro. In poche parole, una macro è una sequenza programmabile di azioni. Ci sono due modi per crearlo:

1. Utilizzando l'apposita voce di menu;

2. Manualmente.

Il primo metodo è molto semplice e non richiede particolari abilità di programmazione. Ma prima di iniziare a creare una macro, devi approfondire le impostazioni di sicurezza. Vai su File / Opzioni / Centro protezione / Impostazioni Centro protezione / Impostazioni macro. Qui devi scegliere Includi tutte le macro.

Ora è tutto pronto e procediamo direttamente alla creazione di una macro. Apri la scheda Sviluppatore e fare clic.

Apparirà una finestra in cui è necessario specificare il nome della macro, assegnare un tasto per l'avvio rapido e selezionare anche la cartella di lavoro in cui si desidera salvare la macro.

Dopo aver premuto il pulsante ok si avvia la registrazione delle macro. Tutto ciò che fai sarà registrato. Ad esempio, puoi creare una tabella e modificarla: imposta il numero richiesto di righe, colonne e la loro larghezza. Quindi, per interrompere la registrazione, è necessario fare clic sul pulsante corrispondente.

Ora, per eseguire la nostra macro, è necessario sulla scheda Sviluppatore spingere macro.

Nella finestra che appare, trova la macro che abbiamo creato e fai clic su Eseguire... Puoi anche utilizzare la scorciatoia da tastiera che abbiamo inserito durante la creazione della macro.

Non ci sono molti vantaggi in questo metodo di creazione di macro. Nella maggior parte dei casi, tale macro può essere applicata solo nella cartella di lavoro corrente e per una situazione specifica. È molto più efficiente usare una macro scritta usando un linguaggio di programmazione Visual Basic... Tuttavia, la creazione di una tale macro richiede determinate abilità. Viene considerato un esempio di creazione di una macro in VBA.

Buongiorno a tutti. Alexey Gulynin è in contatto. In questo articolo, vorrei parlare di come registrare una macro... Nei miei esempi, userò Microsoft Excel 2007, ma puoi usarne uno qualsiasi (è auspicabile che la versione non fosse inferiore alla mia). Vai su Excel, seleziona Visualizza - Macro - Registra macro:

Nelle versioni 2010 e 2013, il pannello potrebbe differire, ma non in modo significativo. Successivamente, dovrebbe aprirsi questa finestra di dialogo, chiamata "Registra macro":

Specifica il nome della macro se questa è la soluzione finale e la utilizzerai in futuro. Se hai intenzione di registrare una macro per spiare qualcosa (che hai dimenticato), puoi lasciare il nome così com'è.

È vietato utilizzare il nome della macro: spazi, segni di punteggiatura, numeri all'inizio del nome. Puoi usare: lettere inglesi e russe, numeri (non all'inizio), trattino basso. È inoltre vietato nominare una macro il cui nome sarà lo stesso di qualsiasi oggetto esistente in Excel.
Per una macro, puoi impostare una scorciatoia da tastiera, quando viene premuta, verrà eseguita. Tutte le macro non hanno bisogno di appendere i tasti di scelta rapida, devi solo riagganciare quelli che useremo spesso. In generale, ci sono molti modi per eseguire una macro, come opzione, può essere posizionata sulla barra multifunzione (a partire dalla versione 2010), nella versione 2007 è presente una barra multifunzione, ma non può essere modificata, può essere posizionata su un foglio. Inoltre, una macro può essere eseguita su qualsiasi evento. I tasti di scelta rapida hanno sensibilità sia al caso che al layout, quindi fai attenzione a quale tasto di scelta rapida assegnare a una macro. Puoi usare lettere grandi, per questo devi premere Ctrl + Maiusc + La tua lettera.

Se la macro viene salvata in questo libro, sarà fisicamente in questo file. Pertanto, alla chiusura del file, questa macro non sarà disponibile. Quando è giustificato farlo: una macro è necessaria solo per i calcoli in questo libro e non in altri. Se questa macro deve essere utilizzata in altri file Excel (cartelle di lavoro), questo file dovrà essere sempre tenuto aperto o costantemente aperto e chiuso, il che è completamente scomodo. Qui devi scegliere un altro modo di salvare: libro macro personale.

È quasi un file normale e si chiama Personal.xlsb. Questo file si trova nella cartella XLStart e si apre in modalità nascosta. Finora non abbiamo scritto nulla su questo file - non esiste. Non appena scriviamo qualcosa, apparirà. Tutto nella cartella XLStart si avvia automaticamente. Il vantaggio qui è che la macro che hai registrato nel tuo libro mastro personale sarà sempre disponibile (non dovrai aprirla costantemente - chiudila). Attiro la vostra attenzione sul fatto che sarà disponibile solo su questo computer.
Puoi anche aggiungere una descrizione alla macro.

Scriviamo la seguente macro, che creerà un nuovo foglio e scriverà la frase Hello VBA nella cella C4. Inoltre, l'iscrizione dovrebbe essere in rosso e lo stile dovrebbe essere in grassetto. Fare clic su OK.
Inseriamo il foglio in questo modo:

Ora nella cella C4 scriviamo la frase Hello VBA. Quindi premi Ctrl + Invio per rimanere nella stessa cella e applicare la formattazione. Se premiamo semplicemente Invio, il focus andrà su un'altra cella (non ne abbiamo bisogno). Quindi fai clic con il pulsante destro del mouse sulla cella e seleziona Formato celle e vai alla scheda Carattere, seleziona rosso e colore e rendi il carattere in grassetto:

Fare clic su OK. Ora possiamo completare la nostra macro. Questo può essere fatto in diversi modi: Visualizza - Macro - Interrompi registrazione, può essere disabilitato anche nell'angolo in basso a sinistra (nella versione 2007) nella barra di stato (lo vedete nell'immagine 3). "Foglio4" è stato creato. Eliminiamolo e usiamo una macro per creare un nuovo foglio. Per fare ciò, utilizzeremo il tasto di scelta rapida che abbiamo impostato per la macro Ctrl + Maiusc + D. Assicurati di essere sul layout russo. Se hai fatto tutto correttamente, dovresti aver creato un nuovo foglio con il record di cui abbiamo bisogno.

Excel ha la capacità più potente, ma allo stesso tempo utilizzata molto raramente, di creare sequenze automatiche di azioni utilizzando le macro. Una macro è una soluzione ideale se hai a che fare con un'attività dello stesso tipo che viene ripetuta molte volte. Ad esempio, elaborazione di dati o formattazione di documenti secondo un modello standardizzato. Allo stesso tempo, non è necessaria la conoscenza dei linguaggi di programmazione.

Sei già curioso di sapere cos'è una macro e come funziona? Quindi vai avanti coraggiosamente - quindi, passo dopo passo, esamineremo l'intero processo di creazione di una macro insieme a te.

Cos'è Macro?

Una macro in Microsoft Office (sì, questa funzionalità funziona allo stesso modo in molte applicazioni della suite Microsoft Office) è un codice di programma in un linguaggio di programmazione (VBA) memorizzato all'interno di un documento. Per renderlo più chiaro, un documento di Microsoft Office può essere paragonato a una pagina HTML, quindi una macro è analoga a Javascript. Ciò che Javascript può fare con i dati HTML su una pagina Web è molto simile a ciò che può fare una macro con i dati in un documento di Microsoft Office.

Le macro sono in grado di eseguire quasi tutte le azioni che desideri su un documento. Eccone alcuni (una parte molto piccola):

• Applicare stili e formattazione.
• Eseguire varie operazioni con dati numerici e di testo.
• Utilizzare origini dati esterne (file di database, documenti di testo, ecc.)
• Crea un nuovo documento.
• Esegui tutte le azioni di cui sopra in qualsiasi combinazione.

Creazione di una macro: un esempio pratico

Ad esempio, prendiamo il file più comune CSV... È un semplice foglio di calcolo 10x20 pieno di numeri da 0 a 100 con intestazioni di colonne e righe. Il nostro compito è trasformare questo set di dati in una tabella formattata presentabile e generare i totali per ogni riga.

Come già accennato, una macro è un codice scritto nel linguaggio di programmazione VBA. Ma in Excel, puoi creare un programma senza scrivere una singola riga di codice, cosa che faremo adesso.

Per creare una macro, apri Visualizzazione(Visualizza)> macro(Macro)> Registra macro(Registrazione di una macro...)

Dai un nome alla tua macro (senza spazi) e premi ok.

Da questo momento in poi, TUTTE le tue azioni con il documento vengono registrate: modifiche alle celle, scorrimento della tabella, persino ridimensionamento della finestra.

Excel segnala che la registrazione macro è abilitata in due punti. Innanzitutto, nel menu macro(Macro) - invece di una stringa Registra macro(Registrazione macro...) è apparsa la riga Interrompi registrazione(Interrompi la registrazione).

Secondo, nell'angolo in basso a sinistra della finestra di Excel. Icona Fermare(quadrato piccolo) indica che la modalità di registrazione macro è attiva. Facendo clic su di esso si interromperà la registrazione. Al contrario, quando la modalità di registrazione non è abilitata, è presente un'icona in questo punto per abilitare la registrazione delle macro. Facendo clic su di esso si otterrà lo stesso risultato dell'abilitazione della registrazione tramite il menu.

Ora che la modalità di registrazione macro è attiva, passiamo al nostro compito. Innanzitutto, aggiungiamo le intestazioni per i dati di riepilogo.

• = SOMMA (B2: K2) o = SOMMA (B2: K2)
• = MEDIA (LA2: K2) o = MEDIA (LA2: K2)
• = MIN (B2: K2) o = MIN (B2: K2)
• = MASSIMO (L2: K2) o = MASSIMO (L2: K2)
• = MEDIA (LA2: K2) o = MEDIA (LA2: K2)

Ora seleziona le celle con le formule e copiale su tutte le righe della nostra tabella tirando la maniglia di completamento automatico.

Dopo aver completato questo passaggio, i totali corrispondenti dovrebbero apparire su ogni riga.

Rispettivamente:

• = SOMMA (L2: L21) o = SOMMA (L2: L21)
• = MEDIA (B2: K21) o = MEDIA (B2: K21)- per calcolare questo valore è necessario prendere esattamente i dati iniziali della tabella. Se prendi la media delle medie delle singole righe, il risultato sarà diverso.
• = MIN (N2: N21) o = MIN (N2: N21)
• = MASSIMO (O2: O21) o = MASSIMO (O2: O21)
• = MEDIA (LA2: K21) o = MEDIA (LA2: K21)- calcoliamo utilizzando i dati iniziali della tabella, per il motivo sopra indicato.

Ora che abbiamo finito con i calcoli, iniziamo a formattare. Per cominciare, imposta lo stesso formato di visualizzazione dei dati per tutte le celle. Seleziona tutte le celle sul foglio, per questo usa la scorciatoia da tastiera Ctrl + A, oppure clicca sull'icona Seleziona tutto che si trova all'intersezione delle intestazioni di riga e di colonna. Quindi premere Stile virgola Scheda (Formato delimitato) Casa(Casa).

• Carattere tipografico in grassetto.
• Allineamento al centro.
• Riempi di colore.

Infine, personalizzeremo il formato dei totali.

Ecco come dovrebbe apparire alla fine:

Se sei soddisfatto di tutto, interrompi la registrazione della macro.

Congratulazioni! Hai appena registrato tu stesso la tua prima macro di Excel.

Per utilizzare la macro creata, è necessario salvare il documento Excel in un formato che supporti le macro. Innanzitutto, devi eliminare tutti i dati dalla tabella che abbiamo creato, ad es. farne un modello vuoto. Il fatto è che in futuro, lavorando con questo modello, importeremo in esso i dati più recenti e rilevanti.

Per cancellare tutti i dati da tutte le celle, fare clic con il tasto destro sull'icona Seleziona tutto, che si trova all'intersezione delle intestazioni di riga e colonna, e dal menu contestuale seleziona la voce Elimina(Elimina).

Ora il nostro foglio è completamente cancellato da tutti i dati, mentre la macro rimane registrata. Dobbiamo salvare la cartella di lavoro come modello di macro Excel con estensione XLTM.

Un punto importante! Se salvi il file con l'estensione XLTX, allora la macro non funzionerà al suo interno. A proposito, puoi salvare la cartella di lavoro come modello Excel 97-2003, che ha il formato XLT, supporta anche le macro.

Quando il modello viene salvato, puoi chiudere Excel in sicurezza.

Esecuzione di una macro in Excel

Prima di svelarvi tutte le possibilità della macro che avete creato, credo sia giusto fare attenzione ad un paio di punti importanti riguardanti le macro in generale:

• Le macro possono essere dannose.
• Leggi di nuovo il punto precedente.

Il codice VBA è molto potente. In particolare, può eseguire operazioni su file esterni al documento corrente. Ad esempio, una macro può eliminare o modificare qualsiasi file nella cartella I miei documenti... Per questo motivo, esegui e consenti solo le macro provenienti da fonti attendibili.

Per eseguire i nostri dati di formattazione macro, apri il file modello che abbiamo creato nella prima parte di questo tutorial. Se disponi di impostazioni di sicurezza standard, quando apri il file, verrà visualizzato un avviso sopra la tabella sopra la tabella che informa che l'avvio delle macro è disabilitato e un pulsante per abilitarne l'esecuzione. Poiché abbiamo realizzato il modello da soli e ci fidiamo di noi stessi, premiamo il pulsante Abilitare il contenuto(Includi contenuto).

Il prossimo passo è importare l'ultimo set di dati aggiornato dal file CSV(abbiamo creato la nostra macro sulla base di tale file).

Quando si importano dati da un file CSV, Excel potrebbe richiedere di configurare alcuni parametri per il corretto trasferimento dei dati alla tabella.

Al termine dell'importazione, vai al menu macro Scheda (Macro) Visualizzazione(Visualizza) e seleziona il comando Visualizza macro(Macro).

Nella finestra di dialogo che si apre, vedremo una riga con il nome della nostra macro FormatoDati... Selezionalo e clicca Correre(Correre).

Quando la macro inizia a funzionare, vedrai come il cursore della tabella salta da una cella all'altra. Dopo alcuni secondi, con i dati verranno eseguite le stesse operazioni della registrazione della macro. Quando tutto è pronto, la tabella dovrebbe avere lo stesso aspetto dell'originale, che abbiamo formattato manualmente, solo con dati diversi nelle celle.

Dai un'occhiata sotto il cofano: come funziona una macro?

Come già accennato più di una volta, una macro è un codice di programma in un linguaggio di programmazione Visual Basic per applicazioni(VBA). Quando attivi la modalità di registrazione macro, Excel registra effettivamente ogni azione che intraprendi sotto forma di istruzioni nella lingua VBA. In modo semplice, Excel scrive il codice del programma per te.

Per vedere questo codice di programma, è necessario nel menu macro Scheda (Macro) Visualizzazione(Visualizza) clic Visualizza macro(Macro) e nella finestra di dialogo che si apre, fare clic su Modificare(Modificare).

Si aprirà una finestra Visual Basic per applicazioni, in cui vedremo il codice del programma della macro che abbiamo registrato. Sì, hai capito bene, qui puoi modificare questo codice e persino creare una nuova macro. Le azioni che abbiamo eseguito con la tabella in questa lezione possono essere registrate utilizzando la registrazione macro automatica in Excel. Ma le macro più complesse, con una sequenza e una logica di azioni finemente sintonizzate, richiedono una programmazione manuale.

Aggiungiamo un altro passaggio al nostro compito ...

Immagina che il nostro file di dati originale data.csv viene creato automaticamente da qualche processo e viene sempre salvato su disco nella stessa posizione. Per esempio, C: \ Dati \ data.csv- percorso del file con i dati aggiornati. Il processo di apertura di questo file e di importazione dei dati da esso può anche essere registrato in una macro:

1. Apri il file modello in cui abbiamo salvato la macro - FormatoDati.
2. Crea una nuova macro denominata Caricare dati.
3. Durante la registrazione di una macro Caricare dati importare dati da file data.csv- come abbiamo fatto nella parte precedente della lezione.
4. Al termine dell'importazione, interrompere la registrazione della macro.
5. Elimina tutti i dati dalle celle.
6. Salva il file come modello con attivazione macro di Excel (estensione XLTM).

Pertanto, eseguendo questo modello, hai accesso a due macro: una carica i dati, l'altra li formatta.

Se vuoi iniziare a programmare, puoi combinare le azioni di queste due macro in una, semplicemente copiando il codice da Caricare dati all'inizio del codice FormatoDati.

Se Microsoft Excel dispone di attività ripetitive, è possibile registrare una macro per automatizzare tali attività. Una macro è un'azione o un insieme di azioni che puoi eseguire quante ne vuoi. Quando si crea una macro, vengono registrati i clic del mouse e le sequenze di tasti. Dopo aver creato una macro, puoi modificarla per apportare piccole modifiche al tuo lavoro.

Diciamo che ogni mese crei un report per il tuo responsabile della contabilità. Vuoi formattare in rosso i nomi utente con account scaduti e applicare anche la formattazione in grassetto. Puoi creare una macro che applica rapidamente queste modifiche di formattazione alle celle selezionate ed eseguirla.

Procedura

Registrazione macro

Ulteriori azioni

Procedura

Informazioni aggiuntive

Puoi sempre porre una domanda alla community tecnica di Excel, chiedere aiuto nella community di Risposte o suggerire una nuova funzionalità o un miglioramento sul sito web.

Le macro sono insostituibili. Una macro può essere definita come una sequenza di comandi (in sequenza), che possono essere clic, sequenze di tasti o anche piccole righe di codice con funzionalità più avanzate. Queste sequenze vengono scritte nel modulo VBA ed eseguite secondo necessità.

Il vantaggio principale di una macro è il tempo che fornisce, perché automatizza ciò che deve essere fatto manualmente in più passaggi. È molto utile quando è necessario eseguire lo stesso insieme di attività più volte.

Una macro può essere creata in due modi diversi:

• Utilizzo del registratore di macro: In questa procedura, Excel salva ogni azione che farà parte di una macro, quindi il registratore di macro converte tali azioni in comandi di Visual Basic, Applications Edition (VBA).
• Usando VBA: Se conosci un po' di programmazione o addirittura conosci VBA, puoi creare tu stesso le tue macro. Per farlo è sufficiente utilizzare l'editor VBA presente nelle ultime versioni di Microsoft Excel.

Come accennato in precedenza, ci sono molte cose che puoi fare con le macro, ma alcune differiscono dalle altre in quanto sono più comunemente utilizzate nel lavoro quotidiano. Dai un'occhiata a 10 macro per aiutarti con i tuoi fogli di calcolo.

1) Mostra messaggio

Basta incollare il comando MsgBox nella tua macro. Ecco come nell'esempio seguente:

MsgBox "testo del messaggio"

Questa riga di codice può essere inserita in qualsiasi macro, il che è molto utile quando è necessario inviare avvisi all'utente del foglio di calcolo in cui è in esecuzione la macro.

2) Eseguire la macro all'apertura del foglio

In alcuni casi, la macro deve essere aperta insieme al foglio di lavoro. In questo caso, basta montare la macro con il parametro Auto_Open(). Controlla l'esempio

SottoAuto_Apri ()
MsgBox "Per tutto ciò che riguarda Excel, vai su www.https: //luz.vc/"
Fine sotto

Nell'esempio sopra, dopo aver aperto il foglio di calcolo, verrà visualizzato un messaggio. Non dimenticare di abilitare le macro nel tuo Excel.

3) Foglio di lavoro con data e ora correnti

Puoi scrivere la data e l'ora correnti su un foglio di lavoro. L'esempio seguente inserisce questi dati nella cella A1, vedi

Sub writeDataEora ()
Intervallo ("A1") = Adesso
Fine sotto

4) Eseguire la stessa azione per ogni cella selezionata.

Ad un certo punto, è necessario eseguire un'azione sulle celle selezionate dall'utente. Per fare ciò, creare una macro come nell'esempio seguente.

Sub a doSpeedCell ()
Per ogni cella in Selection.Cells
Cella MsgBox
Prossimo
Fine sotto

5) Lo stesso per tutte le celle selezionate

Puoi creare la stessa macro in precedenza, ma in un modo leggermente più completo, ovvero l'azione non verrà eseguita passo dopo passo. Nell'esempio seguente, "Hello" viene scritto su tutte le celle.

Sub to doSomethingAllAsCells ()
Selection.Cells.Value = "(! LANG: Ciao"!}
Fine sotto

Puoi fare molti inserimenti di codice come cambiare il colore del testo, la dimensione del carattere, il tipo di carattere e altro.

6) Identificazione delle formule in ogni cella

Sub CheckFormula ()
Se Range ("A1") HasFormula = True allora
MsgBox "C'è una formula"
Ancora
MsgBox "Non è una formula"
Finisci se
Fine sotto

7) Cambia il colore della cella al passaggio del mouse

Puoi anche fornire una maggiore interattività con gli utenti di fogli di calcolo. Idealmente, l'interattività ha lo scopo di facilitare la manutenzione e la visualizzazione delle informazioni. La macro seguente è un esempio di come le tabelle possono visualizzare le celle con la stessa tonalità di colore quando il mouse passa i dati che mettono in correlazione quelle celle.

Foglio di lavoro secondario privato_SelectionChange (ByVal Target As Range)

Dim home line come gamma
Gamma
Dim Line2 a lungo

Cells.Interior.ColorIndex = xlNone

Riga2 = Target.Riga

Imposta inizio riga = intervallo ("A" e riga2, destinazione)

Dipinge la cella selezionata nella colonna 5
Imposta riga = Intervallo (celle (Target.Row, 1), celle (Target.Row, 5))

Con righello
.Interior.ColorIndex = 12
Termina con

8) Cambia colore all'interno e caratteri

Questa macro cambia i colori all'interno e all'origine delle celle in modo che corrispondano alla lettera delle celle.

Sub Colorir_interior_letra ()
Per N = 1 fino all'intervallo ("O65536"). Fine (xlUp) .Riga

Selezionare l'intervallo di scala ("O" e N)
Caso "A"
Intervallo ("O" e N) .Interior.ColorIndex = 3
Intervallo ("O" e N) .Font.ColorIndex = 1

Caso "B"
Intervallo ("O" e N) .Interior.ColorIndex = 4
Intervallo ("O" e N) .Font.ColorIndex = 2

Caso "C"
Intervallo ("O" e N) .Interior.ColorIndex = 5
Intervallo ("O" e N) .Font.ColorIndex = 3

Caso "D"
Intervallo ("O" e N) .Interior.ColorIndex = 7
Intervallo ("O" e N) .Font.ColorIndex = 12

Caso altro
Intervallo ("O" e N) .Interior.ColorIndex = 6
Intervallo ("O" e N) .Font.ColorIndex = 4
Fine selezione

Successivo N

9) Macro parlante

Ho già pensato che sarebbe stato interessante avere un foglio di calcolo che conterrà i dati che contiene, è esattamente ciò che fa questa macro. Nell'esempio seguente, la tabella ha parole che vanno dalla cella A1 ad A5, ma puoi scegliere la spaziatura che funziona meglio per te, quindi cambia semplicemente A1: A5 presentato nel codice sottostante con la spaziatura che preferisci.

Sub ExcelFalling ()

Intervallo ("A1: A5").