Straipsniai / Excel

Excel

Excel formulės: 15 dažniausiai naudojamų su pavyzdžiais

Simona Klovaitė · · 7 min. skaitymo

Ranka su rašikliu virš balto lapo, šalia skaičiuotuvas ir planšetė ant šviesaus medinio stalo

Excel'yje yra keli šimtai funkcijų, bet daugumai kasdienių darbų užtenka penkiolikos: SUM, AVERAGE, MIN ir MAX, COUNT ir COUNTA, ROUND, IF, SUMIFS, COUNTIFS, XLOOKUP, IFERROR, TODAY, NETWORKDAYS, TRIM, TEXTJOIN ir UNIQUE. Jei moki šias ir supranti, kada langelio nuorodą reikia užrakinti su $, susitvarkysi su ataskaitomis, sąskaitomis, grafikais ir sąrašais be nuolatinio googlinimo.

Žemiau – kiekviena formulė su vienu pavyzdžiu iš tos pačios lentelės. Pavyzdžio failą rasi nemokamuose prieduose, tad gali ne tik skaityti, bet ir bandyti.

Kodėl ne 400, o 15

Excel funkcijų sąrašas atrodo kaip žodynas. Ir mokytis jo kaip žodyno – beviltiška: išmoksti, nepanaudoji, pamiršti. Daug paprasčiau išmokti tas, kurios sprendžia tavo pasikartojančius darbus: susumuoti, suskaičiuoti, palyginti, surasti, sutvarkyti tekstą.

Čia ta pati logika, kaip ir su visais įrankiais: įrankis pats savaime nieko nepadaro. Svarbu, kad jį naudotum. Penkiolika formulių, kurias naudoji kasdien, verta daugiau nei šimtas, kurias kažkada matei.

Pavyzdžio lentelė

Visi pavyzdžiai remiasi viena lentele „Užsakymai“ (duomenys – bandomieji):

ABCDEFG
1DataĮmonėPrekėKiekisKainaSumaBūsena
22026-10-01Įmonė AVaržtai1615,99=D2*E2Apmokėta
32026-10-03Įmonė BKlijai354,99=D3*E3Laukiama
42026-10-08Įmonė ARankena288,99=D4*E4Apmokėta
……………………

Duomenys eina iki 100 eilutės.

Prieš formules: dvi taisyklės

1. Formulė visada prasideda =. Be lygybės ženklo Excel tai laiko tekstu.

2. Kartais nuorodą reikia užrakinti su $. Kai formulę tempi žemyn, Excel pats keičia nuorodas: D2 tampa D3, D4 ir t. t. Tai patogu, kol nereikia, kad viena nuoroda liktų vietoje. Pavyzdžiui, PVM tarifas yra langelyje J1. Formulė =F2*$J$1 tempiama žemyn visada ims J1. Greitasis klavišas – F4, kai žymeklis stovi ant nuorodos.

Dar viena smulkmena: jei tavo kompiuteryje dešimtainis skyriklis yra kablelis (kaip Lietuvoje), Excel funkcijos argumentus atskiria kabliataškiu ;. Taip rašau ir čia. Jei tavo Excel'yje formulė su ; neveikia, keisk į kablelį.

Skaičiavimas: 5 pagrindinės

1. SUM – susumuoti

=SUM(F2:F100) – visų užsakymų suma.

Greičiausias būdas – pažymėti langelį po stulpeliu ir paspausti Alt + = (Windows) arba Cmd + Shift + T (Mac). Excel pats pasiūlys sritį.

2. AVERAGE – vidurkis

=AVERAGE(F2:F100) – vidutinė užsakymo suma.

Atkreipk dėmesį: AVERAGE praleidžia tuščius langelius, bet įskaičiuoja nulius. Jei vietoj „nėra duomenų“ įrašai 0, vidurkis bus mažesnis.

3. MIN ir MAX – mažiausia ir didžiausia reikšmė

