Excel's Database and List Management - Các Hàm Quản lý Cơ sở dữ liệu và Danh sách

[BNTT]

List of Excel's Database and List Management


Danh mục các Các Hàm Quản lý Cơ sở dữ liệu và Danh sách

• Nói chung về Các Hàm Quản lý Cơ sở dữ liệu và Danh sách

• Một số ví dụ về cách dùng Criteria để nhập điều kiện

DAVERAGEhttp://giaiphapexcel.com/forum/showpost.php?p=57007&postcount=3 (database, field, criteria) : Tính trung bình các giá trị trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DCOUNThttp://giaiphapexcel.com/forum/showpost.php?p=57011&postcount=4 (database, field, criteria) : Đếm các ô chứa số liệu trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DCOUNTAhttp://giaiphapexcel.com/forum/showpost.php?p=57014&postcount=5 (database, field, criteria) : Đếm các ô "không rỗng" trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DGEThttp://giaiphapexcel.com/forum/showpost.php?p=57024&postcount=6 (database, field, criteria) : Trích một giá trị từ một cột của một danh sách hay cơ sở dữ liệu, khớp với điều kiện được chỉ định.

DMAXhttp://giaiphapexcel.com/forum/showpost.php?p=57030&postcount=7 (database, field, criteria) : Trả về trị lớn nhất trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DMINhttp://giaiphapexcel.com/forum/showpost.php?p=57032&postcount=8 (database, field, criteria) : Trả về trị nhỏ nhất trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DRODUCThttp://giaiphapexcel.com/forum/showpost.php?p=57038&postcount=9 (database, field, criteria) : Nhân các giá trị trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSTDEVhttp://giaiphapexcel.com/forum/showpost.php?p=57039&postcount=10 (database, field, criteria) : Ước lượng độ lệch chuẩn của một tập hợp theo mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSTDEVPhttp://giaiphapexcel.com/forum/showpost.php?p=57040&postcount=11 (database, field, criteria) : Tính độ lệch chuẩn của một tập hợp theo toàn thể các tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DSUMhttp://giaiphapexcel.com/forum/showpost.php?p=57041&postcount=12 (database, field, criteria) : Cộng các số trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DVARhttp://giaiphapexcel.com/forum/showpost.php?p=57057&postcount=13 (database, field, criteria) : Ước lượng sự biến thiên của một tập hợp dựa trên một mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

DVARPhttp://giaiphapexcel.com/forum/showpost.php?p=57058&postcount=14 (database, field, criteria) : Tính toán sự biến thiên của một tập hợp dựa trên toàn thể tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, item2,...) : Trả về dữ liệu được lưu giữ trong báo cáo PivotTable. Có thể dùng GETPIVOTDATA để lấy dữ liệu tổng kết từ một báo cáo PivotTable, với điều kiện là phải thấy được dữ liệu tổng kết từ trong báo cáo đó.
​

Nói chung về Các Hàm Quản lý Cơ sở dữ liệu và Danh sách

Microsoft Excel cung cấp nhiều hàm dùng cho việc phân tích dữ liệu trong danh sách hay cơ sở dữ liệu. Những hàm này bắt đầu bằng chữ D.

Mỗi hàm đều sử dụng 3 đối số là database, field và criteria; những đối số này là các tham chiếu đến các mảng trong bảng tính.

• database : Là một dãy các ô tạo nên danh sách hay cơ sở dữ liệu. Một cơ sở dữ liệu là một danh sách dữ liệu gồm những mẩu tin, và có cột dữ liệu là các trường (field). Dòng đầu tiên trong danh sách luôn chứa tên trường.

• field : Cho biết cột nào được sử dụng trong hàm. field có thể được cho ở dạng text với tên cột được để trong cặp dấu ngoặc kép (như "Age", "Yield"...) hay là số đại diện cho vị trí của cột (1, 2, ...)

• criteria : Là một dãy các ô chứa điều kiện. Có thể dùng bất cứ dãy nào cho phần điều kiện này, miễn là dãy đó có ít nhất một tên cột và một ô bên dưới tên cột để làm điều kiện cho hàm (xem thêm bài Một số ví dụ về cách dùng Criteria để nhập điều kiện ở sau đây).

Trong các bài chi tiết về từng hàm, tôi sẽ sử dụng bảng tính sau đây để làm ví dụ minh họa về cách sử dụng hàm:

Với vùng database là A5:E11.​

 

[BNTT]

Một số ví dụ về cách dùng Criteria để nhập điều kiện

Cách thể hiện dấu bằng (=) khi nhập điều kiện:

