TỔNG HỢP các hàm thống kê trong excel RẤT CẦN THIẾT

TỔNG HỢP các hàm thông dụng trong excel NÊN BIẾT

Hàm thống kê là một trong số các loại hàm được sử dụng nhiều nhất trong Excel. Khi chúng ta phân tích dữ liệu hay làm các loại báo cáo, bài tổng hợp… đều cần sử dụng loại hàm này. Có thể nói, nó là loại hàm cực kỳ hữu dụng đối với những người làm công việc hành chính văn phòng, bàn giấy.

Hàm thống kê không phải chỉ có một loại mà nó bao gồm rất nhiều loại hàm khác nhau. Cùng chúng tôi tìm hiểu về các loại hàm thống kê cơ bản trong Excel nhé!

1. Nhóm hàm tính tổng (Sum, Sumif, Sumifs)

1.1 Hàm SUM: Tính tổng

  • Công dụng: Hàm SUM cộng các giá trị. Bạn có thể cộng các giá trị riêng lẻ, tham chiếu hoặc phạm vi ô hay kết hợp cả ba.
  • Cú pháp: =SUM(Number1, Number2..)

Ví dụ:

  • =SUM(20,40,60) nghĩa là cộng các số hạng lại với nhau và cho ra kết quả 120.
  • =SUM(A1:A3) cho ra kết quả làm 120 (Tính tổng các dãy số từ A1 đến A3)

Ham sum trong excel

1.2 Hàm SUMIF: Hàm tính tổng các ô có điều kiện

  • Công dụng: Bạn sử dụng hàm SUMIF để tính tổng các giá trị trong một phạm vi đáp ứng tiêu chí mà bạn xác định.
  • Cú pháp: =SUMIF(Range, Criteria, Sum_range). Trong đó:
    • Range: Là hàng hoặc cột mà bạn đã chọn
    • Criteria: Đặt điều kiện, điều kiện này bạn có thể đặt là số, là biểu thức hoặc là chuỗi đều được.
    • Sum_range: Là các ô mà bạn thực sự cần tính tổng.

Ví dụ: Tính tổng số màu cam ở cột A có số lượng xuất hiện trong cột B, có công thức =SUMIF(A1:A8,”cam”,B1:B8)

ham sumif trong excel

1.3 Hàm SUMIFS: Hàm tính tổng các ô trong vùng, phạm vi lựa chọn thỏa mãn một hay nhiều điều kiện.

  • Cú pháp: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
  • Trong đó:
    • Sum_range: là các ô cần tính tổng trong bảng dữ liệu, những giá trị trống và giá trị text được bỏ qua, tham số bắt buộc phải có.
    • Criteria_range1: phạm vi cần được kiểm tra bằng điều kiện criteria1, là giá trị bắt buộc.
    • Criteria1: điều kiện áp dụng cho criteria_range1, có thể là một số, biểu thức, tham chiếu ô để xác định ô nào trong criteria_range1 sẽ được tính tổng, cũng là giá trị bắt buộc.
    • Criteria_range2,criteria2,…: tùy chọn các phạm vi và điều kiện bổ sung, tối đa 127 cặp criteria_range, criteria.

Ví dụ: Tính tổng sản phẩm mà nhân viên Hoài bán ra có đơn giá mặt hàng dưới 400.000đ.

Huong dan su dung ham sumifs trong excel

Tại ô nhập kết quả chúng ta điền công thức =SUMIFS(D2:D7,C2:C7,”Hoài”,E2:E7,”<400000″) rồi nhấn Enter.

Trong đó:

  • D2:D7 là vùng cần tính tổng mặt hàng.
  • C2:C7 là vùng giá trị điều kiện tên nhân viên.
  • “Hoài” là điều kiện tên nhân viên cần tính số mặt hàng chứa trong vùng C2:C7.
  • E2:E7 là điều kiện mặt hàng.
  • “400.000” là điều kiện chứa mặt hàng trong vùng E2:E7.

1.4 Hàm SUMPRODUCT: Tính tổng các mảng có điều kiện

  • Cú pháp: =SUMPRODUCT(array1, [array2], [array3], …)
  • Trong đó:
    • Array1: đối số mảng đầu tiên mà người dùng muốn nhân các số liệu rồi tính tổng. Đối số này bắt buộc phải có.
    • Array2, Array3: là đối số mảng có thể tùy chọn có hoặc không, trong phạm vi từ 2 đến 255.

