Duomenų higiena ir atomizacija
Duomenų higiena yra pamatas, ant kurio statoma visa analitika. Dirbant su 5000 eilučių masyvu, net 1% klaidų (pvz., skirtingi miesto trumpiniai) reiškia 50 klaidingų įrašų, kurie iškraipys galutines suvestines. Pagrindinis principas čia yra atomizacija - informacijos išskaidymas iki mažiausių loginių vienetų, kad kiekviename stulpelyje būtų tik viena duomenų rūšis.
Šiame skyriuje naudosime Flash Fill (Ctrl+E) - įrankį, kuris pagal pavyzdį atpažįsta
duomenų struktūrą ir ją pritaiko visam stulpeliui. Tai idealus būdas tvarkyti stulpelį Raw_Record,
kuriame sujungta data, ID ir miestas. Taip pat pasitelksime Find & Replace su „Wildcards“
simboliais (*), kurie leidžia masiškai suvienodinti miestų pavadinimus.
Užduotis #1: Higienos testas (5000 eilučių)
1. Stulpelyje Raw_Record sukurkite naują stulpelį „Sąskaitos ID“. Įveskite pirmą reikšmę ir paspauskite Ctrl + E.
2. Stulpelyje Miestas suvienodinkite trumpinius (pvz., „Vln.“, „Kaun.“, „Klaip.“) naudodami Find & Replace (Ctrl+H).
Dinaminė analizė su SUBTOTAL
Kai duomenų masyvas didelis, standartinė SUM funkcija tampa pavojinga, nes skaičiuoja viską,
net ir paslėptas eilutes. Profesionalus analitikas naudoja SUBTOTAL su kodu 109,
kad būtų skaičiuojamos tik matomos, filtruotos reikšmės.
Kartu su Slicers gaunamas interaktyvus filtravimo modelis, kuris leidžia greitai analizuoti duomenis pagal kategoriją ar regioną.
Užduotis #2: Dinaminė suvestinė
1. Virš lentelės sukurkite formulę: =SUBTOTAL(109; [Pardavimo_Suma]).
2. Įterpkite Slicer stulpeliui Kategorija ir stebėkite, kaip automatiškai persiskaičiuoja suma.
Struktūros keitimas ir grupavimas
Duomenys dažnai atkeliauja „supakuoti“. Stulpelyje Skaidymo_Laukas esantys įrašai gali būti
skaidomi per Text to Columns pagal skyriklį. Tai tipinė ETL proceso dalis.
Grupavimas (Group/Outlining) leidžia valdyti informacijos tankumą, suskleisti detales ir matyti mėnesio ar ketvirčio lygio suvestines.
Pradiniai duomenys: teksto sujungimo funkcijos
& - teksto jungimo operatorius, sujungia kelis tekstus.
UPPER - tekstą paverčia DIDŽIOSIOMIS raidėmis.
LOWER - tekstą paverčia mažosiomis raidėmis.
PROPER - kiekvieną žodį pradeda didžiąja raide.
LEFT - grąžina n simbolių iš kairės.
MID - nuo n-tos pozicijos grąžina m simbolių.
RIGHT - grąžina n simbolių iš dešinės.
LEN - suskaičiuoja teksto simbolių kiekį.
FIND - suranda teksto pradžios poziciją.
REPLACE - nuo n-tos pozicijos pakeičia m simbolių kitu tekstu.
Išskaidymo pavyzdžiai
1. Sąrašas su vardais ir pavardėmis
Jonas Petrauskas
Antanas Kazlauskas
Ona Praulienė
Ludvikas van Beethoven
2. Automobilių numerių sąrašas
EEN628
AEK753
KBL239
Užduotis #3: Skaidymas ir grupavimas
1. Stulpelį Skaidymo_Laukas suskaidykite į 3 dalis per Data -> Text to Columns, naudodami „Semicolon“ (;).
2. Sugrupuokite duomenis pagal stulpelį Data pasirinkdami „Months“ ir „Years“.
Išplėstinis sąlyginis formatavimas
Sąlyginis formatavimas su formulėmis leidžia greitai aptikti anomalijas dideliuose duomenų masyvuose. Pavyzdžiui, galima automatiškai nuspalvinti visą eilutę, jei pelnas neigiamas.
Naudojant mišriąsias nuorodas (pvz., =$I2<0) taisyklė pritaikoma teisingai visam diapazonui.
Užduotis #4: Anomalijų aptikimas
1. Sukurkite stulpelį „Pelnas“ (Pardavimo_Suma - Sąnaudos).
2. Visam duomenų diapazonui pritaikykite taisyklę su formule: =$I2<0 ir pažymėkite raudonu fonu.