›
›

Sử dụng Excel trợ giúp việc xử lý thông tin kinh tế

ppt 70 trang vanle 2860
Download
Bạn đang xem 20 trang mẫu của tài liệu "Sử dụng Excel trợ giúp việc xử lý thông tin kinh tế", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pptsu_dung_excel_tro_giup_viec_xu_ly_thong_tin_kinh_te.ppt

Nội dung text: Sử dụng Excel trợ giúp việc xử lý thông tin kinh tế

  1. Sử dụng Excel trợ giúp việc xử lý thông tin kinh tế
  2. Nội dung chính ◼ Ôn tập Excel cơ bản ◼ Công cụ phân tích dữ liệu ◼ Các hàm tài chính ◼ Cơ sở dữ liệu
  3. Ôn tập Excel cơ bản
  4. Nội dung chính ◼ Các khái niệm cơ bản: workbook, sheet, column, row, cell, range ◼ Các kiểu dữ liệu: text, number, formula, error ◼ Các thao tác cơ bản: nhập dữ liệu, tính công thức, định dạng ◼ Các địa chỉ ô: địa chỉ tương đối, địa chỉ tuyệt đối, địa chỉ hỗn hợp. ◼ Các hàm thông dụng: – Nhóm hàm thống kê – Nhóm hàm logic – Nhóm hàm toán học – Nhóm hàm ký tự –
  5. Một số hàm mẫu thông dụng ❖ Các hàm thống kê • Hàm SUM- Tính tổng các giá trị số SUM(number1, number2, ) Trong đó: number1, number2, có thể là các giá trị số, các địa chỉ ô hoặc vùng chứa số, các biểu thức số, - Chú ý: có thể dùng nút Autosum để tính tổng nhanh. • Hàm MAX – tính giá tri lớn nhất MAX(number1, number2, ) • Hàm MIN – tìm giá trị nhỏ nhất Min(number1, number2, )
  6. • Hàm AVERAGE – tính giá trị trung bình AVERAGE(number1, number2, ) • COUNT- đếm số ô chứa giá trị số COUNT(number1, number2, ) • Hàm COUNTA - đếm số ô chứa giá trị COUNTA(value1, value2, ) • Hàm SUMIF – tính tổng các ô thỏa mãn điều kiện SUMIF(range, criteria, sum_range) • Hàm COUNTIF – đếm số ô thỏa mãn điều kiện COUNTIF(range, criteria) • Hàm SUMPRODUCT- tính tổng của tích các phần tử ma trận SUMPRODUCT(array1, array2, array3, )
  7. ❖ Các hàm logic • Hàm IF : trả lại giá trị thứ nhất nếu biểu thức logic cho giá trị TRUE hoặc trả giá trị thứ 2 nếu biểu thức logic cho giá trị FALSE IF(logical_test, value_if_true, value_if_false) Ví dụ: = IF(2>3, “a”, “b”) cho giá trị b = IF (2<3,5+4,8*2) cho giá trị 9
  8. • Hàm AND: cho giá trị đúng nếu tất cả các đối có giá trị TRUE, cho giá trị FALSE nếu một hoặc nhiều đối có giá trị FALSE. AND(logical1, logical2, ) • Hàm OR: cho giá trị TRUE nếu bất kỳ một đối nào có giá trị TRUE. Cho giá trị FALSE nếu tất cả các giá trị của đối là FALSE. OR (logical1, logical2, ) • Hàm NOT: cho giá trị phủ định của đối NOT(logical)
  9. ❖ Các hàm tham chiếu • Hàm VLOOKUP: Tìm một giá trị trong cột bên trái cùng của một bảng tham chiếu và sau đó trả lại giá trị trên cùng hàng đó giao với cột được chỉ định trong đối của hàm Quy cách: VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Trong đó: – Lookup_value là giá trị cần tìm ở cột đầu tiên của bảng tham chiếu. Nó có thể là một giá trị, một địa chỉ ô hoặc một dãy ký tự.
  10. – Table_array : là bảng tham chiếu. Nó có thể là địa chỉ vùng (thường để địa chỉ tuyệt đối) hoặc tên vùng. Nếu range_lookup là TRUE thì các giá trị ở cột thứ nhất của bảng tham chiếu phải được đặt theo thứ tự tăng dần. Nếu range_lookup là FALSE, thì các giá trị này chỉ cần khác nhau chứ không cần phải sắp xếp. Các giá trị ở cột đầu tiên của bảng tham chiếu có thể là text, numbers, hoặc các giá trị logic. Các chữ hoa và chữ thường là tương đương.
  11. – Col_index_num là số thứ tự của cột trong bảng tham chiếu mà từ đó có thể nhận được giá trị cần tìm. Chỉ số cột 1 cho giá trị ở cột thứ nhất, chỉ số 2 cho giá trị ở cột thứ 2, Nếu col_index_num số cột của bảng tham chiếu thì VLOOKUP cho giá trị lỗi #REF! – Range_lookup là giá trị logic chỉ định cách tìm kiếm chính xác hoặc gần đúng. Nếu nó có giá trị TRUE hoặc được bỏ qua thì chỉ định cách tìm gần đúng. Nói cách khác, nếu không tìm thấy giá trị chính xác ở cột đầu tiên thì nó sẽ tham chiếu theo giá trị lớn nhất trong các giá trị bé hơn giá trị cần tìm. Nếu nó có giá trị FALSE, thì VLOOKUP sẽ tìm chính xác. Nếu không thấy giá trị cần tìm thì nó sẽ báo lỗi #N/A.
  12. ❖ Các hàm ngày tháng • Hàm DAY: trả lại giá trị ngày của đối ngày tháng DAY(Serial_number) • Hàm Month: trả lại giá trị tháng của đối ngày tháng MONTH(Serial_number) • Hàm YEAR: trả lại giá trị năm của đối ngày tháng. YEAR(Serial_number) • Hàm Today: trả lại giá trị ngày tháng hiện thời TODAY()
  13. ❖ Các hàm ký tự • Hàm LEFT: trả lại n ký tự kể từ trái sang phải của chuỗi ký tự LEFT(Text, num_chars) • Hàm RIGHT: trả lại n ký tự kể từ phải sang trái của chuỗi ký tự RIGHT(Text, num_chars) • Hàm TRIM: cắt các ký tự trống thừa trong chuỗi ký tự TRIM(Text)
  14. • Hàm UPPER: biến chuỗi ký tự thành chữ in hoa UPPER(text) • Hàm LOWER: biến chuỗi ký tự thành chữ thường LOWER(text) • Hàm PROPER: biến chữ đầu từ thành chữ in hoa PROPER(text)
  15. Phân tích dữ liệu
  16. Một số công cụ cơ bản ◼ Thống kê mô tả ◼ Phân tích tương quan ◼ Phân tích hồi quy ◼ Lập bảng tính tần suất ◼ Dự báo – Dự báo nhờ hàm hồi quy – Hàm FORECAST
  17. Thống kê mô tả ◼ Công cụ thống kê mô tả cho phép tạo một báo cáo bao gồm những chỉ số thống kê chung nhất của dãy dữ liệu cần khảo sát
  18. Các bước ◼ Thực hiện Tool -> Data Analysis, xuất hiện hộp hội thoại Data Analysis ◼ Chọn Descriptive Statistic và kích OK. ◼ Trong hộp hội thoại Descriptive Statistic chọn: – Input Range: chọn vùng dữ liệu cần biết các chỉ số thống kê – Group by: Row – nếu dãy dữ liệu được tổ chức theo hàng, Column – nếu dãy dữ liệu được tổ chức theo cột – Chọn Label in First column (First row) nếu trong vùng dữ liệu chọn có chứa cả tiêu đề – Output Range: chọn một ô làm địa chỉ trái trên của vùng xuất dữ liệu – Chọn Summary Statistic – Chọn OK. ◼ Quan sát kết quả thu được
  19. Xác định hệ số tương quan ◼ Thực hiện Tools -> Data Analysis, xuất hiện hộp hội thoại Data Analysis ◼ Chọn Correlation và kích OK. ◼ Trong hộp hội thoại Correlation chọn: – Input Range: địa chỉ các dãy dữ liệu cần xác định hệ số tương quan – Group by: Rows hoặc Columns – Chọn Label in First column/row –nếu trong vùng dữ liệu chọn có chứa các tiêu đề – Output Range: chọn địa chỉ ô trái trên của vùng kết quả – Chọn OK
  20. Phân tích hồi quy ◼ Nhập bảng dữ liệu theo dạng cột ◼ Thực hiện Tools -> Data Analysis, xuất hiện hộp hội thoại Data Analysis ◼ Chọn Regression và kích OK. ◼ Trong hộp hội thoại Regression chọn: – Input Y Range: địa chỉ của vùng dữ liệu chỉ hàm số – Input X Range: địa chỉ của vùng dữ liệu chỉ đối số – Chọn Label nếu trong vùng dữ liệu chứa các tiêu đề – Output Range: chọn địa chỉ ô trái trên của vùng kết quả – Chọn OK. ◼ Trong bảng SUMMARY OUTPUT, chú ý giá trị của hai hệ số a và b để có thể viết được phương trình hồi quy có dạng Y = ax+b hoặc Y = a1X1 + a2X2 + anXn + b
  21. Dự báo ◼ Dự báo nhờ hàm tương quan – Xác định hàm tương quan – Thay giá trị dự báo của X để tìm giá trị cần dự báo của Y ◼ Dự báo nhờ hàm FORECAST FORECAST(X,known_Y,Known_X)
  22. Tính tần suất ◼ Thực hiện Tools -> Data Analysis, xuất hiện hộp hội thoại Data Analysis ◼ Nhập bảng dữ liệu và khoảng phân bố (bin) theo yêu cầu ◼ Chọn Histogram và kích OK. ◼ Trong hộp hội thoại Histogram chọn: – Input Range: địa chỉ của vùng dữ liệu – Bin Range: chọn khoảng phân bố – Chọn Label nếu trong vùng dữ liệu chứa các tiêu đề – Output Range: chọn địa chỉ ô trái trên của vùng kết quả – Chọn OK.
  23. Các hàm tài chính ◼ Nhóm hàm khấu hao ◼ Nhóm hàm tính hiệu quả đầu tư ◼ Nhóm hàm chứng khoán
  24. Nhóm hàm khấu hao
  25. Các hàm khấu hao ◼ Hàm SLN (Straight Line): khấu hao đều ◼ Hàm DB (Declining Balance): khấu hao theo số dư giảm đều. ◼ Hàm DDB (Double Declining Balance): khấu hao theo số dư giảm kép. ◼ Hàm VDB (Variable Declining Balance): khấu hao biến đổi. ◼ Hàm SYD (Sum of Year’s Digit): khấu hao theo tổng kí số của năm. ◼ Công thức tính khấu hao theo tỉ lệ khấu hao cố định.
  26. Hàm SLN ◼ Chức năng: trả lại giá trị khấu hao đều của một tài sản cố định tại một kì xác định ◼ Quy cách: SLN(cost,salvage,life) Trong đó: – Cost: giá trị ban đầu của tài sản – Salvage: giá trị còn lại của tài sản – Life: đời hữu dụng của tài sản
  27. Hàm DB ◼ Chức năng: Tính giá trị khấu hao của một tài sản ở một kì xác định bằng phương pháp số dư giảm dần. ◼ Quy cách: DB(cost,salvage,life,period,month) Trong đó: - Cost: giá ban đầu của tài sản. - Salvage: giá trị còn lại của tài sản. - Life: số kì tài sản được tính khấu hao (đôi khi còn được gọi là đời hữu dụng của tài sản. - Period: kì muốn tính khấu hao. Kì phải cùng đơn vị với đời hữu dụng. - Month: số tháng tính khấu hao của năm đầu tiên. Nếu bỏ qua đối này, nó có giá trị ngầm định là 12.
  28. Ghi chú: ◼ phương pháp số dư giảm dần theo một mức cố định tính khấu hao theo tỉ lệ cố định. DB sử dụng các công thức sau để tính khấu hao cho một kì: (cost - total depreciation from prior periods) * rate Trong đó: rate = 1 - ((salvage / cost) ^ (1 / life)) Được làm tròn đến 3 chữ số thập phân. ◼ Khấu hao cho các kì đầu tiên và kì cuối cùng là trường hơpự đặc biệt. Đối với kì đầu tiên, DB sử dụng công thức sau: cost * rate * month / 12 ◼ Đối với kì cuối cùng, DB sử dụng công thức sau: ((cost - total depreciation from prior periods) * rate * (12 - month)) / 12
  29. Hàm DDB ◼ Chức năng: tính khấu hao của một tài sản cho 1 kì xác định bằng cách sử dụng phương pháp số dư giảm kép hoặc một vài phương pháp khác do ta chỉ định. ◼ Quy cách DDB(cost,salvage,life,period,factor) Trong đó: - Cost: giá trị ban đầu của tài sản - Salvage: giá trị còn lại của tài sản – Life: đời hữu dụng – Period: kì muốn tính khấu hao (phải cùng đơn vị với life. – Factor: tỉ lệ tính khấu hao. Nếu bỏ qua đối này nó có giá trị ngầm định là 2 ◼ Ghi chú: cả 5 đối đều phải là các số dương.
  30. ◼ Theo phương pháp này thì khấu hao tại kì thứ nhất là lớn nhất và giảm dần ở các kỳ tiếp theo. DDB sử dụng công thức sau để tính khấu hao cho một kỳ ((cost-salvage) - total depreciation from prior periods) * (factor/life) ◼ Ta có thể thay đổi factor nếu không muốn sử dụng phương pháp khấu hao giảm kép.
  31. Hàm VDB ◼ Chức năng: tính giá trị khấu hao của một tài sản cho bất kì giai đoạn nào do ta chỉ định bằng cách sử dụng phương pháp khấu hao giảm kép hoặc khấu hao theo tỉ lệ do ta chỉ định. ◼ Quy cách: VDB(cost,salvage,life,start_period, end_period,factor,no_switch) ◼ Trong đó: – Cost: giá trị ban đầu của tài sản. – Salvage: giá trị còn lại của tài sản – Life: đời hữu dụng của tài sản
  32. – Start_period: bắt đầu kì muốn tính khấu hao (phải cùng đơn vị với life). – End_period: cuối kì muốn tính khấu hao (phải cùng đơn vị với life). – Factor: tỉ lệ khấu hao. Nếu bỏ qua đối này nó có giá trị bằng 2 (phương pháp số dư giảm kép). Ta có thể thay đổi giá trị của factor theo nhu cầu – No_switch: giá trị logic xác định có muốn chuyển sang khấu hao đều hay không khi khấu hao lớn hơn tính toán theo DB. Nếu no_switch là TRUE, Microsoft Excel không chuyển sang khấu hao đều ngay cả khi khấu hao lớn hơn phép tính DB. Nếu no_switch là FALSE hoặc bỏ qua thì Excel chuyển sang khấu hao đều khi khấu hao lớn hơn phép tính DB. ◼ Tất cả các đối, trừ đối no_switch phải là số dương.
  33. Hàm SYD ◼ Chức năng: tính khấu hao theo tổng kí số của năm (sum-of-years' digits) của một tài sản tại một kì xác định. ◼ Quy cách: SYD(cost,salvage,life,per) ◼ Trong đó: – Cost: giá trị ban đầu của tài sản – Salvage: giá trị còn lại của tài sản – Life: đời hữu dụng của tài sản – Per: kì tính khấu hao (phải cùng đơn vị với life). ◼ Công thức:
  34. Nhóm hàm tài chính
  35. Các hàm tài chính ◼ Hàm FV ◼ Hàm FV Schedule ◼ Hàm PV ◼ Hàm NPV ◼ Hàm IRR ◼ Hàm PMT ◼ Hàm NPER ◼ HÀm RATE
  36. Hàm FV ◼ Chức năng: tính giá trị tương lai (future value) của một khoản đầu tư với món tièn đầu tư cố định theo định kì và lãi suất không đổi ◼ Quy cách: FV(rate,nper,pmt,pv,type) ◼ Trong đó: – Rate: lãi suất của từng kì – Nper: tổng số kì đầu tư cho một tài sản – Pmt: số tiền trả mỗi kì, phải giống nhau trong tất cả các kì. Pmt bao gồm tiền gốc và lãi nhưng không tính đến thuế và các khoản chi phí khác. Nếu bỏ qua đối pmt thì bắt buộc phải có đối pv. – Pv: giá trị hiện tại của tài sản hoặc tổng tiền tích lũy quy về hiện tại. Nếu bỏ qua đối PV thì bắt buộc phải có đối pmt. – Type: là số 0 nếu khoản đầu tư định kì ở đầu từng kì; là số 1 nếu khoản đầu tư định kì ở cuối kì.
  37. Hàm Fvschedule ◼ Chức năng: tính giá trị tương lai của một khoản đầu tư sau khi áp dụng một dãy lãi suất thay đổi ◼ Quy cách: FVSCHEDULE(principal,schedule) ◼ Trong đó: – Principal: là giá trị hiện tại – Schedule: là mảng lãi suất được áp dụng.
  38. Hàm PV ◼ Chức năng: tính giá trị hiện tại của một khoản đầu tư. Giá trị hiện tại là tổng dòng tiền đầu tư trong tương lai được quy về hiện tại. ◼ Ví dụ, khi ta vay tiền thì số tiền vay là giá trị hiện tại đối với người cho vay. ◼ Quy cách: PV(rate,nper,pmt,fv,type) ◼ Trong đó: – Rate: lãi suất của từng kì. – Nper: tổng số kì chi trả cho một tài sản. – Pmt: số tiền chi trả trong mỗi kì và phải không đổi trong tất cả các kì. Nếu không có đối pmt thì phải có đối fv. – Fv: giá trị tương lai.Nếu bỏ qua đối fv thì phải có đối pmt. – Type: là số 0 nếu chi trả ở cuối kì hoặc số 1 nếu chi trả ở đầu kì.
  39. Hàm NPV ◼ Chức năng: tính giá trị hiện tại ròng (net present value) của một khoản đầu tư bằng cách sử dụng tỉ lệ chiết khấu và chuỗi các dòng tiền chi trả tuơng lai (giá trị âm) và các lợi nhuận thu về (các giá trị âm) ◼ Quy cách: NPV(rate,value1,value2, ) ◼ Trong đó: – Rate: tỉ lệ chiết khấu của từng kì – Value1, value2, là 1 đến 29 đối biểu thị dòng tiền chi trả hoặc lợi nhuận thu về. Value1, value2, Phải được tính định kì (khoảng thời gian bằng nhau) và ở cuối mỗi kì. ◼ Chú ý: Việc tính NPV dựa vào dòng tiền tương lai. Nếu việc trả tiền đầu tiên xảy ra ở đầu kì thứ nhất thì giá trị đầu tiên này phải được cộng vào hàm NPV chứ không được đưa vào đối số của hàm NPV.
  40. ◼ Nếu gọi n là số lượng dòng tiền trong danh sách các giá trị thì công thức tính NPV là: ◼ NPV tương tự hàm PV (present value). Sự khác nhau cơ bản giữa PV và NPV là ở chỗ PV cho phép dòng tiền bắt đầu tại hoặc cuối kì hoặc tại đầu kì. Không giống các giá trị dòng tiền của NPV có thể biến đổi, dòng tiền của PV bắt buộc phải không thay đổi trong suốt giai đoạn đầu tư.
  41. Hàm IRR ◼ Chức năng: tính tỉ lệ nội hoàn cho một chuỗi các dòng tiền được chi trả hoặc thu về theo định kì (hàng tháng hoặc hàng năm). Tỉ lệ nội hoàn là lãi suất nhận được mà tại đó giá trị NPV = 0. ◼ Quy cách: IRR(values,guess) ◼ Trong đó: – Values: là mảng các giá trị hoặc các địa chỉ ô chứa các giá trị muốn tính tỉ lệ nội hoàn. Values phải chứa ít nhất một giá trị dương và một giá trị âm. – Guess là giá trị dự báo của IRR.
  42. ◼ Microsoft Excel sử dụng kỹ thuật lặp để tính IRR. Bắt đầu với guess, IRR lặp lại các phép tính cho tới khi kết quả chính xác tới 0.00001 phần trăm. Nếu IRR không tìm ra kết quả sau 20 lần thử, Excel báo lỗi #NUM!. ◼ Trong đa số các trường hợp, ta không cần phải cho giá trị của guess để tính IRR. Nếu bỏ qua guess, nó nhận giá trị 0.1 (10%). ◼ Nếu IRR cho giá trị lỗi #NUM! hoặc nếu kết quả không giống như ta mong muốn thì chúng ta phải cho giá trị khác của guess.
  43. Hàm PMT ◼ Chức năng: tính tiền phải trả (cả gốc và lãi) của một khoản vay trong một kì nếu như phải trả số tiền trong các kì là như nhau và lãi suất không thay đổi. ◼ Quy cách: PMT(rate,nper,pv,fv,type)
  44. Hàm NPER ◼ Cho số kì của một khoản đầu tư dựa trên những khoản trả tiền định kì, không đổi và lãi suất không đổi ◼ Quy cách: NPER(rate, pmt, pv, fv, type)
  45. Hàm RATE ◼ Cho lãi suất trong một kì của một khoản tiền trả góp hàng kì. ◼ RATE được tính bằng phép lặp và có thể không có nghiệm hoặc có nhiều nghiệm. Nếu các kết quả liên tiếp của RATE không hội tụ tới 0.0000001 sau 20 phép lặp thì RATE cho hía trị lỗi #NUM! ◼ Quy cách: RATE(nper,pmt,pv,fv,type,guess)
  46. Cơ sở dữ liệu
  47. Các chức năng cơ bản của cơ sở dữ liệu ◼ Sắp xếp dữ liệu ◼ Tìm kiếm và lọc dữ liệu ◼ Sử dụng Form ◼ Tổng hợp dữ liệu ◼ Hàm CSDL
  48. Sắp xếp dữ liệu ◼ Một số khái niệm - Khóa sắp xếp: - khóa chính - khóa thứ hai - khóa thứ ba - Thứ tự sắp xếp: - Tăng dần (Ascending) - Giảm dần (Descending) - Tùy biến (custom list).
  49. Custom list ▪ Tạo Custom List ▪ Cách 1: + Tạo dãy trên bảng tính + Thực hiện Tools -> Options -> Custom List + Trong hộp Import List from cells: chọn địa chỉ dãy có sẵn + Kích nút Import + OK ▪ Cách 2: + Thực hiện Tools -> Options -> Custom List + Chọn New List + Gõ chuỗi + Kích nút Add + OK
  50. Sắp xếp dữ liệu ▪ Sắp xếp nhanh - Sử dụng các nút A-> Z hoặc Z-> A ▪ Sắp xếp theo nhiều tiêu thức ▪ Chọn một ô trong bảng dữ liệu ▪ Thực hiện Data -> Sort ▪ Chọn các thông số trong hộp hội thoại ▪ Sort by: chọn trường làm khóa 1 ▪ Then by: chọn trường làm khóa 2 (nếu cần) ▪ Then by: chọn trường làm khóa 3 (nếu cần) ▪ Optiions: nếu chọn sắp xếp theo thứ tự tùy biến ▪ OK
  51. ▪ Sử dụng Custom list: điền chuỗi hoặc sắp xếp dữ liệu ▪ Xóa Custom list + Thực hiện Tools -> Options -> Custom List + Chọn chuỗi cần xóa + Kích nút Delete + OK
  52. Lọc dữ liệu ▪ Lọc tự động (AutoFilter) ▪ Lọc nâng cao (Advanced Filter)
  53. Lọc tự động ▪ Chọn 1 ô trong vùng dữ liệu cần lọc ▪ Thực hiện Data -> Filter -> AutoFilter. Xuất hiện các mũi tên lọc. ▪ Chọn mũi tên lọc cạnh trường cần lọc ▪ Chọn một trong các lựa chọn tại hộp thả xuống ▪ Chọn 1 giá trị ▪ All ▪ Top 10 ▪ Custom ▪ Có thể chọn các điều kiện lọc ở các cột khác nhau.
  54. Lọc nâng cao ▪ Ngoài vùng dữ liệu phải có vùng tiêu chuẩn: ▪ Vùng tiêu chuẩn trực tiếp: tổi thiểu một cột, hai hàng. Hàng đầu phải là tên của trường trong cơ sở dữ liệu. Hàng thứ hai trở đi là các tiêu chuẩn cần lọc. ▪ Vùng tiêu chuẩn gián tiếp: tổi thiểu một cột, hai hàng. Hàng đầu phải để trống hoặc đặt tên khác với tên của trường trong cơ sở dữ liệu. Hàng thứ hai trở đi phải là công thức. * Nếu các điều kiện trên cùng hàng tương ứng với phép AND. Nếu các điều kiện trên các hàng khác nhau tương ứng với phép OR.
  55. ▪ Thao tác lọc nâng cao + Thực hiện Data -> Filter ->Advanced Filter. + Trong hộp hội thoại, chọn các thông số - Filter the list, in-place: lọc tại chỗ - Copy to another location: sao chép kết quả lọc đến vị trí khác - List range: địa chỉ cùng dữ liệu - Criteria range: địa chỉ cùng tiêu chuẩn - copy to: địa chỉ ô trái trên của vùng đích. - Unique record only: chỉ kết xuất 1 trong số bản ghi trùng nhau + OK.
  56. Form - Chọn vùng dữ liệu - Thực hiện Data -> Form - Chọn các thông số tương ứng trong hộp hội thoại - New: tạo bản ghi mới - Delete: xóa bản ghi hiện thời - Restore: khôi phục giá trị cũ của bản ghi hiện thời - Find Prev: chuyển tới bản trước đó - Find Next: chuyển tới bản ghi tiếp theo - Criteria/Form: tìm các bản ghi thỏa mãn tiêu chuẩn - Close: đóng hộp hội thoại
  57. Tổng hợp dữ liệu ◼ Subtotal ◼ Consolidate ◼ Pivot Table
  58. Subtotal ▪ SubTotal là chức năng tổng hợp dữ liệu theo nhóm
  59. ▪ Tạo Subtotal - Tạo cơ sở dữ liệu - Sắp xếp theo khóa phân nhóm - Data -> Subtotal, chọn tiếp + At Each Change In: chọn tên trường phân nhóm. + Use Function: chọn hàm (190) + Add Subtotal To: chọn tên trường cần tổng hợp dữ liệu + Replace Current Subtotals: thay thể các tổng con hiện thời + Page Brreak Between Group: ngắt trang giữa các nhóm +Summary Below Data: kết quả tổng hợp nằm dưới dữ liệu chi tiết. ▪ Hủy Subtotal - Chọn vùng đã có Subtotal - Data -> Subtotal -> Remove All.
  60. Cosolidate ◼ Đây là chức năng cho phép tổng hợp dữ liệu từ nhiều bảng tính có cấu trúc tương tự nhau.
  61. Các bước ◼ Chọn 1 hoặc một vài ô ở vùng đích ◼ Thực hiện Data -> Consolidate ◼ Trong hộp hội thoại chọn – Function: chọn hàm - Reference: chọn địa chỉ vùng dữ liệu nguồn - Kích nút Add. - Lặp lại 2 bước trên nên nếu cần thiết - Uses Label in: top row - sử dụng nhãn ở hàng trên cùng, left colums – sử dụng nhãn ở cột trái trên. - Create links to source data: tạo liên kết với dữ liệu nguồn. - Kích OK.
  62. PIVOT TABLE ◼ Các thành tố trên một bảng cân đối tổng hợp – Trường phân trang (Page Fields): là tên trường của CSDL được chọn sao cho ứng với mỗi giá trị của nó sẽ có một trang báo cáo cân đối tổng hợp của những trường khác. – Trường dòng báo cáo (Pivot Table Row): Đó là tên trường của CSDL được chọn để ứng với mỗi giá trị của nó sẽ có một dòng trên trang báo cáo và giá trị của nó sẽ nằm ở ô đầu của dòng báo cáo.
  63. ◼ Trường cột báo cáo (Pivot Table Column): là tên trường của CSDL được chọn để xác định tên các cột trên báo cáo. ◼ 4. Nội dung của báo cáo: Đó là tên trường cần được tổng hợp giá trị ở các ô báo cáo. ◼ 5. Tổng (Grand Total): Tổng theo hàng và theo cột được tính và được ghi ở cột bên phải và hàng cuối cùng của một báo cáo.
  64. Tạo Pivot Table ◼ Thực hiện Data -> PivotTable and PivotChart Report. Xuất hiện hộp hội thoại PivotTable and PivotChart Wizard - Step1 of 3. – Chọn Microsoft Excel List or Databasse. – Kích Next. Xuất hiện hộp hội thoại PivotTable and PivotChart Wizard - Step2 of 3. ◼ Chọn địa chỉ vùng dữ liệu trong hộp Range. – Kích nút Next, xuất hiện tiếp hộp hội thoại PivotTable and PivotChart Wizard - Step3 of 3.
  65. ◼ Kích vào nút Layout, hộp hội thoại PivotTable and PivotChart Wizard - Layout. Dùng chuột kéo tên các trường vào những vùng tương ứng theo yêu cầu của bài toán cần tổng hợp ◼ Sau khi kích OK. Chọn tiếp vị trí đặt bảng kết quả. ◼ Kích vào nút Finish, chúng ta thu được kết quả cần tổng hợp
  66. Xử lý và biến đổi các kết quả bảo cáo ▪ Lựa chon một tiêu chuẩn cần tổng hợp (ví dụ 1) ▪ - Lựa chon một số tiêu chuẩn cần tổng hợp ▪ Chuyển đổi vị trí các trường ▪ Bổ sung, xoá bớt các trường
  67. Các hàm CSDL Quy cách chung: (Database, Field, Criteria) Trong đó: - Database: địa chỉ hoặc tên vùng CSDL - Field: địa chỉ hoặc số thứ tự của trường trong CSDL - Criteria: địa chỉ hoặc tên vùng tiêu chuẩn
  68. Các hàm CSDL 1. Hàm tính trung bình trên CSDL DAVERAGE(Database, Field, Criteria) 1. Hàm đếm số lượng trên CSDL DCOUNT(Database, Field, Criteria) 3. Đếm số lượng bản ghi trên CSDL DCOUNTA(Database, Field, Criteria) 4. Lấy giá trị bản ghi thoả mãn tiêu chuẩn DGET(Database, Field, Criteria) 5. Tìm giá trị lớn nhất của một trường thoả mãn tiêu chuẩn DMAX (Database, Field, Criteria)
  69. 6. Tìm giá trỊ nhỏ nhất của một trường thỏa mãn tiêu chuẩn DMIN (Database, Field, Criteria) 7. Tìm độ lệch chuẩn trên một trường thỏa mãn tiêu chuẩn DSTDEV (Database, Field, Criteria) 8. Tính tổng có lựa chọn trên một trường DSUM (Database, Field, Criteria) 9. Tính phương sai dựa trên một mẫu của CSDL DVAR (Database, Field, Criteria)
  70. TÍNH TỔNG HỢP THEO NHÓM CON – SUBTOTAL ◼Quy cách: SUBTOTAL (Function_num, Ref) Trong đó: - Function_num là số hiệu của hàm tính cho dòng tổng hợp con. Nó có các giá trị sau: 1. AVERAGE 2. COUNT 3. COUNTA 4. MAX 5. MIN 6. PRODUCT 7. STEDEV 8. STDEVP 9. SUM 10. VAR 11. VARP - Ref là tham chiếu tới vùng muốn tổng hợp theo nhóm. Chú ý: Nếu trong vùng tham chiếu của một hàm SUBTOTAL lại chứa một hàm SUBTOTAL khác thì Excel bỏ qua các dòng này để khỏi tính toán hai lần một giá trị.