Hello good morning all this time I will try to explain the meaning and functions of Microsoft Excel, Microsoft Excel is an application to automatically process data that can be either basic calculations, formulas, the use of functions, data processing and tables, graphing and management of data.
The use of the formula itself can be either addition, subtraction, multiplication, and so forth. While the use of the functions can be aimed at the use of the formula to calculate in the form of mathematical and non-mathematical formula.
Microsoft Excel can also be used to solve a variety of administrative purposes, from simple to complex. On the use of such a simple purpose suppose to make the planning needs of an enterprise, such as planning of goods, quantity and price.
Until now, Microsoft Excel has reached the 2013 version, while I myself still using version 2007. Well though different versions but the function remains the same, only distinguishable feature is definitely better than the previous version.
In Microsoft Excel workbook we work with the system, while there is a worksheet in the workbook or worksheet. On this worksheet we work with using the columns and rows that form a small boxed in the form of cells where we enter data.
Understanding and IF functions in Microsoft Excel
IF functions in Microsoft Excel can be used as a logic formula to calculate or determine the value of a statement where the IF function will give a certain value if the logic is right or wrong.IF (logical_test_value, value_if_true, value_if_false).
Logical_test required. 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 evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use the comparison calculation operator.Value_if_true Optional. Value if you want to restore logical_test argument evaluates to TRUE. For example, the value of this argument is the text string "Within budget" and logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test argument evaluates to TRUE and value_if_true omitted (ie, there is only one point after logical_test argument), then the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for value_if_true argument.Value_if_false Optional. Value if you want to restore logical_test argument evaluates to FALSE. For example, the value of this argument is the text string "Over budget" and logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test argument evaluates to FALSE and value_if_true omitted (ie, there is only one point after logical_test argument), then the IF function returns the logical value FALSE. If logical_test evaluates to FALSE and the value argument value_if_false omitted (ie, there is only one point after value_if_true argument), then the IF function returns a value of 0 (zero).
Definition and function SUM, AVERAGE, MAX, MIN, VLOOKUP and HLOOKUP
A. SUM is a function to search for the amount of data contents in a certain range= SUM (0.7 * E2; 0.3 * F2)Where the value column UTS (E3) multiplied by 0.7 and the value column UAS (F3) multiplied by 0.3 and if it is multiplied, results of E3 and F3 value added and will be printed on the G3B. MAXMAX is a function to search for the highest value of a range= MAX (E2: E6)First click E7 column, and write = MAX (first data that E2 and drag to E6) then it will print the highest value in the column E7C. MINMIN is a function to search for the lowest value of a range= MIN (E2: E6)To search for the lowest value in the table UTS value first column click E8 and click the first data that E2 and E6 and drag to the lowest value will be printed in the column E8D. AVERAGE
To find the value of the average of a range can use the AVERAGE function= AVERAGE (E2: E6)First click E9 column, and write = AVERAGE (the first data that E2 and drag to E6) then it will be printed in the column value E9 score score.
VLOOKUP and HLOOKUP
VLOOKUP: Looking for value, where the provisions table beberbentuk vertical (downward structured data)HLOOKUP: Looking for value, where the provisions shaped horizontal table (data arrayed horizontally).= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)= HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)
Description use VLOOKUP and HLOOKUPlookup_value: value or reference cell is used as a key in the search data.table_array: table or range that holds the data you want searched.col_index_num: the number of columns you want captured value for the VLOOKUP function.row_index_num: line number to be retrieved value for HLOOKUP function.range_lookup: TRUE or FALSE logic value, where you want VLOOKUP or HLOOKUP function returns a value of approx method (TRUE) or return value appropriately (FALSE).
Example Excel VLOOKUP
= VLOOKUP (1002, $ A $ 2: $ C $ 4.3, FALSE) will generate 68= VLOOKUP (1003, $ A $ 2: $ C $ 4.2, FALSE) will produce GHI= B10 * VLOOKUP (C10, $ A $ 2: $ C $ 4.3, FALSE) will produce 340= B11 * VLOOKUP (C11, $ A $ 2: $ C $ 4.3, FALSE) will produce 320= B12 * VLOOKUP (C12, $ A $ 2: $ C $ 4.3, FALSE)
Sample image:
Excel HLOOKUP Example
= HLOOKUP (B1, $ B2 $ 1: $ D2, FALSE) will generate XYZ
= HLOOKUP (B1, $ B $ 1: $ D $ 3.3, FALSE) will generate 33
Sample image:
1.COUNT
Count function to count the number of data and not the entire amount of data in the form of numbers in a range.
COUNT formula
= COUNT (Range)
Examples of the image is below:
The formula = COUNT (D5: D8)Then enter then you will get the number of the above data.
2. COUNTA
COUNTA function to determine the number of data in a range. Differences with Count is that Count could only count the number of data in the form of numbers, while COUNTA in the form of words or letters.
3. COUNTIF
Countif is a function that is used to calculate or chopping the cell or range based on certain criteria.For example a lot of data in a range of how many words or the same cell, he could be the same name, the same figure and the other as his. then we can determine how many of these cells.
Function LEFT, MID, RIGHT
Function Left, Right, and Mid is a function used to take some of the characters of the data in Microsoft Excel starting from the left, center and right.
1. Function Left
Left function is a function used to take some of the characters of the data in Microsoft Excel starting from the left.The general formula Left functions are as follows:= Left (text; num_char)The purpose of the formula above are: the text is where the data to be retrieved character, num_char is how many characters we want to take.
2. Functions Right
Right function is a function used to take some of the characters of the data in Microsoft Excel that is on the right.The general formula Right functions are as follows:= Right (text; num_char)The purpose of the formula above are: Text is the place where the data to be retrieved character, num_char is is how many characters we want to take.
3. The function Mid
Mid function is a function used to take some of the characters of the data in Microsoft Excel starting from the middle.The general formula Mid functions are as follows:= Mid (text; start_num; num_char)The purpose of the formula above are: the text is where the data to be retrieved character, character star_num is taken starting from how the character from the left, num_char is how many characters we want to take.
so that I can explain may be useful for everything :) thank you for visiting my blog :)
Tidak ada komentar:
Posting Komentar