=MAX(F2:F100) – didžiausias užsakymas, =MIN(A2:A100) – pirmo užsakymo data. Datos Excel'yje yra skaičiai, todėl MIN ir MAX veikia ir su jomis.

4. COUNT ir COUNTA – kiek yra įrašų

=COUNT(D2:D100) skaičiuoja tik langelius su skaičiais. =COUNTA(B2:B100) – visus netuščius langelius, ir su tekstu. Užsakymų skaičiui naudok COUNTA ant stulpelio, kuris visada užpildytas (pvz., „Įmonė“).

5. ROUND – suapvalinti

=ROUND(F2*1,21;2) – suma su PVM, suapvalinta iki centų.

Skirtumas svarbus: langelio formatas tik rodo du skaičius po kablelio, o ROUND iš tikrųjų suapvalina. Kai sumuoji daug eilučių su PVM, be ROUND galutinė suma gali skirtis centu nuo sąskaitos.

Sąlygos: kai atsakymas priklauso nuo situacijos

6. IF – jei taip, tai…, jei ne, tai…

=IF(F2>500;"Reikia patvirtinimo";"")

Struktūra visada ta pati: =IF(sąlyga;ką rodyti, jei taip;ką rodyti, jei ne). Tekstas rašomas kabutėse, tuščios kabutės "" reiškia „nieko nerodyti“.

7. SUMIFS – sumuoti pagal sąlygas

=SUMIFS(F2:F100;B2:B100;"Įmonė A";G2:G100;"Apmokėta") – kiek Įmonė A jau sumokėjo.

Pirmas argumentas – ką sumuoji, toliau poromis: kur ieškoti ir ko ieškoti. Galima dėti ir skaičių sąlygas: ">500". Yra ir SUMIF (viena sąlyga), bet SUMIFS veikia ir su viena, tad užtenka įsiminti ją vieną.

8. COUNTIFS – skaičiuoti pagal sąlygas

=COUNTIFS(G2:G100;"Laukiama") – kiek užsakymų dar neapmokėta.

Ta pati logika kaip SUMIFS, tik nereikia sumuojamos srities. Patogu su datomis: =COUNTIFS(A2:A100;">="&DATE(2026;10;1)) – kiek užsakymų nuo spalio 1 d.

Paieška: kai duomenys kitoje lentelėje

9. XLOOKUP – surasti reikšmę

Turi atskirą lapą „Kainos“ su prekių kainomis. Užsakymų lentelėje nori, kad kaina atsirastų pati:

=XLOOKUP(C2;Kainos!A:A;Kainos!B:B)

Ieškai prekės pavadinimo (C2) kainų lapo A stulpelyje ir grąžini, kas parašyta B stulpelyje. XLOOKUP yra naujesnė VLOOKUP sesuo – paprastesnė ir patikimesnė. Kuo jos skiriasi ir kada kurią rinktis, išsamiai rašiau straipsnyje apie VLOOKUP ir XLOOKUP.

10. IFERROR – vietoj klaidos parodyti tai, ką nori

=IFERROR(XLOOKUP(C2;Kainos!A:A;Kainos!B:B);"Nėra kainos")

Kai prekės kainų lape nėra, vietoj #N/A matysi aiškų užrašą. Tik nenaudok IFERROR klaidoms paslėpti, kurių nesupranti – pirmiausia išsiaiškink, kodėl formulė lūžta.

Datos

11. TODAY – šiandienos data

=TODAY() – visada rodo šiandienos datą ir atsinaujina, kai atidarai failą. Naudinga skaičiuojant, kiek dienų praėjo: =TODAY()-A2 – prieš kiek dienų gautas užsakymas.

12. NETWORKDAYS – darbo dienos tarp dviejų datų

=NETWORKDAYS(A2;TODAY();$J$2:$J$20) – kiek darbo dienų užsakymas laukia.

