Advanced Microsoft Excel tutorial xls


Télécharger Advanced Microsoft Excel tutorial xls

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Microsoft Excel 2007 Tutorial

CIS*1000*DE

 

Open Microsoft Excel 2007

                                             START                              Double click on

the ICON on

desktop

                              PROGRAMS                  OR

Microsoft Office 2007

Microsoft Excel 2007

Saving your

Workbook

• To save your document, simply click on the MS 2007 logo in the top left?hand corner [         ] and the menu bar that you see on your right here will drop down giving you various options, including saving.

Saving your Workbook cont’d

•      When you save a workbook in MS Excel 2007, it automatically saves with “.xlsx” as its extension.

•      However, you can also save your workbook so that it’s readable by earlier versions of MS Excel (97 – 2003) by simply selecting “Excel 97?2003 workbook (*.xls)” when choosing what file type to save as (please see the screenshot below)

 

                                    Getting Help in MS Excel 2007                     

•     To get help in Excel 2007, look for the help icon [      ] in the top right?hand corner of the screen and click on it.

•     You will be presented with a window that looks like the one here on the right.

Some Terminology

•     Workbook – An Excel file containing several worksheets

•     Worksheet – Rectangular grid of rows & columns that labels and values are inserted into

•     Cell ? The intersection of a row and column, identified by an address (ex. A1, F4, Z55)

•     Value – Numerical data in a cell

•     Label – Non?numerical data in a cell

•     Table – A logically distinct group of cells, visually distinguished with borders and shading


 

Cell Referencing

Navigation Shortcuts

In addition to the intuitive mouse and arrowkey movement between cells, these shortcuts can make lots of data easier to input:

Home:

Move to beginning of row

Ctrl+Home:

Move to “A1”

Enter:

Move one cell down

TAB:

Move one cell to the right

Shift+TAB:

Move one cell to the left

End+     :

Move to last used cell in row

End+    :

Move to last used cell in column

F2:

Edit the the content of a cell

Renaming Worksheets

rename and choose rename

Entering Data

• To enter simple data into a cell we can either:

– Position the cursor on the cell                            and then type on the keyboard and press Enter

OR

– Position the cursor on a cell and then click on the

formula bar,                                            type the data, press Enter or click on the          to accept or on the  d      to cancel.

Inserting New Rows and Columns

•    Click on the row or column header

•    Right click and choose insert

•    Rows are inserted above the selected row

•    Columns are inserted to the left of the selected column

Note: Affected merged cells will automatically grow

Deleting Rows and Columns

An Example…

•     Type a number into any cell in row “8”

•     Right?click on a row header “8” & select Delete

•     Type a number into any cell in column “B”

•     Right?click on the column letter “B”, & select Delete

–    The row/column is removed, along with any formulas, data and formatting. 

–    The adjacent cells are shifted up/left and the row or column is re?labeled to reflect the new change

Column Width

•    Make the first column, A, wide enough to accommodate the width of the text labels.

•    Right?click on the column letter                          and you’ll be given the                           following options:

•    Select “Column Width”

•    Shortcut, you can also click on the line the column letters and drag cursor left or right.

Merging Cells

•    Merging allows one cell to take up multiple rows and/or columns

•    To merge 2 or more cells, highlight the desired cells to merge and click on the merge                                             

& center button:

Merging Cells cont’d

 

Currency Style

•     We can change the format of numerical data to appear as currency.

•     Highlight some columns which contain numbers

•     Click the Currency button  on the Home tab 

Note: there are also buttons for Percentage Style         , and Comma Style      

•     Click on the Decrease Decimal                                   button         twice to remove                                  decimal places

A Quick Note About Percent Style


0.01 = 1%

0.1   = 10%

1      = 100 %

10    = 1000 %

100  = 10000% ¼ = 0.25 = 25%

½ = 0.50 = 50%

¾ = 0.75 = 75%

etc…


Using Formulas

