Uvjetno oblikovanje (5)
Popisi i rasponi (5)
Makronaredbe (VBA procedure) (63)
Razno (39)
Greške i greške u Excelu (3)

Kako ubrzati i optimizirati VBA kod

Prije ili kasnije, oni koji pišu u Visual Basicu za aplikacije imaju problem - iako kod olakšava život i radi sve automatski, potrebno je jako puno vremena. U ovom sam članku odlučio prikupiti nekoliko jednostavnih preporuka koje će vam pomoći da ubrzate rad VBA koda, a u nekim slučajevima je prilično impresivan - desetke, ili čak više puta. Glavni fokus članka je na početnicima, pa su najjednostavnije metode optimizacije navedene na početku članka. Više "dubokih" rješenja za optimizaciju koda data su na kraju članka, jer za primjenu ovih rješenja potrebno je dovoljno iskustva u VB-u i odmah takve metode optimizacije nekome možda neće biti jasne.

1. Ako u kodu ima puno Activate i Select, posebno u ciklusima, trebali biste ih se odmah riješiti. Kako to učiniti, napisao sam u članku: Odaberite i aktivirajte - zašto su potrebni i jesu li potrebni?
2. Obavezno onemogućite tijekom izvršavanja koda:
   • automatsko preračunavanje formula. Kako se formule ne bi ponovno izračunavale pri svakoj manipulaciji na listu tijekom izvršavanja koda - to može jako usporiti kod ako ima puno formula:

     Application.ScreenUpdating = False

   • Za svaki slučaj onemogućite praćenje događaja. To je potrebno kako Excel ne bi izvršio nikakve postupke događaja koji se mogu nalaziti na listu u kojem se vrše promjene. U pravilu su to događaji promjene ćelija, aktiviranja listova itd.:

     ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

     ako se ispis vrši unutar koda, onda je preporučljivo ovaj redak umetnuti odmah iza retka koji ispisuje list (pod uvjetom da se ispis ne događa u ciklusu. U ovom slučaju nakon završetka ciklusa ispisa).

   • Za svaki slučaj možete isključiti prikaz informacija u statusnoj traci programa Excel (u kojim slučajevima se informacije tamo uopće prikazuju i zašto možete saznati u članku: Prikaži napredak). Iako to ne troši puno resursa, ponekad ipak može ubrzati kod:

     Application.StatusBar = False

     Application.StatusBar = False

   Glavna stvar koju treba zapamtiti - sva ta svojstva moraju se ponovno uključiti nakon što se kod pokrene. Inače, može doći do problema s radom unutar Excela. Na primjer, ako zaboravite omogućiti automatski ponovni izračun formula - većina formula će se ponovno izračunati isključivo prisilnom metodom - Shift+F9. A ako zaboravite isključiti ažuriranje zaslona - to jest, postoji šansa da se blokirate u mogućnosti rada na listovima i knjigama. Iako bi svojstvo ScreenUpdating prema zadanim postavkama trebalo vratiti True ako je bilo onemogućeno unutar procedure, bolje je ne oslanjati se na to i naviknuti se na prisilno vraćanje svih svojstava na njihova prava mjesta. Zapravo, sve se svodi na nekoliko redaka:

   "Povratak ažuriranja zaslona Application.ScreenUpdating = True "Povratak automatskog ponovnog izračuna formula Application.Calculation = xlCalculationAutomatic "Omogući praćenje događaja Application.EnableEvents = True

   Kako ovaj kod izgleda u praksi? Pretpostavimo da trebate napisati u petlji od 10 000 reda vrijednosti:

   Sub TestOptimize() "onemogući ažuriranje zaslona Application.ScreenUpdating = False "Onemogući automatsko ponovno izračunavanje formula Application.Calculation = xlCalculationManual "Onemogući praćenje događaja Application.EnableEvents = False "Onemogući paginaciju ActiveWorkbook.ActivePlaySheetBreDiak filling FieldDirectDirect. lr As Long For lr = 1 do 10000 ćelija (lr, 1).Value = lr "na primjer, samo numerirajte redove Dalje "Povratak ažuriranja zaslona Application.ScreenUpdating = True "Povratak automatskog ponovnog izračuna formula Application.Calculation = xlCalculationAutomatic " Omogući aplikaciju za praćenje događaja EnableEvents = True EndSub

   Pauze na ispisanim stranicama ne mogu se vratiti - one u svakom slučaju usporavaju rad.

3. Treba izbjegavati petlje poput Do While za pronalaženje posljednje ćelije. Početnici često čine ovu grešku. Mnogo je učinkovitije i brže izračunati zadnju ćeliju na cijelom listu ili u određenom stupcu bez ove petlje Do While kočenja. Ja obično koristim

   lLastRow = Ćelije(Redci.Broj,1).Kraj(xlUp).Red

   lLastRow = Ćelije(Redci.Broj,1).Kraj(xlUp).Red

   Ostale opcije za određivanje posljednje ćelije detaljno sam opisao u članku:

