Phần mềm

9 hàm thống kê thông dụng trong Excel bạn không nên bỏ qua

Hàm thống kê trong Excel khá đa dạng và phong phú. Nhắc đến chúng, bạn không thể không biết đến sự hiện diện của các hàm: COUNT COUNTIFS, RANK,… Mỗi hàm sở hữu cấu trúc, tính năng và cách dùng khác nhau. Tuy vậy, chúng lại có sự tương trợ giúp đỡ nhau để giải quyết công việc. 

Hàm Count – Đếm dữ liệu kiểu số

Hàm Count là một trong những hàm thống kê trong Excel khá thông dụng. Chúng được ứng dụng hầu hết trong công việc tài chính và nhân sự. 

Cu-phap-ham-COUNT
Cú pháp hàm COUNT

Cú pháp

Bạn cần ghi nhớ cú pháp của hàm như sau:

COUNT(giá trị1, [giá trị2], …)

Trong đó:

Giá trị 1, giá trị 2,… được xem là những tham chiếu ô hay dãy. Ở đó, bạn có nhu cầu đếm những ô này bằng con số.

Ở các phiên bản hệ điều hành Microsoft hiện nay, COUNT đã được cải thiện chức năng đáng kể. Điển hình là chúng có thể chấp nhân lên đến 255 đối số. Trong khi đó ở phiên bản cổ xưa, chúng chỉ đáp ứng 30 giá trị mà thôi.

Chẳng hạn: Công thức cho kết quả tổng số tại dải A1:A100:

=COUNT(A1:A100)

Bạn nên nhớ rằng ở hệ thống Excel nội bộ, ngày tháng được save thông qua dạng số sê-ri. Bởi vậy, hàm Excel COUNT sẽ đếm bao gồm ngày và thời gian.

Cách dùng hàm COUNT

Để vận dụng tốt hàm COUNT vào công việc, bạn cần ghi nhớ quy tắc dưới đây:

  • Trong trường hợp đối số của công thức ≥ 1 thuộc tham chiếu hay dải ô, chỉ số hay ngày tháng hoặc thời gian đã được ước tính. Những ô này sẽ ở trong trạng thái để trắng hoặc bao gồm bất kỳ dữ liệu nào nhưng khác dữ liệu số.
  • Trong trường hợp, các giá trị được nhập trực tiếp trong đối số Excel COUNT. Những giá trị sau sẽ cho kết quả: số, ngày tháng, thời gian hay giá trị Boolean thuộc TRUE và FALSE. Ngoài ra, chúng còn ở dạng biểu diễn văn bản của những con số. Điều này đồng nghĩa với 1 số thuộc dấu ngoặc kép như “5”.

Ví dụ: COUNT được tính công thức sẽ trả về kết quả 4. Bởi giá trị được tính:1, “2”, 1/1/2016 và TRUE.

=COUNT(1, “apples”, “2”, 1/1/2016, TRUE)

Cu-phap-ham-COUNT-01
Ví dụ minh họa về hàm COUNT

Hàm Counta – Đếm toàn bộ các ô có chứa dữ liệu

Không giống như hàm Count, hàm Counta có khả năng đếm toàn bộ những ô bao gồm dữ liệu. Trong khi đó, hàm Count chỉ có thể đếm ô dữ liệu thuộc kiểu số mà thôi.

Cú pháp

Hàm Counta có cú pháp như sau:

COUNTA (value1, [value2], …)

Trong đó value1, value2,… chính là tham số hay dải ô tại vị trí bạn cần tính những ô không thuộc ô trống.

Chẳng hạn bạn cần đếm ô có giá trị thuộc dãy A1: A100, công thức ứng dụng là: 

=COUNTA(A1:A100)

Công thức để đếm những ô không rỗng thuộc 1 số phạm vi không liền nhau: 

=COUNTA(B2:B10, D2:D20, E2:F10)

Bạn có thể nhận thấy phạm vi được đề xuất để sử dụng công thức Counta không bắt buộc phải thuộc cùng kích thước. Điều này có nghĩa 1 dải ô sẽ bao gồm 1 số hàng và cột không giống nhau.

Cu-phap-ham-COUNT-02
Sự khác biệt giữa hàm COUNT và hàm COUNTA

Cách dùng

Count thuộc hàm thống kê trong Excel có cách sử dụng khá đơn giản. Trong quá trình ứng dụng hàm, bạn cần biết hàm được sử dụng để đếm những ô chứa bất cứ loại dữ liệu nào. Chúng gồm có:

– Số

– Ngày tháng / thời gian

– Giá trị của văn bản

– Những giá trị Boolean thuộc TRUE và FALSE

– Những giá trị lỗi như #VALUE hay # N/A

– Chuỗi văn bản dạng trống (“”)

Đôi khi bạn sẽ bị rơi vào tình trạng khó hình dung bởi kết quả mà hàm Counta mang lại. Điểm mấu chốt nằm ở hàm có khả năng đếm cả những ô mà mắt thường chúng ta nhìn là rỗng. Tuy nhiên xét về mặt kỹ thuật thì chúng lại không phải rỗng. 

Hàm Countif – Hàm đếm dữ liệu với 1 điều kiện

Hàm có chức năng đếm tổng số ô đáp ứng 1 điều kiện khi truyền vào. Chúng không có sự nhận diện chữ Hoa và chữ thường.

Cu-phap-ham-COUNT-03
Cú pháp hàm thống kê trong Excel – hàm COUNTIF

Cú pháp

Cú pháp của hàm khá đơn giản, không quá phức tạp:

= COUNTIF(range, criteria)

Trong đó:

  • Range: 1 mảng hoặc 1 dải của những ô có nhu cầu đếm. Giá trị của ô ở dạng text, tên, mảng hoặc tham chiếu. Ô rỗng sẽ không được tính khi đếm.
  • Criteria: Điều kiện cần đếm (số, chuỗi, biểu thức, …). Ban có thể sử dụng ký tự đại diện như: “?” tượng trưng cho 1 ký tự. Hoặc “*” tượng trưng cho nhiều ký tự

Điều này được thể hiện rõ qua hình dưới:

Hàm Countifs – hàm thống kê trong Excel

Hàm Countifs được ứng dụng đa dạng trong nhiều tình huống công việc. Bởi vậy bất kỳ ai đang làm quen với Excel đều không thể bỏ qua hàm đặc biệt này.

Cú pháp

Công thức hàm COUNTIFS như sau:

=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)

Trong đó:

  • criteria_range1 – xác định phạm vi tiên phong ứng dụng điều kiện thứ nhất (criteria1), bắt buộc.
  • criteria1 – Đặt điều kiện với dạng số, ô tham chiếu, hoặc chuỗi văn bản, 1 mảng hay 1 hàm Excel khác, bắt buộc. Tiêu chuẩn này nhằm vào các ô tính sẽ được đếm và có thể hiển thị là 10, “<=32”, A6, “sweets”.
  • [criteria_range2, criteria2]… – Đại diện cho những điều kiện thêm vào, tùy chọn. Cặp điều kiện thuộc công thức có thể nhập lên đến 127 cặp.
Cu-phap-ham-COUNT-04
Cú pháp hàm COUNTIFS

Bạn cần nắm chắc cú pháp của hàm Countifs. Như vậy bạn mới có thể vận dụng chúng linh hoạt trong công việc. Microsoft Excel sẽ hiện lên những đối số trong hàm ngay thời điểm bạn bắt đầu gõ. Đối số được nhập vào sẽ được tô đậm ngay lập tức.

Một số lưu ý

  • Hàm có thể được dùng để đếm những ô thuộc 1 phạm vi đơn lẻ ứng với 1 điều kiện duy nhất. Tương tự đối với nhiều vùng dữ liệu ứng với nhiều điều kiện. Ở trường hợp khác, chỉ các ô tính có thể thỏa mãn toàn bộ điều kiện thì sẽ đếm được.
  • Một vùng dữ liệu bổ sung phải sở hữu số hàng và số cột tương đồng với vùng sẽ liệu khởi điểm (đối số criteria_range1).
  • Các vùng dữ liệu liền kề và cách nhau cũng có thể đếm được.
  • Trường hợp tiêu chí là 1 tham chiếu đến 1 ô trống, hàm giải quyết chúng như 1 giá trị bằng không (0).
  • Bạn có thể ứng dụng những wildcard characters thuộc những tiêu chí – dấu hoa thị (*) hay dấu chấm hỏi (?).
Cu-phap-ham-COUNT-05
Cách dùng hàm COUNTIFS với ký tự đặc biệt

Cách dùng hàm COUNTIFS với những ký tự đại diện

Bạn cần biết, hàm Countifs có thể ứng dụng với một số ký tự đại diện như:

  • Dấu hỏi (?) – trùng hợp với bất cứ ký tự đơn nào. Chúng có khả năng đếm những ô bắt đầu và kết thúc thông qua 1 ký tự nhất định.
  • Dấu hoa thị (*) – khớp với bất cứ dãy ký tự nào. Bạn sẽ sử dụng chúng để đếm ô chứa 1 từ cụ thể hay 1 ký tự trong ô.

Lưu ý: Trong trường hợp bạn cần đếm những ô chứa dấu “?” hoặc “*”, bạn hãy gõ dấu (~) trước dấu “*” hoặc dấu “?”.

Hàm Countblank 

Hàm Countblank có chức năng đếm số ô trống (rỗng) thuộc vùng chọn được xác định của bảng dữ liệu.

Cú pháp

=COUNTBLANK(RANGE)

Trong đó: Range  được hiểu là phạm vi bắt buộc mà bạn có nhu cầu đếm những ô trống.

Ví dụ

Để hiểu rõ cách thức dùng hàm Countblank, bạn nên tham khảo hai ví dụ dưới đây:

Ví dụ 1: Dùng hàm để tìm số lượng học sinh chưa nhận được điểm thuộc bảng dữ liệu

Cu-phap-ham-COUNT-06
Tìm số học sinh chưa đạt đủ điểm

Công thức áp dụng =COUNTBLANK(D5:D9). Lúc này ta sẽ thu về kết quả là 2 bạn.

Cu-phap-ham-COUNT-07
Kết quả trả về 2

Ví dụ 2: Đếm số sinh viên không đáp ứng chỉ tiêu môn học này.

Sử dụng công thức =COUNTBLANK(D5:D14), ta có kết quả như hình dưới.

Cu-phap-ham-COUNT-08
Kết quả học sinh không đáp ứng đủ chỉ tiêu môn học

Hàm Rank

Một trong số những hàm thống kê trong Excel bạn không nên bỏ lỡ là hàm Rank. Hàm này có tính năng trả về thứ hạng của 1 số thuộc danh sách các số. Thứ hạng của số đó là kích thước của chungs thuộc tương quan với những giá trị khác nằm ở danh sách. 

Cú pháp

RANK(number,ref,[order])

Trong đó:

  • Number: Yếu tố bắt buộc. Đây chính là số mà bạn cần tìm thứ hạng của chúng.
  • Ref yếu tố bắt buộc. Đây là 1 mảng hoặc tham chiếu tới 1 danh sách số. Những giá trị không thuộc số nằm ở tham chiếu sẽ bị loại bỏ.
  • Order có thể tùy chọn. Đây là 1 con số nêu rõ cách xếp hạng số.

Trong trường hợp thứ tự = 0 hoặc được loại bỏ thì Microsoft Excel xếp hạng số tương tự như lúc tham chiếu là 1 danh sách xếp theo thứ tự giảm dần.

Còn với thứ tự thuộc bất cứ giá trị nào ≠ 0. Microsoft Excel xếp hạng số giống như lúc tham chiếu là 1 danh sách xếp theo thứ tự tăng dần.

Cu-phap-ham-COUNT-09
Cách dùng hàm RANK

Chú thích

Hàm RANK cho kết quả là những số trùng lặp cùng 1 thứ hạng. Mặc dù vậy, sự góp mặt của số trùng lặp sẽ tác động đến thứ hạng của số theo sau. Chẳng hạn như trong 1 danh sách số nguyên được xếp thứ tự tăng dần. Nếu số 10 hiện lên 2 lần và có thứ hạng là 5 thì số 11 sẽ sở hữu thứ hạng là 7. Như vậy không có thứ hạng 6 hiện diện.

Vì một nhu cầu nào đó, bạn cần xác định thứ hạng có xét đến sự ngang hàng. Ở ví dụ trên, bạn có thể cần lấy thứ hạng đã điều chỉnh dành cho số 10 là 5,5. Bạn sẽ tiến hành thông qua cách đưa hệ số điều chỉnh về giá trị mà hàm RANK đã trả về. Hệ số điều chỉnh sẽ phù hợp cho cả trường hợp thứ hạng tính toán theo phương án thứ tự giảm dần. Khi đó, thứ tự bằng 0 hoặc bị loại bỏ. Hoặc thứ tự tăng dần sẽ có giá trị ≠ 0.

Hệ số điều chỉnh của thứ hạng ngang hàng= [COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1)]/2.

Hàm Average – Hàm tính giá trị trung bình

Hàm Average hiểu đơn giản là hàm trung bình. Chúng sẽ cho phép chúng ta tính được kết quả trung bình của một dãy số. 

Cú pháp

AVERAGE(number1, [number2], …)

Trong đó:

  • Number1: Bắt buộc. Số này là số đầu tiên, tham chiếu ô hay phạm vi mà bạn cần tính trung bình.
  • Number2, … Tùy chọn. Chúng thuộc những số, tham chiếu ô hay phạm vi bổ sung mà bạn cần tính trung bình. Số lượng không được vượt quá 255
Cu-phap-ham-COUNT-10
Cú pháp hàm AVERAGE

Chú thích

  • Đối số có thể là dạng số hoặc tên, phạm vi hay tham chiếu ô có bao gồm số.
  • Những giá trị logic và biểu thị văn bản nằm trong số mà bạn nhập trực tiếp danh sách vào những đối số không được đếm.
  • Trường hợp 1 đối số tham chiếu ô hay phạm vi gồm giá trị logic, văn bản hay ô trống. Khi đó các giá trị này sẽ bị loại bỏ. Mặc dù vậy, cá ô giá trị 0 vẫn được tính.
  • Những đối số thuộc văn bản hoặc giá trị lỗi không thể biến thành thành số sẽ tạo ra các lỗi.
  • Trường hợp bạn cần thâu tóm các giá trị logic và dạng biểu thị số dưới dạng văn bản trong 1 tham chiếu tương tự như 1 phần phép tính. Bạn hãy sử dụng hàm AVERAGEA.
  • Trường hợp bạn cần tính trung bình của các giá trị thỏa mãn 1 số điều kiện nhất định. Bạn sẽ sử dụng hàm AVERAGEIF hay hàm AVERAGEIFS.

Hàm Column, Columns

Trong quá trình dùng hàm Column và Columns nhiều người dễ bị nhầm lẫn. Bởi thực tế chúng khá giống nhau về cú pháp. 

Chức năng

  • Hàm COLUMNS: Cho kết quả số cột thuộc 1 vùng dữ liệu cho trước.
  • Hàm COLUMN: Cho kết quả số thứ tự cột của một ô của Excel tính từ cột đầu tiên là cột A trong bảng tính Excel.

Cú pháp 

Để không bị nhầm lẫn trong cách sử dụng, bạn cần ghi nhớ cú pháp của hai hàm: 

COLUMNS: =COLUMNS(array) trong đó array là vùng dữ  liệu cho trước.

COLUMN: =COLUMN[reference] trong đó reference là ô tham chiếu.

Ví dụ minh họa

Bảng dữ liệu được cho như sau:

Cu-phap-ham-COUNT-11
Bảng dữ liệu cho sẵn

Hàm COLUMNS, sử dụng công thức: =COLUMNS(B4:D36), được kết quả như hình dưới.

Cu-phap-ham-COUNT-12
Kết quả trả về C12

Hàm COLUMN, ô tham chiếu là ô C8, sử dụng công thức: =COLUMN(C8). Kết quả được như hình.

 

Kết quả trả về
Kết quả trả về

Hàm tìm giá trị lớn nhất, nhỏ nhất theo điều kiện

