Macro in Excel - Istruzioni per l'uso. Mironov

Buona giornata!

Voglio dedicare questo articolo a una sezione così vasta di MS Excel come macro, o meglio, ricominciamo e vediamo come creare una macro in Excel, a cosa serve e come usarla nel tuo lavoro.

Come sai dalla tua esperienza, quando si lavora con una macro c'è molta "routine", cioè vengono eseguite le stesse operazioni e azioni necessarie per ottenere un risultato, può essere la compilazione dello stesso tipo di tabelle o moduli, elaborando dati simili a gemelli settimanali, rapporti mensili, ecc. Ma l'uso delle macro ti consentirà di eseguire queste azioni in modalità automatica, sfruttando al massimo le capacità di Excel, gettando queste operazioni di routine e monotone sulle potenti spalle di Excel. Inoltre, il motivo per l'utilizzo delle macro potrebbe essere quello di aggiungere le funzionalità necessarie che non sono ancora implementate nelle funzioni standard di Excel (ad esempio, output, raccolta dati su un foglio, ecc.).

Se non hai mai sentito parlare di una macro, la sua definizione più accurata sarebbe la seguente, si tratta di azioni programmate in una sequenza specifica e registrate nell'ambiente di programmazione nel linguaggio Visual Basic for Applications (VBA)... Una macro può essere eseguita più volte e questo costringerà Excel a eseguire qualsiasi sequenza di azioni di cui abbiamo bisogno, che semplicemente non ci piace o non vogliamo eseguire manualmente. Nonostante la grande varietà di linguaggi di programmazione per l'intero complesso Microsoft Office, VBA è lo standard e funziona in qualsiasi applicazione della suite per ufficio.

Quindi, creare una macro in Excel è possibile in 2 modi:

Crea una macro in Excelutilizzando un registratore di macro

Innanzitutto, chiariamo cos'è un registratore di macro e cosa ha a che fare una macro con esso.

Registratore di macroÈ un piccolo programma incorporato in Excel che interpreta qualsiasi azione dell'utente nei codici del linguaggio di programmazione VBA e scrive i comandi ottenuti nel processo di lavoro nel modulo del programma. Cioè, se, con il registratore di macro acceso, creiamo il rapporto giornaliero di cui abbiamo bisogno, il registratore di macro registrerà tutto nei suoi comandi passo dopo passo e, di conseguenza, creerà una macro che creerà automaticamente un rapporto giornaliero.

Questo metodo è molto utile per coloro che non hanno le capacità e le conoscenze per lavorare nell'ambiente linguistico VBA. Ma questa facilità di esecuzione e registrazione di una macro ha i suoi svantaggi, oltre che i suoi vantaggi:

• Il registratore di macro può registrare solo ciò che può toccare, il che significa che può registrare azioni solo quando vengono utilizzati pulsanti, icone, comandi di menu e tutto in questo spirito, opzioni che non sono disponibili per esso;
• Nel caso in cui sia stato commesso un errore durante il periodo di registrazione, verrà registrato anche questo. Ma puoi usare il pulsante per annullare l'ultima azione, cancellare l'ultimo comando che hai scritto in modo errato in VBA;
• La registrazione nel registratore di macro viene eseguita solo entro i limiti della finestra di MS Excel e, nel caso in cui chiudi il programma o ne accenda un altro, la registrazione verrà interrotta e si interromperà.

Per abilitare il registratore di macro per la registrazione, procedi come segue:

Il prossimo passo nel lavorare con il registratore di macro sarà l'impostazione dei suoi parametri per un'ulteriore registrazione della macro, questo può essere fatto nella finestra "Registrazione di una macro", dove:

• Campo nome macro- puoi scrivere un nome che comprendi in qualsiasi lingua, ma deve iniziare con una lettera e non contenere segni di punteggiatura e spazi;
• casella di scelta rapida da tastiera- verrà utilizzato da te, in futuro, per avviare rapidamente la tua macro. Nel caso in cui sia necessario registrarne uno nuovo, questa opzione sarà disponibile nel menu "Servizio" - "Macro" - "Macro" - "Esegui" o sulla scheda "Sviluppatore" premendo il pulsante "Macro";
• campo "Salva in ..."- puoi specificare il luogo in cui il testo della macro verrà salvato (ma non inviato), e queste sono 3 opzioni:
  • "Questo libro"- la macro verrà scritta nel modulo della cartella di lavoro corrente e può essere eseguita solo se questa cartella di lavoro di Excel è aperta;
  • "Un nuovo libro"- la macro verrà salvata nel modello sulla base del quale viene creata una nuova cartella di lavoro vuota in Excel, il che significa che la macro sarà disponibile in tutte le cartelle di lavoro che verranno create su questo computer da quel momento;
  • "Macrolibro personale"- è uno speciale libro di macro Excel chiamato "Personale.xls" ed è usato come una speciale libreria di archiviazione di macro. All'avvio, le macro del libro "Personal.xls" vengono caricate in memoria e possono essere eseguite in qualsiasi libro in qualsiasi momento.
• Campo descrizione- qui puoi descrivere cosa e come dovrebbe fare la macro, perché è stata creata e che funzioni ha, questo è un campo puramente informativo, che è chiamato a memoria.

Dopo aver eseguito e registrato la tua macro, dopo aver completato tutte le azioni necessarie, puoi interrompere la registrazione con il comando Interrompi registrazione e la tua macro verrà creata utilizzando il registratore di macro.

Crea una macro in Excelnell'editor di Visual Basic.

In questo metodo, vedremo come creare una macro in Excel tramite l'editor del programma VBA, che, come ho detto sopra, è integrato in qualsiasi versione di Excel.

L'editor del programma VBA viene avviato in modo diverso, a seconda della versione del programma Excel:

• nelle versioni di Excel 2003 e successive, abbiamo bisogno nel menu "Servizio", scegliere oggetto "Macro" e premere "Editor visivo di base";
• nelle versioni di Excel 2007 e successive, abbiamo bisogno nella scheda "Sviluppatore" premi il bottone "Editor visivo di base"... Nel caso in cui non trovi questa scheda, devi attivarla selezionando la voce di menu "File" - "Opzioni" - "Personalizza la barra multifunzione" e nella finestra di dialogo, attiva la scheda "Sviluppatore".

Nella finestra che si apre, puoi contemplare l'interfaccia dell'editor VBA, tutto è scritto in inglese e non ci sono localizzatori, non devi cercare, ma solo accettare e lavorare, soprattutto perché all'inizio è solo incomprensibile, e allora tutto sarà familiare.

Quindi, come posso lavorare nell'editor VBA, dove è memorizzato e come creare una macro in Excel. Domande di questa natura sorgono non appena vedete l'editore e ora le prenderemo in considerazione tutte.

Tutte le nostre macro verranno memorizzate nel cosiddetto moduli software... In qualsiasi libro di qualsiasi versione di Excel, possiamo creare qualsiasi modulo di programma in qualsiasi quantità e inserire in essi tutte le macro che abbiamo creato. Un modulo è in grado di contenere qualsiasi numero di macro necessarie o create. I moduli sono disponibili nella finestra "Esplora progetti" e si trovano nell'angolo in alto a sinistra dell'editor di macro (puoi anche chiamarlo con la scorciatoia da tastiera CTRL + R).

I moduli di programma nell'editor VBA esistono in diverse forme e vengono utilizzati per diverse opzioni e situazioni:

In realtà, la macro stessa quando si lavora in un modulo standard si presenta così:

Consideriamo un esempio di macro funzionante:

• Tutte le macro inizieranno necessariamente con un operatore Sub seguito dal nome della macro e da un elenco di argomenti tra parentesi. Nei casi in cui mancano argomenti, le parentesi devono essere lasciate vuote;
• Senza fallo, tutte le macro finiscono con l'operatore Fine sotto;
• Dati che sono tra operatori Sub e Fine sotto, è il corpo della macro che verrà eseguito quando viene eseguita la macro. Nell'esempio la macro verifica e, in fase di inserimento dei dati, li trova nella lista del database e visualizza il valore specificato dai criteri.

Come puoi vedere, il secondo metodo è più difficile da usare e capire se non hai esperienza nella programmazione in generale o in particolare in VBA. È molto difficile capire e capire quali comandi e come vengono inseriti, quali argomenti utilizza affinché la macro inizi a svolgere il suo lavoro in modalità automatica. Ma la strada sarà dominata da chi cammina, come dicevano gli antichi saggi, e quindi non dovresti arrenderti, ma seguire i comandamenti del nonno di Lenin ...

Creazione di un pulsante per eseguire macro nella barra degli strumenti

Come ho detto prima, puoi chiamare una procedura macro con una combinazione di tasti di scelta rapida, ma è molto noioso ricordare quale combinazione è assegnata a chi, quindi è meglio creare un pulsante per eseguire la macro. Creare pulsanti, eventualmente di diversi tipi, ovvero:

Questo metodo è disponibile per qualsiasi versione di MS Excel e consiste nel fatto che porteremo il pulsante direttamente nel nostro foglio di lavoro come oggetto grafico. Per fare ciò, hai bisogno di:

• In MS Excel 2003 e versioni precedenti vai al menù "Visualizzazione", scegliere "Barra degli strumenti" e premere il pulsante "Forme".
• In MS Excel 2007 e versioni successive hai bisogno nella scheda "Sviluppatore" apri il menu a tendina "Inserire" e seleziona un oggetto "Pulsante".

Dopo tutto questo, devi disegnare un pulsante sul tuo foglio mentre tieni premuto il pulsante sinistro del mouse. Al termine del processo di disegno, si aprirà automaticamente una finestra in cui sarà necessario selezionare la macro che deve essere eseguita quando si fa clic sul pulsante.

Come creare funzioni personalizzate in VBA

In linea di principio, la creazione delle cosiddette funzioni definite dall'utente non è molto diversa dalla creazione di una normale macro in un modulo di programma standard. La differenza tra questi concetti è che la macro eseguirà le azioni in essa inerenti con gli oggetti del libro o del foglio (queste sono formule, celle, ecc.), Ma la funzione personalizzata funziona solo con i valori che riceve da noi e sono gli argomenti dati iniziali per i calcoli.

Ad esempio, per creare una funzione personalizzata per l'imposta sul valore aggiunto, ovvero l'IVA, dobbiamo aprire il nostro editor VBA e aggiungere un nuovo modulo, selezionare dal menu "Inserire" paragrafo "Modulo" e inserisci il testo per la nostra funzione lì: Va notato che la principale differenza tra una funzione e una macro è l'intestazione Funzione sostituito Sub e c'è un elenco pieno di argomenti, nel nostro esempio è Summa... Dopo che il nostro codice è stato inserito, sarà disponibile nella finestra standard della funzione guidata, che si trova nel menu "Formule", paragrafo "Inserisci funzione".
E scegli una categoria "Definito dall'utente" in cui verrà visualizzata la nostra funzione scritta "NDS".
Dopo aver selezionato la nostra funzione, puoi posizionare il cursore su una cella con un argomento, che conterrà l'importo per il quale calcoliamo l'IVA, tutto avviene come con una normale funzione.
E questo è tutto per me! Lo spero davvero un articolo su come creare una macro in Excel è stato comprensibile e utile per te. Sarei molto grato per i commenti lasciati, poiché questo è un indicatore di leggibilità e ispira a scrivere nuovi articoli! Condividi quello che leggi con i tuoi amici e metti mi piace!

Poche persone sanno che la prima versione del popolare prodotto Microsoft Excel è apparsa nel 1985. Da allora, ha subito diverse modifiche ed è richiesto da milioni di utenti in tutto il mondo. Allo stesso tempo, molti lavorano con solo una piccola parte delle capacità di questo processore di fogli di calcolo e non sanno nemmeno come la loro vita potrebbe essere semplificata programmando in Excel.

Cos'è VBA

La programmazione in Excel viene eseguita utilizzando il linguaggio di programmazione Visual Basic for Application, originariamente integrato nel più famoso processore di fogli di calcolo di Microsoft.

