Bài giảng môn Microsoft Excel 2000

Chọn Format/Rename hoặc Kớch chuột phải lờn tờn Sheets chọn Rename. Gừ tờn mới/ Enter. 54. Cấu tạo một sheet:Một sheet cú 255 cột (Column) được đỏnh số bằng cỏc kớ tự chữ cỏi: A, B,. . .AA,AB. . . IV gọi là địa chỉ cột.+ Một sheet co 65.536 hàng (Rows) được đỏnh số từ 1, 2, . . . 65536 gọi là địa chỉ hàng+ Giao của hàng và cột là ụ (cell). Một ụ được thụng qua và .Vớ dụ: A1 (cột A dũng 1)65. Đặt PasswordVào menu File/ Chọn Save hoặc Save as.../Chọn Tools/ chọn General Options.../ Đỏnh Password để mở và sửa cho WorkBook/Click Ok và nhập lại Password để xỏc nhận.Hoặc vào Menu Tools/ProtectionProtect Sheets: Đặt mật khẩu cho SheetProtect Workbook: Đặt cho tệp (hay Work)Protect and Sharing WorkBook: Đặt mật khẩu và chia sẻ WorkBook7Chương II. Nhập Liệu trong Excel 1. Cỏch nhập dữ liệu:- Kớch chuột vào ụ cần nhập- Hoặc dựng cỏc phớm di chuyển đến ụ cần nhập- Kết thỳc nhập cú thể gừ Enter để xuống ụ tiếp theo hoặc Tab để sang ụ bờn cạnh. - Muốn xuống dũng trong một ụ nhấn Alt+Enter- Nếu muốn xoỏ dữ liệu vừa nhập nhấn Esc82. Cỏc kiểu dữ liệu:Kiểu chuỗi (Label): Là một hoặc nhiều kớ tự chữ và số. Khụng dựng giỏ trị là Label để tớnh toỏn. Một Label cú thể dài 32000 kớ tự Kiểu số (Number): Số cú thể chứa cỏc kớ tự bao gồm: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9. +, -, (), /, %, E, e. Excel xem ngày và thời gian như là số do đú nếu chỳng ta nhập thời gian và ngày thỏng khụng hợp lệ nú sẽ biến thành kiểu Label. Chỳng ta cú thể thay đổi kiều ngày thỏng mặc định của mỏy tớnh theo ý mỡnh bằng cỏch:Chọn Start/Settings/Control Panel/ Chọn Regional Settings/Date và khai bỏo lại kiểu ngày thỏngCụng thức: Nếu một ụ là kết quả của một cụng thức thỡ nú cú dạng: = cụng thứcVớ dụ: =A8*B79Trong kiểu cụng thức cú thể kốm cỏc hàm tớnh toỏnCỏc toỏn tử thường dựng:Toỏn tửTờn toỏn tửVớ dụKết quả+Cộng=4+812-Trừ=12-84*Nhõn=12*896/Chia=12/43^Mũ=8^44096, >, So sỏnh=4>8False&Nối chuỗi=“Hồng”& “Nhung”HồngNhung:Tham chiếu=Sum(B5:B15),Ngăn cỏch=Sum(B5:B9,D4:D8)%Phần trăm=8*4%0.3210Cỏc bỏo lỗi thường gặp khi nhập cụng thức:#DIV/0!: Trong cụng thức cú phộp toỏn chia cho số 0.#N/A:(No Value Is Available) khụng cú giỏ trị nào ỏp dụng#NUM!: Cỏc con số trong cụng thức khụng hợp lý#REF!: Trong cụng thức cú tham chiếu đến những ụ khụng tồn tại, hoặc đó bị xoỏ#VALUE!: Trong cụng thức dựng sai kiểu dữ liệu, như cộng trừ cỏc dữ liệu dạng chuỗi làm cho kết quả trở nờn vụ nghĩa#NAME!: Sử dụng tờn ụ, vựng khụng hợp lý hoặc nhập dữ liệu dạng chuỗi khụng để trong nhỏy đụi “ ”#NULL!: bạn đó chốn hai khu vực khụng giao tiếp nhau113. Địa chỉ tương đối và địa chỉ tuyệt đốiĐịa chỉ tương đối: Khi sao chộp cụng thức cú địa chỉ tương đối thỡ địa chỉ ụ trong cụng thức sẽ được cập nhật và thay đổi thành địa chỉ tương đối khỏc Địa chỉ tương đối được viết như sau: =  =B5Địa chỉ tuyệt đối: Khi sao chộp cụng thức cú địa chỉ tuyệt đối thỡ địa chỉ ụ trong cụng thức khụng thay đổi Địa chỉ tuyệt đối được viết như sau: =$$  =$B5; =B$5; =$B$5 124. Chốn chỳ thớchChốn chỳ thớch vào ụ:Xỏc định ụ cần chốn chỳ thớchChọn Insert/Comment hoặc kớch phải chuột lờn ụ cần chỳ thớch và chọn Insert comment. Gừ chỳ thớch vào hộp .Kết thỳc bằng kớch chuột ra khỏi commentMuốn chỉnh sửa lại chỳ thớch chọn Edit CommentHiện thị tất cả cỏc chỳ thớch: View/ Chọn CommentMuốn xoỏ chỳ thớch: Chọn ụ cú chỳ thớch, Vào Edit/ Chọn Clear/ Chọn Comments135.Chốn cỏc Hyperlink(siờu liờn kết)*Tạo liờn kết đến một ụ của Sheet khỏc trong WorbookChọn ụ, hoặc một đối tượng cần tạo liờn kếtChọn Insert/ hyperlink/ Chọn Place in this DocumentType the cell reference: đỏnh địa chỉ ụ cần tham chiếuTrong khung Or select a...: chọn Sheet cần tham chiếuClick OK14*Tạo liờn kết đến đối tượng khỏc- Chọn ụ cần chốn siờu liờn kết- Chọn Insert/Hyperlink/Chọn Existing File or Web Page/ Chọn Recent Files, hoặc Browsed Pages, hoặc Inserted Links- Chọn tờn tệp/Chọn OK*Tạo liờn kết đến một tệp mới:- Chọn Insert/ hyperlink/ Chọn Create new Document- Name of new Document: Gừ tờn tệp cần tạo - OK*Tạo liờn kết gửi thư điện tử- Chọn Insert/ Hyperlink/ Chọn E- mail Address.- Gừ địa chỉ E- mail/ Chọn Ok- Khi Click vào siờu liờn kết  xuất hiện hộp thoại để soạn thảo e-mail156. Xử lớ dữ liệu:Xoỏ dữ liệu: Chọn ụ chứa dữ liệu cần xúa. Chọn Edit/Clear. Cú 4 kiểu xoỏ:All: Xoỏ toàn bộFormats: Chỉ xoỏ phần định dạng của ụContents: Chỉ xoỏ phần nội dung dữ liệu trong ụComments: Chỉ xoỏ phần chỳ thớch của ụDi chuyển hoặc sao chộp dữ liệu:Chọn ụ hoặc vựng cần di chuyển (sao chộp). + Di chuột nằm trờn khung viền ngoài của khối ụ chọn, giữ chuột và kộo đến vị trớ cần di chuyển đến (nếu sao chộp vừa di chuột vừa giữ Ctrl)+ Hoặc chọn Edit/Cut (hoặc Copy) xỏc định ụ đến chọn Edit/Paste167. Đỏnh dóy số tự động cho một cộtNếu là một nhón cú kớ tự số cuối cựng hoặc là dóy số thỡ chỳng ta cú thể đỏnh tự động tăng dần bằng cỏch như sau:Gừ vào ụ đầu tiờn giỏ trị đầu tiờn của dóy. Gừ vào ụ thứ hai giỏ trị thứ hai của dóy. Hiệu của hai ụ này là giỏ trị bước nhảy của dóy. Chọn cả hai ụ. Kớch chuột vào dấu hỡnh vuụng ở gúc ụ. Giữ và kộo chuột đến hết dóy Hoặc vào Edit/chọn Fill/chọn series 17Chương 3. Định dạng1. Cố định hàng, cột như tiờu đề: Khi dữ liệu nhiều thỡ chỳng ta nờn cố định hàng và cột để dễ nhận dữ liệu quan sỏt. Xỏc định vị trớ ụ sỏng nằm phớa dưới cỏc hàng và nằm bờn phải cỏc cột cần giữ lại như tiờu đề Chọn Window/ Freeze Panes (Muốn bỏ thỡ chọn Unfreez Panes) 182. Chốn ụ, chốn dũng, chốn cộtChốn ụ: Chọn Insert/cells/ Hộp thoại chốn ụ, hàng, cột hiện ra.Chọn Shift cells right :Chốn khối ụ và đẩy dữ liệu sang phải bắt đầu từ cột đầu tiờn khối ụ chọnShift cells down: Chốn khối ụ và đẩy dữ liệu xuống dưới bắt đầu từ hàng đầu tiờn trong khối ụ chọnChốn dũng:Chọn vị trớ hàng cần chốn. Chọn Insert/RowsHoặc kớch chuột phải lờn hàng cần chốn chọn Insert RowsChốn cột:Chọn vị trớ cột cần chốn. Chọn Insert/ColumnHoặc kớch phải chuột lờn cột cần chốn chọn Insert ColumnChốn thờm Sheet mới Vào Insert/ WorkSheet Hoặc Click chuột phải tại tờn một WorkSheet/ Chọn Insert193. Định dạng đường lưới:Đỏnh dấu vựng cần định dạng: Vào Format/cells/. Hoặc kớch chuột phải chọn Format cellsNumber: Kiểu dữ liệu Text Alignment: Kiểu hiển thị văn bản Pattern: Đặt màu nềnBoder: Định nột lướiProtection: Bảo vệTắt tất cả đường lưới: Tools/Option/ViewSau đú bỏ check tại Gridlines204. Thay đổi thuộc tớnh hàngĐỏnh dấu hàngThay đổi độ cao hàngChọn Format/Row/Heightẩn hàngChọn Format/Row/HideHiện lại hàngChọn Format/Row/Unhide215. Thay đổi thuộc tớnh cộtĐỏnh dấu cộtThay đổi độ rộng cộtChọn Format/Column/Widthẩn cộtChọn Format/ Column /HideHiện lại cộtChọn Format/ Column /Unhide226. Quy định dữ liệu hiển thị trong ụChọn cột hoặc ụ cần quy định kiểu hiển thị Chọn Format/Cells/Chọn Tab AlignmentLeft: Trỏi sangRight: Phải sangCenter: Giữa Muốn viết trờn nhiều ụChọn Wrap textMuốn thay đổi hướng chữ:Chọn cỏc hướng trong hộp thoại Orientation237. Quy định kiểu dữ liệu trong ụĐỏnh dấu cột cần quy định kiểu dữ liệuChọn Format/CellsChọn NumberNumber: sốCurrency: tiền tệDate: Ngày thỏngTime: Thời gian . . . Hay chọn Custom để tự đặt kiểu dữ liệu248.Sắp xếp DLSắp xếp dữ liệu trên một cộtSắp xếp trên nhiều cột:- Từ Menu Data, chọn Sort-> hộp thoại Sort hiện ra- Chọn trường chính để sắp xếp trong hộp danh sách thả xuống Sort by. Đồng thời chọn kiểu sắp xếp+ Ascending: tăng dần.+ Descending: giảm dần.- ở phần Then by thứ nhất, chọn cột thứ hai cho việc sắp xếp ở phần Then by thứ hai, chọn cột thứ ba cho việc sắp xếp Chọn Header row để sắp xếp cả dòng tiêu đề, No header row để bỏ dòng tiêu đề.Nhấn OK để sắp xếp259. Tớnh năng AutoFormatTự động định dạng theo một số khuụn mẫu định sẵnĐỏnh dấu vựng cần định dạngVào Format/AutoFormatChọn một trong những mẫu cú sẵn, hoặc chọn Options để đặt lại một sụ thuộc tớnh2610. Định dạng theo yờu cầuVào Format/Conditional FormatingChọn cỏc biểu thức toỏn học,đỏnh cỏc giỏ trị tương ứng, chọn Format để định dạng lại. Click Ok27Vớ dụTại cột LCB những người 130030000 cú màu đỏ28Chương 4: Hàm trong ExcelKhái niệm: Hàm là dạng công thức được viết sẵn để giúp tính toán nhanh hơn. Tất cả các hàm đều có dạng tổng quát như sau: =(danh sách đối số)Tên hàm: Là tên các hàm do Excel qui địnhDanh sách đối số có thể nhận: + Các giá trị số + Danh sách các ô + Địa chỉ ô + Tên các vùng + Các công thức + Các hàmTên hàm ko phân biệt chữ hoa, thườngĐối số đặt trong dấu ngoặc đơn, giữa các đối số cách nhau dấu(;) hoặc(,). Nếu dấu chấm (.) ngăn cách các nhóm số hàng nghìn và dấu phẩy(,) ngăn cách phần thập phân Thì trong các hàm, dấu chấm phẩy(;) ngăn cách giữa các đối số. Ngược lại thì dấu phẩy(,) ngăn cách giữa các đối số29I. Nhóm hàm số:1. Hàm ABS(Số):-> Lấy giá trị tuyệt đối của một sốVí dụ: =2+ASB(-5.2) -> Kết quả là: 7.22. Hàm INT(Số): -> Lấy phần nguyên của một số.Ví dụ: = INT(6.7) -> Kết quả: = 63. Hàm MOD(Số bị chia, số chia)-> Lấy giá trị dư của phép chiaVí dụ: = MOD(10,3) -> Kết quả: =14. Hàm ROUND(Số cần làm tròn, số lẻ):-> Làm tròn sốVí dụ: = Round(15.3524,2) -> Kết quả: = 15,35 = Round(15.813,0) -> =16 =Round(15268.534,-3) ->150005. Hàm SQRT(Số):-> Lấy căn bậc haiVí dụ: = SQRT(16) -> =4306.Hàm Cos(x), sin(x), tan(x):->Lấy cos, sin, tang của tham số xVớ dụ: Cos(75)-> kết quả:0,9217 đơn vị radian7. Hàm Power(x, y): ->Tớnh x mũ y8. Hàm Fact(n)-> Tớnh n giai thừa9. Hàm Product(x,y,)-> Tớnh tớch x*y*...(Tối đa đến 30 tham số)10. Hàm Ln(n)-> Lấy loganepe của số n11. Hàm Log(n,m)->Logarit cơ số m của n12. Hàm Lg(n): ->Logarit thập phân của n31II. Nhóm hàm thống kê:1. Hàm Average (Số thứ nhất, số thứ hai,)-> Tính bình quân của các số trong ngoặcVí dụ: = AVERAGE(4,6,8,10) -> = 72. Hàm COUNT (Giá trị 1, giá trị 2,)-> Đếm các phần tử kiểu sốVí dụ:COUNT(B1:B4) -> =3Đếm các phần tử kiểu số trong vùng B1:B43. Hàm COUNTA (Giá trị1, giá trị 2,)-> Đếm các phần tử có dữ liệuVí dụ: CountA(A1:B8) ->Kết quả: 12324. Hàm MAX – MIN (Số thứ nhất, số thứ hai,)-> Lấy số lớn nhất, nhỏ nhất trong ngoặc.Ví dụ:Max(B2:B6) -> =28 Min(B2:B6) ->= 45. Hàm SUM (Số thứ nhất, số thứ hai,)-> Tính tổng các sốVí dụ: Sum (B2:B5) -> = 286. Hàm Rank (x,ref, order) ->Hàm xếp thứ hạng. Trong đó: + x: là giá trị cần xác định thứ hạng + Ref: Vùng cần xác định thứ hạng. Vùng này luôn để trong địa chỉ tuyệt đối + Order: kiểu sắp xếp thứ hạng Nếu Order=0: xếp theo chiểu giảm dần Nếu order=1 : xếp theo chiều tăng dần33III. Nhóm hàm chuỗi:1.Hàm LEFT (Chuỗi, số ký tự muốn lấy)-> Lấy các ký tự phía bên trái của chuỗiVí dụ: = LEFT(“TpHCM”,2) ->Kết quả: Tp2. RIGHT (Chuỗi, số ký tự muốn lấy)-> Lấy các ký tự phía bên phải của chuỗiví dụ: = RIGHT(“TpHCM”,3) -> Kết quả: HCM3. MID (Chuỗi, m, n)-> Lấy từ trái qua phải n ký tự từ vị trí thứ mVí dụ: = MID(“Vu Thi Thu”,4,3) -> Kết quả: Thi4. Hàm LEN (Chuỗi)-> Trả về độ dài của chuỗiVí dụ: LEN(Tin hoc) ->Kết quả: 7345. Hàm UPPER (Chuỗi):-> Đổi chuỗi thanh chữ inVí dụ: =Upper(“Lê Văn” ->kết quả: LÊ VĂN6. Hàm LOWER(Chuỗi):-> Đổi chuỗi thành chữ thườngVí dụ: =LOWER(“Lê Văn”) -> Kết quả: lê văn7. Hàm PROPER(chuỗi)-> Đổi ký tự đầu thành chữ InVí dụ: = Proper(“vu thi thu”) -> kết quả: Vu Thi Thu8. Hàm TRIM(Chuỗi) -> cắt bỏ khoảng trắng ở đầu và cuối chuỗiVí dụ: = Trim(“ Số đỏ” ) -> Kết quả: =“Số đỏ”359. Hàm Replace ( ,m,n, ): ->Từ vị trí thứ m và lấy ra n kí tự của . Thay vào đó là . Ví dụ =replace(“lê thu na”, 4,3,”Hoa”) ->Kq: lê hoa na 10. Hàm Rept (,n):-> Cho kết quả là được lặp lại n lần. Ví dụ = rept(“Long”;3) ->Kq là LongLongLong11.Hàm Search (,): ->Cho biết vị trí xuất hiện trong .Ví dụ = Search(“Tuấn”,”nguyễn Tuấn anh”) -> Kq là 83612.Hàm Concatenate(;;...)->Dùng để nối các chuỗi là ... lại với nhau . Ví dụ = concatenate(“Loan”,” Son”) ->Kq là: Loan Son * Hoặc dùng toán tử & để ghép các chuỗi Ví dụ: “Mai “&”Ha” -> kq là Mai Ha13. Hàm Value() -> Hàm đổi chuỗi dạng số thành số37IV. Nhóm hàm điều kiện1. Hàm IF(Điều kiện, giá trị đúng, giá trị sai)-> Trả về giá trị đúng nếu điều kiện đúng, ngược lại trả về giá trị saiVí dụ: IF(C4>6,”Nhanh”,“Chậm”)2. SUMIF(Vùng1,“điều kiện”, vùng2)-> Tính tổng các ô trong vùng 2 tương ứng với các ô vùng 1 thoả mãn điều kiệnVí dụ: = SumIf(B4:B7,”>5”,C4:C7) -> Kết quả: =22383. COUNTIF(Vùng,”điều kiện”)-> Đếm các ô thoả mãn điều kiện trong vùngVí dụ COUNTIF(B4:B7,”Kết quả: =139V. Nhóm hàm Date/Time1.Hàm Today (Tháng, ngày,năm) -> Cho ngày tháng hiện tại của máyVí dụ: Để tính tuổi =int((today()-ns)/365)2.Hàm now(): ->Cho ra ngày và giờ hiện tại của máy3.Hàm Month (Tháng, ngày,năm)->Cho ra tháng của .Ví dụ: =month(“27/10/2005”): là 104. WEEKDAY (Tháng, ngày,năm)-> trả về số thứ trong tuầnví dụ Date(15/10/2005) ->Kết quả: = 3405.Hàm Day (Tháng, ngày,năm) -> Cho ra ngày .Ví dụ = day(“27/10/2005”): là 276.Hàm Year (Tháng, ngày,năm) -> Cho ra nămVí dụ =year(“27/10/2005”): là 2005Tuoi= year(today()-)7.Hàm Hour()->Cho ra giờ. VD=HOUR("12:48:56") ->Kq là 128.Hàm Minute()-> cho ra số phút9.Hàm Second( ) ->Cho ra số giây41VI. Nhóm hàm LOGIC1. Hàm AND(Các biểu thức điều kiện)-> Trả về giá trị “Và” Logic( True hay False) của các biểu thức điều kiện.Ví dụ: = IF(AND(B4>5,E4=“A”),”Đ”,”H”)-> nếu B4 lớn hơn 5 và(AND) E4 là A thì cho kết quả là”Đ”, ngược lại cho kết quả là”H”422. Hàm OR(Các biểu thức điều kiện):-> Trả về giá trị “Hoặc”Logic(True hay False) của biểu thức điều kiện.Ví dụ: =IF(OR(B4nếu B4 nhỏ hơn 5 hoặc(OR)C4 nhỏ hơn 1 thì cho kết quả là “Không đạt”, ngược lại sẽ cho kết quả là “Đạt”3. Hàm NOT(Các biểu thức điều kiện):-> Trả về giá trị “Không” Logic(True Hay False) của các biểu thức điều kiện.Ví dụ: =IF(AND(B4)>5, NOT(C4=0)),”Đ”,”H”)-> nếu B4 lớn hơn 5 và C4 bằng 0 thì kết quả là”Đ”, ngược lại sẽ xho kết quả là”H”43ANot ATFFTABA Or BTTTTFTFTTFFFABA And BTTTTFFFTFFFF44VII. Nhóm hàm tìm kiếm1. VLOOKUP(x, vùng tham chiếu,n,0):-> Tìm giá trị x ở cột thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở cột thứ n.Ví dụ: Lập công thức cho ô C13 như sau:=VLOOKUP(B13,$A$2:$B$5,2,0) -> Kết quả: SắtTìm một giá trị bằng giá trị của ô13 trong cột thứ nhất của vùng A2:B5 và lấy giá trị tương ứng ở cột thứ 2452. Hàm HLOOKUP(x, vùng tham, n, 0):->Tìm giá trị x ở dòng thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở dòng thứ nVí dụ: Lập công thức cho ô D13:= HLOOKUP(B13,$A$7:$D$8,2,0)-> kết quả: D13= 100CY: Trong công thức trên nên đặt giá trị tuyệt đối cho địa chỉ vùng để không bị thay đổi khi sao chép.463.Hàm Choose(n,)->Lấy ra giá trị thứ n trong từ bên tráiVí dụ = choose(3, 200,300,400,500,700): kq là 4004. Hàm Index(): có hai dạng* Dạng 1: = Index(,,): ->kq là giá trị của ô ứng với dòng và cột của vùng cần tìm, trong đó và cột : là dòng , cột trong vùng cần tìm* Dạng 2: = index((),,,)47VIII. Cỏc hàm tài chớnh1. Hàm PV(Rate, Nper, Pmt, Fv, Type ):Tớnh số tiền phải bỏ ra để đầu tư vào một việc nào đú(trả về trị giỏ đầu tư lỳc đầu kỳ)Rate : Lói suất trong một khoảng thời gianNper : Tổng số cỏc khoảng thời gian đầu tưPmt : Số tiền thu được trong một khoảng thời gian thường kỳFv : Số tiền cú được trong tương lai sau tổng số khoảng thời gian NperType : Giỏ trị logic, xỏc định khoản thanh toỏn xuất hiện đầu kỳ (=1) hay cuối kỳ (= 0 hoặc bỏ trống)48Ví dụ: Bài toán như sau: Tính tổng số tiền tương đương bạn phải trả lúc đầu kỳ(thay vì trả góp), để mua trả góp chiếc xe với khoản chi thường kỳ là $220 mỗi tháng và trong 4 năm(48 tháng) với lãi suất 9%?= PV(0.09/12,48,220)Kết quả: $8,840.650 Có nghĩa là nếu bạn có tiền mặt và trả Tiền một lúc là $8,840.65, còn nếu bạn trả góp, thì phải trả $220*48 tháng=$10,560492. Hàm FV(Rate, Nper, Pmt, PV, Type):Tớnh số tiền cú được sau thời gian Nper, với lói suất Rate (tớnh theo năm), khi đầu tư một số tiền PV (Trả về gớ trị được hưởng ở cuối kỳ đầu tư)Rate : Lói suất trong một khoảng thời gianNper : Tổng số cỏc khoảng thời gian đầu tưPmt : Số tiền lói thu được trong một khoảng thời gianPv : Số tiền phải bỏ ra để đầu tư vào việc nào đú.Type : Giỏ trị logic, xỏc định khoản thanh toỏn xuất hiện đầu kỳ (=1) hay cuối kỳ (= 0 hoặc bỏ trống)50Ví dụ: Bài toán như sau: Bạn đầu tư lúc đầu là: $2000(Pv).Và chi thêm mỗi tháng là: $100 trong 5 năm(60 tháng).Với lãi suất 8% một nămTính giá trị được hưởng ở cuối kỳ?=FV(0.08/12,60,100,2000,1)  Kết quả: $10,376.36Nghĩa là bạn sẽ được hưởng: $10,376.36. Thay vì, nếu không tính lãi là: $2,000+$100*60 tháng=$8,000($ 21,094.38)513.Hàm PMT() : ->Tìm số tiền phải trả trong mỗi tháng khi vay ngân hàng(Trả về khoản chi trả thường kỳ cho một niên khoản)Ví dụ: Bài toán như sau: + Đang vay ngân hàng một khoản là: 50.000.000 đồng + Lãi suất hàng năm là 7.5% + Để trả dần hàng tháng trong vòng 10 năm- Vậy phải trả mỗi tháng cho ngân hàng với số tiền là bao nhiêu?Ta sử dụng hàm PMT như sau: =PMT((7.5%/12),120,50000000) ->593,508.85=$600.000524.Hàm NPER() : -> Tìm số tháng phải trả một khoản tiền cố định khi vay ngân hàng (trả về số kỳ khoản của một đầu tư) Ví dụ: Bài toán như sau: + Đang vay ngân hàng một khoản là: 50,000,000 đồng + Lãi suất hàng năm là 7.5% + Phải trả dần hàng tháng mỗi tháng là 2,000,000- Vậy phải mất bao nhiêu thời gian để trả hết cho ngân hàng số tiền trên?Ta sử dụng hàm NPER như sau: = NPER((7.5%/12),-2000000,50000000) --> Gần 28 tháng -> 2 năm 4 thángChú ý: Các giá trị bạn chi là âm(-); Bạn nhận được là dương(+)53Bảng trợ giúp sử dụng hàm.Click biểu tượng Paste Function fx trên thanh công cụ để lựa chọn hàm muốn sử dụng.Trong hộp thoại Paste Function, Click chọn tên nhóm hàm trong khung Function Category như : + All: Tất cả các hàm+ Financial: Nhóm hàm tài chính+ Date& Time: Ngày và giờ+ Lookup.: Nhóm hàm tìm kiếm+. Tên các hàm trong nhóm sẽ hiện thị khung Function name, cú pháp của hàm và hướng dẫn sử dụng sẽ hiện thị bên dưới. Sau khi chọn tên hàm cần dùng, Click nút OK và nhập các thông số tham biến cần thiết.54Chương 5: Biểu ĐồGiới thiệu về biểu đồ1. Biểu đồ(Chart):Là biểu diễn cỏc con số, dữ liệu bằng hỡnh ảnh, để người đọc nắm bắt được cỏc thụng tin một cỏch trực quan2. Khối dữ liệu:Để tạo được biểu đồ cần cú ớt nhất 2 ụ dữ liệu số khỏc nhau3. Cỏc thành phần của biểu đồ:- Chart Title: Tiờu đề của biểu đồ- X Title: Tiờu đề trục X; Y Title: Tiờu đề trục Y- Category label: Tiờu đề dữ liệu sốData Series: Đường biểu diễn của biểu đồ- Legend: Dóy dữ liệu so sỏnh55Thụng thường để tạo một biểu đồ cần thực hiện cỏc bước sau:B1: Đỏnh dấu vựng dữ liệu cần so sỏnh bằng biểu đồB2: Chọn Insert/Chart hoặc Click biểu tượng trờn thanh cụng cụB3: Thực hiện 4 bước sau trong cưa sổ Chart Winzard như sau:Cú cỏc loại biểu đồ:- Column: Cột- Bar: thanh ngang- Line: đường kẻ- Pie: rẻ quạt-.56Bước 1: Chọn kiểu cho biểu đồ- Chọn loại biểu đồ trong khung Chart type- Chọn dạng biểu đồ trong khung Chart sub-type- Sau khi chọn xong click Next57Bước 2: Chọn vựng dữ liệu nguồn- Định lại vựng dữ liệu trong khung Data range- Chọn dạng biểu đồ lấy dữ liệu theo dũng(Row) hoặc theo cột(Column)58Bước 3: Nhập tiờu đề cho biểu đồ trong khung Chart title, và cỏc thuộc tớnh khỏcSau đú chọn Next59Bước 4: Chọn Sheet để đặt biểu đồ- As new sheet: ở bảng tớnh mới- As object in: ở bảng tớnh hiện hành- Chọn Finish để kết thỳc60Vớ dụ:61II. Hiệu chỉnh biểu đồDi chuyển: click & drag đối tượngThay đổi kớch cỡ: đưa chuột đến một trong cỏc gúc của đối tượng, click và kộo để thay đổiĐịnh dạng kiểu chữ và màu nền:Chọn Format/ Selected hoặc double click trờn đối tượng đú.Chọn Font để định dạng kiểu chữChọn Patterns để định dạng lại màu nền hoặc chọn bảng màu trờn thanh DrawingXoỏ: Right click trờn đối tượng và chọn Clear hoặc chọn đối tượng và gừ phớm Delete62Chương 6Cơ sở dữ liệu trong EXcel Khỏi niệm cơ sở dữ liệu trong Excel- Mỗi Sheet hoặc WorkBook được coi là một cơ sở dữ liệu- Một loạt cỏc dũng trờn Sheet, mỗi dũng chứa cỏc dữ liệu liờn quan đến nhau để mụ tả một đối tượng nào đú. Cỏc dũng đú gọi là danh sỏch- Dũng đầu tiờn phải là tiờu đề cột- Cột trong danh sỏch gọi là trường- Dũng trong danh sỏch gọi là bản ghi63Khái niệm các vùng dữ liệu:Vùng dữ liệu(Database):Là vùng cơ sỏ dữ liệu gồm ít nhất 2 dòng(Row). Dòng đầu tiên chứa các tiêu đề cột(Field Name), các dòng còn lại chứa dữ liệu gọi là mẩu tin(Record).2. Vùng tiêu chuẩn(Criteria):Là vùng tiêu chuẩn chứa điều kiện để tìm kiếm, xoá, rút trích hay trích lọc. Vùng này gồm ít nhất 2 dòng. Dòng đầu chứa tiêu đề, các dòng còn lại chứa điều kiện.3. Vùng rút trích(Extract):Là vùng dữ liệu chứa các mẩu tin của dữ liệu (Database) thoả mãn điều kiện vùng tiêu chuẩn64I. Sử dụng Form để nhập liệuB1: Tạo cấu trúc của bảngB2: Vào menu Data/FormNew: Tạo bản ghi mớiDelete: Xóa bản ghi hiện thờiRestore: Phục hồi bản ghi vừa xóa:Find Prev : Hiển thị bản ghi trước bản ghi hiện thờiFind Next: Hiển thị bản ghi sau bản ghi hiện thờiCriteria: Điều kiện Close: Đóng Form lại65II.Quy định việc nhập dữ liệu cho cỏc ụĐể hạn chế việc nhập sai dữ liệu, ta đặt lại giới hạn của dữ liệu và đưa ra thụng bỏo. Cỏch làm như sau:Vào menu Data/ chọn ValidationLựa chọn thuộc tớnh và đặt lại cỏc hạn chế của dữ liệu trong Tab SettingsĐặt thuộc tớnh hiện thị thụng bỏo trong quỏ trỡnh nhập liệu trong Tab Input Mesage Đưa ra cỏc thụng bỏo nhập sai dữ liệu trong Tab Error Alert66Vớ dụ:1. Hiện thụng bỏo ngăn cản việc nhập dữ liệu, nếu nhập sai.Chọn menu Data/Validation/Setting, trong Allow chọn List,chọn Source chọn một vựng dữ liệu.Trong Tab Error Alert, chọnStop trong Style và nhập thụng bỏo như sau:Title: “Thụng bỏo!”Error Message: “Khụng cú hàng này trong danh mục.”2. Quy định cột Don gia là sốnguyờn, cú giỏ trị>500 và=7 Or DLi>=8 Or DHoa>=871IV. Phương pháp trích rútB1: Xác định vùng dữ liệu(Database) và tạo trước vùng tiêu chuẩn(Criteria) và vùng rút trích(Extract).B2:Đưa trỏ chuột vào ô bất kỳ trong vùng dữ liệu. Chon Data\Filter\Advanced filter. Kiểm chọn vào Copy to another locationB3:Nhập địa chỉ vùng dữ liệu cần trích vào khung List range. Nhập vùng điều kiện trong khung Criteria range. Nhập địa chỉ ô đầu của vung rút trích trong khung Copy toB4: Click OK72Ví dụ:Tạo danh sách các sinh viên có vùng ưu tiên là “B”73V.Sử dụng SubTotals để tổ chức CSDLKhi muốn thống kê theo nhóm chúng ta dùng SubTotalsVí dụ: Thống kê theo khu vực, theo loại điểm, theo ngày tháng, theo mặt hàng, Cách làm như sau:- Đánh dấu vùng CSDL cần thống kê theo tổng - Sắp xếp theo trường cần tạo nhóm- Vào menu Data/ SubTotals* Đặt các lựa chọn:- At each change in: Trường cần thống kê- Use Function: Danh sách các hàm có thể dùng để thống kê- Add SubTotal to: Vị trí ghi dữ liệu sau khi đã thống kê74Vớ dụ:75VI. Tạo các bảng Pivot- Bảng Pivot là một khả năng quản lý dữ liệu của Excel dùng để tổ chức, phân tích, theo yêu cầu đặt ra.Các bước để tạo như sau:Bước 1: Chọn vùng dữ liệu để tạo PivotVào Data/ Pivot Table and Pivot Chart Report Sau đó chọn NextXuất hiện hộp thoại sau:76Bước 2: Chọn vùng dữ liệuDùng chuột đánh dấu vùng dữ liệu cho chính xác77Bước 3: Chọn vị trí để lưu bảng PivotNew worksheet: lưu ra một worksheet mớiExisting worksheet: lưu ngay tại worksheet đang làm việcChọn Finish78Bước 4: Thao tác trên cửa sổ Pivot- Chọn tên trường làm chức năng cột, bằng cách kéo tên trường vào vùng Drop Column Fields Here- Chọn tên trường làm chức năng dòng, bằng cách kéo tên trường vào vùng Drop Row Fields Here- chọn trường làm chức năng kết quả, bằng cách kéo tên trường vào vùng Drop Data Items Here79Sắp xếp lại các trường trong bảng Pivot:- Click vào tên trường trong bảng và di chuyển nó đến các vị trí mới. Có thể xoá đi các trường không cần thiết bằng cách rê chúng tới thanh công cụ Pivot Table Nhập các trường mới bàng cách rê tên trường từ thanh công cụ vào bảng PivotThay đổi hàm trong bảng Pivot:Theo mặc định hàm Sum được dùng để cộng các giá trị trong vùng Data của bảng Pivit, nhưng bạn có thể thay đổi hàm này bằng các bước sau:+ Double Click vào ô góc bên trái trên của bảng Pivot, xuất hiện hộp thoại sau:80+ Trong danh sách Summarize by, chọn hàm mới cần tính Định dạng dữ liệu trong Pivot: Chọn ô dữ liệu trong bảng Pivot Nhấn vào nút Fields Setting(số 1) trên thanh công cụ Pivot tableNhấn vào nút Number trong hộp thoại Pivot Table Filed để mở hộp thoại Format Cells -> Định lại dạng dữ liệu cho cột Nhấn OK để xác nhận và đóng hộp thoại81VII. Hiện thị biểu đồ PivotGiống như các bảng Pivot, các biểu đồ Pivot có các trường động được tuỳ biến mà bạn có thể rê qua lại tới thanh công cụ Pivot Table và di chuyển khắp vùng biểu đồ.Để tạo biểu đồ Pivot, thực hiện các bước sau:Nhấn vào một ô trong bảng mà bạn muốn chuyển thành một biểu đồ PivotTrưng diện thanh công cụ Pivot Table Click vào nút Chart Wizard(Số 2)Bạn có thể tuỳ biến và định dạng biểu đồ, như phần biểu đồ của Excel.82 Chương 7 In ấn trong excel 1. Xem trước khi in- Click vào biểu tượng Print Review trên thanh công cụ. Hoặc chọn trình đơn File\Print Review- Chọn vào Margins để đặt lại khoảng cách lề trên, dưới, phải, trái bằng cách kéo, hoặc vào File/chọn Page Setup/ chọn Tab Margins - Muốn phóng lớn vị trí nào click tại vị trí đó- Click nút Close để thoát.832. Định dạng trang in:- Khi muốn thay đổi lại khổ giấy trang in:Chọn File\Page setup\Page- Đặ