Tổng hợp doanh thu theo tháng và chi nhánh

Vấn đề bạn đang gặp

Bạn có nhật ký bán hàng của ba tháng: mỗi hàng có ngày, chi nhánh và doanh thu. Cuối tháng sếp hỏi tổng doanh thu từng tháng và từng chi nhánh. Bạn lọc từng tháng rồi cộng tay thì chậm, và mỗi lần số liệu đổi lại phải làm lại.

Bạn dùng =SUMIFS($C$2:$C$31,$A$2:$A$31,">="&F2,$A$2:$A$31,"<="&G2) với ngày đầu tháng ở F2 và ngày cuối tháng ở G2. Kết quả ra, nhưng tổng của mỗi tháng nhỏ hơn con số bạn tính bằng tay từ báo cáo gốc.

Lập bảng điều kiện và tính tổng từng tháng

Tổng doanh thu và số giao dịch của từng tháng

1. 1

   Làm gì: Ở G2 (Đến ngày) gõ =EOMONTH(F2,0).

   Vì sao: EOMONTH trả về ngày cuối cùng của tháng chứa ngày ở F2. Đối số thứ hai là 0 nghĩa là chính tháng đó, không dịch sang tháng khác. Bạn khỏi phải nhớ tháng có 30 hay 31 ngày.

2. 2Chỗ hay sai

   Làm gì: Ở H2 (Doanh thu) gõ =SUMIFS($C$2:$C$31,$A$2:$A$31,">="&F2,$A$2:$A$31,"<"&(G2+1)).

   Vì sao: SUMIFS nhận vùng cộng trước, rồi từng cặp vùng và điều kiện: ngày từ ngày đầu tháng trở đi, và ngày nhỏ hơn ngày kế tiếp của ngày cuối. Điều kiện có dấu so sánh đặt trong nháy kép rồi nối bằng & với ô chứa ngày.

3. 3

   Làm gì: Ở I2 (Số giao dịch) gõ =COUNTIFS($A$2:$A$31,">="&F2,$A$2:$A$31,"<"&(G2+1)).

   Vì sao: COUNTIFS đếm số hàng thỏa mọi điều kiện. Nó có cùng hai cặp vùng và điều kiện ngày như SUMIFS, chỉ không có vùng cộng.

4. 4

   Làm gì: Chọn G2:I2 và kéo xuống hàng 4.

   Vì sao: Dấu $ giữ vùng ngày và vùng doanh thu đứng yên, còn F2 và G2 đổi theo từng tháng khi kéo xuống.

Tự hỏi mình

• Vì sao mốc cuối là "nhỏ hơn ngày kế tiếp" mà không phải "nhỏ hơn hoặc bằng ngày cuối"?
• Nếu bạn đổi ngày ở F2 sang tháng khác, những ô nào tự cập nhật?

Điền vào chỗ trống

=SUMIFS($C$2:$C$31,$A$2:$A$31,">="&F2,$A$2:$A$31,)

Kiểm tra

Tổng theo chi nhánh trong một tháng

Khi cần tổng cho từng chi nhánh trong một tháng, bạn thêm cặp vùng chi nhánh và điều kiện chi nhánh. Khoảng ngày nằm ở hai ô riêng để đổi tháng chỉ cần sửa hai ô.

Doanh thu và số giao dịch của từng chi nhánh trong khoảng ngày G1 đến G2

1. 1

   Làm gì: Ghi ngày đầu ở G1 và ngày cuối ở G2, rồi liệt kê tên chi nhánh ở F5:F7.

   Vì sao: Bảng điều kiện nằm ngay trên trang tính nên bạn nhìn thấy mình đang tổng hợp gì, và đổi tháng không phải sửa công thức.

2. 2Chỗ hay sai

   Làm gì: Ở G5 gõ =SUMIFS($C$2:$C$31,$B$2:$B$31,F5&"*",$A$2:$A$31,">="&$G$1,$A$2:$A$31,"<="&$G$2).

   Vì sao: Điều kiện chi nhánh là ô F5 nối với dấu *: * thay cho mọi ký tự theo sau, nên "Hà Nội*" khớp cả "Hà Nội" lẫn "Hà Nội " (thừa dấu cách). $G$1 và $G$2 có $ để đứng yên khi kéo xuống.

3. 3

   Làm gì: Ở H5 gõ =COUNTIFS($B$2:$B$31,F5&"*",$A$2:$A$31,">="&$G$1,$A$2:$A$31,"<="&$G$2), rồi chọn G5:H5 và kéo xuống hàng 7.

   Vì sao: COUNTIFS dùng cùng ba điều kiện, không có vùng cộng. Ở bảng này ngày không kèm giờ nên dùng nhỏ hơn hoặc bằng ngày cuối là đủ; nếu ngày có giờ, hãy đặt mốc cuối là ngày kế tiếp như ở bảng tháng.

Tự hỏi mình

• Nếu có chi nhánh tên "Hà Nội 2", điều kiện "Hà Nội*" có còn đúng không?

Cách khác: PivotTable (sẽ có sau)