Za iskusnije VBA korisnike dat ću nekoliko rješenja za optimizaciju kodova u raznim situacijama:

• Najbolja optimizacija koda, ako morate izravno raditi sa ćelijama lista, obraditi ih i, eventualno, mijenjati vrijednosti, tada je brže obaviti svu obradu u nizu i odjednom ga istovariti na list . Na primjer, gornji kod za popunjavanje ćelija brojevima će u ovom slučaju izgledati ovako:

  Sub TestOptimize_Array() "Izravno kod za popunjavanje ćelija Dim arr, lr koliko dugo "zapamti u nizu u jednom zamahu svih vrijednosti 10000 redaka prvog stupca arr = Ćelije(1, 1).Promijeni veličinu(10000).Vrijednost "ako trebate dopunu za dva ili više stupaca "arr = Ćelije(1, 1).Promijeni veličinu(10000, 2).Vrijednost"ili "arr = Raspon(Ćelije(1, 1),Ćelije(10000, 2)).Vrijednost "ili automatski izračunajte zadnju ćeliju i unesite podatke u niz, počevši od ćelije A3 "llastr = Cells(Rows.Count, 1).End(xlUp).Row "posljednja ćelija stupca A "arr = Raspon(Ćelije(3, 1),Ćelije(llastr, 2)).Vrijednost Za lr = 1 do 10000 arr(lr,1) = lr "Popunite niz serijskim brojevima Sljedeći "Prenesite obrađeni niz natrag na list u istim ćelijamaĆelije(1, 1).Promijeni veličinu(10000).Vrijednost = arr End Sub

  Sub TestOptimize_Array() "Izravno kod za popunjavanje ćelija Dim arr, lr As Long "zapamti sve vrijednosti 10000 redaka prvog stupca arr = Cells(1, 1).Resize(10000).Value "(!LANG : ako trebate ispuniti dva ili više stupaca"arr = Cells(1, 1).Resize(10000, 2).Value "ili"arr = Range(Cells(1, 1),Cells(10000, 2)).Value "ili automatski izračunajte zadnju ćeliju i unesite podatke u niz, počevši od ćelije 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!}

  Ali ovdje također treba uzeti u obzir činjenicu da veliki nizovi mogu jednostavno uzrokovati prelijevanje memorije. Ovo je najrelevantnije za 32-bitne sustave, gdje je za VBA i Excel dodijeljeno manje memorije nego u 64-bitnim sustavima.

• Ako koristite brzi IF - IIF, zamijenite ga s IF ... Onda ... Ostalo
• Također je bolje koristiti isti IF ... Onda ... Else umjesto Switch() i Shoose()
• U većini slučajeva, provjera niza za "nije prazan" je bolja s Len() nego izravna usporedba s prazninom: Len(s)=0 umjesto s = "" . To je zbog činjenice da je rad sa nizovima mnogo sporiji od rada s brojčanim podacima.
• Nemojte nepotrebno primjenjivati ​​konkatenaciju nizova. Na primjer, s = "AB" bit će brži od: s = "A" & "B"
• Nemojte izravno uspoređivati ​​tekstualne vrijednosti. Bolje je koristiti ugrađenu funkciju StrComp:
  Ako s s1 Tada će biti sporije od
  Ako je StrComp(s, s1, vbBinaryCompare) = 0
  a još više ako usporedba mora biti neosjetljiva na velika i mala slova:
  Ako LCase(s) LCase(s1) Tada će biti sporije od
  Ako je StrComp(s, s1, vbTextCompare) = 0
• For...Next petlje su općenito brže od Do...Loop petlje.
• Izbjegavajte dodjeljivanje varijabli tipa Variant . Iako je iskušenje veliko, ovaj tip oduzima puno memorije i dodatno usporava kod. Također, za objektne varijable, bezličan globalni tip Object treba izbjegavati kad god je to moguće i koristiti konkretan tip:

  Dim rRange kao raspon, wsSh kao radni list

• Ako radite s nizovima, to možete eksplicitno navesti prilikom deklariranja:

  Dim arr() kao niz, arr2() kao dugačak

  ali to je samo ako postoji povjerenje da će strogo specificirani tipovi podataka biti uneseni u niz

Naravno, to nisu svi trikovi i rješenja za optimizaciju. Ali prvi par bi trebao biti dovoljan. Osim toga, uvijek biste trebali koristiti zdrav razum. Na primjer, ako se kod pokrene za 2 sekunde, vjerojatno ga nema smisla dalje optimizirati. Naravno, ako ovaj kod nije jedan od onih koji jednostavno mijenjaju vrijednost jedne ili dvije ćelije.

Je li članak pomogao? Podijelite link sa svojim prijateljima! Video lekcije

