Microsoft Excel
[pic]
Lecturer: Haider Abdul Kareem Aljendeel
First Class
Microsoft Excel
Spreadsheet Basics
Microsoft Excel is a spreadsheet program that you can use to organize, analyze and attractively present data such as a budget or sales report. Each Excel file is a workbook that can hold many worksheets. The worksheet is a grid of columns, designated by letters, and rows, designated by numbers. The letters and numbers of the columns and row called labels are displayed in gray buttons across the top and left side of the worksheet. The intersection of a column and a row is called a cell. Each cell on the spreadsheet has a cell address that is the column letter and the row number. Cells can contain text, numbers, or mathematical formulas.
[pic]
Title bar
The Title bar contains the name of the program Microsoft Excel, and the default name of the file Book 1 that would change as soon as you save your file.
Menu bar
The Menu bar contains menus that include all the commands you need to use to work
your way through Excel such as File, Edit, View, Insert, Format, Tools, Data,
Window, and Help.
Standard Toolbar
This toolbar is located just below the Menu bar at the top of the screen and allows you to quickly access basic Excel commands.
[pic]
[pic]
[pic]
Task Pane
The Task Pane appears each time you start Excel. To display or hide the task pane:
From the Menu bar, select View > Task pane To close it, click on the small X button at the top-left corner.
The task pane contains several options:
• Getting started: It allows you to connect to the internet to get more information
on Microsoft Excel also you can open saved files from your local PC.
• Help: in case you are lost and you need some feedback. Under Search For you
can directly type your keyword and Excel will provide you with information
(on/offline).
• Search Results: Allows you to view the result of your previous search under
Help.
• Clip Art: allows you to search the Clip Art Gallery using keywords.
• Research: if you are doing a research Excel can provide you with online
information. You can choose the reference books you would like Microsoft to
take into consideration while searching.
• Clipboard: a list of the items you have recently cut, pasted, or copied
• New Workbook: you can open a new blank workbook or select one from the
existing workbooks available in your local computer, or select one of the
templates saved in Excel.
• Shared Workspace: you can create a document workspace if you want to share a
copy of your document. A workspace also enables you to invite others, assign
them tasks, and link to additional resources.
Adding and Renaming Worksheets
The worksheets in a workbook are accessible by clicking the Worksheet tabs in the lower part of the screen. By default, three worksheets are included in the default
workbook. To add a sheet, select Insert > Worksheet from the Menu bar. To rename the Worksheet tab, right-click on the tab with the mouse and select Rename from the
Shortcut menu. Type the new name and press the Enter key.
Adding Worksheets, Rows, Columns, and Cells
• Worksheets: Add a worksheet to a workbook by selecting Insert > Worksheet
from the Menu bar.
• Row: To add a row to a worksheet, select Insert > Rows from the Menu bar, or
highlight the row by clicking on the row label, right-click with the mouse, and
choose Insert.
• Column: Add a column by selecting Insert > Columns from the Menu bar, or
highlight the column by clicking on the column label, right-click with the mouse,
and choose Insert.
• Cells: Add a cell by selecting the cells where you want to insert the new cells,
Click Insert > Cells > Click an option to shift the surrounding cells to the right or
down to make room for the new cells.
Resizing Rows and Columns
There are two ways to resize rows and columns: The first way is to resize a row by dragging the line below the label of the row you would like to resize. Resize a column in a similar manner by dragging the line to the right of the label corresponding to the column you want to resize.
Or
The second way is to click the row or column label and select Format > Row > Height or Format > Column > Width from the Menu bar to enter a numerical value for the height of the row or width of the column.
Selecting Cells
Before a cell can be modified or formatted, it must first be selected (highlighted). Refer to the table below for selecting groups of cells.
|Cells to select |Mouse action |
|One cell |click once in the cell |
|Entire row |click the row label |
|Entire column |click the column label |
|Entire worksheet |click the whole sheet button (upper left corner of the labels “empty |
| |label”) |
|Cluster (مجموعة)of cells |drag mouse over the cells or hold down the SHIFT key while using the |
| |arrow keys |
Deleting Rows, Columns, and Cells
Rows: select the row by clicking its number, Click Edit > Delete
Columns: select the column by clicking its letter, Click Edit > Delete
Cells: select the cells you want to delete, Click Edit > Delete
Freeze Panes
If you have a large worksheet with column and row headings, those headings will
disappear as the worksheet is scrolled. By using the Freeze Panes feature, the headings can be visible at all times.
[pic] [pic]
1. Click the label of the row that is below the row that you wish to keep frozen at the
top of the worksheet.
2. Select Window > Freeze Panes from the Menu bar.
Note: To remove the frozen panes, select Window > Unfreeze Panes
Freeze panes have been added to row 1 in the image above. Notice that the row
numbers skip from 1 to 6. As the worksheet is scrolled, row 1 will remain stationary
while the remaining rows will move.
Formulas
Formulas are entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operators placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar. See the example below to view the formula for calculating the subtotal for a number of textbooks. The formula multiplies the quantity and price of each textbook and adds the subtotal for each book.
[pic]
Linking Worksheets
When working with formulas, you may want to use a cell from a worksheet other than your current worksheet. For example, the value of cell A1 in the current worksheet and cell A2 in the second worksheet can be added using the format "sheetname! cell-address". The formula for this example would be "=A1+Sheet2! A2" where the value of cell A1 in the current worksheet is added to the value of cell A2 in the worksheet named "Sheet2".
Relative, Absolute, and Mixed Referencing
Calling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if a simple addition formula in cell C1 "= (A1+B1)" is copied to cell C2, the formula would change to "= (A2+B2)" to reflect the new row. To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs "$" within the cell addresses in the formula. Continuing the previous example, the formula in cell C1 would read "= ($A$1+$B$1)" if the value of cell C2 should be the sum of cells A1 and B1. Both the column and row of both cells are absolute and will not change when copied. Mixed referencing can also be used where only the row or column are fixed. For example, in the formula "= (A$1+$B2)", the row of cell A1 is fixed and the column of cell B2 is fixed.
Basic Functions
Functions can be a more efficient way of performing mathematical operations than formulas. For example, if you wanted to add the values of cells D1 through D10, you would type the formula "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10". A shorter way would be to use the SUM function and simply type "=SUM (D1:D10)". Several other functions and examples are given in the table below.
Function Example Description
SUM =SUM(A1:A100) finds the sum of cells A1 through A100
AVERAGE =AVERAGE(B1:B10) finds the average of cells B1 through B10
MAX =MAX(C1:C100) returns the highest number from cells C1 . . through C100
MIN =MIN(D1:D100) returns the lowest number from cells D1 . . . . through D100
SQRT =SQRT(D10) finds the square root of the value in cell D10
Functions & Formulas Fundamentals
The following definitions are necessary to understand the basics of creating Excel
formulas and functions.
Formula Definition
A formula allows you to calculate and analyze data in your worksheet. Formulas perform calculations such as addition or multiplication; formulas can also combine values.
Formula Syntax
Formula syntax (تركيبة)is the structure or order of the formula elements. All formulas begin with an equal sign (=) in Excel followed by operands (the data to be calculated) and the operators. Operands can be values that don’t change (constants), a range reference, a label, a name, or a worksheet function.
Formula Bar
The Formula bar is an area located at the top of the worksheet window that is used to
enter or edit values or formulas in cells or charts. The Formula bar displays the constant value or formula in the active cell. To display or hide the Formula bar; select from the Menu bar, View > Formula.
[pic]
Function Definition
A function in Excel is a built-in formula that performs a mathematical operation or
returns information specified by the formula. As with every formula created in Excel,
each function starts with an equal (=) sign.
Function Syntax
The syntax of a function begins with the function name, followed by an opening
Parenthesis(قوس), the arguments for the function separated by commas, and a closing
parenthesis. If the function starts a formula, an equal sign (=) displays before the function
name. Example: =SUM (D2:F8)
In the above example, the function name is Sum and the argument for the function is the range “D2:F8”.
Arguments
An argument(الازاحة) is the reference behind the function. The reference can be any of the following type:
[pic]
[pic]
[pic]
Operator Order
Formulas are calculated left to right, using order of precedence(الاسبقية), the parentheses have high order of precedence, i.e.:every thing inside them is evaluated first.
Excel performs operations in the order shown in the following table.
[pic]
Errors in Formulas
When a formula is prevented to run normally, Excel will notify you with an error
message. Each error message helps users identify the problem they are facing. The
following table lists common Excel errors that you might face.
[pic]
Use the fill handle to fill data
You can use the Series command (point to Fill on the Edit menu, and then click Series) to fill data into worksheet cells. You can also have Excel automatically continue a series of numbers, number and text combinations, dates, or time periods, based on a pattern that you establish. However, to quickly fill in several types of data series, you can select cells and drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) [pic].
Fill in a series of numbers, dates, or other built-in series items
Using the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.), you can quickly fill cells in a range with a series of numbers or dates or with a built-in series for days, weekdays, months, or years.
1-Select the first cell in the range that you want to fill.
2-Type the starting value for the series.
3-Type a value in the next cell to establish a pattern.
For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4. If you want the series 2, 2, 2, 2..., you can leave the second cell blank.
4-Select the cell or cells that contain the starting values.
5-Drag the fill handle [pic]across the range that you want to fill.
To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.
ABSOLUTE REFERENCE
You can use the dollar sign ($) to "fix" or "freeze" a column or a row in a formula. When the formula will be copied, the parts that are frozen will not change. To freeze, you must place the dollar sign ($) in front of the column or row position.
|A1 |Neither the column nor the row is frozen. |
|$A1 |The column is frozen but not the row. |
|A$1 |The row is frozen but not the column. |
|$A$1 |The row and the column are frozen. |
Example:
Write numbers and following formula in the appropriate cells.
A1: 100 , A2: 200 , A3: =$A1+A2 now use autofill until you reach E3
The formulas in the B3 to E3 cells are:
|Cell |Result |Formula |
|B3 |100 |=$A1+B2 |
|C3 |100 |=$A1+C2 |
|D3 |100 |=$A1+D2 |
|E3 |100 |=$A1+E2 |
By "freezing" the A column in the formula, this reference will always look for the value in the first column (A). This is necessary when you want to look for the contents of a cell that contains a variable and you wish to copy the formula. A formula that has a relative position would not allow you to look for the contents of the A1 cell once it's copied elsewhere. By freezing the column or the row in the formula, you'll not be obliged to change the formula once you copied it to other cells.
If you want to copy a formula horizontally, you may only need to freeze the column reference ($A1). If you wish to copy a formula vertically, you may need to freeze the row or row reference (A$1). If you a coping the formula to a different row and column, you may need to freeze both. It depends on where the data is located relative to the position of your formula. If they're always in the same cell, you need to freeze the column of row to get an absolute reference. Excel will always look for the data in the same column or row.
Function in Microsoft Excel
The cell range:
Use the cell range to identify some adjacent cell. The range are identified by the first and last cell in the range, and separated by a colon(:) symbols.
[pic] A1:D4
[pic] A2,B3,C2
1- Sum Function
Adds all the numbers in a range of cells
Syntax(التركيبة)
=sum(number1,number2,…..)
number1,number2,…are 1 to 255 arguments for which you want the total value or sum.
=sum(Range)
Example: For the above figure
1- =sum(A1:D4) 2- =sum(A2,B3,C2)
Example1 :1-Find the summation of
each Column put the result in row 5
2)Find the total sum .
Answer:
1)=sum(A2:A4) then autofill
2)=sum(A2:D4)
Example2:Find the total sum
=sum(A1,A3,B2,C1,D3)
2- Sumif Function
Adds the cells specified by a given criteria
Syntax
1- If the range to be test not as same as the range to be sum
Sumif(range,criteria,sum-range)
Range to be test Range to be sum
2-If the range to be test the same as the range to be sum
Sumif(range,criteria)
Example1: Find the summation of cell
Which have values greater than 40
Ans: sum(A2:D4;">40";A2:D4)
Example 2: Find the total sum of sales in January
Month for sales shop from the following table
Answer:
=Sumif(a2:a8;"=January";b2:b8)=170
3- Average Function
Returns the average(arithmetic mean) of the arguments
Syntax
Average(number1,number2,….)
number1,number2,…are 1 to 255 arguments for which you want the average
Example: Find the total average of the
Cells
=average(A1:D5)
4- Averageif Function
Returns the average (arithmetic mean) of the cells in a range that mean a given criteria.
Syntax
=Averageif(range,criteria,average_range)
Range is one or more cells to average, including numbers or names, arrays,or references that contain numbers.
Criteria is the criteria in the form of a number,expression,cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32,"32",">32","apples", or B4.
Average _range is the actual set of cells to average.
Example: Find the average for the
Cells which have values greater than 45
=averageif(A1:D5,">45",A1:D5)
Or = averageif(A1:D5,">45")
=73.23
5- COUNT FUNCTION
Counts the number of cells that contain numbers, including dates and formulas. Ignores all blank cells and cells that contain text and error.
Syntax
=count(value1,value2,…..)
Value1 and value2,… are 1 to 255 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.
6- COUNTA FUNCTION
Counts the numbers of cells that contain numbers, including dates, formulas, and cells that contain text or error. Ignore all blank cell.
Syntax
=CountA(value1,value2,…..)
Value1 and value2,… are 1 to 255 arguments representing the values you want to count.
7- COUNTBLANK
Counts empty cells in a specified range of cells
Syntax
=Countblank(range)
Example:
Find COUNT,COUNTA,
COUNTBLANK
=COUNT(A1:D5)=15
=COUNTA(A1:D5)=18
=COUNTBLANK(A1:D5) =2
8- COUNTIF FUNCTION
Counts the number of cells within a range that meet the given criteria.
Syntax
=Countif(Range,criteria)
Range: Is one or more cells to count , including numbers or names, arrays or references that contain numbers.Blank and text values are ignored.
Criteria : is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example criteria can be expressed as 32,"32",">32","Apples" or B2.
Example: Find the numbers of cells that contain values greater than or equal 35
=countif(A1:E4;">=35")
=14
9-Standard Deviation
Estimate standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value(the mean).
Syntax
=STDEV(number1,number2,….)
STDEV uses the following formula:
[pic]
Where x is the sample mean average(number1,number2,.....) and n is the sample size.
Example: Find the standard deviation
=STDEV(A1:E4)
10- Median Function
Return the median of the given numbers. The median is the number in the middle of a set of numbers.
Syntax
=median(number1,number2,….)
Example: Find the median of the following data
Ans:
=median(A1:D5)
11- Round Function
Rounds a number to a specified number of digits
Syntax
Round(number, number of digits)
Number: is the number you want to round
Number of digits: Specifies the number of digits to which you want to round number.
12- Roundup Function
Rounds a number up, away from zero
Syntax
Roundup(number, number of digits)
Number: is any real number you want to rounded up.
Number of digits: Specifies the number of digits to which you want to round number.
13- Rounddown Function
Rounds a number down, toward zero
Syntax
Rounddown(number, number of digits)
Number: is any real number you want to rounded down.
Number of digits: Specifies the number of digits to which you want to round number.
Example:
[pic]
14- INT Function
Rounds a number down to the nearest integer
Syntax
=int(number)
Number: is the real number you want to round down to an integer
Example:
int(3.9)=3
15- MOD Function
Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
Syntax
=MOD(number, divisor)
Number: Is the number for which you want to find the remainder.
Divisor: Is the number by which you want to divide number.
Remarks:
* If divisor is 0, MOD returns the #DIV/0! Error value.
* The MOD function can expressed in terms of the INT function:
MOD(n,d)=n-d*INT(n/d)
Example : if n=5 and d=2
Mod(5,2)=1
Or MOD(n,d)=n-d*INT(n/d)
=5-2*int(5/2)=1
16- If function
Returns one value if a condition you specify evaluated to TRUE and another value if it evaluated to FALSE.
Use IF to conduct conditional tests on values and formulas.
Syntax
IF(logical_test,value_if_true,value_if_false)
Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.
Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.
Example: Compose the following table to calculate the value of [pic] only:
a) If value of x less or equal 15.
b) If value of z greater or equal 22.
| |A |B |C |D |E |F |
|1 |x |8 |25 |11 |18 |10 |
|2 |z |20 |4 |8 |12 |30 |
|3 |y | | | | | |
Answer:
1) If(B1=22; B1^2+sqrt(b2);"error")then use Autofill
|x |8 |25 |11 |18 |10 |
|z |20 |4 |8 |12 |30 |
|y |error |error |error |error |105.477 |
17- MAX Function
Returns the largest value in a set of values.
Syntax
MAX(number1,number2,...)
Number1, number2, ... are 1 to 255 numbers for which you want to find the maximum value.
Remarks
• You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors. (في حالة وجود خطا في احد الخلايا فسوف يعطي خطا في الناتج)
• If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text must not be ignored, use MAXA instead.(في حالة وجود خلايا فارغة او تحتوي على جمل فيتم اهمالها اما اذا لم يكن يراد اهمالها فيتم استخدام MAXA
• If the arguments contain no numbers, MAX returns 0 (zero).
اذا لم يكن هنالك اي رقم فان اكبر رقم سيكون صفر
Note: In MAXA Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).
Example: Find the maximum from the
following table:
Answer: =Max(A1:D4)=765
18- MIN Function
Returns the smallest number in a set of values.
Syntax
MIN(number1,number2,...)
Number1, number2, ... are 1 to 30 numbers for which you want to find the minimum value.
Example: Find the Minumum from the
following table:
Answer: =Min(A1:D4)=23
19- LN Function
Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).
Syntax
LN(number)
Number is the positive real number for which you want the natural logarithm.
Remark
LN is the inverse of the EXP function.
Example:
| |A |B |
|1 |Formula |Description (Result) |
|2 |LN(86) |Natural logarithm of 86 (4.454347) |
|3 |LN(2.7182818) |Natural logarithm of the value of the constant e (1) |
|4 |=LN(EXP(3)) |Natural logarithm of e raised to the power of 3 (3) |
20-EXP Function
Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm.
Syntax
EXP(number)
Number is the exponent applied to the base e.
Remarks
• To calculate powers of other bases, use the exponentiation operator (^).
• EXP is the inverse of LN, the natural logarithm of number.
Example:
| |A |B |
|1 |Formula |Description (Result) |
|2 |exp(1) |Approximate value of e (2.718282) |
|3 |exp(2) |Base of the natural logarithm e raised to the power of 2 |
| | |(7.389056) |
21- LOG Function
Returns the logarithm of a number to the base you specify.
Syntax
LOG(number,base)
Number is the positive real number for which you want the logarithm.
Base is the base of the logarithm. If base is omitted, it is assumed to be 10
Example:
| |A |B |
|1 |Formula |Description (Result) |
|2 |log(10) |Logarithm of 10 (1) |
|3 |=LOG(8, 2) |Logarithm of 8 with base 2 (3) |
|4 |=LOG(86, 2.7182818)|Logarithm of 86 with base e (4.454347) |
22-LOG10 Function
Returns the base-10 logarithm of a number.
Syntax
LOG10(number)
Number is the positive real number for which you want the base-10 logarithm.
Example:
| |A |B |
|1 |Formula |Description (Result) |
|2 |1.934498451 |Base-10 logarithm of 86 (1.934498451) |
|3 |LOG10(10) |Base-10 logarithm of 10 (1) |
|4 |LOG10(100000) |Base-10 logarithm of 1E5 (5) |
|5 |LOG10(10^5) |Base-10 logarithm of 10^5 (5) |
23- PI Function
Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
Syntax
PI( )
Example:
| |A | |
|1 |Radius | |
|2 |3 | |
| |Formula |Description (Result) |
| |PI() |Pi (3.14159265358979) |
| |PI()/2 |Pi/2 (1.570796327) |
| |PI()*(A2^2) |Area of a circle, with the radius above (28.27433388) |
24- SQRT Function
Returns a positive square root.
Syntax
SQRT(number)
Number is the number for which you want the square root.
Remark
If number is negative, SQRT returns the #NUM! error value.
| |A | |
|1 |Data | |
|2 |-16 | |
| |Formula |Description (Result) |
| |SQRT(16) |Square root of 16 (4) |
| |SQRT(A2) |Square root of the number above. Because|
| | |the number is negative, an error is |
| | |returned (#NUM!) |
| |SQRT(ABS(A2)) |Square root of the absolute value of the|
| | |number above (4) |
25- FACT Function
Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.
Syntax
FACT(number)
Number is the nonnegative number you want the factorial of. If number is not an integer, it is truncated(يقطع اي ان الحاسبة سوف تحوله الى عدد صحيح بحذف الاعشار ).
| |A |B |
|1 |Formula |Description (Result) |
|2 |FACT(5) |Factorial of 5, or 1*2*3*4*5 (120) |
|3 |FACT(1.9) |Factorial of the integer of 1.9 (1) |
|4 |FACT(0) |Factorial of 0 (1) |
|5 |#NUM! |Negative numbers cause an error value |
| | |(#NUM!) |
|6 |FACT(1) |Factorial of 1 (1) |
26- SIN Function
Returns the sine of the given angle.
Syntax
SIN(number)
Number is the angle in radians for which you want the sine.
Remark
If your argument is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
| |A |B |
|1 |Formula |Description (Result) |
|2 |SIN(PI()) |Sine of pi radians (0, approximately) |
|3 |SIN(PI()/2) |Sine of pi/2 radians (1) |
|4 |SIN(30*PI()/180) |Sine of 30 degrees (0.5) |
|5 |SIN(RADIANS(30)) |Sine of 30 degrees (0.5) |
27- COS Function
Returns the cosine of the given angle.
Syntax
COS(number)
Number is the angle in radians for which you want the cosine.
Remark
If the angle is in degrees, multiply it by PI()/180 or use the COS function to convert it to radians.
| |A |B |
|1 |Formula |Description (Result) |
|2 |COS(1.047) |Cosine of 1.047 radians (0.500171) |
|3 |COS(60*PI()/180) |Cosine of 60 degrees (0.5) |
|4 |COS(RADIANS(60)) |Cosine of 60 degrees (0.5) |
28- TAN Function
Returns the tangent of the given angle.
Syntax
TAN(number)
Number is the angle in radians for which you want the tangent.
Remark
If your argument is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians
| |A |B |
|1 |Formula |Description (Result) |
|2 |TAN(0.785) |Tangent of 0.785 radians (0.99920) |
|3 |TAN(45*PI()/180) |Tangent of 45 degrees (1) |
|4 |TAN(RADIANS(45)) |Tangent of 45 degrees (1) |
29- ABS Function
Returns the absolute value of a number. The absolute value of a number is the number without its sign.
Syntax
ABS(number)
Number is the real number of which you want the absolute value.
| |A | |
|1 |Data | |
|2 |-4 | |
| |Formula |Description (Result) |
| |ABS(2) |Absolute value of 2 (2) |
| |ABS(-2) |Absolute value of -2 (2) |
| |ABS(A2) |Absolute value of -4 (4) |
Create an array formula
Array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.).
When you enter an array formula, Microsoft Excel automatically inserts the formula between { } (braces).
About array formulas and array constants
An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula.
Array constants can be used in place of references when you don't want to enter each constant value in a separate cell on the worksheet.
Some of the built-in functions are array formulas, and must be entered as arrays to get the correct results.
Calculate a single result
You can use an array formula to perform several calculations to generate a single result. This type of array formula can simplify a worksheet model by replacing several different formulas with a single array formula.
1. Click the cell in which you want to enter the array formula.
2. Type the array formula.
For example, the following calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the individual values for each stock.
When you enter the formula ={SUM(B2:C2*B3:C3)} as an array formula, it multiples the Shares and Price for each stock, and then adds the results of those calculations together.
3. Press CTRL+SHIFT+ENTER.
Calculate a multiple result
Some worksheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments have.
1. Select the range of cells in which you want to enter the array formula.
2. Type the array formula.
For example, given a series of Price figures (column B) for a series of amoumts (column A), the function determines the straight-line values.To display all of the results of the formula, it is entered into six cells in column C (C1:C6).
1. When you enter the formula = (B1:B6*A1:A6) as an array formula, it produces six separate results, based on the six sales figures and the six amounts.
2. Press CTRL+SHIFT+ENTER.
[pic]
Correlations:
Returns the correlation coefficient of the array1 and array2 cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners.
Syntax
CORREL(array1,array2)
Array1 is a cell range of values.
Array2 is a second cell range of values.
Remarks
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• If array1 and array2 have a different number of data points, CORREL returns the #N/A error value.
• If either array1 or array2 is empty, or if s (the standard deviation) of their values equals zero, CORREL returns the #DIV/0! error value.
• The equation for the correlation coefficient is:
[pic]
where x and y are the sample means AVERAGE(array1) and AVERAGE(array2).
Example1: Use array formulas to calculate the value of R assuming that the values of x and y is located in the range B1:J2. And m is the number of data points.
[pic]
Answer: from the data m=9
[pic]
=(9*SUM(C1:H1*D2:I2)-SUM(C1:J1*C2:J2))/FACT(9) then Press CTRL+SHIFT+ENTER
Example2: Use array formulas to calculate the value of R assuming that the values of x and y is located in the range C1:K2. And m is the number of data points.
Answer: from the data m=9
[pic]
=8*SUM(E1:K1*C2:I2)/(FACT(9)*SUM(SQRT(D1:K1*C2:J2)))
CTRL+SHIFT+ENTER
Example3:
Use array formulas to calculate the value of R assuming that the values of x and y is located in the range A1:B8. And m is the number of data points.
[pic]
Answer: m=8 from the data
[pic]
=SUM(A1:A8-B1:B8/(8*(8-4))) and press CTRL+SHIFT+ENTER
ملاحظة : في المصفوفات هنالك ثلاث حالات الاولى اذا كان المطلوب ناتج واحد وذلك اذا كان المطلوب حاصل جمع او اي عملية رياضية تؤدي الى الحصول على ناتج واحد وبعد الانتهاء من كتابة المعادلة نضغط المفناح CTRL+SHIFT+ENTER اما الثاني اذا كان هنالك اكثر من ناتج فنقوم اولا بتظليل منطقة الناتج ثم نكتب المعادل ثم نضغط لى المفتاح CTRL+SHIFT+ENTER اما الحالة الثالثة اذا كان المطلوب حاصل جمع او عدد ويكون مشروط بشرط معين فان المعادلة سوت تكتب كما في ادناه مع ملاحظة استعمال فقط sum والشرط يكون في الداخل اي اننا لانستعمل sumif او countif .
Example: Compose the following worksheet and apply array formula
1) Find the summation of sales where January month
And the region is north?
2) Find the summation of sales where January month
And the region is south?
Answer:
1) =SUM ((A2:A7="january") * (B2:B7="north") * (C2:C7)) and press CTRL+ SHIFT + ENTER=150
2) =SUM((A2:A7="january")*(B2:B7="south")*(C2:C7)) =30
Analysis:
To analyze go to Tools then formula auditing then evaluate formula
[pic]
[pic]
Then press evaluate
حيث سوف يذهب اولا الى العمود A فاذا كان الشهر يساوي January فيحوله الى True اما اذا لم يكن كذلك فيحوله الى false
[pic]
بعد ذلك يذهب الى العمود B فاذا كان north فيحوله الى True اما اذا كان South فيحوله الى false
[pic]
[pic]
بعد ذلك سوف يقوم بضرب اول قيمة في الاقواس الناتجة من العمود A مع اول قيمة من الاقواس الناتجة من العمود B حيث ان True*True=True, True*False=False, False*False=False
[pic]
بعد ذلك سوف يقوم بتحويل كل True الى 1 وكل False الى صفر
[pic]
بعد ذلك سوف يقوم بكتابة الارقام الموجودة في العمود C وتضرب في الناتج من العملية السابقة حيث تضرب اول قيمة في اول قيمة وثاني قيمة في ثاني قيمة وهكذا
[pic]
[pic]
بعد ذلك يقوم بجمع الارقام لنحصل على الناتج النهائي
[pic]
Example 2: Find how many students will be accepted to complete PhD study if you now that the average in B.Sc must be greater than or equal 70 and in M.Sc greater than or equal 75 and Year of Ministration greater than or equal than 2.
[pic]
Analysis:
[pic]
[pic]
[pic]
[pic]
[pic]
[pic]
[pic]
[pic]
[pic]
[pic]
[pic]
Visual Basic
It's a computer programming system developed and owned by Microsoft. Visual Basic was originally created to make it easier to write programs for the Windows computer operating system. The basis of Visual Basic is an earlier programming language called BASIC that was invented by Dartmouth College professors John Kemeny and Thomas Kurtz. Visual Basic is often referred to using just the initials, VB. Visual Basic is easily the most widely used computer programming system in the history of software.
How can I write a complete program?
From the problem description you should solve the problem in terms of the following:
1- Analysis التحليل
2- Algorithm Construction(تشييد الخوارزمية)
3- Flow chart
4- Programming البرمجة
5- Saving الحفظ
6- Testing الاختبار
7- Execution التنفيذ
8- Documentation التوثيق
Analysis
1- Understand the nature of the problem and the required technique.
2- Understand the form and data to be used as input and what is expected for output.
3- Should you solve a general problem or particular case?
4- Research using notes, library, and consultation(التشاور) with colleagues زملاء and instructor(المدرس).
Algorithmالخورازميات
The term algorithm originally referred to any computation performed via a set of rules applied to numbers written in decimal form. The word is derived from the phonetic pronunciation of the last name of Abu Ja'far Mohammed ibn Musa al-Khowarizmi, who was an Arabic mathematician who invented a set of rules for performing the four basic arithmetic operations (addition, subtraction, multiplication and division) on decimal numbers.
An algorithm is a representation of a solution to a problem. If a problem can be defined as a difference between a desired situation and the current situation in which one is, then a problem solution is a procedure, or method, for transforming the current situation to the desired one. We solve many such trivial problems every day without even thinking about it, for example making breakfast, travelling to the workplace etc. But the solution to such problems requires little intellectual effort and is relatively unimportant. However, the solution of a more interesting problem of more importance usually involves stating the problem in an understandable form and communicating the solution to others. In the case where a computer is part of the means of solving the problem, a procedure, explicitly stating the steps leading to the solution, must be transmitted to the computer. This concept of problem solution and communication makes the study of algorithms important to computer science.
Throughout history, man has thought of ever more elegant ways of reducing the amount of labour needed to do things. A computer has immense potential for saving time/energy, as most (computational) tasks that are repetitive or can be generalised can be done by a computer. For a computer to perform a desired task, a method for carrying out some sequence of events, resulting in accomplishing the task, must somehow be described to the computer. The algorithm can be described on many levels because the algorithm is just the procedure of steps to take and get the result. The language used to describe an algorithm to other people will be quite different from that which is used by the computer, however the actual algorithm will in essence be the same.
Definition:
An algorithm is procedure consisting of a finite set of unambiguous(غير غامض) rules (instructions) which specify a finite sequence of operations that provides the solution to a problem, or to a specific class of problems for any allowable set of input quantities (if there are inputs). In other word, an algorithm is a step-by-step procedure to solve a given problem
Alternatively, we can define an algorithm as a set or list of instructions for carrying out some process step by step. A recipe in a cookbook is an excellent example of an
algorithm. The recipe includes the requirements for the cooking or ingredients and the
method of cooking them until you end up with a nice cooked dish.
In the same way, algorithms executed by a computer can combine millions of elementary steps, such as additions and subtractions, into a complicated mathematical calculation.
Also by means of algorithms, a computer can control a manufacturing process or coordinate the reservations of an airline as they are received from the ticket offices all over the country. Algorithms for such large-scale processes are, of course, very complex, but they are built up from pieces.
One of the obstacles to overcome in using a computer to solve your problems is that of translating the idea of the algorithm to computer code (program). People cannot normally understand the actual machine code that the computer needs to run a program, so programs are written in a programming language such as C or Pascal or visual basic, which is then converted into machine code for the computer to run.
وهي مجموعة من العبارات والاحداث التي يمكن ترتيبها على شكل خطوات . وذلك يتم عن طريق الفهم الدقيق لهذه المسالة سواء كانت حسابية او منطقية وبالتالي تحويلها الى مخطط انسيابي.
Algorithm properties
1- Finiteness (No. of steps)
2- Definiteness (Clear and easy to follow)
3- Input (problem information)
4- Output( Results and answer)
5- Efficiency (Yield the desired results without waste or unnecessary efforts).
Example: For each value of a,b,c,d and x determine the value of Y
[pic]
Solution1: Solution2:
Step1: Get a,b,c,d,x Step1: Get a,b,c,d,x
Step2: from y=axxx+bxx+cx+d Step2: from y=((ax+b)x+c)x+d
Step3: get Y Step3: get Y
Finiteness 3 steps 3 steps
Definiteness unambiguous ambiguous
Inputs 4 variables 4 variables
Output 1 variables 1 variables
Efficiency (3 add., 6 multip.) (3 add., 3 multip)
Step 2 more efficient than step 1
Flow chart
A device which easily displays the steps of the algorithm as well as the overall relationships of the steps.
Flow Chart Symbols
Symbol Name Function
Flow lines Direction of flow
Indicats a range of a loop
Input Input to a program
Output Output from a program
[pic] Process Indicate arithmetic calculations or moving data
Connector Allows the flowchart to be drawn without
intersecting lines or without a reverse flow
(It will contain a word or number to clarify its meaning)
Decision Used to ask a question that can be answered in a binary
format (Yes/No,True/False) Inside of this is a logical
expression or the comparison operator(:).
Iteration box
Terminal Start and End
General Rules for flowcharting
1. All boxes of the flowchart are connected with Arrows. (Not lines)
2. Flowchart symbols have an entry point on the top of the symbol with no other entry points. The exit point for all flowchart symbols is on the bottom except for the Decision symbol.
3. The Decision symbol has two exit points; these can be on the sides or the bottom and one side.
4. Generally a flowchart will flow from top to bottom. However, an upward flow can be shown as long as it does not exceed 3 symbols.
5. Connectors are used to connect breaks in the flowchart. Examples are:
• From one page to another page.
• From the bottom of the page to the top of the same page.
• An upward flow of more than 3 symbols
6. Subroutines have their own and independent flowcharts.
ملاحظات حول ال flow chart: يتم الربط عند رسم المخطط بواسطة سهم وليس خط مع العلم ان نقطة الدخول في الاعلى تكون في اي نقطة مع مراعاة عدم وضع نقطة دخول اخرى وتكون نقطة الخروج في الاسفل فقط في حالة ال decision box فيكون اما من الاسفل او من الجوانب.
يتم استعمال ال connector عند الانتقال من صفحة الى اخرى او عند الانتقال من اسفل الصفحة الى الاعلى او بالعكس ( في حالة الرجوع الى خطوة سابقة او الذهاب الى خطوة لاحقة من اجل تجنب تقاطع الخطوط).
Naming Subs and Functions
عند كتابة البرنامج يجب اختيار اسم للبرنامج يتناسب مع السؤال ويجب تجنب كتابة الرموز التالية #, $, %, &, @, ^, *, or !. حيث يجب ان يبدأ الاسم بحرف وليس رقم ويجب تجنب ترك الفراغات عند كتابة اسم البرنامج .
كل اسم يجب ان لا يتجاوز 256 رمز
There are rules to follow when you name procedures, i.e
• VBA does not differentiate between uppercase and lowercase letters.
• You can’t use any of the following characters in a name: #, $, %, &, @, ^, *, or !.
• Letters, numbers, and some punctuation characters are allowed, but the first character must be a letter.
• Each name should be less than 255 characters. (Of course, you would never make a procedure name this long.)
• No spaces or periods in the name.
Its common sense that a procedure’s name should describe the routine’s purpose. For example, GetUserName, InputTaxData, PerformSort_and etc.
Some programmers prefer using sentence like names that provide a complete description of the procedure. Some examples include WriteReportToTextFile and Get_Print_Options_and_Print_Report. The use of such lengthy names has pros and cons. On the one hand, such names are descriptive, unambiguous and not too long. You can have your own naming style, just make the names descriptive and to avoid meaningless names such as GetIt, DoThis, RunAgain, and Macro1.
Function and sub
The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. We will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.
Function
If you want Excel VBA to perform a task that returns a result, you can use a function. Place a function into a module (In the Visual Basic Editor, click on Insert and then Module). For example, the function with name Area.
Function Area( x,y)
Area = x * y
End Function
Explanation: This function has two arguments . You can use the name of the function (Area) in your code to indicate which result you want to return (here x * y).
You can now refer to this function (in other words call the function) from somewhere else in your code by simply using the name of the function and giving a value for each argument.
Sub
If you want Excel VBA to perform some actions, you can use a sub. Place a sub into a module (In the Visual Basic Editor, click on Insert and then Module). For example, the sub with name Area.
Sub Area( )
X=
Y=
range ("A").value=x*y
End Sub
Explanation: This sub has two arguments. It does not have a return type! You can refer to this sub (call the sub) from somewhere else in your code by simply using the name of the sub and giving a value for each argument.
اي انه في حالة الـ function يمكن تنفيذ البرنامج وذلك بالطريقة الاعتيادية اما في حالة الـ Sub فلايمكن ذلك وانما يمكن استدعاءه من خلال برنامج اخر .
ملاحظات مهمة حول ال Sub و ال Function:
1- تستخدم عادة البرنامج الفرعي الـ Sub في حال وجود اكثر من ناتج (اي ناتج واحد اوكثر ) او الـ Function فتستخدم في حال وجدود ناتج واحد فقط.
2- طريقة ادخال المجاهيل في الـ Sub تتم عن طريق اما الـ inputbox او excel sheet او عن طريق التصميم اما في حالة الـ Function فيتم بنفس الطرق اعلاه عدا يمكن ادخال المجاهيل عن طريق الاقواس الموجودة قرب العنوان مع مراعاة تعريف المجاهيل التي تم ادخاله في ال General. مثلا Function valuesum(x,y).
3- طريقة الاخراج في الـ Sub تتم عن طريق اختيار الخلية المراد ظهور الناتج بها مثلا: Range("A1").value=sum او Cells(1,1).value=sum , اما في حالة الـ Function فيتم بكتابة اسم البرنامج=الناتج مثلا valuesum=sum.
4- كتابة الـ Sub و الـFunction تكون عن طريق الذهاب الى صفحة الاكسل ثم الضغط على مفتاح Alt+F11 ثم insert ثم Module سوف تظهر واجهة التي سوف يتم كتابة الشفرة بها.
او في الـ window7 نذهب الى Tools ثم Macro ثم Visual basic اما في window xp نذهب الى insert ثم visual basic
5- عند استخدام inputbox في ادخال اي قيمة مجهولة مثلا Max = InputBox("please enter number") فعند تنفيذ البرنامج سوف يظهر لنا صندوق حوار نكتب فيه قيمة المتغير او الاسم اذا كان اسم شخص معين ثم نضغط على المفتاح OK.
6- عند تنفيذ برنامج ونريد ان يظهر صندوق نخبر به المستخدم انه يجب ان يدخل المعلومات الصحيحة فنكتب msgbox"please enter correct information" فعند تنفيذ البرنامج سوف يظهر هذا الصندوق
7- عند البدء بكتابة الشفرة لاي برنامج نقوم اولا بكتابة اسم البرنامج ثم نقوم بتعريف جميع الرموز المستخدة في السؤال اذا كان اسم string او عدد صحيح integer او عدد حقيقي single او عدد متكون اكثر من 6 ارقام double
Dim x,y as single
8- طريقة تنفيذ البرنامج في البرنامج الفرعي ال sub تتم عن طريق الضغط على اشارة التشغيل play
حيث سوف يظهر الناتج في الخلية التي تم اختيارها اثناء كتابة الشفرة Range("A1").value=sum
اما في حال ال function فيتم الذهاب الى صفحة الاكسل واختيار الخلية المراد ظهور الناتج فيها وكتابة اسم البرنامج ثم اقواس وفي حالة ادخال المجاهيل في الاقواس قرب اسم البرنامج يجب ادخال قيمها عند التنفيذ في الاقواس.
Decision Makers
There are two primary decision making tools in VBA: If...Then and Select Case. We can look at the loop structures as decision makers also, but they are kind of indirect decision makers. The If...Then, and its brothers If...Then...Else and If...Then...ElseIf...Else along with Select Case are very definitely there to assist you in changing the path of the program or the logic of a process; i.e., they help you make decisions about what to do next based on the result of calculations or actions at a specific point in your process.
IF...THEN
This is the most basic of the decision makers. Using it assumes there is pretty much only one test to perform and only one action to take if the result of the test is true. It can be written as a one-line statement such as:
If X = 2 Then Y = 5
Very straight forward statement: if at this point in the process X equals 2, then set Y to 5. If X does not equal 2 at this point, Y will retain whatever value it has at the moment.
IF...THEN...ELSE
Taking If...Then to the next step, this decision maker lets us exercise a couple of options based on the value of something. This next snippet of code shows us how it can be used:
If X = 2 Then
Y = 5
Z = 9
Else
Y = 1
Z = 3
End If
So here we are saying that when X = 2, we set Y, Z , but if X is some value other than 2 then we set those same variables to a different set of values.
IF...THEN...ELSEIF...ELSE
Using this combination we can test for different values of a particular item. For this example, we need to set Y, Z to specific values when X is either 2 or 3, and another set of values when it is not 2 or 3.
If X = 2 Then
Y = 5
Z = 9
ElseIf X = 3 Then
Y = 0
Z = 99
Else
Y = 1
Z = 3
End If
You may actually have many ElseIf ... Then statements before the final Else statement, making this a multi-conditional decision maker. But for the times when you have many decisions to make based on the value of one (or more) variable value(s), the Select Case statement is more efficient.
Decision Structure or Selection Structure
The decision structure or mostly commonly known as a selection structure, is case where in the algorithm, one has to make a choice of two alternatives by making decision depending on a given condition.
Selection structures are also called case selection structures when there are two or more alternatives to choose from.
This structure can be illustrated in a flowchart as follows:
[pic]
If condition is true then
do task A
else
Do Task-B
End if
In this example, the condition is evaluated, if the condition is true Task-A is evaluated and if it is false, then Task-B is executed.
A variation of the construct of the above figure is shown below
[pic]
From the above structure, we have the following
If condition is true then
Do Task-A
In this case, if condition is false, nothing happens. Otherwise Task-A is executed(نفذ).
The selection requires the following
• Choose alternative(خيار, بديل) actions as a result of testing a logical condition
• Produce code to test a sequence of logical tests.
A third structure causes the certain steps to be repeated.
[pic]
ملاحظة: عتد استخدام ال If statement وكان الشرط مكون من شقين فيكتب بهذه الصيغة:
If x>2 and x=50 then
Sum=sum+x
Sum1=sum1+1
End if
Next I
Average=sum/sum1
Range("a1").value=sum
Range("a2").value=average
End sub
Example3: write a program in VBA to find a factorial of any number
Ans:
Sub factorial( )
Dim f ,I as integer
Dim x as single
F=1
X= inputbox("please enter number")
If x ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- step by step 1 using the sumif function
- step by step 1 open a non native file directly in excel
- developing e learning content
- introduction to excel formulae and functions
- advanced excel statistical functions formulae
- microsoft office excel 2013 warren hills regional school
- sumif and countif shivalik public school
- core ct home
- microsoft excel
- count and sum your data in excel 2002
Related searches
- microsoft excel 2010 user guide
- microsoft excel coupon
- microsoft excel loan calculator template
- microsoft excel guide pdf
- microsoft excel formulas pdf
- microsoft excel pdf manual
- microsoft excel 2010 instruction manual
- microsoft excel 2010 manual pdf
- microsoft excel 2016 manual pdf
- microsoft excel manual 2010
- microsoft excel training manual pdf
- microsoft excel tutorial pdf download