E lektroninės lentelės MS Excel 200x

1. Elektroninės lentelėsMS Excel 200x 1. Formulės ir skaičiuoklės

2. Turinys • Paprastos formulės ir loginės operacijos • Funkcijos • Ląstelės koordinatės • Skaičiuoklės • Praktinės užduotys

3. Paprastos formulės Paprasčiausios formulės yra šios: Aritmetinės operacijos: +, -, *, /, ^ (kėlimas laipsniu) Teksto operacijos: & (dviejose ląstelėse esančių teksto eilučių sujungimas). Pavyzdys: =A1+” ”+B1 sujungs duomenis iš dviejų ląstelių, tarp jų palikdama tarpą Santykio operacijos: <, >, >=, <=, <> – jų reikšmės yra loginės konstantos TRUE (teisinga) arba FALSE (klaidinga). Pavyzdžiai (A1-B1)^C1 (A1+B1) > C1/100 – rezultatas yra loginė išraiška (konstanta TRUE arba FALSE)

4. Loginės operacijos • TRUE(), FALSE() – loginės reikšmės “teisinga” ir “klaidinga”. Naudojamos palyginimui su ląstelių reikšmėmis. • AND(), OR() – operatoriai, atitinkantys matematinės logikos konjunkcijos ir disjunkcijos operacijas. Kiekvienas jų gali turėti iki 30 sąlygų, atskirtų kabliataškiu. • Pavyzdys: =AND(2>1;3<10) gausime TRUE, =AND(2>1;3>10) gausime FALSE. • NOT() – operatorius, atitinkantis matematinės logikos neiginį. Sąlygos operatorius IF – tai funkcija, galinti patikrinti, ar ląstelėje esanti reikšmė tenkina užduotą sąlygą ir priskirti atitinkamą reikšmę kitai ląstelei. • IF (logical_test - loginis testas; value_if_true - reikšmė jei loginis testas teisingas; value_if_false - reikšmė jei loginis testas neteisingas). • Uždavus loginę sąlygą, pagal jos sprendimą ląstelėje gali būti viena iš reikšmių. Pavyzdžiui: "A1" - 15, "A2" - 17, tada: =IF(A1<A2;A2-A1;A1-A2) gausime 2. Jei "A1“ ir "A2" reikšmes sukeistume vietomis, vis tiek atsakymas bus du, nes tada loginė sąlyga bus FALSE ir bus atliekamas antrasis veiksmas.

5. Sąlygos operatorius Sąlygos operatorius IF – tai funkcija, galinti patikrinti, ar ląstelėje esanti reikšmė tenkina užduotą sąlygą ir priskirti atitinkamą reikšmę kitai ląstelei. • IF (logical_test - loginis testas; value_if_true - reikšmė jei loginis testas teisingas; value_if_false - reikšmė jei loginis testas neteisingas). • Uždavus loginę sąlygą, pagal jos sprendimą ląstelėje gali būti viena iš reikšmių. Pavyzdžiui: "A1" = 15, "A2" = 17, • tada: =IF(A1<A2;A2-A1;A1-A2) gausime 2. • Jei "A1“ ir "A2" reikšmes sukeistume vietomis, vis tiek atsakymas bus 2, nes tada loginė sąlyga bus FALSE ir bus atliekamas antrasis veiksmas. • Vietoje value_if_trueir/arba value_if_false galima naudoti įdėtus operatorius IF. Išraiškoje gali būti iki 7 įdėtų IF operatorių.