("Donja traka":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"desno","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1" ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"desno","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffect5delay0 textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:apsolute; top:0px; lijevo:0px; širina:100%; visina:100% ; boja pozadine:#333333; neprozirnost:0,6; filter:a lpha(opacity=60);","titlecss":"display:block; položaj:relativan; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; boja:#fff;","descriptioncss":"display:block; položaj:relativan; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; boja:#fff; margin-top:8px;","buttoncss":"display:block; položaj:relativan; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Sadrži 32.767 znakova. Stoga funkcija brze promjene širine stupca prema njegovom sadržaju nije uvijek prikladna. :) Iako u većini slučajeva pomaže. Pomaknite pokazivač miša preko desnog ruba zaglavlja stupca i dvaput kliknite na njega tako da program odabere optimalnu širinu stupca. Ovaj trik radi za više stupaca, kao i za cijeli radni list.

2. Kako brzo odabrati cijeli redak ili stupac

Pri radu s velikim količinama podataka nemoguće je bez tipkovničkih prečaca s kojima se lako kretati po listu i birati pojedine stupce ili retke. Kombinacije Ctrl+razmaknica I Shift + razmaknica samo odgovoran za brzi odabir trenutnog stupca ili retka.

Jednom sam gledao četverogodišnjaka kako koristi prečace za kopiranje i lijepljenje za unos ključa igre. Izgleda kao nerazdvojivo trojstvo ctrl+c, Ctrl+V I Ctrl+A apsorbira mlada generacija zajedno s majčinim mlijekom. Usput, naredba "Odaberi sve" u Excelu ima malu značajku: jedan klik Ctrl+A odabire trenutni raspon, a dvostruko odabire cijelu tablicu.

4. Kako napraviti izmjene na više listova u isto vrijeme

Repa kuhana na pari, tvoj izlaz! Na primjer, da biste promijenili veličinu fonta na svim listovima odjednom, morate ih skupiti u grupu tako da kliknete na kartice listova dok držite pritisnutu tipku Ctrl.

5. Kako premjestiti liniju u trenutnoj ćeliji

Ako se želite rugati novajlija u - zamolite ga da unese podatke unutar ćelije u nekoliko redaka. Naravno, školski trik, ali kod starije generacije radi s praskom. Kombinacija Alt+Enter sprječava skok na drugu ćeliju i pomiče kursor na novi redak unutar trenutne ćelije.

Za umetanje dodatnog retka ili stupca potrebna su tri ili četiri klika mišem. To nije puno, ali samo dok vam ne zatreba desetak novih stupaca odjednom, na primjer. Međutim, nakon što ste prethodno odabrali potreban broj već postojećih ćelija, možete izaći s istim brojem klikova.

7. Kako brzo umetnuti trenutni datum

Očigledno vruće tipke Ctrl + ; I Ctrl + Shift + ; treba puštati na radiju umjesto jutarnje himne. Ove kombinacije treba ponavljati sve dok na planetu ne ostane nijedan radnik koji ručno ubacuje datum i vrijeme.

• tutorial

Predgovor

Dogodilo se da danas mnogi ljudi moraju raditi (pisati makronaredbe) u VBA u Excelu. Neke makronaredbe sadrže stotine redaka koda koji se moraju izvršavati svaki dan (tjedan, mjesec, tromjesečje i tako dalje) i, u isto vrijeme, oduzimaju dosta vremena. Čini se da je proces automatiziran i da nije potrebna ljudska intervencija, ali vrijeme potrebno za izvršavanje makronaredbe može trajati nekoliko desetaka minuta, pa čak i nekoliko sati. Vrijeme je, kako kažu, novac, a u ovom ću postu pokušati značajno ubrzati vrijeme izvršavanja vašeg makroa i, možda, to će se pozitivno odraziti na vaše poslovanje, a u konačnici i novac.

Prije početka rada

Prije nego što prijeđem izravno na stvar, želio bih vam skrenuti pozornost na post: Nekoliko savjeta za rad s VBA u Excelu. Konkretno, blok "Ubrzajte makronaredbe" sadrži korisne primjere koda koje biste trebali koristiti zajedno s mojim savjetima za ubrzavanje rada kako biste postigli maksimalne rezultate.

Ubrzavanje makronaredbe

Dakle, do točke... Kako biste stvarno ubrzali rad VBA-a u Ecxelu, morate razumjeti da pristup ćeliji na listu oduzima puno vremena. Ako želite zapisati jednu vrijednost u ćeliju, onda to neće trebati puno vremena, ali ako trebate napisati (čitati, referirati) na tisuće ćelija, tada će trebati puno više vremena. Što učiniti u takvim slučajevima? Nizovi dolaze u pomoć. Nizovi se pohranjuju u memoriju, a VBA obavlja operacije u memoriji stotine ili čak tisuće puta brže. Stoga, ako imate tisuće, stotine tisuća vrijednosti u svojim podacima, tada vrijeme izvršavanja makronaredbe može potrajati od nekoliko minuta do nekoliko sati, a ako te podatke prenesete u niz, tada se izvršavanje makronaredbe može smanjiti na nekoliko sekundi (minuta).

Navest ću primjer koda i objasniti u komentarima što je što, bit će jasnije. Osim toga, neke linije koda koje nisu izravno povezane s procesom ubrzanja mogu dobro doći.

Primjer

Pretpostavimo da imamo podatke o "Sheet1" ("Sheet1"). Podaci su sadržani u 50 stupaca (stupci sadrže naslove) i 10.000 redaka. Na primjer, u zadnji stupac trebamo unijeti vrijednost koja je jednaka vrijednosti u drugom stupcu podijeljenoj s vrijednošću u trećem stupcu (počevši od 2. retka, budući da prvi sadrži naslov). Zatim ćemo uzeti prvih 10 stupaca i kopirati ih u “Sheet2” (“Sheet2”), za daljnju obradu (za druge potrebe). Iako je primjer banalan, čini mi se da može odraziti cijelu bit ovog posta.

"Da biste eksplicitno inicijalizirali varijable, omogućite ovu opciju "Ovo će pomoći izbjeći mnoge pogreške Opcija Eksplicitni podtest() "Pristupit ćemo listovima kroz varijable Dim Sheet1_WS, Sheet2_WS kao radni list "Varijabla za prosljeđivanje pojma na listu (u nizu ) Dim i As Long" Niz u koji će biti pohranjeni naši podaci Dim R_data As Variant "Varijable posljednjeg retka i stupca Dim FinalRow, FinalColumn As Long "Možete inicijalizirati list ne imenom, već serijskim brojem "Set Sheet1_WS = Application.ThisWorkbook.Worksheet("Sheet1") Postavite Sheet1_WS = Application.ThisWorkbook.Sheets(1) Postavite Sheet2_WS = Application.ThisWorkbook.Sheets(2) "Tražite zadnji redak koji nije prazan u prvom stupcu" Također, u zadnji red, u prvom stupcu, ne smije biti prazna ćelija. Naravno, ako uopće postoje podaci u ovom retku. Inače će zadnji red biti zadnja neprazna ćelija. FinalRow = Sheet1_WS.C ells(Rows.Count, 1).End(xlUp).Row "=10 000 "Traži posljednji neprazan stupac u prvom retku FinalColumn = Sheet1_WS.Cells(1, Columns.Count).End(xlToLeft). Stupac "=50 "Dodijelite raspon podataka na Listu 1 nizu R_data = Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) Za i = 2 Završnom redu "Izvedite operacije s podacima trebamo. "Provjerite da nema dijeljenja s nulom. "Pretpostavlja se da su stupci 2 i 3 numerički podaci "U suprotnom je potrebno rukovanje pogreškama Ako R_data(i, 3)<>0 Tada R_data(i, FinalColumn) = R_data(i, 2) / R_data(i, 3) End If Next i "Kopiraj podatke iz niza natrag na Sheet1 "Prije toga obrišite podatke na listu (ako postoji formatiranje ili formule, onda je bolje Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) Co prvi kopiraj podatke u Sheet_WS.Cells 10 stupaca. Sheet2_WS.Range( Sheet2_WS.Cells(1, 1), Sheet2_WS.Cells(FinalRow, 10)) = R_data "Zatvorite radnu knjigu i spremite je Workbooks(Application.ThisWorkbook.Name).Close SaveChanges:=Tr SaveChanges:=Tr.

