Prijavi problem


Obeleži sve kategorije koje odgovaraju problemu

Još detalja - opišite nam problem


Uspešno ste prijavili problem!
Status problema i sve dodatne informacije možete pratiti klikom na link.
Nažalost nismo trenutno u mogućnosti da obradimo vaš zahtev.
Molimo vas da pokušate kasnije.

Analiza i prezentacija podataka u Džupiteru

6. Апсолутне и релативне адресе ћелија

У овој лекцији нагласак је на формулама. Објашњавамо:

  1. како се копирају формуле и како Ексел мења формулу током копирања,
  2. шта је релативна, а шта апсолутна адреса ћелије и како то утиче на копирање формула, и
  3. како подацима у ћелији додати знак за валуту.

Пример: Зарада од продаје воћа -- рачунање удела у заради

У овом примеру ћемо израчунати зараду једног продавца воћа у једном дану. За сваку врсту воћа ћемо израчунати и који удео зарада од продаје те врсте воћа чини у укупној заради продавца.

Корак 1. Преузми табелу Zarada.xlsx са следећег линка и учитај је у Ексел:

Zarada.xlsx

Табела изгледа овако:

Zarada

Корак 2.

  1. У поље B1 унеси неки датум (датум за који се прави анализа), а у поље B2 унеси име продајног места (било какав текст).
  2. Спој ћелије A3:E3 (Merge Cells) и повећај величину слова текста у новој (великој) ћелији.
  3. Ћелије B2 и B4:E4 поравнај по десној ивици.
  4. За стил текста у ћелијама A4:E4 одабери bold и italic.

Табела треба да изгледа отприлике овако:

Zarada

Корак 3. У ћелије B5:B15 унеси произвољне бројеве. Они представљају количину воћа која је продата, рецимо овако:

Zarada

Корак 4. За сваку врсту воћа израчунај зараду тако што ћеш у ћелију D5 унети формулу

= B5*C5

и онда ту формулу "развући" до краја листе:

Zarada

6.1. Интелигентно копирање формула

Корак 5. Израчунај укупну зараду тако што ћеш у ћелију D16 унети формулу

=SUM(D5:D15)

Zarada

Корак 6. Даље, израчунаћемо колико је укупно килограма воћа продато тај дан. Један начин да се то уради је, наравно, да се у ћелију B16 унесе формула

=SUM(B5:B15)

али ћемо сада показати бржи начин да се то уради. Користићемо наредбе из групе "Clipboard"

Zarada

Пошто смо већ у ћелији D16 израчунали збир бројева у ћелијама D5:D15, и пошто је формула коју треба да унесемо у ћелију B15 "аналогна", просто ћемо ископирати формулу из ћелије D16 у ћелију B16. Ево како.

Кликни на ћелију D16 и онда кликни на икону која одговара операцији "Copy" (енгл. "ископирај"):

Zarada

У табели се ништа није десило, али је Ексел ископирао формулу у посебан део меморије који се зове Clipboard.

Сада кликни на ћелију B16 и онда кликни на икону која одговара операцији "Paste" (енгл. "налепи") која ће садржај из Clipboard-а ископирати у ћелију на коју смо кликнули, али интелигентно:

Zarada

Ексел је ископирао формулу у ћелију B16, али се прилагодио чињеници да смо формулу пренели из колоне D у колону B. Зато сада у колони B16 пише:

Zarada

Ова операција је веома важна и у жаргону се зове Copy/Paste.

Важан закључак: Ексел уме да копира садржај ћелије у неку другу ћелију. Ако садржај који се копира број, датум, време или текст, приликом копирања неће ништа бити промењено. Али, ако је садржај који се копира формула, она ће бити прилагођена положају нове ћелије и биће измењена на одговарајући начин! Ово својство Ексела зовемо интелигентно копирање формула.

Приметимо још нешто: операција "развлачења ћелије за бубуљицу" коју смо до сада користили да брзо копирамо формуле је само вишеструка примена операције Copy/Paste.

