nota excel 2003 advance

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: a) Penyediaan laporan-laporan yang melibatkan pengiraan b) Membuat carta serta analisis data c) Memudahkan kawalan untuk setiap dokumen yang telah dihasilkan d) Konsep Worksheet Chart dan Database dalam Excel untuk memudahkan

kerja-kerja harian. e) Penggunaan fungsi logik

Borneosys Sdn.Bhd 1


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


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 simbol

sama dengan =

lebih besar daripada >

kurang daripada <

lebih besar atau sama dengan >

kurang atau sama dengan <=

tak sama dengan ! =

Terdapat beberapa logical function lain dalam Ms Excel.antaranya:

Function 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 IFBorneosys Sdn.Bhd 3


Fungsi Logik IF

Cara formulanya:

= 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:

A B

1 Price Over a dollar?

2 $.95 No

3 $1.37 Yes

5 14000 0.08

6 8453 0.05

Example of IFtyped into column B

Compares Answer

=IF (A2>1,"Yes","No") is ( .95 > 1) No

=IF (A3>1, "Yes", "No") is (1.37 > 1) Yes

=IF (A5>10000, .08, .05) is (14000 > 10000) .08

=IF (A6>10000, .08, .05) is (8453 > 10000) .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%))

Borneosys Sdn.Bhd 4


1.2 MATH Function ( fungsi matematik)

Ms excel mempunyai banyak fungsi matematik di dalamnya.antara operasi paling asas

ialah seperti darab,tolak,bahagi ,tambah seperti berikut:

A B

1 5 3

2 7 4

3 8 6

Operation SymbolConstant

DataReferenced

DataAnswer

Multiplication * = 5 * 6 = A1 * B3 30

Division / = 8 / 4 = A3 / B2 2

Addition + = 4 + 7 = B2 + A2 11

Subtraction - = 8 - 3 = A3 - B1 5

Antara fungsi tambahan yang terdapat pada Math function ialah seperti:

Function Description Math/TRIGABS Returns the absolute value of a number MATHACOS Returns the arccosine of a number TRIGACOSH Returns the inverse hyperbolic cosine of a number TRIGASIN Returns the arcsine of a number TRIGASINH Returns the inverse hyperbolic sine of a number TRIGATAN Returns the arctangent of a number TRIGATAN2 Returns the arctangent from x- and y-coordinates TRIGATANH Returns the inverse hyperbolic tangent of a number TRIGCEILING Rounds a number to the nearest integer or to the nearest multiple

of significance MATHCOMBIN Returns the number of combinations for a given number of objects MATHCOS Returns the cosine of a number TRIGCOSH Returns the hyperbolic cosine of a number TRIGDEGREES Converts radians to degrees TRIGEVEN Rounds a number up to the nearest even integer MATHEXP Returns e raised to the power of a given number MATHFACT Returns the factorial of a number MATHFACTDOUBLE Returns the double factorial of a number MATHFLOOR Rounds a number down, toward zero MATHGCD Returns the greatest common divisor MATHINT Rounds a number down to the nearest integer MATHLCM Returns the least common multiple MATHLN Returns the natural logarithm of a number MATHLOG Returns the logarithm of a number to a specified base TRIGLOG10 Returns the base-10 logarithm of a number TRIG

Borneosys Sdn.Bhd 5


MDETERM Returns the matrix determinant of an array MATHMINVERSE Returns the matrix inverse of an array MATHMMULT Returns the matrix product of two arrays MATHMOD Returns the remainder from division MATHMROUND Returns a number rounded to the desired multiple MATHMULTINOMIAL Returns the multinomial of a set of numbers MATHODD Rounds a number up to the nearest odd integer MATHPI Returns the value of pi TRIGPOWER Returns the result of a number raised to a power MATHPRODUCT Multiplies its arguments MATHQUOTIENT Returns the integer portion of a division MATHRADIANS Converts degrees to radians TRIGRAND Returns a random number between 0 and 1 MATHRANDBETWEEN Returns a random number between the numbers you specify MATHROMAN Converts an arabic numeral to roman, as text MATHROUND Rounds a number to a specified number of digits MATHROUNDDOWN Rounds a number down, toward zero MATHROUNDUP Rounds a number up, away from zero MATHSERIESSUM Returns the sum of a power series based on the formula MATHSIGN Returns the sign of a number MATHSIN Returns the sine of the given angle TRIGSINH Returns the hyperbolic sine of a number TRIGSQRT Returns a positive square root MATHSQRTPI Returns the square root of (number * pi) MATHSUBTOTAL Returns a subtotal in a list or database MATHSUM Adds its arguments MATHSUMIF Adds the cells specified by a given criteria MATHSUMPRODUCT Returns the sum of the products of corresponding array

