Excel pažengusiems – formulės ir VBA

Kodėl Excel vis dar dominuoja duomenų pasaulyje

Kalbant apie duomenų analizę, daugelis iš karto galvoja apie Python, R ar kokią nors fancy BI platformą. Bet realybė tokia, kad didžioji dalis verslo sprendimų vis dar priimama žiūrint į Excel lapus. Ir tai nėra vien tik inercija ar senų žmonių įprotis – Excel tiesiog veikia. Greitai, intuityviai, be diegimo procedūrų ir licencijų biurokratijos.

Problema ta, kad dauguma žmonių Excel naudoja kaip pažangų skaičiuotuvą. Sudeda skaičius, spaudžia SUM, gal dar AVERAGE. O tai – tik ledkalnio viršūnė. Tikroji Excel galia prasideda ten, kur baigiasi standartiniai kursai: sudėtingose formulių kombinacijose, dinaminiuose masyvuose ir VBA automatizavime.

Šiame straipsnyje kalbėsime apie tai, kas iš tikrųjų skiria pažengusį Excel vartotoją nuo pradedančiojo. Jokių elementarių paaiškinimų – tiesiai į reikalą.

Formulių logika – ne tik sintaksė, bet ir mąstymo būdas

Viena dažniausių klaidų – žmonės mokosi formules kaip atskirus įrankius. VLOOKUP čia, IF ten. Bet pažengęs Excel vartotojas mąsto kitaip: formulė yra logikos grandinė, kurioje kiekviena funkcija yra tik dalis didesnio mechanizmo.

Paimkime paprastą pavyzdį. Tarkime, turite pardavimų duomenis ir norite rasti konkretaus vadybininko pardavimų sumą tik tam tikram produktui. Pradedantysis padarytų filtrą ir rankiniu būdu sudėtų. Pažengęs vartotojas parašytų:

=SUMPRODUCT((A2:A100="Jonas")*(B2:B100="Produktas X")*C2:C100)

SUMPRODUCT čia veikia kaip daugybinis filtras. Kiekviena sąlyga grąžina TRUE/FALSE masyvą (1 ir 0), jie dauginami tarpusavyje, ir gaunamas tikslus rezultatas. Jokio filtravimo, jokių pagalbinių stulpelių.

Dar vienas konceptas, kurį verta suprasti – masyvo formulės. Iki Excel 365 jos buvo įvedamos su Ctrl+Shift+Enter ir žymimos figūriniais skliaustais. Dabar dinaminio masyvo formulės veikia automatiškai, bet principas tas pats: formulė apdoroja ne vieną reikšmę, o visą diapazoną vienu metu.

Praktinis patarimas: prieš rašydami formulę, pirmiausia suformuluokite logiką žodžiais. „Noriu rasti visus įrašus, kur data yra šį mėnesį IR suma viršija 1000 EUR.” Tada kiekvieną loginę dalį verčiate į funkcijas. Tai žymiai efektyviau nei bandyti prisiminti sintaksę iš atminties.

XLOOKUP, LET ir kitos modernios funkcijos, kurias verta žinoti

Jei vis dar naudojate VLOOKUP kaip pagrindinį paieškos įrankį – laikas keistis. Ne todėl, kad VLOOKUP blogas, o todėl, kad XLOOKUP yra tiesiog geresnis visais atžvilgiais.

XLOOKUP sintaksė:

=XLOOKUP(ieškoma_reikšmė; paieškos_masyvas; grąžinamas_masyvas; [jei_nerasta]; [atitikimo_režimas]; [paieškos_režimas])

Pagrindiniai privalumai prieš VLOOKUP:

• Gali ieškoti ir į kairę, ne tik į dešinę
• Nereikia nurodyti stulpelio numerio – tiesiog nurodote grąžinamą diapazoną
• Galite nurodyti, ką rodyti, jei reikšmė nerasta (vietoj klaidų pranešimų)
• Palaiko atvirkštinę paiešką

Bet tikra revoliucija – funkcija LET. Ji leidžia formulėje apibrėžti kintamuosius, kas drastiškai pagerina sudėtingų formulių skaitomumą ir greitį. Pavyzdys:

=LET(
  pardavimai; C2:C100;
  tikslas; D2:D100;
  skirtumas; pardavimai - tikslas;
  AVERAGE(skirtumas)
)

Vietoj to, kad tą patį diapazoną minėtumėte kelis kartus formulėje, jį apibrėžiate vieną kartą. Excel skaičiuoja greičiau, o jūs suprantate formulę greičiau.

Taip pat verta paminėti FILTER, SORT ir UNIQUE funkcijas – jos pakeičia tai, ką anksčiau darydavote rankiniu filtru arba sudėtingomis masyvo formulėmis. FILTER leidžia dinamiškai filtruoti duomenis pagal sąlygą ir rezultatus automatiškai išsklaidyti į kaimynines ląsteles.

Sąlyginė logika – IF grandinės ir jų alternatyvos