U ovom primjeru, niz je ispunjen navedenim rasponom. Ako imamo eksplicitno definiran dvodimenzionalni niz, tada njegovu vrijednost možemo kopirati na list na ovaj način:

Dim R_new() kao varijanta ........................................ ... " Eksplicitno navedite veličinu niza ReDim R_new(1 do FinalRow, 1 do 50) kao varijantu .......................... ...... ........... Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, 50)) = R_new()

Zaključak

Većina operacija nad podacima može se izvesti u nizu, dok se na listu prikazuje samo rezultat. Ponekad ima smisla prikazati rezultat na listu, zatim izvršiti neke radnje (kao što je sortiranje) i ponovno učitati podatke u niz.

Za mene je bilo veliko iznenađenje ubrzati rad makronaredbe zbog nizova, budući da su podaci na listovima, zapravo, već dvodimenzionalni niz. No, pokazalo se da je pristup memoriji mnogo brži od pristupa ćelijama na listu.

U budućnosti planiram napisati savjete (primjere) za brzo pronalaženje podataka na listu, ali ovo će biti drugi post. Ako imate pitanja, komentare, pišite.

Hvala na pažnji. Sretan razvoj.

Ekonomisti obično provode oko pola svog radnog vremena u Excelu. Istovremeno, mnogima od nas komunikacija s programom zadaje popriličnu glavobolju, jer. obavljanje rutinskih operacija oduzima puno vremena. Razgovarajmo o tome kako raditi brže u Excelu.

