Kodėl Excel vis dar karalius
Jei dirbate su duomenimis – nesvarbu, ar tai biudžeto lentelės, klientų sąrašai, ar pardavimų ataskaitos – greičiausiai Excel yra vienas iš tų įrankių, kurį atidaro kiekvieną rytą. Ir nors pastaraisiais metais atsirado daugybė alternatyvų: Google Sheets, Notion, Airtable ir dar šimtas kitų, Excel kažkodėl vis tiek lieka ant stalų. Ir ne be reikalo.
Problema ta, kad dauguma žmonių naudoja Excel kaip paprastą lentelę – suvedinėja skaičius, sumuoja stulpelius ir tiek. Tai tarsi pirkti sportinį automobilį ir važiuoti tik pirma pavara. Excel turi funkcijų, kurios gali sutaupyti valandas darbo per savaitę, automatizuoti nuobodžias užduotis ir padaryti duomenų analizę tikrai įdomią. Šiame straipsnyje – ne teorija, o konkretūs įrankiai, kuriuos galite pradėti naudoti šiandien.
VLOOKUP ir XLOOKUP – paieška, kuri keičia žaidimą
Jei kada nors teko rankiniu būdu ieškoti konkretaus įrašo tūkstantiniame sąraše, žinote, kaip tai atrodo: slinkite žemyn, žiūrite, vėl slinkite, prarandate vietą, pradedate iš naujo. VLOOKUP buvo sukurta būtent tam, kad šią kančią baigtų.
Esmė paprasta: nurodote, ko ieškote, kuriame diapazone ieškoti ir kurį stulpelį grąžinti. Pavyzdžiui, turite produktų kodų sąrašą ir atskirą lentelę su kainomis. Vietoj to, kad rankiniu būdu suvestumėte kiekvieną kainą, VLOOKUP tai padaro per sekundę:
=VLOOKUP(A2, $D$2:$F$100, 3, FALSE)Čia A2 – tai, ko ieškome, $D$2:$F$100 – lentelė, kurioje ieškome, 3 – trečias stulpelis iš tos lentelės (kainos), o FALSE reiškia, kad norime tikslaus atitikimo, ne apytikrio.
Tačiau VLOOKUP turi rimtą trūkumą: ji gali ieškoti tik į dešinę. Jei jūsų paieškos stulpelis yra dešiniau nei rezultato stulpelis – nesiseka. Čia į sceną įeina XLOOKUP, kuri pasirodė Excel 365 versijoje ir yra tiesiog geresnė versija visko:
=XLOOKUP(A2, D2:D100, B2:B100, "Nerasta")XLOOKUP ieško bet kuria kryptimi, leidžia nurodyti, ką rodyti, jei nieko nerasta, ir apskritai yra intuityvesnė. Jei turite Excel 365 – naudokite XLOOKUP ir pamirškite VLOOKUP. Jei ne – VLOOKUP vis tiek atlieka darbą.
Praktinis patarimas: visada naudokite absoliučias nuorodas (su $ ženklais) paieškos diapazonui, kad kopijuodami formulę žemyn ji nenuslinktų į neteisингą vietą.
IF, IFS ir sąlyginė logika be programavimo
IF funkcija yra viena iš tų dalykų, kurie atrodo paprastai, bet atskleidžia tikrą galią, kai pradedi jas kombinuoti. Principas elementarus: jei kažkas teisinga – daryk viena, jei ne – daryk kita.
=IF(B2>1000, "Didelis užsakymas", "Mažas užsakymas")Bet realus gyvenimas retai būna toks paprastas. Dažnai reikia kelių sąlygų. Anksčiau žmonės lipdė IF funkcijas viena į kitą kaip rusiškas matrioška lėles:
=IF(B2>5000, "A kategorija", IF(B2>1000, "B kategorija", IF(B2>500, "C kategorija", "D kategorija")))Tai veikia, bet skaityti tokią formulę po mėnesio – tikras siaubas. Štai čia IFS funkcija išgelbsti situaciją:
=IFS(B2>5000, "A kategorija", B2>1000, "B kategorija", B2>500, "C kategorija", TRUE, "D kategorija")Daug skaitomiau. Paskutinis TRUE argumentas veikia kaip „visais kitais atvejais” – tai numatytoji reikšmė.
Dar viena naudinga kombinacija – IF su AND arba OR. Pavyzdžiui, norite pažymėti eilutę tik tada, kai užsakymas didelis IR klientas iš Vilniaus:
=IF(AND(B2>1000, C2="Vilnius"), "Prioritetas", "Įprastas")Praktinis patarimas: jei formulė tampa per ilga ir sudėtinga, apsvarstykite, ar neverta ją išskaidyti į kelis tarpinio skaičiavimo stulpelius. Geriau turėti tris suprantamus stulpelius nei vieną formulę, kurios niekas nesupranta.
SUMIF, COUNTIF ir jų galingesnės sesės
Sumuoti visus skaičius stulpelyje – tai SUM. Bet ką daryti, kai norite sumuoti tik tam tikrus skaičius pagal sąlygą? Čia ateina SUMIF.
Tarkime, turite pardavimų lentelę su pardavėjų vardais ir sumomis, ir norite sužinoti, kiek iš viso pardavė Jonas:
=SUMIF(A2:A100, "Jonas", B2:B100)Pirmas argumentas – kur ieškoti sąlygos, antras – pati sąlyga, trečias – ką sumuoti. Galite naudoti ir pakaitos simbolius: „Jon*” surastų ir Joną, ir Jonathaną.
COUNTIF veikia analogiškai, tik skaičiuoja eilučių kiekį, o ne sumą:
=COUNTIF(A2:A100, "Vilnius")Bet tikroji galia – SUMIFS ir COUNTIFS (su S gale), kurios leidžia nurodyti kelias sąlygas vienu metu. Norite sužinoti, kiek Jonas pardavė tik Vilniuje ir tik sausio mėnesį?
=SUMIFS(C2:C100, A2:A100, "Jonas", B2:B100, "Vilnius", D2:D100, ">=2024-01-01", D2:D100, "<=2024-01-31")Tai jau rimta analizė be jokio papildomo įrankio. Šios funkcijos ypač naudingos, kai dirbate su dideliais duomenų masyvais ir reikia greitai gauti atsakymus į konkrečius klausimus.
Praktinis patarimas: datos sąlygose visada naudokite tekstinį formatą arba DATEVALUE funkciją, kad išvengtumėte klaidų dėl datos formato skirtumų tarp kompiuterių.
Pivot lentelės – duomenų analizė be formulių
Jei SUMIFS yra formulių kelias iki duomenų analizės, tai Pivot lentelės yra greitas kelias. Ir daugelis žmonių jų vengia, nes atrodo sudėtingai. Iš tikrųjų – tai vienas paprasčiausių ir galingiausių Excel įrankių.
Pivot lentelė leidžia paimti tūkstančius eilučių duomenų ir per kelias sekundes juos sugrupuoti, susumuoti ir vizualizuoti. Jokių formulių – tik vilkimas ir paleidimas.
Kaip sukurti: pažymėkite savo duomenis, eikite į Insert → PivotTable, pasirinkite, kur norite ją patalpinti, ir spustelėkite OK. Dešinėje pasirodys laukai – tiesiog vilkite juos į reikiamas sritis:
- Rows – kas bus eilutėse (pvz., pardavėjų vardai)
- Columns – kas bus stulpeliuose (pvz., mėnesiai)
- Values – ką skaičiuosite (pvz., pardavimų suma)
- Filters – papildomi filtrai visai lentelei
Per 30 sekundžių gausite lentelę, kuri parodys kiekvieno pardavėjo pardavimus kiekvieno mėnesio pjūviu. Tai, ką rankiniu būdu darytumėte valandą.
Pivot lentelės taip pat leidžia greitai keisti perspektyvą – tiesiog pertempkite laukus ir viskas perskaičiuojama automatiškai. Tai idealus įrankis, kai dar nežinote, kokio atsakymo ieškote, ir tiesiog norite „pavartyti" duomenis.
Praktinis patarimas: kai atnaujinate pradinius duomenis, Pivot lentelė automatiškai neatsinaujina. Reikia dešiniu pelės klavišu spustelėti ir pasirinkti „Refresh". Arba galite nustatyti automatinį atsinaujinimą failo atidarymo metu per PivotTable Options.
Sąlyginis formatavimas – kai duomenys kalba patys
Duomenys lentelėje – tai tik skaičiai ir tekstas. Bet kai pridedate spalvas, ikonėles ir duomenų juostas, staiga viskas tampa akivaizdu net žmogui, kuris pirmą kartą žiūri į jūsų ataskaitą.
Sąlyginis formatavimas (Conditional Formatting) leidžia automatiškai keisti langelio išvaizdą pagal jo reikšmę. Pavyzdžiui:
- Raudona spalva – pardavimai žemiau plano
- Žalia spalva – pardavimai viršija planą
- Geltona – artėja terminas
Tai nustatoma per Home → Conditional Formatting. Galite naudoti paruoštus šablonus (Color Scales, Data Bars, Icon Sets) arba kurti savo taisykles.
Vienas iš galingiausių naudojimo būdų – formatuoti visą eilutę pagal vieno langelio reikšmę. Pavyzdžiui, norite, kad visa eilutė taptų raudona, jei stulpelyje D yra žodis „Vėluoja". Pasirinkite visą duomenų diapazoną, sukurkite naują taisyklę su formule:
=$D2="Vėluoja"Svarbu: $ prieš D, bet ne prieš 2. Taip formulė fiksuoja stulpelį, bet leidžia eilutės numeriui kisti kiekvienai eilutei.
Praktinis patarimas: neperkraukite lentelės spalvomis. Du ar trys spalvų kodai – puiku. Dešimt skirtingų spalvų – chaosas. Mažiau yra daugiau, ypač kai lentelę reikia spausdinti ar rodyti projektoriuje.
TEXT, CONCATENATE ir duomenų formatavimo magija
Darbas su duomenimis dažnai reiškia, kad gaunate juos tokius, kokie jie yra, o ne tokius, kokie jums reikia. Datos gali būti teksto formatu, vardai ir pavardės – atskiruose stulpeliuose, kodai – su nereikalingais tarpais. TEXT ir susijusios funkcijos padeda tai sutvarkyti.
TEXT funkcija leidžia formatuoti skaičius ir datas kaip tekstą su nurodytu formatu:
=TEXT(A2, "YYYY-MM-DD")Tai naudinga, kai reikia datos teksto formatu, pavyzdžiui, kuriant failo pavadinimus ar ataskaitas.
CONCATENATE arba paprastesnis variantas su & ženklu leidžia sujungti tekstus:
=A2&" "&B2Arba sudėtingiau – sukurti pilną adresą iš atskirų laukų:
=A2&", "&B2&", "&C2&" "&D2Naujesnėse Excel versijose yra TEXTJOIN, kuri dar patogesnė, nes leidžia nurodyti skyriklį vieną kartą:
=TEXTJOIN(", ", TRUE, A2:D2)Antras argumentas TRUE reiškia, kad tuščius langelius praleisti – labai naudinga, kai ne visi laukai visada užpildyti.
Kitos naudingos tekstinės funkcijos: TRIM pašalina perteklinius tarpus, UPPER/LOWER/PROPER keičia raidžių dydį, LEFT/RIGHT/MID ištraukia teksto dalis pagal poziciją.
Praktinis patarimas: jei gaunate duomenis iš išorinių sistemų ir datos neatpažįstamos kaip datos, pabandykite DATEVALUE funkciją arba naudokite Data → Text to Columns, kad Excel teisingai interpretuotų formatą.
Kai Excel tampa tikru darbo įrankiu, o ne kančia
Visas šias funkcijas galima mokytis metų metus ir vis tiek atrasti kažką naujo. Bet svarbiausia – pradėti jas naudoti praktiškai, o ne tik skaityti apie jas. Geriausias būdas įsisavinti VLOOKUP – turėti realų problemą, kurią ji išsprendžia. Geriausias būdas suprasti Pivot lenteles – paimti savo darbo duomenis ir tiesiog pabandyti.
Keletas bendrų principų, kurie padės dirbti su Excel efektyviau: pirmiausia, laikykite duomenis švariais – vienas faktas vienoje ląstelėje, jokių sujungtų langelių duomenų srityse, nuoseklūs formatai. Antra, naudokite lentelių formatą (Insert → Table) vietoj paprastų diapazonų – tai automatiškai išplečia formules naujoms eilutėms ir pagerina Pivot lentelių darbą. Trečia, dokumentuokite sudėtingas formules komentarais, kad po pusmečio suprastumėte, ką darėte.
Excel nėra tobulas įrankis. Jis turi savo ribas – labai dideli duomenų masyvai lėtina darbą, bendradarbiavimas realiuoju laiku vis dar nėra toks sklandus kaip Google Sheets, o kai kurios analizės reikalauja Python ar SQL. Bet kasdieniam darbui su duomenimis, ataskaitoms, skaičiavimams ir vizualizacijai – Excel su šiomis funkcijomis yra viskas, ko reikia. Ir kuo geriau jas mokate, tuo mažiau laiko praleidžiate prie kompiuterio darant tai, ką galėtų daryti formulė.
