Tìm hiểu 12 hàm hữu ích nhất trong Excel để phân tích dữ liệu

Thanh Hằng
Thanh Hằng
Nov 01 2021

Có hơn 475 hàm trong Excel . Điều này có thể khiến bạn choáng ngợp khi bắt đầu phân tích dữ liệu. Với số lượng lớn các hàm, có thể khó biết hàm nào sẽ sử dụng cho công việc của mình.

Các hàm Excel hữu ích nhất là những hàm làm cho công việc trở nên dễ dàng. Và tin tốt là hầu hết người dùng Excel đều có một bộ công cụ chỉ gồm một vài hàm đáp ứng hầu hết các nhu cầu của họ.

Gitiho sẽ tổng hợp cho bạn 12 hàm Excel hữu ích nhất để phân tích dữ liệu. Các hàm này cung cấp cho bạn công cụ để xử lý phần lớn các tác vụ phân tích dữ liệu trong Excel.

Xem thêm: Hướng dẫn cách viết các hàm trong Excel chi tiết, dễ hiểu nhất

Biến Excel thành công cụ phân tích dữ liệu chuyên sâu.

Hàm Excel để phân tích dữ liệu

Hàm IF

Hàm IF cực kỳ hữu ích giúp chúng ta có thể tự động ra quyết định trong bảng tính Excel của mình.

Với hàm IF, chúng ta có thể so sánh biểu thức hay thực hiện một phép tính khác hoặc hiển thị một giá trị khác phụ thuộc vào kết quả của một bài kiểm tra logic.

Công thức hàm IF là:

=IF(logical test; value if true; value if false)

Trong đó:

  • Logical_test: Điều kiện.
  • Value_if_true: Giá trị trả về nếu thỏa điều kiện
  • Value_if_false: Giá trị trả về nếu không thỏa điều kiện.

Trong ví dụ dưới đây, chúng ta sẽ nhận được kết quả là “Có” nếu ngày giao hàng trong cột C muộn hơn 7 ngày so với ngày đặt hàng trong cột B. Nếu không, từ “Không” được hiển thị.

=IF (D2>7;"Có";"Không")

 

Hàm SUMIFS

Hàm SUMIFS là một trong những hàm Excel hữu ích nhất dùng để tính tổng các giá trị đáp ứng những tiêu chí được chỉ định .

Excel cũng có một hàm tên là SUMIF thực hiện nhiệm vụ tương tự nhưng nó chỉ có thể kiểm tra một điều kiện, trong khi SUMIFS có thể kiểm tra nhiều điều kiện. Vì vậy, về cơ bản bạn có thể bỏ qua SUMIF, thay vào đó sử dụng hàm SUMIFS vì nó vượt trội hơn.

Hàm SUMIFS giúp bạn tính tổng phạm vi giá trị, sau đó kiểm tra từng phạm vi và tiêu chí để kiểm tra phạm vi giá trị đó.

Công thức hàm SUMIFS là:

=SUMIFS(sum range; criteria range 1; criteria 1;[criteria range; criteria 2]…)

Trong đó:

  • Sum_range (bắt buộc): 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.
  • Criteria range 1 (bắt buộc): Phạm vi cần được kiểm tra bằng điều kiện criteria 1.
  • Criteria 1 (bắt buộc): Điều kiện áp dụng cho criteria range 1, có thể là một số, biểu thức, tham chiếu ô để xác định ô nào trong criteria range 1 sẽ được tính tổng.
  • [criteria range 2, criteria 2] (tùy chọn): Các điều kiện bổ sung. Bạn có thể nhập tới 127 cặp điều kiện trong công thức.

Trong ví dụ này, chúng ta đang tính tổng các giá trị trong cột C cho vùng được nhập vào ô E4.

= SUMIFS (C2:C7;B2:B7;E4)

   

Hàm COUNTIFS

Hàm COUNTIFS là một hàm lớn, thông dụng dùng để phân tích dữ liệu Excel.

Hàm COUNTIFS sẽ đếm số lượng giá trị đáp ứng các tiêu chí đã chỉ định. Do đó, nó không yêu cầu một phạm vi tổng như SUMIFS.

Công thức hàm COUNTIFS là:
=COUNTIFS (criteria range 1; criteria 1; [criteria range; criteria 2]…)