Alternative Excelu praktički nema. Nije iznenađujuće, jer Excel vam omogućuje gotovo sve. To je kao prazan štafelaj za umjetnika. Sjedneš za nju i počneš slikati svoju sliku.

Razmaz, drugi bris... Prođe nekoliko sati i... Par malih pločica i graf standardnog dizajna. Žalosno zar ne?

I meni se to dogodilo. Radiš, radiš. Već iscrpljena, a posao je tek počeo. Što učiniti s tim?

Postoji izlaz! Istina, ne znaju svi za njega, pa stoga moraju ostati na poslu, odgoditi okupljanja s prijateljima, spojeve s voljenima i jednostavno izgorjeti na poslu. Ne znam za vas, ali meni ne odgovara, a danas ću vam reći svoje tajne brzog rada u Excelu.

Zanimljiv? Onda idemo!

Prvo što treba učiniti je izbaciti miša!

Tako ste dobro pročitali. Za brži rad u Excelu morate ukloniti miš. SAMO nemojte žuriti zatvoriti karticu, sada će sve postati jasno.

Razumijem da ti je miš jako drag. Ne možete živjeti bez nje, ali vjerujte mi, ona je ta koja vas zaustavlja. Nisam luda, samo znam mnogo praktičniji i brži način navigacije Excel stranicama i jednostavno nemojte koristiti ovaj divni gadget.

Sve se radi o vrućim tipkama koje vam omogućuju jednostavno prebacivanje između stranica munjevitom brzinom, odabir, dodavanje ili brisanje zgrada/stupaca. Općenito, poznavanje doslovno 5-7 kombinacija ubrzava rad za red veličine. Mnoge operacije navigacije mišem traju 3-5 ili čak 10 puta dulje.

Dat ću vam primjer. Za dodavanje više redaka u Excel postoje tri opcije:

1. Opcija 1. Uzmite miš, zadržite ga iznad odgovarajućeg stupca ili nekoliko stupaca, ovisno o tome koliko stupaca trebate umetnuti. Desnom tipkom miša kliknite i s padajućeg okvira odaberite Zalijepi.
2. Opcija 2. Korištenje gumba na kartici Početak u grupi Ćelije. Opet odaberite, idite na karticu Početna. Kliknite na odgovarajuće gumbe da biste dobili rezultat.
3. Opcija 3. Pritisnite za označavanje stupca. Ctrl + 'Razmaknica', za dodavanje stupca kliknite Ctrl+'+'.

Možete reći da uštede nisu velike. Na jedan način ću to učiniti za 15-20 sekundi, na drugi za 3-5. A što ću s oslobođenih 12-15 sekundi? I općenito, koliko vrućih tipki trebate naučiti da osjetite razliku?

Slažem se! Vrijeme ušteđeno jednom vrućom tipkom nije sjajno, ali sav naš rad u Excelu monotono je ponavljanje istih operacija stotine puta u različitim slijedovima. Znači ako je uvjetna ekonomičnost na jednoj operaciji pretpostavit ćemo 10 sekundi. Kada se jedna operacija ponavlja 20-30 puta dnevno, ušteda od poznavanja jedne kombinacije tipki je 3-5 minuta. Dakle, poznavanje 10 vrućih tipki štedi 30-50 minuta vremena!!! E sad to je super!!!

Savjet broj dva slijedi iz prvog - naučite prečice. Nema toliko korisnih vrućih tipki, pa ćete nakon što naučite 10-20 kombinacija brzo osjetiti razliku u brzini.

"Dobar savjet!" - prigovarate. “A kako ih podučavaš? Stajati na tabure ispred kolega i recitirati kao poeziju? 🙂

Ne. Naravno, nije tako. pokušajte pronaći vlastiti način učenja prečaca. Što se može učiniti za to?

Na primjer, možete uzeti popis prečaca i odabrati 3-5 najčešće korištenih funkcija. Pokušajte ih razraditi, vježbajte kako rade. Naviknite se pritiskati ih kada su vam potrebni. S vremenom će i sami prsti ležati na tipkovnici tako da se možete brzo i jednostavno prebacivati ​​između listova. Naučio ove - idi na sljedeći, pa još jedan i još jedan.

I naravno, ovdje je popis korisnih prečaca i prečaca:

Prečice će vam zasigurno pomoći da brže radite u Excelu. Uvježbajte njihovu upotrebu i vrlo brzo će se brzina vašeg rada značajno povećati. Ali to nije sve!

Imam još nekoliko savjeta u trgovini. Spreman? Ići!

Excel savjet broj TRI. Funkcije i njihove kombinacije koje morate savladati

Možda zvuči otrcano, ali morate znati funkcije da biste brže radili u Excelu. Siguran sam da vi, dragi moj čitatelju, tečno poznajete Excel funkcionalnost i da možete napraviti vrlo cool izvještaje i izračune u Excelu. No, moguće je da vam nisu podložne sve funkcije i da se ima čemu težiti.

