Cú pháp chung của nhóm hàm cơ sở dữ liệu

 Cú pháp chung của nhóm hàm cơ sở dữ liệu:

=Tên_hàm(database,field,criteria)

Trong đó:

• Database: địa chỉ vùng cơ sở dữ liệu.
• Field: cột cần tính trong vùng cơ sở dữ liệu, có thể sử dụng tên cột hoặc số thứ tự cột trong database.
• Criteria: địa chỉ vùng tiêu chuẩn.

Cho bảng kết quả học tập của sinh viên như sau:

Cơ sở dữ liệu minh họa cho các hàm cơ sở dữ liệu

Hàm DAVERAGE

• Cú pháp: DAVERAGE(database, field, criteria)
• Chức năng: Tính trung bình cộng các giá trị số trong cột (field) của cơ sở dữ liệu thỏa mãn tiêu chuẩn chỉ định.
• Ví dụ: Tính trung bình cộng của các sinh viên có kết quả >=5.

• Lập vùng tiêu chuẩn:

• Lập công thức: = DAVERAGE(A1:H17, H1, A19:A20) kết quả là 7.3.

Hàm DCOUNT

• Cú pháp: DCOUNT(database, field, criteria)
• Chức năng: Đếm các ô chứa giá trị số trong cột (field) của của cơ sở dữ liệu thỏa mãn tiêu chuẩn chỉ định.
• Ví dụ: Cho biết số sinh viên nữ có kết quả đạt (>=5).
• Lập vùng tiêu chuẩn:

• Lập công thức: =DCOUNT(A1:H17, H1, A23:B24) -> kết quả là 3.

Hàm DCOUNTA

• Ví dụ: =DCOUNTA(A1:H17, H1, A23:B24) -> kết quả là 0.
• Chức năng: Đếm các ô không rỗng trong cột (field) của cơ sở dữ liệu thỏa mãn tiêu chuẩn chỉ định.
• Cú pháp: DCOUNTA(database, field, criteria)

 Hàm DGET

• Cú pháp: DGET(database, field, criteria)
• Cú pháp: Trích ra một giá trị trong cột (field) của cơ sở dữ liệu thỏa mãn tiêu chuẩn chỉ định.
• Ví dụ: Cho biết tên sinh viên nữ có kết quả >=8
• Lập vùng tiêu chuẩn:

• Lập công thức: =DGET(A1:H17, C1, A28:B29) -> kết quả là “Thùy”.

Hàm DMAX

• Cú pháp: DMAX(database, field, criteria)
• Chức năng: Trả về giá trị số lớn nhất trong cột (field) của cơ sở dữ liệu thỏa mãn tiêu chuẩn chỉ định.
• Ví dụ: Cho biết điểm kết quả cao nhất của sinh viên “Nam” học ngành “Truyền thông và mạng máy tính”.
• Lập vùng tiêu chuẩn:

• Lập công thức: =DMAX(A1:H17,H1, A33:B34) -> kết quả là 7.9.

Hàm DMIN

• Cú pháp: DMIN(database, field, criteria)
• Chức năng: Trả về trị số nhỏ nhất trong cột (field) của cơ sở dữ liệu thỏa mãn tiêu chuẩn chỉ định.
• Ví dụ: Cho biết điểm kết quả thấp nhất của sinh viên nữ học ngành Kế toán – Kiểm toán
• Lập vùng tiêu chuẩn:

• Lập công thức: =DMIN(A1:H17, H1, A37:B38) kết quả là 3.8.

Hàm DPRODUCT

• Cú pháp: DPRODUCT(database, field, criteria)
• Chức năng: Trả về tích các giá trị số trong cột (field) của cơ sở dữ liệu thỏa mãn tiêu chuẩn chỉ định.
• Ví dụ: Cho vùng dữ liệu

• Yêu cầu: tính tích các giá trị <14000 và có STT <=4.
• Thực hiện:
  • Lập vùng tiêu chuẩn:

• Lập công thức: =DPRODUCT(A1:B6, B1, A9:B10) kết quả là 162,000,000.

Hàm DSUM

• Cú pháp: DSUM (database, field, criteria)
• Chức năng: Trả về tổng các giá trị số trong cột (field) của cơ sở dữ liệu thỏa mãn tiêu chuẩn chỉ định.
• Ví dụ: Cho bảng doanh số từng mặt hàng đã bán trong quý 1.