components MATHSUMSQ Returns the sum of the squares of the arguments MATHSUMX2MY2 Returns the sum of the difference of squares of corresponding

values in two arrays MATHSUMX2PY2 Returns the sum of the sum of squares of corresponding values in

two arrays MATHSUMXMY2 Returns the sum of squares of differences of corresponding values

in two arrays MATHTAN Returns the tangent of a number TRIGTANH Returns the hyperbolic tangent of a number TRIGTRUNC Truncates a number to an integer MATH

* Kesemua fungsi ini memerlukan argumen untuk pengunaannya.

Beberapa fungsi math yang sering digunakan dalam membantu mengira dalam perkerjaan kita seharian misalnya ialah:

Borneosys Sdn.Bhd 6


a) ABS Returns the absolute value of a number. The absolute value of a number is the number without its sign.

Cara formulanya : = ABS(number)

Contoh:

1

2

A

Data

-4

FormulaDescription

(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) : = SUM (nombor pertama : nombor terakhir ) (2) : = SUM (no pertama : nombor terakhir , nombor lain lagi ) Contoh :

A

1 25

2 50

3 75

4 test

5

Example Cells to ADD Answer

=sum(A1:A3) A1, A2, A3 150

=sum(A1:A3, 100) A1, A2, A3 and 100 250

=sum(A1+A4) A1, A4 #VALUE!

=sum(A1:A2, A5) A1, A2, A5 75

C) TRUNC Truncates a number to an integer by removing the fractional part of the number.

Borneosys Sdn.Bhd 7


Cara formulanya : = TRUNC(number) number = is the number you want to truncate. Contoh :

1

2

3

4

A B

FormulaDescription

(Result)

=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)

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 DescriptionAVEDEV Returns the average of the absolute deviations of data points from their meanAVERAGE √ Returns the average of its argumentsAVERAGEA Returns the average of its arguments, including numbers, text, and logical valuesBETADIST Returns the beta cumulative distribution functionBETAINV Returns the inverse of the cumulative distribution function for a specified beta

distributionBINOMDIST Returns the individual term binomial distribution probabilityCHIDIST Returns the one-tailed probability of the chi-squared distributionCHIINV Returns the inverse of the one-tailed probability of the chi-squared distributionCHITEST Returns the test for independenceCONFIDENCE Returns the confidence interval for a population meanCORREL Returns the correlation coefficient between two data setsCOUNT √ Counts how many numbers are in the list of argumentsCOUNTA Counts how many values are in the list of argumentsCOUNTBLANK Counts the number of blank cells within a rangeCOUNTIF Counts the number of nonblank cells within a range that meet the given criteriaCOVAR Returns covariance, the average of the products of paired deviationsCRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than

or equal to a criterion valueDEVSQ Returns the sum of squares of deviationsEXPONDIST Returns the exponential distributionFDIST Returns the F probability distributionFINV Returns the inverse of the F probability distributionFISHER Returns the Fisher transformationFISHERINV Returns the inverse of the Fisher transformationFORECAST Returns a value along a linear trendFREQUENCY Returns a frequency distribution as a vertical arrayFTEST Returns the result of an F-test

Borneosys Sdn.Bhd 8


GAMMADIST Returns the gamma distributionGAMMAINV Returns the inverse of the gamma cumulative distributionGAMMALN Returns the natural logarithm of the gamma function, Γ(x)GEOMEAN Returns the geometric meanGROWTH Returns values along an exponential trendHARMEAN Returns the harmonic meanHYPGEOMDIST Returns the hypergeometric distribution

INTERCEPT Returns the intercept of the linear regression lineKURT Returns the kurtosis of a data setLARGE Returns the k-th largest value in a data setLINEST Returns the parameters of a linear trendLOGEST Returns the parameters of an exponential trendLOGINV Returns the inverse of the lognormal distributionLOGNORMDIST Returns the cumulative lognormal distribution

MAX √ Returns the maximum value in a list of argumentsMAXA Returns the maximum value in a list of arguments, including numbers, text, and

logical valuesMEDIAN √ Returns the median of the given numbersMIN √ Returns the minimum value in a list of argumentsMINA Returns the smallest value in a list of arguments, including numbers, text, and

logical valuesMODE √ Returns the most common value in a data setNEGBINOMDIST Returns the negative binomial distribution

NORMDIST Returns the normal cumulative distributionNORMINV Returns the inverse of the normal cumulative distributionNORMSDIST Returns the standard normal cumulative distributionNORMSINV Returns the inverse of the standard normal cumulative distributionPEARSON Returns the Pearson product moment correlation coefficientPERCENTILE Returns the k-th percentile of values in a rangePERCENTRANK Returns the percentage rank of a value in a data set