Također, svaka funkcija može imati mnogo značajki korištenja. Dakle, na primjer, znate li da funkcija SUM može dodavati vrijednosti iz različitih radnih listova u vašu radnu knjigu bez potrebe za odabirom svakog pojedinačno? Oni.

Umjesto formule

=SUM(List1!A1; List2!A1; List3!A1; List4!A1; List5!A1; List6!A1; List7!A1; ... ListN!A1)

Formula će izgledati tako

=SUM(list1:listN!A1)

Čemu sve ovo? Uz bogatu funkcionalnost Excela, koja je u njegovim standardnim formulama, postoji mnogo kombinacija čije poznavanje omogućuje rješavanje nestandardnih zadataka. Dakle, Excel uopće nema funkciju MINESLI. Da, ima SUMMESLI, COUNTIF, ali MINESLI nije. Također MAXIF, MEDIANIF itd., ali sve se to rješava korištenjem funkcija područja. Možda ste vidjeli kada je formula umotana u vitičaste zagrade.

Neke funkcije rade dobro samo u kombinaciji. Ovo je ono o čemu sada govorim o INDEX-U i PRETRAŽIVANJU. Čini se da glupe funkcioniraju pojedinačno, ali u paru pružaju izvrsnu funkcionalnost.

Što je potrebno za ovo? Na primjer, pretplatite se na našu grupu u Facebook i čekati nove postove. 😉

Također pogledajte popis formula na koje preporučujem da obratite pozornost:

U Excelu postoji mnogo više funkcija, ali za sada se možete nositi s njima. Siguran sam da će vam većina njih pomoći da brže radite u Excelu i da budete barem značajno učinkovitiji.

Excel savjet broj ČETIRI. Struktura i formatiranje u datotekama

Ako ste se susreli s principima modeliranja, onda očito znate da je, kako biste izbjegli pogreške, dovoljno posložiti stvari u svojim izračunima.

Zbunjujuće kalkulacije, gomilanje i nezgodna navigacija dovode do činjenice da čak i sam autor počinje zalutati u datoteci.

Kako se to može izbjeći? Dat ću ti nekoliko savjeta:

• Formatirajte datoteku. Neka imate isti broj decimalnih mjesta, isti font u cijelom dokumentu i ograničenu paletu boja. Sada volim koristiti palete iste boje, ali različitih tonova (svijetloplava, plava i tamnoplava). Izgleda vrlo elegantno.
• Odvojeni ulazni podaci, izračuni i rezultati. To nije uvijek potrebno, ali kada ima puno podataka, prisutnost sažete ploče jednostavno štedi.
• Učinite iste formule po stupcu ili retku. slažem se da nije baš ispravno kada se isti pokazatelj u različitim razdobljima promatra različito. U ovom slučaju to obično nije vidljivo dok ne pogledate formulu. a ponekad možeš zaboraviti.
• Pokušajte izbjeći kružne reference i reference na vanjske datoteke. Obično daju različite rezultate na različitim računalima. Postojao je slučaj kada je pogreška u ažuriranju vanjskih veza bila veća od 7 nula. Ay!
• Pojednostavite i uštedite prostor. Excel vam omogućuje da napravite onoliko redaka i stupaca koliko želite. U svojih 10 godina rada s programom, nikada nisam koristio list u potpunosti. Malo je vjerojatno da ćete uspjeti, stoga nemojte ograđivati ​​složene formule. Bolje je izvršiti izračun u nekoliko koraka.

Evo nekoliko savjeta. Vjerujte mi, olakšavaju život. Manje je pogrešaka, izračuni su točniji i jasniji. ako imate još savjeta, ostavite ih u komentarima.

U ovom savjetu uopće nisam originalan. Ironično, postizanje majstorstva ovisi o vama i o tome koliko i koliko ćete vježbati.

Želio bih održati intenzivni tečaj od 28 minuta, nakon čega će svi poznavati Excel bolje nego što znaju njegovi programeri, ali nažalost. Ovo se ne događa. Konstantan i naporan rad dovodi do uistinu impresivnih rezultata. Zato vrlo brzo pokrećemo Excel trening koji će biti dostupan na našim stranicama besplatno (barem za prvih 1000 korisnika).

Pavlov Nikolaj

U ovom članku želim vam predstaviti najučinkovitije tehnike Microsoft Excela koje sam prikupio tijekom proteklih 10 godina rada na projektima i vođenja treninga na ovom prekrasnom programu. Ovdje nema opisa super-složenih tehnologija, ali postoje trikovi za svaki dan - jednostavni i učinkoviti, opisani bez "vode" - samo "suhi talog". Za savladavanje većine ovih primjera trebat će vam samo minutu ili dvije, ali će vam uštedjeti mnogo više.

Brzi skok na željeni list

