Tự động phân biệt doanh thu lớn nhất, nhỏ nhất trong báo cáo bán hàng

Hãy thử tưởng tượng vào 1 buổi sáng đẹp trời, bạn nhận được 1 bản báo cáo bán hàng của nhân viên gửi tới mà trong đó có thể tự động phân biệt những nhân viên có doanh thu lớn nhất, những nhân viên có doanh thu nhỏ nhất theo từng tháng. Thật tuyệt! báo cáo như vậy mới hay làm sao. Bạn sẽ dễ dàng phân tích được báo cáo đó, đánh giá được hiệu quả của từng nhân viên một cách nhanh chóng. Chúng ta hãy cùng tìm hiểu xem báo cáo đó ra sao nhé:

1. Mục tiêu

Dựa vào báo cáo bán hàng quý 1, xác định:

• Tháng có doanh thu lớn nhất của từng nhân viên
• Nhân viên có tổng doanh thu cao nhất quý 1

Kết quả cần đạt được như sau:

2. Cách thực hiện

Các bạn tải mẫu báo cáo sau đây để cùng làm theo hướng dẫn: Tải về file mẫu

Trong file mẫu chúng ta có bảng dữ liệu ban đầu như sau:

Yêu cầu 1: Xác định tháng có doanh thu cao nhất của mỗi nhân viên

Bước 1: Phân tích yêu cầu

• Trong báo cáo gồm 3 tháng tương ứng với 3 cột
• Tháng có doanh thu cao nhất là tìm giá trị lớn nhất
• Tính trên mỗi nhân viên là xét giá trị lớn nhất theo từng dòng

Bước 2: Xây dựng công thức tìm giá trị lớn nhất theo từng dòng

Hàm xác định giá trị lớn nhất là hàm MAX

Giá trị lớn nhất về doanh thu của nhân viên 01 (tại dòng 4)

=MAX(C4:E4)

Để tìm ra giá trị nào là lớn nhất trong 3 giá trị, chúng ta thực hiện so sánh từng giá trị với giá trị lớn nhất.

+ Với tháng 1 có logic kiểm tra như sau: C4 = Max(C4:E4)

• Nếu kết quả là TRUE thì C4 là tháng có doanh thu lớn nhất
• Nếu kết quả là FALSE thì C4 không phải tháng có doanh thu lớn nhất

+ Tương tự như vậy so sánh các giá trị ở D4, E4 lần lượt với hàm Max

Bước 3: Tìm hiểu về cách cố định tọa độ trong công thức

Do nội dung của hàm MAX luôn cố định từ cột C tới cột E, tương ứng với 3 tháng. Do đó chúng ta có thể cố định tọa độ các cột này để sử dụng công thức MAX được dễ hơn. Cách cố định cột: Đặt dấu $ trước tên cột:

=MAX($C4:$E4)

Khi áp dụng tương tự công thức ở bước 2 xuống các nhân viên tiếp theo, chúng ta thấy các dòng sẽ thay đổi tương ứng với mỗi nhân viên. Do đó không cần cố định các dòng (không cần đặt dấu $ trước số 4)

Khi đó ta có thể kiểm tra kết quả như sau:

I4 =C4=MAX($C4:$E4) => Kết quả là True vì 81 > 69 và 81 > 60

Áp dụng công thức I4 sang cột J, cột K từ dòng 4 tới dòng 17 ta có kết quả như bảng trên.

Bước 4: Áp dụng công thức trong Conditional formatting

Với chức năng Conditional formatting trong Excel có thể giúp chúng ta tự động tô màu, định dạng ô theo điều kiện. Ở đây chúng ta thấy các giá trị trong bảng I4:K17 đã có các kết quả TRUE, FALSE => Khi đó áp dụng Conditional formatting với vùng bảng này sẽ thực hiện định dạng cho các ô có kết quả là TRUE

Cách thực hiện như sau:

• Bôi đen vùng bảng I4:K17
• Chọn tab HOME > Chức năng Conditional formatting > Chọn New Rule
• Trong mục New Rule chọn Use a formula to determine which cells to format
• Thiết lập như sau:

• Công thức: Áp dụng công thức như ở ô I4
• Format: thiết lập định dạng khi kết quả đúng là màu đỏ, chữ trắng
• Bấm ok

Chúng ta thu được kết quả như sau:

Tương tự như vậy, chúng ta có thể áp dụng trực tiếp công thức đó vào bảng tính từ C4:E17 mà không cần thông qua bảng I4:K17. Ví dụ ở I4:K17 chỉ giúp chúng ta dễ hình dung ra cách thức mà Conditional formatting hoạt động.

Kết quả mong muốn như sau:

Tùy theo mong muốn làm nổi bật nội dung mà chúng ta có thể định dạng trong Conditional formatting cho phù hợp.

Yêu cầu 2: Tìm nhân viên có doanh thu cao nhất

Bước 1: Phân tích yêu cầu

• Doanh thu cao nhất sẽ xét trong tổng doanh thu = cột F
• Nhân viên có doanh thu cao nhất sẽ sẽ mỗi nhân viên trong toàn bộ danh sách nhân viên: Xét mỗi ô ở cột A với vùng A4:A17

Bước 2: Xây dựng công thức tìm giá trị lớn nhất theo từng dòng

Giá trị lớn nhất ở cột F = MAX(F4:F17)

So sánh mỗi giá trị ở cột F với giá trị lớn nhất ở cột F để xác định số lớn nhất:

=F4=MAX(F4:F17)

Bước 3: Tìm hiểu về cách cố định tọa độ trong công thức

Do vùng F4:F17 là vùng cố định trong mỗi công thức so sánh, do đó chúng ta cần cố định cả tọa độ cột và tọa độ dòng ở vùng này.

Công thức xét tại F4 như sau:

=F4=MAX($F$4:$F$17)

Tương tự với yêu cầu 1, chúng ta có thể xác định kết quả như sau (cột M):

Bước 4: Áp dụng công thức trong Conditional formatting

Tương tự bước 1, chúng ta tạo mới 1 Rule trong Conditional formatting như sau:

Định dạng màu nền là màu vàng, chữ đỏ, đậm

Kết quả thu được như sau (áp dụng trên cột F và cột M)

Như vậy là chúng ta đã xác định được đúng yêu cầu ban đầu về phân biệt các giá trị lớn nhất rồi.

Để phân biệt các giá trị nhỏ nhất, chúng ta làm tương tự với giá trị lớn nhất, trong đó hàm sử dụng là hàm MIN thay cho hàm MAX.

Lưu ý: Khi tìm giá trị nhỏ nhất cần thay đổi cách định dạng (format) để dễ phân biệt với giá trị lớn nhất.

—–

Chúc các bạn học tốt cùng Học Excel Online!