5 hàm phổ biến nhất trong Excel cho phân tích dữ liệu (đi kèm ví dụ minh họa)

Dù có rất nhiều công cụ chuyên dùng cho phân tích dữ liệu như Power BI, Tableau, hay ngôn ngữ lập trình Python, Excel là một công cụ mạnh mẽ trong việc phân tích dữ liệu, được sử dụng rộng rãi trong nhiều lĩnh vực khác nhau nhờ vào sự linh hoạt và dễ sử dụng.  Trong bài viết này, Insight Ouch sẽ cùng bạn khám phá 5 hàm trong Excel được sử dụng nhiều trong phân tích dữ liệu để bạn có thể áp dụng vào quá trình phân tích của mình nhé!

Để minh họa cho các hàm Excel trong bài viết này, chúng ta sẽ sử dụng một dataset mẫu dưới đây:

Dataset này bao gồm các thông tin cơ bản về một nhóm người, bao gồm tên, tuổi, chiều cao, cân nặng, địa chỉ, email. Với bộ dữ liệu này, chúng ta sẽ thực hiện nhiều loại phân tích khác nhau, từ việc đếm số lượng người theo tuổi tác, tìm kiếm thông tin cụ thể, đến tính toán các giá trị tổng, trung bình, ghép & cắt chuỗi ký tự,.... với các hàm trong Excel (tải Dataset tại đây).

1. Đếm giá trị với hàm COUNTIFS()

Khái niệm hàm COUNTIFS() trong Excel: 

Hàm COUNTIFS() trong Excel được sử dụng để đếm số lượng ô trong một hoặc trong nhiều cột đáp ứng tất cả các tiêu chí được chỉ định. Đây là một hàm cực kỳ hữu ích trong Excel khi bạn cần lọc dữ liệu dựa trên nhiều điều kiện cụ thể.

Ví dụ: bảng dữ liệu về nhân viên và bạn muốn đếm số nhân viên nam làm việc ở bộ phận kinh doanh, lúc này bạn có thể sử dụng hàm COUNTIFS() để đếm số nhân viên thỏa mãn điều kiện là nam giới và đang làm việc ở phòng kinh doanh. 

Cú pháp hàm COUNTIFS():

=COUNTIFS(Range_1, Criteria_1, Range_2, Criteria_2, ...)

Trong đó: 

• Range_1: Phạm vi đầu tiên bạn muốn kiểm tra.

• Criteria_1: Tiêu chí áp dụng cho phạm vi đầu tiên.

• Range_2: Phạm vi thứ hai bạn muốn kiểm tra (tùy chọn).

• Criteria_2: Tiêu chí áp dụng cho phạm vi thứ hai (tùy chọn).

…

Bạn có thể thêm tối đa 127 cặp [Phạm vi, Tiêu chí] để kiểm tra nhiều điều kiện khác nhau. 

Đọc thêm: Kết hợp hàm INDEX và MATCH trong Excel để dò nhiều điều kiện như thế nào?

Ví dụ sử dụng hàm COUNTIFS():

Trong bộ dữ liệu "Dummy People", chúng ta muốn đếm số người sống ở từng thành phố.

Đầu tiên chúng ta sẽ đi đếm số người ở thành phố New Orleans (city) - Orleans (Country) với công thức: 

=COUNTIFS($F$2:$F$20, $K6, $G$2:$G$20, L$5)

Trong đó: 

• $F$2:$F$20, $K6 hiểu một cách đơn giản là kiểm tra cột City xem có giá trị nào là New Orleans hay không

• $G$2:$G$20, L$5 là kiểm tra cột Country xem có giá trị nào là Orleans hay không

Nếu giá trị nào thỏa mãn cả hai điều kiện trên thì hãy đếm. 

Làm tương tự với các cột còn lại, ta sẽ có bảng kết quả dưới đây: 

TIPS quan trọng với COUNTIFS(): 

Ngoài đếm các ô có chứa chuỗi ký tự tương ứng với các chuỗi ký tự chỉ định trước, ví dụ đếm số người có Country là Orleans, hàm COUNTIFS() trong Excel cho phép bạn đếm số lượng ô đáp ứng nhiều tiêu chí thuộc nhiều loại khác nhau: 

• Giá trị số (Numeric Value): Bao gồm các giá trị như số nguyên (như 10), số thập phân (như 20.2), phân số (như 1/10), và các giá trị logic như TRUE hoặc FALSE. Các biểu thức này giúp bạn đếm các ô có chứa giá trị tương ứng.

Ví dụ: Đếm số người có tuổi là 30 với công thức: =COUNTIFS(Age_Range, 30)