PivotTable (bảng tổng hợp) là công cụ để tính và tóm tắt dữ liệu mà không cần viết từng công thức. DeskLab sẽ có bài riêng cho PivotTable (bài L21-01); phần này chưa luyện được trong ứng dụng, nên bài hiện tại dùng công thức.

Vì sao lỗi này xảy ra

Excel lưu một ngày là một số, và nếu có giờ thì phần giờ là phần thập phân của số đó. Microsoft mô tả như sau: các chữ số bên phải dấu thập phân của số thứ tự là giờ, các chữ số bên trái là ngày. Định dạng ô chỉ quyết định cách hiện, không đổi giá trị dùng để tính: giá trị thật vẫn nằm trong thanh công thức.

Vì vậy một giao dịch lúc 14:20 ngày 31/08 có giá trị lớn hơn 31/08 lúc 0 giờ. Điều kiện nhỏ hơn hoặc bằng 31/08 so với 31/08 lúc 0 giờ, nên giao dịch đó bị bỏ ra, dù mắt bạn thấy ngày 31/08. Điều kiện nhỏ hơn 01/09 lấy được mọi giờ của ngày 31/08.

Với tên chi nhánh, nguyên nhân giống bài đếm chấm công: dấu cách thừa ở cuối làm hai tên khác nhau. Microsoft cảnh báo điều này với hàm đếm COUNTIF (khi đếm chữ, dữ liệu không nên có dấu cách ở đầu hoặc cuối, nếu không hàm có thể trả về kết quả không mong đợi), và trên lưới DeskLab, SUMIFS và COUNTIFS cũng không khớp ô thừa dấu cách.

• Tổng của một tháng nhỏ hơn tổng thật, đúng phần của ngày cuối tháng.

  Nguyên nhân

  Điều kiện cuối là "<="&G2 với ngày cuối tháng, nhưng ngày của giao dịch có giờ nên lớn hơn ngày đó lúc 0 giờ.

  Cách sửa

  Dùng mốc ngày kế tiếp và điều kiện nhỏ hơn: "<"&(G2+1).

• Tổng của một vài chi nhánh nhỏ hơn tổng thật.

  Nguyên nhân

  Một số tên chi nhánh thừa dấu cách ở cuối, nên không khớp tên trong bảng điều kiện.

  Cách sửa

  Nối dấu * vào điều kiện chi nhánh, ví dụ F5&"*", hoặc xóa dấu cách thừa trong dữ liệu gốc.

• Hàng dưới ra 0 hoặc kết quả lạ sau khi kéo công thức xuống.

  Nguyên nhân

  Vùng dữ liệu hoặc ô ngày bị trượt theo vì thiếu $, nên công thức nhìn sang vùng sai.

  Cách sửa

  Khóa vùng dữ liệu, ví dụ $A$2:$A$31, và các ô ngày dùng chung, ví dụ $G$1.

• Công thức báo lỗi hoặc ra kết quả lạ dù điều kiện đúng.

  Nguyên nhân

  Các vùng điều kiện và vùng cộng không cùng số hàng. Microsoft yêu cầu vùng điều kiện có cùng số hàng và cột với vùng cộng.

  Cách sửa

  Kiểm tra mọi vùng cùng điểm đầu và điểm cuối, ví dụ cùng là hàng 2 đến hàng 31.

Đọc xong, giờ thử

Luyện tập ngay (2 bài tập)

Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.

Bắt đầu luyện tập

Chấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.

Trên Excel thật

• Lưới DeskLab và Excel thật: điều kiện dạng ">="&F2 (dấu so sánh nối với ô ngày) là cách Microsoft dùng ở ví dụ của COUNTIFS, và trên lưới nó cho kết quả đúng. Trên lưới, điều kiện nhỏ hơn hoặc bằng một ngày bỏ ra giao dịch của ngày đó có giờ sau 0 giờ, giống như phép so sánh số thông thường; bài tập 1 kiểm tra đúng điều này.
• Ngày tháng: ngày trong bài viết theo dạng ngày/tháng/năm. Microsoft khuyên nhập ngày bằng hàm DATE hoặc lấy từ kết quả của công thức khác, vì ngày nhập dạng chữ có thể gây lỗi; hãy nhập ngày đầu tháng bằng =DATE(2026,9,1) hoặc lấy từ ô có sẵn.

Câu hỏi thường gặp

Vì sao tổng tháng nhỏ hơn tổng thật, đúng phần của ngày cuối tháng?

Giá trị ngày của giao dịch có thể kèm giờ, trong khi ô chỉ hiện ngày. Giao dịch lúc chiều ngày 31 lớn hơn ngày 31 lúc 0 giờ, nên điều kiện nhỏ hơn hoặc bằng ngày 31 bỏ sót nó. Dùng điều kiện nhỏ hơn ngày 1 của tháng sau.

Có cách nào tổng hợp mà không viết công thức không?

Có thể dùng PivotTable. Phần PivotTable sẽ có sau trên DeskLab, nên trong bài này bạn làm bằng công thức.