Bài giảng Tin học 2 - Chương 7: Cơ sở dữ liệu trong Excel

1CHƯƠNG 7CƠ SỞ DỮ LIỆU TRONG EXCEL7.1. CƠ SỞ DỮ LIỆUCơ sở dữ liệu trong Excel gồm:Trường (Field): là một thuộc tính nào đó của đối tượng. Ví dụ: Họ tên, Giới tính, Ngày sinh, HSL, vvBản ghi (Record): là giá trị của các trường tương ứng. Ví dụ: Nguyễn Văn A, Nam, 23/04/1978, 3.67, vv27.2. SỬ DỤNG FORM ĐỂ NHẬP DỮ LIỆU7.2.1. Hiển thị chức năng FORMChọn File/Options/Chọn Customize RibbonTại Choose commands from: chọn All commands, trong phần này bạn chọn Form/Nhấn Add/Chọn Tab và nhấn OK.37.2. SỬ DỤNG FORM ĐỂ NHẬP DỮ LIỆU7.2.2 Chức năng formBước 1: Bôi đen vùng cơ sở dữ liệuBước 2: Thực hiện Data/FormBước 3: Thực hiện các chức năng trên hộp thoại.4Nhập mớiXóa bản ghiTìm đến bản ghi kế tiếpĐóng hộp thoại formXóa bản ghiTìm đến bản ghi trước đóTìm theo điều kiện7.3. QUY ĐỊNH DỮ LIỆU KHI NHẬP (VALIDATION)Bôi đen vùng dữ liệu cần đặt điều kiện nhập.Chọn Data/Data Validation/Data Validation. Xuất hiện hộp thoại:5Đặt quy tắc nhập dữ liệuHiển thị lời nhắc khi di chuyển đến vùng nhậpChọn mức độ quy định nhập dữ liệu7.3. QUY ĐỊNH DỮ LIỆU KHI NHẬP (VALIDATION)Thẻ Settings:Allow: + Any value: giá trị bất kỳ + Whole number: quy định cho dữ liệu kiểu số + Decimal: quy định cho dữ liệu kiểu thập phân. + List: Dữ liệu nhập vào theo danh sách (nhập vào source hoặc bôi đen vùng danh sách) + Date: quy định cho dữ liệu kiểu ngày. + Time: quy định cho dữ liệu kiểu thời gian. + Text length: quy định theo chiều rộng của dữ liệu kiểu ký tự. + Custom: quy tắc xác định theo công thức, bắt đầu bởi dấu = 67.3. QUY ĐỊNH DỮ LIỆU KHI NHẬP (VALIDATION)Thẻ Input MessageShow input message when cell is selected: Tích chọn nếu di chuyển chuột vào thì hiển thị lời nhắc.Title: Tiêu đề của trên hộp thoại thông báo.Input Message: Nội dung thông báo. 77.3. QUY ĐỊNH DỮ LIỆU KHI NHẬP (VALIDATION)Thẻ Error AlertShow error alert after invalid data is entered: Tích chọn để hiển thị thông báo lỗi khi dữ liệu nhập vào không đúng quy định nhập.Style: Chọn mức độ quy định +STOP: mức độ cao nhất, không cho nhập dữ liệu nếu vi phạm quy tắc. + WARNING: vẫn cho phép nhập dữ liệu khi vi phạm quy tắc nhưng người dùng nhấn YES. + INFORMATION: vẫn cho nhập dữ liệu khi vi phạm quy tắc nhưng người dùng nhấn OK. Title: tiêu đề của cửa sổ thông báo.Error Message: Nội dung thông báo khi nhập dữ liệu vi phạm quy tắc. 87.4. SẮP XẾP CSDLBước 1: Chọn vùng CSDL.Bước 2: Chọn Data\Sort. Xuất hiện hộp hội thoại:97.5. SỬ DỤNG AUTOFILTER, ADVANCED FILTER7.5.1. Chức năng AutoFilterAuto Filter là tính năng cho phép người dùng lọc ra những bản ghi thỏa mãn điều kiện. Để thực hiện lọc ta thực hiện như sau:Bước 1: Bôi đen vùng CSDL (cả hàng tiêu đề)Bước 2: Chọn Data/Filter.Tại trường có điều kiện lọc, kích chọn:107.5.1. Auto Filter+ Equals: lọc ra bản ghi có giá trị bằng giá trị bạn gõ vào hộp thoại.+ Does Not Equal: lọc ra các bản ghi có giá trị khác giá trị mà bạn gõ vào hộp thoại+ Greater Than: lọc ra các bản ghi lớn hơn giá trị mà bạn gõ vào+ Greater Than Or Equal To: lọc ra các bản ghi có giá trị lớn hơn hoặc bằng giá trị mà bạn gõ vào+ Less Than: lọc ra bản ghi có giá trị nhỏ hơn giá trị gõ vào.+ Less Than Or Equal To: lọc ra các bản ghi có giá trị nhỏ hơn hoặc bằng giá trị bạn gõ vào.117.5.1. Auto Filter+ Between: lọc ra những bản ghi nằm trong khoảng giá trị mà bạn gõ vào.+ Top 10: giữ lại 10 bản ghi có giá trị lớn nhất+ Above Average: lọc ra những bản ghi có giá trị lớn hơn trung bình cộng của tất cả các bản ghi trong cột.+ Below Average: lọc ra những bản ghi có giá trị nhỏ hơn trung bình cộng của tất cả các bản ghi trong cột.+ Begins With: Lọc ra bản ghi bắt đầu bởi ký tự mà bạn gõ vào.+ Ends With: Lọc ra bản ghi kết thúc bởi ký tự mà bạn gõ vào.+ Contains: Lọc ra bản ghi chứa ký tự mà bạn gõ vào.+ Does Not Contain: Lọc ra bản ghi không chứa ký tự mà bạn gõ vào.127.5.1. Auto Filter+ Custom Filter: tùy chọn khác, hiển thị hộp thoại:Tại vị trí 1 tích chọn vào hộp thả, xuất hiện các tùy chọn như trên, gõ giá trị vào vị trí 3, nếu có hai điều kiện đồng thời thì tích chọn And, điều kiện hoặc thì tích chọn Or và chọn tại vị trí 2, gõ giá trị vào vị trí số 4.* Hủy lọc:Để hủy tính năng lọc, chọn Data/Filter. 137.5.2. ADVANCED FILTERThiết lập vùng điều kiện (criteria range)- Vùng điều kiện có hai loại: điều kiện trực tiếp và điều kiện gián tiếp.* Vùng điều kiện trực tiếp:Được thành lập bằng cách sau: Gồm tối thiểu hai hàng, hàng đầu tiên phải là tên trường trong CSDL, từ hàng thứ hai trở đi gõ giá trị cần so sánh với nó, có thể kết hợp dấu >, ,>=, <=, =.Nếu điều kiện đồng thời (and) thì giá trị bản ghi của các trường cùng hàng, còn điều kiện hoặc thì phải khác hàng.147.5.2. ADVANCED FILTERThiết lập vùng điều kiện (criteria range)* Vùng điều kiện trực tiếp:Ví dụ 1:+ Điều kiện lọc những ra những người có Giới tính là Nữ hoặc Thực lĩnh lớn hơn 5 triệu:157.5.2. ADVANCED FILTERThiết lập vùng điều kiện (criteria range)* Vùng điều kiện trực tiếp:Ví dụ 1:+ Điều kiện lọc ra những người có Họ là “Đỗ” và Thực lĩnh nhỏ hơn 5 triệu:167.5.2. ADVANCED FILTERa. Thiết lập vùng điều kiện (criteria range)* Vùng điều kiện gián tiếp:Được thiết lập gồm hai hàng và một cột, hàng đầu tiên lấy tên bất kỳ (không được trùng với bất kỳ tên trường nào trong CSDL), hàng thứ hai là biểu thức logic, có thể kết hợp nhiều hàm, so sánh trên bản ghi đầu tiên của CSDL, kết quả trả về TRUE hoặc FALSE.Ví dụ: + Điều kiện lọc những ra những người có Giới tính là Nữ hoặc Thực lĩnh lớn hơn 5 triệu:177.5.2. ADVANCED FILTERa. Thiết lập vùng điều kiện (criteria range)* Vùng điều kiện gián tiếp:Ví dụ: + Điều kiện lọc ra những người có Họ là “Đỗ” và Thực lĩnh nhỏ hơn 5 triệu:18+ Điều kiện lọc ra những người ở phòng "Kế hoạch" và Thực lĩnh khác 6 triệu:7.5.2. ADVANCED FILTERb. Sử dụng tính năng Advanced FilterBước 1: Bôi đen vùng cơ sở dữ liệu (hàng tiêu đề không được trộn – Merge cell)Bước 2: Chọn Data/Tại Sort & Filter chọn Advanced. Xuất hiện hộp thoại:197.5.2. ADVANCED FILTERb. Sử dụng tính năng Advanced FilterAction: + Filter the list, in-place: Lọc và để kết quả tại bảng csdl đang chọn.+ Copy to another location: Lưu kết quả sau khi lọc sang một vị trí khác, vị trí này được xác định bởi phần Copy to (chỉ cần chọn 1 cell trong vùng trống để lưu)List range: vùng csdlCriteria range: vùng điều kiện (được thiết lập bằng vùng điều kiện gián tiếp hoặc trực tiếp)Unique records only: tích chọn này để giữ lại một bản ghi nếu trùng nhau.Nhấn OK207.6. CHỨC NĂNG SUBTOTALBước 1: Sắp xếp vùng CSDL, sắp xếp theo trường phân nhóm.Bước 2: Bôi đen vùng CSDL đã được sắp xếp, chọn Data/Subtotal. Xuất hiện hộp hội thoại:217.6. CHỨC NĂNG SUBTOTALAt each change in: Chọn trường đã tham gia phân nhóm.Use function: Chọn hàm tham gia tổng hợp.+ Sum: tính tổng (hàm mặc định)+ Count: Đếm số bản ghi+ Max: Tính giá trị lớn nhất+ Min: Tính giá trị nhỏ nhất+ Product: Tính tích các giá trị227.6. CHỨC NĂNG SUBTOTALAdd subtotal to: Chọn trường mà thực hiện tính toán trên dữ liệu của trường đó.Replace current subtotals: Tích chọn để thay thế Subtotal hiện tại.Page break between group: Đặt dấu ngắt trang giữa các nhóm.Summary below data: Kết quả tổng hợp để cuối mỗi nhóm.Remove all: Xóa bỏ chức năng Subtotal.Nhấn OK: Chấp nhận các thiết lập.Cancel: Bỏ qua. 237.7. HÀM SUBTOTALSubtotal là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất.Cú pháp: SUBTOTAL(function_num,ref1,ref2,...)Function_num là các con số từ 1 đến 11 và từ 101 đến 111 qui định hàm nào sẽ được dùng để tính toán trong subtotalRef1, ref2,... là các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trên đó.247.7. HÀM SUBTOTALFunction_numHàm tính toánTính toán cả các giá trị ẩn (bằng Hide)Không tính toán các giá trị ẩn (bằng Hide)1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARP257.8. NHÓM HÀM CSDLCÚ PHÁP CHUNG:DTên hàm(database, field, criteria)Trong đó: - D Tên hàm có thể là các hàm: DAVERAGE, DSUM, DMAX, DMIN, DCOUNT, DCOUNTA.- database: vùng cơ sở dữ liệu bao gồm cả tên các trường và các bản ghi.- field: thứ tự xuất hiện tên trường trong cơ sở dữ liệu, hoặc địa chỉ ô chứa tên trường hoặc tên trường đặt trong cặp dấu “”, là trường tham gia tính toán trên nó.- criteria: vùng điều kiện để thực hiện tính toán, vùng này có thể là vùng trực tiếp hoặc gián tiếp.267.8. NHÓM HÀM CSDL1. DSUM (database, field, criteria)* Ý nghĩa: Sử dụng để tính tổng trên trường field theo điều kiện, field phải có dữ liệu kiểu số.* Ví dụ: Dựa vào Bang luong, tính tổng Thực lĩnh của những người có giới tính nữ hoặc thực lĩnh lớn hơn 5 triệu:277.8. NHÓM HÀM CSDL2. DMAX(database, field, criteria)Ý nghĩa: Sử dụng để đưa ra giá trị lớn nhất trên trường field theo điều kiện, field phải có dữ liệu kiểu số.3. DMIN(database, field, criteria)* Ý nghĩa: Sử dụng để đưa ra giá trị nhỏ nhất trên trường field theo điều kiện, field phải có dữ liệu kiểu số.4. DAVERAGE(database, field, criteria)* Ý nghĩa: Sử dụng để đưa ra trung bình cộng trên trường field theo điều kiện, field phải có dữ liệu kiểu số.287.8. NHÓM HÀM CSDL5. DCOUNT(database, field, criteria)* Ý nghĩa: Đếm số bản ghi trên trường field theo điều kiện, field phải có dữ liệu kiểu số.* Ví dụ: Dựa vào Bang luong, đếm số người có giới tính nữ hoặc thực lĩnh lớn hơn 5 triệu:297.8. NHÓM HÀM CSDL6. DCOUNTA(database, field, criteria)* Ý nghĩa: Đếm số bản ghi trên trường field theo điều kiện, dữ liệu của trường đếm có thể là kiểu số, ký tự, vv.* Ví dụ: Dựa vào Bang luong, đếm số người có giới tính nữ hoặc thực lĩnh lớn hơn 5 triệu:307.9. CHỨC NĂNG CONSOLIDATEChức năng Consolidate cho phép tổng hợp dữ liệu trên nhiều sheet hoặc trên các sheet của các Book khác nhau. Với điều kiện là CSDL đó có cấu trúc giống nhau, và kết quả được lưu ở một sheet bất kỳ. * Thao tác thực hiện chức năng Consolidate- Bước 1: Đặt con trỏ tại vị trí ô cần để bảng tổng hợp, thực hiện lệnh Data/Tại Data Tools chọn Consolidate.- Bước 2: Xuất hiện hộp hội thoại:317.9. CHỨC NĂNG CONSOLIDATE* Function:Sum: tính tổngAverage: tính trung bình cộngMax: tính giá trị lớn nhấtMin: tính giá trị nhỏ nhất.Count: Đếm số bản ghi.Reference: vùng dữ liệu tham gia tổng hợp.Browse: thêm vùng dữ liệu nếu dữ liệu ở tệp khácAll reference: hiển thị vùng dữ liệu tham gia tổng hợp.327.10. TẠO CÁC BẢNG PIVOTĐể thực hiện việc tạo bảng tổng hợp bằng Pivot Table, bạn thực hiện các bước sau:- Bước 1: Bôi đen bảng CSDL.- Bước 2: Chọn Insert/Pivot Table/Pivot Table. Xuất hiện hộp thoại:33Chọn vùng dữ liệuChọn vị trí lưu bảng Pivot7.10. TẠO CÁC BẢNG PIVOTXuất hiện bảng có dạng:347.10. TẠO CÁC BẢNG PIVOTMột số khái niệm:+ Report Filter:Chọn trường lọc dữ liệu trên bảng Pivot.+ Column Labels: Kéo trường hiển thị dưới dạng cột trong bảng Pivot.+ Row Lables: Kéo trường hiển thị dưới dạng hàng trong bảng Pivot.+ Values: Trường tham gia quá trình tính toán (sử dụng các hàm trong Function).357.10. TẠO CÁC BẢNG PIVOTVí dụ: Tính tổng thực lĩnh theo Chức vụ và Phòng ban, lọc theo giới tính. 367.10. TẠO CÁC BẢNG PIVOTChú ý: Có thể thay đổi các trường bằng việc kéo thả, thay đổi hàm tính toán bằng cách tích chọn vào phần Sum of Thực lĩnh trong Values, chọn Value Field Settings. Xuất hiện hộp hội thoại:377.11. TẠO BIỂU ĐỒ PIVOT CHARTPivot Chart là chức năng cho phép tổng hợp dữ liệu dưới dạng biểu đồ.Để thực hiện được chức năng này:Bước 1: Bôi đen vùng dữ liệu cần tạo biểu đồ.Thực hiện Insert/Pivot Table/Pivot Chart38Chọn vùng dữ liệuChọn vùng lưu biểu đồ Pivot7.11. TẠO BIỂU ĐỒ PIVOT CHART39+ Report Filter: Những Field nào được thêm vào trong vùng này, sẽ được dùng để lọc toàn bộ dữ liệu nguồn. Nói cách khác, PivotChart chỉ hiển thị những dữ liệu nào thỏa mãn điều kiện của Report Filter. + Axis Field: Chọn trường hiển thị trên biểu đồ theo trục hoành (nằm ngang) + Legend Field: trường hiển thị chú giải theo trục tung (nằm dọc). + Value: Tính toán trên trường để hiển thị dữ liệu trên biểu đồ mặc định là hàm SUM. Bạn có thể chọn hàm khác bằng việc tích vào trường trên vùng Value, chọn Value Field Settings (giống như trong Pivot Table).7.11. TẠO BIỂU ĐỒ PIVOT CHART40Mặc định là hàm SUM. Chọn hàm khác bằng việc tích vào trường trên vùng Value, chọn Value Field Settings (giống như trong Pivot Table). Ví dụ: Biểu đồ tổng thực lĩnh theo chức vụ, hiển thị theo phòng ban.