Daugelis Excel vartotojų žino IF. Kai kurie žino, kad IF galima įdėti į kitą IF. Bet kai tų įdėtų IF tampa 5-6, formulė virsta košmaru, kurį sunku skaityti ir dar sunkiau derinti.

Klasikinis problemos pavyzdys – kategorijų priskyrimas pagal reikšmę:

=IF(A2>=90;"Puikiai";IF(A2>=75;"Gerai";IF(A2>=60;"Patenkinamai";IF(A2>=50;"Silpnai";"Nepatenkinamai"))))

Tai veikia, bet skaitomumas – nulinis. Alternatyva – IFS funkcija:

=IFS(A2>=90;"Puikiai";A2>=75;"Gerai";A2>=60;"Patenkinamai";A2>=50;"Silpnai";TRUE;"Nepatenkinamai")

Žymiai aiškiau. Bet dar elegantiškai – SWITCH arba XLOOKUP su apytiksliu atitikimu, kai dirbate su fiksuotomis kategorijomis.

Kitas svarbus konceptas – loginių operatorių kombinacijos. AND ir OR funkcijos leidžia kurti sudėtingas sąlygas:

=IF(AND(B2="Aktyvus";C2>1000;D2<>"Atšauktas");"Kvalifikuotas";"Nekvalifikuotas")

Praktinis patarimas: jei formulėje turite daugiau nei 3 įdėtus IF – sustokite ir pagalvokite, ar nėra geresnio sprendimo. Dažniausiai yra. Arba IFS, arba CHOOSE su indeksu, arba paieška pagalbinėje lentelėje su XLOOKUP.

Dinaminiai masyvai – Excel elgesio pasikeitimas

Excel 365 įvedė dinaminių masyvų palaikymą, ir tai iš tikrųjų pakeitė žaidimo taisykles. Anksčiau formulė grąžindavo vieną reikšmę į vieną ląstelę. Dabar formulė gali automatiškai „išsipilti” į kaimynines ląsteles tiek, kiek reikia rezultatams.

Tai vadinama spill elgesiu. Pavyzdys – jei rašote:

=SORT(UNIQUE(A2:A100))

Gausite automatiškai surūšiuotą unikalių reikšmių sąrašą, kuris dinamiškai atsinaujins, kai keisis šaltinio duomenys. Jokių papildomų veiksmų.

Svarbu žinoti: jei „išsipylimo” zonoje yra kita reikšmė, formulė grąžins klaidą #SPILL!. Tai dažna problema, kai žmonės nesupranta, kaip dinaminiai masyvai veikia. Sprendimas paprastas – išvalykite zoną, į kurią formulė bando išsipilti.

Dinaminių masyvų operatorius # (hash) leidžia referuoti į visą išsipylusį diapazoną. Jei A1 ląstelėje yra formulė, kuri išsipila į A1:A20, galite rašyti A1# kitoje formulėje, ir ji automatiškai apims visą diapazoną, net jei jo dydis keičiasi.

VBA pagrindai – kodėl verta mokytis net ir 2024-aisiais

Kiekvieną kartą, kai kas nors paskelbia „VBA miręs”, atsiranda dar vienas finansų analitikas, kuris automatizuoja mėnesio ataskaitą ir sutaupo 4 valandas per savaitę. VBA niekur nedingo, ir greičiausiai nedings dar ilgai.

VBA (Visual Basic for Applications) – tai programavimo kalba, įmontuota į Office programas. Ji leidžia automatizuoti bet ką, ką galite padaryti rankiniu būdu Excel, ir dar daugiau.

Pradėti reikia nuo makrorekorderiaus. Įjunkite jį (Developer → Record Macro), atlikite veiksmus rankiniu būdu, sustabdykite įrašymą ir pažiūrėkite, kokį kodą Excel sugeneravo. Tai geriausias būdas suprasti VBA sintaksę be jokių vadovėlių.

Bazinė VBA struktūra atrodo taip:

Sub ManoMakro()
    ' Čia rašomas kodas
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Duomenys")
    
    ws.Range("A1").Value = "Sveiki, Excel!"
    
End Sub

Keletas esminių konceptų, kuriuos reikia suprasti nuo pat pradžių:

• Objektų hierarchija: Application → Workbook → Worksheet → Range → Cell. Kiekvienas objektas turi savybes ir metodus.
• Kintamieji: visada deklaruokite kintamuosius su Dim ir naudokite Option Explicit modulio viršuje – tai padės išvengti klaidų.
• Ciklai: For Each ir For...Next – pagrindiniai iteracijos įrankiai.
• Sąlygos: If...Then...Else veikia panašiai kaip Excel formulėse.

VBA praktikoje – realūs automatizavimo scenarijai

Teorija gerai, bet pažiūrėkime, ką VBA iš tikrųjų leidžia daryti kasdieniniame darbe.

Scenarijus 1: Automatinis ataskaitos generavimas

Tarkime, kiekvieną pirmadienį turite paimti duomenis iš vieno lapo, juos apdoroti ir sukurti suvestinę kitame lape. Rankiniu būdu – 30 minučių. Su VBA – 10 sekundžių.