• Biểu thức (Expression): Bao gồm các biểu thức logic như ">5", "<10". Các biểu thức này giúp bạn đếm các ô có giá trị lớn hơn hoặc nhỏ hơn một giá trị nhất định.

Ví dụ: Đếm số người có tuổi lớn hơn 25 với công thức: =COUNTIFS(Age_Range, ">25")

• Ký tự đại diện (Wildcards): Bao gồm các ký tự như * và ?, giúp bạn linh hoạt trong việc đếm các ô có chứa các mẫu ký tự cụ thể

Trong đó: 

* (dấu sao): Đại diện cho bất kỳ chuỗi ký tự nào.

? (dấu hỏi): Đại diện cho một ký tự duy nhất.

Ví dụ: Đếm số người có tên bắt đầu bằng chữ "A" với công thức: =COUNTIFS(Name_Range, "A*")

Ví dụ: Đếm số người có tên có đúng 5 ký tự với công thức: =COUNTIFS(Name_Range, "?????")

Nguồn: Medium

Việc sử dụng đa dạng các loại tiêu chí này trong hàm COUNTIFS() giúp bạn linh hoạt hơn trong việc lọc và đếm dữ liệu, mang lại hiệu quả cao trong quá trình phân tích dữ liệu.

2. Tìm kiếm giá trị với hàm VLOOKUP()

Khái niệm hàm VLOOKUP() trong Excel: 

Hàm VLOOKUP() trong Excel giúp bạn tra cứu một giá trị trong một cột và trả về một giá trị trong cùng hàng từ một cột khác. Đây là một hàm hữu ích để tra cứu thông tin trong các bảng dữ liệu lớn.

Ví dụ: Giả sử bạn có một bảng danh sách nhân viên với hai cột: "Tên" và "Số điện thoại". Nếu bạn muốn tìm số điện thoại của nhân viên có tên là "Nguyễn Văn A", bạn có thể sử dụng VLOOKUP(). Hàm này sẽ tìm kiếm "Nguyễn Văn A" trong cột "Tên" và trả về số điện thoại tương ứng từ cột "Số điện thoại".

Cú pháp hàm VLOOKUP():

=VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])

Trong đó: 

• Lookup_value: Giá trị bạn muốn tìm kiếm.

• Table_array: Phạm vi ô chứa dữ liệu cần tra cứu.

• Col_index_num: Số thứ tự của cột trong phạm vi Table_array từ đó trả về giá trị.

• Range_lookup: Một giá trị logic (TRUE hoặc FALSE) xác định xem bạn muốn tìm kiếm chính xác (FALSE) hay gần đúng (TRUE).

Ví dụ sử dụng hàm VLOOKUP():

Trong bộ dữ liệu "Dummy People" chúng ta muốn tìm email của nhân viên dựa theo tên, ví dụ tìm email của nhân viên có tên là James. 

Chúng ta sử dụng VLOOKUP để tìm với công thức dưới đây:   

=VLOOKUP(J4, A2:H20, 8, FALSE)

Trong đó: 

• J4: Giá trị tìm kiếm, tức là tên nhân viên bạn muốn tìm (James)

• A2:H20: Phạm vi dữ liệu chứa các thông tin về nhân viên.

• 8: Số thứ tự của cột chứa email trong phạm vi A2:H20. Cột thứ 8 chứa email.

• FALSE: Xác định rằng bạn muốn tìm kiếm chính xác.

Làm tương tự để tìm kiếm Email của các nhân viên khác, ta sẽ có bảng kết quả dưới đây: 

Lưu ý với hàm VLOOKUP()

Một hạn chế của hàm VLOOKUP() là cột chứa giá trị tìm kiếm (Lookup_value) phải nằm bên trái cột chứa giá trị trả về trong phạm vi Table_array. Nếu không, hàm sẽ không hoạt động đúng.

Để khắc phục hạn chế này, Excel đã giới thiệu hàm XLOOKUP() mới với nhiều tính năng linh hoạt hơn, hiện có sẵn cho người dùng Microsoft 365. Bạn có thể tìm hiểu thêm tại đây.  

3. Tổng hợp giá trị với hàm SUM(),COUNT(), AVERAGE()

Trong Excel, việc tính toán và tổng hợp dữ liệu là những thao tác cơ bản và quan trọng giúp bạn có được cái nhìn tổng quan về tệp dữ liệu cần phân tích. Các hàm SUM(), COUNT(), và AVERAGE() là những công cụ hữu ích giúp bạn thực hiện các phép tính này một cách nhanh chóng và hiệu quả.

