Bài viết này mô tả cú pháp công thức và cách dùng hàm DAVERAGE trong Microsoft Excel.
Mô tả
Tính trung bình các giá trị trong một trường (cột) bản ghi trong danh sách hay cơ sở dữ liệu khớp với các điều kiện mà bạn xác định.
Cú pháp
DAVERAGE(database, field, criteria)
Cú pháp hàm DAVERAGE có các đối số sau đây:
-
Cơ sở dữ liệu là phạm vi ô tạo thành danh sách hoặc cơ sở dữ liệu. Cơ sở dữ liệu là một danh sách chứa các dữ liệu liên quan, trong đó các hàng thông tin liên quan là các bản ghi và các cột dữ liệu là các trường. Hàng đầu tiên của danh sách có chứa nhãn cho mỗi cột.
-
Field chỉ rõ cột nào được dùng trong hàm. Hãy nhập nhãn cột đặt trong dấu ngoặc kép, ví dụ như "Tuổi" hoặc "Hoa lợi" hay một số (không có dấu trích dẫn) thể hiện vị trí cột trong danh sách: 1 cho cột đầu tiên, 2 cho cột thứ 2, v.v.
-
Criteria là phạm vi ô chứa các điều kiện mà bạn chỉ rõ. Bạn có thể dùng bất kỳ phạm vi nào cho đối số criteria, miễn là nó có chứa ít nhất một nhãn cột và ít nhất một ô bên dưới nhãn cột đó, mà trong đó bạn xác định điều kiện cho cột đó.
Chú thích
-
Bạn có thể dùng bất kỳ phạm vi nào cho đối số criteria, miễn là nó có chứa ít nhất một nhãn cột và ít nhất một ô bên dưới nhãn cột để xác định điều kiện.
Ví dụ, nếu phạm vi G1:G2 có chứa nhãn cột Thu nhập trong ô G1 và số tiền 10.000 trong ô G2, bạn có thể định nghĩa phạm vi đó là Khớp Thu nhập và sử dụng tên gọi này làm đối số criteria trong các hàm cơ sở dữ liệu.
-
Mặc dù phạm vi tiêu chí có thể được định vị ở bất kỳ đâu trên trang tính, nhưng bạn không nên đặt phạm vi tiêu chí bên dưới danh sách. Nếu bạn thêm thông tin vào danh sách, thông tin mới sẽ được thêm vào hàng đầu tiên bên dưới danh sách. Nếu hàng bên dưới danh sách không trống, Excel không thể thêm thông tin mới vào được.
-
Hãy đảm bảo là phạm vi tiêu chí đó không chồng lấp lên danh sách.
-
Để thực hiện thao tác trên toàn bộ một cột trong cơ sở dữ liệu, hãy nhập một dòng trống bên dưới nhãn cột trong phạm vi tiêu chí.
Ví dụ
Sao chép dữ liệu ví dụ trong bảng sau đây và dán vào ô A1 của một bảng tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.
Cây |
Chiều cao |
Tuổi thọ |
Hoa lợi |
Lợi nhuận |
Chiều cao |
---|---|---|---|---|---|
=Táo |
>10 |
<16 |
|||
=Lê |
|||||
Cây |
Chiều cao |
Tuổi thọ |
Hoa lợi |
Lợi nhuận |
|
Táo |
18 |
20 |
14 |
105 |
|
Lê |
12 |
12 |
10 |
96 |
|
Anh đào |
13 |
14 |
9 |
105 |
|
Táo |
14 |
15 |
10 |
75 |
|
Lê |
9 |
8 |
8 |
76,8 |
|
Táo |
8 |
9 |
6 |
45 |
|
Công thức |
Mô tả |
Kết quả |
|||
=DAVERAGE(A4:E10, "Hoa lợi", A1:B2) |
Hoa lợi trung bình của những cây táo cao trên 10 feet. |
12 |
|||
=DAVERAGE(A4:E10, 3, A4:E10) |
Tuổi trung bình của tất cả các cây trong cơ sở dữ liệu. |
13 |
Ví dụ về tiêu chí
-
Gõ dấu bằng vào ô sẽ cho thấy rằng bạn muốn nhập công thức. Để hiển thị văn bản có chứa dấu bằng, hãy đặt văn bản và dấu bằng trong ngoặc kép, giống như thế này:
"=Davolio"
Bạn cũng có thể làm điều đó nếu bạn nhập biểu thức (tổ hợp các công thức, toán tử và văn bản) và bạn muốn hiển thị dấu bằng thay vì để Excel dùng nó trong tính toán. Ví dụ:
=''= mục nhập ''
Trong đó mục nhập là văn bản hay giá trị mà bạn muốn tìm. Ví dụ:
Những gì bạn gõ vào ô |
Những gì Excel đánh giá và hiển thị |
---|---|
="=Davolio" |
=Davolio |
="=3000" |
=3000 |
-
Khi lọc dữ liệu văn bản, Excel không phân biệt giữa các ký tự viết hoa và viết thường. Tuy nhiên, bạn có thể dùng công thức để thực hiện tìm kiếm phân biệt chữ hoa, chữ thường. Để có ví dụ, hãy xem Lọc văn bản bằng cách dùng tìm kiếm phân biệt chữ hoa, chữ thường, ở phần dưới bài viết này.
Các phần sau đây cung cấp ví dụ về các tiêu chí phức tạp.
Nhiều tiêu chí trong một cột
Logic Boole: (Người bán hàng = "Davolio" OR Người bán hàng = "Buchanan")
Để tìm những hàng đáp ứng nhiều tiêu chí cho một cột, hãy gõ tiêu chí này ngay dưới tiêu chí kia trong các hàng riêng biệt của phạm vi tiêu chí.
Trong phạm vi dữ liệu sau đây (A6:C10), phạm vi tiêu chí (B1:B3) hiển thị những hàng chứa "Davolio" hoặc "Buchanan" trong cột Người bán hàng (A8:C10).
|
A |
B |
C |
---|---|---|---|
1 |
Loại |
Nhân viên bán hàng |
Doanh số |
2 |
=Davolio |
||
3 |
=Buchanan |
||
4 |
|||
5 |
|||
6 |
Loại |
Nhân viên bán hàng |
Doanh số |
7 |
Nước giải khát |
Suyama |
5122 $ |
8 |
Thịt |
Davolio |
450 $ |
9 |
nông sản |
Buchanan |
6328 $ |
10 |
Nông sản |
Davolio |
6544 $ |
Nhiều tiêu chí trong nhiều cột, trong đó tất cả các tiêu chí đều phải đúng
Logic Boole: (Loại = "Nông sản" AND Doanh số > 1000)
Để tìm những hàng đáp ứng nhiều tiêu chí trong nhiều cột, hãy gõ tất cả các tiêu chí vào cùng hàng của phạm vi tiêu chí.
Trong phạm vi dữ liệu sau đây (A6:C10), phạm vi tiêu chí (A1:C2) hiển thị tất cả các hàng chứa "Nông sản" trong cột Loại và có giá trị lớn hơn 1.000 $ trong cột Doanh số (A9:C10).
|
A |
B |
C |
---|---|---|---|
1 |
Loại |
Nhân viên bán hàng |
Doanh số |
2 |
=Nông sản |
>1000 |
|
3 |
|||
4 |
|||
5 |
|||
6 |
Loại |
Nhân viên bán hàng |
Doanh số |
7 |
Nước giải khát |
Suyama |
5122 $ |
8 |
Thịt |
Davolio |
450 $ |
9 |
nông sản |
Buchanan |
6328 $ |
10 |
Nông sản |
Davolio |
6544 $ |
Nhiều tiêu chí trong nhiều cột, trong đó bất kỳ tiêu chí nào cũng có thể đúng
Logic Boole: (Loại = "Nông sản" OR Người bán hàng = "Davolio")
Để tìm hàng đáp ứng nhiều tiêu chí trong nhiều cột, trong đó bất kỳ tiêu chí nào cũng có thể đúng, hãy gõ tiêu chí vào các hàng khác nhau của phạm vi tiêu chí.
Trong phạm vi dữ liệu sau đây (A6:C10), phạm vi tiêu chí (A1:B3) hiển thị tất cả các hàng chứa "Nông sản" trong cột Loại hoặc "Davolio" trong cột Người bán hàng (A8:C10).
|
A |
B |
C |
---|---|---|---|
1 |
Loại |
Nhân viên bán hàng |
Doanh số |
2 |
=Nông sản |
||
3 |
=Davolio |
||
4 |
|||
5 |
|||
6 |
Loại |
Nhân viên bán hàng |
Doanh số |
7 |
Nước giải khát |
Suyama |
5122 $ |
8 |
Thịt |
Davolio |
450 $ |
9 |
nông sản |
Buchanan |
6328 $ |
10 |
Nông sản |
Davolio |
6544 $ |
Nhiều bộ tiêu chí trong đó mỗi bộ chứa các tiêu chí cho nhiều cột
Logic Boole: ( (Người bán hàng = "Davolio" AND Doanh số >3000) OR (Người bán hàng = "Buchanan" AND Doanh số > 1500) )
Để tìm những hàng đáp ứng nhiều bộ tiêu chí, trong đó mỗi bộ chứa tiêu chí cho nhiều cột, hãy gõ mỗi bộ tiêu chí vào từng hàng riêng biệt.
Trong phạm vi dữ liệu sau đây (A6:C10), phạm vi tiêu chí (B1:C3) hiển thị các hàng chứa cả giá trị "Davolio" trong cột Người bán hàng và giá trị lớn hơn 3.000 $ trong cột Doanh số, hoặc các hàng có chứa "Buchanan" trong cột Người bán hàng và giá trị lớn hơn 1.500 $ trong cột Doanh số (A9:C10).
|
A |
B |
C |
---|---|---|---|
1 |
Loại |
Nhân viên bán hàng |
Doanh số |
2 |
=Davolio |
>3000 |
|
3 |
=Buchanan |
>1500 |
|
4 |
|||
5 |
|||
6 |
Loại |
Nhân viên bán hàng |
Doanh số |
7 |
Nước giải khát |
Suyama |
5122 $ |
8 |
Thịt |
Davolio |
450 $ |
9 |
nông sản |
Buchanan |
6328 $ |
10 |
Nông sản |
Davolio |
6544 $ |
Nhiều bộ tiêu chí trong đó mỗi bộ chứa các tiêu chí cho một cột
Logic Boole: ( (Doanh số > 6000 AND Doanh số < 6500 ) OR (Doanh số < 500) )
Để tìm các hàng đáp ứng nhiều bộ tiêu chí, trong đó mỗi bộ chứa tiêu chí cho một cột, hãy đặt cùng một đề mục cột cho nhiều cột.
Trong phạm vi dữ liệu sau đây (A6:C10), phạm vi tiêu chí (C1:D3) hiển thị các hàng chứa các giá trị từ 6.000 đến 6.500 và các giá trị nhỏ hơn 500 trong cột Doanh số (A8:C10).
|
A |
B |
C |
D |
---|---|---|---|---|
1 |
Loại |
Nhân viên bán hàng |
Doanh số |
Doanh số |
2 |
>6000 |
<6500 |
||
3 |
<500 |
|||
4 |
||||
5 |
||||
6 |
Loại |
Nhân viên bán hàng |
Doanh số |
|
7 |
Nước giải khát |
Suyama |
5122 $ |
|
8 |
Thịt |
Davolio |
450 $ |
|
9 |
nông sản |
Buchanan |
6328 $ |
|
10 |
Nông sản |
Davolio |
6544 $ |
Các tiêu chí để tìm những giá trị văn bản có chung một số ký tự, nhưng không có chung các ký tự khác
Để tìm những giá trị văn bản có chung một số ký tự, nhưng không có chung các ký tự khác, hãy thực hiện một hoặc nhiều thao tác sau đây:
-
Nhập một hoặc nhiều ký tự mà không có dấu bằng (=) để tìm những hàng có giá trị văn bản nằm trong cột bắt đầu bằng các ký tự đó. Ví dụ, nếu bạn gõ văn bản Dav làm tiêu chí, Excel tìm thấy "Davolio," "David" và "Davis."
-
Dùng ký tự đại diện.
Các ký tự đại diện sau đây có thể được dùng làm tiêu chí so sánh.
Sử dụng |
Để tìm |
---|---|
? (dấu hỏi) |
Bất kỳ ký tự đơn nào Ví dụ, sm?th tìm "smith" và "smyth" |
* (dấu hoa thị) |
Bất kỳ số ký tự nào Ví dụ, *east tìm "Northeast" và "Southeast" |
~ (dấu ngã) tiếp theo là ?, *, hoặc ~ |
Dấu hỏi, dấu hoa thị hoặc dấu ngã Ví dụ: fy91~? tìm "fy91?" |
Trong phạm vi dữ liệu sau đây (A6:C10), phạm vi tiêu chí (A1:B3) hiển thị những hàng có các ký tự đầu tiên là "Me" trong cột Loại hoặc những hàng có ký tự thứ hai là "u" trong cột Người bán hàng (A7:C9).
|
A |
B |
C |
---|---|---|---|
1 |
Loại |
Nhân viên bán hàng |
Doanh số |
2 |
Me |
||
3 |
=?u* |
||
4 |
|||
5 |
|||
6 |
Loại |
Nhân viên bán hàng |
Doanh số |
7 |
Nước giải khát |
Suyama |
5122 $ |
8 |
Thịt |
Davolio |
450 $ |
9 |
nông sản |
Buchanan |
6328 $ |
10 |
Nông sản |
Davolio |
6544 $ |
Các tiêu chí là kết quả của công thức
Bạn có thể dùng giá trị được tính toán vốn là kết quả của công thức để làm tiêu chí. Hãy ghi nhớ các điểm quan trọng sau đây:
-
Công thức phải đánh giá là TRUE hoặc FALSE.
-
Vì bạn đang dùng công thức, hãy nhập công thức như thông thường và không gõ biểu thức theo cách sau đây:
=''= mục nhập ''
-
Không dùng nhãn cột để làm nhãn tiêu chí; hãy để trống nhãn tiêu chí hoặc dùng nhãn không phải là nhãn cột trong phạm vi (trong ví dụ dưới đây, đó là Giá trị Trung bình Tính toán và Kết quả khớp Chính xác).
Nếu bạn dùng nhãn cột trong công thức thay vì tham chiếu ô tương đối hay tên phạm vi, Excel sẽ hiển thị giá trị lỗi chẳng hạn như #NAME? hoặc #VALUE! trong ô chứa tiêu chí. Bạn có thể bỏ qua lỗi này vì nó không ảnh hưởng đến cách lọc phạm vi.
-
Công thức bạn dùng cho tiêu chí phải sử dụng tham chiếu tương đối để tham chiếu đến ô tương ứng trong hàng thứ nhất (trong ví dụ dưới đây, đó là ô C7 và A7).
-
Tất cả các tham chiếu khác trong công thức phải là tham chiếu tuyệt đối.
Các tiểu mục sau đây cung cấp ví dụ cụ thể về các tiêu chí được tạo ra từ kết quả của công thức.
Lọc các giá trị lớn hơn trung bình của tất cả các giá trị trong phạm vi dữ liệu
Trong phạm vi dữ liệu sau đây (A6:D10), phạm vi tiêu chí (D1:D2) hiển thị những hàng có giá trị trong cột Doanh số lớn hơn trung bình của tất cả các giá trị Doanh số (C7:C10). Trong công thức, "C7" tham chiếu tới cột được lọc (C) của hàng đầu tiên trong phạm vi dữ liệu (7).
|
A |
B |
C |
D |
---|---|---|---|---|
1 |
Loại |
Nhân viên bán hàng |
Doanh số |
Giá trị Trung bình Tính toán |
2 |
=C7>AVERAGE($C$7:$C$10) |
|||
3 |
||||
4 |
||||
5 |
||||
6 |
Loại |
Nhân viên bán hàng |
Doanh số |
|
7 |
Nước giải khát |
Suyama |
5122 $ |
|
8 |
Thịt |
Davolio |
450 $ |
|
9 |
nông sản |
Buchanan |
6328 $ |
|
10 |
Nông sản |
Davolio |
6544 $ |
Lọc văn bản bằng cách dùng tìm kiếm phân biệt chữ hoa, chữ thường
Trong phạm vi dữ liệu (A6:D10), phạm vi tiêu chí (D1:D2) hiển thị các hàng chứa "Nông sản" trong cột Loại bằng cách dùng hàm EXACT để thực hiện tìm kiếm phân biệt chữ hoa, chữ thường (A10:C10). Trong công thức, "A7" tham chiếu tới cột được lọc (A) của hàng đầu tiên trong phạm vi dữ liệu (7).
|
A |
B |
C |
D |
---|---|---|---|---|
1 |
Loại |
Nhân viên bán hàng |
Doanh số |
Kết quả khớp Chính xác |
2 |
=EXACT(A7, "Nông sản") |
|||
3 |
||||
4 |
||||
5 |
||||
6 |
Loại |
Nhân viên bán hàng |
Doanh số |
|
7 |
Nước giải khát |
Suyama |
5122 $ |
|
8 |
Thịt |
Davolio |
450 $ |
|
9 |
nông sản |
Buchanan |
6328 $ |
|
10 |
Nông sản |
Davolio |
6544 $ |