Trong đó:

  • criteria range 1 (bắt buộc): Xác định phạm vi đầu tiên áp dụng điều kiện đầu tiên (criteria 1).
  • criteria 1 (bắt buộc): Điều kiện cho dạng số, ô tham chiếu, chuỗi văn bản, một mảng hoặc một hàm Excel khác. Tiêu chí này xác định những ô tính nào sẽ được đếm.
  • [criteria range 2, criteria 2] (tùy chọn): Các điều kiện bổ sung. Bạn có thể nhập tới 127 cặp điều kiện trong công thức. 

Trong ví dụ này, chúng ta cùng đếm số lần bán sản phẩm từ khu vực được nhập vào ô E3 có giá trị từ 150 trở lên.

=COUNTIFS (B2:B7;E4;C2:C7;">=150")

 

Lưu ý: Khi sử dụng hàm SUMIFS và hàm COUNTIFS, tiêu chí phải được nhập dưới dạng văn bản hoặc dưới dạng tham chiếu ô. Ví dụ này, mình đã sử dụng cả hai kỹ thuật trong cùng một công thức giúp bạn hiểu rõ hơn.

Hàm TRIM

Hàm TRIM là một hàm tuyệt vời để sẽ xóa tất cả các khoảng trắng khỏi một ô ngoại trừ các khoảng trắng đơn giữa các từ. Hàm TRIM được sử dụng phổ biến nhất để loại bỏ các dấu cách ở cuối. Điều này thường xảy ra khi nội dung được dán từ một nơi khác hoặc khi người dùng vô tình nhập khoảng trắng ở cuối văn bản.

Trong ví dụ này, hàm COUNTIFS trước đó không hoạt động vì một khoảng trắng đã vô tình xuất hiện ở cuối ô B7, (B7 = Hồ Chí Minh + khoảng trắng).

 

Chúng ta khó có thể nhìn thấy được khoảng trắng này trong một trang tính nhiều dữ liệu, có nghĩa là nó không được xác định cho đến khi có một hàm nào đó không hoạt động.

Hàm TRIM sẽ giúp bạn xóa khoảng trắng khỏi văn bản. Công thức hàm TRIM là:

=TRIM(text)

Trong ví dụ này, hàm TRIM được sử dụng trong một cột riêng biệt để làm sạch dữ liệu trong cột "Chi nhánh" để phân tích .

=TRIM(B2)

Khi đó, hàm COUNTIFS có dữ liệu đúng và hoạt động chính xác.


Hàm CONCATENATE

Hàm CONCATENATE có chức năng kết hợp các giá trị từ nhiều ô tính thành một. Chức năng này rất hữu ích để ghép các phần khác nhau của văn bản như tên của ai đó, địa chỉ, số tham chiếu, đường dẫn tệp hoặc URL.

Công thức hàm CONCATENATE là:

=CONCATENATE(text1;text2;text3;…)

Trong ví dụ này, CONCATENATE được sử dụng để kết hợp họ và tên thành một tên đầy đủ của khách hàng. Thêm một khoảng trắng được nhập để ngăn cách giữa text1 và text2.

= CONCATENATE (A2;" ";B2)

Hàm LEFT và hàm RIGHT

Hàm LEFThàm RIGHT sẽ thực hiện hành động đối lập so với hàm CONCATENATE. Hai hàm này sẽ trích xuất một số ký tự được chỉ định ở đầu hoặc cuối văn bản.

Điều này có thể được sử dụng để trích xuất các phần của địa chỉ, URL hoặc tham chiếu để phân tích thêm.

Hàm LEFT và hàm RIGHT yêu cầu những thông tin giống nhau, nó muốn biết văn bản ở đâu và bạn muốn trích xuất bao nhiêu ký tự.

Công thức hàm LEFT là:

=LEFT(text, num chars)

Công thức hàm RIGHT là:

=RIGHT(text, num chars)

Trong ví dụ này, cột A chứa thông tin được tạo thành từ Mã khách hàng (hai ký tự đầu tiên), mã giao dịch và sau đó là mã đăng ký (ký tự cuối cùng).

Hàm LEFT sau đây được sử dụng để trích xuất Mã khách hàng.

=LEFT(A2;2)


 

Hàm RIGHT được sử dụng để trích xuất ký tự cuối cùng từ các ô trong cột A. Ví dụ này cho biết mã đăng ký của khách thuộc dịch vụ nào.

=RIGHT(A2;1)

Hàm VLOOKUP

Hàm VLOOKUP là một trong những hàm được sử dụng và ứng ụng phổ biến nhất trong Excel.