Nếu bạn đang tìm hiểu hàm thống kê trong Excel thì không thể không biết đến hàm tìm giá trị max và min có điều kiện. Các hàm được coi là bí quyết để bạn tìm chính xác giá trị max và min khi có điều kiện đi kèm.

Hàm tìm giá trị lớn nhất MAXIFS

Hàm cho phép bạn có thể tìm ra giá trị max có điều kiện.

Cú pháp

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Trong đó: 

– max_range (đối số bắt buộc): phạm vi thực tiễn là căn cứ để tìm giá trị max.

– criteria_range1 (đối số bắt buộc): tập hợp những ô được đánh giá dựa trên nhiều tiêu chí.

– criteria1 (đối số bắt buộc): Tiêu chí ở dạng số, biểu thức hay văn bản xác định ô nào sẽ được đánh giá là max.

– criteria_range2, criteria2,…: Phạm vi thêm vào và những chỉ tiêu được liên kết của chúng. Số cặp tiêu chí tối đa là 126 cặp.

Ví dụ minh họa

Bảng dữ liệu thể hiện doanh số của 1 cửa hàng. Việc tôi cần làm là tìm ra số cửa hàng bán áo thun nữ đạt doanh số max thuộc tháng 1.

Cu-phap-ham-COUNT-14
Bảng doanh thu tháng 1

Bạn cần thực hiện các bước sau:

Bước 1

Bạn gõ công thức vào Excel

 =MAXIFS(D2:D16,A2:A16,F3,B2:B16,G3)

Trong đó: 

  • D2:D16: cột Doanh thu (max_range)
  • A2:A16: cột Tháng (criteria_range1)
  • F3: Tiêu chuẩn Tháng 1 (criteria1)
  • B2:B16: Cột Sản phẩm (criteria_range2)
  • G3: Tiêu chí dành cho Áo thun nữ (criteria2)

Bước 2

Bạn cần cố định những giá trị: D2:D16, A2:A16, B2:B16 thông qua việc nhấn F4 ở bàn phím.

Bước 3

Bạn gõ Enter để trả về kết quả max doanh thu trong tháng 1.

Cu-phap-ham-COUNT-15
Cửa hàng doanh thu max

Hàm tìm giá trị nhỏ nhất – hàm MINIFS 

Hàm này cách sử dụng khá tương tự như hàm MAXIFS.

Cú pháp

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Trong đó: 

– min_range (đối số bắt buộc): phạm vi thực tế nhằm tìm ra giá trị min. 

– criteria_range1 (đối số bắt buộc): tập hợp toàn bộ ô được đánh giá theo nhiều tiêu chí.

– criteria1 (đối số bắt buộc): Tiêu chí thuộc dạng số, biểu thức hay văn bản xác định ô nào sẽ được đánh giá là min.

– criteria_range2, criteria2,…: Phạm vi thêm vào và những tiêu chí được gắn kết chúng. Cặp tiêu chí không vượt quá 126 cặp.

Ví dụ

Cũng với bảng dữ liệu trên. Bạn cần tìm ra cửa hàng có doanh thu thấp nhất trong tháng 1. Khi đó công thức bạn áp dụng là:

=MINIFS(D2:D16,A2:A16,F9,B2:B16,G9)

Cu-phap-ham-COUNT-16
Cửa hàng doanh thu min

Bước tiếp theo là bạn cố định toàn bộ phạm vi lại. Cuối cùng là nhấn Enter để hiển thị kết quả min.

Có lẽ bài viết là sự tổng hợp đầy đủ nhất mọi hàm thống kê trong Excel. Qua đây bạn đã nắm rõ trong lòng bàn tay toàn bộ công thức và cách sử dụng của hàm thống kê. Hy vọng bạn sẽ ứng dụng chúng thành công và linh hoạt trong công việc. 

>>> Xem thêm: Khám phá 3 hàm nối chuỗi trong Excel đơn giản và hiệu quả nhất – digitalfuture

>>> Xem thêm: Kiểm tra độ sai lệch của dữ liệu bằng các hàm so sánh trong Excel – digitalfuture

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Back to top button