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

Hàm IF

Hàm IF là cực kỳ hữu ích trong Excel, hàm này sẽ giúp bạn 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.

Hàm IF yêu cầu bạn thực hiện kiểm tra logic, hành động nào cần thực hiện nếu kiểm tra là đúng và hành động thay thế nếu kết quả của kiểm tra là sai.

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

Trong ví dụ dưới đây, chúng tôi đã hiển thị từ “Yes” 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ừ “No” được hiển thị.

=IF(D2>7,"Yes","no")

Ngoài ra, hàm IF còn được kết hợp với hàm AND, OR và một số hàm logic khác để thực hiện những yêu cầu kiểm tra điều kiện phức tạp.

Để tìm hiểu kỹ hơn về hàm IF và cách sử dụng, các bạn có thể xem thêm: Hướng dẫn Hàm IF và cách sử dụng hàm IF trong Excel

Hàm SUMIFS

SUMIFS là một trong những hàm Excel hữu ích nhất. Nó tính tổng các giá trị đáp ứng các 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ự ngoại trừ 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 vì SUMIFS là một hàm vượt trội.

Hàm sẽ yêu cầu bạn tổng hợp phạm vi giá trị, sau đó kiểm tra từng phạm vi và tiêu chí.

=SUMIFS(sum range, criteria range 1, criteria 1, …)

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

= SUMIFS (C2: C9, B2: B9, E3)

Để tìm hiểu kỹ hơn về hàm SUMIFS và cách sử dụng, các bạn có thể xem thêm: Hướng dẫn cách dùng hàm sumifs đơn giản qua bài tập

Hàm COUNTIFS 

Hàm COUNTIFS là một hàm lớn khác để phân tích dữ liệu Excel. Nó rất giống với hàm SUMIFS ở trên. 

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.

=COUNTIFS(criteria range 1, criteria 1, …)

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

=COUNTIFS(B2:B9,E3,C2:C9,">=200”)

Khi sử dụng hàm SUMIFS và 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 sử dụng cả hai kỹ thuật trong cùng một công thức.

Hàm TRIM

Hàm này 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 giữa các từ.

Việc sử dụng phổ biến nhất của hàm TRIM này là 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 được sử dụng ở cuối ô B6.

Người dùng không thể nhìn thấy khoảng trống này, có nghĩa là nó không được xác định cho đến khi thứ gì đó ngừng hoạt động. Trong trường hợp đó hàm TRIM sẽ nhắc bạn xóa khoảng trắng khỏi văn bản.

=TRIM(text)

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

=TRIM(B2)

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

Hàm CONCATENATE

Hàm CONCATENATE kết hợp các giá trị từ nhiều ô thành một. Điều này rất hữu ích để nối 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 hoặc đường dẫn tệp hoặc URL.

Nó nhắc bạn về các giá trị khác nhau để sử dụng.

= 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 đủ. Một khoảng trắng được nhập cho đối số text2.

=CONCATENATE (A2,"",B2)

Xem thêm: Hướng dẫn cách dùng hàm CONCATENATE để nối chuỗi, nối ô trong Excel

Hàm LEFT/RIGHT

Các hàm LEFT và RIGHT sẽ thực hiện các nhiệm vụ đối lập của hàm CONCATENATE. Cả hai sẽ trích xuất một số ký tự được chỉ định từ đầu và 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à RIGHT yêu cầu thông tin giống nhau và giúp người dùng muốn biết văn bản ở đâu và bạn muốn trích xuất bao nhiêu ký tự.

=LEFT(text, num chars)
=RIGHT(text, num chars)

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

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

=LEFT(A2,2)

Hàm RIGHT có thể đượ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 khách hàng ở miền Nam hay miền Bắc.

=RIGHT(A2,1)

Xem thêm: Hướng dẫn cách sử dụng hàm RIGHT trong Excel hiệu quả nhất

Hàm VLOOKUP

Hàm VLOOKUP là một trong những hàm được sử dụng phổ biến và dễ nhận biết nhất trong Excel. Nó sẽ tìm kiếm một giá trị trong bảng và trả về thông tin từ một cột khác liên quan đến giá trị đó.

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

Hàm VLOOKUP gợi ý cho 4 thông tin khác nhau gồm:

• 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 nào bạn muốn thực hiện.

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

 Trong ví dụ này, chúng tôi có một bảng chứa doanh số bán hàng từ nhân viên. Có một bảng khác với thông tin thêm về những nhân viên này.

Sử dụng hàm VLOOKUP để xác định nhân viên ở khu vực nào có doanh số cao nhất.

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

=VLOOKUP(B2,$G$2:$H$12,2,FALSE)

Đây có thể là một trong những hàm khó học hơn đối với người mới bắt đầu làm quen với công thức Excel. Bạn có thể tìm hiểu về hàm VLOOKUP chuyên sâu hơn trong bài viết: Hướng dẫn chi tiết cách dùng hàm vlookup cơ bản nhất cho người mới bắt đầu

Hàm IFERROR

Đôi khi những sai sót xảy ra có thể là vô tình hoặc những lỗi này có thể là điều bạn có thể đoán trước được. Hàm VLOOKUP trước đây là một ví dụ điển hình cho điều này.

Như trong hình dưới đây lỗi phát sinh vì lỗi đánh máy 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.

Sử dụng hàm IFERROR, chúng tôi có thể hiển thị một lỗi có ý nghĩa hơn lỗi mà Excel cung cấp hoặc thậm chí thực hiện một phép tính khác.

Hàm IFERROR yêu cầu hai điều. Giá trị để kiểm tra lỗi và hành động cần thực hiện thay thế.

Trong ví dụ này, chúng tôi kết hợp hàm IFERROR với hàm VLOOKUP để hiển thị một thông báo có ý nghĩa hơn.

=IFERROR(VLOOKUP(B2,$G$2:$H$12,2,FALSE),"Name not found. Check both lists")

Xem thêm: Hướng dẫn dùng hàm IfError trên Excel

Hàm VALUE 

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

Điều này thường có thể dẫn đến dữ liệu có đị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 như tính tổng nếu Excel không nhận ra chúng là một số.

Trong trường hợp này hàm VALUE ở đây để trợ giúp. Công việc của hàm này là chuyển đổi các số được lưu trữ dưới dạng văn bản thành số .

=VALUE(text)

Trong ví dụ này, công thức sau chuyển đổi các giá trị 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)