Đọc thêm: 15 câu hỏi phân tích đề bài giúp tìm ra hướng phân tích dữ liệu hiệu quả

Hàm SUM(): 

Khái niệm: Hàm SUM() được sử dụng để tính tổng các giá trị trong một phạm vi ô xác định trước.

Cú pháp:

=SUM(number1, [number2], ...)

Ví dụ: Trong bộ dữ liệu  "Dummy People", tính tổng các giá trị trong cột Age với công thức: 

= SUM (C2:C20) 

Hàm COUNT(): 

Khái niệm: Hàm COUNT() đếm số lượng ô có chứa giá trị số trong một phạm vi ô xác định trước.

Cú pháp:

=COUNT(value1, [value2], ...)

Ví dụ: Trong bộ dữ liệu  "Dummy People", đếm số người có thông tin về chiều cao (Height) với công thức: 

= COUNT(E2:E20)

Nếu bạn muốn đếm cả những ô chứa văn bản hoặc giá trị logic, bạn có thể sử dụng hàm COUNTA().

Ví dụ: Trong bộ dữ liệu  "Dummy People", đếm số người có thông tin về tên gọi (First_Name) với công thức: 

= COUNTA(A2:A20)

Hàm AVERAGE(): 

Khái niệm: Hàm AVERAGE() tính giá trị trung bình của các giá trị trong một phạm vi ô xác định trước.

Cú pháp:

=AVERAGE(number1, [number2], ...)

Ví dụ: Trong bộ dữ liệu  "Dummy People", tính cân nặng trung bình của mọi người với công thức: 

= COUNTA(A2:A20)

Sau khi tính toán ta sẽ nhận được kết quả trả về như sau: 

Đọc thêm: 10 khái niệm thống kê cơ bản cần biết khi làm việc với dữ liệu

4. Kết hợp các chuỗi văn bản với CONCATENATE()

Khái niệm hàm CONCATENATE() trong Excel: 

Hàm CONCATENATE() trong Excel giúp kết hợp các chuỗi văn bản từ nhiều ô khác nhau thành một chuỗi duy nhất. Điều này đặc biệt hữu ích khi cần ghép nối các thông tin từ nhiều cột để tạo thành một giá trị tổng hợp, chẳng hạn như ghép nối họ và tên, địa chỉ, hoặc các thông tin sản phẩm.

Cú pháp hàm CONCATENATE():

=CONCATENATE(Text1, [Text2], ...)

Trong đó: 

• Text1: Chuỗi văn bản đầu tiên bạn muốn kết hợp. Đây có thể là một chuỗi ký tự, một tham chiếu ô, hoặc một giá trị văn bản được đặt trong dấu ngoặc kép.

• [Text2], ...: Các chuỗi văn bản bổ sung mà bạn muốn kết hợp. 

  …

Ví dụ sử dụng hàm CONCATENATE():

Trong bộ dữ liệu  "Dummy People", ta sẽ cần kết hợp 2 cột First_Name và Last_Name để tạo thành một cột Full Name chứa đầy họ và tên đầy đủ:

Chúng ta sẽ sử dụng hàm CONCATENATE() để ghép chuỗi giá trị đầu tiên với công thức: 

=CONCATENATE(A2, " ", B2)

Trong đó: 

• A2: Chứa giá trị ở cột First_Name (James)

• " ": Dấu cách để tách họ và tên

• B2: Chứa giá trị ở cột Last_Name (Butt)

Công thức này sẽ kết hợp giá trị trong ô A2 và B2 với một dấu cách giữa chúng, tạo thành một chuỗi văn bản đầy đủ. Làm tương tự chúng ta sẽ lấy được cột Full Name bao gồm họ và tên đầy đủ. 

Một số lỗi thường gặp phải với CONCATENATE(), và cách khắc phục: 

#NAME?

Nguyên nhân: Lỗi này xảy ra khi bạn quên đặt dấu ngoặc kép xung quanh chuỗi văn bản. Khi bạn muốn kết hợp một chuỗi văn bản trực tiếp, bạn phải đặt chuỗi đó trong dấu ngoặc kép. Nếu thiếu dấu ngoặc kép, Excel sẽ không nhận diện được chuỗi văn bản và báo lỗi #NAME?

Ví dụ:

Sửa lại: =CONCATENATE(A2, "Hello", B2)

Dấu ngoặc kép không mong muốn trong kết quả

Nguyên nhân: Lỗi này xảy ra khi bạn quên dấu phẩy để tách các tham số chuỗi văn bản. Khi bạn quên dấu phẩy, Excel sẽ hiểu rằng phần văn bản sau dấu ngoặc kép là một phần của chuỗi kết quả, và sẽ thêm dấu ngoặc kép vào kết quả cuối cùng.

