Formattazione condizionale (5)
Elenchi e intervalli (5)
Macro (procedure VBA) (63)
Varie (39)
Bug e glitch di Excel (3)

Come velocizzare e ottimizzare il codice VBA

Prima o poi, chi scrive in Visual Basic for Applications ha un problema, anche se il codice semplifica la vita e fa tutto automaticamente, ma per molto tempo. In questo articolo, ho deciso di raccogliere alcuni semplici consigli che aiuteranno ad accelerare il lavoro del codice VBA, mentre in alcuni casi è molto impressionante: dozzine o anche più volte. L'obiettivo principale dell'articolo è sui principianti, quindi i metodi di ottimizzazione più semplici sono presentati all'inizio dell'articolo. Alla fine dell'articolo vengono fornite soluzioni più "profonde" per l'ottimizzazione del codice. per applicare queste soluzioni, è necessaria una sufficiente esperienza in VB e fin dall'inizio tali metodi di ottimizzazione potrebbero essere incomprensibili a qualcuno.

1. Se il tuo codice contiene molti elementi Activate e Select, specialmente in loop, dovresti eliminarli immediatamente. Ho scritto come farlo nell'articolo: Seleziona e attiva: perché sono necessari e sono necessari?
2. Assicurati di disabilitare per la durata del codice:
   • ricalcolo automatico delle formule. In modo che le formule non vengano ricalcolate con ogni manipolazione sul foglio durante l'esecuzione del codice, questo può rallentare notevolmente il codice se ci sono molte formule:

     Application.ScreenUpdating = False

   • Disabilita il monitoraggio degli eventi per ogni evenienza. È necessario che Excel non esegua alcuna procedura di evento che potrebbe trovarsi nel foglio in cui vengono apportate le modifiche. Di norma, questi sono eventi di modifica delle celle, attivazione di fogli, ecc.:

     ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

     se la stampa avviene all'interno del codice, allora è consigliabile inserire questa riga subito dopo la riga che stampa il foglio (a patto che la stampa non avvenga in loop. In questo caso, dopo la fine del ciclo di stampa).

   • Per ogni evenienza, puoi disattivare la visualizzazione delle informazioni nella barra di stato di Excel (in quali casi le informazioni vengono visualizzate lì e perché puoi scoprirlo nell'articolo: Visualizza il processo di esecuzione). Sebbene questo non consumi molte risorse, a volte può comunque velocizzare il lavoro del codice:

     Application.StatusBar = False

     Application.StatusBar = False

   La cosa principale da ricordare è tutte queste proprietà devono essere riattivate dopo aver eseguito il codice... In caso contrario, potrebbero esserci problemi con il lavoro all'interno di Excel. Ad esempio, se dimentichi di abilitare il ricalcolo automatico delle formule - la maggior parte delle formule verrà ricalcolata esclusivamente con un metodo forzato - Cambio+F9... E se dimentichi di disattivare l'aggiornamento dello schermo, c'è la possibilità di impedirti di lavorare su fogli e libri. Anche se, per impostazione predefinita, la proprietà ScreenUpdating dovrebbe tornare a True, se è stata disabilitata all'interno della procedura, è meglio non sperare in questo e abituarsi a riportare forzatamente tutte le proprietà al loro posto. In sostanza, tutto si riduce a poche righe:

   "Restituzione dell'aggiornamento dello schermo Application.ScreenUpdating = True" Restituzione del ricalcolo automatico delle formule Application.Calculation = xlCalculationAutomatic "Attivazione del rilevamento eventi Application.EnableEvents = True

   Come appare questo codice in pratica. Supponiamo di voler scrivere i valori in un ciclo di 10.000 righe:

   Sub TestOptimize () "Disabilita l'aggiornamento dello schermo Application.ScreenUpdating = False" Disabilita il ricalcolo automatico delle formule Application.Calculation = xlCalculationManual "Disabilita il monitoraggio degli eventi Application.EnableEvents = False" Disabilita l'impaginazione ActiveWorkbook.ActiveSheet.DisplayPageBreaks code = False lr As Long For lr = da 1 a 10000 celle (lr, 1) .Value = lr "ad esempio, numera le righe Next" Restituisce l'aggiornamento dello schermo Application.ScreenUpdating = True "Restituisce il ricalcolo automatico delle formule Application.Calculation = xlCalculationAutomatic" Attiva il monitoraggio di Eventi dell'applicazione EnableEvents = True End Sub

   Gli strappi nelle pagine stampate non devono essere restituiti: in ogni caso rallentano il lavoro.

3. I cicli come Do While per trovare l'ultima cella dovrebbero essere evitati. I principianti spesso commettono questo errore. È molto più efficiente e veloce calcolare l'ultima cella nell'intero foglio di lavoro o in una colonna specifica senza questo ciclo Do While di frenatura. io di solito uso

   lLastRow = Cells (Rows.Count, 1) .End (xlUp) .Row

   lLastRow = Cells (Rows.Count, 1) .End (xlUp) .Row

   Ho descritto altre opzioni per determinare l'ultima cella in dettaglio nell'articolo:

Per gli utenti VBA più esperti, fornirò diverse soluzioni per l'ottimizzazione dei codici in varie situazioni:

• La migliore ottimizzazione del codice, se devi lavorare direttamente con le celle del foglio, elaborarle e, eventualmente, modificarne i valori, allora è più veloce fare tutte le elaborazioni in un array e scaricarle sul foglio in una volta . Ad esempio, il codice sopra per riempire le celle con i numeri sarà simile a questo in questo caso:

  Sub TestOptimize_Array () "Direttamente il codice per riempire le celle Dim arr, lr As Long "memorizziamo in un array in un colpo solo tutti i valori di 10.000 righe della prima colonna arr = Celle (1, 1) .Ridimensiona (10000) .Valore "se hai bisogno di riempire due o più colonne "arr = Celle (1, 1) .Ridimensiona (10000, 2) .Valore"o "arr = Intervallo (Celle (1, 1), Celle (10000, 2)). Valore "o calcola automaticamente l'ultima cella e inserisci i dati nell'array, a partire dalla cella A3 "llastr = Cells (Rows.Count, 1) .End (xlUp) .Row" l'ultima cella della colonna A "arr = Intervallo (Celle (3, 1), Celle (llastr, 2)). Valore Per lr = 1 fino a 10000 arr (lr, 1) = lr "riempi l'array con numeri ordinali Prossimo "Scarichiamo la matrice elaborata sul foglio nelle stesse celle Celle (1, 1) .Ridimensiona (10000) .Valore = arr End Sub

  Sub TestOptimize_Array() "Direttamente il codice per riempire le celle Dim arr, lr As Long" memorizziamo in un array in un colpo solo tutti i valori di 10000 righe della prima colonna arr = Cells (1, 1) .Resize (10000) .Value "(! LANG: se hai bisogno di riempire due o più colonne"arr = Cells(1, 1).Resize(10000, 2).Value "o"arr = Range(Cells(1, 1),Cells(10000, 2)).Value "o calcola automaticamente l'ultima cella e inserisci i dati nell'array, a partire dalla cella A3"llastr = Cells(Rows.Count, 1).End(xlUp).Row "последняя ячейка столбца А "arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr "заполняем массив порядковыми номерами Next "Выгружаем обработанный массив обратно на лист в те же ячейки Cells(1, 1).Resize(10000).Value = arr End Sub!}

  Ma qui dovresti anche prendere in considerazione il fatto che grandi array possono semplicemente causare un overflow di memoria. Ciò è più rilevante per i sistemi a 32 bit, in cui viene allocata meno memoria su VBA ed Excel rispetto ai sistemi a 64 bit.

• Se stai usando IF veloce - IIF, sostituiscilo con IF ... Allora ... Else
• È anche meglio usare lo stesso IF ... Quindi ... Else invece di Switch () e Shoose ()
• Nella maggior parte dei casi, è meglio controllare una stringa per "non vuoto" usando Len () rispetto al confronto diretto con il vuoto: Len (s) = 0 invece di s = "". A causa del fatto che lavorare con le stringhe è molto più lento rispetto ai dati numerici
• Non utilizzare la concatenazione di stringhe inutilmente. Ad esempio, s = "AB" sarà più veloce di: s = "A" & "B"
• Non confrontare direttamente i valori di testo. Meglio usare la funzione StrComp integrata:
  Se s s1 Allora sarà più lento di
  Se StrComp (s, s1, vbBinaryCompare) = 0
  e ancora di più se il confronto deve essere senza distinzione tra maiuscole e minuscole:
  Se LCase (s) LCase (s1) Allora sarà più lento di
  Se StrComp (s, s1, vbTextCompare) = 0
• For... I loop successivi sono generalmente più veloci di Do... Loops
• Evitare di assegnare a variabili di tipo Variant. Anche se la tentazione è grande, questo tipo occupa molta memoria e rallenta ulteriormente il codice. Inoltre, per le variabili oggetto, dovresti evitare, se possibile, il tipo globale impersonale Object e utilizzare un tipo specifico:

  Dim rRange come intervallo, wsSh come foglio di lavoro

• Se lavori con gli array, puoi specificarlo esplicitamente quando dichiari:

  Dim arr () come stringa, arr2 () come lungo

  ma questo è solo se c'è la certezza che i tipi di dati rigorosamente specificati verranno inseriti nell'array

Naturalmente, questi non sono tutti trucchi e soluzioni per l'ottimizzazione. Ma nelle prime coppie dovrebbe bastare. Inoltre, dovresti sempre usare il buon senso. Ad esempio, se il codice viene eseguito in 2 secondi, probabilmente non ha senso ottimizzarlo ulteriormente. Naturalmente, se questo codice non è uno di quelli che cambiano solo il valore di una o due celle.

L'articolo è stato d'aiuto? Condividi il link con i tuoi amici! Video lezioni

("Barra inferiore" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectslidelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss " :" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: assoluto; top: 0px; left: 0px; width: 100%; height: 100% ; background -colore: # 333333; opacità: 0,6; filtro: a lpha (opacità = 60); "," titlecss ":" display: block; posizione: relativa; carattere: grassetto 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; posizione: relativa; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; colore: #fff; margin-top: 8px; "," buttoncss ":" display: block; posizione: relativa; margin-top: 8px; "," texteffectresponsive": true," texteffectresponsivesize": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: nessuno! importante; "," buttoncssresponsive": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

Contiene 32.767 caratteri. Pertanto, la funzione di modificare rapidamente la larghezza di una colonna in base al suo contenuto non è sempre conveniente. :) Anche se nella maggior parte dei casi aiuta. Spostare il cursore del mouse sul bordo destro dell'intestazione della colonna e fare doppio clic su di essa in modo che il programma selezioni la larghezza ottimale della colonna. Questo trucco funziona per più colonne e per l'intero foglio.

2. Come selezionare rapidamente un'intera riga o colonna

Quando si lavora con grandi quantità di dati, è impossibile fare a meno delle scorciatoie da tastiera, con l'aiuto delle quali è facile spostarsi nel foglio e selezionare singole colonne o righe. Combinazioni Ctrl + barra spaziatrice e Maiusc + barra spaziatrice sono responsabili della selezione rapida della colonna o riga corrente.

Una volta ho visto un bambino di quattro anni usare i tasti di scelta rapida copia e incolla per inserire una chiave per un gioco. Sembra una trinità inseparabile Ctrl + C, Ctrl + V e Ctrl + A assorbito dalle giovani generazioni insieme al latte materno. A proposito, il comando "Seleziona tutto" in Excel ha una piccola funzionalità: un solo clic Ctrl + A seleziona l'intervallo corrente e double seleziona l'intera tabella.

4. Come apportare modifiche su più fogli contemporaneamente

Rapa al vapore, la tua via d'uscita! Ad esempio, per modificare la dimensione del carattere su tutti i fogli contemporaneamente, è necessario raggrupparli in un gruppo facendo clic sulle schede del foglio mentre si tiene premuto il tasto Ctrl.

5. Come trasferire una riga nella cella corrente

Se vuoi prendere in giro un principiante, chiedigli di inserire i dati all'interno della cella in più righe. Certo, un trucco scolastico, ma con la vecchia generazione funziona con il botto. Combinazione Alt + Invio impedisce di saltare a un'altra cella e sposta il cursore su una nuova riga all'interno della cella corrente.

L'inserimento di una riga o colonna aggiuntiva richiede da tre a quattro clic. Non è molto, ma solo finché non avrai bisogno di una dozzina di nuove colonne contemporaneamente, ad esempio. Tuttavia, avendo precedentemente selezionato il numero richiesto di celle già esistenti, puoi scendere con lo stesso numero di clic.

7. Come inserire velocemente la data corrente

A quanto pare tasti di scelta rapida Ctrl +; e Ctrl + Maiusc +; dovrebbe essere suonato alla radio invece dell'inno mattutino. Queste combinazioni dovrebbero essere ripetute fino a quando quel gran lavoratore rimane sul pianeta che guida manualmente la data e l'ora.

• Tutorial

Prefazione

È successo che oggi molte persone devono lavorare (scrivere macro) in VBA in Excel. Alcune macro contengono centinaia di righe di codice che devono essere eseguite ogni giorno (settimana, mese, trimestre e così via), eppure richiedono una discreta quantità di tempo. Sembra che il processo sia automatizzato e non sia necessario l'intervento umano, ma il tempo necessario per eseguire una macro può richiedere decine di minuti, o anche diverse ore. Il tempo, come si suol dire, è denaro e in questo post cercherò di accelerare in modo significativo i tempi di esecuzione della tua macro e, forse, questo avrà un effetto positivo sulla tua attività e, alla fine, sul denaro.

Prima di iniziare il lavoro

Prima di andare dritto al punto, vorrei attirare la tua attenzione sul post: alcuni suggerimenti per lavorare con VBA in Excel. In particolare, la sezione "Accelerazione delle macro" contiene utili esempi di codice che dovrebbero essere utilizzati insieme ai miei consigli su come velocizzare il lavoro per ottenere i massimi risultati.

Velocizza la macro

Quindi, al punto ... Per accelerare davvero il lavoro di VBA in Ecxel, devi capire che l'accesso a una cella su un foglio richiede molto tempo. Se vuoi scrivere un valore in una cella, non ci vorrà molto tempo, ma se devi scrivere (leggere, accedere) a migliaia di celle, ci vorrà molto più tempo. Cosa fare in questi casi? Gli array vengono in soccorso. Gli array sono archiviati in memoria e VBA esegue operazioni in memoria centinaia, se non migliaia di volte più velocemente. Pertanto, se hai migliaia, centinaia di migliaia di valori nei tuoi dati, il tempo di esecuzione della macro può richiedere da alcuni minuti a diverse ore e se questi dati vengono trasferiti a un array, l'esecuzione della macro può essere ridotta a alcuni secondi (minuti).

Ti darò un codice di esempio e spiegherò cosa c'è nei commenti, così sarà più chiaro. Inoltre, possono essere utili alcune righe di codice che non sono direttamente correlate al processo di accelerazione.

Esempio

Supponiamo di avere dati su "Foglio1". I dati sono contenuti in 50 colonne (le colonne contengono titoli) e 10.000 righe. Ad esempio, dobbiamo inserire nell'ultima colonna un valore che sia uguale al valore della seconda colonna diviso per il valore della terza colonna (a partire dalla 2a riga, poiché la prima contiene il titolo). Quindi prenderemo le prime 10 colonne e le copieremo su "Foglio2" per ulteriori elaborazioni (per altre esigenze). Lascia che l'esempio sia banale, ma, come mi sembra, può riflettere l'intero punto di questo post.

"Per inizializzare esplicitamente le variabili, abilitare questa opzione" Questo aiuterà ad evitare molti errori Option Explicit Sub Test () "Accenderemo ai fogli tramite le variabili Dim Sheet1_WS, Sheet2_WS As Worksheet" Variabile per passare il termine sul foglio (in un array) Dim i As Long " Un array in cui verranno archiviati i nostri dati Dim R_data As Variant "Variabili dell'ultima riga e colonna Dim FinalRow, FinalColumn As Long" È possibile inizializzare il foglio non per nome, ma per numero ordinale "Set Sheet1_WS = Application.ThisWorkbook.Worksheet (" Sheet1 ") Imposta Sheet1_WS = Application.ThisWorkbook.Sheets (1) Imposta Sheet2_WS = Application.ThisWorkbook.Sheets (2) "Cerca l'ultima riga non vuota nella prima colonna" È necessario che i dati non vengano filtrati, altrimenti l'ultima riga sarà l'ultima riga nel filtro " Anche nell'ultima riga, nella prima colonna, non dovrebbe esserci una cella vuota. Naturalmente, se ci sono dati in questa riga In caso contrario, l'ultima riga sarà l'ultima cella non vuota.FinalRow = Sheet1_WS.C ells (Rows.Count, 1) .End (xlUp) .Row "= 10 000" Trova l'ultima colonna non vuota nella prima riga FinalColumn = Sheet1_WS.Cells (1, Columns.Count) .End (xlToLeft) .Column "= 50 "Assegnazione dell'intervallo di dati all'array su Sheet 1 R_data = Sheet1_WS.Range (Sheet1_WS.Cells (1, 1), Sheet1_WS.Cells (FinalRow, FinalColumn)) For i = 2 To FinalRow" Eseguire le operazioni sui dati che bisogno. "Verificare che non vi sia alcuna divisione per zero." Si presume che le colonne 2 e 3 contengano dati numerici "Altrimenti, sarà necessaria la gestione degli errori Se R_data (i, 3)<>0 Quindi R_data (i, FinalColumn) = R_data (i, 2) / R_data (i, 3) End If Next i "Copia i dati dall'array a Sheet1" Prima di ciò, cancella i dati sul foglio (se c'è formattazione o formule, allora è meglio Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range (Sheet1_WS.Cells (1, 1), Sheet1_WS.Cells (FinalRow, FinalColumn)) = R_data "Copia dati su Sheet2, copia prime 10 colonne Sheet2 Sheet2_WS.Cells (1, 1), Sheet2_WS.Cells (FinalRow, 10)) = R_data "Chiudi la cartella di lavoro e salvala Cartelle di lavoro (Application.ThisWorkbook.Name). Chiudi SaveChanges: = True End Sub

In questo esempio, la matrice viene riempita con l'intervallo specificato. Se abbiamo un array bidimensionale esplicitamente specificato, possiamo copiarne il valore sul foglio come segue:

Dim R_new () Come variante ............................................ "Specificare in modo esplicito la dimensione dell'array ReDim R_new (1 in FinalRow, 1 in 50) As Variant .......................... ... ........... Sheet1_WS.Range (Sheet1_WS.Cells (1, 1), Sheet1_WS.Cells (FinalRow, 50)) = R_new ()

Conclusione

La maggior parte delle operazioni sui dati può essere eseguita in una matrice, visualizzando solo il risultato sul foglio. A volte è consigliabile visualizzare il risultato su un foglio, quindi eseguire alcune azioni (ad esempio l'ordinamento) e ricaricare i dati nell'array.

Per me è stata una grande sorpresa velocizzare il lavoro della macro grazie agli array, visto che i dati sui fogli, infatti, sono già un array bidimensionale. Ma si scopre che l'accesso alla memoria è molto più veloce delle celle sul foglio.

In futuro, ho intenzione di scrivere suggerimenti (esempi) per trovare rapidamente i dati su un foglio, ma questo sarà già un altro post. Se avete domande, commenti, scrivete.

Grazie per l'attenzione. Buoni sviluppi.

Gli economisti di solito trascorrono circa la metà del loro tempo in Excel. Allo stesso tempo, la comunicazione con il programma dà a molti di noi molti grattacapi. l'esecuzione delle operazioni di routine richiede molto tempo. Parliamo di come diventare più veloci in Excel.

Non esiste praticamente alcuna alternativa a Excel. Non sorprende che Excel ti permetta di fare qualsiasi cosa. È come un cavalletto vuoto per un artista. Ti siedi davanti a lui e inizi a dipingere il tuo quadro.

Una sbavatura, un'altra sbavatura... Passano diverse ore e... Un paio di piccole tavolette e un grafico dal design standard. Triste non è vero?

E questo è successo a me. Tu lavori, tu lavori. Già esaurito, e il lavoro è appena iniziato. Quindi cosa puoi fare al riguardo?

C'è un'uscita! È vero, non tutti lo conoscono e quindi devono rimanere fino a tardi al lavoro, rimandare le riunioni con gli amici, gli appuntamenti con i propri cari e semplicemente esaurirsi al lavoro. Non so voi, ma non fa per me, e oggi vi racconterò i miei segreti per lavorare velocemente in Excel.

Interessante? Andiamo allora!

La prima cosa da fare è buttare via il topo!

È così che hai letto bene. Per lavorare più velocemente in Excel, devi rimuovere il mouse. SOLO non affrettarti a chiudere la scheda, ora tutto diventerà chiaro.

Capisco che il topo ti è molto caro. Non puoi vivere senza, ma credimi, è quello che ti ferma. Non sono pazzo, conosco solo un modo molto più comodo e veloce per navigare le pagine di Excel e semplicemente non uso questo meraviglioso gadget.

Si tratta di tasti di scelta rapida che ti consentono di passare semplicemente da una pagina all'altra alla velocità della luce, selezionare, aggiungere o eliminare righe/colonne. In generale, la conoscenza di letteralmente 5-7 combinazioni accelera il lavoro di un ordine di grandezza. Molte operazioni di navigazione con il mouse richiedono 3-5 o anche 10 volte di più.

Lasciate che vi faccia un esempio. Esistono tre opzioni per aggiungere più righe in Excel:

1. Opzione 1. Prendi il mouse, passalo sopra la colonna corrispondente o più colonne, a seconda di quante colonne devi inserire. Fare clic con il pulsante destro del mouse, selezionare Incolla nella casella a discesa.
2. Opzione 2. Utilizzando i pulsanti nella scheda Home nel gruppo Celle. Seleziona di nuovo, vai alla scheda Home. Facciamo clic sui pulsanti corrispondenti per ottenere il risultato.
3. Opzione 3. Per selezionare una colonna, premere Ctrl + 'Spazio', per aggiungere una colonna, fare clic su Ctrl + '+'.

Puoi dire che i risparmi non sono grandi. Un modo lo farò in 15-20 secondi, l'altro in 3-5. E cosa farò con i 12-15 secondi liberati? E in generale, di quanti tasti di scelta rapida hai bisogno per imparare a sentire la differenza?

Concordo! Il tempo risparmiato da un tasto di scelta rapida non è eccezionale, ma tutto il nostro lavoro in Excel è una ripetizione monotona delle stesse operazioni centinaia di volte in sequenze diverse. Ciò significa che si presume che il risparmio condizionale su un'operazione sia di 10 secondi. Quando si ripete un'operazione 20-30 volte al giorno, il risparmio dalla conoscenza di una combinazione di tasti di scelta rapida è di 3-5 minuti. Ciò significa che la conoscenza di 10 tasti di scelta rapida consente di risparmiare 30-50 minuti di tempo!!! Ma questo è già bello!!!

Il suggerimento numero due segue dal primo: impara i tasti di scelta rapida. Non ci sono così tanti tasti di scelta rapida utili, quindi dopo aver appreso 10-20 scorciatoie, sentirai rapidamente la differenza nella velocità di lavoro.

"Bel consiglio!" - ti opponi. - “E come insegnarli? Stare su uno sgabello davanti ai colleghi e recitare come una poesia?" ?

No. Ovviamente non è così. prova a trovare il tuo modo di imparare le scorciatoie da tastiera. Cosa si può fare per questo?

Ad esempio, puoi prendere un elenco di tasti di scelta rapida e selezionare 3-5 delle funzioni più utilizzate. Prova a risolverli, pratica come funzionano. Abituati a colpirli quando ne hai bisogno. Nel tempo, le dita stesse giacciono sulla tastiera in modo da poter passare rapidamente e comodamente da un foglio all'altro. Imparato questi: vai al prossimo, e poi ancora e ancora.

E, naturalmente, ecco un elenco di utili scorciatoie da tastiera e tasti di scelta rapida:

Le scorciatoie da tastiera ti aiuteranno sicuramente a lavorare più velocemente in Excel. Esercitati a usarli e la tua velocità di lavoro aumenterà in modo significativo molto rapidamente. Ma non è tutto!

Ho ancora qualche consiglio in magazzino. Pronto? Andare!

Suggerimento Excel numero TRE. Funzioni e loro combinazioni che devono essere padroneggiate

Può sembrare banale, ma è necessario conoscere le funzioni per lavorare più velocemente in Excel. Sono sicuro che tu, mio ​​caro lettore, conosci bene le funzionalità di Excel e puoi creare report e calcoli molto interessanti in Excel. Tuttavia, è possibile che non tutte le funzioni siano soggette a te e che ci sia qualcosa per cui lottare.

Inoltre, ogni funzione può avere molti usi. Quindi, ad esempio, sai che la funzione SOMMA può riassumere valori da diversi fogli della tua cartella di lavoro senza doverli selezionare separatamente? Quelli.

Al posto della formula

= SOMMA (Foglio1! A1; Foglio2! A1; Foglio3! A1; Foglio4! A1; Foglio5! A1; Foglio6! A1; Foglio7! A1; ... FoglioN! A1)

La formula sarà simile

= SOMMA (Foglio1: FoglioN! A1)

A cosa serve tutto questo? Oltre alla ricca funzionalità di Excel, che è nelle sue formule standard, ci sono molte combinazioni, la cui conoscenza consente di risolvere attività non standard. Quindi, Excel non ha affatto la funzione MINESLI. Sì, c'è SUMIF, COUNTIF, ma MINESLY no. Anche MAXESLI, MEDIANESLI, ecc., ma tutto questo si risolve utilizzando le funzioni delle aree. Potresti aver visto quando una formula è racchiusa tra parentesi graffe.

Alcune funzioni funzionano alla grande solo in combinazione. Questo è ciò di cui sto parlando ora di INDEX e POISKPOS. Sembrerebbe che siano funzioni stupide separatamente, ma insieme danno un'eccellente funzionalità.

Cosa è necessario per questo? Ad esempio, iscriviti al nostro gruppo in Facebook e attendi la pubblicazione di nuovi post. ?

Controlla anche l'elenco delle formule a cui ti consiglio di prestare attenzione:

Ci sono molte altre funzioni in Excel, ma per ora puoi capirlo. Sono sicuro che la maggior parte di essi ti aiuterà a lavorare più velocemente in Excel e ad essere almeno significativamente più efficiente.

Suggerimento Excel numero QUATTRO. Struttura e formattazione nei file

Se ti sei imbattuto nei principi della modellazione, allora sai ovviamente che per evitare errori è sufficiente mettere le cose in ordine nei tuoi calcoli.

Calcoli confusi, confusione e navigazione scomoda portano al fatto che anche l'autore stesso inizia a vagare nel file.

Come si può evitare? Ecco alcuni suggerimenti:

• Formatta il file. Ti consente di avere lo stesso numero di posizioni decimali, lo stesso carattere in tutto il documento e una tavolozza di colori limitata. Mi piace usare tavolozze dello stesso colore ma con tonalità diverse (ciano, blu e blu scuro). Sembra molto elegante.
• Dati di input, calcoli e risultati separati. Questo non è sempre necessario, ma quando ci sono molti dati, la presenza di una piastra pivot salva semplicemente.
• Fai le stesse formule per colonna o riga. d'accordo non molto correttamente quando lo stesso indicatore in periodi diversi è considerato in modo diverso. Tuttavia, di solito questo non è visibile finché non si esamina la formula. ma puoi anche dimenticare l'onore.
• Cerca di evitare riferimenti circolari e collegamenti a file esterni. Di solito danno risultati diversi su computer diversi. Si è verificato un caso in cui l'errore durante l'aggiornamento dei collegamenti esterni era superiore a 7 zeri. Ay!
• Semplifica e risparmia spazio. Excel ti consente di creare tutte le righe e le colonne che desideri. Nei miei 10 anni di lavoro con il programma, non ho mai usato completamente il foglio. È improbabile che tu abbia successo, quindi non recintare formule complesse. Meglio fare il calcolo in pochi passaggi.

Questi sono i suggerimenti. Credimi, semplificano la vita. Ci sono meno errori, i calcoli sono più precisi e comprensibili. se hai altri suggerimenti, lasciali nei commenti.

In questo consiglio, non sono assolutamente originale. Stranamente, ma il raggiungimento della padronanza dipende solo da te e da quanto e duramente ti eserciti.

Vorrei dedicare 28 minuti intensivi, dopodiché tutti conosceranno Excel meglio di quanto lo sappiano i suoi sviluppatori, ma ahimè. Questo non accade. Il lavoro costante e duro porta a risultati davvero impressionanti. Ecco perché molto presto lanceremo la formazione Excel, che sarà disponibile gratuitamente sul nostro sito Web (almeno per i primi 1000 utenti).

Pavlov Nikolay

In questo articolo, vorrei presentarti le tecniche più efficaci per lavorare in Microsoft Excel, raccolte da me negli ultimi 10 anni di lavoro su progetti e conduzione di corsi di formazione su questo meraviglioso programma. Non c'è nessuna descrizione di tecnologie super complesse qui, ma ci sono tecniche per tutti i giorni - semplici ed efficaci, descritte senza "acqua" - solo "residuo secco". La maggior parte di questi esempi richiederà meno di uno o due minuti per essere padroneggiata, ma ti aiuterà a risparmiare molto di più.

Salto veloce al foglio desiderato

Ti capita di lavorare con cartelle di lavoro Excel con molti fogli? Se ce ne sono più di una dozzina, ogni passaggio al successivo foglio necessario diventa di per sé un piccolo problema. Una soluzione semplice ed elegante a un tale problema è fare clic nell'angolo in basso a sinistra della finestra sui pulsanti di scorrimento delle schede del foglio non con il tasto sinistro, ma con il tasto destro del mouse - il sommario del libro con un completo apparirà l'elenco di tutti i fogli e potrai andare al foglio desiderato con un solo movimento:

Questo è molto più veloce che scorrere le schede del foglio con gli stessi pulsanti alla ricerca di ciò di cui hai bisogno.

Copia senza danneggiare la formattazione

Quante centinaia (migliaia?) di volte ho visto questa immagine, in piedi dietro i miei ascoltatori durante gli allenamenti: l'utente inserisce una formula nella prima cella e poi la "allunga" sull'intera colonna, interrompendo la formattazione delle righe sottostanti, poiché questo metodo copia non solo la formula, ma anche il formato della cella. Di conseguenza, è inoltre necessario correggere manualmente il danno. Un secondo per copiare e poi 30 secondi per riparare il disegno danneggiato dalla copia.

A partire da Excel 2002, esiste una soluzione semplice ed elegante a questo problema. Immediatamente dopo aver copiato (trascinato) la formula sull'intera colonna, è necessario utilizzare uno smart tag, una piccola icona che appare temporaneamente nell'angolo in basso a destra dell'intervallo. Facendo clic su di esso verrà visualizzato un elenco di possibili opzioni di copia, in cui è possibile selezionare Copia solo valori (Riempi senza formattazione). In questo caso, le formule vengono copiate, ma la formattazione no:

Copia solo le celle visibili

Se lavori in Microsoft Excel da più di una settimana, devi aver già affrontato un problema simile: in alcuni casi, durante il copia-incolla, vengono inserite più celle di quante ne siano state, a prima vista, copiate. Ciò può verificarsi se l'intervallo da copiare include righe/colonne nascoste, raggruppamenti, subtotali o filtri. Prendiamo come esempio uno di questi casi:

In questa tabella vengono calcolati i subtotali e le righe sono raggruppate per città: questo è facile da capire dai pulsanti più-meno a sinistra della tabella e dalle interruzioni nella numerazione delle righe visibili. Se selezioniamo, copiamo e incolliamo i dati da questa tabella nel solito modo, otterremo 24 righe in più. Vogliamo solo copiare e incollare i totali!

Puoi risolvere il problema evidenziando scrupolosamente ogni riga di totali mentre tieni premuto il tasto CTRL, come faresti per selezionare intervalli non contigui. Ma cosa succede se non ci sono tre o cinque di queste linee, ma diverse centinaia o migliaia? C'è un altro modo, più veloce e più conveniente:

Seleziona l'intervallo da copiare (nel nostro esempio è A1: C29)

Premi il tasto F5 sulla tastiera e poi il pulsante Speciale nella finestra che si apre.
Apparirà una finestra che consente all'utente di selezionare non tutto in una riga, ma solo le celle necessarie:

In questa finestra, seleziona l'opzione Solo celle visibili e fai clic su OK.

La selezione risultante può ora essere copiata e incollata in sicurezza. Di conseguenza, otterremo una copia delle celle visibili e inseriremo solo le 5 righe di cui abbiamo bisogno invece delle 29 non necessarie.

Se sospetti di dover eseguire spesso un'operazione del genere, ha senso aggiungere un pulsante alla barra degli strumenti di Microsoft Excel per chiamare rapidamente tale funzione. Questo può essere fatto tramite il menu Strumenti> Personalizza, quindi vai alla scheda Comandi, nella categoria Modifica, trova il pulsante Seleziona celle visibili e trascinalo sulla barra degli strumenti:

Conversione di righe in colonne e viceversa

Un'operazione semplice, ma se non sai come eseguirla correttamente, puoi passare mezza giornata a trascinare e rilasciare manualmente le singole celle:

In effetti, tutto è semplice. In quella parte della matematica superiore che descrive le matrici, c'è il concetto di trasposizione - un'azione che cambia righe e colonne in una matrice l'una con l'altra. In Microsoft Excel, questo viene fatto in tre movimenti: Copia la tabella

Fare clic con il tasto destro su una cella vuota e selezionare il comando Incolla speciale

Nella finestra che si apre, imposta il flag Transpose e clicca su OK:

Aggiungi rapidamente dati a un grafico

Immaginiamo una situazione semplice: hai un report dell'ultimo mese con un grafico visivo. Il compito è aggiungere nuovi dati numerici al grafico questo mese. Il modo classico per risolverlo è aprire la finestra dell'origine dati per il grafico, dove è possibile aggiungere una nuova serie di dati inserendo il suo nome e selezionando l'intervallo con i dati richiesti. E questo è spesso più facile a dirsi che a farsi: tutto dipende dalla complessità del diagramma.

Un altro modo - semplice, veloce e bello - è selezionare celle con nuovi dati, copiarle (CTRL + C) e incollare (CTRL + V) direttamente nel grafico. Excel 2003, a differenza delle versioni successive, supporta anche la possibilità di trascinare l'intervallo di celle selezionato con i dati e rilasciarlo direttamente nel grafico utilizzando il mouse!

Se vuoi controllare tutte le sfumature e le sottigliezze, puoi usare non il solito, ma la pasta speciale scegliendo Modifica> Incolla speciale dal menu. In questo caso, Microsoft Excel visualizzerà una finestra di dialogo che consente di configurare dove e come verranno aggiunti i nuovi dati:

Allo stesso modo, puoi creare facilmente un grafico utilizzando i dati di tabelle diverse da fogli diversi. Ci vorrà molto più tempo e fatica per completare lo stesso compito nel modo classico.

Riempire le celle vuote

Dopo aver esportato i report da alcuni programmi in formato Excel o durante la creazione di tabelle pivot, gli utenti spesso ricevono tabelle con celle vuote in alcune colonne. Queste lacune ti impediscono di applicare alle tabelle strumenti familiari e convenienti come Filtro automatico e Ordinamento. Naturalmente, diventa necessario riempire i vuoti con i valori delle celle di livello superiore:

Ovviamente, con una piccola quantità di dati, questo può essere fatto facilmente con una semplice copia - trascinando manualmente ogni cella di intestazione nella colonna A fino alle celle vuote. Ma cosa succede se la tabella contiene diverse centinaia o migliaia di righe e diverse dozzine di città?

C'è un modo per risolvere questo problema in modo rapido e bello usando una formula:

Seleziona tutte le celle nella colonna con i vuoti (cioè l'intervallo A1: A12 nel nostro caso)

Per lasciare solo celle vuote nella selezione, premi il tasto F5 e nella finestra di transizione che si apre, il pulsante Seleziona. Vedrai una finestra che ti permetterà di selezionare quali celle vogliamo evidenziare:

Imposta l'interruttore su Vuoto e fai clic su OK. Ora solo le celle vuote dovrebbero rimanere nella selezione:

Senza modificare la selezione, ad es. senza toccare il mouse, inserire la formula nella prima cella evidenziata (A2). Premi il segno di uguale sulla tastiera e poi la freccia su. Prendiamo la formula che fa riferimento alla cella precedente:

Per inserire la formula creata in una volta in tutte le celle vuote selezionate, premere CTRL + INVIO invece del tasto INVIO. La formula riempirà tutte le celle vuote:

Ora non resta che sostituire le formule con i valori per correggere i risultati. Seleziona l'intervallo A1: A12, copialo e incolla nelle celle i loro valori usando una pasta speciale.

Elenco a discesa nella cella

Un trucco che, senza esagerare, dovrebbe conoscere chiunque lavori in Excel. Il suo utilizzo può migliorare quasi tutte le tabelle, indipendentemente dal suo scopo. In tutti i corsi di formazione, cerco di mostrarlo ai miei ascoltatori il primo giorno.

L'idea è molto semplice: in tutti i casi in cui devi inserire dati da qualsiasi set, invece di inserire manualmente una cella dalla tastiera, seleziona il valore desiderato con il mouse dall'elenco a discesa:

Selezione di un prodotto da un listino prezzi, nome di un cliente da una base clienti, nome completo di un dipendente da una tabella del personale, ecc. Ci sono molte opzioni per usare questa funzione.

Per creare un elenco a discesa in una cella:

Seleziona le celle in cui desideri creare un elenco a discesa.

Se hai Excel 2003 o precedente, seleziona Dati> Convalida dal menu. Se hai Excel 2007/2010, vai alla scheda Dati e fai clic sul pulsante Convalida dati.

Nella finestra che si apre, seleziona l'opzione Elenco dall'elenco a discesa.

Nel campo Origine, è necessario specificare i valori che dovrebbero essere nell'elenco. Ecco le opzioni:

Inserisci le opzioni di testo in questo campo, separate da punto e virgola

Se l'intervallo di celle con i valori iniziali è sul foglio corrente, devi solo selezionarlo con il mouse.

Se si trova su un altro foglio di questo libro, dovrà dare un nome in anticipo (selezionare le celle, premere CTRL + F3, inserire il nome dell'intervallo senza spazi), quindi scrivere questo nome nel campo