Vì dấu bằng (=) được dùng để nhập công thức, nên chúng ta không thể gõ trực tiếp dấu bằng trong những điều kiện.
Để thể hiện được dấu bằng (=) trong ô điều kiện, chúng ta có 2 cách:

• Dùng dấu nháy đơn ở trước dấu bằng: '=Apple
• Đặt cả cụm chỉ điều kiện (gồm cả dấu bằng) trong một cặp ngoặc kép: ="=Apple"

Nhập nhiều điều kiện trong một cột:

Điều kiện: (Salesperson = "Davolio" HOẶC Salesperson = "Buchanan")

Cách nhập như hình sau. Kết quả là những ô được tô đậm (A7:C9)

Nhập nhiều điều kiện trong nhiều cột và tất cả các điều kiện đó đều phải đúng:

Điều kiện: (Type = "Produce" VÀ Sales > 1000)

Cách nhập như hình sau. Kết quả là những ô được tô đậm (A8:C9)

Nhập nhiều điều kiện trong nhiều cột và điều kiện nào đúng cũng được:

Điều kiện: (Type = "Produce" HOẶC Sales = "Davolio")

Cách nhập như hình sau. Kết quả là những ô được tô đậm (A7:C9)

Nhập nhiều nhóm điều kiện mà mỗi nhóm bao gồm điều kiện trong nhiều cột:

Điều kiện: ((Salesperson = "Davolio" VÀ Sales >3000) HOẶC (Salesperson = "Buchanan" VÀ Sales > 1500))


Cách nhập như hình sau. Kết quả là những ô được tô đậm (A8:C9)

Nhập nhiều nhóm điều kiện mà mỗi nhóm bao gồm điều kiện trong một cột:

Điều kiện: ((Sales > 6000 VÀ Sales < 6500) HOẶC (Sales < 500))

Cách nhập như hình sau. Kết quả là những ô được tô đậm (A7:C8)

Nhập điều kiện lọc text dùng ký tự đại diện ? và *:

• Nhập text mà không có dấu bằng (=) ở trước, sẽ lọc những text bắt đầu bắt đầu bằng những text này. Ví dụ: nhập Dav, Excel sẽ tìm những ra từ như "David", "Davolio", "Davis", v.v...

• Ký tự ? sẽ đại diện cho 1 ký tự. Ví dụ: sm?th sẽ đại diện cho "smith" hoặc "smyth"...

• Ký tự * sẽ đại diện cho nhiều ký tự. Ví dụ: *east sẽ đại diện cho "Northeast" hoặc "Southeast"...

Những ô được tô đậm (A6:C8) ở hình sau là kết quả của việc tìm kiếm những Type bắt đầu bằng chữ "Me" hoặc những Salesperson có chữ "u" là ký tự thứ hai.

Ở hai ví dụ sau đây, điều kiện là kết quả của một công thức. Khi dùng điều kiện như vậy, cần lưu ý hai điều sau:

• Không được nhập công thức đó vào bên dưới những cột có tên field (trường dữ liệu) trùng với tên của những field trong database, mà nên tạo thêm một cột khác, với một tên field khác.

• Công thức phải tham chiếu đến những ô nằm trên hàng đầu tiên của database (A7 và C7 hai ví dụ sau)

Lọc những giá trị lớn hơn trung bình cộng của tổng giá trị:

Những ô được tô đậm (A6:C6 và A8:C9) ở hình sau là kết quả của việc lọc ra những Sales lớn hơn trung bình cộng của các Sales:

Lọc những giá trị text phân biệt chữ thường và chữ hoa:

Thường thì khi lọc những giá trị text (như những ví dụ trên đây), Excel không phân biệt cách viết (produce = Produce).

Để Excel phân biệt cách viết, ta dùng hàm EXACT().

 

[BNTT]

Hàm DAVERAGE()

