4 Skyrius: Pažangios paieškos ir duomenų struktūros

1. Duomenų įvesties kontrolė

Data Validation (Duomenų tikrinimas)

Įrankis, skirtas apriboti vartotojo įvedamos informacijos tipą ir rėžius, užtikrinant duomenų bazės švarą.

Parametrai:

Allow (Leisti): pasirenkamas tipas (sąrašas, data, skaičius).
Source (Šaltinis): nurodomas diapazonas, kurio reikšmės bus leidžiamos.
Error Alert: žinutė, kurią matys vartotojas klysdamas.

Praktinis pavyzdys:

Laukelyje, kuriame renkamas „Regionas“, nustatomas sąrašas iš stulpelio su regionų pavadinimais. Vartotojas negali įrašyti klaidingo pavadinimo, nes jį renkasi iš meniu.

2. Vardų valdymas (Defined Names)

Vardų naudojimas vietoj standartinių langelių koordinačių (pvz., `$A$2:$B$50`) leidžia kurti lengviau skaitomas formules ir lanksčias duomenų struktūras.

Name Manager (Vardų vadybininkas)

Centrinis valdymo pultas, kuriame matote visus sukurtus vardus, jų apimtis (Scope) ir nurodomas sritis. Čia galite redaguoti arba šalinti vardus.

Define Name

Rankinis vardo priskyrimas pasirinktam diapazonui. Galima nurodyti komentarą, kuris paaiškina vardo paskirtį.

Create from Selection

Galingiausias įrankis masiniam vardų kūrimui. Excel automatiškai sukuria vardus pagal antraštes (viršuje, kairėje ir pan.).

Analitiko patarimas:

Jei turite didelę lentelę su prekių grupėmis, pažymėkite ją ir naudokite "Create from Selection". Kiekvienas stulpelis gaus savo vardą pagal antraštę – tai bazė dinaminiams sąrašams kurti.

3. Paieškos funkcijų architektūra

VLOOKUP ir HLOOKUP

Klasikinės funkcijos, ieškančios vertikaliai (V) arba horizontaliai (H) pagal sutampantį raktą.

=VLOOKUP(lookup_value; table_array; col_index; [range_lookup])

lookup_value: Reikšmė, kurios ieškome (pvz. prekės kodas).

table_array: Lentelė, kurioje atliekama paieška.

col_index: Stulpelio numeris, iš kurio grąžiname rezultatą.

range_lookup: 0 (tiksli paieška) arba 1 (apytikslė).

Praktika:

Sąskaitos faktūros pildymas: įrašote kliento ID, o funkcija iš "Klientų" lapo atkelia jo adresą ir PVM kodą.

INDEX ir MATCH

Galingas derinys, leidžiantis atlikti paiešką nepriklausomai nuo stulpelių eiliškumo.

=INDEX(grąžinamas_stulpelis; MATCH(ieškoma_reikšmė; ieškomas_stulpelis; 0))

INDEX(array): sritis, kurioje yra atsakymas.

MATCH(value): suranda, kelintoje eilutėje yra ieškomas elementas.

Praktika:

Kai VLOOKUP neveikia (ieškomas stulpelis yra kairėje nuo rakto) arba dinamiškai keičiamas stulpelių kiekis.

IFERROR

Klaidų apdorojimo funkcija, užtikrinanti estetišką ataskaitos vaizdą.

=IFERROR(pagrindinė_formulė; reikšmė_jei_klaida)

Argumentai: pirmas yra skaičiavimas, antras – tekstas ar skaičius (pvz. "0" arba "Neradome"), jei įvyksta klaida.

Praktika: Naudojama visose paieškos formulėse, kad vietoje #N/A klaidų vartotojas matytų nulį arba tuščią langelį.

4. Dinaminio modeliavimo įrankiai

4.1. INDIRECT (Netiesioginė nuoroda)

Tai funkcija, kuri interpretuoja teksto eilutę kaip tikrą nuorodą į langelį arba diapazoną. Tai leidžia formulėms būti visiškai dinamiškoms.

=INDIRECT("A" & 1)
// Rezultatas: Langelio A1 reikšmė

=INDIRECT(B1)
// B1 įrašytas tekstas "Sausis" tampa nuoroda į vardą "Sausis"

Kodėl tai svarbu analitikoje?

Pagrindinis `INDIRECT` panaudojimas – priklausomi išskleidžiamieji sąrašai. Pavyzdžiui, pasirinkus šalį „Lietuva“, antrame sąraše automatiškai matysime tik Lietuvos miestus. Tai pasiekiama sukuriant vardą (Defined Name) „Lietuva“ ir Data Validation laukelyje įrašius `=INDIRECT(šalies_langelis)`.

4.2. CHOOSE šeima (Choose, Chooserows, Choosecols)

CHOOSE

=CHOOSE(index; v1; v2...)

Grąžina pasirinktą reikšmę pagal skaičių. Naudinga scenarijų valdymui.

CHOOSECOLS

=CHOOSECOLS(Table; 1; 3)

Išskiria tik specifinius stulpelius iš didelio masyvo duomenų analizei.

CHOOSEROWS

=CHOOSEROWS(A1:Z100; 1; 5)

Išrenka tik konkrečias eilutes atvaizdavimui.

4.3. LAMBDA

LAMBDA funkcija leidžia Excel aplinkoje susikurti savo individualias funkcijas. Tai reiškia, kad galite apjungti sudėtingą logiką į vieną pavadinimą ir naudoti ją visoje darbaknygėje kaip standartinę funkciją.

=LAMBDA(parametras; skaičiavimo_logika)

Greitas testas (be išsaugojimo):

=LAMBDA(x; x*2)(10)

Funkcija sukuria logiką „padvigubinti“ ir iškart perduoda reikšmę 10. Rezultatas – 20.

Savo funkcijos kūrimas (PVM skaičiavimas):

Name Manager → New → Name: PVM

=LAMBDA(kaina; kaina*1,21)

Nuo šiol galime naudoti: =PVM(A1)

Pažangesnis pavyzdys (su sąlyga):

=LAMBDA(suma; IF(suma>1000; suma*0,9; suma))

Jei suma didesnė nei 1000 – taikoma 10% nuolaida.

LAMBDA + LET kombinacija:

=LAMBDA(kaina; LET( pvm; 0,21; kaina*(1+pvm) ))

LET leidžia kurti vidinius kintamuosius – tai padidina formulės aiškumą ir efektyvumą.

Analitiko įžvalga

LAMBDA leidžia Excel paversti mini programavimo aplinka. Ji ypač vertinga kuriant kartotinius finansinius modelius, automatizuojant ataskaitų logiką ar supaprastinant sudėtingas formules, kurios kitu atveju būtų sunkiai skaitomos ir prižiūrimos.