• Yêu cầu: Cho biết doanh số bán được trong tháng 2 của mặt hàng máy lạnh và máy giặt của hãng LG.
• Thực hiện:
  • Lập vùng tiêu chuẩn:

• Lập công thức: =DSUM(A3:F18, E3, A20:A22) à kết quả là 395.000,000.

Sử dụng SUBTOTAL

Khác với hàm SUM trong Excel, hàm Subtotal một trong các hàm cơ bản Excel được dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, đếm số ô, tính trung bình trong Excel, tìm giá trị lớn nhất/nhỏ nhất hay tính tổng giá trị danh sách đã lọc trong Excel mà Quản trị mạng đã hướng dẫn bạn đọc. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm SUBTOTAL trong Excel.

Công thức SUBTOTAL trong Excel

Hàm SUBTOTAL có cú pháp =SUBTOTAL(function_num, ref1, [ref2],…). Trong đó:

• Function_num: Các con số từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.
• Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.

Lưu ý:

• Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc.
• Nếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì sẽ được bỏ qua để tránh tính trùng 2 lần.
• Nếu function_num từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).
• Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL sẽ bỏ qua.

Cách sử dụng hàm SUBTOTAL trong Excel

1. Tính tổng các hàng được lọc 

Để hiểu rõ về kiểu tính tổng này bạn tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel. Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là:

=SUBTOTAL(9,pham_vi)

pham_vi ở đây là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.

2. Đếm các ô được lọc không trống

Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống nhìn thấy.

Chẳng hạn với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.

Chúng ta nhập công thức và khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần phải nhớ.

Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.

Còn với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.

3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau

Chẳng hạn chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.

Công thức tính trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.

Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.

Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.

Cách lỗi khi thực hiện hàm SUBTOTAL trong Excel

Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:

• #VALUE!: Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.
• #DIV/0!: Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số).
• #NAME? tên hàm SUBTOTAL sai chính tả.

Hàm DPRODUCT

Hàm DPRODUCT trong Excel.

Nếu hàm PRODUCT() chỉ giúp các bạn nhân các giá trị thì hàm DPRODUCT() giúp các bạn nhân các giá trị theo điều kiện được chỉ định.

Bài viết dưới đây mô tả cú pháp và cách sử dụng hàm DPRODUCT() trong Excel.

Mô tả

Hàm DPRODUCT() trả về kết quả là giá trị của phép nhân các giá trị trong một cột của một danh sách hay một cơ sở dữ liệu theo một điều kiện được chỉ định.

Cú pháp

=DPRODUCT(database,field,criteria)

Trong đó:

- database: là một dãy các ô tạo nên danh sách hay cơ sở dữ liệu bao gồm các dữ liệu cần xử lý bằng hàm DPRODUCT() bao gồm các tên trường (Tiêu đề cột).

- field: trường (cột) dữ liệu được sử dụng trong hàm. Các bạn có thể viết ở dạng text bằng tên cột đặt trong dấu ngoặc kép (ví dụ "Tuổi", "Lương"), field cũng có thể là số đại diện cho vị trí của cột (như 1 cho cột thứ nhất, 2 cho cột thứ hai…) hoặc các bạn có thể nhập trực tiếp tên ô chứa tiêu đề cột (A1, A5…).

- criteria: là phạm vi các ô chứa điều kiện, các bạn có thể dùng phạm vi bất kỳ cho criteria nhưng nó phải chứa ít nhất một Tiêu đề cột và một ô bên dưới chứa điều kiện.

Ghi chú

- Các bạn nên đặt phạm vi điều kiện criteria trên trang tính để khi thêm dữ liệu thì phạm vi chứa điều kiện không thay đổi.

- Phạm vi điều kiện cần tách rời không chèn lên danh sách hay cơ sở dữ liệu cần xử lý.

- criteria bắt buộc phải chứa ít nhất Tiêu đề cột và một ô chứa điều kiện dưới tiêu đề cột.

Ví dụ

Tính tích các số trong cột Giá trị với STT <=4.

Tạo criteria:

Áp dụng hàm DPRODUCT(): =DPRODUCT(C8:D14,D8,C5:C6)

C8:D14 là bảng cơ sở dữ liệu chứa cả tiêu đề cột.

D8 là tên Tiêu đề cột cần tính.

C5:C6 là phạm vi điều kiện criteria.

Ta sẽ được kết quả như sau:

Trên đây bài viết đã giới thiệu cú pháp và ví dụ cụ thể về cách sử dụng hàm DPRODUCT() trong Excel. Tùy theo yêu cầu của từng bài toán mà các bạn áp dụng hàm sao cho phù hợp. Chúc các bạn thành công!

Hàm DMAX

 

Hàm DMAX trong Excel: Hàm tìm giá trị lớn nhất

+ Cú pháp và chức năng của hàm DMAX trong Excel

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

Trong đó:

database: Danh sách cơ sở dữ liệu bao gồm cả cột Tiêu đề

field: Địa chỉ của trường cần tính tổng

criterial: Điều kiện

Chức năng:Tìm giá trị lớn nhất trường dữ liệu <Field> trong Danh sách cơ sở dữ liệu <Database> thỏa mãn điều kiện <Criterial>

+ Ví dụ về hàm DMAX trong Excel

Cho bảng Dữ liệu sau, yêu cầu: Tính lương tháng cao nhất của nhân viên phòng Tài vụ.

Cách dùng hàm Dmax trong excel

Để tính được kết quả trong ô D12, ta áp dụng đúng theo như cú pháp của hàm DMAX.

Ta có:  D12=DMAX($A$3:$D$10,C3,$B$14:$B$15)

Kết quả như sau:

Kết quả hàm Dmax

Hàm DMIN

 

Hàm DMIN trong Excel: Hàm tìm giá trị nhỏ nhất

+ Cú pháp và chức năng của hàm DMIN trong Excel

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

Trong đó:

database: Danh sách cơ sở dữ liệu bao gồm cả cột Tiêu đề

field: Địa chỉ của trường cần tính tổng

criterial: Điều kiện

Chức năng:Tìm giá trị nhỏ nhất trường dữ liệu <Field> trong Danh sách cơ sở dữ liệu <Database> thỏa mãn điều kiện <Criterial>

+ Ví dụ về hàm DMIN trong Excel

Sử dụng hàm Dmin trong Excel

Tương tự như ví dụ trên, nhưng để tìm Lương thấp nhất của Nhân viên phòng Kế hoạch, ta dùng Hàm DMIN, ta có :

D12= DMIN($A$3:$D$10,C3,$B$14:$B$15)

Kết quả:

Kết quả hàm Dmin trong Excel

DAverage

 

Cách sử dụng hàm DAVERAGE trong Excel

Hàm DAVERAGE có cú pháp sử dụng như sau:

DAVERAGE(database, field, criteral)

Trong đó:

• Database: phạm vi ô, danh sách hoặc cơ sở dữ liệu bao gồm cả tiêu đề, là tham số buộc.
• Field: chỉ rõ cột được dùng trong hàm, ô chưa tiêu đề cột hoặc tên tiêu đề cột. Bạn cần nhập tên tiêu đề cột đặt trong dấu ngoặc kép. Cũng có thể sử dụng một số để thể hiện vị trí cột, ví dụ cột đầu tiên là 1, cột thứ hai là 2.
• Criteral: phạm vi ô chứa điều kiện, các bạn có thể sử dụng bất kỳ phạm vi nào trong bảng. Nhưng phạm vi đó chứa ít nhất một tiêu đề cột và ít nhất một ô chứa điều kiện dưới tiêu đề cột đó.

Chúng ta có bảng ví dụ dưới đây:

Bạn sẽ tiến hành tính trung bình sản phẩm của các nhân viên với điều kiện tuổi 24.

Trường hợp 1: Sử dụng địa chỉ tương đối

Tại ô C8, bạn nhập công thức sau: =DAVERAGE(A3:C7,C3,C9:C10)

Sau đó nhấn Enter và được kết quả như hình dưới.

Trường hợp 2: Sử dụng địa chỉ tuyệt đối

Tại ô C8, chúng ta tiến hành nhập công thức: = DAVERAGE($A$3:$C$7,C3,$C$9:$C$10)

Nhấn Enter và được kết quả như hình dưới đây:

Trên đây là cách sử dụng hàm DAVERAGE trong Excel với hai trường hợp địa chỉ tương đối và địa chỉ tuyệt đối. Tùy theo trường hợp mà bạn sử dụng để nhập công thức dữ liệu cho phù hợp

DCountA

 

Hướng dẫn dùng hàm DCOUNT trên Excel

Hàm DCOUNT có cú pháp hàm là DCOUNT(database, field, criteria).

