IT – som værktøj

1. IT – som værktøj Bent Thomsen Institut for Datalogi Aalborg Universitet Bent Thomsen - FIT 7-1

2. Introduction to Spreadsheets Bent Thomsen Bent Thomsen - FIT 7-1

3. What is an electronic spreadsheet? It is the electronic equivalent of an accounting worksheet, comprised of rows and columns to allow you to do many tasks in the organization of numbers in a clear, easy to understand format Bent Thomsen - FIT 7-1

4. What is an electronic spreadsheet? • It is a tool to help you calculate budgets, do economic analysis, statistics, engineering calculations, … • Replaces pen, paper and pocket calculator • Can show diagrams and graphs • Can input data from other programs • Can output data to other programs Bent Thomsen - FIT 7-1

5. Some Advantages of Spreadsheets • Spreadsheets are capable of exploring “what-if”scenarios (e.g. budgets, submitting bids) • Once it is set up properly, the user can save time by never having to set up the spreadsheet again • Blank spreadsheets are called templates. • Monthly salaries,grade sheets Bent Thomsen - FIT 7-1

6. Popular Spreadsheets • Quattro Pro • Lotus 1-2-3 • Microsoft Excel Bent Thomsen - FIT 7-1

7. Spreadsheet terminology • Row- horizontal axis (designated by numbers) • Column- vertical axis (designated by letters) • Cell - intersection of row and column (designated by an address comprised of the column letter and row number e.g. A1) • Block//Range- a rectangular group of one or more cells (identified by block coordinates (e.g. A1:G4) Bent Thomsen - FIT 7-1

8. Spreadsheet terminology (con’t.) • Label- alphanumeric • Value - a number or formula result • Formula- creates relationships among other cells • Template - a notebook that has labels, formulas, and all of the formatting but no actual data (e.g. actual figures and numbers) Bent Thomsen - FIT 7-1

9. How big is a spreadsheet? • Normally you see 9 columns and 18 rows • = 162 cells • One sheet has 256 columns and 65536 rows • = 1677216 cells • That is more than 103000 screens • Would take 34000 A4 pages to print • Take 194 days to fill at one cell pr second Bent Thomsen - FIT 7-1

10. Starting Excel • Menu bar • Blank worksheet in document window Bent Thomsen - FIT 7-1

11. Exploring the Excel Screen Title bar Menu toolbar Standard toolbar Formatting toolbar Screen Tip Active worksheet in workbook window Task Pane: organizes related commands Bent Thomsen - FIT 7-1

12. Activating Toolbars Click on View and Toolbars Toolbars sub-menu appears Click on desired toolbar Check indicates active item; click to deactivate Bent Thomsen - FIT 7-1

13. Moving Around the Worksheet Working in an active cell(intersection of a row and column) Insertion point: where text will be entered I-beam: to place insertion point Cell pointer Bent Thomsen - FIT 7-1

14. Moving Around the Worksheet • Move cell pointer • arrow keys • scroll bars • Change pages • click on tabs • tab scroll buttons Bent Thomsen - FIT 7-1

15. Moving Around the Worksheet • Consider cell B4 active • Note • thick cross mouse pointer • row, column buttons highlighted • After scrolling to right, note … • row button still highlighted • name box still shows B4 as active cell Bent Thomsen - FIT 7-1

16. Moving Around the Worksheet • To select a column • Click on the column heading button • Whole column is highlighted Bent Thomsen - FIT 7-1

17. Entering Labels • Click desired cell to make it active • Label is displayed both in cell and in formula bar as you type • Label displays out of its column • as long as other columns are empty Bent Thomsen - FIT 7-1

18. Worksheet with Labels • Note • Documentationsection • Label cut off, next celloccupied • Labels aligned left Bent Thomsen - FIT 7-1

19. Editing a Cell's Information • Click on desired cell • Cell pointer moves there • Contents displayed in formula bar • Click mouse pointer (I-beam) to location within text • type, delete, copy, paste as needed I Bent Thomsen - FIT 7-1

20. Entering Values • When entering numbers • do not use commas • numbers are right justified by default • To proceed to next cell right use [Tab] or right arrow key • To proceed down, use [Enter] key Bent Thomsen - FIT 7-1

21. Entering Formulas • Formulas are mathematical equations • perform calculations • always start with an equal sign (=) • Formula shows informula bar • Note color referencesin formula . . . Bent Thomsen - FIT 7-1

22. Entering Formulas • After formula entered and cell pointer moved • Formula does not show in formula bar • Result of calculations shows in cell where formula entered Bent Thomsen - FIT 7-1

23. Operators • ^- exponents • +- addition • * - multiplication • / - division • - - subtraction • =- function Bent Thomsen - FIT 7-1

24. Example =5+1*3 8 Bent Thomsen - FIT 7-1

