Bài giảng Tin học ứng dụng trong kinh doanh - Chương 3: Cơ sở dữ liệu trong Excel

Chương 3

Cơ sở dữ liệu trong Excel

GV: Nguyễn Thị Thùy Liên

Email: lien.nguyenthithuy@phenikaa-uni.edu.vn

Khái niệm

❖CSDL(Database) là tập hợp thông tin, dữ liệu được tổ chức theo kiểu danh sách để có thể tìm kiếm, lọc, xóa những dòng dữ liệu thỏa mãn một tiêu chuẩn nào đó một cách nhanh chóng

• Hàng đầu tiên của danh sách chứa tên trường

• Tên của các trường phải là dạng chuỗi và khác nhau

▪ Field (trường):

• Các hàng còn lại chứa thông tin về một đối tượng trong

danh sách

▪ Record (bản ghi):

Tin học ứng dụng

2

1. Các hàm về cơ sở dữ liệu

❖ Cấu trúc tổng quát

Dfunction(Database,Field, Criteria)

▪ Database: Là một CSDL dạng danh sách của Excel

▪

Field: là tên của trường

▪ Criteria: là vùng điều kiện cần thiết mà hàm phải

thỏa mãn để tác động lên trường dữ liệu đã chỉ ra bởi Field

▪ Hàm CSDL sẽ trả lại kết quả tính toán phù hợp với những ràng buộc được chỉ ra ở vùng điều kiện

Tin học ứng dụng

3

1. Các hàm về cơ sở dữ liệu

còn lại chứa các điều kiện

❖ Criteria: Dòng đầu tiên chứa tiêu đề, các dòng

Tin học ứng dụng

4

1. Các hàm về cơ sở dữ liệu

❖ Các hàm cơ sở dữ liệu: 1.

Tính tổng các số trong một cột của cơ sở dữ liệu thỏa mãn điều kiện

=DSUM(Database,Field, Criteria)

2.

Tính trung bình cộng các số trong một cột của cơ sở dữ liệu thỏa mãn điều kiện

=DAVERAGE(Database,Field, Criteria)

3. Đếm các ô chứa số trong một cột của cơ sở dữ liệu thỏa mãn điều

kiện

=DCOUNT(Database,Field, Criteria)

4. Đếm các ô chứa dữ liệu trong một cột của cơ sở dữ liệu thỏa mãn

điều kiện

=DCOUNTA(Database,Field, Criteria)

5. Hàm cho giá trị lớn nhất/nhỏ nhất của các ô trên một cột của cơ sở

dữ liệu thỏa mãn điều kiện

=DMAX(Database,Field, Criteria) =DMIN(Database,Field, Criteria)

Tin học ứng dụng

5

1. Các hàm về cơ sở dữ liệu

Tin học ứng dụng

6

2. Sắp xếp – Menu Data/Sort

các cột để tránh sự mất chính xác dữ liệu.

❖Khi xếp thứ tự 1 danh sách (CSDL), phải chọn tất cả

❖Trường quy định cách xếp thứ tự gọi là khóa.

▪ Các bản ghi cùng giá trị ở khóa thứ nhất được xếp thứ tự

theo khóa thứ 2

▪ Cùng giá trị ở khóa thứ 2 được xếp thứ tự theo khóa thứ 3.

❖DS không có tên trường thì tên cột sẽ thay thế

❖Cách làm: Chọn miền. Chọn Menu Data/Sort…

Tin học ứng dụng

7

2. Sắp xếp – Menu Data/Sort

Tin học ứng dụng

8

3. Tìm kiếm (Lọc dữ liệu) Menu Data/Filter

điều kiện nhất định

❖Mục đích: Lấy ra những bản ghi (thông itn ) thỏa mãn

▪ Filter: Excel hỗ trợ điều kiện lọc

❖Có thể lọc theo 2 cách:

▪ Advanced: người sử dụng định điều kiện lọc

Tin học ứng dụng

9

3.1 Lọc dữ liệu dùng Filter

Chọn miền CSDL gồm cả dòng tên trường

Menu Data > nhóm Sort&Filter > Filter, => ô tên trường có đầu mũi tên thả xuống của hộp danh sách

Kích chuột mũi tên, có danh sách thả xuống cho phép lọc theo các giá trị có sẵn hoặc them điều kiện lọc

Tin học ứng dụng

10

3.1 Lọc dữ liệu dùng Filter

Tin học ứng dụng

11

3.1 Lọc dữ liệu dùng AutoFilter

❖Nếu chọn Custom… sẽ hiện hộp hội thoại Custom AutoFilter để người sử dụng tự định điều kiện lọc:

Tin học ứng dụng

12

3.2 Lọc dữ liệu dùng Advanced Filter

❖B1: Định miền điều kiện:

▪ Dòng đầu ghi tên trường để định điều kiện, chú ý phải giống hệt tên trường của miền CSDL, tốt nhất là copy từ tên trường CSDL.

▪ Các dòng tiếp dưới ghi điều kiện: các dòng điều kiện cùng dòng là phép AND, các điều kiện khác dòng là phép OR.

Tin học ứng dụng

13

3.2 Lọc dữ liệu dùng Advanced Filter

Tin học ứng dụng

14

3.2 Lọc dữ liệu dùng Advanced Filter

❖B2: Thực hiện lọc

▪ Menu Data > nhóm Sort&Filter > Advanced

Tin học ứng dụng

15

3.2 Lọc dữ liệu dùng Advanced Filter

