Khi bạn thức dậy vào một buổi sáng đẹp trời, bạn nhận được một bản báo cáo bán hàng từ các nhân viên gửi đến tôi, trong đó có thể tự động phân biệt những nhân viên có doanh thu lớn nhất và những nhân viên có doanh thu thấp nhất theo từng tháng. Thật tuyệt vời! Tuy nhiên, làm sao để bạn có thể phân tích báo cáo đó một cách nhanh chóng và hiệu quả? Hãy cùng khám phá cách làm chi tiết dưới đây 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:
Bảng dữ liệu báo cáo doanh thu
Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với các kết toán. Bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khóa học Excel:
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
Tiếp tục 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ại Địa Chỉ 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ại địa chỉ 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:
= C4 = MAX($C4:$E4) => Kết quả là True vì 81 > 69 và 81 > 60Áp dụng công thức trong Conditional Formatting cho phé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ấu hình Conditional Formatting
- 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.
- Nhấn OK
Chúng ta thu được kết quả như sau:
Kết quả áp dụng Conditional Formatting
Tùy theo mong muốn là 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ẽ xem 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ại Địa Chỉ 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ả địa chỉ cột và địa chỉ 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ể tạo thêm 1 Rule trong Conditional Formatting như sau:
Cấu hình Conditional Formatting cho yêu cầu 2
Đị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.
Chúc các bạn học tốt cùng Học Excel Online!