Radite li slučajno s Excel radnim knjigama koje se sastoje od velikog broja listova? Ako ih ima više od desetak, onda svaki prijelaz na sljedeći željeni list sam po sebi postaje mali problem. Jednostavno i elegantno rješenje ovog problema je klikanje u donjem lijevom kutu prozora na gumbe za pomicanje kartica listova ne lijevom, već desnom tipkom miša - prikazat će se sadržaj knjige s kompletan popis svih listova i jednim pokretom možete ići na željeni list:

To je puno brže od pomicanja kroz kartice lista s istim gumbima u potrazi za onim što vam treba.

Kopiraj bez oštećenja formatiranja

Koliko sam stotina (tisuća?) puta vidio ovu sliku, kako stoji iza svojih slušatelja tijekom treninga: korisnik upisuje formulu u prvu ćeliju, a zatim je "razvlači" na cijeli stupac, razbijajući formatiranje redaka ispod, budući da ova metoda kopira ne samo formulu, već i format ćelije. Sukladno tome, dalje je potrebno ručno ispraviti štetu. Sekunda za kopiranje, a zatim 30 - za popravak dizajna pokvarenog kopiranjem.

Počevši od programa Excel 2002, postoji rješenje za ovaj problem - jednostavno i elegantno. Odmah nakon kopiranja (povlačenja) formule na cijeli stupac, trebate koristiti pametnu oznaku - malu ikonu koja se privremeno pojavljuje u donjem desnom kutu raspona. Klikom na nju prikazat će se popis mogućih opcija kopiranja, gdje možete odabrati Kopiraj samo vrijednosti ​​(Ispuni bez formatiranja). U ovom slučaju, formule se kopiraju, ali formatiranje nije:

Kopirajte samo vidljive ćelije

Ako radite u Microsoft Excelu više od tjedan dana, već ste se trebali susresti sa sličnim problemom: u nekim slučajevima, prilikom kopiranja i lijepljenja ćelija, umetne se više ćelija nego što je kopirano na prvi pogled. To se može dogoditi ako kopirani raspon uključuje skrivene retke/stupce, grupiranja, međuzbrojeve ili filtriranje. Uzmimo jedan od ovih slučajeva kao primjer:

U ovoj tablici izračunavaju se međuzbrojevi, a redovi su grupirani po gradu - to je lako razumjeti pomoću gumba plus-minus lijevo od tablice i prekidima u numeriranju vidljivih redaka. Ako podatke iz ove tablice odaberemo, kopiramo i zalijepimo na uobičajeni način, dobit ćemo 24 dodatna retka. Želimo kopirati i zalijepiti samo ukupne vrijednosti!

Problem možete riješiti pažljivim odabirom svakog retka ukupnih vrijednosti dok držite pritisnutu tipku CTRL - kao što biste odabrali nesusjedne raspone. Ali što ako nema tri ili pet takvih redaka, već nekoliko stotina ili tisuća? Postoji još jedan, brži i praktičniji način:

Odaberite raspon koji želite kopirati (u našem primjeru to je A1:C29)

Pritisnite tipku F5 na tipkovnici, a zatim tipku Special u prozoru koji se otvori.
Pojavit će se prozor koji omogućuje korisniku da odabere ne sve, već samo potrebne ćelije:

U ovom prozoru odaberite opciju Samo vidljive ćelije (Samo vidljive ćelije) i kliknite U redu.

Rezultirajući odabir sada se može sigurno kopirati i zalijepiti. Kao rezultat, dobit ćemo kopiju točno vidljivih ćelija i umjesto nepotrebnih 29 umetnuti samo 5 redaka koje trebamo.

Ako postoji sumnja da ćete morati često raditi takvu operaciju, onda ima smisla dodati gumb na alatnu traku Microsoft Excela za brzo pozivanje takve funkcije. To se može učiniti kroz izbornik Alati> Prilagodi, zatim idite na karticu Naredbe, u kategoriji Uredi pronađite gumb Odaberi vidljive ćelije i pomaknite ga mišem na alatnu traku:

Pretvaranje redaka u stupce i obrnuto

Jednostavna operacija, ali ako ne znate kako to učiniti ispravno, možete provesti pola dana ručno povlačeći i ispuštajući pojedinačne ćelije:

Zapravo, sve je jednostavno. U onom dijelu više matematike koji opisuje matrice, postoji koncept transpozicije - radnje koja mijenja redove i stupce u matrici jedan s drugim. U Microsoft Excelu to se radi u tri koraka: Kopirajte tablicu

Desnom tipkom miša kliknite praznu ćeliju i odaberite Posebno zalijepi

U prozoru koji se otvori postavite zastavicu Transpose (Transpose) i kliknite OK:

Brzo dodajte podatke u grafikon

