Kursuok.lt analitika
3 Paskaita: Analitinės funkcijos

Duomenų manipuliavimas ir
loginės operacijos

Šiame skyriuje nagrinėsime esminius MS Excel įrankius, kurie leidžia analitikui transformuoti žaliavinius duomenis į pagrįstas verslo įžvalgas naudojant paieškos, logines bei agregavimo funkcijas.

1. Loginės funkcijos ir sąlyginiai skaičiavimai

Loginės funkcijos yra duomenų analizės algoritminis pagrindas, leidžiantis programinei įrangai priimti sprendimus remiantis nustatytais kriterijais. Analitiko darbe tai kritiškai svarbu ne tik paprastam duomenų filtravimui, bet ir sudėtingų verslo taisyklių automatizavimui, klaidų prevencijai bei duomenų klasifikavimui. Naudojant šias funkcijas, galima sukurti dinamiškus modelius, kurie automatiškai reaguoja į pasikeitusius veiklos rodiklius.

Pažengusiame lygmenyje ypatingas dėmesys skiriamas loginių grandinių optimizavimui. Vietoj sudėtingų, daugiasluoksnių įdėtinių funkcijų, rekomenduojama naudoti modernias konstrukcijas, kurios užtikrina skaičiuoklės skaidrumą ir lengvą klaidų paiešką. Tinkamas klaidų apdorojimas (angl. Error Handling) leidžia užtikrinti, kad galutinės ataskaitos išliktų reprezentatyvios net ir esant duomenų trūkumui šaltiniuose.

Pagrindinės funkcijos

=IF()

Vienos sąlygos tikrinimas. Jei sąlyga teisinga, grąžinama viena reikšmė, jei ne – kita.

=IFS()

Kelių sąlygų tikrinimas iš eilės. Grąžinamas rezultatas, atitinkantis pirmąją teisingą sąlygą.

=AND() / =OR()

Kombinuoja kelias sąlygas. AND reikalauja, kad visos būtų teisingos, OR – bent viena.

=IFERROR()

Apsaugo nuo klaidų rodymo (pvz., #DIV/0!). Jei formulė klysta, parodoma nurodyta reikšmė.

Praktinės užduotys (Lentelė: pardavimai_5000)

  1. Sandorio pelningumo klasifikavimas: Sukurkite stulpelį "Pelno_Statusas". Naudodami IF funkciją, pažymėkite sandorį kaip "Aukštas", jei pelnas viršija 150€, kitu atveju – "Standartinis".
  2. Prioritetinių užsakymų identifikavimas: Naudodami AND funkciją, išskirkite sandorius, kurių "Kiekis" viršija 10 vnt. IR regionas yra "Vilnius".
  3. Kelių sąlygų segmentacija: Naudodami IFS, priskirkite klientų nuolaidų kategorijas: "5% nuolaida", jei kiekis >10; "10% nuolaida", jei kiekis >20; kitu atveju – "Be nuolaidos".

2. Paieškos funkcijos: Informacijos integravimas

Verslo analitikoje duomenys dažnai būna išskirstyti per kelias duomenų bazes ar lenteles. Paieškos funkcijos tarnauja kaip pagrindinis integravimo įrankis, leidžiantis susieti transakcijų žurnalus su informaciniais žinynais pagal unikalius identifikatorius. Gebėjimas lanksčiai "pritraukti" reikiamą informaciją iš išorinių lentelių yra būtina kompetencija formuojant konsoliduotas ataskaitas.

Modernūs „Excel“ įrankiai, tokie kaip XLOOKUP, iš esmės pakeitė ankstesnius apribojimus, susijusius su duomenų kryptimi ar stulpelių indeksavimu. Šios funkcijos užtikrina, kad modelis išliktų atsparus lentelės struktūros pokyčiams (pvz., naujų stulpelių įterpimui). Suvokimas, kaip veikia indeksavimas ir atitikimo nustatymas, leidžia analitikui dirbti su dideliais duomenų masyvais be našumo nuostolių.

Pagrindinės funkcijos

=XLOOKUP()

Lanksčiausia paieška. Ieško reikšmės viename stulpelyje ir grąžina atitinkamą iš kito.

=VLOOKUP()

Vertikali paieška. Ieško kairiausiame stulpelyje ir grąžina reikšmę pagal stulpelio numerį.

=INDEX()

Grąžina konkrečią reikšmę iš nurodytos eilutės ir stulpelio susikirtimo vietos.

=MATCH()

Suranda ieškomos reikšmės poziciją (eilės numerį) sąraše.

Praktinės užduotys (Lentelės: pardavimai ir produktai)

  1. Duomenų integravimas: Pagrindinėje lentelėje pardavimai_5000, naudodami XLOOKUP ir prekės ID, sukurkite naują stulpelį "Prekės_Pavadinimas" iš failo produktai_info.
  2. Kainų priskyrimas: Naudodami VLOOKUP arba XLOOKUP, prisitraukite prekės "Vieneto_Kaina" ir suskaičiuokite bendrą "Apyvartą" (Kiekis * Kaina).
  3. Alternatyvi paieška: Išbandykite INDEX ir MATCH derinį, kad rastumėte prekės kategoriją pagal jos pavadinimą.

3. Agregavimas su sąlygomis ir dinaminiai masyvai

Agregavimo funkcijos leidžia transformuoti tūkstančius transakcijų į prasmingas sumas, vidurkius ar skaičius, skirtus verslo rezultatų apžvalgai. Skirtingai nuo bazinių operacijų, sąlyginis agregavimas leidžia atlikti specifinę analizę pagal kelis filtrus vienu metu (pvz., pardavimai tik Vilniuje ir tik Verslo klientams), nenaudojant fizinių filtrų lentelėje. Tai kritiškai svarbu kuriant automatizuotas skydines (angl. Dashboards).

Dinaminiai masyvai (angl. Dynamic Arrays) yra viena galingiausių naujųjų MS Excel savybių. Jos panaikina būtinybę rankiniu būdu kopijuoti formules ir užtikrina, kad rezultatai automatiškai išsiplėstų ar susitrauktų priklausomai nuo duomenų kiekio. Tai suteikia galimybę kurti visiškai automatizuotus unikalių reikšmių sąrašus ir realaus laiko suvestines.

Pagrindinės funkcijos

=SUMIFS()

Susumuoja skaičius pagal vieną ar kelis nustatytus kriterijus.

=COUNTIFS()

Suskaičiuoja įrašus, kurie atitinka nurodytas kelias sąlygas.

=UNIQUE()

Ištraukia unikalių (nesikartojančių) reikšmių sąrašą iš nurodyto diapazono.

=FILTER()

Išfiltruoja visą duomenų masyvą pagal nustatytas sąlygas naujame diapazone.

Praktinės užduotys (Lentelė: pardavimai_5000)

  1. Regioninė analizė: Naudodami UNIQUE, sugeneruokite unikalų regionų sąrašą. Prie kiekvieno regiono, naudodami SUMIFS, apskaičiuokite bendrą parduotą kiekį.
  2. Verslo klientų auditas: Naudodami FILTER, atskirame lape ištraukite visus pardavimus, kurie buvo skirti "Verslo" klientams ir kurių kiekis viršija 12 vnt.
  3. Statistinė suvestinė: Naudodami COUNTIFS, sužinokite, kiek pardavimų įvyko "Internetu" kanalu būtent "Kauno" regione.