6. Funkcijos (I) Skaičiuojant ląstelių reikšmes galima naudoti ne tik aritmetines išraiškas, bet ir visą aibę funkcijų. Tai atliekama įrašius į ląstelę lygybės ženklą ir funkcijos vardą bei parametrus. Formules galima rinkti ir didžiosiomis, ir mažosiomis raidėmis, svarbu teisingai nurodyti funkcijos vardą ir parametrus. Pavyzdžiui: =POWER(A1,6) – B1 – priskiriama A1 reikšmė, pakelta 6 laipsniu, iš kurios atimta B1 reikšmė. =RAND() – ląstelei priskiriama atsitiktinė reikšmė intervale nuo 0 iki 1. Funkcijos gali būti įterpiamos pasirinkus Insert→Function meniu komandą ir pasinaudojant vedliu, kuris padeda teisingai įrašyti parametrus. Yra 9 funkcijų grupės. Pasirinkus kategoriją All, lange FunctionName matysite visas funkcijas, o pasirinkus kategoriją Most Recently Used - matysite paskutines dešimt jūsų naudotų funkcijų.

7. Funkcijos (II) Dažnai naudojamos funkcijos MIN, MAX, SUM, COUNT, AVG taikomos aibei reikšmių. Tokių funkcijų parametras yra reikšmių intervalas, kuris nurodomas taip: <pirmas narys>:<paskutinis narys>. Pavyzdžiui: funkcija AVG(A1:A7) apskaičiuos 7 reikšmių nuo A1 iki A7 aritmetinį vidurkį. funkcija MAX(A1:A7) apskaičiuos 7 reikšmių nuo A1 iki A7 maksimumą. Insert→Function lange funkcijos grupuojamos, kad lengviau būtų rasti norimą. Statistinių, matematinių ir trigonometrinių, finansinių funkcijų grupės yra skirtos įvairiems uždaviniams spręsti, todėl Excel neretai gali pakeisti specializuotus matematinius paketus.

8. Funkcijos (pavyzdžiai) Bendras funkcijos iškvietimo formatas yra: FUNKCIJA(pirmas_kintamasis_arba_konstanta; antras_kintamasis; ... ; paskutinis_kintamasis) Kintamieji atskiriami kabliataškiais. Pavyzdys: yra sąrašas skaičių ląstelėse nuo A1 iki A5, galima rašyti: =SUM(A1;A5) ir =SUM(A1:A5). Pirmuoju atveju gausime A1+A5, antruoju A1+A2+A3+A4+A5. Taip pat galima ir kaip funkcijos kintamąji (arba konstantą) rašyti kitą funkciją. Pavyzdys: Surašykime bet kokius skaičius stulpeliuose nuo A1 iki A4 ir nuo B1 iki B4 (skaičius rašykite skirtingus). Ląstelėje C1 parašykite tokią funkciją: =SUM(MIN(A1:A4);MAX(B1:B4)) Tada ląstelėje C1 visada matysite mažiausios A stulpelio ir didžiausios B stulpelio reikšmių sumą. Nebūtina visą laiką ranka rašyti ląstelės adresus į formulę, galima tiesiog bakstelėti pele į norimą ląstelę ir jos adresas atsiras formulėje automatiškai..

9. Ląstelės koordinatės (I) Ląstelės koordinatės gali būti santykinės ir absoliučios. Skirtumas tarp santykinių ir absoliučių koordinačių tampa svarbus, kai jas naudojančios formulės kopijuojamos į kitą vietą. Jei darome lentelę, kurioje viename stulpelyje turi buti kitų stulpelių suma, tai gali atrodyti, kad reikės kiekvienoje eilutėje rašyti: =SUM(A1;B1) antroje =SUM(A2;B2) ir t.t., o eilučių gali būti 100 ar daugiau. Iš tikrųjų formulę galima kopijuoti taip pat, kaip ir ląstelės reikšmę. Pasirinkus ląstelę, kurios reikšmė suskaičiuojama pagal formulę, galima kopijuoti EditCopy arba Ctrl+C komanda, taip pat tempiant už apatinio dešiniojo kampo. Pakeitus formulės vietą lape, programa automatiškai perskaičiuoja ląstelių adresus formulėje, t.y., kopijuojant ląstelėje C1 esančią formulę =SUM (A1;B1), ląstelėje C2 bus =SUM(A2;B2), ląstelėje C10 - =SUM(A10;B10), ląstelėje D10 - =SUM(B10;C10) ir t.t. Adresai, kurių pavidalas yra A1, vadinami santykiniais ir keičiasi kopijuojant formulę.