Tính trung bình các giá trị trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DAVERAGE(database, field, criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DAVERAGE(A5:E11, "Yield", A1:B2) = 12 (trung bình lợi tức của các cây táo cao trên 10 feet)

= DAVERAGE(A5:E11, 3, A5:E11) = 13 (trung bình tuổi của tất cả các cây có trong database)​

 

[BNTT]

Hàm DCOUNT()

Đếm các ô chứa số liệu trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định.
Trong hàm DCOUNT(), đối số field là tùy chọn. Nếu bỏ qua đối số này thì DCOUNT() sẽ đếm tất cả các mẩu tin trong cơ sở dữ liệu theo điều kiện được chỉ định.

Cú pháp: = DCOUNT(database, [field,] criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DCOUNT(A5:E11, "Age", B1:F2) = 3

(tìm trong database tất cả những mẩu tin có Height nằm trong khoảng 10 đến 16, rồi đếm xem trong số những mẩu tin tìm được này có bao nhiêu mẩu tin có chứa số liệu ở cột Age)​

 

[BNTT]

Hàm DCOUNTA()

Đếm các ô "không rỗng" trong một cột của danh sách hay các trị của một cơ sở dữ liệu, theo một điều kiện được chỉ định.
Trong hàm DCOUNT(), đối số field là tùy chọn. Nếu bỏ qua đối số này thì DCOUNT() sẽ đếm tất cả các mẩu tin trong cơ sở dữ liệu theo điều kiện được chỉ định.

Cú pháp: = DCOUNTA(database, [field,] criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DCOUNTA(A5:E11, "Profit", B1:F2) = 3

(tìm trong database tất cả những mẩu tin có Height nằm trong khoảng 10 đến 16, rồi đếm xem trong số những mẩu tin tìm được này có bao nhiêu mẩu tin ở cột Profit không rỗng)​

 

[BNTT]

Hàm DGET()

Trích một giá trị từ một cột của một danh sách hay cơ sở dữ liệu, khớp với điều kiện được chỉ định.

Cú pháp: = DGET(database, field, criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DGET(A5:E11, "Yield", A1:A3) = #NUM!

(Hàm trả về lỗi #NUM! bởi vì có nhiều hơn một mẩu tin thỏa điều kiện)
​

• Nếu không có mẩu tin nào thỏa điều kiện, hàm DGET() sẽ trả về giá trị lỗi #VALUE!

• Nếu có nhiều hơn một mẩu tin thỏa điều kiện, hàm DGET() sẽ trả về giá trị lỗi #NUM!

 

[BNTT]

Hàm DMAX()

Trả về trị lớn nhất trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DMAX(database, field, criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DMAX(A5:E11, "Profit", A1:A3) = 105

(tìm trong database lợi nhuận lớn nhất thu được từ Apple và Pear)​

 

[BNTT]

Hàm DMIN()

Trả về trị nhỏ nhất trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DMIN(database, field, criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DMIN(A5:E11, "Profit", A1:B2) = 75

(tìm trong database lợi nhuận thấp nhất của những cây táo có độ cao trên 10 feet)​

 

[BNTT]

Hàm DPRODUCT()

Nhân các giá trị trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DPRODUCT(database, field, criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DPRODUCT(A5:E11, "Yield", A1:B2) = 140

(nhân các lợi tức của những cây táo có độ cao trên 10 feet)​

 

[BNTT]

Hàm DSTDEV()

Ước lượng độ lệch chuẩn của một tập hợp theo mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DSTDEV(database, field, criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DSTDEV(A5:E11, "Yield", A1:A3) = 2.96647939

(ước lượng độ lệch chuẩn theo lợi tức (yield) của Apple và Pear nếu dữ liệu trong cơ sở dữ liệu chỉ là một mẫu của toàn thể vườn cây)​

 

[BNTT]

Hàm DSTDEVP()

Tính độ lệch chuẩn của một tập hợp theo toàn thể các tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DSTDEVP(database, field, criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DSTDEVP(A5:E11, "Yield", A1:A3) = 2.65329983

(Độ lệch chuẩn thực sự theo lợi tức (yield) của Apple và Pear nếu dữ liệu trong cơ sở dữ liệu là dữ liệu của toàn thể vườn cây)​

 

[BNTT]

Hàm DSUM()

Cộng các số trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DSUM(database, field, criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DSUM(A5:E11, "Profit", A1:A2) = 225

(Tổng lợi nhuận từ các cây táo)​

 

[BNTT]

Hàm DVAR()

Ước lượng sự biến thiên của một tập hợp dựa trên một mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DVAR(database, [field,] criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DVAR(A5:E11, "Yield", A1:A3) = 8.8

(ước lượng sự biến thiên theo lợi tức (yield) của Apple và Pear nếu dữ liệu trong cơ sở dữ liệu chỉ là một mẫu của toàn thể các tập hợp trong vườn cây)​

 

[BNTT]

Hàm DVARP()

Tính toán sự biến thiên của một tập hợp dựa trên toàn thể tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.

Cú pháp: = DVARP(database, [field,] criteria)

Ví dụ: (Xem bảng tính ở bài 1)

= DVARP(A5:E11, "Yield", A1:A3) = 7.04

(sự biến thiên thực sự theo lợi tức (yield) của Apple và Pear, nếu dữ liệu trong cơ sở dữ liệu là toàn thể các tập hợp trong vườn cây)​

 

[BNTT]

Hàm GETPIVOTDATA()

Trả về dữ liệu được lưu giữ trong báo cáo PivotTable. Có thể dùng GETPIVOTDATA() để lấy dữ liệu tổng kết từ một báo cáo PivotTable, với điều kiện là phải thấy được dữ liệu tổng kết từ trong báo cáo đó.

Để nhanh chóng nhập công thức GETPIVOTDATA(), bạn có thể gõ dấu = vào ô muốn nhận dữ liệu tổng kết, rồi nhấp vào ô chứa số liệu tổng kết của báo cáo PivotTable.

Cú pháp: = GETPIVOTDATA(data_field, pivot_table, field1, item1, field2, item2,...)

data_field : Tên trường chứa dữ liệu tổng kết của báo cáo PivotTable. Tên này phải được đặt trong một cặp dấu nháy kép.​

pivot_data : Tham chiếu đến một ô, dãy ô, hoặc tên dãy ô bên trong một báo cáo PivotTable. Thông tin này được dùng để xác định báo cáo PivotTable nào chứa dữ liệu trả về.​

field1, item1, field2, item2,... : Có thể có từ 1 đến 126 (với Excel 2003 trở về trước thì con số này chỉ là 14) cặp tên field và item mô tả dữ liệu muốn trả về. Những cặp này có thể thuộc bất cứ loại nào. Nếu tên field và item không là ngày tháng hoặc số liệu, cần phải đặt chúng trong cặp dấu nháy kép. Đối với loại báo cáo OLAP PivotTable, items có thể bao gồm đầy đủ đường dẫn lẫn tên của item. Một cặp field và item của OLAP PivotTable có thể như sau:

"[Product]","[Product].[All Products].[Foods].[Baked Goods]"​

Lưu ý:

• Các field hay item tính toán và các phép tính tự tạo có thể được đặt trong phép tính GETPIVOTDATA.

• Nếu pivot_table là một dãy có chứa nhiều hơn 1 báo cáo PivotTable, dữ liệu trả về sẽ là từ báo cáo được tạo ra sau cùng.

• Nếu đối số field và các đối số của item mô tả chỉ mỗi một ô, giá trị của ô đó sẽ được trả về mà không cần biết giá trị đó là chuỗi, là số, là lỗi, hay là một thứ gì đó...

• Nếu item chứa ngày tháng, giá trị phải được biểu diễn dưới dạng một chuỗi số hoặc được thiết lập bằng cách dùng hàm DATE() để giá trị đó sẽ không biến đối khi bảng tính được mở ở một máy khác, có hệ thống định dạng ngày tháng khác với nơi tạo ra nó. Ví dụ, một item tham chiếu tới ngày 5 tháng 3 năm 1999 có thể được nhập là 36224 hay DATE(1999, 3, 5). Thời gian có thể được nhập như một giá trị thập phân hoặc bằng cách dùng hàm TIME().

• Nếu pivot_table không phải là một dãy có chứa báo cáo PivotTable, GETPIVOTDATA() sẽ trả về lỗi #REF!

• Nếu các đối số miêu tả một field không thể thấy được, hoặc nếu chúng gồm một trường không hiển thị, GETPIVOTDATA() cũng sẽ trả về lỗi #REF!

Ví dụ:

Đây là một dãy có chứa báo cáo PivotTable:

• GETPIVOTDATA("Sales", $A$4) trả về tổng giá trị lớn nhất của Sales ($ 49,325)

• GETPIVOTDATA("Sum of Sales", $A$4) cũng trả về tổng giá trị của Sales ($ 49,325): tên trường có thể nhập chính xác như trên bảng tính, hoặc như bản gốc (không có "Sum of", "Count of"...)

• GETPIVOTDATA("Sales", $A$4, "Month", "March") trả về tổng giá trị của Sales trong tháng 3 ($ 30,337)

• GETPIVOTDATA("Sales", $A$4, "Month", "March", "Product", "Produce", "Salesperson", "Buchanan") trả về giá trị ở ô D6 ($ 10,201)

• GETPIVOTDATA("Sales", $A$4, "Region", "South") trả về #REF! bởi vì không tìm thấy dữ liệu trong vùng South.

• GETPIVOTDATA("Sales", $A$4, "Product", "Beverages", "Salesperson", "Davolio") cũng trả về #REF! bởi vì ở đó không có tổng giá trị của Product là Beverages đối với Salesperson là Davolio.