PERMUT Returns the number of permutations for a given number of objectsPOISSON Returns the Poisson distributionPROB Returns the probability that values in a range are between two limitsQUARTILE Returns the quartile of a data setRANK Returns the rank of a number in a list of numbersRSQ Returns the square of the Pearson product moment correlation coefficientSKEW Returns the skewness of a distributionSLOPE Returns the slope of the linear regression lineSMALL Returns the k-th smallest value in a data setSTANDARDIZE Returns a normalized valueSTDEV Estimates standard deviation based on a sampleSTDEVA Estimates standard deviation based on a sample, including numbers, text, and

logical valuesSTDEVP Calculates standard deviation based on the entire populationSTDEVPA Calculates standard deviation based on the entire population, including numbers,

text, and logical valuesSTEYX Returns the standard error of the predicted y-value for each x in the regressionTDIST Returns the Student's t-distributionTINV Returns the inverse of the Student's t-distributionTREND Returns values along a linear trendTRIMMEAN Returns the mean of the interior of a data set

Borneosys Sdn.Bhd 9


TTEST Returns the probability associated with a Student's t-testVAR Estimates variance based on a sampleVARA Estimates variance based on a sample, including numbers, text, and logical valuesVARP Calculates variance based on the entire populationVARPA Calculates variance based on the entire population, including numbers, text, and

logical valuesWEIBULL Returns the Weibull distributionZTEST Returns the one-tailed probability-value of a z-test

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) : = AVERAGE (nombor pertama : nombor terakhir ) (2) : = AVERAGE (no pertama : nombor terakhir , nombor lain lagi )

Contoh:

A

1 25

2 50

3 75

4 100

5

Example Cells to average Answer

=average (A1:A4) A1, A2, A3, A4 62.5

=average (A1:A4, 300) A1, A2, A3, A4 and 300 110

=average (A1:A5) A1, A2, A3, A4, A5 62.5

=average (A1:A2, A4) A1, A2, A4 58.33

ii) MAX Returns the largest value in a set of values.

Borneosys Sdn.Bhd 10


Cara formulanya (1) : = MAX (nombor pertama : nombor terakhir ) (2) : =MAX (no pertama : nombor terakhir , nombor lain lagi )Contoh:

A

1 10

2 20

3 30

4 test

5

Example of Max Cells to look at Ans. Max

=max (A1:A4) A1, A2, A3, A4 30

=max (A1:A4, 100) A1, A2, A3, A4 and 100 100

=max (A1, A3) A1, A3 30

=max (A1, A5) A1, A5 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) : = MIN (nombor pertama : nombor terakhir ) (2) : =MIN (no pertama : nombor terakhir , nombor lain lagi )

Contoh:

Example of min Cells to look at Ans. min

=min (A1:A4) A1, A2, A3, A4 10

=min (A2:A3, 100) A2, A3 and 100 20

=min (A1, A3) A1, A3 10

=min (A1, A5) A1, A5 (displays the smallest number) 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.

Borneosys Sdn.Bhd

A

1 10

2 20

3 30

4 test

5

11


Cara formulanya (1) : = COUNT (nombor pertama : nombor terakhir ) (2) : = COUNT (no pertama : nombor terakhir , nombor lain lagi )

Contoh :

1

2

3

4

5

6

7

8

A

Data

Sales

12/8/2008

19

22.24

TRUE

#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.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 ?


http://www.functionx.com/excel/Lesson12.htm


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)



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)



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)



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. Pergi ke menu “TOOLS” 2. Kemudian Pilih “OPTIONS”3. Klik pada tab “VIEW”4. Pada bahagian “WINDOWS OPTION” ,tandakah √ pada bahagian “FORMULAS”

Apabila butang “OK” ditekan, 2 perkara akan berlaku

1. Formula akan kelihatan pada cell2. Column akan kelihatan membesar

Nota: segalanya akan normal semula jika anda membuang tanda √ pada OPTION dalam rajah diatas.



Sebelum formula dinampakkan:

Setelah formula dinampakkan:



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.2Mencipta Jadual/Table LOOKUP

1. Masukkan data seperti berikut

2. Highlightkan seluruh data dari A1 sehingga C6.

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.



2. KLik pada sel G4.iaitu tempat result akan dipaparkan2. Kemudian pada menu “INSERT” , pilih “FUNCTION”.

3. pilih kategory “Lookup & Reference “

4. Dan klik pada function VLOOKUP

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.

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



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:-



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.

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



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



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.)



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. Select the cells in the list. 2. Click in the Name box, to the left of the formula bar 3. Type a one-word name for the list, e.g. FruitList. 4. 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.



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



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

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

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



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



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.



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.



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



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


nota excel 2003 advance

Documents