10. Ląstelės koordinatės (II) Tačiau kartais reikia, kad kopijuojant ląstelę su formulę i kitą vietą, formulės kintamieji nesikeistų, o išliktų pastovūs. Pavyzdžiui, norime darbuotojų atlyginimų koeficientus, saugomus ląstelėse nuo A1 iki A10, padauginti iš bazinio atlyginimo, saugomo ląstelėje B1. Pirmąjai ląstelei formulė atrodytų taip = A1*B1 Tačiau kopijuojant šią formulę likusioms 9 ląstelėms, visi adresai aitinkamai pasislinks ir žemiau turėsime formules =A2*B2, =A3*B3 ir t.t. Tuo tarpu mums reikia, kad keistųsi tik A stulpelio ląstelių adresai, o bazinio atlyginimo reikšmė visada būtų imama iš ląstelės B1. Prieš ląstelės koordinates, kurias norime išlaikyti nekintamas, įrašomi simboliai $, pavyzdžiui, SUM($A$1;$B$1). Tada nesvarbu kur kopijuosime tą ląstelę, joje visada bus A1 ir B1 ląstelių reikšmių suma. Simboliai $, gali būti rašomi ir tik prieš stulpelio, ir tik prieš eilutės adresą, pavyzdžiui =A$2. Kopijuojant tokią formulę, atitinkamai keisis adreso stulpelio koordinatė, tačiau eilutė visada liks 2. Tokios koordinatės vadinamos absoliučiomis. Mūsų atveju atlyginimo formulė atrodys taip: = A1*$B$1

11. Ląstelės koordinatės (III) Formulėse galima naudoti nuorodas į ląsteles, esančias ne tik tame pačiame lape, bet ir į ląsteles iš kitų lapų ar net kitų dokumentų. Tada būtina nurodyti ne tik ląstelės koordinates lape, bet ir lapo/dokumento vardą. Pavyzdžiai: =Sheet2! $F$39 – absoliučios koordinatės ląstelės F39 to paties dokumento lape “Sheet2” ='Detalus biudžetas 2006'!F39 – santykinės koordinatės ląstelės F39 to paties dokumento lape, pavadintame “Detalus biudžetas 2006” ='D:\LGII\ESF2005\[ESF2005-9 priedas-Mokymai-V3.xls]Indelis'!D$18 – mišrios koordinatės ląstelės D18 dokumento (nurodyta jo tiksli vieta diske ir vardas ESF2005-9 priedas-Mokymai-V3.xls) lape, pavadintame “Indelis” Ląstelės adreso nebūtina rašyti į formulę rankomis – pakanka priėjus atitinkamą vietą spustelti pele ant norimos ląstelės ir jos santykinis adresas (jei reikia, su lapo ir dokumento vardais) automatiškai bus įrašytas į formulę. Absoliutinės koordinatės sužymimos rankomis. Mūsų atveju atlyginimo formulė atrodys taip: = A1*$B$1

12. Skaičiuoklės Galime pastebėti, kad pakeitus reikšmes ląstelėse, formulės, kuriose naudojamos tų ląstelių reikšmės, perskaičiuojamos automatiškai. Skaičiuoklė – tai Excel sukurta forma, kurioje yra vieta įvesti duomenims, bei ląstelės su įrašytomis formulėmis, apskaičiuojančios reikalingus rodiklius priklausomai nuo to, kokie duomenys įvesti. Skaičiuoklės dažnai naudojamos biudžetui sudaryti, kitiems finansiniams ir statistiniams skaičiavimams. Skaičiuoklės pavyzdys (spauskite nuorodą į Excel dokumentą) Panagrinėkite, kokie duomenys naudojami ir kokie apskaičiuojami pateiktame projekto kaštų ir naudos analizės dokumente. Atkreipkite dėmesį, kaip nurodomi adresai ląstelių, esančių kituose lapuose, kaip keičiasi duomenys, keičiant rodiklių reikšmes Indicators lape.