Ví dụ:

Sửa lại: =CONCATENATE("James","Butt")

5. Split data with LEFT(), MID(), RIGHT()

Trong Excel, các hàm LEFT(), MID(), và RIGHT() giúp bạn trích xuất các phần cụ thể của chuỗi văn bản từ một ô dữ liệu, đặc biệt hữu ích khi bạn cần tách hoặc phân tích các chuỗi văn bản dài thành các phần riêng biệt.

Ngoài việc dùng các hàm trong Excel, bạn cũng có thể sử dụng tính năng Split Column trong Power Query để tách từng phần trong một chuỗi văn bản. 

Sử dụng tính năng Split Column để tách cột Contract ra thành hai cột - Slide khóa học Data Visualization & Analytics with Excel

Hàm LEFT()

Khái niệm: Hàm LEFT() được sử dụng để trích xuất một số ký tự nhất định từ phía bên trái của một chuỗi văn bản.

Cú pháp:

=LEFT(text, [num_chars])

Trong đó: 

• text: Chuỗi văn bản mà bạn muốn trích xuất.

• num_chars: Số ký tự bạn muốn trích xuất từ bên trái chuỗi văn bản. Nếu không xác định, mặc định là 1.

Ví dụ: Trong bộ dữ liệu  "Dummy People", có thể cắt chuỗi để lấy ký tự đầu tiên trong cột First_Name với công thức:

=LEFT(J3,1)

Trong đó: 

• J3: Chuỗi văn bản mà bạn muốn trích xuất - James

• 1: Số ký tự bạn muốn trích xuất từ bên trái chuỗi văn bản

Làm tương tự với các ô còn lại chúng ta sẽ lấy được toàn bộ ký tự đầu tiên trong cột First_Name. 

Hàm RIGHT()

Khái niệm: Hàm RIGHT() khá tương tự với LEFT() nhưng được sử dụng để trích xuất một số ký tự nhất định từ phía bên phải của một chuỗi văn bản.

Cú pháp:

=RIGHT(text, [num_chars])

Trong đó: 

• text: Chuỗi văn bản mà bạn muốn trích xuất.

• num_chars: Số ký tự bạn muốn trích xuất từ bên phải chuỗi văn bản. Nếu không xác định, mặc định là 1.

Ví dụ: Trong bộ dữ liệu  "Dummy People", có thể cắt chuỗi để lấy 3 ký tự cuối cùng trong cột First_Name với công thức:

=RIGHT(J3,3)

Trong đó: 

• J3: Chuỗi văn bản mà bạn muốn trích xuất - James

• 3: Số ký tự bạn muốn trích xuất từ bên phải chuỗi văn bản

Làm tương tự với các ô còn lại chúng ta sẽ lấy được toàn bộ 3 ký tự cuối cùng trong cột First_Name. 

Hàm MID()

Khái niệm: Hàm MID() được sử dụng để trích xuất một số ký tự nhất định từ vị trí cụ thể trong một chuỗi văn bản.

Cú pháp:

=MID(text, start_num, num_chars)

Trong đó: 

• text: Chuỗi văn bản mà bạn muốn trích xuất.

• start_num: Vị trí bắt đầu trích xuất trong chuỗi văn bản.

• num_chars: Số ký tự bạn muốn trích xuất từ vị trí bắt đầu.

Ví dụ: Trong bộ dữ liệu  "Dummy People", có thể cắt chuỗi để lấy 3 ký tự ở giữa tính từ vị trí thứ 2 trong cột First_Name với công thức:

=MID(J3,2,3)

Tạm kết

Qua bài viết trên, TM đã vừa cùng bạn khám phá các hàm phổ biến nhất trong Excel: COUNTIFS(), VLOOKUP(), SUM(), COUNT(), AVERAGE(), CONCATENATE(), và LEFT(), MID(), RIGHT(). Những hàm này không chỉ giúp bạn xử lý dữ liệu hiệu quả mà còn giúp bạn có được cái nhìn tổng quan hơn về tệp dữ liệu của mình trước khi phân tích. Nắm vững các hàm này sẽ giúp bạn tận dụng tối đa sức mạnh của Excel trong việc xử lý và phân tích dữ liệu. 

Nếu bạn muốn trau dồi thêm kỹ năng xử lý dữ liệu, trực quan hóa dữ liệu và xây dựng dashboard với Excel hãy tham khảo khóa học Data Visualization & Analytics with Excel của TM Data School nhé!