Gli esperti attribuiscono ai suoi meriti la relativa facilità di sviluppo. Come dimostra la pratica, anche gli utenti che non hanno competenze di programmazione professionali possono padroneggiare le basi di VBA. Le peculiarità di VBA includono l'esecuzione dello script nell'ambiente delle applicazioni per ufficio.

Lo svantaggio del programma sono i problemi associati alla compatibilità delle diverse versioni. Sono causati dal fatto che il codice del programma VBA fa riferimento a funzionalità presenti nella nuova versione del prodotto, ma non in quella vecchia. Inoltre, gli svantaggi includono l'eccessiva apertura del codice per la modifica da parte di una persona non autorizzata. Tuttavia, Microsoft Office e IBM Lotus Symphony consentono all'utente di crittografare il seme e impostare una password per visualizzarlo.

Oggetti, collezioni, proprietà e metodi

Questi sono i concetti che devono essere compresi da coloro che lavoreranno nell'ambiente VBA. Prima di tutto, devi capire cos'è un oggetto. In Excel, questo è un foglio, una cartella di lavoro, una cella e un intervallo. Questi oggetti hanno una gerarchia speciale, ad es. obbedire l'un l'altro.

Il principale è Application, che corrisponde al programma Excel stesso. Questo è seguito da Cartelle di lavoro, Fogli di lavoro e Intervallo. Ad esempio, per fare riferimento alla cella A1 su un foglio specifico, è necessario specificare un percorso basato sulla gerarchia.

Per quanto riguarda il concetto di "raccolta", si tratta di un gruppo di oggetti della stessa classe, che nel record ha la forma ChartObjects. I suoi singoli elementi sono anche oggetti.

Il concetto successivo sono le proprietà. Sono una caratteristica necessaria di qualsiasi oggetto. Ad esempio, per Intervallo, è Valore o Formula.

I metodi sono comandi che indicano cosa deve essere fatto. Quando si scrive codice in VBA, devono essere separati dall'oggetto da un punto. Ad esempio, come verrà mostrato in seguito, il comando Celle (1,1) .Seleziona viene utilizzato molto spesso durante la programmazione in Excel. Significa che devi selezionare una cella con le coordinate

Selection.ClearContents viene spesso utilizzato con esso. La sua esecuzione significa cancellare il contenuto della cella selezionata.

Come iniziare

Quindi devi andare all'applicazione VB, per la quale è sufficiente utilizzare la combinazione di tasti "Alt" e "F11". Ulteriore:

• nella barra dei menu situata nella parte superiore della finestra, fare clic sull'icona accanto all'icona di Excel;
• selezionare la squadra Mudule;
• salva cliccando sull'icona con l'immagine;
• scrivere, diciamo, uno schizzo del codice.

Sembra così:

Sottoprogramma ()

"Il nostro codice

Si prega di notare che la riga "" Il nostro codice "verrà evidenziata in un colore diverso (verde). Il motivo è nell'apostrofo all'inizio della riga, il che significa che segue un commento.

Ora puoi scrivere qualsiasi codice e creare un nuovo strumento per te stesso in VBA Excel (vedi esempi di programmi di seguito). Naturalmente, coloro che hanno familiarità con le basi di Visual Basic lo troveranno molto più semplice. Tuttavia, anche coloro che non li hanno, se lo desiderano, potranno abituarsi abbastanza rapidamente.

Macro di Excel

Questo nome nasconde i programmi scritti nel linguaggio Visual Basic for Application. Pertanto, la programmazione in Excel riguarda la creazione di macro con il codice corretto. Grazie a questa funzionalità, il processore per fogli di calcolo Microsoft si sviluppa autonomamente, adattandosi alle esigenze di un determinato utente. Una volta che hai capito come creare moduli per scrivere macro, sei pronto per iniziare a guardare esempi specifici di programmi Excel VBA. È meglio iniziare con i codici più rudimentali.

Esempio 1

Compito: scrivere un programma che copierà il valore del contenuto di una cella e poi scriverà in un'altra.

Per questo:

• aprire la scheda "Visualizza";
• vai all'icona Macro;
• cliccare su "Registra Macro";
• compilare il modulo aperto.

Per semplicità, lascia “Macro1” nel campo “Nome macro” e inserisci, ad esempio, hh nel campo “Scorciatoia da tastiera” (questo significa che puoi avviare il programma con il comando blitz “Ctrl + h”). Premere Invio.

Ora, quando la registrazione della macro è già iniziata, i contenuti di una cella vengono copiati in un'altra. Ritorna all'icona originale. Fare clic su "Registra macro". Questa azione significa la fine del programma.

• di nuovo vai alla riga "Macro";
• selezionare "Macro 1" nell'elenco;
• fare clic su "Esegui" (la stessa azione viene avviata avviando la scorciatoia da tastiera "Ctrl + hh").

Di conseguenza, si verifica l'azione eseguita durante la registrazione della macro.

Ha senso vedere come appare il codice. Per fare ciò, torna alla riga "Macro" e fai clic su "Cambia" o "Invio". Di conseguenza, si trovano in un ambiente VBA. In realtà, il codice della macro stessa si trova tra le righe Sub Macro1 () e End Sub.

Se la copia è stata eseguita, ad esempio, dalla cella A1 alla cella C1, una delle righe di codice apparirà come Intervallo ("C1"). Seleziona. Tradotto, sembra "Intervallo (" C1 "). Seleziona", in altre parole, passa a VBA Excel, alla cella C1.

Il comando ActiveSheet.Paste termina la parte attiva del codice. Significa scrivere il contenuto della cella selezionata (in questo caso A1) nella cella selezionata C1.

Esempio 2

I loop VBA ti aiutano a creare varie macro in Excel.

I loop VBA ti aiutano a creare una varietà di macro. Supponiamo di avere una funzione y = x + x 2 + 3x 3 - cos (x). È necessario creare una macro per ottenere il suo grafico. Questo può essere fatto solo usando i loop VBA.

Per il valore iniziale e finale dell'argomento della funzione, prendi x1 = 0 e x2 = 10. Inoltre, è necessario immettere una costante: il valore per il passaggio di modifica dell'argomento e il valore iniziale per il contatore.

