Hàm COUNT() và COUNTA()

Hàm COUNT() và COUNTA()

Đếm số ô có chứa số lẫn các số bên trong danh sách các đối số.
Thường dùng hàm COUNT() để lấy số mục trong một dãy hoặc trong một mảng các số.

Cú pháp: = COUNT(value1, value2, ...)

value1, value2, ... : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30), các đối số có thể chứa hoặc tham chiếu tới nhiều loại dữ liệu khác nhau, nhưng chỉ những đối số có chứa số mới được đếm

Lưu ý

• Những đối số là số, ngày tháng, hay các chữ thể hiện số sẽ được đếm; còn những đối số là giá trị lỗi hay các chữ không thể dịch thành số sẽ bị bỏ qua

• Nếu đối số là mảng hoặc tham chiếu, thì chỉ các số trong mảng hoặc tham chiếu đó mới được đếm; còn các ô rỗng, các giá trị logic, text, hay giá trị lỗi trong mảng hay tham chiếu đó sẽ bị bỏ qua

• Nếu cần đếm các giá trị logic, text, hay các giá trị lỗi, bạn sử dụng hàmCOUNTA(), với cùng cú pháp. COUNTA() thường được dùng để đếm các ô "không rỗng" bên trong một danh sách.

Ví dụ:

:

Hàm COUNTIF()

Đếm số lượng các ô trong một vùng thỏa một điều kiện cho trước. Hàm COUNTBLANK()

Đếm số ô rỗng trong một vùng.

Cú pháp: = COUNTBLANK(range)

Range : Dãy các ô để đếm số ô rỗng có trong đó.

Lưu ý:

• Các ô có chứa công thức trả về là một chuỗi rỗng cũng sẽ được đếm như các ô rỗng bình thường. Nhưng các ô có chứa giá trị = 0 sẽ không được đếm.

Ví dụ:

Có bảng tính như sau:

COUNTBLANK(A2:C5) = 10

Hàm MAX()

Trả về giá trị lớn nhất (maximum) của một tập giá trị.

Cú pháp: = MAX(number1, number2, ...)

number1, number2, ... : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30)

Lưu ý:

• Các đối số có thể là số, ô rỗng, giá trị logic, hoặc các chữ thể hiện số... Nhưng không bao gồm các đối số bị lỗi hoặc chữ không thể chuyển thành số, những đối số này sẽ gây ra lỗi.

• Nếu đối số là mảng hay tham chiếu, thì chỉ các giá trị số trong mảng hay tham chiếu đó mới được sử dụng. Những ô rỗng, giá trị logic, hay text, v.v... sẽ được bỏ qua, nếu muốn sử dụng cả những giá trị này, bạn có thể dùng hàm MAXA() với cú pháp tương đương.

• Nếu không có đối số nào chứa số, MAX() sẽ trả về kết quả là zero (0).

Ví dụ:

Hàm MIN() và MINA()

Trả về giá trị nhỏ nhất (minimum) của một tập giá trị.

Cú pháp: = MIN(number1, number2, ...)

number1, number2, ... : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30)

Lưu ý:

• Các đối số có thể là số, ô rỗng, giá trị logic, hoặc các chữ thể hiện số... Nhưng không bao gồm các đối số bị lỗi hoặc chữ không thể chuyển thành số, những đối số này sẽ gây ra lỗi.

• Nếu đối số là mảng hay tham chiếu, thì chỉ các giá trị số trong mảng hay tham chiếu đó mới được sử dụng. Những ô rỗng, giá trị logic, hay text, v.v... sẽ được bỏ qua, nếu muốn sử dụng cả những giá trị này, bạn có thể dùng hàm MINA() với cú pháp tương đương.

• Nếu không có đối số nào chứa số, MIN() sẽ trả về kết quả là zero (0).

Ví dụ:

Hàm RANK()

Trả về thứ hạng của một trị trong một tập dữ liệu, là độ lớn của trị này so với các trị khác trong danh sách. Với một danh sách đã sắp xếp, thứ hạng của một số chính là vị trí của số đó trong danh sách. Hàm này thường được dùng để xếp vị thứ cho học sinh dựa vào bảng điểm trung bình.

Đừng nhầm lẫn hàm này với hàm PERCENTRANK(). Hàm PERCENTRANK()() cũng trả về hạng của một trị trong một tập dữ liệu, nhưng đây là số phần trăm của tập dữ liệu đó, hay nói cách khác là xếp hạng một trị trong một tập dữ liệu theo phần trăm của nó trong tập dữ liệu.

Cú pháp: = RANK(number, ref, order)

Number : Là số muốn tìm thứ hạng của nó.

Ref : Là mảng, là tham chiếu hay là danh sách các số. Các giá trị không phải là số trongref sẽ được bỏ qua.

Order : Là một trị logic (0 hay 1) cho biết cách thức sắp xếp các số hạng trong ref.

- Nếu order là 0 hoặc bỏ qua, Excel sẽ tính thứ hạng các số như thể danh sách đã được sắp xếp theo thứ tự giảm dần (từ lớn tới nhỏ)

- Nếu order là 1, Excel sẽ tính thứ hạng các số như thể danh sách đã được sắp xếp theo thứ tự tăng dần (từ nhỏ tới lớn)

Lưu ý:

• RANK() sắp xếp các số giống nhau với cùng một thứ hạng. Tuy nhiên, sự có mặt của những số giống nhau sẽ làm ảnh hưởng đến hạng của các số theo sau. Ví dụ, trong danh sách các số nguyên, nếu số 10 có hai lần và được xếp hạng 7, thì số 11 sẽ xếp hạng 9 (không có hạng 8).

