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

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

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

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

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

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

Zarada.xlsx

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

Корак 2.

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

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

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

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

= B5*C5

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

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

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

=SUM(D5:D15)

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

=SUM(B5:B15)

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

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

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

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

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

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

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

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

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

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

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

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

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

= D5/D16

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

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

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

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

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

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

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

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

= D6/D17

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

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

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

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

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

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

= D5/$D$16

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

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

= D6/$D$16

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

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

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

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

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

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

Добијамо:

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

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

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

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

PriceList.xlsx

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

Корак 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

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

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

= B4*$B$16

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

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

= B4*$B$17

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

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

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

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

4. Уоквири следеће групе ћелија користећи опцију "Outside Borders":

   A2:A13

   A2:D3

   B3:B13

   C3:C13

   D3:D13

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

Корак 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.