Zamislimo jednostavnu situaciju: imate izvješće za prošli mjesec s vizualnim grafikonom. Zadatak je dodati nove numeričke podatke na grafikon već za ovaj mjesec. Klasičan način rješavanja je otvaranje prozora izvora podataka za grafikon, gdje možete dodati novi niz podataka unosom njegovog naziva i odabirom raspona sa željenim podacima. I često je to lakše reći nego učiniti - sve ovisi o složenosti dijagrama.

Drugi način - jednostavan, brz i lijep - je odabrati ćelije s novim podacima, kopirati ih (CTRL+C) i zalijepiti (CTRL+V) izravno u grafikon. Excel 2003, za razliku od kasnijih verzija, čak podržava mogućnost povlačenja odabranog raspona podatkovnih ćelija i ispuštanja izravno u grafikon pomoću miša!

Ako želite kontrolirati sve nijanse i suptilnosti, onda možete koristiti ne uobičajenu, već posebnu pastu odabirom Edit> Paste Special iz izbornika. U tom slučaju, Microsoft Excel će prikazati dijaloški okvir koji vam omogućuje da konfigurirate gdje i kako će se točno dodati novi podaci:

Slično, možete jednostavno izraditi grafikon koristeći podatke iz različitih tablica iz različitih radnih listova. Za izvođenje istog zadatka na klasičan način bit će potrebno puno više vremena i truda.

Punjenje praznih ćelija

Nakon izvoza izvješća iz nekih programa u Excel format ili prilikom izrade zaokretnih tablica, korisnici često završe s tablicama s praznim ćelijama u nekim stupcima. Ove praznine ne dopuštaju vam da na tablice primijenite poznate i prikladne alate poput automatskog filtriranja i sortiranja. Naravno, postoji potreba da se praznine popune vrijednostima iz viših ćelija:

Naravno, s malom količinom podataka, to se lako može učiniti jednostavnim kopiranjem - ručnim rastezanjem svake ćelije zaglavlja u stupcu A do praznih ćelija. A ako tablica ima nekoliko stotina ili tisuća redaka i nekoliko desetaka gradova?

Postoji način da se ovaj problem riješi brzo i lijepo pomoću jedne formule:

Odaberite sve ćelije u praznom stupcu (tj. raspon A1:A12 u našem slučaju)

Kako biste u odabiru zadržali samo prazne ćelije, pritisnite tipku F5 i u navigacijskom prozoru koji se otvori kliknite gumb Odaberi. Vidjet ćete prozor koji vam omogućuje da odaberete koje ćelije želimo istaknuti:

Postavite radio gumb na Prazan i kliknite U redu. Sada u odabiru trebaju ostati samo prazne ćelije:

Bez promjene odabira, t.j. bez dodirivanja miša, unesite formulu u prvu odabranu ćeliju (A2). Pritisnite znak jednakosti, a zatim strelicu prema gore na tipkovnici. Dobivamo formulu koja se odnosi na prethodnu ćeliju:

Da biste kreiranu formulu unijeli odjednom u sve odabrane prazne ćelije, ne pritisnite tipku ENTER, već kombinaciju CTRL + ENTER. Formula će ispuniti sve prazne ćelije:

Sada ostaje samo zamijeniti formule vrijednostima ​​da biste popravili rezultate. Odaberite raspon A1:A12, kopirajte ga i zalijepite u ćelije s njihovim vrijednostima pomoću Paste Special.

Padajući popis u ćeliji

Trik koji bi, bez pretjerivanja, trebali znati svi koji rade u Excelu. Njegova uporaba može poboljšati gotovo svaki stol, bez obzira na njegovu namjenu. Na svim treninzima pokušavam to pokazati svojim slušateljima već prvog dana.

Ideja je vrlo jednostavna – u svim slučajevima kada morate unijeti podatke iz nekog skupa, umjesto ručnog unosa ćelije s tipkovnice, odaberite željenu vrijednost mišem s padajućeg popisa:

Odabir proizvoda iz cjenika, imena kupca iz baze kupaca, punog imena zaposlenika s popisa osoblja itd. Postoji mnogo namjena za ovu značajku.

Za izradu padajućeg popisa u ćeliji:

Odaberite ćelije u kojima želite stvoriti padajući popis.

Ako imate Excel 2003 ili stariji, odaberite Podaci> Provjera valjanosti s izbornika. Ako imate Excel 2007/2010, idite na karticu Podaci i kliknite gumb Provjera valjanosti podataka.

U prozoru koji se otvori odaberite opciju Popis s padajućeg popisa.

U polju Izvor morate navesti vrijednosti koje bi trebale biti na popisu. Evo mogućih opcija:

Unesite opcije teksta u ovo polje odvojene točkom i zarezom

Ako je raspon ćelija s početnim vrijednostima na trenutnom listu, dovoljno je samo ga odabrati mišem.

Ako se nalazi na drugom listu ove knjige, morat će unaprijed dati ime (odaberite ćelije, pritisnite CTRL + F3, unesite naziv raspona bez razmaka), a zatim upišite ovo ime u polje