• Muốn RANK() sắp xếp các số giống nhau với nhưng không cùng một thứ hạng (thứ hạng sẽ chạy liên tục không mất số nào), dùng cú pháp sau đây (kết hợp hàm RANK với hàm COUNT và COUNTIF):

  - Với danh sách xếp từ lớn đến nhỏ:
  

  = RANK(number, ref) + COUNTIF(ref, number) - 1

  -Với danh sách xếp từ nhỏ đến lớn:
  

  = COUNT(ref) - (RANK(number, ref) + COUNTIF(ref, number)) + 2

Ví dụ 1: So sánh một số cách dùng hàm RANK

Hình này đã được thay đổi kích thước. Nhấp vào đây để xem hình đầy đủ. Kích thước hình gốc là 891x354.

Công thức ở B3:B12 = RANK(A3, $A$3:$A$12)

Công thức ở C3:C12 = RANK(A3, $A$3:$A$125) + COUNTIF(A3:A$12, A3) - 1

Công thức ở D3:D12 = RANK(A3, $A$3:$A$12, 1)

Công thức ở E3:E12 = COUNT($A$3:$A$125) - (RANK(A3, $A$3:$A$12) + COUNTIF(A3:A$12, A3)) + 2

Ví dụ 2: Sắp xếp lại một danh sách theo thứ tự từ thấp đến cao

Công thức ở C3:C12 = COUNT($B$3:$B$125) - (RANK(B3, $B$3:$B$12) + COUNTIF(B3:B$12, B3)) + 2

Công thức ở E3:E12 = OFFSET(A$3, MATCH(SMALL(C$3:C$12, ROW() - ROW(E$3)+1), C$3:C$12, 0) - 1, 0)

Ví dụ 3: Sắp xếp lại một danh sách theo thứ tự từ cao đến thấp

Công thức ở C3:C12 = RANK(B3, $B$3:$B$125) + COUNTIF(B3:B$12, B3) - 1

Công thức ở E3:E12 = OFFSET(A$3, MATCH(SMALL(C$3:C$12, ROW() - ROW(E$3) + 1), C$3:C$12, 0) - 1, 0)

Ví dụ 4: Sắp xếp lại một danh sách theo hai bảng dữ liệu

Hình này đã được thay đổi kích thước. Nhấp vào đây để xem hình đầy đủ. Kích thước hình gốc là 873x308.

Công thức ở I5:I14 = VLOOKUP(H5, $B$4:$C$14, 2, 0) + (VLOOKUP(H5, $E$5:$F$14, 2, 0) / 1000) + (ROW() / 1000000)

Công thức ở J5:J14 = RANK(I5, $I$5:$I$14) + COUNTIF($I$5:I5, I5) - 1

Công thức ở L5:L14 = OFFSET(B$5, MATCH(SMALL(J$5:J$14, ROW() - ROW(L$5) + 1), J$5:J$14, 0) - 1, 0)

Cú pháp: = COUNTIF(range, criteria)

Range : Dãy các ô để đếm, có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số. Ô rỗng sẽ được bỏ qua.

Criteria : Điều kiện để đếm. Có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, "32", "> 32", hoặc "apple", v.v...

Lưu ý:

• Có thể dùng các ký tự đại diện trong điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *)

• Khi điều kiện để đếm là những ký tự, COUNTIF() không phân biệt chữ thường hay chữ hoa.

Ví dụ 1: Có bảng tính như sau

Đếm số ô có chữ "Táo" trong dãy A2:A5

COUNTIF(A2:A5, "Táo") = 2

Đếm số ô có chữ "Lê" trong dãy A2:A5

COUNTIF(A2:A5, A4) = 1

Đếm số ô có chữ "Táo" vào số ô có chứa chữ "Lê" trong dãy A2:A5)

COUNTIF(A2:A5, A3) + COUNTIF(A2:A5, A2) = 3

Đếm số ô có giá trị > 55 trong dãy B2:B5

COUNTIF(B2:B5, "> 55") = 2

Đếm số ô có giá trị khác 75 trong dãy B2:B5

COUNTIF(B2:B5, "<>" & B4) = 3

Đếm số ô có giá trị lớn hơn hoặc bằng 32, nhưng không lớn hơn 85, trong dãy B2:B5

COUNTIF(B2:B5, ">=32") - COUNTIF(B2:B5, ">85") = 3

Ví dụ 2: Có bảng tính như sau

Đếm số ô có 2 chữ cuối là "es" trong dãy A2:A7

COUNTIF(A2:A7, "*es") = 2

Đếm số ô có 2 chữ cuối là "es" trong dãy A2:B7

COUNTIF(A2:B7, "*es") = 6

Đếm số ô có 2 chữ cuối là "es" trong dãy A2:A7 mà có chính xác 7 chữ ký tự

COUNTIF(A2:A7, "?????es") = 2

Đếm số ô có chữ (text) trong dãy A2:A7

COUNTIF(A2:A7, "*") = 4

Đếm số ô không chứa chữ (text) trong dãy A2:A7

COUNTIF(A2:A7, "<>"&"*") = 2

Tính tỷ lệ số ô chứa chữ "No" trong dãy ô B2:B7, bao gồm cả những ô rỗng

COUNTIF(B2:B7, "No") / ROWS(B2:B7) = 33%

Tính tỷ lệ số ô chứa chữ "Yes" trong dãy ô B2:B7, không tính những ô rỗng

COUNTIF(B2:B7, "Yes") / (ROWS(B2:B7) - COUNTIF(B2:B7, "<>" & "*")) = 0.5 = 50%