•     A formula is a special entry in a cell, that calculates its value based on other cells, and/or constants

•     By beginning an entry into a cell with an “=” we let Excel know we’re using a formula

•     Without the equal sign “=” Microsoft Excel assumes you’ve entered a label or value

A Simple Formula

• Select any cell on your spreadsheet and type:

=800+100*2

•   Press <Enter>

•   Excel recognizes the ‘=’ sign and calculates the result

•   Note that the formula is shown in the formula bar (while that cell is selected) and that the computed value is displayed in the cell

•   Also note that Excel                                            respects the order                                              of operations                                                  

(BEDMAS)

Linking Cells

•     You can refer to cells in a formula by simply using the cell’s reference name.

•     Enter some data or a label into cell A1

•     Select cell A2 and type:     =A1

•     Now, when the information in A1 is changed, those changes will automatically show up in cell A2 • You can even link cells from different worksheets!

•     For example, in Sheet2 select cell A1 and type:

=My First Spreadsheet!A1 =sheet name!cell address

*Note the Exclamation Mark!!!

Using Cell References In Formulas

•    In Cell A2 type: =A1*2

•    Now, change the value in A1

•    Excel will automatically update the value shown in cell A2

•    So, this is just like a link

the value in A1 is substituted in before Excel

performs the calculations in cell A2

•    This works with cells linked in between different worksheets too! ?

Built?In Functions

•      Excel comes with hundreds of built?in functions which can be used in your formulas.



•      However, the majority of functions need data in order to be useful

–  For example, in order to use the Sum function, you need to tell Excel which values to sum up.

•      These built?in functions can operate by themselves, on a single value, or on multiple values depending on what kind of operation is being performed

–  For example, the Pi function simply returns the value of Pi, the Square Root function needs only one number to work while the Z?Test function (from stats) requires three different numbers in order to work.


•      Built?In Functions are structured like this: FunctionName (Parameter1,…)

•      The FunctionName always comes first and usually describes what the function does.

•      The Parameter is the information that the function needs in order to work

•      This can be a single value or a set of values and can be represented by either numbers (ex. 1, 2, 3, 5, 8, 13, etc…), cell references (ex. A1, B2, C3:D4, etc…) or text.

•      The Parameter(s) always comes second and are always contained within parentheses “ ( ) “.

 

SUM( )

Adds all numbers in a range of cells

PRODUCT( )

Multiplies all the numbers in a range of cells

COUNT( )

Counts all the cells that contain numbers in a range of cells

AVERAGE( )

Calculates the average in a range of cells

MEDIAN( )

Calculates the median in a range of cells

MODE( )

Calculates the mode in a range of cells

POWER( )

Calculates a number raised to a power

SQRT( )

Calculates the square root of a number

MAX( )

Returns the largest number in a range of cells

MIN( )

Returns the smallest number in a range of cells

 

IF( )

Checks whether a condition is met, and returns one value if true, and a different value if false

COUNTIF( )

Count the number of cells within a given range that meet the given condition

PI( )

Returns the value of Pi, accurate to 15 digits

MOD( )

Returns the remainder when a number is divided

 

by

a divisor

LEN( )

Returns the number of characters in some text

ROMAN( )

Converts an Arabic numeral to Roman, as text

CONCATENATE( )

Joins several pieces of text to each other

TODAY( )

Returns the current date

NOW( )

Returns the current date and time

•      There are two different ways to use functions:

1.       The first way is to simply write them out

This will save you time if you know what functions you’ll be using and how to use them

2.       The second way is to use the wizard

This is a great way to learn about the many different kinds of functions and operations that excel can perform and how to use them. =SUM(A1,A5,A10,A15,A20)

•     This formula uses commas and will sum up the values for the five cells A1, A5, A10, A15, A20

=SUM(A1:A20)

•     This formula uses a colon and will sum up the values for the range of cells A1 to A20.

Make sure you understand the difference between these two examples!

•     Let’s do an example!

•     Type numbers into the first 5 rows of column A

•     In cell A6 type: =SUM(A1:A5) • A6 now contains the sum of A1 to A5

•     To find the average you would use:

=AVERAGE(A1:A5)

• How could you find the difference between the biggest number and the smallest number in a range of cells?

=MAX(A1:A5) – MIN(A1:A5)

•   Some functions in Excel accept a condition as a parameter.

This is the range of cells to searchthis is the condition =countif(A1:A100, “>75”)

•   This function will tell us how many cells in the range of A1 to A100 contain a value that is greater than 75.

•   We could have also used  <, =,  <=,  >=  in our condition

                             This is the condition        If TRUE, return this value      If FALSE, return this value

 

=if(A2 > A3, “A2 is bigger”, “A3 is bigger”)

Getting Fancy

=if(countif(A1:A20, “>75”) > 10, “More than half over 75”, “At least half under 75”)

•   So, what does this thing do?

•   Notice how there are 2 different built?in functions.

•   The best way to work through this is to start with the inside   built?in function, in this case, countif( ), and work our way out.

•   The inside function will count the number of cells from A1 to A20 that contain values greater than 75.

•   The outside function will check to see if that “counted” number  of cells is greater than 10, and if it is, then it’ll return “More than half over 75”, otherwise, it’ll return “At least half under 75”


Using the Wizard for Built?In Functions

•   Select any cell on the spreadsheet

•   Click the “=or the “fx” button on the Formula bar

•   The drop?down?box to the left of the “=” can be used to select a function.

 

Using the Wizard for Built?In Functions cont’d

• A help wizard will then guide you through the entry of your formula

 

The Formulas Tab

•    New to MS Excel 2007 is a tab with many of Excel’s built in functions / formulas neatly organized into different categories

•    While some people may prefer to simply type in their desired formula, others may want to explore what Excel has to offer by searching through and experimenting with the options presented in this tab.

Advanced Cell Referencing

•     When we copy/cut & paste a formula Excel transforms it, treating all references as Relative.

•     For example, type random numbers into the first 5 rows of column A again, and enter the formula:

=A1*2 into cell B1

 

•     Now, copy and paste the formula into cells B2, B3, B4, & B5

•     Notice how the formula has changed…

•     …we moved down 3 rows and so too has the cell reference          (it changed from A1 to A4)

 

•     We can also tell Excel not to transform a reference

(making it an Absolute Reference)

•     Let’s do another example

–    In A1 write: “Tax Rate”



–    And in B1, write: “1.15”

•     Next, create a list of items (ex. things that you’d like to sell) in column A and list their prices in their adjacent cells in column B

•     To get the price of the                                         mattress After Tax, we                                         use the formula:

=$B$1*B4

Absolute Relative Reference      Reference

(1.15)     *    ($50.00)    = $57.50                     

 

•     Now, copy and paste the formula into the remaining cells

•     We do this because we want the formula (or rather Excel) to “remember” the location of the tax rate. That way the tax rate is applied to the prices of all the items.

•     If we didn’t add the absolute                                              reference ($B$1), and instead                                   used the relative reference (B1),                               then we would have ended up                                     with the formulas: C5=B2*B5,                                    C6=B3*B6, C7=B4*B7,                                             C8= B5*B8 and that would                                        produced the following: obviously wrong!    


Referencing Review

•     Cells are identified by their:

Column (a letter) & Row (a number)

– Ex. A1, F3

•     Ranges of cells can be referenced using a colon “:” – Ex. A1:B10

•     References to other worksheets take the form:

=‘sheet name’!cell name

For example, ‘My First Spreadsheet’!A1:B10

Relative Referencing Review

•     Most references are relative.

•     For example, when the formula A2*B2 is copied  & pasted into cells C3, C4, C5, and C6, Excel automatically assumes that you wish to apply the formula to row 3 by using A3 & B3 instead of A2 & B2

Absolute Referencing Review

•    When you don’t want a part of your formula transformed you can protect it by adding: ‘$’

•    Columns and rows can be isolated separately or together, for example:

–   $A1   – the column part (A) is locked

–   A$1   – the row part (1) is locked

–   $A$1 – both the row and column are locked

Goal Seek

•    Goal seek is a “what?if” analysis tool

•    Essentially, goal seek changes the value of one cell used in a formula by changing the value of another cell in the formula.

•    Goal seek can be found under “What?If Analysis”

 

Goal Seek (cont’d)

•    With this example I am trying to determine how many apples $5 will buy me.

 

Sorting

•     Sorting can be done from the Home tab by clicking on the “Sort & Filter” option. Choose from…

–    Sort Descending / Sort A to Z

–    Sort Ascending / Sort Z to A

•     Sorting can also be done from the                               Data tab by using the sort buttons

•     To Sort, just highlight the specific cells that you’d like sorted and then choose to have them sorted in Ascending (Z to A) or Descending (A to Z) order.

Sorting cont’d

•    Let’s do an example!

•    Let’s sort this table                                               according to Sex, then                                         

according to Age, and                                           lastly according to Name.

•    Select the entire table and…

•    Home tab      “Sort & Filter”

•    Data tab “Sort & Filter”

Sorting cont’d

 

Sorting cont’d

• As can clearly be                                          seen here, this

table has now been  sorted on three different levels

?

Using Charts

•     Excel offers a variety of chart types.

•     Column, Bar, Line, Pie, and Scatter being the most common choices.

•     But before we can make a chart,                      we’ll need data.

Making A Pie Chart

• For an example  we’ll need some

actual data to chart

so generate a list of                                           monthly expenses                                                such as this one.

 

Making A Pie Chart cont’d

•   To add a chart title, select the Layout tab and click on “Chart Title”

•   Almost everything in the graph can edited by simply right?clicking on the desired component of the graph and selecting the

Making An XY Scatter Plot

   

Making An XY Scatter Plot cont’d

Recall: To edit the graph, use the options found under the Layout tab. From there you can add/edit the chart title, axis titles, legend, etc..

 

Reminder: Formatting a Chart

•    Everything seen on the chart can be selected, formatted, moved, etc.

•    For instance, to change stuff Right Click on:

–   The X?Axis, Y?Axis, Plot Area, Chart Area, Gridlines, Data Series, Legend, or Chart Title and select appropriate option that you’d like to edit

–   You’ll be able to play around with the colours, fonts, scale, alignment, numbering, etc… so explore and find out what’s possible!

 Line Graphs  vs.  Scatter Plots

•     So what is the difference anyways?

•     A Line Graph is almost like a bar graph, except that instead of using bars, it uses points with a line connecting each of the adjacent points together

•     An XY Scatter Plot is the familiar type of graph we all do in math where you have and x?axis and a y?axis that are used to represent coordinates.

•     As well, on a Line Graph, you can use labels on the xaxis (ex. Toronto, Ottawa, Montreal, etc…) while on a XY Scatter Plot, you can only use numbers (ex. 0, 1, 5.5, ½, ?0.23, etc..)

Linking an Excel Chart into Word

•    Highlight a chart in Microsoft Excel

•    Copy it

•    Open a Microsoft Word document

•    Paste it

Note, if the chart in Excel changes, the chart in Word WILL change as well

Importing Data From A Text File

•     To import a data from a text file, go to the Data tab and look for the “Get External Data” section

•     Select the “From Text” option

•     Highlight the file and click “Import”

 

Importing Data From A Text File cont’d

•    The Text Import Wizard should open up

•    Usually you will want to choose “delimited”, as most files come with some sort of separator between the fields

•    Next, select the proper type of delimiter

– For Assignment #2, you will be using a comma delimited file.

•    Now, select “general” as the Data Column Format

•    Click on Finish

Final Notes

•    Practise, Practise, Practise!!!

•    If you have any problems post in the forum



11