1 / 32

259 Lecture 1 – Spring 2013

259 Lecture 1 – Spring 2013. Introduction to Excel. Excel 2003 Definitions and Terminology. Title Bar. Standard Toolbar. Name Box. Formula Bar. Pulldown Menus. Row 15. Cell D15. Column D. Sheet Tab. Excel 2003 Definitions and Terminology. Label (text). Constant (number).

michel
Télécharger la présentation

259 Lecture 1 – Spring 2013

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. 259 Lecture 1 – Spring 2013 Introduction to Excel

  2. Excel 2003 Definitions and Terminology Title Bar Standard Toolbar Name Box Formula Bar Pulldown Menus Row 15 Cell D15 Column D Sheet Tab

  3. Excel 2003 Definitions and Terminology Label (text) Constant (number) Formula (function) Notice that the formula in B8 appears in the Formula Bar

  4. Excel 2007/2010 Definitions and Terminology Office Button Ribbon Home Tab Formula Bar Title Bar Name Box Workbook View Shortcut Row 15 Cell D15 Menu Button Column D Zoom Slider Sheet Tab 4

  5. To show contents of cells, use ctrl` or on the Formulas tab, in the Formula Auditing group, click Show Formulas.

  6. Example 1: Use Excel to create the following Grade Book

  7. Ideas used for Example 1 • Entering labels, constants, and formulas • Formatting cells (font, alignment, number) • Adjusting column width and row height • Fill down and fill right • Built-in functions AVERAGE and SUM

  8. Example 2: Use Excel to make a Semester Schedule

  9. Ideas used for Example 2 • Fill Handle • Merge and Center • Fill Color • Borders • Hyperlink • Format Painter

  10. Example 3: Use Excel to create a plot of Toad Growth • The following table shows the land area in Australia colonized by the American marine toad (Bufo marinis). • Let’s make a plot of Year vs. Area on a Cartesian coordinate system (Year is on the x – axis …)

  11. Making a Chart • Open up a blank Excel worksheet and enter the data for the toad population of Australia over time in the first two columns. • Highlight the cells with data including headings. • On the Insert tab, look in the Charts group for the different types of charts that can be chosen.

  12. Step 1 – Chart Type • The first thing we need to do is to choose an appropriate chart to display the data – in this case we want to show the relationship between the toad population and a given year. • By moving the cursor over a type of chart, you will get a description of how the chart can be used. • A scatter plot compares pairs of values. • Click on the Scatter chart type and choose Scatter with only Markers (the top-left-most chart).

  13. Step 2 – Chart Design and Location • A chart will appear in the sheet. • In the Design tab (under Chart Tools), use the Chart Layout and Chart Styles menus to adjust your chart if needed. • Choose a chart layout without any horizontal lines. • Also note that the chart can be moved to its own sheet via the Move Chart Location menu.

  14. Step 3 – Chart Options • In the Layout tab, under the Design Tools, use the appropriate menus to remove the legend, add a Chart Title, and add axis labels (Axis Titles). • Other chart features can be adjusted from these menus. • For example, we can add a trendline.

  15. Adding a Trendline • Once we have a scatter plot, we can try to find a curve that fits the data. • The simplest type of curve is a straight line or “best fit line”. • Right click on any data point in the chart and choose Add Trendline. • In the new window, choose Linear for the Trend/Regression Type and check the Display Equation on Chart box. • Here is the resulting chart with trendline included! • In this case, the best fit line is y = 13568 x – 3*10^7.

  16. Making a Chart “Automatically” • A chart can also be made by highlighting the data to be put in the chart and pressing the F11 key. • The default chart is a bar chart. • From the Change Chart Type menu you can choose a new chart type and perform the same steps as above!

  17. Importing data from a text file • Often the data we need is given as ASCII characters in a text file. • We can use Excel to open the file and help put the data into into “more usable” form.

  18. Importing data from a text file (cont.) • From Excel, open up the text file that contains the data, with commas or spaces between each piece of data. • The Text Import Wizard will appear!

  19. The Text Wizard • Choose “Delimited” and click Next. • Choose Comma in the Check-box and click Next. • Set the column formats and choose Finish. • The data should be in Excel!

  20. Ideas used for Example 3 • Making a chart with the Chart Wizard • Using F11 to create a chart automatically • Importing data from a text file

  21. Example 4: Create the following Function Table!

  22. Example 4 (cont.) • Creating the first three columns in the table for Example 4 with user-defined functions is straightforward. • For the last column, notice that if we let x(n) = 1+2+ … + n, then we have • x(1) = 1 • x(2) = 3 = 1 + 2 = x(1) + 2 • x(3) = 6 = 1 + 2 + 3 = x(2) + 3 • … • In general, x(n) = x(n-1) + n for n ≥ 2. • Functions like this are called recurrence relations and can be implemented with Excel!

  23. Example 4 (cont.)

  24. Example 4 (cont.) • To print out a table that appears on more than one page, choose the Page Layout tab. • Then choose either Print Titles or the dropdown menu from the Page Setup group. • Click on the Sheet tab and choose the rows to repeat at the top of each page!

  25. Ideas used for Example 4 • Fill handle • Fill down • Creating a formula • Page setup for printing • Recurrence relation

  26. Example 5: Sorting Data • Excel is excellent for sorting data in lists or tables! • For example, suppose we wish to sort a list of famous mathematicians by given Birth Year, Name, and Birth Year followed by Name! • First put the data into an Excel worksheet with Birth Year in column A and Name in column B.

  27. Example 5 (cont.) • To sort by Birth Year, click on any cell in the Birth Year column (column A). • Then click on the Sort Smallest to Largest button. • Repeat with column B to sort by Name!

  28. Example 5 (cont.) • Another option is to choose a cell within the data you wish to sort. • Then click on the Data tab and choose Sort. • The Sort menu allows recursive sorting in either ascending or descending order!

  29. Example 5 (cont.) • When working with data, a useful tool for choosing portions of the data is the AutoFilter. • Select a cell in the data you wish to study, click on the Data tab, and choose the Filter button.

  30. Example 5 (cont.) • With the AutoFilter, you can look at subsets of the data, for example the mathematicians born between 1600 and 1899. • To do so, choose the Number Filters menu in the first column and fill in the Custom AutoFilter accordingly.

  31. Ideas used for Example 5 • Sort Smallest to Largest button • Sort menu • AutoFilter

  32. Homework 1 (due Monday, 1/14/13) • Read the Excel Tutorial posted on our class web page, take the online quiz, and turn in a printed copy of your quiz and quiz score at the beginning of class.

More Related