cơ bản > 3000000 hoặc phụ cấp chức vụ >200

▪ Ví dụ 1: Tìm kiếm và đưa ra những người có Lương

Tin học ứng dụng

16

3.2 Lọc dữ liệu dùng Advanced Filter

cơ bản > 3000000 và phụ cấp chức vụ >200

▪ Ví dụ 2: Tìm kiếm và đưa ra những người có Lương

Tin học ứng dụng

17

17

3.2 Lọc dữ liệu dùng Advanced Filter

>3000000 hoặc sinh trước năm 1983

▪ Ví dụ 3: Tìm kiếm và đưa ra những người có lương

Tin học ứng dụng

18

3.2 Lọc dữ liệu dùng Advanced Filter

Tin học ứng dụng

19

3.2 Lọc dữ liệu dùng Advanced Filter

Tin học ứng dụng

20

20

3.2 Lọc dữ liệu dùng Advanced Filter

Tin học ứng dụng

21

21

3.3 Tạo các dòng tổng (Subtotal)

Vùng dữ liệu ban đầu được sắp xếp theo Phòng ban và Họ tên

❖Subtotal dùng để tổng hợp dữ liệu theo nhóm trong danh sách

Tin học ứng dụng

22

3.3 Tạo các dòng tổng (Subtotal)

Subtotal

❖Vào menu Data > nhóm lệnh Outline > chọn lệnh

Tin học ứng dụng

23

3.3 Tạo các dòng tổng (Subtotal)

Với mỗi thay đổi ở Phòng ban thì dùng hàm Sum để thêm subtotal cho Lương. Thay thế các subtotal nào đang có và ghi phần tổng kết (summary ) phía dưới dữ liệu

Tin học ứng dụng

24

3.3 Tạo các dòng tổng (Subtotal)

Subtotal Lương xuất hiện mỗi khi có thay đổi ở cột Phòng ban. Chú ý sự xuất hiện các dấu móc ở bên trái bảng tính để phân định từng subtotal. Có thể clik các nút phân cấp này để thu gọn hoặc làm bung ra từng cấp subtotal

=> Nên copy dữ liệu qua vùng khác trước khi làm subtotal

Tin học ứng dụng

25

3.4 Biểu đồ

❖Chuyển dữ liệu trong bảng tính thành hình ảnh

 trực quan hóa dữ liệu

 so sánh và cho thấy xu thế dữ liệu

 trình bày quan điểm một cách nhanh chóng

Tin học ứng dụng

26

3.4 Biểu đồ

▪ chú ý chọn cả 1 tiêu đề hàng và 1 tiêu đề cột đối với các đồ

thị kiểu Column, Line, Pie

❖B1. Chọn miền dữ liệu vẽ đồ thị

❖B2. Vào menu Insert | nhóm Charts

Tin học ứng dụng

27

3.4 Biểu đồ

❖B3: Chọn kiểu biểu đồ

❖Chọn kiểu đồ thị có sẵn:

▪ Line: đường so sánh

▪ Column: cột dọc

▪ Bar: thanh

▪ Pie: bánh tròn

▪ XY: đường tương quan

Tin học ứng dụng

28

Các thao tác sau khi tạo đồ thị

❖ Có thể chuyển đồ thị tới vị trí mới bằng phương thức Drag &

Drop

❖ Thay đổi kích thước đồ thị bằng cách kích chuột vào vùng

trống của đồ thị để xuất hiện 8 chấm đen ở 8 hướng, đặt chuột vào chấm đen, giữ trái chuột và di tới kích thước mong muốn rồi nhả chuột.

❖ Thay đổi các thuộc tính của đồ thị (tiêu đề, chú giải,..) bằng

cách nháy chuột phải vào vùng trống của đồ thị và chọn Chart Options..

❖ Thay đổi các thuộc tính của các thành phần đồ thị (font chữ, tỷ lệ các trục, màu sắc nền..) bằng cách nháy chuột phải vào thành phần đó và chọn Format

Tin học ứng dụng

29

Chọn đúng kiểu đồ thị

❖Mỗi đồ thị đều chứa đựng ý nghĩa riêng.

❖Các đồ thị khác nhau có thể truyền đi các thông điệp

=> tùy vào mục đích truyền thông điệp cần phải lựa chọn kiểu đồ thị phù hợp sao cho hiệu quả nhất.

khác nhau của cùng một đối tượng dữ liệu

Tin học ứng dụng

30

Đồ thị cột (Column)

với nhau.

❖Đồ thị cột thích hợp để so sánh trực tiếp các giá trị

Tin học ứng dụng

31

Pie chart

❖Đồ thị bánh để so sánh các phần tử trong 1 tổng thể để làm rõ mức độ đóng góp của từng phần tử. Đây là tư tưởng để biểu diễn cơ cấu, tỷ trọng của 1 đối tượng.

Tin học ứng dụng

32

Line chart

giá trị qua thời gian

❖Biểu đồ Line so sánh các

❖Sử dụng đồ thị Line khi trục hoành là thời gian

❖Đồ thì đường (line) phù hợp biểu diễn xu hướng biến động theo giời gian như sản lượng, doanh thu, và lợi nhuận.

Tin học ứng dụng

33

XY Scatter – mối quan hệ 2 đại lượng

❖Đồ thị XY Scatter phù hợp để thể hiện mối quan hệ giữa các giá trị dữ liệu khoa học hay thống kê

❖Thể hiện 2 thang đo trong 1 đồ thị qua 2 trục X và Y

Tin học ứng dụng

34