Xem thêm: Hướng dẫn cách sử dụng hàm VALUE trong Excel chuyển chuỗi thành số

Hàm UNIQUE 

Hàm UNIQUE là một chức năng mới chỉ dành cho những người sử dụng phiên bản Microsoft 365.

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

• Phạm vi trả về danh sách duy nhấ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).

=UNIQUE(array, by col, exactly once)

Trong ví dụ này, chúng tôi có một danh sách bán sản phẩm và chúng tôi muốn trích xuất một danh sách tên sản phẩm duy nhất. Đối với điều này, chúng tôi chỉ cần cung cấp phạm vi.

=UNIQUE(B2:B15)

Đây là một hàm mảng động và do đó làm tràn kết quả. Đường viền màu xanh lam cho biết phạm vi bị tràn.

Sau đó, chúng ta có thể sử dụng hàm SUMIFS, được đề cập trước đó trong bài viết này, để tính tổng doanh thu cho từng sản phẩm đó.

Điều này sẽ quen thuộc với trước đó. Tuy nhiên, dấu # đã được sử dụng để tham chiếu đến phạm vi bị tràn lần này.

Xem thêm: Cách sử dụng hàm UNIQUE tự động lọc danh sách không trùng trong Excel

Hàm SORT 

Tiếp tục là một chức năng khác chỉ dành cho người đăng ký Microsoft Office 365. Như tên cho thấy, hàm SORT được sử dụng để sắp xếp một danh sách.

Hàm SORT có 4 đối số gồm:

• 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)

Hàm này có thể được sử dụng kết hợp với hàm UNIQUE ở trên để sắp xếp tên sản phẩm theo thứ tự. Đối với điều này, chúng tôi chỉ cần cung cấp cho hàm phạm vi để sắp xếp.

=SORT(UNIQUE(B2:B15))

Hàm FILTER

Tiếp theo hàm SORT còn có hàm FILTER để lọc danh sách. Đây cũng là một chức năng khác chỉ có sẵn cho người dùng Microsoft Office 365 .

Hàm này sẽ lọc một dải ô. Đây là một chức năng cực kỳ mạnh mẽ vô cùng hữu ích  để phân tích dữ liệu và lập báo cáo.

Hàm FILTER nhận ba đối số:

• Phạm vi để lọc

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

• Phải làm gì nếu không có kết quả trả về.

=FILTER(array, include, if empty)

Trong ví dụ này, chỉ các kết quả cho chủ đề được nhập trong ô F2 được trả về.

=FILTER(B2:C12,A2:A12=F2,"No scores")

Việc tìm hiểu và học về các hàm Excel hữu ích nhất để phân tích dữ liệu được đề cập trong bài viết này sẽ giúp bạn phân tích dữ liệu Excel dễ dàng hơn. Nhưng vẫn còn nhiều hàm khác và cả các tính năng khác của Excel mà bạn cần tìm hiểu để trở thành một nhà phân tích dữ liệu thực thụ.

Hai công cụ Excel cần thiết khác cũng rất quan trọng khi phân tích dữ liệu đó là Power Query và Power Pivot .

Power Query giúp việc nhập và chuyển đổi dữ liệu để phân tích trở nên dễ dàng. Và Power Pivot là công cụ hoàn hảo nếu bạn phân tích khối lượng lớn dữ liệu. Nó có thể lưu trữ khối lượng lớn dữ liệu bên ngoài Excel và có ngôn ngữ công thức riêng gọi là DAX.

Tham gia ngay khóa học Power Pivot, Power Query - Biến Excel thành công cụ Phân tích dữ liệu chuyên sâu trên Gitiho để nắm bắt các kỹ năng phân tích dữ liệu của bạn trong Excel ngay hôm nay. Hoặc có thể tìm hiểu các khóa học khác về Excel trên Gitiho tại đây.