Hàm VLOOKUP sẽ tìm kiếm một giá trị trong một bảng và trả về thông tin từ một cột khác liên quan đến giá trị đó.

Nó rất tốt để kết hợp dữ liệu từ các danh sách khác nhau vào một hoặc so sánh hai danh sách để tìm các mục phù hợp hoặc bị thiếu. Hàm VLOOKUP là một công cụ quan trọng trong phân tích dữ liệu Excel.

Công thức hàm VLOOKUP là:

=VLOOKUP(lookup value, table array, column index number, range lookup)

Hàm VLOOKUP yêu cầu bốn phần thông tin:

  • Giá trị bạn muốn tìm kiếm
  • Bảng nào để xem
  • Cột nào có thông tin bạn muốn trả lại
  • Loại tra cứu bạn muốn thực hiện.

Trong ví dụ này, chúng ta có một bảng chứa doanh số bán hàng của các nhân viên.

Yêu cầu là đưa số liệu thể hiện nhân viên ở khu vực nào vào bảng doanh số để phân tích.

Công thức sau được sử dụng trong cột D:

=VLOOKUP(B2;$F$2:$G$7;2;FALSE)

 

Xem thêm: Hướng dẫn phân biệt hàm VLOOKUP với HLOOKUP và bí quyết sử dụng hàm

Hàm IFERROR

Đôi khi những sai sót xảy ra là bất ngờ nhưng đôi khi những sai sót này có thể là điều bạn có thể đoán trước được. Vì vậy, chúng ta sử dụng hàm IFERROR để nhận được giá trị mong muốn khi điều kiện của hàm này cho kết quả lỗi.

Hàm IFERROR yêu cầu hai điều:

  • Giá trị để kiểm tra lỗi
  • Giá trị thay thế.

Cú pháp hàm IFERROR là:

=IFERROR(value, value_if_error)

  • Value (bắt buộc): Đây là đối số cần kiểm tra, có thể là phép tính, công thức, hàm Excel.
  • Value_if_error (bắt buộc): Đây là giá trị trả về nếu công thức bị lỗi.

Hàm VLOOKUP trước đây là một ví dụ điển hình cho điều này. Chúng ta có một lỗi vì có một thiếu sót trong nhập liệu ở tên nhân viên trong bảng bán hàng. Điều này có nghĩa là hàm VLOOKUP không thể tìm thấy tên đó và tạo ra lỗi.

Trong ví dụ này, chúng ta sẽ kết hợp hàm IFERROR và hàm VLOOKUP để có một kết quả chính xác hơn.

=IFERROR(VLOOKUP(B2;$F$2:$G$7;2;FALSE),"Không có dữ liệu")

 

Hàm VALUE

Thông thường tập hợp dữ liệu bạn cần phân tích là được nhập từ hệ thống khác hoặc được sao chép và dán từ một nơi nào đó.

Điều này thường có thể dẫn đến dữ liệu ở định dạng sai, chẳng hạn như một số được lưu trữ dưới dạng văn bản. Bạn không thể thực hiện các tác vụ phân tích dữ liệu chẳng hạn như tính tổng nếu Excel không nhận ra chúng là một số.

May mắn thay, hàm VALUE ở đây để giải quyết vấn đề đó. Công việc của nó là chuyển đổi các số được lưu trữ dưới dạng văn bản thành số.

Cú pháp của hàm VALUE là:

= VALUE (text)

Trong ví dụ này, công thức sau chuyển đổi doanh số bán hàng được lưu trữ dưới dạng văn bản trong cột B thành một số.

= VALUE (B2)


Hàm UNIQUE

Hàm UNIQUE trích xuất danh sách các giá trị riêng biệt hoặc danh sách các giá trị chỉ xuất hiện một lần, tức là các giá trị duy nhất (chỉ dành cho những người sử dụng Microsoft 365)

Hàm muốn biết ba điều:

  • Phạm vi trả về danh sách duy nhất từ

  • Cho dù bạn muốn kiểm tra các giá trị duy nhất theo cột hay theo hàng

  • Cho dù bạn muốn một danh sách duy nhất hay một danh sách riêng biệt (các mục chỉ xuất hiện một lần).

Công thức hàm UNIQUE là:

=UNIQUE(array,[by_col],[occurs_once])

