1 / 40

Intro to Excel

Intro to Excel. CSCI130-03A Instructor: Dr. Imad Rahal. Introduction. Spreadsheets are applications used mostly to store, organize, calculate, and display numeric data Tabular format Divided into rows and columns Rows are numbered Columns are labeled with letters

lcaston
Télécharger la présentation

Intro to Excel

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. Intro to Excel CSCI130-03A Instructor: Dr. Imad Rahal

  2. Introduction • Spreadsheets are applications used mostly to store, organize, calculate, and display numeric data • Tabular format • Divided into rows and columns • Rows are numbered • Columns are labeled with letters • Cells hold data (numeric or text), formulas or functions • Power: • Automatically reapplies formulas as data changes • Average of student grades in a grade book • Automatically adapts formulas when copied to new locations

  3. Introduction • Each Excel file or project is called a workbook • File  New  Workbook • Each workbook contains 3 worksheets by default • Each cell in a worksheet may contain • Text • Numeric • (built-in) Functions • Formulas

  4. SHOW “GRADES.XLS” FILE • Rows are numbered • Columns are assigned letters • L5 is highlighted and formula is shown above the sheet – address is • shown in upper left corner • Data is text (left justified), numeric (right justified), formulas and/or functions

  5. SHOW “GRADES.XLS” FILE Formulas • Formula to calculate average • Changes are automatically reflected • Drag to other cells • Copy/Paste • Begin with an = sign • Either type = in cell • Click on the = above worksheet • May contain • Constants • Single cell references: D5 • Functions • AVERAGE, SUM, etc … • Cell ranges: D5:E7 • Connections: +, -, *, /, ^, AND, Or, NOT

  6. SHOW “GRADES.XLS” FILE • Formula to calculate average • Changes are automatically reflected • Drag to other cells • Copy/Paste

  7. Formulas • Once we’ve written the formula for one cell we use it for other rows or columns • No need to rewrite it • Place cursor on the small square in the lower right corner of cell • Hold on the small black that appears by clicking on the right mouse • Drag to desired cells • Notice how formula changes • If target cell is not close to cell holding formula • Highlight formula cell then Edit  Copy • Go to target cell then Edit  Paste

  8. Formulas • Importance of using cell references • Formulas can be updated automatically when copied to different cells • Formulas can be recalculated when referenced data changes • Relative addressing • A7 contains formula A5+A6 • Copy to B7  ? • B7 is one column to the right of A7 • Copy to C9  ? • C9 is two columns to the right of A7 and 2 rows below it

  9. SHOW “DAY CARE CENTER.XLS” FILE • What is the formula for the total charge? • How can we apply for the rest of the parents? • Any problems?

  10. Formulas • Formula is changing as expected but that is NOT what we want • Solution 1: write values as constants in formula • Changing the values implies changing the formulas • A VERY BAD SOLUTION • Solution 2: Absolute addressing • Place a $ sign before row number  row does not change • Place a $ sign before columnletter  column does not change • Both  cell is fixed • New formula

  11. Functions • As you enter the = sign • The name box changes to a listing of available functions • SUM • Adds all values in some range • Guesses the range • SUM(C1:C4) (along a column) • SUM(C1:F1) (along a row) • SUM(C1:F10) (along a block) • Other functions applicable to ranges: AVERAGE, MAX, MIN, COUNT

  12. Conditional Functions • IF and VLOOKUP function • IF-ELSE and Select Case of VB • IF (condition, yes action, no action) • SHOW DAY CARE CENTER.XLS • Enters one of two possible results in a cell based on condition • Employees.xls, IF(G5>50000,10%,12%) • Employees.xls, IF(F5=“F”,”Yes”,”No”) • Condition part be any equality or an inequality • Compound statements by using AND and OR • If worker hired before 01/01/1999 and is full time then they would be eligible for a raise • =IF(AND(F4="F",H4<DATEVALUE("1/1/1999")),"Yes","No")

  13. VLOOKUP • Better if we have more than two options to select from • Corresponding letter grade of numeric grade • Must have an accompanying lookup table • VLOOKUP(x,y,z) • x = cell containing value to lookup • y = table range (top left and bottom right corners) • z = column in table containing the result to write --- always 2 • Table must be organized in ascending order from smallest to largest • One table entry must match the values being looked up • Direct match (equal one of the values) • Bounds match (table gives lower bounds of ranges) • Once the we get the value of the last cell before we cross the boundaries of range of the given value

  14. SHOW “GRADES.XLS” FILE - TRY VLOOKUP WITH TABLE NAME

  15. Review • Power of Spreadsheets derives from • Automatically reapplies formulas as data changes • Automatically adapts formulas when copied to new cells • Given that F11 = G23+E22*$F$2 • What would L34 be if the same formula is copied there? • M46 + K45*$F$2 • A20? • B32+#REF!*$F$2

  16. Multiple Sheets • A workbook can have many worksheets • 3 initially: Sheet1, Sheet2, and Sheet3 • Click on the tab for a sheet brings it to the foreground • Add new sheets • Right on the sheet tab  Insert • Delete sheets by clicking on their tab • Right on the sheet tab  Delete • Rename sheets • Right on the sheet tab  Rename (ONE WORD) • Rearrange sheets • Click on tab, hold down the button, and drag the tab to desired location

  17. Multiple Sheets • When a cell referenced in a formula by row and column (e.g. C5)  in current worksheet • Reference cells in different sheets, add the sheet name followed by ! • Make sure sheet name is a valid variable (1 string with no spaces) • Sheet2!C5 • Place table on different sheet in grades.xls

  18. Cell Naming • A frequently used cell can be named so as not to have to refer to it by its address all the time • Type in name in Name Box • Hit Enter • A range of cells can also be named similarly by selecting it and proceeding as above • If you select a row or column with a title, the title is suggested by default

  19. Sorting • Much easier than VB • On a single column • Click on a cell in that column • Click on Ascending or Descending sort buttons under SORT AND FILTER in the EDITING group • Information in ALL contiguous columns will be moved accordingly • To sort only specific columns, highlight desired area and press desired button • DataSort • On more than one column • Sort by table by total score DESC and then by student names ASC • Sort on names ASC • Sort on score DESC

  20. Graphs • Click on the INSERT ribbon and use one of the chart types in the chart collection • Highlight the data we wish to have on the chart • Select the type of chart • You can preview your data on every chart • Go to Layout Ribbon and add title, gridlines, and data labels • Decide where to place chart • On the Design Ribbon • Click on the location button • Notice how updating data changes the graph accordingly

  21. Forecasting and Simulation • Not only for data entry, nice formatting and calculating formulas • Many functions for simulation and forecasting (whatif analysis) • One of which is thePmt function • Returns the payment amount for a loan based on the interest rate and a constant payment schedule • Some of the functions are: • PMT(RATE, NPER, PV) • RATE is the interest rate for the loan • NPER is the number of payment periods for the loan • PV is the present value or principal of the loan • PMT ALWAYS NEGATIVE UNLESS PV is negative • Show multisheetHousepmts.xls

  22. Macros • Excel built-in functions are great but limited • Macros are a means for the user to define new functions • A macro is a series of Excel actions • Bound together • Executed on a single command • Similar to “command button” subroutines • Good for non-professionals • There are two ways to create them • Recorded macros • Writing macros using VBA (VB for Applications)

  23. Recorded Macros • While working with recorded macros and using Visual Basic within Excel, it is helpful to have the Developer ribbon available • This ribbon might not be currently visible on the main menu • To add the Developer ribbon to the menu, click the Office button and then click Excel Options. • In the Popular category, under “Top options for working with Excel”, check the “Show Developer tab in the Ribbon” check box and click OK. • To record a macro • Click on developer ribbon • Within the Code group, choose Record Macro • You will get a pop-up window asking for a name, description, and control key for the macro • Give your macro a meaningful name, a short description, and assign it to a key for future execution

  24. Recorded Macros • Start by positioning the cursor where you want it to be before the macro begins. • Once you begin recording, everything you do will be recorded, so you don’t want to position your cursor after you start recording, unless you want the macro to begin in the same cell every time it is executed • After you click on ok, the window will disappear and everything you do now will be recorded as part of the macro until you click on Stop Recording in the Code group • Now enter the keystrokes you want as part of your macro, pressing the stop recording button when finished • When you save an Excel sheet containing a macro, make sure you save as it .xlsm – i.e. macro enabled workbook

  25. Recorded Macros • There is an icon in the Code group that allows you to set the addressing mode to relative addressing. • If you don’t change this, the default mode for recorded macros is absolute referencing • Macros designed with absolute addresses always use the same cells on a worksheet, in other words, if you wanted a macro that would print your name in cell A10 and your e-mail address in B10, you would use absolute addressing • You could write the same macro with relative addressing to print your name in whatever cell you position the cursor in, with your e-mail address right below it • Relative addressing is much more flexible, and what you will want most of the time, so make sure this button is pressed before you start recording your macro when you wish to have your macro use relative addressing

  26. Recorded Macros • Past Due Accounting Example • Clear Sheet and save • Record, Select cells, clear and save • Fill penalty column (if overdue=yes) • 10% if amount >$120 • 5% otherwise • Check recorded macros • DeveloperMacrosClick on desiredEdit • Complicated to read • What is FormulaR1C1? • Only good for small things • Adding prerecorded text • Executing simple formulas

  27. Writing Macros using VBA • Much closer to what we did in VB • Simpler statements • Begin as if you’re recording a macro and stop recording • After giving it a name and control character • Go and edit the macro • Past Due Accounting.xls • TheActiveCell and ActiveCell.offset(x,y)

  28. VBA Examples • Macros for a single row • User places cursor in right spot. Use Activecell.Offset(x,y) • Macros that loop downwards • Cells(x,y).Select • Row=0 • Do While ActiveCell.Offset(Row,0)<>”” • ... • Row=Row+1 • Loop • Add the numbers in consecutive cells (in col 1) assuming the first cell is at A1 • Dim Number As Single, Sum As Single • Dim Row As Integer • Cells(1,1).Select • Sum = 0 • Row = 0 • Do While ActiveCell.Offset(Row,0)<>”” • Number = ActiveCell.Offset(Row,0) • Sum = Sum + Number • Row = Row + 1 • Loop

  29. Other Features • Assigning Macros to Buttons • Rather than using Ctrl + Character all the time • More handy to have a button • Insert  Shapes • Draw button • Right Click on the button  Assign macro • Any of the VB commands can be used • InputBox • ActiveCell = InputBox(“Enter you name”,”Namebox”) • Msgbox

  30. Other Features • Reading data from data files • Open file, assign the data to the active cell and them move cell down to across the sheet • File in same folder as project directory • Open ActiveWorkbook.Path & "\data.txt" For Input As #1

  31. File Input in Excel • Design macros for the following, each activated by a separate button • (1) Ask the user for her name via an inputbox and store in active cell • (2) Read data from a text file • Salesperson, Sales • Write salespersons in column A and their sales in column B starting row 4 • (3) Find the salesperson with the highest sales and display his/her name • (4) Find sales of an input salesperson • (3) Find salespersons with sales >= some input • The number of salespersons is not known but • The row after the last salesperson is empty

  32. File Input in Excel • Sub ReadSalesPersons() • Dim Sales As Single, Row As Integer • Dim Salesperson As String • Open ActiveWorkbook.Path & "\sales.txt" For Input As #1 • Range(“A4”).Select • Row=0 • Do While Not EOF(1) • Input#1, Salesperson, Sales • ActiveCell.Offset(Row,0) = Salesperson • ActiveCell.Offset(Row,1) = Sales • Row = Row + 1 • Loop • End Sub

  33. File Input in Excel • Sub FindMax () • Dim MaxSales As Single, MaxiName As String • Dim Row As Integer • Range(“A4”).Select ‘place on first name • Row = 0 • MaxiName = ActiveCell.Offset(0,0) • MaxiSales =ActiveCell.Offset(0,1) • Do While ActiveCell.Offset(Row,0)<>”” • If(ActiveCell.Offset(Row,1) > Maxi) Then • Maxi = ActiveCell.Offset(Row, 1) • MaxiName = ActiveCell.Offset(Row,0) • End If • Row = Row + 1 • Loop • Msgbox “The salesperson with the highest sales is:” & MaxiName , , FormatCUrrent(MaxiSales) • End Sub

  34. Excel Problems • Summaries: • (1) Do something for the Selected-Row using VBA • ActiveCell.Offset(x,y) • (2) Loop throughout a sheet using VBA • Range(“XY”).Select • Row=0 • Do While ActiveCell.Offset(Row,0)<>”” • … • Row = Row + 1 • Loop • (3) Reading from a file to a sheet • Do While Not EOF(1)

  35. File Input in Excel • Design macros for the following, each activated by a separate button • (1) Ask the user for her name via an inputbox and store in active cell • (2) Read data from a text file • Salesperson, Sales • Write salespersons in column A and their sales in column B starting row 4 • (3) Find the salesperson with the highest sales and display his/her name • (4) Find sales of an input salesperson • (3) Find salespersons with sales >= some input • The number of salespersons is not known but • The row after the last salesperson is empty

  36. File Input in Excel • Sub ReadSalesPersons() • Dim Sales As Single, Row As Integer • Dim Salesperson As String • Open ActiveWorkbook.Path & "\sales.txt" For Input As #1 • Range(“A4”).Select • Row=0 • Do While Not EOF(1) • Input#1, Salesperson, Sales • ActiveCell.Offset(Row,0) = Salesperson • ActiveCell.Offset(Row,1) = Sales • Row = Row + 1 • Loop • End Sub

  37. File Input in Excel • Sub FindMax () • Dim MaxSales As Single, MaxiName As String • Dim Row As Integer • Range(“A4”).Select ‘place on first name • Row = 0 • MaxiName = ActiveCell.Offset(0,0) • MaxiSales =ActiveCell.Offset(0,1) • Do While ActiveCell.Offset(Row,0)<>”” • If(ActiveCell.Offset(Row,1) > Maxi) Then • Maxi = ActiveCell.Offset(Row, 1) • MaxiName = ActiveCell.Offset(Row,0) • End If • Row = Row + 1 • Loop • Msgbox “The salesperson with the highest sales is:” & MaxiName , , FormatCUrrent(MaxiSales) • End Sub

  38. Excel Problems • Summaries: • (1) Do something for the Selected-Row using VBA • ActiveCell.Offset(x,y) • (2) Loop throughout a sheet using VBA • Range(“XY”).Select • Row=0 • Do While ActiveCell.Offset(Row,0)<>”” • … • Row = Row + 1 • Loop • (3) Reading from a file to a sheet • Do While Not EOF(1)

More Related