Nota Excel 2003 Advance
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Nota Excel 2003 Advance as PDF for free.
More details
- Words: 5,317
- Pages: 37
Ms Excel Lanjutan 2008
Kursus Microsoft Excel (Advanced) Sinopsis Kursus Microsoft Excel Advanced dibuat untuk memberi kefahaman yang lebih mendalam kepada peserta tentang teknik-teknik penggunaan perisian hamparan (spreadsheet). Penekanan utama adalah untuk tujuan penyelesaian yang melibatkan pengiraan dan ia mempunyai susunan jalur dan barisan dan ini akan memudahkan lagi setiap operasi. Beberapa contoh latihan yang melibatkan formula dan pengiraan diberi kepada peserta untuk diselesaikan.
Kandungan Kursus Lihat pada mukasurat kandungan kursus Objektif Di akhir kursus ini para peserta akan dapat memahami: 1a) Penyediaan laporan-laporan yang melibatkan pengiraan 2b) Membuat carta serta analisis data 3c) Memudahkan kawalan untuk setiap dokumen yang telah dihasilkan 4d) Konsep Worksheet Chart dan Database dalam Excel untuk memudahkan kerja-kerja harian. 5e) Penggunaan fungsi logik
Borneosys Sdn.Bhd
1
Ms Excel Lanjutan 2008
Imbas kembali Komponen pada tetingkap MS® Excel 1. Buka perisian MS Excel 2003 (XP) dan perhatikan paparan utama yang ditunjukkan. Kenalpasti bahagian seperti yang ditunjukkan di dalam rajah di bawah:
Standard Toolbar
Formula bar
Borneosys Sdn.Bhd
2
Ms Excel Lanjutan 2008
BAB 1: ADVANCE FUNCTION (FUNGSI LANJUTAN ) 1.1 Logical Function ( fungsi logik) Penggunaan fungsi bertujuan untuk meringkaskan penulisan suatu rumus dengan maksud yang sama. Bentuk umum fungsi dalam excel adalah terdiri dari tanda = yang di diikuti oleh fungsi dan argumen -argumen fungsi itu. Misalanya:
A5 = A1+A2+A3+A4
atau jika menggunakan fungsi sama A5 = SUM(A1 : A4).
Kedua-duanya akan memberikan hasil penjumlahan yang sama tetapi SUM adalah fungsi yang di Diwujudkan untuk mempermudahkan penjumlahan dengan hanya memerlukan DUA argument Sahaja iaitu A1 dan A4.
Operasi logik selalunya menggunakan symbol : Nama operasi logik sama dengan lebih besar daripada kurang daripada lebih besar atau sama dengan kurang atau sama dengan tak sama dengan
simbol = > < > <= !=
Terdapat beberapa logical function lain dalam Ms Excel.antaranya:
Functio n
Description
AND
Returns TRUE if all of its arguments are TRUE
FALSE
Returns the logical value FALSE
IF
Specifies a logical test to perform
NOT
Reverses the logic of its argument
OR
Returns TRUE if any argument is TRUE
TRUE
Returns the logical value TRUE
Antara Logic Function yang banyak sekali digunakan ialah fungsi
IF
Fungsi Logik IF Cara formulanya: Borneosys Sdn.Bhd
3
Ms Excel Lanjutan 2008
= IF ( ekspresi logik A, perintah 1, perintah 2) jika ekspresi logika menghasilkan nilai true maka akan dilaksanakan perintah 1. sebaliknya jika ekspresi logika menghasilkan False maka yang akan di laksanakan adalah perintah 2 contoh:
1 2 3 5 6
A Price $.95 $1.37 14000 8453
Example of IF typed into column B =IF (A2>1,"Yes","No") =IF (A3>1, "Yes", "No") =IF (A5>10000, .08, .05) =IF (A6>10000, .08, .05)
B Over a dollar? No Yes 0.08 0.05
Compares is ( .95 > 1) is (1.37 > 1) is (14000 > 10000) is (8453 > 10000)
Answer No Yes .08 .05
Latihan 1: Tuliskan maklumat dibawah menggunakan MS Excel. A B C D 1 First Number Second Number Formula 2 13 20=IF(A2>B2,"yes","no") 500 3 12 2=IF(A3>B3,1,2) 4 44 325=IF(A4>B4,"yes","") 5 100 10=IF(A5>B5,"contact this salesperson","give this one a raise") 6 3 50=IF(A6>B6,"contact this salesperson",$D$2) 7 40 100=IF(A7>B7,"contact this salesperson",(B7*50%))
1.2 MATH Function ( fungsi matematik)
Borneosys Sdn.Bhd
4
Ms Excel Lanjutan 2008
Ms excel mempunyai banyak fungsi matematik di dalamnya.antara operasi paling asas ialah seperti darab,tolak,bahagi ,tambah seperti berikut:
1 2 3 Operation Multiplication Division Addition Subtraction
Symbol * / + -
A 5 7 8
B 3 4 6
Constant Data =5*6 =8/4 =4+7 =8-3
Referenced Answer Data = A1 * B3 30 = A3 / B2 2 = B2 + A2 11 = A3 - B1 5
Antara fungsi tambahan yang terdapat pada Math function ialah seperti: Function ABS
Description Returns the absolute value of a number
ACOS ACOSH ASIN ASINH ATAN ATAN2 ATANH CEILING COMBIN COS COSH DEGREES EVEN EXP FACT FACTDOUBLE FLOOR GCD INT LCM LN LOG LOG10 MDETERM MINVERSE
Returns the arccosine of a number Returns the inverse hyperbolic cosine of a number Returns the arcsine of a number Returns the inverse hyperbolic sine of a number Returns the arctangent of a number Returns the arctangent from x- and y-coordinates Returns the inverse hyperbolic tangent of a number Rounds a number to the nearest integer or to the nearest multiple of significance Returns the number of combinations for a given number of objects Returns the cosine of a number Returns the hyperbolic cosine of a number Converts radians to degrees Rounds a number up to the nearest even integer Returns e raised to the power of a given number Returns the factorial of a number Returns the double factorial of a number Rounds a number down, toward zero Returns the greatest common divisor Rounds a number down to the nearest integer Returns the least common multiple Returns the natural logarithm of a number Returns the logarithm of a number to a specified base Returns the base-10 logarithm of a number Returns the matrix determinant of an array Returns the matrix inverse of an array
MMULT
Returns the matrix product of two arrays
Borneosys Sdn.Bhd
Math/TRIG MATH TRIG TRIG TRIG TRIG TRIG TRIG TRIG MATH MATH TRIG TRIG TRIG MATH MATH MATH MATH MATH MATH MATH MATH MATH TRIG TRIG MATH MATH MATH
5
Ms Excel Lanjutan 2008
MOD MROUND MULTINOMIAL
Returns the remainder from division Returns a number rounded to the desired multiple Returns the multinomial of a set of numbers
ODD PI POWER PRODUCT QUOTIENT RADIANS RAND RANDBETWEEN
Rounds a number up to the nearest odd integer Returns the value of pi Returns the result of a number raised to a power Multiplies its arguments Returns the integer portion of a division Converts degrees to radians Returns a random number between 0 and 1 Returns a random number between the numbers you specify
ROMAN ROUND ROUNDDOWN
Converts an arabic numeral to roman, as text Rounds a number to a specified number of digits Rounds a number down, toward zero
ROUNDUP SERIESSUM SIGN SIN SINH SQRT SQRTPI SUBTOTAL
Rounds a number up, away from zero Returns the sum of a power series based on the formula Returns the sign of a number Returns the sine of the given angle Returns the hyperbolic sine of a number Returns a positive square root Returns the square root of (number * pi) Returns a subtotal in a list or database
SUM SUMIF
Adds its arguments Adds the cells specified by a given criteria
SUMPRODUCT
Returns the sum of the products of corresponding array components Returns the sum of the squares of the arguments Returns the sum of the difference of squares of corresponding values in two arrays Returns the sum of the sum of squares of corresponding values in two arrays Returns the sum of squares of differences of corresponding values in two arrays Returns the tangent of a number Returns the hyperbolic tangent of a number Truncates a number to an integer
SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 TAN TANH TRUNC
MATH MATH MATH MATH TRIG MATH MATH MATH TRIG MATH MATH MATH MATH MATH MATH MATH MATH TRIG TRIG MATH MATH MATH MATH MATH MATH MATH MATH MATH MATH TRIG TRIG MATH
* Kesemua fungsi ini memerlukan argumen untuk pengunaannya.
Beberapa fungsi math yang sering digunakan dalam membantu mengira dalam perkerjaan kita seharian misalnya ialah: a) ABS Returns the absolute value of a number. The absolute value of a number is the number without its sign.
Borneosys Sdn.Bhd
6
Ms Excel Lanjutan 2008
Cara formulanya : = ABS(number) Contoh: A 1 2
Data -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)
b) SUM Adds all the numbers in a range of cells. Cara formulanya (1) : (2) :
= SUM (nombor pertama : nombor terakhir ) = SUM (no pertama : nombor terakhir , nombor lain lagi )
Contoh :
1 2 3 4 5 Example =sum(A1:A3) =sum(A1:A3, 100) =sum(A1+A4) =sum(A1:A2, A5)
A 25 50 75 test
Cells to ADD A1, A2, A3 A1, A2, A3 and 100 A1, A4 A1, A2, A5
Answer 150 250 #VALUE! 75
C) TRUNC Truncates a number to an integer by removing the fractional part of the number. Cara formulanya : = TRUNC(number)
number = is the number you want to truncate.
Contoh :
Borneosys Sdn.Bhd
7
Ms Excel Lanjutan 2008 A
B
1
Formula
Description (Result)
2
=TRUNC(8.9 )
Integer part of 8.9 (8)
=TRUNC(8.9)
Integer part of -8.9 (-8)
=TRUNC(PI() )
Integer part of pi (3)
3 4
1.3 STATISTIC Function ( fungsi statistik) Antara fungsi lain yang hampir sama dengan fungsi matematik ialah STATISTIC function seperti: Function dengan tanda √ sering digunakan dalam statistik mudah. Function AVEDEV AVERAGE √ AVERAGEA BETADIST BETAINV BINOMDIST CHIDIST CHIINV CHITEST CONFIDENCE CORREL COUNT √ COUNTA COUNTBLANK COUNTIF COVAR CRITBINOM DEVSQ EXPONDIST FDIST FINV FISHER FISHERINV FORECAST FREQUENCY FTEST GAMMADIST GAMMAINV GAMMALN GEOMEAN GROWTH Borneosys Sdn.Bhd
Description Returns the average of the absolute deviations of data points from their mean Returns the average of its arguments Returns the average of its arguments, including numbers, text, and logical values Returns the beta cumulative distribution function Returns the inverse of the cumulative distribution function for a specified beta distribution Returns the individual term binomial distribution probability Returns the one-tailed probability of the chi-squared distribution Returns the inverse of the one-tailed probability of the chi-squared distribution Returns the test for independence Returns the confidence interval for a population mean Returns the correlation coefficient between two data sets Counts how many numbers are in the list of arguments Counts how many values are in the list of arguments Counts the number of blank cells within a range Counts the number of nonblank cells within a range that meet the given criteria Returns covariance, the average of the products of paired deviations Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value Returns the sum of squares of deviations Returns the exponential distribution Returns the F probability distribution Returns the inverse of the F probability distribution Returns the Fisher transformation Returns the inverse of the Fisher transformation Returns a value along a linear trend Returns a frequency distribution as a vertical array Returns the result of an F-test Returns the gamma distribution Returns the inverse of the gamma cumulative distribution Returns the natural logarithm of the gamma function, Γ(x) Returns the geometric mean Returns values along an exponential trend
8
Ms Excel Lanjutan 2008
HARMEAN HYPGEOMDIST
Returns the harmonic mean Returns the hypergeometric distribution
INTERCEPT KURT LARGE LINEST LOGEST LOGINV LOGNORMDIST MAX √ MAXA
MODE √ NEGBINOMDIST
Returns the intercept of the linear regression line Returns the kurtosis of a data set Returns the k-th largest value in a data set Returns the parameters of a linear trend Returns the parameters of an exponential trend Returns the inverse of the lognormal distribution Returns the cumulative lognormal distribution Returns the maximum value in a list of arguments Returns the maximum value in a list of arguments, including numbers, text, and logical values Returns the median of the given numbers Returns the minimum value in a list of arguments Returns the smallest value in a list of arguments, including numbers, text, and logical values Returns the most common value in a data set Returns the negative binomial distribution
NORMDIST NORMINV NORMSDIST NORMSINV PEARSON PERCENTILE PERCENTRANK
Returns the normal cumulative distribution Returns the inverse of the normal cumulative distribution Returns the standard normal cumulative distribution Returns the inverse of the standard normal cumulative distribution Returns the Pearson product moment correlation coefficient Returns the k-th percentile of values in a range Returns the percentage rank of a value in a data set
PERMUT POISSON PROB QUARTILE RANK RSQ SKEW SLOPE SMALL STANDARDIZE STDEV STDEVA
Returns the number of permutations for a given number of objects Returns the Poisson distribution Returns the probability that values in a range are between two limits Returns the quartile of a data set Returns the rank of a number in a list of numbers Returns the square of the Pearson product moment correlation coefficient Returns the skewness of a distribution Returns the slope of the linear regression line Returns the k-th smallest value in a data set Returns a normalized value Estimates standard deviation based on a sample Estimates standard deviation based on a sample, including numbers, text, and logical values Calculates standard deviation based on the entire population Calculates standard deviation based on the entire population, including numbers, text, and logical values Returns the standard error of the predicted y-value for each x in the regression Returns the Student's t-distribution Returns the inverse of the Student's t-distribution Returns values along a linear trend Returns the mean of the interior of a data set Returns the probability associated with a Student's t-test Estimates variance based on a sample Estimates variance based on a sample, including numbers, text, and logical values Calculates variance based on the entire population Calculates variance based on the entire population, including numbers, text, and
MEDIAN MIN MINA
√ √
STDEVP STDEVPA STEYX TDIST TINV TREND TRIMMEAN TTEST VAR VARA VARP VARPA Borneosys Sdn.Bhd
9
Ms Excel Lanjutan 2008
logical values Returns the Weibull distribution Returns the one-tailed probability-value of a z-test
WEIBULL ZTEST
i) AVERAGE Average which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5. Cara formulanya (1) : (2) :
= AVERAGE (nombor pertama : nombor terakhir ) = AVERAGE (no pertama : nombor terakhir , nombor lain lagi )
Contoh:
A 25 50 75 100
1 2 3 4 5 Example =average (A1:A4) =average (A1:A4, 300) =average (A1:A5) =average (A1:A2, A4)
Cells to average A1, A2, A3, A4 A1, A2, A3, A4 and 300 A1, A2, A3, A4, A5 A1, A2, A4
Answer 62.5 110 62.5 58.33
ii) MAX Returns the largest value in a set of values. Cara formulanya (1) : (2) :
= MAX (nombor pertama : nombor terakhir ) =MAX (no pertama : nombor terakhir , nombor lain lagi )
Contoh:
A Borneosys Sdn.Bhd
10
Ms Excel Lanjutan 2008
1 2 3 4 5 Example of Max =max (A1:A4) =max (A1:A4, 100) =max (A1, A3) =max (A1, A5)
10 20 30 test
Cells to look at A1, A2, A3, A4 A1, A2, A3, A4 and 100 A1, A3 A1, A5
Ans. Max 30 100 30 10
* Nota: Ruang kosong dan teks tidak diambil kira semasa fungsi max berjalan
iii) MIN Returns the smallest value in a set of values. Cara formulanya (1) : (2) :
= MIN (nombor pertama : nombor terakhir ) =MIN (no pertama : nombor terakhir , nombor lain lagi )
Contoh:
A
1 2 3 4 5 Example of min =min (A1:A4) =min (A2:A3, 100) =min (A1, A3) =min (A1, A5)
10 20 30 test
Cells to look at A1, A2, A3, A4 A2, A3 and 100 A1, A3 A1, A5 (displays the smallest number)
Ans. min 10 20 10 10
iii) COUNT Counts the number of cells that contain numbers and counts numbers within the list of arguments. Use COUNT to get the number of entries in a number field that is in a range or array of numbers. Cara formulanya (1) : (2) :
= COUNT (nombor pertama : nombor terakhir ) = COUNT (no pertama : nombor terakhir , nombor lain lagi )
Contoh : Borneosys Sdn.Bhd
11
Ms Excel Lanjutan 2008
A 1
Data
2
Sales
3
12/8/2008
4 5
19
6
22.24
7
TRUE
8
#DIV/0! Formula
Description (Result)
=COUNT(A2:A8)
Counts the number of cells that contain numbers in the list above (3)
=COUNT(A5:A8)
Counts the number of cells that contain numbers in the last 4 rows of the list (2)
=COUNT(A2:A8, 2)
Counts the number of cells that contain numbers in the list, and the value 2 (4)
Nota: • •
Blank entries are not counted. ( Ruang kosong tidak diambil kira dalam COUNT) Text entries are NOT counted ( Juga TEXT tidak di ambil kira dalam COUNT)
1.4 FINANCIAL Function ( fungsi kewangan) Pengenalan Microsoft excel menyediakan beberapa siri fungsi pelbagai jenis bagi operasi yang berkaitan dengan kewangan atau financial. Borneosys Sdn.Bhd
12
Ms Excel Lanjutan 2008
Fungsi ini adalah bergantung kepada faktor iaitu nilai yang hendak dikira.Kebanyakkan fungsi ini adalah berkaitan dengan pelaburan (investments) atau pinjaman kewangan (loan financing). Antara beberapa FINANCIAL function ialah:
i) The Future Value of an Investment Apakah FV As described above and in reality, the Future Value is the amount the item would be worth at the end. In most, if not all, loans, it would be 0. On the other hand, if a customer is borrowing money to buy something like a car, a boat, a piano, etc, the salesperson would ask if the customer wants to put a "down payment", which is an advance of money. Then, the salesperson or loan officer can either use that down payment as the Future Value parameter or simply subtract it from the Present Value and then apply the calculation to the difference. Therefore, you can apply some type of down payment to your functions as the Future Value. To calculate the future value of an investment, you can use the FV() function. The syntax of this function is: = FV(Rate, Periods, Payment, PresentValue, PaymentType)
Contoh : Berapakah jumlah FV dalam masa 5 tahun ?
Borneosys Sdn.Bhd
13
Ms Excel Lanjutan 2008
ii) The Number of Periods of an Investment To calculate the number of periods of an investment or a loan, you can use the NPER() function. Its syntax is: = NPER(Rate, Payment, PresentValue, FutureValue, PaymentType)
Borneosys Sdn.Bhd
14
Ms Excel Lanjutan 2008
iii) Investment or Loan Payment ( Bayaran bulanan) The PMT() function is used to calculate the regular payment of loan or an investment. Its syntax is: =PMT(Rate, NPeriods, PresentValue, FutureValue, PaymentType)
iv) The Amount Paid As Interest During a Period (tahun ke) When a customer is applying for a loan, an investment company must be very interested to know how much money it would collect as interest. This allows the company to know whether the loan is worth giving. Because the interest earned is related to the interest rate, a company can play with the rate (and also the length) of the loan to get a fair (?) amount. The IPMT() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment.
=IPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)
Borneosys Sdn.Bhd
15
Ms Excel Lanjutan 2008
v) .The Interest Rate The RATE() function is used to calculate the interest applied on a loan or an investment. Its syntax is: RATE(NPeriods, Payment, PresentValue, FutureValue, PaymentType, Guess)
Borneosys Sdn.Bhd
16
Ms Excel Lanjutan 2008
1.5 Displaying and Printing formulas Secara asalnya, formula/rumus pada microsoft Excel yang kita buat pada helaian kerja TIDAK AKAN KELIHATAN apabila kita hendak mencetak helaian kerja kita. Apa yang dilihat pada helaian kerja adalah Result atau hasil dari perkiraan formula yang digunakan. Bagaimanakah jika anda ingin mencetak formula pada helaian kerja anda dan bukannya nombor-nombor yang ada hasil dari perkiraan formula ?????? Ikuti Langkah-Langkah Berikut : 1. 2. 3. 4.
Pergi ke menu “TOOLS” Kemudian Pilih “OPTIONS” Klik pada tab “VIEW” Pada bahagian “WINDOWS OPTION” ,tandakah √ pada bahagian “FORMULAS”
Apabila butang “OK” ditekan, 2 perkara akan berlaku 1. Formula akan kelihatan pada cell 2. Column akan kelihatan membesar Nota: segalanya akan normal semula jika anda membuang tanda √ pada OPTION dalam rajah diatas.
Borneosys Sdn.Bhd
17
Ms Excel Lanjutan 2008
Sebelum formula dinampakkan:
Setelah formula dinampakkan:
Borneosys Sdn.Bhd
18
Ms Excel Lanjutan 2008
BAB 2: LOOKUPS DAN DATA TABLE 2.1 FUNGSI LOOKUP
Satu lagi fungsi yang sangat hebat yang terdapat dalam excel ialah fungsi LOOKUP. Fungsi ini membenarkan anda memasukkan nilai anda pada cell kemudian mencarinya pada jadual yang anda dicipta dan kemudian mengambil nilai-nilai pada jadual yang anda cipta itu . Misalnya anda ingin mengambil nama pelajar dalam satu jadual.anda hanya perlu menaip no.matrik pelajar itu sahaja untuk mendapatkan namanya dari jadual tersebut. 2.2 Mencipta Jadual/Table LOOKUP 1. Masukkan data seperti berikut
2. Highlightkan seluruh data dari A1 sehingga C6.
Borneosys Sdn.Bhd
19
Ms Excel Lanjutan 2008
3. Dari menu “INSERT” klik “NAME” dan pilih “DEFINE”.
4. Namakan sebagai HARGA ….kemudian tekan butang OK.
•
Maka jadual rujukan LOOKUP telah terbina yang diberinama HARGA dan telah sedia untuk dilaksanakan dalam operasi.
2.3 Menggunakan fungsi VLOOKUP untuk mengambil nilai pada jadual rujukan. 1. Hasilkan paparan seperti dibawah
Andaian bahawa apabila anda masukkan nilai pada sel F4,sel G4 akan tertera nama barang dari jadual LOOKUP di sebelah. Borneosys Sdn.Bhd
20
Ms Excel Lanjutan 2008
2. KLik pada sel G4.iaitu tempat result akan dipaparkan 2. Kemudian pada menu “INSERT” , pilih “FUNCTION”.
3. pilih kategory “Lookup & Reference “
4. Dan klik pada function VLOOKUP
Borneosys Sdn.Bhd
21
Ms Excel Lanjutan 2008
5. Paparan VLOOKUP akan kelihatan
Penerangan: Lookup_Value = di sel mana nilai diperolehi untuk dibuat perbandingan. Dalam contoh ini nilai akan diperolehi dari sel F4. Table_arry = nama jadual LOOKUP iaitu HARGA. Col_index_num = colum ke barapa pada jadual LOOKUP iaitu HARGA data nya hendak diambil. Dalam kes ini colum 2.
Borneosys Sdn.Bhd
22
Ms Excel Lanjutan 2008
6. Taipakan 200 pada sel F4 dan enter
Untuk meletakkan HARGA SEUNIT yang datanya dari Table LOOKUP,lakukan sekali lagi langkah 5 diatas tetapi kali ini col_index_num =3.
Latihan kefahaman. 1. ciptakan Table Lookup seperti rajah di bawah.
2. Lengkapkan jadual keputusan berikut :
ANDA DIMINTA MENGGUNAKAN FORMULA DAN LOOKUP TABLE
Borneosys Sdn.Bhd
23
Ms Excel Lanjutan 2008
2.4 DATA TABLE
Data table biasanya digunakan dalam menganalisa statistik.Sering digunakan dalam membuat pengiraan unjuran keuntungan,kerugian dan pelbagai lagi. Bagaimana cara membuat data Table ? Mari kita ambil pengiraan mudah iaitu “Berapakah yang harus kita bayar setiap bulan kepada Bank jika kita membuat pinjaman dengan kadar 22% setahun, 18% setahun dan 18 % setahun.??? The formula we need is = PMT( ). PMT(rate, nper, pv) Rate means the interest rate. nper, is how many months you've got to pay the loan back. pv, is how much you want to borrow. Langkah 1: 1. Tuliskan seperti berikut:
Langkah 2.
Letak interest rate pada cell B3 sebagai (rate), Letak our loan length pada cell B4 sebgai (nper), Latakan our loan amount pada cell B5 sebagai (pv). Lihat paparan di sebelah:-
Borneosys Sdn.Bhd
24
Ms Excel Lanjutan 2008
So you need to enter 24.00% in cell B3, 60 in cell B4, and £10,000 in cell B5. We'll enter our formula now. Click inside cell D2 and enter the following: =PMT(B3 / 12, B4, -B5) When you press the enter key on your keyboard, Excel should give you an answer of £287.68.
Langkah 3: So enter some new values in cells C3, C4, and C5. Enter the same ones as in the image below:
So,,,,The new monthly payments are going to go in cells D3 D4 D5.
Borneosys Sdn.Bhd
25
Ms Excel Lanjutan 2008
Langkah 4:
you have to highlight both the new values and the Function you're using. So Highlight the cells C2 to D5. Your spreadsheet should look like this:
We can now create an Excel 2007 Data Table. Ingat … disini ia akan mengirakan bayaran bulanan bagi 22%,20% dan 18%. Cipta data Table: •
From the Excel menu bar, click on Data - > TABLE
Paparan ini akan kelihatan :
Langkah 5:
So click inside the Column input cell box and enter B3:iaitu RUMUS ASAL
Borneosys Sdn.Bhd
26
Ms Excel Lanjutan 2008
Excel will work out the new monthly payments:
18 percent interest rate, our monthly payments would be £253.93. If you click inside any of the cells D3 to D5, then look at the formula bar, you will see this: {=TABLE(,B3)} That's Excel's way of telling you that a Table has been created, based on the input cell B3
Borneosys Sdn.Bhd
27
Ms Excel Lanjutan 2008
BAB 3: ADVANCE LIST MANAGEMENT 3.1 Validating Cell Entries What is Data Validation? Data validation is a tool that helps you control the kind of information that is entered in your worksheet. With data validation, you can: --provide users with a list of choices --restrict entries to a specific type or size --create custom settings
Data validation list
3.2 Provide a Drop-down List of Options
1. Create a List of Items If the list of options is more than a couple of items, it will be easier to maintain if you type the list on a worksheet. The list can be entered on the sheet that will contain the dropdown lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists. a. In single row or column, type the entries you want to see in the drop-down list. (Note: The list must be in a single block of cells -- e.g. you can use A2:A6, but not A2, A4, A6, A8.)
Borneosys Sdn.Bhd
28
Ms Excel Lanjutan 2008
2. Name the List Range If you type the items on a worksheet, and name the range, you can refer to the list from any worksheet in the same workbook. 1. 2. 3. 4.
Select the cells in the list. Click in the Name box, to the left of the formula bar Type a one-word name for the list, e.g. FruitList. Press the Enter key.
3. Apply the Data Validation a. Select the cells in which you want to apply data validation b. From the Data menu, choose Validation. c. From the Allow drop-down list, choose List
d. In the Source box, type an equal sign and the list name, for example: =FruitList e. Click OK.
Borneosys Sdn.Bhd
29
Ms Excel Lanjutan 2008
Error Alert a) Click on the Error Alert tab to activate it b) Add a check mark to the Show error alert after invalid data is entered box.
c) Type your message heading text in the Title box. This text will appear in bold print at the top of the message. d) Type your message in the Error message box. e) Click OK
Borneosys Sdn.Bhd
30
Ms Excel Lanjutan 2008
BAB 4: ADVANCE LIST MANAGEMENT PivoTables and PivotCharts
Introduction One of the most powerful features of Microsoft Excel is the Pivot Table. Pivot tables allow rapid, dynamic, flexible data analysis. Pivot charts add to the flexibility of pivot tables, allowing the same rapid analysis of displayed data, while sacrificing substantial flexibility of normal Excel charts. This article describes how to create pivot tables and pivot charts, and how to create normal charts from pivot tables. Pivot Table Database The source data range for a pivot table must be arranged in a list, which has each record (observation) in a single row, each field (variable) in a single column, a header row with names of the fields, and no blank rows or columns. The following sample data shows monthly sales figures for Reps 1 and 2 in the North region and Reps 3 and 4 in the South region. Sample Pivot Table Database
Borneosys Sdn.Bhd
Month
Region Rep
Amount
Jan
North
Rep 1
7
Jan
North
Rep 2
8
Jan
South
Rep 3
3
Jan
South
Rep 4
3
Feb
North
Rep 1
5
Feb
North
Rep 2
3
Feb
South
Rep 3
5
Feb
South
Rep 4
8
Mar
North
Rep 1
8
Mar
North
Rep 2
6
Mar
South
Rep 3
7
Mar
South
Rep 4
6
31
Ms Excel Lanjutan 2008
Making a Pivot Table To make a pivot table, select either the entire source data range, or a single cell within this range, and choose Pivot Table and Pivot Chart Report... from the Data menu. The various parts of the Pivot Table Wizard verify that the data is from an Excel database and ask whether to create a pivot table or pivot chart, then ask whether the pivot table shall be created on a new worksheet or on an existing sheet. I find it easier to create the pivot table alone, and add a pivot chart (or regular chart) later.
The most important part of a pivot table is its layout. The layout can be designed by clicking the Layout button on the last step of the Pivot Table Wizard, or directly within the worksheet. Drag the Pivot Field buttons into the appropriate area (Page, Row, Column, or Data) of the pivot table.
Pivot Table Wizard's Layout Dialog
Borneosys Sdn.Bhd
32
Ms Excel Lanjutan 2008
Pivot Table Wizard's Layout Dialog A sample pivot table is shown below: the Month and Region fields are in the Row area of the pivot table, the Rep field is in the column area, and the Amount field is in the data area. There are no page fields identified in this example. The data area of the pivot table allows several options for the fields it contains, including Sum, Average, Min, Max, and Count; this example keeps the default Sum of Amount.
Sample Pivot Table
Borneosys Sdn.Bhd
33
Ms Excel Lanjutan 2008
The default pivot table has row and column grand totals, and intermediate subtotals for the Months. The Pivot Table menu on the Pivot Table toolbar has some useful items: Wizard which reruns the Pivot Table Wizard on the active pivot table, Table Options which includes whole-table settings, such as row and column totals, and Field Settings which includes formatting and subtotal settings for the individual pivot fields. Double clicking on a pivot field button also invokes the Field Settings dialog. I like to simplify my pivot tables by removing subtotals and grand totals, as shown in the following example.
Pivot Table without Subtotals or Grand Totals The dropdown arrows on the pivot field buttons display a list of pivot items belonging to that field. You can change whether a pivot item is visible using the checkboxes in the list.
Dropdown Showing Pivot Items within a Pivot Field The layout of an existing pivot table can be changed by rerunning the Pivot Table Wizard, or by dragging the pivot field buttons within the pivot table. The following arrangement was made by dragging the Rep field button to the row area of the pivot table.
Borneosys Sdn.Bhd
34
Ms Excel Lanjutan 2008
Realigned Pivot Table The best way to become familiar and comfortable with pivot tables is to practice on a few samples, experimenting with the different options.
Borneosys Sdn.Bhd
35
Ms Excel Lanjutan 2008
Making a Pivot Chart
There are a number of ways to create a Pivot Chart from a pivot table. •
Click the Chart Wizard button on the Standard Toolbar
•
Click the Chart Wizard button on the Pivot Table Toolbar
•
Select the Chart... item on the Insert menu
•
Select the PivotChart item on the Pivot Table menu (on the Pivot Table toolbar)
A pivot chart is by default created on its own chart sheet. You can move it to a worksheet using the Location... item on the Chart menu, choosing As object in:, and selecting a sheet from the dropdown list. I generally place the pivot chart onto the same sheet as the pivot table. The first simplified pivot table is recreated below with its associated pivot chart.
Simplified Pivot Table and Associated Pivot Chart 1
Borneosys Sdn.Bhd
36
Ms Excel Lanjutan 2008
Notice that the pivot chart has the same pivot field buttons as the pivot table. These field buttons can be dragged or double clicked with the same functionality as those in the pivot table. The dropdown arrows allow selection of specific pivot items as in the pivot table. When the items in the pivot chart are changed, the associated pivot table changes the same way. Realigning the pivot fields of the pivot chart changes the pivot table and pivot chart as shown below (a few formatting changes have also been made).
Realigned Pivot Table and Associated Pivot Chart 2
TAMAT Borneosys Sdn.Bhd
37
Kursus Microsoft Excel (Advanced) Sinopsis Kursus Microsoft Excel Advanced dibuat untuk memberi kefahaman yang lebih mendalam kepada peserta tentang teknik-teknik penggunaan perisian hamparan (spreadsheet). Penekanan utama adalah untuk tujuan penyelesaian yang melibatkan pengiraan dan ia mempunyai susunan jalur dan barisan dan ini akan memudahkan lagi setiap operasi. Beberapa contoh latihan yang melibatkan formula dan pengiraan diberi kepada peserta untuk diselesaikan.
Kandungan Kursus Lihat pada mukasurat kandungan kursus Objektif Di akhir kursus ini para peserta akan dapat memahami: 1a) Penyediaan laporan-laporan yang melibatkan pengiraan 2b) Membuat carta serta analisis data 3c) Memudahkan kawalan untuk setiap dokumen yang telah dihasilkan 4d) Konsep Worksheet Chart dan Database dalam Excel untuk memudahkan kerja-kerja harian. 5e) Penggunaan fungsi logik
Borneosys Sdn.Bhd
1
Ms Excel Lanjutan 2008
Imbas kembali Komponen pada tetingkap MS® Excel 1. Buka perisian MS Excel 2003 (XP) dan perhatikan paparan utama yang ditunjukkan. Kenalpasti bahagian seperti yang ditunjukkan di dalam rajah di bawah:
Standard Toolbar
Formula bar
Borneosys Sdn.Bhd
2
Ms Excel Lanjutan 2008
BAB 1: ADVANCE FUNCTION (FUNGSI LANJUTAN ) 1.1 Logical Function ( fungsi logik) Penggunaan fungsi bertujuan untuk meringkaskan penulisan suatu rumus dengan maksud yang sama. Bentuk umum fungsi dalam excel adalah terdiri dari tanda = yang di diikuti oleh fungsi dan argumen -argumen fungsi itu. Misalanya:
A5 = A1+A2+A3+A4
atau jika menggunakan fungsi sama A5 = SUM(A1 : A4).
Kedua-duanya akan memberikan hasil penjumlahan yang sama tetapi SUM adalah fungsi yang di Diwujudkan untuk mempermudahkan penjumlahan dengan hanya memerlukan DUA argument Sahaja iaitu A1 dan A4.
Operasi logik selalunya menggunakan symbol : Nama operasi logik sama dengan lebih besar daripada kurang daripada lebih besar atau sama dengan kurang atau sama dengan tak sama dengan
simbol = > < > <= !=
Terdapat beberapa logical function lain dalam Ms Excel.antaranya:
Functio n
Description
AND
Returns TRUE if all of its arguments are TRUE
FALSE
Returns the logical value FALSE
IF
Specifies a logical test to perform
NOT
Reverses the logic of its argument
OR
Returns TRUE if any argument is TRUE
TRUE
Returns the logical value TRUE
Antara Logic Function yang banyak sekali digunakan ialah fungsi
IF
Fungsi Logik IF Cara formulanya: Borneosys Sdn.Bhd
3
Ms Excel Lanjutan 2008
= IF ( ekspresi logik A, perintah 1, perintah 2) jika ekspresi logika menghasilkan nilai true maka akan dilaksanakan perintah 1. sebaliknya jika ekspresi logika menghasilkan False maka yang akan di laksanakan adalah perintah 2 contoh:
1 2 3 5 6
A Price $.95 $1.37 14000 8453
Example of IF typed into column B =IF (A2>1,"Yes","No") =IF (A3>1, "Yes", "No") =IF (A5>10000, .08, .05) =IF (A6>10000, .08, .05)
B Over a dollar? No Yes 0.08 0.05
Compares is ( .95 > 1) is (1.37 > 1) is (14000 > 10000) is (8453 > 10000)
Answer No Yes .08 .05
Latihan 1: Tuliskan maklumat dibawah menggunakan MS Excel. A B C D 1 First Number Second Number Formula 2 13 20=IF(A2>B2,"yes","no") 500 3 12 2=IF(A3>B3,1,2) 4 44 325=IF(A4>B4,"yes","") 5 100 10=IF(A5>B5,"contact this salesperson","give this one a raise") 6 3 50=IF(A6>B6,"contact this salesperson",$D$2) 7 40 100=IF(A7>B7,"contact this salesperson",(B7*50%))
1.2 MATH Function ( fungsi matematik)
Borneosys Sdn.Bhd
4
Ms Excel Lanjutan 2008
Ms excel mempunyai banyak fungsi matematik di dalamnya.antara operasi paling asas ialah seperti darab,tolak,bahagi ,tambah seperti berikut:
1 2 3 Operation Multiplication Division Addition Subtraction
Symbol * / + -
A 5 7 8
B 3 4 6
Constant Data =5*6 =8/4 =4+7 =8-3
Referenced Answer Data = A1 * B3 30 = A3 / B2 2 = B2 + A2 11 = A3 - B1 5
Antara fungsi tambahan yang terdapat pada Math function ialah seperti: Function ABS
Description Returns the absolute value of a number
ACOS ACOSH ASIN ASINH ATAN ATAN2 ATANH CEILING COMBIN COS COSH DEGREES EVEN EXP FACT FACTDOUBLE FLOOR GCD INT LCM LN LOG LOG10 MDETERM MINVERSE
Returns the arccosine of a number Returns the inverse hyperbolic cosine of a number Returns the arcsine of a number Returns the inverse hyperbolic sine of a number Returns the arctangent of a number Returns the arctangent from x- and y-coordinates Returns the inverse hyperbolic tangent of a number Rounds a number to the nearest integer or to the nearest multiple of significance Returns the number of combinations for a given number of objects Returns the cosine of a number Returns the hyperbolic cosine of a number Converts radians to degrees Rounds a number up to the nearest even integer Returns e raised to the power of a given number Returns the factorial of a number Returns the double factorial of a number Rounds a number down, toward zero Returns the greatest common divisor Rounds a number down to the nearest integer Returns the least common multiple Returns the natural logarithm of a number Returns the logarithm of a number to a specified base Returns the base-10 logarithm of a number Returns the matrix determinant of an array Returns the matrix inverse of an array
MMULT
Returns the matrix product of two arrays
Borneosys Sdn.Bhd
Math/TRIG MATH TRIG TRIG TRIG TRIG TRIG TRIG TRIG MATH MATH TRIG TRIG TRIG MATH MATH MATH MATH MATH MATH MATH MATH MATH TRIG TRIG MATH MATH MATH
5
Ms Excel Lanjutan 2008
MOD MROUND MULTINOMIAL
Returns the remainder from division Returns a number rounded to the desired multiple Returns the multinomial of a set of numbers
ODD PI POWER PRODUCT QUOTIENT RADIANS RAND RANDBETWEEN
Rounds a number up to the nearest odd integer Returns the value of pi Returns the result of a number raised to a power Multiplies its arguments Returns the integer portion of a division Converts degrees to radians Returns a random number between 0 and 1 Returns a random number between the numbers you specify
ROMAN ROUND ROUNDDOWN
Converts an arabic numeral to roman, as text Rounds a number to a specified number of digits Rounds a number down, toward zero
ROUNDUP SERIESSUM SIGN SIN SINH SQRT SQRTPI SUBTOTAL
Rounds a number up, away from zero Returns the sum of a power series based on the formula Returns the sign of a number Returns the sine of the given angle Returns the hyperbolic sine of a number Returns a positive square root Returns the square root of (number * pi) Returns a subtotal in a list or database
SUM SUMIF
Adds its arguments Adds the cells specified by a given criteria
SUMPRODUCT
Returns the sum of the products of corresponding array components Returns the sum of the squares of the arguments Returns the sum of the difference of squares of corresponding values in two arrays Returns the sum of the sum of squares of corresponding values in two arrays Returns the sum of squares of differences of corresponding values in two arrays Returns the tangent of a number Returns the hyperbolic tangent of a number Truncates a number to an integer
SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 TAN TANH TRUNC
MATH MATH MATH MATH TRIG MATH MATH MATH TRIG MATH MATH MATH MATH MATH MATH MATH MATH TRIG TRIG MATH MATH MATH MATH MATH MATH MATH MATH MATH MATH TRIG TRIG MATH
* Kesemua fungsi ini memerlukan argumen untuk pengunaannya.
Beberapa fungsi math yang sering digunakan dalam membantu mengira dalam perkerjaan kita seharian misalnya ialah: a) ABS Returns the absolute value of a number. The absolute value of a number is the number without its sign.
Borneosys Sdn.Bhd
6
Ms Excel Lanjutan 2008
Cara formulanya : = ABS(number) Contoh: A 1 2
Data -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)
b) SUM Adds all the numbers in a range of cells. Cara formulanya (1) : (2) :
= SUM (nombor pertama : nombor terakhir ) = SUM (no pertama : nombor terakhir , nombor lain lagi )
Contoh :
1 2 3 4 5 Example =sum(A1:A3) =sum(A1:A3, 100) =sum(A1+A4) =sum(A1:A2, A5)
A 25 50 75 test
Cells to ADD A1, A2, A3 A1, A2, A3 and 100 A1, A4 A1, A2, A5
Answer 150 250 #VALUE! 75
C) TRUNC Truncates a number to an integer by removing the fractional part of the number. Cara formulanya : = TRUNC(number)
number = is the number you want to truncate.
Contoh :
Borneosys Sdn.Bhd
7
Ms Excel Lanjutan 2008 A
B
1
Formula
Description (Result)
2
=TRUNC(8.9 )
Integer part of 8.9 (8)
=TRUNC(8.9)
Integer part of -8.9 (-8)
=TRUNC(PI() )
Integer part of pi (3)
3 4
1.3 STATISTIC Function ( fungsi statistik) Antara fungsi lain yang hampir sama dengan fungsi matematik ialah STATISTIC function seperti: Function dengan tanda √ sering digunakan dalam statistik mudah. Function AVEDEV AVERAGE √ AVERAGEA BETADIST BETAINV BINOMDIST CHIDIST CHIINV CHITEST CONFIDENCE CORREL COUNT √ COUNTA COUNTBLANK COUNTIF COVAR CRITBINOM DEVSQ EXPONDIST FDIST FINV FISHER FISHERINV FORECAST FREQUENCY FTEST GAMMADIST GAMMAINV GAMMALN GEOMEAN GROWTH Borneosys Sdn.Bhd
Description Returns the average of the absolute deviations of data points from their mean Returns the average of its arguments Returns the average of its arguments, including numbers, text, and logical values Returns the beta cumulative distribution function Returns the inverse of the cumulative distribution function for a specified beta distribution Returns the individual term binomial distribution probability Returns the one-tailed probability of the chi-squared distribution Returns the inverse of the one-tailed probability of the chi-squared distribution Returns the test for independence Returns the confidence interval for a population mean Returns the correlation coefficient between two data sets Counts how many numbers are in the list of arguments Counts how many values are in the list of arguments Counts the number of blank cells within a range Counts the number of nonblank cells within a range that meet the given criteria Returns covariance, the average of the products of paired deviations Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value Returns the sum of squares of deviations Returns the exponential distribution Returns the F probability distribution Returns the inverse of the F probability distribution Returns the Fisher transformation Returns the inverse of the Fisher transformation Returns a value along a linear trend Returns a frequency distribution as a vertical array Returns the result of an F-test Returns the gamma distribution Returns the inverse of the gamma cumulative distribution Returns the natural logarithm of the gamma function, Γ(x) Returns the geometric mean Returns values along an exponential trend
8
Ms Excel Lanjutan 2008
HARMEAN HYPGEOMDIST
Returns the harmonic mean Returns the hypergeometric distribution
INTERCEPT KURT LARGE LINEST LOGEST LOGINV LOGNORMDIST MAX √ MAXA
MODE √ NEGBINOMDIST
Returns the intercept of the linear regression line Returns the kurtosis of a data set Returns the k-th largest value in a data set Returns the parameters of a linear trend Returns the parameters of an exponential trend Returns the inverse of the lognormal distribution Returns the cumulative lognormal distribution Returns the maximum value in a list of arguments Returns the maximum value in a list of arguments, including numbers, text, and logical values Returns the median of the given numbers Returns the minimum value in a list of arguments Returns the smallest value in a list of arguments, including numbers, text, and logical values Returns the most common value in a data set Returns the negative binomial distribution
NORMDIST NORMINV NORMSDIST NORMSINV PEARSON PERCENTILE PERCENTRANK
Returns the normal cumulative distribution Returns the inverse of the normal cumulative distribution Returns the standard normal cumulative distribution Returns the inverse of the standard normal cumulative distribution Returns the Pearson product moment correlation coefficient Returns the k-th percentile of values in a range Returns the percentage rank of a value in a data set
PERMUT POISSON PROB QUARTILE RANK RSQ SKEW SLOPE SMALL STANDARDIZE STDEV STDEVA
Returns the number of permutations for a given number of objects Returns the Poisson distribution Returns the probability that values in a range are between two limits Returns the quartile of a data set Returns the rank of a number in a list of numbers Returns the square of the Pearson product moment correlation coefficient Returns the skewness of a distribution Returns the slope of the linear regression line Returns the k-th smallest value in a data set Returns a normalized value Estimates standard deviation based on a sample Estimates standard deviation based on a sample, including numbers, text, and logical values Calculates standard deviation based on the entire population Calculates standard deviation based on the entire population, including numbers, text, and logical values Returns the standard error of the predicted y-value for each x in the regression Returns the Student's t-distribution Returns the inverse of the Student's t-distribution Returns values along a linear trend Returns the mean of the interior of a data set Returns the probability associated with a Student's t-test Estimates variance based on a sample Estimates variance based on a sample, including numbers, text, and logical values Calculates variance based on the entire population Calculates variance based on the entire population, including numbers, text, and
MEDIAN MIN MINA
√ √
STDEVP STDEVPA STEYX TDIST TINV TREND TRIMMEAN TTEST VAR VARA VARP VARPA Borneosys Sdn.Bhd
9
Ms Excel Lanjutan 2008
logical values Returns the Weibull distribution Returns the one-tailed probability-value of a z-test
WEIBULL ZTEST
i) AVERAGE Average which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5. Cara formulanya (1) : (2) :
= AVERAGE (nombor pertama : nombor terakhir ) = AVERAGE (no pertama : nombor terakhir , nombor lain lagi )
Contoh:
A 25 50 75 100
1 2 3 4 5 Example =average (A1:A4) =average (A1:A4, 300) =average (A1:A5) =average (A1:A2, A4)
Cells to average A1, A2, A3, A4 A1, A2, A3, A4 and 300 A1, A2, A3, A4, A5 A1, A2, A4
Answer 62.5 110 62.5 58.33
ii) MAX Returns the largest value in a set of values. Cara formulanya (1) : (2) :
= MAX (nombor pertama : nombor terakhir ) =MAX (no pertama : nombor terakhir , nombor lain lagi )
Contoh:
A Borneosys Sdn.Bhd
10
Ms Excel Lanjutan 2008
1 2 3 4 5 Example of Max =max (A1:A4) =max (A1:A4, 100) =max (A1, A3) =max (A1, A5)
10 20 30 test
Cells to look at A1, A2, A3, A4 A1, A2, A3, A4 and 100 A1, A3 A1, A5
Ans. Max 30 100 30 10
* Nota: Ruang kosong dan teks tidak diambil kira semasa fungsi max berjalan
iii) MIN Returns the smallest value in a set of values. Cara formulanya (1) : (2) :
= MIN (nombor pertama : nombor terakhir ) =MIN (no pertama : nombor terakhir , nombor lain lagi )
Contoh:
A
1 2 3 4 5 Example of min =min (A1:A4) =min (A2:A3, 100) =min (A1, A3) =min (A1, A5)
10 20 30 test
Cells to look at A1, A2, A3, A4 A2, A3 and 100 A1, A3 A1, A5 (displays the smallest number)
Ans. min 10 20 10 10
iii) COUNT Counts the number of cells that contain numbers and counts numbers within the list of arguments. Use COUNT to get the number of entries in a number field that is in a range or array of numbers. Cara formulanya (1) : (2) :
= COUNT (nombor pertama : nombor terakhir ) = COUNT (no pertama : nombor terakhir , nombor lain lagi )
Contoh : Borneosys Sdn.Bhd
11
Ms Excel Lanjutan 2008
A 1
Data
2
Sales
3
12/8/2008
4 5
19
6
22.24
7
TRUE
8
#DIV/0! Formula
Description (Result)
=COUNT(A2:A8)
Counts the number of cells that contain numbers in the list above (3)
=COUNT(A5:A8)
Counts the number of cells that contain numbers in the last 4 rows of the list (2)
=COUNT(A2:A8, 2)
Counts the number of cells that contain numbers in the list, and the value 2 (4)
Nota: • •
Blank entries are not counted. ( Ruang kosong tidak diambil kira dalam COUNT) Text entries are NOT counted ( Juga TEXT tidak di ambil kira dalam COUNT)
1.4 FINANCIAL Function ( fungsi kewangan) Pengenalan Microsoft excel menyediakan beberapa siri fungsi pelbagai jenis bagi operasi yang berkaitan dengan kewangan atau financial. Borneosys Sdn.Bhd
12
Ms Excel Lanjutan 2008
Fungsi ini adalah bergantung kepada faktor iaitu nilai yang hendak dikira.Kebanyakkan fungsi ini adalah berkaitan dengan pelaburan (investments) atau pinjaman kewangan (loan financing). Antara beberapa FINANCIAL function ialah:
i) The Future Value of an Investment Apakah FV As described above and in reality, the Future Value is the amount the item would be worth at the end. In most, if not all, loans, it would be 0. On the other hand, if a customer is borrowing money to buy something like a car, a boat, a piano, etc, the salesperson would ask if the customer wants to put a "down payment", which is an advance of money. Then, the salesperson or loan officer can either use that down payment as the Future Value parameter or simply subtract it from the Present Value and then apply the calculation to the difference. Therefore, you can apply some type of down payment to your functions as the Future Value. To calculate the future value of an investment, you can use the FV() function. The syntax of this function is: = FV(Rate, Periods, Payment, PresentValue, PaymentType)
Contoh : Berapakah jumlah FV dalam masa 5 tahun ?
Borneosys Sdn.Bhd
13
Ms Excel Lanjutan 2008
ii) The Number of Periods of an Investment To calculate the number of periods of an investment or a loan, you can use the NPER() function. Its syntax is: = NPER(Rate, Payment, PresentValue, FutureValue, PaymentType)
Borneosys Sdn.Bhd
14
Ms Excel Lanjutan 2008
iii) Investment or Loan Payment ( Bayaran bulanan) The PMT() function is used to calculate the regular payment of loan or an investment. Its syntax is: =PMT(Rate, NPeriods, PresentValue, FutureValue, PaymentType)
iv) The Amount Paid As Interest During a Period (tahun ke) When a customer is applying for a loan, an investment company must be very interested to know how much money it would collect as interest. This allows the company to know whether the loan is worth giving. Because the interest earned is related to the interest rate, a company can play with the rate (and also the length) of the loan to get a fair (?) amount. The IPMT() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment.
=IPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)
Borneosys Sdn.Bhd
15
Ms Excel Lanjutan 2008
v) .The Interest Rate The RATE() function is used to calculate the interest applied on a loan or an investment. Its syntax is: RATE(NPeriods, Payment, PresentValue, FutureValue, PaymentType, Guess)
Borneosys Sdn.Bhd
16
Ms Excel Lanjutan 2008
1.5 Displaying and Printing formulas Secara asalnya, formula/rumus pada microsoft Excel yang kita buat pada helaian kerja TIDAK AKAN KELIHATAN apabila kita hendak mencetak helaian kerja kita. Apa yang dilihat pada helaian kerja adalah Result atau hasil dari perkiraan formula yang digunakan. Bagaimanakah jika anda ingin mencetak formula pada helaian kerja anda dan bukannya nombor-nombor yang ada hasil dari perkiraan formula ?????? Ikuti Langkah-Langkah Berikut : 1. 2. 3. 4.
Pergi ke menu “TOOLS” Kemudian Pilih “OPTIONS” Klik pada tab “VIEW” Pada bahagian “WINDOWS OPTION” ,tandakah √ pada bahagian “FORMULAS”
Apabila butang “OK” ditekan, 2 perkara akan berlaku 1. Formula akan kelihatan pada cell 2. Column akan kelihatan membesar Nota: segalanya akan normal semula jika anda membuang tanda √ pada OPTION dalam rajah diatas.
Borneosys Sdn.Bhd
17
Ms Excel Lanjutan 2008
Sebelum formula dinampakkan:
Setelah formula dinampakkan:
Borneosys Sdn.Bhd
18
Ms Excel Lanjutan 2008
BAB 2: LOOKUPS DAN DATA TABLE 2.1 FUNGSI LOOKUP
Satu lagi fungsi yang sangat hebat yang terdapat dalam excel ialah fungsi LOOKUP. Fungsi ini membenarkan anda memasukkan nilai anda pada cell kemudian mencarinya pada jadual yang anda dicipta dan kemudian mengambil nilai-nilai pada jadual yang anda cipta itu . Misalnya anda ingin mengambil nama pelajar dalam satu jadual.anda hanya perlu menaip no.matrik pelajar itu sahaja untuk mendapatkan namanya dari jadual tersebut. 2.2 Mencipta Jadual/Table LOOKUP 1. Masukkan data seperti berikut
2. Highlightkan seluruh data dari A1 sehingga C6.
Borneosys Sdn.Bhd
19
Ms Excel Lanjutan 2008
3. Dari menu “INSERT” klik “NAME” dan pilih “DEFINE”.
4. Namakan sebagai HARGA ….kemudian tekan butang OK.
•
Maka jadual rujukan LOOKUP telah terbina yang diberinama HARGA dan telah sedia untuk dilaksanakan dalam operasi.
2.3 Menggunakan fungsi VLOOKUP untuk mengambil nilai pada jadual rujukan. 1. Hasilkan paparan seperti dibawah
Andaian bahawa apabila anda masukkan nilai pada sel F4,sel G4 akan tertera nama barang dari jadual LOOKUP di sebelah. Borneosys Sdn.Bhd
20
Ms Excel Lanjutan 2008
2. KLik pada sel G4.iaitu tempat result akan dipaparkan 2. Kemudian pada menu “INSERT” , pilih “FUNCTION”.
3. pilih kategory “Lookup & Reference “
4. Dan klik pada function VLOOKUP
Borneosys Sdn.Bhd
21
Ms Excel Lanjutan 2008
5. Paparan VLOOKUP akan kelihatan
Penerangan: Lookup_Value = di sel mana nilai diperolehi untuk dibuat perbandingan. Dalam contoh ini nilai akan diperolehi dari sel F4. Table_arry = nama jadual LOOKUP iaitu HARGA. Col_index_num = colum ke barapa pada jadual LOOKUP iaitu HARGA data nya hendak diambil. Dalam kes ini colum 2.
Borneosys Sdn.Bhd
22
Ms Excel Lanjutan 2008
6. Taipakan 200 pada sel F4 dan enter
Untuk meletakkan HARGA SEUNIT yang datanya dari Table LOOKUP,lakukan sekali lagi langkah 5 diatas tetapi kali ini col_index_num =3.
Latihan kefahaman. 1. ciptakan Table Lookup seperti rajah di bawah.
2. Lengkapkan jadual keputusan berikut :
ANDA DIMINTA MENGGUNAKAN FORMULA DAN LOOKUP TABLE
Borneosys Sdn.Bhd
23
Ms Excel Lanjutan 2008
2.4 DATA TABLE
Data table biasanya digunakan dalam menganalisa statistik.Sering digunakan dalam membuat pengiraan unjuran keuntungan,kerugian dan pelbagai lagi. Bagaimana cara membuat data Table ? Mari kita ambil pengiraan mudah iaitu “Berapakah yang harus kita bayar setiap bulan kepada Bank jika kita membuat pinjaman dengan kadar 22% setahun, 18% setahun dan 18 % setahun.??? The formula we need is = PMT( ). PMT(rate, nper, pv) Rate means the interest rate. nper, is how many months you've got to pay the loan back. pv, is how much you want to borrow. Langkah 1: 1. Tuliskan seperti berikut:
Langkah 2.
Letak interest rate pada cell B3 sebagai (rate), Letak our loan length pada cell B4 sebgai (nper), Latakan our loan amount pada cell B5 sebagai (pv). Lihat paparan di sebelah:-
Borneosys Sdn.Bhd
24
Ms Excel Lanjutan 2008
So you need to enter 24.00% in cell B3, 60 in cell B4, and £10,000 in cell B5. We'll enter our formula now. Click inside cell D2 and enter the following: =PMT(B3 / 12, B4, -B5) When you press the enter key on your keyboard, Excel should give you an answer of £287.68.
Langkah 3: So enter some new values in cells C3, C4, and C5. Enter the same ones as in the image below:
So,,,,The new monthly payments are going to go in cells D3 D4 D5.
Borneosys Sdn.Bhd
25
Ms Excel Lanjutan 2008
Langkah 4:
you have to highlight both the new values and the Function you're using. So Highlight the cells C2 to D5. Your spreadsheet should look like this:
We can now create an Excel 2007 Data Table. Ingat … disini ia akan mengirakan bayaran bulanan bagi 22%,20% dan 18%. Cipta data Table: •
From the Excel menu bar, click on Data - > TABLE
Paparan ini akan kelihatan :
Langkah 5:
So click inside the Column input cell box and enter B3:iaitu RUMUS ASAL
Borneosys Sdn.Bhd
26
Ms Excel Lanjutan 2008
Excel will work out the new monthly payments:
18 percent interest rate, our monthly payments would be £253.93. If you click inside any of the cells D3 to D5, then look at the formula bar, you will see this: {=TABLE(,B3)} That's Excel's way of telling you that a Table has been created, based on the input cell B3
Borneosys Sdn.Bhd
27
Ms Excel Lanjutan 2008
BAB 3: ADVANCE LIST MANAGEMENT 3.1 Validating Cell Entries What is Data Validation? Data validation is a tool that helps you control the kind of information that is entered in your worksheet. With data validation, you can: --provide users with a list of choices --restrict entries to a specific type or size --create custom settings
Data validation list
3.2 Provide a Drop-down List of Options
1. Create a List of Items If the list of options is more than a couple of items, it will be easier to maintain if you type the list on a worksheet. The list can be entered on the sheet that will contain the dropdown lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists. a. In single row or column, type the entries you want to see in the drop-down list. (Note: The list must be in a single block of cells -- e.g. you can use A2:A6, but not A2, A4, A6, A8.)
Borneosys Sdn.Bhd
28
Ms Excel Lanjutan 2008
2. Name the List Range If you type the items on a worksheet, and name the range, you can refer to the list from any worksheet in the same workbook. 1. 2. 3. 4.
Select the cells in the list. Click in the Name box, to the left of the formula bar Type a one-word name for the list, e.g. FruitList. Press the Enter key.
3. Apply the Data Validation a. Select the cells in which you want to apply data validation b. From the Data menu, choose Validation. c. From the Allow drop-down list, choose List
d. In the Source box, type an equal sign and the list name, for example: =FruitList e. Click OK.
Borneosys Sdn.Bhd
29
Ms Excel Lanjutan 2008
Error Alert a) Click on the Error Alert tab to activate it b) Add a check mark to the Show error alert after invalid data is entered box.
c) Type your message heading text in the Title box. This text will appear in bold print at the top of the message. d) Type your message in the Error message box. e) Click OK
Borneosys Sdn.Bhd
30
Ms Excel Lanjutan 2008
BAB 4: ADVANCE LIST MANAGEMENT PivoTables and PivotCharts
Introduction One of the most powerful features of Microsoft Excel is the Pivot Table. Pivot tables allow rapid, dynamic, flexible data analysis. Pivot charts add to the flexibility of pivot tables, allowing the same rapid analysis of displayed data, while sacrificing substantial flexibility of normal Excel charts. This article describes how to create pivot tables and pivot charts, and how to create normal charts from pivot tables. Pivot Table Database The source data range for a pivot table must be arranged in a list, which has each record (observation) in a single row, each field (variable) in a single column, a header row with names of the fields, and no blank rows or columns. The following sample data shows monthly sales figures for Reps 1 and 2 in the North region and Reps 3 and 4 in the South region. Sample Pivot Table Database
Borneosys Sdn.Bhd
Month
Region Rep
Amount
Jan
North
Rep 1
7
Jan
North
Rep 2
8
Jan
South
Rep 3
3
Jan
South
Rep 4
3
Feb
North
Rep 1
5
Feb
North
Rep 2
3
Feb
South
Rep 3
5
Feb
South
Rep 4
8
Mar
North
Rep 1
8
Mar
North
Rep 2
6
Mar
South
Rep 3
7
Mar
South
Rep 4
6
31
Ms Excel Lanjutan 2008
Making a Pivot Table To make a pivot table, select either the entire source data range, or a single cell within this range, and choose Pivot Table and Pivot Chart Report... from the Data menu. The various parts of the Pivot Table Wizard verify that the data is from an Excel database and ask whether to create a pivot table or pivot chart, then ask whether the pivot table shall be created on a new worksheet or on an existing sheet. I find it easier to create the pivot table alone, and add a pivot chart (or regular chart) later.
The most important part of a pivot table is its layout. The layout can be designed by clicking the Layout button on the last step of the Pivot Table Wizard, or directly within the worksheet. Drag the Pivot Field buttons into the appropriate area (Page, Row, Column, or Data) of the pivot table.
Pivot Table Wizard's Layout Dialog
Borneosys Sdn.Bhd
32
Ms Excel Lanjutan 2008
Pivot Table Wizard's Layout Dialog A sample pivot table is shown below: the Month and Region fields are in the Row area of the pivot table, the Rep field is in the column area, and the Amount field is in the data area. There are no page fields identified in this example. The data area of the pivot table allows several options for the fields it contains, including Sum, Average, Min, Max, and Count; this example keeps the default Sum of Amount.
Sample Pivot Table
Borneosys Sdn.Bhd
33
Ms Excel Lanjutan 2008
The default pivot table has row and column grand totals, and intermediate subtotals for the Months. The Pivot Table menu on the Pivot Table toolbar has some useful items: Wizard which reruns the Pivot Table Wizard on the active pivot table, Table Options which includes whole-table settings, such as row and column totals, and Field Settings which includes formatting and subtotal settings for the individual pivot fields. Double clicking on a pivot field button also invokes the Field Settings dialog. I like to simplify my pivot tables by removing subtotals and grand totals, as shown in the following example.
Pivot Table without Subtotals or Grand Totals The dropdown arrows on the pivot field buttons display a list of pivot items belonging to that field. You can change whether a pivot item is visible using the checkboxes in the list.
Dropdown Showing Pivot Items within a Pivot Field The layout of an existing pivot table can be changed by rerunning the Pivot Table Wizard, or by dragging the pivot field buttons within the pivot table. The following arrangement was made by dragging the Rep field button to the row area of the pivot table.
Borneosys Sdn.Bhd
34
Ms Excel Lanjutan 2008
Realigned Pivot Table The best way to become familiar and comfortable with pivot tables is to practice on a few samples, experimenting with the different options.
Borneosys Sdn.Bhd
35
Ms Excel Lanjutan 2008
Making a Pivot Chart
There are a number of ways to create a Pivot Chart from a pivot table. •
Click the Chart Wizard button on the Standard Toolbar
•
Click the Chart Wizard button on the Pivot Table Toolbar
•
Select the Chart... item on the Insert menu
•
Select the PivotChart item on the Pivot Table menu (on the Pivot Table toolbar)
A pivot chart is by default created on its own chart sheet. You can move it to a worksheet using the Location... item on the Chart menu, choosing As object in:, and selecting a sheet from the dropdown list. I generally place the pivot chart onto the same sheet as the pivot table. The first simplified pivot table is recreated below with its associated pivot chart.
Simplified Pivot Table and Associated Pivot Chart 1
Borneosys Sdn.Bhd
36
Ms Excel Lanjutan 2008
Notice that the pivot chart has the same pivot field buttons as the pivot table. These field buttons can be dragged or double clicked with the same functionality as those in the pivot table. The dropdown arrows allow selection of specific pivot items as in the pivot table. When the items in the pivot chart are changed, the associated pivot table changes the same way. Realigning the pivot fields of the pivot chart changes the pivot table and pivot chart as shown below (a few formatting changes have also been made).
Realigned Pivot Table and Associated Pivot Chart 2
TAMAT Borneosys Sdn.Bhd
37
Related Documents
Nota Excel 2003 Advance
November 2019 12
Nota Excel
April 2020 2
Advance Excel Assignment_vinod
November 2019 12
Advance Features Of Excel
July 2020 7
Excel 2003
November 2019 23
Excel-2003
May 2020 20More Documents from ""