13. Praktinės užduotys • Pabandyti Insert Function veikimą su įvairiais duomenimis. Panaudoti įprastas matematines funkcijas – sin, cos, log, int; statistines (average, count, max, min), pažįstamas teksto, logines ir kt. funkcijas • Suskirstyti studentų ūgio duomenis į intervalus <160, 160-170, 170-180, 180-190, >190 ir pažymėti intervalus balais: =IF(A1>190;5;IF(A1>180;4; ir t.t.)). • Išsiaiškinti, kaip ar yra priklausomybė tarp jūsų grupės studentų ūgio ir svorio (apskaičiuoti koreliaciją). Rasti maksimalų ir minimalų ūgio nuokrypius nuo vidurkio. • Sukurti skaičiuokles pagal toliau pateiktas sąlygas.

14. 1 užduotis. Pasiūlymo vertinimas (I) Sukurkite skaičiuoklę (statinį determinuotą modelį) pateiktų įsivaizduojamų projekto pasiūlymų vertinimui balais pagal kelis kriterijus. Pasiūlymai vertinami remiantis viešųjų pirkimų atviro konkurso būdu metodika, o kriterijų balai skaičiuojami pagal formules žemiau. Ekonominis naudingumas (S) apskaičiuojamas sudedant dalyvio pasiūlymo kainos (C) ir kitų kriterijų (T) balus: S=C+T Pasiūlymo kainos (C) balai apskaičiuojami mažiausios pasiūlytos kainos (Cmin) ir vertinamo pasiūlymo kainos (Cp) santykį padauginant iš kainos lyginamojo svorio (X): C=Cmin/Cp*X Kriterijaus (Ti) balai pasiūlymui p apskaičiuojami šio kriterijaus reikšmę (Tip) padauginant iš vertinamo kriterijaus lyginamojo svorio (Yi): Ti=Tip*Yi Kriterijaus (Ti) reikšmė konkrečiam pasiūlymui yra šio kriterijaus parametrų balų (Pis) suma: Ti=Pi1+ Pi2+ ... +Pik Kriterijaus parametro balas konkrečiam pasiūlymui (Pis) apskaičiuojamas parametro reikšmę (Ris) palyginant su geriausia to paties parametro reikšme (Ris max) ir padauginant iš vertinamo kriterijaus parametro lyginamojo svorio: Pis = Ris/Ris max * Lis

15. 1 užduotis. Pasiūlymo vertinimas (II) Pateikti 3 pasiūlymai Juos pagal žemiau išvardintus kriterijus vertina 3 ekspertai Kaina. Ji išreiškiama sveikais neneigiamais skaičiais. Kainos lyginamasis svoris X – 60%. Sekančius du kriterijus vertina ekspertai, kiekvienam kriterijaus parametrui skirdami nuo 0 iki 100 balų. 1 kriterijus. Projekto idėja. Kriterijaus lyginamasis svorisY1 – 30%. Šį kriterijų sudaro du parametrai: P11. Strateginis vertinimas. Y21 – 60%. P12. Taktinis vertinimas. Y21 – 40%. 2 kriterijus. Projekto plano kokybė. Kriterijaus lyginamasis svorisY2 – 10%. Šį kriterijų sudaro trys parametrai P21. Pagrįstumas. Parametro lyginamasis svorisY21 – 50%. P22. Aiškumas. Parametro lyginamasis svorisY22 – 25%. P23. Optimalumas. Parametro lyginamasis svorisY23 – 25%. Galutinis balas kiekvienam pasiūlymui gaunamas išvedus visų ekspertų vertinimo vidurkį.

16. 1 užduotis. Pasiūlymo vertinimas (III) Skaičiuoklėje turi būti galimybė kiekvienam ekspertui įvesti parametrų vertinimus balais. Pagal nurodytas formules turi būti automatiškai apskaičiuojami ir parodomi kiekvieno kriterijaus balai kiekvienam ekspertui. Ekspertams turi būti uždrausta keisti reikšmes ląstelėse, kuriose įrašytos formulės. Tarpiniams skaičiavimams gali prireikti antro lapo – duomenų kopijos, gautos priskiriant pirmojo lapo ląstelių reikšmes. Galutinis rezultatas – lentelė su kainos ir kitų kriterijų (ekspertų vertinimų vidurkiai) svoriniais įverčiais. Turi būti galimybė palikti nulines kainos reikšmes (laikantis vertinimo procedūros ji įrašoma vėliau.)

17. 2 užduotis. Kaštų-naudos analizė (I) Sudarykite dinaminio determinuoto modelio lenteles (balanso skaičiuoklę) įvertinti įsigyto taksi automobilio kaštų-naudos santykiui penkerių metų laikotarpiui. Joje turi būti panaudoti: • Žinomi parametrai (pavyzdžiui, PVM tarifas) • Keičiami parametrai (daromos prielaidos, pavyzdžiui, vidutinis keleivių skaičius per dieną) • Pastovūs dydžiai (pavyzdžiui, nusidėvėjimo procentas) • Atsitiktiniai dydžiai (pavyzdžiui, išlaidos remontui) • Kintami rodikliai (pavyzdžiui, keleivių skaičiauso augimas) • Skaičiuojami rodikliai • Rodikliai, kurie yra apskaičiuoti iš parametrų ir kitų rodiklių – sumos, vidurkiai, ar pan. (pavyzdžiui, mėnesio išlaidų suma) Turi būti skaičiuojami aktualūs balanso modeliui įverčiai – sumos, vidurkiai, didžiausios ir mažiausios reikšmės.

18. 2 užduotis. Kaštų-naudos analizė (II) Reikšmės, kurios skiriasi nuo priimtinų, turi būti automatiškai išskiriamos spalva (pavyzdžiui, neigiamas pajamų-išlaidų balansas turi tapti raudonas). Pavaizduokite KNA duomenis tinkamiausiu būdu (diagramomis). Apsaugokite ląsteles su nekeičiamomis reikšmėmis nuo duomenų keitimo. Keisdami keičiamus parametrus nustatykite jų reikšmes, su kuriomis investicija nebūtų nuostolinga. Per kiek metų ji atsipirks?

19. 3 užduotis. Statistinis modelis Sudarykite statistinį grupės modelį su bent keturiais parametrais, pasirinktais pagal dominančią sritį (pavyzdžiui, biometriniai rodikliai, pažangumo rodikliai ir pan.). Turi būti suskaičiuoti visi jums žinomi imtįapibūdinantysparametrai. Mokėti juos interpretuoti (paaiškinti, ką reiškia gauti skaičiai) Įvertinti, ar tinkamai modelis, sudarytas imties pagrindu, atitinka visą studentų populiaciją. Įvertinti parametrų sklaidą bei tarpusavio koreliacijas, mokėti pakomentuoti rezultatus. Sukurti formulę prognozei, pavyzdžiui, jei pastebėjote sąsają tarp KMI ir pažangumo, koks galėtų būti pažangumas įvedus naujus KMI duomenis. Patikrinti formulės tinkamumą su realiais duomenimis. Pavaizduoti duomenis taip, kad jie atskleistų kuo daugiau informacijos. Reikšmės, kurios skiriasi nuo priimtinų, turi būti automatiškai išskiriamos spalva (pavyzdžiui, per didelis kūno masės indeksas turi tapti raudonas). Apsaugoti ląsteles su nekeičiamomis reikšmėmis nuo duomenų keitimo.