Savaitgalius Excel atmeta pats, o trečias argumentas – švenčių dienų sąrašas, kurį įsirašai atskirame stulpelyje. Jei tavo savaitgalis ne šeštadienis ir sekmadienis, yra NETWORKDAYS.INTL – ją naudoju darbo grafiko šablone.

Tekstas

13. TRIM – nuvalyti tarpus

=TRIM(B2) – pašalina tarpus pradžioje, pabaigoje ir dvigubus tarpus viduryje.

Kai duomenis kopijuoji iš kitos sistemos, „Įmonė A“ ir „Įmonė A “ (su tarpu gale) Excel'iui yra du skirtingi dalykai. Dėl to SUMIFS ir XLOOKUP „neranda“ to, kas akivaizdžiai yra.

14. TEXTJOIN – sujungti tekstą

=TEXTJOIN(" – ";TRUE;B2;C2) grąžins „Įmonė A – Varžtai“. Pirmas argumentas – skyriklis, antras – ar praleisti tuščius langelius.

Dviem langeliams užtenka ir &: =B2&" – "&C2.

15. UNIQUE – unikalių reikšmių sąrašas

=UNIQUE(B2:B100) – visų įmonių sąrašas be pasikartojimų. Šalia gali dėti SUMIFS ir per minutę turėsi suvestinę pagal įmones.

UNIQUE veikia Microsoft 365, Excel 2021 ir naujesnėse versijose. Senesnėse versijose tą patį padarysi per Duomenys → Šalinti dublikatus.

Visos 15 vienoje lentelėje

FormulėKą daroPavyzdys
SUMSumuoja=SUM(F2:F100)
AVERAGEVidurkis=AVERAGE(F2:F100)
MIN / MAXMažiausia / didžiausia=MAX(F2:F100)
COUNT / COUNTAKiek skaičių / kiek įrašų=COUNTA(B2:B100)
ROUNDApvalina=ROUND(F2*1,21;2)
IFJei – tai=IF(F2>500;"Tvirtinti";"")
SUMIFSSumuoja pagal sąlygas=SUMIFS(F:F;B:B;"Įmonė A")
COUNTIFSSkaičiuoja pagal sąlygas=COUNTIFS(G:G;"Laukiama")
XLOOKUPSuranda reikšmę=XLOOKUP(C2;Kainos!A:A;Kainos!B:B)
IFERRORKlaidos vietoje – tavo tekstas=IFERROR(…;"Nėra")
TODAYŠiandienos data=TODAY()-A2
NETWORKDAYSDarbo dienos=NETWORKDAYS(A2;TODAY())
TRIMValo tarpus=TRIM(B2)
TEXTJOINJungia tekstą=TEXTJOIN(" – ";TRUE;B2;C2)
UNIQUEUnikalus sąrašas=UNIQUE(B2:B100)

Klaidų žodynas: ką Excel bando pasakyti

Klaida formulėje nėra bausmė – tai Excel'io būdas pasakyti, ko jam trūksta. Dažniausios:

KlaidaKą reiškiaKą tikrinti
#DIV/0!Dalyba iš nulioAr daliklis ne tuščias langelis ir ne 0
#N/ANerastaPaieškos reikšmė, tarpai, skaičius kaip tekstas
#NAME?Neatpažintas pavadinimasFunkcijos rašyba, kabutės aplink tekstą, Excel versija
#VALUE!Netinkamas duomenų tipasAr skaičiavime nepateko tekstas
#REF!Nuoroda dingoAr neištrynei eilutės ar stulpelio, į kurį rodė formulė
####Netelpa į langelįTai ne klaida – praplatink stulpelį

Jei formulė ilga ir neaišku, kurioje vietoje lūžta, pažymėk langelį ir naudok Formulės → Įvertinti formulę. Excel parodys skaičiavimą žingsnis po žingsnio.

Kad formulės nelūžtų: paversk duomenis lentele

Viena gudrybė, kuri išsprendžia pusę problemų: pažymėk duomenis ir paspausk Ctrl + T (Mac – Cmd + T). Duomenų sritis tampa Excel lentele.