Sub GeneruotiAtaskaita()
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim suma As Double
    
    Set wsData = ThisWorkbook.Sheets("Duomenys")
    Set wsReport = ThisWorkbook.Sheets("Ataskaita")
    
    ' Išvalome seną ataskaitą
    wsReport.Range("A2:D1000").ClearContents
    
    ' Randame paskutinę eilutę su duomenimis
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    
    ' Kopijuojame ir apdorojame duomenis
    For i = 2 To lastRow
        If wsData.Cells(i, "C").Value > 0 Then
            wsReport.Cells(i - 1, "A").Value = wsData.Cells(i, "A").Value
            wsReport.Cells(i - 1, "B").Value = wsData.Cells(i, "B").Value
            wsReport.Cells(i - 1, "C").Value = wsData.Cells(i, "C").Value
        End If
    Next i
    
    MsgBox "Ataskaita sugeneruota!", vbInformation
End Sub

Scenarijus 2: Duomenų validacija ir taisymas

Gaunate duomenis iš išorinės sistemos, kur datos formatuotos kaip tekstas, vardai rašomi didžiosiomis raidėmis, o kai kurie laukai tušti. VBA gali viską sutvarkyti per sekundes:

Sub TvarkytiDuomenis()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False ' Greičiau veiks
    
    For i = 2 To lastRow
        ' Konvertuojame datą
        If IsDate(ws.Cells(i, "B").Value) Then
            ws.Cells(i, "B").Value = CDate(ws.Cells(i, "B").Value)
            ws.Cells(i, "B").NumberFormat = "yyyy-mm-dd"
        End If
        
        ' Tvarkome vardą
        ws.Cells(i, "A").Value = Application.WorksheetFunction.Proper(ws.Cells(i, "A").Value)
        
        ' Pažymime tuščius laukus
        If ws.Cells(i, "C").Value = "" Then
            ws.Cells(i, "C").Interior.Color = RGB(255, 200, 200)
        End If
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "Duomenys sutvarkyti! Eilučių: " & lastRow - 1
End Sub

Svarbus patarimas: visada naudokite Application.ScreenUpdating = False prieš didelę kilpą ir True po jos. Tai gali pagreitinti kodą 10-20 kartų, nes Excel nepiešia kiekvieno žingsnio ekrane.

Kitas svarbus dalykas – klaidų apdorojimas. Niekada nerašykite VBA kodo be On Error GoTo arba bent On Error Resume Next (nors pastarasis – tik kai tikrai žinote, ką darote). Nekontroliuojamos klaidos gali sugadinti duomenis arba palikti Excel nestabilios būsenos.

Kai Excel ir VBA susitinka su realiu pasauliu

Pažengęs Excel naudojimas – tai ne tik žinojimas, kaip rašyti sudėtingas formules ar VBA kodą. Tai supratimas, kada naudoti kurį įrankį, ir gebėjimas matyti visą sistemą.

Keletas praktinių rekomendacijų, kurias išmokstate tik per patirtį:

Duomenų struktūra pirmiausia. Jokia formulė ar VBA makro neišgelbės blogai struktūruotų duomenų. Prieš rašydami bet ką, įsitikinkite, kad duomenys yra lentelės formatu (ne suvestinės), kiekvienas stulpelis turi aiškią reikšmę, ir nėra sujungtų ląstelių (jos – tikras pragaras automatizavimui).

Excel lentelės (Tables) – ne prabanga, o būtinybė. Ctrl+T paverčia diapazoną į struktūruotą lentelę. Formulės automatiškai išsiplečia naujoms eilutėms, nuorodos tampa aiškesnės (Lentelė1[Suma] vietoj C2:C100), ir VBA kodas tampa patikimesnis.

Dokumentuokite savo VBA kodą. Po trijų mėnesių jūs patys neprisiminkite, ką darė ta 200 eilučių procedūra. Komentarai su apostrofu (') nieko nekainuoja, bet sutaupo daug laiko ateityje.

Testuokite su mažais duomenimis. Prieš paleidžiant makro su 50 000 eilučių, išbandykite su 50. Klaidas lengviau rasti, o jei kas nors negerai – neprarasite daug laiko.

Naudokite Named Ranges. Vietoj to, kad formulėse rašytumėte $A$1:$A$100, suteikite diapazonui vardą per Name Manager. Formulės tampa skaitomesnės, o jei diapazonas keičiasi – keičiate tik vienoje vietoje.

Galiausiai – Excel ir VBA mokymasis yra iteratyvus procesas. Nėra momento, kai galite pasakyti „dabar žinau viską”. Kiekvieną kartą, kai sprendžiate naują problemą, atraskite naują funkciją ar techniką. Geriausi Excel vartotojai, kuriuos esu matęs, nėra tie, kurie žino visas funkcijas mintinai – tai tie, kurie žino, kaip greitai rasti sprendimą ir suprasti, kaip jis veikia. O tai jau ne Excel klausimas – tai mąstymo būdas.