Tutte le macro di esempio VBA Excel vengono create utilizzando la stessa procedura presentata sopra. In questo caso particolare, il codice è simile a:

Sottoprogramma ()

scopare = 0.1

Fai mentre x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)

Celle (i, 1) .Value = x1 (il valore di x1 viene scritto nella cella con le coordinate (i, 1))

Celle (i, 2) .Value = y (il valore y viene scritto nella cella con le coordinate (i, 2))

i = i + 1 (il contatore è attivo);

x1 = x1 + shag (l'argomento viene modificato dalla dimensione del passo);

Fine Sub.

Come risultato dell'esecuzione di questa macro in Excel, otteniamo due colonne, la prima delle quali contiene i valori per x e la seconda per y.

Quindi viene tracciato un grafico utilizzandoli in modo standard per Excel.

Esempio 3

Per implementare i loop in VBA Excel 2010, come in altre versioni, insieme alla già data costruzione Do While, viene utilizzato For.

Considera un programma che crea una colonna. Ogni cella conterrà i quadrati del numero della riga corrispondente. L'uso del costrutto For ti consentirà di scriverlo in brevissimo tempo, senza utilizzare un contatore.

Per prima cosa devi creare una macro come descritto sopra. Successivamente, scriviamo il codice stesso. Supponiamo che siamo interessati ai valori per 10 celle. Il codice è simile a questo.

Per i = da 1 a 10 Successivo

Il comando viene tradotto in linguaggio "umano" come "Ripeti da 1 a 10 con incrementi di uno".

Se il compito è ottenere una colonna con quadrati, ad esempio, tutti i numeri dispari nell'intervallo da 1 a 11, allora scriviamo:

Per i = da 1 a 10 passaggio 1 Avanti.

Qui il passo è un passo. In questo caso è uguale a due. Per impostazione predefinita, l'assenza di questa parola nel ciclo significa che il passaggio è uno.

I risultati ottenuti devono essere salvati nelle celle con il numero (i, 1). Quindi, ad ogni avvio del ciclo, con un aumento di i della dimensione del passo, aumenterà automaticamente anche il numero della riga. Pertanto, il codice sarà ottimizzato.

In generale, il codice sarà simile a:

Sottoprogramma ()

Per i = da 1 a 10 Passaggio 1 (puoi semplicemente scrivere Per i = da 1 a 10)

Celle (i, 1) .Value = i ^ 2 (cioè il valore del quadrato i è scritto nella cella (i, 1))

Avanti (in un certo senso, svolge il ruolo di contatore e indica un altro inizio del ciclo)

Fine Sub.

Se tutto è stato eseguito correttamente, inclusa la registrazione e l'esecuzione di una macro (vedere le istruzioni sopra), ogni volta che viene chiamata, si otterrà una colonna della dimensione specificata (in questo caso, composta da 10 celle).

Esempio 4

Nella vita di tutti i giorni, molto spesso è necessario prendere questa o quella decisione, a seconda di alcune condizioni. Non puoi farne a meno in VBA Excel. Esempi di programmi, in cui viene scelto l'ulteriore corso dell'esecuzione dell'algoritmo, e inizialmente non predeterminato, utilizzano molto spesso il costrutto If… Then (per casi complessi) If… Then… END If.

Consideriamo un caso specifico. Supponiamo di dover creare una macro per Excel in modo che la cella con le coordinate (1,1) sia scritta:

1 se l'argomento è positivo;

0 se l'argomento è null;

-1 se l'argomento è negativo.

La creazione di una tale macro per Excel inizia nel modo standard, utilizzando i tasti di scelta rapida Alt e F11. Successivamente, viene scritto il seguente codice:

Sottoprogramma ()

x = Cells (1, 1) .Value (questo comando assegna x il valore del contenuto della cella alle coordinate (1, 1))

Se x> 0 Allora Celle (1, 1) .Value = 1

Se x = 0 Allora Celle (1, 1) .Valore = 0

Se x<0 Then Cells(1, 1).Value = -1

Fine Sub.

Resta da eseguire la macro e ottenere il valore desiderato per l'argomento in Excel.

Funzioni VBA

Come avrai notato, la programmazione nel più famoso processore di fogli di calcolo di Microsoft non è poi così difficile. Soprattutto se impari a usare le funzioni VBA. In totale, questo linguaggio di programmazione, creato appositamente per scrivere applicazioni in Excel e Word, ha circa 160 funzioni. Possono essere divisi in diversi grandi gruppi. Questo:

• Funzioni matematiche. Applicandoli all'argomento, si ottiene il valore del coseno, del logaritmo naturale, della parte intera, ecc.
• Funzioni finanziarie. Grazie alla loro presenza e all'utilizzo della programmazione in Excel si possono ottenere efficaci strumenti di calcolo contabile e finanziario.
• Funzioni di elaborazione di array. Questi includono Array, IsArray; LBound; UBound.
• Funzioni VBA Excel per stringhe. Questo è un gruppo abbastanza numeroso. Include, ad esempio, le funzioni Space per creare una stringa con un numero di spazi pari a un argomento intero o Asc per tradurre i caratteri in codice ANSI. Tutti sono ampiamente utilizzati e consentono di lavorare con le righe in Excel, creando applicazioni che facilitano notevolmente il lavoro con queste tabelle.
• Funzioni di conversione dei tipi di dati. Ad esempio, CVar restituisce il valore dell'argomento Expression convertendolo nel tipo di dati Variant.
• Funzioni per lavorare con le date. Estendono notevolmente quelli standard, ad esempio la funzione WeekdayName restituisce il nome (completo o parziale) del giorno della settimana tramite il suo numero. Il timer è ancora più utile. Ti dà il numero di secondi trascorsi dalla mezzanotte a un punto specifico della giornata.
• Funzioni per convertire un argomento numerico in diversi sistemi numerici. Ad esempio, Oct emette un numero in rappresentazione ottale.
• Funzioni di formattazione. Il più importante di questi è il formato. Restituisce una Variant con un'espressione formattata secondo le istruzioni fornite nella descrizione del formato.
• eccetera.

Lo studio delle proprietà di queste funzioni e della loro applicazione amplierà notevolmente l'ambito di Excel.

Esempio 5

Proviamo a passare alla risoluzione di problemi più complessi. Ad esempio:

Dato un documento cartaceo della relazione del livello effettivo dei costi dell'impresa. Necessario:

• sviluppare la sua parte modello utilizzando il processore di fogli di calcolo Excel;
• creare un programma VBA che richiederà i dati iniziali per compilarlo, eseguire i calcoli necessari e compilarli nelle celle corrispondenti del modello.

Consideriamo una delle soluzioni.

Creazione del modello

Tutte le azioni vengono eseguite su un foglio standard in Excel. Le celle libere sono riservate per inserire i dati sul nome dell'azienda consumatrice, l'importo dei costi, il loro livello e il fatturato. Poiché il numero di società (aziende) per le quali viene redatto il rapporto non è fisso, le celle per l'inserimento dei valori in base ai risultati e il nome completo dello specialista non sono riservate in anticipo. Al foglio di lavoro viene assegnato un nuovo nome. Ad esempio, "Օ rapporti".

Variabili

Per scrivere un programma per il riempimento automatico di un modello, è necessario selezionare le designazioni. Saranno utilizzati per le variabili:

• NN - numero della riga della tabella corrente;
• TP e TF - fatturato pianificato ed effettivo;
• SF e SP - costi effettivi e pianificati;
• IP e IF: il livello dei costi pianificato ed effettivo.

Indichiamo con le stesse lettere, ma con il "prefisso" Itog, l'accumulo del totale per questa colonna. Ad esempio, ItogTP - fa riferimento a una colonna della tabella intitolata "Fatturato previsto".

Risolvere un problema utilizzando la programmazione VBA

Utilizzando le designazioni introdotte, otteniamo formule per le deviazioni. Se vuoi calcolare in%, abbiamo (F - P) / P * 100 e in totale - (F - P).

I risultati di questi calcoli possono essere inseriti al meglio direttamente nelle celle appropriate nel foglio di calcolo Excel.

Per i totali di fatto e di previsione, si ottengono utilizzando le formule ItogP = ItogP + P e ItogF = ItogF + F.

Per le deviazioni, utilizzare = (ItogF - ItogP) / ItogP * 100, se il calcolo viene effettuato in percentuale e nel caso del valore totale - (ItogF - ItogP).

I risultati, ancora una volta, vengono scritti immediatamente nelle celle appropriate, quindi non è necessario assegnarli alle variabili.

Prima di avviare il programma creato, è necessario salvare la cartella di lavoro, ad esempio con il nome "Report1.xls".

Il pulsante "Crea tabella dei rapporti" deve essere premuto solo 1 volta dopo aver inserito le informazioni di intestazione. Dovresti conoscere anche altre regole. In particolare, il pulsante "Aggiungi Riga" deve essere premuto ogni volta dopo aver inserito nella tabella i valori per ogni tipo di attività. Dopo aver inserito tutti i dati, è necessario fare clic sul pulsante "Fine" e quindi passare alla finestra "Excel".

Ora sai come risolvere le attività di Excel utilizzando le macro. La capacità di utilizzare vba excel (vedi esempi di programmi sopra) potrebbe anche essere necessaria per lavorare nell'ambiente dell'editor di testo attualmente più popolare "Word". In particolare, puoi creare pulsanti di menu scrivendo, come mostrato all'inizio dell'articolo, oppure scrivendo codice, grazie al quale molte operazioni sul testo possono essere eseguite premendo i pulsanti di turno o tramite la scheda Visualizza e il Icona di macro.

Il manuale contiene macro sui seguenti argomenti:
Esecuzione di una macro con la ricerca di una cella.
Eseguire una macro all'apertura di una cartella di lavoro.
Esecuzione di una macro quando viene digitata nella cella "2".
Esecuzione di una macro quando si preme "Invio".
Aggiungi la tua scheda "Componenti aggiuntivi" (formato cella) al pannello.
Lavorare con i file (cioè scambiare dati con TXT, RTF, XLS, ecc.).
Verifica dell'esistenza di un file nel percorso specificato.
Cerca il file desiderato.
Automazione della cancellazione dei file.
Testo libero sulla barra di stato.
Ripristino della barra di stato.
Riga di scorrimento nella barra di stato.

Cambia rapidamente il titolo della finestra.
Modifica del titolo della finestra (nascondendo il nome del file).
Ritorna all'intestazione originale.
Cosa è aperto in questo momento.
Lavorare con file di testo.
Scrivere e leggere un file di testo.
Elaborazione di più file di testo.
Determinazione della fine di una riga di un file di testo.
Copia da un file di testo in Excel.

Copia il contenuto in un file di testo.
Esporta i dati in txt.
Esportazione dati in html.
Importazione di dati che richiedono più di 256 colonne.
Crea copie di backup di file importanti.
Conteggio del numero di aperture di file.
Emissione del percorso del file nella cella attiva.
Copiare il contenuto del file RTF in Excel.
Copia dei dati da una cartella di lavoro chiusa.
Estrazione di dati da un file chiuso.
Cerca una parola nei file.
Crea un file di testo e inserisci il testo nel file.
Crea un file di testo e inserisci il testo (rilevamento della fine del file).
Crea documenti Word da un foglio di calcolo Excel.
Comandi per creare ed eliminare directory.
Ottenere la directory corrente.
Cambio rubrica.

Visualizza tutti i file nella directory.
Visualizza tutti i file nella directory.
Area di lavoro di Microsoft Excel.
Cartella di lavoro.
Il numero di nomi di cartelle di lavoro.
Protezione della cartella di lavoro.
Vietare la stampa di un libro.
Apertura di un libro (o file di testo).
Apertura di un libro e aggiunta di testo alla cella A1.
Quanti libri sono aperti.
Chiusura di tutti i libri.
Chiusura di una cartella di lavoro solo quando viene soddisfatta una condizione.
Salva la cartella di lavoro con un nome che rappresenta la data corrente.
Se la cartella di lavoro è stata salvata.
Crea un libro con un foglio.
Crea un libro.
Rimozione di nomi non necessari.
Riproduzione rapida di una cartella di lavoro.
Ordina fogli.
Trovare il valore massimo su tutti i fogli del libro.
Foglio di lavoro.
Verifica se il foglio di lavoro è protetto.
Elenco dei fogli ordinati.
Crea un nuovo foglio.
Crea un nuovo foglio.
Rimozione dei fogli a seconda della data.
Copiare un foglio in un libro.
Copia di un foglio in una nuova cartella di lavoro (creata).
Sposta un foglio in un libro.
Sposta più fogli in una nuova cartella di lavoro.
Sostituisci il file esistente.
"Ribaltando" il libro.
Inserisci un'intestazione e un piè di pagina con il nome del libro, il foglio e la data corrente.
Il foglio esiste.
Il foglio esiste.
Visualizza il numero di fogli nella cartella di lavoro attiva.
Visualizza il numero di fogli nella cartella di lavoro attiva come collegamenti ipertestuali.
Visualizza i nomi dei fogli attivi uno per uno.
Visualizza il nome e i numeri di foglio del libro corrente.
Rendi il foglio invisibile.
Quante pagine ci sono su tutti i fogli?
Cella e intervallo (colonne e righe).
Copia le righe su un altro foglio.
Copia le colonne su un altro foglio.
Conta il numero di celle che contengono i valori specificati.
Conta il numero di celle in un intervallo che contengono valori specificati.
Conta il numero di celle visibili in un intervallo.
Determina il numero di celle in un intervallo e la somma dei loro valori.
Contare il numero di celle.
Ricalcolo automatico dei dati della tabella quando si modificano i suoi valori.
Inserimento dati nelle celle.
Inserimento di dati tramite formule.
Inserimento dati seriale.
Inserimento di dati testuali nelle celle.
Output alle celle del titolo del libro, foglio e numero di fogli.
Rimozione di righe vuote.
Rimozione di righe vuote.
Rimozione di righe vuote.
Eliminazione condizionale di una riga.
Rimozione delle linee nascoste.
Rimuovi le linee nascoste usate o le linee con altezza zero.
Rimozione dei duplicati tramite maschera.

Seleziona un intervallo sopra la cella corrente.
Seleziona una cella e inserisci un numero lì.
Evidenziare i valori negativi.
Allocazione di un intervallo e utilizzo di indirizzi assoluti.

Selezione di celle per spaziatura.
Selezione di più intervalli.
Movimento attraverso le cellule.
Trova la cella vuota più vicina in una colonna.
Trovare il valore massimo.
Cerca e sostituisci per motivo.
Cerca un valore e visualizza il risultato in una finestra separata.

Cerca con l'evidenziazione dei dati trovati.
Ricerca per condizione nell'intervallo.
Trova l'ultima cella non vuota in un intervallo.
Trova l'ultima cella non vuota in una colonna.
Trova l'ultima cella non vuota in una stringa.
Trova una cella blu in un intervallo.
Trova un valore negativo in un intervallo ed evidenzia in blu.
Trova l'esistenza di un valore in una colonna.
Cerca le corrispondenze in un intervallo.
Cerca una cella in un intervallo.
Cerca una cella in un intervallo.
Trovare un valore approssimativo in un intervallo.
Trova l'inizio e la fine di un intervallo che contiene dati.
Cerca l'inizio dei dati.
Sostituzione automatica dei valori.
Riempimento rapido di un intervallo (array).
Riempimento a intervalli (array).
Riempiendo l'intervallo specificato (array).
Riempimento dell'intervallo (array).
Calcolo della somma dei primi valori dell'intervallo.
Posizionamento nella cella di un orologio elettronico.
"Allarme".
Decorazione dei limiti superiore e inferiore della gamma.
Indirizzo di cella attivo.
Coordinate della cella attiva.
Formula di cella attiva.
Ottieni una formula da una cella.
Tipo di dati della cella.
Visualizza l'indirizzo della fine dell'intervallo.
Ottenere informazioni sull'intervallo selezionato.
Prendi una parola con 13 caratteri in una cella.
Creazione di un elenco modificabile (tabella).
Verifica del valore vuoto.
Intersezione di cellule.
Moltiplica l'intervallo selezionato per.
Moltiplicazione simultanea di tutti i dati nell'intervallo.
Dividendo l'intervallo per.
Quadra ogni cella nell'intervallo.
Riepiloga i dati delle sole celle visibili.
La somma delle celle con valori numerici.
Durante la somma, il cursore si trova all'interno dell'intervallo.

Maturazione degli interessi a seconda dell'importo.
Maturazione degli interessi a seconda dell'importo.
Un esempio riassuntivo del calcolo della commissione.
Movimento di gamma.
Offset dalla cella selezionata.
Passa attraverso le celle lungo la colonna.
Crea un riempimento per un intervallo.
Selezione del parametro della cella.
Divisione di un intervallo.
Combinazione di dati di intervallo.
Combinazione di dati di intervallo.
Scopri la colonna o la riga massima.
Limitazione dei possibili valori dell'intervallo.
Testare la velocità di lettura e scrittura degli intervalli.
Apri MsgBox quando selezioni una cella.
Nascondere una riga.
Nascondere più righe.
Nascondere una colonna.
Nascondere più colonne.
Nascondi una riga in base al nome della cella.
Nascondere più righe per indirizzi di cella.
Nascondi una colonna in base al nome della cella.
Nascondere più colonne per indirizzi di cella.
Cella lampeggiante.
Lavorare con le note.
Visualizza tutte le note nel foglio di lavoro.
Funzione di estrazione dei commenti.
Elenco delle note dei fogli protetti.

Elenco delle note in un elenco separato.
Elenco delle note in un elenco separato.

Contando il numero di note.
Note di conteggio.
Seleziona le celle con le note.
Visualizza tutte le note.
Cambia il colore delle note.
Aggiunta di note.
Aggiunta di note all'intervallo in modo condizionale.
Trasferimento di un commento da e verso una cella.

Trasferimento di valori da una cella a un commento.
Schede personalizzate sulla barra multifunzione.
Aggiunta della barra degli strumenti.
Aggiunta di un pulsante alla barra degli strumenti.
Pannello con un pulsante.
Pannello con due pulsanti.
Crea un pannello a destra.

Creazione di un menu personalizzato (opzione 1).
Creazione di un menu personalizzato (opzione 2).
Creazione di un menu personalizzato (opzione 3).
Creazione di un menu personalizzato (opzione 4).
Creazione di un menu personalizzato (opzione 5).
Creazione di un menu personalizzato (opzione 6).
Creazione di un elenco di elementi nel menu principale Excel.
Creazione di un elenco di voci del menu contestuale.
Visualizza la barra degli strumenti in una condizione specifica.
Nascondere e mostrare le barre degli strumenti.
Crea un tooltip per i miei pulsanti.
Crea un menu basato sui dati di un foglio di lavoro.
Creazione di un menu contestuale.
Blocco del menu di scelta rapida.
Aggiunta di un comando al menu Strumenti.
Aggiunta di un comando al menu Visualizza.
Creazione di un pannello con un elenco.
Cartone animato con un assistente nel ruolo principale.
Integrando l'helper con testo, titolo, pulsante e icona.
Nuove opzioni di assistente.
Utilizzo dell'assistente per selezionare un colore di riempimento.
FINESTRE DI DIALOGO.
Funzione INPUTBOX (tramite input di valore).
Anteprima chiamata.
Configurazione dell'immissione dei dati in una finestra di dialogo.

Apre la finestra di dialogo (Apri file).
Apre la finestra di dialogo (Stampa).
Altre finestre di dialogo.
Chiamare il browser da Excel.
Finestra di dialogo per l'immissione dei dati.
Finestra di dialogo Impostazioni carattere.
Valori standard.
Formattazione del testo. Tabelle. BORDI E RIEMPIMENTO.
Visualizza un elenco di caratteri disponibili.
Selezione di tutti i numeri dal testo.
Lettera maiuscola solo all'inizio del testo.
Conteggio del numero di ripetizioni del testo di ricerca.
Seleziona un elemento arbitrario dal testo.
Visualizza il testo in primo piano.
Testo inglese - in lettere maiuscole.
Avvio di una tabella dei simboli da Excel.
informazioni su utente, computer, stampante, ecc.
Ottieni nome utente.
Risoluzione di visualizzazione del monitor.
Ottieni informazioni sulla stampante che stai utilizzando.
Visualizza le informazioni sui dischi sul tuo computer.
USERFORM.
SCHEMI.
Costruire un diagramma usando una macro.
Salvare il diagramma in un file separato.
Creazione ed eliminazione di un diagramma premendo un pulsante.
Visualizza un elenco di diagrammi in una finestra separata.
Applicazione di una tavolozza di colori casuale.
L'effetto della trasparenza del grafico.
Crea un grafico basato sui dati di più fogli di lavoro.
Crea etichette per i dati del grafico.
DIVERSI PROGRAMMI.
Un programma per comporre cruciverba.
Crea la copertina del DVD.
Gioco del campo minato.
Gioco "Indovina l'animale".
Calcolo basato su celle di un certo colore.
ALTRE FUNZIONI E MACRO.
Richiamo dei tasti funzione.
Calcolo della media aritmetica.
Conversione di numeri in denaro.
Cerca il lunedì più vicino.
Contando il numero di anni interi.
Calcolo della media ponderata.
Converti il ​​numero del mese nel suo nome.
Utilizzo di link relativi.
Converti una tabella Excel in formato HTML.
Generatore di numeri casuali.
I numeri casuali si basano su un intervallo.
Applicare una funzione senza inserirla in una cella.
Conteggio di oggetti nominati.
Attiva il filtro automatico utilizzando una macro.
Creazione della linea strisciante.
Creazione di un'immagine in esecuzione.
Autoforme rotanti.
Richiamo della tavola dei colori.
Creazione di una calcolatrice.
Declinazione di cognome, nome e patronimico.
DATA E ORA.
Visualizzazione di data e ora.
Visualizzazione di data e ora.
Ottenere la data di sistema.
Estrarre data e ora.
Funzione DateFull La versione di MS Office utilizzata non è specificata.

Macro per evidenziare la cella A1 su ogni foglio della cartella di lavoro attiva. Questo sposta anche lo schermo.

Sub A1SelectionEachSheet () Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets (i) .Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range ("a1"). Select Next Sheets (1) .Seleziona Application.ScreenUpdating = True End Sub

Macro per copiare il foglio corrente un numero specificato di volte. Utile per testare alcune macro: modifiche apportate, verifica su una copia dei dati. Copie esaurite: esegui di nuovo la macro

Sub SimpleCopy () Dim i As Integer, j As Integer i = Application.InputBox Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after: = Sheets (Sheets.Count) ActiveSheet .Name = "Copy" & j Next j Application.ScreenUpdating = True End Sub

Crea fogli con titoli da un intervallo specificato su un foglio

Sub CreateFromList () Dim cell As Range For Every cell In Selection Sheets.Add after: = Sheets (Sheets.Count) ActiveSheet.Name = cell.Value Next cell End Sub

Markros di invio di posta ritardato. Macro rivista dal libro di John Walkenbach Programmazione VBA professionale

Sub Email () Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject ("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Set OutMail = OutApp.CreateItem (0) On Error Riprendi Avanti con OutMail .To = " [e-mail protetta]".Subject =" Report vendite ".Attachments.Add" C: \ Test.txt ".Body =" Corpo email ".DeferredDeliveryTime = Replace (Date,". "," / ") &" 11:00:00 " .send" .Display per generare un messaggio e aprirlo End With On Error GoTo 0 Set OutMail = Niente pulizia: Set OutApp = Niente End Sub

Macro del sommario leggermente modificata di Nikolai Pavlov.
Se il foglio "Sommario" esiste già nel libro, la macro suggerisce di eliminarlo. In caso contrario, crea un foglio "Sommario" e inserisce collegamenti con i nomi dei fogli

Sub TableOfContent () Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False With ActiveWorkbook Per ogni foglio di lavoro in ActiveWorkbook.Worksheets If Worksheet.Name = "Sommario" Then Answer = MsgBox ("La cartella di lavoro ha un foglio denominato Table of Contents. Eliminarlo? ", VbYesNo) If Answer = vbNo Then Exit Sub If Answer = vbYes Then Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets (Array (1 )).Seleziona Fogli.Aggiungi fogli (1) .Nome = "Sommario" With ActiveWorkbook Per ogni foglio In ActiveWorkbook.Worksheets If sheet.Name<>"Sommario" Quindi imposta cella = Fogli di lavoro (1) .Celle (foglio.Indice, 1) .Fogli di lavoro (1) .Collegamenti ipertestuali.Aggiungi ancora: = cella, Indirizzo: = "", Sottoindirizzo: = "" "& foglio .Name & "" "&"! A1 "cell.Formula = sheet.Name End If Next sheet End With Rows (" 1: 1 "). Elimina Application.ScreenUpdating = True End Sub

Ordina i fogli dalle procedure guidate VBA. La macro ordina anche i fogli nascosti. Non funzionerà se il libro ha una struttura protetta

Sub SORT_ALL_LISTS () Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject ("Scripting.Dictionary") "ricorda lo stato di visibilità di ogni foglio e fallo tutto visibile per ogni iSht in ActiveWorkbook.Sheets oDict.Item (iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook "ordina i fogli visibili per i = 1 in .Sheets.Count - 1 per j = i + 1 In .Sheets.Count If UCase (.Sheets (i) .Name)> UCase (.Sheets (j) .Name) Then .Sheets (j) .Sposta prima: =. Sheets (i) Next j Next i End With " ripristina lo stato di visibilità originale di ciascuno dei fogli Per ogni iSht in ActiveWorkbook.Sheets iSht.Visible = oDict.Item (iSht.Name) Next Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Importazione delle colonne "Field1" e "Field2" dal foglio "Sheet1" del file Excel "C:\Manager.xls" tramite connessione ADODB e incollaggio del contenuto a partire dalla cella A1 del foglio corrente

Per automatizzare le attività ripetitive, è possibile registrare una macro utilizzando Macro Recorder in Microsoft Excel. Immagina di avere date in formati casuali e di voler applicare un formato a tutti. Questo può essere fatto usando una macro. È possibile registrare una macro utilizzando il formato desiderato e riprodurre la macro se necessario.

Quando registri una macro, tutto ciò che fai viene registrato come codice Visual Basic, Applications Edition (VBA). Queste azioni possono includere l'immissione di testo o numeri, la selezione di celle o comandi sulla barra multifunzione o menu, la formattazione di celle, righe o colonne e persino l'importazione di dati da una fonte esterna come Microsoft Access. Visual Basic for Applications (VBA) fa parte del linguaggio di programmazione Visual Basic. È disponibile nella maggior parte delle applicazioni di Office. Sebbene VBA ti permetta di automatizzare i processi all'interno e tra le applicazioni di Office, non è necessario essere esperti nella programmazione o conoscere il linguaggio VBA perché il registratore di macro si occuperà di tutto ciò che devi fare.

È importante sapere che quando registri una macro, quasi tutto ciò che fai viene registrato. Pertanto, se si commette un errore, ad esempio facendo clic sul pulsante sbagliato, il registratore di macro registrerà tale azione. In tal caso, è possibile riscrivere l'intera sequenza o modificare il codice VBA. Pertanto, prima di registrare il processo, dovresti risolverlo bene. Più accuratamente si registra la sequenza, più efficiente sarà il funzionamento della macro.

Sviluppatore che è nascosto per impostazione predefinita, quindi devi prima abilitarlo. Per ulteriori informazioni, vedere l'articolo Visualizzazione della scheda Sviluppatore.

Registrazione macro

Nella scheda Sviluppatore clic macro per visualizzare le macro associate alla cartella di lavoro. In alternativa è possibile premere i tasti ALT + F8... Si aprirà una finestra di dialogo macro.

Attenzione:

Utilizzo del codice registrato in Visual Basic Editor (VBE)

Con Visual Basic Editor (VBE), è possibile aggiungere al codice registrato le proprie variabili, strutture di controllo e altri elementi non supportati dal registratore di macro. Poiché il registratore di macro acquisisce quasi tutti i passaggi che si verificano durante la registrazione, potrebbe essere necessario rimuovere anche il codice non necessario. Rivedere il codice registrato è un ottimo modo per imparare a programmare in VBA o affinare le tue abilità.

Per un esempio di modifica del codice registrato, vedere Introduzione a VBA in Excel.

Registrazione macro

È utile sapere quanto segue prima di registrare le macro:

Una macro scritta per funzionare con un intervallo di Excel verrà eseguita solo su celle in tale intervallo. Pertanto, se si aggiunge una nuova riga all'intervallo, la macro non verrà applicata.

Se è necessario registrare una lunga sequenza di attività, si consiglia di utilizzare invece diverse macro più piccole.

La macro può contenere anche attività non correlate a Excel. Il processo macro può estendersi ad altre applicazioni di Office e altri programmi che supportano Visual Basic, Applications Edition (VBA). Ad esempio, puoi registrare una macro che prima aggiorna una tabella in Excel e poi apre Outlook per inviarla via e-mail.

Le macro e gli strumenti VBA si trovano nella scheda Sviluppatore che è nascosto per impostazione predefinita, quindi devi prima abilitarlo.

Si prega di selezionare Eccellere > Parametri> Nastro e pannello.

Per registrare una macro, seguire le istruzioni di seguito.

Lavorare con le macro registrate in Excel

Nella scheda Sviluppatore clic macro per visualizzare le macro associate alla cartella di lavoro. Si aprirà una finestra di dialogo macro.

Nota: Le macro non possono essere annullate. Prima di eseguire la macro registrata per la prima volta, salvare o creare una copia della cartella di lavoro per evitare modifiche indesiderate. Se non sei soddisfatto dei risultati dell'esecuzione della macro, puoi chiudere la cartella di lavoro senza salvarla.

Vedere di seguito per ulteriori informazioni sull'utilizzo delle macro in Excel.

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.