25. Order Calculations are Performed • First exponents • Then any multiplication and division in the order they occur • Then any addition and subtraction in the order they occur Bent Thomsen - FIT 7-1

26. Parentheses • Operations within parentheses are performed before those outside. • Within the parentheses the basic rules are followed. • Multiple sets of parentheses, the innermost are executed first followed by the next set. Bent Thomsen - FIT 7-1

27. Built-in functions • Functions are pre-written formulas • Functions must start with an equal sign • Functions takes value(s), perform an operation, and returns a value(s) • Values you use with a function are arguments • =AVERAGE(D3:D7) • AVERAGE is the function • D3:D7 is the argument Bent Thomsen - FIT 7-1

28. Using Functions • Advantages of predefined functions • save time • more accurate • Using AutoSum • Click cell atbottom of column • Click AutoSumbutton • Excel assumesit should totalthe column • SUM functioninserted Bent Thomsen - FIT 7-1

29. Using Functions • AutoSum can also be used to right of a row of numbers Bent Thomsen - FIT 7-1

30. Using Functions • Note end results of using AutoSum • Note: • Click AutoSum button once to display formula,again to apply • SUM formuladisplays in Formula bar Bent Thomsen - FIT 7-1

31. Using the Function Insert Feature • Click on Insert, and Function • Insert Function dialog box appears Select function category Choose specific function desired Bent Thomsen - FIT 7-1

32. Arguments of function must be specified Note calculated result of inserted function Animated border shows selected range Formula appears in cell Using the Function Insert Feature Bent Thomsen - FIT 7-1

33. Note calculated result of inserted function Using the Function Insert Feature Bent Thomsen - FIT 7-1

34. Click on Next button to proceed Creating a Chart • Select series of numbers from worksheet • Click Chart Wizardbutton • Dialog box opens • Choose charttype, sub-type • Note previewbutton Bent Thomsen - FIT 7-1

35. Creating a Chart • Step 2 • Review and change series range asneeded • Click CategoryLabelsbutton to specifysource of labelsfor chart Bent Thomsen - FIT 7-1

36. Click on Next button to proceed Creating a Chart • Labels now show inlegend • Range for labelsnow displayed Bent Thomsen - FIT 7-1

37. Click on Next button to proceed Creating a Chart • Step 3 • Enter titles (whichwill show on preview) • Specify legend detailson legend tab • Specify Data Label details as shown Bent Thomsen - FIT 7-1

38. Creating a Chart • Step 4 • Specify where chart will appear • Click Finish Bent Thomsen - FIT 7-1

39. Creating a Chart • Chart is displayed as object in worksheet Note Chart toolbar displayed while chart is selected Bent Thomsen - FIT 7-1

40. Moving, Resizing a Chart • With chart selected, mouse cursor changes to when mouse key pressed • This is the "movement pointer" Bent Thomsen - FIT 7-1

41. Formatting a Chart • Chart features may be altered after the fact • Colors, patterns, fill effects, fonts, borders • Area to be formatted must be selected • Be careful to click directly on the chart element • Double Click on Piewedge, Data Pointdialog box appears Bent Thomsen - FIT 7-1

42. Formatting a Chart • Double click on title of chart and Format Chart Title dialog box appears • Note options • Fonts • Patterns • Alignment Bent Thomsen - FIT 7-1

43. Formatting a Chart • Formatted chart shows changes • Box around title • Change in fonts • Pie slice pulled out • Chart toolbar • Select items • Pull updialog boxes Bent Thomsen - FIT 7-1

44. Changing a Chart Type • Possible to use the same data and change to a different chart • Column chart to bar chart • Bar chart to line chart • Can also switch between sub-types • Flat pie chart to 3D pie chart • Important to use a chart type which best represents what the data portrays Bent Thomsen - FIT 7-1

45. Changing a Chart Type • To change chart type • Select the chart • Click on Chart, then Chart Type • Opens Chart Type dialog box Bent Thomsen - FIT 7-1

46. Changing a Chart Type Choosing 3-D sub-type • Chart Type dialog box Specify a type as the default type Bent Thomsen - FIT 7-1

47. Change angle of elevation Rotate view Changing a Chart Type • 3-D view can also be changed • Click Chart, then 3-D view • Opens 3-D View dialog box Bent Thomsen - FIT 7-1

48. Changing a Chart Type • Resulting chart • 3-D view • View has been elevated to 30 degrees Bent Thomsen - FIT 7-1

49. Previewing and Printing a Worksheet • Click the preview button on the standard toolbar • Worksheet preview displayed • note mouse cursoris magnifier • click on area tozoom in Bent Thomsen - FIT 7-1

50. Previewing and Printing a Worksheet • Click the Printbutton • Print dialog boxappears • Note options Bent Thomsen - FIT 7-1