6.2. Апсолутна адреса ћелије

Корак 7. За сваку врсту воћа израчунаћемо удео те врсте воћа у укупној заради и приказати га у облику процента. Као што смо већ видели, проценти се рачунају као обичан количник дела и целине, и онда се одговарајуће ћелије само форматирају на одговарајући начин.

У нашем примеру целину представља укупна зарада (број у ћелији D16), док су делови чији удео у целини рачунамо бројеви који представљају зараду сваке појединачне врсте воћа.

Да бисмо израчунали удео који зарада на јабукама носи у укупној заради у ћелију E5 ћемо уписати формулу

= D5/D16

(количник дела и целине):

Zarada

Добијамо неки децимални број који ћемо касније форматирати као проценат.

Пошто ћелије E6:E15 садрже исту формулу, само ћемо кликути на ћелију E5 и "развући" до дна:

Zarada

УПС! ГДЕ СМО ПОГРЕШИЛИ?

Да видимо. Ако кликнемо на ћелију E5 и притиснемо тастер [F2] Ексел ће нам показати формулу која је у ћелији и означиће ћелије које учествују у формули:

Zarada

Формула у ћелији E5 је добра. (Како не би била када смо је ручно унели.)

Хајде сада да кликнемо на ћелију E6 и притиснемо тастер [F2]:

Zarada

Ето зашто се Ексел буни: формула у ћелији E6 гласи

= D6/D17

и D6 је добро (то је зарада на крушкама), али D17 је празна ћелија. Када се број у ћелији D6 подели ничим добије се грешка, наравно.

Зашто смо добили ову формулу? Погледајмо табеларни приказ ситуације:

Ћелија Формула у ћелији Формула коју желимо
E5 = D5/D16 = D5/D16
E6 = D6/D17 = D6/D16

Када смо формулу из ћелије E5 ископирали у ћелију E6 пренели смо формулу један ред ниже. Тако је D5 постало D6, што нам одговара, али је D16 постало D17, што нам никако не одговара.

Волели бисмо када бисмо могли да убедимо Ексел да при преласку са E5 на E6 он промени D5 на D6, али желимо да при томе D16 остане D16. И то је, наравно, могуће!

Обрисаћемо сада све (селектујемо ћелије E5:E15) и притиснемо тастер [Del] (или [Delete]) на тастатури:

Zarada

Поново ћемо у поље E5 унети формулу, али овај пут ћемо написати:

= D5/$D$16

Zarada

Симболима \$ које смо додали испред имена колоне и броја врсте ћелије D16 смо _фиксирали_ ту ћелију у следећем смислу: _приликом копирања формуле овај елемент формуле неће бити модификован; он ће увек остати \\$D\$16_.

Zarada

Да се уверимо да је то заиста тако ископираћемо формулу из ћелије E5 у ћелију E6 користећи Copy/Paste акцију. Ако притиснемо тастер [F2] док стојимо на ћелији E6 видимо да је у њу уписана формула

= D6/$D$16

Zarada

Дакле, приликом копирања формуле из E5 у E6, D5 постало D6, али је \$D\\$16 остало \$D\\$16.

Сада слободно можемо да кликнемо на ћелију E6 и "развучемо" формулу до дна табеле:

Zarada

У свакој ћелији се налази коректна формула. На пример,

Zarada

Адреса ћелије као што је D6 се зове релативна адреса зато што је Екселу приликом копирања формуле дозвољено да је мења.

Адреса ћелије као што је \$D\\$16, која је фиксирана, се зове апсолутна адреса зато што Екселу приликом копирања формуле није дозвољено да је мења (зато је апсолутна, непроменљива).

Корак 8. За крај треба још бројеве које смо добили у колони E приказати у облику процента. Да бисмо то постигли, селектоваћмо ћелије E5:E15 и као формат приказа одабрати "Percentage":

Zarada

Добијамо:

Zarada

За крај, сними табелу као Ексел фајл и као ПДФ фајл.

6.3. Пример: Прерачун валута