Ką tai duoda:

  • Naujos eilutės įsitraukia pačios. Įrašai užsakymą 101 eilutėje – SUM ir SUMIFS jį jau mato, nereikia keisti F2:F100 į F2:F101.
  • Formulės nusitempia pačios. Įrašai formulę vienoje stulpelio eilutėje – ji atsiranda visame stulpelyje.
  • Formulės tampa skaitomos. Vietoj =SUM(F2:F100) matai =SUM(Užsakymai[Suma]). Po pusės metų atsidaręs failą iš karto suprasi, ką skaičiuoja.

Jei pildai failą kas savaitę, šis žingsnis dažnai svarbesnis už bet kurią naują funkciją.

Kaip neprivelti ir neprisigalvoti

Kai išmoksti kelias funkcijas, kyla pagunda skaičiuoti viską. Prieš rašydamas naują formulę, paklausk savęs: ar tai atsakys į klausimą, kurį man tikrai užduoda? Kiek Įmonė A skolinga, kiek užsakymų laukia, kuris mėnuo buvo geriausias. Jei atsakymo niekas nenaudos, formulė tik apsunkins failą. Lentelė turi padėti priimti sprendimą, o ne gražiai atrodyti.

Jei ieškojai „excel formulės pdf“ – šią lentelę kaip vieno lapo atmintinę rasi kartu su pavyzdžio failu nemokamuose prieduose.

Mano patarimas: nesimokyk visų penkiolikos iš karto. Pažiūrėk į failą, su kuriuo dirbi dažniausiai, ir paklausk savęs: ką čia darau rankomis kiekvieną savaitę? Skaičiuoju su skaičiuotuvu ir perrašau? Tai SUM arba SUMIFS. Ieškau kainos kitame faile? XLOOKUP. Pradėk nuo vienos formulės, kuri išspręs tą vieną pasikartojantį darbą. Kai ji taps įprasta, imk kitą.

Užduotis tau

  1. Atsidaryk failą, kurį pildai kiekvieną savaitę.
  2. Surask vieną veiksmą, kurį darai rankomis (sumuoji, skaičiuoji, ieškai, perrašai).
  3. Pakeisk jį viena formule iš sąrašo.
  4. Įsirašyk į kalendorių 15 min. bloką kitai savaitei tuo pačiu metu – kitai formulei.

Dažni klausimai

Kuo skiriasi formulė nuo funkcijos Excel'yje?

Formulė – viskas, kas langelyje prasideda =, pavyzdžiui, =D2*E2. Funkcija – Excel'io paruoštas skaičiavimas su pavadinimu, pavyzdžiui, SUM ar XLOOKUP. Funkcija visada yra formulės dalis, bet ne kiekviena formulė turi funkciją.

Kodėl Excel formulė rodo klaidą #VALUE! arba #NAME?

#VALUE! dažniausiai reiškia, kad skaičiuojant pateko tekstas – pavyzdžiui, skaičius įrašytas su tarpu ar taškas vietoj kablelio, todėl Excel jį laiko tekstu. #NAME? reiškia, kad Excel neatpažįsta funkcijos pavadinimo: yra rašybos klaida, trūksta kabučių aplink tekstą arba tavo Excel versijoje tos funkcijos nėra (pvz., XLOOKUP ar UNIQUE senesnėse versijose).

Ar Excel formulės veikia ir Google Sheets?

Visos 15 formulių iš šio straipsnio Google Sheets yra ir rašomos taip pat. Skirtumų yra sudėtingesnėse funkcijose ir makrokomandose, bet kasdieniams darbams failą gali drąsiai atsidaryti ir Google Sheets.

Excel ABC

Jei nori šias formules išmokti nuosekliai, su pratimais ir sprendimais, jas žingsnis po žingsnio rodo Julius Barodicas Excel ABC kurse. Kitus kursus rasi kursų puslapyje.

Plačiau

Šaltiniai

Susiję straipsniai