1 / 54

Computer Science 1000

Computer Science 1000. Spreadsheets I. Permission to redistribute these slides is strictly prohibited without permission. Computers and Data suppose we ask a computer to remember a retail transaction a list of 5 items the cost of each item the subtotal the tax associated the final total

tal
Download Presentation

Computer Science 1000

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Computer Science 1000 Spreadsheets I Permission to redistribute these slides is strictly prohibited without permission

  2. Computers and Data • suppose we ask a computer to remember a retail transaction • a list of 5 items • the cost of each item • the subtotal • the tax associated • the final total • how does the computer do this? Socks $ 4.99 Pants $ 65.99 Shirt $ 47.99 Tweed Suit Jacket $119.99 Driving Gloves $ 28.99 Subtotal $267.95 Tax $ 13.40 Total $281.35

  3. Computers and Data • computer memory (RAM) – a simplified version • your computer memory is like a massive set of storage “bins” • think of a post office with 8 billion mailboxes • each bin stores a byte of information • each bin has a label • called its address • each running program is given a subset of those bins, called its address space

  4. Computers and Data • when your program wants to store data • it chooses a memory location that’s: • a) large enough • e.g. if it’s storing a piece of text that is 20 characters, it must find a 20 byte piece of memory • b) currently unused • we don’t want to overwrite existing program information • the data is copied to that location in memory • the address of that memory is remembered • this is accomplished using a variable, in programming language speak Computer Memory K name E V

  5. Computers and Data • when your program wants to access data that it has stored • it locates the appropriate spot in memory using its remembered address • the data can be copied from that spot • perhaps to be used in a computation, or sent to an I/O device Computer Memory K name E V

  6. Computers and Data • some points to consider: • the layout of the data in memory is usually not important to the program • as long as it can access the data it needs (by its memory address), that’s fine • from our previous example, perhaps the transaction items get stored at various locations in a program’s memory • the format of the data in memory is usually not important to the program • suppose the price of an item was $4,356.90 • the computer will simply store this (in binary form) as the floating-point number 4356.9

  7. Computers and Data • while layout and format might not be important to the computer, those details are important to the user • layout issues: • proximity creates cohesion – like items can be placed together, while unlike items can be distanced from each other – facilitating categorization (and hence, faster searching) • certain alignment of text facilitates easier reading • aligning decimal places in numbers facilitates easy number comparison • format issues: • certain formatting (currency: commas, dollar signs, two decimal places) are familiar to us • text formatting (bold, italic) allow us to highlight critical information (headers, keywords, etc)

  8. Computers and Data • to summarize: • when dealing with information, layout and formatting of data can facilitate some key advantages for users, even if that information is not necessary for the data to be stored • there are a number of applicationprograms designed to present information in a meaningful way to a person • browsers (markup languages – next week) • spreadsheets (today!)

  9. Spreadsheet • a two-dimensional grid of data, that allows relationships between items of data • each grid location is called a cell • each horizontal line of cells is called a row • each vertical line of cells is called a column • hence, each cell has a unique row/column identification that differentiates it from the other cells

  10. Example (Excel™) Cell

  11. Example (Excel™) Row

  12. Example (Excel™) Column

  13. Spreadsheet – Software • Microsoft Excel • the most widely used office software • part of the Office software suite • OpenOffice Calc • a free spreadsheet alternative • part of the OpenOffice suite • LibreOffice Calc • another free alternative • part of the LibreOffice suite • many similarities (cell addressing, numbered rows, lettered columns) • some differences (e.g. ; vs , ) • for this course, we will use Microsoft Excel, as it is available in your computer lab

  14. Spreadsheet – Row Labeling • each row in a spreadsheet has a label • for most spreadsheet programs, the label is a number • first row labeled with 1 • last row depends on the application and its version • e.g. my Excel 2010 goes up to row 1048576 • rows are often stated as “Row” plus their label • e.g. one would refer to the highlighted row below as “Row 4”

  15. Spreadsheet – Column Labeling • each column in a spreadsheet has a label • for most spreadsheet programs, the label is a letter • first column labeled with A • columns are stated as “Column” plus their label • e.g. one would refer to the highlighted row below as “Column E”

  16. Spreadsheet – Column Labeling • what happens when we run out of letters? • e.g. what is the 27th column called? • the letters roll over, much like digits do • first 26 columns: A – Z • next 26 columns: AA-AZ • next 26 columns: BA-BZ

  17. Spreadsheet – Cell Labeling • a cell typically belongs to one row and one column • this ignores merged cells, but we’ll ignore that for now • a cell’s label is the combination of its column and its row • the cell highlighted in black is A1 • the cell highlighted in red is C3

  18. Cell Data • at it’s simplest level, a spreadsheet simply stores data • each cell holds a value • to insert data into a spreadsheet • click on a cell (this will highlight it) • type the data you want to store • press Enter, or select another cell

  19. Cell Data • what types of data can a spreadsheet hold? • numbers • text • dates/times • each of these can be formatted in a particular way • e.g. numbers can be displayed as currency ($, two decimal places) or a percentage • dates can be displayed in different formats

  20. Spreadsheet – Example • insert the data from our retail transaction into the spreadsheet • items in Column A • prices in Column B (as a simple number) • solution: • highlight Cell A1, type Socks, press Enter • highlight Cell B1, type 4.99, press Enter • repeat for next seven lines Socks $ 4.99 Pants $ 65.99 Shirt $ 47.99 Tweed Suit Jacket $119.99 Driving Gloves $ 28.99 Subtotal $267.95 Tax $ 13.40 Total $281.35

  21. Spreadsheets – Cell Dimensions • in our last example, Tweed Suit Jacket and Driving Gloves do not fit in their cells • hence, part of the item name is hidden behind its price • we need to make the column wider • to do this: • manually: place your cursor at right side of column label, and drag • automatically: select your column (click on column label), and choose Autofit Column Width from the Format dropdown

  22. Manual Autofit

  23. Spreadsheets – Cell Dimensions • note that row dimensions can also be controlled, in the same way as column dimensions • for example, suppose we wanted to provide a bit of separation between the SubTotal line and the items of purchase • manual: place mouse between row labels and drag • autofit: same menu as before, but select Autofit Row Height • note that the automatic solution won’t work for this example, as it won’t increase the height of the cell

  24. Cell Formatting • as mentioned, a formatting can be applied to a cell • note that this does not affect the value being stored, just the value that is displayed • there are many possibilities for formatting • we will consider a few here • text • numbers • some others will be considered in your lab • the best method for learning: try them out

  25. Cell Formatting - Text • formatting text in cells is much like formatting in a word processor • can control font, colour, background, alignment, word wrap, etc • process: • select cells you wish to format • choose the appropriate control from the Ribbon • example: italicize the Subtotal label and amount, and bold the Total and amount

  26. Cell Ranges • the previous works, but is a bit inconvenient • select A6, italicize • select B6, italicize • what if Row 6 contained 25 cells that we wished to apply this to? • Solution: Cell Range • a collection of cells, highlighted at once • any formatting or editing (e.g. delete) will be applied across all cells in a range

  27. Cell Ranges • to select a range: • drag your cursor from the top-left cell of your desired range to the bottom-right • e.g. to select all cells from D2 to F7

  28. Cell Ranges • if a range is rectangular, it is denoted as topLeft : bottomRight • in previous example, selected range was D2:F7 • note that multiple rectangular ranges can be selected at once • denoted as range1, range 2 • e.g. D2:E6, G2:H5 • to select multiple ranges, hold down Ctrl button

  29. Cell Ranges • from previous example • we need to boldface the bottom two cells • let’s use a Range to do this • select the range using drag method • click the bold (B) button in the Font group

  30. Cell Formatting – Numeric • spreadsheets offer different formatting options for numbers • number of decimal places • types (currency, %) • and others • many of these options found in the Number group of the Home ribbon • if no formatting is specifically applied to the cell, then a general formatting is assumed • no trailing zeroes • no commas or symbols

  31. Cell Formatting – Numeric • from previous example, numbers should be formatted as a currency • select the numbers (as a range) • from the drop-down menu in the Number group of the Home ribbon, select Currency

  32. Cell Formatting – Numeric • many other options for numeric formatting not shown in Ribbon • to see these, click the symbol beside the Number label in the ribbon • this dialog box gives detailed control over how a number is displayed

  33. Cells – Stored Value vs. Displayed Value • recall previous comment that formatting and layout doesn’t affect the value being stored • we can see the value being stored by examining the formula bar when a cell is highlighted • the formula bar (labeled fx) is positioned directly above the sheet • as the following slide shows: • text values are stored as is, with no bold/italic etc … • numeric values are stored without any formatting Formula Bar

  34. Cells – Stored Value vs. Displayed Value • why is this important? • the power of a spreadsheet lies in its ability to compute values based on data in other cells (we’ll see this soon) • the value used for computation is the stored value, not the displayed value • in example below, cell A1 has been formatted to show only one digit after the decimal point • if we were to multiply the value of cell A1 by 2, we would get 1.5, not 1.6

  35. Inserting Data • we’ve seen how to append rows of data to an existing spreadsheet • suppose we wish to add data somewhere other than the end • e.g. suppose we want to add another purchased item to our list, or a set of headers • a couple of options • select the entire table as a range, and drag the border using your mouse • insert a new row, by right clicking on the row to insert above, and select Insert

  36. Inserting Rows - Drag Not a good option in this case – notice the taller rows

  37. Inserting Rows – Right Click Much better – the correct rows are still tall.

  38. Inserting Rows • in either case, we’re now free to add headers • we’ll apply cell shading, boldface font, and a larger font size, to make the headings really stand out Font Size Boldface Cell Shade

  39. Spreadsheets - Borders • every cell has a light border surrounding it • called gridlines • extra borders can be added via the borders drop-down • to add extra borders: • select the range that you would like to border • use the drop-down to select border: • placement (all, top, bottom, left, right) • style (thick, thin, etc) • from our previous example: • add a single border above subtotal • add a single border above total • add a double border below total

  40. Adding Border above Subtotal • other borders are added in similar manner

  41. Spreadsheets - Borders • if you wish to disable gridlines (to really see your borders), uncheck Gridlines in View ribbon

  42. Sorting Data • spreadsheets typically allow us to sort data • this has many useful applications • class grades (scholarships) • sports – points (awards, fantasy pools) • method: • select range that you wish to sort • click the sort button • select the type of sort (ascending/descending)

  43. Sorting Data • what happened? • Excel’s sort assumes that your data has a header that is not to be sorted • solution (choose one): • include the header in your range • choose Custom Sort, and deselect the My Data has headers option

More Related