Ví dụ: Giả sử bạn có số lượng ở các ô A2:A4, và bạn muốn tính tổng. Nếu bạn đang giải một phép toán ở trường, thì bạn sẽ nhân số lượng với giá tiền của mỗi sản phẩm, rồi cộng chúng lại. Trong Microsoft Excel, bạn có thể nhận được kết quả chỉ với một công thức SUMPRODUCT: =SUMPRODUCT(A2:A4,B2:B4)

Hàm SUMPRODUCT: Tính tổng các mảng có điều kiện

2. Hàm AVERAGE: Tính giá trị trung bình

  • Công dụng: Hàm AVERAGE dùng để tính trung bình cộng của một dãy số trong trang tính.
  • Cú pháp: =AVERAGE(number1,number2,…) Trong đó,
    • number1 (Bắt buộc): Số thứ nhất, tham chiếu ô, hoặc phạm vi mà bạn muốn tính trung bình.
    • number2,… (Tùy chọn): Các số, tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tính trung bình, tối đa là 255.

Ví dụ: Tính lương trung bình theo thông tin bên dưới, ta có công thức =AVERAGE(C3:C8) và nhận được kết quả là giá trị trung bình của 6 tháng lương.

Hàm AVERAGE trong excel

3. Nhóm hàm đếm dữ liệu (COUNT, COUNTA, COUNTIF, COUNTIFS)

  • Công dụng hàm Count/Counta: Thay vì đếm thủ công các ô chứa số trong một bảng dữ liệu khổng lồ, bạn có thể sử dụng hàm COUNT. Ví dụ, bạn gõ =COUNT(A1:A20) sẽ cho kết quả có bao nhiêu ô chứa số từ ô A1 đến ô A20 trong bảng tính. Tương tự, hàm COUNTA cũng dùng hỗ trợ đếm ô. Tuy nhiên, thay vì chỉ đếm các ô chứa số, COUNTA sẽ hỗ trợ bạn đếm bất kỳ ô nào có chứa nội dung (cả số và cả chữ cái). Cú pháp hàm COUNTA bạn thực hiện tương tự như COUNT.
  • Cú pháp hàm Count/Counta
    • =COUNTA (Value1, [value2], [value3],…) Dùng để đếm số ô có chứa nội dung bất kỳ bao gồm chữ số, chữ cái hay biểu tượng, hay nói cách khác nó dùng để đếm các ô không trống.
      • Trong đó:
        • Value1 là đối số bắt buộc, là vùng dữ liệu cần đếm.
        • Value2 và Value3 là các tùy chọn vì được đặt trong dấu [], không bắt buộc có.
    • =COUNT(vùng chọn để đếm) Dùng để đếm số ô chứa dữ liệu Số trong vùng được chọn.

Ví dụ: Tìm số lượng mà giá trị trong ô của vùng dữ liệu là số, ta có công thức cho ví dụ này là =COUNT(A2:C5). Kết quả đạt được là tổng số lượng ô có chứa số.

Hàm COUNT trong excel

Hàm đếm ô không trống COUNTA

Hàm đếm ô không trống COUNTA

Hàm Countif: Hàm đếm dữ liệu theo 1 điều kiện

  • Cú pháp: =Countif(Vùng dữ liệu bạn cần tìm,Điều kiện bạn cần tìm)

Ví dụ: Tìm số mặt hàng có số lượng bán >= 10 kg

Hàm Countif: Hàm đếm dữ liệu theo 1 điều kiện

Điều kiện sử dụng với nội dung là “>=10” tại cột số lượng bán với công thức hàm là =COUNTIF(C1:C8,”>=10″) và nhấn Enter. Kết quả sẽ ra 6 mặt hàng với số lượng bán >= 10 kg.

Hàm Countifs: Hàm đếm dữ liệu theo nhiều điều kiện

  • Cú pháp: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Trong đó:

  • Criteria_range1 là vùng chọn đầu tiên cần thống kê, giá trị bắt buộc.
  • Criteria1 là điều kiện áp dụng cho vùng chọn criteria_range1, giá trị bắt buộc có thể là ô, biểu thức, văn bản.
  • [criteria_range2, criteria2] là các cặp vùng chọn và điều kiện bổ sung, cho phép tối đa 127 cặp vùng chọn và điều kiện

Ví dụ: Tính số lượng nhân viên Nam có 25 ngày công làm việc

Hàm Countifs Hàm đếm dữ liệu theo nhiều điều kiện