Trong đó:

  • array(bắt buộc): Vùng, cột hoặc hàng mà bạn muốn những giá trị duy nhất được trả về
  • [by_col] (tùy chọn): giá trị mặc định là FALSE – sắp xếp theo hàng, TRUE – sắp xếp theo cột
  • [occurs_once] (tùy chọn), cho phép bạn tìm các giá trị thực sự duy nhất, tức là các giá trị chỉ xuất hiện một lần (TRUE) hoặc tất cả các giá trị khác biệt (FALSE). Nếu bạn bỏ qua đối số này, nó sẽ mặc định là FALSE và trả về một danh sách riêng biệt

Ví dụ tìm giá trị duy nhất trong bảng dưới đây:


Công thức hàm UNIQUE trong trường hợp này là: =UNIQUE(A2:A10)

Hàm SORT

Hàm SORT có chức năng sắp xếp một danh sách theo yêu cầu (chỉ dành cho phiên bản Microsoft 365).

Hàm SORT yêu cầu bốn đối số:

  • Phạm vi sắp xếp

  • Cột nào để sắp xếp phạm vi theo

  • Thứ tự sắp xếp phạm vi (tăng dần hoặc giảm dần)

  • Sắp xếp các hàng hay cột.

=SORT(array, sort index,sort order, by col)

Trong đó: 

  • array: Mảng dữ liệu cần sắp xếp (có thể gồm nhiều dòng, nhiều cột liên tục).
  • sort_index: Sắp xếp theo cột hay dòng thứ mấy trong mảng dữ liệu (nhập số cụ thể). Nếu bỏ qua mặc định là cột/dòng đầu tiên.
  • sort_order: Thứ tự sắp xếp theo tăng dần hay giảm dần. Nếu bỏ qua mặc định là tăng dần.
  • by_col: Sắp xếp theo cột hay theo dòng. Nếu bỏ qua mặc định là theo cột.

Ví dụ: Sắp xếp cho số điểm của học sinh theo thứ tự tăng dần

Cú pháp hàm SORT là: =SORT(C2:C6;1;1)

Trong đó: 

  • C2:C6: Phạm vi cần sắp xếp
  • 1: Sắp xếp theo dòng thứ 1
  • 1: Sắp xếp theo thứ tự tăng dần

Hàm SORT kết hợp với hàm UNIQUE

Yêu cầu lọc ra một danh sách các sản phẩm duy nhất theo thứ tự bảng chữ cái:

Cú pháp hàm SORT trong trường hợp này là: =SORT(UNIQUE(C2:C9))

Hàm FILTER

Hàm FILTER là một chức năng chỉ được cung cấp trong Microsoft 365 .

Hàm FILTER giúp bạn dễ dàng tìm kiếm và lọc dữ liệu theo một hoặc nhiều điều kiện cho trước. Đây là một chức năng cực kỳ mạnh mẽ và là niềm mơ ước để phân tích dữ liệu và tạo báo cáo.

Hàm FILTER yêu cầu ba đối số:

  • Phạm vi để lọc

  • Tiêu chí chỉ định kết quả nào sẽ trả về

  • Hành động nào cần thực hiện nếu không có kết quả nào được trả về.

Cú pháp hàm FILTER là: 

=FILTER(array. include,if_emplty)

  • array (bắt buộc): Vùng dữ liệu cần lọc (không sử dụng tới hàng tiêu đề). Đây là điểm khác biệt giữa hàm FILTER và công cụ Filter thông thường
  • include (bắt buộc): Điều kiện cần lọc và cột dữ liệu cần lọc (Xác định đồng thời cả 2 yếu tố này: Điều kiện cần lọc là gì? Nằm trong cột nào?)
  • if_empty (tùy chọn): Kết quả trả về trong trường hợp không có kết quả thỏa mãn điều kiện (Nếu không tìm và lọc được kết quả thì hàm FILTER sẽ trả về giá trị gì?)

Ví dụ lọc những mặt hàng loại A và loại B trong bảng dưới đây:

Xem thêm: TẠI SAO NÊN DÙNG HÀM FILTER ĐỂ LỌC DỮ LIỆU TRONG EXCEL OFFICE 365

Kết luận

Hy vọng bài viết này đã giúp bạn hiểu hơn về cách sử dụng 12 hàm Excel phổ biến và thông dụng cho việc phân tích dữ liệuNếu bạn có bất kỳ câu hỏi nào liên quan đến bài viết này hoặc về bất kỳ chủ đề Excel nào khác, hãy cho chúng mình biết trong phần bình luận bên dưới. Và đừng quên theo dõi chúng mình để xem thêm các bài viết bổ ích khác nhé!

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội