Snmek 1

Anglicky v odbornch pedmtech"Support of teaching technical subjects in EnglishTutorial: Obchodn akademie

Topic: References

Prepared by: Mgr. Zdenk Hrdina

Projekt Anglicky v odbornch pedmtech, CZ.1.07/1.3.09/04.0002je spolufinancovn Evropskm socilnm fondem a sttnm rozpotem esk republiky.

By reference we mean the situation in which we use in a particular cell data from another cell

What is a reference?

Example:

The B4 cell contains a formula referencing to cells B2 and B3If we change the values in cells B2 or B3, the value in the B4 cell will change as well

1) = B2 referencing the B2 cell from the same sheet and workbook2) =list2!B2 referencing the B2 cell located on the sheet called list23) ='[sesit.xls]list1'!B2 referencing the B2 cell located on the sheet called list1 in a workbook called sesit.xlsExamples of references

We can enter references in lower-case or upper-case letters, Excel always converts them to upper-case lettersNames of sheets and workbooks that are made up of more than one word need to be enclosed in single quotation marksNotes

relative when copying a formula, it adapts to the new positionmixed when copying a formula, only the column or the row adapts and the second element of the reference stays the samerow absolute the row doesnt change but the column doescolumn absolute the column doesnt change but the row doesabsolute when copying a formula, it stays the same.Types of references

Relative reference the same as in the first example - e.g. B2Mixed referencerow-absolute - B$2column-absolute - $B2Absolute reference - $B$2What do the individual types of references look like?

1) directly from the keyboardCzech keyboard Alt Gr + American keyboard Shift + 42) using the F4 key reapeated pressing of the F4 key toggles between the possible variants of a given referencebefore pressing the F4 key it is necessary to click on the formulaHow can we insert the $ character?

By correctly using the mentioned types of references we can save time and make our work more effective.Use of references?

Complete the table with formulas and correct types of references so that the copying of formulas into the other cells is as effective as possiblezadani.xlsx

Exercise:

BARILLA, Ji, Pavel SIMR a Kvtue SKOROV.Microsoft Excel 2010:Podrobn uivatelsk pruka. Brno: Computer Press, 2010. ISBN 978-80-251-3077-3.Microsoft Office.Relativn a absolutn odkazy - MS Excel[online]. 2011-12-11 [2011-12-16].

Sources