Trong bài viết lần này, Học Excel Online sẽ đến với một ứng dụng thực tiễn của hàm COUNTIF trong việc lập bảng phục vụ mục đích tổng kết báo cáo, đó là việc thiết lập số thứ tự thành phần bảng phân loại.

Cụ thể, ta sẽ bắt đầu từ bảng tính như sau:

Mục đích ở đây là ta sẽ đánh số thứ tự để phân loại. Sau đó tách thành các nhóm lớn để tổng hợp và… gửi các sếp  😛 .

Hãy cùng Học Excel Online giải quyết nhé. Bài toán này có thể giải quyết chỉ bằng vài hàm đơn giản mà trọng tâm là hàm COUNTIF thôi đấy. Các bạn tin không?

Sử dụng các hàm xử lý ký tự lấy dữ liệu từ thông tin có sẵn

Đầu tiên, ta phải “phân nhóm” sản phẩm trước đã. Có thể thấy rõ rằng có 3 nhóm ở đây bao gồm: bút chì, bút bi, thước kẻ.

Giờ hãy tạo cột phụ bằng cách click chuột phải vào chữ A và chọn Insert để tạo ra cột mới. Làm thao tác này 2 lần để tạo 2 cột mới nhé.

Cột đầu tiên ta sẽ điền “Số thứ tự”, cột thứ hai là “Loại sản phẩm”. Tất nhiên các bạn có thể đặt những cái tên hay hơn và vắn tắt hơn. Và kéo rộng cột ra một chút bằng cách chọn cột muốn mở rộng, đưa con trỏ chuột vào lề cột tới khi nó chuyển như trong hình, sau đó kéo thả.

Bước tiếp theo, ta sẽ dùng các hàm liên quan tới ký tự, văn bản để lấy loại sản phẩm từ tên sản phẩm. Trong bài này, logic lấy chuỗi sẽ là từ dấu cách thứ 2 đổ lại.

1. Sử dụng hàm FIND để tìm ra vị trí của dấu cách thứ nhất theo cú pháp: =FIND(” “,C2)
2. Sau khi tìm ra vị trí của dấu cách thứ nhất, ta tìm đến dấu cách thứ 2 cũng theo cú pháp: =FIND(” “,C3,FIND(” “,C3)+1
3. Cuối cùng dùng hàm LEFT để lấy chuỗi kí tự tính từ đầu đến dấu cách: =LEFT(C3,FIND(” “,C3,FIND(” “,C3)+1)-1)

Giải thích một chút:
Công thức của hàm FIND là:

=FIND(find_text,within_text,[start_num])

Với find_text là kí tự cần tìm kiếm, within_text là chuỗi kí tự chứa find_text, start_num là vị trí bắt đầu. Kết quả của hàm FIND sẽ trả về vị trí của find_text trong within_text. 

Khi tiến hành FIND lần đầu, ta sẽ tìm được vị trí của dấu cách thứ nhất.

Khi tiến hành FIND lần thứ hai, tại start_num ta lấy vị trí của dấu cách thứ nhất cộng với 1, khi đó Excel sẽ trả về kết quả là vị trí của dấu cách thứ hai.
Sau đó ta dùng hàm LEFT với công thức =LEFT(kí tự cần lấy chuỗi, số kí tự lấy từ chuỗi). Trong bài ta trừ đi 1 ở số kí tự lấy chuỗi để không lấy dấu cách vào loại sản phẩm.

Kết quả:

Sau đó, ta tiến hành Sort để phân loại sản phẩm theo các bước như hình:

Các bạn có để ý là trong tình huống thực tế thì chẳng ai làm ngược như bài hướng dẫn cả, khi mà nhập loại sản phẩm từ tên sản phẩm, phải không nào? Tuy nhiên thông qua việc “ngược đời” này, Học Excel Online muốn nói một cách chi tiết nhất về các tình huống phát sinh cũng như các hàm bổ trợ giúp các bạn không bị thụ động dựa vào các khuôn mẫu có sẵn, mà có thể tự chủ động tìm nhiều hướng giải quyết vấn đề.

Sử dụng hàm COUNTIF thiết lập số thứ tự thành phần của bảng phân loại

Bây giờ ta sẽ tiến đến bước thiết lập số thứ tự thành phần của bảng phân loại. Yêu cầu ở đây là mỗi loại sản phẩm sẽ có bộ số thứ tự riêng.

Có thể hiểu rằng ta sẽ tiến hành đếm từ 1 cho đến x tương ứng với loại sản phẩm. Khi loại sản phẩm thay đổi, bộ đếm sẽ trở về 1.

Để thực hiện ta làm như sau:

Tại ô A2 nhập công thức:

=COUNTIF($B$2:B2,B2)

Có nghĩa là với điều kiện chạy theo từng ô từ B2 đến Bx, vùng đếm sẽ trải rộng ra từ B2:B2, B2:B3… cho đến B2:Bx.

Sau đó áp dụng công thức cho những ô còn lại bằng cách fill:

Kết quả:

Vậy là chúng ta đã hoàn thành cơ bản bảng tính rồi. Tất cả những yêu cầu cần thiết đã được xử lý trong đây!

Tuy nhiên, ta vẫn có thể làm cho nó trở nên hoàn thiện hơn.

Hoàn thiện bảng tính bằng định dạng. Sử dụng SUBTOTAL tính tổng thành phần

Định dạng bảng tính

Hãy chăm chút cho “ngoại hình” của bảng tính bằng cách sử dụng các định dạng nào:

Đầu tiên là tiêu đề cột

Tiếp theo là thành phần các cột:

Sử dụng SUBTOTAL tính tổng thành phần bảng tính

Chức năng SUBTOTAL sẽ phân biệt các thành phần trong bảng tính theo những phân loại, đồng thời sử dụng các hàm tính toán giúp xử lý bảng tính nhanh hơn.

Đầu tiên, chọn toàn bộ bảng tính. Sau đó click vào SUBTOTAL. Một hộp thoại sẽ hiện ra như sau:

Tại đây ta sẽ lưu ý tới những vùng được khoanh đỏ:

–At each change in: Phân chia theo thành phần nào. Ta chọn phân chia theo Loại sản phẩm.

–Use Function: Sử dụng hàm nào. Ở đây ta sẽ tính tổng số lượng thành phần, vậy nên chọn hàm SUM.

–Add Subtotal to: Sử dụng hàm cho cột nào. Ta chọn Số lượng.

Sau đó ấn OK.

Vậy là cơ bản xong rồi đó các bạn!

Trong bài viết này, Học Excel Online đã hướng dẫn những bước cơ bản nhất về cách xử lý và định dạng một bảng tính thành phần. Hy vọng qua bài học này, các bạn sẽ có thêm được những kiến thức hữu ích để ứng dụng trong công việc một cách hiệu quả. Và đừng quên theo dõi những bài viết tiếp theo nhé.

Chúc các bạn học tốt