Показаћемо сада још један пример у коме се природно користе апсолутне адресе ћелија.

Корак 1. Са следећег линка преузми документ PriceList.xlsx који садржи ценовник једне радње у три валуте: еврима, британским фунтама и динарима:

PriceList.xlsx

Табела изгледа овако:

PriceList

Корак 2. Прво ћемо мало да улепшамо табелу.

  1. Спој (Merge Cells) ћелије A1:D1 (наслов табле), постави величину слова на 16, обој позадину ових ћелија тамно сиво, а слова бело.
  2. Постави величину слова у ћелијама A2:D3 на 12.
  3. Спој (Merge Cells) ћелије A15:C15 (наслов мале табеле); обој позадину ових ћелија тамно сиво, а слова бело.
  4. Прошири колоне A, B, C и D тако да сав садржај буде видљив.
  5. Спој (Merge Cells) ћелије A2 и A3 и центрирај садржај вертикално.
  6. Спој (Merge Cells) ћелије B2:D2

Табела сада изгледа овако:

PriceList

Корак 3. Израчунаћемо цене производа у британским фунтама и у динарима на основу курса евра који је дат у малој табели. Да бисмо израчунали цену првог производа у британским фунтама у ћелију C4 ћемо унети

= B4*$B$16

PriceList

У овој формули ћелија B4 није фиксирана јер приликом копирања формуле у следећи ред желимо да она постане B5 (и тако добијемо формулу која конвертује у британске фунте цену наредног производа на листи). С друге стране, ћелија B16 је фиксирана изразом \$B\\$16 јер се на сваки ред табеле примењује курс фунте из ћелије B16, и то не треба да се мења приликом копирања формуле.

Из истог разлога ћемо у ћелију D4 унети формулу

= B4*$B$17

PriceList

Сада можемо ове две формуле да брзо ископирамо у остатак табеле. Селектоваћемо ћелије C4:D4

PriceList

и "развући" до дна табеле:

PriceList

Корак 4. За крај ћемо још мало улепшати табелу.

  1. Прикажи све цене на две децимале.
  2. Поравнај по десној ивици садржај у ћелијама B3:D13.
  3. Испред цена у фунтама додај симбол за британску фунту, а испред цена у еврима додај симбол за евро.
  4. Уоквири следеће групе ћелија користећи опцију "Outside Borders":

    A2:A13

    A2:D3

    B3:B13

    C3:C13

    D3:D13

Табела на крају треба да изгледа овако:

PriceList

Корак 5. Сними табелу и као Ексел фајл и као ПДФ фајл.

6.4. Задаци

Задатак 1. Са следећег линка преузми датотеку која садржи податке о берачима воћа на једној плантажи:

Jabuke.xlsx

  • Прошири колоне ако је потребно и улепшај табелу.
  • Одреди укупну количину јабука, крушака и трешања које су убране.
  • За сваког берача утврди колико је зарадио ако знаш да су износи убраног воћа дати у килограмима, а цена брања по килограму воћа је дата испод табеле са берачима.

Задатак 2. У ауто-салону се продаје 10 врста аутомобила и преглед продаје је дат у документу на следећем линку:

Automobili.xlsx

Свака врста аутомобила има своју Nabavnu cenu (колона F).

На набавну цену власник салона зарачунава своју маржу (проценат) из ћелије C3 и висина марже се исказује у колони G.

Osnovna cena аутомобила (колона H) је набавна цена увећана за маржу.

На основну цену се зарачунава Carina у одређеном проценту (колона G). Проценат за царину се налази у ћелији C4.

На збир основне цене и царине се даље зарачунава ПДВ (колона H) у одређеном проценту. Проценат ПДВ-а се налази у ћелији C5.

Ukupna Cena се рачуна као збир Osnovne cene, Carine и ПДВ-а (колона I).

У колону Popust (колона J) се уноси могући попуст који даје продавац у процентима на основну цену, ако му се купац "свиђа".

Израчунати Prodajnu cenu.