1 / 25

Excel 1& 2

Excel 1& 2. objectives. Enter and edit cell data. Manage worksheets, rows and columns. Apply alignment and number formats . Use semi-selection to create a formula Use relative, absolute, and mixed cell references in formulas Insert a function Total values with the SUM function

sancha
Télécharger la présentation

Excel 1& 2

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. Excel 1&2

  2. objectives • Enter and edit cell data. • Manage worksheets, rows and columns. • Apply alignment and number formats. • Use semi-selection to create a formula • Use relative, absolute, and mixed cell references in formulas • Insert a function • Total values with the SUM function • Insert basic statistical functions • Determine results with the IF function • Use lookup functions • Use range names

  3. Introduction • A worksheet is a spreadsheet that contains formulas, values, text, and visual aids • A workbook is a file containing related worksheets

  4. Navigating Worksheets

  5. Example • Create a new excel workbook. • Rename the sheets to Houses and GradeBook. • Delete third worksheet. • Create a new worksheet ‘Functions’ and create data in it.

  6. Formula • A formula begins with the equal sign = • Examples: =A1+A2 =C2*5 • If data is altered, formulas automatically recalculate results

  7. Semi selection to create formula • Semi-selection uses the mouse pointer to build a formula containing cell references or ranges • This technique is also called pointing

  8. Functions • An Excel function is a predefined formula that performs a calculation • Syntax is the set of rules that govern correct formation of a function • An argument is an input, such as a cell or range • A function begins with the equal sign (=) followed by the function name and arguments in parentheses Example: =SUM(A1:A3)

  9. Function Categories And Description

  10. Inserting a Function • When a function is typed, Formula AutoComplete displays a list of functions matching the partial entry

  11. Inserting basic functions • A function ScreenTip is a small pop-up descriptionthat displays the function arguments • Totaling values with SUM function • =SUM(number 1, [number 2],…..) • Using Average Function • =AVERAGE(number 1,[number2],…..) • Using Median Function • =MEDIAN(number 1,[number2],…..)

  12. More Math and Statistical Functions

  13. Date/Time Functions

  14. Logical Functions • IF function • IF(logical_test,value_if_true,value_if_false) • The IF function has three arguments: • A logical test or condition that is true or false • The resulting value if the condition is true • The resulting value if the condition is false

  15. Nested functions • A nested function occurs when one function is embedded as an argument to another function; for example: =IF(A1<A2,MIN(B1:B5),MAX(B1:B5)) • Compute the MIN function if A1 is less than A2 • Compute the MAX function if A1 is not less than A2

  16. Cell References • Excel offers three types of cell references for use when a formula is copied • Absolute $A$1 • Relative A1 • Mixed $A1 or A$1 • $ indicates that the row number or column letter will not be modified during a copy

  17. Example • Download Excel1&2-Example.xlsx file to your computer and save it. • Copy the contents to the workbook you created. • Apply cell style to cell A1 and cell range A7 to C7. • Adjust column A width as required. • Wrap text for cell range A7 to C7.

  18. Example • Format cell range A8 to A12 as Currency with 0(zero) decimal places. • In cell B8 type the formula =A8*$B$5 • Autofill B9 through B12. • In cell C8 type =A8- and click on cell B8 • Autofill C9 through C12.

  19. Example after working

  20. Lookup functions • Lookup functions are used to look up values in a table to perform calculations or display results • For example, a teacher may want to look up an average in order to assign a grade • When searching a range, the breakpoint is the lowest value

  21. Creating lookup table • A lookup table typicallylists breakpoints in one column and return values in a second column

  22. Vlookup function • The VLOOKUP function searches a lookup table for a value and returns the result from the related column • VLOOKUP has three required arguments: • Lookup value • Table array (range of lookup table) • Column index of return value

  23. example • In Day4-Example.xlxs open GradeBook worksheet • In cell F3 type the formula for lookup as =VLOOKUP(E3,$A$3:$B$7,2) • Autofill the remaining cells.

  24. Range Name • A range name is a word or phrase used to identify a cell or cell range • Range names make formulas easier to read • Create a range name • Click the Formulas tab, click Define Name in the Defined Names group to open the New Name dialog box, type the range name in the Name box, and then click OK • Click in the Name Box, type the range name, and then press Enter.

More Related