Elektronins lentels MS Excel 200 x 1 Formuls

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

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

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: =A 1+” ”+B 1 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 (A 1 -B 1)^C 1 (A 1+B 1) > C 1/100 – rezultatas yra loginė išraiška (konstanta TRUE arba FALSE)

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: "A 1" - 15, "A 2" - 17, tada: =IF(A 1<A 2; A 2 -A 1; A 1 -A 2) gausime 2. Jei "A 1“ ir "A 2" reikšmes sukeistume vietomis, vis tiek atsakymas bus du, nes tada loginė sąlyga bus FALSE ir bus atliekamas antrasis veiksmas.

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: "A 1" = 15, "A 2" = 17, tada: =IF(A 1<A 2; A 2 -A 1; A 1 -A 2) gausime 2. Jei "A 1“ ir "A 2" reikšmes sukeistume vietomis, vis tiek atsakymas bus 2, nes tada loginė sąlyga bus FALSE ir bus atliekamas antrasis veiksmas. Vietoje value_if_true ir/arba value_if_false galima naudoti įdėtus operatorius IF. Išraiškoje gali būti iki 7 įdėtų IF operatorių.

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(A 1, 6) – B 1 – priskiriama A 1 reikšmė, pakelta 6 laipsniu, iš kurios atimta B 1 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 Function Name matysite visas funkcijas, o pasirinkus kategoriją Most Recently Used - matysite paskutines dešimt jūsų naudotų funkcijų.

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(A 1: A 7) apskaičiuos 7 reikšmių nuo A 1 iki A 7 aritmetinį vidurkį. funkcija MAX(A 1: A 7) apskaičiuos 7 reikšmių nuo A 1 iki A 7 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.

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 A 1 iki A 5, galima rašyti: =SUM(A 1; A 5) ir =SUM(A 1: A 5). Pirmuoju atveju gausime A 1+A 5, antruoju A 1+A 2+A 3+A 4+A 5. Taip pat galima ir kaip funkcijos kintamąji (arba konstantą) rašyti kitą funkciją. Pavyzdys: Surašykime bet kokius skaičius stulpeliuose nuo A 1 iki A 4 ir nuo B 1 iki B 4 (skaičius rašykite skirtingus). Ląstelėje C 1 parašykite tokią funkciją: =SUM(MIN(A 1: A 4); MAX(B 1: B 4)) Tada ląstelėje C 1 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. .

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(A 1; B 1) antroje =SUM(A 2; B 2) 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 C 1 esančią formulę =SUM (A 1; B 1), ląstelėje C 2 bus =SUM(A 2; B 2), ląstelėje C 10 - =SUM(A 10; B 10), ląstelėje D 10 =SUM(B 10; C 10) ir t. t. Adresai, kurių pavidalas yra A 1, vadinami santykiniais ir keičiasi kopijuojant formulę.

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 A 1 iki A 10, padauginti iš bazinio atlyginimo, saugomo ląstelėje B 1. Pirmąjai ląstelei formulė atrodytų taip = A 1*B 1 Tačiau kopijuojant šią formulę likusioms 9 ląstelėms, visi adresai aitinkamai pasislinks ir žemiau turėsime formules =A 2*B 2, =A 3*B 3 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 B 1. 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 A 1 ir B 1 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: = A 1*$B$1

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: =Sheet 2! $F$39 – absoliučios koordinatės ląstelės F 39 to paties dokumento lape “Sheet 2” ='Detalus biudžetas 2006'!F 39 – santykinės koordinatės ląstelės F 39 to paties dokumento lape, pavadintame “Detalus biudžetas 2006” ='D: LGIIESF 2005[ESF 2005 -9 priedas-Mokymai-V 3. xls]Indelis'!D$18 – mišrios koordinatės ląstelės D 18 dokumento (nurodyta jo tiksli vieta diske ir vardas ESF 2005 -9 priedas-Mokymai-V 3. 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: = A 1*$B$1

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.

Praktinės užduotys 1. 2. 3. 4. 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, 170180, 180 -190, >190 ir pažymėti intervalus balais: =IF(A 1>190; 5; IF(A 1>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.

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=Pi 1+ Pi 2+. . . +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

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 svoris Y 1 – 30%. Šį kriterijų sudaro du parametrai: P 11. Strateginis vertinimas. Y 21 – 60%. P 12. Taktinis vertinimas. Y 21 – 40%. 2 kriterijus. Projekto plano kokybė. Kriterijaus lyginamasis svoris Y 2 – 10%. Šį kriterijų sudaro trys parametrai P 21. Pagrįstumas. Parametro lyginamasis svoris Y 21 – 50%. P 22. Aiškumas. Parametro lyginamasis svoris Y 22 – 25%. P 23. Optimalumas. Parametro lyginamasis svoris Y 23 – 25%. Galutinis balas kiekvienam pasiūlymui gaunamas išvedus visų ekspertų vertinimo vidurkį.

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. )

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: n Žinomi parametrai (pavyzdžiui, PVM tarifas) n Keičiami parametrai (daromos prielaidos, pavyzdžiui, vidutinis keleivių skaičius per dieną) q q q 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 n q 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.

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?

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ūdinantys parametrai. 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.