Tại ô cần nhập kết quả chúng ta nhập công thức =COUNTIFS(C2:C7,”Nam”,D2:D7,25) rồi nhấn Enter.

Trong đó:

  • C2:C7 là vùng đếm 1 bắt buộc với Giới tính của nhân viên.
  • Nam là điều kiện của vùng đếm 1.
  • C2:C7 là vùng đếm 2 với Ngày công của nhân viên.
  • 25 là điều kiện của vùng đếm 2.

Kết quả chúng ta có 2 nhân viên là Nam có 25 ngày công.

4. Nhóm hàm tìm giá trị lớn nhất và nhỏ nhất (MIN, MAX, LAGRE, SMALL)

  • Công dụng hàm Min/Max: Sử dụng hàm MAX và hàm MIN để tìm giá trị lớn nhất, nhỏ nhất của các đối số hay vùng dữ liệu.
  • Cú pháp: 
    • =MAX(number 1, number 2, …)
    • =MIN(number 1, number 2, …)  Trong Đó: Number 1, number 2 là các đối số hoặc vùng dữ liệu.

Ví dụ: Giá trị nhỏ nhất trong vùng dữ liệu của ví dụ này, ta có công thức =MIN(A2:C5) và nhận được kết quả 25 là giá trị nhỏ nhất cần tìm.

Ham min max trong excel

Ngược lại, với hàm =MAX(A2:C5) sẽ trả về con số có giá trị lớn nhất trong phạm vi cần tìm là 89

Hàm LARGE: chỉ ra giá trị lớn nhất, lớn thứ k trong mảng dữ liệu cho trước, giúp bạn tìm được giá trị mong muốn từ thứ hạng của giá trị đó trong mảng dữ liệu.

  • Cú pháp: =LARGE( array;k)

Trong Đó:

  • Array: Mảng dữ liệu mà bạn muốn tìm giá trị lớn nhất thứ k trong đó
  • k: Thứ hạng của giá trị mà bạn muốn tìm trong mảng dữ liệu.

Ví dụ: Tìm ra bạn có điểm số cao nhất danh sách và bạn có điểm số cao thứ 2 trong bảng danh sách dưới đây:

Hàm LARGE

Kết quả sau khi nhập hàm là:

Hàm LARGE 1

Hàm SMALL: Tìm giá trị nhỏ nhất, nhỏ thứ k trong một list các dãy giá trị khác nhau.

  • Cú pháp: =SMALL(array,k)
  • Trong Đó:
    • Array: Mảng dữ liệu mà bạn muốn tìm giá trị nhỏ nhất thứ k trong đó
    • k: Thứ hạng của giá trị mà bạn muốn tìm trong mảng dữ liệu.

5. Hàm RANK: Hàm xếp hạng trong Excel

Hàm RANK là hàm được dùng để sắp xếp, phân hạng dữ liệu, số liệu. Bạn có thể dễ dàng sắp xếp, phân hạng dữ liệu từ cao đến thấp hoặc từ thấp đến cao.

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

Trong đó:

  • Number: Giá trị cần xếp hạng trong khối.
  • Ref: Danh sách, khối dữ liệu cần sắp xếp.
  • Order: Thứ tự cần sắp xếp, kiểu sắp xếp (tăng dần hoặc giảm dần). Trong trường hợp order = 0 (hoặc không có tham số này) thì sẽ tính từ cao xuống thấp. Còn order = 1 sẽ xếp từ thấp lên cao.

Ví dụ: Xếp hạng theo thứ tự tăng dần với hàm RANK

Như vậy ta thấy:

  • Người có điểm TB cao nhất là 9.5 được xếp hạng 1
  • Người có điểm TB là 8.1 được xếp thứ 2, trong đó có 2 người cùng điểm, do đó có 2 vị trí xếp hạng 2 là dòng 3 và dòng 6
  • Do 2 người đồng hạng 2 nên sẽ không có xếp hạng 3. Hạng tiếp theo sẽ là hạng 4, ứng với số điểm TB là 7.6

Xếp hạng theo thứ tự tăng dần với hàm RANK

Xếp hạng theo thứ tự giảm dần với hàm RANK: chúng ta muốn xếp hạng theo số lỗi mà mỗi người mắc phải. Ai ít lỗi nhất thì xếp thứ 1. Số lỗi tăng dần thì thứ tự xếp hạng sẽ tăng dần.

Xếp hạng theo thứ tự tăng dần với hàm RANK

Lịch khai giảng Chát Zalo Chát Facebook Google map 0981 550 510