Trong đó:

• Database: Phạm vi ô hay vùng chứa dữ liệu cần đếm, bao gồm cả hàng tiêu đề trong vùng dữ liệu, là tham số bắt buộc.
• Field: Tên trường hoặc cột được dùng để kiểm tra điều kiện đếm. Nếu nhập trực tiếp tiêu đề cột thì đặt trong dấu ngoặc kép, nếu nhập số thì nhập số thứ tự tương ứng của cột đó tính từ bên trái.
• Criteria: Vùng dữ liệu chứa ô điều kiện. Có thể dùng bất kỳ phạm vi nào, nhưng phải có ít nhất 1 tiêu đề cột và 1 ô chứa điều kiện dưới ô tiêu đề cột.

Lưu ý:

• Không nên đặt phạm vi điều kiện bên dưới danh sách vì thông tin mới sẽ được thêm vào cuối danh sách.
• Đảm bảo phạm vi điều kiện không ghi đè lên danh sách.
• Để thao tác toàn bộ một cột trong danh sách nên nhập 1 dòng trống bên dưới tiêu đề cột trong phạm vi điều kiện.

Chúng ta sẽ tiến hành xử lý bảng dữ liệu dưới đây, đếm số học sinh thỏa mãn các điều kiện khác nhau.

1. Tính số học sinh có điểm Văn lớn hơn hoặc bằng 8

Theo bảng thì dữ liệu cần đếm thuộc kiểu dữ liệu số trong cột điểm Văn. Tại ô nhập kết quả chúng ta nhập công thức là =DCOUNT(A2:E7,D2,B9:B10) rồi nhấn Enter.

Trong đó:

• A2:E7: Vùng dữ liệu chứa dữ liệu cần đếm.
• D2: Tên cột dữ liệu để kiểm tra điều kiện.
• B9:B10: Vùng dữ liệu chứa điều kiện.

Cần phải nhập tên trường của vùng dữ liệu chứa điều kiện trùng với tên trường trong bảng dữ liệu.

Bước 2:

Sau khi nhấn Enter kết quả như hình dưới đây. Sẽ có 2 học sinh có điểm Văn lớn hơn hoặc bằng 8.

2. Tính số học sinh lớp A1 có điểm Toán lớn hơn hoặc bằng 7

Theo yêu cầu thì sẽ có 2 điều kiện cần tìm giá trị. Nhưng hàm DCOUNT chỉ sử dụng đếm kiểu dữ liệu nên chúng ta có thể tạo vùng điều kiện chứa Lớp và điểm Toán như hình. Vùng điều kiện sẽ chứa 2 điều kiện, nhưng tên cột chứa điều kiện sẽ để cột chứa dữ liệu số.

Nhập công thức là =DCOUNT(A2:E7);C2;B9:B10) và nhấn Enter.

Bước 2:

Kết quả sẽ chỉ có 1 học sinh lớp A1 có điểm Toán lớn hơn hoặc bằng 7.

3. Tính học sinh lớp A1 có điểm 3 môn đều lớn hoặc bằng 5

Bước 1:

Chúng ta sẽ thực hiện đếm dữ liệu số có nhiều điều kiện và có kèm điều kiện không phải dữ liệu số. Lập vùng điều kiện như hình. Sau đó bạn nhập công thức là =DCOUNT(A2:E7;C2;B9:E10) và nhấn Enter.

Bước 2:

Kết quả sẽ ra duy nhất 1 học sinh của lớp A1 có điểm 3 môn đều lớn 5.

4. Tính số học sinh lớp A1 đi thi

Ở đây sẽ có vùng điều kiện là lớp A1. Tại ô nhập kết quả nhập công thức là =DCOUNT(A2:E7;B2;B9:B10) rồi nhấn Enter như hình.

Kết quả sẽ ra 0 mặc dù có 2 học sinh lớp A1 đi thi. Điều này là do hàm DCOUNT chỉ đếm với dữ liệu số nên điều kiện lớp A1 thuộc kiểu ký tự nên kết quả hàm trả về là 0.

Trên đây là cách sử dụng hàm DCOUNT trên Excel, hàm đếm dữ liệu với điều kiện cho trước. Với những ví dụ tìm số liệu khác nhau và ví dụ về hàm DCOUNT trả về kết quả 0 bên trên, bạn sẽ biết được cách lựa chọn vùng điều kiện như nào cho chính xác.