Uvjetno oblikovanje (5)
Liste i rasponi (5)
Makroi (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, ali jako dugo. U ovom članku odlučio sam prikupiti nekoliko jednostavnih preporuka koje će vam pomoći da ubrzate rad VBA koda, dok je u nekim slučajevima vrlo impresivan - desetine ili čak više puta. Glavni fokus članka je na početnicima, stoga su najjednostavnije metode optimizacije predstavljene na početku članka. Više "dubokih" rješenja za optimizaciju koda dato je na kraju članka. da biste primijenili ova rješenja, potrebno vam je dovoljno iskustva u VB-u i odmah takve metode optimizacije mogu nekome biti nerazumljive.

1. Ako vaš kod sadrži puno Activate i Select, posebno u petljama, trebali biste ih se odmah riješiti. Napisao sam kako to učiniti u članku: Odaberite i aktivirajte - zašto su potrebni i jesu li potrebni?
2. Obavezno onemogućite za vrijeme trajanja koda:
   • automatsko preračunavanje formula. Tako da se formule ne preračunavaju sa svakom manipulacijom na listu tokom izvršavanja koda - to može divlje usporiti kod ako ima puno formula:

     Application.ScreenUpdating = False

   • Onemogućite praćenje događaja za svaki slučaj. Neophodno je da Excel ne izvršava nikakve procedure događaja koje se mogu nalaziti u listu u kojem su izvršene promjene. U pravilu, to su događaji promjene ćelija, aktiviranje listova itd.:

     ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

     ako se štampa unutar koda, onda je preporučljivo umetnuti ovaj red odmah iza reda koji ispisuje list (pod uslovom da se štampanje ne odvija u petlji. U ovom slučaju, nakon završetka ciklusa štampanja).

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

     Application.StatusBar = False

     Application.StatusBar = False

   Glavna stvar koju treba zapamtiti je sva ova svojstva moraju se ponovo uključiti nakon pokretanja koda... U suprotnom, može doći do problema s radom unutar programa Excel. Na primjer, ako zaboravite omogućiti automatsko ponovno izračunavanje formula - većina formula će biti preračunata isključivo prisilnom metodom - Shift+F9... A ako zaboravite da isključite osvježavanje ekrana, postoji šansa da se blokirate u radu na listovima i knjigama. Iako bi se po defaultu svojstvo ScreenUpdating trebalo vratiti na True, ako je bilo onemogućeno unutar procedure, bolje je ne nadati se tome i naviknuti se na prisilno vraćanje svih svojstava na njihova mjesta. U suštini, sve se svodi na nekoliko redova:

   "Vraćanje ažuriranja ekrana Application.ScreenUpdating = True" Vraćanje automatskog ponovnog izračuna formula Application.Calculation = xlCalculationAutomatic "Uključivanje praćenja događaja Application.EnableEvents = Tačno

   Kako ovaj kod izgleda u praksi. Pretpostavimo da želite da zapišete vrednosti u petlji od 10.000 redova:

   Sub TestOptimize () "Onemogući ažuriranje ekrana 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.ActivePager.Breaksl code = 1 do 10000 ćelija (lr, 1) .Value = lr "na primjer, samo numerirajte redove Sljedeće" Vratite ažuriranje ekrana Application.ScreenUpdating = True "Povrati automatsko ponovno izračunavanje formula Application.Calculation = xlCalculationAutomatic" Uključite praćenje Događaji aplikacije EnableEvents = True End Sub

   Poderane odštampane stranice ne moraju da se vraćaju - one u svakom slučaju usporavaju rad.

3. Treba izbjegavati petlje poput Do While za pronalaženje posljednje ćelije. Početnici često prave ovu grešku. Mnogo je efikasnije i brže izračunati posljednju ćeliju u cijelom radnom listu ili u određenoj koloni bez ove petlje Do While kočenja. Ja obično koristim

   lLastRow = Ćelije (broj redova, 1) .End (xlUp) .Row

   lLastRow = Ćelije (broj redova, 1) .End (xlUp) .Row

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

Za iskusnije korisnike VBA ponudit ću nekoliko rješenja za optimizaciju kodova u različitim situacijama:

• Najbolja optimizacija koda, ako morate direktno raditi sa ćelijama lista, obraditi ih i, eventualno, mijenjati vrijednosti, onda 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 () „Direktno kod za popunjavanje ćelija Dim arr, lr As Long "spremamo u niz u jednom potezu sve vrijednosti od 10.000 redova prve kolone arr = Ćelije (1, 1) .Promijeni veličinu (10000) .Vrijednost "ako trebate popuniti dvije ili više kolona "arr = Ćelije (1, 1) .Promijeni veličinu (10000, 2) .Vrijednost"ili "arr = Opseg (ć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" zadnja ćelija kolone A "arr = Opseg (Ćelije (3, 1), Ćelije (llastr, 2)). Vrijednost Za lr = 1 do 10000 arr (lr, 1) = lr "popunite niz rednim brojevima Sljedeći „Obrađeni niz vraćamo na list u istim ćelijamaĆelije (1, 1) .Promijeni veličinu (10000) .Vrijednost = arr End Sub

  Sub TestOptimize_Array () "Direktno kod za popunjavanje ćelija Dim arr, lr As Long" memorišemo u nizu u jednom naletu sve vrijednosti od 10000 redova prve kolone arr = Cells (1, 1) .Promijenite veličinu (10000) .Vrijednost "(! LANG: ako trebate popuniti dvije ili više kolona"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 sisteme, gde je manje memorije dodeljeno na VBA i Excel nego na 64-bitnim sistemima.

• Ako koristite brzi IF - IIF, zamijenite ga sa IF ... Onda ... Ostalo
• Također je bolje koristiti isti IF ... Onda ... Else umjesto Switch () i Shoose ()
• U većini slučajeva, bolje je provjeriti string da "nije prazan" koristeći Len () nego direktno poređenje sa prazninom: Len (s) = 0 umjesto s = "". Zbog činjenice da je rad sa stringovima mnogo sporiji nego sa numeričkim podacima
• Nemojte nepotrebno koristiti konkatenaciju nizova. Na primjer, s = "AB" će biti brži od: s = "A" & "B"
• Nemojte direktno upoređivati ​​tekstualne vrijednosti. Bolje je koristiti ugrađenu StrComp funkciju:
  Ako s s1 Tada će biti sporije od
  Ako je StrComp (s, s1, vbBinaryCompare) = 0
  a još više ako poređenje mora biti neosjetljivo na velika i mala slova:
  Ako LCase (s) LCase (s1) Tada će biti sporije od
  Ako je StrComp (s, s1, vbTextCompare) = 0
• Za ... sljedeće petlje su općenito brže od Do ... petlje
• Izbjegavajte dodjeljivanje varijablama tipa Variant. Iako je iskušenje veliko - ovaj tip zauzima puno memorije i dodatno usporava kod. Također, za objektne varijable, trebali biste izbjegavati, ako je moguće, bezlični globalni tip Object i koristiti određeni tip:

  Dim rRange kao opseg, wsSh kao radni list

• Ako radite sa nizovima, onda ovo možete eksplicitno specificirati kada deklarirate:

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

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

Naravno, ovo nisu svi trikovi i rješenja za optimizaciju. Ali u prvim parovima to bi trebalo biti dovoljno. Osim toga, uvijek treba koristiti zdrav razum. Na primjer, ako se kod izvrši za 2 sekunde, onda vjerovatno nema smisla dalje optimizirati ga. Naravno, ako ovaj kod nije jedan od onih koji samo mijenjaju vrijednost jedne ili dvije ćelije.

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

("Bottom bar" :( "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 ":" leftlide ","di texteffect " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "desno", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffect6du0" , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "desno", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffects1 "textefcs1 :" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; položaj: apsolutni; vrh: 0px; lijevo: 0px; širina: 100%; visina: 100% ; pozadina -boja: # 333333; neprozirnost: 0,6; filter: a lpha (prozirnost = 60); "," titlecss ":" prikaz: blok; položaj: relativan; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; boja: #fff; "," descriptioncss ":" prikaz: blok; 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 ": istina," texteffectresponsivesize ": 640," titlecssresponsive ":" veličina fonta: 12px; "," descriptioncssresponsive ":" display: nema! važno; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

Sadrži 32.767 znakova. Stoga funkcija brze promjene širine stupca prema njegovom sadržaju nije uvijek zgodna. :) Mada u većini slučajeva pomaže. Pomerite kursor miša preko desne ivice zaglavlja kolone i dvaput kliknite na njega tako da program odabere optimalnu širinu kolone. Ovaj trik radi za više stupaca kao i za cijeli list.

2. Kako brzo odabrati cijeli red ili kolonu

Kada radite s velikim količinama podataka, nemoguće je bez prečica na tipkovnici, uz pomoć kojih se lako kreće po listu i odabire pojedine stupce ili redove. Kombinacije Ctrl + razmaknica i Shift + razmaknica oni su odgovorni za brz izbor trenutne kolone ili reda.

Jednom sam gledao četverogodišnjaka kako koristi prečice za kopiranje i lijepljenje da unese ključ za igru. Izgleda kao nerazdvojivo trojstvo Ctrl + C, Ctrl + V i Ctrl + A apsorbuje mlada generacija zajedno sa majčinim mlekom. Usput, naredba "Odaberi sve" u Excelu ima malu funkciju: jedan klik Ctrl + A odabire trenutni raspon, a dvostruko odabire cijelu tablicu.

4. Kako napraviti izmjene na nekoliko listova u isto vrijeme

Repa na pari, tvoj izlaz! Na primjer, da biste promijenili veličinu fonta na svim listovima odjednom, morate ih grupirati u grupu tako što ćete kliknuti na kartice listova dok držite pritisnutu tipku Ctrl.

5. Kako prenijeti red u trenutnoj ćeliji

Ako želite da se sprdate sa početnikom - zamolite ga da unese podatke unutar ćelije u nekoliko redova. Naravno, školski trik, ali sa starijom generacijom radi na pravi način. Kombinacija Alt + Enter sprečava skok na drugu ćeliju i pomiče kursor na novi red unutar trenutne ćelije.

Za umetanje dodatnog reda ili kolone potrebna su tri do četiri klika. Ovo nije puno, ali samo dok vam ne zatreba desetak novih kolona 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 hotkeys Ctrl +; i Ctrl + Shift +; trebalo bi da se pušta na radiju umesto jutarnje himne. Ove kombinacije treba ponavljati sve dok na planeti ne ostane onaj vrijedni radnik koji ručno ubacuje datum i vrijeme.

• Tutorial

Predgovor

Tako se dogodilo da danas mnogo ljudi mora da radi (piše makroe) u VBA u Excelu. Neki makroi sadrže stotine linija koda koji se moraju izvršavati svaki dan (sedmica, mesec, kvartal, itd.), a ipak im je potrebno dosta vremena. Čini se da je proces automatiziran i da nema potrebe za ljudskom intervencijom, ali vrijeme potrebno za izvršavanje makroa može potrajati desetine minuta, pa čak i nekoliko sati. Vrijeme je, kako kažu, novac, a u ovom postu ću pokušati značajno ubrzati vrijeme izvršavanja vašeg makroa i, možda, to će se pozitivno odraziti na vaše poslovanje, a na kraju i novac.

Prije početka rada

Pre nego što pređemo direktno na stvar, želeo bih da vam skrenem pažnju na post: Nekoliko saveta za rad sa VBA u Excel-u. Konkretno, blok “Ubrzavanje makronaredbi” sadrži neke korisne primjere koda koje biste trebali koristiti zajedno s mojim savjetima o ubrzavanju vašeg rada za maksimalne rezultate.

Ubrzajte makro

Dakle, do tačke... Da biste zaista ubrzali rad VBA u Ecxelu, morate shvatiti da pristup ćeliji na listu oduzima dosta vremena. Ako želite da upišete jednu vrijednost u ćeliju, onda to neće trebati puno vremena, ali ako trebate napisati (čitati, pristupiti) hiljadama ćelija, biće potrebno mnogo više vremena. Šta učiniti u takvim slučajevima? Nizovi dolaze u pomoć. Nizovi se čuvaju u memoriji, a VBA obavlja operacije u memoriji stotine, ako ne i hiljade puta brže. Stoga, ako imate hiljade, stotine hiljada vrijednosti u vašim podacima, tada vrijeme izvršavanja makroa može potrajati od nekoliko minuta do nekoliko sati, a ako se ti podaci prenesu u niz, tada se izvršavanje makroa može smanjiti na nekoliko sekundi (minuta).

Dat ću vam primjer koda i objasniti šta je što u komentarima, pa će vam biti jasnije. Osim toga, neke linije koda koje nisu direktno povezane s procesom ubrzanja mogu biti korisne.

Primjer

Pretpostavimo da imamo podatke na “Sheet1”. Podaci se nalaze u 50 kolona (kolone sadrže naslove) i 10.000 redova. Na primjer, u posljednju kolonu trebamo unijeti vrijednost koja je jednaka vrijednosti u drugoj koloni podijeljenoj s vrijednošću u trećoj koloni (počevši od 2. reda, jer prva sadrži naslov). Zatim ćemo uzeti prvih 10 kolona i kopirati ih u “Sheet2” za dalju obradu (za druge potrebe). Neka primjer bude trivijalan, ali, kako mi se čini, može odraziti cijeli smisao ovog posta.

"Da eksplicitno inicijalizirate varijable, omogućite ovu opciju" Ovo će pomoći da se izbjegnu mnoge greške Opcija Eksplicitni podtest () "Pristupit ćemo listovima kroz varijable Dim Sheet1_WS, Sheet2_WS kao radni list" Varijabla za prenošenje termina na listu (u array) Dim i As Long " Niz u kojem će biti pohranjeni naši podaci Dim R_data As Variant "Varijable posljednjeg reda i kolone Dim FinalRow, FinalColumn As Long" Možete inicijalizirati list ne imenom, već rednim brojem "Set Sheet1_WS = Application.ThisWorkbook.Worksheet (" Sheet1 ") Set Sheet1_WS = Application.ThisWorkbook.Sheets (1) Set Sheet2_WS = Application.ThisWorkbook.Sheets (2) "Traži posljednju nepraznu liniju u prvoj koloni" To je potrebno je da se podaci ne filtriraju, inače će zadnji red biti zadnji red u filteru " Takođe, u posljednjem redu, u prvoj koloni, ne smije biti prazna ćelija. Naravno, ako u ovom ima podataka U suprotnom, zadnji red će biti posljednja neprazna ćelija. FinalRow = Sheet1_WS.C ells (Rows.Count, 1) .End (xlUp) .Row "= 10 000" Pronađite posljednju nepraznu kolonu u prvom redu FinalColumn = Sheet1_WS.Cells (1, Columns.Count) .End (xlToLeft) .Column "= 50 "Dodjeljivanje raspona podataka nizu na listu 1 R_data = Sheet1_WS.Range (Sheet1_WS.Cells (1, 1), Sheet1_WS.Cells (FinalRow, FinalColumn)) Za i = 2 do FinalRow" Izvršimo operacije podataka koje potreba. "Provjera da nema dijeljenja sa nulom." Pretpostavlja se da kolone 2 i 3 sadrže numeričke podatke "U suprotnom će biti potrebno rukovanje greškama Ako R_data (i, 3)<>0 Zatim R_data (i, FinalColumn) = R_data (i, 2) / R_data (i, 3) End If Next i "Kopiraj podatke iz niza nazad na Sheet1" Prije toga, obrišite podatke na listu (ako postoje 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 = R_data. prvih 10 kolona. Sheet2 Sheet2_WS.Cells (1, 1), Sheet2_WS.Cells (FinalRow, 10)) = R_data "Zatvorite radnu knjigu i sačuvajte je Radne knjige (Application.ThisWorkbook.Name). Zatvori SaveChanges: = True End Sub

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

Dim R_new () Kao varijanta ........................................ "Izričito 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 je preporučljivo prikazati rezultat na listu, zatim izvršiti neke radnje (na primjer, sortiranje) i ponovo učitati podatke u niz.

Za mene je bilo veliko iznenađenje ubrzati rad makroa zbog nizova, pošto su podaci na listovima, zapravo, već dvodimenzionalni niz. Ali ispostavilo se da je pristup memoriji mnogo brži od ćelija na listu.

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

Hvala na pažnji. Srećan razvoj događaja.

Ekonomisti obično provode oko pola svog vremena u Excelu. Istovremeno, komunikacija sa programom mnogima od nas zadaje mnogo glavobolje. izvođenje rutinskih operacija oduzima dosta vremena. Hajde da razgovaramo o tome kako da budemo brži u Excel-u.

Praktično ne postoji alternativa Excelu. Nije iznenađujuće što vam Excel omogućava da radite gotovo sve. On je kao prazan štafelaj za umetnika. Sjedneš kod njega i počneš da slikaš svoju sliku.

Razmaz, još jedan bris... Prođe nekoliko sati i... Par malih tableta i grafikon standardnog dizajna. Tužno zar ne?

I ovo mi se desilo. Radiš, radiš. Već iscrpljena, a posao je tek počeo. Dakle, šta možete učiniti povodom toga?

Postoji izlaz! Istina, ne znaju svi za njega i zato moraju ostati do kasno na poslu, odlagati okupljanja s prijateljima, sastanke sa voljenima i jednostavno izgarati na poslu. Ne znam za vas, ali meni ne odgovara, a danas ću vam otkriti svoje tajne brzog rada u Excelu.

Zanimljivo? Idemo onda!

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

Tako ste dobro pročitali. Da biste brže radili u Excelu, morate ukloniti miša. SAMO nemojte žuriti da zatvorite karticu, sada će sve postati jasno.

Razumijem da ti je miš jako drag. Bez toga ne možete živjeti, ali vjerujte mi, to vas zaustavlja. Nisam lud, samo znam mnogo praktičniji i brži način navigacije Excel stranicama i jednostavno ne koristim ovaj divni gadžet.

Sve se radi o vrućim tipkama koje vam omogućavaju jednostavno prebacivanje između stranica munjevitom brzinom, odabir, dodavanje ili brisanje redova/kolona. Općenito, poznavanje doslovno 5-7 kombinacija ubrzava rad za red veličine. Mnoge navigacijske operacije pomoću miša traju 3-5 ili čak 10 puta duže.

Dozvolite mi da vam dam primjer. Postoje tri opcije za dodavanje više redova u Excel:

1. Opcija 1. Uzmite miš, zadržite pokazivač miša preko odgovarajuće kolone ili nekoliko kolona, ​​ovisno o tome koliko kolona trebate umetnuti. Kliknite desnim tasterom miša, izaberite zalepi u padajućem okviru.
2. Opcija 2. Korištenje dugmadi na kartici Početak u grupi Ćelije. Odaberite ponovo, idite na karticu Početna. Kliknemo na odgovarajuću dugmad da dobijemo rezultat.
3. Opcija 3. Da odaberete kolonu, pritisnite Ctrl + 'razmak', da dodate kolonu, kliknite Ctrl + ‘+’.

Može se reći da uštede nisu velike. Na jedan način ću to uraditi za 15-20 sekundi, na drugi za 3-5. I šta ću sa oslobođenih 12-15 sekundi? I općenito, koliko prečaca vam je potrebno da naučite da osjetite razliku?

Slažem se! Vrijeme ušteđeno jednim prečicama nije veliko, ali sav naš rad u Excelu je monotono ponavljanje istih operacija stotine puta u različitim sekvencama. To znači ako se pretpostavlja da su uvjetne uštede na jednoj operaciji 10 sekundi. Kada se jedna operacija ponavlja 20-30 puta dnevno, ušteda od poznavanja jedne kombinacije tipki je 3-5 minuta. To znači da poznavanje 10 vrućih tipki štedi 30-50 minuta vremena !!! Ali ovo je već cool !!!

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

"Dobar savjet!" - prigovaraš. - „A kako ih naučiti? Stanite na stolicu ispred kolega i recitujte kao poeziju?" 🙂

br. Naravno da nije tako. pokušajte pronaći svoj način učenja prečica na tastaturi. Šta se može učiniti za ovo?

Na primjer, možete uzeti listu interventnih tipki i odabrati 3-5 funkcija koje se najčešće koriste. Pokušajte ih razraditi, vježbajte kako rade. Naviknite se da ih udarate kada vam zatrebaju. S vremenom će i sami prsti ležati na tastaturi tako da možete brzo i jednostavno prelaziti između listova. Naučio ovo - idi na sljedeći, pa opet i opet.

I naravno, evo liste korisnih prečica i prečaca na tastaturi:

Prečice na tastaturi će vam svakako pomoći da brže radite u Excelu. Vježbajte ih i vaša radna brzina će se značajno povećati vrlo brzo. Ali to nije sve!

Imam još nekoliko savjeta na lageru. Spreman? Idi!

Excel savjet broj TRI. Funkcije i njihove kombinacije koje se moraju savladati

Možda zvuči otrcano, ali morate znati funkcije da biste brže radili u Excelu. Siguran sam da vi, dragi moj čitaoče, tečno poznajete Excel funkcionalnost i da možete napraviti vrlo cool izvještaje i proračune u Excelu. Međutim, moguće je da vam nisu podložne sve funkcije i da postoji nešto čemu treba težiti.

Također, svaka funkcija može imati mnogo namjena. Dakle, na primjer, znate li da funkcija SUM može sabrati vrijednosti iz različitih listova vaše radne knjige bez potrebe da birate svaku od njih zasebno? One.

Umjesto formule

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

Formula će izgledati tako

= ZBIR (list 1: list N! A1)

čemu sve ovo? Pored bogate funkcionalnosti Excela, koja je u njegovim standardnim formulama, postoji mnogo kombinacija čije poznavanje omogućava rješavanje nestandardnih zadataka. Dakle, Excel uopće nema funkciju MINESLI. Da, postoji SUMIF, COUNTIF, ali MINESLY nije. Također MAXESLI, MEDIANESLI 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 sjajno samo u kombinaciji. Ovo je ono o čemu sada govorim o INDEX-u i POISKPOS-u. Čini se da su to glupe funkcije odvojeno, ali zajedno daju odličnu funkcionalnost.

Šta je potrebno za ovo? Na primjer, pretplatite se na našu grupu u Facebook i sačekajte da se objave novi postovi. 😉

Pogledajte i listu formula na koje preporučujem da obratite pažnju:

Postoji mnogo više funkcija u Excelu, ali to za sada možete shvatiti. Siguran sam da će vam većina njih pomoći da brže radite u Excelu i da budete barem značajno efikasniji.

Excel savjet broj ČETIRI. Struktura i formatiranje u fajlovima

Ako ste se susreli sa principima modeliranja, onda očito znate da je dovoljno da stvari posložite u proračunima kako biste izbjegli greške.

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

Kako se to može izbjeći? Evo nekoliko savjeta:

• Formatirajte datoteku. Neka imate isti broj decimalnih mjesta, isti font u cijelom dokumentu i ograničenu paletu boja. Volim koristiti palete iste boje, ali različitih tonova (cijan, plava i tamno plava). Izgleda veoma sa stilom.
• Odvojeni ulazni podaci, proračuni i rezultati. To nije uvijek potrebno, ali kada ima puno podataka, prisustvo okretne ploče jednostavno štedi.
• Uradite iste formule po stupcu ili redu. ne slažu se baš ispravno kada se isti indikator u različitim periodima posmatra drugačije. Međutim, to obično nije vidljivo dok ne pogledate formulu. ali možete i zaboraviti na čast.
• Pokušajte izbjeći kružne reference i veze do vanjskih datoteka. Obično daju različite rezultate na različitim računarima. Postojao je slučaj kada je greška pri ažuriranju vanjskih veza bila veća od 7 nula. Ay!
• Pojednostavite i uštedite prostor. Excel vam omogućava da napravite onoliko redova i kolona koliko želite. U mojih 10 godina rada sa programom, nikada nisam koristio list u potpunosti. Malo je vjerovatno da ćete uspjeti, stoga nemojte ograđivati ​​složene formule. Bolje je izračunati u nekoliko koraka.

Ovo su savjeti. Vjerujte mi, oni olakšavaju život. Manje je grešaka, proračuni su precizniji i razumljiviji. ako imate još savjeta - ostavite ih u komentarima.

U ovom savjetu apsolutno nisam originalan. Čudno, ali postizanje majstorstva zavisi samo od vas i od toga koliko i naporno vežbate.

Želio bih dati intenzivnih 28 minuta, nakon kojih će svi poznavati Excel bolje nego što znaju njegovi programeri, ali nažalost. Ovo se ne dešava. Konstantan i naporan rad dovodi do zaista impresivnih rezultata. Zato vrlo brzo pokrećemo Excel obuku koja će biti dostupna na našoj web stranici besplatno (barem za prvih 1000 korisnika).

Pavlov Nikolay

U ovom članku želim da vam predstavim najefikasnije tehnike za rad u Microsoft Excel-u, koje sam prikupio tokom proteklih 10 godina rada na projektima i vođenja obuka na ovom divnom programu. Ovdje nema opisa super-kompleksnih tehnologija, ali postoje tehnike za svaki dan - jednostavne i efikasne, opisane bez "vode" - samo "suvi ostatak". Za savladavanje većine ovih primjera trebat će vam manje od jedne ili dvije minute, ali će vam pomoći da uštedite mnogo više.

Brzi skok na željeni list

Da li slučajno radite sa Excel radnim sveskama sa puno listova? Ako ih ima više od desetak, onda svaki prijelaz na sljedeći neophodan list sam po sebi postaje mali problem. Jednostavno i elegantno rješenje za takav problem je da kliknete u donjem lijevom kutu prozora na tipke za pomicanje kartica listova ne lijevom, već desnom tipkom miša - sadržaj knjige sa kompletnim Pojavit će se lista svih listova i jednim pokretom možete prijeći na željeni list:

Ovo je mnogo brže od skrolovanja kroz kartice listova sa istim dugmadima u potrazi za onim što vam treba.

Kopirajte bez oštećenja formatiranja

Koliko stotina (hiljada?) puta sam vidio ovu sliku, kako stoji iza svojih slušalaca tokom treninga: korisnik u prvu ćeliju unese formulu i onda je "razvuče" preko cijele kolone, razbijajući formatiranje redova ispod, budući da ova metoda kopira ne samo formulu, već i format ćelije. Shodno tome, dalje je potrebno ručno ispraviti štetu. Sekunda za kopiranje, a zatim 30 sekundi za popravljanje dizajna oštećenog kopiranjem.

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

Kopirajte samo vidljive ćelije

Ako radite u Microsoft Excel-u više od nedelju dana, sigurno ste se već suočili sa sličnim problemom: u nekim slučajevima se prilikom kopiranja-lepljenja ubaci više ćelija nego što je na prvi pogled kopirano. Ovo se može dogoditi ako raspon koji se kopira uključuje skrivene redove/kolone, grupe, međuzbirove ili filtriranje. Uzmimo jedan od ovih slučajeva kao primjer:

U ovoj tabeli se izračunavaju međuzbroji i redovi su grupisani po gradovima - to je lako razumeti pomoću dugmadi plus-minus sa leve strane tabele i po prekidima u numerisanju vidljivih redova. Ako selektujemo, kopiramo i zalijepimo podatke iz ove tabele na uobičajen način, dobićemo 24 dodatna reda. Želimo samo kopirati i zalijepiti ukupne vrijednosti!

Problem možete riješiti tako što ćete pomno označiti svaki red ukupnih vrijednosti dok držite pritisnutu tipku CTRL, kao što biste učinili za odabir nesusjednih raspona. Ali šta ako nema tri ili pet takvih linija, već nekoliko stotina ili hiljada? Postoji još jedan, brži i praktičniji način:

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

Pritisnite taster F5 na tastaturi, a zatim dugme Specijalno u prozoru koji se otvori.
Pojavit će se prozor koji omogućava korisniku da izabere ne sve u nizu, već samo potrebne ćelije:

U ovom prozoru odaberite opciju Samo vidljive ćelije i kliknite na OK.

Rezultirajući odabir sada se može sigurno kopirati i zalijepiti. Kao rezultat toga, dobićemo kopiju vidljivih ćelija i ubaciti samo 5 linija koje su nam potrebne umjesto nepotrebnih 29.

Ako sumnjate da ćete morati često izvoditi takvu operaciju, onda ima smisla dodati dugme na Microsoft Excel alatnu traku za brzo pozivanje takve funkcije. To se može uraditi kroz meni Alati> Prilagodi, zatim idite na karticu Komande, u kategoriji Uredi pronađite dugme Odaberi vidljive ćelije i prevucite ga na traku sa alatkama:

Pretvaranje redova u kolone i obrnuto

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

U stvari, sve je jednostavno. U onom dijelu više matematike koji opisuje matrice, postoji koncept transpozicije - radnja koja mijenja redove i stupce u matrici jedan s drugim. U programu Microsoft Excel to se radi u tri pokreta: Kopiraj tabelu

Kliknite desnim tasterom miša na praznu ćeliju i izaberite komandu Paste Special

U prozoru koji se otvori postavite oznaku Transpose i kliknite OK:

Brzo dodajte podatke u grafikon

Zamislimo jednostavnu situaciju: imate izvještaj za prošli mjesec sa vizuelnim grafikonom. Zadatak je dodati nove numeričke podatke na grafikon ovog mjeseca. Klasičan način rješavanja je otvaranje prozora izvora podataka za grafikon, gdje možete dodati novu seriju podataka unosom njenog naziva i odabirom raspona sa potrebnim podacima. A to je često lakše reći nego učiniti - sve ovisi o složenosti dijagrama.

Drugi način - jednostavan, brz i lijep - je da odaberete ćelije s novim podacima, kopirate ih (CTRL + C) i zalijepite (CTRL + V) direktno u grafikon. Excel 2003, za razliku od kasnijih verzija, čak podržava mogućnost prevlačenja odabranog raspona ćelija s podacima i ispuštanje direktno 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 ovom slučaju, Microsoft Excel će prikazati dijaloški okvir koji vam omogućava da konfigurirate gdje i kako će se dodati novi podaci:

Isto tako, možete lako kreirati grafikon koristeći podatke iz različitih tabela sa različitih listova. Biće potrebno mnogo više vremena i truda da se isti zadatak završi na klasičan način.

Popunjavanje praznih ćelija

Nakon izvoza izvještaja iz nekih programa u Excel format ili prilikom kreiranja pivot tabela, korisnici često dobijaju tabele sa praznim ćelijama u nekim kolonama. Ove praznine vas sprečavaju da primenite poznate i praktične alate kao što su AutoFilter i Sortiranje na tabele. Naravno, postaje potrebno popuniti praznine vrijednostima iz ćelija višeg nivoa:

Naravno, sa malom količinom podataka, to se može lako uraditi jednostavnim kopiranjem - ručnim povlačenjem svake ćelije zaglavlja u koloni A dole do praznih ćelija. Ali šta ako tabela sadrži nekoliko stotina ili hiljada redova i nekoliko desetina gradova?

Postoji način da brzo i lijepo riješite ovaj problem koristeći jednu formulu:

Odaberite sve ćelije u koloni sa prazninama (tj. raspon A1: A12 u našem slučaju)

Da biste u odabiru ostavili samo prazne ćelije, pritisnite tipku F5 i u prozoru za tranziciju koji se otvori, tipku Odaberi. Vidjet ćete prozor koji vam omogućava da odaberete koje ćelije želimo istaknuti:

Postavite prekidač na prazno i ​​kliknite na OK. Sada u odabiru trebaju ostati samo prazne ćelije:

Bez promjene odabira, tj. bez dodirivanja miša, unesite formulu u prvu označenu ćeliju (A2). Pritisnite znak jednakosti na tastaturi, a zatim strelicu nagore. Uzmimo formulu koja se odnosi na prethodnu ćeliju:

Da biste kreiranu formulu unijeli odjednom u sve odabrane prazne ćelije, pritisnite CTRL + ENTER umjesto tipke ENTER. Formula će popuniti sve prazne ćelije:

Sada ostaje samo zamijeniti formule vrijednostima kako biste popravili rezultate. Odaberite raspon A1: A12, kopirajte ga i zalijepite u ćelije njihove vrijednosti pomoću posebne paste.

Padajuća lista u ćeliji

Trik koji bi, bez pretjerivanja, trebali znati svi koji rade u Excelu. Njegova upotreba može poboljšati gotovo svaki sto, bez obzira na njegovu namjenu. Na svim treninzima pokušavam da to pokažem svojim slušaocima već prvog dana.

Ideja je vrlo jednostavna - u svim slučajevima kada morate unijeti podatke iz bilo kojeg skupa, umjesto ručnog unosa ćelije s tastature, odaberite željenu vrijednost mišem sa padajuće liste:

Odabir proizvoda iz cjenovnika, imena klijenta iz baze klijenata, punog imena zaposlenika iz tabele osoblja itd. Postoji mnogo opcija za korištenje ove funkcije.

Za kreiranje padajuće liste u ćeliji:

Odaberite ćelije u kojima želite da kreirate padajuću listu.

Ako imate Excel 2003 ili stariji, izaberite Podaci> Validacija iz menija. Ako imate Excel 2007/2010, idite na karticu Podaci i kliknite na dugme Provjera valjanosti podataka.

U prozoru koji se otvori izaberite opciju Lista sa padajuće liste.

U polju Izvor morate navesti vrijednosti koje bi trebale biti na listi. Evo opcija:

Unesite tekstualne opcije u ovo polje, odvojene tačkom i zarezom

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

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